reg: on delete cascade
I created two tables like this: create table customer(SID int,name varchar(20),primary key(SID)); create table orders(OID int,O_Date date,customer_SID int,primary key(OID),Foreign key(customer_SID) references customer(SID) on delete cascade on update cascade); And inserted values into it.but when i deleted a row from customer which has reference in orders it didn't showed any error..it deleted the value in customer table while it's reference in orders remain unchanged.Plz help me in solving this.
Re: Migrate HUGE Database
How very inconsistent and obnoxious. But yet far more secure. FWIW, if you're transferring between machines you can gzip the output of mysqldump to compress it, resulting in far less transfer time. Eg. mysqldump -u username -p database_name | gzip -c dump.sql.gz IIRC -- Richard Heyes Employ me: http://www.phpguru.org/cv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
R: Migrate HUGE Database
Hi Terry, you should try using mysql command line tools. Aloha! Claudio Nanni -Messaggio originale- Da: Terry Babbey [mailto:[EMAIL PROTECTED] Inviato: lunedì 10 marzo 2008 19.30 A: mysql@lists.mysql.com Oggetto: Migrate HUGE Database Hello, I have a huge database that I would like to migrate from a server running 4.0.16 to a server running the Windows version 5.0.45. The database is approximately 3,500,000 records. I get timeout errors using PHPMyAdmin to export the data. Does anyone have any suggestions for how I can do this? Thanks, Terry Terry Babbey Infrastructure Specialist Information Technology, Lambton College of Applied Arts Technology [EMAIL PROTECTED], 519.542.7751 x3293 Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto * This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reg: on delete cascade
On Tue, Mar 11, 2008 at 1:51 AM, smriti Sebastian
[EMAIL PROTECTED] wrote:
I created two tables like this:
create table customer(SID int,name varchar(20),primary key(SID));
create table orders(OID int,O_Date date,customer_SID int,primary
key(OID),Foreign key(customer_SID) references customer(SID) on delete
cascade on update cascade);
And inserted values into it.but when i deleted a row from customer which has
reference in orders it didn't showed any error..it deleted the value in
customer table while it's reference in orders remain unchanged.Plz help me
in solving this.
mysql DROP TABLE IF EXISTS `orders`,`customer`;
Query OK, 0 rows affected, 1 warning (0.17 sec)
mysql CREATE TABLE customer(
- SID int,
- name varchar( 20 ) ,
- PRIMARY KEY ( SID )
- );
Query OK, 0 rows affected (0.27 sec)
mysql CREATE TABLE orders(
- OID int,
- O_Date date,
- customer_SID int,
- PRIMARY KEY ( OID ) ,
- FOREIGN KEY ( customer_SID ) REFERENCES customer( SID ) ON DELETE CASCADE
ON UPDATE CASCADE
- );
Query OK, 0 rows affected (0.36 sec)
mysql INSERT INTO `customer` (`SID` ,`name`)
- VALUES ('1', 'Rob');
Query OK, 1 row affected (0.17 sec)
mysql INSERT INTO `orders` (`OID` ,`O_Date` ,`customer_SID`)
- VALUES ('0', '2008-03-11', '1');
Query OK, 1 row affected (0.19 sec)
mysql SELECT * FROM `customer`
- INNER JOIN `orders` ON `customer_SID` =SID;
+-+--+-++--+
| SID | name | OID | O_Date | customer_SID |
+-+--+-++--+
| 1 | Rob | 0 | 2008-03-11 |1 |
+-+--+-++--+
1 row in set (0.33 sec)
mysql DELETE FROM `customer`
- WHERE SID = 1;
Query OK, 1 row affected (0.16 sec)
mysql SELECT * FROM `orders`;
Empty set (0.00 sec)
mysql SELECT `ENGINE` FROM `information_schema`.`TABLES`
- WHERE `TABLE_NAME` IN('orders','customer');
++
| ENGINE |
++
| InnoDB |
| InnoDB |
++
2 rows in set (0.45 sec)
mysql SHOW CREATE TABLE `customer`\G
*** 1. row ***
Table: customer
Create Table: CREATE TABLE `customer` (
`SID` int(11) NOT NULL default '0',
`name` varchar(20) default NULL,
PRIMARY KEY (`SID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql SHOW CREATE TABLE `orders`\G
*** 1. row ***
Table: orders
Create Table: CREATE TABLE `orders` (
`OID` int(11) NOT NULL default '0',
`O_Date` date default NULL,
`customer_SID` int(11) default NULL,
PRIMARY KEY (`OID`),
KEY `customer_SID` (`customer_SID`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_SID`) REFERENCES `customer`
(`SID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
If you do not specify ENGINE and your default engine (probably MyISAM
) engine does not support FK constraints then MySQL will silently
ignore your constraint.
Also:
do you want any of these columns to be able to be NULL? I would think
you probably want the NOT NULL option for everything involved.
I like to use 'USING' in my queries
mysql SELECT * FROM `customer`
- INNER JOIN `orders` USING(`SID`);
but can not because you have different names for the columns. I would
suggest having single naming convention.
--
Rob Wultsch
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
calculating breaks for an employee
Just thinking out-loud, but what design would lend itself to: scheduled break: 11:00-11:15 actual break: 10:30-10:40; 11:20:11:25 the point is to capture the fact that the break was taken not at the scheduled time, as well as the odd duration. Plus, the quantity of breaks. Typically, two breaks per day, but exceptions to that should be flagged with a trigger, as well as odd durations. For quantity, COUNT can come into play. How would the duration of the break be counted? I assume that dates can be subtracted from each other resulting in a duration? Is there some sort of scalar to represent that the break wasn't at the correct time? A metric? I'm thinking that the schedule would be its own table, and then an events table to store login/logouts, breaks and meal breaks. Would it make sense for the PK of both tables to be the employee ID number? thanks, Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Updating rows in a table with the information from the same table
MariSok schrieb: I have a products table with historical price information. Some records are missing price information. I added another field - closest_price, to be populated for records with 0 price. This would be price values from the same table, same product with non-zero price with earliest date. So my update statement looks like this: update t1 a, (select price_date, product_id, price from t1 group by product_id having price_date = min(price_date) and price != 0 ) b set a.closest_price = b.price where a.product_id = b.product_id and a.price = 0; This statement doesn't work. I don't get error - just 0 rows updated. I do get results from b if I ran it on its own. Appreciate any help try: UPDATE t1 a SET a.closest_price = ( SELECT b.price FROM t1 b WHERE b.price != 0 AND b.product_id = a.product_id ORDER BY b.price_date DESC LIMIT 1 ) WHERE a.price = 0; -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: on delete cascade
What is your default storage ? Do this : SHOW ENGINES; You should see something like this: mysql show engines; ++-++ | Engine | Support | Comment | ++-++ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | | BerkeleyDB | NO | Supports transactions and page-level locking | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | | EXAMPLE| NO | Example storage engine | | ARCHIVE| YES | Archive storage engine | | CSV| YES | CSV storage engine | | ndbcluster | NO | Clustered, fault-tolerant, memory-based tables | | FEDERATED | YES | Federated MySQL storage engine | | MRG_MYISAM | YES | Collection of identical MyISAM tables | | ISAM | NO | Obsolete storage engine | ++-++ 12 rows in set (0.48 sec) In my system, the default is MyISAM. References only work with Transaction-based like InnoDB. When you create table with a storage engine other than InnoDB, the syntax for REFERENCE and CASCASE DELETE is parsed but nothing is actually implemented. This URL explains that : http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html Make sure those tables are InnoDB. -Original Message- From: smriti Sebastian [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 11, 2008 4:52 AM To: mysql@lists.mysql.com Subject: reg: on delete cascade I created two tables like this: create table customer(SID int,name varchar(20),primary key(SID)); create table orders(OID int,O_Date date,customer_SID int,primary key(OID),Foreign key(customer_SID) references customer(SID) on delete cascade on update cascade); And inserted values into it.but when i deleted a row from customer which has reference in orders it didn't showed any error..it deleted the value in customer table while it's reference in orders remain unchanged.Plz help me in solving this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reg: on delete cascade
I'll reformat the SQL with line breaks so it looks more readable to me. On Tue, 11 Mar 2008, smriti Sebastian [EMAIL PROTECTED] wrote: I created two tables like this: create table customer(SID int, name varchar(20), primary key (SID)); create table orders(OID int, O_Date date, customer_SID int, primary key (OID), Foreign key (customer_SID) references customer (SID) on delete cascade on update cascade); And inserted values into it.but when i deleted a row from customer which has reference in orders it didn't showed any error..it deleted the value in customer table while its reference in orders remain unchanged. Googling a little ... http://forums.mysql.com/read.php?135,172458,185110#msg-185110 says Unless innodb is the default engine, you need CREATE TABLE(...) engine=innodb; The manual at http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html says In MySQL Server 3.23.44 and up, the InnoDB storage engine supports checking of foreign key constraints, including CASCADE, ON DELETE, and ON UPDATE. See Section 13.2.6.4, FOREIGN KEY Constraints. For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it. In the future, the implementation will be extended to store this information in the table specification file so that it may be retrieved by mysqldump and ODBC. At a later stage, foreign key constraints will be implemented for MyISAM tables as well. So it may be silently ignoring the foreign key. The manual at http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html talks more about it. It doesn't define parent table or child table, and further Googling makes it look like my first assumption had it reversed, that the ORDERS table here is the child table and CUSTOMERS is the parent table, so I gather that deleting from CUSTOMERS should indeed delete from ORDERS if you use InnoDB. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrate HUGE Database
On Tue, 11 Mar 2008, Richard Heyes [EMAIL PROTECTED] wrote: How very inconsistent and obnoxious. But yet far more secure. You misunderstand. I realize that putting the password on the command line is insecure in the presence of ps auxwww and Process Explorer and such. I'm kvetching about how mysql and mysqldump parse their arguments, that * -uUSERNAME and -pPASSWORD are parsed similarly * -u USERNAME and -p UNRELATED_ARGUMENT are not -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
lock write and sql_cache
does the lock table on MySQL (I'm using 5.0.22 or 5.0.45) works even for a cached result? this is my scenario, (I'm using MyIsam tables) thread 1: LOCK TABLE foo WRITE; thread 2: SELECT * FROM foo ; (locked) thread 1: UNLOCK TABLES; thread 2: result of SELECT * .. and that's is, IMHO, correct; btw, since the result of the select is in the cache, it seems I'm unable to 'lock' again the table: thread 1: LOCK TABLE foo WRITE; thread 2: SELECT * FROM foo ; I have the result of the select :( the only way I found to avoid that is use the SQL_NO_CACHE (then SELECT SQL_NO_CACHE FROM foo) is that correct?! there is a way to avoid to specify SQL_NO_CACHE ? or there is something wrong ?! thank you in avance. bye bye marco -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help me format this statement
I am an idiot. table_a and table_b have exactly the same structure. How do I say this in SQL: INSERT (all records from table_a) into table_b where table_a.customer = '12' Just trying to eventually duplicate the whole table, one customer's set of records at a time. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me format this statement
Thanks to everyone who replied. So simple I couldn't see it. :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me format this statement
On Wednesday 12 March 2008 00:14:46 Brian Dunning wrote: I am an idiot. table_a and table_b have exactly the same structure. How do I say this in SQL: INSERT (all records from table_a) into table_b where table_a.customer = '12' Just trying to eventually duplicate the whole table, one customer's set of records at a time. Thanks. IIRC ... something like insert into Table_b select table_a where table_a.customer = '12' Check the EXCELLENT docs at ... http://dev.mysql.com/doc/refman/5.1/en/sql-syntax.html ... and refer to the SQL statement syntaxes ... datamanipulation HTH W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Store MySQL data files and log files (log bin) separated!!!
Hello everyone, I am compiling mysql source and need stored MySQL data files and log files (log bin) separated. For example: /var/log/mysql /* log files and /sgdb/data /* data files I used --localstatedir in ./configure but both data files and log files was stored together. How do this Thanks Marcos Santos Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento! http://br.mail.yahoo.com/
