Previous Thread
Next Thread
Print Thread
Hop To
Joined: Jan 2005
Posts: 35
P
newbie
newbie
P Offline
Joined: Jan 2005
Posts: 35
We just moved to a new dedicated server, all the system files moved fine. When I tried to import the .sql database that was dumped using mysqldump, It hangs on ubbt_posts and in phpmyadmin, shows 0 rows and has this "table is marked as crashed and should be repaired mysql" when you hover over the ubbt_posts section.

The .sql file is about 1.4gb and has 1.3 million posts if I recall.

Is it just too large? What do I do?

Thanks!

PS, UBB is 7.2.2, upgrading after we get this issue squared away.

Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
Are you importing the tables using phpmyadmin or are you doing it direct from the command line? Something that large, you'll probably need to be doing it from the command line if possible.

Joined: Apr 2007
Posts: 3,940
Likes: 1
SD Offline
Former Developer
Former Developer
Joined: Apr 2007
Posts: 3,940
Likes: 1
most likely it has to be done via SSH smile

also set the SSH client to not timeout and send 'noops', so you don't lost that session either while mysql does the import smile

Joined: Jan 2005
Posts: 35
P
newbie
newbie
P Offline
Joined: Jan 2005
Posts: 35
Yes, i am using the command line, the command line i'm using is:

mysql -u ubb -h ltf1636 -p ubb < 021409.sql

I'll make those changes to my putty settings and try again. It doesn't look like putty is timing out though. I can open up another ssh window and kill the mysql process and in the window I was importing, it will show "Terminated" as soon as I hit the kill button. How long should the import process take? I gave up after 5 hours. The server is a GenuineIntel, intel(R) Xeon(R)CPU 3065 @ 2.33GHz with 4 gigs of ram, Red Hat, dedicated.

Thanks

Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
What you can do is have another session going and logged in to MySQL. While the import is going, run this command:

show processlist

You can watch that to see if the import is still running. It can take a while if it's pretty big, which yours is.

Joined: Jan 2005
Posts: 35
P
newbie
newbie
P Offline
Joined: Jan 2005
Posts: 35
Il'l give that a shot, thanks Rick.

Jason

Joined: Jan 2005
Posts: 35
P
newbie
newbie
P Offline
Joined: Jan 2005
Posts: 35
Trying again, looks like it may have paused here? The first show processlist that I execute, it showed a post being imported, now it says this.... It's up to 270 seconds as I post this.

Code
+------+------+--------------------------------------+------+---------+------+----------------------+--------------------------------------------------+
| Id   | User | Host                                 | db   | Command | Time | State                | Info                                             |
+------+------+--------------------------------------+------+---------+------+----------------------+--------------------------------------------------+
| 1819 | ubb  | ltf1636.tam.us.siteprotect.com:54948 | ubb  | Query   |  137 | Repair with keycache | /*!40000 ALTER TABLE `ubbt_POSTS` ENABLE KEYS */ | 
| 1825 | ubb  | localhost                            | ubb  | Query   |    0 | NULL                 | show processlist                                 | 
+------+------+--------------------------------------+------+---------+------+----------------------+--------------------------------------------------+

Last edited by p74vic; 02/15/2009 12:50 AM.
Joined: Jan 2005
Posts: 35
P
newbie
newbie
P Offline
Joined: Jan 2005
Posts: 35
I've done a little research and tweaked the my.cnf file and now it says:

Code
+----+------+--------------------------------------+------+---------+------+-------------------+--------------------------------------------------+
| Id | User | Host                                 | db   | Command | Time | State             | Info                                             |
+----+------+--------------------------------------+------+---------+------+-------------------+--------------------------------------------------+
| 67 | ubb  | ltf1636.tam.us.siteprotect.com:44306 | ubb  | Query   |   35 | Repair by sorting | /*!40000 ALTER TABLE `ubbt_POSTS` ENABLE KEYS */ | 
| 68 | ubb  | localhost                            | ubb  | Query   |    0 | NULL              | show processlist                                 | 
+----+------+--------------------------------------+------+---------+------+-------------------+--------------------------------------------------+

From what I read, repair by sorting is MUCH faster than repair by keycache. Keycache kicks in when sorting fails due to lack of resources. Hopefully this works smile

Joined: Jan 2005
Posts: 35
P
newbie
newbie
P Offline
Joined: Jan 2005
Posts: 35
Ok, problem solved. I checked the mysqld.log file and it showed the 28 out of space error. It looks like the /tmp folder was not allowing mysql to write files to it for temp use because it was full. I solved this by created /mysqltmp with 777 permissions and then adding tmpdir=/mysqltmp to the my.cnf. The next time I ran the import, it completed in under 10 mins.

Hope this threads helps someone in the future!

Jason

Last edited by p74vic; 02/15/2009 5:05 AM.

Link Copied to Clipboard
ShoutChat
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
spam issues
by ECNet - 03/19/2024 11:45 PM
Looking for a forum
by azr - 03/15/2024 11:26 PM
Editing Links in Post
by Outdoorking - 03/15/2024 9:31 AM
Question on barkrowler and the like
by Mors - 02/29/2024 6:51 PM
Member Permissions Help
by domspeak - 02/27/2024 6:31 PM
Who's Online Now
3 members (rootman, Gizmo, Nightcrawler), 562 guests, and 186 robots.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
Los Angeles
Los Angeles
by isaac, August 6
3D Creations
3D Creations
by JAISP, December 30
Artistic structures
Artistic structures
by isaac, August 29
Stones
Stones
by isaac, August 19
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20230217)