Fwd: strange mysql update ..
Any update from anybody ? -- Forwarded message -- From: umapathi b umapath...@gmail.com Date: Thu, Sep 8, 2011 at 4:28 AM Subject: Re: strange mysql update .. To: Ananda Kumar anan...@gmail.com Cc: mysql@lists.mysql.com Here is the o/p after the update .. user_id: 16078845 drivers_license: TEST1140DL login_date: 2011-06-19 11:20:07 course_id: 1011 regulator_id: 10840 test_info: completion_date: 2011-06-19 11:37:16 print_date: NULL password: test1140dl certificate_number: NULL login: test114...@1140dl.com - Umapathi On Thu, Sep 8, 2011 at 4:23 AM, Ananda Kumar anan...@gmail.com wrote: Can you lets us know what is the output of select * from user_info where user_id=16078845; On Thu, Sep 8, 2011 at 1:02 PM, umapathi b umapath...@gmail.com wrote: I wanted to change the login_date of one user . The original data of that user is like this .. select * from user_info where user_id = 16078845 \G *** 1. row *** user_id: 16078845 drivers_license: TEST1140DL login_date: 2011-06-19 11:20:07 course_id: 1011 regulator_id: 10840 test_info: completion_date: 2011-06-19 11:37:16 print_date: NULL password: test1140dl certificate_number: NULL login: test114...@1140dl.com I fired the update statement in a wrong way ..like this .. update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1 ; ( I forgot to use where . instead of where I used and ) update user_info set login_date='2011-08-05 04:15:05' where user_id =16078845 limit 1 ; ( this is the query intended ) after the update ..I got this message .. mysql update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1; Query OK, 1 row affected, 1 warning (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 It shows that one record is affected and one row changed .. I did show warnings ..the output is like this .. mysql show warnings; +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-08-05 04:15:05' | +-+--+-+ But I could not get any record in the table with the updated login_date .. mysql select * from user_info where login_date like '2011-08-05%' ; Empty set (0.67 sec) So my question is what happened exactly ? Why no records updated ? Help is highly appreciated in this regard .. - Umapathi umapath...@gmail.com
Re: strange mysql update ..
Try searching for a row that has a login_date of '-00-00 00:00:00' - Derek On Sep 9, 2011, at 8:52 AM, umapathi b wrote: Any update from anybody ? -- Forwarded message -- From: umapathi b umapath...@gmail.com Date: Thu, Sep 8, 2011 at 4:28 AM Subject: Re: strange mysql update .. To: Ananda Kumar anan...@gmail.com Cc: mysql@lists.mysql.com Here is the o/p after the update .. user_id: 16078845 drivers_license: TEST1140DL login_date: 2011-06-19 11:20:07 course_id: 1011 regulator_id: 10840 test_info: completion_date: 2011-06-19 11:37:16 print_date: NULL password: test1140dl certificate_number: NULL login: test114...@1140dl.com - Umapathi On Thu, Sep 8, 2011 at 4:23 AM, Ananda Kumar anan...@gmail.com wrote: Can you lets us know what is the output of select * from user_info where user_id=16078845; On Thu, Sep 8, 2011 at 1:02 PM, umapathi b umapath...@gmail.com wrote: I wanted to change the login_date of one user . The original data of that user is like this .. select * from user_info where user_id = 16078845 \G *** 1. row *** user_id: 16078845 drivers_license: TEST1140DL login_date: 2011-06-19 11:20:07 course_id: 1011 regulator_id: 10840 test_info: completion_date: 2011-06-19 11:37:16 print_date: NULL password: test1140dl certificate_number: NULL login: test114...@1140dl.com I fired the update statement in a wrong way ..like this .. update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1 ; ( I forgot to use where . instead of where I used and ) update user_info set login_date='2011-08-05 04:15:05' where user_id =16078845 limit 1 ; ( this is the query intended ) after the update ..I got this message .. mysql update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1; Query OK, 1 row affected, 1 warning (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 It shows that one record is affected and one row changed .. I did show warnings ..the output is like this .. mysql show warnings; +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-08-05 04:15:05' | +-+--+-+ But I could not get any record in the table with the updated login_date .. mysql select * from user_info where login_date like '2011-08-05%' ; Empty set (0.67 sec) So my question is what happened exactly ? Why no records updated ? Help is highly appreciated in this regard .. - Umapathi umapath...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
strange mysql update ..
I wanted to change the login_date of one user . The original data of that user is like this .. select * from user_info where user_id = 16078845 \G *** 1. row *** user_id: 16078845 drivers_license: TEST1140DL login_date: 2011-06-19 11:20:07 course_id: 1011 regulator_id: 10840 test_info: completion_date: 2011-06-19 11:37:16 print_date: NULL password: test1140dl certificate_number: NULL login: test114...@1140dl.com I fired the update statement in a wrong way ..like this .. update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1 ; ( I forgot to use where . instead of where I used and ) update user_info set login_date='2011-08-05 04:15:05' where user_id =16078845 limit 1 ; ( this is the query intended ) after the update ..I got this message .. mysql update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1; Query OK, 1 row affected, 1 warning (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 It shows that one record is affected and one row changed .. I did show warnings ..the output is like this .. mysql show warnings; +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-08-05 04:15:05' | +-+--+-+ But I could not get any record in the table with the updated login_date .. mysql select * from user_info where login_date like '2011-08-05%' ; Empty set (0.67 sec) So my question is what happened exactly ? Why no records updated ? Help is highly appreciated in this regard .. - Umapathi umapath...@gmail.com
Re: strange mysql update ..
Can you lets us know what is the output of select * from user_info where user_id=16078845; On Thu, Sep 8, 2011 at 1:02 PM, umapathi b umapath...@gmail.com wrote: I wanted to change the login_date of one user . The original data of that user is like this .. select * from user_info where user_id = 16078845 \G *** 1. row *** user_id: 16078845 drivers_license: TEST1140DL login_date: 2011-06-19 11:20:07 course_id: 1011 regulator_id: 10840 test_info: completion_date: 2011-06-19 11:37:16 print_date: NULL password: test1140dl certificate_number: NULL login: test114...@1140dl.com I fired the update statement in a wrong way ..like this .. update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1 ; ( I forgot to use where . instead of where I used and ) update user_info set login_date='2011-08-05 04:15:05' where user_id =16078845 limit 1 ; ( this is the query intended ) after the update ..I got this message .. mysql update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1; Query OK, 1 row affected, 1 warning (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 It shows that one record is affected and one row changed .. I did show warnings ..the output is like this .. mysql show warnings; +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-08-05 04:15:05' | +-+--+-+ But I could not get any record in the table with the updated login_date .. mysql select * from user_info where login_date like '2011-08-05%' ; Empty set (0.67 sec) So my question is what happened exactly ? Why no records updated ? Help is highly appreciated in this regard .. - Umapathi umapath...@gmail.com
Re: strange mysql update ..
Here is the o/p after the update .. user_id: 16078845 drivers_license: TEST1140DL login_date: 2011-06-19 11:20:07 course_id: 1011 regulator_id: 10840 test_info: completion_date: 2011-06-19 11:37:16 print_date: NULL password: test1140dl certificate_number: NULL login: test114...@1140dl.com - Umapathi On Thu, Sep 8, 2011 at 4:23 AM, Ananda Kumar anan...@gmail.com wrote: Can you lets us know what is the output of select * from user_info where user_id=16078845; On Thu, Sep 8, 2011 at 1:02 PM, umapathi b umapath...@gmail.com wrote: I wanted to change the login_date of one user . The original data of that user is like this .. select * from user_info where user_id = 16078845 \G *** 1. row *** user_id: 16078845 drivers_license: TEST1140DL login_date: 2011-06-19 11:20:07 course_id: 1011 regulator_id: 10840 test_info: completion_date: 2011-06-19 11:37:16 print_date: NULL password: test1140dl certificate_number: NULL login: test114...@1140dl.com I fired the update statement in a wrong way ..like this .. update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1 ; ( I forgot to use where . instead of where I used and ) update user_info set login_date='2011-08-05 04:15:05' where user_id =16078845 limit 1 ; ( this is the query intended ) after the update ..I got this message .. mysql update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1; Query OK, 1 row affected, 1 warning (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 It shows that one record is affected and one row changed .. I did show warnings ..the output is like this .. mysql show warnings; +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-08-05 04:15:05' | +-+--+-+ But I could not get any record in the table with the updated login_date .. mysql select * from user_info where login_date like '2011-08-05%' ; Empty set (0.67 sec) So my question is what happened exactly ? Why no records updated ? Help is highly appreciated in this regard .. - Umapathi umapath...@gmail.com
Re: strange mysql update ..
I fired the update statement in a wrong way ..like this .. update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1 ; ( I forgot to use where . instead of where I used and ) update user_info set login_date='2011-08-05 04:15:05' where user_id =16078845 limit 1 ; ( this is the query intended ) after the update ..I got this message .. mysql update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1; Query OK, 1 row affected, 1 warning (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 It shows that one record is affected and one row changed .. I did show warnings ..the output is like this .. | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-08-05 04:15:05' So my question is what happened exactly ? Why no records updated ? A lot of casting: (1) login_date='2011-08-05 04:15:05' and user_id =16078845; And implies boolean, so the result is the either true or false. MySQL doesn't like using non-numbers as booleans (the '2011-08-05 04:15:05') , this is the double spoken of. (2) login_date = false (or true, but that doesn't matter) But MySQL doesn't know booleans, to a number it is: (3) login_date = 0 But the column is a DATETIME (or TIMESTAMP) column, 0 or 1 is an incorrect value, cast to: (4) login_date = -00-00 00:00:00 So, somewhere there's (or was, may be overwritten) a record with that value, just 1 due to the limit 1, otherwise, the whole table would have that as a login_date (doesn't matter wether it was true or false). Check out: DB 5.1.58-1-log:(none) mysql SELECT 1 AND 1; +-+ | 1 AND 1 | +-+ | 1 | +-+ 1 row in set (0.00 sec) DB 5.1.58-1-log:(none) mysql SELECT 0 AND 1; +-+ | 0 AND 1 | +-+ | 0 | +-+ 1 row in set (0.01 sec) DB 5.1.58-1-log:(none) mysql SELECT '1' AND 1; +---+ | '1' AND 1 | +---+ | 1 | +---+ 1 row in set (0.03 sec) DB 5.1.58-1-log:(none) mysql SELECT 'a' AND 1; +---+ | 'a' AND 1 | +---+ | 0 | +---+ 1 row in set, 1 warning (0.03 sec) DB 5.1.58-1-log:(none) mysql SHOW WARNINGS; +-+--+---+ | Level | Code | Message | +-+--+---+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'a' | +-+--+---+ 1 row in set (0.01 sec) -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: strange mysql update ..
Nice Rik! On Thu, Sep 8, 2011 at 3:19 PM, Rik Wasmus r...@grib.nl wrote: I fired the update statement in a wrong way ..like this .. update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1 ; ( I forgot to use where . instead of where I used and ) update user_info set login_date='2011-08-05 04:15:05' where user_id =16078845 limit 1 ; ( this is the query intended ) after the update ..I got this message .. mysql update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845 limit 1; Query OK, 1 row affected, 1 warning (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 It shows that one record is affected and one row changed .. I did show warnings ..the output is like this .. | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-08-05 04:15:05' So my question is what happened exactly ? Why no records updated ? A lot of casting: (1) login_date='2011-08-05 04:15:05' and user_id =16078845; And implies boolean, so the result is the either true or false. MySQL doesn't like using non-numbers as booleans (the '2011-08-05 04:15:05') , this is the double spoken of. (2) login_date = false (or true, but that doesn't matter) But MySQL doesn't know booleans, to a number it is: (3) login_date = 0 But the column is a DATETIME (or TIMESTAMP) column, 0 or 1 is an incorrect value, cast to: (4) login_date = -00-00 00:00:00 So, somewhere there's (or was, may be overwritten) a record with that value, just 1 due to the limit 1, otherwise, the whole table would have that as a login_date (doesn't matter wether it was true or false). Check out: DB 5.1.58-1-log:(none) mysql SELECT 1 AND 1; +-+ | 1 AND 1 | +-+ | 1 | +-+ 1 row in set (0.00 sec) DB 5.1.58-1-log:(none) mysql SELECT 0 AND 1; +-+ | 0 AND 1 | +-+ | 0 | +-+ 1 row in set (0.01 sec) DB 5.1.58-1-log:(none) mysql SELECT '1' AND 1; +---+ | '1' AND 1 | +---+ | 1 | +---+ 1 row in set (0.03 sec) DB 5.1.58-1-log:(none) mysql SELECT 'a' AND 1; +---+ | 'a' AND 1 | +---+ | 0 | +---+ 1 row in set, 1 warning (0.03 sec) DB 5.1.58-1-log:(none) mysql SHOW WARNINGS; +-+--+---+ | Level | Code | Message | +-+--+---+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'a' | +-+--+---+ 1 row in set (0.01 sec) -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA
Re: mysql update
Did you check the server load when it took 10 min. Check the query log, number of connections and the number of queries in query log at that time. We need to analyze the system with the collected data what we have for monitoring. Thanks Suresh Kuna On Fri, Jan 22, 2010 at 12:25 PM, madunix madu...@gmail.com wrote: I have the following update procedure that update mySQL DB over the internet between source Linux Centos (local machine on my net behind a DMZ with real IP A.B.C.D) and target Linux fedora (web server www.myweb.com) every day on a specific time 18:00 through a crontab on my source linux server server(source) ---DMZ---ASA---Router-InternetHostingCompany---Myweb(target) [r...@source]# mysql -u updatex -p -h www.myweb.com test sample.SQL [r...@source]$ mysql -u updatex -p -h www.myweb.com test sample.SQL Enter password: * CURTIME() 19:41:44 CURTIME() 19:50:09 [r...@source]$ mysql -u updatex -p -h www.myweb.com test sample.SQL Enter password:* CURTIME() 08:26:08 CURTIME() 08:26:34 I did the above procedure multiple times in different times in the day. the duration of this procedure takes from 22sec to 10min see above, before a while it was running constant with duration of 30sec. I checked with my ISP, hosting company and network nothing been changed from the structure/configuration. [r...@source]# lsof -i -P | grep 3306 mysqld 3806 mysql 11u IPv4 10926 TCP *:3306 (LISTEN) mysql 15150user3u IPv4 297528 TCP 192.168.10.5:8376-www.myweb.com:3306 (ESTABLISHED) [r...@target]# netstat -a |grep mysql tcp0 0 *:mysql *:* LISTEN tcp0 0 www.myweb.:mysql A.B.C.D:8366 TIME_WAIT tcp0 11 www.myweb.:mysql A.B.C.D:8372 ESTABLISHED also i attached tcp connection between the nodes as above from source and target, can any one help why i have this behavior and how can i fix the delay, thinking doing QoS or clean up and remoteexcution at that time ... Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA
Re: mysql update
Hi, How big is sample.sql dump ? there are many different things, might taking the time. dns resolve issue, network issue tcp0 0 www.myweb.:mysql A.B.C.D:8366 *TIME_WAIT* (It should established) load on the server, check mysql processlist (any wrong query is there) Thanks, Krishna On Fri, Jan 22, 2010 at 12:25 PM, madunix madu...@gmail.com wrote: I have the following update procedure that update mySQL DB over the internet between source Linux Centos (local machine on my net behind a DMZ with real IP A.B.C.D) and target Linux fedora (web server www.myweb.com) every day on a specific time 18:00 through a crontab on my source linux server server(source) ---DMZ---ASA---Router-InternetHostingCompany---Myweb(target) [r...@source]# mysql -u updatex -p -h www.myweb.com test sample.SQL [r...@source]$ mysql -u updatex -p -h www.myweb.com test sample.SQL Enter password: * CURTIME() 19:41:44 CURTIME() 19:50:09 [r...@source]$ mysql -u updatex -p -h www.myweb.com test sample.SQL Enter password:* CURTIME() 08:26:08 CURTIME() 08:26:34 I did the above procedure multiple times in different times in the day. the duration of this procedure takes from 22sec to 10min see above, before a while it was running constant with duration of 30sec. I checked with my ISP, hosting company and network nothing been changed from the structure/configuration. [r...@source]# lsof -i -P | grep 3306 mysqld 3806 mysql 11u IPv4 10926 TCP *:3306 (LISTEN) mysql 15150user3u IPv4 297528 TCP 192.168.10.5:8376-www.myweb.com:3306 (ESTABLISHED) [r...@target]# netstat -a |grep mysql tcp0 0 *:mysql *:* LISTEN tcp0 0 www.myweb.:mysql A.B.C.D:8366 TIME_WAIT tcp0 11 www.myweb.:mysql A.B.C.D:8372 ESTABLISHED also i attached tcp connection between the nodes as above from source and target, can any one help why i have this behavior and how can i fix the delay, thinking doing QoS or clean up and remoteexcution at that time ... Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
mysql update
I have the following update procedure that update mySQL DB over the internet between source Linux Centos (local machine on my net behind a DMZ with real IP A.B.C.D) and target Linux fedora (web server www.myweb.com) every day on a specific time 18:00 through a crontab on my source linux server server(source) ---DMZ---ASA---Router-InternetHostingCompany---Myweb(target) [r...@source]# mysql -u updatex -p -h www.myweb.com test sample.SQL [r...@source]$ mysql -u updatex -p -h www.myweb.com test sample.SQL Enter password: * CURTIME() 19:41:44 CURTIME() 19:50:09 [r...@source]$ mysql -u updatex -p -h www.myweb.com test sample.SQL Enter password:* CURTIME() 08:26:08 CURTIME() 08:26:34 I did the above procedure multiple times in different times in the day. the duration of this procedure takes from 22sec to 10min see above, before a while it was running constant with duration of 30sec. I checked with my ISP, hosting company and network nothing been changed from the structure/configuration. [r...@source]# lsof -i -P | grep 3306 mysqld 3806 mysql 11u IPv4 10926 TCP *:3306 (LISTEN) mysql 15150user3u IPv4 297528 TCP 192.168.10.5:8376-www.myweb.com:3306 (ESTABLISHED) [r...@target]# netstat -a |grep mysql tcp0 0 *:mysql *:* LISTEN tcp0 0 www.myweb.:mysql A.B.C.D:8366 TIME_WAIT tcp0 11 www.myweb.:mysql A.B.C.D:8372 ESTABLISHED also i attached tcp connection between the nodes as above from source and target, can any one help why i have this behavior and how can i fix the delay, thinking doing QoS or clean up and remoteexcution at that time ... Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql update query gives error of lock wait timeout
On Sat, Aug 29, 2009 at 12:09 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I have a query which gives an error of lock wait timeout only this transaction is running with 2 records in the table. The query is : Update Test Set TestFlag = 1 Where TestID = 5; Can this one transaction lock my entire table? Its depend on the storage engine used for the table. For example if the table use MyISAM then the transaction will lock entire table, but if you use InnoDB the transaction will lock only the row which used in transaction. *cmiiw* -- Muhammad Subair
Re: Mysql update query gives error of lock wait timeout
Hi, You are right. The storage engine i m using is InnoDB. but then my concern is why this simple update query takes so long. Is it true that if its innodb table then even after specifying where condition it scans all indexed rows. I am bit confused as mysql.com have mentioned on there site that even if its a select it locks all the rows and it is reading with share lock mode. Can anyone give brief idea about what is exclusive lock and how it works and what is share lock mode and how it works. Thanks in advance. -- Regards, Manasi Save Artificial Machines Pvt Ltd. On Sat, Aug 29, 2009 at 12:09 PM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I have a query which gives an error of lock wait timeout only this transaction is running with 2 records in the table. The query is : Update Test Set TestFlag = 1 Where TestID = 5; Can this one transaction lock my entire table? Its depend on the storage engine used for the table. For example if the table use MyISAM then the transaction will lock entire table, but if you use InnoDB the transaction will lock only the row which used in transaction. *cmiiw* -- Muhammad Subair -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql update query gives error of lock wait timeout
Increase your Innodb_buffer_pool_size. It will solve your problem. Thanks, Krishna On Sat, Aug 29, 2009 at 10:39 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Hi All, I have a query which gives an error of lock wait timeout only this transaction is running with 2 records in the table. The query is : Update Test Set TestFlag = 1 Where TestID = 5; Can this one transaction lock my entire table? -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. manasi.s...@artificialmachines.com Ph:- 9833537392 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Mysql update query gives error of lock wait timeout
Hi All, I have a query which gives an error of lock wait timeout only this transaction is running with 2 records in the table. The query is : Update Test Set TestFlag = 1 Where TestID = 5; Can this one transaction lock my entire table? -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. manasi.s...@artificialmachines.com Ph:- 9833537392 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL update 4.0.20 w/RPM's
Hello, got this error when upgrading our MySQL: rpm -Uvh MySQL-shared-4.0.20-0.i386.rpm Preparing...### [100%] file /usr/lib/libmysqlclient.so.12.0.0 from install of MySQL-shared-4.0.20-0 conflicts with file from package MySQL-shared-compat-4.0.18-0 file /usr/lib/libmysqlclient_r.so.12.0.0 from install of MySQL-shared-4.0.20-0 conflicts with file from package MySQL-shared-compat-4.0.18-0 MySQL upgraded to version 4.0.20 with no apparent problems so far, from 4.0.18 but was wondering if this could be a problem down the road or should we remove the old shared or shared-compact RPM's, then do the upgrade to the 4.0.20 RPM's ?? These are the current installed RPM's, O/S RH/Linux 7.3: MySQL-bench-4.0.18-0 MySQL-embedded-4.0.20-0 MySQL-shared-4.0.18-0 MySQL-server-4.0.20-0 MySQL-devel-4.0.20-0 MySQL-shared-compat-4.0.18-0 MySQL-client-4.0.20-0 -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN Messager: [EMAIL PROTECTED] Cell:1.985.320.1191 - Office:1.712.737.2548 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Doubts with mysql update,delete queries
Hi all: Having some doubts with sql select queries here : Right now, I have a student and a withdraw tables. For example, when student John has withdrawn, his name will be inserted into withdraw table but John's record will be kept in student table for a period of 30 days before it is deleted from student table. In the meantime, in student table, the status for John should be set to 0 (inactive). Wonder the solutions I have mentioned is possible? And if so, how shld i go about doing it ??Hope to get some help really soon...Thanks in advance! student table withdraw table --- - |student_name | status |student_name | withdraw_reason --- - | John |0 | John | migrate --- - | James |1 | --- Regards, Irin Chiang. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doubts with mysql update,delete queries
Hi all: Having some doubts with sql select queries here : Right now, I have a student and a withdraw tables. For example, when student John has withdrawn, his name will be inserted into withdraw table but John's record will be kept in student table for a period of 30 days before it is deleted from student table. In the meantime, in student table, the status for John should be set to 0 (inactive). Wonder the solutions I have mentioned is possible? And if so, how shld i go about doing it ??Hope to get some help really soon...Thanks in advance! student table withdraw table --- - |student_name | status |student_name | withdraw_reason --- - | John |0 | John | migrate --- - | James |1 | --- Regards, Irin Chiang. --- Hi There, I would have a delete_at column on the table and once a row is queued for delete, I would update this row with the date n days from now. I would then have a daily cron that went looking for rows to delete on that day Cheers Chris --- Hi, yah that's what I'm looking for, creating a cronjob whereby this cron will automatically delete the record once 30 days is up...maybe using something like that: DELETE FROM tablename WHERE (DATA_SUB(NOW()), INTERVAL 30 DAY) name_of_date_column; But now the point is, what if the student decided to cancel his withdrawal within these 30 days, (that was also the reason why to keep the record for 30 before deletion ) , how shld i go about updating the status of the student to 1 (active)again ???And, how to cancel the cronjob so that it will not delete the student record after 30 days???I hope I have made myself clear... Any useful links that teaches how to create a Cronjob Regards, Irin Chiang. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doubts with mysql update,delete queries
On 12/3/03 7:27 PM [EMAIL PROTECTED] wrote: Hi, yah that's what I'm looking for, creating a cronjob whereby this cron will automatically delete the record once 30 days is up...maybe using something like that: DELETE FROM tablename WHERE (DATA_SUB(NOW()), INTERVAL 30 DAY) name_of_date_column; But now the point is, what if the student decided to cancel his withdrawal within these 30 days, (that was also the reason why to keep the record for 30 before deletion ) , how shld i go about updating the status of the student to 1 (active)again ??? How is the 0 getting in to the status field in the first place? If he cancels the withdrawal (through some web form you have?) Then update the table accordingly. And, how to cancel the cronjob so that it will not delete the student record after 30 days???I hope I have made myself clear... Add an AND clause to your DELETE statement that checks to see if the status field is 1 or 0. --Jo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql update
i want to update every row in the mysql table that matches a part of each row in it. ie. for example if a table contains the following rows : Pid:21577 PPid: 21174 Uid:501 Gid:501 i want to change the first row to Pid:30209 similarly for any row plz advice me Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger 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: mysql update
Prem Soman [EMAIL PROTECTED] wrote: i want to update every row in the mysql table that matches a part of each row in it. ie. for example if a table contains the following rows : Pid:21577 PPid: 21174 Uid:501 Gid:501 i want to change the first row to Pid:30209 similarly for any row Sorry, but your explanation is not clear enough for me. Use UPDATE command to change values: http://www.mysql.com/doc/en/UPDATE.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql update on myisam
Hi there, I'm running MySQL-server-4.0.10-0.i386 on a Linux Box (SuSE 8.1) and all tables are MyISM. Today a made strange observation. All update statements on one table where completely ignored by the server. There was no error message - just a 0 rows affected. After restart of mysqld everyting worked fine and the privious statement was successful. Since I don't know how to reproduce this a more generall question. Does anybody know of bugs under 4.0.10-0 which prevent write access to MyISAM tables without affecting read access. Might it be that a lock on this table was stuck... Always thankful for help in any way Jerg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql Update
Hi everybody I sent a couple of mail recently, asking some help for some strange behaviour with select and updates. I hope many of you have read it...the problem was that making a SELECT something FROM table WHERE 'Where_Clause' gave some results, 0. the query: UPDATE table SET something WHERE 'THe_Same_Clause_As_Before' gave: 0 matching rows, 0 rows changed. that seemed to me strange, so that I started to investigate about my tables and indexes. I was using Mysql-4.0.1-alpha. I decided to update, switching to version 4.0.11-gamma. The problem has disappeared! Maybe it was a bug. I hope this can be useful for someone. Daniele - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: mysql update user
Marco, Tuesday, October 29, 2002, 7:22:44 AM, you wrote: MB I need your expert and slow guided assistance. MB I would like someone to explain to me the correct mysql commands MB monitor to achive the following correctly. MB It would greatly help if you work for a hosting company. MB The question is: MB User asks to be given access to mysql database for his hosting MB account. MB username: marco MB pssword: mysecret MB database name: lotsofusers. MB Of first I set the administrative and root password using the document here: MB ftp://216.94.9.36/Setting%20the%20Root%20and%20Administrative%20Passwords.doc MB Then What I would like to do is: MB allow another user access to create and complete control over his MB database, inside tables and data information inside the database MB (we are talking about total control, creation, editing and deletion, MB (the ins and outs of that database assigned to the user called: marco: MB Enter the following command at your shell prompt: MB mysql -u root -p lotsofusers MB I am in. MB Then here is where I get bit stuck. MB I would some one to teach me how to do for that user MB and allow him/website to have access to that database and that database MB alone, having complete control over it. MB Using the example I have given you please teach me how to do so in detail?. MB I look forward to be taken a novice, As I have gone through the docs online. MB and it was not a great teacher!. Online manual contains enough info about your question :) Read about GRANT: http://www.mysql.com/doc/en/GRANT.html It's easy to set permissions using GRANT. if you want to give all rights on the certain database you can do it like GRANT ALL ON database_name.* TO 'user_name'@'host_name' IDENTIFIED BY 'user_password'; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql update user
Hello there, I need your expert and slow guided assistance. I would like someone to explain to me the correct mysql commands monitor to achive the following correctly. It would greatly help if you work for a hosting company. The question is: User asks to be given access to mysql database for his hosting account. username: marco pssword: mysecret database name: lotsofusers. Of first I set the administrative and root password using the document here: ftp://216.94.9.36/Setting%20the%20Root%20and%20Administrative%20Passwords.doc Then What I would like to do is: allow another user access to create and complete control over his database, inside tables and data information inside the database (we are talking about total control, creation, editing and deletion, (the ins and outs of that database assigned to the user called: marco: Enter the following command at your shell prompt: mysql -u root -p lotsofusers I am in. Then here is where I get bit stuck. I would some one to teach me how to do for that user and allow him/website to have access to that database and that database alone, having complete control over it. Using the example I have given you please teach me how to do so in detail?. I look forward to be taken a novice, As I have gone through the docs online. and it was not a great teacher!. Please help?!. regards, m a r c o - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: MySQL update subquery replacement
Steve, Tuesday, July 23, 2002, 7:36:39 AM, you wrote: S Can anyone tell me how do achieve the following in MySQL: S Update oitems S SET Pstatus = SELECT product.Pstatus from products S WHERE oitems.Pstatus not like %Printed% SAND oitems.ProdCode=products.Code S i.e. I need to update all order item status flields that have not been S printed, with the matching product's current status If you are using 4.0.2 you can use multi-table updates otherwise you can't do it with single SQL statement. Note multi-table updates are only beta. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL update subquery replacement
Can anyone tell me how do achieve the following in MySQL: Update oitems SET Pstatus = SELECT product.Pstatus from products WHERE oitems.Pstatus not like %Printed% AND oitems.ProdCode=products.Code i.e. I need to update all order item status flields that have not been printed, with the matching product's current status regards Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL update subquery replacement
OOps somehow my last request got re: on the Subject Can anyone tell me how do achieve the following in MySQL: Update oitems SET Pstatus = SELECT product.Pstatus from products WHERE oitems.Pstatus not like %Printed% AND oitems.ProdCode=products.Code i.e. I need to update all order item status flields that have not been printed, with the matching product's current status regards Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: install mysql (update)
tecnico, Friday, July 12, 2002, 3:30:26 AM, you wrote: t I am trying to update my version and I am not getting. My current version is t 3.23.49-nt. I am using MySql in Windows 2000. t When I try to install it happens the mistake -103: t An error occurred during the moves date process: -103 t Component: servers t File group: t File: Did you removed your old version before or you installed new MySQL server in another location? If you removed your old version before installation and then got this error, reboot Windows, and try to install again. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: install mysql (update)
At 21:30 11/7/2002 -0300, [EMAIL PROTECTED] wrote: Hi, I am trying to update my version and I am not getting. My current version is 3.23.49-nt. I am using MySql in Windows 2000. When I try to install it happens the mistake -103: An error occurred during the moves date process: -103 Component: servers File group: File: Shutdown the current server before to begin the new install. Regards, -- For technical support contracts, visit https//order.mysql.com __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel Angel Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ São Paulo - Brazil ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
install mysql (update)
I am trying to update my version and I am not getting. My current version is 3.23.49-nt. I am using MySql in Windows 2000. When I try to install it happens the mistake -103: An error occurred during the moves date process: -103 Component: servers File group: File: thank you - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL Update Query with a Join
I'm told that one cannot write an Update Query with a Join in MySQL. Does anyone know if this is true? Thank you for your help Joe Joseph W. Cherner, President SmokeFree Educational Services, Inc. http://www.smokefree.org Never doubt that a small group of thoughtful citizens can change the world. Indeed, it's the only thing that ever has. Margaret Mead - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL Update based on SELECT CRITERIA
* USER Is there any way to do an Update...Select like an InsertSelect? For instance can I fill one table with data from another table. The columns do not match exactly so a table copy won't do much good. But the data retrieved in the select command is compatible with the new table fields. Use INSERT ... SELECT. What you describe is not an update, it is an insert. The columns of the new table does not have to match with the exising table, you just need to formulate the select so that the correct fields are selected in the correct order: insert into whatever select f1, mid(f2,5,3) as f2, year(f3) as f3 from someother; You can even use joins: insert into whatever select f1, mid(f2,5,3) as f2, year(another.f3) as f3 from someother, another where another.id = someother.something; -- Roger query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL Update based on SELECT CRITERIA
Is there any way to do an Update...Select like an InsertSelect? For instance can I fill one table with data from another table. The columns do not match exactly so a table copy won't do much good. But the data retrieved in the select command is compatible with the new table fields. sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: DBI and MySQL update log question
Comments inline below.
On Wednesday 09 January 2002 18:13, Paul DuBois wrote:
At 17:23 -0800 1/9/02, Kyle Hayes wrote:
[snipped replication explanation]
The program works by getting the contents of the update log and using
the Perl DBI do() function on each statement.
The problem that occurs causes this (debug output):
SQL: use acc;
DBD::mysql::db do failed: You have an error in your SQL syntax near
'; ' at line 1 at /opt/bin/replicator_client line 199, GEN2 line 4.
I have found that this works _IFF_ I strip off the trailing linefeed
after the semicolon at the end of the statement. I could swear that
we create SQL statement in Perl here docs that end with a semicolon
and several empty lines. Is there a DBI guru that can tell me what is
going on? I really notice this especially in USE statements...
Don't add the semicolon. That's for when you use the mysql client,
which you aren't doing.
Hmm, most of our SQL is generated with the semicolon. It definitely works
with the semicolon. I print out what I send to DBI-do() and it has a
semicolon.
The error message is complaining about the semicolon, by the way,
not the linefeed.
As far as I can tell, this is not true. Here's the main loop that works
(changed slightly to protect the innocent :-):
$input_line = $input;
if($input_line =~ m/^\;$/) {
# OK, we have the end of a SQL statement.
# remove trailing newline
chomp($input_line);
# add this to the rest of the SQL
$sql .= $input_line;
# run it through the database
if(length($sql)0 ) {
# use eval because we want to catch errors.
eval { $rc = $dbh-do($sql); };
} else {
$rc = 1;
}
# did we have a problem?
if(!defined($rc) or $@) {
# bad news, we have a crash problem.
... print out stuff for the error log file...
exit(1);
}
# SQL successfully handled, reset the SQL accumulation variable.
$sql = '';
} else {
# no match, still part of the SQL statement. Strip comments.
if($input_line !~ m/^\s*\#/) {
$sql .= $input_line;
}
}
Obviously, you need to wrap this in a loop, but this is what is working
now. I have added debugging printouts at various places above and can
state that the SQL that goes through DBI does have a trailing semicolon,
but not a trailing newline. Really weird.
Best,
Kyle
--
Cut costs, Fax smart. Use iPrint2Fax worldwide and save!
- iPrint2Fax software is FREE, no fax modem required.
- FREE iPrint2Fax to a single email address.
- iPrint2Fax to PSTN based Fax (Up to 95% Savings)
- iPrint2Fax Broadcasting: Send 100s of faxes and fax to emails
in the time it takes to send just one!
==
FREE software download available at www.iPrint2Fax.com
==
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
DBI and MySQL update log question
I am putting the finishing touches on a completely reworked replication server/client scheme (we cannot use MySQL's native replication for what we are doing). The whole thing is written in Perl/DBI and runs on Linux (x86). We are using various versions of MySQL, but all 3.23.x series. We are slowly upgrading to the latest version 3.23.47. I don't know the version of the MySQL Perl DBI libraries we use, but they are not too old. We have mixed Red Hat and SuSE systems. The program works by getting the contents of the update log and using the Perl DBI do() function on each statement. The problem that occurs causes this (debug output): SQL: use acc; DBD::mysql::db do failed: You have an error in your SQL syntax near '; ' at line 1 at /opt/bin/replicator_client line 199, GEN2 line 4. I have found that this works _IFF_ I strip off the trailing linefeed after the semicolon at the end of the statement. I could swear that we create SQL statement in Perl here docs that end with a semicolon and several empty lines. Is there a DBI guru that can tell me what is going on? I really notice this especially in USE statements... Anyone have an idea? I haven't found anything particularly relevent in the mailing list archives. Best, Kyle -- Cut costs, Fax smart. Use iPrint2Fax worldwide and save! - iPrint2Fax software is FREE, no fax modem required. - FREE iPrint2Fax to a single email address. - iPrint2Fax to PSTN based Fax (Up to 95% Savings) - iPrint2Fax Broadcasting: Send 100s of faxes and fax to emails in the time it takes to send just one! == FREE software download available at www.iPrint2Fax.com == - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: DBI and MySQL update log question
At 17:23 -0800 1/9/02, Kyle Hayes wrote: I am putting the finishing touches on a completely reworked replication server/client scheme (we cannot use MySQL's native replication for what we are doing). The whole thing is written in Perl/DBI and runs on Linux (x86). We are using various versions of MySQL, but all 3.23.x series. We are slowly upgrading to the latest version 3.23.47. I don't know the version of the MySQL Perl DBI libraries we use, but they are not too old. We have mixed Red Hat and SuSE systems. The program works by getting the contents of the update log and using the Perl DBI do() function on each statement. The problem that occurs causes this (debug output): SQL: use acc; DBD::mysql::db do failed: You have an error in your SQL syntax near '; ' at line 1 at /opt/bin/replicator_client line 199, GEN2 line 4. I have found that this works _IFF_ I strip off the trailing linefeed after the semicolon at the end of the statement. I could swear that we create SQL statement in Perl here docs that end with a semicolon and several empty lines. Is there a DBI guru that can tell me what is going on? I really notice this especially in USE statements... Don't add the semicolon. That's for when you use the mysql client, which you aren't doing. The error message is complaining about the semicolon, by the way, not the linefeed. Anyone have an idea? I haven't found anything particularly relevent in the mailing list archives. Best, Kyle - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Update Logging ???
On Fri, Sep 21, 2001 at 01:43:32AM -0400, Will French wrote: Actually yes (if I read the documentation correctly - I have not tried this). But if his ISP won't allow it, he can't do it. Maybe I misunderstood. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 27 days, processed 543,190,508 queries (232/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Update Logging ???
Preface #1: Hi MySQL Admin! There's an oddity at http://www.mysql.com/doc/. Entering my.cnf returns no hits. Entering cnf returns all relevant hits. Preface #2: Hi All! Folks, this is the *best* online documentation I have ever ever seen. You can save youself lotsa time waiting for replies here (if there's even gonna -be- a reply!) by checking there -first- and then if there's something you don't understand name the page and why you don't understand it. This is also one of the best user supported lists around but again only if someone else care to write something, and you can understand the answer. You can even add comments to the docs! I haven't tried it yet, it's a 'register by your email address' thing, but this is a -great- idea to build FAQs right into the docs. I'm impressed. I'm not a 'RTFM-you-wimdit-and-go-away' kinda guy. I answer questions -and- point to the relevant docs. You'll almost never see me ask a question because I read all the traffic here, look through the books, and search the docs and the net (google and groups.google) intensively before posting anywhere. It works 99.64% of the time. The ML search but is a PITA and that's a can of worms I don't care to open here. Seriously, the online docs are fantastic. In any case Hello Christopher! Actually yes (if I read the documentation correctly - I have not tried this). In MySql Manual section 4.10.5 Replication Options in my.cnf, I find: binlog-do-db=database_name Tells the master it should log updates for the specified database, and exclude all others not explicitly mentioned. (Example: binlog-do-db=some_database) Will ( Others): So what you are essentially saying is that I can use the my.cnf file to turn on my own options. That makes sense. Can anyone help me sort out what the my.cnf file would look like? That's wy to much to go into here generically. See http://www.mysql.com/doc/ and enter cnf. The first two hits contain all the gruesome details. In addition to binlog-do-db you'll probably also need to define log-bin to a path you have write permission. Jeremy posted simply 'No' but http://www.mysql.com/doc/O/p/Option_files.html says that ~/.my.cnf overrides previous cnf options. The server can override the user at the command line but only trying it can prove whether it works or not. Have a :) day! jb -- jim barchuk [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL Update Logging ???
Friends: My ISP does not enable update logging with their MySQL server. Is it possible for them or me to turn on update logging just for MY database(s) ??? I appreciate any help God Bless America, Christopher Raymond
Re: MySQL Update Logging ???
On Thu, Sep 20, 2001 at 04:25:29PM -0500, Christopher Raymond wrote: Friends: My ISP does not enable update logging with their MySQL server. Is it possible for them or me to turn on update logging just for MY database(s) ??? No. Jeremy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Update Logging ???
on 9/21/01 12:43 AM, Will French at [EMAIL PROTECTED] wrote: Actually yes (if I read the documentation correctly - I have not tried this). In MySql Manual section 4.10.5 Replication Options in my.cnf, I find: binlog-do-db=database_name Tells the master it should log updates for the specified database, and exclude all others not explicitly mentioned. (Example: binlog-do-db=some_database) Will ( Others): So what you are essentially saying is that I can use the my.cnf file to turn on my own options. That makes sense. Can anyone help me sort out what the my.cnf file would look like? Thanks, Christopher Raymond - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SSL, mysql, update
As far as I know, 'update' is a reserved word and you should quote it with reversed quotes (you know they are on my keyboard near '1'). I hope it helps. Daniel BI Hi, I'm useing mysql php and SSL on a colbalt raq, the problem I'm having is that when ever I use the update query it doesn't always update, say just UPDATE table SET f1 = 'update' WHERE id = '2' some times when I first run the page it works but then other times it doesn't .. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SSL, mysql, update
Hi, I'm useing mysql php and SSL on a colbalt raq, the problem I'm having is that when ever I use the update query it doesn't always update, say just UPDATE table SET f1 = 'update' WHERE id = '2' some times when I first run the page it works but then other times it doesn't update, I also use phpMyAdmin and it does the same thing (only on the update though nothing else) this only started happening once we installed SSL on our server. Just wondering if anyone knows how to fix this. Thanks Viper_SB ___ Do You Yahoo!? Get your free @yahoo.ca address at http://mail.yahoo.ca - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Reserved words in MySQL update
Hi. On Wed, Feb 14, 2001 at 03:28:23PM -, [EMAIL PROTECTED] wrote: Description: Upgrading from 3.22 to 3.23 with a field having a reserved name ALTER TABLE barfs and doesn't let me do ALTER TABLE casualties CHANGE when cas_when DATE NOT NULL; ALTER TABLE casualties CHANGE `when` cas_when DATE NOT NULL; should work. Note, that backticks will allow to use reserved words or special characters in table, field and other names/labels. Bye, Benjamin. cos of the reserved word "when". How-To-Repeat: Have a field called "when" in 3.22, upgrade to 3.23 Fix: Manually edited the .frm file - didn't feel right Need something to either do it automatically on upgrade, or a standalone utility to modify the mysql files directly. [...] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Reserved words in MySQL update
Description: Upgrading from 3.22 to 3.23 with a field having a reserved name ALTER TABLE barfs and doesn't let me do ALTER TABLE casualties CHANGE when cas_when DATE NOT NULL; cos of the reserved word "when". How-To-Repeat: Have a field called "when" in 3.22, upgrade to 3.23 Fix: Manually edited the .frm file - didn't feel right Need something to either do it automatically on upgrade, or a standalone utility to modify the mysql files directly. Submitter-Id: submitter ID Originator:David Croft Organization: MySQL support: none Synopsis: Reserved words in MySQL update Severity: serious Priority: medium Category: mysql Class: change-request Release: mysql-3.23.32 (Official MySQL binary) Environment: System: Linux papaya 2.2.18 #3 Sat Feb 10 02:36:04 EST 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.2/specs gcc version 2.95.2 2220 (Debian GNU/Linux) Compilation info: CC='gcc' CFLAGS='-O6 -mpentium -fomit-frame-pointer' CXX='gcc' CXXFLAGS='-O6 -mpentium -fomit-frame-pointer -felide-constructors' LDFLAGS='-static' LIBC: lrwxrwxrwx1 root root 13 Feb 9 08:38 /lib/libc.so.6 - libc-2.1.3.so -rwxr-xr-x1 root root 887712 Jan 12 04:59 /lib/libc-2.1.3.so -rw-r--r--1 root root 2089420 Jan 12 05:00 /usr/lib/libc.a -rw-r--r--1 root root 178 Jan 12 05:00 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --enable-assembler --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --disable-shared Perl: This is perl, version 5.005_03 built for i386-linux - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
