I'm trying to insert about 8 million records from an access database to a mysql database in GoDaddy.
I built a desktop java app to manage the inserts chunking 5000 records each time. I use Laravel 5.1 to manage the inserts in the server. So, basically, the java app send a request to a laravel php route, then it take care of inserting directly in my MySQL database.
Edit: people will insert data directly into the Access, so I have to watch the MDB file for changes. This is why I can't just export from Access and import into the MySQL.
The first part of records inserts successfully, but than when I send another request, I get this error:
2015-10-28 10:43:57.844 java[3475:280209] Communications error: <OS_xpc_error: <error: 0x7fff7298bb90> { count = 1, contents =
"XPCErrorDescription" => <string: 0x7fff7298bf40> { length = 22, contents = "Connection interrupted" }
}>
Got xpc error message: Connection interrupted
org.apache.http.NoHttpResponseException: api.mydomain.com:80 failed to respond
Make sure you have MySQL odbc connector is installed:
https://dev.mysql.com/downloads/connector/odbc/
create your DNS to your MySQL server (you can do this via odbc manager or just with a notepad)
your DNS file will look like this:
[ODBC]
DRIVER=MySQL ODBC 5.3 Unicode Driver ' check what driver is installed
UID=username to the server
PORT=3306
PWD= password to the server
DATABASE=dbname
SERVER=serverip/name
Save the DNS file somewhere lets call it GoDaddy_MySQL.dns
Open up you ACCESS database.
- External Data
- ODBC
- Select "link to the data source by creating"
- select the godaddy_mysql.dns file
- if all your connection details are correct Access will show you tables & views to import. Click the tables you would like to upload data from your Access dtabase.
- Now you have linked the actual MySQL table in your MS Access database.
All you need to do is uploading data from your local table to your linked table simply by:
you can chunk your upload by using the TOP keyword. if you add a where condition with (is not already on the linked table) you can always upload new records automatically to your MySQL server.
if you are and will still use your Access database you can also switch from local to linked tables so all new entry will automatically uploaded to your godaddy server.
Pseudo:
insert into linked_table select top 5000 from your local table where local_records_are not in linked table.
Try a staggered MySQL importer that is run on the GoDaddy server. Like Bigdump: http://www.ozerov.de/bigdump/
Export from Access, import to MySQL. It's run directly on the godaddy server, so the data is already on the server and then just read. I've used this numerous times on GoDaddy for large imports.
Cheers!
