There is nothing painful than having to rebuild a database after you find that the records in the database are corrupted. Data corruption in databases can take many forms – from allowing inaccurate data to be entered to data field truncation due to inadequate field length. This post gives an introduction to the data quality issues you will encounter when designing database applications.
(more…)
mysql
How to automatically backup MySQL database to Dropbox
Ever wanted to backup your MySQL database to Dropbox automatically. Well, there is a nice portable solution with Dropbox Uploader. Dropbox Uploader is a BASH script which can be used to upload, download, delete, list files, move, copy and much more from Dropbox. It’s written in the BASH scripting language and only needs cURL to work. I’ve tested it on my Windows/Cygwin environment at it works like a charm.
(more…)
MySQL transactions in NodeJS
In a previous post we saw how to access and query MySQL from NodeJS. In this post we will see how we can use MySQL transactions in NodeJS. We will be using the same library as used before, so if you have not installed it go ahead and install it. The library supports simple transaction at the connection level.
(more…)
Export a subset of a database table to reproduce a query
One frequently needs to export some MySQL tables along with certain queries that will work with that table for testing. Usually one only needs a subset of records from the table which will work with the selected queries, rather then the complete table. This can be required when the table contains thousands or millions of records and we do not want to export the complete data set, as this can be time consuming during import, or maybe we only want to provide the other user with some selected records for security reasons.
Say we have a simple WordPress database with a SELECT statement like the following:
SELECT * FROM wp_posts WHERE post_status = 'draft' |
If you want to only export records that will match the corresponding query above, then we can use the ‘mysqldump’ command-line utility with the ‘–where’ option.
(more…)
Splitting large MySQL dump files
One of the frustrating things with working with MySQL is of importing large sql dump files. Either you get a ‘max execution time exceeded’ error from PHP or a ‘Max_allowed_packet_size’ from MySQL. In a recent task I needed to import a table of around a million records on a remote host, which quickly became an exercise in frustration due to various limitations on the server. SSH was of no help as changing the configuration files was restricted to the root user.
My last resort was to split the huge ‘INSERT’ statements into smaller size files. Manually doing the same is obviously time consuming and error prone; the only other solution is to write a small script to split the insert statements. This was to be a quick hack so the parsing code was to be of minimum complexity. Splitting a sql dump containing extended insert statements is somewhat complex so you need to have the dump file in a simple format – each insert statement should be on its own line as shown below.
(more…)
Creating SQL schemas with Doctrine DBAL
A tedious task during web development is that of database schema creation. A schema containing a few tables comprising of a small set of rows is quick, while that containing dozens of tables and large numbers of columns is a tedious process. I usually resort to a small php script with some regular expression tossed in to automatically create a schema from a text file definition. But that is a little buggy as I’ve to manually add the indexes and other small things. Now that Doctrine has released a DBAL library, this will provide a nice ability to automatically create sql schemas.
(more…)
Increase your MySQL productivity with Toad
Recently my favorite MySQL Swiss Army Knife has to be Toad for MySQL. Not only does it have a plethora of tools and interesting features, it is free. A few days back I’d to compare database schemas of different versions of a application to see if some table fields had changed between versions. Comparing a database schema containing above 200 tables can be a time consuming job if you do not have the right tools. Toad makes the work easier with its schema and data compare feature, which lets you easily compare schema and also the data from two different databases. You can even synchronize the two schemas or the data therein so both the databases contain the same schema and data.
Besides these it has a nice query builder, somewhat like the one in Microsoft Access. Toad also can manage your Amazon EC2 instances with its built-in tool, which I’ve yet to try. Other than these it has the other regular features – a database explorer, table builder, database diagram creator etc. Toad is now my favorite tool for managing MySQL data.
Adminer – a fast MySQL administration tool
Before you go ahead and think that you don’t need yet another MySQL management tool, I think you should at least take a quick look at this one. Adminer is a single file (yes! everything in a single php file) tool to manage all your MySQL related tasks. All you have to do is upload the adminer.php file to your server and voila! you are ready to go. No messing around with huge software’s to manage your database. Weighing only 170KB, its easy to upload to any server. A demo of the same is included on the Adminer site.
(more…)
Copying tables in MySQL
Whether you need to copy a test table to a production database, or you need to duplicate a table with only some selected rows or you just need to backup the original table, copying tables is a frequent task most of us regularly do. In this post we will see some quick methods to make copies of MySQL database tables.
(more…)
SchemaBank: Browser based SQL schema designer
Whether desktop or web based, there has recently been a proliferation of SQL design tools on the net. SchemaBank may look like one more in the box. But a detailed look at the features will show you more on offer. SchemaBank is a visual data modelling tool which can help you create ERD diagrams within all modern browsers. It supports MySQL and PostgreSQL.
(more…)