Testing On Update & Delete Cascade
When I added indexes and foreign keys to my tables I also inserted ON UPDATE CASCADE and ON DELETE CASCADE.
However, when I tried testing that this works by entering:-
select * from address, name where address.name_ID=name.ID;
It says empty set. What am I doing wrong?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Testing On Update & Delete Cascade
When I added indexes and foreign keys to my tables I also inserted ON UPDATE CASCADE and ON DELETE CASCADE. However, when I tried testing that this works by entering:- select * from address, name where address.name_ID=name.ID; It says empty set. What am I doing wrong?
ON DELETE CASCADE ON DELETE UPDATE
I'm using this "reference_definition" with the motor MyISAM and version 5.0.27-community-nt, and it doesn't work. The child tables don't delete (with "ON DELETE CASCADE") or doesn't update (with "ON UPDATE CASCADE")
ON DELETE CASCADE
I have added my first two MySQL tables: CREATE TABLE a ( id int NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), content varchar(20) NOT NULL, done date NOT NULL ) CREATE TABLE w ( id int NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), swedish varchar(20) NOT NULL, english varchar(20) NOT NULL, assignment_id int NOT NULL, INDEX fk_assignmentid (assignment_id), FOREIGN KEY (assignment_id) REFERENCES assignment(id) ON DELETE CASCADE ) I have added data to both tables, so that the value in column assignment_id in table w corresponds to the value in column id in table a. When I delete the record in table a, shouldn't the records in table w that has correspondning values also be deleted automatically?
On Delete Cascade
i have 3 tables. 1 has the main key in it, the other 2 tables reference the main key as a foreign key WITH on delete delete cascade set. the problem is that when i delete the rows from the main table, the delete does not cascade.
Cascade Delete Or Triggers?
Something that annoys me about MySQL is the fact that triggers are not activated when a row is deleted as a result of a cascade delete. I fail to see why this is, but perhaps there is a good reason, or its a bug, who knows. Anyway, I want to use some triggers here and there to ensure the data is as tidy as possible, and remove anything that doesn't need to exist any more (e.g. I have a couple of 1 to 1 relationships that really do need to be two separate tables), but the lack of cascade delete is causing problems. I started replacing one or two of the cascade deletion settings with triggers, to simulate the cascade, and allow the other triggers to work, but as I add more tables and it all gets more complicated its becoming a real nightmare maintaining it, and working out which cascades need to be triggers. My question is this, is there any way at all to get cascades to activate triggers? Or would there be a major performance impact if I just dropped all cascades and replaced them all with triggers?
Temporary On Delete Cascade
I'm facing a little problem in deleting records from MySQL. My database contains around a 100 tables and there are so many relationship among them (just like spaghetti). I want to delete couple of records with all their children but the problem is that the constraints do not have an "On delete cascade". I do not want to add the delete cascade to the constraints but I was wondering if there is a temporary cascade deletion in MySQL so when I delete a record it will automatically delete all its children at once.
On Delete Cascade Does Not Get Triggered
i have created my database on a ubuntu linux, its running as slave-master but it seems like the on delete cascade does not get triggered, I have no idea why!!! Code:
Delete Cascade In Many To Many Relation
I have table A and B that has many to many relation (Table C is the many to many) I want that when i delete records from A, the records from C and B will also delete. I set on delete cascade in C table FK (for A and B) but when i delete records from A it delete only A,C. Is there any way to delete A,B,C when i delete records from A??? I know that the problem is that this is many to many , so there is an option that some records from B will be delete despite the fact that they have also relation with records that has not deleted, but i dont care to delete those records.
Delete Cascade In Many To Many Relation
I have table A and B that has many to many relation (Table C is the many to many) I want that when i delete records from A, the records from C and B will also delete. I set on delete cascade in C table FK (for A and B) but when i delete records from A it delete only A,C. Is there any way to delete A,B,C when i delete records from A??? I know that the problem is that this is many to many , so there is an option that some records from B will be delete despite the fact that they have also relation with records that has not deleted, but i dont care to delete those records.
Cascade Delete Using Foreign Keys
i have been using Mysql for an application I am trying to implement. For the last few weeks I have understood that mysql aint any good with foreign keys and cascade deletes and updates. Then I recently read it can be done effectively with something like: CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) TYPE = InnoDB; can anyone explain the InnoDB - hows it all tie into Mysql. Does this need to be stated as tables are created - does it keep consistency effectively - as up till now I was going to use nested queries going through all the relevant tables.
Foreign Key Contraints, On Delete Cascade Not Working?
I created the following .sql file to demonstrate a problem I'm having. According to the manual: If |ON DELETE CASCADE| is specified, and a row in the parent table is deleted, then InnoDB automatically deletes also all those rows in the child table whose foreign key values are equal to the referenced key value in the parent row. However:
Foreign Key Contraints, On Delete Cascade Not Working?
I created the following .sql file to demonstrate a problem I'm having. According to the manual: If |ON DELETE CASCADE| is specified, and a row in the parent table is deleted, then InnoDB automatically deletes also all those rows in the child table whose foreign key values are equal to the referenced key value in the parent row. Code:
Manual Cascade Delete Implementation Needed
I am trying to manual clean up a child table when parent table records are deleted. I tried: DELETE FROM agencyImage WHERE imageID IN (SELECT images.imageID FROM images LEFT OUTER JOIN agencyImage ON (images.imageID=agencyImage.imageID) WHERE agencyImage.imageID is null) This failed because you can't delete from a table that you are looking at. So how do I delete the record from agencyImage table if no record exist in the parent images table?
How To Cascade Delete Related Records Without Deleting The Main Record?
Let's say I have the following database tables: CREATE TABLE user ( id INT UNSIGNED NOT NULL PRIMARY KEY, username VARCHAR(20) NOT NULL, ) ENGINE = INNODB; CREATE TABLE user_email ( user_id INT UNSIGNED NOT NULL PRIMARY KEY, email VARCHAR(100) NOT NULL, FOREIGN KEY (user_id) REFERENCES user(id) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE = INNODB; CREATE TABLE user_phone ( user_id INT UNSIGNED NOT NULL PRIMARY KEY, phone VARCHAR(100) NOT NULL, FOREIGN KEY (user_id) REFERENCES user(id) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE = INNODB; What I want to do is delete all records in the 'user_email' and 'user_phone' tables without deleting the record in the user table. I have a database without nearly 20 tables with one-to-one and one-to-many relationships with the user record and I need a way to cascade delete records in relationship tables.
ON UPDATE CASCADE Fails
I have a table which has a foreign key relationship with itself. I want and expect my updates to cascade (deletes definitely cascade as expected) but instead I just get error 1217: foriegn key error. I have written example code to use in reproducing the problem: Code:
Update Better Than Delete
running mysql 4.1 on a dedicated server (it's actually a non-standard cluster setup) just wantd to know, i realise that mysql locks tables for updates but does it lock tables for deletes? and if not, would a DELETE followed by an INSERT be faster than one UPDATE statement?
DELETE,UPDATE Or Other Statement In WHERE
I would like to let my admins write WHERE statements. . In panel where you can see all users of my site, admin should have input where he can put some filters in WHERE statment. E.G. standard query is SELECT login FROM USERS . and admin would write age > 18 and male = 'f' . php script would conact it and it would execute SELECT login FROM users WHERE age > 18 and male = 'f' I have question about security of this solution. Is possible to put in WHERE statement sobe dangeurous statemetns like DELETE of UPDATE?
Cascading Update/delete
MS SQL Server has support for cascading update or delete. Does MySQL have anything like that?
Update / Insert / Delete Log
I'm wondering if there is any way to get a lot of recent update/insert/delete statements performed on a db by a particular user?
Cascase Update And Delete
I have two tables (both InnoDB) and I want to have one updated on the insert of data in another. The two table names are: team_tbl and team_ostat_tbl. When a new entry into the team_tbl is conducted, I want a record to automatically be created in the team_ostat_tbl. This is what I have: team_tbl: team_id (PK) and some other columns. team_ostat_tbl: team_ostat_id (PK), team_id (FK) and some other columns. I have cascade update on, but when I insert a record into the team_tbl nothing is inserted in the team_ostat_tbl.
Dry Run Of A Mysql UPDATE/REPLACE/DELETE
Is there anyway to check what an UPDATE etc. query will do to the database before it actually does it? More like a test run of the query. What I would like to do is: 1) See the number of rows affected 2) See what those rows look like currently 3) See what the rows would look like after the query I am trying to write a little php script that syncs two tables from different databases, but would like to be able to run it first without making any changes. Unfortunately I am stuck with mysql 4.0.18.
How To Update/Delete Bunch Of Rows Quickly?
I have a textarea that allow user to edit a bunch of rows of data quickly. It either add/delete/update rows. How do I do it quickly? The db table has rows of names. e.g. the db table has: car |Susan dog |John monkey |Chris John enters: plane boat house After processing, the db table should say car |Susan plane |John boat |John house |John monkey |Chris How do I update the db table quickly? If I use update query, it will update John's rows but there is no rows to update? There are only rows to add and delete at this time. I don't want to delete all records of John every time John enters the data, because a user can potentially have a lot of rows in the table.
Block Update/delete Commands Without Where Clause
I want to prevent accidental updates/deletes without where clause. most of them can be equivalent to drop table but they can be done with update/delete permissions. is there any option to do that.
Cannot Delete Or Update A Parent Row: A Foreign Key Constraint Fails
I am trying to delete a record through a user interface, but when i try to delete the record i get the following message: Cannot delete or update a parent row: a foreign key constraint fails The parent table is a products table and has four child tables linked through a product id foreigh key. The table is an innoDB type.
Problem With CASCADE.
I want to use Cascade in MySQL and here is all the code I have used to make my database, but I just cant get the foreign keys with to work with Cascade... area_id int PRIMARY KEY NOT NULL AUTO_INCREMENT, area varchar(20)); INSERT INTO ygb_area (area) VALUES ('København'),('Storkøbenhavn'),('Nordsjælland'),('Sydsjælland'),('Vestsjælland'), ('Andet'); CREATE TABLE ygb_user( user_id int PRIMARY KEY NOT NULL AUTO_INCREMENT, email varchar(50) NOT NULL, password varchar(12) NOT NULL, admin int ); CREATE TABLE ygb_mail( mail_id int PRIMARY KEY NOT NULL AUTO_INCREMENT, inrigtig liderlig tøs!!!');............................................
Testing MySQL
I'm trying to test MySQL (i've already tested php and it works, so I'm not real sure I need this step, but I'm sure I need to know how to do it reagardless). I test it using localhost as the hostname and it works except that this is what the page looks like. ....
Testing For Bit Set In Int Field
Say I have a column of type int(4) and it represents a bitmapped pattern, so bit pos 1 means "apples", 2 means "oranges", 3 means pears etc. The value 3 is "apples & oranges". How would I construct a query such that I could return these? For example, if I was searching for oranges and the bitmap field was called type in table fruit the logic would be SELECT FROM fruit WHERE (type & 0x02) > 0; All my tests so far just return all values.
DB Testing Software
Are there any available tools for testing mysql db performance? What do you guys typically use for testing your db's performance and data integrity?
MySQL Testing
I just recently installed MySQL and am also running an APACHE 2.0 server on my computer (Windows XP machine). I followed the instructions in the help document on installing MySQL and I believe it is installed properly, no errors occured, it was pretty straight forward. However, when it comes to testing, the documentations on give commands using the "Windows Command Line Client" and not the MySQL Command Line Client. I installed MySQL as a service, that turns on and off with Windows.
CASCADE And Foreign Keys
I've noticed a strange behavior while I wanted to DROP my tables in my database. I have some InnoDB tables as well, and while I wanted to delete `form_form` I got an error message: #1217 - Cannot delete or update a parent row: a foreign key constraint fails Now this is clearly because of some foreign key issue. THe interesting thing is that I don't have any RESTRICT or NO ACTION clause in my db. As far as I understood from the docs CASCADE and SET NULL shouldn't cause any problem like not letting me delete. PS: Even after EMPTYing all the InnoDB tables I can't DROP the parent table (form_form) before I dropp the two child tables. Code:
How Do I Set Up My System As A Testing Server
I work on a Windows XP Pro. Platform and I am having trouble setting my system up as a Testing Server for PHP pages. I want to be able to test scripts and build my database. I tried downloading and installing MYSQL and PHP but it still does not work. Is there some kind of Configuration I need to do in the PHP.ini file?
Load Testing Tools
We are developing an application using PHP and MySQL 4.0.16, that we need to stress test. Can someone please point me to some testing tools we can use?
Equality Testing With NULLs?
When I do "SELECT count(1) FROM table_name WHERE 0 <> 2", I get the total number of rows in the table. When I do "SELECT count(1) FROM table_name WHERE NULL <> 2", I get 0. Shouldn't I the same answer as I did with WHERE 0 <> 2?
Load Testing Tools
We are developing an application using PHP and MySQL 4.0.16, that we need to stress test. Can someone please point me to some testing tools we can use?
Testing SSL Conections W/ MYSQL
I recently installed all MySQL 4.1.1 including max RPMS. During the installation i used "--nodeps" on MAX package and at Shared-compat cause they were requiring the libssl.0.9.6 and libcrypto.0.9.6. I am using a RH 9 and these files are installed on my system. The result for mysql query : show variables like 'have_openssl'; is have_openssl | YES I created a user using the grant command with 'require ssl' like this: [color=blue] > grant insert,select on MyDatabase.* to myssluser@myhost require ssl;[/color] Query OK I can connect using this username and CLIENT_SSL param (isn't this param just for php 4.3 or higher?): mysql_connect($dbserver, "myssluser", $dbpass, CLIENT_SSL); It runs on my php 4.2.2 compiled with openssl 0.9.7, but i need to TEST if this is a real ssl connection.
Load Testing MySQL
I know this is quite a bit off topic, but i was wondering if anyone else out there had any experience with Segue's Silk Performer load testing tools. More specifically, i am trying to compile a test script to allow me to perform some transactional database testing to load test a MySQL 4.0.13 database server running behind some apache webservers. I have obtained an ODBC driver from MySQL and have some specific queries i would like to use in the test. I am have used SP to load test the webservers specifically, but i am not 100% sure how to run this against a database server now. If anyone has any experience they can share, please feel free to email me off list.
Testing Speed And Query Cache
I'm trying to test performance on some queries with a million of registers on my table, using various keys. The problem is that the first time i run the query, it takes about 4/5 seconds, but after that it takes about 0.02 seconds. I know that query cache can be flushed with reset query cache, flush query cache, flush tables also modifing some caps at the sql query, but i'll never take 4/5 seconds again. for example: select * from table where id=2 order by index_field desc; 4/5 seconds. select * from table where id=2 order by index_field desc; 0.02 seconds. select * from table where id=3 order by index_field desc; 4/5 seconds. select * from table where id=3 order by index_field desc; 0.02 seconds. etc, etc...
Changing Table Set Up To ONUPDATE CASCADE
I need to change one of my tables so that it will ONUPDATE CASCADE. I don;t see how to do this in PhpMyAdmin so would someone please tell me either how to do it or if I need to create a new table from scratch.
Testing Parameters Within A SELECT Statement
I am building a SELECT statement where I will be passed search parameters depending on what user has entered. Each parameter may have a value or maybe NULL. What construct (CASE, IF, etc) can I use to build my WHERE clause so that the passed value is included or if NULL, all rows are returned. SELECT * FROM table WHERE col_a = val_a (or if VAL is NULL then all rows) AND col_b = val_b (or if VAL is NULL then all rows) AND col_c = val_c (or if VAL is NULL then all rows) etc ...
Bogus Datasets For Database Testing
I'm teaching myself mysql and wonder if there are any sources of inane, complex, bogus data that I can download and insert into a table(s.) I have created some very small sets to practice joins and whatnot but they are not very big or complex, or exciting. compressed tar of text strings (no blobs) could have plenty of data but still be a manageable download. It would also be a nice test of my machine and tuning skills.
ERROR 1045 When Testing In The Control Center
In the MySQL Control Center 0.9.4-beta, I right click the top level server name and choose the Edit option. Then I have entered a User Name and a Password in the General TAB. I then mark the option "Promt for Password" and hit the "Test" button. I get a small window where I should enter the password for User name@Host name. I enter the password and hit the OK button. Now I get the error message "ERROR 1045; Access denied for user; 'User name@Host name' (Using password: YES)" When looking in the Help menu, the Contents - F1 is not activated, so I can't get any online help. I'm just wondering why the test for the password failed and why there are no online help in the consol?
Testing The Database? Backing Up, General Checks
I want to know what else I can be doing to check the stability of our database. Currently what I do every evening is download the complete database through PHPMyAdmin on our dedicated server, once downloaded I start up the Wamp Server and import the database locally through PHPMyAdmin on my own machine. The next thing I do is run the Check Tables command on all the tables to see if everything is where it is. It always comes back as OK on each table so I presume everything is ok? Is there anything else I should be doing, or is what I am doing correct? Every so often we delete auctions to free up space in the database. Once this is done we have overhead in the tables. Sometimes this can be 2-10mb, so what I do then is run the optimise command on the tables, again am I correct in doing this? Finally our database is now over 40mb, to me this massive as the largest database I have worked on before has been 4mb, please dont laugh. What sort of database sizes have you seen in MySql or are you running at present? What is MySql like in the long run stability wise. If looked after can we get many years of good service from it.
Testing Access To MySQL-DB In Windows-Network
Background: I am writing in C# a program, which reads data out of a MySQL-DB. When the DB is on my local developer PC everything is fine. But when trying to access the MySQL-DB on a server in our Windowsnetwork, I receive the message: "Host '**' is not allowed to connect to this MySQL server".
Testing For An Empty Set Returned By A Nested Select Statement
For those of you following the "While Loop Within Select" thread... I've been restructuring my database to incorporate the changes. Now I've got a table of skipped stops rather than a table of stops (I need it that way for various reasons) and I need to return the schedule to the user as long as the user's stop isn't in the list of skipped stops. SO say something like: SELECT yada yada yada FROM schedule AS 's1' WHERE ( SELECT skippedstop_id FROM skippedstops WHERE schedule_id=s1.schedule_id AND location_id=$origin ) == "EMPTY SET" Only I have no idea how to actually test for the empty set from the nested select...
Complex Query - UPDATE Within UPDATE?
Edit: Before anyone leaves this thread, don't be put off by the regular expressions! They are not the problem, so please stay and read. OK, this query has got my head spinning. I am basically creating a query that goes through each product in a table to update the stock for that particular item with that particular size (i.e. I am talking about shoes - different models and each model has different sizes (uk kids 12 -> uk 11). With each shoe it does (or is meant to do) the following: 1. The PHP script that runs the query is looping through every size outside of the query 2. So for each of these sizes it checks to see whether the product it is currently on matches the size it is on 3. When it finds the size it is on, it then deducts the correct number of units from the stock table 4. The final WHERE clause makes sure this subquery inside the UPDATE only happens when the StockUpdated field of the Product table equals 0 (in other words, the stock hasn't been counted before) Basically what I need to do, is first to make sure what I currently have got does the above correctly but also I need the query to UPDATE the StockUpdated field to 1 only when it has been updated successfully. How could I do this? Unfortunately I cannot just add an extra update entry to the end of the query as this would update the StockUpdated field regardless of whether it has been properly counted or not. Here is the query I have so far (with a little simple PHP around it doing the loop): PHP $shoesizes = array(1 => 'ukk12','ukk13','uk1','uk2','uk3','uk4','uk5','uk6','uk7','uk8','uk9','uk10','uk11'); $numshoesizes = count($shoesizes); for($i = 1; $i < $numshoesizes; $i++) { $stockupdate = " UPDATE heelys_stock,items_ordered SET heelys_stock.size_".$shoesizes[$i]." = (SELECT CASE WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^0-9]12( )?(' -- if UK Kids 12 THEN heelys_stock.size_ukk12 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^0-9]13( )?(' -- if UK Kids 13 THEN heelys_stock.size_ukk13 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]1( )?(' -- if UK 1 THEN heelys_stock.size_uk1 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]2( )?(' -- if UK 2 THEN heelys_stock.size_uk2 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]3( )?(' -- if UK 3 THEN heelys_stock.size_uk3 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]4( )?(' -- if UK 4 THEN heelys_stock.size_uk4 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]5( )?(' -- if UK 5 THEN heelys_stock.size_uk5 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]6( )?(' -- if UK 6 THEN heelys_stock.size_uk6 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]7( )?(' -- if UK 7 THEN heelys_stock.size_uk7 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]8( )?(' -- if UK 8 THEN heelys_stock.size_uk8 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]9( )?(' -- if UK 9 THEN heelys_stock.size_uk9 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]10( )?(' -- if UK 10 THEN heelys_stock.size_uk10 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]11( )?(' -- if UK 11 THEN heelys_stock.size_uk11 - (items_ordered.Amount/items_ordered.Price) FROM items_ordered WHERE items_ordered.StockUpdated = 0) WHERE (heelys_stock.id = (SELECT heelys_stock.id FROM heelys_stock,heelys_shoe WHERE SUBSTRING_INDEX(items_ordered.Product,',',1) = heelys_shoe.full_shoe_name)) , items_ordered.StockUpdated = 1" // at the moment this last update of the items_ordered table happens to every record!!! even if the other part of query fails // update stock for size $i mysql_query($stockupdate); } Hope someone can see how I can do this? I've been working on this query for 2 or 3 hours now and I've been making reasonable progress but now I am really stumped.
Update Replication, Force Update
Recently an error in the db on my master caused the slave to fail. I noticed this after a few days when I looked at the status and it said "Slave_SQL_Running: No". After looking further I saw what the error was on the master. What is the best method of re-synching the databases? Is there a command to force a re-replication or synch of the dbs? Would you delete the slave's db and update over? In this case, is there a command to pull the data down from the master?
|
TRACKER
