Topic: Error when installing clean copy of 2.2 on SMF 1.1.8

[David]

This is the error I get:

Database Error
Invalid default value for 'approved'
File: C:*****\******\Packages\temp\install1-1.php
Line: 852 

I have tried it on 3 seperate clean installs of 1.1.8 but with the same results.

It installs to other versions fine...but I need 1.1.8 for a particular forum that I am running....

Please help.  This mod is THE BEST...and I cannot live without it.

Thanks so much in advance.

David

[Nathaniel]

Are they totally clean installs? Ie. Are the databases completely new or have you just replaced the files of an older forum?

If its an older database with data from an older version of SimplePortal then try running the SQL query below for your SMF database, using PHPMyAdmin.

Code: [Select]

ALTER TABLE `{$db_prefix}sp_articles` CHANGE `approved` `approved` TINYINT( 2 ) NOT NULL
Replace the '{$db_prefix}' variable with your database prefix. You should also backup your database before attempting this.

[David]

Are they totally clean installs? Ie. Are the databases completely new or have you just replaced the files of an older forum?

Yes sir, totally and completely new installs..new directories, new database, new SQL directories, new names for all directories.

I know this error must sound extremely odd to you, because it certainly does to me as well. I've never seen anything like it.  I should add that these are clean SMF 1.1.8 forums, nothing added, not even another mod installed before Simple Portal. 

When I go to install the mod...it tells me everything "passed"....no issues...and then it tosses back the error:

Database Error
Invalid default value for 'approved'
File: C:*****\******\Packages\temp\install1-1.php
Line: 852 

Now...it doesnt do this for other versions of SMF.  Only 1.1.8.  I would gladly ditch 1.1.8 except there are several mods that are essential to these forums that are only designed for 1.1.8 at this point. 

Any help would be appreciated. It's just the strangest error I have ever seen, and I can't figure it out.

Thanks,

David

[Nathaniel]

I find it an extremely strange error as well. SimplePortal 2.2 installs on SMF 1.1.8 just fine for me.

Also that error is in the 'sp_db_change_column' function, which should never even be called if you are just installing simpleportal, only if the tables are already there and need updating.

Is the process actually adding any incomplete database information? Try removing the '{$db_prefix}sp_%' tables, if it has added any of them.

I'll see if any of the developers have any idea about this issue.

[[SiNaN]]

I've tested sp_db_change_column() function several times with the field in question, but didn't get any errors. So we have to first see the full query created by that function on your forum to make any guesses.

Make this change in your Settings.php file.

Code: (Find) [Select]

$db_error_send = 1;

Code: (Replace) [Select]

$db_error_send = 1;
$db_show_debug = true;
And run the install script again. You should now see the full query below the error, if not check the Forum Error Log for that too. Once you get it, copy it here and take the change you applied in Settings.php back.

[David]

I've tested sp_db_change_column() function several times with the field in question, but didn't get any errors. So we have to first see the full query created by that function on your forum to make any guesses.

Make this change in your Settings.php file.

Code: (Find) [Select]

$db_error_send = 1;

Code: (Replace) [Select]

$db_error_send = 1;
$db_show_debug = true;
And run the install script again. You should now see the full query below the error, if not check the Forum Error Log for that too. Once you get it, copy it here and take the change you applied in Settings.php back.

Now I get the error:

Database Error
MyISAM table 'smf_sp_articles' is in use (most likely by a MERGE table). Try FLUSH TABLES.
File: C:\******\******\******\Packages\temp\install1-1.php
Line: 784

I have never had these issues before and still don't with any other mod.  The old version of SP works as well, when I install it clean.

Could this be something to do with the server and their SQL setup, perhaps?

[[SiNaN]]

Sorry for the delayed response. Would you please try the attached package?

[David]

Sorry for the delayed response. Would you please try the attached package?

Hi again...don't worry about the delay. I know the team is reliable and will get back to us....no worries here.

I tried the file you sent.    At first, it looks good....says it passed...no issues. I click "install".....



Then it takes FOREVER to process and install (60 to 90 seconds)

And then I get this error message:



"BLOB/TEXT column 'label' can't have a default value
File: *****\*****\*****
Line 854"

I have no clue at this point how to pinpoint this problem and make it work.

I'm no expert, but I have some moderate experience.  I know my way around PHP and SQL to some extent...and I am stumped.

Thanks again for all your support and patience.  It is much appreciated.

[[SiNaN]]

Again sorry for the delay, we're quite busy with 2.2.1 and some other stuff.

Attached package should, hopefully, will be fine for you.

[David]

Again sorry for the delay, we're quite busy with 2.2.1 and some other stuff.

Attached package should, hopefully, will be fine for you.

I tried it several times....I still get :

"BLOB/TEXT column 'label' can't have a default value
File: *****\*****\*****
Line ***"

this time I decided to try editing the thing myself from the file you sent me....but all it did was move the error around to different lines and make things worse.

The thing that perplexes me the most is every other mod out there, including TinyPortal...works just fine.  The problem is...I DO NOT WANT TinyPortal.   I've shopped around and cannot find anything that I like, need, or WANT more than SP.

Any ideas?  I've run out.

Thanks,

David

[[SiNaN]]

Any chance you could provide me a temporary admin and FTP access? This is quite an unusual error and as I cannot reproduce it in my installations, I'd appreciate if you would let me debug the issue.

[David]

Any chance you could provide me a temporary admin and FTP access? This is quite an unusual error and as I cannot reproduce it in my installations, I'd appreciate if you would let me debug the issue.

Absolutely...

I'll set up another clean SMF 1.1.8. and you can upload SP from scratch if you like?  That way you can see the original errors all the way to the present ones...

I will email you with the details....

Thank you very much.

David

[[SiNaN]]

The error had two sides:

1) If there were no SP tables in the database: In this case, while default blocks were being inserted, text fields (which cannot have a default value) that didn't had a value passed caused errors. Solution was passing an empty string.

2) If there were SP tables in the database: However, in this case; although a text field cannot have a default value, list_columns function returned an empty string for text fields and change_column function was trying to set the default value as an empty string. Despite the fact that it should be fine to do that, it is probably related to MySQL configuration. Solved this by ignoring default value field when returned value is an empty string.

The 2.2 package I uploaded on your test forum has the fixed install scripts within, so you can use that package for your installations.

Thanks for letting us debug the issue on your server.

[David]

The error had two sides:

1) If there were no SP tables in the database: In this case, while default blocks were being inserted, text fields (which cannot have a default value) that didn't had a value passed caused errors. Solution was passing an empty string.

2) If there were SP tables in the database: However, in this case; although a text field cannot have a default value, list_columns function returned an empty string for text fields and change_column function was trying to set the default value as an empty string. Despite the fact that it should be fine to do that, it is probably related to MySQL configuration. Solved this by ignoring default value field when returned value is an empty string.

The 2.2 package I uploaded on your test forum has the fixed install scripts within, so you can use that package for your installations.

Thanks for letting us debug the issue on your server.

SiNaN....

I have to tell you that some of what you have said...went over my head...I'm just learning this stuff now.  If there is something I can change related to MySQL configuration...would you have any idea what that would be?  I will NEVER have an SMF site without SP on it. So, when Simple Portal does its upgrades, I am, naturally, worried about future installation problems.  Since I know that the problem is MySql related (I kind of guessed it was), if you have any advice on that, I would certainly take it.

You said: "Thanks for letting us debug the issue on your server."

Here's what I can't figure out....

I came to you with a problem that turns out to be completely unrelated to SP.  99.999 percent of your members are installing it with no problems...you debugged MY server problem, rewrite your stuff around MY errors to make it work.....

and YOU are thanking ME?

This is why I will NEVER leave Simple Portal.  You all go above and beyond the call of duty...even for clueless folks like me. 

You can't know how grateful I am.

There comes a time for professionalism...and then there comes a time where all I can say is "YOU PEOPLE TOTALLY ROCK, DUDE!!!"

Thank you again.

David

[[SiNaN]]

SiNaN....

I have to tell you that some of what you have said...went over my head...I'm just learning this stuff now.  If there is something I can change related to MySQL configuration...would you have any idea what that would be?  I will NEVER have an SMF site without SP on it. So, when Simple Portal does its upgrades, I am, naturally, worried about future installation problems.  Since I know that the problem is MySql related (I kind of guessed it was), if you have any advice on that, I would certainly take it.

I'm not very skilled with MySQL, ufortunately, but I came across a MySQL bug report similar to this:

http://bugs.mysql.com/bug.php?id=13794

The last comment might be your solution:

I've found it not a bug.
It is caused by the configuration file automatically generated by the configuration tool
under Windows.
In the my.ini, sql-mode is set to STRICT_TRANS_TABLES by default, which causes this error.

However, we are trying to make SP compatible with all types of configurations. That means, if SMF is working on your server, we have to make sure that SP works on the same server too.

You said: "Thanks for letting us debug the issue on your server."

Here's what I can't figure out....

I came to you with a problem that turns out to be completely unrelated to SP.  99.999 percent of your members are installing it with no problems...you debugged MY server problem, rewrite your stuff around MY errors to make it work.....

and YOU are thanking ME?

This is why I will NEVER leave Simple Portal.  You all go above and beyond the call of duty...even for clueless folks like me. 

You can't know how grateful I am.

There comes a time for professionalism...and then there comes a time where all I can say is "YOU PEOPLE TOTALLY ROCK, DUDE!!!"

Thank you again.

David

Thanks for the kind words. And it is comments like this, shows us that we are on the right path. Thanks again for your support for SimplePortal. It wouldn't have been possible without you, our users.