Category Archives: MySql

MySql Auto-Increment

While playing around with MySql recently, I discovered a strange little quirk. An automatically incremented field cannot be reset – that is, it cannot be reset to a lower value than it currently is. For example, say that you insert a lot of records into a table, or that you manually add a key that’s very high:

MySql.Data.MySqlClient.MySqlException: ‘Duplicate entry ‘2147483647’ for key ‘test_table.PRIMARY”

In my case, I was simply playing around with some settings – if you have a table with more records than that then you may have other issues than this. However, my initial idea was to reset the count:

ALTER TABLE myschema.test_table AUTO_INCREMENT = 1

It turns out that, whilst you can do this, you can only do it for numbers higher than it currently is. Presumably this is to prevent conflicts or whatever. Anyway, the way around it for me was to simply insert a new record, but to override the setting of the auto-increment:

INSERT INTO `myschema`.`test_table`
(`firstname`,
`surname`,
`key`)
VALUES
('',
'',
1);

You would then need to remove this record. Not ideal, but the only way I could get this to work.

datagen

A few weeks ago, I was looking into making a change to a project in work that uses DbUp. For some reason, I took away from that the overwhelming urge to write my own data generator. It’s far from finished, but I came up with datagen. This currently only comes in a MySql flavour, but my plan is to add a few more database engines.

The idea behind this is that you can generate pseudo data in your database. It’s not a tool in its own right, because I wanted to allow it to be customisable. To install , simply reference the package:

<PackageReference Include="datagen.MySql" Version="1.0.0" />

You can then populate the data in an entire schema. Just create a console app (this works with any app type that can physically access the database):

using datagen.Core;
using datagen.MySql;
using datagen.MySql.MySql;

var valueGenerator = new ValueGenerator(
    true,
    DateTime.Now,
    DateTime.Now.AddDays(-100),
    DateTime.Now.AddDays(10));

string connectionString = "Server=127.0.0.1;Port=3306;Database=datagentest;Uid=root;Pwd=password;AllowUserVariables=True";

var mySqlDefaults = new MySqlDefaults(connectionString);

var generate = new Generate(
    connectionString,
    valueGenerator,
    mySqlDefaults.DataTypeParser,
    mySqlDefaults .UniqueKeyGenerator);
await generate.FillSchema(20, "datagentest");

The code above allows you to create a ValueGenerator – there is a default one in the package, but you can easily write your own. FillSchema then adds 20 rows to every table in the schema.

Limitations

There are currently a few limitations – the main two being that this will only work with MySql currently, and that it does not deal with foreign keys (it will just omit that data).

Feel free to contribute, offer suggestions, or contibute.