« Posts under Programming

Kayako V3 to V4 Importing

December 7, 2011 / posted in Programming, SysAdmin / Comments Off

Kayako has a new version of their ticket system available. We have a customer that uses this system extensively. The database format for the new version of Kayako isn’t compatible with the older database schema, so you have to run an import script that processes the old database information and imports it into the new database for V4.

The problems begin when you start to deal with large installations of Kayako. This one customer has over 4.2 million rows of information in their current Kayako installation (this is based on what the import script is reporting). Kayako’s importing script is said to have some issues with memory leaks and memory utilization with large imports and they have included a command line option to help get around this issue.

./index.php /Base/Import/Version3/<limit>
is the number of data loops to be performed for each run. Overall, not a bad approach to work around some limitations. I personally would have gone a different route and spent some serious amounts of time on memory optimization and clean up in the import scripting, but that is just me.

My problem with this process is how the import script actually works. Each time you run the import command the system prompts you for the database information of the previous installation.

====================
Version3 Import
====================
Database Host: localhost
Database Name: database
Database Port (enter for default port):
Database Socket (enter for default socket):
Database Username: username
Database Password: password

To my knowledge, there is not any way currently to pass this information via the command line or settings file. It must be manually entered each and every time you run the import script. You can imagine that for 4.2 million records, this would get rather tiresome after the second or third time.

So… I wrote a script that uses expect to get around it:

#!/usr/bin/expect

set dbhost "localhost"
set dbuser "user"
set dbpass "password"
set dbname "database"

spawn /path-to-the-new-kayako-install/console/index.php /Base/Import/Version3/50

expect "Database Host:"
send "$dbhost\r"

expect "Database Name:"
send "$dbname\r"

expect "Database Port (enter for default port):"
send "\r"

expect "Database Socket (enter for default socket):"
send "\r"

expect "Database Username:"
send "$dbuser\r"

expect "Database Password:"
send "$dbpass\r"

interact

This will pass the values to the executed script filling in the values as required, and then allow the script to execute until it is complete. The interact line was the part I needed to find with some trial and error.

So there you go… use that to work around the dumb requirements of having to type that information in every time.