[Maria-developers] COMPOUND STATEMENT fails on Windows.
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: 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: 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" -> END IF; 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 'END IF' at line 10 MariaDB [test]> 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. -- Peter -- Webyog
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-progra... 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
Obviously I neeed to set another delimiter. Thanks! -- Peter On Wed, Oct 22, 2014 at 12:40 PM, Sergei Golubchik <serg@mariadb.org> wrote:
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-progra...
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
participants (2)
-
Peter Laursen -
Sergei Golubchik