MySQL: How to insert blob in table

In order to insert a blob (image, txt, etc) in table FOO in MySQL you should execute the LOAD_FILE [1] statement:

mysql> insert into FOO(data) values(LOAD_FILE('/path/to/file.txt'));

But in my case NULLs were inserted in column data.


Troubleshooting [2]:
mysql> select load_file('/path/to/file.txt'); -- returns NULL

Solution: file.txt should be owned by mysql user/group (although its permissions are -rw-rw-r--; i.e. granted read access to others)

$ sudo chown mysql:mysql /path/to/file.txt



References

[1] From the manual:

    LOAD_FILE(file_name)

    Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full path name to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes. If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

    If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.

    As of MySQL 5.0.19, the character_set_filesystem system variable controls interpretation of file names that are given as literal strings.

[2] Thanks StackOverflow
    

MySQL error: Cannot delete or update a parent row: a foreign key constraint fails"

When trying to delete table foo(DROP TABLE foo1;) I got the following error:

Cannot delete or update a parent row: a foreign key constraint fails


As root execute the following to see what in what state is the db:

SHOW ENGINE INNODB STATUS;
------------------------
LATEST FOREIGN KEY ERROR
------------------------
140729 23:16:08  Cannot drop table `mydb`.`foo1`
because it is referenced by `mydb`.`foo2`


This tells us that table foo2 has fk to foo1. So remove the fk in foo2 (set it to null) or drop foo2 (this was the case for me since foo2 was empty -- db was in an inconsistent state).

How to bind MySQL to multiple addresses

/etc/mysql/my.cnf: bind-address = 0.0.0.0

How to install Tora with support for MySQL

sudo apt-get install tora libqt4-sql-mysql 

How to generate an Entity Relationship (ER) diagram of a schema from a MySQL connection

Using MySQL Workbench
Install MySQL Workbench (http://micharg.blogspot.com/2011/11/how-to-install-mysql-workbench.html)

Create connection to a db/schema which already exists:

Open MySQL Workbench and: Database > Reverse Engineer > ...

How to install MySQL Workbench

Ubuntu Oneiric (11.10):

sudo apt-add-repository ppa:olivier-berten/misc
sudo apt-get update
sudo apt-get install mysql-workbench-gpl

MySQL: ERROR 1010 (HY000): Error dropping database (can't rmdir './foodb', errno: 39)

The problem

mysql> drop database foodb;
ERROR 1010 (HY000): Error dropping database (can't rmdir './foodb', errno: 39)

The solution

# cd /var/lib/mysql 
var/lib/mysql# ls
foodb
/var/lib/mysql# rm -fr foodb
mysql> drop database foodb;
ERROR 1008 (HY000): Can't drop database 'foodb'; database doesn't exist
mysql> create database foodb;
Query OK, 1 row affected (0.00 sec)
mysql> drop database foodb;
Query OK, 0 rows affected (3.38 sec)

enable UTF8 for all future tables in MySQL

If you want to store strings != english then you have to enable UTF8 in MySQL.

Create a MySQL db with UTF8 support

$ mysql -u root -p
mysql> CREATE USER 'micharg'@'%' IDENTIFIED BY 'micharg1234';
mysql> CREATE DATABASE mydb DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
mysql> GRANT ALL ON mydb.* TO 'micharg'@'localhost' identified by 'micharg1234'; 

Manage users in MySQL

## show all users
mysql> select host, user, password from mysql.user;

## create user
mysql> CREATE USER 'micharg'@'%' IDENTIFIED BY 'micharg1234';

## delete user
mysql> drop user micharg;

Set max blob size in MySQL

Edit /etc/mysql/my.cnf (Linux) or my.ini (Windows) to prevent exceptions "BatchUpdateException: Packet for query is too large (####### > 1048576)".
Default blob size is 1MB.

eg for uploading max 32MB

[mysqld]
max_allowed_packet=32M

[mysqldump]
max_allowed_packet=32M

MySQL Reset Password

1. start mysqld and restart it with the --skip-grant-tables option.

MySQL Transaction Support

Tables must be InnoDB [http://www.insaneprogramming.be/?p=200] for transaction support

In order to set default tables type edit /etc/mysql/my.cnf:
default-table-type=innodb

execute sql script

$ mysql>source /path/to/script.sql