Update from 3.0.11 to 3.3.14 SQL Error

Get help with installation and running phpBB 3.3.x here. Please do not post bug reports, feature requests, or extension related questions here.
phyler
Registered User
Posts: 7
Joined: Mon Dec 16, 2024 1:11 am

Update from 3.0.11 to 3.3.14 SQL Error

Post by phyler »

Support Request Template
What version of phpBB are you using? phpBB 3.3.14
What is your board's URL? http://coloradoclassicbroncos.com
Who do you host your board with? HostGator
How did you install your board? I used the download package from phpBB.com
What is the most recent action performed on your board? Update from a previous version of phpBB3
Is registration required to reproduce this issue? No
Do you have any MODs installed? No
Do you have any extensions installed? No
What version of phpBB3 did you update from? phpBB 3.0.11
What styles do you currently have installed? prosilver
What language(s) is your board currently using? British English
Which database type/version are you using? MySQL 5
What is your level of experience? New to PHP and phpBB
What actions did you take (updating your board; installing a MOD, style or extension; etc.) prior to this problem becoming noticeable? I was upgrading from 3.0.11 for our forum to 3.3.14. Everything was going smoothly until the step to update the DB. It got to 90% and posted a SQL error (pasted below).

I can restore the DB if needed but this seems like something that would happen again if I did.
Please describe your problem.

Code: Select all

General Error:SQL ERROR [ mysqli ]Incorrect integer value: '' for column 'user_avatar_type' at row 1 [1366]SQLINSERT INTO coloradoclassicbroncos_users (username, username_clean, user_password, user_email, group_id, user_type, user_permissions, user_timezone, user_dateformat, user_lang, user_style, user_actkey, user_ip, user_regdate, user_passchg, user_options, user_new, user_inactive_reason, user_inactive_time, user_lastmark, user_lastvisit, user_lastpost_time, user_lastpage, user_posts, user_colour, user_avatar, user_avatar_type, user_avatar_width, user_avatar_height, user_new_privmsg, user_unread_privmsg, user_last_privmsg, user_message_rules, user_full_folder, user_emailtime, user_notify, user_notify_pm, user_notify_type, user_allow_pm, user_allow_viewonline, user_allow_viewemail, user_allow_massemail, user_sig, user_sig_bbcode_uid, user_sig_bbcode_bitfield, user_form_salt) VALUES ('DuckDuckGo [Bot]', 'duckduckgo [bot]', '', '', '6', 2, '', 0, 'D M d, Y g:i a', 'en', '17', '', '', 1734311263, 1734311263, 230271, 0, 0, 0, 1734311263, 0, 0, '', 0, '9E8DA7', '', '', 0, 0, 0, 0, 0, 0, -3, 0, 0, 1, 0, 1, 1, 1, 0, '', '', '', 'i1j3pndtnq081gyh') in file /home1/zillacon/public_html/phpbb/db/driver/driver.php on line 1028
Generated by SRT Generator
rxu
Extensions Development Team
Posts: 4009
Joined: Wed Oct 25, 2006 12:46 pm
Location: Siberia, Russian Federation

Re: Update from 3.0.11 to 3.3.14 SQL Error

Post by rxu »

What is the type of user_avatar_type coumn in the coloradoclassicbroncos_users table structure at this point? Either int or varchar?
phyler
Registered User
Posts: 7
Joined: Mon Dec 16, 2024 1:11 am

Re: Update from 3.0.11 to 3.3.14 SQL Error

Post by phyler »

Looks like it it tinyint(2).

Thanks!
rxu
Extensions Development Team
Posts: 4009
Joined: Wed Oct 25, 2006 12:46 pm
Location: Siberia, Russian Federation

Re: Update from 3.0.11 to 3.3.14 SQL Error

Post by rxu »

It should have been converted to varchar(255) already before this point, but something went wrong, probably phpBB bug but not sure.
User avatar
janus_zonstraal
Registered User
Posts: 6618
Joined: Sat Aug 30, 2014 1:30 pm

Re: Update from 3.0.11 to 3.3.14 SQL Error

Post by janus_zonstraal »

Try to do the update in smallere steps
3.0.11to 3.1.0
3.1.0 to 3.2.0
3.2.0 to 3.3.14
Sorry! My English is bat ;) !!!
User avatar
warmweer
Jr. Extension Validator
Posts: 12072
Joined: Fri Jul 04, 2003 6:34 am
Location: somewhere in the space-time continuum

Re: Update from 3.0.11 to 3.3.14 SQL Error

Post by warmweer »

Looking at the nr of posts on your board, updating 3.0.11 to 3.3.14 shouldn't be a problem.
However, you've got at least one modification : mobiquo. That needs to be uninstalled completely before starting the upgrade.
Also you're using style acidtech. That should be deactivated before starting the upgrade.

You're enforcing https but cookie_secure is set to false.


Before starting the upgrade you need to sanitise the database using the STK.

Once your board is "clean" switch PHP version (to a version compatible for 3.3.14) before starting the upgrade.

Upgrading from 3.0 to 3.3
Spelling is freeware, which means you can use it for free.
On the other hand, it is not open source, which means you cannot change it or publish it in a modified form.


Time flies like an arrow, but fruit flies like a banana.
phyler
Registered User
Posts: 7
Joined: Mon Dec 16, 2024 1:11 am

Re: Update from 3.0.11 to 3.3.14 SQL Error

Post by phyler »

Thanks. I ended up having to revert the board back to a restore point.

Working through the suggestions.

I get an HTTP 500 error when trying to use the Database Cleaner from the STK. Any thoughts on that? I don't see anything in the Error log that would reference why though.

Thanks!
phyler
Registered User
Posts: 7
Joined: Mon Dec 16, 2024 1:11 am

Re: Update from 3.0.11 to 3.3.14 SQL Error

Post by phyler »

Ok, posting back here just to keep this altogether. Happy to start a new thread if needed.

I was able to work with the hosting provider to get back to PHP 5.6. This got the board back up and running as well as the STK working.

That said, when I go to use the Database Cleaner, it is showing a lot of tables.

https://photos.app.goo.gl/ehjktCV6jAcJpvb8A

This makes me think there are more mods I need to remove before I update the DB. Specifically, I'm concerned about the Arcade stuff. I see in the Users table that there are references to the Arcade (i.e. user_arcade_permissions). I don't see the Database Cleaner showing it will clean up within a table, just that it will drop tables that are "Extra".

Should I be worried? Or just run the DB cleaner, and proceed with the update (I will have to update the PHP module before the DB Update phase of the Install of 3.3).

Thanks!
User avatar
warmweer
Jr. Extension Validator
Posts: 12072
Joined: Fri Jul 04, 2003 6:34 am
Location: somewhere in the space-time continuum

Re: Update from 3.0.11 to 3.3.14 SQL Error

Post by warmweer »

phyler wrote: Sun Jan 12, 2025 10:01 pm ...
That said, when I go to use the Database Cleaner, it is showing a lot of tables.

https://photos.app.goo.gl/ehjktCV6jAcJpvb8A

This makes me think there are more mods I need to remove before I update the DB.
There's no thinking about it: the non default tables have to go, except perhaps if the mod was "continued" as an extension. For that you'll have to list your mods and search in the extension database to find out whether there is an extension version. In many cases the mod itself contains info as to the origin/author of the mod so read though the installation files, perhaps you can still contact the author.
phyler wrote: Sun Jan 12, 2025 10:01 pm Specifically, I'm concerned about the Arcade stuff. I see in the Users table that there are references to the Arcade (i.e. user_arcade_permissions). I don't see the Database Cleaner showing it will clean up within a table, just that it will drop tables that are "Extra".

Should I be worried? Or just run the DB cleaner, and proceed with the update (I will have to update the PHP module before the DB Update phase of the Install of 3.3).
There has been an arcade extension (perhaps there still is) but I have no idea if that was a conversion of the arcade mod you're using. The extension area usually contains info regarding upgrading. In some cases the (old) data can be kept/transformed. Another place to look in is the extension support area. If it is a conversion of a 3.0.x mod, it'll usually be mentioned there (with upgrade instructions if it's possible).
Spelling is freeware, which means you can use it for free.
On the other hand, it is not open source, which means you cannot change it or publish it in a modified form.


Time flies like an arrow, but fruit flies like a banana.
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 6593
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.

Re: Update from 3.0.11 to 3.3.14 SQL Error

Post by thecoalman »

Backup you DB and files and ther should be nothing to worry about because you can always revert to backup
phyler wrote: Sun Jan 12, 2025 10:01 pm
This makes me think there are more mods I need to remove before I update the DB.
You don't need to remove anything.

Uninstall any styles except prosilver so it's set as default, uninstall any language packs except British English so it's default.

Download 3.0.14 from here: https://download.phpbb.com/pub/release/ ... 3.0.14.zip

On the server delete everything except config.php. /images /cache /store and /files

Delete config.php in download and upload everything else, you'll get prompted for some duplicate files and just select overwrite. This will insure you have latest .htaccess files.

Point your browser at yoursite.com/install/ to upgrade to 3.0.14. Delete or rename install folder.

Once upgraded you should now have mod free forum without any errors.

Upload STK and run it. The DB cleaner removes extra tables and fields. Technically this is not required however they could create a conflict in the future plus it's just clutter since they are not compatible with 3.3.14. For example you have notifications table in your screenshot, that will likely cause error with upgrade to 3.3.14 where one is created.

Paste this into the config.php in the download for 3.3.14, you need to edit in your db name, username and password. These can be found in existing config.php

Code: Select all

<?php
// phpBB 3.3.x auto-generated configuration file
// Do not change anything in this file!
$dbms = 'phpbb\\db\\driver\\mysqli';
$dbhost = '';
$dbport = '';
$dbname = '';
$dbuser = '';
$dbpasswd = '';
$table_prefix = 'phpbb_';
$phpbb_adm_relative_path = 'adm/';
$acm_type = 'phpbb\\cache\\driver\\file';

@define('PHPBB_INSTALLED', true);
@define('PHPBB_ENVIRONMENT', 'production');
// @define('DEBUG_CONTAINER', true);
Switch the server to php 8 to 8.3, 8.3 if available.

Delete everything on server except /images /cache /store and /files

Upload all of the contents of 3.3.14, once again you will be prompted to overwrite existing files. Choose to overwrite.

Point your browser at install folder to upgade to 3.1.14.
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison
User avatar
warmweer
Jr. Extension Validator
Posts: 12072
Joined: Fri Jul 04, 2003 6:34 am
Location: somewhere in the space-time continuum

Re: Update from 3.0.11 to 3.3.14 SQL Error

Post by warmweer »

As to the gallery:
You may want to have a look at https://github.com/satanasov/phpbbgallery/tree/master.
If the gallery mod you used was the nickvergessen one, then the data can perhaps be kept for the extension version. (see https://www.phpbb.com/customise/db/exte ... pic/226316).

Mind you, it's not going to be simple.
Spelling is freeware, which means you can use it for free.
On the other hand, it is not open source, which means you cannot change it or publish it in a modified form.


Time flies like an arrow, but fruit flies like a banana.
phyler
Registered User
Posts: 7
Joined: Mon Dec 16, 2024 1:11 am

Re: Update from 3.0.11 to 3.3.14 SQL Error

Post by phyler »

Honestly, I don't need to keep anything other then the posts and pictures in the posts.

I don't care about the Arcade or Gallery either.

I'll try running the update after dropping the extra tables. It was more of a concern around any additional columns in the tables that need to stay.

I'll look at the extensions table as well.

The plan is to upgrade to PHP 8.3 as part of it (since 3.3.x doesn't support PHP 5.6 anyways).

Appreciate it. I'll find a time to do it (making backups along the way) and report back. The joy of inheriting a system that hasn't been updated in darn near forever. :)
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 6593
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.

Re: Update from 3.0.11 to 3.3.14 SQL Error

Post by thecoalman »

phyler wrote: Tue Jan 14, 2025 11:55 pm I'll try running the update after dropping the extra tables. It was more of a concern around any additional columns in the tables that need to stay.
That's specifically what the DB cleaner in the STK is for. For 3.0 you installed mods which could also create tables and columns. Generally there is no uninstall unless you do it manually. The DB cleaner knows which tables and columns are stock and will will remove the unnecessary ones.

Same thing with the files, they are altered from core files. This is probably the reason it was never updated, there may not be path to upgrade keeping the mods and even the version update is difficult.

If you followed the directions I gave once you run the DB cleaner you'll have stock install of phpBB 3.0.14 both files and DB ready for clean upgrade to 3.3.13


I don't care about the Arcade or Gallery either.
The gallery mod might be something to pay attention too. I have no idea about how it's implemented but if for example it used it's own directory <img> tags in posts referencing those files will return 404 . If it used phpBB's /files folder to store files and they are not attached to posts you are going to have a lot of extra files.
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison

Return to “[3.3.x] Support Forum”