About the error that occurs when the SavePoint statement is used in Mysql _mysql

Source: Internet
Author: User
Tags mysql version reserved savepoint

A few days ago to help colleagues solve a case, in the master-slave replication environment, from the library on the MySQL version number is 5.5.5, encountered the following error:

#其他非相关信息我都隐藏掉了
 [(yejr@imysql.com)]> show slave status \g;
 Slave_io_running:yes
 slave_sql_running:no
 last_errno:1064
 last_error:error ' You have a Error in your SQL Syntax Check the manual that corresponds to your MySQL server version for the right syntax to use near ' 6e86db84_14847168f19__800 0 ' at line 1 ' query. Default database: ' act '. Query: ' savepoint 6e86db84_14847168f19__8000 '
 last_io_errno:0
 last_io_error:
 last_sql_errno:1064
 Last_sql_error:error ' You have a Error in your SQL syntax check the manual this corresponds to your MySQL server V Ersion for the "right" syntax to use near ' 6e86db84_14847168f19__8000 ' in line 1 ' in query. Default database: ' Act_log '. Query: ' SavePoint 6e86db84_14847168f19__8000 '

The first feeling is to encounter reserved keywords, but see such a long string, should not be reserved keyword.
After trying, the last discovery is that the character "E" in the string could be an error if it exists, and it should seem to be a bug.
In the MySQL bug system did find the bug, but look at the bug description, in the 5.5 version should have been repaired before, it seems too unreliable ah ~ ~
About this bug:savepoint identifier is occasionally considered as floating point numbers

In fact, in addition to the upgrade, the solution is also very simple, the savepoint after the identifier string with the reverse quotation mark (wave number of the lower key, English is called the Backticks key) cited on the line.
For example:

SavePoint ' 6e86db84_14847168f19__8000 ';

That's it.

This case also prompts us, when writing SQL, when it comes to the names of databases, tables, fields, identifier, and so on, it is best to use reverse quotes to ensure that they are available.
Once saw the online datasheet has a field name is check, the name in MySQL is very early in the reserved keyword, fortunately, the development of students more reliable, have been added inverted quotes.

2 Bugs about SavePoint:
SavePoint Identifier should is enclosed with backticks
SavePoint identifier is occasionally considered as floating point numbers

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.