0

The MySQL USE-statement doesn't work as expected.

I'm using the mysql -e command to concatenate USE and SELECT:

I've got a MySQL-server running with

show databases;

Result: testing

Testing contains Match.

Then I execute this bash:

MYSQL_RESULT=`mysql -h $DBIP -u testuser -ptestuser $DBSCHEMA -e "USE $DBSCHEMA; $SQL"`

with $DBSCHEMA = testing and $SQL being a simple "select * from Match;"

**Problem: **

Warning: Using a password on the command line interface can be insecure.

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Match' at line 1

It does not find Match even though it is definitely a part of testing. select * from testing.Match works (with and without USE beforehand)

Is my syntax wrong?

Server version: 5.5.43-0+deb7u1 (Debian)
Community
  • 1
  • 1
user2075719
  • 111
  • 1
  • 6

2 Answers2

1

You don't need USE $DBSCHEMA; because your bash script already has $DBSCHEMA in it mysql -h $DBIP -u testuser -ptestuser $DBSCHEMA -e

The actual problem though is because match is a reserved word. Cchange your query to

SELECT * FROM match

e4c5
  • 52,766
  • 11
  • 101
  • 134
0

I think the problem is that match is also a keyword within mysql, it is used for performing fulltext serches (match ... against ...). When you use the testing.match form, then mysql can clearly decide that you are referencing the match table within testing database. Without the testing. part, mysql thinks you want to perform a fulltext search.

Solutions:

  1. Change the name of the table, so it's not a reserved word within mysql.
  2. Use the dbname.tablename format.
  3. Encapsulate the word match within backticks (`)
Shadow
  • 33,525
  • 10
  • 51
  • 64