Thread: [SQL-CVS] [ sqlobject-Bugs-1663785 ] ADD CONSTRAINT fails
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: SourceForge.net <no...@so...> - 2007-02-19 19:49:04
|
Bugs item #1663785, was opened at 2007-02-19 21:49 Message generated for change (Tracker Item Submitted) made by Item Submitter You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1663785&group_id=74338 Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: MySQL Group: SQLObject from repository Status: Open Resolution: None Priority: 5 Private: No Submitted By: dado1945 (dado1945) Assigned to: Nobody/Anonymous (nobody) Summary: ADD CONSTRAINT fails Initial Comment: Environment: Windows, MySql 5.0, SQLObject from svn Description: (1) It seems SQLObject creates tables alphabetically and adds constraints after creation of each table. It might happen that required table is not created yet for constraint. It is interesting that "sql sql" shows that constraints will be added after all tables are created. (2) Another problem is that foreign key and id has different types (INT and INT UNSIGNED). Because of those two problems constrain creation fails. E.g. this query: ALTER TABLE blog ADD CONSTRAINT blog_language_id_exists FOREIGN KEY (language_id) REFERENCES language (id) gives following error: sqlobject.dberrors.OperationalError: Can't create table '.\tp\#sql-1b8_35.frm' (errno: 150) (1) This problem might be solved by changing line 590 in file sqlobject\manager\command.py: tableConstraints = soClass.createTable() to: tableConstraints = soClass.createTable(applyConstraints=False) I guess the same fix is required by line 583. (2) I think foreign key type must be changed to UNSIGNED INT but I have not found part where to do that. Because of that I just removed UNSIGNED from sqlobject\mysql\mysqlconnection.py line 172. That's not the best fix but the fastest one I have found. Note: I guess this problem might exist for some other databases as well. ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1663785&group_id=74338 |
From: SourceForge.net <no...@so...> - 2007-02-20 15:58:51
|
Bugs item #1663785, was opened at 2007-02-19 22:49 Message generated for change (Comment added) made by phd You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1663785&group_id=74338 Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: MySQL Group: SQLObject from repository Status: Open >Resolution: Accepted Priority: 5 Private: No Submitted By: dado1945 (dado1945) >Assigned to: Oleg Broytmann (phd) Summary: ADD CONSTRAINT fails Initial Comment: Environment: Windows, MySql 5.0, SQLObject from svn Description: (1) It seems SQLObject creates tables alphabetically and adds constraints after creation of each table. It might happen that required table is not created yet for constraint. It is interesting that "sql sql" shows that constraints will be added after all tables are created. (2) Another problem is that foreign key and id has different types (INT and INT UNSIGNED). Because of those two problems constrain creation fails. E.g. this query: ALTER TABLE blog ADD CONSTRAINT blog_language_id_exists FOREIGN KEY (language_id) REFERENCES language (id) gives following error: sqlobject.dberrors.OperationalError: Can't create table '.\tp\#sql-1b8_35.frm' (errno: 150) (1) This problem might be solved by changing line 590 in file sqlobject\manager\command.py: tableConstraints = soClass.createTable() to: tableConstraints = soClass.createTable(applyConstraints=False) I guess the same fix is required by line 583. (2) I think foreign key type must be changed to UNSIGNED INT but I have not found part where to do that. Because of that I just removed UNSIGNED from sqlobject\mysql\mysqlconnection.py line 172. That's not the best fix but the fastest one I have found. Note: I guess this problem might exist for some other databases as well. ---------------------------------------------------------------------- >Comment By: Oleg Broytmann (phd) Date: 2007-02-20 18:58 Message: Logged In: YES user_id=4799 Originator: NO 1. Fixed in the SVN, revision 2329. 2. In the MySQL Reference Manual there is an example: CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id) ) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) TYPE=INNODB; So it seems a foreign keys have no obligation to be UNSIGNED. Why do you think they *must* be UNSIGNED? ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1663785&group_id=74338 |
From: SourceForge.net <no...@so...> - 2007-02-20 17:01:27
|
Bugs item #1663785, was opened at 2007-02-19 21:49 Message generated for change (Comment added) made by dado1945 You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1663785&group_id=74338 Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: MySQL Group: SQLObject from repository Status: Open Resolution: Accepted Priority: 5 Private: No Submitted By: dado1945 (dado1945) Assigned to: Oleg Broytmann (phd) Summary: ADD CONSTRAINT fails Initial Comment: Environment: Windows, MySql 5.0, SQLObject from svn Description: (1) It seems SQLObject creates tables alphabetically and adds constraints after creation of each table. It might happen that required table is not created yet for constraint. It is interesting that "sql sql" shows that constraints will be added after all tables are created. (2) Another problem is that foreign key and id has different types (INT and INT UNSIGNED). Because of those two problems constrain creation fails. E.g. this query: ALTER TABLE blog ADD CONSTRAINT blog_language_id_exists FOREIGN KEY (language_id) REFERENCES language (id) gives following error: sqlobject.dberrors.OperationalError: Can't create table '.\tp\#sql-1b8_35.frm' (errno: 150) (1) This problem might be solved by changing line 590 in file sqlobject\manager\command.py: tableConstraints = soClass.createTable() to: tableConstraints = soClass.createTable(applyConstraints=False) I guess the same fix is required by line 583. (2) I think foreign key type must be changed to UNSIGNED INT but I have not found part where to do that. Because of that I just removed UNSIGNED from sqlobject\mysql\mysqlconnection.py line 172. That's not the best fix but the fastest one I have found. Note: I guess this problem might exist for some other databases as well. ---------------------------------------------------------------------- >Comment By: dado1945 (dado1945) Date: 2007-02-20 19:01 Message: Logged In: YES user_id=1533929 Originator: YES 1. Thanks :) 2. I don't think it must be UNSIGNED. I think foreign key must have the same type as id. Let's take your example and change a little bit. I have added UNSIGNED to parent id: CREATE TABLE parent(id INT UNSIGNED NOT NULL, PRIMARY KEY (id) ) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) TYPE=INNODB; Now child.parent_id and parent.id has different types (even if both are 32 bits). That's not good behavior and that's happens in SQLObject's MySQL case. parent.id will always be UNSIGNED INT (sqlobject\mysql\mysqlconnection.py line 172) and foreign key will be INT (I don't know where it comes from). ADD CONSTRAIN seems to check if types are equal and fails. ---------------------------------------------------------------------- Comment By: Oleg Broytmann (phd) Date: 2007-02-20 17:58 Message: Logged In: YES user_id=4799 Originator: NO 1. Fixed in the SVN, revision 2329. 2. In the MySQL Reference Manual there is an example: CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id) ) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) TYPE=INNODB; So it seems a foreign keys have no obligation to be UNSIGNED. Why do you think they *must* be UNSIGNED? ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1663785&group_id=74338 |
From: SourceForge.net <no...@so...> - 2007-03-19 15:49:25
|
Bugs item #1663785, was opened at 2007-02-19 22:49 Message generated for change (Comment added) made by phd You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1663785&group_id=74338 Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: MySQL Group: SQLObject from repository >Status: Closed >Resolution: Fixed Priority: 5 Private: No Submitted By: dado1945 (dado1945) Assigned to: Oleg Broytmann (phd) Summary: ADD CONSTRAINT fails Initial Comment: Environment: Windows, MySql 5.0, SQLObject from svn Description: (1) It seems SQLObject creates tables alphabetically and adds constraints after creation of each table. It might happen that required table is not created yet for constraint. It is interesting that "sql sql" shows that constraints will be added after all tables are created. (2) Another problem is that foreign key and id has different types (INT and INT UNSIGNED). Because of those two problems constrain creation fails. E.g. this query: ALTER TABLE blog ADD CONSTRAINT blog_language_id_exists FOREIGN KEY (language_id) REFERENCES language (id) gives following error: sqlobject.dberrors.OperationalError: Can't create table '.\tp\#sql-1b8_35.frm' (errno: 150) (1) This problem might be solved by changing line 590 in file sqlobject\manager\command.py: tableConstraints = soClass.createTable() to: tableConstraints = soClass.createTable(applyConstraints=False) I guess the same fix is required by line 583. (2) I think foreign key type must be changed to UNSIGNED INT but I have not found part where to do that. Because of that I just removed UNSIGNED from sqlobject\mysql\mysqlconnection.py line 172. That's not the best fix but the fastest one I have found. Note: I guess this problem might exist for some other databases as well. ---------------------------------------------------------------------- >Comment By: Oleg Broytmann (phd) Date: 2007-03-19 18:49 Message: Logged In: YES user_id=4799 Originator: NO It's been fixed in r2381: http://sourceforge.net/mailarchive/forum.php?thread_id=31822124&forum_id=32002. All keys are now signed. ---------------------------------------------------------------------- Comment By: dado1945 (dado1945) Date: 2007-02-20 20:01 Message: Logged In: YES user_id=1533929 Originator: YES 1. Thanks :) 2. I don't think it must be UNSIGNED. I think foreign key must have the same type as id. Let's take your example and change a little bit. I have added UNSIGNED to parent id: CREATE TABLE parent(id INT UNSIGNED NOT NULL, PRIMARY KEY (id) ) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) TYPE=INNODB; Now child.parent_id and parent.id has different types (even if both are 32 bits). That's not good behavior and that's happens in SQLObject's MySQL case. parent.id will always be UNSIGNED INT (sqlobject\mysql\mysqlconnection.py line 172) and foreign key will be INT (I don't know where it comes from). ADD CONSTRAIN seems to check if types are equal and fails. ---------------------------------------------------------------------- Comment By: Oleg Broytmann (phd) Date: 2007-02-20 18:58 Message: Logged In: YES user_id=4799 Originator: NO 1. Fixed in the SVN, revision 2329. 2. In the MySQL Reference Manual there is an example: CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id) ) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) TYPE=INNODB; So it seems a foreign keys have no obligation to be UNSIGNED. Why do you think they *must* be UNSIGNED? ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1663785&group_id=74338 |