Error Code 1206 On Insert With A Select From Another Database

[Meservy]

I am doing an insert on a database table and using an select to fill all the values. . It has worked until recently as the source table is just under 2 million records.

The error I get is

Query : INSERT INTO obituary3.obit (NameFull, NameLast, NameGiven, NameFullObit, NewsPaper, NewsPaperID, PublishedDate, Location, Submit…

Error Code : 1206

The total number of locks exceeds the lock table size

Execution Time : 00:00:00:000

Transfer Time : 00:00:00:000

Total Time : 00:00:00:000

The following is the full sql statement . . .

INSERT INTO obituary3.obit (NameFull, NameLast, NameGiven, NameFullObit, NewsPaper, NewsPaperID, PublishedDate, Location, SubmitSrc, ObitText, ObitTextLength, ObitWords,

ObitType, PublisherID, PublishedDatesText, DeathDate, BirthDate,

ImageName1, ImageName2, ImageName3, ImageName4, FuneralHome, FuneralURL, CharityURL, ReviewedBy, UpdComplete)

SELECT NameFull, NameLast, NameGiven, NameFullObit, NewsPaper, NewsPaperID, PublishedDate, Location, SubmitSrc, ObitText, ObitTextLength, ObitWords, ObitType, PublisherID, PublishedDatesText,

DeathDate, BirthDate, ImageName1, ImageName2, ImageName3, ImageName4, FuneralHome, FuneralURL, CharityURL, ReviewedBy, UpdComplete

FROM obituary.obit

ORDER BY PublishedDate, NewsPaperID, NameLast, NameGiven

[peterlaursen]

This is a server problem (all 4-digit error messages starting with “1” are) .  The same error would occur with any client executing the same statement.

Please check this google-result:

http://www.google.dk/search?client=opera&rls=da&q=Error+Code+:+1206+The+total+number+of+locks+exceeds+the+lock+table+size&sourceid=opera&ie=utf-8&oe=utf-8

.. if you find something relevant.  

I suggest that you try to

1) execute UNLOCK TABLES

2) (or simpler) restart the server

3) and if server is a version affected by one of the bugs reported in above search upgrade the server. BTW: what is the server version?

[Meservy]

Thank you that solution worked wonderfully. . . . Appreciate your help.

'peterlaursen' wrote on '19:

This is a server problem (all 4-digit error messages starting with “1” are) .  The same error would occur with any client executing the same statement.

Please check this google-result:

http://www.google.dk/search?client=opera&rls=da&q=Error+Code+:+1206+The+total+number+of+locks+exceeds+the+lock+table+size&sourceid=opera&ie=utf-8&oe=utf-8

.. if you find something relevant.  

I suggest that you try to

1) execute UNLOCK TABLES

2) (or simpler) restart the server

3) and if server is a version affected by one of the bugs reported in above search upgrade the server. BTW: what is the server version?

[Author]

Posts