Sometimes it’s necessary to set the starting point of a MySQL auto-increment value.
Normally, MySQL starts auto-incrementing at 1. But let’s say you want to start at 10.000, because you want at least a five figure number. You can use the following query to set the MySQL auto-index:
ALTER TABLE some_table AUTO_INCREMENT=10000 If you want to delete all records from your table and restart auto-index at 1, you might be tempted to run a DELETE query, followed by the above example, setting the auto increment value to 1.
There are times when you have a lot of data in a database (let’s say wp_posts for a Wordpress blog like Ariejan.net). When you need to find and replace certain strings, this can be a very tedious task. Find all posts containing the “needle” string and manually replace all these occurrences with “chocolate”. With about 200 posts, you can imagine how long this would take to do manually.
But, as I always say: “You’re a programmer!
I’ve been using a simple Rails application locally with a SQlite 3 database for some time. Now I want to move to another host and use MySQL instead. But guess what? You can’t just migrate your data!
Here are some easy steps on how to migrate your data to MySQL. First of all you need to dump your SQLite3 database. This includes transaction statements and create commands. That’s fine. Since we also migrate the schema information, our RoR app will not know any difference after we change config/database.
When you create a Rails application a database.yml files is included with some default configuration for your database. Unfortunately these are defaults for MySQL. If you want to use another database, like SQlite, you’d have to rewrite the entire configuration file. And that’s not what you want!
Luckily, rails is very adapative and we can make it do all the work for us.