Query execution was interrupted, error #1317

Question

What I have is a table with a bunch of products (books, in this case). My point-of-sale system generates me a report that has the ISBN (unique product number) and perpetual sales.

I basically need to do an update that matches the ISBN from one table with the ISBN from the other and then add the sales from the one table to the other.

This needs to be done for about 30,000 products.

Here is the SQL statement that I am using:

UPDATE `inventory`,`sales` 
   SET `inventory`.`numbersold` = `sales`.`numbersold` 
 WHERE `inventory`.`isbn` = `sales`.`isbn`;

I am getting MySQL Error:

#1317 SQLSTATE: 70100 (ER_QUERY_INTERRUPTED) Query execution was interrupted

I am using phpMyAdmin provided by GoDaddy.com

`UPDATE inventory,sales` ... is that correct? I mean the comma between `inventory,sales` — Rahul, Jun 27 '13 at 22:25
I mean your query is wrong the way you posted ... it should be `UPDATE inventory SET inventory.numbersold = sales.numbersold WHERE inventory.isbn = sales.isbn` — Rahul, Jun 27 '13 at 22:29
@Rahul when I run that SQL statement, I get this error #1054 - Unknown column 'sales.isbn' in 'where clause' — PTC, Jun 27 '13 at 22:31
well, I am going by your post ... you must have a table named `sales` in your database. In order to help you you need to provide the complete query along with tables (probably schema) involved in query. — Rahul, Jun 27 '13 at 22:33
That column isn't unknown, but it only works when I do the inventory, sales at the beginning of the UPDATE statement. — PTC, Jun 27 '13 at 22:34
@Prix inventory contains 49,212 rows. In each of those rows is information about the books (i.e. Title, Author, Publisher, etc.). Sales contains 26,455 rows. I realize that there is not an equal number of rows in each table. That's ok. I just need ones that match to be updated. Make sense? — PTC, Jun 27 '13 at 22:45
@Prix yes. Same error. I'm thinking this might be a limitation of my GoDaddy database? Thoughts? — PTC, Jun 27 '13 at 22:53
@PTC you would have to ask the support, I've done a small test and both queries run just fine see it here: [**Yours**](http://sqlfiddle.com/#!2/97aaf/1/0) and [**mine**](http://sqlfiddle.com/#!2/7a767/1/0) — Prix, Jun 27 '13 at 22:59
@Prix this could be my problem. My ISBN field is a text datatype. Would int be a better datatype for this? It is a 13 digit unique product ID for books. Sorry if this is a beginner question. I am a beginner, so I don't know all of the ins and outs of this. — PTC, Jun 27 '13 at 23:02
if you remove the dashes you could use `mediumint` or `int` but I don't think that is the issue here. — Prix, Jun 27 '13 at 23:05
@PTC ask godaddy is they have a query timeout, if they do and your query takes longer than that it will be interrupted. — Prix, Jun 27 '13 at 23:10

score 9 · Answer 1 · answered Jun 27 '13 at 23:27

I've probably come to this a bit late, but... It certainly looks like the query is being interrupted by an execution time limit. There may be no easy way around this, but here's a couple of ideas:

Make sure that inventory.isbn and sales.isbn are indexed. If they aren't, adding an index will reduce your execution time dramatically.

if that doesn't work, break the query down into blocks and run it several times:

UPDATE `inventory`,`sales` 
  SET `inventory`.`numbersold` = `sales`.`numbersold` 
WHERE `inventory`.`isbn` = `sales`.`isbn`
  AND substring(`inventory`.sales`,1,1) = '1';

The AND clause restricts the search to ISBNs starting with the digit 1. Run the query for each digit from '0' to '9'. For ISBNs you might find selecting on the last character gives better results. Use substring(inventory.sales,-1)`

score 0 · Answer 2 · answered Jun 27 '13 at 23:23

0

try to use INNER JOIN in the two tables like that

       UPDATE `inventory` 
       INNER JOIN `sales` 
       ON  `inventory`.`isbn` = `sales`.`isbn`
       SET `inventory`.`numbersold` = `sales`.`numbersold`

answered Jun 27 '13 at 23:23

echo_Me

37,078
5
58
78

score 0 · Answer 3 · answered Nov 28 '16 at 10:03

UPDATE inventory,sales SET inventory.numbersold = sales.numbersold WHERE inventory.isbn = sales.isbn AND inventory.id < 5000

UPDATE inventory,sales SET inventory.numbersold = sales.numbersold WHERE inventory.isbn = sales.isbn AND inventory.id > 5000 inventory.id < 10000

...

If the error, you can try to reduce the number to 1000, for example

Query execution was interrupted, error #1317

3 Answers3

Linked