Upgrade from 1.14 to 2.0 stops with SQL message 'lmr.logTime' in 'field list'

[shorepower]

Please see attached , the upgrade (using full upgrade package ) stops on step

Executing upgrade script 1 of 3.
Executing: "Converting activity logs..." (2 of 8 - of this script)

with error  Unknown column 'lmr.logTime' in 'field list'

Thx a lot in advance for help resolving this error


Log attached below:

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!

Creating new tables and inserting default data... Successful!
Creating "themes"... Successful!
Creating "collapsed_categories"... Successful!
Creating and verifying "permissions"... Successful!
Inserting data into "permissions"... Successful!
Creating and verifying "board_permissions"... Successful!
Inserting data into "board_permissions"... Successful!
Creating "moderators"... Successful!
Creating "attachments"... Successful!
Creating "log_notify"... Successful!
Creating "log_polls"... Successful!
Creating "log_actions"... Successful!
Creating "poll_choices"... Successful!
Creating "smileys"... Successful!
Loading default smileys... Successful!
Dropping "log_search" and recreating it... Successful!
Dropping "sessions" and recreating it... Successful!
Verifying "settings"... Successful!
Converting activity logs... Successful!
Converting "log_online"... Successful!
Converting "log_floodcontrol"... Successful!
Converting "log_karma"... Successful!
Retiring "log_clicks"... Successful!
Converting "log_notify"... Successful!
Converting "log_errors"... Successful!
Converting "log_boards"... Unsuccessful!
This query:

    SELECT lmr.ID_BOARD, lmr.ID_MEMBER, lmr.logTime
    FROM smf_log_mark_read AS lmr
    LEFT JOIN smf_log_boards AS lb ON (lb.ID_BOARD = lmr.ID_BOARD AND lb.ID_MEMBER = lmr.ID_MEMBER)
    WHERE lb.logTime < lmr.logTime;

Caused the error:

    Unknown column 'lmr.logTime' in 'field list'

[shorepower]

Some additional information and recreation of the sequence I have followed.

Have tried to start the migration of the current forum (background here http://www.simplemachines.org/community/index.php?topic=437721.0) to a new platform but there are several errors which I'm trying to resolve.

I started by making a full backup of the DB (via SMF) and moved the complete 1.12 SMF library over to the UNIX environment.

Then I import the database through PHPMyadmin which gives me the  following SQL error

Error
SQL query:
CREATE TABLE `smf_sessions` (
session_idchar( 32 ) NOT NULL default '',
last_update int( 10 ) unsigned NOT NULL default '',
data text NOT NULL default '',
PRIMARY KEY ( session_id )
) ENGINE = MYISAM ;
MySQL said: 
#1067 - Invalid default value for 'last_update'

I have followed the suggestion (this thread http://www.simplemachines.org/community/index.php?topic=254655.0) and manually executed the following  SQL statement instead which is accepted and a table is created.

CREATE TABLE smf_sessions (
  session_id char(32) NOT NULL,
  last_update int(10) unsigned NOT NULL,
  data text NOT NULL,
  PRIMARY KEY (session_id)
) TYPE=MyISAM;


Using repair_settings.php I updated the access information for the new DB.  Checking settings.php everything is registered correctly but when SMF is launched I get a connection error from MySQL

Connection Problems
Sorry, SMF was unable to connect to the database. This may be caused by the server being busy. Please try again later.


Running  test.php (as suggested in this thread http://www.simplemachines.org/community/index.php?topic=55080.0)  the following error is returned

Access denied for user 'xxx'@'yyyy.se' (using password: NO)


I have triple validated that this is the correct information (note: mysql database is running on another server).

For the sake of it I also tried to upgrade but directly get the following error

Table 'xxx_yy.smf_settings' doesn't exist

Instead I completely scratch the installation and reinstalls a full 1.14 version. Now the access to the DB works (using the same credentials entered via repair_settings.php earlier) . The following message is  displayed.

Forum Refreshed
While installing, the installer found that (with the details you provided) one or more of the tables this installer might create already existed.
Any missing tables in your installation have been recreated with the default data, but no data was deleted from existing tables.

The Forum launches just fine under 1.14 but when I start the upgrade to 2.0 I get the SQL error described in my first post in this thread

Any help appreciated.

[Norv]

A quick note about this:

Access denied for user 'xxx'@'yyyy.se' (using password: NO)

I have triple validated that this is the correct information (note: mysql database is running on another server).

The database user may have permissions to the first database only, and not to the new one.

About:

Executing upgrade script 1 of 3.
Executing: "Converting activity logs..." (2 of 8 - of this script)

and

Converting "log_boards"... Unsuccessful!
This query:

    SELECT lmr.ID_BOARD, lmr.ID_MEMBER, lmr.logTime
    FROM smf_log_mark_read AS lmr
    LEFT JOIN smf_log_boards AS lb ON (lb.ID_BOARD = lmr.ID_BOARD AND lb.ID_MEMBER = lmr.ID_MEMBER)
    WHERE lb.logTime < lmr.logTime;

Caused the error:

    Unknown column 'lmr.logTime' in 'field list'[/i]

I understand from the OP that you upgrade from 1.1.14. Yet, the upgrade is executing the script from 1.0 as well, it's assuming you would have 1.0... Can you please tell the result of the following query on your 1.1.14 database:

Code Select Expand


SELECT value from smf_settings where variable='smfVersion';


[shorepower]

A quick note about this:

Access denied for user 'xxx'@'yyyy.se' (using password: NO)

I have triple validated that this is the correct information (note: mysql database is running on another server).

The database user may have permissions to the first database only, and not to the new one.

Actually i have created a new DB and new users and these are the credentials used in both cases. Interesting enough  now when restored the installation (the new on 1.14) to recreate the the situation before I started the upgrade I could not use the copied 1.14 installation and I once again got a DB connection error. So I had to reinstall 1.14 and then the connection works. Seems like something is "binding" the machine that runs the SMF code and the backend MYSQL DB Server (MYSQL is running on a separate server so I do not use localhost) and that this "binding" is created at installation time.

About:

Executing upgrade script 1 of 3.
Executing: "Converting activity logs..." (2 of 8 - of this script)

and

Converting "log_boards"... Unsuccessful!
This query:

    SELECT lmr.ID_BOARD, lmr.ID_MEMBER, lmr.logTime
    FROM smf_log_mark_read AS lmr
    LEFT JOIN smf_log_boards AS lb ON (lb.ID_BOARD = lmr.ID_BOARD AND lb.ID_MEMBER = lmr.ID_MEMBER)
    WHERE lb.logTime < lmr.logTime;

Caused the error:

    Unknown column 'lmr.logTime' in 'field list'[/i]

I understand from the OP that you upgrade from 1.1.14. Yet, the upgrade is executing the script from 1.0 as well, it's assuming you would have 1.0... Can you please tell the result of the following query on your 1.1.14 database:

Code Select Expand


SELECT value from smf_settings where variable='smfVersion';


I get the following result

MySQL returned an empty result set (i.e. zero rows).

[Norv]

Please try running this, in phpmyadmin: (on the 1.1.14 installation that you wish to execute the upgrade script on)

Code Select Expand


INSERT INTO smf_settings (variable, value) VALUES ('smfVersion', '1.1.14');


[shorepower]

Please try running this, in phpmyadmin: (on the 1.1.14 installation that you wish to execute the upgrade script on)

Code Select Expand


INSERT INTO smf_settings (variable, value) VALUES ('smfVersion', '1.1.14');


Thx, it worked now.

Seems to be several underlying problems with the old installation in Windows in combination with way the ISP have set up the UNIX environment which I will have to dig into, but right now I at least have a full exact copy up and running.

So thanks a lot for the excellent support.