Category Archives: MySQL

MySQL execute statements directly from the (linux) console

Posted on by

Executing a query quickly from the console is nice to be able to do (without having to enter the MySQL console).

  • This will simply output the result to the console where you are. 
     user@server:~$ mysql -uUSERNAME -p DATABASENAME --execute "REPAIR TABLE position";

    Replace USERNAME and DATABASENAME with your vars

  • If you would like the output to a file 
     mysql -uUSERNAME -p DATABASENAME --execute "SELECT * FROM test" > test.sql;

    Replace USERNAME and DATABASENAME with your vars

  • If you would like the output to be sent as a email 
     mysql -uUSERNAME -p DATABASENAME --execute "SELECT * FROM test" | mail -s 'SUBJECT' example@mail.com

    Replace USERNAME, DATABASENAME , SUBJECT and example@mail.com with your vars

MYSQL Creating several UUIDs at one single time

Posted on by

I recently ran into the problem that I had too add two rows to a database that uses a UUID (in this case converted to a binary) as a key.
The problem I had was that running two insert statements after each other (just separated by “;”) then the same id was generated.
A workaround I have found is to sleep for a second between the two statements.

Examples (using SELECT instead of INSERT):
Getting the same id twice

SELECT UNHEX(REPLACE(UUID(),'-','')) AS uuid1,  UNHEX(REPLACE(UUID(),'-','')) AS uuid2;
-- Shows: "849392548f1311df91b70019dbd264f1"	"849392908f1311df91b70019dbd264f1"

Adding a sleep to get different ids

SELECT UNHEX(REPLACE(UUID(),'-','')) AS uuid1, SLEEP(1), UNHEX(REPLACE(UUID(),'-','')) AS uuid2
-- Shows: "3901c1928f1511df91b70019dbd264f1"	"0"	"399a5b148f1511df91b70019dbd264f1"

And another one using INSERTS

INSERT INTO testTable (id) VALUES (UNHEX(REPLACE(UUID(),'-','')));
SLEEP(1);
INSERT INTO testTable (id) VALUES (UNHEX(REPLACE(UUID(),'-','')));

MySQL inserting UUID to a binary(16) field

Posted on by

It is advantageous to store a UUID (acc to RFC 4122) in a binary(16) field in the database.
It is not hard to create such a table and to insert data into it.

CREATE TABLE IF NOT EXISTS test_table (
  id BINARY(16) NOT NULL,
  name VARCHAR(128) NOT NULL,
  PRIMARY KEY  (id)
) 
 
INSERT INTO test_table (id, name) VALUES
(UNHEX(REPLACE(UUID(),'-','')), 'test1'),
(UNHEX(REPLACE(UUID(),'-','')), 'test2'),
(UNHEX(REPLACE(UUID(),'-','')), 'test3')

MySQL importing a sql file

Posted on by

To simply import a sql file (and get any results printed to the prompt)

mysql -uUSERNAME -p DBNAME < FILENAME

The parameters are
-u username (note no space between the u and USERNAME)
-p password (can be given as the username (without a space between p and PASSWORD; or if omitted mysql will ask for it)
DBNAME is the name of the database to import to (if the sql file don’t create a database on it’s own)
FILENAME is the name of the file to import

Other parameters can be
-h server host (dns name or ip) if -h is omitted then localhost is assumed.
-P port number
-f force (will not break on errors)

To import a sql file and then have to output to a file (for instance when running from a crontab)

mysql -uUSERNAME -p DBNAME < FILENAME > OUTFILE

Works just as the previous import except that any output will be written to OUTFILE.

MySQL creating a database and user from the console

Posted on by

Here is how to quickly create a database and add a user to a mysql database (the mysql console).
(Yes some values need to be changed)

  • mysql> use mysql;
  • mysql> CREATE DATABASE dbname;
  • mysql> INSERT INTO user (Host,User,Password) VALUES (‘localhost’, ‘username’, PASSWORD(‘password’));
  • mysql> GRANT ALL ON dbname.* TO ‘username’;
  • mysql> FLUSH PRIVILEGES;
  • mysql> exit

Update: This approch will not work when using MySQL server under Windows.

mysqldump Couldn’t execute references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)

Posted on by

If you get an error messages like “mysqldump: Couldn’t execute ‘SHOW FIELDS FROM `store_information`’: View ‘database.viewname’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)” then a number of things might be wrong:

  • The view is “corrupted” (this could happen if the table the view is of has been updated in a way so that the view is no longer correct – for instance if the table structure is updated)
  • The user you are using are lacking permissions (to the view/table (that the view is referencing) )

What I usually do when this happens to solve it is to:

  1. Verify if the user running the mysqldump has the permissions (or test as root if you can – as root has more permissions).

    If this is the case: update the permissions on the user running the dump – or change to another user

  2. Try to find out if the table the view is referencing has been updated (think back what have I done/check with the rest of the team)
    Or if you do not wish to/can not do this, check a previous backup up the base table (from when mysqldump worked with the view) and compare the structure with the current structure in the database.

    3. If this is the case: I just drop the view and recreate the view so that it gets corrected in the underlying db structure and re-run the dump.

More info for people using this notebook as a article; here is some manual pages that will help you along if you don’t know how to alter permissions/create views.
More information about user permissions
More information about views