MySQL Importing Problems

[lizardqing]

I am trying to import an existing database for a phpbb2 forum onto Hostmonster.I have all ready done one the exact same way and have it working fine but now the second one keeps giving me errors. This is the latest one

SQL query: Documentation

SELECT *
FROM Array
WHERE CONCAT_WS( "-", cat_id, cat_title, cat_order ) = "4"
ORDER BY cat_id, cat_title, cat_order
LIMIT 0 , 30

MySQL said: Documentation
#1146 - Table 'scalefir_phpb2.Array' doesn't exist

Any ideas on how to get this working right before I can not access the old database anymore? Thanks.

[charlesgan]

make sure you export the first database right. And check the complete insert option. Output to text file.

from hostmonster phpmyadmin >import. Import the whole file.

i do this important many times. should works :)

[lizardqing]

Thanks I'll give it a whirl.

[lizardqing]

Just looked and there is no option for complete insert.

And when I output it to a text file, do I save it as .txt or the sql extension?

[lizardqing]

Found the complete insert. But all it winds up doing is coming up with a blank page after working forever. Guess there is an issue with the host, which is why I am needing to move it.

[lizardqing]

Getting this error now.


Error

There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

ERROR: Unclosed quote @ 92
STR: '
SQL:
INSERT INTO phpbb_search_wordlist (word_text, word_id, word_common) VALUES('�', '20795', '0');


SQL query:

INSERT INTO phpbb_search_wordlist (word_text, word_id, word_common) VALUES('�', '20795', '0');

MySQL said: Documentation
#1062 - Duplicate entry '?' for key 1

-----------------------

Error

SQL query: Documentation

SELECT *
FROM Array
WHERE CONCAT_WS( "-", word_text, word_id, word_common ) = "?"
ORDER BY word_text, word_id, word_common
LIMIT 0 , 30

MySQL said: Documentation
#1146 - Table 'scalefir_phpb2.Array' doesn't exist



I have exported through the forum admin. I really at a loss.

[theprogrammer]

I'm gonna tell ya what to do "lizardqing" :)

just download this hack and add it to your phpbb2 forum,
http://www.phpbb.com/mods/db/index.php?i=misc&mode=display&contrib_id=1511&sid=696d0a65e937e1e8cfcc5b0d8a3bcd15

and backup all your tables through the new option in the control panel, except:

phpbb_post_text
phpbb_privmsg_text
phpbb_search_wordlist
phpbb_search_wordmatch << this could be very very big if your forum is old. (it's important for the search feature for all of your posts)

for these tables named above, you should back them up one by one, because they are the biggest tables in the database. and if your old hosting service accept zipping files, choose before backingup "as a zip file" because it will save a lot of time downloading those files.

the new module will save all the tables as files in your old hosting space, and you can download them via the control panel or via ftp program if some files were too big to download it by http.

next, you can import them to your new server, with two ways,

1- from phpmyadmin (by importing the database tables, and don't forget to make a database first with the same old name). but if you couldn't because of the size of some tables, then do it via the second way below:

2- download a small php program called "bigdump" and change the values in it as you want then upload it to your new host (make sure that the character set is OK)

download from here http://www.alqa3a.org/bigdump.zip

upload the sql big files via ftp to your new host in the same directory of bigdump.php, and open bigdump.php to start importing data, also, everytime you will import one to the database you have to change the $filename value in the bigdump.php to the same sql file name you want to import it, then upload it again.

any further question just ask me :)

Yours,
The Programmer

[lizardqing]

OK, I will see if I can figure all that out. Lovely GA heat fried my brain today so I a a bit slow tonight.

The full backup size of the database is only about 11 MB , but I don't know if that is considered big or not.


First problem, I can't make the database the same name. The accounts have different top level names. Or does just the name after the _ have to be the same?

[theprogrammer]

11 MB is not too big, I got more than 60!

anyway, don't try to download the backup through the backup tool integrated with phpbb, because it's not working properly ..

export the whole database by the hack I gave you, or by phpmyadmin

and import them by phpmyadmin, just build a database with whatever its name and choose it, then press on "import" link, choose the sql files you download them and import them with proper Characterset.

Yes, you can name the database with whatever you want, but don't forget to change the name of database in config.php file in phpbb side by side to changing the user name and password as you chose and link to your new database.

Regards
The Programmer

[lizardqing]

Where do i install the hack to? So far I have not been able to get it to work.

[sjlplat]

Are you trying to import the database through phpBB2?

I normally move my databases around with phpMyAdmin:

To Export:

- Go to phpMyAdmin
- Select your database from the drop-down menu on the left
- Select Export from the menubar at the top
- Check the Save as file box
- Press the Go button
- Save your file locally

To Import:

- Create your new database with no tables. Make sure you have created the database with the appropriate user and password to match your phpBB installation.
- Go to phpMyAdmin
- Select your database from the drop-down menu on the left
- Select Import from the menubar at the top
- Click the Choose button and select your sql file
- Click Go to add your tables to the new database

[lizardqing]

Well now I get this one.

Error

SQL query:

CREATE TABLE phpbb_users(
user_id mediumint( 8 ) NOT NULL ,
user_active tinyint( 1 ) DEFAULT '1',
username varchar( 25 ) NOT NULL ,
user_password varchar( 32 ) NOT NULL ,
user_session_time int( 11 ) NOT NULL ,
user_session_page smallint( 5 ) NOT NULL ,
user_lastvisit int( 11 ) NOT NULL ,
user_regdate int( 11 ) NOT NULL ,
user_level tinyint( 4 ) ,
user_posts mediumint( 8 ) unsigned NOT NULL ,
user_timezone decimal( 5, 2 ) DEFAULT '0.00' NOT NULL ,
user_style tinyint( 4 ) ,
user_lang varchar( 255 ) ,
user_dateformat varchar( 14 ) DEFAULT 'd M Y H:i' NOT NULL ,
user_new_privmsg smallint( 5 ) unsigned NOT NULL ,
user_unread_privmsg smallint( 5 ) unsigned NOT NULL ,
user_last_privmsg int( 11 ) NOT NULL ,
user_emailtime int( 11 ) ,
user_viewemail tinyint( 1 ) ,
user_attachsig tinyint( 1 ) ,
user_allowhtml tinyint( 1 ) DEFAULT '1',
user_allowbbcode tinyint( 1 ) DEFAULT '1',
user_allowsmile tinyint( 1 ) DEFAULT '1',
user_allowavatar tinyint( 1 ) DEFAULT '1' NOT NULL ,
user_allow_pm tinyint( 1 ) DEFAULT '1' NOT NULL ,
user_allow_viewonline tinyint( 1 ) DEFAULT '1' NOT NULL ,
user_notify tinyint( 1 ) DEFAULT '1' NOT NULL ,
user_notify_pm tinyint( 1 ) NOT NULL ,
user_popup_pm tinyint( 1 ) NOT NULL ,
user_rank int( 11 ) ,
user_avatar varchar( 100 ) ,
user_avatar_type tinyint( 4 ) NOT NULL ,
user_email varchar( 255 ) ,
user_icq varchar( 15 ) ,
user_website varchar( 100 ) ,
user_from varchar( 100 ) ,
user_sig text,
user_sig_bbcode_uid varchar( 10 ) ,
user_aim varchar( 255 ) ,
user_yim varchar( 255 ) ,
user_msnm varchar( 255 ) ,
user_occ varchar( 100 ) ,
user_interests varchar( 255 ) ,
user_actkey varchar( 32 ) ,
user_newpasswd varchar( 32 ) ,
user_login_tries smallint( 5 ) unsigned NOT NULL ,
user_last_login_try int( 11 ) NOT NULL ,
user_show_quickreply tinyint( 1 ) DEFAULT '1' NOT NULL ,
user_quickreply_mode tinyint( 1 ) DEFAULT '1' NOT NULL ,
user_open_quickreply tinyint( 1 ) DEFAULT '1' NOT NULL ,
PRIMARY KEY ( user_id ) ,
KEY user_session_time( user_session_time ) ,
KEY user_sig( user_sig ) ,
KEY user_sig_2( user_sig )
);

MySQL said: Documentation
#1170 - BLOB/TEXT column 'user_sig' used in key specification without a key length



So I think at this point I am just going to say to hell with and forget about the database. Chalk it up to one last screwing from the old host. It is taking to much time that I can be spending doing something else to be getting nothing but errors. Thanks for you help anyway though.

[lizardqing]

Are you trying to import the database through phpBB2?

I normally move my databases around with phpMyAdmin:

To Export:

- Go to phpMyAdmin
- Select your database from the drop-down menu on the left
- Select Export from the menubar at the top
- Check the Save as file box
- Press the Go button
- Save your file locally

To Import:

- Create your new database with no tables. Make sure you have created the database with the appropriate user and password to match your phpBB installation.
- Go to phpMyAdmin
- Select your database from the drop-down menu on the left
- Select Import from the menubar at the top
- Click the Choose button and select your sql file
- Click Go to add your tables to the new database

That is what I have done and I get the errors. Get them with both an empty database and one that has the phpbb empty tables there after installing the forum.

[r2b2]

What are the version of MySql on each of the two sides? Maybe one is running a newer/older version which is causing your issue?

[lizardqing]

If these are the versions, then

Old Host - phpMyAdmin 2.6.4-pl2

Host Monster - phpMyAdmin - 2.10.0.2

[r2b2]

I was really talking about the MySql versions... I believe you can see this on the PhpMyAdmin main page?

[lizardqing]

That would make sense.

Old host - MySQL 4.1.11

Hostmonster - 4.1.21

Sometimes can't see the forest for the trees.

[sjlplat]

Ok, so you installed the forum before importing the tables?

Did you delete the new database tables before importing the old ones?

[lizardqing]

Tried both ways. Get the errors no matter. I guess there is just something corrupted with the export from the old host.

[r2b2]

Just had a look and the SQL isn't valid as you need to specify how many characters to use for a key when using a column of type TEXT (e.g. KEY user_sig( user_sig(999) )

Maybe the PHPMyAdmin export can't handle creating this information?

Perhaps if you find the script that is used to create new installations of phpBB2 this may tell you what values to stick in the SQL query?

(Hope that all makes sense :D )

[lizardqing]

Not a bit. I have decided to just go ahead and start over. To much time trying to figure it out. Just chalk it up as one last kick from the old host. Now if I could just get my computer to recognize the new home of the site. Did for a bit while I was setting up the forum and then all of a sudden reverted to pulling up the old site. Never had this many headaches. Thanks for everyones advice though, wish I could have gotten something to work.