5

I'm trying to do a clean export-import of a malformed sqlite file.

Using info from techblog.dorogin.com/sqliteexception-datab..., this is what I did sqlite3 oldfile:

  1. .mode insert
  2. .output tempfile
  3. .dump

Then I created a new sqlite3 newfile:

  • .read tempfile

Error:

sqlite> .read tempfile
Error: near line 52330: Expression tree is too large (maximum depth 1000)
Error: near line 53097: Expression tree is too large (maximum depth 1000)
Error: near line 53427: Expression tree is too large (maximum depth 1000)
Error: near line 54013: Expression tree is too large (maximum depth 1000)
Error: near line 54014: Expression tree is too large (maximum depth 1000)
Error: near line 54047: Expression tree is too large (maximum depth 1000)
Error: near line 54048: Expression tree is too large (maximum depth 1000)
Error: near line 54227: Expression tree is too large (maximum depth 1000)
Error: near line 54294: Expression tree is too large (maximum depth 1000)
Error: near line 54373: Expression tree is too large (maximum depth 1000)
Error: near line 54374: Expression tree is too large (maximum depth 1000)
Error: near line 56688: Expression tree is too large (maximum depth 1000)
Error: near line 57950: Expression tree is too large (maximum depth 1000)
Error: near line 58015: Expression tree is too large (maximum depth 1000)
Error: near line 58077: Expression tree is too large (maximum depth 1000)
Error: near line 58246: Expression tree is too large (maximum depth 1000)
Error: near line 59795: Expression tree is too large (maximum depth 1000)
Error: near line 60439: Expression tree is too large (maximum depth 1000)
Error: near line 61501: Expression tree is too large (maximum depth 1000)
Error: near line 61523: Expression tree is too large (maximum depth 1000)
Error: near line 61811: Expression tree is too large (maximum depth 1000)
Error: near line 61824: Expression tree is too large (maximum depth 1000)

In the output file, my maximum line is 35737 chars.

How can I fix this error?

What are some solutions to do a clean export-import of a malformed sqlite file?

Pacerier
  • 86,231
  • 106
  • 366
  • 634
  • Have you googled that? Looks like it appears with more than 1000 conditions in one query – juergen d Mar 31 '17 at 16:07
  • @juergend, Yea, I need a solution. – Pacerier Mar 31 '17 at 16:08
  • Show one of the offending lines. – CL. Mar 31 '17 at 16:23
  • @CL., There're many, many lines. Sample line: `INSERT INTO NotesData_content VALUES(3981,'..redact..','=='||char(10)||'..redact..'||char(10)||'=='||char(10)||'..redact..'||char(10)||'=='||char(10)||'..redact..'||char(10)||` ................... `||'..redact..');` – Pacerier Mar 31 '17 at 16:29
  • If Google sent you here: The error means that your SELECT statement is too complex. This https://stackoverflow.com/questions/50390108/django-using-expression-tree-is-too-large-maximum-depth-1000 has a bit of explanation why that might have happened. – Chris Sep 30 '19 at 16:15
  • @Chris You mean it only depends on SELECT statement and not on database size? I tried to delete some rows and then the problem resolved. It seems that expression tree depth depends on some other parameters but I couldn't find any details. – mahdi Apr 14 '20 at 11:08
  • @mahdi the issue i have experienced does only depend on the SELECT statement. But if you compile that SELECT statement by looping over the records of your database, then the size of the SELECT statement will have a direct correlation with the number of elements - hence - the size of the database. Deleting records will decrease the number of elements to loop over. – Chris Apr 16 '20 at 11:01

1 Answers1

7

This is due to a change in version 3.18.0's sqlite3:

In the output of the ".dump" command in the CLI, quote newline and carriage-return characters using the char() function, so that they do not get eaten by end-of-line processing logic in the OS or in other command-line utilities and/or libraries.

If there are too many newline characters in a single string value, the resulting SQL expression becomes too complex.

This was fixed in version 3.19.0. If you are still using 3.18.0, you can work around this by converting the file to use raw newlines instead:

sed -e "s/'||char(10)||'/\\n/g" < tempfile > tempfile_with_newlines
CL.
  • 173,858
  • 17
  • 217
  • 259
  • Cool, did they just fixed it in 3.19 due to this thread report? – Pacerier Aug 07 '17 at 01:24
  • Yes; the bug report based on this was the first. – CL. Aug 07 '17 at 07:20
  • To clarify. The change in question changes the behavior of `.dump` to circumvent the issue of `|| char(10) ||` or `|| x'0a' ||` resulting in an expression tree that is too large. It doesn't fix the expression tree becoming too large. – CervEd Jul 29 '23 at 09:34