Developing Code Coverage for MySQL tests

I have always been a strong advocate of good testing of any system. I started on a project last year with Drizzle to produce coverage tests to facilitate verifying syntax and helping in comparison with MySQL.

From my extensive experience in code generation from the past 20 years, I produced about 3 years ago when at MySQL Professional Services a Java based solution with a small meta language to automated the creation of a large number of tests. At the time is was some 475 tests and 200k lines of mysql-test syntax when I first looked at validating the Nitro Storage Engine.

A number of issues with mysql-tests including the support of multiple storage engines had me last year write a Proposed Testing Protocol for Drizzle for further discussion. While the desire for improvements still exists, we can not deny the benefit of leveraging the large amount of scope the current MySQL Test Suite , and so I have started in the creation of tests in this format.

My first output is to validate all possible variants of creating various data types in MySQL. This includes the obvious datatype, but also NULL, NOT NULL syntax, sizing with (n) and (m,n), UNSIGNED and ZEROFILL. What my tests also cover is not that the syntax is valid, but also the cases where the syntax is invalid (e.g. you can use UNSIGNED for Character fields). This POC is a small example of what can be produced.

The below first test enabled me to easily confirm valid and invalid syntax within Drizzle. This for example helped in clarifying Drizzle Data Types . I hope to be able to use the same framework to confirm MariaDB syntax and MySQL compatibility.

ct_syntax_1col.test

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
CREATE TABLE t1 (c1 tinyint NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 smallint NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 int NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bigint NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 double NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 decimal NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bit NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 date NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 time NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 datetime NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 timestamp NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 year NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 tinytext NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 text NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 mediumtext NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 longtext NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 tinyblob NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 blob NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 mediumblob NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 longblob NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 char NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 binary NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 varchar NULL);
--error 1064
CREATE TABLE t1 (c1 varbinary NULL);

CREATE TABLE t1 (c1 tinyint NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 smallint NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 int NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bigint NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 double NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 decimal NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bit NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 date NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 time NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 datetime NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 timestamp NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 year NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 tinytext NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 text NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 mediumtext NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 longtext NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 tinyblob NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 blob NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 mediumblob NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 longblob NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 char NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 binary NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 varchar NOT NULL);
--error 1064
CREATE TABLE t1 (c1 varbinary NOT NULL);

CREATE TABLE t1 (c1 tinyint(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 smallint(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 int(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bigint(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 decimal(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bit(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 char(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 varchar(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 binary(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 varbinary(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 text(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 blob(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 timestamp(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 year(1) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 double(1) NULL);
--error 1064
CREATE TABLE t1 (c1 date(1) NULL);
--error 1064
CREATE TABLE t1 (c1 time(1) NULL);
--error 1064
CREATE TABLE t1 (c1 datetime(1) NULL);
--error 1064
CREATE TABLE t1 (c1 tinytext(1) NULL);
--error 1064
CREATE TABLE t1 (c1 mediumtext(1) NULL);
--error 1064
CREATE TABLE t1 (c1 longtext(1) NULL);
--error 1064
CREATE TABLE t1 (c1 tinyblob(1) NULL);
--error 1064
CREATE TABLE t1 (c1 mediumblob(1) NULL);
--error 1064
CREATE TABLE t1 (c1 longblob(1) NULL);

CREATE TABLE t1 (c1 tinyint(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 smallint(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 int(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bigint(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 decimal(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bit(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 char(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 varchar(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 binary(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 varbinary(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 text(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 blob(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 timestamp(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 year(1) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 double(1) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 date(1) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 time(1) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 datetime(1) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 tinytext(1) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 mediumtext(1) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 longtext(1) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 tinyblob(1) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 mediumblob(1) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 longblob(1) NOT NULL);

CREATE TABLE t1 (c1 decimal(5,2) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float(5,2) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 double(5,2) NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 tinyint(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 smallint(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 int(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 bigint(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 bit(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 date(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 time(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 datetime(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 timestamp(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 year(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 char(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 varchar(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 binary(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 varbinary(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 tinytext(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 text(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 mediumtext(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 longtext(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 tinyblob(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 blob(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 mediumblob(5,2) NULL);
--error 1064
CREATE TABLE t1 (c1 longblob(5,2) NULL);

CREATE TABLE t1 (c1 decimal(5,2) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float(5,2) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 double(5,2) NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 tinyint(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 smallint(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 int(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 bigint(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 bit(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 date(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 time(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 datetime(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 timestamp(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 year(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 char(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 varchar(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 binary(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 varbinary(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 tinytext(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 text(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 mediumtext(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 longtext(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 tinyblob(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 blob(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 mediumblob(5,2) NOT NULL);
--error 1064
CREATE TABLE t1 (c1 longblob(5,2) NOT NULL);

CREATE TABLE t1 (c1 tinyint UNSIGNED NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 smallint UNSIGNED NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 int UNSIGNED NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bigint UNSIGNED NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float UNSIGNED NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 double UNSIGNED NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 decimal UNSIGNED NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 year UNSIGNED NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 bit UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 date UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 time UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 datetime UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 timestamp UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 char UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 varchar UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 binary UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 varbinary UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 tinytext UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 text UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 mediumtext UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 longtext UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 tinyblob UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 blob UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 mediumblob UNSIGNED NULL);
--error 1064
CREATE TABLE t1 (c1 longblob UNSIGNED NULL);

CREATE TABLE t1 (c1 tinyint UNSIGNED NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 smallint UNSIGNED NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 int UNSIGNED NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bigint UNSIGNED NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float UNSIGNED NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 double UNSIGNED NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 decimal UNSIGNED NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 year UNSIGNED NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 bit UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 date UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 time UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 datetime UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 timestamp UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 char UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 varchar UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 binary UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 varbinary UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 tinytext UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 text UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 mediumtext UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 longtext UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 tinyblob UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 blob UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 mediumblob UNSIGNED NOT NULL);
--error 1064
CREATE TABLE t1 (c1 longblob UNSIGNED NOT NULL);

CREATE TABLE t1 (c1 tinyint ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 smallint ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 int ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bigint ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 double ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 decimal ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 year ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 bit ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 date ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 time ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 datetime ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 timestamp ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 char ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 varchar ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 binary ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 varbinary ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 tinytext ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 text ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 mediumtext ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 longtext ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 tinyblob ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 blob ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 mediumblob ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 longblob ZEROFILL NULL);

CREATE TABLE t1 (c1 tinyint ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 smallint ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 int ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bigint ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 double ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 decimal ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 year ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 bit ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 date ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 time ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 datetime ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 timestamp ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 char ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 varchar ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 binary ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 varbinary ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 tinytext ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 text ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 mediumtext ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 longtext ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 tinyblob ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 blob ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 mediumblob ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 longblob ZEROFILL NOT NULL);

CREATE TABLE t1 (c1 tinyint UNSIGNED ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 smallint UNSIGNED ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 int UNSIGNED ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bigint UNSIGNED ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float UNSIGNED ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 double UNSIGNED ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 decimal UNSIGNED ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 year UNSIGNED ZEROFILL NOT NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 bit UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 date UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 time UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 datetime UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 timestamp UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 char UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 varchar UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 binary UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 varbinary UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 tinytext UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 text UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 mediumtext UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 longtext UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 tinyblob UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 blob UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 mediumblob UNSIGNED ZEROFILL NOT NULL);
--error 1064
CREATE TABLE t1 (c1 longblob UNSIGNED ZEROFILL NOT NULL);

CREATE TABLE t1 (c1 tinyint UNSIGNED ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 smallint UNSIGNED ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 int UNSIGNED ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 bigint UNSIGNED ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 float UNSIGNED ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 double UNSIGNED ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 decimal UNSIGNED ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 (c1 year UNSIGNED ZEROFILL NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;

#Error conditions
--error 1064
CREATE TABLE t1 (c1 bit UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 date UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 time UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 datetime UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 timestamp UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 char UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 varchar UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 binary UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 varbinary UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 tinytext UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 text UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 mediumtext UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 longtext UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 tinyblob UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 blob UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 mediumblob UNSIGNED ZEROFILL NULL);
--error 1064
CREATE TABLE t1 (c1 longblob UNSIGNED ZEROFILL NULL);
Tagged with: Databases Drizzle MySQL Open Source

Related Posts

Why Being Proactive Is Always a Winning Approach

Why Being Proactive Is Always a Winning Approach

Many companies manage production infrastructure using a reactive model rather than a proactive one. Organizations typically react to warnings and alerts, then implement corrective actions in response. While some companies have well-designed architectural patterns—such as feature flags and rate limiting—that can quickly mitigate the impact of issues, these are merely temporary solutions, not resolutions.

Read more
AWS CLI support for Aurora DSQL and S3 Tables

AWS CLI support for Aurora DSQL and S3 Tables

If you were following the AWS Re:invent keynote yesterday there were several data specific announcements including Aurora DSQL and S3 Tables . Wanting to check them out, I downloaded the latest AWS CLI 2.

Read more
Migrating off of WordPress - A Simplified Stack

Migrating off of WordPress - A Simplified Stack

The ongoing drama between Wordpress v WP Engine continues to cross my reading list, but I have permanently removed WordPress from my website. I have finally transitioned away from the complex Linux/Apache/MySQL/PHP (LAMP) stack required for self-hosting WordPress on my professional website.

Read more