[BETA] Datetime column default value is not compatible with mysql 8 #1057

New Issue

Open

opened 2024-02-18 08:43:48 +00:00 by vistamedia · 1 comment

vistamedia commented

2024-02-18 08:43:48 +00:00

Member

Steps to reproduce the issue

Compile the demo component.

Expected result

The default datetime columns (created, modified and checked_out_time) to be compatibe with mysql 8

Actual result

They still use 0000-00-00 00:00:00 as default value.

System information (as much as possible)

OS Name & Version: Mac 14.2.1
MySql Version: 5.7
PHP Version: 7.4
Joomla Version: 3.10.12
JCB Version: 3.1.39
Browser: Firefox developer edition

Additional comments

I solve it adding some extra queries in the post install script.

SET SESSION sql_mode = '';
ALTER TABLE `#__mycomponent_myview` CHANGE `created` `created` DATETIME NULL; 
ALTER TABLE `#__mycomponent_myview` CHANGE `modified` `modified` DATETIME NULL; 
ALTER TABLE `#__mycomponent_myview` CHANGE `checked_out_time` `checked_out_time` DATETIME NULL;

### Steps to reproduce the issue Compile the demo component. ### Expected result The default datetime columns (created, modified and checked_out_time) to be compatibe with mysql 8 ### Actual result They still use 0000-00-00 00:00:00 as default value. ### System information (as much as possible) - OS Name & Version: Mac 14.2.1 - MySql Version: 5.7 - PHP Version: 7.4 - Joomla Version: 3.10.12 - JCB Version: 3.1.39 - Browser: Firefox developer edition ### Additional comments I solve it adding some extra queries in the post install script. ``` SET SESSION sql_mode = ''; ALTER TABLE `#__mycomponent_myview` CHANGE `created` `created` DATETIME NULL; ALTER TABLE `#__mycomponent_myview` CHANGE `modified` `modified` DATETIME NULL; ALTER TABLE `#__mycomponent_myview` CHANGE `checked_out_time` `checked_out_time` DATETIME NULL; ```

vistamedia added the

Beta

label 2024-02-18 08:43:48 +00:00

vistamedia commented

2024-02-19 06:13:20 +00:00

Author

Member

Here is an example of postflight install and update script :

$component = 'notifications';
$tables    = ['message'];
$columns   = ['created', 'modified', 'checked_out_time'];

$query = "SET SESSION sql_mode = '';";
$this->db->setQuery($query);
try
{
	$this->db->execute();
}
catch (\Exception $e)
{
	$this->app->enqueueMessage($e->getMessage(), 'error');
}
foreach ($tables as $table)
{
	foreach ($columns as $column)
	{
		$query = "ALTER TABLE `#__{$component}_{$table}` CHANGE `{$column}` `{$column}` DATETIME NULL;";
		$this->db->setQuery($query);
		try
		{
			$this->db->execute();
		}
		catch (\Exception $e)
		{
			$this->app->enqueueMessage($e->getMessage(), 'error');
		}
	}
}

Here is an example of postflight install and update script : ``` $component = 'notifications'; $tables = ['message']; $columns = ['created', 'modified', 'checked_out_time']; $query = "SET SESSION sql_mode = '';"; $this->db->setQuery($query); try { $this->db->execute(); } catch (\Exception $e) { $this->app->enqueueMessage($e->getMessage(), 'error'); } foreach ($tables as $table) { foreach ($columns as $column) { $query = "ALTER TABLE `#__{$component}_{$table}` CHANGE `{$column}` `{$column}` DATETIME NULL;"; $this->db->setQuery($query); try { $this->db->execute(); } catch (\Exception $e) { $this->app->enqueueMessage($e->getMessage(), 'error'); } } } ```