Praying and Running

One of the applications that I get to play with on a regular basis is a PHP, Javascript AJAX environment that acts as a frontend for a MySQL database. I wanted to add range constraints to some of the fields in order to reduce typo's. I created before triggers with the following basic skeleton for each table that I wanted to add the range constraint to. if NEW.columnname > theupperrange then SELECT `**columnname is out of range**` from thetable into @str_val; end if; This will cause the insert to fail with the follow error message Unknown column `**columnname is out of range**` because of the nature of the application (if an error occurs in MySQL the PHP returns the error message) it then becomes the JavaScript's XMLHttprequest.responseText. so on the javascript side of the application i look for the Unknown column like this //req is the name of the XMLHttpRequest object i create to handle interaction between //the javascript and PHP. if(req.responseTest.indexOf("Unknown...

You bring data into a referenced column through LOAD DATA LOCAL INFILE but when you try to insert data into the referencing column you get the "Cannot add or update a foreign key constraint fails...." error. You have looked at the referenced column and the referencing data again and again and you don't see the problem. If you open up Query Browser and look at the referenced data you may find paragraph marks. This problem confounded me for quite some time. I understood that the paragraph marks were causing the problem but I didn't understand how they were getting there. This is a classic case where "RTFM" applies. MySQL by default understands line termination by the return character "\r". The Windows OS by default writes line termination as a carriage return and a new line "\r\n". The extra \n is causing the problem. How to deal with this is right in the MySQL manual. But I thought I would post it because I do and did "RTFM" and...

When you use LOAD DATA LOCAL INFILE on a table that contains a foreign key constraint you get an error with an explanation that a the table cannot be updated because a foreign key constraint fails..... 1. Turn off foreign key checks with the following command at the MySQL command line client a. set foreign_key_checks = 0; 3. issue your load data command again 4. once the data is loaded issue the following query select referencing_table . referencing_column FROM referencing_table LEFT JOIN referenced_table ON ( referencing_table . referencing_column = referenced_table.referenced_column ) WHERE referenced_table.referenced_column IS NULL; This will give you a list of all the column values in the offending table that do not match a value in the referenced table. 5. Update all of the values listed in the query results from above 6. turn the foreign_key_checks back on with the following a. set foreign_key_checks = 1; Why this works We are doing a LEFT JOIN which means that everything on t...