| Bug #37217 | Some SHOW commands not working properly if no CLIENT_NO_SCHEMA cpbt bit | ||
|---|---|---|---|
| Submitted: | 5 Jun 2008 7:22 | Modified: | 28 Nov 2008 20:08 |
| Reporter: | Geert Vanderkelen | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
| Version: | 5.1.24,5.1bzr | OS: | Any |
| Assigned to: | Geert Vanderkelen | CPU Architecture: | Any |
[6 Jun 2008 7:39]
Geert Vanderkelen
Use Wireshark and MySQL 5.1.24, I'm making sure I'm sending the correct thing. CLI gets back list of engines, connpy gets a 'No database selected' error. ## MySQL CLI 0000 02 00 00 00 45 08 00 45 ed cc 40 00 40 06 00 00 ....E..E ..@.@... 0010 7f 00 00 01 7f 00 00 01 c1 6e 0c ea 1f eb ab ad ........ .n...... 0020 3c d5 eb 8a 80 18 ff ff fe 39 00 00 01 01 08 0a <....... .9...... 0030 1e e7 d1 1a 1e e7 d0 ce 0d 00 00 00 03 53 48 4f ........ .....SHO 0040 57 20 45 4e 47 49 4e 45 53 W ENGINE S ## Connpy 0000 02 00 00 00 45 00 00 45 79 10 40 00 40 06 00 00 ....E..E y.@.@... 0010 7f 00 00 01 7f 00 00 01 c1 78 0c ea 86 a2 0e 16 ........ .x...... 0020 09 6e 11 bb 80 18 ff ff fe 39 00 00 01 01 08 0a .n...... .9...... 0030 1e e7 d9 2c 1e e7 d9 2c 0d 00 00 00 03 53 48 4f ...,..., .....SHO 0040 57 20 45 4e 47 49 4e 45 53 W ENGINE S
[9 Jun 2008 15:29]
Geert Vanderkelen
K, so, Connector/J still works OK where my connector fails.. I guess there is something I'm missing about the change between versions there..
[8 Jul 2008 13:46]
Geert Vanderkelen
Since 5.1.24 a Clients needs to unset the CLIENT_NO_SCHEMA client flag. If set, it will fail on SHOW ENGINES for example. No Connector is yet affect, except when you make your own of course :) Verified using Latest source from Bazaar (5.1.28) (on Linux). I think it's related to a patch pushed in bug #27944, but unsure.
[8 Jul 2008 14:14]
Davi Arnaut
By unsetting you mean that the connector takes all server capabilities and put then on the client flags?
[8 Jul 2008 15:54]
Geert Vanderkelen
What I'm doing right now:
1) Get the server capabilities from the handshake packet.
2) Take these capabilities and clear the bits we don't one. In code:
_default_client_flags = ~(ClientFlag.COMPRESS + ClientFlag.SSL
+ ClientFlag.ODBC + ClientFlag.IGNORE_SIGPIPE + ClientFlag.IGNORE_SPACE
+ ClientFlag.FOUND_ROWS
+ ClientFlag.NO_SCHEMA
+ ClientFlag.RESERVED)
client_flags = self.capabilities & _default_client_flags
3) send the authentication packet
What happens is following when sending a SHOW ENGINES:
* When we don't clear the CLIENT_NO_SCHEMA bit (1 << 4) I get following:
SERVER FLAGS 63487
CAPABILITIES FLAGS 63487
CLIENT FLAGS 42653
1046 (3D000): No database selected
* When I clear the CLIENT_NO_SCHEMA bit, it works:
SERVER FLAGS 63487
CAPABILITIES FLAGS 63487
CLIENT FLAGS 42637
[u'MyISAM', u'DEFAULT', u'Default engine as of MySQL 3.23 with great performance', u'NO', u'NO', u'NO']
[u'MRG_MYISAM', u'YES', u'Collection of identical MyISAM tables', u'NO', u'NO', u'NO']
...
The thing is that this just changed between two versions, that's what bothering me the most. I guess I just forgot clearing the bit, but how should I know?
[8 Jul 2008 16:09]
Davi Arnaut
> 2) Take these capabilities and clear the bits we don't one. In code: You shouldn't be doing this, the server capabilities is a advertisement of what the sever supports and this doesn't mean that you should buy it just because it's advertised. You should always start with a zeroed client flags and only set the bits that you want and are supported by the server. > The thing is that this just changed between two versions, that's what bothering me the most. > I guess I just forgot clearing the bit, but how should I know? http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol I guess the wording there is a bit poor but it's very important to note that the server capabilities is just a advertisement of what the server supports. If we introduced a new flag and advertised it and the connector didn't unset it.. could lead to terrible problems.
[8 Jul 2008 16:30]
Geert Vanderkelen
>> 2) Take these capabilities and clear the bits we don't one. In code: > You shouldn't be doing this, the server capabilities is a advertisement of what the sever > supports and this doesn't mean that you should buy it just because it's advertised. You > should always start with a zeroed client flags and only set the bits that you want and are > supported by the server. Yup, that's how I did it opening the bug report. Zero'ed and adding what I needed. Doing like I do now was just a humble attempt to fix 'something' :) Reverting again already. > http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol Yeah, I try to update it as much as I can > I guess the wording there is a bit poor but it's very important to note that the server > capabilities is just a advertisement of what the server supports. If we introduced a new > flag and advertised it and the connector didn't unset it.. could lead to terrible > problems. That old, patched doc can be made better. But the issue still remains. What changed in the server code that this flag breaks stuff so easily between 5.1.23 to 5.1.24? If this is something we have to document, we document. I don't think we need to fix anything in code.. except the 'capabilites' typo in the source :)
[8 Jul 2008 19:34]
Davi Arnaut
The change is that with the path for Bug#27944 the server started advertising all the capabilities supported and this can cause problem for clients that wrongly passes then back to the server by only switching off some bits. I agree that this should have been documented better. As for the variable name and comments typos, I'll fix then.
[8 Jul 2008 20:15]
Geert Vanderkelen
> The change is that with the path for Bug#27944 the server started advertising all the > capabilities supported and this can cause problem for clients that wrongly passes then > back to the server by only switching off some bits. I agree that this should have been > documented better. Well, as said before, I was not using anything passed back from the server when I opened the bug report. Setting CLIENT_NO_SCHEMA 'on' will result in very bizar error messages. Whatever CLIENT_NO_SCHEMA did before, is now broken, since 5.1.24. I still don't fully understand what that flag is doing anyway. This is not a problem of the client, but the server not using a flag correctly IMHO. BTW, this is only SHOW commands doing stuff with the INFORMATION_SCHEMA, so that's definitely something to look after. The more I look at it, the more I think Bug#27944 has nothing to do with it :)
[8 Jul 2008 23:55]
Davi Arnaut
OK, I thought you were complaining about something that changed between the recent 5.1 versions, but as far as I can see, this behavior of CLIENT_NO_SCHEMA also affects 5.0. But first, what are you using CLIENT_NO_SCHEMA for? Its seems to be a ODBC thing...
[9 Jul 2008 0:09]
Davi Arnaut
Could you try the following patch? It should make "show databases" work with the CLIENT_NO_SCHEMA flag set.
=== modified file 'sql/sql_show.cc'
--- sql/sql_show.cc 2008-03-19 13:32:28 +0000
+++ sql/sql_show.cc 2008-07-09 00:07:34 +0000
@@ -3995,7 +3995,7 @@ int make_schema_select(THD *thd, SELECT_
make_lex_string(thd, &table, schema_table->table_name,
strlen(schema_table->table_name), 0);
if (schema_table->old_format(thd, schema_table) || /* Handle old syntax */
- !sel->add_table_to_list(thd, new Table_ident(thd, db, table, 0),
+ !sel->add_table_to_list(thd, new Table_ident(thd, db, table, 1),
0, 0, TL_READ, (List<String> *) 0,
(List<String> *) 0))
{
[9 Jul 2008 9:30]
Geert Vanderkelen
The patch seems to work fine (using 5.1 bzr)! I'm setting the CLIENT_NO_SCHEMA flag and it doesn't fail anymore with the error (btw, you should not set a default database when testing). Why I was using this flag? By mistake really :)
[10 Jul 2008 21:44]
Davi Arnaut
I would like to point out that I'm not really sure if this is a bug, because to me it seems that CLIENT_NO_SCHEMA was intended to work this way and it will not only break show commands, it will break anything that uses a fully qualified database name different from the current one (like select * from mysql.proc). My patch is also not a definitive fix (as it fixes only just one case). I would suggest we get rid of CLIENT_NO_SCHEMA all together unless there is a strong use case for it.
[13 Jul 2008 14:57]
Geert Vanderkelen
I agree. And it's also nothing we should break our heads on to much now. The problem is now it's somehow documented. I just hope nothing else changed there under the hood. CLIENT_NO_SCHEMA didn't break show commands before 5.1.24. I've put it D4/W5 now.
Description: Using my Python client/server protocol implementation I was surprised to find that SHOW commands working perfectly using 5.1.23 are breaking when sending them to 5.1.24. For example, using the CMD_QUERY: When I do SHOW DATABASES, having no current database: CMD_QUERY: 0f 00 00 00 03 53 48 4f 57 20 44 41 54 41 42 41 53 45 53 mysql.connector.errors.InterfaceError: 1046 (3D000): No database selected General query log says: 12 Query SHOW DATABASES When doing SHOW ENGINES, current database is `test`: mysql.connector.errors.InterfaceError: 1146 (42S02): Table 'test.engines' doesn't exist CMD_QUERY: 0d 00 00 00 03 53 48 4f 57 20 45 4e 47 49 4e 45 53 3 Query SHOW ENGINES A SHOW PROCESSLIST works still ok using the CMD_QUERY. Could it be that SHOW commands using INFORMATION_SCHEMA affected by this? How to repeat: Using the mysql CLI, this all works fine. I'm using my own client/server implementation in Python. The MySQLdb using libmyqlclient is also working. I'm doing a simple: stmt_select = "SHOW ENGINES" cursor.execute(stmt_select) rows = cursor.fetchall() This gives 1146 (42S02): Table 'test.engines' doesn't exist Works fine with mysqld 5.1.22 and 5.1.23 just fine. Fails with 5.1.24 and 5.1.25. Using MySQL 5.0.62 it works well too. Suggested fix: Well, somehow I hope I'm doing something wrong here..