percona-lab / mysql_random_data_load Goto Github PK

MySQL random data loader

License: Apache License 2.0

Go 93.43% Makefile 5.46% Shell 1.11%

mysql_random_data_load's Introduction

Random data generator for MySQL

Many times in my job I need to generate random data for a specific table in order to reproduce an issue.
After writing many random generators for every table, I decided to write a random data generator, able to get the table structure and generate random data for it.
Plase take into consideration that this is the first version and it doesn't support all field types yet!

NOTICE
This is an early stage project.

Supported fields:

Field type	Generated values
tinyint	0 ~ 0xFF
smallint	0 ~ 0XFFFF
mediumint	0 ~ 0xFFFFFF
int - integer	0 ~ 0xFFFFFFFF
bigint	0 ~ 0xFFFFFFFFFFFFFFFF
float	0 ~ 1e8
decimal(m,n)	0 ~ 10^(m-n)
double	0 ~ 1000
char(n)	up to n random chars
varchar(n)	up to n random chars
date	NOW() - 1 year ~ NOW()
datetime	NOW() - 1 year ~ NOW()
timestamp	NOW() - 1 year ~ NOW()
time	00:00:00 ~ 23:59:59
year	Current year - 1 ~ current year
tinyblob	up to 100 chars random paragraph
tinytext	up to 100 chars random paragraph
blob	up to 100 chars random paragraph
text	up to 100 chars random paragraph
mediumblob	up to 100 chars random paragraph
mediumtext	up to 100 chars random paragraph
longblob	up to 100 chars random paragraph
longtext	up to 100 chars random paragraph
varbinary	up to 100 chars random paragraph
enum	A random item from the valid items list
set	A random item from the valid items list

How strings are generated

If field size < 10 the program generates a random "first name"
If the field size > 10 and < 30 the program generates a random "full name"
If the field size > 30 the program generates a "lorem ipsum" paragraph having up to 100 chars.

The program can detect if a field accepts NULLs and if it does, it will generate NULLs ramdomly (~ 10 % of the values).

Usage

mysql_random_data_load <database> <table> <number of rows> [options...]

Options

Option	Description
--bulk-size	Number of rows per INSERT statement (Default: 1000)
--debug	Show some debug information
--fk-samples-factor	Percentage used to get random samples for foreign keys fields. Default 0.3
--host	Host name/ip
--max-fk-samples	Maximum number of samples for fields having foreign keys constarints. Default: 100
--max-retries	Maximum number of rows to retry in case of errors. See duplicated keys. Deafult: 100
--no-progressbar	Skip showing the progress bar. Default: false
--password	Password
--port	Port number
--Print	Print queries to the standard output instead of inserting them into the db
--user	Username
--version	Show version and exit

Foreign keys support

If a field has Foreign Keys constraints, random-data-load will get up to --max-fk-samples random samples from the referenced tables in order to insert valid values for the field.
The number of samples to get follows this rules:
1. Get the aproximate number of rows in the referenced table using the rows field in:

EXPLAIN SELECT COUNT(*) FROM <referenced schema>.<referenced table>

1.1 If the number of rows is less than max-fk-samples, all rows are retrieved from the referenced table using this query:

SELECT <referenced field> FROM <referenced schema>.<referenced table>

1.2 If the number of rows is greater than max-fk-samples, samples are retrieved from the referenced table using this query:

SELECT <referenced field> FROM <referenced schema>.<referenced table> WHERE RAND() <= <fk-samples-factor> LIMIT <max-fk-samples>

Example

CREATE DATABASE IF NOT EXISTS test;

CREATE TABLE `test`.`t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tcol01` tinyint(4) DEFAULT NULL,
  `tcol02` smallint(6) DEFAULT NULL,
  `tcol03` mediumint(9) DEFAULT NULL,
  `tcol04` int(11) DEFAULT NULL,
  `tcol05` bigint(20) DEFAULT NULL,
  `tcol06` float DEFAULT NULL,
  `tcol07` double DEFAULT NULL,
  `tcol08` decimal(10,2) DEFAULT NULL,
  `tcol09` date DEFAULT NULL,
  `tcol10` datetime DEFAULT NULL,
  `tcol11` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `tcol12` time DEFAULT NULL,
  `tcol13` year(4) DEFAULT NULL,
  `tcol14` varchar(100) DEFAULT NULL,
  `tcol15` char(2) DEFAULT NULL,
  `tcol16` blob,
  `tcol17` text,
  `tcol18` mediumtext,
  `tcol19` mediumblob,
  `tcol20` longblob,
  `tcol21` longtext,
  `tcol22` mediumtext,
  `tcol23` varchar(3) DEFAULT NULL,
  `tcol24` varbinary(10) DEFAULT NULL,
  `tcol25` enum('a','b','c') DEFAULT NULL,
  `tcol26` set('red','green','blue') DEFAULT NULL,
  `tcol27` float(5,3) DEFAULT NULL,
  `tcol28` double(4,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

To generate 100K random rows, just run:

mysql_random_data_load test t3 100000 --user=root --password=root

mysql> select * from t3 limit 1\G
*************************** 1. row ***************************
    id: 1
tcol01: 10
tcol02: 173
tcol03: 1700
tcol04: 13498
tcol05: 33239373
tcol06: 44846.4
tcol07: 5300.23
tcol08: 11360967.75
tcol09: 2017-09-04
tcol10: 2016-11-02 23:11:25
tcol11: 2017-03-03 08:11:40
tcol12: 03:19:39
tcol13: 2017
tcol14: repellat maxime nostrum provident maiores ut quo voluptas.
tcol15: Th
tcol16: Walter
tcol17: quo repellat accusamus quidem odi
tcol18: esse laboriosam nobis libero aut dolores e
tcol19: Carlos Willia
tcol20: et nostrum iusto ipsa sunt recusa
tcol21: a accusantium laboriosam voluptas facilis.
tcol22: laudantium quo unde molestiae consequatur magnam.
tcol23: Pet
tcol24: Richard
tcol25: c
tcol26: green
tcol27: 47.430
tcol28: 6.12
1 row in set (0.00 sec)

How to download the precompiled binaries

There are binaries available for each version for Linux and Darwin. You can find compiled binaries for each version in the releases tab:

https://github.com/Percona-Lab/mysql_random_data_load/releases

To do

Add suport for all data types.
Add supporrt for foreign keys.
Support config files to override default values/ranges.
Support custom functions via LUA plugins.

Version history

0.1.10

Fixed argument validations
Fixed ~/.my.cnf loading

0.1.10

Fixed connection parameters for MySQL 5.7 (set driver's AllowNativePasswords: true)

0.1.9

Added support for bunary and varbinary columns
By default, read connection params from ${HOME}/.my.cnf

0.1.8

Fixed error for triggers created with MySQL 5.6
Added Travis-CI
Code clean up

0.1.7

Support for MySQL 8.0
Added --print parameter
Added --version parameter
Removed qps parameter

0.1.6

Improved generation speed (up to 50% faster)
Improved support for TokuDB (Thanks Agustin Gallego)
Code refactored
Improved debug logging
Added Query Per Seconds support (experimental)

0.1.5

Fixed handling of NULL collation for index parser

0.1.4

Fixed handling of time columns
Improved support of GENERATED columns

0.1.3

Fixed handling of nulls

0.1.2

New table parser able to retrieve all the information for fields, indexes and foreign keys constraints.
Support for foreign keys constraints
Added some tests

0.1.1

Fixed random data generation

0.1.0

Initial version

mysql_random_data_load's People

Contributors

Stargazers

Watchers

mysql_random_data_load's Issues

undefined: getters.NewRandomBinary

failed to build, getters.NewRandomBinary is undefined.

/main.go:426:28: undefined: getters.NewRandomBinary

panic: runtime error: integer divide by zero

Don't know what specifically caused this

[root@ip-10-0-0-170 ~]# mysql_random_data_load --version  xx xx 2
Version   : v0.1.10
Commit    : 77ba5b493094d79318d941ea45f43268115c7be1
Branch    : master
Build     : 2018-10-10T16:39:41-0300
Go version: go1.11.1

panic: runtime error: integer divide by zero

goroutine 1 [running]:
main.main()
	/home/karl/go/src/github.com/Percona-Lab/mysql_random_data_load/main.go:187 +0x1536

--version functionality is not working correctly

For some reason, it is broken now (used to work correctly):

agustin@bm-support01 ~/bin $ mysql_random_data_load --version
FATA[0000] required argument 'database' not provided
agustin@bm-support01 ~/bin $ mysql_random_data_load --version  db
FATA[0000] required argument 'table' not provided
agustin@bm-support01 ~/bin $ mysql_random_data_load --version  db table
FATA[0000] required argument 'rows' not provided
agustin@bm-support01 ~/bin $ mysql_random_data_load --version  db table 23
Version   : v0.1.10
Commit    : 77ba5b493094d79318d941ea45f43268115c7be1
Branch    : master
Build     : 2018-10-10T16:39:41-0300
Go version: go1.11.1

Support for column names with special characters (å, ä, ö)

While testing a legacy system I had problems inserting data into a column named "Behörighet", among others.
With that example the column name in the query becomes "Beh%C3%B6righet", which gives an error with --debug
DEBU[2021-05-11T10:40:49+02:00] Cannot run insert: Error 1054: Unknown column 'Beh%C3%B6righet' in 'field list'

Unable to launch

On Ubuntu 16.04.4 LTS I get the following error. What's wrong?
I've installed go even if in the documentation is not mandatory.

panic: invalid argument to Int63n

goroutine 1 [running]:
math/rand.(*Rand).Int63n(0xc420066180, 0x8000000000000000, 0x3fc408d2ac22c4d3)
/usr/local/go/src/math/rand/rand.go:108 +0x10d
math/rand.Int63n(0x8000000000000000, 0x16a)
/usr/local/go/src/math/rand/rand.go:267 +0x37
github.com/Percona-Lab/mysql_random_data_load/internal/getters.(*RandomDecimal).Value(0xc42028c060, 0xc42010e180, 0xc42010e2d7)
/home/karl/golang/src/github.com/Percona-Lab/mysql_random_data_load/internal/getters/decimal.go:17 +0x85
github.com/Percona-Lab/mysql_random_data_load/internal/getters.(*RandomDecimal).String(0xc42028c060, 0xc42010e180, 0x16a)
/home/karl/golang/src/github.com/Percona-Lab/mysql_random_data_load/internal/getters/decimal.go:22 +0x2f
github.com/Percona-Lab/mysql_random_data_load/internal/getters.(*RandomDecimal).Quote(0xc42028c060, 0xc420084580, 0x155)
/home/karl/golang/src/github.com/Percona-Lab/mysql_random_data_load/internal/getters/decimal.go:26 +0x2b
main.run(0xc420011720, 0xc4200f2400, 0xc4200f2800, 0xc420020150, 0xc42028e000, 0x1c, 0x20, 0x1, 0xa, 0x807790, ...)
/home/karl/golang/src/github.com/Percona-Lab/mysql_random_data_load/main.go:251 +0x1bc
main.main()
/home/karl/golang/src/github.com/Percona-Lab/mysql_random_data_load/main.go:189 +0xbb4

panic: invalid argument to Int63n

Anyone having same issue?

(Centos 7 x64)

./mysql_random_data_load test test 10 --bulk-size=1 -h 127.0.0.1 --max-threads=1 --print -u root -p test
panic: invalid argument to Int63n

goroutine 1 [running]:a
math/rand.(*Rand).Int63n(0xc00008e180, 0x8000000000000000, 0x3fc408d2ac22c4d3)
/usr/local/go/src/math/rand/rand.go:111 +0x10d
math/rand.Int63n(0x8000000000000000, 0x16d)
/usr/local/go/src/math/rand/rand.go:319 +0x37
github.com/Percona-Lab/mysql_random_data_load/internal/getters.(*RandomDecimal).Value(0xc0001f6440, 0xc0002fe000, 0xc0002fe15a)
/home/karl/go/src/github.com/Percona-Lab/mysql_random_data_load/internal/getters/decimal.go:17 +0x85
github.com/Percona-Lab/mysql_random_data_load/internal/getters.(*RandomDecimal).String(0xc0001f6440, 0xc0002fe000, 0x16d)
/home/karl/go/src/github.com/Percona-Lab/mysql_random_data_load/internal/getters/decimal.go:22 +0x2f
github.com/Percona-Lab/mysql_random_data_load/internal/getters.(*RandomDecimal).Quote(0xc0001f6440, 0xc0000b66e0, 0x158)
/home/karl/go/src/github.com/Percona-Lab/mysql_random_data_load/internal/getters/decimal.go:26 +0x2b
main.run(0xc0001220c0, 0xc000114480, 0xc000114700, 0xc00006c0e0, 0xc0002d2000, 0x1c, 0x20, 0xa, 0x1, 0xc00005b8c0, ...)
/home/karl/go/src/github.com/Percona-Lab/mysql_random_data_load/main.go:273 +0x1f1
main.main()
/home/karl/go/src/github.com/Percona-Lab/mysql_random_data_load/main.go:211 +0xa1a

INFO[0000] cannot get table 'service1' struct: cannot read indexes: sql: expected 14 destination arguments in Scan, not 13

Probably a similar issue with #27

MariaDB 10.6.5 Centos 7

[root@rafael-dagandan-node3 ~]# ./mysql_random_data_load --host=127.0.0.1 --port=3306 --user=test --password=test test service1 200 --debug
INFO[0000] cannot get table service1 struct: cannot read indexes: sql: expected 14 destination arguments in Scan, not 13

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| service1 |          0 | PRIMARY  |            1 | ID          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
1 row in set (0.001 sec)```

INFO[0028] cannot get table tableName struct: cannot read trigger: sql: Scan error on column index 5: unsupported Scan, storing driver.Value type <nil> into type *time.Time

Hi,

I get the following error when trying to insert 1000 random rows to any of my tables that have triggers defined on them:
INFO[0028] cannot get table tableName struct: cannot read trigger: sql: Scan error on column index 5: unsupported Scan, storing driver.Value type into type *time.Time

FR: Use SHOW INDEX STATUS output to derive data distribution

I believe we could use output of SHOW INDEX STATUS to find out how many unique values each column should present.

+------------------------+------------+--------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                  | Non_unique | Key_name           | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------------+------------+--------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| performance_by_affcode |          0 | PRIMARY            |            1 | id              | A         |  2183073712 |     NULL | NULL   |      | BTREE      |         |               |
| performance_by_affcode |          0 | affcode_per_day    |            1 | profile_id      | A         |        1113 |     NULL | NULL   | YES  | BTREE      |         |               |
| performance_by_affcode |          0 | affcode_per_day    |            2 | ad_id_in_target | A         |     8838355 |     NULL | NULL   | YES  | BTREE      |         |               |
| performance_by_affcode |          0 | affcode_per_day    |            3 | affcode         | A         |    33076874 |     NULL | NULL   |      | BTREE      |         |               |
| performance_by_affcode |          0 | affcode_per_day    |            4 | date            | A         |  2183073712 |     NULL | NULL   |      | BTREE      |         |               |
| performance_by_affcode |          1 | campaign_id_date   |            1 | campaign_id     | A         |         447 |     NULL | NULL   | YES  | BTREE      |         |               |
| performance_by_affcode |          1 | campaign_id_date   |            2 | date            | A         |      326563 |     NULL | NULL   |      | BTREE      |         |               |
| performance_by_affcode |          1 | last_updated       |            1 | last_updated    | A         |    13311425 |     NULL | NULL   |      | BTREE      |         |               |
| performance_by_affcode |          1 | profile_ad_id_date |            1 | profile_id      | A         |         919 |     NULL | NULL   | YES  | BTREE      |         |               |
| performance_by_affcode |          1 | profile_ad_id_date |            2 | ad_id_in_target | A         |    10754057 |     NULL | NULL   | YES  | BTREE      |         |               |
| performance_by_affcode |          1 | profile_ad_id_date |            3 | date            | A         |  2183073712 |     NULL | NULL   |      | BTREE      |         |               |
| performance_by_affcode |          1 | profile_affcode    |            1 | profile_id      | A         |         763 |     NULL | NULL   | YES  | BTREE      |         |               |
| performance_by_affcode |          1 | profile_affcode    |            2 | affcode         | A         |    31638749 |     NULL | NULL   |      | BTREE      |         |               |
| performance_by_affcode |          1 | profile_id_date    |            1 | profile_id      | A         |         723 |     NULL | NULL   | YES  | BTREE      |         |               |
| performance_by_affcode |          1 | profile_id_date    |            2 | date            | A         |      265387 |     NULL | NULL   |      | BTREE      |         |               |
+------------------------+------------+--------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

The maximum cardinality value would be the total rows to generate, and you can nest-loop from largest to smallest. So if you had columns A, B and C with cardinality 2, 3 and 6 respectively you would end up with

A |  B  |  C
---+---+----
1  |  1   |  1  
2  |  2   |  2 
1  |  3   |  3 
2  |  1   |  4
1  |  2   |  5
2  |  3   |  6

[note for usage: if an index does not exists customer could provide SELECT COUNT(DISTINCT...) for the relevant columns and we could manually add lines to the output based on that to test on our side]

--Print

This is below Low priority, but below it shows the options and --Print does not work, but --print does. Maybe a typo? Thanks

Option	Description
--bulk-size	Number of rows per INSERT statement (Default: 1000)
--debug	Show some debug information
--fk-samples-factor	Percentage used to get random samples for foreign keys fields. Default 0.3
--host	Host name/ip
--max-fk-samples	Maximum number of samples for fields having foreign keys constarints. Default: 100
--max-retries	Maximum number of rows to retry in case of errors. See duplicated keys. Deafult: 100
--no-progressbar	Skip showing the progress bar. Default: false
--password	Password
--port	Port number
--Print	Print queries to the standard output instead of inserting them into the db
--user	Username
--version	Show version and exit

Release new build

Last release version was shipped in 2019 and the PR fix: --max-fk-samples was not applied #36 is merged, though new release build is pending.

information_schema.columns table incorrectly parsed for 5.5 versions

In mysql 5.5 versions, the information_schema.columns doesn't have the DATETIME_PRECISION column, so there were errors when trying to run against 5.5:

ERRO[0000] Cannot get table fields: sql: expected 19 destination arguments in Scan, not 20

The following function should be changed (PR in progress for this):

https://github.com/Percona-Lab/mysql_random_data_load/blob/master/tableparser/tableparser.go#L213

Add support for FULLTEXT indexes

The tool is currently returning the following:

2017/11/29 20:01:17 cannot get table table_fulltext struct: cannot read constraints: sql: Scan error on column index 5: unsupported Scan, storing driver.Value type <nil> into type *string

CREATE TABLE `table_fulltext` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `string` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `string` (`string`(15)),
  FULLTEXT KEY `FT_string` (`string`)
) ENGINE=InnoDB;

ANSI mode breaks the tool for some tables

When ANSI and/or ANSI_QUOTES is listed in sql_mode, the tool fails to insert rows to some tables.
It works with simple table like this:

mysql [localhost:5727] {msandbox} (test) > show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE "t1" (
  "id" int(11) DEFAULT NULL,
  KEY "id" ("id")
)
1 row in set (0.00 sec)

$ mysql_random_data_load test t1 10 -u msandbox -p msandbox --host="127.0.0.1" -P 5727
INFO[2019-06-24T11:56:21+02:00] Starting                                     
   0s [====================================================================] 100%
INFO[2019-06-24T11:56:21+02:00] 10 rows inserted

But does not work for this table:

mysql [localhost:5727] {msandbox} (test) > show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE "t3" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "tcol01" tinyint(4) DEFAULT NULL,
  "tcol02" smallint(6) DEFAULT NULL,
  "tcol03" mediumint(9) DEFAULT NULL,
  "tcol04" int(11) DEFAULT NULL,
  "tcol05" bigint(20) DEFAULT NULL,
  "tcol06" float DEFAULT NULL,
  "tcol08" decimal(10,2) DEFAULT NULL,
  "tcol09" date DEFAULT NULL,
  "tcol10" datetime DEFAULT NULL,
  "tcol11" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  "tcol12" time DEFAULT NULL,
  "tcol13" year(4) DEFAULT NULL,
  "tcol14" varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  "tcol15" char(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  "tcol16" blob,
  "tcol17" text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  "tcol18" mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  "tcol19" mediumblob,
  "tcol20" longblob,
  "tcol21" longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  "tcol22" mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  "tcol23" varchar(3) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  "tcol24" varbinary(10) DEFAULT NULL,
  "tcol25" enum('a','b','c') CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  "tcol26" set('red','green','blue') CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  "tcol27" float(5,3) DEFAULT NULL,
  "tcol28" double(4,2) DEFAULT NULL,
  PRIMARY KEY ("id","tcol11")
)
1 row in set (0.00 sec)

$ mysql_random_data_load test t3 10 -u msandbox -p msandbox --host="127.0.0.1" -P 5727
INFO[2019-06-24T11:56:49+02:00] Starting                                     
  --- [--------------------------------------------------------------------]   0%
INFO[2019-06-24T11:56:50+02:00] 0 rows inserted

Tested with

Version   : v0.1.10
Commit    : 77ba5b493094d79318d941ea45f43268115c7be1
Branch    : master
Build     : 2018-10-10T16:39:41-0300
Go version: go1.11.1

Add --print to enable printing commands instead of directly running them.

Instead of the tool generating and running the SQL commands needed to insert data, add a new flag that can instead print the queries needed to STDOUT (or file), without running them.

It should still need connection to the server to get table definitions.

It is not executed in mysql 8.0.12.

When trying to use this tool in Docker's mysql: latest (8.0.12), the error INFO [0000] Can not set time zone to UTC: this authentication plugin is not supported is displayed and can not be executed.

However, it works in Docker's mysql:5.6 case.
Also, in 8.0.12 the query SET @@ session.time_zone = "+00: 00"; could be executed directly.

Of course, the database schema is the same.
What is the cause?

Makefile isn't idiot-proof

Hi Percona guys,

Seems dumb but I've run make as root in amazon linux 2017, /bin deleted as result, It was a test vm. But ...

Thanks in advance!

Errors when autoincrement value is used for PK

mysql [localhost] {msandbox} (sakila) > show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=65535 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

SELECT COUNT(*) FROM actor;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

As we can see, the PK is a smallint, the table is empty, and the AUTO_INCREMENT value is at 65535 (this was a table that was full, then all rows were deleted).

If we try to use the tool, it will insert only one row, and will error out with duplicate key errors:

mysql_random_data_load_linux_amd64 sakila actor 5 --host=127.0.0.1 --port=12323 --user=msandbox --password=msandbox 
2017/12/22 00:12:29 Starting
2017/12/22 00:12:29 cannot run insert: Error 1467: Failed to read auto-increment value from storage engine
2017/12/22 00:12:29 Adding extra 5 rows.
   0s [============>-------------------------------------------------------]  20%
Error 1062: Duplicate entry '65535' for key 'PRIMARY'
Error 1062: Duplicate entry '65535' for key 'PRIMARY'
Error 1062: Duplicate entry '65535' for key 'PRIMARY'
...

Only one row is inserted:

SELECT * FROM actor;
+----------+-----------------------------------------------+-----------------------------------------------+---------------------+
| actor_id | first_name                                    | last_name                                     | last_update         |
+----------+-----------------------------------------------+-----------------------------------------------+---------------------+
|    65535 | fugiat ratione tempora quia aliquam nulla dol | tempore aut harum debitis corporis sed repell | 2017-08-29 05:09:01 |
+----------+-----------------------------------------------+-----------------------------------------------+---------------------+
1 row in set (0.00 sec)

It may make sense to try to choose another PK value on error.

Unique key constraints not followed

CREATE TABLE `u1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `u1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u1` (`u1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

./random_data_load_linux_amd64 -uroot -p*** -h192.168.3.2 test u1 100;
2017/09/19 06:47:25 Starting
2017/09/19 06:47:25 Error inserting values: Error 1062: Duplicate entry '25630' for key 'u1'

Go - use of internal package not allowed

Greetings,
Trying to compile on FreeBSD. I'm I missing something?

[root@master] ~/.gopkg/src/github.com/github/mysql_random_data_load# go build main.go
main.go:13:2: use of internal package not allowed

[root@master] ~/.gopkg/src/github.com/github/mysql_random_data_load# go get -t -v ./... main.go:13:2: use of internal package not allowed

main.go line:13
"github.com/Percona-Lab/mysql_random_data_load/internal/getters"
"github.com/Percona-Lab/mysql_random_data_load/tableparser"

Fixed by using:
go get -u -v github.com/Percona-Lab/mysql_random_data_load

mysql 8.0.16 - INFO[0000] cannot get table clients struct: cannot read indexes: sql: expected 15 destination arguments in Scan, not 13

With version 0.1.10 on macOS X 10.14.5

~/chefdb  ./mysql_random_data_load chefdb clients 1000 --user=root
INFO[0000] cannot get table clients struct: cannot read indexes: sql: expected 15 destination arguments in Scan, not 13

mysql> show indexes from clients;
+---------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name                | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| clients |          0 | PRIMARY                 |            1 | clients_id       | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| clients |          0 | clients_hostname_UNIQUE |            1 | clients_hostname | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

@percona-csalguero

Cannot set time zone to UTC: Error 2003: Authentication with backend failed. Session will be closed.

I tried to use your program on Debian 9. It says:

INFO[0000] Cannot set time zone to UTC: Error 2003: Authentication with backend failed. Session will be closed.

How can I solve this problem? I need to test my database cluster.

Can't profile with pprof

Hey Carlos! :)

I have a request: can you add profiling support, such that I can see what's going on and report proper issues? I'm having some performance issues and I can't figure out what's going on :)

this shows the snippet of code you need to add, it's a single parameter and a small piece of code to handle it: https://blog.golang.org/profiling-go-programs

Thanks!!!

cannot get field type: bit

It appears the the bit type is not handled correctly

Versions

[root@ip-10-0-0-170 ~]# mysql_random_data_load --version x x 2
Version   : v0.1.10
Commit    : 77ba5b493094d79318d941ea45f43268115c7be1
Branch    : master
Build     : 2018-10-10T16:39:41-0300
Go version: go1.11.1

[root@ip-10-0-0-170 ~]# rpm -qa|grep Percona
Percona-Server-client-57-5.7.27-30.1.el6.x86_64
Percona-Server-57-debuginfo-5.7.27-30.1.el6.x86_64
Percona-Server-shared-57-5.7.27-30.1.el6.x86_64
Percona-Server-server-57-5.7.27-30.1.el6.x86_64

Steps to reproduce

BIT type with DEFAULT NULL

[root@ip-10-0-0-170 ~]# mysql theswamp -e "show create table customer2"
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                          |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customer2 | CREATE TABLE `customer2` (
  `customer_id` bigint(20) NOT NULL,
  `receive_fx_insight_emails` bit(1) DEFAULT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[root@ip-10-0-0-170 ~]# mysql_random_data_load --no-progress --max-threads=1 --bulk-size=5000 theswamp customer2 5
INFO[2019-10-10T14:17:42Z] Starting                                     
INFO[2019-10-10T14:17:42Z] cannot get field type: receive_fx_insight_emails: bit
 
INFO[2019-10-10T14:17:43Z] 5 rows inserted

[root@ip-10-0-0-170 ~]# mysql theswamp -e "select * from  customer2"
+---------------------+---------------------------+
| customer_id         | receive_fx_insight_emails |
+---------------------+---------------------------+
| 3916589616287113937 | NULL                      |
| 4037200794235010051 | NULL                      |
| 5577006791947779410 | NULL                      |
| 6129484611666145821 | NULL                      |
| 8674665223082153551 | NULL                      |
+---------------------+---------------------------+

BIT type with NOT NULL

[root@ip-10-0-0-170 ~]# mysql theswamp -e 'show create table customer3'
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                      |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customer3 | CREATE TABLE `customer3` (
  `customer_id` bigint(20) NOT NULL,
  `receive_fx_insight_emails` bit(1) NOT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[root@ip-10-0-0-170 ~]# mysql_random_data_load --no-progress --max-threads=1 --bulk-size=5000 theswamp customer3 5
INFO[2019-10-10T14:23:55Z] Starting                                     
INFO[2019-10-10T14:23:55Z] cannot get field type: receive_fx_insight_emails: bit
 
INFO[2019-10-10T14:23:56Z] 5 rows inserted

[root@ip-10-0-0-170 ~]# mysql theswamp -e 'select * from customer3;'
+---------------------+---------------------------+
| customer_id         | receive_fx_insight_emails |
+---------------------+---------------------------+
| 3916589616287113937 |                           |
| 4037200794235010051 |                           |
| 5577006791947779410 |                           |
| 6129484611666145821 |                           |
| 8674665223082153551 |                           |
+---------------------+---------------------------+

Insert Random Data into Tables Using mysql_random_data_load

what is error?

INFO[0000] cannot get table film struct: cannot read trigger: sql: Scan error on column index 5: unsupported Scan, storing driver.Value type into type *time.Time

--max-fk-samples --fk-samples-factor not working

I found that these parameters are not working, I read source code, samples and factors are using a fix-number rather than a parameter value.

Add support for --version

Using --version is not supported yet:

shell> ~/bin/mysql_random_data_loader --version
mysql_random_data_loader: error: unknown long flag '--version', try --help

No support for TINYTEXT.

Currently, there is no support for tinytext data type:

$ ~/src/go/src/github.com/Percona-Lab/mysql_random_data_load/build/mysql_random_data_load --user=root --password=msandbox --host=127.0.0.1 --port=12999 test articles 1000
INFO[2018-03-30T16:12:33-04:00] Starting                                     
INFO[2018-03-30T16:12:33-04:00] cannot get field type: summary: tinytext
    
   0s [====================================================================] 100%
INFO[2018-03-30T16:12:38-04:00] 1000 rows inserted

0.1.6 error

create a smaller table then in the example.

mysql> CREATE TABLE eric.t2( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, t TIME DEFAULT NULL, vc10 VARCHAR(10), vc25 VARCHAR(25), vc100 VARCHAR(100), ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, dt DATETIME DEFAULT NULL, e ENUM( 'x', 'a', 'b', 'c', 'd', 'e', 'f' ) NOT NULL ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 DEFAULT COLLATE = utf8mb4_0900_as_cs;

try to add some data and get the following INFO error. no data in the table.

$ mysql_random_data_loader --host="127.0.0.1" --user=root --password=xxx eric t2 500 INFO[0000] cannot get table t2 struct: cannot read indexes: sql: expected 14 destination arguments in Scan, not 13

I'm on a Mac with mysql v.8.0.3

Add support for JSON

Json column format is not supported:

INFO[2019-03-13T18:31:25Z] Starting
INFO[2019-03-13T18:31:25Z] cannot get field type: payload: json

percona-lab / mysql_random_data_load Goto Github PK

mysql_random_data_load's Introduction

Random data generator for MySQL

Supported fields:

How strings are generated

Usage

Options

Foreign keys support

Example

How to download the precompiled binaries

To do

Version history

0.1.10

0.1.10

0.1.9

0.1.8

0.1.7

0.1.6

0.1.5

0.1.4

0.1.3

0.1.2

0.1.1

0.1.0

mysql_random_data_load's People

Contributors

Stargazers

Watchers

Forkers

mysql_random_data_load's Issues

Versions

Steps to reproduce

Recommend Projects

Recommend Topics

Recommend Org