Hosted Site DB import into Owned Site

[christinasc]

Does anyone know how to go about doing an import from the Hosted Site DB into the Owned Site?

Can this be done easily with a direct import using phpmyadmin?

Please point me in the right direction. Thanks.

[supportskins]

Hi,
This can be simple or tedious process depending upon your server configuration. Do you have root access to your server. The back up file generated by Kayako is in .tar.gz format and depending upon your database size, it can take long hours to import the database if the it is too big in size.

Regards,

SupportSkins Admin

[christinasc]

Unfortuantely my DB is located behind a firewall i don't have root access to that. I guess i'll have to ask the hosting company for help on that.

[supportskins]

How big is your database size? We can carry out the transfer if you are interested.

[bear]

Originally Posted by supportskins

The back up file generated by Kayako is in .tar.gz format and depending upon your database size, it can take long hours to import the database if the it is too big in size.

This is untrue. If the db size was so large it would take "hours" to import, the import would time out and fail anyway. If you have a phpMyAdmin export that's under a few MBs in size, and were able to export it from that interface, it should take only a few minutes at most to import.

I can try to assist you if you get in touch with me via PM.

[supportskins]

Just to clarify, if your PHP settings max_execution_time is set with a larger value it will not time out instantly. This can be faster with root access as you can import the database using wget command.

[bear]

Originally Posted by supportskins

This can be faster with root access as you can import the database using wget command.

You mean retrieve the db using wget, don't you? Wget (if enabled for underpriveleged users) doesn't do anything but fetch.

[supportskins]

Yes but isn't fetching the large database the only "real" when migrating from hosted to Owned? Anyhow, with no root access phpMyAdmin is the other possible option.

[bear]

It's not the only issue, but with shell (doesn't need to be root in most cases) it's simpler. If not shell, there are also scripts that can be used if PhpMyAdmin fails (db's can be too large for this interface as well).
At any rate, the discussion is academic, since we don't know if size is an issue here.

[christinasc]

My DB size is only 7MB gzipped and tarred. I tried uploading it via the PHPAdmin interface but i guess it never made it in or timed out.

How do you know for sure your upload is done correctly? Do you get all the tickets and users to show up automatically?

Originally Posted by supportskins

How big is your database size? We can carry out the transfer if you are interested.

[bear]

If it worked, you should be able to see the tables in phpMyAdmin. Imports are kind of slow using that interface, so make sure you allow enough time for it to complete. If the information hasn't changed from the previous install, and you're using the same files, you should be able to access the installation just as you did the last one.

I don't suppose you have shell access with your account do you?
If not, would your host import via shell on your behalf (we regularly do this for clients)?

[christinasc]

So I tried importing the hosted DB export into our owned version and it gave me this error:


> mysql --user=dbXXXXX --password=XXXX --host=XXXXXXX dbXXXXX < backup_07b2943b7960.sql

ERROR 1064 (00000) at line 20: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT CHARSET=latin1' at line 7

=======

I downloaded it directly from the kayako site for hosted backups, and unzipped and untarred it - by doing gunzip and tar -xvpf.

I also did a mysqldump from our kayako owned license database and then tried to import it, which worked with fine -- no errors, so i'm sure that importing work.

Can anyone give any suggestions why i can't import Kayako's hosted backup sql db? It seems like there is a problem with the default sql file generated by kayako's hosted backup.

[bear]

Could be mySQL version differences. What versions of mySQL are on each server?

Two fixes are to edit the sql file to remove instances of "DEFAULT CHARSET=latin1", or to dump the database using compatibility mode...though this solution comes with it's own bug...it's been known to ignore auto-increment flags in tables, breaking things worse.

Best bet is to edit out the CHARSET lines....a good search and replace program should do it.

[christinasc]

I think parts of sql tar.gz file from the kayako hosted version is beyond my control - it's not entirely text -some of it is binary.

No idea what kayako is actually running on their servers. I submitted a ticket but still haven't heard back from them in a day.

on my server, i have mysqladmin Ver 8.41 Distrib 5.0.16, for pc-linux-gnu on i686

Originally Posted by bear

Could be mySQL version differences. What versions of mySQL are on each server?

Two fixes are to edit the sql file to remove instances of "DEFAULT CHARSET=latin1", or to dump the database using compatibility mode...though this solution comes with it's own bug...it's been known to ignore auto-increment flags in tables, breaking things worse.

Best bet is to edit out the CHARSET lines....a good search and replace program should do it.

[bear]

Originally Posted by christinasc

I think parts of sql tar.gz file from the kayako hosted version is beyond my control - it's not entirely text -some of it is binary.

That might be images in the KB or something like that. That would appear as odd long strings.

on my server, i have mysqladmin Ver 8.41 Distrib 5.0.16, for pc-linux-gnu on i686

MySQL version 5.0.16 is very new. I'd suggest the cause is most likely Kayako running an older version and the dumps aren't fully compatible.
Have you tried editing the info out as suggested (on a copy, of course)?

To see what version is on Kayako's server, go into your admin area, and in the left menu choose "diagnostics". Click "mysql info", and scroll down that page until you see the word "version". That's your mySQL version...