kedarvj / mysql-random-data-generator Goto Github PK

You may see following errors:

MariaDB [test]> call populate('test','mytest',1000,'Y');
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

mysql 8 - schema is:

DROP TABLE IF EXISTS `blogapp_post`;
CREATE TABLE `blogapp_post` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(230) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `body` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `created` datetime(6) DEFAULT NULL,
  `last_updated` datetime(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

call populate('dkrdbm','blogapp_post',10,'y')
@func_query
INSERT INTO dkrdbm.blogapp_post VALUES (NULL, get_string(230), get_varchar(65535), get_datetime(), get_datetime());

Can someone comment on what I am doing wrong?

Tested on various data types in MySQL 5.7.20. Works well except for the new datatype "geometry." Results below.

CREATE TABLE ugly (
ID int(11) DEFAULT NULL,
name varchar(255) DEFAULT NULL,
animals enum('cow','giraffe','kangaroo','buffalo','seal') DEFAULT NULL,
weight float(8,4) DEFAULT NULL,
price decimal(7,2) DEFAULT NULL,
copyright year(4) DEFAULT NULL,
orderdate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
groupid smallint(5) unsigned DEFAULT NULL,
letter char(1) DEFAULT NULL,
picture blob,
createdate datetime DEFAULT NULL,
g geometry DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

mysql> call populate('test','ugly',100,'N');
ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field

mysql> alter table ugly drop column g;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> call populate('test','ugly',100,'N');
...
1 row in set (1.37 sec)

Query OK, 0 rows affected (1.37 sec)

Sample row:

ID: 9932419
name: AExwPMpntgyZCOmTHGmmATPtwbToMUoJGeCAUURzSVXdxbPYvfrqDVR XoAPvFUFviGFllxEFqLKsFcrDSEsZWJHhNwRXkfZGhRMlSEyzbLAxHWIEYctIkcFYXSxHXQkAcJq DsacjLfuDMYIuVuneIeuIjWgQMlSGFiZwkJRimMcWCXhSQBl SOqmnARIQfbPZzzYtXFo IUvuLxaLGZeuLyfiAfVoI aDOmPrmlsdrzssKyixN ismfRVbtM
animals: seal
weight: 1373.7651
price: 64858.56
copyright: 1983
orderdate: 2012-03-16 18:39:00
groupid: 54
letter: g
picture: 83a31448f44ba9ab3a05e352386dc524
createdate: 2012-05-22 12:01:10

function get_float(in_precision int, in_scale int) do not work properly when precision is 1 and scale is 0
i.e
table data type decimal(1,0)

This series of commands demonstrates the problem:

echo "create database testdb;" > test.sql
echo "use testdb;" >> test.sql
wget -nv  https://raw.githubusercontent.com/kedarvj/mysql-random-data-generator/master/populate.sql
cat populate.sql >> test.sql
echo "create table mytable(text varchar(10), number float(10,2), mydate date);" >> test.sql
echo "call populate('testdb','mytable',100000,'n');" >> test.sql
echo "select count(*) from mytable;" >> test.sql
mysql < test.sql

Output will be:

Developed by
Kedar Vaijanapurkar
count(*)
100199

The problem appears where the number of records generated is > 999 and appears to grow as the number of records requested grows.

Generating 10,000 rows in a table of 10 columns of VARCHAR(25) plus an auto-increment int primary key (11 columns total). Hardware is very powerful. 4 core, 24GB RAM, on NVME drives (the hardware is actually much larger, but this is a container and those are the constraints).

Might I be doing something wrong? Or is that pretty typical?

This error
ERROR 1172 (42000): Result consisted of more than one row
appear when trying to call populate_fk to a child table.
Is this the correct way to use it or i am using it wrong? and how to use the two scripts to populate my database

Why can't you specify the data format, if only you can support regular expressions