MySQL Λανθασμένη τιμή datetime: '0000-00-00 00:00:00:00&#39,

Πρόσφατα ανέλαβα ένα παλιό έργο που δημιουργήθηκε πριν από 10 χρόνια. Χρησιμοποιεί τη MySQL 5.1.

Μεταξύ άλλων, πρέπει να αλλάξω το προεπιλεγμένο σύνολο χαρακτήρων από latin1 σε utf8.

Για παράδειγμα, έχω πίνακες όπως αυτός:

  CREATE TABLE `users` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `first_name` varchar(45) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `last_name` varchar(45) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `username` varchar(127) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
    `email` varchar(127) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
    `pass` varchar(20) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
    `active` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'Y',
    `created` datetime NOT NULL,
    `last_login` datetime DEFAULT NULL,
    `author` varchar(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT 'N',
    `locked_at` datetime DEFAULT NULL,
    `created_at` datetime DEFAULT NULL,
    `updated_at` datetime DEFAULT NULL,
    `ripple_token` varchar(36) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    `ripple_token_expires` datetime DEFAULT '2014-10-31 08:03:55',
    `authentication_token` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `index_users_on_reset_password_token` (`reset_password_token`),
    UNIQUE KEY `index_users_on_confirmation_token` (`confirmation_token`),
    UNIQUE KEY `index_users_on_unlock_token` (`unlock_token`),
    KEY `users_active` (`active`),
    KEY `users_username` (`username`),
    KEY `index_users_on_email` (`email`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1677 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

Έστησα το δικό μου Mac για να δουλέψω πάνω σε αυτό. Χωρίς να το σκεφτώ πολύ, έτρεξα το "brew install mysql" το οποίο εγκατέστησε τη MySQL 5.7. Οπότε έχω κάποιες συγκρούσεις εκδόσεων.

Κατέβασα ένα αντίγραφο αυτής της βάσης δεδομένων και την εισήγαγα.

Αν προσπαθήσω να εκτελέσω ένα ερώτημα όπως αυτό:

  ALTER TABLE users MODIFY first_name varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci    NOT NULL

Λαμβάνω αυτό το σφάλμα:

  ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 1

Σκέφτηκα ότι θα μπορούσα να το διορθώσω αυτό με:

  ALTER TABLE users MODIFY created datetime  NULL DEFAULT '1970-01-01 00:00:00';
  Query OK, 0 rows affected (0.06 sec)
  Records: 0  Duplicates: 0  Warnings: 0

αλλά παίρνω:

  ALTER TABLE users MODIFY first_name varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci    NOT NULL ;
  ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 1

Πρέπει να ενημερώσω κάθε τιμή;

Προγραμματισμός
mysql
Κάνε Φεβρουάριος 2016 в 10:23
Δεν υπάρχουν ακόμα προβολές

Δεν ήμουν σε θέση να το κάνω αυτό:

UPDATE users SET created = NULL WHERE created = '0000-00-00 00:00:00'

(σε MySQL 5.7.13).

Έπαιρνα συνεχώς το σφάλμα Λανθασμένη τιμή datetime: '0000-00-00 00:00:00'.

Περιέργως, αυτό δούλεψε: SELECT * FROM users WHERE created = '0000-00-00 00:00:00'. Δεν έχω ιδέα γιατί το πρώτο αποτυγχάνει και το δεύτερο λειτουργεί... ίσως κάποιο σφάλμα της MySQL;

Σε κάθε περίπτωση, αυτό το ερώτημα UPDATE λειτούργησε:

UPDATE users SET created = NULL WHERE CAST(created AS CHAR(20)) = '0000-00-00 00:00:00'
Σχόλια (8)

Αλλαγή της προεπιλεγμένης τιμής για μια στήλη με μια δήλωση ALTER TABLE, π.χ.

 ALTER TABLE users MODIFY created datetime  NULL DEFAULT '1970-01-02'

... δεν αλλάζει οποιεσδήποτε τιμές που είναι ήδη αποθηκευμένες. Η "προεπιλεγμένη" τιμή ισχύει για τις γραμμές που εισάγονται και για τις οποίες δεν παρέχεται τιμή για τη στήλη.

Όσον αφορά τον λόγο για τον οποίο αντιμετωπίζετε το σφάλμα, είναι πιθανό ότι η ρύθμιση sql_mode για τη συνεδρία σας περιλαμβάνει NO_ZERO_DATE.

Παραπομπή: Η ρύθμιση αυτή δεν έχει καμία σχέση με την κατάσταση που επικρατεί στο σύστημα: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date

Όταν κάνατε την "εισαγωγή", οι εντολές SQL που έκαναν την INSERT σε αυτόν τον πίνακα εκτελέστηκαν σε μια συνεδρία που επέτρεπε μηδενικές ημερομηνίες.

Για να δείτε τη ρύθμιση sql_mode:

SHOW VARIABLES LIKE 'sql_mode' ;

-ή-

SELECT @@sql_mode ;

Όσον αφορά το πώς να "διορθώσετε" το τρέχον πρόβλημα, έτσι ώστε το σφάλμα να μην εμφανίζεται όταν εκτελείτε την εντολή ALTER TABLE.

Υπάρχουν διάφορες επιλογές:

  1. αλλάξτε το sql_mode ώστε να επιτρέπει μηδενικές ημερομηνίες, αφαιρώντας τις NO_ZERO_DATE και NO_ZERO_IN_DATE. Η αλλαγή μπορεί να εφαρμοστεί στο αρχείο my.cnf, οπότε μετά από μια επανεκκίνηση του MySQL Server, η μεταβλητή sql_mode θα αρχικοποιηθεί με τη ρύθμιση στο αρχείο my.cnf.

Για μια προσωρινή αλλαγή, μπορούμε να τροποποιήσουμε τη ρύθμιση με μια μόνο συνεδρία, χωρίς να απαιτείται συνολική αλλαγή.

-- save current setting of sql_mode
SET @old_sql_mode := @@sql_mode ;

-- derive a new value by removing NO_ZERO_DATE and NO_ZERO_IN_DATE
SET @new_sql_mode := @old_sql_mode ;
SET @new_sql_mode := TRIM(BOTH ',' FROM REPLACE(CONCAT(',',@new_sql_mode,','),',NO_ZERO_DATE,'  ,','));
SET @new_sql_mode := TRIM(BOTH ',' FROM REPLACE(CONCAT(',',@new_sql_mode,','),',NO_ZERO_IN_DATE,',','));
SET @@sql_mode := @new_sql_mode ;

-- perform the operation that errors due to "zero dates"

-- when we are done with required operations, we can revert back
-- to the original sql_mode setting, from the value we saved
SET @@sql_mode := @old_sql_mode ;

  1. αλλάξτε τη στήλη created ώστε να επιτρέπει τιμές NULL, και ενημερώστε τις υπάρχουσες γραμμές ώστε να αλλάξετε τις μηδενικές ημερομηνίες σε μηδενικές τιμές

  2. ενημερώστε τις υπάρχουσες γραμμές για να αλλάξετε τις μηδενικές ημερομηνίες σε έγκυρες ημερομηνίες

Δεν χρειάζεται να εκτελέσουμε μεμονωμένες δηλώσεις για να ενημερώσουμε κάθε γραμμή. Μπορούμε να ενημερώσουμε όλες τις γραμμές με μια κίνηση (υποθέτοντας ότι πρόκειται για έναν πίνακα λογικού μεγέθους. Για έναν μεγαλύτερο πίνακα, για να αποφύγουμε την τεράστια παραγωγή rollback/undo, μπορούμε να εκτελέσουμε την πράξη σε λογικά μεγάλα κομμάτια).

Στην ερώτηση, η τιμή AUTO_INCREMENT που εμφανίζεται για τον ορισμό του πίνακα μας διαβεβαιώνει ότι ο αριθμός των γραμμών δεν είναι υπερβολικός.

Αν έχουμε ήδη αλλάξει τη στήλη created ώστε να επιτρέπει τιμές NULL, μπορούμε να κάνουμε κάτι τέτοιο:

UPDATE  `users` SET `created` = NULL WHERE `created` = '0000-00-00 00:00:00'

Ή, μπορούμε να τις ορίσουμε σε μια έγκυρη ημερομηνία, π.χ. 2 Ιανουαρίου 1970

UPDATE  `users` SET `created` = '1970-01-02' WHERE `created` = '0000-00-00 00:00:00'

(Σημειώστε ότι μια τιμή datetime των μεσάνυχτων της 1ης Ιανουαρίου 1970 ('1970-01-01 00:00:00') είναι μια "μηδενική ημερομηνία". Αυτή θα αξιολογηθεί ως '0000-00-00 00:00:00:00'

Yoan Tournade
Επεξεργασμένη απάντηση Κάνε Ιούλιος 2017 в 2:01
Σχόλια (5)
Λύση

Η πρότασή μου, αν ο πίνακας είναι άδειος ή όχι πολύ μεγάλος, είναι να εξάγετε τις εντολές δημιουργίας ως αρχείο .sql και να τις ξαναγράψετε όπως θέλετε. Επίσης, κάντε το ίδιο αν έχετε υπάρχοντα δεδομένα, δηλαδή εξάγετε τις δηλώσεις εισαγωγής (προτείνω να το κάνετε αυτό σε ξεχωριστό αρχείο όπως οι δηλώσεις δημιουργίας). Τέλος, απορρίψτε τον πίνακα και εκτελέστε πρώτα create statement και μετά inserts.

Μπορείτε να χρησιμοποιήσετε γι' αυτό είτε την εντολή mysqldump, που περιλαμβάνεται στην εγκατάσταση της MySQL είτε μπορείτε επίσης να εγκαταστήσετε το MySQL Workbench, το οποίο είναι ένα δωρεάν γραφικό εργαλείο που περιλαμβάνει και αυτή τη δυνατότητα με πολύ παραμετροποιήσιμο τρόπο χωρίς να χρειάζεται να αναζητήσετε συγκεκριμένες επιλογές εντολών.

Σχόλια (3)