Re: TIMESTAMP field not automatically updating last_updated field
Hank wrote: Are the other fields in the update statement actually changing the data? I don't know for sure, but if the data on disk is the same as the update statement, mysql won't actually update the record, and therefore might not update the last_updated field also. Just a thought. Yes, I understand that one concept. I have seen it before If you do an update on a record but the actually values that you are passing in the statement are the exact values as were there before, no update to the timestamp field is made because none of the records values actually changed But no, that is not my situation. I've tested it and I am actually changing the values in the table (of course not specifying a new value for the TIMESTAMP field) but still the TIMESTAMP field doesn't auto-update. What disturbes me is that it works fine in one particular table but all the others it works. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TIMESTAMP field not automatically updating last_updated field
Ferindo Middleton Jr wrote: Hank wrote: Are the other fields in the update statement actually changing the data? I don't know for sure, but if the data on disk is the same as the update statement, mysql won't actually update the record, and therefore might not update the last_updated field also. Just a thought. Yes, I understand that one concept. I have seen it before If you do an update on a record but the actually values that you are passing in the statement are the exact values as were there before, no update to the timestamp field is made because none of the records values actually changed But no, that is not my situation. I've tested it and I am actually changing the values in the table (of course not specifying a new value for the TIMESTAMP field) but still the TIMESTAMP field doesn't auto-update. What disturbes me is that it works fine in one particular table but all the others it works. Ferindo I'm running 5.0.19-nt. I haven't had a chance to test it but should it make any difference if I say: last_updated TIMESTAMP, than if I say all this: last_updatedTIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, I think this may be the difference in why some tables are auto incrementing and others aren't. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
TIMESTAMP field not automatically updating last_updated field
I think I've seen this complaint posted before but I ignored but now I realize that in some of my db tables' last_updated field the value is automatically updating on UPDATEs to records while in other tables the last_updated fields for some strange reason aren't automatically updating. I'll usually use the following line in my table declarations: last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, In some tables it automatically updates on subsequent updates to the table and in others it will not. The purpose here is to have the last_updated field automatically append to the current timestamp... the application on the front end doesn't specify the time to MySQL but rather expects that it's always going to be UPDATEd to the current time slot. What am I doing wrong what command should I issue to my tables to correct it? Thanks Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TIMESTAMP field not automatically updating last_updated field
jonathan wrote: are you having two timestamp fields in a table (ie a created and a last_updated)? -j On Mar 30, 2006, at 5:17 PM, Ferindo Middleton Jr wrote: I think I've seen this complaint posted before but I ignored but now I realize that in some of my db tables' last_updated field the value is automatically updating on UPDATEs to records while in other tables the last_updated fields for some strange reason aren't automatically updating. I'll usually use the following line in my table declarations: last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, In some tables it automatically updates on subsequent updates to the table and in others it will not. The purpose here is to have the last_updated field automatically append to the current timestamp... the application on the front end doesn't specify the time to MySQL but rather expects that it's always going to be UPDATEd to the current time slot. What am I doing wrong what command should I issue to my tables to correct it? Thanks Ferindo --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] No just the one timestamp field (last_updated) which I expect to be given a timestamp on the initial INSERT and then continue to be automatically updated to the current time on subsequent UPDATEs to any given row... Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
getting COUNT() TO return 0 for null matches in a query, how?
I have the following query which counts the records from a table called registration that have an schedule_id that matches a record in another table called schedules. The below query works fine but how can I get it to return a COUNT() of 0 each instance where there is no record in the registration table that matches a schedules.id record? SELECT schedules.id, schedules.start_date, schedules.end_date, COUNT(schedules.id) FROM schedules, registration_and_attendance WHERE registration_and_attendance.schedule_id = schedules.id GROUP BY schedules.id ORDER BY start_date -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
including column name headers in OUTFILE output?
If say something like the following from the mysql command line tool: SELECT * FROM dognames INTO OUTFILE 'C:/outfiles/dognames.tab'; How do I get mysql to include the column names in the file's output? Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ignores foreign key relationships between tables?
Paul DuBois wrote: At 20:41 -0500 1/30/06, Ferindo Middleton Jr wrote: Paul DuBois wrote: At 18:03 -0500 1/29/06, Ferindo Middleton Jr wrote: Ferindo Middleton Jr wrote: Ferindo Middleton Jr wrote: Paul DuBois wrote: At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. I am using InnoDB. I use MySQL Administrator and InnoDB is what it says all my tables are already using so it must have chosen that by default or something. Does this mean that I shouldn't have been able to delete records from my schedules table above that had a foreign key in the registration table? Thanks. Ferindo Paul, I discovered that this foreign key constraint wasn't present in these tables anymore due to my own action. You see, I had backed up my database before using MySQL Administrator, not knowing that is was backing up such tables constructs as foreign keys, etc. So the database I'm looking at today isn't the same database I originally created with the same constraints... I'm going to stop using MySQL Administrator... using it seems somewhat misleading and it made me think that the tables sand the constraints I made on them were still present. Thanks. Ferindo I take it back. I imported the data in my database above without using the MySQL Administrator backup utility and first re-CREATEing the db tables in my database But still MySQL still allows for cross-referenced records between my schedules table and the registration table schedule id field to be deleted. Why do you think this is happening. Is this yet another feature that MySQL doesn't really support yet? Is MySQL totally ignoring the REFERENCES part of the schedule_id field from my registration table above. I've determined that I'm using InnoDB so why isn't it working? Ferindo Looking at: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html I see no examples that are missing FOREIGN KEY. What happens if you change your table definition to change: schedule_id INTEGER REFERENCES schedules(id) To: schedule_id INTEGER, FOREIGN KEY (schedule_id) REFERENCES schedules(id) Also, SERIAL is an alias for a BIGINT type, so you'll probably need to make schedule_id a BIGINT. Thanks Paul, However, I tried changing my table definition in a test db like you suggest above to say: schedule_id BIGINT, FOREIGN KEY (schedule_id) REFERENCES schedules(id) ... but I get this error message still which seems to imply that I'm not forming this foreign key constraint correctly: ERROR 1005 (HY000): Can't create table '.\test\registration.frm' (errno: 150) any ideas what I'm doing wrong? I think so. But first, a tip: When you get an error like that from InnoDB, try SHOW ENGINE INNODB STATUS (or just SHOW INNODB STATUS in older versions of MySQL). Part of the output of this statement will likely contain more detail about the error. In this case, the error is my fault. :-) The referenced column and the referencing column must have the same data type, and I said that SERIAL was an alias for a BIGINT column. But it's really a BIGINT UNSIGNED column, so schedule_id has to be BIGINT UNSIGNED as well. And since SERIAL is also NOT NULL, you might as well make schedule_id NOT NULL, too. These definitions worked for me: CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_dateDATE NOT NULL ) ENGINE = InnoDB; CREATE TABLE registration ( idSERIAL
Re: MySQL ignores foreign key relationships between tables?
Paul DuBois wrote: At 18:03 -0500 1/29/06, Ferindo Middleton Jr wrote: Ferindo Middleton Jr wrote: Ferindo Middleton Jr wrote: Paul DuBois wrote: At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. I am using InnoDB. I use MySQL Administrator and InnoDB is what it says all my tables are already using so it must have chosen that by default or something. Does this mean that I shouldn't have been able to delete records from my schedules table above that had a foreign key in the registration table? Thanks. Ferindo Paul, I discovered that this foreign key constraint wasn't present in these tables anymore due to my own action. You see, I had backed up my database before using MySQL Administrator, not knowing that is was backing up such tables constructs as foreign keys, etc. So the database I'm looking at today isn't the same database I originally created with the same constraints... I'm going to stop using MySQL Administrator... using it seems somewhat misleading and it made me think that the tables sand the constraints I made on them were still present. Thanks. Ferindo I take it back. I imported the data in my database above without using the MySQL Administrator backup utility and first re-CREATEing the db tables in my database But still MySQL still allows for cross-referenced records between my schedules table and the registration table schedule id field to be deleted. Why do you think this is happening. Is this yet another feature that MySQL doesn't really support yet? Is MySQL totally ignoring the REFERENCES part of the schedule_id field from my registration table above. I've determined that I'm using InnoDB so why isn't it working? Ferindo Looking at: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html I see no examples that are missing FOREIGN KEY. What happens if you change your table definition to change: schedule_id INTEGER REFERENCES schedules(id) To: schedule_id INTEGER, FOREIGN KEY (schedule_id) REFERENCES schedules(id) Also, SERIAL is an alias for a BIGINT type, so you'll probably need to make schedule_id a BIGINT. Thanks Paul, However, I tried changing my table definition in a test db like you suggest above to say: schedule_id BIGINT, FOREIGN KEY (schedule_id) REFERENCES schedules(id) ... but I get this error message still which seems to imply that I'm not forming this foreign key constraint correctly: ERROR 1005 (HY000): Can't create table '.\test\registration.frm' (errno: 150) any ideas what I'm doing wrong? Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ignores foreign key relationships between tables?
Ferindo Middleton Jr wrote: Ferindo Middleton Jr wrote: Paul DuBois wrote: At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. I am using InnoDB. I use MySQL Administrator and InnoDB is what it says all my tables are already using so it must have chosen that by default or something. Does this mean that I shouldn't have been able to delete records from my schedules table above that had a foreign key in the registration table? Thanks. Ferindo Paul, I discovered that this foreign key constraint wasn't present in these tables anymore due to my own action. You see, I had backed up my database before using MySQL Administrator, not knowing that is was backing up such tables constructs as foreign keys, etc. So the database I'm looking at today isn't the same database I originally created with the same constraints... I'm going to stop using MySQL Administrator... using it seems somewhat misleading and it made me think that the tables sand the constraints I made on them were still present. Thanks. Ferindo I take it back. I imported the data in my database above without using the MySQL Administrator backup utility and first re-CREATEing the db tables in my database But still MySQL still allows for cross-referenced records between my schedules table and the registration table schedule id field to be deleted. Why do you think this is happening. Is this yet another feature that MySQL doesn't really support yet? Is MySQL totally ignoring the REFERENCES part of the schedule_id field from my registration table above. I've determined that I'm using InnoDB so why isn't it working? Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL ignores foreign key relationships between tables?
I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ignores foreign key relationships between tables?
Paul DuBois wrote: At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. I am using InnoDB. I use MySQL Administrator and InnoDB is what it says all my tables are already using so it must have chosen that by default or something. Does this mean that I shouldn't have been able to delete records from my schedules table above that had a foreign key in the registration table? Thanks. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL ignores foreign key relationships between tables?
Ferindo Middleton Jr wrote: Paul DuBois wrote: At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote: I have two tables, registration schedules, that look like this: CREATE TABLE registration ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, schedule_id INTEGER REFERENCES schedules(id), ); CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, ); The registration table above references the the schedules table via the schedule_id. Why does MySQL allow a row created in the schedules table be DELETED if it has a matching schedule_id in the registration table. These two tables share a relationship based on registration.schedule_id schedules.id. I've tried this same syntax in PostgreSQL and it doesn't allow the schedules.id record to be deleted without first removing any records in the registration table which carry a matching schedule_id record. Isn't that the point of a relational database?- TO CHECK RELATIONSHIPS between tables and enforce that those relationships aren't broken? I find it disappointing that MySQL ignores this relationship. Add ENGINE = InnoDB to the end of your table definitions. Foreign keys are supported only for InnoDB tables in MySQL. I am using InnoDB. I use MySQL Administrator and InnoDB is what it says all my tables are already using so it must have chosen that by default or something. Does this mean that I shouldn't have been able to delete records from my schedules table above that had a foreign key in the registration table? Thanks. Ferindo Paul, I discovered that this foreign key constraint wasn't present in these tables anymore due to my own action. You see, I had backed up my database before using MySQL Administrator, not knowing that is was backing up such tables constructs as foreign keys, etc. So the database I'm looking at today isn't the same database I originally created with the same constraints... I'm going to stop using MySQL Administrator... using it seems somewhat misleading and it made me think that the tables sand the constraints I made on them were still present. Thanks. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with using CONSTRAINT declaration
I have the following table where I have a CHECK CONSTRAINT to check for logical data values but for some reason it's not working on INSERTs to the table. MySQL doesn't give any error message when I CREATE TABLE. Any ideas what I'm doing wrong?... or Is this type of declaration not supported... What command can you issue from the command line to check the existence of CONTRAINT declarations such as this? CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, start_time TIME, end_time TIME, CONSTRAINT end_date_cannot_be_before_start_date CHECK (end_date = start_date), CONSTRAINT end_time_cannot_be_before_start_time CHECK (end_time = start_time), PRIMARY KEY (class_id, start_date, end_date, start_time, end_time) ); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SETting values to TABLE field at TRIGGER runtime
Gleb Paharenko wrote: Hello. It seems that you forgot to OPEN the cursor. The trigger should be similar to this one: CREATE TRIGGER trigger_registration_and_attendance_before_insert BEFORE INSERT ON registration_and_attendance FOR EACH ROW BEGIN DECLARE schedule_class_id INT; DECLARE schedule_class_id_cursor CURSOR FOR SELECT class_id FROM schedules WHERE schedules.id = new.schedule_id; OPEN schedule_class_id_cursor; FETCH schedule_class_id_cursor INTO schedule_class_id; SET new.class_id = schedule_class_id; CLOSE schedule_class_id_cursor ; END; Ferindo Middleton Jr wrote: Is it possible to SET values on fields that involve the TABLE that invoked the TRIGGER with SET actions. I have the following lines in my trigger: delimiter // CREATE TRIGGER trigger_registration_and_attendance_before_insert BEFORE INSERT ON registration_and_attendance FOR EACH ROW BEGIN DECLARE schedule_class_id INT; DECLARE schedule_class_id_cursor CURSOR FOR SELECT class_id FROM schedules WHERE schedules.id = new.schedule_id; FETCH schedule_class_id_cursor INTO schedule_class_id; SET new.class_id = schedule_class_id; END; The server accepts this but new.class_id doesn't get a value when I do an INSERT. Why won't this work? Ferindo Hi, I tried the code above, opening and the cursor before assigning the value called from the declaration into new.class_id but it still doesn't work. The class_id field isn't picking up the value it should from my schedules table. I can't figure out why - frustrating this. Thanks. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SETting values to TABLE field at TRIGGER runtime - FLAW IN MYSQL TRIGGER IMPLEMENTATION?
Ferindo Middleton Jr wrote: Gleb Paharenko wrote: Hello. It seems that you forgot to OPEN the cursor. The trigger should be similar to this one: CREATE TRIGGER trigger_registration_and_attendance_before_insert BEFORE INSERT ON registration_and_attendance FOR EACH ROW BEGIN DECLARE schedule_class_id INT; DECLARE schedule_class_id_cursor CURSOR FOR SELECT class_id FROM schedules WHERE schedules.id = new.schedule_id; OPEN schedule_class_id_cursor; FETCH schedule_class_id_cursor INTO schedule_class_id; SET new.class_id = schedule_class_id; CLOSE schedule_class_id_cursor ; END; Ferindo Middleton Jr wrote: Is it possible to SET values on fields that involve the TABLE that invoked the TRIGGER with SET actions. I have the following lines in my trigger: delimiter // CREATE TRIGGER trigger_registration_and_attendance_before_insert BEFORE INSERT ON registration_and_attendance FOR EACH ROW BEGIN DECLARE schedule_class_id INT; DECLARE schedule_class_id_cursor CURSOR FOR SELECT class_id FROM schedules WHERE schedules.id = new.schedule_id; FETCH schedule_class_id_cursor INTO schedule_class_id; SET new.class_id = schedule_class_id; END; The server accepts this but new.class_id doesn't get a value when I do an INSERT. Why won't this work? Ferindo Hi, I tried the code above, opening and the cursor before assigning the value called from the declaration into new.class_id but it still doesn't work. The class_id field isn't picking up the value it should from my schedules table. I can't figure out why - frustrating this. Thanks. Ferindo Hello again Gleb, After further testing I have determined that my statement above is incorrect... The trigger above is being evaluated... well SORT OF. The class_id field is part of the primary key of registration_attendance table. With the application I developed to load data into these tables, if I do something like force an arbitrary value for the class_id field within the application, the system works on INSERTs and the trigger appears to be executed -overwriting- whatever value I manually hard coded into the application to be passed to the class_id field. As you can see this trigger is supposed to happen BEFORE INSERT but it appears data from my application is being evaluated into the table before the trigger fires. I guess I could just force an arbitrary value on the field as a workaround but isn't this a flaw. Shouldn't the trigger be executed before the database evaluates data against the table? I've used a trigger similar to this in Postgresql and the Postgresql db wouldn't introduce the data to the table until after the trigger executes which is how it should be. This appears to be a flaw in the MySQL implementation of TRIGGER implementation BEFORE INSERT. It appears that in MySQL, what it may be doing is: 1. Evaluate the data against the table although not committing the INSERT data 2. Execute the BEFORE INSERT TRIGGER 3. Then actually INSERT the data - When I should Perform Step 2 from above, Executing the Trigger before beginning any evaluation of the data into the database table. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SETting values to TABLE field at TRIGGER runtime
Is it possible to SET values on fields that involve the TABLE that invoked the TRIGGER with SET actions. I have the following lines in my trigger: delimiter // CREATE TRIGGER trigger_registration_and_attendance_before_insert BEFORE INSERT ON registration_and_attendance FOR EACH ROW BEGIN DECLARE schedule_class_id INT; DECLARE schedule_class_id_cursor CURSOR FOR SELECT class_id FROM schedules WHERE schedules.id = new.schedule_id; FETCH schedule_class_id_cursor INTO schedule_class_id; SET new.class_id = schedule_class_id; END; The server accepts this but new.class_id doesn't get a value when I do an INSERT. Why won't this work? Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with TRIGGER, unresponsive
I have these two tables: 'registration_and attendance' and 'schedules' They both share a common class_id field. I'm trying to write a Trigger which will set the class_id field for 'registration_and attendance' equal to the schedules.class_id matching the registration_and_attendance.schedule_id The below trigger doesn't return any error message when I try to load it into my db but the end result should be a value that registration_and_attendance table picks up for the class_id matching the foreign key, schedule_id, the two tables share. However, nothing happens. there is no value in 'new.class_id' on INSERTs to registration_and_attendance. delimiter // CREATE TRIGGER trigger_registration_and_attendance_before_insert BEFORE INSERT ON registration_and_attendance FOR EACH ROW BEGIN DECLARE schedule_class_id, b INT; DECLARE schedule_class_id_cursor CURSOR FOR SELECT class_id FROM schedules WHERE schedules.id = new.schedule_id; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN schedule_class_id_cursor; REPEAT FETCH schedule_class_id_cursor INTO schedule_class_id; UNTIL b = 1/* I wonder if this loop is even necessary because schedule_class_id_cursor should only return one value anyway */ END REPEAT; CLOSE schedule_class_id_cursor; SET new.class_id = schedule_class_id; END; // -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
parse error creating table
I have been trying to create a table but mysql 5.0.15-nt-max is having a problem parsing the statement. Anyone know what the problem is in the syntax of the following table creation statement: CREATE TABLE registration_and_attendance ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, sf182_receivedBOOLEAN NOT NULL DEFAULT TRUE, registrant_email_address TEXT, cc_email_list TEXT, bureau_id INTEGER REFERENCES bureaus(id), office TEXT, class_id INTEGER NOT NULL REFERENCES classes(id), schedule_id INTEGER REFERENCES schedules(id), start_date DATE, end_date DATE, enrolled BOOLEAN, attendedBOOLEAN, completed BOOLEAN, cancelledBOOLEAN DEFAULT FALSE, cancelled_commentsTEXT, comments TEXT, email_confirmation_sent BOOLEAN NOT NULL, employment_status_id INTEGER REFERENCES employment_statuses(id) NOT NULL, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, last_user_updated TEXT, waitlisted BOOLEAN DEFAULT FALSE, overflow_registrantBOOLEAN DEFAULT FALSE, attach_hotel_listing_directions BOOLEAN, instructor_legacy TEXT, time_legacy TIME WITHOUT TIME ZONE, ssn_legacy TEXT, position_grade_title TEXT, office_phone_legacy TEXT, contractor_legacy BOOLEAN, no_show_legacy BOOLEAN, status_legacy TEXT, funding_id INTEGER REFERENCES funding_types(id), PRIMARY KEY (firstname, lastname, class_id, start_date, end_date) ); I get the following error message with the above statement but I can't figure out what the problem is: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT N ULL, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, l' at line 23 Thanks, Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: parse error creating table
Thanks Peter. I did originally use this table in a Postgresql db. Thanks for you advice. Your suggestions below allowed me to create this table and I learned a thing t two about proper usage of the TIMESTAMP data type. The intended effect is to get a timestamp field that inserts the current system time on inserts and continues to update the field with the current timestamp on updates without the application or use needing to specify it are you saying that the timestamp attribute alone will do that? Ferindo Peter Brawley wrote: Ferindo One problem is: employment_status_id INTEGER REFERENCES employment_statuses(id) NOT NULL, NOT NULL should be before REFERENCES. Also, in: last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, (i) NOT NULL is superfluous since the default is given by CURRENT_TIMESTAMP. (ii) specifying DEFAULT CURRENT_TIMESTAMP defeats auto-resetting of the timestamp on updates. Is that what you want? To get auto-setting on INSERTs and UPDATEs, just write last_updated TIMESTAMP, Also the manual doesn't mention TIME WITHOUT TIME ZONE. Are you thinking of PostgreSQL? PB - Ferindo Middleton Jr wrote: I have been trying to create a table but mysql 5.0.15-nt-max is having a problem parsing the statement. Anyone know what the problem is in the syntax of the following table creation statement: CREATE TABLE registration_and_attendance ( idSERIAL NOT NULL UNIQUE, firstnameVARCHAR(256) NOT NULL, middlenameTEXT, lastnameVARCHAR(256), suffix TEXT, sf182_receivedBOOLEAN NOT NULL DEFAULT TRUE, registrant_email_address TEXT, cc_email_list TEXT, bureau_id INTEGER REFERENCES bureaus(id), office TEXT, class_id INTEGER NOT NULL REFERENCES classes(id), schedule_id INTEGER REFERENCES schedules(id), start_date DATE, end_date DATE, enrolled BOOLEAN, attendedBOOLEAN, completed BOOLEAN, cancelledBOOLEAN DEFAULT FALSE, cancelled_commentsTEXT, comments TEXT, email_confirmation_sent BOOLEAN NOT NULL, employment_status_id INTEGER REFERENCES employment_statuses(id) NOT NULL, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, last_user_updated TEXT, waitlisted BOOLEAN DEFAULT FALSE, overflow_registrantBOOLEAN DEFAULT FALSE, attach_hotel_listing_directions BOOLEAN, instructor_legacy TEXT, time_legacy TIME WITHOUT TIME ZONE, ssn_legacy TEXT, position_grade_title TEXT, office_phone_legacy TEXT, contractor_legacy BOOLEAN, no_show_legacy BOOLEAN, status_legacy TEXT, funding_id INTEGER REFERENCES funding_types(id), PRIMARY KEY (firstname, lastname, class_id, start_date, end_date) ); I get the following error message with the above statement but I can't figure out what the problem is: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT N ULL, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, l' at line 23 Thanks, Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UNIQUE constraint, proper use
I have this SQL statement: CREATE TABLE rooms ( idSERIAL, room_name TEXT UNIQUE, location TEXT, last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, last_user_updatedTEXT, PRIMARY KEY(id) ); When I run this I get the following error: ERROR 1170 (42000): BLOB/TEXT column 'room_name' used in key specification without a key length I'm more used to PostgreSQL and when I run this command there, I don't get this error message. I recognized that by changing room_name to a varchar type, I won't get the error message. Also, the PostgreSQL mailing lists had a special group from SQL-related issue, but I didn't see one of these types of lists in the lists of groups for MySQL community so I'm sorry if I irritate anyone by posting to the wrong group. Thanks. Ferindo Middleton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
