Tag Archive for MySQL

MySQL client ran out of memory

December 31, 2009 Business Intelligence, Development No comments

I have been test­ing a num­ber of BI visu­al­iza­tion tools lately, and to do this I decided to use a set of Click­stream data that I have, which is approx­i­mately 6,500,000 rows. When I began load­ing the data into whichever tool I was test­ing I got an error that said “MySQL client ran out of mem­ory”. I am using the MySQL 5.1 ODBC Dri­ver. My con­nec­tion looks as fol­lows.
MySQL Connection
Ini­tially I checked the “Allow big result sets” in the con­nec­tion, how­ever this did not do any­thing. The only solu­tions that worked was to add the “Option=1048576″ to my con­nec­tion string.

Can’t connect to MySQL server on…

April 20, 2009 Business Intelligence, Development, Open Source No comments

I recently encoun­tered a prob­lem with a fresh MySQL install, whereby I could not con­nect to the server from a remote machine. After some dig­ging I found that there are a num­ber of things that you need to do to allow remote connections.

  1. Allow remote con­nec­tions in the my.conf. On *nix instal­la­tions this is usu­ally sit­u­ated in /etc or /etc/mysql. There is a line that says bind-address = 127.0.0.1. This is basi­cally say­ing that it will only lis­ten for con­nec­tions on the local­host. Com­ment this line out with a #
  2. Restart MySQL. /etc/init.d/mysql restart
  3. Grant per­mis­sions. To grant per­mis­sions to all data­bases for a par­tic­u­lar user on your net­work for exam­ple, you would use grant all priv­i­leges on *.* to username@”%” iden­ti­fied by “pass­word”; This says that the user user­name, on all remote hosts (iden­ti­fied by the %) will be granted access.

Backing up and restoring a mysql database

April 19, 2009 Development No comments

I recently had a prob­lem whereby I needed to down­load a data­base from a shared host­ing envi­ron­ment to a pri­vate server for report­ing pur­poses. The job needed to run daily. The way I went about it is as follows:

  1. Cre­ate a cron jobe entry on the shared server to cre­ate a mysql­dump extract of the data­base. The syn­tax to do this is mysql­dump –user=[username] –password=[thepassword] [database_name] | gzip > /home/bemobile/database_backups/bkup.sql.gz
  2. Now on the report­ing machine, I cre­ated a sep­a­rate shell script to down­load the archive file. The script looks as follows:
  3. #!/bin/bash –vx
    ftp –i –n [ftp server]«END_SCRIPT
    quote USER [user­name]
    quote PASS [pass­word]
    cd database_backups.
    lcd /usr/local/be-mobile/bi/data.
    prompt off
    get bkup.sql.gz /usr/local/be-mobile/bi/data/bkup.sql.gz
    bye
    END_SCRIPT
    gzip –dvf /usr/local/be-mobile/bi/data/bkup.sql.gz
    mysql –user=[username] –password=[password] [data­base name to be restored] < /usr/local/be-mobile/bi/data/bkup.sql

To add the script to the cron, I edit the /etc/crontab file (for the sys­tem, not user spe­cific) with the fol­low­ing entry: 15 0 * * * /usr/local/be-mobile/bi/scripts/database_download.sh This means that it will run the script every­day at 12:15 in the evening.

Thats it, Bobs your uncle. It could really be a bit cleaner, with bet­ter error han­dling and log­ging, but it is sim­ple, easy & it works.