Reading table information......
Hi all, Below are the two examples. In the example 2 its giving 'Reading table information.' where as in the example 1 its not giving. I am not able to find out why it is. Example 2 server is configured by me where as example 1 server is configured by some body else. Example 1: [pr...@beta praja]$ mysql -h 172.185.1.199 -u dip -D dip -pdip Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 177299 to server version: 5.0.32-Debian_7etch8-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql exit --- Example 2: pr...@de76:~$ mysql -h 192.168.1.10 -u dip -D dip -pdip Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 278 Server version: 5.0.32-Debian_7etch8-log Debian etch distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql -- Krishna Chandra Prajapati Mob: 9912924044 Email-id: prajapat...@gmail.com
Re: WL#946 and Changing time literal format
Hi! Konstantin == Konstantin Osipov kos...@sun.com writes: Konstantin * Michael Widenius mo...@mysql.com [09/01/30 14:53]: Its more important that we don't break things for current users than try to be concerned about possible wrong usage that no one seams to do or find important enough to complain about. Konstantin Monty, I disagree with this statement. Our current users use the Konstantin current versions of the server. It's a separate question of what Konstantin support we're willing to give them and for how long. Konstantin In the new versions we should hold high the expectations of new Konstantin users, and they are about standard compliance, and also about ease Konstantin of migration. Sorry, but the above is not true. We have asked user over and over again what they think about the standard and they have said it's not critical or even important to them.; What is important that we don't break their old applications! When going forward, we must prioritize old user to new ones! The old ones are our current or customers in the near future. If we make them unhappy we don't have a business anymore. The new users will mainly listen to old user if they should use MySQL or not. If we make the old ones angry, we don't get new users. Konstantin sql_modes are not a solution since they make the server code a Konstantin mess, and won't let us make everyone happy anyway. I disagree that it makes the code messy. The code depends on how you implement them. sql_modes are there to help people easier switch to a newer server and gives them time to upgrade their old applications over time. When you have an application with million of code, it will take time to find and fix all issues. Seeing able to resolve things when things are found to break by simply using a sql_mode may save the day for them. It's important that you see the usage of MySQL from theu user point of view; Saying that something is complex and we will not do it, will not satisfy a user that needs it. Konstantin MySQL server needs a vision. Sticking to expectations of existing Konstantin users is looking back into (not-so) glorious past. Our existing users is the second biggest user base for any database. We reached this level as MySQL has worked to their expectations. Trying to do things differently, like other companies have tried, will just lead to failures. Konstanting Trying to make everybody happy is infeasible. Konstantin Our only option is to move forward Konstantin to meet expectations of our modern adopters, and they are largely Konstantin more intelligent, with past database experience, so the standard Konstantin compliance is high on their list. On what do you base your observation ? It's not what our users have been telling us on MySQL conferences. People are using MySQL because it's different and can satisfy their needs. Standards are useful, but not important for our current or future users. Getting the job done and not having downtime, even when upgrading, that is important! Konstantin What's worse, is that while we're fighting internally when to make Konstantin an incompatible change and when not, our change management process Konstantin is a mess. That's another issue, but it's not any reason to abound features that some of our users may depend on. Konstantin We introduce incompatible changes in every major release, so Konstantin people are forced to migrate their applications manually again and Konstantin again. And yet we can't plan our changes in a way that a bulk Konstantin incompatible changes in a certain area are done at once, forcing Konstantin people to look into the problem once only, rather than on every Konstantin upgrade. That is a problem with our development processes, has nothing to do with sql modes. Konstantin It's a pity we can't shift our focus and mental efforts from Konstantin developing a shared understanding what incompatible changes are Konstantin right and called for, to developing the best way of making Konstantin changes. Just focusing on one area doesn't solve any problems. What is needed is to have a good understanding of all aspect of the problem. I agree that we need to change things. I disagree that doing incompatible changes without planning and carefull thinking about how this will affect our user base is the right way to go. Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
WHERE vs. ON
Somebody, I think it was somebody from MySQL, said that you should never put anything into a WHERE clause that could be put into the ON clause of a JOIN. My guess is that this helps with the optimization, but it seems counter-intuitive to me. I've never followed that advice, but I'm starting to experiment with it. My first, rather simple example didn't seem to accomplish much: giiexpress.com: mysqlexplain - SELECT prod.prod_num, prod.prod_title, prod.prod_samp_doc - FROM pub JOIN prod ON pub.pub_id = prod.pub_id - WHERE pub.pub_code = 'dc' - AND prod.prod_discont = 0 - AND prod.prod_samp_doc IS NOT NULL - ORDER BY prod.prod_num\G *** 1. row *** id: 1 select_type: SIMPLE table: pub type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 652 Extra: Using where; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: prod type: ref possible_keys: pub_id key: pub_id key_len: 46 ref: giiexpr_db.pub.pub_id rows: 112 Extra: Using where 2 rows in set (0.25 sec) giiexpress.com: mysqlexplain - SELECT prod.prod_num, prod.prod_title, prod.prod_samp_doc - FROM pub JOIN prod ON pub.pub_id = prod.pub_id - AND pub.pub_code = 'dc' - WHERE prod.prod_discont = 0 - AND prod.prod_samp_doc IS NOT NULL - ORDER BY prod.prod_num\G *** 1. row *** id: 1 select_type: SIMPLE table: pub type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 652 Extra: Using where; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: prod type: ref possible_keys: pub_id key: pub_id key_len: 46 ref: giiexpr_db.pub.pub_id rows: 112 Extra: Using where 2 rows in set (0.06 sec) Are the results the same because my query is too simple? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com http://www.giiexpress.com www.giiexpress.com www.etudes-marche.com
RE: WHERE vs. ON
ON condition uses the same columnname from both source and target tables whereas any column expressions can go in the WHERE clause... Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: jschwa...@the-infoshop.com To: mysql@lists.mysql.com Subject: WHERE vs. ON Date: Tue, 3 Feb 2009 12:24:52 -0500 Somebody, I think it was somebody from MySQL, said that you should never put anything into a WHERE clause that could be put into the ON clause of a JOIN. My guess is that this helps with the optimization, but it seems counter-intuitive to me. I've never followed that advice, but I'm starting to experiment with it. My first, rather simple example didn't seem to accomplish much: giiexpress.com: mysqlexplain - SELECT prod.prod_num, prod.prod_title, prod.prod_samp_doc - FROM pub JOIN prod ON pub.pub_id = prod.pub_id - WHERE pub.pub_code = 'dc' - AND prod.prod_discont = 0 - AND prod.prod_samp_doc IS NOT NULL - ORDER BY prod.prod_num\G *** 1. row *** id: 1 select_type: SIMPLE table: pub type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 652 Extra: Using where; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: prod type: ref possible_keys: pub_id key: pub_id key_len: 46 ref: giiexpr_db.pub.pub_id rows: 112 Extra: Using where 2 rows in set (0.25 sec) giiexpress.com: mysqlexplain - SELECT prod.prod_num, prod.prod_title, prod.prod_samp_doc - FROM pub JOIN prod ON pub.pub_id = prod.pub_id - AND pub.pub_code = 'dc' - WHERE prod.prod_discont = 0 - AND prod.prod_samp_doc IS NOT NULL - ORDER BY prod.prod_num\G *** 1. row *** id: 1 select_type: SIMPLE table: pub type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 652 Extra: Using where; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: prod type: ref possible_keys: pub_id key: pub_id key_len: 46 ref: giiexpr_db.pub.pub_id rows: 112 Extra: Using where 2 rows in set (0.06 sec) Are the results the same because my query is too simple? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com http://www.giiexpress.com www.giiexpress.com www.etudes-marche.com _ Windows Live™ Hotmail®…more than just e-mail. http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t2_hm_justgotbetter_howitworks_012009
Re: WHERE vs. ON
On Tue, Feb 3, 2009 at 12:24 PM, Jerry Schwartz jschwa...@the-infoshop.com wrote: Somebody, I think it was somebody from MySQL, said that you should never put anything into a WHERE clause that could be put into the ON clause of a JOIN. My guess is that this helps with the optimization, but it seems counter-intuitive to me. It seems like it shouldn't make any difference if the optimizer is smart enough, but in my experience it sometimes does. Because of that, I always try to put conditions in the ON clauses when I can, even if they are not about joining the tables. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WHERE vs. ON
On Tue, Feb 3, 2009 at 1:54 PM, Jerry Schwartz jschwa...@the-infoshop.com wrote: From: Martin Gainty [mailto:mgai...@hotmail.com] Sent: Tuesday, February 03, 2009 1:03 PM To: Jerry Schwartz; mysql@lists.mysql.com Subject: RE: WHERE vs. ON ON condition uses the same columnname from both source and target tables whereas any column expressions can go in the WHERE clause... [JS] That isn't necessarily true. ON a.x = b.y Is valid. You don't even need to reference either table in the join. ON ROUND( RAND() ) (yes, I have found the need to use that) Join by rand! The on clause is just something evaluated for each row that if it returns an expression that evaluates to true will allow the row to be joined. -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Algorithm for resolving foreign key dependencies?
Hi, First of all, I apologise in advance for any mind-altering, or headache-inducing effects this question may have. I've spent the past two days trying to figure it out, and all I've got to show for it is a mostly-working recursive depth-first-search routine and an empty packet of painkillers. MySQL version: 5.0.67-0ubuntu6 I'm trying to write a code generator (in Python) that reads in a MySQL database, enumerates all the tables, then produces INSERT, DELETE and UPDATE code in PHP. The INSERT and UPDATE code generation was fairly easy, and works quite well. What I'm having trouble with is the DELETE code generator -- more specifically, resolving foreign key references. Basically, what I have is a tree built in memory, so I can go: tableinfo['thetable']['fieldname']['refs'] And get a complete list of all the tables (and the fields within that table) that reference 'fieldname' in 'thetable'. What I want is an answer to the question: If all my foreign keys were set to 'ON DELETE CASCADE', what would I need to do to delete row 'X' in table 'Y' without violating any foreign key constraints? Here's an example. Let's say I've got these tables: CREATE TABLE `Manufacturers` ( `idManufacturer` int(11) NOT NULL auto_increment, `name` varchar(255) NOT NULL, PRIMARY KEY (`idManufacturer`) ) ENGINE=InnoDB CREATE TABLE `Parts` ( `idPart` int(11) NOT NULL auto_increment, `idManufacturer` int(11) NOT NULL, `partnumber` int(11) NOT NULL, PRIMARY KEY (`idPart`), KEY `Parts_idManufacturer_FKIndex` (`idManufacturer`), CONSTRAINT `Parts_ibfk_1` FOREIGN KEY (`idManufacturer`) REFERENCES `Manufacturers` (`idManufacturer`) ) ENGINE=InnoDB And my database contains: Manufacturers: idManufacturername 123 Any Company Inc. Parts: idPart idManufacturer partnumber 1 123 12345 Now, let's say I want to do this: DELETE FROM Manufacturers WHERE idManufacturer=123 Because I have a part that references Manufacturer #123, I have to do this instead: DELETE FROM Parts WHERE idManufacturer=123 DELETE FROM Manufacturer WHERE idManufacturer=123 What I want is something I can feed the table definitions to, and the name of the table I want to delete a row from (in this case 'Manufacturers'), and generate a list of the DELETE commands that would allow me to delete that row while enforcing FK dependencies. I figure this is going to have to work something like mathematical expression evaluation -- build up a list of dependencies, then deal with the deepest dependency first. Catch being I can't see an obvious way to deal with generating the necessary DELETE commands without having to write a massive if recursion_level = 0 then generate_a_straight_delete else if recursion_level = 1 then... statement... Thanks, -- Phil. usene...@philpem.me.uk http://www.philpem.me.uk/ If mail bounces, replace 08 with the last two digits of the current year. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Algorithm for resolving foreign key dependencies?
Try looking at the information_schema.KEY_COLUMN_USAGE table (where referenced_table_schema is not null). It will show you the FK relationships. You could then create a tree that you could use to find the hierarchy. For that, I suggest looking at http://www.artfulsoftware.com/infotree/mysqlquerytree.php. The information_schema table is already sort of an edge-list, although each node is made up of the tuple (table_schema, table_name, column_name) or (referenced_table_schema, referenced_table_name, referenced_column_name). Donna news n...@ger.gmane.org wrote on 02/03/2009 05:38:34 PM: Andy Shellam wrote: Am I missing something here? (It is late after a long day, I admit!) Only something I forgot to mention. All the foreign keys are set up as ON DELETE RESTRICT, meaning MySQL's response to a foreign key violation is to spit out an error message to the effect of I'm sorry, Dave, I can't let you do that. The problem is, the target platform doesn't use foreign keys for performance reasons. I want to use foreign keys in development as a bug-trappingmethod -- I'd rather see an FK violation error in development than get an angry email from a customer asking why there's a part listed that doesn't seem to have a manufacturer. The plan was to write a code-generator that would generate all the database code for me, then I could deal with the page templates and display logic myself (thus eliminating ~80% of the boring, repetitive work). I want the generated code to handle foreign keys itself, rather than relying on the database. As I said above, if foreign key constraints didn't slow things down markedly, I'd use them in production. Based on the (admittedly limited) testing I've done, application-side FK enforcement is considerably faster than using ON DELETE CASCADE and letting MySQL deal with the foreign keys. I don't like writing database code by hand (it all follows a standard template), so I figured I'd write a program to do it for me. Work smarter not harder and all that :) Thanks, -- Phil. usene...@philpem.me.uk http://www.philpem.me.uk/ If mail bounces, replace 08 with the last two digits of the current year. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? unsub=ddevaudre...@intellicare.com -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Algorithm for resolving foreign key dependencies?
Sounds like you want to walk tables in order of their fk dependencies - a topological ordering. You might want to take a look at SQLAlchemy which has some methods to do just this in sqlalchemy.sql.util: def sort_tables(tables, reverse=False): sort a collection of Table objects in order of their foreign-key dependency. ~Andrew On Tue, Feb 3, 2009 at 3:40 PM, Philip Pemberton usene...@philpem.me.uk wrote: Hi, First of all, I apologise in advance for any mind-altering, or headache-inducing effects this question may have. I've spent the past two days trying to figure it out, and all I've got to show for it is a mostly-working recursive depth-first-search routine and an empty packet of painkillers. MySQL version: 5.0.67-0ubuntu6 I'm trying to write a code generator (in Python) that reads in a MySQL database, enumerates all the tables, then produces INSERT, DELETE and UPDATE code in PHP. The INSERT and UPDATE code generation was fairly easy, and works quite well. What I'm having trouble with is the DELETE code generator -- more specifically, resolving foreign key references. Basically, what I have is a tree built in memory, so I can go: tableinfo['thetable']['fieldname']['refs'] And get a complete list of all the tables (and the fields within that table) that reference 'fieldname' in 'thetable'. What I want is an answer to the question: If all my foreign keys were set to 'ON DELETE CASCADE', what would I need to do to delete row 'X' in table 'Y' without violating any foreign key constraints? Here's an example. Let's say I've got these tables: CREATE TABLE `Manufacturers` ( `idManufacturer` int(11) NOT NULL auto_increment, `name` varchar(255) NOT NULL, PRIMARY KEY (`idManufacturer`) ) ENGINE=InnoDB CREATE TABLE `Parts` ( `idPart` int(11) NOT NULL auto_increment, `idManufacturer` int(11) NOT NULL, `partnumber` int(11) NOT NULL, PRIMARY KEY (`idPart`), KEY `Parts_idManufacturer_FKIndex` (`idManufacturer`), CONSTRAINT `Parts_ibfk_1` FOREIGN KEY (`idManufacturer`) REFERENCES `Manufacturers` (`idManufacturer`) ) ENGINE=InnoDB And my database contains: Manufacturers: idManufacturername 123 Any Company Inc. Parts: idPart idManufacturer partnumber 1 123 12345 Now, let's say I want to do this: DELETE FROM Manufacturers WHERE idManufacturer=123 Because I have a part that references Manufacturer #123, I have to do this instead: DELETE FROM Parts WHERE idManufacturer=123 DELETE FROM Manufacturer WHERE idManufacturer=123 What I want is something I can feed the table definitions to, and the name of the table I want to delete a row from (in this case 'Manufacturers'), and generate a list of the DELETE commands that would allow me to delete that row while enforcing FK dependencies. I figure this is going to have to work something like mathematical expression evaluation -- build up a list of dependencies, then deal with the deepest dependency first. Catch being I can't see an obvious way to deal with generating the necessary DELETE commands without having to write a massive if recursion_level = 0 then generate_a_straight_delete else if recursion_level = 1 then... statement... Thanks, -- Phil. usene...@philpem.me.uk http://www.philpem.me.uk/ If mail bounces, replace 08 with the last two digits of the current year. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.b.gar...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: WHERE vs. ON
From: Martin Gainty [mailto:mgai...@hotmail.com] Sent: Tuesday, February 03, 2009 1:03 PM To: Jerry Schwartz; mysql@lists.mysql.com Subject: RE: WHERE vs. ON ON condition uses the same columnname from both source and target tables whereas any column expressions can go in the WHERE clause... [JS] That isn't necessarily true. ON a.x = b.y Is valid.
Re: Algorithm for resolving foreign key dependencies?
Andy Shellam wrote: Am I missing something here? (It is late after a long day, I admit!) Only something I forgot to mention. All the foreign keys are set up as ON DELETE RESTRICT, meaning MySQL's response to a foreign key violation is to spit out an error message to the effect of I'm sorry, Dave, I can't let you do that. The problem is, the target platform doesn't use foreign keys for performance reasons. I want to use foreign keys in development as a bug-trapping method -- I'd rather see an FK violation error in development than get an angry email from a customer asking why there's a part listed that doesn't seem to have a manufacturer. The plan was to write a code-generator that would generate all the database code for me, then I could deal with the page templates and display logic myself (thus eliminating ~80% of the boring, repetitive work). I want the generated code to handle foreign keys itself, rather than relying on the database. As I said above, if foreign key constraints didn't slow things down markedly, I'd use them in production. Based on the (admittedly limited) testing I've done, application-side FK enforcement is considerably faster than using ON DELETE CASCADE and letting MySQL deal with the foreign keys. I don't like writing database code by hand (it all follows a standard template), so I figured I'd write a program to do it for me. Work smarter not harder and all that :) Thanks, -- Phil. usene...@philpem.me.uk http://www.philpem.me.uk/ If mail bounces, replace 08 with the last two digits of the current year. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Algorithm for resolving foreign key dependencies?
Hi Philip, Am I missing something here? (It is late after a long day, I admit!) In the example case you've given, if the foreign key in Parts is set to ON DELETE CASCADE, and you delete a row from Manufacturer, MySQL will first delete the associated records in Parts before deleting the row from Manufacturer - all you have to do is issue the DELETE FROM Manufacturer WHERE query - MySQL will handle the rest. If Parts was also the primary key table for another table (e.g. PartNumbers) and that relationship was set to ON DELETE CASCADE, and you issued the DELETE FROM Manufacturer... query, MySQL would delete the associated records from all 3 tables automatically. The only time you'd have to manually issue all 3 DELETE statements were if your foreign keys didn't have the ON DELETE CASCADE option set. Andy Philip Pemberton wrote: Hi, First of all, I apologise in advance for any mind-altering, or headache-inducing effects this question may have. I've spent the past two days trying to figure it out, and all I've got to show for it is a mostly-working recursive depth-first-search routine and an empty packet of painkillers. MySQL version: 5.0.67-0ubuntu6 I'm trying to write a code generator (in Python) that reads in a MySQL database, enumerates all the tables, then produces INSERT, DELETE and UPDATE code in PHP. The INSERT and UPDATE code generation was fairly easy, and works quite well. What I'm having trouble with is the DELETE code generator -- more specifically, resolving foreign key references. Basically, what I have is a tree built in memory, so I can go: tableinfo['thetable']['fieldname']['refs'] And get a complete list of all the tables (and the fields within that table) that reference 'fieldname' in 'thetable'. What I want is an answer to the question: If all my foreign keys were set to 'ON DELETE CASCADE', what would I need to do to delete row 'X' in table 'Y' without violating any foreign key constraints? Here's an example. Let's say I've got these tables: CREATE TABLE `Manufacturers` ( `idManufacturer` int(11) NOT NULL auto_increment, `name` varchar(255) NOT NULL, PRIMARY KEY (`idManufacturer`) ) ENGINE=InnoDB CREATE TABLE `Parts` ( `idPart` int(11) NOT NULL auto_increment, `idManufacturer` int(11) NOT NULL, `partnumber` int(11) NOT NULL, PRIMARY KEY (`idPart`), KEY `Parts_idManufacturer_FKIndex` (`idManufacturer`), CONSTRAINT `Parts_ibfk_1` FOREIGN KEY (`idManufacturer`) REFERENCES `Manufacturers` (`idManufacturer`) ) ENGINE=InnoDB And my database contains: Manufacturers: idManufacturername 123 Any Company Inc. Parts: idPart idManufacturer partnumber 1 123 12345 Now, let's say I want to do this: DELETE FROM Manufacturers WHERE idManufacturer=123 Because I have a part that references Manufacturer #123, I have to do this instead: DELETE FROM Parts WHERE idManufacturer=123 DELETE FROM Manufacturer WHERE idManufacturer=123 What I want is something I can feed the table definitions to, and the name of the table I want to delete a row from (in this case 'Manufacturers'), and generate a list of the DELETE commands that would allow me to delete that row while enforcing FK dependencies. I figure this is going to have to work something like mathematical expression evaluation -- build up a list of dependencies, then deal with the deepest dependency first. Catch being I can't see an obvious way to deal with generating the necessary DELETE commands without having to write a massive if recursion_level = 0 then generate_a_straight_delete else if recursion_level = 1 then... statement... Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Algorithm for resolving foreign key dependencies?
Donna, Try looking at the information_schema.KEY_COLUMN_USAGE table (where referenced_table_schema is not null). It will show you the FK relationships. You could then create a tree that you could use to find the hierarchy. For that, I suggest looking at http://www.artfulsoftware.com/infotree/mysqlquerytree.php. The information_schema table is already sort of an edge-list, although each node is made up of the tuple (table_schema, table_name, column_name) or (referenced_table_schema, referenced_table_name, referenced_column_name). Yep that's a tree but it ain't the one he needs. He can build a tree of the FK children of db.tbl by modifying Listing 7 in http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html#edge_list to use a version of the followings query as the algorithm walks down the tree: |SELECT c.table_schema,u.table_name,u.column_name,u.referenced_column_name FROM information_schema.table_constraints AS c INNER JOIN information_schema.key_column_usage AS u USING( constraint_schema, constraint_name ) WHERE c.constraint_type = 'FOREIGN KEY' AND u.referenced_table_schema='db' AND u.referenced_table_name = 'tbl' ORDER BY c.table_schema,u.table_name; | PB - ddevaudre...@intellicare.com wrote: Try looking at the information_schema.KEY_COLUMN_USAGE table (where referenced_table_schema is not null). It will show you the FK relationships. You could then create a tree that you could use to find the hierarchy. For that, I suggest looking at http://www.artfulsoftware.com/infotree/mysqlquerytree.php. The information_schema table is already sort of an edge-list, although each node is made up of the tuple (table_schema, table_name, column_name) or (referenced_table_schema, referenced_table_name, referenced_column_name). Donna news n...@ger.gmane.org wrote on 02/03/2009 05:38:34 PM: Andy Shellam wrote: Am I missing something here? (It is late after a long day, I admit!) Only something I forgot to mention. All the foreign keys are set up as ON DELETE RESTRICT, meaning MySQL's response to a foreign key violation is to spit out an error message to the effect of I'm sorry, Dave, I can't let you do that. The problem is, the target platform doesn't use foreign keys for performance reasons. I want to use foreign keys in development as a bug-trappingmethod -- I'd rather see an FK violation error in development than get an angry email from a customer asking why there's a part listed that doesn't seem to have a manufacturer. The plan was to write a code-generator that would generate all the database code for me, then I could deal with the page templates and display logic myself (thus eliminating ~80% of the boring, repetitive work). I want the generated code to handle foreign keys itself, rather than relying on the database. As I said above, if foreign key constraints didn't slow things down markedly, I'd use them in production. Based on the (admittedly limited) testing I've done, application-side FK enforcement is considerably faster than using ON DELETE CASCADE and letting MySQL deal with the foreign keys. I don't like writing database code by hand (it all follows a standard template), so I figured I'd write a program to do it for me. Work smarter not harder and all that :) Thanks, -- Phil. usene...@philpem.me.uk http://www.philpem.me.uk/ If mail bounces, replace 08 with the last two digits of the current year. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? unsub=ddevaudre...@intellicare.com -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare. Internal Virus Database is out of date. Checked by AVG - http://www.avg.com Version: 8.0.176 / Virus Database: 270.10.12/1909 - Release Date: 1/22/2009 7:08 AM
