strange mysql update ..

--20cf303bff7274968104ac690fce
Content-Type: text/plain; charset=ISO-8859-1

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: 11111111
completion_date: 2011-06-19 11:37:16
print_date: NULL
password: test1140dl
certificate_number: NULL
login: test1140dl@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
umapathi.b@gmail.com

--20cf303bff7274968104ac690fce--

Re: strange mysql update ..

--20cf30563dc1b7ceff04ac69c662
Content-Type: text/plain; charset=ISO-8859-1

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 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: 11111111
> completion_date: 2011-06-19 11:37:16
> print_date: NULL
> password: test1140dl
> certificate_number: NULL
> login: test1140dl@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
> umapathi.b@gmail.com
>

--20cf30563dc1b7ceff04ac69c662--

Re: strange mysql update ..

--20cf301d416ee11a1e04ac69d8de
Content-Type: text/plain; charset=ISO-8859-1

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: 11111111
completion_date: 2011-06-19 11:37:16
print_date: NULL
password: test1140dl
certificate_number: NULL
login: test1140dl@1140dl.com


- Umapathi

On Thu, Sep 8, 2011 at 4:23 AM, Ananda Kumar 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 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: 11111111
>> completion_date: 2011-06-19 11:37:16
>> print_date: NULL
>> password: test1140dl
>> certificate_number: NULL
>> login: test1140dl@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
>> umapathi.b@gmail.com
>>
>
>

--20cf301d416ee11a1e04ac69d8de--

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 = 0000-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=gcdmg-mysql-2@m.gmane.org

Re: strange mysql update ..

--20cf305b0daaba14b804ac7aab11
Content-Type: text/plain; charset=ISO-8859-1

Nice Rik!

On Thu, Sep 8, 2011 at 3:19 PM, Rik Wasmus 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 = 0000-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=sureshkumarilu@gmail.com
>
>


--
Thanks
Suresh Kuna
MySQL DBA

--20cf305b0daaba14b804ac7aab11--

Fwd: strange mysql update ..

--90e6ba6134ee16dab304ac81a67a
Content-Type: text/plain; charset=ISO-8859-1

Any update from anybody ?


---------- Forwarded message ----------
From: umapathi b
Date: Thu, Sep 8, 2011 at 4:28 AM
Subject: Re: strange mysql update ..
To: Ananda Kumar
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: 11111111
completion_date: 2011-06-19 11:37:16
print_date: NULL
password: test1140dl
certificate_number: NULL
login: test1140dl@1140dl.com


- Umapathi


On Thu, Sep 8, 2011 at 4:23 AM, Ananda Kumar 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 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: 11111111
>> completion_date: 2011-06-19 11:37:16
>> print_date: NULL
>> password: test1140dl
>> certificate_number: NULL
>> login: test1140dl@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
>> umapathi.b@gmail.com
>>
>
>

--90e6ba6134ee16dab304ac81a67a--

Re: strange mysql update ..

Try searching for a row that has a login_date of '0000-00-00 00:00:00'

- Derek

On Sep 9, 2011, at 8:52 AM, umapathi b wrote:

> Any update from anybody ?
>=20
>=20
> ---------- Forwarded message ----------
> From: umapathi b
> Date: Thu, Sep 8, 2011 at 4:28 AM
> Subject: Re: strange mysql update ..
> To: Ananda Kumar
> Cc: mysql@lists.mysql.com
>=20
>=20
> Here is the o/p after the update ..
>=20
>=20
> user_id: 16078845
> drivers_license: TEST1140DL
> login_date: 2011-06-19 11:20:07
> course_id: 1011
> regulator_id: 10840
> test_info: 11111111
> completion_date: 2011-06-19 11:37:16
> print_date: NULL
> password: test1140dl
> certificate_number: NULL
> login: test1140dl@1140dl.com
>=20
>=20
> - Umapathi
>=20
>=20
> On Thu, Sep 8, 2011 at 4:23 AM, Ananda Kumar =
wrote:
>=20
>> Can you lets us know what is the output of
>>=20
>> select * from user_info where user_id=3D16078845;
>>=20
>>=20
>> On Thu, Sep 8, 2011 at 1:02 PM, umapathi b =
wrote:
>>=20
>>> I wanted to change the login_date of one user . The original data of =
that
>>> user is like this ..
>>>=20
>>> select * from user_info where user_id =3D 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: 11111111
>>> completion_date: 2011-06-19 11:37:16
>>> print_date: NULL
>>> password: test1140dl
>>> certificate_number: NULL
>>> login: test1140dl@1140dl.com
>>>=20
>>> I fired the update statement in a wrong way ..like this ..
>>>=20
>>> update user_info set login_date=3D'2011-08-05 04:15:05' and user_id
>>> =3D16078845
>>> limit 1 ;
>>> ( I forgot to use where . instead of where I used and )
>>> update user_info set login_date=3D'2011-08-05 04:15:05' where =
user_id
>>> =3D16078845 limit 1 ; ( this is the query intended )
>>>=20
>>> after the update ..I got this message ..
>>> mysql> update user_info set login_date=3D'2011-08-05 04:15:05' and =
user_id
>>> =3D16078845 limit 1;
>>> Query OK, 1 row affected, 1 warning (0.02 sec)
>>> Rows matched: 1 Changed: 1 Warnings: 0
>>>=20
>>> It shows that one record is affected and one row changed ..
>>> I did show warnings ..the output is like this ..
>>>=20
>>> mysql> show warnings;
>>>=20
>>> =
+---------+------+------------------------------------------ --------------=
-+
>>> | Level | Code | Message
>>> |
>>>=20
>>> =
+---------+------+------------------------------------------ --------------=
-+
>>> | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-08-05 =
04:15:05'
>>> |
>>>=20
>>>=20
>>> =
+---------+------+------------------------------------------ --------------=
-+
>>>=20
>>> 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)
>>>=20
>>> So my question is what happened exactly ?
>>> Why no records updated ?
>>>=20
>>> Help is highly appreciated in this regard ..
>>>=20
>>> - Umapathi
>>> umapathi.b@gmail.com
>>>=20
>>=20
>>=20


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg