Upgrade from 2.0.14 -> 2.1 beta3 fails

Started by atxbyea, November 12, 2017, 11:02:37 AM

Previous topic - Next topic

atxbyea

Hi all!

Trying to update one of my forums from 2.0 to 2.1 for testing purposes, I have cloned my vm running the forum and copied the files across, just for the hell of it I have also done a chmod -R 777 on all files.

Now, when I run the upgrade.php I get the following error, although I cannot see an actual error in the debugging (I chose to display debugging during upgrade).

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 1 of 2.
Executing: "Cleaning up after old themes..." (33 of 41 - of this script)


Renaming table columns...done
Converting "log_online"...done
Implementing board redirects...done
Creating spider table...done
Removing a spider...done
Creating spider hit tracking table...done
Making some changes to spider hit table...done
Creating spider statistic table...done
Resetting settings_updated...done
Changing stats settings...done
Enable cache if upgrading from 1.1 and lower..done
Changing visual verification setting...done
Changing visual verification setting, again...done
Changing default personal text setting...done
Removing allow hide email setting...done
Ensuring stats index setting present...done
Ensuring forum width setting present...done
Replacing old calendar settings...done
Deleting old calendar settings...done
Adjusting calendar maximum year...done
Adding advanced signature settings...done
Updating spam protection settings...done
Adjusting timezone settings...done
Checking theme layers are correct for default themes...done
Adding index to log_notify table...done
Creating "custom_fields" table...done
Adding search ability to custom fields...done
Fixing default value field length...done
Enhancing privacy settings for custom fields...done
Checking display fields setup correctly...done
Adding new custom fields columns...done
Creating "log_digest" table...done
Adding digest option to "members" table...done
Creating "log_packages" table...done
Adding extra "log_packages" columns...done
Changing URL to SMF package server...done
Creating "mail_queue" table...done
Adding new mail queue settings...done
Change mail queue indexes...done
Adding type to mail queue...done
Creating "log_reported" table...done
Creating "log_reported_comments" table...done
Adding moderator center permissions...done
Adding moderation center preferences...done
Creating member notices table...done
Creating comments table...done
Adding user warning column...done
Ensuring warning settings are present...done
Creating "log_group_requests" table...done
Adding new membergroup table columns...done
Creating "group_moderators" table...done
Altering attachment table...done
Adding file hash...done
Populate the attachment extension...done
Updating thumbnail attachments JPG...done
Updating thumbnail attachments PNG...done
Calculating attachment mime types...done
Creating "approval_queue" table...done
Adding approved column to attachments table...done
Adding approved column to messages table...done
Adding unapproved count column to topics table...done
Adding approved column to topics table...done
Adding approved columns to boards table...done
Adding post moderation permissions...done
Adding columns to log_errors table...done
Updating error log table...done
Creating Scheduled Task Table...done
Populating Scheduled Task Table...done
Adding the simple machines scheduled task...done
Deleting old scheduled task items...done
Moving auto optimise settings to scheduled task...done
Creating Scheduled Task Log Table...done
Adding new scheduled task setting...done
Setting the birthday email template if not set...done
Creating "permission_profiles" table...done
Adding profile columns to boards table...done
Adding profile columns to board permission table...done
Cleaning up some 2.0 Beta 1 permission profile bits...done
Migrating old board profiles to profile system...done
Removing old board permissions column...done
Check the predefined profiles all have the right permissions...done
Adding inherited permissions...done
Make sure admins and moderators don't inherit...done
Deleting old permission settings...done
Removing old permission_mode column...done
Adding column to hold the boards being ignored ...done
Purge flood control ...done
Adding advanced flood control ...done
Sorting out flood control keys ...done
Adding guest voting ...done
Implementing admin feature toggles...done
Adding advanced password brute force protection to "members" table...done
Add the columns and the keys to log_actions ...done
Add the user log...done
Update the information already in log_actions...done
Creating repository table ...done
Add in the files to get from Simple Machines...done
Ensure that the table has the filetype column...done
Set the filetype for the files...done
Ensure that the files from Simple Machines get updated...done
Adding personal message rules table...done
Adding new message status columns...done
Set the new status to be correct....done
Adding personal message tracking column...done
Adding personal message tracking column...done
Adding Open ID Assocation table...done
Adding column to hold Open ID URL...done
Creating subscriptions table...done
Creating log_subscribed table...done
Clean up any pre-2.0 mod settings..done
Clean up any pre-2.0 mod settings (part 2)..done
Confirming paid subscription keys are in place ...done
Adding scheduled task...done
Adding pruning option...done
Adding restore from recycle feature...done
Changing field_options column to a larger field type...done
Changing ignore_boards column to a larger field type...done
Changing event title column to a larger field type...done
Changing holidays title column to a larger field type...done
Adding reset poll timestamp and guest voters counter...done
Fixing guest voter tallys on existing polls...done
Changing all tinytext columns to varchar(255)...done
Adding column that stores the PM receiving setting...done
Enable the buddy and ignore lists if we have not done so thus far...done
Add new security settings for attachments and avatars...done
Add other attachment settings...done
Checking for "babylon" and removing it if necessary...done
[b]!!Error!
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3[/b]


And it will just hang here indefinitivly.

Illori

mariaDB is not fully supported at this time. if you want to run it and run into any issues like the above you should report the issue on github so the developers are made aware of it.

also you should try the github version in case this or other bugs have been fixed since beta 3 was release.

shawnb61

MariaDB appears to confuse some SMF column names with reserved words.  This does not happen to all MariaDB installs, only some. 

So if you are facing this issue, you can try to fix the offending SQL and move past it, but you will likely encounter similar problems throughout the upgrade. 

In this case, it appears you have gotten past removal of the babylon theme.  This takes place in the file 'other\upgrade_2-0_mysql.sql'. 

The next step does the following, ~line 2817:

/******************************************************************************/
--- Installing new smileys sets...
/******************************************************************************/

---# Installing new smiley sets...
---{
// Don't do this twice!
if (empty($modSettings['installed_new_smiley_sets_20']))
{
// First, the entries.
upgrade_query("
UPDATE {$db_prefix}settings
SET value = CONCAT(value, ',aaron,akyhne')
WHERE variable = 'smiley_sets_known'");
// Second, the names.
upgrade_query("
UPDATE {$db_prefix}settings
SET value = CONCAT(value, '\nAaron\nAkyhne')
WHERE variable = 'smiley_sets_names'");
// This ain't running twice either.
upgrade_query("
REPLACE INTO {$db_prefix}settings
(variable, value)
VALUES
('installed_new_smiley_sets_20', '1')");
}
---}
---#


My suspicion is that MariaDB is considering variable & value to be reserved words, and that, for your installation of MariaDB, this code should be modified to put them in single quotes, e.g., 'variable' and 'value'. 

I bet you could confirm this at a SQL prompt, e.g., in phpMyAdmin - try these updates with & without the quotes.  That would confirm the issue (without rerunning the entire upgrade). 
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

shawnb61

It would be interesting to see if variable & value are in your reserved words table, in sql/lex.h. 

For reference:
https://mariadb.com/kb/en/library/reserved-words/
https://mariadb.com/kb/en/library/reserved-words/+comments/697

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

atxbyea

Quote from: shawnb61 on November 12, 2017, 12:07:38 PM
It would be interesting to see if variable & value are in your reserved words table, in sql/lex.h. 

For reference:
https://mariadb.com/kb/en/library/reserved-words/ [nofollow]
https://mariadb.com/kb/en/library/reserved-words/+comments/697 [nofollow]

It would appear to be so :

  { "VALUE",      SYM(VALUE_SYM)},

https://github.com/atcurtis/mariadb/blob/master/sql/lex.h [nofollow]


shawnb61

I would contact the MariaDB folks & ask them how that happened...

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

atxbyea

Quote from: shawnb61 on November 12, 2017, 12:01:30 PM
MariaDB appears to confuse some SMF column names with reserved words.  This does not happen to all MariaDB installs, only some. 

So if you are facing this issue, you can try to fix the offending SQL and move past it, but you will likely encounter similar problems throughout the upgrade. 

In this case, it appears you have gotten past removal of the babylon theme.  This takes place in the file 'other\upgrade_2-0_mysql.sql'. 

The next step does the following, ~line 2817:

/******************************************************************************/
--- Installing new smileys sets...
/******************************************************************************/

---# Installing new smiley sets...
---{
// Don't do this twice!
if (empty($modSettings['installed_new_smiley_sets_20']))
{
// First, the entries.
upgrade_query("
UPDATE {$db_prefix}settings
SET value = CONCAT(value, ',aaron,akyhne')
WHERE variable = 'smiley_sets_known'");
// Second, the names.
upgrade_query("
UPDATE {$db_prefix}settings
SET value = CONCAT(value, '\nAaron\nAkyhne')
WHERE variable = 'smiley_sets_names'");
// This ain't running twice either.
upgrade_query("
REPLACE INTO {$db_prefix}settings
(variable, value)
VALUES
('installed_new_smiley_sets_20', '1')");
}
---}
---#


My suspicion is that MariaDB is considering variable & value to be reserved words, and that, for your installation of MariaDB, this code should be modified to put them in single quotes, e.g., 'variable' and 'value'. 

I bet you could confirm this at a SQL prompt, e.g., in phpMyAdmin - try these updates with & without the quotes.  That would confirm the issue (without rerunning the entire upgrade).

I am not a pro with sql-statements, I did try to run it both in cli and phpmyadmin to no avail, I'm probably missing something..


MariaDB [ferus]> upgrade_query("UPDATE {$db_prefix}settings SET 'value' = CONCAT('value', ',aaron,akyhne') WHERE 'variable' = 'smiley_sets_known'");
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'upgrade_query("UPDATE {$db_prefix}settings SET 'value' = CONCAT('value', ',aaron' at line 1



atxbyea

Quote from: Illori on November 12, 2017, 11:20:13 AM
mariaDB is not fully supported at this time. if you want to run it and run into any issues like the above you should report the issue on github so the developers are made aware of it.

also you should try the github version in case this or other bugs have been fixed since beta 3 was release.

It would appear that the git version is a pure install version, without any upgrade scripts, or am I missing something ? Tried unpacking it and adding the upgrade script from the upgrade package, but no go.

shawnb61

If you're trying to experiment with the sql prompt, you will need to substitute the {$db_prefix} with your db-prefix, e.g., smf_.   Sorry for not pointing that out. 

Since you have confirmed value is a reserved word, I am convinced that's the problem.

But...  Why are most MariaDB users OK, & only some are not?  That's the real question.  Must be some mod or setting that is causing 'value' to become reserved in your environment.

So...  I think you will want your host to open a ticket with the MariaDB folks to answer that question:
How did 'value' become reserved?
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

atxbyea

Quote from: shawnb61 on November 12, 2017, 12:41:42 PM
If you're trying to experiment with the sql prompt, you will need to substitute the {$db_prefix} with your db-prefix, e.g., smf_.   Sorry for not pointing that out. 

Since you have confirmed value is a reserved word, I am convinced that's the problem.

But...  Why are most MariaDB users OK, & only some are not?  That's the real question.  Must be some mod or setting that is causing 'value' to become reserved in your environment.

So...  I think you will want your host to open a ticket with the MariaDB folks to answer that question:
How did 'value' become reserved?

I am my own host, been since 1997(long live garages and cheap power!) :-) so I would have to open that ticket myself I guess..

shawnb61

Quote from: atxbyea on November 12, 2017, 12:36:38 PM
It would appear that the git version is a pure install version, without any upgrade scripts, or am I missing something ? Tried unpacking it and adding the upgrade script from the upgrade package, but no go.

There are special instructions for using the latest git download here:
https://www.simplemachines.org/community/index.php?topic=530252.msg3816577#msg3816577

But you will have the same issue with the latest version...  'Value' is not treated as a reserved word throughout 2.0 & 2.1 code. 
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

shawnb61

Any way you can use MySQL?

Or open a ticket directly with MariaDB?
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

shawnb61

This is an issue that has been bugging the team for a while...  I was hoping you could provide a few things to us...

Could you provide: 
- the MariaDB version
- the OS and version (i.e. CentOS 7.3)
- a "SHOW VARIABLES" in the database and give us all the data (PM me with this info, not sure you want to post it)
- the /etc/my.ini file would also be great (PM me with this info, not sure you want to post it)

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

atxbyea

Quote from: shawnb61 on November 12, 2017, 12:47:22 PM
Any way you can use MySQL?

Or open a ticket directly with MariaDB?

I see no reason why not, I can just export from mariadb, delete mariadb, install mysql and then import the database again I guess..

I just removed mariadb and installed mysql community edition, imported the database, still same error..

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 1 of 2.
Executing: "Cleaning up after old themes..." (33 of 41 - of this script)


Renaming table columns...done
Converting "log_online"...done
Implementing board redirects...done
Creating spider table...done
Removing a spider...done
Creating spider hit tracking table...done
Making some changes to spider hit table...done
Creating spider statistic table...done
Resetting settings_updated...done
Changing stats settings...done
Enable cache if upgrading from 1.1 and lower..done
Changing visual verification setting...done
Changing visual verification setting, again...done
Changing default personal text setting...done
Removing allow hide email setting...done
Ensuring stats index setting present...done
Ensuring forum width setting present...done
Replacing old calendar settings...done
Deleting old calendar settings...done
Adjusting calendar maximum year...done
Adding advanced signature settings...done
Updating spam protection settings...done
Adjusting timezone settings...done
Checking theme layers are correct for default themes...done
Adding index to log_notify table...done
Creating "custom_fields" table...done
Adding search ability to custom fields...done
Fixing default value field length...done
Enhancing privacy settings for custom fields...done
Checking display fields setup correctly...done
Adding new custom fields columns...done
Creating "log_digest" table...done
Adding digest option to "members" table...done
Creating "log_packages" table...done
Adding extra "log_packages" columns...done
Changing URL to SMF package server...done
Creating "mail_queue" table...done
Adding new mail queue settings...done
Change mail queue indexes...done
Adding type to mail queue...done
Creating "log_reported" table...done
Creating "log_reported_comments" table...done
Adding moderator center permissions...done
Adding moderation center preferences...done
Creating member notices table...done
Creating comments table...done
Adding user warning column...done
Ensuring warning settings are present...done
Creating "log_group_requests" table...done
Adding new membergroup table columns...done
Creating "group_moderators" table...done
Altering attachment table...done
Adding file hash...done
Populate the attachment extension...done
Updating thumbnail attachments JPG...done
Updating thumbnail attachments PNG...done
Calculating attachment mime types...done
Creating "approval_queue" table...done
Adding approved column to attachments table...done
Adding approved column to messages table...done
Adding unapproved count column to topics table...done
Adding approved column to topics table...done
Adding approved columns to boards table...done
Adding post moderation permissions...done
Adding columns to log_errors table...done
Updating error log table...done
Creating Scheduled Task Table...done
Populating Scheduled Task Table...done
Adding the simple machines scheduled task...done
Deleting old scheduled task items...done
Moving auto optimise settings to scheduled task...done
Creating Scheduled Task Log Table...done
Adding new scheduled task setting...done
Setting the birthday email template if not set...done
Creating "permission_profiles" table...done
Adding profile columns to boards table...done
Adding profile columns to board permission table...done
Cleaning up some 2.0 Beta 1 permission profile bits...done
Migrating old board profiles to profile system...done
Removing old board permissions column...done
Check the predefined profiles all have the right permissions...done
Adding inherited permissions...done
Make sure admins and moderators don't inherit...done
Deleting old permission settings...done
Removing old permission_mode column...done
Adding column to hold the boards being ignored ...done
Purge flood control ...done
Adding advanced flood control ...done
Sorting out flood control keys ...done
Adding guest voting ...done
Implementing admin feature toggles...done
Adding advanced password brute force protection to "members" table...done
Add the columns and the keys to log_actions ...done
Add the user log...done
Update the information already in log_actions...done
Creating repository table ...done
Add in the files to get from Simple Machines...done
Ensure that the table has the filetype column...done
Set the filetype for the files...done
Ensure that the files from Simple Machines get updated...done
Adding personal message rules table...done
Adding new message status columns...done
Set the new status to be correct....done
Adding personal message tracking column...done
Adding personal message tracking column...done
Adding Open ID Assocation table...done
Adding column to hold Open ID URL...done
Creating subscriptions table...done
Creating log_subscribed table...done
Clean up any pre-2.0 mod settings..done
Clean up any pre-2.0 mod settings (part 2)..done
Confirming paid subscription keys are in place ...done
Adding scheduled task...done
Adding pruning option...done
Adding restore from recycle feature...done
Changing field_options column to a larger field type...done
Changing ignore_boards column to a larger field type...done
Changing event title column to a larger field type...done
Changing holidays title column to a larger field type...done
Adding reset poll timestamp and guest voters counter...done
Fixing guest voter tallys on existing polls...done
Changing all tinytext columns to varchar(255)...done
Adding column that stores the PM receiving setting...done
Enable the buddy and ignore lists if we have not done so thus far...done
Add new security settings for attachments and avatars...done
Add other attachment settings...done
Checking for "babylon" and removing it if necessary...done
!!Error!
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3



Server version: 5.7.20 MySQL Community Server (GPL)

atxbyea

Quote from: shawnb61 on November 12, 2017, 12:55:06 PM
This is an issue that has been bugging the team for a while...  I was hoping you could provide a few things to us...

Could you provide: 
- the MariaDB version
- the OS and version (i.e. CentOS 7.3)
- a "SHOW VARIABLES" in the database and give us all the data (PM me with this info, not sure you want to post it)
- the /etc/my.ini file would also be great (PM me with this info, not sure you want to post it)

Long live vmware snapshots ;-)

Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1

cat /etc/debian_version
9.2
Linux hostname 4.9.0-4-686-pae #1 SMP Debian 4.9.51-1 (2017-09-28) i686 GNU/Linux


Saw nothing in variables that I couldn't share, attached!

Did you mean my.cnf ?

# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/



atxbyea

Quote from: shawnb61 on November 12, 2017, 12:45:45 PM
Quote from: atxbyea on November 12, 2017, 12:36:38 PM
It would appear that the git version is a pure install version, without any upgrade scripts, or am I missing something ? Tried unpacking it and adding the upgrade script from the upgrade package, but no go.

There are special instructions for using the latest git download here:
https://www.simplemachines.org/community/index.php?topic=530252.msg3816577#msg3816577

But you will have the same issue with the latest version...  'Value' is not treated as a reserved word throughout 2.0 & 2.1 code.

Now also tested with the latest GIT version :-) same error! (with mariadb atleast) just had to try.

albertlast

When this is the issue:

/******************************************************************************/
--- Installing new smileys sets...
/******************************************************************************/

---# Installing new smiley sets...
---{
// Don't do this twice!
if (empty($modSettings['installed_new_smiley_sets_20']))
{
// First, the entries.
upgrade_query("
UPDATE {$db_prefix}settings
SET value = CONCAT(value, ',aaron,akyhne')
WHERE variable = 'smiley_sets_known'");
// Second, the names.
upgrade_query("
UPDATE {$db_prefix}settings
SET value = CONCAT(value, '\nAaron\nAkyhne')
WHERE variable = 'smiley_sets_names'");
// This ain't running twice either.
upgrade_query("
REPLACE INTO {$db_prefix}settings
(variable, value)
VALUES
('installed_new_smiley_sets_20', '1')");
}
---}
---#


Than is this the fix:
/******************************************************************************/
--- Installing new smileys sets...
/******************************************************************************/

---# Installing new smiley sets...
---{
// Don't do this twice!
if (empty($modSettings['installed_new_smiley_sets_20']))
{
// First, the entries.
upgrade_query("
UPDATE {$db_prefix}settings
SET `value` = CONCAT(`value`, ',aaron,akyhne')
WHERE `variable` = 'smiley_sets_known'");
// Second, the names.
upgrade_query("
UPDATE {$db_prefix}settings
SET `value` = CONCAT(`value`, '\nAaron\nAkyhne')
WHERE `variable` = 'smiley_sets_names'");
// This ain't running twice either.
upgrade_query("
REPLACE INTO {$db_prefix}settings
(`variable`, `value`)
VALUES
('installed_new_smiley_sets_20', '1')");
}
---}
---#

shawnb61

This issue will continue as long as mariadb thinks value is a reserved word.  Albertlast's fixes will get you one step further, but in truth you will need to audit all of the upgrade code - the *mysql.sql files.

I'll be honest, I do not understand why it failed for mysql.   Are you sure it wasn't still using mariadb somehow?
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

atxbyea

Quote from: shawnb61 on November 13, 2017, 11:17:04 AM
This issue will continue as long as mariadb thinks value is a reserved word.  Albertlast's fixes will get you one step further, but in truth you will need to audit all of the upgrade code - the *mysql.sql files.

I'll be honest, I do not understand why it failed for mysql.   Are you sure it wasn't still using mariadb somehow?

Aye, mariadb was purged from the server, and only MySQL was running, with the version mentioned.

shawnb61

Well I just did a 2.0.15 => 2.1 beta upgrade (current from git) in my MySQL 5.7.11 environment & had no such issue.  I'm a bit flummoxed. 

Here are a couple straws to grasp at:
  -  What php version?
  -  Have you tried executing upgrade.php from your browser, not the command line?

I admit I never use the command line - the browser is just too easy.  But I'm trying to think of things that are different here...
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Advertisement: