Update Failing With Error 1062
I issue an update statement containing a where clause on the primary index, so as to update a single record. Howerver this is failing with "Duplicate entry '6' for key 1" -
update clients.calendarentry
set Subject = 'presentation'
where idCalendarEntry = 6;
In the table, the field 'idCalendarEntry' is declared as :
`idCalendarEntry` int(10) unsigned NOT NULL auto_increment
The server version of MySql I am using is 5.0.24
The client version of MySql I am using is 5.0.11
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
BLOB Update Failing On SQLParamData
trying to update a BLOB column using SQLParamData / SQLPutData and it all works fine until the BLOB is > ~700KB. I'm putting it in 1KB chunks, but I have also tried 2KB without success. The SQLPutdata succeeds, but when the final SQLParamData is called SQL_ERROR is returned and the log file shows: DIAG [S1000] [MySQL][ODBC 3.51 Driver][mysqld-4.1.8-standard]Lost connection to MySQL server during query (2013) my.cnf cantains the following: set-variable = key_buffer_size=16M set-variable = max_allowed_packet=1M
Error 1062
My host suddenly went out of business, and deleted all my files without a notice. The only thing I have now is a week old back up of mysql database. I use ssh, and everytime it gives me this dreadful error message: " ERROR 1062 at line 238892: Duplicate entry '189864' for key 1" Can someone please help me correct this error, or overriding it? I'm ignorant in mysql... but can follow instructions if someone kind teach me, please
ERROR 1062
we are using mysql 4.025 running on linux (for vbulletin 3.01). We want to migrate (or better: import) this to mysql 4.41 running on windows 2003. When importing the old mysql (200mb) database, we get an error: "ERROR 1062 (23000) at line 2040853: Duplicate entry 'dr³' for key 2" Has someone an idea what to do? The guys from vbulletin just tell me that if there are different versions of mysql running, there can be problems. Not much help for the moment ...
Error #1062
I get the error "#1062 - Duplicate entry '[some name in tsPI]' for key 1" when I run this query: select tsPI, SUM(tsHours) from Timesheets group by tsPI tsPI is a varchar(100) storing Principal Investigator names, which includes values like "Doe, John" and "N/A" for negligible names. tsHours is a float. I want to retrieve the total hours submitted for each PI. What possibly can be going wrong with this aggregate query
MySQL Error #1062
I have been running a MySQL database for a couple of years. I have recently been doing some development which has meant me sending some test records to the database. I have just received an error (#1062 duplicate entry) and am stumped as to how to get around it! I have a field (INT6 auto inc) that I use to generate a unique reference. I have a feeling that I have somehow set this (manually on one record) to zero. Whenever I try to add a record now (either programatically via PHP or using phpMyAdmin) I get the error. I presumed that I could simply browse the rows and remove the offending line, however it does not display when I browse using phpMyAdmin. Any help/pointers greatly appreciated!
Error Message 1062
i have just restored a backup of all me databases after i messed things up with my website, anyways the back up went ok but i got the following errors ERROR 1062 at line 241: Duplicate entry '179' for key 1 ERROR 1062 at line 242: Duplicate entry '180' for key 1 ERROR 1062 at line 243: Duplicate entry '181' for key 1 ERROR 1062 at line 244: Duplicate entry '182' for key 1 ERROR 1062 at line 245: Duplicate entry '183' for key 1 ERROR 1062 at line 246: Duplicate entry '184' for key 1 ERROR 1062 at line 247: Duplicate entry '185' for key 1 ERROR 1062 at line 248: Duplicate entry '186' for key 1 ERROR 1062 at line 249: Duplicate entry '187' for key 1 ERROR 1062 at line 250: Duplicate entry '188' for key 1 i am having a couple minor prob with my site and thoought it may be linked to this. explain what this means and how to correct it.
ERROR 1062 (23000)
I have been performing mysqldump many times over a year on this database to resync a replication server from time to time. This morning to my shock and horror my attempt to load the sql file into my backup database produced this result: Code: ERROR 1062 (23000) at line 6427: Duplicate entry 'â?' for key 1 I would love to know how to find more information about this entry and how to fix it inside a dump file of 2.4 GB. The vi editor just won't cut it in this case. *sigh*
ERROR 1062: Duplicate Entry For Key 2
I get that error when I try to update a table in my database This is my table +----+------------+---------+-----------+ | ID | CPRNR | NAME | SURNAME | +----+------------+---------+-----------+ | 1 | 1234567890 | Ole | Hansson | | 2 | 9876543211 | Ulla | Jensen | | 3 | 1234321234 | Ole | Olsson | | 4 | 7654321234 | Petter | ?es | | 5 | 9876543213 | Ulla | Jensen | +----+------------+---------+-----------+ 5 rows in set (0.00 sec) And my update lools like this: update patient set cprnr='9876543211',fornavn='Ulla',efternavn='Jense n' where id='5'; This is the error then : ERROR 1062: Duplicate entry '9876543211' for key 2 I don't get it.
Importing From MySQLdump Gives Error...1062 Dupe Entry...
When I try to load a 26GB mysqldump file I get the following error: ERROR 1062 (23000) at line 16953: Duplicate entry 'EZX7575P06-2006...' for key 1 Is there a command I can use to skip errors on the re-import of the mysqldump? I looked at the documentation and did a search, but returned a lot of results that did not pertain to this error code and or a skip errors command.
ERROR 1062 (23000) At Line 6427: Duplicate Entry ...
I have been performing mysqldump many times over a year on this database to resync a replication server from time to time. This morning to my shock and horror my attempt to load the sql file into my backup database produced this result:
PHP Failing To Output First Row
I'm using the following code to output the contents of my database, which is working fine apart from the fact that it seems to be ignoring the first result. $sql=mysql_connect ($host, $user, $pass) or die (mysql_error()); mysql_select_db ($data) or die (mysql_error()); $result = mysql_query ("SELECT tag, name, description FROM `tricktionary` WHERE alpha='a' ORDER BY `name` ASC") or die(mysql_error()); $trick = mysql_fetch_array ($result); while($trick = mysql_fetch_array($result)){ echo '<div class="focus"><h2 id="'. $trick['tag'] .'">'. $trick['name'] .'</h2><p>'. $trick['description'] .'</p></div>';} I've triple checked that the actual database entry in question has a value of 'a' for 'alpha', which it does, so is there something wrong with the while statement?
Mysql_connect Is Failing
This seems a little ridiculous. But I don't know how to connect to my db on a real server. I've been developing on my computer and connecting has been to localhost with no password or user. Now that I have everything uploaded to the real server I don't know what I am supposed to use to connect. The host is godaddy. The database is alaskaerrands. I have a username and password but if I put godaddy.com as the host the page just sits there do something; but who knows what?
PHP Mysql_connect Randomly Failing
I've got a library of PHP code whose first line is a mysql_connect statement, like this: $dbh=mysql_connect() or die("mysql connect failed: $php_errmsg"); Approximately 1% of the time it just fails, for no stated reason: Warning: mysql_connect() [http://www.php.net/function.mysql-connect]: in /var/httpd/htdocs/pi/pi.php on line 3 mysql connect failed: Any ideas why this would be happening? PHP is version 4.3.1 (same results with the latest 4.3.3 release candidate), Mysql is 4.0.12
PHP Mysql_connect Randomly Failing
I've got a library of PHP code whose first line is a mysql_connect statement, like this: $dbh=mysql_connect() or die("mysql connect failed: $php_errmsg"); Approximately 1% of the time it just fails, for no stated reason: Warning: mysql_connect() [http://www.php.net/function.mysql-connect]: in /var/httpd/htdocs/pi/pi.php on line 3 mysql connect failed: Any ideas why this would be happening? PHP is version 4.3.1 (same results with the latest 4.3.3 release candidate), Mysql is 4.0.12
Mysqldump Restore Failing
I created a backup of my database with mysqldump. I then installed a new server, and restored succesfully the database. After this, for some reason I deleted the database (mysql drop database), and created it again (mysql create database). Now I cannot anymore restore the database with mysqldump. What is the reason? The original *.sql file is exactly the same as it used to be. The mysqldump restore stops after the "SETs" as below, and just doesn't continue. no errors or warnings given. code: .....
Re-created Table Failing
Re-created a table in my database. All field names and values are identical to the original table. Table name is correct too. Now the web-pages will not store data to the table.
Failing To Import An Export From PhpMyAdmin
My hosting company has MySQL 5 and so do I on my local Fedora Core 5 Linux machine. However, when I import a dump file from their enviornment into mine, I get this error ERROR 1064 (42000) at line 21428: 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 'TABLE `EC_SAVED_ORDER_LINE_ITEMS` ADD CONSTRAINT `FK1_SAVED_ORDER_LINE_ITEMS` ' at line 3 This is the line (and those around it) from my file. It is complaining about the first line. What is the problem? Code:
Modify Table Engine Type Failing.
I keep getting the following error when trying to change the tables engine type. Why is this failing and how can I fix it? mysql> ALTER TABLE trade_messages ENGINE = INNODB; Query OK, 827793 rows affected, 1 warning (1 min 3.90 sec) Records: 827793 Duplicates: 0 Warnings: 0 mysql> SHOW WARNINGS; +---------+------+--------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------+ | Warning | 1266 | Using storage engine MyISAM for table 'trade_messages' | +---------+------+--------------------------------------------------------+ 1 row in set (0.00 sec)
#1062 - Duplicate Entry '127' For Key 1
Table set up like this: `id` `makeid` `name` (the id is primary auto-increment) This is for a list of car makes and models, I also have a make table that the makeid links to. I currently have 127 rows in this table. I try to add another with this SQL: INSERT INTO `ct_a_model` ( `id` , `makeid` , `name` ) VALUES ( '', 15, 'FEROZA II' ) I thought that if I don't specify an ID then it would auto increment, as it has done with all the other 127. Also if I specify an id: INSERT INTO `ct_a_model` ( `id` , `makeid` , `name` ) VALUES ( 128, 15, 'FEROZA II' ) I still get the same error. Can anyone point me in a direction?
Getting Error In Batch Update
I am using Mysql 4.1.13 with Tomcat5 and Connector mysql-connector-java-3.1.10. When I try to update the table in batch mode sometimes( When server is idle for a long time) get following error. Code:
UPDATE Syntax Error
I've just installed MySQL 5 on my Mac and am trying to update a couple of tables - the syntax I'm using seems to be right, but I constantly get an error. Here's what I have - feel free to point out my stupidity... SELECT ibf_members.id, ibf_pfields_content.member_id, ibf_members.mgroup, ibf_pfields_content.field_2 FROM ibf_members INNER JOIN ibf_pfields_content ON ibf_members.id = ibf_pfields_content.member_id UPDATE ibf_pfields_content SET field_2='Member' WHERE ibf_members.mgroup = 3
Mysql UPDATE Error
the following results in "Invalid SQL syntax error". I tried a few different options but none seemed to have worked. Would someone please help. I believe the error is around/after the WHERE statement. $query = "UPDATE ATAMA_users SET ". "user_name = "" . $formVars["username"] . "", " . "user_passwd = "" . $formVars["loginPassword"] . "", " . "user_firstname = "" . $formVars["firstname"] . "", " . "user_lastname = "" . $formVars["lastname"] . "", " . "user_email = "" . $formVars["email"] . "", " . "user_phone = "" . $formVars["phone"] . "" . " WHERE user_id = " . $userID . """ ;
Error On UPDATE Command
on this statemet: UPDATE semaphoreTable SET semaphore = CONTROL_NONE i get this error: Unknown column 'CONTROL_NONE' in 'field list'" it is indeed true that there is no column named CONTROL_NONE. i am trying to set the data in semaphore to CONTROL_NONE semaphoreTable consists of one field, called semaphore, a varchar(50)
MySQL Update Syntax Error
I am currently developing a simple content management system for my personal website hosted at waynegrills.co.uk and have stepped into a trench full of water with no rope to help me out, if you get what I mean, this error keeps on popping up what is wrong with the following code? if(isset($_GET['changeorder'])) { foreach ($HTTP_POST_VARS as $key=>$value) { if (substr($key, 0, 6) == "order|") { $orderid = substr($key, 6); $query = "UPDATE pages SET order = '$value' WHERE id = '$orderid' LIMIT 1"; mysql_query($query) or die("The coder FUCKED up - SQL Error: ".mysql_error()); } } }
Update Doesn't Work - 1064 Error
The code: update course_series_assignments as c, users as u set c.user=u.UsersID where c.user=u.User; ...returns an error "1064-You have an error in your SQL syntax near 'as c, users as u set c.user=u.UsersID where c.user=u.User;' at line 1". The c.user column currently contains varchar data like "John Smith", as does u.User. The u.UsersID column contains an auto-incremented unsigned integer. I want the integers to replace the full names in the c table where they match the User column in the u table.
Blob Update In Perl DBI Gives Syntax Error
I have a Perl app that stores pictures in a blob. Now I want to load a thumbnail version of the picture into another blob column, but the update fails with a syntax error. I'm able to insert the new record, with blobs, into a separate table, but can't update the original record. Is it not possible to update blobs?
Error On Update Or Insert Data But No Message
I have an application on asp and mysql 5.0, connecting with ODBC 3,51. When the application insert data or update data, MySql don't send error message, but the data don't update or insert. The transaction is ignored!!!!. Then, on my application I can't detect error. My code: set Command1 = Server.CreateObject("ADODB.Command") Command1.ActiveConnection = MM_base_STRING Command1.CommandText = "UPDATE EXAM SET MaAnt='"&MaAnt&"',Mois='"&Mois&"',Annee='"&Annee&"',[...]WHERE Id_Exam='"&id&"'" Command1.CommandType = 1 Command1.CommandTimeout = 0 Command1.Prepared = true Command1.Execute() Have anybody an idea?
Blob Update In Perl DBI Gives Syntax Error
I have a Perl app that stores pictures in a blob. Now I want to load a thumbnail version of the picture into another blob column, but the update fails with a syntax error. I'm able to insert the new record, with blobs, into a separate table, but can't update the original record. Is it not possible to update blobs?
Update INT UNIQUE Column - Duplicate Entry Error
As an example, I'm creating a table which holds following values in MySql 5.0: idcol lft (unique) 1 1 2 2 3 3 4 4 -> when I try to update the lft column using : UPDATE unique_example set lft = lft +1 where lft > 1; I get following error message : Duplicate entry '3' for key 2 Exactly the same approach works using SQL server 2000. Apparently, MySQL processes the rows one by one, as it throws an error on the UNIQUE lft column from whom I wish to update all rows at once? Code:
1146 MySqlCC Error Cant Update Table Data
I am working with MYSQLCC (Control Center) and when I try and add data to a table using the run query it errors out with error 1146. error 1146. table rossoptical.1 doesn't exist. my table name is asphericallenses It seems like there is a reference pointer problem! This is my first time using the MYSQLCC and I dropped the table and recreated it with all of the data. I Still the same problem. I have read a lot about other problems here and I think I have done everything suggested. even when I run the select query I get the same problem, but I do see the columns of data, I just can not change the data.
Error 1452 Cannot Add Or Update A Child Row: A Foreign Key Constraint Fails.
I am creating a contacts database using family information. My tables in question look like this: create table Household ( HouseholdID int(4) not null, LastName varchar(100), constraint pk_HouseholdID primary key (HouseholdID) ); create table Personal ( PersonalID int (4)not null, HouseholdID int (4) not null, AddressID int (4) not null, FirstName varchar (20), LastName varchar (60), constraint pk_PersonalID primary key (personalID) ); create table ContactInfo ( AddressID int (4) not null, PersonalID int (4) not null, lastName varchar(50) not null, mailingAddress varchar(60), City varchar(25), state varchar (20), ZipCode varchar(5), PhoneNumber int (10), EmailAddress varchar (60), constraint pk_AddressID primary key (AddressID) ); ** I have others, but these are the ones involoved in the issue** Here is what I am trying to update with: insert Personal values (0100,0001, 1000, 'Gram & Gram', 'Holbrook'); THis is my error: [mobile3] ERROR 1452: Cannot add or update a child row: a foreign key constraint fails (`directory/personal`, CONSTRAINT `fk_AddressID` FOREIGN KEY (`AddressID`) REFERENCES `contactinfo` (`AddressID`))
Error #1293: There Can Be Only One TIMESTAMP Column With CURRENT_TIMESTAMP In DEFAULT Or ON UPDATE Clause
I am using MySQL 4.0/4.1 version. And I am trying to add two timestamp columns to a single table. The columns are insert_date and updat_date to capture the date/time the record was initially inserted as well as the date/time the record was last updated respectively. When I try to set one column (insert_date) with a default value of CURRENT_TIMESTAMP and the other column (updat_date) with ON UPDATE CURRENT_TIMESTAMP, I end up getting the following error: #1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause Is there any way around this problem?
#1062 - Duplicate Entry 'blah Blah Blah' For Key 1
I keep getting the following error in a SELECT query: #1062 - Duplicate entry 'blah blah blah' for key 1 Here's the query: select count(*) as freq, problemType from Problems group by problemType order by freq desc I'm running a problem report system, and I want to write a query which counts the number of occurrences reported for each problem type. I couldn't make sense of the error because it sounds like an INSERT problem but I'm clearly using a SELECT statement. I then did a test migrate of all the data from the Problems table to an Oracle DB table, and the same query ran successfully. I am thinking this is a MySQL bug but I'm not sure.
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?
Unable To Update - Have Update Priveledge
I've been granded update priveledge to tables in a few databases on our server: Select_priv ,Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, References_priv, Index_priv, Alter_priv, Create_tmp_table_priv, and Lock_tables_priv are all set to 'Y' for a selected database. I can connnect and read the data and data definitions just fine. I cannot insert, update, delete, create, or alter any table in this database. Our admin worked for a couple of hours yesterday trying to resolve the issue, but no joy. I've googled for this problem and the most relavent posts I've found were problems related with users not being able to connect. I can connect and I can read, I just can't do any of the other priviledges.
ON DUPLICATE KEY UPDATE - Don't Have A Key But Want To Update
This may seem like more of a PHP question but I will come onto the MySQL part in a minute. Basically I have a PHP script that lets users upload 2 different CSV files to different tables. The problem I have is that sometimes these CSV files are updated and then need to be reimported into the database through the script. I have made this script totally generic so that when you change the table that is being entered to the rest of the script will alter itself to import the correct CSV to that table. But a problem arises when I have a CSV file that contains each product sold and the shopper who bought the product (so the shopperID may appear in more than 1 row) - however this file does not have a unique id for each row meaning that when I use an INSERT INTO with a ON DUPLICATE KEY UPDATE... it will just insert more rows instead of updating rows because there is now key in the table. The format of the table is like this (with some example data to show you more clearly): +-----------+-------------+---------+------+--------+------+ | ShopperID | Product Code | Product | Price | Amount | Units | +-----------+-------------+---------+------+--------+------+ 546733 1315 prod 1 64.00 64.00 0 546733 1316 prod 2 43.99 43.99 0 OK just ignore the last 2 fields - that is a problem with the old system there was which does not add the units up right. Anyway basically I might have the same shopper ordering 2 things which will come up on different rows - I have 3 things I cannot do: 1. I can't make the ShopperID field the PRIMARY KEY because I have many times where there are 2 rows with the same shopper 2. And I cannot just do an UPDATE when it gets to a duplicate entry because it will overwrite every time it gets to a shopper that already exists. 3. I don't want to add a unique primary key to the CSV as this will mean that the system I am creating loses its ability of being automatic because the person (an admin) that uploads the file will have to make sure that it has that extra field which is auto incrementing. Does anyone have any ideas on how I can get round this? Here is my PHP function which contains the query: PHP // this function for insert data to csv function makeINSERTS($text, $table, $tablefields, $correctcsv){ $insert = array(); //make array for hold data insert $i = 0; $success = true; while(list($key, $val) = each($text)){ // Insert the data $insert[$i] = "INSERT INTO ".$table." VALUES('"; $insert[$i] .= implode("','", $val); $insert[$i] .= "') ON DUPLICATE KEY UPDATE "; foreach($tablefields as $k=>$field){ $insert[$i] .= "`$field`='{$val[$k]}',"; } $insert[$i] = substr($insert[$i],0,strlen($insert[$i])-1); // echo $insert[$i].'<p></p>' $result = mysql_query($insert[$i]); if(!$result) { echo 'FAILURE to insert/update any or all of the database form uploaded CSV!<br />' $success = false; die('Query failed: ' . mysql_error().'<br />'); } $i++; } if($success == true && $correctcsv == true){ echo 'SUCCESS in uploading the CSV file to the database!<br />' } else { } return $insert; }
Update Queries Do Not Always Update
We are running a MySQL database using PHP to create html pages and forms to update and add information to the db. Sometimes the update query works, sometimes it doesn't - or at least, not permanently. A new record may be added, but five minutes later it has sometimes, but not always, mysteriously disappeared. Where should I look for a solution? Is there a cache that MySQL creates that may be being accessed unwittingly? We are using the standard php and MySQL versions that come as standard on a Mac OS X Tiger 10.4.7 server. The browser we are using is Firefox 2.0.4.
Create Table Error. #1064 - You Have An Error In Your SQL Syntax; Check The Manual That Corresponds
I have been trying to create two tables with the SQL below. I have the SQL in file and tried to import it to PhpMysqlAdmin in my control panel. The wierd thing is that the first table gets created while the second one is not created and error is thrown instead. The error message is #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 ....
ERROR 1010 (HY000): Error Dropping Database (can't Rmdir '.a', Errno: 41)
I am using MySql 5.0.21-community Edition for widows XP. I can successfully connect to the MySql Server as a root and create a database 'a'. Which creates a directory 'a' under my mysqldata directory with a single file 'db.opt'. Now dropping the database 'a' gives the following error ERROR 1010 (HY000): Error dropping database (can't rmdir '.a', errno: 41) The directory mysqldataa does not have any other files or hidden files. The commands I have executed are: ------------ Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 6 to server version: 5.0.21-community-log Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> create database a; Query OK, 1 row affected (0.09 sec) mysql> drop database a; ERROR 1010 (HY000): Error dropping database (can't rmdir '.a', errno: 41) mysql> --------- I have searched web, lists, bugs and could not find any.
Error 1064 Restoring PhpMyAdmin Dump (error On Foreign Key Clause)
I am getting an Error 1064 when I try to restore a dump I created using phpMyAdmin from an online database into a newly created database on my home system. The error points to a section of the dump file that adds foreign key constraints. I've already disabled the foreign key checks. The problem seems to occur only with tables in which I have more than one foreign key. The dump file foreign key constraint section looks very odd to me because multiple ALTER TABLE statements to add foreign keys are separated only by commas, not semi-colons, and even weirder, each time a new statement to add a foreign key constraint is created, it is then repeated in the creation of the next foreign key constraint. Code:
I Get This Error Error: Libmysqlclient.so.10: Cannot Open Shared Object File:
I have moced an app from one server to another and I am testinga python module that will extract lead from a database and send them to our email vendor. However, In running the script as a test, I get this error: Import Error: libmysqlclient.so.10: cannot open shared object file: What doe sit mean and is there an easy way to fix it?
Got Error 134 From Storage Engine. Error Number 1030
Sometimes when I execute a query on a database, I get the following error from MySQL: Got error 134 from storage engine. Error number 1030 Does anyone know what this mean? What does error 1030 mean and can I get other values for error 134? I heard people saying that this is an indication that a table got corrupted, but I want to hear more about this problem.
Mysqldump, Getting Error Error #1016, Can't Open *.ibd File
So we have a MySQL database that suddenly started reporting "can't open *.ibd file" errors on InnoDB tables in phpMyAdmin. (MyISAM are fine). To recover the data is all I want at this point -- so one suggestion I came across was to try and dump using mysqldump like this: mysqldump -u root -p mydatabase databasedump.sql Enter password: ******************* mysqldump: Got error: 1016: Can't open file: 'access.ibd' (errno: 1) when using LOCK TABLES Is there a way to run this dump so that it will export the database tables and ignore the *.ibd error? I had read that removing *.frm files might help, but our testing suggests that it isn't effective either.
New Error 1146 Error And Behavior With Mysqlcc + Mysql-5.0.15
I have just switched from mysql-4.1.14 on a FreeBSD-4.11 box to mysql-5.0.15 on a FreeBSD-6.0 box. In both environments I use mysqlcc to select, insert and update tables. On the 4.1.14 machine, using mysqlcc, I could double click in a field and change its value; only if I had selected all the fields. I couls not do this if I had selected a subset of the fields. The number of rows returned didn't matter, as long as I did "select * from..." I could directly edit a field value in mysqlcc. Now, under mysql-5.0.15, I cannot edit fields at all, whether I "select * from..." or not. And every select I do gives me this error in mysqlcc's explain pane: [mymachine] ERROR 1146: Table 'tablename.1' doesn't exist Well it certainly does exist, and I can edit its values using the mysql client or phpmyadmin (ugh, clumsy). There are lots of posts on the net about error 1146 and various apps and various versions of mysql and even mysqlcc. mysqlcc had this problem with an earlier version of mysql than 5.x but this supposedly was fixed with release 0.94. Can anyone give me some insight on what's going on here, or a workaround? Or another gui client that works as well as mysqlcc?
Error #1103 - Incorrect Table Name Error?
I have always been able to copy my live database to my test database using "copy database" then my hosting company moved my site to a another server and now I can not copy my database anymore. My site works 100% with no problems but I cannot copy the database anymore This is what I am doing: I select "Copy database to" + "Structure and data" + "Add DROP TABLE" (like I have always done) but now it says a error "MySQL said: #1103 - Incorrect table name 'ABOUT' I use MySQL version 4.1.21-standard & PHP version 4.4.4 I looked this error up and it said Error: 1103 SQLSTATE: 42000 (ER_WRONG_TABLE_NAME) Actual error: Error SQL query: DROP TABLE IF EXISTS `thebill_testspecmakers.com`.`ABOUT` MySQL said: #1103 - Incorrect table name 'ABOUT' I read that I have to upgrade to ver 5.1 Oracle, SQL server etc. conform to standard SQL in respect to table naming. MySQL only does from version 5.1.
MySQL Error, #1064 - You Have An Error In Your SQL Syntax.
I was adding a table in phpMyAdmin and when i clicked on go it produced this SQL CREATE TABLE `pm` ( `to` VARCHAR( 30 ) NOT NULL , `from` VARCHAR( 30 ) NOT NULL , `message` BLOB( 25000 ) NOT NULL , UNIQUE ( `to` , `from` ) ) and this error MySQL said: #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 '(25000) NOT NULL, UNIQUE (`to`, `from`))' at line 1
|
TRACKER
