I'm trying to use the System Versioned Tables feature of MariaDB 10.3 in Laravel. However, I'm getting the following exception whenever I try to run the artisan migration that alters the table.
C:\htdocs\Computers>php artisan migrate:fresh
Dropped all tables successfully.
Migration table created successfully.
PDOException : SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'row_start'
at C:\htdocs\Computers\database\migrations\2018_07_14_191112_add_system_version_to_computers.php:16
12| * @return void
13| */
14| public function up()
15| {
> 16| DB::connection()->getPdo()->exec("ALTER TABLE `COMPUTERS` ADD SYSTEM VERSIONING;");
17| }
18|
19| /**
20| * Reverse the migrations.
Exception trace:
1 PDO::exec("ALTER TABLE `COMPUTERS` ADD SYSTEM VERSIONING;")
C:\htdocs\Computers\database\migrations\2018_07_14_191112_add_system_version_to_computers.php:16
2 AddSystemVersionToComputers::up()
C:\htdocs\Computers\vendor\laravel\framework\src\Illuminate\Database\Migrations\Migrator.php:359
Please use the argument -v to see more details.
I've tried this on an empty table as well as one that already has data
I've tried suppressing the PDO exceptions with the following but it acts as if it works but the system versioning is not added when I check the table
DB::connection()->getPdo()->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);I am able to add system versioning to the table if I execute the same SQL in HeidiSQL
I've searched through the Laravel documentation and code for any methods that would allow me to add this table option when created but couldn't find anything.
I could manually work around this but I really want to include this in the Laravel migration files so it will be included in the Git repository.
Result of running command
SHOW CREATE TABLE `computers`;
...
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| computers | CREATE TABLE `computers` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `computers_name_unique` (`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
Below are the results of running the same SQL statement from the MariaDB command line. Notice the addition of "WITH SYSTEM VERSIONING" in the table definition.
MariaDB [computers]> ALTER TABLE COMPUTERS ADD SYSTEM VERSIONING;
Query OK, 0 rows affected (0.025 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [computers]> SHOW CREATE TABLE `computers`;
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| computers | CREATE TABLE `computers` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `computers_name_unique` (`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci WITH SYSTEM VERSIONING |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)
Ran the same alter table command in Artisan Tinker and got same error:
>>> DB::connection()->getPdo()->exec("ALTER TABLE `COMPUTERS` ADD SYSTEM VERSIONING;");
PDOException with message 'SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'row_start''
Still get the same error if I try to grant all for computers-lcl user.
DROP USER IF EXISTS `computers-lcl`@`localhost`;
FLUSH PRIVILEGES;
GRANT ALL ON *.* TO `computers-lcl`@`localhost` IDENTIFIED BY 'qej8lOjoc81Ekub1d26uzerOv332Qa' WITH GRANT OPTION;
Thanks in advance for any guidance given.
