MySQL Bugs: #34629: CHANGE MASTER TO doesn't permit quoted values

Bug #34629	CHANGE MASTER TO doesn't permit quoted values
Submitted:	17 Feb 2008 20:35	Modified:	18 Feb 2008 12:36
Reporter:	Baron Schwartz (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
CHANGE MASTER TO doesn't allow me to quote the port number or master log position.  This is important because DBD::mysql quotes these values when I use a prepared statement.  (There's a workaround, but it's a hassle).

How to repeat:
mysql> change master to master_host='127.0.0.1', master_port='12345';
ERROR 1064 (42000): 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 ''12345'' at line 1

And:

mysql> change master to master_host='127.0.0.1', master_port=12345, master_log_file='mysql-bin.000001', master_log_pos='98';
ERROR 1064 (42000): 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 ''98'' at line 1

But the following is not a syntax error when I remove the quotes:

mysql> change master to master_host='127.0.0.1', master_port=12345, master_log_file='mysql-bin.000001', master_log_pos=98;
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first

START SLAVE UNTIL also doesn't like a quoted position.

Isn't the real problem with DBD::mysql, then?

I'd say it's a pain point with DBI as a whole, which makes it a little awkward to specify the type of parameters.  But that's the Perl-ish idiom.  Values should be interpreted as numbers when needed, and as strings when needed.

MySQL tries to do this too: I even think I've seen something in the official documentation (or maybe one of the books you wrote?) that says one of MySQL's goals is to try to do what people expect.  In this case I think MySQL should interpret the quoted values as numbers, which it does in most other contexts.

Current behaviour is clearly documented in http://dev.mysql.com/doc/refman/5.0/en/change-master-to.html. So, formally, this is "not a bug"... 

But I do agree that this is inconsistent with many other cases when strings representing numbers are accepted.

I don't see where the current documentation says only integer literals are accepted?

Related: issue #1337

related:

http://bugs.mysql.com/bug.php?id=65390
(Change master logging should be copy/pastable... (but quotes numeric parameters))