auto_increment with FOREIGN KEY UPDATE CASCADE courses Lost connection to MySQL server
| softwareVers | installedDate | softwareSource | softwareNote | localMods | ++--+--+---+-+--+---+ | vi-3 | vim | 1.1 | 2003-10-28| www.test.com.au | NULL | NULL | ++--+--+---+-+--+---+ 1 row in set (0.00 sec) mysql select * from software_machineOSs; ERROR 2013: Lost connection to MySQL server during query mysql select * from software_machineOSs; ERROR 2006: MySQL server has gone away No connection. Trying to reconnect... Connection id:1 Current database: dept_db ERROR 2013: Lost connection to MySQL server during query * Only different is auto_increment id, The reason for to have key for the id is otherwise it failes. mysql create table test ( - name CHAR(20) NOT NULL, - id INT(2) unsigned zerofill NOT NULL auto_increment, - PRIMARY KEY(name, id) - ) TYPE=INNODB; ERROR 1075: Incorrect table definition; There can only be one auto column and it must be defined as a key mysql mysql create table test ( - name CHAR(20) NOT NULL, - id INT(2) unsigned zerofill NOT NULL auto_increment, - KEY(id), - PRIMARY KEY(name, id) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ON DELETE CASCADE ON UPDATE CASCADE
) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY (softwareID, osName) ) TYPE=INNODB; osRevision CHAR(20), - INDEX (softwareID), - FOREIGN KEY (softwareID) REFERENCES software (softwareID) ON DELETE CASCADE ON UPDATE CASCADE, - PRIMARY KEY (softwareID, osName) - ) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql mysql insert into software (softwareID,softwareName,softwareVers) values (test-1.1, test, 1.1); Query OK, 1 row affected (0.00 sec) mysql insert into software_machineOSs (softwareID,osName,osRevision) values (test-1.1, win, 2002); Query OK, 1 row affected (0.00 sec) mysql select * from software; ++--+--+---+ | softwareID | softwareName | softwareVers | installedDate | ++--+--+---+ | test-1.1 | test | 1.1 | NULL | ++--+--+---+ 1 row in set (0.01 sec) mysql select * from software_machineOSs; ++++ | softwareID | osName | osRevision | ++++ | test-1.1 | win| 2002 | ++++ 1 row in set (0.00 sec) mysql update software set softwareID=test-1.6 where softwareID=test-1.1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from software; ++--+--+---+ | softwareID | softwareName | softwareVers | installedDate | ++--+--+---+ | test-1.6 | test | 1.1 | NULL | ++--+--+---+ 1 row in set (0.00 sec) mysql select * from software_machineOSs; ++++ | softwareID | osName | osRevision | ++++ | test-1.6 | win| 2002 | ++++ 1 row in set (0.00 sec) -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: Foreign key ... ON UPDATE CASEADE]
Original Message Subject: Foreign key ... ON UPDATE CASEADE Date: Thu, 27 Mar 2003 12:08:34 +1100 From: vinita Vigine Murugiah [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Hello I have been use mysql ver 3.23.53, also I'm using on DELETE CASECADE. It seems working fine. I believe UPDATE CASECADE is working from version 4.0.8. - I have following problem (purchaseOrder_items::orderNum reference purchaseOrder_items; ::orderNum) select * from purchaseOrder; +--+-++---++ | orderNum | orderSupplierID | orderDate | orderNote | orderTotal | +--+-++---++ | 123 | k1 | 2003-03-26 | testing |250 | +--+-++---++ 1 row in set (0.00 sec) mysql select * from purchaseOrder_items; +--++---+--+---+ | orderNum | itemID | itemList | itemCost | itemDelivDate | +--++---+--+---+ | 123 | book1 | equipment | 100 | 2003-03-26| | 123 | book2 | equp | 150 | 2003-03-26| +--++---+--+---+ 2 rows in set (0.00 sec) mysql update purchaseOrder_items set orderNum=567 where orderNum=123; ERROR 1216: Cannot add a child row: a foreign key constraint fails mysql mysql mysql update purchaseOrder set orderNum=567 where orderNum=123; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails -- I can NOT edit the orderNum. It seems the only way to edit the orderNum is, delete the children (purchaseOrder_items) or move to a tem table, then edit the parent orderNum and add back the children with the new orderNum, This is to many transactions. Is there any better way to edit foreign key values Of course I can switch to the a upper mysql version(4.0.8), if that is the only solution, which one is recommendable?? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign key ... ON UPDATE CASEADE
Hello I have been use mysql ver 3.23.53, also I'm using on DELETE CASECADE. It seems working fine. I believe UPDATE CASECADE is working from version 4.0.8. - I have following problem (purchaseOrder_items::orderNum reference purchaseOrder_items; ::orderNum) select * from purchaseOrder; +--+-++---++ | orderNum | orderSupplierID | orderDate | orderNote | orderTotal | +--+-++---++ | 123 | k1 | 2003-03-26 | testing |250 | +--+-++---++ 1 row in set (0.00 sec) mysql select * from purchaseOrder_items; +--++---+--+---+ | orderNum | itemID | itemList | itemCost | itemDelivDate | +--++---+--+---+ | 123 | book1 | equipment | 100 | 2003-03-26| | 123 | book2 | equp | 150 | 2003-03-26| +--++---+--+---+ 2 rows in set (0.00 sec) mysql update purchaseOrder_items set orderNum=567 where orderNum=123; ERROR 1216: Cannot add a child row: a foreign key constraint fails mysql mysql mysql update purchaseOrder set orderNum=567 where orderNum=123; ERROR 1217: Cannot delete a parent row: a foreign key constraint fails -- I can NOT edit the orderNum. It seems the only way to edit the orderNum is, delete the children (purchaseOrder_items) or move to a tem table, then edit the parent orderNum and add back the children with the new orderNum, This is to many transactions. Is there any better way to edit foreign key values Of course I can switch to the a upper mysql version(4.0.8), if that is the only solution, which one is recommendable?? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
exist
HI Does mysql support function EXIST I got the following error mysql select * from arcdata d - WHERE year='2003' AND semester='1' - AND EXIST (SELECT * FROM arcdata_unitCodes u - WHERE u.idnum = d.idnum AND u.semester=d.semester AND u.year=d.year - AND unitcode LIKE '433%') - limit 1 - ; ERROR 1064: You have an error in your SQL syntax near '(SELECT * FROM arcdata_unitCodes u WHERE u.idnum = d.idnum A' at line 3 Try to select the student who are atleast doing one computer sciencs subject (433123, 433252...) Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Foreign key in INNODB
vinita Vigine Murugiah wrote: Egor Egorov wrote: On Thursday 16 January 2003 00:12, vinita Vigine Murugiah wrote: Rafal Jank wrote: On Wed, 15 Jan 2003 11:02:44 +1100 vinita Vigine Murugiah [EMAIL PROTECTED] wrote: HI I'm not sure what is wrong with my CREATE TABLE syntax, Please advice There is no index on ifname in table equipment_interfaces. Well.. Then how come Foreign key equipID in table equipment_interfaces worked?? There is no INDEX on equipID in table equipment?? equipID is a PRIMARY KEY ;) Well!!! ifname also a Primary key!! ysql CREATE TABLE equipment_interfaces ( - equipID CHAR(20) NOT NULL, - ifName CHAR(20) NOT NULL, - ifType ENUM(10Mbs,100Mbs,1Gb,CDDI,FDDI), - ifNet CHAR(50), - ifIPv4addr CHAR(20), - INDEX (equipID), - FOREIGN KEY (equipID) REFERENCES equipment (equipID) ON DELETE CASCADE, - PRIMARY KEY (equipID, ifName) - ) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql CREATE TABLE equipment ( - equipID CHAR(20) NOT NULL, - assetNumber CHAR(20), - serialNumber CHAR(50), - description BLOB, - barcode CHAR(20), - room CHAR(20), - area CHAR(20), - owner CHAR(100), - createDate DATE, - PRIMARY KEY (equipID) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) [skip] -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Foreign key in INNODB
Rafal Jank wrote: On Wed, 15 Jan 2003 11:02:44 +1100 vinita Vigine Murugiah [EMAIL PROTECTED] wrote: HI I'm not sure what is wrong with my CREATE TABLE syntax, Please advice There is no index on ifname in table equipment_interfaces. Well.. Then how come Foreign key equipID in table equipment_interfaces worked?? There is no INDEX on equipID in table equipment?? mysql CREATE TABLE equipment ( - equipID CHAR(20) NOT NULL, - assetNumber CHAR(20), - serialNumber CHAR(50), - description BLOB, - barcode CHAR(20), - room CHAR(20), - area CHAR(20), - owner CHAR(100), - createDate DATE, - PRIMARY KEY (equipID) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql CREATE TABLE equipment_interfaces ( - equipID CHAR(20) NOT NULL, - ifName CHAR(20) NOT NULL, - ifType ENUM(10Mbs,100Mbs,1Gb,CDDI,FDDI), - ifNet CHAR(50), - ifIPv4addr CHAR(20), - INDEX (equipID), - FOREIGN KEY (equipID) REFERENCES equipment (equipID) ON DELETE CASCADE, - PRIMARY KEY (equipID, ifName) - ) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) *** mysql CREATE TABLE equipment_interfaces_IPv4alias ( - equipID CHAR(20) NOT NULL, - ifName CHAR(20) NOT NULL, - ifIPv4alias CHAR(20) NOT NULL, - INDEX (equipID), - FOREIGN KEY (equipID) REFERENCES equipment (equipID) ON DELETE CASCADE, - INDEX (ifName), - FOREIGN KEY (ifName) REFERENCES equipment_interfaces (ifName) ON DELETE CASCADE, - PRIMARY KEY (equipID, ifName, ifIPv4alias) - ) TYPE=INNODB; ERROR 1005: Can't create table './test/equipment_interfaces_IPv4alias.frm' (errno: 150) mysql *** ** [vinita@mungah] vinita [1:54] perror 150 Error code 150: Unknown error: 150 -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Foreign key in INNODB
HI I'm not sure what is wrong with my CREATE TABLE syntax, Please advice mysql CREATE TABLE equipment ( - equipID CHAR(20) NOT NULL, - assetNumber CHAR(20), - serialNumber CHAR(50), - description BLOB, - barcode CHAR(20), - room CHAR(20), - area CHAR(20), - owner CHAR(100), - createDate DATE, - PRIMARY KEY (equipID) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql CREATE TABLE equipment_interfaces ( - equipID CHAR(20) NOT NULL, - ifName CHAR(20) NOT NULL, - ifType ENUM(10Mbs,100Mbs,1Gb,CDDI,FDDI), - ifNet CHAR(50), - ifIPv4addr CHAR(20), - INDEX (equipID), - FOREIGN KEY (equipID) REFERENCES equipment (equipID) ON DELETE CASCADE, - PRIMARY KEY (equipID, ifName) - ) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) *** mysql CREATE TABLE equipment_interfaces_IPv4alias ( - equipID CHAR(20) NOT NULL, - ifName CHAR(20) NOT NULL, - ifIPv4alias CHAR(20) NOT NULL, - INDEX (equipID), - FOREIGN KEY (equipID) REFERENCES equipment (equipID) ON DELETE CASCADE, - INDEX (ifName), - FOREIGN KEY (ifName) REFERENCES equipment_interfaces (ifName) ON DELETE CASCADE, - PRIMARY KEY (equipID, ifName, ifIPv4alias) - ) TYPE=INNODB; ERROR 1005: Can't create table './test/equipment_interfaces_IPv4alias.frm' (errno: 150) mysql * [vinita@mungah] vinita [1:54] perror 150 Error code 150: Unknown error: 150 -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto increment
HI ALL I couldn't have an auto-increment in ver 3.23.53 for the table INNODB, mysql CREATE TABLE roomLockAuto ( - roomNum CHAR(20) NOT NULL, - id INT(2) unsigned zerofill NOT NULL auto_increment, - lockNum CHAR(20) NOT NULL, - PRIMARY KEY (roomNum, id) - ) TYPE=INNODB; ERROR 1075: Incorrect table definition; There can only be one auto column and it must be defined as a key mysql CREATE TABLE roomLockAuto ( - roomNum CHAR(20) NOT NULL, - id INT(2) unsigned zerofill NOT NULL auto_increment, - lockNum CHAR(20) NOT NULL, - PRIMARY KEY (roomNum, id) - ) ; Query OK, 0 rows affected (0.01 sec) -- BUT it works if I do mysql CREATE TABLE roomLockAuto ( - roomNum CHAR(20) NOT NULL, - id INT(2) unsigned zerofill NOT NULL auto_increment, - lockNum CHAR(20) NOT NULL, - PRIMARY KEY (id) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) --- BUT I really want PRIMARY KEY (roomNum, id) Any idea??? -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto increment
HI kayamboo wrote: sql, query, OfCourse that is the default criterian for an auto increment column. It need to be the primary key ID is part of the Primary key, It works fine for other table type. I do *NOT* want +---+-+---+ | roomNum | id | lockNum | +---+--+-+ | 3.1 | 01 | 3| | 3.1 | 02 | 5 | | 3.2 | 03 | 8 | ID is 3 +---+-+--+ BUT I want to see +---+-+---+ | roomNum | id | lockNum | +---+--+-+ | 3.1 | 01 | 3| | 3.1 | 02 | 5 | | 3.2 | 01 | 8 |== ID is one +---+-+--+ But you can try a simple script to achieve this and declare your *id* as INT column rather an auto_increment What do you mean? I want the ID to be incremented automatically!! regards - Original Message - From: vinita Vigine Murugiah [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, January 07, 2003 11:35 AM Subject: Re: auto increment HI ALL I couldn't have an auto-increment in ver 3.23.53 for the table INNODB, mysql CREATE TABLE roomLockAuto ( - roomNum CHAR(20) NOT NULL, - id INT(2) unsigned zerofill NOT NULL auto_increment, - lockNum CHAR(20) NOT NULL, - PRIMARY KEY (roomNum, id) - ) TYPE=INNODB; ERROR 1075: Incorrect table definition; There can only be one auto column and it must be defined as a key mysql CREATE TABLE roomLockAuto ( - roomNum CHAR(20) NOT NULL, - id INT(2) unsigned zerofill NOT NULL auto_increment, - lockNum CHAR(20) NOT NULL, - PRIMARY KEY (roomNum, id) - ) ; Query OK, 0 rows affected (0.01 sec) -- BUT it works if I do mysql CREATE TABLE roomLockAuto ( - roomNum CHAR(20) NOT NULL, - id INT(2) unsigned zerofill NOT NULL auto_increment, - lockNum CHAR(20) NOT NULL, - PRIMARY KEY (id) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) -- - BUT I really want PRIMARY KEY (roomNum, id) Any idea??? -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto increment
Paul DuBois wrote: At 23:02 -0500 1/6/03, Matt Hargraves wrote: Well, he can do it, he just can't do it the way that he's trying to do it. He needs to do what the rest of the world does and separate out his different fields into different tables for labeling things. If he needs to make a different table for each 'room' or whatever, then so be it, he just can't try doing it all in one table. He simply needs to go pick up some books that explain what normalization is and apply those rules and he'll be fine. Well, either that, or just use MyISAM tables, which do support composite-key, multiple-independent-sequences the way he wants. :-) Thanks for the info!! The reason why I'm using INNODB table is to support the FOREIGN key to have the transaction safe tables. well. I was bit surprised to see that it does NOT support the multiple-independent-sequencing. Say for Eg I have a table for supplier which has info on their ID, Name, accountNum, URL etc...) Then I have another table called supplier_address because one supplier could have more than one mailing address. ++-+ | Field| Type | ++-+ | supplierID | varchar(20) | | address| blob | +--++ In this case ( I guess I can't split the table any more ) inorder to uniquely identify the entry I have to use the multiple-independent-auto increment. I can't use address as part of the primary keys, it doesn't have the Characteristic to use as part of the primary key(blob). Well. If mysql doesn't support this, then i need to change the design a bit. Thanks Matt - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: vinita Vigine Murugiah [EMAIL PROTECTED]; kayamboo [EMAIL PROTECTED]; mysql [EMAIL PROTECTED] Sent: Monday, January 06, 2003 10:49 PM Subject: Re: auto increment At 14:34 +1100 1/7/03, vinita Vigine Murugiah wrote: HI kayamboo wrote: sql, query, OfCourse that is the default criterian for an auto increment column. It need to be the primary key ID is part of the Primary key, It works fine for other table type. I do *NOT* want +---+-+---+ | roomNum | id | lockNum | +---+--+-+ | 3.1 | 01 | 3| | 3.1 | 02 | 5 | | 3.2 | 03 | 8 | ID is 3 +---+-+--+ BUT I want to see +---+-+---+ | roomNum | id | lockNum | +---+--+-+ | 3.1 | 01 | 3| | 3.1 | 02 | 5 | | 3.2 | 01 | 8 |== ID is one +---+-+--+ Sorry. InnoDB tables do not support the use of composite (multiple-column) indexes to generate multiple independent sequences within a table this way. You cannot achieve the behavior you want with InnoDB. But you can try a simple script to achieve this and declare your *id* as INT column rather an auto_increment What do you mean? I want the ID to be incremented automatically!! regards - Original Message - From: vinita Vigine Murugiah [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, January 07, 2003 11:35 AM Subject: Re: auto increment HI ALL I couldn't have an auto-increment in ver 3.23.53 for the table INNODB, mysql CREATE TABLE roomLockAuto ( - roomNum CHAR(20) NOT NULL, - id INT(2) unsigned zerofill NOT NULL auto_increment, - lockNum CHAR(20) NOT NULL, - PRIMARY KEY (roomNum, id) - ) TYPE=INNODB; ERROR 1075: Incorrect table definition; There can only be one auto column and it must be defined as a key mysql CREATE TABLE roomLockAuto ( - roomNum CHAR(20) NOT NULL, - id INT(2) unsigned zerofill NOT NULL auto_increment, - lockNum CHAR(20) NOT NULL, - PRIMARY KEY (roomNum, id) - ) ; Query OK, 0 rows affected (0.01 sec) - - BUT it works if I do mysql CREATE TABLE roomLockAuto ( - roomNum CHAR(20) NOT NULL, - id INT(2) unsigned zerofill NOT NULL auto_increment, - lockNum CHAR(20) NOT NULL, - PRIMARY KEY (id) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) - - - BUT I really want PRIMARY KEY (roomNum, id) Any idea??? -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL
Left Join more than one table
HI I'm using ver 3:23:49 3:23:53, I thought A LEFT JOIN B USING (c) is Identical to A LEFT JOIN B ON A.c = B.c It seems it is NOT the case if you are joining more that ONE table one of them is EMPTY. Please see the following Example mysql select * from room; +-+--+--+ | roomNum | roomCapacity | roomType | +-+--+--+ | L1.4| 10 | tute | | L1.6| 26 | lab | +-+--+--+ 2 rows in set (0.00 sec) mysql select * from roomLock; +-+-+ | roomNum | lockNum | +-+-+ | L1.4| K1 | | L1.4| K1.4| | L1.6| k1.6| +-+-+ 3 rows in set (0.00 sec) mysql select * from roomOwner; Empty set (0.02 sec) mysql select * from room LEFT JOIN roomLock USING (roomNum) LEFT JOIN roomOwner USING (roomNum) - ; +-+--+--+-+-+-+---+ | roomNum | roomCapacity | roomType | roomNum | lockNum | roomNum | ownerName | +-+--+--+-+-+-+---+ | L1.4| 10 | tute| L1.4| K1 | NULL| NULL | | L1.4| 10 | tute| L1.4| K1.4| NULL| NULL | | L1.6| 26 | lab | L1.6| k1.6| NULL| NULL | +-+--+--+-+-+-+---+ 3 rows in set (0.01 sec) * I would expect the same result if I Left join the roomOwner followed by roomNum, BUT IT IS NOT Is it a bug?? or my expectation is wrong?? * mysql select * from room LEFT JOIN roomOwner USING (roomNum) LEFT JOIN roomLock USING (roomNum) - ; +-+--+--+-+---+-+-+ | roomNum | roomCapacity | roomType | roomNum | ownerName | roomNum | lockNum | +-+--+--+-+---+-+-+ | L1.4| 10| tute | NULL| NULL | NULL| NULL| | L1.6| 26|lab| NULL| NULL | NULL| NULL| +-+--+--+-+---+-+-+ 2 rows in set (0.00 sec) But If I use ON EXPR it WORKS, under http://www.mysql.com/doc/en/JOIN.html A LEFT JOIN B USING (C1,C2,C3,...) is defined to be semantically identical to an ON expression like this: A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3,... mysql select * from room LEFT JOIN roomOwner ON room.roomNum = roomOwner.roomNum - LEFT JOIN roomLock ON room.roomNum = roomLock.roomNum; +-+--+--+-+---+-+-+ | roomNum | roomCapacity | roomType | roomNum | ownerName | roomNum | lockNum | +-+-+-+-+---+-++ | L1.4| 10 | tute | NULL | NULL | L1.4 | K1 | | L1.4| 10 | tute | NULL | NULL | L1.4 | K1.4| | L1.6| 26 | lab | NULL | NULL| L1.6 | k1.6| +-++-+-+---+-+-+ Thanks Vinita - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Creating foreign key
HI Victoria, Harald, Dyego you all were right, the database is not supporting the InnoDb tables; I configure it with --with --innodb option add innodb_data_file_path to the my.cnf. Now it's working!! Thank you so much Vinita sql Dyego Souza do Carmo wrote: vvM HI vvM still not working :-( vvM I didn't do the installation , Do we need to set any option vvM specifically during the installation for foreign key to work?? The InnoDB is loaded ? please run show variables like '%innodb%'; and put here. tnks. sql,query - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: using explicit locking
HI I have a similar question. I'm using InnoDb table type (ver 3.23.53), according to the web side, it does a row level locking. Does this mean that I don't need to do any table locking during the Update/Delete/Insert queries?? How do I test it that It really doing the locking (other than take you word ;-) ) Thanks VInita Stephen Aichele wrote: Hello. MySQL newbie here. I've managed to build my first web database using MySQL and accessing it with Python CGI scripts. I only have two tables going, and not many columns per table, but I'm suspecting that there will be many calls to INSERT, UPDATE, and SELECT. So my question is this: do I need to use explicit locking to make this work well in a web environment? Or will the implicit locking of MySQL handle all the dirty work? thanks. Stephen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Creating foreign key
HI I'm not sure what I'm doing wrong in the following example, the foreign key is NOT working. I'm using ver3.23.53. - mysql CREATE TABLE unit ( - unitCode CHAR(20) NOT NULL, - unitName CHAR(50), - unitYear CHAR(20) NOT NULL, - PRIMARY KEY (unitCode, unitYear) - ) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql CREATE TABLE unit_semesters ( - unitCode CHAR(20) NOT NULL, - unitYear CHAR(20) NOT NULL, - semester ENUM(S, 1, 2, Y, R, N) NOT NULL, - PRIMARY KEY (unitCode, unitYear, semester), - INDEX unit_code (unitCode), - FOREIGN KEY (unitCode) REFERENCES room (unitCode) ON DELETE CASCADE - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO unit (unitCode, unitName, unitYear) - VALUES (433121, testing1, 2000); Query OK, 1 row affected (0.00 sec) ERROR*** I expect the following INSERT to fail since the unitCode(433222) doesn't exist in the table unit BUT mysql INSERT INTO unit_semesters (unitCode, unitYear, semester) - VALUES (433222, 2000, 1); Query OK, 1 row affected (0.00 sec) mysql select * from unit; +--+--+--+ | unitCode | unitName | unitYear | +--+--+--+ | 433121 | testing1 | 2000 | +--+--+--+ 1 row in set (0.00 sec) mysql select * from unit_semesters; +--+--+--+ | unitCode | unitYear | semester | +--+--+--+ | 433222 | 2000 | 1| +--+--+--+ 1 row in set (0.00 sec) mysql Thank you very much vinita - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Creating foreign key
HI still not working :-( I didn't do the installation , Do we need to set any option specifically during the installation for foreign key to work?? mysql CREATE TABLE unit ( - unitCode CHAR(20) NOT NULL, - unitName CHAR(50), - unitYear CHAR(20) NOT NULL, - PRIMARY KEY (unitCode, unitYear), - INDEX unit_code (unitCode) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql CREATE TABLE unit_semesters ( - unitCode CHAR(20) NOT NULL, - unitYear CHAR(20) NOT NULL, - semester ENUM(S, 1, 2, Y, R, N) NOT NULL, - PRIMARY KEY (unitCode, unitYear, semester), - INDEX unit_code (unitCode), - FOREIGN KEY (unitCode) REFERENCES unit (unitCode) ON DELETE CASCADE - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO unit (unitCode, unitName, unitYear) - VALUES (433121, testing1, 2000); Query OK, 1 row affected (0.00 sec) ERROR ** mysql INSERT INTO unit_semesters (unitCode, unitYear, semester) - VALUES (433222, 2000, 1); Query OK, 1 row affected (0.00 sec) Silmara wrote: Change room to unit FOREIGN KEY (unitCode) REFERENCES room (unitCode) ON DELETE CASCADE FOREIGN KEY (unitCode) REFERENCES unit(unitCode) ON DELETE CASCADE Hope this helps, Sil - Original Message - From: vinita vigine Murugiah [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, November 17, 2002 9:15 PM Subject: Creating foreign key HI I'm not sure what I'm doing wrong in the following example, the foreign key is NOT working. I'm using ver3.23.53. -- --- mysql CREATE TABLE unit ( - unitCode CHAR(20) NOT NULL, - unitName CHAR(50), - unitYear CHAR(20) NOT NULL, - PRIMARY KEY (unitCode, unitYear) - ) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql CREATE TABLE unit_semesters ( - unitCode CHAR(20) NOT NULL, - unitYear CHAR(20) NOT NULL, - semester ENUM(S, 1, 2, Y, R, N) NOT NULL, - PRIMARY KEY (unitCode, unitYear, semester), - INDEX unit_code (unitCode), - FOREIGN KEY (unitCode) REFERENCES room (unitCode) ON DELETE CASCADE - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO unit (unitCode, unitName, unitYear) - VALUES (433121, testing1, 2000); Query OK, 1 row affected (0.00 sec) ERROR*** I expect the following INSERT to fail since the unitCode(433222) doesn't exist in the table unit BUT mysql INSERT INTO unit_semesters (unitCode, unitYear, semester) - VALUES (433222, 2000, 1); Query OK, 1 row affected (0.00 sec) mysql select * from unit; +--+--+--+ | unitCode | unitName | unitYear | +--+--+--+ | 433121 | testing1 | 2000 | +--+--+--+ 1 row in set (0.00 sec) mysql select * from unit_semesters; +--+--+--+ | unitCode | unitYear | semester | +--+--+--+ | 433222 | 2000 | 1| +--+--+--+ 1 row in set (0.00 sec) mysql Thank you very much vinita - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
cmd load is not allowed in ver3.23.53???
HI Does any one encounter the same problem?? If load cmd in not working in ver3.23.53, is there any alternative cmd for loading table??? Thanks vinita # mysql select version(); +---+ | version() | +---+ | 3.23.33 | +---+ mysql load data local infile '/home/tech/vinita/TESTmungah/mungah_3.23.53/load_toCheckSafe.txt' - into table roomLockAuto; Query OK, 100 rows affected (0.04 sec) Records: 100 Deleted: 0 Skipped: 0 Warnings: 100 mysql select version(); +-+ | version() | +-+ | 3.23.53-log | +-+ 1 row in set (0.00 sec) mysql load data local infile '/home/tech/vinita/TESTmungah/mungah_3.23.53/load_toCheckSafe.txt' - into table roomLockAuto; **ERROR 1148: The used command is not allowed with this MySQL version mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem
HI You may NOT have create privileges. check your privileges by doing show grants for username@host; You can give CREATE privileges for databases tables GRANT CREATE ON *.* To username IDENTIFIED BY 'passwd' Refer to http://www.mysql.com/doc/en/GRANT.html Phung Huu Phu wrote: Dear Sir or Madam: I have installed MySQL database to Sun Enterperise 250 with Solaris 8 OS. I can run database server and query but I can't create database. Please tell me know how can I correct it ? Thank in advance. Phu - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Perl with Mysql :
HI all
I'm trying to do the following
---
# want to select (just preparing) every thing from the table PERSON
where i don't know the deptID yet.
$per = $dbh-prepare(SELECT * FROM person WHERE deptID = ?);
# selecting deptID from the table ACCOUNT, say with some condition
$acc = $dbh-prepare(SELECT deptID FROM account WHERE ;
$acc-execute()
or die Can't execute the SQL statment: $DBI::errstr\n;
while ( @accRow = $acc-fetchrow_array ) {
# For each deptID I get from the ACCOUNT table, I want all the info from
the PERSON table
$per-execute($accRow[0] )
or die Can't execute the SQL statment:
$DBI::errstr\n;
..
-
When I execute above coding I got error saying
DBD::mysql::st execute failed: Column: 'deptID' in where clause is
ambiguous at ./mkPasswdFile line 76, STDIN chunk 2.
I'm following the example in http://www.perl.com/pub/a/1999/10/DBI.html
The line 76 : $per-execute($accRow[0] )
I also tried quote($accRow[0]), any idea???
Thank you for you help
Vinita
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select Query
Chris Kay wrote: Query ( that gets past the anti spam ) Question is.. I have a select where I want to get ID 15 id 25 Can I do something like WHERE ID = 15,25 I don't think this will work, In SQL you can do WHERE id IN (15, 25) This IN() function doesn't seems working in MYSQL though, Any one encounter the same problem?? Can refer to http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html Or do I have to do WHERE ID = 15 ID = 25 - Chris Kay Techex Communications Website: www.techex.com.au Email: [EMAIL PROTECTED] Telephone: 1300 88 111 2 - Fax: (02) 9970 5788 - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SubSelect Query
HI all' mysql select userGroup from machineGroup_userGroups where hostname=mulga; +---+ | userGroup | +---+ | staff | | tech | +---+ 2 rows in set (0.01 sec) mysql select username from account where loginGroup=staff OR loginGroup=tech; +--+ | username | +--+ | aharwood | | alistair | ... works fine BUT mysql select username from account where loginGroup IN - (select userGroup from machineGroup_userGroups where hostname=mulga) ; ERROR 1064: You have an error in your SQL syntax near 'select userGroup from machineGroup_userGroups where hostname' at line 2 Both loginGroup userGroup are VAR(20) Any idea what is wrong?? Thanks Vinita Paul DuBois wrote: At 12:00 +1000 10/3/02, Chris Kay wrote: Query ( that gets past the anti spam ) Question is.. I have a select where I want to get ID 15 id 25 Can I do something like WHERE ID = 15,25 Or do I have to do WHERE ID = 15 ID = 25 ... WHERE ID IN (15,25) - Chris Kay Techex Communications Website: www.techex.com.au Email: [EMAIL PROTECTED] Telephone: 1300 88 111 2 - Fax: (02) 9970 5788 - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqlimport
HI Egor OK, I want to create new databases, to do that I need database create privilege. Grant CREATE ON ## to ddb@localhost IDENTIFIED BY 'passwd' What should go into #, is it a *? but I should NOT given permission to access other databases. Thanks Egor Egorov wrote: vinita, Monday, September 23, 2002, 9:12:24 AM, you wrote: vvM I can create a database called test, vvM mysql create database test; vvM Query OK, 1 row affected (0.03 sec) vvM BUT I can't create any other databases??? vvM mysql create database tem; vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'tem' vvM mysql create database proj; vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'proj' Seems user 'ddb' or anonymous user has privileges only on the database 'test'. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqlimport
Hi paul Paul DuBois wrote: At 9:05 +1000 9/24/02, vinita vigine Murugiah wrote: HI Egor OK, I want to create new databases, to do that I need database create privilege. Grant CREATE ON ## to ddb@localhost IDENTIFIED BY 'passwd' What should go into #, is it a *? but I should NOT given permission to access other databases. No, it should be db_name.* Then I can ONLY create database called db_name, then every time when I create a database, I have to ask the root to give me create permission on xxx database. This is inconvenient. Is there any other work around?? Thanks Thanks Egor Egorov wrote: vinita, Monday, September 23, 2002, 9:12:24 AM, you wrote: vvM I can create a database called test, vvM mysql create database test; vvM Query OK, 1 row affected (0.03 sec) vvM BUT I can't create any other databases??? vvM mysql create database tem; vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'tem' vvM mysql create database proj; vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'proj' Seems user 'ddb' or anonymous user has privileges only on the database 'test'. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqlimport
Paul DuBois wrote: At 9:41 +1000 9/24/02, vinita vigine Murugiah wrote: Hi paul Paul DuBois wrote: At 9:05 +1000 9/24/02, vinita vigine Murugiah wrote: HI Egor OK, I want to create new databases, to do that I need database create privilege. Grant CREATE ON ## to ddb@localhost IDENTIFIED BY 'passwd' What should go into #, is it a *? but I should NOT given permission to access other databases. No, it should be db_name.* Then I can ONLY create database called db_name, then every time when I create a database, I have to ask the root to give me create permission on xxx database. This is inconvenient. Is there any other work around?? As far as I can tell, you're not making any sense: - First you say you want to grant permission for a given database, and *NOT* to access other databases. - Now you say you don't want to have to specify permissions for given databases. You can't have it both ways. Do you want permission for a specific database, or for all databases? If I am misinterpreting you, then please be more specific. HI sorry for the confusion. well.. I want to create a database where I can give *any* name (ofcourse, the database doesn't exist). Are you trying to say, I need to ask the root, to give me create privilege on database xxx, even before it exist, then only I can create the database xxx. Then every time when I want to create a new database, I need to ask the root to give create privilege ??? thanks Thanks Thanks Egor Egorov wrote: vinita, Monday, September 23, 2002, 9:12:24 AM, you wrote: vvM I can create a database called test, vvM mysql create database test; vvM Query OK, 1 row affected (0.03 sec) vvM BUT I can't create any other databases??? vvM mysql create database tem; vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'tem' vvM mysql create database proj; vvM ERROR 1044: Access denied for user: 'ddb@localhost' to database 'proj' Seems user 'ddb' or anonymous user has privileges only on the database 'test'. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqlimport
Hi I can create a database called test, mysql create database test; Query OK, 1 row affected (0.03 sec) BUT I can't create any other databases??? mysql create database tem; ERROR 1044: Access denied for user: 'ddb@localhost' to database 'tem' mysql create database proj; ERROR 1044: Access denied for user: 'ddb@localhost' to database 'proj' Any idea Thanks Vinita - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
auto Incerment
HI I'm having problem with the AUTO_INCREMENT, we are using ver 3.23.33 This is the definition of table software_machineOSs ++--+--++-++ | Field | Type | Null | Key | Default | Extra | ++--+---+---+-++ | softwareID | char(20)| YES | MUL| NULL| | | id | int(2) || | NULL | auto_increment | | osName | char(20)| YES| | NULL || | osRevision | char(20)| YES| | NULL || ++--+---+---+---+--+ Adding an entry mysql insert into software_machineOSs (softwareID, id, osName, osRevision) - values (readline-4.3, NULL, test, test3); Selecting entries mysql select * from software_machineOSs where softwareid=readline-4.3; +--++---++ | softwareID | id | osName| osRevision | +--++---++ | readline-4.3 | 1 | Tru64 | 5.1| | readline-4.3 | 1 | Solaris/Sparc | 8 | | readline-4.3 | 1 | Solaris/x86 | 8 | | readline-4.3 | 1 | test | test | | readline-4.3 | 1 | test | test1 | | readline-4.3 | 1 | test | test1 | | readline-4.3 | 1 | test | test3 | +--++---++ 7 rows in set (0.00 sec) As you can see the ID did NOT increment, I'm not sure what I'm doing wrong. I tried with out giving any value for ID, 0 value for ID NULL for ID. Non of them seems working. Is this a bug in the version 3.23.33?? Appreciate your fast response Thank you for your time warm regards Vinita - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto Incerment
HI I'm having problem with the AUTO_INCREMENT, we are using ver 3.23.33 This is the definition of table software_machineOSs ++--+--++-++ | Field | Type | Null | Key | Default | Extra | ++--+---+---+-++ | softwareID | char(20)| YES | MUL| NULL | | | id | int(2) || | NULL| auto_increment | | osName | char(20)| YES| | NULL || | osRevision | char(20)| YES| | NULL || ++--+---+---+---+--+ Adding an entry mysql insert into software_machineOSs (softwareID, id, osName, osRevision) - values (readline-4.3, NULL, test, test3); Selecting entries mysql select * from software_machineOSs where softwareid=readline-4.3; +--++---++ | softwareID | id | osName| osRevision | +--++---++ | readline-4.3 | 1 | Tru64 | 5.1| | readline-4.3 | 1 | Solaris/Sparc | 8 | | readline-4.3 | 1 | Solaris/x86 | 8 | | readline-4.3 | 1 | test | test | | readline-4.3 | 1 | test | test1 | | readline-4.3 | 1 | test | test1 | | readline-4.3 | 1 | test | test3 | +--++---++ 7 rows in set (0.00 sec) As you can see the ID did NOT increment, I'm not sure what I'm doing wrong. I tried with out giving any value for ID, 0 value for ID NULL for ID. Non of them seems working. Is this a bug in the version 3.23.33?? Appreciate your fast response Thank you for your time warm regards Vinita - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto Incerment
Hi Well... I tried all the methodes, tried inserting with out the id, with NULL id with 0 as id. No of them seems working :-( Any other ideas?? Thanks you Arjen van der Weijden wrote: Leave out the id column in your insert-statement vinita vigine Murugiah To: [EMAIL PROTECTED] [EMAIL PROTECTED]cc: .OZ.AU Subject: auto Incerment 18-09-02 08:25 HI I'm having problem with the AUTO_INCREMENT, we are using ver 3.23.33 This is the definition of table software_machineOSs ++--+--++-++ | Field | Type | Null | Key | Default | Extra | ++--+---+---+-++ | softwareID | char(20)| YES | MUL| NULL| | | id | int(2) || | NULL | auto_increment | | osName | char(20)| YES| | NULL || | osRevision | char(20)| YES| | NULL || ++--+---+---+---+--+ Adding an entry mysql insert into software_machineOSs (softwareID, id, osName, osRevision) - values (readline-4.3, NULL, test, test3); Selecting entries mysql select * from software_machineOSs where softwareid=readline-4.3; +--++---++ | softwareID | id | osName| osRevision | +--++---++ | readline-4.3 | 1 | Tru64 | 5.1| | readline-4.3 | 1 | Solaris/Sparc | 8 | | readline-4.3 | 1 | Solaris/x86 | 8 | | readline-4.3 | 1 | test | test | | readline-4.3 | 1 | test | test1 | | readline-4.3 | 1 | test | test1 | | readline-4.3 | 1 | test | test3 | +--++---++ 7 rows in set (0.00 sec) As you can see the ID did NOT increment, I'm not sure what I'm doing wrong. I tried with out giving any value for ID, 0 value for ID NULL for ID. Non of them seems working. Is this a bug in the version 3.23.33?? Appreciate your fast response Thank you for your time warm regards Vinita - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto Incerment
HI Paul Thank you very much!!! yes it works when I have PRIMARY KEY (SoftwareId, id) but didn't work for KEY(SoftwareId, id), since this allows to have multiple entries on the keys, I think vinita Paul DuBois wrote: At 16:25 +1000 9/18/02, vinita vigine Murugiah wrote: HI I'm having problem with the AUTO_INCREMENT, we are using ver 3.23.33 This is the definition of table software_machineOSs ++--+--++-++ | Field | Type | Null | Key | Default | Extra | ++--+---+---+-++ | softwareID | char(20)| YES | MUL| NULL | | | id | int(2) || | NULL | auto_increment | I assume that you have a PRIMARY KEY or UNIQUE index on (softwareID, id) here? What's the table type? Not all table types support composite keys with AUTO_INCREMENT this way. | osName | char(20)| YES| | NULL || | osRevision | char(20)| YES| | NULL || ++--+---+---+---+--+ Adding an entry mysql insert into software_machineOSs (softwareID, id, osName, osRevision) - values (readline-4.3, NULL, test, test3); Selecting entries mysql select * from software_machineOSs where softwareid=readline-4.3; +--++---++ | softwareID | id | osName| osRevision | +--++---++ | readline-4.3 | 1 | Tru64 | 5.1| | readline-4.3 | 1 | Solaris/Sparc | 8 | | readline-4.3 | 1 | Solaris/x86 | 8 | | readline-4.3 | 1 | test | test | | readline-4.3 | 1 | test | test1 | | readline-4.3 | 1 | test | test1 | | readline-4.3 | 1 | test | test3 | +--++---++ 7 rows in set (0.00 sec) As you can see the ID did NOT increment, I'm not sure what I'm doing wrong. I tried with out giving any value for ID, 0 value for ID NULL for ID. Non of them seems working. Is this a bug in the version 3.23.33?? Appreciate your fast response Thank you for your time warm regards Vinita - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem with AUOT_INCREMENT
vinita vigine Murugiah wrote: HI I'm having problem with the AUTO_INCREMENT, we are using ver 3.23.33 This is the definition of table software_machineOSs ++--+--++-++ | Field | Type | Null | Key | Default | Extra | ++--+---+---+-++ | softwareID | char(20)| YES | MUL| NULL| | | id | int(2) || | NULL| auto_increment | | osName | char(20)| YES| | NULL || | osRevision | char(20)| YES| | NULL || ++--+---+---+---+--+ Adding an entry mysql insert into software_machineOSs (softwareID, id, osName, osRevision) - values (readline-4.3, NULL, test, test3); Selecting entries mysql select * from software_machineOSs where softwareid=readline-4.3; +--++---++ | softwareID | id | osName| osRevision | +--++---++ | readline-4.3 | 1 | Tru64 | 5.1| | readline-4.3 | 1 | Solaris/Sparc | 8 | | readline-4.3 | 1 | Solaris/x86 | 8 | | readline-4.3 | 1 | test | test | | readline-4.3 | 1 | test | test1 | | readline-4.3 | 1 | test | test1 | | readline-4.3 | 1 | test | test3 | +--++---++ 7 rows in set (0.00 sec) As you can see the ID did NOT increment, I'm not sure what I'm doing wrong. I tried with out giving any value for ID, 0 value for ID NULL for ID. Non of them seems working. Is this a bug in the version 3.23.33?? Appreciate your fast response Thank you for your time warm regards Vinita - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
