MySQL & SQL_Mode Issues

Sometimes I wonder why I’ve started to do things in MySQL, whilst Oracle PL/SQL give me so little trouble in comparison with MySQL.

O.K. SQL_Mode is not something to throw out of the back-door but I really hope that certain issues will get sorted out in the next version of MySQL. Will have to wait and see?

O.K. the next problem I’ve been running into is to insert the current username into a column in a table, and that is all got to with SQL_mode issues. I like the mode stuff coz it give us better data integrity….

Example Code:
#Loose up on the restriction!

SET @OLD_SQL_MODE=@@SQL_MODE;
DROP SCHEMA IF EXISTS TESTIT;
CREATE SCHEMA TESTIT;
USE TESTIT;
DROP TABLE IF EXISTS customer;
CREATE TABLE customer(
custID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
firstName VARCHAR(50) NOT NULL,
creationDATE DATETIME,
lastUpdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
myUserName VARCHAR(50),
lastUserName VARCHAR(50),
PRIMARY KEY (custID)
);
ALTER TABLE customer AUTO_INCREMENT = 1001;
DROP TRIGGER IF EXISTS testCustUpd1;
DELIMITER |
CREATE TRIGGER testCustUpd1 BEFORE INSERT ON customer
FOR EACH ROW BEGIN
SET NEW.creationDate = NOW();
SET NEW.myUserName = USER();
SET NEW.lastUserName = USER();
END;
|
DELIMITER ;
DROP TRIGGER IF EXISTS testCustUpd2;
CREATE TRIGGER testCustUpd2 BEFORE UPDATE ON customer FOR EACH ROW SET NEW.lastUserName = USER();
#LOOSEN STRICT SQL;
SET SESSION SQL_MODE='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
INSERT INTO customer (firstName) VALUES ('Angel');
INSERT INTO customer (firstName) VALUES ('Bennie');
SELECT * FROM customer;
UPDATE customer
SET firstName='Christo'
WHERE firstName='Bennie';
SELECT * FROM customer;
SET SESSION SQL_MODE=@OLD_SQL_MODE;


CONCLUSION
This will still give you a warning but the tables will be updated. Hope we can see these issues resolved in future releases of MySQL.