UBB.Developers Forums UBB.threads 7 Development How Do I? Importing the Database

I have had to find alternate hosting for my website. Up until now I have had physical access to my machines and haven't had to use FTP/remote management much but this is not the case now. How do I import my existing MySQL database backup into my new MySQL host? I am using PHPMYAdmin but am rather lost at the moment.

If you are talking a database of any size (if you have any tables over 5-10 megs), you should use shell access to export your entire database into a gzip file, and then use shell to import it again.

Much quicker than phpMyAdmin (which I do swear by).

However if it is small - use phpMyAdmin. Could not be easier to do!

and if you don;t have access to shell most hosting companies will do it for you.

hmm-they wont give me shell access but if I send them the file they will put it in. How do I export the db into a zip? I am trying to use PHPMYAdmin on localhost but it doesnt seem to want to save the file.All I get is an empty zip file I have physical access to the machine so I will be doing it directly (winxp) My db is 28 mb

under export you need to save it as a file and it will zip it.

aargh! I been doing this all day.It takes forever (as if its timeing out then when it asks me to save it as a zip file it does but the zip is empty.I have even tried saving it as wwwthreads.sql instead of a zip file (which it does) and it saves it as a 28.1mb file.Then when I try to run it it just takes forever and then says "No MySql Queries"

ask your old ISP if you can get a copy of your database.. give that to the new ISP?

lol-I was my old ISP so yes I have the physical server here.I sent my new host a zipped copy of the mysqldump and I am hoping they can import it via shell.

This is what it tells me when I try to import it:

ERROR 2006 at line 7676: MySQL server has gone away

When I export the db which of these should be checked?

View dump (schema) of database
w3t_addressbook w3t_adminsearches w3t_announcements w3t_banned w3t_bannedemails w3t_boards w3t_cache w3t_calendar w3t_category w3t_censorlist w3t_contentislands w3t_displaynames w3t_favorites w3t_graemlins w3t_groups w3t_iipcache w3t_languages w3t_last w3t_mailer w3t_membersearches w3t_messages w3t_moderators w3t_modnotify w3t_online w3t_permissions w3t_pollmain w3t_polloptions w3t_pollquestions w3t_pollvotes w3t_posts w3t_queries w3t_ratings w3t_registration w3t_reservednames w3t_searchresults w3t_subscribe w3t_usernotes w3t_users w3t_usertitles w3t_version

Structure only
Structure and data
Data only
Export to XML format
Select All / Unselect All
Add 'drop table'
Complete inserts
Extended inserts
Enclose table and field names with backquotes
Save as file ( "zipped" "gzipped" )

What I have done in the past is check STRUCTURE AND DATA.

I then make sure I have my new database named, and then ungzip my file, and load it into the new database.

So my .sql file (or gzip if you prefer) is not creating the database, but it is creating and filling all the tables.

ok I am getting closer-I zipped the actual database (wwwthreads myi etc files) and had him rename them from wwwthredas.* to efs.* etc. But now I am getting this error

Script: /usr/www/efs/ubbthreads/ubbt.inc.php
Line#: 1617
SQL Error: Table 'efs.w3t_Users' doesn't exist
SQL Error #: 1146
Query: SELECT U_Username,U_Password,U_SessionId, U_StyleSheet, U_Status, U_Privates, U_FrontPage, U_Number, U_Banned,U_LastOn,U_OC_Userid, U_SideBar FROM w3t_Users WHERE U_Number = ''


The tabble exists but it is named w3t_Users. Why is it looking for 'efs.w3t_Users'?

Thankyou very much for your help

Honestly not sure. Question though. What reason did you have the files renamed?

Possibly something in that procedure made the error.

It was a a requirement of my host. Since this is a rental server it is their policy to name the db to associate it with the account I presume. I allready asked to have it renamed and was denied.

[]We can't change the database name, it has to match the identifier for your server. If you are importing an old database in the form of an .sql file, you can change that information right near the top of the file. [/]

They are being accomodating though so I am not complaining at all.

This has got to be a path issue either with IIP or UBBThreads and not the actual database I think. Could it be a permissions issue since I went from Windows to Unix? I uploaded my entire directory as is.

>
>As I suspected before, this is because the case for your mysql files is different from what your script appears to expect.
>
>mysql> SELECT U_Username,U_Password,U_SessionId, U_StyleSheet, U_Status,
> -> U_Privates, U_FrontPage, U_Number, U_Banned,U_LastOn,U_OC_Userid, U_SideBar
> -> FROM w3t_Users limit 1;
>ERROR 1146: Table 'efs.w3t_Users' doesn't exist
>
>mysql> SELECT U_Username,U_Password,U_SessionId, U_StyleSheet, U_Status,
> -> U_Privates, U_FrontPage, U_Number, U_Banned,U_LastOn,U_OC_Userid, U_SideBar
> -> FROM w3t_users limit 1;



egad! is this a result of going from Windows to Unix? How do I fix this without rewriting Threads?

okay in my config file I can't say for instance

$config['dbtype'] = "mysql";
$config['dbserver'] = "localhost";
$config['dbuser'] = "mysqluser";
$config['dbpass'] = "yaddayadda";
$config['dbname'] = "ubbthreads";

I have to say

$config['dbtype'] = "mysql";
$config['dbserver'] = "localhost";
$config['dbuser'] = "mhomedirectory_mysqluser";
$config['dbpass'] = "yaddayadda";
$config['dbname'] = "mhomedirectory_ubbthreads";

so I would look at your config.php in your includes directory and change the lines to

$config['dbtype'] = "mysql";
$config['dbserver'] = "localhost";
$config['dbuser'] = "efs_mysqluser";
$config['dbpass'] = "yaddayadda";
$config['dbname'] = "efs_ubbthreads";

Let me clarify my mistake which I am realizing now- I imported a windows MYSql database (which is probably ok) and I also zipped and ftp'd my Windows installed UBBThreads installation
to a Linux server instead of installing the Linux version as my board is hacked up.My board is now looking for w3t_Users and what I have is w3t_users and unfortunately all my tables are lower case as the latter and we all know that Linux is not as forgiving as Windows (except in the blue screen case)

So now my question is this.Do I need to actually grab the Linux version of my board and do a proper install and will the database work at that point or is there another way.

I am sure this is the problem and also why my MySQLDump .sql
file would not function properly because I created the tables with the windows install as well.

My mess-Thanks for the help again

If you are moving Windows to Linux, you'll need to rename all the tables. Omegatron wrote a "how to" for this:
https://www.ubbdev.com/forum/showflat.php/Cat/0/Number/70437

Well I am getting closer thankyou so much.Now I have a variety of errors to deal with some of which may be permissions. For starters do you know what this error is from?
Script: /usr/www/efs/ubbthreads/pals/pollpal.php
Line#: 85
SQL Error: Got error 127 from table handler
SQL Error #: 1030
Query: SELECT B_Poll,B_Replies,B_Number,B_Board,B_Posted, B_Subject FROM w3t_Posts WHERE B_Number = B_Main AND B_Approved = 'yes' AND B_Board = 'efs' AND B_Poll != '' ORDER BY B_Posted DESC LIMIT 0,1

"SQL Error: Got error 127 from table handler"

Error 127 is generally a corrupt table.

REPAIR TABLE w3t_Posts

should fix it.

ooh that is a scary one to use on that particualar table

We have a list of errors and solutions posted here for reference:
http://www.ubbcentral.com/boards/showflat.php/Cat/0/Number/35663

Thanks Josh that will help.

This error was not on that list though

The server encountered an internal error and was unable to complete your request.

Error message:
Premature end of script headers: ubbthreads.php

"SQL Error: Got error 127 from table handler" is in that post
Towards the bottom of the error section.

[]
SQL ERROR: Unable to do query: Blah blah blah Got error 127 from table handler

Error 127 = Unknown Error / Record File Crashed


You can generally look at what table or tables it was trying to Query and run the Repair Table command for that table.

For example, if the query throwing the error is the w3t_Posts table, you can try entering this SQL Query:

REPAIR TABLE w3t_Posts


If this does not work, you may need to ask your webhost to run myisamchk on your database. You can also try dumping and restoring the database with the mysqldump command.
[/]

I think you mis-understood. This is a new error that I get. The previous one was fixed by repairing w3t_Posts

This error I get when trying to login or go to the Main Index

Server error!

The server encountered an internal error and was unable to complete your request.

Error message:
Premature end of script headers: login.php

If you think this is a server error, please contact the webmaster.
Error 500
emeraldforestseattle.nuclearfallout.net
Thu Oct 7 21:02:21 2004
Apache/2.0.50 (Unix)

Ah- I understand now. You got by the post table error.

Not sure why you're getting the new error.
I'd try re-uploading clean login.php login.tmpl and login.php language files, to make sure there's no corruption there.

Well I tried toing a REPAIR TABLE on the whole lot of them and that reduced my db from 30mb to 4mb-Thank God for backups

Any idea what could have caused this?
Script: /usr/www/efs/ubbthreads/ubbt.inc.php
Line#: 491
SQL Error: Can't open file: 'w3t_Online.MYD'. (errno: 145)
SQL Error #: 1016
Query: REPLACE INTO w3t_Online (O_Username,O_Last,O_What,O_Board,O_Read,O_Type,O_Extra,O_Agent,O_Refer) VALUES ('-ANON-208.54.15.1','1097282232','index','','','a','','Mozilla/5.0 (Windows; U; Windows NT 5.0; rv:1.7.3) Gecko/20041001 Firefox/0.10.1','')


Now when I go to the tables It says this about that particular one

w3t_Online Browse Select Insert Properties Drop Empty in use

and and seems the the file w3t_Online.MYD is no longer in the db folder. All I did was open the entrance page

SELECT FROM w3t_Users WHERE U_SQL-Hell > '1';
1 results returned
ChAoS

I am getting closer- I had the host replace the files again and now I get this
Table 'w3t_Online' is read only

So I emailed him back to change the permissions on the db.I also re-uploaded UBBThreads in ASCII (except images) just in case I didn't thr first time. Now I just wait

I am still getting this
The server encountered an internal error and was unable to complete your request.

Error message:
Premature end of script headers: index.php

If you think this is a server error, please contact the webmaster.

At this point I am just going to try a fresh install and then maybe restoring a backup from the control panel. I am about ready to throw my hands up on this one. Thanks for all your help everyone

Well I am reading along, but the latest errors you are getting is like nothing I have ever run into.

But best of luck!

I am going to try a fresh install of the Linux version.Maybe the fact that I zipped my Windows installed UbbThreads folder is the problem.

How do I restore a db backup from the new control panel in 6.5?

No restore function for the database only a backup option you will need to manually do the import thru phpMyAdmin.

Yeah, generally if your database is d0rked up, you can't get to the admin panel anyway.

This is really frustrating.The board runs fine on my windows machine.I can create and restore backups just fine but when I try to import it to this new host it just freezes.He has even tried to import it via shell with the same results.

I think this may be a permissions issue because I can install a fresh copy of 6.5 that uses the database but has none of my stylesheets and look but when I do an upgrade I get the server 500 error now.

Error message:
Premature end of script headers: ubbthreads.php

And my host referred me to this

http://64.4.53.250/cgi-bin/linkrd?_lang=...%2dhosting%2316


I have followed these instructions to the letterhttp://www.ubbcentral.com/support/docs/ubbthreads/UBBthreads_Unix_install.html...are these instructions up to date?

Chaos tell ya what shoot one of us a PM and we will try and help ya out. we will need your mysql password and username as well as ftp rights so we can check permissions.. you can always change them later

I got it up-I did the fresh install of 6.5 and had the host copy the db files directly,then brought in my templates and configs and everything is working. One problem I am having though since my domain pointers arent working yet. Everyones avatards(sp hehe) are still trying to use emeraldforestseattle.com and right now I am currently at http://emeraldforestseattle.nuclearfallout.net I cant find out where to change this even though I have set the correct image/avatar path in the control panel.This is causing slow page loads and pegging the cpu at 100%

Try this ChAoS...

UPDATE w3t_Users
SET U_Picture = REPLACE(U_Picture, 'www.emeraldforestseattle.com','emeraldforestseattle.nuclearfallout.net');

yeah what twisty said