MySQL will timeout on connection handles
Hi all, I'd like to clarify on this odd issue. It seems to me that MySQL will automatically drop database handles, assumably if the handle has not been used for some time. Is this true? If so, how can I disable timeout? This is an unexpected behavior, because I have tried MSSQL and Postgre, and they don't exhibit this behavior. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb table locked on delete
Why table locked on delete?
---TRANSACTION 0 476648265, ACTIVE 530 sec, process no 13965, OS thread id
3152999360 updating or deleting, thread declared inside InnoDB 293
mysql tables in use 1,* locked 1*
11090 lock struct(s), heap size 634176, undo log entries 930711
MySQL thread id 16831, query id 11504020 localhost 127.0.0.1 root updating
delete from t_outgoing_sms_3t where month(out_sms_time)=1
mysql insert into t_outgoing_sms_3t (out_sms_dest) values ('1');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
TRANSACTIONS
Trx id counter 0 476676328
Purge done for trx's n:o 0 476646787 undo n:o 0 607975
History list length 7751
Total number of lock structs in row lock hash table 11089
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 476675339, not started, process no 13965, OS thread id
3092185536
MySQL thread id 17088, query id 11546298 10.1.30.70 webreport
---TRANSACTION 0 476675015, not started, process no 13965, OS thread id
3152409920
MySQL thread id 17087, query id 11545451 10.1.30.70 webreport
---TRANSACTION 0 476674717, not started, process no 13965, OS thread id
3160996544
MySQL thread id 17086, query id 11544918 10.1.30.70 webreport
---TRANSACTION 0 476673989, not started, process no 13965, OS thread id
3154309952
MySQL thread id 17085, query id 11543085 10.1.30.70 root
---TRANSACTION 0 476673192, not started, process no 13965, OS thread id
3012753088
MySQL thread id 13733, query id 11548354 10.1.20.10 root
---TRANSACTION 0 476673831, not started, process no 13965, OS thread id
3133078080
MySQL thread id 17083, query id 11542807 10.1.30.70 root
---TRANSACTION 0 476673945, not started, process no 13965, OS thread id
3089042752
MySQL thread id 17082, query id 11542976 10.1.30.70 root
---TRANSACTION 0 476673971, not started, process no 13965, OS thread id
3136485184
MySQL thread id 17081, query id 11548513 10.1.30.70 root
SHOW INNODB STATUS
---TRANSACTION 0 476673972, not started, process no 13965, OS thread id
3140678208
MySQL thread id 17080, query id 11543043 10.1.30.70 root
---TRANSACTION 0 476673981, not started, process no 13965, OS thread id
3031627200
MySQL thread id 17079, query id 11543057 10.1.30.70 root
---TRANSACTION 0 476672586, not started, process no 13965, OS thread id
3057712064
MySQL thread id 16348, query id 11548353 10.1.20.10 root
---TRANSACTION 0 476674015, not started, process no 13965, OS thread id
3146971840
MySQL thread id 17078, query id 11543259 10.1.30.70 root
---TRANSACTION 0 476675361, not started, process no 13965, OS thread id
3099786432
MySQL thread id 17073, query id 11546342 10.1.30.70 webreport
---TRANSACTION 0 476653631, not started, process no 13965, OS thread id
3020028480
MySQL thread id 17070, query id 11516390 10.1.30.70 webreport
---TRANSACTION 0 476673992, not started, process no 13965, OS thread id
3061118272
MySQL thread id 17068, query id 11543095 10.1.30.70 webreport
---TRANSACTION 0 476652727, not started, process no 13965, OS thread id
3061622976
MySQL thread id 17067, query id 11514351 10.1.30.70 webreport
---TRANSACTION 0 476652317, not started, process no 13965, OS thread id
3007901760
MySQL thread id 17066, query id 11513390 10.1.30.70 webreport
---TRANSACTION 0 476671899, not started, process no 13965, OS thread id
3130388032
MySQL thread id 17064, query id 11538944 10.1.30.70 webreport
---TRANSACTION 0 476674311, not started, process no 13965, OS thread id
3004560192
MySQL thread id 17063, query id 11543856 10.1.30.70 webreport
---TRANSACTION 0 476675025, not started, process no 13965, OS thread id
3034377920
MySQL thread id 17061, query id 11545472 10.1.30.70 root
---TRANSACTION 0 476674006, not started, process no 13965, OS thread id
3145268288
MySQL thread id 17060, query id 11543220 10.1.30.70 root
---TRANSACTION 0 476672791, not started, process no 13965, OS thread id
3144875328
MySQL thread id 17058, query id 11540631 10.1.30.70 root
---TRANSACTION 0 476673967, not started, process no 13965, OS thread id
3091792576
MySQL thread id 17057, query id 11543035 10.1.30.70 root
---TRANSACTION 0 476673955, not started, process no 13965, OS thread id
3086768320
MySQL thread id 17056, query id 11543015 10.1.30.70 root
---TRANSACTION 0 476674014, not started, process no 13965, OS thread id
3121804736
MySQL thread id 17055, query id 11543248 10.1.30.70 root
---TRANSACTION 0 476674009, not started, process no 13965, OS thread id
3060135872
MySQL thread id 17054, query id 11543241 10.1.30.70 root
---TRANSACTION 0 476673982, not started, process no 13965, OS thread id
3091989056
MySQL thread id 17053, query id 11543061 10.1.30.70 root
---TRANSACTION 0 476673912, not started, process no 13965, OS thread id
3088842176
MySQL thread id 17052, query id 11542915 10.1.30.70 root
---TRANSACTION 0 476645131, not started, process no 13965, OS thread id
3088449344
MySQL thread id 15781, query id 11548352 10.1.20.10 root
---TRANSACTION 0 476673975, not started, process
Re: Failed to upgrade from 4.1.16 to 5.0.18
Hi Dave i did so, but probably in the wrong moment. i got Access denied when i run the script. you mean that this should have been the very first step after the upgrade. suomi Logan, David (SST - Adelaide) wrote: Hi, Did you run the bin/mysql_fix_privilege_tables script? This is documented in the upgrade procedures on the website under the installation chapter. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: suomi [mailto:[EMAIL PROTECTED] Sent: Thursday, 9 February 2006 6:04 PM To: mysql@lists.mysql.com Subject: Failed to upgrade from 4.1.16 to 5.0.18 Hi listers 1. i did the upgrad as i usually did so far: /etc/init.d/mysql stop rpm -U MySQL-server-standard-5.0.18-0.rhel3.i386.rpm rpm -U MySQL-client-standard-5.0.18-0.rhel3.i386.rpm rpm -U MySQL-shared-standard-5.0.18-0.rhel3.i386.rpm from then on: no chance to enter the database via whatever interface: always Access denied for user on host ... 2. i checked to see, whether the mysql userid had sufficient access privileges to all database files: all access was ok. i also checked the error log myhost.mydomain.tld.err: no hint why access was denied. 3. i then went back to 4.1.16, entered the database (no problem) and for the root user created the long password and intentionally did a flush privileges. 4. then i re-did the above (under 1) upgrade steps again to the same event: Access denied. 5. i then took the procedure described in http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html while in the procedure, when i wanted to enter the flush privileges, again access denied. 6. i checked to see, whether there is some upgrade procedure to follow, where privilege tables are upgraded (as from 3 to 4), and i found none. i read the README even more carefully: nothing found. 7. back to 4.1.16 which essential step did i omitt? suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Failed to upgrade from 4.1.16 to 5.0.18
Hi suomi, From my understanding (and I have just performed it myself about 2 hours ago on one of my servers), I stopped the server, took a backup of the data directories, built the new server and put it in place (same as your rpm step), started the server, ran the fix script as /usr/local/mysql/bin/mysql_fix_privilege_tables --basedir=/usr/local/mysql --user=root --password=xx --verbose It ran ok. I stopped and started the server and away it went with no problems. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: suomi [mailto:[EMAIL PROTECTED] Sent: Thursday, 9 February 2006 6:47 PM To: Logan, David (SST - Adelaide) Cc: mysql@lists.mysql.com Subject: Re: Failed to upgrade from 4.1.16 to 5.0.18 Hi Dave i did so, but probably in the wrong moment. i got Access denied when i run the script. you mean that this should have been the very first step after the upgrade. suomi Logan, David (SST - Adelaide) wrote: Hi, Did you run the bin/mysql_fix_privilege_tables script? This is documented in the upgrade procedures on the website under the installation chapter. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: suomi [mailto:[EMAIL PROTECTED] Sent: Thursday, 9 February 2006 6:04 PM To: mysql@lists.mysql.com Subject: Failed to upgrade from 4.1.16 to 5.0.18 Hi listers 1. i did the upgrad as i usually did so far: /etc/init.d/mysql stop rpm -U MySQL-server-standard-5.0.18-0.rhel3.i386.rpm rpm -U MySQL-client-standard-5.0.18-0.rhel3.i386.rpm rpm -U MySQL-shared-standard-5.0.18-0.rhel3.i386.rpm from then on: no chance to enter the database via whatever interface: always Access denied for user on host ... 2. i checked to see, whether the mysql userid had sufficient access privileges to all database files: all access was ok. i also checked the error log myhost.mydomain.tld.err: no hint why access was denied. 3. i then went back to 4.1.16, entered the database (no problem) and for the root user created the long password and intentionally did a flush privileges. 4. then i re-did the above (under 1) upgrade steps again to the same event: Access denied. 5. i then took the procedure described in http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html while in the procedure, when i wanted to enter the flush privileges, again access denied. 6. i checked to see, whether there is some upgrade procedure to follow, where privilege tables are upgraded (as from 3 to 4), and i found none. i read the README even more carefully: nothing found. 7. back to 4.1.16 which essential step did i omitt? suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
which constraint fails
Hi All. How can I get to know which constraint fails when Cannot delete or update a parent row: a foreign key constraint fails error occurs? Thanks in advance prp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Failed to upgrade from 4.1.16 to 5.0.18
Hi Dave tha' sounds great. i 'll try to do the same immediately. thanks for your help. suomi Logan, David (SST - Adelaide) wrote: Hi suomi, From my understanding (and I have just performed it myself about 2 hours ago on one of my servers), I stopped the server, took a backup of the data directories, built the new server and put it in place (same as your rpm step), started the server, ran the fix script as /usr/local/mysql/bin/mysql_fix_privilege_tables --basedir=/usr/local/mysql --user=root --password=xx --verbose It ran ok. I stopped and started the server and away it went with no problems. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: suomi [mailto:[EMAIL PROTECTED] Sent: Thursday, 9 February 2006 6:47 PM To: Logan, David (SST - Adelaide) Cc: mysql@lists.mysql.com Subject: Re: Failed to upgrade from 4.1.16 to 5.0.18 Hi Dave i did so, but probably in the wrong moment. i got Access denied when i run the script. you mean that this should have been the very first step after the upgrade. suomi Logan, David (SST - Adelaide) wrote: Hi, Did you run the bin/mysql_fix_privilege_tables script? This is documented in the upgrade procedures on the website under the installation chapter. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: suomi [mailto:[EMAIL PROTECTED] Sent: Thursday, 9 February 2006 6:04 PM To: mysql@lists.mysql.com Subject: Failed to upgrade from 4.1.16 to 5.0.18 Hi listers 1. i did the upgrad as i usually did so far: /etc/init.d/mysql stop rpm -U MySQL-server-standard-5.0.18-0.rhel3.i386.rpm rpm -U MySQL-client-standard-5.0.18-0.rhel3.i386.rpm rpm -U MySQL-shared-standard-5.0.18-0.rhel3.i386.rpm from then on: no chance to enter the database via whatever interface: always Access denied for user on host ... 2. i checked to see, whether the mysql userid had sufficient access privileges to all database files: all access was ok. i also checked the error log myhost.mydomain.tld.err: no hint why access was denied. 3. i then went back to 4.1.16, entered the database (no problem) and for the root user created the long password and intentionally did a flush privileges. 4. then i re-did the above (under 1) upgrade steps again to the same event: Access denied. 5. i then took the procedure described in http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html while in the procedure, when i wanted to enter the flush privileges, again access denied. 6. i checked to see, whether there is some upgrade procedure to follow, where privilege tables are upgraded (as from 3 to 4), and i found none. i read the README even more carefully: nothing found. 7. back to 4.1.16 which essential step did i omitt? suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unix_timestamp + milliseconds support?
An efficient way to store time is as UNIX_TIMESTAMP (4 bytes) instead of using DATETIME data type (8 bytes). We were using this technique to save the time in our database. Now we need to support millisecond resolution as we need to store data comming 8 times per second, that is, every 125 milliseconds. Have you already face this problem and come to a solution you are happy with? Thank you for taking the time to read this email for your support. José Antonio
Re: Failed to upgrade from 4.1.16 to 5.0.18
Hi again, it just does not work. follows the entire dialog: [EMAIL PROTECTED] /home/myuser/software rpm -U --nodeps MySQL-server-standard-5.0.18-0.rhel3.i386.rpm Giving mysqld a couple of seconds to exit nicely PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h myhost.mydom.com password 'new-password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should run the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! Please report any problems with the /usr/bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com Starting MySQL[ OK ] [EMAIL PROTECTED] /home/myuser/software netstat -ln |grep 3306 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN [EMAIL PROTECTED] /home/myuser/software rpm -U --nodeps MySQL-client-standard-5.0.18-0.rhel3.i386.rpm [EMAIL PROTECTED] /home/myuser/software rpm -U --nodeps MySQL-shared-standard-5.0.18-0.rhel3.i386.rpm /sbin/ldconfig: File /usr/lib/libnal.so.1.0.1.#prelink#.WkxbRw is empty, not checked. /sbin/ldconfig: File /usr/lib/libnal.so.1.0.1.#prelink#.WkxbRw is empty, not checked. [EMAIL PROTECTED] /home/myuser/software rpm -U --nodeps MySQL-devel-standard-5.0.18-0.rhel3.i386.rpm [EMAIL PROTECTED] /home/myuser/software which mysql_fix_privilege_tables /usr/bin/mysql_fix_privilege_tables [EMAIL PROTECTED] /home/myuser/software mysql_fix_privilege_tables --user root --password=pp --verbose This script updates all the mysql privilege tables to be usable by MySQL 4.0 and above. This is needed if you want to use the new GRANT functions, CREATE AGGREGATE FUNCTION, stored procedures, or more secure passwords in 4.1 You can safely ignore all 'Duplicate column' and 'Unknown column' errors because these just mean that your tables are already up to date. This script is safe to run even if your tables are already up to date! ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) Got a failure from command: cat /usr/share/mysql/mysql_fix_privilege_tables.sql | /usr/bin/mysql --no-defaults --force --user=root --host=localhost --password=pp --database=mysql Please check the above output and try again. If you get an 'Access denied' error, you should run this script again and give the MySQL root user password as an argument with the --password= option [EMAIL PROTECTED] /home/myuser/software suomi Logan, David (SST - Adelaide) wrote: Hi suomi, From my understanding (and I have just performed it myself about 2 hours ago on one of my servers), I stopped the server, took a backup of the data directories, built the new server and put it in place (same as your rpm step), started the server, ran the fix script as /usr/local/mysql/bin/mysql_fix_privilege_tables --basedir=/usr/local/mysql --user=root --password=xx --verbose It ran ok. I stopped and started the server and away it went with no problems. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: suomi [mailto:[EMAIL PROTECTED] Sent: Thursday, 9 February 2006 6:47 PM To: Logan, David (SST - Adelaide) Cc: mysql@lists.mysql.com Subject: Re: Failed to upgrade from 4.1.16 to 5.0.18 Hi Dave i did so, but probably in the wrong moment. i got Access denied when i run the script. you mean that this should have been the very first step after the upgrade. suomi Logan, David (SST - Adelaide) wrote: Hi, Did you run the bin/mysql_fix_privilege_tables script? This is documented in the upgrade procedures on the website under the installation chapter. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street,
RE: Failed to upgrade from 4.1.16 to 5.0.18
Have you tried it without a password? eg. just --user root Are you able to connect with the mysql client? Sorry, I don't know a lot about rpm and how it works as I work on Solaris and use the .tar.gz versions. Hopefully somebody with familiarity with rpm can help out here? I'd be interested to know what is happening. I have noticed the message about setting root password, maybe it thinks this is a new install? Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: suomi [mailto:[EMAIL PROTECTED] Sent: Thursday, 9 February 2006 10:16 PM To: Logan, David (SST - Adelaide) Cc: mysql@lists.mysql.com Subject: Re: Failed to upgrade from 4.1.16 to 5.0.18 Hi again, it just does not work. follows the entire dialog: [EMAIL PROTECTED] /home/myuser/software rpm -U --nodeps MySQL-server-standard-5.0.18-0.rhel3.i386.rpm Giving mysqld a couple of seconds to exit nicely PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h myhost.mydom.com password 'new-password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should run the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! Please report any problems with the /usr/bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com Starting MySQL[ OK ] [EMAIL PROTECTED] /home/myuser/software netstat -ln |grep 3306 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN [EMAIL PROTECTED] /home/myuser/software rpm -U --nodeps MySQL-client-standard-5.0.18-0.rhel3.i386.rpm [EMAIL PROTECTED] /home/myuser/software rpm -U --nodeps MySQL-shared-standard-5.0.18-0.rhel3.i386.rpm /sbin/ldconfig: File /usr/lib/libnal.so.1.0.1.#prelink#.WkxbRw is empty, not checked. /sbin/ldconfig: File /usr/lib/libnal.so.1.0.1.#prelink#.WkxbRw is empty, not checked. [EMAIL PROTECTED] /home/myuser/software rpm -U --nodeps MySQL-devel-standard-5.0.18-0.rhel3.i386.rpm [EMAIL PROTECTED] /home/myuser/software which mysql_fix_privilege_tables /usr/bin/mysql_fix_privilege_tables [EMAIL PROTECTED] /home/myuser/software mysql_fix_privilege_tables --user root --password=pp --verbose This script updates all the mysql privilege tables to be usable by MySQL 4.0 and above. This is needed if you want to use the new GRANT functions, CREATE AGGREGATE FUNCTION, stored procedures, or more secure passwords in 4.1 You can safely ignore all 'Duplicate column' and 'Unknown column' errors because these just mean that your tables are already up to date. This script is safe to run even if your tables are already up to date! ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) Got a failure from command: cat /usr/share/mysql/mysql_fix_privilege_tables.sql | /usr/bin/mysql --no-defaults --force --user=root --host=localhost --password=pp --database=mysql Please check the above output and try again. If you get an 'Access denied' error, you should run this script again and give the MySQL root user password as an argument with the --password= option [EMAIL PROTECTED] /home/myuser/software suomi Logan, David (SST - Adelaide) wrote: Hi suomi, From my understanding (and I have just performed it myself about 2 hours ago on one of my servers), I stopped the server, took a backup of the data directories, built the new server and put it in place (same as your rpm step), started the server, ran the fix script as /usr/local/mysql/bin/mysql_fix_privilege_tables --basedir=/usr/local/mysql --user=root --password=xx --verbose It ran ok. I stopped and started the server and away it went with no problems. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/
Passing db names to a stored proc in MySQL 5
Hello List I have tried dynamically assigning a database name to a stored proc via its parameter list: CREATE STORED PROCEDURE testStoredProc (IN test_db_name CHAR) BEGIN SELECT * FROM test_db_name.test_table; END; MySQL does not resolve test_db_name to the value passed in the parameter, and the query fails because there is no database with that name. Is there any way around this? Regards Rory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Column in field list is ambiguous
Thanks Michael, That was the solution. This was the first time I've seen that
error and now it makes plenty of sense.
Thanks,
Grant
Michael Stassen [EMAIL PROTECTED] wrote: Grant Giddens wrote:
Hi,
I'm using mysql 4.1.14 and and getting this error:
Column 'asin' in field list is ambiguous
This is the query that is giving me the error:
SELECT pn_pricecompare_searchdata.prod_id,
MATCH (asin,upc,...) AGAINST ('warcraft' IN BOOLEAN MODE) AS score
FROM pn_pricecompare_searchdata ps
LEFT JOIN pn_pricecompare_product pp
ON (pp.prod_id = ps.prod_id)
WHERE MATCH (asin,upc,...) AGAINST ('warcraft' IN BOOLEAN MODE)
ORDER BY score DESC LIMIT 0,10
The query works fine if I take out the LEFT JOIN
SELECT pn_pricecompare_searchdata.prod_id,
MATCH (asin,upc,...) AGAINST ('warcraft' IN BOOLEAN MODE) AS score
FROM pn_pricecompare_searchdata ps
LEFT JOIN pn_pricecompare_product pp
ON (pp.prod_id = ps.prod_id)
WHERE MATCH (asin,upc,...) AGAINST ('warcraft' IN BOOLEAN MODE)
ORDER BY score DESC LIMIT 0,10
The searchdata table is MyISAM with fulltext enabled on the fields. The
product table is an INNODB table.
I searched all over and can't find a solution. I did find one possible
bug report that might be related: http://bugs.mysql.com/bug.php?id=15607
Is there any workaround I can use?
Any suggestions would be greatly appreciated.
Thanks,
Grant
Is there a column named asin in the pn_pricecompare_product table? If so, try
qualifying asin with the correct table name in your query (use the alias).
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
-
Relax. Yahoo! Mail virus scanning helps detect nasty viruses!
Re: unix_timestamp + milliseconds support?
2006/2/9, Jose Antonio [EMAIL PROTECTED]: An efficient way to store time is as UNIX_TIMESTAMP (4 bytes) instead of using DATETIME data type (8 bytes). We were using this technique to save the time in our database. Now we need to support millisecond resolution as we need to store data comming 8 times per second, that is, every 125 milliseconds. Have you already face this problem and come to a solution you are happy with? a smallint column should be acceptable for storing millisecond (that's what we use), but depending on the way you need to manipulate those millisecond, a tinyint could be acceptable as well (you only store millisecond with a precision of 1/8 second). -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Failed to upgrade from 4.1.16 to 5.0.18
Hi Dave i tried a new proceeding, which seems to work: 1. before upgrading i did: mysqldump -A --all -uroot -p mysql-backup-before-upgrade-to-5.sql 2. i moved the present database away. 3. i did the upgrade as described many messages before. 4. i created a new database. 5. [EMAIL PROTECTED] ~ mysql -u root mysql-backup-before-upgrade-to-5.sql [EMAIL PROTECTED] ~ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 5.0.18-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. localhost.(none)quit [EMAIL PROTECTED] ~ mysql_fix_privilege_tables --user root --verbose This script updates all the mysql privilege tables to be usable by MySQL 4.0 and above. This is needed if you want to use the new GRANT functions, CREATE AGGREGATE FUNCTION, stored procedures, or more secure passwords in 4.1 You can safely ignore all 'Duplicate column' and 'Unknown column' errors because these just mean that your tables are already up to date. This script is safe to run even if your tables are already up to date! ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv' ERROR 1060 (42S21) at line 28: Duplicate column name 'Grant_priv' ERROR 1060 (42S21) at line 29: Duplicate column name 'Grant_priv' ERROR 1060 (42S21) at line 30: Duplicate column name 'Grant_priv' ERROR 1060 (42S21) at line 41: Duplicate column name 'ssl_type' ERROR 1060 (42S21) at line 70: Duplicate column name 'Routine_type' ERROR 1054 (42S22) at line 94: Unknown column 'Type' in 'columns_priv' ERROR 1060 (42S21) at line 100: Duplicate column name 'type' ERROR 1060 (42S21) at line 110: Duplicate column name 'Show_db_priv' ERROR 1060 (42S21) at line 127: Duplicate column name 'max_questions' ERROR 1060 (42S21) at line 137: Duplicate column name 'Create_tmp_table_priv' ERROR 1060 (42S21) at line 140: Duplicate column name 'Create_tmp_table_priv' ERROR 1061 (42000) at line 145: Duplicate key name 'Grantor' ERROR 1054 (42S22) at line 247: Unknown column 'Create_view_priv' in 'where clause' ERROR 1054 (42S22) at line 277: Unknown column 'Create_routine_priv' in 'where clause' ERROR 1054 (42S22) at line 313: Unknown column 'Create_user_priv' in 'where clause' done [EMAIL PROTECTED] ~ 6. [EMAIL PROTECTED] ~ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 to server version: 5.0.18-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. localhost.(none) flush privileges; Query OK, 0 rows affected (0.01 sec) localhost.(none) quit Bye 7. [EMAIL PROTECTED] ~ mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [EMAIL PROTECTED] ~ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 to server version: 5.0.18-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. localhost.(none) 8. this seems to work now, is far from being elegant and straight-forward, but works. i would like to have a more straight-forward method. suomi Logan, David (SST - Adelaide) wrote: Have you tried it without a password? eg. just --user root Are you able to connect with the mysql client? Sorry, I don't know a lot about rpm and how it works as I work on Solaris and use the .tar.gz versions. Hopefully somebody with familiarity with rpm can help out here? I'd be interested to know what is happening. I have noticed the message about setting root password, maybe it thinks this is a new install? Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: suomi [mailto:[EMAIL PROTECTED] Sent: Thursday, 9 February 2006 10:16 PM To: Logan, David (SST - Adelaide) Cc: mysql@lists.mysql.com Subject: Re: Failed to upgrade from 4.1.16 to 5.0.18 Hi again, it just does not work. follows the entire dialog: [EMAIL PROTECTED] /home/myuser/software rpm -U --nodeps MySQL-server-standard-5.0.18-0.rhel3.i386.rpm Giving mysqld a couple of seconds to exit nicely PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h myhost.mydom.com password 'new-password' See
Re: Connection problem after long idle periods
David Logan wrote: Michael Lai wrote: I am currently using MySQL 5.0.15 with mysql-connector-java-3.1.11. I can access the database from my JSPs with no problem except for one small issue. After a long delay (usually overnight), when someone first tries to access the database, I would get the following error: Communications link failure due to underlying exception: ** BEGIN NESTED EXCEPTION ** java.net.SocketException MESSAGE: Broken pipe STACKTRACE: java.net.SocketException: Broken pipe at java.net.SocketOutputStream.socketWrite0(Native Method) at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92) at java.net.SocketOutputStream.write(SocketOutputStream.java:136) at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65) at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2690) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2619) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1552) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666) at com.mysql.jdbc.Connection.execSQL(Connection.java:2978) at com.mysql.jdbc.Connection.execSQL(Connection.java:2902) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:933) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1027) at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92) at org.apache.jsp.processLogin_jsp._jspService(org.apache.jsp.processLogin_jsp:81) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97) at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322) at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291) at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241) at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744) at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527) at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684) at java.lang.Thread.run(Thread.java:595) ** END NESTED EXCEPTION ** Last packet sent to the server was 0 ms ago. But afterwards, on the second and subsequent attempts, the database runs fine. It is only the first try after a long idle period that I get this error message. I am not sure how to fix this. I am guessing that the database connection is down after a long idle period and have to recreate a connection first after the first try. Hi Michael, Try looking at http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html more specifically the connect-timeout variable. You should use SHOW VARIABLES LIKE %connect%; and check the value. It maybe this and you can stretch it out further if you need to. Regards I tried lengthening the connection timeout but it doesn't seem to be working. Here is my ROOT.xml configuration: Context path= docBase=/home/tomcat/applications/ debug=0 reloadable=true Resource name=jdbc/ type=javax.sql.DataSource auth=Container driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost/hostname?autoReconnect=true username= password= maxActive=8 maxIdle=-1 maxWait=1 / /Context Is there any way to keep a connection alive indefinitely? I thought the autoReconnect would do the trick. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Passing db names to a stored proc in MySQL 5
I have tried dynamically assigning a database name to a stored proc via its parameter list: CREATE STORED PROCEDURE testStoredProc (IN test_db_name CHAR) BEGIN SELECT * FROM test_db_name.test_table; END; MySQL does not resolve test_db_name to the value passed in the parameter, and the query fails because there is no database with that name. Is there any way around this? No. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: which constraint fails
Przemysław Klein [EMAIL PROTECTED] wrote on 02/09/2006 04:24:14 AM: Hi All. How can I get to know which constraint fails when Cannot delete or update a parent row: a foreign key constraint fails error occurs? Thanks in advance prp If you issue a SHOW InnoDB STATUS command, you will get the details you are looking for. It's near the beginning of the status dump. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Passing db names to a stored proc in MySQL 5
Rory McKinley [EMAIL PROTECTED] wrote on 02/09/2006 07:37:17 AM: Hello List I have tried dynamically assigning a database name to a stored proc via its parameter list: CREATE STORED PROCEDURE testStoredProc (IN test_db_name CHAR) BEGIN SELECT * FROM test_db_name.test_table; END; MySQL does not resolve test_db_name to the value passed in the parameter, and the query fails because there is no database with that name. Is there any way around this? Regards Rory Use a prepared statement. Build your SQL statement as a string, prepare it, and execute it. It's all right there in the manual. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Passing db names to a stored proc in MySQL 5
I have tried dynamically assigning a database name to a stored proc via its parameter list: CREATE STORED PROCEDURE testStoredProc (IN test_db_name CHAR) BEGIN SELECT * FROM test_db_name.test_table; END; MySQL does not resolve test_db_name to the value passed in the parameter, and the query fails because there is no database with that name. Is there any way around this? Use a prepared statement. Build your SQL statement as a string, prepare it, and execute it. It's all right there in the manual. Indeed, I stand corrected. This page: http://dev.mysql.com/doc/refman/5.0/en/news-5-0-13.html says that this is now possible in Stored Procedures :-) Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table comments truncated at 80 characters
Since upgrading from 4.x to 5.x we've found table comments to be truncated at 80 characters. Is this a configurable option somewhere and I just can't find it in the manual? Thanks, Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query problem
You originally mention your UNION doesn't work but you did not specify the query. This is a simple or query, or union. You can do either: select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where (qty=1 and Sizes_idsizes=2) or (qty=1 and Sizes_idsizes=4); or select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where qty=1 and Sizes_idsizes=2 UNION select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where qty=1 and Sizes_idsizes=4; Please let me know if it's more complex than that, but when I ran it on my test data I got +-+---+--+ | CaseType_idCaseType | Sizes_idsizes | qty | +-+---+--+ | 60 | 2 |1 | | 60 | 4 |1 | | 61 | 2 |1 | | 61 | 4 |1 | +-+---+--+ for both results. -Sheeri On 2/8/06, Conor McTernan [EMAIL PROTECTED] wrote: Sheeri, The table I'm searching on has a composite primary key since it's mapping an N:M relationship between Cases and Sizes. Here's the create statement for the table I'm searching on: DROP TABLE IF EXISTS `CaseType_has_Sizes`; CREATE TABLE `CaseType_has_Sizes` ( `CaseType_idCaseType` int(10) unsigned NOT NULL, `Sizes_idsizes` int(10) unsigned NOT NULL, `qty` int(10) unsigned default NULL, PRIMARY KEY (`CaseType_idCaseType`,`Sizes_idsizes`), KEY `CaseType_has_sizes_FKIndex1` (`CaseType_idCaseType`), KEY `CaseType_has_sizes_FKIndex2` (`Sizes_idsizes`), CONSTRAINT `CaseType_has_Sizes_ibfk_1` FOREIGN KEY (`CaseType_idCaseType`) REFERENCES `CaseType` (`idCaseType`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `CaseType_has_Sizes_ibfk_2` FOREIGN KEY (`Sizes_idsizes`) REFERENCES `Sizes` (`idsizes`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Here's the Cases table: DROP TABLE IF EXISTS `CaseType`; CREATE TABLE `CaseType` ( `idCaseType` int(10) unsigned NOT NULL auto_increment, `caseName` char(32) default NULL, PRIMARY KEY (`idCaseType`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; and here's the Sizes case: DROP TABLE IF EXISTS `Sizes`; CREATE TABLE `Sizes` ( `idsizes` int(10) unsigned NOT NULL auto_increment, `size` char(4) default NULL, `jpSize` char(4) default NULL, PRIMARY KEY (`idsizes`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; On 2/9/06, sheeri kritzer [EMAIL PROTECTED] wrote: Hi Conor, The table you showed us has 2 primary keys, which is not possible. Can you do a SHOW CREATE TABLE on *each* table? -Sheeri On 2/8/06, Conor McTernan [EMAIL PROTECTED] wrote: Hello, I'm having a hell of a time figuring this query out, maybe someone can point me in the right direction. I have a table which lists the case configurations for cases of items. Each case will have a different combination of sizes e.g. Case 002 has 2 size 5, 2 size 7 and 2 size 8 items. What I'm trying to do is, given a group of sizes and quantities how can I find the corresponding ID number for a case. Here's the description of the table. +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | CaseType_idCaseType | int(10) unsigned | NO | PRI | | | | Sizes_idsizes | int(10) unsigned | NO | PRI | | | | qty | int(10) unsigned | YES | | NULL| | +-+--+--+-+-+---+ Here's what I see if I do a select on a CaseType id. select * from CaseType_has_Sizes where CaseType_idCaseType = 61; +-+---+--+ | CaseType_idCaseType | Sizes_idsizes | qty | +-+---+--+ | 61 | 2 |1 | | 61 | 4 |1 | | 61 | 6 |1 | | 61 | 8 |1 | | 61 |24 |1 | | 61 |26 |1 | +-+---+--+ I'm essentially trying to get the same resuts as this select, but in reverse I've tried using UNION but it doesnt seem to get what I'm trying for. Any help appreciated. Conor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL C API: binding parameters to prepared statements
Hi, I am trying to convert a Oracle 7 interface (from our Constraint Logic Programming Platform ECLiPSe) to MySQL. [I guess I should also say that I don't really have any experience with programming any database API previously, so please excuse me if this seems to be a basic question] The interface includes the feature of making multiple executions of prepared statements, binding the parameters to different values before each execution. In the Oracle code, obndra() was used to bind the parameters, and I have replaced this with mysql_stmt_bind_param(). However, I noticed that there might be an important difference -- obndra() binds the *address* of data buffers for the parameters, i.e. after the call, the different paramter values for each execution are loaded into these data buffers, and then executed, without calling obndra() again. From reading the MySQL 5.0 manual, it seems that mysql_stmt_bind_param() is used to bind the *values* of parameter data for a prepared statements, i.e. I need to call mysql_stmt_bind_param() again before executing the statement again, rather than just loading different values into the buffers (i.e. buffer in MYSQL_BIND structure). Am I reading the documentation correctly, i.e. I can't simply replace obndra() with mysql_stmt_bind_param()? Thanks in advance for any help and information! Cheers, Kish Shen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql -u [EMAIL PROTECTED]
Firstly, the correct syntax for the mysql client is: mysql -u root -h hostname_or_ip_address -p and then enter the password you are prompted. You need to make sure you can connect FIRST, and THEN try replication. Running replication as root is a bad idea, by the way. Better to set up a replication user with minimal grants, and use that user. http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html for details. If this doesn't help, try copying and pasting what you type and what's returned. It didn't work and I got an error are not sufficient. -Sheeri On 2/9/06, Chen Abella [EMAIL PROTECTED] wrote: hi. i have trouble with reoplication. i cannot connect to the other server using: mysql -u [EMAIL PROTECTED] and i get an error msg in the log: [ERROR] Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3306': Error: 'Can't connect to MySQL server on 'server1' (13)' how do i fix this? anyone who can help... ? thankyou... chEn Send instant messages to your online friends http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Passing db names to a stored proc in MySQL 5
[EMAIL PROTECTED] wrote: Rory McKinley [EMAIL PROTECTED] wrote on 02/09/2006 07:37:17 AM: Hello List I have tried dynamically assigning a database name to a stored proc via its parameter list: snip snip Use a prepared statement. Build your SQL statement as a string, prepare it, and execute it. It's all right there in the manual. snip I am afraid that it doesn't work - unless I am being thick (yes, I know, not like it hasn't happened before). Used a test statement such as this: PREPARE query_statement FROM SELECT * FROM ?.sites Which causes a syntax error right at the ? Whereas the following statement works fine, but doesn't help me : SELECT * FROM sites WHERE site_name = ? While RTFM, I came across the following: Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth. Which, if combined with my test results leaves me with Martijn's original answer of it not being possible after all :(. Rory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Passing db names to a stored proc in MySQL 5
Rory McKinley [EMAIL PROTECTED] wrote on 02/09/2006 03:06:41 PM:
[EMAIL PROTECTED] wrote:
Rory McKinley [EMAIL PROTECTED] wrote on 02/09/2006 07:37:17 AM:
Hello List
I have tried dynamically assigning a database name to a stored proc
via
its parameter list:
snip
snip
Use a prepared statement. Build your SQL statement as a string,
prepare
it, and execute it. It's all right there in the manual.
snip
I am afraid that it doesn't work - unless I am being thick (yes, I know,
not like it hasn't happened before).
Used a test statement such as this:
PREPARE query_statement FROM SELECT * FROM ?.sites
Which causes a syntax error right at the ?
Whereas the following statement works fine, but doesn't help me :
SELECT * FROM sites WHERE site_name = ?
While RTFM, I came across the following:
Parameter markers can be used only where data values should appear, not
for SQL keywords, identifiers, and so forth.
Which, if combined with my test results leaves me with Martijn's
original answer of it not being possible after all :(.
Rory
You were so close! Try it more like this:
SET sSQL=CONCAT('SELECT * FROM ', passed_in_db_name, '.sites WHERE
site_name=?');
PREPARE query_statement from sSQL
Actually build the string to contain the database name and any other
parameter markers you may need. Then prepare THAT (the string you just
composed). Make better sense?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Multiple many-to-many SELECT
Hello, I apologize if this is a common question; I have been working with it and googling for days, and can't seem to find anyone who has been trying to do the same thing that I am. Maybe I'm using the wrong keywords. In any event, I am desperate for help. Here is my problem: I have a database with several tables related on a many-to-many basis. Here is a simplified description: TABLE: movies +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | id | int(11) | | PRI | 0 | | | catalog_description | text| YES | | NULL| | | title | text| YES | | NULL| | | website_url | text| YES | | NULL| | +-+-+--+-+-+---+ TABLE: director +-+-+--+-+- ++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+- ++ | id | int(11) | | UNI | NULL| auto_increment | | director_first_name | text| | PRI | || | director_last_name | text| | PRI | || +-+-+--+-+- ++ TABLE: producer +-+-+--+-+- ++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+- ++ | id | int(11) | | UNI | NULL| auto_increment | | producer_first_name | text| | PRI | || | producer_last_name | text| | PRI | || +-+-+--+-+- ++ TABLE: director_movies +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | movie_id| int(11) | | | 0 | | | director_id | int(11) | | | 0 | | +-+-+--+-+-+---+ TABLE: producer_movies +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | movie_id| int(11) | | | 0 | | | producer_id | int(11) | | | 0 | | +-+-+--+-+-+---+ There are actually several more related tables and fields, but I think this is enough to give an idea of what I have. What I am trying to do is SELECT all the information about a movie (all fields from the movies table, plus director_first_name, director_last_name, producer_first_name, producer_last_name) based on the id from the movies table. The only problem is that there may not be a producer and/or a director listed for a given movie, in that case, I need those columns to be absent from the results (ideally), or at least return as NULL. I was able to write a select statement that works exactly as I want it in the cases where there is both a director and a producer listed for the movie: SELECT * FROM movies INNER JOIN (director INNER JOIN director_movies ON director_movies.director_id = director.id AND director_movies.movie_id=movies.id) INNER JOIN (country INNER JOIN country_movies ON country_movies.country_id = country.id AND country_movies.movie_id=movies.id) INNER JOIN (producer INNER JOIN producer_movies ON producer_movies.producer_id = producer.id AND producer_movies.movie_id=movies.id) WHERE movies.id = 123 However, if there is no producer and/or director listed for the movie id given, then the query returns 0 rows. I thought I might need to change my JOINs to be LEFT JOINs insead of INNER, but when I change the statement so that it reads as follows: SELECT * FROM movies LEFT JOIN (director INNER JOIN director_movies ON director_movies.director_id = director.id AND director_movies.movie_id=movies.id) LEFT JOIN (country INNER JOIN country_movies ON country_movies.country_id = country.id AND country_movies.movie_id=movies.id) LEFT JOIN (producer INNER JOIN producer_movies ON producer_movies.producer_id = producer.id AND producer_movies.movie_id=movies.id) WHERE movies.id = 123 I get a syntax error #1064 near my WHERE clause, and I can't figure out what's causing it. I am new to understanding JOIN clause syntax, so please excuse me if it's obvious. I'm terribly sorry for such a long post. If anyone has any clue how I can accomplish what I need to do,
Re: Multiple many-to-many SELECT
Jessica, It think your first mistake is that you are trying to implement many-to-many relationships directly. Although it is theoretically possible to do so, I've never seen anyone do it myself. I believe the normal approach is to break each many-to-many relationship into two one-to-many relationships with an association table (sometimes called an intersection table) in the middle. Then, you join the tables together as needed, using inner, left or right joins as appropriate for your situation. I spent several hours detailing most of this in emails on this list several months back in response to a similar question and I _really_ don't want to go through all that typing again :-) Instead, I'm going to refer you to the MySQL archives. If you go to this page - http://lists.mysql.com/mysql/171636 - you will see the beginning of a thread where I was one of the participants. We were discussing many-to-many designs. You probably don't need to read the whole thread but certainly read the first few posts in the thread, especially http://lists.mysql.com/mysql/171645, which is where I first describe how to implement a many-to-many relationship between members (of a video club) and the titles of the movies they rent. That should get you started. You can read more in that thread to hear more of the pros and cons of the issue but you may find this thread wanders somewhat and is completely relevant to your concerns. Another thing you could try, to see other discussions of many-to-many implementations, is to go to http://lists.mysql.com/ and fill in the search box as follows: Search mailing lists for: intersection table Within: MySQL General Discussion Matching: all of the words since: the beginning You may find that other people explain the idea more clearly or convincingly than I do ;-) I have to dash but if you have followup questions, post them in the list and I, or someone else, will likely be able to answer. -- Rhino - Original Message - From: Jessica Yazbek [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, February 09, 2006 5:28 PM Subject: Multiple many-to-many SELECT Hello, I apologize if this is a common question; I have been working with it and googling for days, and can't seem to find anyone who has been trying to do the same thing that I am. Maybe I'm using the wrong keywords. In any event, I am desperate for help. Here is my problem: I have a database with several tables related on a many-to-many basis. Here is a simplified description: TABLE: movies +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | id | int(11) | | PRI | 0 | | | catalog_description | text| YES | | NULL| | | title | text| YES | | NULL| | | website_url | text| YES | | NULL| | +-+-+--+-+-+---+ TABLE: director +-+-+--+-+- ++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+- ++ | id | int(11) | | UNI | NULL| auto_increment | | director_first_name | text| | PRI | || | director_last_name | text| | PRI | || +-+-+--+-+- ++ TABLE: producer +-+-+--+-+- ++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+- ++ | id | int(11) | | UNI | NULL| auto_increment | | producer_first_name | text| | PRI | || | producer_last_name | text| | PRI | || +-+-+--+-+- ++ TABLE: director_movies +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | movie_id| int(11) | | | 0 | | | director_id | int(11) | | | 0 | | +-+-+--+-+-+---+ TABLE: producer_movies +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | movie_id| int(11) | | | 0 | | | producer_id | int(11) | | | 0 | | +-+-+--+-+-+---+ There are actually several more related tables and fields, but I think this is enough to give an idea of what I have. What I am trying
MySQL Connection Problems
Hello all,I'm running into a intermittent and very annoying problem with all of my servers. I'm currently running FreeBSD 4.11, exim-4.51-0, and mySQL 4.1.13. My exim config is setup to have exim do a mySQL lookup in order to determine whether a given user's e-mail is hosted by our e-mail servers.Periodically, exim throws the following error into its panic log:Feb 9 13:24:51 mx_server_1 exim[69953]: 2006-02-09 13:24:51 failed to expand "${lookup mysql {select distinct domain from email_table where domain='$domain'}{$value}}" while checking a list: lookup of "select distinct domain from email_table where domain='bob.com'" gave DEFER: MYSQL connection failed: Can't connect to MySQL server on '127.0.0.1' (61)(where bob.com is one of our customer's domains that we host.)This has been happening for months, and I'm trying to get rid of these errors. The come in bursts - in a single second, I could get as many as 30 to 40 of these messages.Now, I've taken a few different steps to track this down, but the problem is very elusive and hard to replicate. The mySQL error logs are unhelpful. I'm sure that my mySQL server isn't running out of connections, because if it was, I would receive a "too many connection" error message. (I deliberately lowered the max number of mySQL connections to test this.)I'm wondering if there's a system level limit that I'm running into that would be independant of exim or mySQL's configuration. I'm a little hazy in this area, but I would be talking about some sysctl variable, a ulimit on open sockets, or something to that effect.I'm rather stumped on this issue, so if anyone has any suggestions or hints on what might be causing this error or where I might start looking to find the root cause of the problem, I would be very grateful.Thanks,Michael Jeung
smime.p7s
Description: S/MIME cryptographic signature
Re: query problem
Sheeri, Thanks for the help. I tried your sample queries, but they dont really return what I'm looking for. I think I've found a solution though. Given the contents of a case, I'm looking for a unique case id, basicially I want to search for a case if it exists once I've decided the configuration I want. What I'm doing now is I alias the table I'm searching on for each Size, Qty combination, and create a self join on this and the Case_Type_idCaseType id. Here's an example of what I'm doing, I know CaseType_idCaseType '1' exists, and that it's make up is: +-+---+--+ | CaseType_idCaseType | Sizes_idsizes | qty | +-+---+--+ | 1 |10 |1 | | 1 |11 |1 | | 1 |12 |1 | | 1 |13 |1 | | 1 |14 |1 | | 1 |15 |1 | +-+---+--+ Taking these Sizes_idSizes and qty combinations I want to see if I can find the CaseType_idCaseType by itself. SELECT c10.CaseType_idCaseType AS case_id FROM CaseType_has_Sizes AS c10, CaseType_has_Sizes AS c11, CaseType_has_Sizes AS c12, CaseType_has_Sizes AS c13, CaseType_has_Sizes AS c14, CaseType_has_Sizes AS c15 WHERE c10.Sizes_idsizes = 10 AND c10.qty = 1 AND c11.Sizes_idsizes = 11 AND c11.qty = 1 AND c12.Sizes_idsizes = 12 AND c12.qty = 1 AND c13.Sizes_idsizes = 13 AND c13.qty = 1 AND c14.Sizes_idsizes = 14 AND c14.qty = 1 AND c15.Sizes_idsizes = 15 AND c15.qty = 1 AND c10.CaseType_idCaseType = c11.CaseType_idCaseType AND c11.CaseType_idCaseType = c12.CaseType_idCaseType AND c12.CaseType_idCaseType = c13.CaseType_idCaseType AND c13.CaseType_idCaseType = c14.CaseType_idCaseType AND c14.CaseType_idCaseType = c15.CaseType_idCaseType; It's a hell of a query, and I'm sure there's a better way to do it, but it will give me what I'm looking for. +-+ | case_id | +-+ | 1 | | 19 | | 37 | | 42 | | 44 | | 76 | | 110 | | 157 | | 552 | +-+ It's returned 9 cases on this query, but from testing it out, the first case_id returned is the one I'm looking for, all the other cases are ones that are larger than the one i searched on that include the size, qty combinations I specified in the query. If I add a 'LIMIT 1' at the end it will return only the case_id I'm looking for. As I said, I'm sure there's a better way to do this, and if anyone has any suggestions I'd only be happy to listen. Conor On 2/10/06, sheeri kritzer [EMAIL PROTECTED] wrote: You originally mention your UNION doesn't work but you did not specify the query. This is a simple or query, or union. You can do either: select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where (qty=1 and Sizes_idsizes=2) or (qty=1 and Sizes_idsizes=4); or select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where qty=1 and Sizes_idsizes=2 UNION select CaseType_idCaseType,Sizes_idsizes,qty from CaseType_has_Sizes where qty=1 and Sizes_idsizes=4; Please let me know if it's more complex than that, but when I ran it on my test data I got +-+---+--+ | CaseType_idCaseType | Sizes_idsizes | qty | +-+---+--+ | 60 | 2 |1 | | 60 | 4 |1 | | 61 | 2 |1 | | 61 | 4 |1 | +-+---+--+ for both results. -Sheeri On 2/8/06, Conor McTernan [EMAIL PROTECTED] wrote: Sheeri, The table I'm searching on has a composite primary key since it's mapping an N:M relationship between Cases and Sizes. Here's the create statement for the table I'm searching on: DROP TABLE IF EXISTS `CaseType_has_Sizes`; CREATE TABLE `CaseType_has_Sizes` ( `CaseType_idCaseType` int(10) unsigned NOT NULL, `Sizes_idsizes` int(10) unsigned NOT NULL, `qty` int(10) unsigned default NULL, PRIMARY KEY (`CaseType_idCaseType`,`Sizes_idsizes`), KEY `CaseType_has_sizes_FKIndex1` (`CaseType_idCaseType`), KEY `CaseType_has_sizes_FKIndex2` (`Sizes_idsizes`), CONSTRAINT `CaseType_has_Sizes_ibfk_1` FOREIGN KEY (`CaseType_idCaseType`) REFERENCES `CaseType` (`idCaseType`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `CaseType_has_Sizes_ibfk_2` FOREIGN KEY (`Sizes_idsizes`) REFERENCES `Sizes` (`idsizes`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Here's the Cases table: DROP TABLE IF EXISTS `CaseType`; CREATE TABLE `CaseType` ( `idCaseType` int(10) unsigned NOT NULL auto_increment, `caseName` char(32) default NULL, PRIMARY KEY (`idCaseType`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; and here's the Sizes case: DROP
Re: MySQL Connection Problems
On Thursday, 9 February 2006 at 17:02:06 -0800, Michael Jeung wrote:
Periodically, exim throws the following error into its panic log:
Feb 9 13:24:51 mx_server_1 exim[69953]: 2006-02-09 13:24:51 failed to expand
${lookup mysql {select distinct domain from email_table where
domain='$domain'}{$value}} while checking a list: lookup of select distinct
domain from email_table where domain='bob.com' gave DEFER: MYSQL connection
failed: Can't connect to MySQL server on '127.0.0.1' (61)
(where bob.com is one of our customer's domains that we host.)
This has been happening for months, and I'm trying to get rid of
these errors. The come in bursts - in a single second, I could get as
many as 30 to 40 of these messages.
Now, I've taken a few different steps to track this down, but the
problem is very elusive and hard to replicate. The mySQL error logs
are unhelpful. I'm sure that my mySQL server isn't running out of
connections, because if it was, I would receive a too many
connection error message. (I deliberately lowered the max number of
mySQL connections to test this.)
Since you've checked the MySQL logs, can I assume that you've
confirmed that the server hasn't been failing and being restarted?
Even if you don't find any evidence of such problems, can you check
with ps to see when the mysqld was started?
I'm wondering if there's a system level limit that I'm running into
that would be independant of exim or mySQL's configuration. I'm a
little hazy in this area, but I would be talking about some sysctl
variable, a ulimit on open sockets, or something to that effect.
This doesn't seem consistent with the errors you're reporting. If you
did hit some FreeBSD-related limit, it would be reported in
/var/log/messages.
Greg
--
Greg Lehey, Senior Software Engineer
MySQL AB, http://www.mysql.com/
Echunga, South Australia
Phone: +61-8-8388-8286 Mobile: +61-418-838-708
VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED]
Are you MySQL certified? http://www.mysql.com/certification/
pgp2Zc2X7AfT4.pgp
Description: PGP signature
Re: Error reading binlog - recoverable?
I tried mysqlbinlog for 5.0.18, but whether I use the --hexdump option or not, it still aborts with the same error: ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1635131433, event_type: 109 Could not read entry at offset 1384:Error in log format or read error This looks like it's past the end of the binlog file, or very near the end. Check to see if the file is larger than the location the slave is looking at. Is there anything else I can try? I'd consider doing the mysqlbinlog on the binlog file piped '|' into tail to see what the last log entry looks like. I've seen this kind of an error when the machine crashes and the whole binlog entry doesn't make it into the file. If this is the case you can either skip to the next binlog or try skipping the log entry via SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;START SLAVE; Brad Eacker ([EMAIL PROTECTED] ne [EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help configuring INNODB (Customer is ready to sue)
I have a customer who has been in production for a few weeks now having converted from MyISM to INNODB. We have been experiencing a few problems with our application. With that said, could you take a look at the info below from my show INNODB status and let me know if you see any problems based off of the my.cnf configuration. Should I be worried about the free buffers being at 0 Also, if you have any suggestions for modifying my.cnf, please let me know. Server is a Dell 1850, Dual P4 Processor with HT, RHEL4 64-bit, 4GB Memory. The only thing we have running on the server is MySQL v 4.0.26 (upgrading is not an option right now). The web is on another server. Our programmers are working on examining the code and queries. 060209 21:52:39 INNODB MONITOR OUTPUT Per second averages calculated from the last 58 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 32909, signal count 32897 Mutex spin waits 22210, rounds 96667, OS waits 3131 RW-shared spins 59775, OS waits 29579; RW-excl spins 122, OS waits 80 TRANSACTIONS Trx id counter 0 12549 Purge done for trx's n:o 0 12265 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 7200, OS thread id 1147570528 MySQL thread id 40, query id 32699 localhost root show INNODB status FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 10041 OS file reads, 98838 OS file writes, 28068 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 0, seg size 2, 65 inserts, 65 merged recs, 63 merges Hash table size 4980539, used cells 3352510, node heap has 8217 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1 2288366733 Log flushed up to 1 2288366733 Last checkpoint at 1 2288366733 0 pending log writes, 0 pending chkp writes 11189 log i/o's done, 0.00 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 2761264906; in additional pool allocated 6054656 Buffer pool size 153600 Free buffers 0 Database pages 145383 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 49486, created 458759, written 822494 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue Main thread process no. 7200, id 1147169120, state: waiting for server activity Number of rows inserted 19473761, updated 1063, deleted 0, read 6117388 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s MY. CNF # MySQL Server Instance Configuration File [mysqld] port=3306 socket=/tmp/mysql.sock basedir=/usr/local/mysql datadir=/var/lib/mysql default-character-set=latin1 lower_case_table_names=1 server-id=101 #log-slow-queries #long_query_time=1 #log max_allowed_packet=128M skip-bdb max_connections=100 thread_concurrency=10 open_files_limit=512 table_cache=512 thread_cache=20 query_cache_size=64M # replication #log-bin=/var/lib/mysql/binlog #server-id=90 #sync_binlog=1 # thread buffers read_buffer_size=128K read_rnd_buffer_size=256K sort_buffer_size=512K join_buffer_size=128K # myisam myisam_sort_buffer_size=205M key_buffer=384M # innodb innodb_additional_mem_pool_size=8M innodb_flush_log_at_trx_commit=2 innodb_log_buffer_size=8M innodb_buffer_pool_size=2400M innodb_log_file_size=512M innodb_data_file_path=ibdata1:2G:autoextend innodb_flush_method=O_DIRECT innodb_thread_concurrency=32 #*** Other Options *** [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash SHOW STATUS +--++ | Variable_name| Value | +--++ | Aborted_clients | 0 | | Aborted_connects | 2 | | Bytes_received | 3049852512 | | Bytes_sent | 345306910 | | Com_admin_commands | 1 | | Com_alter_table | 832
Re: Need help configuring INNODB (Customer is ready to sue)
What are the problems you've been experiencing? Did you convert all tables? How big is the database? On 2/9/06, Shaun Adams [EMAIL PROTECTED] wrote: I have a customer who has been in production for a few weeks now having converted from MyISM to INNODB. We have been experiencing a few problems with our application. With that said, could you take a look at the info below from my show INNODB status and let me know if you see any problems based off of the my.cnf configuration. Should I be worried about the free buffers being at 0 Also, if you have any suggestions for modifying my.cnf, please let me know. Server is a Dell 1850, Dual P4 Processor with HT, RHEL4 64-bit, 4GB Memory. The only thing we have running on the server is MySQL v 4.0.26 (upgrading is not an option right now). The web is on another server. Our programmers are working on examining the code and queries. 060209 21:52:39 INNODB MONITOR OUTPUT Per second averages calculated from the last 58 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 32909, signal count 32897 Mutex spin waits 22210, rounds 96667, OS waits 3131 RW-shared spins 59775, OS waits 29579; RW-excl spins 122, OS waits 80 TRANSACTIONS Trx id counter 0 12549 Purge done for trx's n:o 0 12265 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 7200, OS thread id 1147570528 MySQL thread id 40, query id 32699 localhost root show INNODB status FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 10041 OS file reads, 98838 OS file writes, 28068 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 0, seg size 2, 65 inserts, 65 merged recs, 63 merges Hash table size 4980539, used cells 3352510, node heap has 8217 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1 2288366733 Log flushed up to 1 2288366733 Last checkpoint at 1 2288366733 0 pending log writes, 0 pending chkp writes 11189 log i/o's done, 0.00 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 2761264906; in additional pool allocated 6054656 Buffer pool size 153600 Free buffers 0 Database pages 145383 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 49486, created 458759, written 822494 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue Main thread process no. 7200, id 1147169120, state: waiting for server activity Number of rows inserted 19473761, updated 1063, deleted 0, read 6117388 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s MY. CNF # MySQL Server Instance Configuration File [mysqld] port=3306 socket=/tmp/mysql.sock basedir=/usr/local/mysql datadir=/var/lib/mysql default-character-set=latin1 lower_case_table_names=1 server-id=101 #log-slow-queries #long_query_time=1 #log max_allowed_packet=128M skip-bdb max_connections=100 thread_concurrency=10 open_files_limit=512 table_cache=512 thread_cache=20 query_cache_size=64M # replication #log-bin=/var/lib/mysql/binlog #server-id=90 #sync_binlog=1 # thread buffers read_buffer_size=128K read_rnd_buffer_size=256K sort_buffer_size=512K join_buffer_size=128K # myisam myisam_sort_buffer_size=205M key_buffer=384M # innodb innodb_additional_mem_pool_size=8M innodb_flush_log_at_trx_commit=2 innodb_log_buffer_size=8M innodb_buffer_pool_size=2400M innodb_log_file_size=512M innodb_data_file_path=ibdata1:2G:autoextend innodb_flush_method=O_DIRECT innodb_thread_concurrency=32 #*** Other Options *** [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash SHOW STATUS +--++ | Variable_name| Value |
Surviving MySQL crash
Hi all, I have a MySQL server servng low-load applications. Problem is, the environment is sometimes unstable, leading the entire OS to crash. I notice that even in low-load situations the MySQL tables can be corrupted during crashes. My question is: is there a way for MySQL to flush when idle for x minutes, or some other way to avoid MySQL crashing (other than moving it out of the environment)? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Surviving MySQL crash
Hi, If they are MyISAM tables, you can run a FLUSH TABLES statement at a periodic interval. A good reference is here http://dev.mysql.com/doc/refman/5.0/en/flush.html Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Foo Ji-Haw [mailto:[EMAIL PROTECTED] Sent: Friday, 10 February 2006 3:45 PM To: mysql@lists.mysql.com Subject: Surviving MySQL crash Hi all, I have a MySQL server servng low-load applications. Problem is, the environment is sometimes unstable, leading the entire OS to crash. I notice that even in low-load situations the MySQL tables can be corrupted during crashes. My question is: is there a way for MySQL to flush when idle for x minutes, or some other way to avoid MySQL crashing (other than moving it out of the environment)? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Passing db names to a stored proc in MySQL 5
[EMAIL PROTECTED] wrote:
snip
You were so close! Try it more like this:
SET sSQL=CONCAT('SELECT * FROM ', passed_in_db_name, '.sites WHERE
site_name=?');
PREPARE query_statement from sSQL
snip
Doh!
I cannot believe that I didn't think of that - seems I was being dense
after all!
Thanks Shawn will give it a try
Rory
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help configuring INNODB (Customer is ready to sue)
Shaun, the my.cnf looks ok. You might be able to raise the InnoDB buffer pool size to 3G, but beware swapping. SHOW INNODB STATUS looks ok, though it would be more informative if it were taken during a typical workload. Free buffers 0 Having free buffers 0 is very normal. Buffers are replaced using the Least Recently Used strategy. What problems does the customer encounter? Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Shaun Adams [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, February 10, 2006 5:58 AM Subject: Need help configuring INNODB (Customer is ready to sue) --=_NextPart_000_00F7_01C62DCC.4BFFEE40 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit I have a customer who has been in production for a few weeks now having converted from MyISM to INNODB. We have been experiencing a few problems with our application. With that said, could you take a look at the info below from my show INNODB status and let me know if you see any problems based off of the my.cnf configuration. Should I be worried about the free buffers being at 0 Also, if you have any suggestions for modifying my.cnf, please let me know. Server is a Dell 1850, Dual P4 Processor with HT, RHEL4 64-bit, 4GB Memory. The only thing we have running on the server is MySQL v 4.0.26 (upgrading is not an option right now). The web is on another server. Our programmers are working on examining the code and queries. 060209 21:52:39 INNODB MONITOR OUTPUT Per second averages calculated from the last 58 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 32909, signal count 32897 Mutex spin waits 22210, rounds 96667, OS waits 3131 RW-shared spins 59775, OS waits 29579; RW-excl spins 122, OS waits 80 TRANSACTIONS Trx id counter 0 12549 Purge done for trx's n:o 0 12265 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 7200, OS thread id 1147570528 MySQL thread id 40, query id 32699 localhost root show INNODB status FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 10041 OS file reads, 98838 OS file writes, 28068 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 0, seg size 2, 65 inserts, 65 merged recs, 63 merges Hash table size 4980539, used cells 3352510, node heap has 8217 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1 2288366733 Log flushed up to 1 2288366733 Last checkpoint at 1 2288366733 0 pending log writes, 0 pending chkp writes 11189 log i/o's done, 0.00 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 2761264906; in additional pool allocated 6054656 Buffer pool size 153600 Free buffers 0 Database pages 145383 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 49486, created 458759, written 822494 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue Main thread process no. 7200, id 1147169120, state: waiting for server activity Number of rows inserted 19473761, updated 1063, deleted 0, read 6117388 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s MY. CNF # MySQL Server Instance Configuration File [mysqld] port=3306 socket=/tmp/mysql.sock basedir=/usr/local/mysql datadir=/var/lib/mysql default-character-set=latin1 lower_case_table_names=1 server-id=101 #log-slow-queries #long_query_time=1 #log max_allowed_packet=128M skip-bdb max_connections=100 thread_concurrency=10 open_files_limit=512 table_cache=512 thread_cache=20 query_cache_size=64M # replication #log-bin=/var/lib/mysql/binlog #server-id=90 #sync_binlog=1 # thread buffers read_buffer_size=128K read_rnd_buffer_size=256K
Re: Surviving MySQL crash
Ji-Haw, if the OS crashes do not corrupt files, then InnoDB tables would survive an OS crash without a problem. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Foo Ji-Haw [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, February 10, 2006 7:05 AM Subject: Surviving MySQL crash Hi all, I have a MySQL server servng low-load applications. Problem is, the environment is sometimes unstable, leading the entire OS to crash. I notice that even in low-load situations the MySQL tables can be corrupted during crashes. My question is: is there a way for MySQL to flush when idle for x minutes, or some other way to avoid MySQL crashing (other than moving it out of the environment)? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Surviving MySQL crash
Heikki Tuuri wrote: Ji-Haw, if the OS crashes do not corrupt files, then InnoDB tables would survive an OS crash without a problem. Thanks for the reply Heikki. Let me rephrase my problem: the data remains intact, but I suspect the indexes are corrupted. Basically I have to run myisamcheck and mysqlcheck to get my tables operational again. No data loss though. Logan (thanks Logan) suggested a periodic flush call. I am wondering if auto flushing is available? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Surviving MySQL crash
Foo Ji-Haw wrote: Heikki Tuuri wrote: Ji-Haw, if the OS crashes do not corrupt files, then InnoDB tables would survive an OS crash without a problem. Thanks for the reply Heikki. Let me rephrase my problem: the data remains intact, but I suspect the indexes are corrupted. Basically I have to run myisamcheck and mysqlcheck to get my tables operational again. No data loss though. Logan (thanks Logan) suggested a periodic flush call. I am wondering if auto flushing is available? Hi, You could set that up as a small cron job (Are you using unix/Linux?) like so 15 * * * * /usr/local/mysql/bin/mysql -u root -pxx -e 'FLUSH TABLES' This would run a FLUSH TABLES once every hour at 15 minutes past. If you are using Windows, sorry I'm not sure how to do it there. Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SOLVED]Passing db names to a stored proc in MySQL 5
snip
You were so close! Try it more like this:
SET sSQL=CONCAT('SELECT * FROM ', passed_in_db_name, '.sites WHERE
site_name=?');
PREPARE query_statement from sSQL
snip
Yup - got it to work - but it is so much hassle that I might seriously
reconsider having queries run across databases, as it works fine for one
or two stored procs, but I really cannot imagine doing hundreds this
way! Only change I had to make was to put the @ in front of the variable
name otherwise the prepare statement bombs!
Thanks Shawn and Martijn for the help
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
