Bug #76508

Comparison of NOT NULL fields without a default value does not work in "Compare Database" tool if a field is not autoincrement

Added by M. Stichweh about 3 years ago. Updated 11 months ago.

Status:
Closed
Priority:
Must have
Assignee:
-
Category:
Install Tool
Target version:
-
Start date:
2016-06-07
Due date:
% Done:

100%

Estimated time:
1.00 h
TYPO3 Version:
7
PHP Version:
5.6
Tags:
Complexity:
easy
Is Regression:
No
Sprint Focus:

Description

In some cases you would like to have a field which should never be NULL, eg. a referencing ID to parent object. But if a database field is defined as NOT NULL field without a default value the comparison does not work because the tool expects NULL as default value. A default of '0' would work but allows inserting of rows without the specific field explicit set.

For example the SQL definition looks like that:

CREATE TABLE tx_foo (
    uid int(11) unsigned NOT NULL auto_increment,
    pid int(11) unsigned DEFAULT '0' NOT NULL,
    tstamp int(11) unsigned DEFAULT '0' NOT NULL,
    crdate int(11) unsigned DEFAULT '0' NOT NULL,

    someid int(11) unsigned NOT NULL,

    PRIMARY KEY (uid),
    KEY parent (pid),
    KEY someid (someid)
) ENGINE=InnoDB;

The install tool will always show a difference for the "someid" field and expects:

someid int(11) unsigned DEFAULT NULL NOT NULL,

But this would result in invalid SQL when creating the table.
So the SqlSchemaMigrationService should be modified to prevent this edge case:

LINE 517 in SqlSchemaMigrationService.php (assembleFieldDefinition method)

    // Add a default value if the field is not auto-incremented (these fields never have a default definition)
    // but not if the field can not be NULL and default value is NULL (this results in invalid SQL)
    if (!stristr($row['Extra'], 'auto_increment') && !($row['Null'] == 'NO' && $row['Default'] === NULL)) {


Related issues

Related to TYPO3 Core - Bug #76641: Database compare and "NULL default NULL" is not recognized Closed 2016-06-15
Related to TYPO3 Core - Feature #77643: Reimplement SqlSchemaMigrationService using SchemaManager Closed 2016-08-25

Associated revisions

Revision 2ba08444 (diff)
Added by Morton Jonuschat almost 3 years ago

[FEATURE] Doctrine: Implement SchemaMigrationService

Implement a SQL schema migration service based on an actual
parser for CREATE TABLE statements that are mapped to Doctrine
Table objects. This enables the use of the Doctrine DBAL
SchemaManager for all schema modifications.

The new Schema migration service is fully aware of multiple
database connections and normalizes MySQL specific data types
to standard compliant types. This mostly affects the TINYINT
data type which gets converted to a SMALLINT.

Resolves: #77643
Resolves: #77369
Resolves: #76508
Resolves: #76641
Resolves: #75205
Resolves: #71645
Resolves: #44991
Releases: master
Change-Id: Ic56941c2ae9717836d89bce74261d11424da340b
Reviewed-on: https://review.typo3.org/49593
Tested-by: Bamboo TYPO3com <>
Reviewed-by: Christian Kuhn <>
Tested-by: Christian Kuhn <>
Reviewed-by: Georg Ringer <>
Tested-by: Georg Ringer <>
Reviewed-by: Wouter Wolters <>
Tested-by: Wouter Wolters <>

History

#1 Updated by Morton Jonuschat about 3 years ago

  • Status changed from New to Needs Feedback
someid int(11) unsigned DEFAULT NULL NOT NULL,
is the definition reported by MySQL for that field (the MySQL server adds the implicit default value), so if you add the full definition of the column to your ext_tables.sql file everything should be ok.

#2 Updated by M. Stichweh about 3 years ago

Morton Jonuschat wrote:

[...] is the definition reported by MySQL for that field (the MySQL server adds the implicit default value), so if you add the full definition of the column to your ext_tables.sql file everything should be ok.

This is correct, if I define the "someid" field in this way in my ext_tables.sql, the compare tool works properly. But when the table is created the first time I get a MySQL (MariaDB) error:

#1067 - Invalid default value for 'someid'

Because of that I cannot define the field like that and the compare tool will always show a difference. This is a problem when updating Typo3 for example since the database update step of the migration wizard will never succeed.

#3 Updated by Gerrit Code Review almost 3 years ago

  • Status changed from Needs Feedback to Under Review

Patch set 12 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/49593

#4 Updated by Gerrit Code Review almost 3 years ago

Patch set 13 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/49593

#5 Updated by Gerrit Code Review almost 3 years ago

Patch set 14 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/49593

#6 Updated by Gerrit Code Review almost 3 years ago

Patch set 15 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/49593

#7 Updated by Morton Jonuschat almost 3 years ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100

#8 Updated by Benni Mack 11 months ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF