Joomla 2.5 with MySQL 5.7 and PHP 5.6: Problem on data type DateTime ‘0000-00-00 00:00:00’

Posted on by R SOTH
Estimated read time 6 min read

The SQL statement you provided appears to be an attempt to modify the structure of the created column in the jvigl_content table by allowing it to accept NULL values. However, the syntax seems correct. It’s trying to change the data type of the created column to DATETIME and allowing NULL values.

If you’re encountering an issue, it might be due to one of the following reasons:

  1. Column Already NULLable: The column created might already accept NULL values, in which case there’s no need to modify it.
  2. Syntax Error: Although the syntax appears correct, sometimes issues arise from database-specific requirements. Check if your database system requires a slightly different syntax for altering column properties.
  3. Table Locks: If there are concurrent operations on the table, it might be locked, preventing alterations.
  4. Insufficient Privileges: Ensure that the user executing the ALTER TABLE statement has the necessary privileges to modify the table structure.
  5. Data Constraints: There might be data in the table that violates constraints or rules defined on the column, preventing the alteration.

If you’re encountering errors, the specific error message returned by your database system can provide more insights into what might be going wrong.

/*
 Navicat Premium Data Transfer

 Source Server         : 165.22.245.82
 Source Server Type    : MySQL
 Source Server Version : 80036
 Source Host           : localhost:3306
 Source Schema         : my_puc

 Target Server Type    : MySQL
 Target Server Version : 80036
 File Encoding         : 65001

 Date: 26/02/2024 09:27:26
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for jvigl_content
-- ----------------------------
DROP TABLE IF EXISTS `jvigl_content`;
CREATE TABLE `jvigl_content`  (
  `id` int(0) UNSIGNED NOT NULL,
  `asset_id` int(0) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'FK to the #__assets table.',
  `title` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',
  `alias` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '' COMMENT 'Deprecated in Joomla! 3.0',
  `title_alias` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '' COMMENT 'Deprecated in Joomla! 3.0',
  `introtext` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `fulltext` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `state` tinyint(0) NOT NULL DEFAULT 0,
  `sectionid` int(0) UNSIGNED NOT NULL DEFAULT 0,
  `mask` int(0) UNSIGNED NOT NULL DEFAULT 0,
  `catid` int(0) UNSIGNED NOT NULL DEFAULT 0,
  `created` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
  `created_by` int(0) UNSIGNED NOT NULL DEFAULT 0,
  `created_by_alias` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',
  `modified` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_by` int(0) UNSIGNED NOT NULL DEFAULT 0,
  `checked_out` int(0) UNSIGNED NOT NULL DEFAULT 0,
  `checked_out_time` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
  `publish_up` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
  `publish_down` datetime(0) NOT NULL DEFAULT '0000-00-00 00:00:00',
  `images` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `urls` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `attribs` varchar(5120) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `version` int(0) UNSIGNED NOT NULL DEFAULT 1,
  `parentid` int(0) UNSIGNED NOT NULL DEFAULT 0,
  `ordering` int(0) NOT NULL DEFAULT 0,
  `metakey` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `metadesc` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `access` int(0) UNSIGNED NOT NULL DEFAULT 0,
  `hits` int(0) UNSIGNED NOT NULL DEFAULT 0,
  `metadata` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `featured` tinyint(0) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Set if article is featured.',
  `language` char(7) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT 'The language code for the article.',
  `xreference` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT 'A reference to enable linkages to external data sets.',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_access`(`access`) USING BTREE,
  INDEX `idx_checkout`(`checked_out`) USING BTREE,
  INDEX `idx_state`(`state`) USING BTREE,
  INDEX `idx_catid`(`catid`) USING BTREE,
  INDEX `idx_createdby`(`created_by`) USING BTREE,
  INDEX `idx_featured_catid`(`featured`, `catid`) USING BTREE,
  INDEX `idx_language`(`language`) USING BTREE,
  INDEX `idx_xreference`(`xreference`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

Solution

The issue with your table creation script is with the datetime default values. In MySQL, the datetime data type doesn’t support the default value ‘0000-00-00 00:00:00’. Instead, you can set the default value to NULL, or you can use the current timestamp as the default value.

Here’s how you can modify the created, modified, checked_out_time, publish_up, and publish_down columns to have default values of NULL:

`created` datetime(0) NULL DEFAULT NULL,
`modified` datetime(0) NULL DEFAULT NULL,
`checked_out_time` datetime(0) NULL DEFAULT NULL,
`publish_up` datetime(0) NULL DEFAULT NULL,
`publish_down` datetime(0) NULL DEFAULT NULL,

Alternatively, if you want to set the default value to the current timestamp when a row is inserted or updated, you can use the DEFAULT CURRENT_TIMESTAMP attribute. Here’s how you can do it:

`created` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
`modified` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`checked_out_time` datetime(0) NULL DEFAULT NULL,
`publish_up` datetime(0) NULL DEFAULT NULL,
`publish_down` datetime(0) NULL DEFAULT NULL,

Adjust the default values based on your requirements. Once you’ve made these changes, the table creation script should work fine.

Certainly! Below is the SQL ALTER statement to modify the columns in your table to set appropriate default values:

ALTER TABLE `jvigl_content`
MODIFY `created` datetime(0) NULL DEFAULT NULL,
MODIFY `modified` datetime(0) NULL DEFAULT NULL,
MODIFY `checked_out_time` datetime(0) NULL DEFAULT NULL,
MODIFY `publish_up` datetime(0) NULL DEFAULT NULL,
MODIFY `publish_down` datetime(0) NULL DEFAULT NULL;

This statement modifies the data type of the specified columns (created, modified, checked_out_time, publish_up, publish_down) to allow NULL values and sets their default values to NULL.

The error you’re encountering is due to MySQL’s strict mode regarding datetime values. The datetime value ‘0000-00-00 00:00:00’ is considered invalid in MySQL.

To address this issue, you have a few options:

  1. Set Default Value to NULL: You can modify the column definition to allow NULL values and set the default value to NULL.
ALTER TABLE `jvigl_content`
MODIFY `checked_out_time` datetime(0) NULL DEFAULT NULL;
  1. Use CURRENT_TIMESTAMP: If you want to have a default timestamp value for the column, you can use CURRENT_TIMESTAMP instead of ‘0000-00-00 00:00:00’. This will automatically insert the current timestamp when a new row is added or updated.
ALTER TABLE `jvigl_content`
MODIFY `checked_out_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP;

Choose the option that best fits your requirements. If you don’t need a default value for the checked_out_time column, allowing NULL values might be the most suitable solution.

Post Views: 349
1

Related Articles