Tag Archives: mysql

innodb: how to have incrementing numbers with no gap

Goal Ensure that a column (c1) in a table (t1) has unique consecutive integer values, independently of rollbacks, simultaneous commits and so on. Context I use mysql innodb storage to benefit from transactions and ensure integrity and consistency of data. Main advantage of transactions is that you can revert them and restore data to their previous content. Can you revert anything? The answer is no. There is one element that cannot be reverted is the value of auto_increment fields. And it’s absolutely normal. Let’s say you have two transactions, both inserting a new row, but first transaction is rolled back […]

mysql: load data from external file

Let’s say you have a list of sql queries in an external file (generated through mysqldump or phpmyadmin export). If generated file is too big to be uploaded via http request: upload it to your server via ftp or similar unzip your content connect to your mysql server (cf this post: connect to mysql server using cli ) select your targeted database if necessary (run: “use <your_db_name>“ run: “source  path_to_file.sql“ That’s it! sources http://dev.mysql.com/doc/refman/5.0/en/recovery-from-backups.html mysql official doc, section 6.2.2 Backup and Recovery http://dev.mysql.com/doc/refman/5.0/en/programs-client.html section 4.5: mysql client program