Textpattern 4.5.7 -> 4.7.1 upgrade failed

mericson · 2018-09-28 06:32:00

User_Error “Invalid default value for ‘Posted’”
in /home/myweb/web/myweb.com/public_html/textpattern/lib/txplib_db.php at line 410.
textpattern/lib/txplib_misc.php:1715 adminErrorHandler()
updateErrorHandler()
textpattern/lib/txplib_db.php:410 trigger_error()
textpattern/lib/txplib_db.php:545 safe_query()
textpattern/update/_to_4.6.0.php:28 safe_alter()
textpattern/update/_update.php:91 include()
textpattern/index.php:214 include()
User_Error “Unknown column ‘owner’ in ‘field list’”
in /home/myweb/web/myweb.com/public_html/textpattern/lib/txplib_db.php at line 410.
textpattern/lib/txplib_misc.php:1715 adminErrorHandler()
updateErrorHandler()
textpattern/lib/txplib_db.php:410 trigger_error()
textpattern/vendors/Textpattern/L10n/Lang.php:494 safe_query()
textpattern/vendors/Textpattern/L10n/Lang.php:414 Textpattern\L10n\Lang->upsertPack()
textpattern/update/_update.php:121 Textpattern\L10n\Lang->installFile()
textpattern/index.php:214 include()

Fatal error: Uncaught Exception: update failed in /home/myweb/web/myweb.com/public_html/textpattern/lib/txplib_misc.php:1719 Stack trace: #0 [internal function]: updateErrorHandler(256, ‘Unknown column …’, ‘/home/myweb/w…’, 410, Array) #1 /home/myweb/web/myweb.com/public_html/textpattern/lib/txplib_db.php(410): trigger_error(‘Unknown column …’, 256) #2 /home/myweb/web/myweb.com/public_html/textpattern/vendors/Textpattern/L10n/Lang.php(494): safe_query(‘INSERT INTO txp…’) #3 /home/myweb/web/myweb.com/public_html/textpattern/vendors/Textpattern/L10n/Lang.php(414): Textpattern\L10n\Lang->upsertPack(Array) #4 /home/myweb/web/myweb.com/public_html/textpattern/update/_update.php(121): Textpattern\L10n\Lang->installFile(‘en-us’) #5 /home/myweb/web/myweb.com/public_html/textpattern/index.php(214): include(‘/home/myweb/w…’) #6 {main} thrown in /home/myweb/web/myweb.com/public_html/textpattern/lib/txplib_misc.php on line 1719

colak · 2018-09-28 10:22:27

May be related to this issue?

uli · 2018-09-28 12:41:48

I’ve had a quick look into the topic colak linked to and don’t want you to miss the topic that Stef/Bloke linked to in that one: There’s a content/null date issue that might be similar to yours.

Generally: Have you tried disabling your plugins? Did you try uploading the installation files a second time? If so, also try a different application, I’ve had mixed results with one of my apps.

And please post your diagnostics here.

Dear devs, is there a recipe that can be generally advised in case of unknown column errors? That type seems to happen more often now.

jakob · 2018-09-28 14:51:39

They’re both related to the fact that the update routine hasn’t performed correctly. If the update routine is interrupted or not performed for some reason – and this is what we need to track down – then using a newer version of Textpattern will inevitably produce errors because the database doesn’t match the queries being asked of it.

For example, the two errors above both occur in /update/_to_4.6.0.php, the missing owner column here and the switch from zero dates to null here but they may not be the only update routines that were skipped.

The unknown column errors can also crop up in conjunction with the most recent update if the txp_skin columns fail to be added during an update.

I’m not sure, though, how to track down why it didn’t work. I asked in another thread whether it might be possible to trigger the update routine again, skipping steps already successfully completed.

The good news is, this can be recovered from and the bad news is, it might entail manually manipulating the database to achieve that. I’ve had to do that a few times updating from very old databases.

mericson · 2018-09-29 02:59:25

Thank you all for your input.

When I saw the I too thought I might have to go in any manually patch up the database. But I also do have a database backup to recreate the problem.

Where might I find a copy of the full correct schema?

Is there a manual way to force the update process to run?

Thanks!
-Mark

Bloke · 2018-09-29 07:42:59

This is a very annoying issue. Please keep hold of the database backup. If it’s not too much to ask, it might be handy to try and recreate the issue for forensics by one of us. Though it may well be more to do with the server environment, its capabilities or alignment of the planets!

In theory you can force an update by logging out, setting the version variable to an older version number in MySQL and then logging in again to trigger the update from that version forward. But depending on how far it got before, YMMV.

etc · 2018-09-29 20:59:48

Weird, but it looks like (_to_4.6.0.php file, line 28)

safe_alter('textpattern', "MODIFY textile_body    VARCHAR(32) NOT NULL DEFAULT '1'");

tries also to reset datetime columns default values to their previous '0000-00-00 00:00:00' defaults which are invalid in MySQL 5.7. If so, we could try to modify them first in one go, before any other field:

safe_alter('textpattern',       "MODIFY Posted      DATETIME NOT NULL,
    MODIFY Expires     DATETIME     NULL DEFAULT NULL,
    MODIFY LastMod     DATETIME NOT NULL,
    MODIFY feed_time   DATE     NOT NULL"); //0000-00-00

mericson · 2018-10-01 00:13:42

Bloke wrote #314297:

This is a very annoying issue. Please keep hold of the database backup. If it’s not too much to ask, it might be handy to try and recreate the issue for forensics by one of us. Though it may well be more to do with the server environment, its capabilities or alignment of the planets!

I definitely have an archive of the database. I will now try the upgrade again, any particular logging that I can turn on in the config or database to collect more information on what is going on?

mericson · 2018-10-01 00:15:13

etc wrote #314299:

tries also to reset datetime columns default values to their previous '0000-00-00 00:00:00' defaults which are invalid in MySQL 5.7. If so, we could try to modify them first in one go, before any other field:

Can you suggest any patches or workarounds that would allow me to get my sight upgraded?

etc · 2018-10-01 09:41:52

mericson wrote #314305:

Can you suggest any patches or workarounds that would allow me to get my sight upgraded?

It would be ace if you could try to

check if version pref in txp_prefs table is still 4.5.7
replace the content of textpattern/update/_to_4.6.0.php file with

<?php

/*
 * Textpattern Content Management System
 * https://textpattern.com/
 *
 * Copyright (C) 2018 The Textpattern Development Team
 *
 * This file is part of Textpattern.
 *
 * Textpattern is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation, version 2.
 *
 * Textpattern is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with Textpattern. If not, see <https://www.gnu.org/licenses/>.
 */

if (!defined('TXP_UPDATE')) {
    exit("Nothing here. You can't access this file directly.");
}

// Remove default zero dates to make MySQL 5.7 happy.
safe_alter('textpattern',       "MODIFY Posted      DATETIME NOT NULL,
    MODIFY Expires     DATETIME     NULL DEFAULT NULL,
    MODIFY LastMod     DATETIME NOT NULL,
    MODIFY feed_time   DATE     NOT NULL"); //0000-00-00
safe_alter('txp_discuss',       "MODIFY posted      DATETIME NOT NULL");
safe_alter('txp_discuss_nonce', "MODIFY issue_time  DATETIME NOT NULL");
safe_alter('txp_file',          "MODIFY created     DATETIME NOT NULL,
    MODIFY modified    DATETIME NOT NULL");
safe_alter('txp_image',         "MODIFY date        DATETIME NOT NULL");
safe_alter('txp_link',          "MODIFY date        DATETIME NOT NULL");
safe_alter('txp_log',           "MODIFY time        DATETIME NOT NULL");
safe_alter('txp_users',         "MODIFY last_access DATETIME     NULL DEFAULT NULL");
// Remove logs and nonces with zero dates.
safe_delete('txp_discuss_nonce', "DATE(issue_time) = '0000-00-00'");
safe_delete('txp_log',           "DATE(time)       = '0000-00-00'");
// Replace zero dates (which shouldn't exist, really) with somewhat sensible values.
safe_update('textpattern', "Posted      = NOW()",   "DATE(Posted)      = '0000-00-00'");
safe_update('textpattern', "Expires     = NULL",    "DATE(Expires)     = '0000-00-00'");
safe_update('textpattern', "LastMod     = Posted",  "DATE(LastMod)     = '0000-00-00'");
safe_update('txp_discuss', "posted      = NOW()",   "DATE(posted)      = '0000-00-00'");
safe_update('txp_file',    "created     = NOW()",   "DATE(created)     = '0000-00-00'");
safe_update('txp_file',    "modified    = created", "DATE(modified)    = '0000-00-00'");
safe_update('txp_image',   "date        = NOW()",   "DATE(date)        = '0000-00-00'");
safe_update('txp_link',    "date        = NOW()",   "DATE(date)        = '0000-00-00'");
safe_update('txp_users',   "last_access = NULL",    "DATE(last_access) = '0000-00-00'");
safe_update('textpattern', "feed_time   = DATE(Posted)", "feed_time    = '0000-00-00'");

safe_alter('textpattern', "MODIFY textile_body    VARCHAR(32) NOT NULL DEFAULT '1'");
safe_alter('textpattern', "MODIFY textile_excerpt VARCHAR(32) NOT NULL DEFAULT '1'");
safe_update('txp_prefs', "name = 'pane_article_textfilter_help_visible'", "name = 'pane_article_textile_help_visible'");

// Rejig preferences panel.
$core_ev = join(',', quote_list(array('site', 'admin', 'publish', 'feeds', 'custom', 'comments')));

// 1) Increase event column size.
safe_alter('txp_prefs', "MODIFY event VARCHAR(255) NOT NULL DEFAULT 'publish'");
safe_alter('txp_prefs', "MODIFY html  VARCHAR(255) NOT NULL DEFAULT 'text_input'");

// 2) Remove basic/advanced distinction.
safe_update('txp_prefs', "type = '".PREF_CORE."'", "type = '".PREF_PLUGIN."' AND event IN ($core_ev)");

// Support for l10n string owners.
$cols = getThings("DESCRIBE `".PFX."txp_lang`");

if (!in_array('owner', $cols)) {
    safe_alter('txp_lang', "ADD owner VARCHAR(64) NOT NULL DEFAULT '' AFTER event");
    safe_create_index('txp_lang', 'owner', 'owner');
}

// Keep all comment-related forms together. The loss of 'preview' ability on the
// comments_display Form is of little consequence compared with the benefit of
// tucking them away neatly when not required.
safe_update('txp_form', "type = 'comment'", "name = 'comments_display'");

// Add protocol to logged HTTP referrers.
safe_update(
    'txp_log',
    "refer = CONCAT('http://', refer)",
    "refer != '' AND refer NOT LIKE 'http://%' AND refer NOT LIKE 'https://%'"
);

// Usernames can be 64 characters long at most.
safe_alter('txp_file',  "MODIFY author VARCHAR(64) NOT NULL DEFAULT ''");
safe_alter('txp_link',  "MODIFY author VARCHAR(64) NOT NULL DEFAULT ''");
safe_alter('txp_image', "MODIFY author VARCHAR(64) NOT NULL DEFAULT ''");

// Consistent name length limitations for presentation items.
safe_alter('txp_form',    "MODIFY name VARCHAR(255) NOT NULL DEFAULT ''");
safe_alter('txp_page',    "MODIFY name VARCHAR(255) NOT NULL DEFAULT ''");
safe_alter('txp_section', "MODIFY page VARCHAR(255) NOT NULL DEFAULT ''");
safe_alter('txp_section', "MODIFY css  VARCHAR(255) NOT NULL DEFAULT ''");

// Save sections correctly in articles.
safe_alter('textpattern', "MODIFY Section VARCHAR(255) NOT NULL DEFAULT ''");
safe_alter('txp_section', "MODIFY name    VARCHAR(255) NOT NULL DEFAULT ''");

// Plugins can have longer version numbers.
safe_alter('txp_plugin', "MODIFY version VARCHAR(255) NOT NULL DEFAULT '1.0'");

// Translation strings should allow more than 255 characters.
safe_alter('txp_lang', "MODIFY data TEXT");

// Add meta description to articles.
$cols = getThings("DESCRIBE `".PFX."textpattern`");

if (!in_array('description', $cols)) {
    safe_alter('textpattern', "ADD description VARCHAR(255) NOT NULL DEFAULT '' AFTER Keywords");
}

// Add meta description to categories.
$cols = getThings("DESCRIBE `".PFX."txp_category`");

if (!in_array('description', $cols)) {
    safe_alter('txp_category', "ADD description VARCHAR(255) NOT NULL DEFAULT '' AFTER title");
}

// Add meta description to sections.
$cols = getThings("DESCRIBE `".PFX."txp_section`");

if (!in_array('description', $cols)) {
    safe_alter('txp_section', "ADD description VARCHAR(255) NOT NULL DEFAULT '' AFTER css");
}

// Remove broken import functionality.
if (is_writable(txpath.DS.'include') && file_exists(txpath.DS.'include'.DS.'txp_import.php')) {
    $import_files = array(
        'BloggerImportTemplate.txt',
        'import_blogger.php',
        'import_mt.php',
        'import_b2.php',
        'import_mtdb.php',
        'import_wp.php'
    );

    if (is_writable(txpath.DS.'include'.DS.'import')) {
        foreach ($import_files as $file) {
            if (file_exists(txpath.DS.'include'.DS.'import'.DS.$file)) {
                unlink(txpath.DS.'include'.DS.'import'.DS.$file);
            }
        }
        rmdir(txpath.DS.'include'.DS.'import');
    }

    unlink(txpath.DS.'include'.DS.'txp_import.php');
}

// Remove unused ipban table or recreate its index (for future utf8mb4 conversion).
if (getThing("SHOW TABLES LIKE '".PFX."txp_discuss_ipban'")) {
    if (!safe_count('txp_discuss_ipban', "1 = 1")) {
        safe_drop('txp_discuss_ipban');
    } else {
        safe_drop_index('txp_discuss_ipban', "PRIMARY");
        safe_alter('txp_discuss_ipban', "ADD PRIMARY KEY (ip(250))");
    }
}

// Recreate indexes with smaller key sizes to allow future conversion to charset utf8mb4.
safe_drop_index('txp_css',     "name");
safe_drop_index('txp_file',    "filename");
safe_drop_index('txp_form',    "PRIMARY");
safe_drop_index('txp_page',    "PRIMARY");
safe_drop_index('txp_section', "PRIMARY");
safe_drop_index('txp_prefs',   "name");
safe_drop_index('textpattern', "section_status_idx");
safe_drop_index('textpattern', "url_title_idx");
// Not using safe_create_index here, because we just dropped the index.
safe_alter('txp_css',     "ADD UNIQUE name (name(250))");
safe_alter('txp_file',    "ADD UNIQUE filename (filename(250))");
safe_alter('txp_form',    "ADD PRIMARY KEY (name(250))");
safe_alter('txp_page',    "ADD PRIMARY KEY (name(250))");
safe_alter('txp_section', "ADD PRIMARY KEY (name(250))");
safe_alter('txp_prefs',   "ADD INDEX name (name(250))");
safe_alter('textpattern', "ADD INDEX section_status_idx (Section(249), Status)");
safe_alter('textpattern', "ADD INDEX url_title_idx (url_title(250))");
// Specifically, txp_discuss_nonce didn't have a primary key in 4.0.3
// so it has to be done in two separate steps.
safe_drop_index('txp_discuss_nonce', "PRIMARY");
safe_alter('txp_discuss_nonce', "ADD PRIMARY KEY (nonce(250))");

// Enforce some table changes that happened after 4.0.3 but weren't part of
// update scripts until now.
safe_alter('txp_css',  "MODIFY name  VARCHAR(255) NOT NULL");
safe_alter('txp_lang', "MODIFY lang  VARCHAR(16)  NOT NULL");
safe_alter('txp_lang', "MODIFY name  VARCHAR(64)  NOT NULL");
safe_alter('txp_lang', "MODIFY event VARCHAR(64)  NOT NULL");
safe_drop_index('txp_form', "name");
safe_drop_index('txp_page', "name");
safe_drop_index('txp_plugin', "name_2");
safe_drop_index('txp_section', "name");

// The txp_priv table was created for version 1.0, but never used nor created in
// later versions.
safe_drop('txp_priv');

// Remove empty update files.
if (is_writable(txpath.DS.'update')) {
    foreach (array('4.4.0', '4.4.1') as $v) {
        $file = txpath.DS.'update'.DS.'_to_'.$v.'.php';

        if (file_exists($file)) {
            unlink($file);
        }
    }
}

// Remove unnecessary licence files that have been moved to root.
if (is_writable(txpath)) {
    foreach (array('license', 'lgpl-2.1') as $v) {
        $file = txpath.DS.$v.'.txt';

        if (file_exists($file)) {
            unlink($file);
        }
    }
}

// Add generic token table (dropping first, because of changes to the table setup).
safe_drop('txp_token');
safe_create('txp_token', "
    id           INT          NOT NULL AUTO_INCREMENT,
    reference_id INT          NOT NULL,
    type         VARCHAR(255) NOT NULL,
    selector     VARCHAR(12)  NOT NULL DEFAULT '',
    token        VARCHAR(255) NOT NULL,
    expires      DATETIME         NULL DEFAULT NULL,

    PRIMARY KEY (id),
    UNIQUE INDEX ref_type (reference_id, type(50))
");

// Category names are max 64 characters when created/edited, so don't pretend
// they can be longer.
safe_alter('textpattern', "MODIFY Category1 VARCHAR(64) NOT NULL DEFAULT ''");
safe_alter('textpattern', "MODIFY Category2 VARCHAR(64) NOT NULL DEFAULT ''");
safe_alter('txp_file',    "MODIFY category  VARCHAR(64) NOT NULL DEFAULT ''");
safe_alter('txp_image',   "MODIFY category  VARCHAR(64) NOT NULL DEFAULT ''");

// Farewell Classic and Remora themes.
$availableThemes = \Textpattern\Admin\Theme::names();

if (!in_array(get_pref('theme_name'), $availableThemes)) {
    set_pref('theme_name', 'hive');
}

No guarantee that it works, but a negative result is still a result.

mericson · 2018-10-06 04:47:02

etc wrote #314308:

No guarantee that it works, but a negative result is still a result.

Sorry about the delay, I did try but it didn’t work

The steps I took were to 1) replace my database with the backup, 2) disable all of the plugins through changing the status in the txp_plugin table to 0, 3) replaced the code in _to_4.6.0.pdf with the code you provided, and 4) login to the admin panel.

It reported:

User_Error "Incorrect datetime value: '0000-00-00 00:00:00' for column 'Expires' at row 1"
in /home/picklit/web/pickl-it.com/public_html/textpattern/lib/txplib_db.php at line 410.

textpattern/lib/txplib_misc.php:1715 adminErrorHandler()
updateErrorHandler()
textpattern/lib/txplib_db.php:410 trigger_error()
textpattern/lib/txplib_db.php:545 safe_query()
textpattern/update/_to_4.6.0.php:29 safe_alter()
textpattern/update/_update.php:91 include()
textpattern/index.php:214 include()

User_Error "Unknown column 'owner' in 'field list'"
in /home/picklit/web/pickl-it.com/public_html/textpattern/lib/txplib_db.php at line 410.

textpattern/lib/txplib_misc.php:1715 adminErrorHandler()
updateErrorHandler()
textpattern/lib/txplib_db.php:410 trigger_error()
textpattern/vendors/Textpattern/L10n/Lang.php:494 safe_query()
textpattern/vendors/Textpattern/L10n/Lang.php:414 Textpattern\L10n\Lang->upsertPack()
textpattern/update/_update.php:121 Textpattern\L10n\Lang->installFile()
textpattern/index.php:214 include()

Fatal error: Uncaught Exception: update failed in /home/picklit/web/pickl-it.com/public_html/textpattern/lib/txplib_misc.php:1719 Stack trace: #0 [internal function]: updateErrorHandler(256, 'Unknown column ...', '/home/picklit/w...', 410, Array) #1 /home/picklit/web/pickl-it.com/public_html/textpattern/lib/txplib_db.php(410): trigger_error('Unknown column ...', 256) #2 /home/picklit/web/pickl-it.com/public_html/textpattern/vendors/Textpattern/L10n/Lang.php(494): safe_query('INSERT INTO txp...') #3 /home/picklit/web/pickl-it.com/public_html/textpattern/vendors/Textpattern/L10n/Lang.php(414): Textpattern\L10n\Lang->upsertPack(Array) #4 /home/picklit/web/pickl-it.com/public_html/textpattern/update/_update.php(121): Textpattern\L10n\Lang->installFile('en-us') #5 /home/picklit/web/pickl-it.com/public_html/textpattern/index.php(214): include('/home/picklit/w...') #6 {main} thrown in /home/picklit/web/pickl-it.com/public_html/textpattern/lib/txplib_misc.php on line 1719

p.

Bloke · 2018-10-06 08:56:50

Thanks for trying. This is somewhat baffling but it probably has to do with the fact that your MySQL is set to strict mode or something. According to this post altering the default value for a table doesn’t change any existing values in the table (as expected) but if you do that in strict mode, MySQL checks the contents before performing the alteration and in this case borks.

The solutions proposed are all logical and maybe something we could try. Since we’ve found the ‘change all values in the table’ route doesn’t work (although, interestingly, the post above the one I linked in the StackOverflow thread intimated that you could get around it by using a WHERE CAST(Expires AS CHAR(20)) = '0000-00-00 00:00:00' construct) I wonder if we could temporarily turn off strict mode prior to doing the changes then reinstate it afterwards?

Textpattern CMS

Textpattern CMS support forum

#1 2018-09-28 06:32:00