maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Peter!

On Oct 22, Peter Laursen wrote:
> I tried to comment on this Blog
> https://blog.mariadb.org/mariadb-10-1-1-compound-statements/, but it won't
> let me use my (corporate) gmail address for authencticaton. So I send
> this mail instead:

strange. I'll check it out.

> I tried the example provided on Windows (both server and client are Win7 64
> bit) using the command line client shipped with MariaDB 10.1:
> 
> Enter password: ********
> Welcome to the MariaDB monitor.  Commands end with ; or \g.
> Your MariaDB connection id is 5
> Server version: 10.1.1-MariaDB mariadb.org binary distribution
> 
> Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
> 
> Type 'help;' or '\h' for help. Type '\c' to clear the current input
> statement.
> 
> MariaDB [(none)]> use test;
> Database changed
> MariaDB [test]> IF @have_csv = 'YES' THEN
>     ->   CREATE TABLE IF NOT EXISTS general_log (
>     ->     event_time TIMESTAMP(6) NOT NULL,
>     ->     user_host MEDIUMTEXT NOT NULL,
>     ->     thread_id BIGINT(21) UNSIGNED NOT NULL,
>     ->     server_id INTEGER UNSIGNED NOT NULL,
>     ->     command_type VARCHAR(64) NOT NULL,
>     ->     argument MEDIUMTEXT NOT NULL
>     ->   ) engine=CSV CHARACTER SET utf8 comment="General log";
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
> that
> corresponds to your MariaDB server version for the right syntax to use near
> '' a
> t line 9
> MariaDB [test]> END IF;
> 
> Obviously the SEMICOLON after comment="General log" disturbs.  But this is
> no better:
... 
> What is the problem here? Is it someWindows-specific bug/issue? BTW it is
> the same in SQLyog (compiled with MariaDB C-API)  as in commandline.

Right. It's exactly the same issue as with CREATE PROCEDURE and friends.
If you need to enter something complex with semicolons inside, you need
to change the delimiter. Here's an example from the manual:
https://mariadb.com/kb/en/using-compound-statements-outside-of-stored-programs/

  MariaDB [test]> delimiter |
  MariaDB [test]> if @have_innodb then
    CREATE TABLE IF NOT EXISTS innodb_index_stats (
      database_name    VARCHAR(64) NOT NULL,
      table_name       VARCHAR(64) NOT NULL,
      index_name       VARCHAR(64) NOT NULL,
      last_update      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      stat_name        VARCHAR(64) NOT NULL,
      stat_value       BIGINT UNSIGNED NOT NULL,
      sample_size      BIGINT UNSIGNED,
      stat_description VARCHAR(1024) NOT NULL,
      PRIMARY KEY (database_name, table_name, index_name, stat_name)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
  end if|

Also note (the manual explains it too) that you could get a warning "Unknown
storage engine", despite the IF. The statement may be not executed, but
it will be parsed anyway, and the parser can warn about unknown engine.

Regards,
Sergei