Open shlomi-noach opened 4 years ago
I think this is the same issue we're seeing with a PHP Laravel test. The following CREATE TABLE
(output of SHOW CREATE TABLE
from vanilla MySQL, but same result from the actual CREATE TABLE
) fails:
CREATE TABLE `users` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`email_verified_at` timestamp NULL DEFAULT NULL,
`password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
The error is:
ERROR 1067 (42000): vtgate: http://6fec745a2fd3:33574/: target: test.0.master, used tablet: test-1 (): vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'email_verified_at' (errno 1067) (sqlstate 42000) (CallerID: userData1): Sql: "create table users (\n\tid bigint(20) unsigned not null auto_increment,\n\t`name` varchar(255) collate utf8mb4_unicode_ci not null,\n\temail varchar(255) collate utf8mb4_unicode_ci not null,\n\temail_verified_at timestamp default null,\n\t`password` varchar(255) collate u
When filing a bug, please include the following headings if possible. Any example text in this template can be deleted.
Overview of the Issue
A
CREATE TABLE
statement, which is accepted by MySQL as valid, is not accepted by Vitess. The problem is with aTIMESTAMP
column -- which not the 1st in the table (1stTIMESTAMP
column is special because it defaultsCURRENT_TIMESTAMP
if no directive is given).For 2nd
TIMESTAMP
column, if no DEFAULT directive or DEFAULT value are given, the implicit behavior is that the colunm isNULL
-able and defaultsNULL
. That's how MySQL interprets it. But vitess errors out.Reproduction Steps
schema
:(actual table extracted from
orchestrator
)Vitess errors with:
Note
Invalid default value for 'end_timestamp' (errno 1067) (sqlstate 42000)
The above table definition is equivalent to the following:
Notice
end_timestamp timestamp NULL DEFAULT NULL
The latter is accepted by vitess. Vitess should support the former variation as well.
Operating system and Environment details
Any.