Jump to content

SystemUpdater SQL Error


CaelanStewart
 Share

Recommended Posts

Hi,

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

post-3143-0-76522800-1458569065_thumb.pn

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:

post-3143-0-84395500-1458573202_thumb.pn

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.

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...