Migrating & Upgrrading, Incorrect datetime value: '0000-00-00 00:00:00' & more

Started by jitspoe, April 03, 2020, 09:04:45 AM

Previous topic - Next topic

jitspoe

Been a while!  I'm taking an old 1.1 forum from an old server and simultaneously migrating and upgrading (because the old version won't even run on the new server), which has been a bit of a nightmare.  After much struggling with "The upgrader was unable to find some crucial files.", which deliberately hides what it can't find (I had to modify the source to start printing things out to track down a path problem with the themes, which had their paths stored inside the database itself, not the Settings.php), I finally got the upgrade to start happening, but now I'm hitting this snag:

Error!
Incorrect datetime value: '0000-00-00 00:00:00' for column 'lastUpdated' at row 1

Not sure where it's pulling this value from.  'lastUpdated' doesn't appear to be in any of the existing tables.  I'll have to dig into it some more later, but I figured I'd post here before I go to sleep.

Edit: Oh, and a side note -- not sure if it ever normally happens, but if you have your themes pointing to a different web server, the upgrade.php completely breaks.  Just showed un-css'd times new roman text, and the progress bars didn't work, and nothing happened.

Shambles


vbgamer45

Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

shawnb61

Are you using the latest from GitHub?

Or did you download 2.1 rc2 from this site?

Using the latest from GitHub is the way to go.  Those date issues are old.  The theme issue may have been dealt with as well. 

1.1 upgrades => 2.1 should work (even 1.0!), but you need the latest code. 
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

jitspoe

I assume this is the repo? https://github.com/SimpleMachines/SMF2.1

How do I grab the upgrade package?

Edit: I see upgrade.php in the other dir.  How do I need to structure this so it's like a normal upgrade and not overwriting old files that should be left and then upgraded, I mean.  Or should I just copy upgrade.php by itself?

Edit2: Is it safe to restart the upgrade after it's gotten stuck?

jitspoe

I set up the files the same as they were in the upgrade package, and it seemed to be going smoothly, but it still failed.  First I noticed this at the top:


Notice
: Undefined offset: 0 in
/<web path>/forums/Sources/Subs-Admin.php
on line
1130


And then I got the dreaded error:
Database Changes
Executing database changes
Please be patient - this may take some time on large forums. The time elapsed increments from the server to show progress is being made!
Executing upgrade script 3 of 3.
Executing: "Adding support for deny boards access" (9 of 68 - of this script)
Error!
Incorrect datetime value: '0000-00-00 00:00:00' for column 'lastUpdated' at row 1

shawnb61

Correct, you copy the upgrade*.* files from /other down to the forum root, then execute upgrade.php.

The undefined offset issue is known & a fix is on the way.  (https://github.com/SimpleMachines/SMF2.1/pull/6036)

The datetime issue doesn't look familiar at all.  We had some earlier, but I am not aware of any current issues...

First things first - The RC code is not yet production.  Personally, I wouldn't use it on a production site; I have my own production sites on 2.0.17.  A couple caveats noted here:
https://www.simplemachines.org/community/index.php?topic=570377.0

If you want to proceed with 2.1, a couple questions:
- Are you running the upgrade via the command line or via the browser? 
- Did you see any other errors reported during the upgrade?
- PG or Mysql?   What version?
- What version of php?

(At the moment, for multi-version upgrades, the browser is definitely more mature than CLI.)
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

jitspoe

- I'm running via the browser.
- There were errors relating to "crucial files" not being found, but that was because the theme was pointing to the wrong directory, which I resolved.  I think the undefined offset and incorrect datetime value are the only errors I have now.
- mysql  Ver 14.14 Distrib 5.6.41-84.1, for Linux (x86_64) using  6.0, Database client version: libmysql - 5.1.73
- PHP version: 7.2.7
(Using hostgator)

I can switch over to 2.0.  I just figured if I should grab the latest and (hopefully) greatest when going through this whole process. :)  If I've gone halfway through the conversion and failed, will I be able to switch back to 2.0, or do I have to wipe and re-import the database and files?

I feel like, fundamentally, the issue will still be there, though, even with the 2.0 update.  The old database has what's now considered invalid dates, and I probably need to run a query to convert all the 0000 dates to NULL.  Or maybe those aren't in an old database?  I'd have to dig through the script.  What's being put into lastUpdated?  Either it was a different type of field before or the import process I used let me import bad date values.  I think I just ran "mysql -u whatever -p < database.sql"

shawnb61

I don't think so, as the error was in the 2.1 step (3 of 3)!

2.0.17 is the latest production version!

There are a lot of columns renamed, etc, over the years, and that is usually done in the form of add, move data, delete.  My theory is you have an extra (unused) column somewhere, as that column isn't in 2.1.  Or 2.0, either...  It's in 1.x. 

If you could somehow send me the structure of your DB, that would help!
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

jitspoe

Ok, I've tracked it down (somehow missed this last time I looked):  smf_boards has the lastUpdated column:

# Name Type Collation Attributes Null Default Comments Extra Action
1 id_boardPrimaryIndex smallint(5) UNSIGNED No None AUTO_INCREMENT Change Change Drop Drop

    More More

2 id_catIndex tinyint(4) UNSIGNED No 0 Change Change Drop Drop

    More More

3 child_level tinyint(4) UNSIGNED No 0 Change Change Drop Drop

    More More

4 id_parentIndex smallint(5) UNSIGNED No 0 Change Change Drop Drop

    More More

5 board_order smallint(5) No 0 Change Change Drop Drop

    More More

6 id_last_msg int(10) UNSIGNED No 0 Change Change Drop Drop

    More More

7 id_msg_updatedIndex int(10) UNSIGNED No 0 Change Change Drop Drop

    More More

8 member_groupsIndex varchar(255) utf8_unicode_ci No -1,0 Change Change Drop Drop

    More More

9 id_profile smallint(5) UNSIGNED No 1 Change Change Drop Drop

    More More

10 name varchar(255) utf8_unicode_ci No Change Change Drop Drop

    More More

11 description text utf8_unicode_ci No None Change Change Drop Drop

    More More

12 num_topics mediumint(8) UNSIGNED No 0 Change Change Drop Drop

    More More

13 num_posts mediumint(8) UNSIGNED No 0 Change Change Drop Drop

    More More

14 count_posts tinyint(4) No 0 Change Change Drop Drop

    More More

15 id_theme tinyint(4) UNSIGNED No 0 Change Change Drop Drop

    More More

16 override_theme tinyint(4) UNSIGNED No 0 Change Change Drop Drop

    More More

17 tempBoardname mediumtext utf8_unicode_ci No None Change Change Drop Drop

    More More

18 lastUpdated datetime No None Change Change Drop Drop

    More More

19 redirect varchar(255) utf8_unicode_ci No Change Change Drop Drop

    More More

20 unapproved_posts smallint(5) No 0 Change Change Drop Drop

    More More

21 unapproved_topics smallint(5) No 0 Change Change Drop Drop

    More More


What's interesting is that it's a datetime, but somehow it imported the bad values.  Here's hoping setting them to something else will fix it...

Database Changes
Executing database changes
Please be patient - this may take some time on large forums. The time elapsed increments from the server to show progress is being made!
Executing: "Adding support for alerts" (13 of 68)
Error!
Incorrect date value: '1991-04-00' for column 'birthdate' at row 515


Oh, for crying out loud!

Please use the edit button, rather than make consecutive posts, if you adding information when less than one hour has elapsed.  Posts merged - Iris.

jitspoe

Ok, manually fixed all the dates and I have the forum functional.  Got a couple critical errors:


Guest
Today at 03:30:36 AM
Backtrace information
Type of error: Critical
Cannot access attachments upload path! (/var/www/html/forums/attachments) https://digitalpaint.org/forums/index.php?scheduled=task;ts=0

Guest
Today at 02:48:52 AM
172.74.102.23 Backtrace information
Type of error: Critical
JSON decode error: Syntax error, malformed JSON step=6&substep=0&data=eyJjdXJzdGVwIjo2LCJsYW5nIjoiZW5nbGlzaCIsInJpZCI6MjkwLCJwYXNzIjoxMzgxNSwiZGVidWciOjAsImpzIjoxfQ==&ssi=1
/<new web path>/Sources/Load.php (Line 69)


Guess there's another setting hiding somewhere -- thought I replaced all the /var/www's.  (side note: I hate the fact that I have to set the absolute path on this instead of having it relative to the directory I put it in -- lots of configurations have to be changed when moving).

Not sure about the JSON thing.

jitspoe

I can't find the setting for the attachments directory anywhere...  where is that hiding?  I've grepped all the php files for var/www and I've been looking through the db for a while.

Edit: Found it in the admin settings on the forum itself, but got an error when I tried to edit it:
Oops, there seems to be a problem.
/<new path>/forums/attachments: Unable to move. A directory already exists at that path.


I must be blind.  I can't seem to find the database or config file that has these settings...

shawnb61

Are you upgrading to 2.0 or 2.1?

Attachment folder settings may be found under:
   Admin | Forum | Attachments and Avatars | Attachment Settings

This value is stored in the settings table, with the variable value of attachmentUploadDir.

If it is a single directory, this is a simple value.  If you have multiple directories, in 2.0 this is a serialized string for an array of directory names, where in 2.1, this is JSON encoded array of dir names.

Hope this helps,


Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

jitspoe

Oops, thought I updated this (I think I tried, but the edit option disappeared?).  phpMyAdmin had the database split into multiple pages, and I didn't notice the next arrows.  The smf_settings table was on the next page.

So, just in case anybody else happens to be both migrating and upgrading forums like this and happens upon this thread from searching around, I think these were the problems I had to address:

path in Settings.php
theme_dir for each theme in smf_themes
attachmentsUploadDir in smf_settings
On my old server, things were in /var/www/forums, but on hostgator, they're something like /home<num>/<username>/www/<damain name>/forums

On the database side, the dump converted some of the search index values to integers, like "0" became 0, and "00" became 00, which caused errors when importing, so I just deleted the search indexing data.

Some of the dates were bad, causing errors when converting, so I had to manually fix dates in lastUpdated (smf_boards) and birthdate (smf_members) fields.

That all gets things up and running.  Now to fix this undefined index error, which seems to still be spamming the logs even after the upgrade.

jitspoe

So it seems the undefined index I'm getting now is related to the smiley conversion:
Type of error: Undefined
8: Undefined index: <url>/Subs.php (Line 3281)



        // Replace away!
        $message = preg_replace_callback($smileyPregSearch, function($matches) use ($smileyPregReplacements)
                {
                        return $smileyPregReplacements[$matches[1]];
                }, $message);


Also, I just noticed another problem.  Some special characters did not get converted properly.  Not sure where in the process this failed.  Original database had "utf8_general_ci" for the Collation, and the new one has "utf8_unicode_ci".  Now things look like "Kategorie für Beiträge auf Deutsch." instead of "Kategorie für Beiträge auf Deutsch."

jitspoe

Ok -- new problem.  Old attachments aren't working.  At one point in time, attachments were just stored as their filename.  At some point, this was switched over to storing some sort of hash as the filename.  Attachments with no hash set fail to download.  Attachments with hashes work.  I imagine the old version checked if the file_hash was set, and if not, used the filename instead.  Is 2.1 supposed to do this, or is this something the upgrade script should have converted?

Also, should I be posting each of these issues in a separate thread?  Lots of stuff going on here so it's easier for me to keep dumping stuff in this thread, but I can mark this as solved and open new ones if that's better for you.

shawnb61

jitspoe -

You are clearly running into several layers of issues here.  I strongly recommend you start over.

Some observations:
- I can't tell if the attachment issue is a file restore issue or something to do with the upgrade.  When we do see this, it is normally tied to problems with the file system restore. 
- There is nothing in the upgrade process that will change the collation to utf8-unicode-ci.  I believe that is a problem with the DB restore. 
- The corrupted characters are associated with double-encoding.  This is usually a problem with the DB restore. 

You are changing hosts, php versions, SMF versions all at the same time, on a newly restored system that appears to have been untested.  It is almost impossible to determine which problem is caused by which.

I strongly suggest the next steps of action be:
- Start over.  Empty both the file system & the DB.
- Set PHP to 5.4.  I can run 1.1 on Hostgator with no issues with php 5.4. 
- If 5.4 isn't in your php selector, call Hostgator & have them temporarily add it. 
- Restore your 1.1 DB and files to your new host.  Start fresh.
- Confirm your DB collations are proper & match the way they were on your old host. 
- Run repair_settings.php to get your settings proper for the new host. 
- TEST AND VERIFY YOU HAVE A FULLY WORKING 1.1 SMF ENVIRONMENT BEFORE UPGRADING...  Look at all the problem areas you have mentioned: avatars, attachments, smileys, text. 

ONLY AFTER you have a fully working 1.1 environment:
- Change your php to somewhere from 7.1 - 7.3.
- Upgrade to 2.0.17.

You can try 2.1, but bear in mind it is not yet in production, and you will have to run repeated upgrades (losing mods, themes, customizations) to get it current when new versions are released. 
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

jitspoe

So the utf8 characters getting corrupted issue was definitely a database import issue.  Adding "--default-character-set=utf8" fixed that, in case anybody wondered.  Not sure why that has to be specified when the fields were specified as utf8, but whatever.

mysql -u username -p --default-character-set=utf8 < thingtoimport.sql

jitspoe

Going to mark this as solved.  Went through the process again with the 2.0 forums and didn't have the issue, and it can manually be fixed in 2.1.  I'd suggest setting up a database that has bad date value, such as "0000-00-00 00:00:00" in the <prefix>_boards lastUpdated field, to test the update script.  Might save some headaches.

Guess I'll hold off on 2.1 for now, since it seems like there's still some pretty heavy changes going in.

Edit: Aww, man, I just noticed my flaky keyboard added an extra "r" in the subject.

shawnb61

Glad you got it sorted out!

So attachments, avatars, smileys, etc., are all working fine?  No double-encoding?

lastUpdated doesn't exist on the <prefix>_boards table for either SMF 1.1, 2.0, or 2.1.  That column did exist in SMF 1.0, but gets dropped during the SMF 1.1 upgrade. 

You need to remove that column at some point - certainly before upgrading to 2.1.  2.1 enforces 'strict' mode, and as you have seen, a 0 date violates 'strict' mode.  I suspect you are seeing the remnants of an ancient issue with your forum's 1.0 => 1.1 upgrade. 

As you suggested, I just tested with 0 values in that column (it was an integer field) in a SMF 1.0 environment and was able to upgrade all the way thru to 2.1 without a problem.  The column was properly removed in the 1.1 step. 

Tony the Tiger confirms there are no issues with your spelling. 
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Advertisement: