autocommit was set to 0 for a database and the database is 13 GB we need to shut the database down but when we
wee offline one works today ….
where was the setting in my.cnf to allow connections not only from local hosts ?
what would you use as a datatype in mysql for a column that will only hold calues between -2 and +2 inclusive
INT(2) signed ?
TINYINT
there is none, use TRIGGERs to enforce it
weigon i dont want to worry about enforcement
127
just looking for the most appropriate data type
in that case, follow Procyon`
yeap
ta
!tell xored about external
xored remove bind-address= and skip-networking from my.cnf and grant permission to the external ‘user’@'host’ and remove any firewall rules blocking port 3306
i have installed the mysql hosting odbc driver ( never worked with it before ). No trying to export a table from access directly through a odbc connection. But he asks me to choose a ODBC connection in a directory. What does that mean ?
play with it
and make sure to check the dropdown
your trying to completely move an access database into mysql?
I would suggest, rather that trying to migrate it in some crazy way, building a new mysql database hosting and migrating gently over to it
else its all going to go pete tong, guaranteed. I mean, trying to shove access into MS sql fucks up
its all a crock of shit
bah Shrews http://bugs.mysql.com/bug.php?id=29395 on win2k as well
bummer
silly bug
I want to do a migration job
I’m getting this error when executing a query. I’m MySQL 4.0.38, any hints? ERROR 126 (HY000): Incorrect key file for table ‘/tmp/#sql_4cdd_0.MYI’; try to repair it
so repair it , and check hardware
or is that a full temp error
archivist, I don’t have a file /tmp/#sql_4cdd_0.MYI
It’s perhaps a temporary table for a join. I’m not really sure.
low on disk space?
it may have failed to create when making a temp
I have 150mb but I will free more and try
yeah
guys i install odbc from MYSQL for windows..but how do i configrue it ?
How to implement datetime type value minus several days?
for example, select * from foo1 where createtime between (NOW(), NOW()-3days)
how to compose sql that can do something like this?
that was it, my bad.. thanks much!
edit my.cnf and restart mysqld
NOW() – INTERVAL 3 DAY
what is that interval function to use?
the mysql server does not run on the windows machine and i have been told, the ODBC driver is client only. So why should i modify my.cnf
!m JBond2 date time func
Sorry – I have no idea what function you’re talking about! but try http://dev.mysql.com/date time func
!m JBond2 date time
Sorry – I have no idea what function you’re talking about! but try http://dev.mysql.com/date time
dammit
!man date and time f
see http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
hehe
archivist:
oh I missed the odbc part
see the link from the_wench above
go to the control panel and edit it, I suppose. I don’t use that OS much.
Shrews or cheat !man now(
i’m just losing my mind
i blame therion
aw poor thing
You’re welcome!
is the mysql odbc driver free for use in a firm ?
hehe
that depends on the license.
“Comply with the GPL”
you were prompted to read the license at install time, were you?
As always for dev reasons, i pressed ok Iam not the one who will use it
iam the one who will administrate it
perhaps you can reinstall it and read it.
GPL
so its free, thank you
there you go.
hi, is there a better way to ORDER BY than “order by abs(X)” where X is the integer representation of a string? (i want to have “host1″ before “host10″, “host100″ and so on)
if not, how do i get the integer representation of a string
you can CAST it to INTEGER and make an alias
then, ORDER BY aliasname
andiolsi, store the number separately
hmm ill try that casting thing, storing the number isnt very practical here
is there a way to know when the corrent week begins and ends ?
andiolsi, that will be slow
if i could use the unix sort program i’d just add “-n”
the idea is to makes sure you can use an index
well i didnt create this database schema, its lacking numeric primary keys and so on, i just want that list to come out sorted correctly. the guy who is supposed to do the programming cant figure out how to do it. and i totally dont care for the performance, if it was for me, i would sort it
in the application.
sounds baaaad, have fun
:/
according to the reference manual i cannot cast just into “INTEGER”
if i have many cfg1 … cfgn how can i make regexp? ‘cfg(\d+)’ ?
is that correct ?
noob question, and a tad silly one… how do i empty a database without dropping it? ie. since i can’t recreate the database just dropping it and reacreating it isn’t an option…
speaking for regular expressions, yes if it fails try [0-9] instead of \d and maybe {1,} instead of “+”
thanxs andiolsi
works cav[0-9][0-9][0-9]
with cav(\d+) syntaxs works but not filter..
anyway thanxs a lot
Is there a way to stop a group by using a temporary table and a sort?
like ‘cav%’ could use an index and be faster
describe SELECT Pb1.id, Pb2.id, CDR_id, CDR.*
FROM CDR
LEFT JOIN (PhonebookEntries as Pb1, PhonebookEntries as Pb2)
ON ((Pb1.phonebookId = ‘1′ AND CDR_terminating LIKE CONCAT(Pb1.number,’%')) AND
(Pb2.phonebookId = ‘2′ AND CDR_terminating LIKE CONCAT(Pb2.number,’%')))
GROUP BY CDR_id
pastebin
LIMIT 0,10;
try http://pastebin.ca or http://pastebin.mysql-es.org
ops yeh, sorry forgot :/
hay
Horses eat hay
hoy
http://pastebin.mysql-es.org/62
i cant figure that cast thing out. i just need a statement to return the integer of a string whithout using variables aliases or such things, are you shure cast can do that?
andiolsi, probably need to cut the string part out
double slowness
i have a list of hosts, and i need to order them by hostname, how do i “cut” a string out, if its needed to do the sorting part?
a string is a string
?
host01
the hosts are named without leading 0’s
like host1, host2, host12 …
so accept the crapness
maybe i should just plain ignore you, it will be faster that way
Any suggestions how I can speed-up the following: http://pastebin.mysql-es.org/62
with less like and concat
Hi
Its fine (just) until I do a group by and since i know its a left join and I am grouping on the primary key why should it need to complete the entire query before it does a group? Surely it could just group them together as it goes along?!
repaste with the create for the cdr table (probably needs a compound index) or be done some other way
Voltaire, its not fine without the group by, note the cdr count
Yeh. It is slow, but since the user will be paging through data set it is at least acceptable.
Whenever you have GROUP BY the entire interim result set must be obtained before aggregation
hi
Seems like you are thinking it is possible to get first ten CDR_ids in ascending order and then apply them implicitly in WHERE clause so the aggregation is done on smaller set. Not yet implemented if at all possible
Guess ill have to open up the engine and add an optimisation case
Not the engine, but the Optimizer code
I want to clarify something – with mysql on a linux system is it pretty much standard to set innodb_flush_method = O_DIRECT and take a small penalty for writes?
Meanwhile you can do that optimization manually with temporary table and one more join
Possible i think in some sitauations; this being one of them I think.
how do you mean? Wouldnt that still require the full CDR set to be passed?
“Incorrect key file for table ‘/var/tmp/#sql_3dc4_1.MYI’; try to repair it” – any ideas?
As long as CDR_id is indexed no
this shouldn’t be happening. Please submit a bug at bugs.mysql.com
Nope if CDR_id is indexed. In worst case the entire index will be scanned, but not the entire table as in your query
you might have to walk me through your thinking.
will do… however, I’m not sure if I can create a minimal test-case for it
Voltaire suggessts interesting optimization in case of SELECT .. GROUP BY column_with_high_cardinality LIMIT N;
I wonder if such WL exists
please try to create least possible. If that fails, we prefer to have huge testcase than no tescase… it will be hard to analyze such bug w/o testcase…
karmazilla, out of temp space??
reading…
The optimisation would be in a case of a left join and grouping on a unique index only i think.
LEFT JOIN is another story.
does anyone know why the first query only uses where, but when the where is taken away it then uses filesort? how can i stop it using filesort? http://nopaste.com/p/akzIgOXPF
ain’t it… got 7.5 GB worth of available temp space
ok
however, the query has triple nested selects and 23 joins…. explain takes 10 minutes on it 
straight_join the optimiser is confused
sounds like a worthy idea
especially with self joins
you have to order the query execution though
the’re no self joins… only left joins to other tables and mostly on PKs or natural keys
salle: we have a bug in the works ATM that, perhaps, could help with this… Igor’s making cost-based choice between doing ORDER/GROUP BY + LIMIT and full table scan
http://bugs.mysql.com/bug.php?id=28404
So it is already there between the lines
yes, sort of.
Bad news. You can’t claim copyright of that idea
Thanks.
Fudge
by nested selects, do you mean FROM-subqueries? If yes, I’d try replacing “FROM (SELECT ….)” with “FROM tmp_table”, where tmp_table is manually made table with the select contets
spetrunia is the guru when it comes to teh optimizer not me
hmm nice to know that
where are the MYD and MYI files usually stored?
in the same place as .frm
In the database directory. Don’t ask where it is
i think its /var/lib/mysql/useraccount/
You should know.
I forgot
Hello
but dont exist for innodb
Is there away to see the information in the host cache (the one that gets flushed with “flush hosts”) ?
anyone know why the 2nd query uses filesort but not the first? http://nopaste.com/p/akzIgOXPF
I’m not use it’s possible to break it up in multiple queries… it’s a report query for Jasper reports, so I’ll have to investigate that
the first query can use a range with your listname key cause your like in the where clause starts with a character and not a %
the second one as to read all table and then sort
ah right
but i do have an index on listname
so why cant it just grab them all in the order of that index
is there a way to stop it using filesort?
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
ooh complicated lol. i’ll have a read through it later. thanks for ur hel
help*
I’m having problems using mysql dump. I use this command: “mysqldump -p –tab=. drupal” and I get this error: mysqldump: Got error: 1: Can’t create/write to file ‘/home/brian/dbbackups/drupal/access.txt’ (Errcode: 13) when executing ‘SELECT INTO OUTFILE’
does the table size affect query speed?
JoeyJoeJo, you dont have permission to write the file
yes it does
well, it’s weird because I’m running this command as root
mysqld runs as mysql
I just figured out that if I take the –tab subcommand out and just save all the stuff to a txt file, it works
so it’s something with that –tab
Hrm, may I ask what exactly is wrong here?
You have an error in your sql hosting syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”novice’ ( ‘id’ INTEGER NOT NULL AUTO_INCREMENT, ‘naslovnovice’ VARCHAR(100)’ at line 1
Where line 1 is
“CREATE TABLE ‘novice’ (
‘id’ INTEGER NOT NULL AUTO_INCREMENT,
‘naslovnovice’ VARCHAR(100) NOT NULL,
I really can’t figure out what exactly is wrong
Hi guys, I am tryng to setup dual master replication between two mysql hosting servers
one is 5.0.22 on RHEL5 and the other is 5.0.32 on Debian Etch
Matic`Makovec, are those single quotes? you need to use backticks
Matic`Makovec, or nothing at all
(or sql_mode = ansi_quotes)
from 5.0.32 to .22 it works fine but the other way round i run into a problem where the replication tries to create the database its replicating, but the database hosting already exists
HarrisonF, thanks
is this a bug?
weigon, where or how exactly do you do that?
isn’t multi-master the feature which is documented to not work ?
you build a ring, right ?
server-ids are unique and log-slave-updates is set
and you make sure that the two can’t write the same data at the same time
there are only two servers with unique ids
log-slave-updates?
imhotepp, the create database would only be replicated if it is in the binary log, did you start replication at the current offset?
and you write into both at the same time or only into one ?
both will be written to
HarrisonF, well I use double quotes before CREATE and single when it comes to name of table
I think that is my problem…
2 mins…
before we go on, you know about UPDATE, INSERT and DELETE anomalia ?
Matic`Makovec, that’s nice, you can’t use single quotes around identifiers
… or about conflicts and not existing conflict resolution
i am afraid not, any online resource?
disregarding network overhead/delays and considering proper indexes,
does making several small queries (1 per table) has the same performance as executing a big complex one (for JOINS)?
rsd, it depends on the execution path
what do you mean?
rsd, either one could be faster depending on the exact query
hi
rsd, the answer is maybe
for example, supose I have a SELECT for a table and there is a field city which is an external key to a table city
instead of joining it
in the same select
I do an extra SELECT to get the city name
when i do a select in an in-clause of a select query, my mysqld 5.1.11 (win) needs too long (actually not tested how long) to deliver the results.
for each row
srv1 executes UPDATE tbl SET fld = “foo” WHERE id = 1; and srv2 execs UPDATE tbl SET fld = “bar” WHERE id = 1; at the same time
if i paste the results from the inner select into the in-clause, it works within seconds.
any ideas?
what happens ?
rsd, do the join in that case
ok, but would it be a bug performance hit for mysqld?
big* not bug
to do it manually?
yes
the network round trips will kill you to do it manually, there is no point in doing it manually
I am working on a lib for this
and network is not a problem
weigon, they won’t be executed at the same time, the table will be locked for each update, but it will end up as either “foo” or “bar”
we have 2 databases which use async-replication. They WILL be executed at the same time
no ideas?
SELECT … WHERE x IN (SELECT … ) ?
yep
and if i paste the result set of the inner select statically into that query, it’s workin.
how are dual masters setup then?
they aren’t
in mysql-replication you can’t have more than one writer for a data-object at the same time
there is no protective locking
you can use MySQL Cluster if you need to
with sync-replication you don’t have this problem
the machines are far apart
over the inter-web-net
if you can make sure that the two machines never write the same data at the same time, no problem
another problem are UNIQUE indexes .. let’s say on usernames. You always need some kind of conflict resolution.
the easier way of conflict resolution is making sure there are no conflicts
damnit
i think i’ll downgrade to 5.0.x and see if the problem persists.
bug submitted: http://bugs.mysql.com/bug.php?id=30180
do you know of a manual page where we discuss a ring setup ?
no idea
SELECT … WHERE fld IN ( SELECT * FROM (SELECT …) AS foo))
it’s a hack but should work
it tricks the MySQL server host to read from a temp-table instead of executing the inner SELECT for each round
weigon, i’m sorry, but now i see 3 selects!?
nice, isn’t it ?
SELECT … WHERE x IN (SELECT … ) becomes:
SELECT … WHERE x IN (SELECT * FROM (SELECT … ) AS foo)
ah i got it.
crazy shit. thx.
the extra SELECT is bogus in the logic, but tricks MySQL into another way to execute the query
weigon, so there are some design issues in mysqls query handling?
i remember working for several months with a large data warehouse within an oracle instance – no such problems ever seen.
the IN (SELECT …) isn’t optimized at all right now
IIRC that will be fixed in 6.0 which will go into alpha soon
ok, so now i downgraded to 5.0.45 for nothing. but at least it’s called stable, feels better :]
ah nice it’s workin. thx again.
yes one hell of a query. I’m afraid we won’t be able to do anything about it until we can repeat this on our side.
hello
Hmm
from (
select * from enduserhistory.sms_new_minigloballog_combined
union all (
This means that it will read through the entire sms_new_minigloballog_combined tables
s/tables/table/
in postgresql there is a mechanism called vacuuming…. is there something equvallent?
….in mysql
?
I wonder if the problem with the query is that the temp table becomes just too big
not really. mysql table types that use mvcc tend to do the cleanup in-line, rather than pushing it to a background process
xzilla, purge is a background thread in InnoDB (for removing old tuples that aren’t needed anymore)
akin to autovacuum? i could have sworn all clean up was triggered by actually running events
if i wanna use the rhee3 binarys on my server, what do i need? do i just need the server rpm, or do i need client, shared libs and anyting else?
xzilla, for ROLLBACK itself it is generally done by the thread issuing the command (though it can be in the background as well, for example for crash recovery)
xzilla, I noticed, that some inserts hang indefinately
xzilla, so I thought, this could be due to a “dirty” table, or old indixes
xzilla, the removal of old tuples no longer needed is always done in a background thread
what spawns the background thread?
keex, that is most likely due to log file bottlenecks with disk writes
so I should apply any aplicable where clause to the “select * from enduserhistory.sms_new_minigloballog_combined” query ?
xzilla, it is always there, created when the server starts
xzilla, it checks periodically if it needs to clean up
hmm… so it is like autovacuum
xzilla, kinda, except much cheaper due to the fact we have a list of old tuples and you have to do a full table scan
this doesnt seem to jive with what i read on it, but its been awhile since i had to know the perticulars 
yes, if there is any. MySQL atm is unable to push selection conditions down into FROM (….)… you have to do it manually
note though, HarrisonF is an authority on this, and i am not, so take his info over mine
hi all!!
xzilla, it has always been that way
I can do it with the timestamp range check and the sessionId like … part, but the rest of the outer where clause is actually generated at runtime
xzilla, you may be thinking of the normal rollback command which is done in the thread
ok, xzilla thank you kindly
must be
i have some problem, there isn’t patch for use comma how decimal separator?
xzilla, pg treats them the same way (old versions and rolled back tuples), whereas InnoDB handles them differently
HarrisonF, how do I find out about these bottle necks?
sorry for my english…i’m italian
is there a way to force cleanup, ala manually running a vacuum command ?
keex, SHOW INNODB STATUS is useful
xzilla, nope
Hi, I have a db of about 150000 records to update over a WAN connection. the update takes 1-2 secs per row. is there any way to make this quicker?
if i wanna use the rhee3 binarys on my server, what do i need? do i just need the server rpm, or do i need client, shared libs and anyting else?
danielgee7, batch the inserts
how do i do that?
I inherited a MySQL database and I notice it has multiple indexes on the same field, with different index names
insert blah values(1,2,3,4),(1,2,3,4),(1,2,3,4),…..
is MySQL smart enough that it won’t actually index the same field twice?
the insert is pretty quick, its the update that takes long
add an index to your update matching criteria ?
danielgee7, write it as a select and explain it, check for index use
looks like my answer is no. http://www.mysqlperformanceblog.com/2006/08/17/duplicate-indexes-and-redundant-indexes/
add an index to your update matching criteria ?
danielgee7, write it as a select and explain it, check for index use
oops, sorry, was just copying that
you can do an update through a select? i’m lost
no its a way of checking for existence of an index
HarrisonF, I can imagine you are very busy. would you still have a look at the SHOW INNODB STATUS output?
keex, sure, paste it somewhere
keex,it is taken while the slow inserts are occuring?
does anyone know where mysql logs replication errors
– /var/log/mysql.log is empty
ok, i understand
http://phpfi.com/253772
HarrisonF, the insert simply times out
keex, the system is completely idle, heh
keex, paste it while the insert is hanging
HarrisonF, yes… that happens sporadically mostly in the middle of the night..
hey guys is it possible for a database to have a relationship with another database? for instance, if i have an datababase1.entity.entityId = 1; is there any possible way, database2 will ahve knowledge of that key?
HarrisonF, the dhcp-server that executes the perlscript, that in turn hangs during insert , then crashes..
in an integer column can I store a value like “34.45″
or would I need to specify it as decimal?
does that look like an integer
no
if money then decimal
is there a decimal data type for mysql?
keex, well getting an SHOW INNODB STATUS when it is hanging would be very useful
(which is what i was originally asking)
yes
ok thanks
money decimal (4,2) ?
Yes, on the same server. For accessing remote databases, see the federated storage engine.
problem still exists. I have data and when i load it, it cuts off the first char of the first column. how do i correct this? if I put space(s) in the column it picks up the chars but I don’t want to do that
BlkPoohba, wont get rich on (4,2)
i agree but doesn’t that cover 34.45?
4 digits 2 decimal. isn’t that what that means?
we dont know his real range
money decimal(8,2) should cover the average joe. super joe may need money decimal(10,2) avg Gates would use something like money decimal(15,2) i wish i was a Gates.
what about a Gates in Zimbabian $
hehe
anyway… back to my issues. what would cut the first char off the load data
bad line end
‘\n’ i assume, its a flat file
but it does it for the first line also
I have a feeling that the importer is fussy about them I had a problem last week
how did u resolve it? i tried copying to a new file, saving in different formats, csv, tsv using notepad, gvim, nothing seems to fix it
I cured my problems with a php csv import
i was thinking about writing a perl script to do it but damn… i shouldn’t have to
I know
dudes, i want a field in a table wich can be text, or a number or an IP, how can i define it?
I was getting data correctly for x lines then after that every sedond line was in the last field
something like “(…,$FILEDNAME,….)”
StereoSkit, think about how you will search it
i dont get it
hello people
what the best maxine for run mysql in big supermarket?
varchar?
hi all
ill just say, “SELECT $FIELD FORM …”
form
from
merde
been server!!
varchar?
i am quite new to mysql, and have an general question about schemes.
when building (example) an inventory list, how to make the “tree-like” structure?
categories have subcategories etc.
trees
http://www.dbazine.com/oracle/or-articles/tropashko4 and http://www.sitepoint.com/article/hierarchical-data-database, http://jan.kneschke.de/projects/mysql/sp/ http://jan.kneschke.de/projects/mysql/sp/sp_tree.sql http://www.vbmysql.com/articles/database-design/managing-hierarchical-data-in-mysql/ http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
ok
can mysql run things from the command line?
i know im crazy for askin
so, most webshops, build their “catalogue” with a tree?
some
ok, so a nested set should be good, for building a inventory-db, right?
hi, how can you change the engine for a temporary table?
danielgee7, why?
created a temp table to do that update I asked you about. I think I set it to memory and must have run out of memory space.
btw, the update runs within 3 secs for 156000 records by doing it that way.
I think there is a disk option iirc
thanks! busy reading up on it now. thanks for your help.
i have some problem, there isn’t patch for use comma how decimal separator?
cristianmm, you could use replace() to change the . to ,
Anyone have any good suggestiosn for an alternative to ‘SELECT DISTINCT’ ?
!man replace(
see http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
group by ?
doesn’t that still use a temporary table?
Trying to speed up a query..
that wasn’t the question
lol
details details..
^__^
Any other ides?
group by
perhaps you could show us the query and the explain for that query
“Anyone have any good suggestiosn for an alternative to ‘SELECT DISTINCT’ ?”
for example, does the distinct operate on a key column?
hello folks
Dont forget the bots!
hello bots
i have a column named last_login which lists date/time – is it possible to count the number of records matching the current date?
other words i would like to know how many accounts were active today
not sure how to make such date comparison
select sum from table where datecol = now()
damn.. this easy?
sum(*), even
yes
thank you so much – let me test now
you have to convert now() to the whole day too, probably
but does it matter that same column also includes time?
not if you select on the whole day
hm
0 and
when i importo a utf-8 file in a utf-8 database, wich copatibility mode should i choose ?
i had no idea how flexible mysql was…
many thanks adaptr – testing now
kaydara compatibility mode ? ITYM collation
itym ?
adaptr, itym stands for ?
0 and
any sense in that?
well, no
select id from db1.account where last_login = now();
but it’s better – just returns empty set
0′ AND ‘2007-08-02
!man sum
see http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
!man between
see http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html
oh damn this is really kind of you as i was about to dig into docs
!man now
see http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
you are going to dive into the documentation
he he
about to test your line plus i will do some heavy reading…
thank you again
err it doesn’t like the sum(*) thing
!man sum
see http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
9 [ERROR] Slave: Error ‘Duplicate entry ‘660551′ for key 1′ on query. Default database: ‘drupal52′. Query: ‘INSERT INTO accesslog (title, path, url, hostname, uid, sid, timer, timestamp) values(”, ‘rss.xml’, ”, ‘209.85.238.5′, 0, ‘0434af6d7703776f55a7032afdc7f636′, 0, 1185838032)’,
Error_code:
I keep getting that error (although the replication still seems to be working).. is there an easy way I can delete that entry on the slave?
adaptr just found out that COUNT(*) is better than SUM(*)
how would I set up a foreign key to keep anything from being deleted in the referenced table if there was still an instance in the child table? RESTRICT?
CONSTRAINT ?
a foreign key by definition is a constraint.
then you cannot delete from the referenced table if there is a NOT NULL on the column, obviously
please, what command do I use to change variables like collation_connection, collation_database, etc?
how can i get this to go in alphabetical order?
“SELECT actor FROM all_actors WHERE actor LIKE ‘” . $letter . “%’”
obviously you’re not familiar with the different options regarding the actions possible
LoRez obviously, yes – so why are you asking ?
Chatmasta add ORDER BY actor at the end
I would like to change these from latin1_swedish to latin1_general_ci
really new to mysql here, but how does the database privileges work? is it possible to allow a user to create/manage his own databases without giving him access to all other databases on the server?
adaptr, i did that, but it just returns blank.
Chatmasta then the query returns blank without it as well
it doesnt though 
im not getting any PHP error, either, even with error reporting on E_ALL
then you’re not doing exactly what you’re showing
ieugh
also, you’re comparing to a string, use LIKE $letter.’%’ instead – you probably didn’t close your quotes properly or something
nvm, i got it
never mind ? I don’
t think so.. show what you screwed up!
how will anybody ever learn unless you tell us what it was ?
it wasnt a eok that didnt work like i thought it would
adaptr, what i thought was the problem had absolutely nothing to do with mysql
that made zero sense
i just was uploading the file
ah, okay
er
uploading the wrong file*
what can typically cause a table to be “crashed”? Is it only if the server is ‘mistreated’? (e.g. by shutting it down abruptly) .. or can it happen through normal usage of the database?
will an alter table to add a foreign key take a long time on a large table?
i want to find all records where col3 character length is less than 5
Can I make a table that uses _two_ columns as the primary key?
yes
depends, but the time is proportional to the size of the table. Also, the table is read (and sometimes write) locked during the update
aliver sure
What do I google on to see an example of how to do that?
search the mysql docs for “PRIMARY KEY”
Oh, that’s all? Okay.
yes
standard feature
primary(field1,field2)
it has 10.3M rows in it.
then it’ll take 10.3M times X, where X is an unknown quantity that is much smaller than a second
(I created a FK today on a table with 10k rows in it and that was almost instantaneous)
so it’s never instantaneous, it rebuilds the table…
can someone please help me understand this code? it doesn’t make sense to me because it seems like its saying if you haven’t connected to the db – http://pastebin.ca/641947
i said almost
Hmm, nothing about how to use two columns as a primary key is in the section that talks about primary keys.
!man primary
see http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
PRIMARY KEY [index_type] (index_col_name,…)
the ,… means “add whatever needed”
so yes, it’s very much there
Ah, okay, my bad.
if you really enjoy the experience you can add up to 254 columns to a compound primary
Uhh, that doesn’t sound so good.
but one might question the need for a database in that case
so I’m not sure where that code says to connect to the DB in the first place
would someone be able to help me create a wordpress plugin?
isn’t that just *wrong* in some strange geeky way?
(254 columns)
like, you must be insulting some MySQL deity in doing that
not really, if you choose the order right you end up with a clustered index
it’s just not particularly *useful* for any kind of normalized table
hrm
(it’ll be a clusetred index on the entire row)
Hi, i’m getting an error with this statement. please help – LOAD DATA LOCAL INFILE ‘c:/tempstk.txt’ INTO TABLE tmpdb FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (gcode,SC2)
how do i stirp ‘-’ from a col?
Please someone could help me to find the good index that I should put on a table for this query ? http://pastebin.com/dd84e07d
I tried multiples index (multiples or not) and it’s not good
b4ckUp, you are only showing one of the tables and no explain
what explain do you want ?
oh sorry
explain of this query
okay
Therion!
hiiii!
does mysql support foreign keys across databases?
yes
domas|TW!!@!@!!!2334$##@@#!
whoah
wth.. are we in a young girls high school now?
can anybosy help please?
yes
*anybody
young girls!! where?!!!?
gief!
can you help me with with the error is here – “LOAD DATA LOCAL INFILE ‘c:/tempstk.txt’ INTO TABLE tmpdb FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (gcode,SC2)”
http://pastebin.com/m639f9460
)à
anyone able to help me with this code? it doesn’t make sense to me – http://pastebin.ca/641947
swimrr is it SQL ?
what’s going on with your code ? and it’s PHP
mysql
its from a book, but it confuses me – doesn’t it say that if you’re not connected, to die – yet it never has a connect command?
mysql_connect returns TRUE if connected
line 1 is the connect command
but that only connects to the DB right, it doesn’t select the table?
while u’re doing a $var = php_function(); You execute it… But it’s a PHP problem, not MySQL
doesn’t line 2 say that if ‘myblog’ is not selected, to die the command?
if you are connected, it tests also the select_db
nope, there’s !
on PHP !function(); it’s like NOT function();
b4ckUp, a compound index on comments id,userid or userid,id whichever works best
so if it can’t select your db, it dies, else, you’re connected
and PK on id only ?
huh – I thought line to was trying to select a table in the dB
thanks
how can i show all procedures?
NULL in possible_keys and ref in both cases
I am having a little trouble with a GROUP BY statement. I have duplicate entries obviously, and one of my columns is reporting the last variable while reporting the first group’d by item… I need to change it to match the first grouped by item
b4ckUp, just noticed c.date as well
I don’t understand why
has to scan the entrie table looking at the dates
yep
so idx with id,userid,date ?
dunno if date first will be best but try
how can I retrive the latest recor in a table ?
how can I retrieve the latest record in a table ?
b4ckUp, and run analyse table to it can make a better guess
it lists it as possible_keys but MySQL doesn’t use it… Again 130k rows with “ALL” type
HaNNiBaL80, first decide what latest means to you
by date
b4ckUp, then it may think50% and a scan is quicker
or id, its always the last one to enter
order by date/id desc limit 1
so there’s nothing I can do ? I have to scan the 130k rows each time ?
date/id? What kind of date is that?
it takes more than 4 ou 5 sec each time
rejig to a subquery to trick the optimiser or whatever
a subquery like what ?
I didn’t use this before, if you can show me it would be great
I have a innodb problem
Uh, very strange. I tried the idx (date,userid,id) there’s the idx on “key” but not in “ref”…
order in the index matters
it needs to match the query’s needs
I see, but MySQL doesn’t use it… (I follow the query’s needs)
hello all
i have having an issue i have never encountered before
autocommit was set to 0 for a database, and the database is 13 GB. we need to shut the database down, but when we start it back up, its going to need to read in those transactions that were not committed, which is going to take a very long time (this happened once before, and it took 2 days
to start back up). This time there is even more data. is there a quicker way for us to start this back up?
let me make a pastie… but the short version is, it seems like records are missing for certain queries, but exist for others
s/missing/not retrieved/
The initial problem requiring us to restart the DB ios that it has reached it’s thread max, and we’re unable to connect to it.
what could be the cause of this nonsense
http://pastie.caboo.se/84083
and about the subquery ? How can I do that ?
can I make this assumption…if I’m looking at all the code to create 2 databases including their tables inside, and I don’t see any triggers/views or references to another database which is on the same server, that there is no connection from 1 database to another, and they must be using
external code to match up data, like select db1.tab1, db2.tab2 to combine the data?
is this valid GRANT ALL ON ejabberd.* TO ‘ejabberd’@'10.0.0.0/8′ IDENTIFIED BY ‘password; ?
valid for what ?
does it parse ? yes
10.0.0.0/8 , insted of localhost as it was before
the syntax
although I’m not 100% sure on the netblock
what is the limit for PK on innodb’s?
have you *tried* it ?
there’s a ‘ missing near password;
how do i remove the previous a grant command
i want to remove the previous block i set
revoke
b4ckUp, c.userid!=6 basicly says all the table except which will be 50% therefore a scan
b4ckUp, revoke ALL ON ejabberd.* TO ‘ejabberd’@'10.0.0.0/8′ IDENTIFIED BY ‘password’; ?
so without c.userid !=6 my query should be faster, archivist ?
may be
the c.date may also equate to a large part of the table
yep
I tried without c.userid !=6 and the result of explain is the same
how do i strip ‘-’ from 99b-2345?
!man replace(
see http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
Hi! Is it possible to do this? Every MySQL-user should be able to create databases and remove the once he has created. The user shall have full access to the databases he creates. But a user shall have no access to any other database.
!man prefix
Sorry – I have no idea what function you’re talking about! but try http://dev.mysql.com/prefix
so, nothing could be done ?
dunno rethink your sql
rejeep set up database prefixes for your users, this allows them to create prefix_database databases at will, and only have permissions on those
how do i allow all hosts in a domain GRANT ALL ON ejabberd.* TO ‘ejabberd’@'domain.com’ IDENTIFIED BY ‘password’;
rethink my bdd design or query ?
i want *.domain.com
@’%’
@’
b4ckUp, whichever gets the result you need
raah !
adaptr, Ok! Any link where I can read about it?
@’ or @’%’ ?
all hosts, and ‘%.domain.com’ but it’s ALL IN THE MANUAL !
rejeep, just read the grant docs
rejeep the manual ?
okay, but I have to go where ? Looking for a subquery ?
rejeep GRANT ALL ON userprefix_*.*, or summin’
b4ckUp, sorry
you’re welcome
Ok, thanks guys, I’ll try!
I really need to fix this query ASAP
Any ideas for a decent back up solution, php based?
Anyone around with a Windows MySQL 5.1 setup?
I want to know if –use-threads is appearing in the mysqlimport binaries.
is there anyway to get around the connection error “can’t create new thread” without restarting mysql so that we can make a connection (even if it’s just one)
Login as root
if i do a truncate table ‘table1′ it will clear the data but leave the table’s fields and datatypes alone, correct?
what datatype do you usually have for dates?
Codler – http://dev.mysql.com/doc/refman/5.0/en/datetime.html
http://google.com
!man truncate
see http://dev.mysql.com/doc/refman/5.0/en/truncate.html
yes btw
ok
lol
So you use Google to answer someone else’s question, but not yours?
http://pastebin.ca/642025 why is ip continued to the next line?
seekwill – i get generic crap answers =/
Try searching: site:dev.mysql.com truncate
seekwill – from the first line of the 5.0 mysql doc: TRUNCATE TABLE empties a table completely.
completely? as in removes the fields? or just the data within the table?
Well, what would you have without the fields?
Is there a newline in the “ip” column for that row?
Why didn’t you read the second line?
i was just thinkin that. shouldn’t be but probably is
seekwill – a table with no fields – hense a a completely empty table!
A table must have at least one field
You might want to try visually highlight any strange whitespace with something like: SELECT CONCAT(‘–’, ip, ‘–’) FROM combined;
There’s a newline…
seekwill – oh, didn’t know that – but that ensures me it won’t kill my fields
The second line would have told you that.
again, vague statements, though
Basically, it’s the same as “DELETE FROM table”. On other databases, like Oracle, TRUNCATE behaves somewhat differently
practical differences?
How does it behave in Oracle?
It ignores transactions, rollback segments and nukes all the rows in a table very very quickly.
In MySQL, there’s no practical difference between TRUNCATE and DELETE
Not for MyISAM
ghakko, there is
As stated in the truncate pge…
There is? I’ve got 5.0.1 installed here, and the docs say there’s no difference
Where in the docs does it say that?
Section 13.2.9, in the documentation for 5.0.2: “TRUNCATE TABLE is mapped to DELETE”
You’re missing the prefix…
You’re taking it out of context
For InnoDB before version 5.0.3, TRUNCATE TABLE is mapped to DELETE, so there is no difference.
ghakko, online delete docs … As stated, a DELETE statement with no WHERE clause deletes all rows. A faster way to do this, when you do not want to know the number of deleted rows, is to use TRUNCATE TABLE. See Section 13.2.9, TRUNCATE Syntax.
http://dev.mysql.com/doc/refman/5.0/en/truncate.html
Aha, I see. It’s changed since 5.0.3. I’ve got an older version
Sorry about that.
This applied even in 4.0
wooot woot! I finally turned up circular replication with 4 nodes
Good luck!
hi all, how comes I can use ‘mysql’ (console one) but when I try to telnet localhost 3306 / or nmap
next – automatic fail over
my 3306 port is closed.
I am sure that it runs on 3306 fresh install and didn’t changed anything.
because it’s connecting through the sock?
so I can’t use external program to connect to it?
external
remove bind-address= and skip-networking from my.cnf and grant permission to the external ‘user’@'host’ and remove any firewall rules blocking port 3306
The MySQL client can connect over a Unix socket or over TCP. You may have brought up the database with networking disabled
I don’t even have my.cnf )
make one
I have 3 .cnf huge medium small and another with 4gb
ok will take a look to those then
Are you really going to be connecting to your database from a remote host? Or are all your applications running locally on the same host?
on the same host, but just for ATM i need to be able to connect from external IP
I am designing the database with an application and I don’t want to get sql, upload, run. over and over
this software allows me to connect directly and do that stuff.
I think my “my.cnf” is weird one the_wench I don’t have any bind-adress part or something like that
Firewall
dont have any
“bind-address” probably won’t be in the stock my.cnf file. It’s not needed unless you need to make MySQL listen on a specific network interface. If you leave it out, it’ll accept TCP connections on any network interface
What you do need, though, is to comment out “skip-networking” to allow TCP connections
still same result, just restarted the server and put the my.cnf in /etc/
I can connect with phpmyadmin/mysql
but the port is closed
ocZio, what op system
linux, 2.6 kernel, slackware distro
haha
You’ve got to figure out where your my.cnf file ought to be, if not in /etc
any firewall
I don’t have any firewall!
ok ghakko will check that too
Why not?
You might want to look at the output of “mysqladmin”; it’ll tell you where MySQL expects its my.cnf file to be
./usr/libexec/mysqld –verbose –help | grep cnf
=/etc/my.cnf ~/.my.cnf /usr/etc/my.cnf
Ok. Did you comment out “skip-networking” or UN-comment it from my.cnf?
I think you need to give me root access to the box.
hey there, is there a way to instead of logging every request only log 1 or 2 dbs requests ?
I can solve anything.
What kind of request?
right now it keeps the logs of all querys
i just want a few dbs to be loged
General query log?
yes
!man general query
see http://dev.mysql.com/doc/refman/5.0/en/query-log.html
thnx
That was for me…
Nope, doesn’t look like you can
ok
so it is either log all or none
sure
I don’t think you can log only certain databases in the general query log. You can, though, do that with binary logs
ghakko I found the solution
k
in my rc.mysqld there was a –skip-nerworking in the command line
Was that Slackware’s fault?
I think so, since the script comes with the package (:
now I am able to connect
but my host isnt allowed to access lol
!man grant
see http://dev.mysql.com/doc/refman/5.0/en/grant.html
Be careful not to grant public access to your database.
well god damn I am sick of this :p all the time I took now… I could use that time to just upload and run the script lol :p
Blame Slackware
I blame ghakko for not spotting the issue earlier.
seekwill updated…
anyone know what im doing wrong when creating a hardlinke between apache and mysql
Invalid cross-device link
external
?
remove bind-address= and skip-networking from my.cnf and grant permission to the external ‘user’@'host’ and remove any firewall rules blocking port 3306 and make sure no overrides on the mysqld commandline
hehe
anyway thank you guys
I will just upload and run the script with phpmyadmin
What’s a “hardlink” between Apache and MySQL? Why would you want that?
well there both in chroot
so i need them to talk
Good luck
arnt i suppot to copy the mysql.sock to the tmp folder
slackware sox
*rox
Slackware sucks
seekwill, slack own your little ass
k
whats your problem about slackware
i like slackware
i used it for the longest time
hard to use ?
You can’t make hard links across file systems, and you can’t symlink your way out of a chroot. You need to either put the MySQL listener socket in the Apache chroot, or accept TCP connections instead
ahahaha
randoman, +1
Their weird users.
ok though so
i read that
ok
so just copy it
You can’t just copy the socket in!
for sure
any ideas?
Are both MySQL and Apache chrooted? If that’s the case, then you need a bind mount to get past this
yes
how would i go by doing the tcp connections
i guess ill google it
afternoon all. i’m using the nested sets method of tree storage and have done an explain. (http://www.slexy.org/paste/4124). there are 62 rows in the LeadSource table. in the explain there are 3 tables in which it is estimated that it will have to
look at all rows. is this query going to be horrid when there are more rows? is there a way to optimize it any better?
I think the_wench was explaining to ocZio how to do just that 5 minutes ago.
ahh
missed that
ill try to scrolll up
external
remove bind-address= and skip-networking from my.cnf and grant permission to the external ‘user’@'host’ and remove any firewall rules blocking port 3306 and make sure no overrides on the mysqld commandline
Do you collect stats to see which is the most frequently asked factoid
no
I could trawl the log
But it would be cooler if you used SQL.
Doncha wanna be cool?
DONTCHA!
I have a desk fan to be cool
lol
With the added overhead of the UPDATEs, it’ll keep your case fans on
With the added overhead of the UPDATEs, it’ll keep your case fans on
gentoo–
multi
!man multi server
Sorry – I have no idea what function you’re talking about! but try http://dev.mysql.com/multi server
seekwill wot ugrade to use triggaaaars
heh
a friend suggested to use the binary packages. he thought it’d be easier maintenance
fatpelt, there is a page on the subject
But that’s not gentooy
!man running m
see http://dev.mysql.com/doc/refman/5.0/en/multiple-servers.html
hehe. yeah i know. we’ve got a new server on order, but i’m thinking that i’d like to get a head start. i need some trigger action
You can emerge the second MySQL server into a chroot, if you can spare the CPU cycles and disk space
fill yer box like snoyes does
hmm. that is an interesting one. that would allow for pretty easy maintenance especially if i build binary packages for the chroot
/me currently have 317G available. we’re ok for a while
is there a chance of reversing a drop database command?
i just dropped a database by mistake…
pick up database;
from your backup
i don’t have the latest backup
like 12h old..
jbalint did you mean that’s a command that will reverse the drop?
just don’t tell me that the data are gone…
must be possible to reverse that
i was joking
thats only 12hrs typing or learn to use binlog, if it was on
archivist it was off
so no way to get data back?
you don’t replicate?
hmm, you could restore the 12h old backup, setup a slave and import it, then setup a master and replicate via binlog. I’m not sure how you’ll get the binlog position though
wrt nested sets, since i have to update the tree on inserts and deletes, should i also just store each node’s depth in the tree too?
what’s the difference between org.gjt.mm.mysql.Driver class and com.mysql.jdbc.Driver ?
hello
“10.15.9″, “1.5.19″, etc… However, probably “9″ will be bigger than “10″. How to sort it correctly?
SettlerX, better normalising
??
3 int columns
or table or…
i don’t know how many levels will be…
exactly then..
normalization
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html and http://datamodel.org/NormalizationRules.html and http://mysqldump.azundris.com/archives/20-Nermalisation.html and some here http://www.keithjbrown.co.uk/vworks/mysql/
hehe, i just went through those hoping to answer a different question
is it that you’ve got child & parent items in the same table?
yes, all categories are in 1 table
so “1.5.19″ means that 1 is a parent of 5 is a parent of 19 is a parent of current row?
yes; or 19 = current category’s ID for avoiding problems
dumped my .mysql database, its like 10 megs…. how would i go about splitting up the file into many many many many chunks…
I am running nix
SettlerX, you’re gonna have trouble working with that model
just use a parentId which is a foreign id pointing back to the id
i have parentID column but i have to display all categories too without recurention or display higher categories of a category.
!tell SettlerX about trees
http://www.dbazine.com/oracle/or-articles/tropashko4 and http://www.sitepoint.com/article/hierarchical-data-database, http://jan.kneschke.de/projects/mysql/sp/ http://jan.kneschke.de/projects/mysql/sp/sp_tree.sql http://www.vbmysql.com/articles/database-design/managing-hierarchical-data-in-mysql/ http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
good bot
i can go either way on this, but i’m thinking it’d be better to optimize for viewing… anybody have a suggestion as to whether i should make a column for min height, column for max height, & then a true/false as to whether it’s in metric or not… or whether i should make a column each for
metric min/max & inches min/max?
or might the bot have an answer?
bot smack
bot snack
botsnack
ooooh let me bend over and lift my skirt for that
uhh
hello
is there’s a command/query i could run to see all the external connections?
i know i enable the query log i could see them
but is there any way to check them on the fly (ie like mtop/mytop would show)?
show processlist; ?
that’ll show local connections too
hehe that would work, thanks, i’ll filter the output
silly me
how can i see what character set a table is using
describe tablename?
nope
heh ur right
hello there. im getting some “Didn’t find any fields in table” and “Incorrect information in file” on two innodb dbs
what could be wrong?
Exact error message
thanks guys, bye!
!m qwerty_ repair
qwerty_ see http://dev.mysql.com/doc/refman/5.0/en/repair.html
phpmyadmin will show that info, but i’m not sure what db command it uses
Repair InnoDB?
i dont think thatll do
What is the exact error message you are getting? Plus the query.
no queryes
You’re getting the error in the paniclog then?
show table status shows me all tables corrupted
ew
“corrupted” == Incorrect information in file
where the innodb data tables stored?
data files*
all in one tablespace in the datadir
I keep getting the error “/usr/bin/ld: cannot find -lmysqlclient_r”. I also apparently don’t have the “libmysqlclient_r.so” module. I compile mysql from source and it’s running fine. This error comes when trying to compile mysql-administrator
How is it possible that the same exact query will result in different output if the DB has not changed and there are no calls to random?
derenrich, an innodb estimated number?
What is the query?
no, nothing like that
Does it involve floating numbers?
no
it’s just selects, joins, a where and an order
ooh, this is like 20 questions.
Does it involve self joins?
upper left middle sidways inner join
.frm files?
.frm is not the data
only inner joins and no frms
just the format
SELECT b.fund_id, b.fund_name, r.fund_id, r.ytd, a.fund_id, a.user_id, a.user_type
Does it involve VARCHAR?
FROM watchlist a
JOIN funds b ON a.fund_id = b.fund_id
JOIN return_stats r ON r.fund_id = a.fund_id
WHERE a.user_type = ‘??’
AND a.user_id = ‘??’
that’s the query where the ? marks are some values
And actual and expected results.
wheres the datadir then?
qwerty_, sounds like the innodb table space is missing/unreadable
actual results vary randomly
(seemingly)
Again, how about pasting some examples so we don’t have to guess?
what varies i should say is the number of rows returned
qwerty_, see my.cnf for your location
sadly i can’t paste examples
Well, if it’s number of rows return, it’s different.
Someone else is making changes.
/etc/my.cnf has no location in it
Or you are and not knowing
I’m doing the queries from phpmyadmin…So there aren’t side effects of the queries. I doubt changes are being made, but I’ll check that possiblity out
thanks
help now is apreciated :/
mysqldump -T db db -u user -p Can’t create/write to file (Errcode: 2)
all.sql works fine
I have a dilemat between materialized path and nested trees
qwerty_, check the difference to your backup my.cnf
I’m going crazy.
is implimenting nested trees atm on one of our systems
s/is imple/i am imple/
so you mean?
sorry, i missed some chars there. i’m currently in the process of implementing nested sets
usorry, i missed some chars there. i’m currently in the process of implementing nested sets /u
havent been touched since 2005
anyone know why i would have “libmysqlclient.so” but not “libmysqlclient_r.so”?
I discarded nested trees before (too hard) and the method of making all connections between categories too.
I’ve thought materialized path is the best solution (but there are some variants of it). However, I have read more about nested trees and i think about use this method.
However, i want to display all categories in good sequence and i don’t know if nested trees are so good like materialized path.
SettlerX, seemed good to me .
as per the queries in the dev.mysql article
in materialized path i could count amount of . to get the level of category. How is it in nested trees?
depth
in one of those queries
look at them all
i’m in the middle of implementing nested sets too
or perhaps better said, reimplementing
seems this is the place to be…
of course, the php code built around it is very complex
all connections between categories is also good method but i had a problem with sorting…
so.. i’m having some trouble cleaning it up
well.. i did have 1 sorting problem
so far
that i haven’t gotten an answer for
sorting thread head, while keeping p/c relations
anyone know why how I can compile mysql and still not have “libmysqlclient_r.so” but do have “libmysqlclient.so”
but it’s prolly due to my sql fu being weak
Post your problem description (URL). I’ll take a look when I have a moment.
SharkWave, i might be able to tell you if i knew what the _r part meant
my assumption is restart
typically what that means
for instance, if inturrupted
that doesn’t seem right?
the call should restart
maybe..
does anyone have any opinions on the GA quality of the 5.1 beta? how unstable is it?
Xgc, i’ll bother you some other time when i clean this crap
Xgc, i’ll bother you some other time when i clean this crap
the code has all this stuff for depth buoys and such
bit above my head
hrm
trying to take theparts i don’t need out still
keep breaking it in the process tho
if i have a query that’s using count/having/group_by, how do I actually count() the total number of rows
5
thats the query
i’m thinking about keeping the depth each node and updating it along with the positioning on insert/update/delete
Some questions about “nested tree”:
or not… no questions
fatpelt, is that necessary??
it doesn’t seem so
How do I find the type of a column? as in ‘tell me the type of column x from table y’
i am getting depth on my get query, but not touching it on insert/update/delete
the l/r stuff should give you enough info to delete properly
show create table table_name; is one way
http://www.slexy.org/paste/4124 i can’t get the indexes right
funny fatpelt ..
(it isn’t an issue with the deletion, but the depth calculation)
hottest pastebin
Total over all groups? Three ways. 1) WITH ROLLUP … 2) Join with a derived table to provide the total. 3) Wrap that in a SELCET COUNT(vers) FROM (…) v1;
