Advertisement:

Author Topic: Upgrade from 2.0.14 -> 2.1 beta3 fails  (Read 4101 times)

Offline atxbyea

  • Semi-Newbie
  • *
  • Posts: 16
Upgrade from 2.0.14 -> 2.1 beta3 fails
« on: November 12, 2017, 11:02:37 AM »
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).

Code: [Select]
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.

Offline Illori

  • Project Manager
  • SMF Master
  • *
  • Posts: 48,086
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #1 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.

Offline shawnb61

  • Support Specialist
  • Full Member
  • *
  • Posts: 508
    • sbulen on GitHub
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #2 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:

Code: [Select]
/******************************************************************************/
--- 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

Offline shawnb61

  • Support Specialist
  • Full Member
  • *
  • Posts: 508
    • sbulen on GitHub
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #3 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/
https://mariadb.com/kb/en/library/reserved-words/+comments/697

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

Offline atxbyea

  • Semi-Newbie
  • *
  • Posts: 16
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #4 on: November 12, 2017, 12:21:16 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]


Offline shawnb61

  • Support Specialist
  • Full Member
  • *
  • Posts: 508
    • sbulen on GitHub
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #5 on: November 12, 2017, 12:27:59 PM »
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

Offline atxbyea

  • Semi-Newbie
  • *
  • Posts: 16
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #6 on: November 12, 2017, 12:29:38 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:

Code: [Select]
/******************************************************************************/
--- 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..


Code: [Select]
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


Offline atxbyea

  • Semi-Newbie
  • *
  • Posts: 16
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #7 on: November 12, 2017, 12:36:38 PM »
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.

Offline shawnb61

  • Support Specialist
  • Full Member
  • *
  • Posts: 508
    • sbulen on GitHub
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #8 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?
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Offline atxbyea

  • Semi-Newbie
  • *
  • Posts: 16
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #9 on: November 12, 2017, 12:42:59 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..

Offline shawnb61

  • Support Specialist
  • Full Member
  • *
  • Posts: 508
    • sbulen on GitHub
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #10 on: November 12, 2017, 12:45:45 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

Offline shawnb61

  • Support Specialist
  • Full Member
  • *
  • Posts: 508
    • sbulen on GitHub
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #11 on: November 12, 2017, 12:47:22 PM »
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

Offline shawnb61

  • Support Specialist
  • Full Member
  • *
  • Posts: 508
    • sbulen on GitHub
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #12 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)

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

Offline atxbyea

  • Semi-Newbie
  • *
  • Posts: 16
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #13 on: November 12, 2017, 12:58:06 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..

Code: [Select]
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)

Offline atxbyea

  • Semi-Newbie
  • *
  • Posts: 16
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #14 on: November 12, 2017, 01:06:02 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 ?

Code: [Select]
# 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/


Offline atxbyea

  • Semi-Newbie
  • *
  • Posts: 16
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #15 on: November 12, 2017, 02:53:43 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.

Online albertlast

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 177
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #16 on: November 12, 2017, 03:28:30 PM »
When this is the issue:

Code: [Select]
/******************************************************************************/
--- 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:
Code: [Select]
/******************************************************************************/
--- 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')");
}
---}
---#

Offline shawnb61

  • Support Specialist
  • Full Member
  • *
  • Posts: 508
    • sbulen on GitHub
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #17 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?
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Offline atxbyea

  • Semi-Newbie
  • *
  • Posts: 16
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #18 on: November 13, 2017, 02:05:07 PM »
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.

Offline shawnb61

  • Support Specialist
  • Full Member
  • *
  • Posts: 508
    • sbulen on GitHub
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #19 on: November 13, 2017, 11:07:33 PM »
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

Offline atxbyea

  • Semi-Newbie
  • *
  • Posts: 16
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #20 on: November 15, 2017, 08:36:10 AM »
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...

root@ferus:~# php -v
PHP 7.0.19-1 (cli) (built: May 11 2017 14:04:47) ( NTS )
Copyright (c) 1997-2017 The PHP Group
Zend Engine v3.0.0, Copyright (c) 1998-2017 Zend Technologies
    with Zend OPcache v7.0.19-1, Copyright (c) 1999-2017, by Zend Technologies


I always run the upgrades from the web, never tried the CLI :-)

My MySQL was 5.7.20 as you can see above, that is a difference though.

Offline atxbyea

  • Semi-Newbie
  • *
  • Posts: 16
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #21 on: November 15, 2017, 08:42:57 AM »
When this is the issue:

Code: [Select]
/******************************************************************************/
--- 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:
Code: [Select]
/******************************************************************************/
--- 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')");
}
---}
---#

Even after editing the script I still get the following

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 MariaDB server version for the right syntax to use near '' at line 3

Offline shawnb61

  • Support Specialist
  • Full Member
  • *
  • Posts: 508
    • sbulen on GitHub
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #22 on: November 15, 2017, 11:23:08 AM »
Which file did you update?   Remember to install from github, you gotta copy the files from /other.  Make sure you're running an updated file.

That particular file reapplies 2.0 updates as a safety measure.  If you were properly 2.0.x already, those updates are not needed.   (Though the file is...)
So another thing to try would be to just delete all sections from the babylon theme cleanup on down.  The real meat is in the 2.1 .sql file.
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Offline atxbyea

  • Semi-Newbie
  • *
  • Posts: 16
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #23 on: November 16, 2017, 08:22:29 AM »
Which file did you update?   Remember to install from github, you gotta copy the files from /other.  Make sure you're running an updated file.

That particular file reapplies 2.0 updates as a safety measure.  If you were properly 2.0.x already, those updates are not needed.   (Though the file is...)
So another thing to try would be to just delete all sections from the babylon theme cleanup on down.  The real meat is in the 2.1 .sql file.

Aye, this was with the "official" non git version, I have copied the extracted upgrade.zip to the www-root and edited the "upgrade_2-0_mysql.sql" file as above (with ` ` around the value and variable entires, and pico does confirm the change by making those expressions blue when I add it).

So just delete everything from line 2817 and down ?

Offline shawnb61

  • Support Specialist
  • Full Member
  • *
  • Posts: 508
    • sbulen on GitHub
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #24 on: November 16, 2017, 10:15:58 AM »
So just delete everything from line 2817 and down ?

Let's go from 2754 down, just in case it was not really done with babylon. 

I think it will successfully do the first few steps in the 2.1 mysql.sql file.  But where it fails will help confirm/disprove the suspicion that 'value' is the culprit. 

And if 'value' is the culprit, I don't think your edits were being executed - the quotes should have fixed that.  The edited *mysql.sql files should end up in your forum's root directory during the upgrade (same directory as settings.php & upgrade.php). 
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Offline Nolt

  • Language Moderator
  • Full Member
  • *
  • Posts: 485
  • Gender: Male
  • /dev/null
    • smfpolska on Facebook
    • Wizzi
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #25 on: November 16, 2017, 05:07:56 PM »
Fingers crossed to see MariaDB support :)

Offline SleePy

  • Site Team Lead
  • SMF Master
  • *
  • Posts: 29,941
  • Gender: Male
  • Thats his happy face.
    • jdarwood007 on GitHub
    • @jdarwood on Twitter
    • SleePy Code - My personal site
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #26 on: November 16, 2017, 08:25:14 PM »
Did you mean my.cnf ?

Code: [Select]
# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

Yes my bad, it should be the my.cnf.
As you can see from the config, it is including all files in those directories.  Can you include those as well?

Your running Debian though, so thats interesting.  I thought the issue was relevant with CentOS.  Are you also running this as 32 bit?

Fingers crossed to see MariaDB support :)
There is no official support for MariaDB, but I will see what is causing this at least.
Jeremy D — Site Team / SMF Developer
Support the SMF Support team!
Profiles:
GitHub

Offline atxbyea

  • Semi-Newbie
  • *
  • Posts: 16
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #27 on: November 18, 2017, 07:25:11 AM »
Did you mean my.cnf ?

Code: [Select]
# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

Yes my bad, it should be the my.cnf.
As you can see from the config, it is including all files in those directories.  Can you include those as well?

Your running Debian though, so thats interesting.  I thought the issue was relevant with CentOS.  Are you also running this as 32 bit?

Fingers crossed to see MariaDB support :)
There is no official support for MariaDB, but I will see what is causing this at least.

Not very much interesting in the files though, but they are attached!

root@ferus:~# uname -a
Linux ferus 4.9.0-4-686-pae #1 SMP Debian 4.9.51-1 (2017-09-28) i686 GNU/Linux


Indeed 32bit

Offline atxbyea

  • Semi-Newbie
  • *
  • Posts: 16
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #28 on: November 18, 2017, 07:29:52 AM »
So just delete everything from line 2817 and down ?

Let's go from 2754 down, just in case it was not really done with babylon. 

I think it will successfully do the first few steps in the 2.1 mysql.sql file.  But where it fails will help confirm/disprove the suspicion that 'value' is the culprit. 

And if 'value' is the culprit, I don't think your edits were being executed - the quotes should have fixed that.  The edited *mysql.sql files should end up in your forum's root directory during the upgrade (same directory as settings.php & upgrade.php).

Deleting from 2754 and down did let the upgrade complete with no errors!

But..

Previously installed Themes and smileys appear to be missing in the installation, rather expected though since those are the lines we removed.

Tried to import the theme, but it seems to not be compatible with 2.1 anyway.. so I probably have to play around to get that working also..

Your theme does not show it has compatibility with SMF 2.1 Beta 4. Please contact the theme author.
« Last Edit: November 18, 2017, 07:53:21 AM by atxbyea »

Offline shawnb61

  • Support Specialist
  • Full Member
  • *
  • Posts: 508
    • sbulen on GitHub
Re: Upgrade from 2.0.14 -> 2.1 beta3 fails
« Reply #29 on: November 18, 2017, 10:34:03 AM »
2.1 themes are incompatible with 2.0 themes.  You need to wait for some 2.1 themes.

You could have left your old smileys folder there (and attachments, and avatars) and kept them.   
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp