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
