explicit DEFAULTs on columns
Posted by: Joseph Michaud
Date: November 09, 2021 02:13PM

The export of the table in our product's DB should normally look like this:


DROP TABLE IF EXISTS `notification`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `notification` (
`notification_id` int NOT NULL AUTO_INCREMENT,
`notification_userid` int NOT NULL,
`notification_createdon` datetime NOT NULL,
`notification_reviewid` int NOT NULL,
`notification_severity` int unsigned NOT NULL,
`notification_url` varchar(255) NOT NULL,
`notification_title` varchar(255) NOT NULL,
`notification_textplain` text NOT NULL,
`notification_texthtml` text NOT NULL,
`notification_groupid` int NOT NULL,
`notification_creatorid` int NOT NULL,
PRIMARY KEY (`notification_id`),
KEY `idx_notification_userId` (`notification_userid`),
KEY `idx_notification_reviewId` (`notification_reviewid`),
KEY `idx_notification_groupId` (`notification_groupid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;


I have a customer who provides the following export for the structure of that table in the installation at their site:


DROP TABLE IF EXISTS `notification`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `notification` (
`notification_id` int NOT NULL AUTO_INCREMENT,
`notification_userid` int NOT NULL DEFAULT '0',
`notification_createdon` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`notification_reviewid` int NOT NULL DEFAULT '0',
`notification_severity` int unsigned NOT NULL DEFAULT '0',
`notification_url` varchar(255) NOT NULL DEFAULT '',
`notification_title` varchar(255) NOT NULL,
`notification_textplain` mediumtext NOT NULL,
`notification_texthtml` mediumtext NOT NULL,
`notification_creatorid` int NOT NULL DEFAULT '0',
`notification_groupid` int NOT NULL DEFAULT '0',
`notification_calendar` char(1) NOT NULL DEFAULT 'N',
`notification_cclist` varchar(255) NOT NULL,
PRIMARY KEY (`notification_id`),
KEY `idx_notification_userId` (`notification_userid`),
KEY `idx_notification_reviewId` (`notification_reviewid`),
KEY `idx_notification_groupId` (`notification_groupid`)
) ENGINE=InnoDB AUTO_INCREMENT=22816030 DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;



(The first definition is from my installation of an earlier schema version. The second definition is from the customer's installation of a later schema version.)

In our maintenance of the schema for this table, we have never added explicit DEFAULTs of zero to the columns like this. All of the other tables in the export are similarly changed. They are currently using 8.0.25 but the DB was likely originally created with 5.X.

I'm trying to figure out how this could happen. Is there some automated way to add a DEFAULT constraint to the column definitions? Could this be done during an export/import of the DB? Any clues would be welcome.

Options: ReplyQuote


Subject
Written By
Posted
explicit DEFAULTs on columns
November 09, 2021 02:13PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.