getting database errors when posting or if attaching a picture .

[geminiman]

ok so ive been getting a few database errors . one is when trying to attach a picture to a post .at the moment we cant attach an image to a post .likewise we cant seem to attach files . a very very large red block of code (looks like html code ) appears .

Field 'expire_date' doesn't have a default value
public_html/Sources/Subs-Attachments.php
Line: 781

   Type of error: Database
Database Error: Field 'expire_date' doesn't have a default value


         INSERT INTO boxkingssatellite_attachments(`id_folder`, `id_msg`, `filename`, `file_hash`, `fileext`, `size`, `width`, `height`, `mime_type`, `approved`)
         VALUES
            (1, 0, SUBSTRING('test image.jpg', 1, 255), SUBSTRING('c42bbb65f7e6657f94fd9bdf0f443e07cbc566b6', 1, 40), SUBSTRING('jpg', 1, , 12358, 400, 355, SUBSTRING('image/jpeg', 1, 20), 1) https://www.satbox.info/index.php?action=post2;start=0;board=415
/public_html/Sources/Subs-Attachments.php (Line 781)

i have looked and found posts about similar errors but not the same error .

admin is showing quite a few cron errors , i think i may have mentioned this in my older thread . errors appear from what i can see to be related at the minute to what ever action we make on the site , IE going from index to private messages for example .

Type of error: Cron
8: Trying to access array offset on value of type null

here is another error we have had when trying to post a post that includes text that has been copy and pasted from another site .

Incorrect string value: '\xE2\xEF\xBF\xBD\xEF\xBF...' for column 'body' at row 1
File: /home3/boxkings/public_html/Sources/Subs-Post.php
Line: 1913

i would be very grateful for any help or advice with the above errors . thanks in advance guys .

[Doug Heffernan]

Field 'expire_date' doesn't have a default value

There is no smf expire_date field in the smf_attachments table a.f.a.i.k. Most likely it has been added by a mod.

You need to give that field a default value of null. You can do that from phpmyadmin.
If you do not use the mod that has added the afore mentioned field anymore, you can safely delete it from the smf_attachments table, or in your case it will be boxkingssatellite_attachments.

Incorrect string value: '\xE2\xEF\xBF\xBD\xEF\xBF...' for column 'body' at row 1

What is the collation character set to for your database? What mods do you have installed?

[geminiman]

hi doug thanks for the reply . how exactly do is set a value of null ? .the value (i looked at the line in the file mentioned above ) is 1 . well that is all that is in the line number in question .

how do i know what mod it is ? so far as i can see in the errors no mod name is listed .

sorry i dont know what you mean when you say collation character .

installed mods are (to the best of my memory )
shoutbox
ezportal
newsletter pro
smf gallery lite

EDIT :

ok after a bit of reading i went to phpmyadmin and to the database in question , and to the expire_date field . in the DEFAULT VALUE i clicked on the drop down box and selected NULL , then i clicked save . i then went back to my site and tried to attach a picture to a post and i got the same error again .

Field 'expire_date' doesn't have a default value
File: public_html/Sources/Subs-Attachments.php
Line: 781

so it now has a default value set , its null , yet the error is still saying no default value .

there are 4 default values
NONE
AS DEFINED
NULL
CURRENT_TIMESTAMP

i can only set value to none or null , the other two give an error .

[Doug Heffernan]

hi doug thanks for the reply .

No problem.

how exactly do is set a value of null ?

Go to the boxkingssatellite_attachments table, click the Structure button at the top, select said column and click the edit button. Next step is to untick the NOT NULL option and save the changes.

ok after a bit of reading i went to phpmyadmin and to the database in question , and to the expire_date field . in the DEFAULT VALUE i clicked on the drop down box and selected NULL , then i clicked save . i then went back to my site and tried to attach a picture to a post and i got the same error again .

Most likely the changes were not applied correctly. Please see the above reply on how make the edits.

how do i know what mod it is ? so far as i can see in the errors no mod name is listed .

installed mods are (to the best of my memory )
shoutbox
ezportal
newsletter pro
smf gallery lite

Go to the Package Manager and see what mods are listed there. Check their installer file to see which one has added that field. If you will not find it in the current mods, chances are it has been added by a mod that you no longer use. In that case, the best thing is to delete said custom field altogether from the database.

sorry i dont know what you mean when you say collation character .

Click the database name that you have used to install your forum and see what it says under the Collation tab.

[vbgamer45]

I can say for my three ezportal, newsletter pro, smf gallery lite none of them touch the attachments table

[Doug Heffernan]

That narrows it down considerably. @geminiman, you listed only 4 mods above as being currently installed. The only mod left to check is the shoutbox mod.

If that has not added said column, most likely it has been added from a previously installed mod which is no longer in use. If that turns out to be the case, make first a backup of your boxkingssatellite_attachments table and then remove said column, (expire_date) altogether from the database. There is no point in cluttering the database with leftover stuff from uninstalled/unused mods imo.

Regarding the Incorrect string value issue, can you please post a screenshot of the structure of your boxkingssatellite_messages table?

[geminiman]

That narrows it down considerably. @geminiman, you listed only 4 mods above as being currently installed. The only mod left to check is the shoutbox mod.

If that has not added said column, most likely it has been added from a previously installed mod which is no longer in use. If that turns out to be the case, make first a backup of your boxkingssatellite_attachments table and then remove said column, (expire_date) altogether from the database. There is no point in cluttering the database with leftover stuff from uninstalled/unused mods imo.

Regarding the Incorrect string value issue, can you please post a screenshot of the structure of your boxkingssatellite_messages table?

ok im following you here . the old admin / IT guy on our site that my friend originally had doing work for us (in fact there were two of them ) installed a lot of stuff and to be honest caused me a few problems site wise . but anyway as i type this there are i think now 51 items in package manager . i really want to get rid of as many of them as possible especially as many wont even be compatible .

so i im looking for a remove or delete option to get rid of that column but i cant find it , nearest i can find is DROP . i have backed up the attachments folder as you recommended .

ok ive attached a screenshot of messages as you asked . here it is


by the way vbgamer thank you for your input , much appreciated .

[Doug Heffernan]

ok im following you here . the old admin / IT guy on our site that my friend originally had doing work for us (in fact there were two of them ) installed a lot of stuff and to be honest caused me a few problems site wise . but anyway as i type this there are i think now 51 items in package manager . i really want to get rid of as many of them as possible especially as many wont even be compatible .

The best/faster way to uninstall the mods would be to overwrite your forum files with those from the large 2.1.1. upgrade package, minus the upgrade files. Then you can delete all the mods that you do not want to keep anymore and re install the ones that you want to keep using.

so i im looking for a remove or delete option to get rid of that column but i cant find it , nearest i can find is DROP . i have backed up the attachments folder as you recommended .

Yes, the drop option is the right one. You can also delete it with a sql query:

Code Select Expand

ALTER TABLE boxkingssatellite_attachments DROP expire_date;
Note that you should not drop said field before you have identified the mod that has added it and that mod is not in use anymore.

ok ive attached a screenshot of messages as you asked . here it is

I see that the collation character is set to latin1. What about the rest of the tables? What is the default collation character set to?

[geminiman]

thanks doug is there a way you can suggest that would help me identify which mod added to that field ? .

i will check the collation character of all fields and get back to you .

[Doug Heffernan]

thanks doug is there a way you can suggest that would help me identify which mod added to that field ? .

The only way that I can think of is to check the mods that have added to the database. You should check the code of the install file of those mods to see which one has added that field. Start with the mods that have added to the attachment system, for obvious reasons.

Hope it helps.

[geminiman]

thanks doug .sorry i dont mean to be a pain here , but when you say the install file for a mod or mods where can i find those files ? .i will check tho collation of other tables as you asked tomorrow and get back to you .thanks

[Doug Heffernan]

thanks doug .sorry i dont mean to be a pain here ,

No problem. And you are not being a pain at all.

but when you say the install file for a mod or mods where can i find those files ?

The install file should be inside the mod 's folder. You will have to unzip the mods and then find the package-info.xml inside the folder. Open it up and look for this part:

Code Select Expand

<database>Install.php</database>
The file called Install.php is the file that has the data that the mod adds to the database. That is taken from my mod. Other authors might have named said file differently. Whatever the name is between the <database></database> tags is the name of the file that you need. Hope it helps. Please let me know if you will have any additional questions.

[geminiman]

thanks once again doug , i really do appreciate the time and help you are giving me .

ok so do i take it that all mod zip files are the same ones stored in the packages folder . if so yes i can check those . the old admin and tech guys have installed a lot of stuff . but if as i say i have the right folder (packages ) ill have a look at the zip files for the sort of mods i know they installed .

just in regards collation character for the most part most tables have latin1_swedish_ci

however some are ut8f_bin there are about 16 tables with this
and there are some with ut8_general_ci there are about 30 of those .but by a long way the bulk of fields have latin1_swedish_ci

what exactly should i be looking for there doug ? . talk soon

EDIT:

by the way the expire_date field in attachments table is set to null now . but its still not allowing me to attach  ANYTHING to a post . after i upload an attachment i get a great big red block of what looks TO ME to be html , but im no expert so im not certain . i will attach a screen shot of this to this post .You cannot view this attachment.

and the following error also

Field 'expire_date' doesn't have a default value
File: /home3/boxkings/public_html/Sources/Subs-Attachments.php
Line: 781

so once again even tho i set the default value to null im still getting an error message there is no default value set . so im at a loss here .

[Doug Heffernan]

thanks once again doug , i really do appreciate the time and help you are giving me .

You are welcome.

ust in regards collation character for the most part most tables have latin1_swedish_ci

however some are ut8f_bin there are about 16 tables with this
and there are some with ut8_general_ci there are about 30 of those .but by a long way the bulk of fields have latin1_swedish_ci

what exactly should i be looking for there doug ? . talk soon

I think it would be best if you converted all the tables to utf-8 instead of having different collation character sets for your database. You can convert them from Your Admin Panel->Maintenance->Database->Convert HTML-entities to UTF-8 characters. Then make sure to use the utf-8 version language. But before you do the conversion, make first a backup of the database.

Have a look at this link on how to do a proper and thorough utf-8 conversion.

https://wiki.simplemachines.org/smf/UTF-8_Readme

but im no expert so im not certain . i will attach a screen shot of this to this post .

I think that a database clean up is in order. A suggestion, if I may, overwrite your forum files with those from the large 2.1.1. upgrade package, minus the upgrade files. Delete all the mods that you do not want to keep anymore and and re install the ones that you want to keep using. Then run the Smf Database Checker tool to see all the custom data that have been left over by the uninstalled/unused mods and delete them. They serve no purpose other than cluttering up the database and causing issues down the road, like the aforementioned field.

[geminiman]

"I think it would be best if you converted all the tables to utf-8 instead of having different collation character sets for your database. You can convert them from Your Admin Panel->Maintenance->Database->Convert HTML-entities to UTF-8 characters."

hi doug that cant be done as i have no such option in admin panel / maintenance / database


Use a persistent connection
Send emails on database connection errors
Database username to use in SSI mode
Database password to use in SSI mode
(confirm)

Automatically fix broken tables

the above options are all that i have . not sure why i dont have the option you mention .


i can certainly delete all un needed or unwanted mods for sure , i will do that and run the database checker that you linked to .

EDIT :

"I think that a database clean up is in order. A suggestion, if I may, overwrite your forum files with those from the large 2.1.1. upgrade package, minus the upgrade files. Delete all the mods that you do not want to keep anymore and and re install the ones that you want to keep using. Then run the Smf Database Checker tool to see all the custom data that have been left over by the uninstalled/unused mods and delete them. They serve no purpose other than cluttering up the database and causing issues down the road, like the aforementioned field."


ok so i deleted all un needed mods . thats done , there are just 5 in package manager . only 3 are installed .

i ran the smf database checker tool , it ran but i am at a loss how to delete anything . there is no option at all that i can see to delete anything . there is no red (deletes ) at all , just greens or yellows . sorry maybe i am missing something here doug , if so apologies .

[Steve]

hi doug that cant be done as i have no such option in admin panel / maintenance / database


Use a persistent connection
Send emails on database connection errors
Database username to use in SSI mode
Database password to use in SSI mode
(confirm)

Automatically fix broken tables

the above options are all that i have . not sure why i dont have the option you mention .

Because you're looking at the server database settings. So go to Admin, then scroll down to Maintenance, then click Forum Maintenance. It will be next to the server option you clicked on.

The option Doug mentioned should be the last one down.

[geminiman]

hi steve my apologies you are correct i did look in the server options . HOWEVER i do not have the option that doug mentions , ITS NOT THERE .

ok so maintenance , then forum maintenance  , here are the options in it

ROUTINE
DATABASE
MEMBERS
TOPICS
INTEGRATION HOOKS

now as what i need to do according to doug is database related i selected database , and there are only two options in there

OPTIMIZE
COVERT TO MEDIUM TEXT

so is the above convert to medium text the option i need ? . if not i dont have the option in question . if that is indeed the option i can select that for sure .

[geminiman]

ok just in regard the original error i reported above in my first reply regarding the expire date error . well i have deleted all old and un needed mods , only mods that remain are a select few NEWLY ADDED mods .

i dropped the field doug said to drop , the filed that was causing the error . so there is now no field and no default value . but the same error persists .

Field 'expire_date' doesn't have a default value
File: /home3/boxkings/public_html/Sources/Subs-Attachments.php
Line: 781

so we still cant attach any files or anything to posts . sorry for posting a second post here , i would have edited my last post and added the info to that but was not able to for some reason . only option i could see was report post .

[Doug Heffernan]

ROUTINE
DATABASE
MEMBERS
TOPICS
INTEGRATION HOOKS

now as what i need to do according to doug is database related i selected database , and there are only two options in there

Can you please post a screenshot of your Admin Panel->Maintenance->Database area?

i dropped the field doug said to drop , the filed that was causing the error . so there is now no field and no default value . but the same error persists .

Field 'expire_date' doesn't have a default value
File: /home3/boxkings/public_html/Sources/Subs-Attachments.php
Line: 781

If that field has been droped and the mod that had added it has been uninstalled, the error should not ocurr anymore. Are you sure that you are editing the right database?
 

[Steve]

@Doug Heffernan, shouldn't 'Convert HTML-entities to UTF-8 characters' also be an option in there?