Tag Archives: mysql

Programming

MySQL CSV import skipping rows

Leave a comment

I was experiencing problems with loading CSV files into MySQL tables. I noticed that it was skipping every other row in most cases. I was using the following SQL code to load the CSV file:

LOAD DATA LOCAL INFILE 'test.csv'
INTO TABLE games
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 0 LINES;

Solution:
It turns out that the CSV file that I was loading had line endings that my MySQL client did not recognize. I found out that the CSV file was coming from a Windows machine while I was trying to load it onto a Mac OSX machine’s MySQL client/server.

To fix this, I ran the following conversion command, which removed the Windows specific line endings:

1
cat test.csv | tr -d '\r' > "test.fixed.csv"

I verified that this fixed the file by opening it up in TextWrangler and noting the line ending style on the bottom status bar.

Helpful Tips

mysqldump and .my.cnf tip to avoid “ignoring option ‘–databases'” error

2 Comments

Don’t like how the mysqldump command keeps reading the .my.cnf and outputting the following warning/error:
mysqldump: ignoring option '--databases' due to invalid value 'dbname'

It’s been reported as a bug to mysql devs, but they keep saying it is by design, and working as intended. Here’s how you should structure the .my.cnf to avoid it:


[client]
user=user1
password=pw1
[mysql]
database=dbname

This way the “mysqldump dbname” doesn’t return that hideous error message and “mysql dbname” also works like a charm.

Helpful Tips

MySQL: Drop all tables with prefix

1 Comment

I use the mysql command line tool to administrate my MySQL. I needed a quick way to drop all tables with the following prefix.

SELECT CONCAT('DROP TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '`;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'prefix%' and TABLE_SCHEMA = 'dbname';

Then all you have to do is take a text editor and replace all the “|” signs around the result.

PS: As you can see the query can be modified to get all tables with suffix, or all tables in a schema, or TRUNCATE all tables in the criteria.

Programming

Convert Django MySQL Database Tables to Unicode

Leave a comment

When I created a Django application, I hadn’t noticed that my MySQL was defaulted to latin character set (probably by Virtualmin or CentOS’s default MySQL values). So I didn’t want to delete my current project and start again. So here are the commands to convert a database to unicode:

for the database

ALTER DATABASE djangodb CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

on each table do

ALTER TABLE djangotablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci
Programming

MySQL trigger error: Explicit or implicit commit is not allowed in stored function or trigger.

3 Comments

I haven’t seen this documented in MySQL docs, so I’ll share this little hidden nuissance. When compiling a trigger, MySQL throws the following error: Explicit or implicit commit is not allowed in stored function or trigger.

What does it mean?
The code inside the trigger is doing a commit. Looking at the code for the trigger alone is not enough, you must check all procedure/function calls, because the code inside any calls could really be (part of) the problem.

Problem?
The problem is usually we look in our code and find that there are no “explicit” commits. The problem is that there is a implicit commit happening somewhere and it is hard to pinpoint where if you didn’t know this one hidden fact:

Depending on version and storage engine, TRUNCATE can cause the table to be dropped and recreated. This provides a much more efficient way of deleting all rows from a table, but it does perform an implicit COMMIT. You might want to use DELETE instead of TRUNCATE.

Moral
DELETE FROM tablename rather than TRUNCATE TABLE tablename.