Cant Write Or Delete To Table
I cant get my php script to insert or delete records into a mysql
table. I can view them all fine but that's about it. I've checked
the user permissions on mysql and have set them all to 'Y' incase that
was the problem but still no joy. Can anyone suggest what the problem
may be? I'm tried the usual RTFM but cant spot the problem
View Complete Forum Thread with Replies
Related Forum Messages:
Cant Write Or Delete
I cant get my php script to insert or delete records into a mysql table. I can view them all fine but that's about it. I've checked the user permissions on mysql and have set them all to 'Y' incase that was the problem but still no joy. Can anyone suggest what the problem may be? I'm tried the usual RTFM but cant spot the problem
View Replies !
Read/write Table Efficiency
I've got a bit of an efficiency question, and just trying to figure out how best to manage this. For my site HearWhere.com, I have a table which holds artists shows. When i am refreshing the collection of shows and geocoding the locations, the database response for user queries slows significantly - which is understandable. One option I'm considering is to have one database which the users connect to for queries, and a second separate database for writing the updates, and then I can just move the updated database to become the live database on a daily basis. The other option I think is just to have a separate table which I write the updates to, and then just replace the table the users interact with from the table which was updated Either way I go, at the moment everything would be done on the same sql server - I don't currently have enough traffic to justify multiple servers.
View Replies !
Delete From Table Statement Doesn't Delete
I have a weird thing happened in my application where I have run a statemement Delete from tblmysqlReceipt; but later I checked through in my log file there line which have been delete is still exist. If any one can help me I can even post the log files too.
View Replies !
Search And Write, Or Write And Recover?
The problem: I need to generate a 'unique string' for each row in a table. I already use auto_increment for system dependencies between tables. What is the best approach one of these or another? After generating a candidate 'unique string' the two strategies that came to mind are: 1. to then search the table's column to see if it is already assigned; locking the table for write while searching and writing the new row, or 2. set the column to UNIQUE when defining the table. Just go ahead and write the new row if you get a "non-unique" exception, generate another 'unique string' and try again. I've tried both on a small XP laptop and get "lock timeout exceptions" rather quickly using #1. But replace those with lots of re-writes when there starts to get "collisions" of 'unique string's.
View Replies !
Write/Move/Save Pdf To MySQL Table?
Is upload the only way to pass a file to a table? I have a contract.pdf which is generated via php (FPDF) and saved in a temporary file. I need that file then moved /written to the appropriate customer table in our database (MySQL ) so that the temporary file may then be overwritten by the next Customer/Order. While I am able to upload the file via <input type="file" name="uploadpdf> and related uploader.php, asking our web-users to “browse” for the file does not suit our needs, and of course adding a default value <input type="file" name="uploadpdf" value="http://localhost/crm/modules/Orders/contract.pdf"> doesn’t work either. As the file is always the same (modules/Orders/contract.pdf) how may I manage that move automatically, (say with a ‘Confirm Order’ button) to the database?
View Replies !
InnoDB: Right For Write-heavy Table? Server Tuning?
I've decided to merge 4500 identical tables into one. They were previously partitioned with one table per user of the system, but I'm imagining I may not need to do this anymore for this particular table. I'd like to do so to reduce the number of files in this database's directory (currently over 15,000), to reduce backup complexity, and maybe improve performance. That's where I'm still unsure. This is the summary table for W3Counter with fields website_id, date, unique_visits, return_visits, page_views. The website_id and date uniquely identify each row. It is a write-heavy table (probably more than 99% INSERT/UPDATE compared to SELECT). I may be making things up, but I believe I remember InnoDB showing superior performance for this type of table. I'd imagine row-level locking would decrease lock contention when being hit by dozens of threads concurrently -- queries trying to update different rows would be allowed since that row isn't locked, unlike MyISAM where the entire table is locked by each query. Merging all the tables into this one would mean starting with about 1.5 million rows and growing about 4500 per day. So what do you think? Is InnoDB the right way to go for this table? Should I expect better performance than MyISAM for the inserts/updates? Should I expect the same or better performance on SELECTs that SUM() those counts over various date ranges, and sometimes group by WEEK(), MONTH() or YEAR() on the date column? I'm not sure whether the primary key is sufficient for that type of query, if a key on just the website_id would help those, or what. Any opinions? My other reason for this thread is to get some pointers on tuning the server variables for InnoDB tables. There's much less written about this than those mainly applicable to MyISAM tables. Which are most important for a table like this? Right now no other tables will be InnoDB as the rest benefit greatly from MyISAM-specific features (like prefix compression on indexes and maintenance of row counts).
View Replies !
ERROR 1022 (23000): Can't Write; Duplicate Key In Table On SELECT
I'm using the mysql server that comes with ubuntu-server 8.04 (mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2) and I have a rather big database (about 5M records). If I try to do this mysql> select count(*),lastname from data group by lastname; all i get is the following error: ERROR 1022 (23000): Can't write; duplicate key in table '' The lastname column is the only one returning this error. (The statement works for firstname, city, street, etc...) Does anybody have an idea as to what's happening here? I've tried searching but could only find this error in relation to database updates and insertions (which would be logical considering the 'duplicate key' part).
View Replies !
Delete Rows From Table A, Which Dont Exist In Table B (base On Column X)
I have 2 tables identically structured. A & B Table A, has column: Product (product code) as primary key Table B doesn't. Apart from that they have the exact same fields. There's also a column: supplier I want to Delete * from table A, where does not exist in B (based on column: Product) & where supplier = apples So to elaborate. Table A is my main table, but it now contains outdated products from supplier apple. Table B has the latest list of products from supplier apple. So I want to remove old products from A that supplier apple no longer makes. mysql version 4.0.27
View Replies !
How To Delete From 1 Table Some Rows Based On Match Results In 2nd Table?
How to delete from TABLE-1 all rows with indexes "i" that match to index j=2 from TABLE-2? TABLE 1: +---+------+ | i | name | +---+------+ | 1 | item1 | | 1 | item2 | | 7 | item3 | <-- delete all rows with i=5,6,7 | 6 | item4 | <-- delete | 5 | item5 | <-- delete | 5 | item6 | <-- delete | 7 | item7 | <-- delete +---+------+ TABLE 2: +---+---+------+ | j | i | name | +---+---+------+ | 1 | 1 | item1 | | 1 | 3 | item2 | | 1 | 2 | item3 | | 2 | 5 | item4 | <---- j=2 => i=5 | 2 | 6 | item5 | <---- j=2 => i=6 | 2 | 7 | item6 | <---- j=2 => i=7 | 3 | 8 | item7 | +---+---+------+
View Replies !
How To Delete Rows In A Table Where Url Shows Only 1 Time In Whole Table
I don't know a whole lot about mysql, i need to fix some things in my db and what i described in the title is the first thing id like to do. Now, I have a table which lists referring urls, clicks to them and the time. Everytime i get a hit from lets say url1.com it gets a new row with a time id (like 1218613809). I am not resetting this table since it has to track the incoming hits forever since the moment they start sending traffic. But now the table starts getting the hickups now and then, it has about 850.000 records in it and it cant handle it anymore. I need to do a 'repair table' action and then it's good again, but it happens more and more often lately. So first i want to delete the rows of the urls who have only sent me 1 click. These urls who sent me 1 click only have one row. When an url sent me 20 clicks, that url is in 20 rows in the table. So I want to delete the rows of the table with the url's that only show once in the entire table. How do i do this?
View Replies !
How To Delete All Rows From One Table With Id NOT Found In Second Table?
How can I delete all rows from table `one` with id NOT found in table `two`? Here is my situation: Table `one` contains transaction on items identified by `id` Table `two contains detailed information on items, identified by the same `id`. Periodically, items are be deleted form Table `two`. My question is: How do I find (and delete) all the rows in table `one` that refer to an `id` that is NO LONGER in table `two` ?
View Replies !
Delete From Two Table
I have two separate tables - ADDRESS and CUSTOMER I want to delete all the ROWS under the table ADDRESS, WHERE CUSTOMER CUSTOMER_ID=1 (CUSTOMER_ID is located in the CUSTOMERS table.)
View Replies !
Delete A Table
i have noticed that there are loads of unwanted table in my mysql database and i need to completely delete those tables..but i find only two options through myphp admin...empty and drop.. but i need to delete the whole table ..how can i do it??
View Replies !
DELETE With Related Table
I have a table (tableA), with a data field (saved as a VARCHAR in YYYY-MM-DD). I have a second table, tableB, which has a field with an int field which refers to the ID of tableA (or is null). I call this field tableB.idA I am trying to delete rows in tableA where the date is less than a specified value AND there is no reference to said row in tableB. But how to specify this in SQL. I tried : DELETE FROM tableA WHERE tableA.date <'2007-01-01' and tableB.idA!= tableA.id; only to get the exception Unknown table 'tableB' in where clause.
View Replies !
Multi-table Delete
i want to only delete from table bids using info from table listing. i tried thisELETE FROM bids USING bids INNER JOIN listing WHERE bids.listingid=listing.listingid AND listing.listingid=1
View Replies !
Multiple Table Delete
I have the following script that will delete records from multiple tables: DELETE course_session_date_time_location, course_enroll FROM course_session_date_time_location AS s, course_enroll AS e WHERE s.course_ID = '$course_ID' AND e.course_ID = '$course_ID' AND s.course_session = '$course_session' AND e.course_session = '$course_session' AND s.course_date = '$course_date' AND e.course_date = '$course_date' My problem is that sometimes there are no records in table "course_enroll". Is there a way to alter my WHERE syntax so that my script will delete from both tables when records exist in both, and will delete from just table "course_session_date_time_location" when records only exist there?
View Replies !
How To Delete NULL From A Table
I have a table that has NULL values and need to delete them. This is a part of the table that has NULL data ORDER ID DATE IDcard '506', '10', '22112007', 'm3625184.326' '506', '10', 'NULL', 'm3625184.326' '506', '10', '22112007', 'm3625184.326' '506', '10', 'NULL', 'm3625184.326' '506', '11', '22112007', 'm3625184.326' '506', '11', 'NULL', 'm3625184.326' '506', '11', '22112007', 'm3625184.326' '506', '11', 'NULL', 'm3625184.326' I try to do it this way : DELETE FROM r506_1x WHERE fentrada = 'NULL'
View Replies !
Beginning Of Table After A Delete
i have a small LAMP server running to keep a log of service requests I receive with an HTML/PHP front end to allow users and myself to interact with the DB.Everything was running smoothly and I was able to add/delete/edit entries normally. Yesterday I deleted some entries at the beginning of the DB and soon afterwards I noticed that all new entries were being appended from the point at which I had earlier deleted the entries, at the beginning of the DB. After doing some testing I realized that new entries are now written starting at whatever point I have last added/deleted an entry from the DB. I can't see that this is intended behavior since this did not happen for the first 6 months of using this DB and it is not something I have ever seen happen in previous dealings with MySQL, or any other DB for that matter.Any thoughts on what might have caused this?My PHP to delete based on unique ID: $sql = "DELETE FROM ServiceRequest_Data WHERE sr_id = $id"; mysql_query($sql) My PHP to add a new entry: $sql = "INSERT INTO ServiceRequest_Data values ( 0, CURDATE(), CURTIME(), '$status', '$name', '$room', '$email', '$problemType', '$problemDetails' )"; mysql_query($sql) My PHP to edit an entry: $sql = "UPDATE ServiceRequest_Data SET status = '$newStatus' WHERE sr_id = $id"; mysql_query($sql)
View Replies !
Delete Table Data Completely
I can't seem to find a way to delete all records of a table. I created my tables in MySQL Query Browser, then fill them up with VC++, but some records were incorrect, And I had to recreate the table again. So please help, I just wanted to delete the records rather than dropping the table completely and start over again..
View Replies !
Delete Rows After 24 Hours In A Table?
I'm gonna keep track of all uniqe visitor on a web page and therefore I need to store all visitors IP-number. Each IP-number is only counted as a visit if its on diffrent days. So the IPs will be saved for 24 hours. Is there a way to automaticly delete rows after 24 hours in a table?
View Replies !
Delete Data From One Table Found In Another
I am a beginner at MySql, self taught and I have a problem I can't figure out. I have a website that my users can sign up for a newsletter and from what I'm told I need to have an unsub link. So I have an unsub link where my users can give me their email address and I can delete it from my database. The problem is I have one table with my current subscribers and one table with my unsubs. I need a query that can delete the the unsub emails from the subscribers table. Nothing I have tried seems to work, but then again I am not a MySql guru. I have tried DELETE * from table1 where (SELECT email from table2); and DELETE from table1 where email = any (SELECT email from table2); They both say query ok but nothing happens, no rows are effected.
View Replies !
Delete Duplicate Entries In Table
I added a custom input system to my user management script, and I created a table to hold each custom inputs values, holding the user id's value, field id, and the value. Each time profile information is updated, a row is inserted for each input field. This works, however it causes the table to become congested with duplicate entries pretty quickly. The table looks like this: field_values field_value_id (int, auto increment, unique) field_value_field_id (int) field_value_userid (int) field_value_value (varchar 255) DUPLICATE entries are DUPLICATE on all the columns except field_value_id. It doesn't matter which is deleted, as long as the unique rows are kept. It would be nicer though if I could delete all the DUPLICATE entries based on field_value_field_id and field_value_userid, and delete the oldest, keeping the newest.
View Replies !
Delete Duplicate Entry From A Table
I have a question about delete command: I have to delete duplicate entry from a table and I think I have to use the following statement: delete from ip_siti_gbw where data_evento in ( select data_evento from ip_siti_gbw group by data_evento having count (data_evento ) > 1 ) When I execute I get an error about count function: FUNCTION statistiche.count does not exist Can I use count function into a subquery such this?
View Replies !
Query Delete All Rows From The Table?
I have a table called "repairs" with multiple rows (let's say for the purposes of this post - from repair_id=1 through to repair_id=10) Why does this query delete all rows from the table? DELETE FROM repairs WHERE repair_id=3 OR 4
View Replies !
Multi-Table Delete & InnoDB
I have a system where several tables have a foreign key referencing a main table. When deleting a main table entry, I was planning on having multiple delete statements for each table. However, I realized that I could use foreign key Constraints to handle the deletion of rows in the dependent tables. I have been using DBDesigner4 to visually design my database and I have used it for creating the initial db. I actually like it quite well and I've had good success with it so far. I've also been using SQLyog for additional access to data during development. Even though DBDesigner shows that my dependent tables have a foreign key, when looking at the create statement, it simply calls them INDEX. When looking at the same statement through SQLyog it calls the FK simply KEY. When looking at the foreign key constraints, it shows in the CREATE & ALTER statement calling the foreign key FOREIGN KEY and then adding the reference and ON DELETE info (such as cascade). So, my questions are these. First, should I delete the existing KEY and then ALTER the table and add the FOREIGN KEY & constraints OR can I simply alter the tables and add constraints to the existing KEY index? How important is it that I use the constraints as opposed to simply having delete statements for each table? Is it simply for efficiancy and speed? Code:
View Replies !
Delete Using Multiple Table: Works On 4.1.20, But Not On 4.0.16
I've a simple delete query delete from joingrp as J using joingrp as J,msggrp as M where J.msgid=M.msgid and M.parent!=0 When I tried it on phpMyadmin at ixweb, having mysql-4.1.20, it works as desired. But when I tried it on phpMyadmin at iPowerweb, having 4.0.16, it says #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'as J using joingrp as J,msggrp as M where J.msgid=M.msgid Can I solve this without using any server-side loops?
View Replies !
Confused About Multi-table Delete
I need to delete rows across 4 tables, and I'm a little confused about how to go about it. My first attempt was to use Joins, but I got a syntax error that I couldn't figure out. I consulted the manual, and going by what it said, I came up with: DELETE s01_Baskets , s01_BasketItems , s01_BasketOptions , s01_BasketCharges FROM s01_Baskets , s01_BasketItems , s01_BasketOptions , s01_BasketCharges WHERE s01_Baskets.basket_id = s01_BasketItems.basket_id && s01_Baskets.basket_id = s01_BasketOptions.basket_id && s01_Baskets.basket_id = s01_BasketCharges.basket_id && s01_Baskets.basket_id IN (2401080,2401085,2401091) && s01_Baskets.lastupdate < ?' I ran the query in phpmyadmin on a backup set to be safe, and I ended up deleting 0 rows. I'm sure that I am doing the join methods wrong. Can someone show me my error? All 4 tables share the same basket_id key. BasketItems, BasketOptions and BasketCharges may all have 0 or more rows per basket_id.
View Replies !
Multi-table Delete Problem
I'm trying to do is delete from multiple tables like using a foreign key, but also be able to delete from just one table if the others don't have a match. For example if I use "t1.id = 2" at the end of the query below both rows get deleted, but if I use "t1.id = 3" the id 3 row in t1 doesn't get deleted. I am using myisam tables. Is there a way to do this or will I have to split it up into multiple delete queries? t1 t2 +----++----+ | id || id | +----++----+ | 2 || 2 | | 3 |+----+ +----+ DELETE FROM t1, t2 USING t1, t2 WHERE t1.id = t2.id AND t1.id = 2
View Replies !
Seeking Constraint For Table Delete
whether someone tries to inject through Perl or hacks into the server and tries to do it from there, I want to set up mysql that tables canot be deleted. perhaps along the lines of how foreign key constraints work. I have checked the docs but can't seem to find anything.
View Replies !
How Do I Delete The Content Of 1 Table But Look At 2 Tables
How do I delete the content of 1 Table but look at 2 Tables to generate the list of rows that should be deleted. For example, say this is the SELECT command: MySQL Code: SELECT user_id, advertising_amount FROM users, payments WHERE users.id = payments.user_id AND click = 'yes' AND vs_respmessage = 'Deducted';
View Replies !
Delete All Records In A Table Takes Forever -- Anyone Know Why?
I have a table called table_a that has 1 record in it. I delete that record, which because of foreign keys (the tables are type InnoDB) will cause the records in 8 other tables to be deleted. The 8 other tables have a maximum of 200,000 records in them. 2 of them have that many while the remaining have < 5,000 records. Currently, deleting that 1 record has taken hours upon hours with no end in site. Does anyone know why in the world this would take so long? Is there any way to speed it up (maybe a config setting I am missing or something)?
View Replies !
Delete /Change Localhost For Root In User Table
I have updated my Host-Information for root in the users table - and guess what happened - I can't login with root anymore. What is more - I took as update address something like http://serverbla.com. So guess what, I can't login because of the protocol information. Since I have a webshop installed on the machine I would like to reset only this information. Is there any chance to do that without crashing the webshop?
View Replies !
Recovery Data From Delete Query And Multi Table Deletion In MySQL
When I test the Delete multi table function in MySQL, DELETE [LOW_PRIORITY] [QUICK] [IGNORE] table_name[.*] [, table_name[.*] ...] FROM table-references [WHERE where_definition] I accidentally delete all data in one table. All data in that table are gone when I try to select them out in Control Center. But when I go into the /mysql/data/mydatabase/, I see a MYD, MYI, frm for that table. And it seems that data is still inside the MYD, althought it's not formatted correctly. So my first qusetion is whether we can recovery data from an accidentally delete action, using MYD or some other log files. My second question is about the multi table deletion in MYSQL For example : "DELETE t1,t2 FROM t1,t2 WHERE t1.id=5 AND t2.id=5" It only runs successfully when all table (i.e. t1 AND t2) have row(s) whose id is equal to 5. In other words, it would NOT delete rows with id = 5 in t1 when t2 has NO record with id = 5, both tables must at least have one record with id =5. So is it the case for multi table deletion in MySQL or I have done somethings wrong?
View Replies !
How To Write A Trigger
we have an invoice table with fields ( invoice_id,date, item_id,item_name, price_per_item,quantity,cust_id) here (item_id ,item_name and price_per_item) are from table item_details. now i have to write a trigger to track the users who have changed the price_per_item column before insertion in invoice table.
View Replies !
|
TRACKER
