22618

Inserting millions of records from local to godaddy hosting

Question:

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

Answer1:

Make sure you have MySQL odbc connector is installed: <a href="https://dev.mysql.com/downloads/connector/odbc/" rel="nofollow">https://dev.mysql.com/downloads/connector/odbc/</a>

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.

<ol><li>External Data</li> <li>ODBC</li> <li>Select "link to the data source by creating"</li> <li>select the godaddy_mysql.dns file</li> <li>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.</li> <li>Now you have linked the actual MySQL table in your MS Access database.</li> </ol>

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.

Answer2:

Try a staggered MySQL importer that is run on the GoDaddy server. Like Bigdump: <a href="http://www.ozerov.de/bigdump/" rel="nofollow">http://www.ozerov.de/bigdump/</a>

<a href="http://ccm.net/faq/7342-export-access-database-to-mysql" rel="nofollow">Export from Access</a>, 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!

Recommend

  • AVPlayer won't let me access URL
  • NSWorkspace runningApplications doesn't return my XPC mach service
  • Yahoo finance historical stock price power query returns 301 response
  • How to access data/data files of Android without a terminal on the Android device
  • How to silently drop a request in Tomcat?
  • IllegalStateException “System services not available to Activities before onCreate()”
  • Errno::ECONNREFUSED No connection could be made because
  • How to run requests.get asynchronously in Python 3 using asyncio?
  • String encoding problem on PdoStatement->bindParam()?
  • Send Tor NEWNYM request using Java
  • ORA-12154: TNS:could not resolve the connect identifier specified
  • Argument 5: cannot convert from 'System.Drawing.Image' to 'string' - calling cla
  • twisted.internet.error.ConnectError when run scrapy spider
  • openssl handshake failed
  • Connecting Google Cloud SQL with Wordpress on Google Compute Engine
  • WP7 difficulties binding data to listbox itemssource - won't refresh
  • Install phpMongo Drivers on windows
  • Changing media screen makes div overlay
  • Ruby 1.8.6 Array#uniq not removing duplicate hashes
  • I am receiving HibernateException “No Hibernate Session bound to thread, and configuration does not
  • Configure Spring's MappingJacksonHttpMessageConverter
  • Cloud Code function running twice
  • what makes a request a new request in asp.net C#
  • AppleScript : find open tab in safari by name and open it
  • Check for zero lines output from command over SSH
  • nonblocking BIO_do_connect blocked when there is no internet connected
  • MailKit: The IMAP server replied to the 'EXAMINE' command with a 'BAD' response
  • How can I send an e-mail from a vbs script
  • Accessing IRQ description array within a module and displaying action names
  • AES padding and writing the ciphertext to a disk file
  • How to set my toolbar fixed while scrolling android
  • Display Images one by one with next and previous functionality
  • AT Commands to Send SMS not working in Windows 8.1
  • retrieve vertices with no linked edge in arangodb
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Getting Messege Twice Using IMvxMessenger
  • Change div Background jquery
  • apache spark aggregate function using min value
  • Sorting a 2D array using the second column C++