While the process of writing software remains a challenging, sometimes frustrating endeavor, I was reminded recently how simple some tasks have become. The data available on the internet (along with the tools we have to manipulate that data) make answering some difficult questions a rather enjoyable undertaking. Today’s post is the first part in a […]
On my current project, we wanted to use named parameters in our queries. Unfortunately, we are using the MySQL ODBC driver for .NET, and named parameters are not supported. Positional parameters are supported, so we created a little utility to help.
TL;DR $ ssh me@remoteserver 'mysqldump -u user -psecret production_database | \ gzip -9' | gzip -d | mysql local_database$ ssh me@remoteserver 'mysqldump -u user -psecret production_database | \ gzip -9' | gzip -d | mysql local_database Use Some Pipes Bro If you pass a command to ssh as an argument, it will be executed on […]
When it comes to exporting a tab-delimited file from a mysql database table, you have a couple different options without having to resort to heavier programming in Ruby, PHP, Java, etc. Instead, you can use MySQL’s SELECT INTO OUTFILE, run a SELECT statement and redirect the output to a file, or use mysqldump. I started […]
When creating a migration using Ruby on Rails and MySQL, the default MySQL type created when defining an integer field is an 11-digit signed int. This will allow you to store signed integers up to 2147483647. However MySQL has the BIGINT storage type which stores signed integers up to 9223372036854775807.
There are a few different options for accessing a MySQL database with ADO.net. Connector/NET, written in C#, has some upsides over the ODBC driver. Unfortunately, we ran into a number of problems with it and switched to the ODBC driver. With a few small changes we were able to convert from one to the other. […]
It happens countless times for many reasons. You attempt to start or restart your MySQL server after a small configuration change and MySQL stubbornly refuses to start. Or, if it does start, some important functionality, such as InnoDB support, is missing. Most Linux users will be familiar with the following pleasant response:
When working with large databases, SQL structure effects query performance dramatically. I recently refactored a query that looked like this: </p> <pre> SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 where t2.c = 1)</pre> <p> Refactored into: </p> <pre> SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.b AND t2.c […]
One of the requirements for setting up replication with MySQL is to turn on binary logging. Binary logs contain records of database changes or changes to actual data. The binary logs do not record results of general queries which do not affect data such as SELECT or SHOW. However, results of queries which affect the […]
Occasionally, I need to use the data in a single table from a full MySQL database backup dump. Ordinarily, you’d just import the database dump into MySQL and go. Unfortunately, if the dump file is several Gigabytes (even when compressed), it becomes time-consuming to SCP this file around and then load it into MySQL. In […]