Re: not allowing empty strings
Well, maybe my problem isn't necessarily on user input. My app does check for this and not allow empty strings. I guess the problem more so exists because I get files from the client to LOAD into the database. I have several SQL scripts in a library that I choose from to load the data, different scripts will be run based on the various formats the client sends to me to load the data. Maybe I should do a better job examining and tweaking my various scripts I have for checking and handling such empty string values when loading files. But I just wanted to build some kind of constraint into the database itself so just in case I forget certain details, maybe the constraint would be there within the database itself. I also plan to hand over such operations to other admins eventually and I wanted the constraint to be in the structure of the tables where necessary. I used to use PostgreSQL and I think this feature was easier to command using CREATE TABLE. I imagine this kind of feature would be an enhancement that could otherwise be avoided by a more aggressive quality control by a db admin but I think it would be a good idea to have some kind of NOT EMPTY constraint on a database considering NULL and an empty string ( '' ) are separate values. In the real world they really mean the same thing and the solution seems obvious to put such a constraint at the database creation level (in the structure) when you think about it in practical terms. That way you could have a safeguard so developers and db admins wouldn't have to worry about, especially when some them have very large sets of data to manage and pass among to different organizations when trying to integrate data between different systems, all of which, again, share the practical idea that an empty string is equal in value to a NULL value. Ferindo On Sat, Mar 22, 2008 at 8:20 PM, Rob Wultsch [EMAIL PROTECTED] wrote: On Sat, Mar 22, 2008 at 5:03 PM, Ferindo Middleton [EMAIL PROTECTED] wrote: Is there a way to not allow empty strings in the database for a data type. I have a column set to not null but sometimes users enter empty strings which are also unacceptable. How can I force MySQL to disallow empty strings in addition to not null. Ferindo A trigger should work for you. http://dev.mysql.com/doc/refman/5.0/en/triggers.html A far better option would be validating in your app. -- Rob Wultsch -- Ferindo Middleton Web Services 2.0 Java Servlet Container Administrator and Application Developer/MySQL and PostgreSQL Database Administrator/Infrastructure and Integration Management Specialist specializing in Linux/MySQL/Apache Tomcat web application development and open source solutions/Perception Augmentation and Artificial Synapsis Control Supplementation Research Specialist for AI Wetware-to-Software Interface and Design -Sleekcollar-
not allowing empty strings
Is there a way to not allow empty strings in the database for a data type. I have a column set to not null but sometimes users enter empty strings which are also unacceptable. How can I force MySQL to disallow empty strings in addition to not null. Ferindo
finding duplicate key
I have a table (customers) without a primary key. I want to make the email_address field the primary key, only problem is, several records already share the same email_address How do I write a query which will show me all the instances where email_address is duplicated throughout the table. -- Ferindo
How do you allow external computers to access server instance?
I've installed MySQL5 on a machine running Windows XP. I'm not an advanced user so I chose all the default configuration settings when I went through the setup wizard for the server instance. The database works fine and I can access it when I'm sitting at the computer through the command line client and MySQL Query Browser. However, if I try to access the server instance from another computer on my LAN via MySQL Query Browser, I get a message saying the connection is refused How do I configure the server to allow incoming connections from other computers on my network? -- Ferindo Middleton -Sleekcollar-
Re: How do you allow external computers to access server instance?
I found how to bind to addrees to but didn't find anything in my.ini about
skip-networking but now I have this problem where the I can't connect
locally sitting at the computer using hostname localhost if I type in
the IP address of the computer I get a messsage saying {Hostname} is not
allowed to connect to this MySQL server
Ferindo
On 6/21/07, Baron Schwartz [EMAIL PROTECTED] wrote:
Hi,
Ferindo Middleton wrote:
I've installed MySQL5 on a machine running Windows XP. I'm not an
advanced
user so I chose all the default configuration settings when I went
through
the setup wizard for the server instance. The database works fine and I
can
access it when I'm sitting at the computer through the command line
client
and MySQL Query Browser. However, if I try to access the server instance
from another computer on my LAN via MySQL Query Browser, I get a message
saying the connection is refused How do I configure the server to
allow
incoming connections from other computers on my network?
Try to configure bind-address to the server's IP address, and ensure
skip-networking is not defined.
Baron
--
Ferindo Middleton
Web Application Developer/Database Administrator/IT Infrastructure and
Integration Management Specialist/Perception Augmentation and Control
Supplementation Research Specialist for AI Wetware-to-Software Interface and
Design
-Sleekcollar-
loop through SELECT statement query results in a Trigger
Is there a way to loop through individual query records within a stored procedure or trigger. If I have table called client_names (id SERIAL, first name TEXT, middlename TEXT, lastname TEXT, suffix TEXT, pet_id INT, properly_trained TEXT) and I have a trigger on it, I'd like to iterate through individual query rows back at another table having a foreign key on pet_id. For example: CREATE TRIGGER update_clients_with_week_assignment_based_on_pet_id_in_pets_table BEFORE INSERT ON client_names FOR EACH ROW BEGIN (SELECT * FROM pets;) label1: LOOP IF (pets row.pet_type) = 4 THEN SET properly_trained = 1; ITERATE label1; END IF; LEAVE label1; END LOOP label1; SET @x = p1; END Is this possible. Can you loop through the query results of a SELECT statement in a trigger, function, or procedure. Ferindo
Re: change format of date fields during LOAD DATA INFILE?
I was using OpenOffice... And I couldn't get it to keep the format -mm-dd I saw on screen in that format when I went to save it as a text file I was able to I suppose this should be reported to their developers as an enhancement. There's no way to get MySQL to accept dates in a different format when performing the operation on the command line though? Ferindo On 10/16/06, Jerry Schwartz [EMAIL PROTECTED] wrote: I just tested it with Excel, as it will save the date as seen if you save the worksheet to a text file. I do this quite a bit, actually, to put spreadsheet data into MySQL. Often I use Excel macros to construct entire UPDATE or INSERT statements, and save those into a text file for MySQL to inhale. I can't speak for OpenOffice. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Ferindo Middleton [mailto:[EMAIL PROTECTED] ] Sent: Saturday, October 14, 2006 9:40 PM To: mysql Subject: change format of date fields during LOAD DATA INFILE? Is there a way to change the format of date fields MySQL is expecting when LOADing data from a file? I have no problem with the format MySQL saves the date but most spreadsheet programs I use don't make it easy to export text files with date fields in the format -MM-DD even if I formated the field that way on-screen. It would be great if you could tell MySQL on the command line to expect dates in the format Month/Day/Year or something like that and be able to interpret that and convert the date to the format it's expecting on the fly. -- Ferindo
Re: help with update query
I agree. I should check for empty strings intead of nulls. The application doesn't convert them to null and the default value when a user leaves the field blank on the web page is to save it as an empty string. Thanks. Ferindo On 10/16/06, Jerry Schwartz [EMAIL PROTECTED] wrote: You might want to check for an empty string () rather than null. From what I can tell, HTML forms don't give you NULL values if you leave fields empty, they return . Unless your programs internally convert empty strings to NULL, you won't find NULL in your table. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Ferindo Middleton [mailto:[EMAIL PROTECTED] Sent: Saturday, October 14, 2006 9:16 PM To: Dan Buettner Cc: mysql Subject: Re: help with update query Thanks Dan. This does help. This a pretty straight-forward idea. I could even save the results of this query to a text file and possibly review it a little before running it so I don't acidentally do anything funky and I could see the impact this would have on the data before applying it. I think maybe I'll even add a WHERE email_address IS NULL line within the UPDATE concatenation so I don't overwrite any records that already have an email_address. I'll try this. Thanks alot! Ferindo On 10/14/06, Dan Buettner [EMAIL PROTECTED] wrote: Ferindo, I had a similar task recently, and the problem you'll run into is that you can't select from and update the same table at once. What I ended up doing was doing a SELECT to build the update queries for me. Something like this: SELECT CONCAT( UPDATE bowler_score SET email_address = ', email_address, ' , WHERE firstname = ', firstname, ' , AND middlename = ', middlename, ' , AND lastname = ', lastname, ' , AND race = ', race, ' , AND religion = ', religion, '; ) FROM bowler_score WHERE email_address LIKE [EMAIL PROTECTED] This finds all the entries where there appears to be a valid email address (contains @), and updates all the other records for that individual. Note this is not very efficient, since a LOT of update queries will be generated, and also that if one person has more than one email address (a typo perhaps) you will lose all but one address for them. But it should work, and it's pretty easy. HTH, Dan On 10/13/06, Ferindo Middleton [EMAIL PROTECTED] wrote: I have a table, bowler_score_records, with the following columns: id, firstname, middlename, lastname, race, religion, email_address, bowling_score, gamedate As records get entered to this table, sometimes the users forget to input the email_address but the users always capture the full name, race, and religion. Assuming that no two individuals (bowlers) would happen to have the same name, race, and religion. I need to write a query to update the email_address for all the records where the users forgot to input it based on the idea that records carrying the same full name, race, and religion are in fact the same person, hence the same email_address. Based on the schema described above, how would you write it? -- Ferindo -- Ferindo Middleton Web Application Developer/Database Administrator/IT Infrastructure and Integration Management Specialist/Perception Augmentation and Control Supplementation Research Specialist for AI Wetware-to-Software Interface and Design -Sleekcollar- -- Ferindo Middleton Web Application Developer/Database Administrator/IT Infrastructure and Integration Management Specialist/Perception Augmentation and Control Supplementation Research Specialist for AI Wetware-to-Software Interface and Design -Sleekcollar-
Re: help with update query
Thanks Dan. This does help. This a pretty straight-forward idea. I could even save the results of this query to a text file and possibly review it a little before running it so I don't acidentally do anything funky and I could see the impact this would have on the data before applying it. I think maybe I'll even add a WHERE email_address IS NULL line within the UPDATE concatenation so I don't overwrite any records that already have an email_address. I'll try this. Thanks alot! Ferindo On 10/14/06, Dan Buettner [EMAIL PROTECTED] wrote: Ferindo, I had a similar task recently, and the problem you'll run into is that you can't select from and update the same table at once. What I ended up doing was doing a SELECT to build the update queries for me. Something like this: SELECT CONCAT( UPDATE bowler_score SET email_address = ', email_address, ' , WHERE firstname = ', firstname, ' , AND middlename = ', middlename, ' , AND lastname = ', lastname, ' , AND race = ', race, ' , AND religion = ', religion, '; ) FROM bowler_score WHERE email_address LIKE [EMAIL PROTECTED] This finds all the entries where there appears to be a valid email address (contains @), and updates all the other records for that individual. Note this is not very efficient, since a LOT of update queries will be generated, and also that if one person has more than one email address (a typo perhaps) you will lose all but one address for them. But it should work, and it's pretty easy. HTH, Dan On 10/13/06, Ferindo Middleton [EMAIL PROTECTED] wrote: I have a table, bowler_score_records, with the following columns: id, firstname, middlename, lastname, race, religion, email_address, bowling_score, gamedate As records get entered to this table, sometimes the users forget to input the email_address but the users always capture the full name, race, and religion. Assuming that no two individuals (bowlers) would happen to have the same name, race, and religion. I need to write a query to update the email_address for all the records where the users forgot to input it based on the idea that records carrying the same full name, race, and religion are in fact the same person, hence the same email_address. Based on the schema described above, how would you write it? -- Ferindo -- Ferindo Middleton Web Application Developer/Database Administrator/IT Infrastructure and Integration Management Specialist/Perception Augmentation and Control Supplementation Research Specialist for AI Wetware-to-Software Interface and Design -Sleekcollar-
change format of date fields during LOAD DATA INFILE?
Is there a way to change the format of date fields MySQL is expecting when LOADing data from a file? I have no problem with the format MySQL saves the date but most spreadsheet programs I use don't make it easy to export text files with date fields in the format -MM-DD even if I formated the field that way on-screen. It would be great if you could tell MySQL on the command line to expect dates in the format Month/Day/Year or something like that and be able to interpret that and convert the date to the format it's expecting on the fly. -- Ferindo
help with update query
I have a table, bowler_score_records, with the following columns: id, firstname, middlename, lastname, race, religion, email_address, bowling_score, gamedate As records get entered to this table, sometimes the users forget to input the email_address but the users always capture the full name, race, and religion. Assuming that no two individuals (bowlers) would happen to have the same name, race, and religion. I need to write a query to update the email_address for all the records where the users forgot to input it based on the idea that records carrying the same full name, race, and religion are in fact the same person, hence the same email_address. Based on the schema described above, how would you write it? -- Ferindo
multiple primary keys on one table?
I have a primary key set on a table which consists of the combination of the values: firstname, lastname, and a schedule_id (BIGINT(20))... I have this so the records in this table do not have duplicates, being that no one record should have the exact same name and schedule_id identifier. However, I want to keep this same restriction while also ensuring that no two records have the same email_address and schedule_id identifier... You can't have the db enforce two different primary keys on one table, so how would I implement having this kind of restriction, which, in itself, seems to require that I have a second primary key to enforce another constraint to dissalow records to be added that carry the same combination of: email_address and schedule_id? just ferindo
Re: multiple primary keys on one table?
Thanks. I now have this issue where I can't create the unique index on this table because the email_address is often times blank or unknown for a while (The front-end_application sets is value to '' (blank) on inserts and updates if the user doesn't enter it so instead of going in as NULL, the field gets a blank field value... Is there a way to get UNIQUE index to treat blank data fields that are part of the index as null and not enforce the constraint if the subject columns are blank (whitespace)... Do I need to force the application to not pas the value into the field and make it NULL instead. just ferindo On 10/5/06, Dan Buettner [EMAIL PROTECTED] wrote: Ferindo, you can create multiple UNIQUE indexes on a table to enforce your data requirements. http://dev.mysql.com/doc/refman/5.0/en/alter-table.html Dan On 10/5/06, Ferindo Middleton [EMAIL PROTECTED] wrote: I have a primary key set on a table which consists of the combination of the values: firstname, lastname, and a schedule_id (BIGINT(20))... I have this so the records in this table do not have duplicates, being that no one record should have the exact same name and schedule_id identifier. However, I want to keep this same restriction while also ensuring that no two records have the same email_address and schedule_id identifier... You can't have the db enforce two different primary keys on one table, so how would I implement having this kind of restriction, which, in itself, seems to require that I have a second primary key to enforce another constraint to dissalow records to be added that carry the same combination of: email_address and schedule_id? just ferindo
unexpected trigger behavior on BEFORE UPDATE trigger using NEW. and OLD. column values
I have two databases that effect each other when triggers get excecuted. There is a schedules database that updates registration database. The problem I have is with the enrolled, attended, waitlisted, completed, cancelled, etc. booleans values. The registration db has triggers on it that enforce certain logical rules to be enforced to ensure that logical registration statuses get correctly (Ex. the user of the database should be enrolled=true if cancelled=true) The problem I have is that, ON schedule UPDATEs, the registration data gets defaulted back to enrolled = true even if pre-existing regitration data in the db is already set to say attend, or completed status. I will paste the two triggers below if you might just notic something wrong with the logic causing this unexpected behavior when the trigger defaults pre-existing registration data to match the enrolled state: CREATE TRIGGER trigger_on_schedule_updates AFTER UPDATE ON schedules FOR EACH ROW BEGIN UPDATE registration_and_attendance SET class_id = new.class_id, start_date = new.start_date, end_date = new.end_date WHERE schedule_id = new.id; END; CREATE TRIGGER trigger_registration_and_attendance_before_update BEFORE UPDATE ON registration_and_attendance FOR EACH ROW BEGIN IF (new.enrolled = true) THEN SET new.attended = false; SET new.completed = false; SET new.waitlisted = false; SET new.cancelled = false; END IF; IF (new.attended = true) THEN SET new.enrolled = true; SET new.waitlisted = false; SET new.completed = false; SET new.cancelled = false; END IF; IF (new.completed = true) THEN SET new.enrolled = true; SET new.attended = true; SET new.waitlisted = false; SET new.cancelled = false; END IF; IF (new.waitlisted = true) THEN SET new.enrolled = false; SET new.attended = false; SET new.completed = false; SET new.cancelled = false; END IF; IF (new.cancelled = true) THEN SET new.enrolled = false; SET new.attended = false; SET new.completed = false; SET new.waitlisted = false; SET new.overflow_registrant = false; END IF; END; Ferindo -- justferindo
unexpected trigger behavior on trigger
I have two databases that effect each other when triggers get excecuted. There is a schedules database that updates registration database. The problem I have is with the enrolled, attended, waitlisted, completed, cancelled, etc. booleans values. The registration db has triggers on it that enforce certain logical rules to be enforced to ensure that logical registration statuses get correctly (Ex. the user of the database should be enrolled=true if cancelled=true) The problem I have is that, ON schedule UPDATEs, the registration data gets defaulted back to enrolled = true even if pre-existing regitration data in the db is already set to say attend, or completed status. I will paste the two triggers below if you might just notic something wrong with the logic causing this unexpected behavior when the trigger defaults pre-existing registration data to match the enrolled state: CREATE TRIGGER trigger_on_schedule_updates AFTER UPDATE ON schedules FOR EACH ROW BEGIN UPDATE registration_and_attendance SET class_id = new.class_id, start_date = new.start_date, end_date = new.end_date WHERE schedule_id = new.id; END; CREATE TRIGGER trigger_registration_and_attendance_before_update BEFORE UPDATE ON registration_and_attendance FOR EACH ROW BEGIN IF (new.enrolled = true) THEN SET new.attended = false; SET new.completed = false; SET new.waitlisted = false; SET new.cancelled = false; END IF; IF (new.attended = true) THEN SET new.enrolled = true; SET new.waitlisted = false; SET new.completed = false; SET new.cancelled = false; END IF; IF (new.completed = true) THEN SET new.enrolled = true; SET new.attended = true; SET new.waitlisted = false; SET new.cancelled = false; END IF; IF (new.waitlisted = true) THEN SET new.enrolled = false; SET new.attended = false; SET new.completed = false; SET new.cancelled = false; END IF; IF (new.cancelled = true) THEN SET new.enrolled = false; SET new.attended = false; SET new.completed = false; SET new.waitlisted = false; SET new.overflow_registrant = false; END IF; END; Ferindo -- justferindo
Re: error loading data from file ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
Well that's good to know. This is a InnoDB engine db. I did try specifying the how the fields and lines in the file are terminated to the LOAD DATA INFILE program but I still get the same error message. Still have no idea what I'm doing wrong. I was getting errors where the program was complaining about various type mismatches with the data I'm loading that I went in a fixed maunually using Excel but then it stopped complaining tht I had bad data types after I made all the fixes and now it just shows this error mesage when attempting to load the data into the table: ERROR 1329 (02000): No data - zero rows fetched, selected, or processed Ferindo On 6/26/06, Dan Buettner [EMAIL PROTECTED] wrote: Ferindo - I believe there is a 64K limit on the length of a record in a MyISAM table (text and binary columns excluded), but I haven't ever seen a number of columns limit. Based on your table description below I don't think you're close to such a problem. Based on the error you receive (no data), I think it's much more likely the command you're using isn't quite right for the data source you've got. See http://dev.mysql.com/doc/refman/5.0/en/load-data.html for all the ins and outs of LOAD DATA INFILE. Off the top of my head, two things: 1 - you are using tab delimited data. By default, I believe LOAD DATA INFILE expects a comma delimited file. You should therefore specify FIELDS TERMINATED BY \t 2 - you are using Windows, which uses CRLF line endings. I believe LOAD DATA INFILE by default expects UNIX standard line endings. You should therefore specify LINES TERMINATED BY \r\n Something like this then: LOAD DATA INFILE 'C:/Program Files/Apache Software Foundation/Tomcat 5.5/ webapps/utrad/docs/rebuild_scratch_area/test.tab' INTO TABLE reggie FIELDS TERMINATED BY \t LINES TERMINATED BY \r\n; Hope this helps Dan Ferindo Middleton wrote: Does MySQL have any constraints when it comes to the number columns that it can accurately support/import in any one table/file. When I see this file that is failing to get properly read into the database, that's what comes to mind... It's failing because of some kind of constraint or threshold of the db so instead of giving a meaningful messgae, it just says: ERROR 1329 (02000): No data - zero rows fetched, selected, or processed Ferindo On 6/23/06, Ferindo Middleton [EMAIL PROTECTED] wrote: I guess my general reason for posting this was to ask: Are there any known issues with the LOAD DATA INFILE comand in MySQL? However, I stripped all the data in the file (test.tab) down to one record which still wouldn't load. Here is the command: mysql LOAD DATA INFILE 'C:/Program Files/Apache Software Foundation/Tomcat 5.5/ webapps/utrad/docs/rebuild_scratch_area/test.tab' INTO TABLE reggie; The fields inside the file are tab-delimited and look like this: 16411 5 Rupert Settles Settles Settles 1 esunindyo \N 207 \N 12 \N 2005-01-03 2005-01-07 1 1 1 0 \N Deobligation 1 2 \N fmiddleton 0 0 \N \N 11:00:00 \N \N \N 0 0 4 0 0 \N \N \N 1 The structure of the TABLE reggie is this: CREATE TABLE `reggie ` ( `id` bigint(20) unsigned NOT NULL auto_increment, `title_salutation_id` bigint(20) unsigned NOT NULL, `firstname` varchar(128) NOT NULL, `middlename` varchar(128) NOT NULL default '', `lastname` varchar(128) NOT NULL default '', `suffix` varchar(128) NOT NULL default '', `paper_received` tinyint(1) NOT NULL default '1', `addr` text, `cc_email_list` text, `fortran_id` bigint(20) default NULL, `office` text, `class_id` bigint(20) unsigned NOT NULL, `schedule_id` bigint(20) default NULL, `start_date` date NOT NULL default '-00-00', `end_date` date NOT NULL default '-00-00', `enrolled` tinyint(1) default NULL, `attended` tinyint(1) default NULL, `completed` tinyint(1) default NULL, `cancelled` tinyint(1) default '0', `cancelled_comments` text, `comments` text, `email_confirmation_sent` tinyint(1) NOT NULL, `employment_status_id` bigint(20) unsigned NOT NULL default '0', `last_updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `last_user_updated` text, `waitlisted` tinyint(1) default '0', `overflow_registrant` tinyint(1) default '0', `attach_hotel_listing_directions` tinyint(1) default NULL, `instructor_legacy` text, `time_legacy` time default NULL, `ssn_legacy` text, `position_grade_title` text, `office_phone_legacy` text, `contractor_legacy` tinyint(1) default NULL, `no_show` tinyint(1) default NULL, `funding_id` bigint(20) unsigned NOT NULL default '0', `incomplete` tinyint(1) default NULL, `prerequisites_completed` tinyint(1) default NULL, `score` smallint(5) unsigned default NULL, `per_diem_cost` decimal(10,0) default NULL, `travel_cost` decimal(10,0) default NULL, `first_migration` tinyint(1) unsigned default NULL, PRIMARY KEY (`firstname`,`middlename`,`lastname`,`suffix
Re: error loading data from file ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
Does MySQL have any constraints when it comes to the number columns that it can accurately support/import in any one table/file. When I see this file that is failing to get properly read into the database, that's what comes to mind... It's failing because of some kind of constraint or threshold of the db so instead of giving a meaningful messgae, it just says: ERROR 1329 (02000): No data - zero rows fetched, selected, or processed Ferindo On 6/23/06, Ferindo Middleton [EMAIL PROTECTED] wrote: I guess my general reason for posting this was to ask: Are there any known issues with the LOAD DATA INFILE comand in MySQL? However, I stripped all the data in the file (test.tab) down to one record which still wouldn't load. Here is the command: mysql LOAD DATA INFILE 'C:/Program Files/Apache Software Foundation/Tomcat 5.5/ webapps/utrad/docs/rebuild_scratch_area/test.tab' INTO TABLE reggie; The fields inside the file are tab-delimited and look like this: 16411 5 Rupert Settles Settles Settles 1 esunindyo \N 207 \N 12 \N 2005-01-03 2005-01-07 1 1 1 0 \N Deobligation 1 2 \N fmiddleton 0 0 \N \N 11:00:00 \N \N \N 0 0 4 0 0 \N \N \N 1 The structure of the TABLE reggie is this: CREATE TABLE `reggie ` ( `id` bigint(20) unsigned NOT NULL auto_increment, `title_salutation_id` bigint(20) unsigned NOT NULL, `firstname` varchar(128) NOT NULL, `middlename` varchar(128) NOT NULL default '', `lastname` varchar(128) NOT NULL default '', `suffix` varchar(128) NOT NULL default '', `paper_received` tinyint(1) NOT NULL default '1', `addr` text, `cc_email_list` text, `fortran_id` bigint(20) default NULL, `office` text, `class_id` bigint(20) unsigned NOT NULL, `schedule_id` bigint(20) default NULL, `start_date` date NOT NULL default '-00-00', `end_date` date NOT NULL default '-00-00', `enrolled` tinyint(1) default NULL, `attended` tinyint(1) default NULL, `completed` tinyint(1) default NULL, `cancelled` tinyint(1) default '0', `cancelled_comments` text, `comments` text, `email_confirmation_sent` tinyint(1) NOT NULL, `employment_status_id` bigint(20) unsigned NOT NULL default '0', `last_updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `last_user_updated` text, `waitlisted` tinyint(1) default '0', `overflow_registrant` tinyint(1) default '0', `attach_hotel_listing_directions` tinyint(1) default NULL, `instructor_legacy` text, `time_legacy` time default NULL, `ssn_legacy` text, `position_grade_title` text, `office_phone_legacy` text, `contractor_legacy` tinyint(1) default NULL, `no_show` tinyint(1) default NULL, `funding_id` bigint(20) unsigned NOT NULL default '0', `incomplete` tinyint(1) default NULL, `prerequisites_completed` tinyint(1) default NULL, `score` smallint(5) unsigned default NULL, `per_diem_cost` decimal(10,0) default NULL, `travel_cost` decimal(10,0) default NULL, `first_migration` tinyint(1) unsigned default NULL, PRIMARY KEY (`firstname`,`middlename`,`lastname`,`suffix`,`class_id`,`start_date`,`end_date`), UNIQUE KEY `id` (`id`), KEY `fk_registration_class_id_must_always_match_a_classes_id` (`class_id`), KEY `fk_registration_title_id_must_always_match_title_salutations_id` (`title_salutation_id`), KEY `fk_registration_bureau_id_must_always_match_a_bureaus_id` (`bureau_id`), KEY `fk_funding_id_check_for_registration_and_attendance` (`funding_id`), KEY `fk_employment_status_id_check_for_registration_and_attendance` (`employment_status_id`), CONSTRAINT `fk_employment_status_id_check_for_registration_and_attendance` FOREIGN KEY (`employment_status_id`) REFERENCES `employment_statuses` (`id`), CONSTRAINT `fk_funding_id_check_for_registration_and_attendance` FOREIGN KEY (`funding_id`) REFERENCES `funding_types` (`id`), CONSTRAINT `fk_registration_bureau_id_must_always_match_a_bureaus_id` FOREIGN KEY (`bureau_id`) REFERENCES `bureaus` (`id`), CONSTRAINT `fk_registration_class_id_must_always_match_a_classes_id` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`), CONSTRAINT `fk_registration_title_id_must_always_match_title_salutations_id` FOREIGN KEY (`title_salutation_id`) REFERENCES `title_salutations` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Do you know why this this one data record won't load?... why MySQL says: ERROR 1329 (02000): No data - zero rows fetched, selected, or processed Ferindo On 6/23/06, Gerald L. Clark [EMAIL PROTECTED] wrote: Ferindo Middleton wrote: I'm trying to load data into a table from a file but I get an error message: ERROR 1329 (02000): No data - zero rows fetched, selected, or processed This error message isn't very specific as to what is going wrong and I have no idea what it is about the data file that is wrong. Of course, I know that there is in fact data in the file and that it is proper data types matching the table structure so I don't know why this error message is occuring or what
error loading data from file ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
I'm trying to load data into a table from a file but I get an error message: ERROR 1329 (02000): No data - zero rows fetched, selected, or processed This error message isn't very specific as to what is going wrong and I have no idea what it is about the data file that is wrong. Of course, I know that there is in fact data in the file and that it is proper data types matching the table structure so I don't know why this error message is occuring or what it is about the file that's stopping it from being loaded. -- justferindo
Re: error loading data from file ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
I guess my general reason for posting this was to ask: Are there any known issues with the LOAD DATA INFILE comand in MySQL? However, I stripped all the data in the file (test.tab) down to one record which still wouldn't load. Here is the command: mysql LOAD DATA INFILE 'C:/Program Files/Apache Software Foundation/Tomcat 5.5/ webapps/utrad/docs/rebuild_scratch_area/test.tab' INTO TABLE reggie; The fields inside the file are tab-delimited and look like this: 16411 5 Rupert Settles Settles Settles 1 esunindyo \N 207 \N 12 \N 2005-01-03 2005-01-07 1 1 1 0 \N Deobligation 1 2 \N fmiddleton 0 0 \N \N 11:00:00 \N \N \N 0 0 4 0 0 \N \N \N 1 The structure of the TABLE reggie is this: CREATE TABLE `reggie ` ( `id` bigint(20) unsigned NOT NULL auto_increment, `title_salutation_id` bigint(20) unsigned NOT NULL, `firstname` varchar(128) NOT NULL, `middlename` varchar(128) NOT NULL default '', `lastname` varchar(128) NOT NULL default '', `suffix` varchar(128) NOT NULL default '', `paper_received` tinyint(1) NOT NULL default '1', `addr` text, `cc_email_list` text, `fortran_id` bigint(20) default NULL, `office` text, `class_id` bigint(20) unsigned NOT NULL, `schedule_id` bigint(20) default NULL, `start_date` date NOT NULL default '-00-00', `end_date` date NOT NULL default '-00-00', `enrolled` tinyint(1) default NULL, `attended` tinyint(1) default NULL, `completed` tinyint(1) default NULL, `cancelled` tinyint(1) default '0', `cancelled_comments` text, `comments` text, `email_confirmation_sent` tinyint(1) NOT NULL, `employment_status_id` bigint(20) unsigned NOT NULL default '0', `last_updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `last_user_updated` text, `waitlisted` tinyint(1) default '0', `overflow_registrant` tinyint(1) default '0', `attach_hotel_listing_directions` tinyint(1) default NULL, `instructor_legacy` text, `time_legacy` time default NULL, `ssn_legacy` text, `position_grade_title` text, `office_phone_legacy` text, `contractor_legacy` tinyint(1) default NULL, `no_show` tinyint(1) default NULL, `funding_id` bigint(20) unsigned NOT NULL default '0', `incomplete` tinyint(1) default NULL, `prerequisites_completed` tinyint(1) default NULL, `score` smallint(5) unsigned default NULL, `per_diem_cost` decimal(10,0) default NULL, `travel_cost` decimal(10,0) default NULL, `first_migration` tinyint(1) unsigned default NULL, PRIMARY KEY (`firstname`,`middlename`,`lastname`,`suffix`,`class_id`,`start_date`,`end_date`), UNIQUE KEY `id` (`id`), KEY `fk_registration_class_id_must_always_match_a_classes_id` (`class_id`), KEY `fk_registration_title_id_must_always_match_title_salutations_id` (`title_salutation_id`), KEY `fk_registration_bureau_id_must_always_match_a_bureaus_id` (`bureau_id`), KEY `fk_funding_id_check_for_registration_and_attendance` (`funding_id`), KEY `fk_employment_status_id_check_for_registration_and_attendance` (`employment_status_id`), CONSTRAINT `fk_employment_status_id_check_for_registration_and_attendance` FOREIGN KEY (`employment_status_id`) REFERENCES `employment_statuses` (`id`), CONSTRAINT `fk_funding_id_check_for_registration_and_attendance` FOREIGN KEY (`funding_id`) REFERENCES `funding_types` (`id`), CONSTRAINT `fk_registration_bureau_id_must_always_match_a_bureaus_id` FOREIGN KEY (`bureau_id`) REFERENCES `bureaus` (`id`), CONSTRAINT `fk_registration_class_id_must_always_match_a_classes_id` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`), CONSTRAINT `fk_registration_title_id_must_always_match_title_salutations_id` FOREIGN KEY (`title_salutation_id`) REFERENCES `title_salutations` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Do you know why this this one data record won't load?... why MySQL says: ERROR 1329 (02000): No data - zero rows fetched, selected, or processed Ferindo On 6/23/06, Gerald L. Clark [EMAIL PROTECTED] wrote: Ferindo Middleton wrote: I'm trying to load data into a table from a file but I get an error message: ERROR 1329 (02000): No data - zero rows fetched, selected, or processed This error message isn't very specific as to what is going wrong and I have no idea what it is about the data file that is wrong. Of course, I know that there is in fact data in the file and that it is proper data types matching the table structure so I don't know why this error message is occuring or what it is about the file that's stopping it from being loaded. Since you did not show us the data, nor the command you used to load the data, we can't tell you what is wrong. -- Gerald L. Clark Supplier Systems Corporation -- Ferindo Middleton Technical Lead - Research and AUI Infrastructure Development Sleekcollar Software
Re: how to default column value to lower( )
Thanks Jay. Yeah, I'll just write a trigger... It sure would be cool though if you could say something like... ADD COLUMN new_column VARCHAR(100) DEFAULT LOWER(new_column) and then be done without having to explicitly define a trigger for it. This is for a userid field. Userid values at my org must be lowercase so we can ensure that the userid values are portable and can be used exactly as caputred within the db accross all our various systems... I just didn't want developers/admins to have to worry about making/forcing this integrity checks within the application and even if there's some person that accidentally INSERTs records on the command line without following this all-lowercase requirement. Ferindo On 5/19/06, Jay Pipes [EMAIL PROTECTED] wrote: You could always use a trigger on BEFORE UPDATE/BEFORE INSERT which changes NEW.column to LOWER(column). I know it's not declarative, but it works. sheeri kritzer wrote: Not that I know of, but if you don't do binary (case-sensitive) searching then does it really matter?? You can retrieve with LOWER, or put it in your application, if your application needs to display it that way. -Sheeri On 5/18/06, Ferindo Middleton [EMAIL PROTECTED] wrote: I have column and I want to make sure the db is always making sure the value that gets input into this VARCHAR() column is always lowercase; Is there a way to set the value of a column within a table to automatically be lowercase. I know how to use the LOWER() function when performing queries but is there a way to define LOWER( ) within the definition of the table column itself without having the application specify LOWER( ) to any value passed to this column or if a record had to be manually input and the admin forgot to make sure all the characters were lowercase. Ferindo -- Jay Pipes Community Relations Manager, North America, MySQL Inc. Roaming North America, based in Columbus, Ohio email: [EMAIL PROTECTED]mob: +1 614 406 1267 Are You MySQL Certified? http://www.mysql.com/certification Got Cluster? http://www.mysql.com/cluster
Re: how to default ... DATE column to another column date field, without using a trigger?
I have a similar desire like my first question regarding a DATETIME field. I have a DATETIME field in my db that I would like to DEFAULT to the exact value of another DATETIME field in the same table... However, if I write a trigger for this and the application or the user or someone accessing the table specifies a value for the second DATETIME field then I imagine the trigger would always overwrite it well I imagine if I take advantage of the mew.field_name and old.field_name values of the db, I could find away to allow someone to specify ... But I guess again, it seems as though something like this should be able to be configured on the command line when inititally creating the talbe or the column without having to manually write a trigger for it... youshould be able to just say something like, for instance, ALTER TABLE tablename ADD COLUMN start_date DATE, cut_off_date DATE DEFAULT start_date It would be cool if you could do that. Ferindo On 5/19/06, Ferindo Middleton [EMAIL PROTECTED] wrote: Thanks Jay. Yeah, I'll just write a trigger... It sure would be cool though if you could say something like... ADD COLUMN new_column VARCHAR(100) DEFAULT LOWER(new_column) and then be done without having to explicitly define a trigger for it. This is for a userid field. Userid values at my org must be lowercase so we can ensure that the userid values are portable and can be used exactly as caputred within the db accross all our various systems... I just didn't want developers/admins to have to worry about making/forcing this integrity checks within the application and even if there's some person that accidentally INSERTs records on the command line without following this all-lowercase requirement. Ferindo On 5/19/06, Jay Pipes [EMAIL PROTECTED] wrote: You could always use a trigger on BEFORE UPDATE/BEFORE INSERT which changes NEW.column to LOWER(column). I know it's not declarative, but it works. sheeri kritzer wrote: Not that I know of, but if you don't do binary (case-sensitive) searching then does it really matter?? You can retrieve with LOWER, or put it in your application, if your application needs to display it that way. -Sheeri On 5/18/06, Ferindo Middleton [EMAIL PROTECTED] wrote: I have column and I want to make sure the db is always making sure the value that gets input into this VARCHAR() column is always lowercase; Is there a way to set the value of a column within a table to automatically be lowercase. I know how to use the LOWER() function when performing queries but is there a way to define LOWER( ) within the definition of the table column itself without having the application specify LOWER( ) to any value passed to this column or if a record had to be manually input and the admin forgot to make sure all the characters were lowercase. Ferindo -- Jay Pipes Community Relations Manager, North America, MySQL Inc. Roaming North America, based in Columbus, Ohio email: [EMAIL PROTECTED]mob: +1 614 406 1267 Are You MySQL Certified? http://www.mysql.com/certification Got Cluster? http://www.mysql.com/cluster -- Ferindo Middleton Technical Lead - Research and AUI Infrastructure Development Sleekcollar Software
how to default column value to lower( )
I have column and I want to make sure the db is always making sure the value that gets input into this VARCHAR() column is always lowercase; Is there a way to set the value of a column within a table to automatically be lowercase. I know how to use the LOWER() function when performing queries but is there a way to define LOWER( ) within the definition of the table column itself without having the application specify LOWER( ) to any value passed to this column or if a record had to be manually input and the admin forgot to make sure all the characters were lowercase. Ferindo
how to extract common text string from field?
Suppose you have a field in a db table that holds email addresses and all of the address end in domain.com. Is there a MySQL function that can be used to extract the first part of the email address, the username (the part of the email address before the 'domain.com' part of the email address). I was hoping somehow to do this within a SELECT statement so instead of seeing the email address I would see the username without the 'domain.com' part. Ferindo
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]
