SystemUpdater SQL Error

[CaelanStewart]

Hi,

Upon upgrading a site from 2.6.1 to 2.7.2 (latest stable), Update #12 fails with an SQL error:

I narrowed it down to this query (SystemUpdate12.php):

ALTER TABLE pages ADD published datetime DEFAULT NULL AFTER `created_users_id`

The strange thing comes now: the error only seems to arise when PHP executes the query with the PW's DB class. I executed the queries in SystemUpdate12.php manually in phpMyAdmin and, and force update 12 to return true in the 'update' method of the SystemUpdater class and the other updates ALL worked seamlessly, only that one was an issue. PW seems to be functioning fine, too.

EDIT: Turns out I was a silly sod and didn't paste the queries in the right place in phpMyAdmin, and I had though they'd executed when they actually hadn't. So when I pasted the above query in the correct place in phpMyAdmin, I got the same error as I did in PW:

I tried repairing the table, before I tried the above and that had no effect.

The installation has no other plugins (other than AllInOne Minify) or modifications to the 'pages' table.

Bit of a weird issue, but I got it working pretty quickly.

[CaelanStewart]

Small update: I had pasted the queries into the wrong areas in phpMyAdmin, so I had thought it had executed successfully none of them had actually been executed. I have a PHP error when I publish a page now.

[BitPoet]

The message looks like the "created" column has a wrong default value, so the create statement for the published column probably triggered the error but isn't its cause. Open the schema for the pages table in phpMyAdmin and have a look there. The default value for "created" should be a date in 'YYYY-MM-DD HH:MM:SS' notation, if it's not, enter something halfway reasonable there (it shouldn't really matter which date though as PW will overwrite it), then try to execute the statements from SystemUpdate12.php again.

[CaelanStewart]

The message looks like the "created" column has a wrong default value, so the create statement for the published column probably triggered the error but isn't its cause. Open the schema for the pages table in phpMyAdmin and have a look there. The default value for "created" should be a date in 'YYYY-MM-DD HH:MM:SS' notation, if it's not, enter something halfway reasonable there (it shouldn't really matter which date though as PW will overwrite it), then try to execute the statements from SystemUpdate12.php again.

You were close, the default PW installation set the 'created' column value (not the default value) to 0000-00-00 for the home, admin, page, add and other default PW pages that come with the PW install. I changed all of those to some other date before the modified date for each of those rows, and the update ran perfectly.

The version first used for this PW site is 2.5, so maybe MySQL removed support for zero-dates, and that's why it cropped up now. But I haven't touched those columns/rows and only AIOM plugin has been installed.