It is necessary to execute sql query to the table for 85 million lines (6.8 GB, 4 columns, column id in the index).

UPDATE Detailinfo SET id=REPLACE(id,'"','')

Falls out with an error (1206): The total table size * /

If you remove the index and try to run the query without it, then mysql just hangs.

The database is located on LAN, so the issue of a temporary shutdown does not bother.

Can I somehow cut the request for portions?

  • one
    And if in portions to update and add a condition? so that the extra entries are not selected and updated in pieces, i.e. doing something like UPDATE... WHERE id like '%"%' LIMIT 0, 1000 - BOPOH
  • as suggested by the English. You can still try stack through LOCK / UNLOCK TABLES, it should turn off the line lock on which the error falls out - kroder
  • The problem with lok / unlock will not be solved. So the request hangs even without an index. The solution with WHERE already tried, hangs anyway. The feeling that WHERE performs is not the first. ps Although without UPDATE finds only 80k lines in which it is necessary to do REPLACE . - Zhenya40
  • Why UPDATE 100% of the table? WHERE id like '% "%' required. - nick_n_a

2 answers 2

Although without UPDATE it finds only 80k lines, in which it is necessary to make a REPLACE

Then I do not see a problem if you have a local database. Make a request of the form:

 SELECT CONCAT('UP DATE Detailinfo SET Id=''',REPLACE(Id, '"', '') ,'''WHERE Id=''',Id,'''') FROM Detailinfo WHERE name LIKE '%"%'

Get 80 lines of the form:

 UPDATE Detailinfo SET Id='jennifer lopez'WHERE Id='""jennifer lopez""' UPDATE Detailinfo SET Id='ray liotta'WHERE Id='"ray" "liotta"' UPDATE Detailinfo SET Id='samuel l. jackson'WHERE Id='"samuel" l. "jackson"'

Then copy them and run.

Most likely, if there are only 80 lines, you can neatly write UPDATE with WHERE, but you need to look at the real data than the server doesn’t like simple UPDATE with WHERE.

UPD: stop 80k lines? Then a little bit will not work :)

Can I somehow cut the request for portions?

Try this query:

 SELECT CONCAT('' ,'UP DATE Detailinfo SET Id=REPLACE(Id, ''"'', '''')' ,'WHERE Id BETWEEN ''', MIN(Id), ''' AND ''', MAX(Id) ,''' AND Id LIKE ''%"%''' )QRY FROM( SELECT Id, @I := @I+1 AS N ,1000000 AS K --тут задайте нужное значение K FROM Detailinfo, (SELECT @I := -1)I ORDER BY Id )T GROUP BY NN%K

He will return you 850 dml UPDATE requests of the form:

 UPDATE Detailinfo SET Id=REPLACE(Id, '"', '')WHERE Id BETWEEN 'alan rickman' AND 'gary oldman' AND Id LIKE '%"%' UPDATE Detailinfo SET Id=REPLACE(Id, '"', '')WHERE Id BETWEEN 'george clooney' AND 'michael caine' AND Id LIKE '%"%' UPDATE Detailinfo SET Id=REPLACE(Id, '"', '')WHERE Id BETWEEN 'michael douglas' AND 'steve martin' AND Id LIKE '%"%' UPDATE Detailinfo SET Id=REPLACE(Id, '"', '')WHERE Id BETWEEN 'sylvester stallone' AND 'tommy lee jones' AND Id LIKE '%"%'

Each of which will process a pack of 1 million. rows, pack size can be changed by setting the K parameter in the query.

How it works: sorts all the rows in the order of Id, numbers. Then it groups each pack (K pieces), finds the minimum and maximum Id values, and generates the corresponding. DML request.

Since Id has an index, it should be executed normally. If you put K = 10 million. receive 85 DML requests.

  • one
    You can also insert your 50k rows into an intermediate label with tempTable(Id INT, IdForUpdate строка) fields tempTable(Id INT, IdForUpdate строка) and then write queries such as the update Detailinfo set .... where id in (select idForUpdate from tempTable where id between 1 and 10000) is also a division on packs - pegoopik
  • Request with DML also hung. I solved the problem by running INSERT with WHERE id like '%"%' into a separate table, doing a REPLACE and then reverting the INSERT into the initial table. However, now you need to somehow execute DELETE extra rows (it also hangs), but the result is satisfied. - Zhenya40
  • @Mike Used LOCK for DELTE : LOCK TABLES Detailinfo WRITE; DELETE FROM Detailinfo WHERE id like '%"%'; UNLOCK TABLES Detailinfo; LOCK TABLES Detailinfo WRITE; DELETE FROM Detailinfo WHERE id like '%"%'; UNLOCK TABLES Detailinfo; collapsed again with error 1206. The extra processes cannot hang, after such frills from the database, you have to quickly overload the Open Server (and with it all the processes, respectively) - Zhenya40
  • @Zhenya40 By the way, the usual select with the same condition that you give for delete by speed works fine. try the same select but with a small limit. then notice that delete also allows you to set limit. Those. You can safely delete 1000 lines of say - Mike

They write that if it is impossible to use indexes, MySQL can lock all records that it scans.

The problem occurs because of the insufficient size of the innodb_buffer_pool_size setting. So, if constraints through WHERE or LIMIT do not save you, you can try changing this parameter.

https://stackoverflow.com/q/10253482/272885