2

The story

I've recently setup replication on my MySQL server. I went down the route of Master - Master replication (2 servers, each is Master and Slave to the other, as described here - http://brendanschwartz.com/post/12702901390/mysql-master-master-replication).

I have started to notice some queries are failing or displaying unexpected behaviour using PhpMyAdmin on WAMP. For example, suddenly:

-- This fails
SELECT * FROM `tableName` WHERE `columnName` = 10;
-- This succeeds
SELECT * FROM tableName WHERE columnName = 10;
-- In some queries, specific columns seem to be the issue
-- So even this might work, but not always
SELECT * FROM `tableName` WHERE columnName = 10;
-- Sometimes it's even due to specific columns
-- So this might succeed
SELECT * FROM `tableName` WHERE `anotherColumnName` = 10;

I soon realised it was probably PhpMyAdmin's fault as doing it via the MySQL shell worked. Then I realised it also worked on PhpMyAdmin on my own machine which is Linux based, and the PhpMyAdmin version is much newer.

However, I do run into issues with backticks when trying to run some queries from regular shell, for example:

# From my bash shell, this fails
mysql -s -uUser -pPassword -e "SELECT `columnName` FROM `dbName`.`tableName`;"
# This succeeds
mysql -s -uUser -pPassword -e "SELECT columnName FROM dbName.tableName;"

I do not believe in coincidences, so I figured I better check what's going on.

What I have so far

I have searched for it online and saw there might be an issue with backticks and replication because of the way it gets saved in the log (I think). But it still doesn't quite answer how replication is still working for me, but some queries are failing with backticks on. If anything, my data supports there are no issues with backticks.

I know what backticks are for - https://dba.stackexchange.com/questions/23129/benefits-of-using-backtick-in-mysql-queries

My MySQL servers are running:

  • Ubuntu 14.04.01 (64 bit Server Edition)
  • MySQL Ver 14.14 Distrib 5.5.41, for debian-linux-gnu (x86_64) using readline 6.3

My questions are:

  1. Why exactly are backticks bad for replication? Am I likely to run into issues with backticks or has my experience been due to esoteric, platform-specific issues?
  2. Is there a recommended way of dealing with this issue?
  3. My thinking is to get rid of all the backticks in the code. My column names are all in plain English alphabet and camelCase so I shouldn't need to use backticks anyway.
    1. I thought maybe I could switch to sql_mode = ANSI_QUOTES but I'm worried such a big change would require thorough testing of the entire system because I'm not sure how it would affect other aspects of the queries such as the values, etc.
    2. I also thought the issue could be the backticks have become part of the name but it doesn't fit with the behaviour, and I checked.

I would appreciate any input anyone has with regards to this issue.

Community
  • 1
  • 1
SimpleAnecdote
  • 765
  • 9
  • 17

1 Answers1

0

First of all, my suggestion is to keep backticks and to use them. Without using them, you might risk your application breaking after an update of MySQL. Just have a look at newly introduced reserved words for MySQL 5.6 (or any earlier version) - they are case insensitive, so camel case won't help you here (maxValue or reSignal). If you use any of them as table or column names, your application will break. Just don't risk that.

Now, to you questions:

# From my bash shell, this fails
mysql -s -uUser -pPassword -e "SELECT `columnName` FROM `dbName`.`tableName`;"

Did you had a look at the error message, by chance? Backticks in the shell (bash) actually are used for code execution. So echo "host is `hostname`" will execute the command hostname and replace that placeholder with the (terminal) output of that command. So the string gets changed to host is computer0815 and this will be fed to echo.

As you likely do not have commandd called columnName, dbName or tableName, the shell possibly replaced them by an empty string (and issued errors) so the query seen by MySQL read as SELECT FROM .; which clearly is invalid and hence failed.

Hint: Try again using single quotes ' instead of double quotes " to avoid shell expansion.

  1. Backticks are not bad for replication. I use them everywhere and I never encountered an error due to them - not even in replication.
  2. How do the queries fail? Do they fail in phpMyAdmin and you get an error? Do they fail in an application? Do you get an error? Carefully analyse the error message - it will get you closer to what is wrong.
  3. ANSI quotes are not related to backticks, but instead to double quotes ". So a WHERE `login` = "name" will check for the column login to be the string name (no ANSI quotes) or for the column login to have the same value as column name (ANSI quotes). If you quote your strings using single quotes ' as in WHERE `login` = 'name' enabled or disabled ANSI quotes won't make any difference.

In order to get any more (detailed) help, please explain in detail how the queries fail or why you believe the queries fail.

Shi
  • 4,178
  • 1
  • 26
  • 31