News:

Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

Strange issue with copied forum and database errors.

Started by GL700Wing, June 24, 2018, 08:40:19 AM

Previous topic - Next topic

GL700Wing

Earlier today I copied a new forum I'd setup from one hosting service to another.  The new forum was an almost identical clone of an existing forum which has been working without any issues for more than a year (the new forum didn't need some of the mods I'd installed on the source forum).  In addition, I've done heaps of testing on the new forum over the last couple of weeks and I haven't had any issues with registering new members, posting topics, etc.

However, when I copied the new forum to the new hosting service I started getting database errors when registering new members and posting topics (and I suspect there may be some more surprises in store) ...

Basically the issue is that there are integer fields in some of the database tables that have been created by currently installed mods that do not have a default value and I'm getting errors like "Database Error: Field 'topics' doesn't have a default value."  (I've worked out that the Topic Count On Display mod created this field in the members table).

What I don't understand - and would like to work out how to resolve - is why this issue isn't affecting the source database/forum (or any of it's clones that I use for testing and development on the same hosting service) which has been running flawlessly for more than a year and what database setting/configuration option I need to change on the new hosting service.  I checked and confirmed that the PHP versions and configuration, PHP info, mod_security, etc are all the same.
Life doesn't have to be perfect to be wonderful ...

Aleksi "Lex" Kilpinen

I would guess your original install and the copy are running different versions of MySQL. There were some versions that just assumed all fields should always have a default value.
Slava
Ukraini!
"Before you allow people access to your forum, especially in an administrative position, you must be aware that that person can seriously damage your forum. Therefore, you should only allow people that you trust, implicitly, to have such access." -Douglas

How you can help SMF

GL700Wing

Quote from: Aleksi "Lex" Kilpinen on June 24, 2018, 08:58:54 AM
I would guess your original install and the copy are running different versions of MySQL. There were some versions that just assumed all fields should always have a default value.
According to cPanel they are both running MySQL 10.2.15-MariaDB.
Life doesn't have to be perfect to be wonderful ...

shawnb61

Check the data structure for the table/field in question.   Is there a default value?   Compare the forum without errors to the one with errors - confirm the db is aligned.

Can you provide a list of mods?   This is probably due to a mod that added a column without a default value.   
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Arantor

The original forum has code that puts the value into the database; the clone forum doesn't have the code and gets upset at there not being code to put the value into the database.

GL700Wing

Quote from: shawnb61 on June 24, 2018, 09:20:32 AM
Check the data structure for the table/field in question.   Is there a default value?
Checked and confirmed that there is no default value in either database.

QuoteCompare the forum without errors to the one with errors - confirm the db is aligned.
Also done - the database generating the errors was imported from a backup of the database that doesn't generate errors.

QuoteCan you provide a list of mods?   This is probably due to a mod that added a column without a default value.
I could but as per my first post I've already determined that Topic Count on Display is one of the culprits.
Life doesn't have to be perfect to be wonderful ...

GL700Wing

Quote from: Arantor on June 24, 2018, 09:34:24 AM
The original forum has code that puts the value into the database; the clone forum doesn't have the code and gets upset at there not being code to put the value into the database.
I copied everything to the new host (ie, forum software and database) and the issue definitely doesn't occur on the host where I prepared the new forum.
Life doesn't have to be perfect to be wonderful ...

shawnb61

Sorry, missed that.

Are the SMF versions current & aligned?

Next thing I would try is a deinstall/reinstall of that mod. 

I'd try the support thread for that mod - though it looks a little stale:
https://www.vguitarforums.com/smf/index.php?topic=20254.0

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

GL700Wing

Quote from: shawnb61 on June 24, 2018, 09:50:34 AM
Sorry, missed that.

Are the SMF versions current & aligned?
Yes - I copied everything to the new host (ie, forum software and database) and the issue definitely doesn't occur on the host where I prepared the new forum.

QuoteNext thing I would try is a deinstall/reinstall of that mod.
I've done that but it made no difference because the field is only created the first time the mod is installed.  That said, I have installed the mod on a brand new SMF 2.0.15 forum on the new host and I get the following database error when I try to register a new user:
Field 'topics' doesn't have a default value
File: /home/ultiowne/public_html/forum_labs/Sources/Subs-Members.php
Line: 771


I have also confirmed that the database errors do not occur when I install the mod on a brand new SMF 2.0.15 forum on the original host.

QuoteI'd try the support thread for that mod - though it looks a little stale
I know that essentially the issue is with the mod (ie, it shouldn't have created an integer field in the database that didn't have a default value) but what I'm trying to understand is why this issue is happening on one host and not the other.
Life doesn't have to be perfect to be wonderful ...

shawnb61

Understood.   (I have that particular form of OCD myself...)

Where I would head next is comparing phpinfo.php output across the two environments, paying special attention to anything affecting error reporting or strict modes.
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

vbgamer45

It would be caused by different versions of mysql and or mysql config differences.
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

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

Chen Zhen

#11
Your MariDB installation likely had some strict mode set as default when it was installed.
Imo you can simply adjust your sql_mode settings to remove the strict mode that is causing the problem.

The mysql config file is possibly located at: /etc/mysql/my.cnf or use your control panel to adjust your mysql configuration.
Look for: sql_mode= 

ref.
https://mariadb.com/kb/en/library/changes-improvements-in-mariadb-102/
Incompatible Changes | SQL_MODE

My SMF Mods & Plug-Ins

WebDev

"Either you repeat the same conventional doctrines everybody is saying, or else you say something true, and it will sound like it's from Neptune." - Noam Chomsky

Sir Osis of Liver

There's a mysql bug that does that, haven't seen it in mariadb.  Assign a default value of 'null' to the empty fields, that usually (not always) fixes it.

Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

GL700Wing

Quote from: vbgamer45 on June 24, 2018, 10:45:16 AM
It would be caused by different versions of mysql and or mysql config differences.
According to the information in cPanel on both hosts the versions of mySql are the same so their must be differences in the way they are configured.




Quote from: Chen Zhen on June 24, 2018, 11:41:57 AM
Your MariDB installation likely had some strict mode set as default when it was installed.
Imo you can simply adjust your sql_mode settings to remove the strict mode that is causing the problem.

The mysql config file is possibly located at: /etc/mysql/my.cnf or use your control panel to adjust your mysql configuration.
Look for: sql_mode= 

ref.
https://mariadb.com/kb/en/library/changes-improvements-in-mariadb-102/
Incompatible Changes | SQL_MODE
Thanks for that info - it seems like the sql_mode settings are different.  However, and I'm on shared hosting, I don't think I can change the settings.  What I will do though is check the other SMF databases I have and make sure that all 'NOT NULL' integer fields have been assigned a default value so that my other forums don't end up in trouble if the hosting company changes the sql_mode settings at a later date.




Quote from: Sir Osis of Liver on June 24, 2018, 05:29:28 PM
Assign a default value of 'null' to the empty fields, that usually (not always) fixes it.
That's the fix I've implemented.




Thanks everyone for your help.
Life doesn't have to be perfect to be wonderful ...

GigaWatt

"This is really a generic concept about human thinking - when faced with large tasks we're naturally inclined to try to break them down into a bunch of smaller tasks that together make up the whole."

"A 500 error loosely translates to the webserver saying, "WTF?"..."

GL700Wing

Quote from: GigaWatt on June 25, 2018, 08:21:09 AM
Can this be marked as solved?
I guess - I've come across another mod with the same issue and I've now determined that the server I moved the forum to has SQL strict mode enabled whereas the one it came from doesn't.

I'll continue checking and testing the new forum but I've also checked all the mods I use that add database columns and I'll install those on a test forum on the new server to see if any of them add incorrectly configured columns.

Unfortunately there's no way of knowing when you install a mod that adds one or more columns to a database if the columns are configured correctly and also unfortunately uninstalling the mod doesn't resolve this issue it causes (ie, breaking parts of the forum) because the  column isn't (usually) removed by the uninstall process.
Life doesn't have to be perfect to be wonderful ...

GigaWatt

Quote from: GL700Wing on June 25, 2018, 08:32:16 AM
Unfortunately there's no way of knowing when you install a mod that adds one or more columns to a database if the columns are configured correctly and also unfortunately uninstalling the mod doesn't resolve this issue it causes (ie, breaking parts of the forum) because the  column isn't (usually) removed by the uninstall process.

Most mod authors assume that you might come back to using the mod some day (if you happen to uninstall it), so in case you don't want to lose the data created by the previous install of the mod, they leave columns and/or tables in the database untouched with the assumption that "meeeh, it's not bothering anyone", which is true in most cases, but not in cases like yours. And in case the user decides to install the mod again, "hey, look at that, this is nice, all my data is still here :)", which is also convenient in most cases. What I can tell you from experience, it's not really convenient when doing upgrades from one major version to another :-\.

In the cases mentioned above, as in your case, I think there is no other option except to delete the said column/columns or table/tables from phpMyAdmin.

I guess I'll leave this as unsolved, in case there are more problems with some other mods ;).
"This is really a generic concept about human thinking - when faced with large tasks we're naturally inclined to try to break them down into a bunch of smaller tasks that together make up the whole."

"A 500 error loosely translates to the webserver saying, "WTF?"..."

Arantor

Properly written mods should give you the option to clean house when uninstalling.

Chen Zhen

Here is a list of some SQL_MODES which you can opt:
https://mariadb.com/kb/en/library/sql-mode/

It appears that version of MariaDB has modes that allow it to mimic other MySQL versions.
Find out one of the other forum MySQL version and attempt to use the mode that mimics its behavior.
You will likely need to reset MariaDB and/or Apache/Nginx after changes.
That may be a way to solve your problem.
 

My SMF Mods & Plug-Ins

WebDev

"Either you repeat the same conventional doctrines everybody is saying, or else you say something true, and it will sound like it's from Neptune." - Noam Chomsky

GigaWatt

Quote from: Arantor on June 25, 2018, 09:48:52 AM
Properly written mods should give you the option to clean house when uninstalling.

Exactly my thoughts.
"This is really a generic concept about human thinking - when faced with large tasks we're naturally inclined to try to break them down into a bunch of smaller tasks that together make up the whole."

"A 500 error loosely translates to the webserver saying, "WTF?"..."

Advertisement: