Can't create table

[greenspan]

Hi. I hope my post is in the right forum. Anyway, my site went down and got my backups from my host. I restored the files and DB. My site is running fine, however, I'm now getting frequent "vBulletin Database Error!" in my admin Email. Here's the message:

============================
Database error in vBulletin 3.8.3:

Invalid SQL:

CREATE TABLE IF NOT EXISTS taggregate_temp_1250002560 (
threadid INT UNSIGNED NOT NULL DEFAULT '0',
views INT UNSIGNED NOT NULL DEFAULT '0',
KEY threadid (threadid)
) ENGINE = MEMORY;

MySQL Error : Can't create table 'taggregate_temp_1250002560' (errno: 13)
Error Number : 1005
Request Date : Tuesday, August 11th 2009 @ 10:56:07 PM
Error Date : Tuesday, August 11th 2009 @ 10:56:07 PM
Script : http://www.mysite.com/forums/cron.php?rand=1250002564
Referrer : http://www.mysite.com/forums/showthread.php?t=2832
IP Address : 202.21.227.84
Username : Unregistered
Classname : vB_Database_MySQLi
MySQL Version :
============================
I'm guessing that it's a permission issue. The backup that I got was in the form of myd, myi and frm files. I pasted them into var/lib/mysql/<database> and has the permission of 755. I tried making it 660 but got more database errors. Can someone point me to the right direction? Thanks in advance.

[Marco van Herwaarden]

OS error code 13: Permission denied

MySQL can not create temporary tables. Please consult your host for a solution.

PS Transfering the databae by moving files is not the recommended method and can cause many issues like the one you are running into now. Was MySQL stopped (source & target) when the files where moved?

[greenspan]

Thanks Marco for the quick reply.

I just dropped my host a ticket regarding this problem. I'm sorry I cannot understand your question about MySQL getting stopped. But what I did was I created a new database then a folder got shown in var/lib/mysql. I then pasted the database files there.

[Marco van Herwaarden]

You can not just copy the files of a database while MySQL is running. MySQL should be stopped first. Also the files need to be tranfered without any conversions to a similar Operating System as the source server is using.

The only reliable method of backing up and restoring a database is with shell access via telnet or ssh. This is because backing up with a PHP script like that in the Admin CP or phpMyAdmin can result in PHP timeouts errors and an incomplete backup file. Please see the instructions here:

Backup:
http://www.vbulletin.com/docs/html/maintenance_ssh_backup

Restore:
http://www.vbulletin.com/docs/html/maintenance_ssh_restore

P.S. If you don't have shell access, some people have also reported success with these scripts:

MySQLDumper:
http://www.mysqldumper.de/en/index.php

MySQLHotxcopy:
http://www.vbulletin.com/forum/showthread.php?t=134821&highlight=mysqlhotcopy

Bigdump:
http://www.ozerov.de/bigdump.php

[greenspan]

Thanks Marco for the tips. Yes, I have ssh and I'll make use of that next time. However, I'm still having a problem with the creation of tempo tables. My host said that they've assigned all privileges for the database user in my database but I'm still encountering the DB error. Can you tell me which specific area should I check? Maybe my host is missing something. Thanks.

[Marco van Herwaarden]

You will need to check where MySQL creates it's temporary files, this is a setting in the MySQL configuration i think. Then check if MySQL has permissions in that directory (and if older files exist, permission to overwrite them).

Is the error message still exactly the same?

[greenspan]

Yes. The error is the same. Like this one:
MySQL Error : Can't create table 'taggregate_temp_1250265360' (errno: 13)

I relayed your response to my host. Hopefully, they can help. Thanks.

[greenspan]

My host said that they did the changes, however, I'm still encountering the error. Just a question, are the tempo folders really stored as files in harddisk and not in the memory?