Archive for Development
MySQL client ran out of memory
I have been testing a number of BI visualization tools lately, and to do this I decided to use a set of Clickstream data that I have, which is approximately 6,500,000 rows. When I began loading the data into whichever tool I was testing I got an error that said “MySQL client ran out of memory”. I am using the MySQL 5.1 ODBC Driver. My connection looks as follows.
Initially I checked the “Allow big result sets” in the connection, however this did not do anything. The only solutions that worked was to add the “Option=1048576″ to my connection string.
Can’t connect to MySQL server on…
I recently encountered a problem with a fresh MySQL install, whereby I could not connect to the server from a remote machine. After some digging I found that there are a number of things that you need to do to allow remote connections.
- Allow remote connections in the my.conf. On *nix installations this is usually situated in /etc or /etc/mysql. There is a line that says bind-address = 127.0.0.1. This is basically saying that it will only listen for connections on the localhost. Comment this line out with a #
- Restart MySQL. /etc/init.d/mysql restart
- Grant permissions. To grant permissions to all databases for a particular user on your network for example, you would use grant all privileges on *.* to username@”%” identified by “password”; This says that the user username, on all remote hosts (identified by the %) will be granted access.
Backing up and restoring a mysql database
I recently had a problem whereby I needed to download a database from a shared hosting environment to a private server for reporting purposes. The job needed to run daily. The way I went about it is as follows:
- Create a cron jobe entry on the shared server to create a mysqldump extract of the database. The syntax to do this is mysqldump –user=[username] –password=[thepassword] [database_name] | gzip > /home/bemobile/database_backups/bkup.sql.gz
- Now on the reporting machine, I created a separate shell script to download the archive file. The script looks as follows:
- #!/bin/bash –vx
ftp –i –n [ftp server]«END_SCRIPT
quote USER [username]
quote PASS [password]
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] [database 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 system, not user specific) with the following entry: 15 0 * * * /usr/local/be-mobile/bi/scripts/database_download.sh This means that it will run the script everyday at 12:15 in the evening.
Thats it, Bobs your uncle. It could really be a bit cleaner, with better error handling and logging, but it is simple, easy & it works.
Twitter Down
Twitter Down
I went to twitter today, and for the first time I got the “Twitter is over capacity” message. I heard they had many problems a few months ago, but thought they were through the worst of it. As an IT geek I’d love to know more about their architecture, does anyone know of anything like this?
