How to fix definer does not exist error 1449 MySQL
Kedar
Explaining and providing solutions of MySQL error 1449: The user specified as a definer does not exist using SQL SECURITY INVOKER and DEFINER.
Browsing Tag
routine
2 posts
How To Generate Random test Data In MySQL
Are you tired of manually generating test data for your MySQL tables? If you’re looking for random data generator, look no further! Introducing the MySQL Random Data Generator, a powerful…
19 responses to “How To Generate Random test Data In MySQL”
I like this approach very much. Unfortunately in my case it doesn’t work with MySql 8.x. When I run the procedure, it keeps saying that there is an error in the SQL syntax near ‘NULL’ – any idea ?
better late than never!! Would you be able to share your table definition please?
Very useful, very nice.
Proc populate() needs …
WHEN col_datatype in (‘date’) THEN
SET func_query=concat(func_query,’get_date(), ‘);
… before WHEN … (‘datetime’… )
Great work. Thanks for reusable code. Awesome, simple and effective
hey,
I am getting this error:
ERROR 1172 (42000): Result consisted of more than one row
any suggestions ?
Well that sounds like a bug, though I use this tool fine! Can you provide with more info (like say schema/command)?
Thanks,
Kedar.
Hi Kedar, thanks for this wonderful utility. I have a query for you. Often the tables are linked using PK-FK. How do i take care of this relationship in the test data generated. i.e The entries in FK column should be based on PK values. thanks a ton.
Somehow missed this!! Well presently we do not have this implemented here. What our function does is actually disable fk check and loads the dummy data.
To make actual relation more work is required here. I will keep it in my todo list but not sure when I’d be able to justify that!
If you can, do share.
We do have git if you want to fork: https://github.com/kedarvj/mysql-random-data-generator
Regards,
Kedar
Sorted it out using ..
“SET collation_connection = ‘utf8_general_ci’;”
Glad you sorted it Ashish… Hope this has worked for you.
Cheers.
I have added this to git as an issue here –> https://github.com/kedarvj/mysql-random-data-generator/issues/4
hi, I keep getting this error on calling populate
“ERROR 1267 (HY000) : Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation ‘='”
I like your approach. It’s quick and simple, however when I generate data it inserts nulls and 0 into the database. Any idea?
I have one suggestion for the size of the table names. 20 is too short. It needs to be longer to support longer table names.
I figured out my problem. I wasn’t passing in the correct db name. Thanks much for the script! It’s exactly what I needed. Not many scripts out there that can:
1. generate data based on the schema
2. generate random data
3. can generate data without any configuration
This is nice. I hope you continue to build on it!
Good to hear you fixed ur problem. Do share as you like this.
keep visiting,
Kedar.
The routine to create test data can and should be created from PHP (or your programming language). The logic should never be in the database, even for the testing logic.
The above code can be confusing and I’m not sure if it works across the board – what if you have images stored in the database? Will the code above work?
Hello itoctopus,
As already said, these are mysql stored procedure & functions combinely generates dummy data that aimed to be used only for testing purpose. I found it useful and hence shared.
Logically this script fetches table’s mata data and as per each field’s datatype, it generates random data for respective datatype. If any of the datatype is not handled it generates a varchar data.
I’ve tested it and use it for testing purposes. I do keep updating it as per requirements.
About your question, ofcourse BLOB datatype hasn’t been considered here but it will generate dummy VARCHAR data for that! You can ofcourse write a function and update procedure to generate BLOB data.
I hope I cleared your concern. Do try and let me know.
Thanks,
Kedar.
Alright, most of us know that. This is an SQL, not a PHP solution.
Maybe. You haven’t tried it yet have only negative things to say.
I personally like seeing his method and also look and find how I might change things.
Its nice..
Reduced my work to insert records for testing purpose. Just need to access the function…