Principal Software Engineer/Architect, motorcyclist, drummer, and artist
This indicates that the user already exists, or did exist but that all of the data for that user has not been deleted.
As the mysql root user:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'hostname';
DROP USER 'user'@'hostname'Then re-try creating the user.→ Continue reading “Solution for MySQL ERROR 1396 (HY000): Operation CREATE USER failed for”
So I’m dumping a database on a remote server to pull down to my local box to do some development. When loading the mysqldump file I encountered the error:
ERROR 1005 (HY000) at line 8680: Can't create table 'database.table' (errno: 150)After doing some searching online it seems that is one of the notoriously cryptic MySQL error messages that is woefully overloaded.
I did manage to fix the error (which ended up being a foreign key reference from another table → Continue reading “Figuring out MySQL ERROR 1005 (HY000) Can’t create table (errno: 150)”
Following are a couple of ways to execute SQL on a remote MySQL server via the shell.
Executing SQL directly from the command line:
$ mysql -u uid -p -h remote.host database -e ‘SQL query here;’
Executing SQL from a file on the local host on the remote server:
$ mysql -u uid -p -h remote.host database < file.sql
You can also connect to the remote mysql server and from the command line execute SQL from files on the local → Continue reading “Executing MySQL Queries and Commands from the Command Line to a Remote Server”
Following is how to generate dynamic SQL in a shell script and then execute those queries.
Let’s say, for instance, that you have a list of tables that you want to flush regularly during development and don’t want to type in the SQL queries each time. Moreover, you just want to maintain a list of the table names and add and remove from it when necessary and have your script dynamically generate and execute the delete statements.
For the purposes → Continue reading “Executing Dynamically Generated SQL Queries from a Shell Script”