sqlmysqldatabasemysqlcommandmysql-command-line-client

What does `\g` mean at the end of a MySQL statement? And how do I fix queries not running?


I have an auto-generated SQL script to run on 5.6.17-157.2.

It worked fine on 5.5.33-1.17.1.

Every SQL statement shows \g at the end. For example

CREATE TABLE articoli
(
ID                                                                INT,
titolo                                                            LONGTEXT,
sottotitolo                                                       LONGTEXT,
descrizione                                                       LONGTEXT,
note                                                              LONGTEXT,
nomeopzione1                                                      LONGTEXT,
nomeopzione2                                                      LONGTEXT,
nomeopzione3                                                      LONGTEXT,
pagina                                                            CHAR(100),
sottopagina                                                       SMALLINT,
plain_titolo                                                      CHAR(200),
plain_sottotitolo                                                 CHAR(200),
nomeopzione4                                                      LONGTEXT,
KEY (ID),
KEY (pagina),
KEY (sottopagina)
);\g

What changed between the two version to break query execution? How can I tell 5.6 to accept \g and don't care?

I can't just change the SQL. It's auto-generated code that must run as final step of a monstrous software abomination "daily update" (https://serverfault.com/questions/458340/euro-character-messed-up-during-ftp-transfer)

[Update] Better change the question: it's not enough to know what is that. I need to get the queries running.


Solution

  • MySQL already does accept \g, but it must follow a SQL statement.

    The \g is basically the same as ; That is, it is the terminator for a statement and that means send it to the server for parsing and execution.

    Your sample shows a create table statement terminated by both a semicolon and \g. This results in the create table statement running, because it has a semicolon. But then it tries to run another statement terminator without a statement.

    Try this:

    mysql> ;
    ERROR: 
    No query specified
    

    Of course there was no query specified, this just shows a semicolon with no query.

    It's the same with a line with nothing but \g:

    mysql> \g
    ERROR: 
    No query specified
    

    And if you run a real query, and then a redundant terminator of either type, you get something similar. It runs the first query, then fails on the empty query:

    mysql> select 123; ;
    +-----+
    | 123 |
    +-----+
    | 123 |
    +-----+
    
    ERROR: 
    No query specified
    
    mysql> select 123; \g
    +-----+
    | 123 |
    +-----+
    | 123 |
    +-----+
    
    ERROR: 
    No query specified
    

    I don't know what you mean about this code is generated and you can't change it. You'll have to, because what you've got won't work.

    I would suggest you strip out the \g from your file before trying to run it. Here's an example of a file containing the bad empty-query pattern, and using sed to remove the redundant \g:

    $ cat > bad.sql
    select 123; \g
    
    $ sed -e 's/\\g//g' bad.sql
    select 123;