Mysql – load_file() producing null

Back on January 21, 2009, Peter Zaitsev stated the following on mysqlperformanceblog.com

As the call for action – I would surely like someone to see if EXT3 can be fixed in this regard as it is by far the most common file system for Linux. It is also worth to investigate if something can be done on MySQL side – may be opening binlog with O_DSYNC flag if sync_binlog=1 instead of using fsync will help ? Or may be binlog pre-allocation would be good solution.

As of yet, I know of no one having touched this issue. O_DSYNC as a default is not an appealing prospect but does accommodate faster writes that are not really verified. That why there is so much hype around O_DIRECT.

I can tell you do not have the InnoDB Plugin installed. With the InnoDB Plugin, several variables should exist.

You should upgrade InnoDB in one of two ways:

• Upgrade to MySQL 5.5 (All InnoDB Plugin enhancements are part of MySQL 5.5)
• Upgrade the InnoDB Plugin

Once you have done so, you can enhance InnoDB to

• access more CPUs and Cores
• increase read and write I/O threads
• scale the I/O capacity (this is especially needed for different storage media)

Here are my past posts on the settings you can change for this:

• Sep 12, 2011 : Possible to make MySQL use more than one core?
• Sep 20, 2011 : Multi cores and MySQL Performance
• Apr 26, 2012 : Is the CPU performance relevant for a database server?
• Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian

This should do the trick for you:

DELIMITER $$
DROP TRIGGER IF EXISTS `employee_INSERT` $$
CREATE TRIGGER `employee_INSERT` 
AFTER INSERT ON `employee`
FOR EACH ROW
BEGIN
    INSERT INTO employee_tools (Id, Tool)
    SELECT new.Id, tools.Tool_Name 
            FROM tools
            WHERE tools.Division = new.Division;
END $$
DELIMITER ;