temp search table full - any concern ?

[aegersz]

hi, I saw this message in the log and wondered if I can make any settings changes to avoid this probably innocuous error.

[ERROR] /usr/sbin/mysqld: The table 'smf209_tmp_log_search_topics' is full

I hope that I'm not wasting your time.

[br360]

Can you try to empty out the unimportant logs in your admin panel and see if that helps?

It's in Admin>Maintenance> Forum Maintenance> Routine

[Kindred]

And have a chat with your host

[aegersz]

Can you try to empty out the unimportant logs in your admin panel and see if that helps?

It's in Admin>Maintenance> Forum Maintenance> Routine

I have never done that before ... Done !

[aegersz]

Can you try to empty out the unimportant logs in your admin panel and see if that helps?

It's in Admin>Maintenance> Forum Maintenance> Routine

I have never done that before ... Done !

I am sort of my host as i built the server and only pay for my virtual machine ... why do you recommend that, please ?

[Arantor]

I'd be surprised if that fixed the problem.

I'd be interested to know if the table in question is MyISAM or InnoDB, and if MyISAM, how big the table's .myd file is. More likely, optimising the table in phpMyAdmin will fix it.

[aegersz]

Hi Arantor, funny that you should ask this as i have just moved to InnoDB and i optimise my tables daily.

I don't know if it fixed the problem but i have just restarted mysqld and will wait and see.

But i am unclear as to what the error actually means ? ie, what triggered it, do you know ?

[albertlast]

Why no one recommand to him to truncate(delete all entries) the table?

[aegersz]

i have just been looking for that table and it does not exist now !

i guess the "tmp" part would explain that ...

Due to memory constraints, i have also "detuned" mysql and InnoDB to use the smallest possible viable memory usage ... could that be an aggravating factor ?

I only have 1 GB on my system and 2 GB SWAP (SSD) [Linux swapiness of 72] but that's all we can really afford as we run a free site.

[shawnb61]

Why no one recommand to him to truncate(delete all entries) the table?

For fear of breaking search.   I would think a preferred method would be to delete and rebuild the index in the admin control panel.

But if that works, the benefit, I think,  would be temporary.

I prefer Arantor's suggestions.

More helpful info on Innodb settings in the user comments here:
https://dev.mysql.com/doc/refman/5.7/en/full-table.html

[shawnb61]

What search method is used, custom index, full text, or none?

[aegersz]

More helpful info on Innodb settings in the user comments here:
https://dev.mysql.com/doc/refman/5.7/en/full-table.html

excellent information and after reading it, I decided that the innodb_log_file_size should be doubled as it was smaller than the database (DB is about 60 MB only).

I will report back if that doesn't do the trick. thanks again.

[aegersz]

What search method is used, custom index, full text, or none?

sorry to sound silly but what specifically are you referring to ? the sort options i allow for my forum or how SQL internal sorts are managed ? (or neither ??)

[Arantor]

i have just moved to InnoDB and i optimise my tables daily.

Don't.

could that be an aggravating factor ?

Yes.

What search method is used, custom index, full text, or none?

SMF fills that particular table in all cases.

[aegersz]

OK, cool. I will turn this off now.

in cron i had it daily and then i realised that I also had the SMF Scheduled task doing it TWICE a day

... but how often to optimise ?

[Arantor]

On InnoDB? I do it maybe once a year.

[aegersz]

really ?

wow ... thanks for helping me out - i have NEVER worked with databases in my life and this SMF venture was the first time i had to every manage one.

Then those damned table crashes (yearly but still aggravating) and solid SMF Support advice led me to implement InnoDB and here i am.

still struggling but with your help, hopefully less so. 

[Arantor]

OK, so here's the reason.

When MySQL puts data into a table, it leaves some space for new records, and for those existing records to be changed.

If you optimise an InnoDB table, it compresses the table and removes all that extra space left for edits, so any time you have to edit a row or add items to the table, it might have to move data around to insert it.

[aegersz]

yes, that makes sense.

i never really came across much information about implementing InnoDB (or didn't look hard enough) so i'm glad to have checked my logs, had this problem, got LOTS of your input (which surprised me) and hopefully now i will have a more robust and easier to maintain database.

I want to achieve 100% DB availability for the next year !

marking SOLVED until it recurrs, if ever.