CODEWITHSUNDEEP
mysqlweb.py
Milo Gertjejansen
Milo Gertjejansen

Reputation: 511

I can't select query tables in mysql, but only with certain names

I have a couple tables in a database named 19efdf22eeefaa6dc322be094bb2da475d4d457c and 8e0b5a906b375bc5f6c44ca4274d7374ea43435a.

When I run the code

SELECT * 
FROM table_name
ORDER BY name ASC

Where table_name is one of the above tables, the query fails on the second one but not the first one and I am not sure why.

This seems to happen randomly and I found that if I surround the table names with "`" (the grave mark), then it works just fine, but I am using web.py for my web framework and there is some useful wrappers for easy queries involving insert and select.

I am generating these string using a Python hash function but they are all pretty much the same (not the same, per say, but all similar).

Upvotes: 0

Views: 2335

Answers (2)

olegsv
olegsv

Reputation: 1462

in MySQL, tables with names starting with a digit 0-9 can only be accessed with backticks.

I suggest a simple fix - prepend table names with "_" .

Upvotes: 1

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44363

I am not sure either but you should put backquotes around every identifier like this:

SELECT * 
FROM `table_name`
ORDER BY `name` ASC

MySQL understands the backquote around database name, table name, and column name in this way. Here is an old URL how what you did is indeed recommended : http://mail.python.org/pipermail/db-sig/2007-August/005172.html

You could just insert before and after \x60 (the backquote) to the table name. Forgive me, I am not conversant in Python.

UPDATE 2013-05-20 22:08 EDT

Here is my conjecture: The first two characters, if taken to be a hexadecimal value, is less than 128 and I think the character set for information_schema.tables may be interfering with the table name's interpretation. You will have to experiment with table names where the first two characters are less or equal to 7F. The only way to know if this is case is to add Python code that retries the SHA1 hash again if the first two characters (in terms of hexadecimal value) is greater than 7F.

Give it a Try !!!

Upvotes: 3

Related Questions