Strange Behaviour Querying Mysql.user

[larsen]

Hi,

when I execute the following statements (both at the same time), I get a strange result (see below)

Code:

SELECT * FROM USER WHERE USER = '456';
SELECT * FROM USER WHERE USER = '456';

Code:

(0 row(s) returned)
Execution Time : 00:00:00:046
Transfer Time : 00:00:00:000
Total Time : 00:00:00:046

Query : SELECT * FROM USER WHERE USER = '456'
Error Code : 1146
Table 'mysql.USER' doesn't exist
Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000

Using MySQL 5.0.32 and SQLyog 8.3.

I don´t know if this is a MySQL or SQLyog problem. Can anyone reproduce this behaviour?

Lars

[larsen]

The problem seems to be caused by copying the first statement and inserting it. SQLyog automatically changes the “user” to “USER” when I enter it (first statement). Then it is copied as “USER”.

I can fix the problem with the second statement by simply overwriting “USER” with “user” (what becomes “USER” automatically again).

[peterlaursen]

It is not reproducible for me with servers 5.0.90 and 5.1.45 0n Windows and 5.1.32 on Linux.

MESSAGES tab output on Windows (with default 'lower_case_table_names' setting):

Quote:

(0 row(s) returned)

Execution Time : 00:00:00:000

Transfer Time  : 00:00:00:000

Total Time     : 00:00:00:000

(0 row(s) returned)

Execution Time : 00:00:00:000

Transfer Time  : 00:00:00:000

Total Time     : 00:00:00:000

On Linux: 

Quote:

Query : SELECT * FROM USER WHERE USER = '456'

Error Code : 1146

Table 'mysql.USER' doesn't exist

Execution Time : 00:00:00:000

Transfer Time  : 00:00:00:000

Total Time     : 00:00:00:000

Query : SELECT * FROM USER WHERE USER = '456' LIMIT

Error Code : 1146

Table 'mysql.USER' doesn't exist

Execution Time : 00:00:00:000

Transfer Time  : 00:00:00:000

Total Time     : 00:00:00:000

(obviously as table names are case sensitive on Linux as default, but not on Windows)

1) Please tell: on what platform is the server running?  

2) Could you try a more recent server?  5.0.32 is very old and rather buggy. 

3) You may `backquote`the table name `user`. SQLyog will not capitalize inside (back)quoted strings. 

4) Also you may turn off the 'auto capitalization' feature from 'preferences' 

Also see attached screenshot from autocomplete CTRL+SPACE popup.  It tells that 'user' is 1) a keyword 2) a function 3) a table name and 4) a column name.  And in your settings you have selected to capitalize functions automatically.

[attachment=1353:user.jpg]

[peterlaursen]

“The problem seems to be caused by copying the first statement and inserting it”

Also this I tried. I get consistently the same response for both statements

[larsen]

1) Linux

2) I only have a 5.0.51a server as this is the newest version you get from Debian Lenny.

3) It does work then.

4) works, too

5) Sorrry, no powertools with community version here =)

I still see the problem in SQLyog: When I type in “user”, SQLyog recognizes this as a function instead of a table name and capitalizes it. Internally though, it passes it as “user” to the server. Now, when I copy it, SQLyog inserts it as “USER” and also passes it as such to the server, where this of course is not an existing table.

[peterlaursen]

This “Internally though, it passes it as “user” to the server. Now, when I copy it, SQLyog inserts it as “USER” and also passes it as such to the server, where this of course is not an existing table.” is what I cannot reproduce. As told I get consistently same response for both queries.  But let of testers try fiddle with this.

[larsen]

I can live with it, now that I know the behaviour, and I don´t regularly use the mysql.user table either. What eventually means that I will forget the behaviour till next time I have to use it 😉

If I can help with testing, please let me know.

[peterlaursen]

Please let us consider this again after the weekend.  We have scheduled other tasks for the rest of this week (and I do not consider it urgent).

[larsen]

No, certainly not urgent. Do as you like =)

And thanks for the quick support! I am repeatedly astonished about the response times 😉

[Author]

Posts