Projects ⇒ Bugs ⇒ #881: MySQL 5.0 Strict Mode Install Issues ⇒ CPG Dragonfly™ CMS
Bug #881 MySQL 5.0 Strict Mode Install Issues
Project: Dragonfly 9.0.3 -> 9.3 Category: SQL
Submitted: Monday, January 08, 2007 (13:52:04) Modified: Friday, January 12, 2007 (08:27:19)
Status: Closed Assigned to: NanoCaiordo
PHP Version: 4.4.4 HTTPD Server: Apache 1.3
Votes: 1
Vote results:
Avg. Score: 5.0 ± 0.0 Reproduced: 0 of 0 (0.0%)

View/Vote Add Comment

by: BrokenCrust
I am trying to upgrade a install to 9.1.1 with a MySQL 5.0.26 database in strict mode.

I get a SQL error on the _modules table (doesn't like NULLS in the custom_title column). Works OK in not strict mode. Work around is to make column entires blank but not NULL.

I also get an error on the _users table for the user_regdate column. I also do not get this error in not strict mode.

Reproduce code:

_modules error

On /install.php
While executing query "ALTER TABLE cpg_modules CHANGE custom_title custom_title VARCHAR(255) NOT NULL DEFAULT ''"

the following error occured: Data truncated for column 'custom_title' at row 25

In: c:\cpgtest\html\includes\classes\installer.php on line: 111

_user error

On /install.php
While executing query "ALTER TABLE cpg_users CHANGE user_regdate user_regdate INT NOT NULL DEFAULT '0'"

the following error occured: Out of range value adjusted for column 'user_regdate' at row 1

In: c:\cpgtest\html\includes\classes\installer.php on line: 111

Expected result:
I expect the installer to be database error free. ;)

Actual result:
Installer errors out and stops. The _modules error can be worked around, but the _user reg_date error is a show stopper so the installer has failed.
by: NanoCaiordo
This bug has been fixed in the CVS.

Snapshots of the sources are packaged every 6 hours; this change
will be in the next snapshot. You can grab the snapshot at the
Downloads section.

Thank you for the report, and for helping us to make Dragonfly 9.0.3 -> 9.1 better.

Index: includes/classes/db_check.php =================================================================== RCS file: /cvs/html/includes/classes/db_check.php,v retrieving revision 1.9 diff -u -r1.9 db_check.php --- includes/classes/db_check.php 10 Dec 2006 03:49:35 -0000 1.9 +++ includes/classes/db_check.php 12 Jan 2007 07:30:51 -0000 @@ -59,14 +59,15 @@ (isset($row['Default']) != isset($col['Default']) || $row['Default'] != (string)$col['Default'])) { # not the same so modify - $installer->add_query('CHANGE', $table, array($field, $col['Type'], $col['Null'], $col['Default'])); if (eregi('(DATETIME|TIMESTAMP)', $row['Type'])) { - db_check::dttotime($querytable, $table, $table_ids[$table], $field); + db_check::dttotime($querytable, $table, $table_ids[$table], $field, array($field, $col['Type'], $col['Null'], $col['Default'])); } else if (!eregi('int', $row['Type'])) { if (($table == 'users' || $table == 'users_temp') && $field == 'user_regdate') { db_check::rdtotime($querytable, $table, 'user_id', $field); + $installer->add_query('CHANGE', $table, array($field, $col['Type'], $col['Null'], $col['Default'])); } } else if ($table == 'users' && $field == 'user_active_cp' && !eregi('int',$row['Type'])) { + $installer->add_query('CHANGE', $table, array($field, $col['Type'], $col['Null'], $col['Default'])); db_check::enumtoint($querytable, $table, 'user_id', $field); } } @@ -193,10 +194,12 @@ } # Function to convert DATETIME & TIMESTAMP fields in records to INT - function dttotime($querytable, $table, $id, $field) + function dttotime($querytable, $table, $id, $field, $column) { global $db, $installer; $result = $db->query("SELECT $id, UNIX_TIMESTAMP($field) FROM $querytable"); + $installer->add_query('DEL', $table, $field); + $installer->add_query('ADD', $table, $column); while ($row = $db->fetch_array($result, SQL_NUM)) { $installer->add_query('UPDATE', $table, "$field='".$db->escape_string($row[1])."' WHERE $id='$row[0]'"); }
User Info

Welcome Anonymous


Support for DragonflyCMS in a other languages: