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
gunr2171
15.9k25 gold badges61 silver badges87 bronze badges
asked Jun 27, 2013 at 22:23
13
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)`
answered Jun 27, 2013 at 23:27
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, 2013 at 23:23
echo_Meecho_Me
37k5 gold badges58 silver badges78 bronze badges
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
answered Nov 28, 2016 at 10:03
I was having the same problem after upgrading my ODBC Connector from 5.2.7 to 5.3.7, but using MS Access.
I manually added the options
max_execution_time = 0
In my my.ini on server (5.7.10 version) in [mysqld] section. I restarted the server and it worked with the ODBC Connector 5.3.7
UPDATE
Today I executed a query that is longer than 60 seconds and the bug is back. Yesterday my queries were running in less 60 seconds (Before adding max_execution_time = 0 to my.ini, neither were they running).
I executed this command in my PC (ODBC 5.3.07):
show variables like '%max_execution_time%;
Results:
max_execution_time 60000
obs: 60000 = (60 seconds)
I execute a to configure with 5 minutes
SET global max_execution_time = 300000;
show variables like '%max_execution_time%;
Results:
max_execution_time 60000;
In other machine with ODBC (5.2.7) I execute:
show variables like '%max_execution_time%;
Results:
max_execution_time 300000;
In another test:
SET global max_execution_time = 0;
In my Machine (ODBC 5.3.07);
Results:
max_execution_time 60000
In the other Machine (ODBC 5.2.7)
Results:
max_execution_time 0;
I think this 5.3 driver is frozen with max_execution_time setting to 60000 (60 seconds)
Hi,
That’s not the first time I get such error, on no particular request or table and it begins to worry me.
It even happens very often regarding the few amount of requests I can do. I’m afraid about what will happen once the website will be on production.
I own a PRO hosting at OVH. So, I doubt the problem could come from their side…
Here are the infos I can provide from Debugkit. I hope this will evoke something.
From VARIABLES page:
messageSQLSTATE[70100]: <<Unknown error>>: 1317 Query execution was interrupted url/Sitraexports/processexport
errorUnserializable object - CakeDatabaseException. Error: SQLSTATE[70100]: <<Unknown error>>: 1317 Query execution was interrupted in /home/modulwoo/www/2ndGuideTest/vendor /cakephp/cakephp/src/Database/Schema/Collection.php, line 130
code500
_serialize(array)
From SQL LOG
SHOW FULL COLUMNS FROM `paiements` 16 8
SHOW INDEXES FROM `paiements` 1 0
SELECT * FROM information_schema.key_column_usage AS kcu INNER JOIN information_schema.referential_constraints AS rc ON (kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME) WHERE kcu.TABLE_SCHEMA = 'modulwoomobtst' AND kcu.TABLE_NAME = 'paiements' and rc.TABLE_NAME = 'paiements'
Stacktrace:
2015-04-22 20:48:30 Error: [CakeDatabaseException] SQLSTATE[70100]: <<Unknown error>>: 1317 Query execution was interrupted
Request URL: /Sitraexports/processexport
Stack Trace:
#0 /home/modulwoo/www/2ndGuideTest/vendor/cakephp/cakephp/src/Database/Schema/Collection.php(102): CakeDatabaseSchemaCollection->_reflect('ForeignKey', 'paiements', Array, Object(CakeDatabaseSchemaTable))
#1 /home/modulwoo/www/2ndGuideTest/vendor/cakephp/cakephp/src/Database/Schema/CachedCollection.php(65): CakeDatabaseSchemaCollection->describe('paiements', Array)
#2 /home/modulwoo/www/2ndGuideTest/vendor/cakephp/cakephp/src/ORM/Table.php(425): CakeDatabaseSchemaCachedCollection->describe('paiements')
#3 /home/modulwoo/www/2ndGuideTest/vendor/cakephp/cakephp/src/ORM/Query.php(141): CakeORMTable->schema()
#4 /home/modulwoo/www/2ndGuideTest/vendor/cakephp/cakephp/src/ORM/Query.php(123): CakeORMQuery->addDefaultTypes(Object(AppModelTablePaiementsTable))
#5 /home/modulwoo/www/2ndGuideTest/vendor/cakephp/cakephp/src/ORM/Table.php(1147): CakeORMQuery->__construct(Object(CakeDatabaseConnection), Object(AppModelTablePaiementsTable))
#6 /home/modulwoo/www/2ndGuideTest/vendor/cakephp/cakephp/src/ORM/Table.php(882): CakeORMTable->query()
#7 /home/modulwoo/www/2ndGuideTest/vendor/cakephp/cakephp/src/ORM/Association.php(582): CakeORMTable->find('all', Array)
#8 /home/modulwoo/www/2ndGuideTest/vendor/cakephp/cakephp/src/ORM/Association/SelectableAssociationTrait.php(95): CakeORMAssociation->find('all', Array)
#9 /home/modulwoo/www/2ndGuideTest/vendor/cakephp/cakephp/src/ORM/Association/BelongsToMany.php(924): CakeORMAssociationBelongsToMany->_buildBaseQuery(Array)
#10 /home/modulwoo/www/2ndGuideTest/vendor/cakephp/cakephp/src/ORM/Association/SelectableAssociationTrait.php(52): CakeORMAssociationBelongsToMany->_buildQuery(Array)
#11 /home/modulwoo/www/2ndGuideTest/vendor/cakephp/cakephp/src/ORM/EagerLoader.php(539): CakeORMAssociationBelongsToMany->eagerLoader(Array)
#12 /home/modulwoo/www/2ndGuideTest/vendor/cakephp/cakephp/src/ORM/Query.php(645): CakeORMEagerLoader->loadExternal(Object(CakeORMQuery), Object(CakeDatabaseLogLoggingStatement))
#13 /home/modulwoo/www/2ndGuideTest/vendor/cakephp/cakephp/src/Datasource/QueryTrait.php(218): CakeORMQuery->_execute()
#14 /home/modulwoo/www/2ndGuideTest/vendor/cakephp/cakephp/src/ORM/Query.php(596): CakeORMQuery->_all()
#15 /home/modulwoo/www/2ndGuideTest/src/Controller/SitraexportsController.php(144): CakeORMQuery->all()
#16 [internal function]: AppControllerSitraexportsController->processexport()
#17 /home/modulwoo/www/2ndGuideTest/vendor/cakephp/cakephp/src/Controller/Controller.php(404): call_user_func_array(Array, Array)
#18 /home/modulwoo/www/2ndGuideTest/vendor/cakephp/cakephp/src/Routing/Dispatcher.php(114): CakeControllerController->invokeAction()
#19 /home/modulwoo/www/2ndGuideTest/vendor/cakephp/cakephp/src/Routing/Dispatcher.php(87): CakeRoutingDispatcher->_invoke(Object(AppControllerSitraexportsController))
#20 /home/modulwoo/www/2ndGuideTest/webroot/index.php(37): CakeRoutingDispatcher->dispatch(Object(CakeNetworkRequest), Object(CakeNetworkResponse))
#21 {main}
Paiements table is just read to build a list of possible values.
Of course I can repeat the exact same request many times without problem before getting it back, maybe, or maybe not… :-/
Regards,
Alain
It’s one of the more frustrating errors you can get in ASP coding against a SQL database. Query execution was interrupted, maximum statement execution time exceeded. Usually this comes along with a error ‘80004005’.
What this means in essence is that your query you’re trying to run is taking too long. Your MySQL database has a maximum time limit set, in order to prevent runaway long queries from bogging down your system. Sure, that’s fine, but sometimes you need to run something for a while to get a specific task done.
How do you get around the timeout error?
Your first option is to try a server timeout setting. Something like:
Server.ScriptTimeout=30000
Depending on the type of action you’re trying to do, this could do the trick. But there are going to be specific kinds of queries that still get caught up in a time limit. So next we have:
objCmd.CommandTimeout=10000
This works if you’ve set up a command object a la Server.CreateObject (“ADODB.Command”) – if you do that, you can then set a timeout value for it.
However, what if you’re not doing a command? What if instead you’re doing a recordset type of activity, and it’s the initial selection of the recordset that is timing out?
This is where it gets fun.
Where your code says something like:
SQLText = “select field 1, field 2 from …
add in something like this:
SQLText = “select /*+ MAX_EXECUTION_TIME(60000) */ field 1, field 2 from …
That time is in milliseconds. So this is 60 seconds.
The database realizes that you’ve embedded a special command, interprets it, and then goes on and does normal SQL processing on the rest.
Very cool!
#1 17.05.2010 11:35:39
- galko
- Участник
- Зарегистрирован: 17.05.2010
- Сообщений: 3
Ошибка секционирования
Всем привет!
Вот пришлось заняться оптимизацией БД, как написано в мануалах не всегда выходит в реальной жизни.
Отсылаю команду:
ALTER TABLE bhost_entries
PARTITION BY HASH (e_id)
(
PARTITION partition1 ENGINE = MYISAM,
PARTITION partition2 ENGINE = MYISAM,
PARTITION partition3 ENGINE = MYISAM
);
Возвращается ответ:
Query : ALTER TABLE bhost_entries PARTITION BY HASH (e_id) ( PARTITION partition1 ENGINE = MYISAM, PARTITION partition2 ENGINE = MYISAM,…
Error Code : 1317
Query execution was interrupted
Total Time : 00:00:49:688
Почему возвращается ошибка???
Отредактированно galko (17.05.2010 11:46:22)
Неактивен
#2 17.05.2010 13:38:49
- paulus
- Администратор
- Зарегистрирован: 22.01.2007
- Сообщений: 6753
Re: Ошибка секционирования
Ну, кто-то убивает Ваш запрос.
Неактивен
#3 17.05.2010 13:46:48
- galko
- Участник
- Зарегистрирован: 17.05.2010
- Сообщений: 3
Re: Ошибка секционирования
Может от того что таблица очень большая (30ГБ), а процесс разбиения наверное не очень быстрый, по таймауту убивается.
Неактивен
#4 17.05.2010 14:56:02
- paulus
- Администратор
- Зарегистрирован: 22.01.2007
- Сообщений: 6753
Re: Ошибка секционирования
Надо учитывать, что MySQL не накладывает ограничений на время выполнения запросов.
Т.е. таймаут происходит на клиентской стороне.
Неактивен
#5 17.05.2010 17:15:39
- galko
- Участник
- Зарегистрирован: 17.05.2010
- Сообщений: 3
Re: Ошибка секционирования
Всё хорошо, хостеры сделали своё дело.
Неактивен