SQLite Forum


50 forum posts by user LarryBrasfield occurring on or before 2021-01-23 17:22:44.

More ↑
17:22 Reply: sqlite3_exec: the 3rd argument (artifact: edf011cc98 user: LarryBrasfield)

Your very long response does nothing absolutely nothing to help me search for a resolution.

I can see that. The "search" you intend must exclude using heap checking tools or changing your code to see whether use of simpler data types correlates with your problem. Good luck with that "search"; you will need it.

You did not answer Keith's question, "Are you attempting to 'hang onto a pointer' after your callback returns?"

I suggest you re-read the opening paragraph in my response.

I did read it and found a simple "No." followed by an elaboration of facts unrelated to the question. It was just as if you answered a different question.

When I call sqlite3_exec from my code, on which line in sqlite3.c does it land?

Your response does not help.

The line number might be 116761 if your SQLite version is 3.25. Does that help? Or do you expect some shoemaker's elf to provide a table with the line number you demand for every version of SQLite you might be using?

I provided guidance for finding that line in whatever version of sqlite3.c you happen to have. To say that does not help shows that your notion of what will be useful in your quest is extremely limited -- so limited that I doubt you will find it here or at stackoverflow.

On balance, since I am getting 3 records back with what I have suggests that the problem is more subtle

More subtle than what? More subtle than the effects of heap corruption?

To any experienced programmer, the fact that you see 3 (or 6 or 2) "successful" callback executions followed by an address fault when sqlite3_exec() is called suggests that the SQLite library code has been asked to use a corrupted heap, and that corruption has led to an address fault when the heap manager attempts to use the corrupted heap data structure. As many thousands of test cases show and many thousands of the library's users know, the library is pretty good about not corrupting the heap. Hence, it is reasonable to suspect that your code is corrupting the heap. Yet you are immune to doing the simple work needed to ascertain whether or not that is happening. Too much work, or beyond your ken, I suppose. Better to see if somebody else has a solution.

And I am not even thinking that it is anything to do with SQLite yet; that is why I've tried with several versions thereof.

Strange. Several versions of your failing code would be a better experiment. For example, if your callback, (which you have not revealed, even at the stackoverflow site where your plea for help was also made), does nothing with the data passed to it (by indirect reference), does the address fault still occur? Does it still occur if the callback makes only deep copies of the data? Is the heap intact across your sqlite3_exec() calls? Is it intact across execution of your callback? Inserting some diagnostics would be much more fruitful than trying different versions of sqlite3.c and hoping that matters.

Ryan has given you good advice for helping others to help you. And as Kees noted (and I confirm), you tried but failed to do that. Is this because you do not know what code is actually running when your callback is called? Or are you simply hopeful that with a few seconds more work posting that link that somebody is going to spot your bug?

Given what I see of your programming skill outside of the nice, managed execution environment that C# provides, I think you would be way ahead to use System.Data.SQLite and its SQLiteDataReader class to pull data from your SQLite database. Clearly, you are not yet knowledgeable enough about using C to be using the Native Code interfacing capability. (If you were, you would not be asking others to find a function definition for you in sqlite3.c .)

02:15 Reply: sqlite3_exec: the 3rd argument (artifact: e3d78a87bc user: LarryBrasfield)

Since my code works for 3 records, it is probably 'correct'.

That is nowhere close to true. However, it does suggest a debugging strategy. After each record, do a heap integrity check.

I must also submit that your definition of "works" is far too narrow. Maybe your code produces the results you expect 3 times, but it clearly causes some degradation of something, such that successive calls have a lower probability of satisfying even your weak kind of "works".

I've run out of options for debugging the reason for the error I am getting after 3 rows. Hopefully, someone can share their insight so I can solve this problem.

Well, I already suggested an approach. How did that work out? The result may very well show you that whether "it works" depends on something in your code.

You did not answer Keith's question, "Are you attempting to 'hang onto a pointer' after your callback returns?" This leads me to suspect you do not know what he means or why that programming sin should be among your chief suspects. (The values you cite in your not-quite-an-answer to him are not literals.)

When I call sqlite3_exec from my code, on which line in sqlite3.c does it land?

If you can find "sqlite3_exec(" in sqlite3.c, you will find instances of that text in 4 categories: (1) Inside of comments; (2) A forward declaration; (3) In other code calling into sqlite3_exec(); and (4) A definition of sqlite3_exec(...). When you call it, that last is where your call "lands".

I feel compelled to note that somebody who cannot look at sqlite3.c to find a function definition is unlikely to be able, absent extremely compelling evidence, to correctly diagnose that a bug lies outside his own code and hence must be in somebody else's code. The sqlite3_exec() API is used successfully by many thousands of SQLite library users, and the whole library API is tested extensively for every release and intervening code drop. The overwhelming likelihood is that your callback is doing something that cannot work reliably. If it passes around soon-to-be-stale pointers to dynamically allocated memory, to be used after they are in fact stale, (such as Keith and I suspect leading to our suggestions), that is what you must stop doing.

Your debugging effort only going to be hindered by your wish to absolve your own code. That is an attitude you would do better to shed. Your goal during debugging is to figure out what is going wrong, with enough detail that it is reasonable to decide what code is violating its (explicit or implicit) contract. Only then can you proclaim the location of bug. You are nowhere close to that point, and having counted loops before a crash is not a sign of being close.

17:41 Reply: dll file not found in 64 bit pc application developped in 32 bil pc (artifact: f96ca15d6e user: LarryBrasfield)

Visual Studio has been capable of targeting the 64-bit Intel/AMD CPU for years now. If you have that dev tool in a recent version, you just have to select that platform target. If your version cannot do that, either get a current version or use the GNU toolchain built to do that.

This is not really on-topic in this SQLite forum.

17:30 Reply: sqlite3_exec: the 3rd argument (artifact: 6325a6e45d user: LarryBrasfield)

You will generally need a debug build to conveniently debug code at the source level. But that is unlikely to be of much help because this is what you will find: The SQLite library is suffering an address fault because it has been given a trashed heap and is susceptible to the GIGO principle. The heap is almost certainly being taken from its pristine (not-garbage) initial condition to a trashed condition by effects flowing from your code or possibly other portions of the application using the SQLite library.

I suggest you get your callback scheme working with some simple data type that does not require heap allocation (such as floats or integers), and only once you have that working make the transition to passing strings or blobs through your native-mode / coddled-execution interface. There is far less opportunity to be using bad pointers with the simple data types.

18:46 Reply: sqlite3 .shell - conditional executions with `[[ expr ]]` constructs (artifact: 1cdc4674b9 user: LarryBrasfield)

I have to say that Warren's suggestion makes more sense than what follows here. Nevertheless:

In the sqlite3 shell, this does what you apparently intend: .output /tmp/furd .print fin=file1.doc .print [[ \"${fin##.}\" = \"doc\" ]] .print echo \"$?\n${fin##.}\" .print echo \"$fin\" .output stdout .shell chmod +x /tmp/furd .shell /bin/bash -c /tmp/furd .shell rm /tmp/furd . Note that the backslashes are understood by the sqlite3 shell meta-command scanner to mean "Don't treat the next character specially." Without being escaped, the double-quotes are used to group text and then they vanish from the result. If I was not too lazy to try it, I might have found a way to do this without a temporary file, but it is too hard to see what is happening that way for my taste.

14:24 Reply: windows: tester.tcl question (artifact: 9ea6dcc338 user: LarryBrasfield)

I had suggested just using the shell command, 'pwd', without realizing that it is not a built-in and only works for me because a pwd.exe is locally available.

However, I believe a fine resolution is to replace the offending line with: string trim [exec -- $::env(ComSpec) /c CD ] , which works because the shell normally referred to by the ComSpec environment variable interprets a bare 'CD' command by outputting the current working directory. (IOW: delete the two '%' characters.)

03:08 Reply: sqlite3 .shell - conditional executions with `[[ expr ]]` constructs (artifact: 4e9ec254a9 user: LarryBrasfield)

After some digging it seems that "true" shell involved in sqlite3 .shell execution in my case is /bin/dash

If you had dug into shell.c, you would have found that the .shell meta-command merely defers to whatever the system() C library call does. That is likely to vary from one system to another for bare shell-like commands, but I dare say it is enough to let you specify that a particular shell be run if you simply name it as the first word in the arguments to .shell.

02:53 Reply: Reload WAL when modified by outside process (artifact: d17715d46a user: LarryBrasfield)

Hi, Ben.

It appears that you do recognize the value of relying only on the documented API behavior, so my worry over any fly-by-wire-(driven by a computer) airplane I might ride is considerably alleviated. I hope you appreciate that, from your original post, it appeared that you intended to go outside of such droll constraints, and had already tried. And I imagine now that you appreciate that some inexperienced developers may need to think about how try-it-and-see (and hope for the best) approaches are likely to play out. Please accept my apology for hastily putting you into that category.

Richard and the other members of the SQLite dev team are good about establishing, via documentation, what SQLite behavior can be relied upon. At the same time, they make substantial implementation changes to improve performance while holding to the documented API behavior and continuing to pass the extensive tests which help them catch inadvertent deviations from that behavior. It is for that latter reason that I considered your early failure to be fortunate, and thought you might also.

Good luck with that replication project. I have seen how challenging it can be to get such to work well and reliably. If your tool does that, I would be very tempted to use it (for a personal project) if there was sufficient reason to believe it was reliable. Getting replication to work across live instances would be great, if it was reliable. Maybe a connection proxy, able to deal with the need for closing and reopening "real" connections, could be made to work.

Cheers, -Larry

23:22 Reply: Reload WAL when modified by outside process (artifact: fda7f7332d user: LarryBrasfield)

You are treading on extremely thin ice if you do what you claim to want.

I advise using the VACUUM INTO filename statement (in "SQL") or the online backup API to get your copy made, and forget about trying to 2nd guess how things are working under the covers.

I would also advise you to consider yourself lucky that your doomed-to-fail (eventually) initial copying attempt failed early. And, going even further, I hope you appreciate that "try it and see what happens" is a perilous approach to writing software. I hope you learn not to do that before ever working on flight software, banking software, any software I use, or nuclear armed missile launch software.

17:47 Edit reply: Windows x64 SQLite extensions... (artifact: 006edfdc95 user: LarryBrasfield)

The mingw-w64 gcc builds are readily obtained for the modern Windows platform. The MingW-W64-builds builds are easy to install and work well for building SQLite and extensions for it. You don't need Visual Studio. Even if you wish to use the Microsoft compiler, that can be obtained with the DDK. (device development kit)

Building it yourself is far more secure than resorting to using DLL's you might find on the "internet". It's also easy to do, as you can see at the link.

17:45 Reply: Windows x64 SQLite extensions... (artifact: 6c5eea3698 user: LarryBrasfield)

The mingw-w64 gcc builds are readily obtained for the modern Windows platform. The MingW-W64-builds builds are easy to install and work well for building SQLite and extensions for it. You don't need Visual Studio. Even if you wish to use the Microsoft compiler, that can be obtained with the DDK. (device development kit)

Building it yourself is far more secure than resorting to using DLL's you might find on the "internet". It's also easy to do, as you can see at the link.

20:28 Delete reply: windows: tester.tcl question (artifact: b15196b92c user: LarryBrasfield)
20:27 Reply: windows: tester.tcl question (artifact: 41c0d8264c user: LarryBrasfield)

I have not yet sorted out why this does not work on Windows with the MagicSplat TCL distribution. However, this does work: string trim [exec -- $::env(ComSpec) /c pwd ] , and I believe it does what was intended for the less lucky incantation.

15:55 Reply: How to Load SQLite db file from IsolatedStorage Location (artifact: 2390b77688 user: LarryBrasfield)

Because "Isolated Storage" has the same semantics as other parts of the filesystem, and is accessed using the same API's, it ought to work. Why don't you try it and see?

15:45 Reply: Error in SQLite 'all VALUES must have the same number of terms' (artifact: cc9931e0eb user: LarryBrasfield)

create_invoices = """ INSERT INTO invoices (invoice_no, date, customer_name, gstin, product, hsn, uom, quantity, rate, amount, supplier) VALUES (?,?),('list') """

Ignoring quite a bit of information not relevant to the error shown in your thread title, I say: Your INSERT statement claims that 11 columns are to given values, (by naming them in the first parenthesized list), then provide only a set of 2 values ((?,?)) and set of 1 value (('list')). For that INSERT statement to be semantically valid, the value lists would each need to have 11 members. Because it is not semantically valid, the statement preparation phase rejects that SQL with the error you see.

02:39 Reply: What is the best way to have a custom order? (artifact: b22dad8111 user: LarryBrasfield)

With the linked list, it is only necessary to adjust the prior or subsequent neighboring nodes' links. For a singly-linked list, all nodes which continue to have the same successor remain untouched after an insertion or deletion.

14:46 Reply: What is the best way to have a custom order? (artifact: 275b915dca user: LarryBrasfield)

I have found, in circumstances where I needed a field just for ordering, that using a floating point value eliminates (or postpones for a long time) the need to mess with rows for which the ordering is not changing. Until those values have quite a few decimal places, it is always possible to find a value between a pair of (differing) neighboring values.

14:41 Reply: 2 queries asking the same, give different answers (artifact: 953814db0f user: LarryBrasfield)

I tried your suggestion, but the query gives no result at all. I don't know what that can mean.

For your query [a], it means that the 2nd (EXCEPT ...) subquery returns the same set as, or a superset of, the 1st subquery.

[a. SELECT MAX("fld0"), fld0 FROM table EXCEPT SELECT MAX("fld0"), fld0 FROM table where ("fld1" = 'val1' or "fld1" = 'val2' or "fld1" = 'val3'); ]

Sometimes, a good way to debug a complex query is to break it down into its component subqueries, run those, and see what their individual result sets are.

14:30 Reply: sqlite3_exec: the 3rd argument (artifact: f49a8e33f5 user: LarryBrasfield)

The callback function is executed as many times as there are rowsin [sic] the result. Correct?

That's what the docs for sqlite3_exec() claim, and I have found it to be true.

The callback function returns the column names every time it is called? Correct? If affirmative, isn't this superfluous?

Yes, and no. For some applications, it is a great convenience that the column names are available to the callback.

How does the callback function handle the different types in the result columns? (Int, Float etc)?

That would be up to the library user. The sqlite3_column_type() function is likely to be useful if the column types are not known or thought to be coerceable.

My callback function consistently fails on the second call: any ideas on how to overcome this? (I'm using C#)

It is time to learn how to use a debugger. Also, interfaces between the .Net CLR execution context and native code are generally non-trivial. More study is indicated. My guess is that you have botched a memory ownership issue. However, this subtopic is truly off-topic in this forum.

14:09 Reply: "headers on" yet no headers (artifact: bf45ef2790 user: LarryBrasfield)

The behavior you would prefer is my preference also. But the CLI shell does not work that way. The changes necessary to make do so are not well localized in the code. A callback scheme is used to emit the data, and some of them emit a header also for the first row. But with an empty result set, nothing is emitted.

Several years ago, I suggested changes to "fix" this, without visible effect.

21:10 Reply: Can't migrate auto-increment bigint columns (artifact: da4b137aed user: LarryBrasfield)

Since the DDL is created by EFCore at runtime as part of a migration, it's not really up to us. ...

When I advised, "Say 'integer' rather than 'bigint'", I was not referring to the generator of that DDL. You had said, "I work on a database that has several bigint (Int64) columns as Ids." I am sure that is where that troublesome 'bigint' type arose, merely replicated by the EFCore. In SQLite, the column type incantation for an up-to-64-bit integer that serves as a primary key and the rowid is 'INTEGER PRIMARY KEY'. If that went into your migration, you would not be getting that problematic column definition for your "ef_temp_Thing" table's ID column.

19:17 Reply: How to replace SQLite DB in WAL-mode ? (artifact: 2d204386b6 user: LarryBrasfield)

The backup API would circumvent your race concern. Of course, due to the same uncertainties leading to that concern, there would still be a question as to what version of the database would be replaced by the created backup.

16:29 Reply: Can't migrate auto-increment bigint columns (artifact: 406bad1391 user: LarryBrasfield)

Two solutions, either of which will (likely) work:

  1. Say 'integer' rather than 'bigint'. You will still get an integer primary key up to 64 bits (as needed for its actual value.)

  2. Do not say 'AUTOINCREMENT'. That is useless except in special cases which yours is probably not. Unless you require strictly ascending key values or must permanently retire key values, AUTOINCREMENT can be eliminated without adversely impacting the automatic selection of new key values.

15:14 Reply: Issues with attached databases handle from custom Android SQLite package (artifact: 305498ae44 user: LarryBrasfield)

This appears to be a duplicate of Using android AAR with modified sqlite_open_v2() - attach gives problems? If so, it would be a courtesy for you and your cohort to consolidate your inquiry and clues into a single thread rather than creating scatter-shot threads here. I suggest you add your additional clues posted above to that other thread instead of encouraging a pair of parallel discussions.

14:00 Reply: Using android AAR with modified sqlite_open_v2() - attach gives problems (artifact: 1e8d4507a5 user: LarryBrasfield)

Sorry, but this has to be said:

Your evidence clearly implies that some code of your own creation is causing the problem. Yet you give only the barest hint as to what the code is. [a] I could offer a zillion guesses as to what is wrong with that code if I could type faster and was immortal. That would be a waste of my time, other readers' time, and your time. So, I await a better set of clues as to what went wrong.

[a. The subset of "code" that uses the callback technique to do "things" is quite large, undoubtedly super-astronomically large. ]

Another point: It is considered rude to repost the same inquiry under different thread titles. If, after a period of time, no reply appears in your single thread, the better approach is to reply to your own post. You might even use that opportunity to add some details that would improve the prospects of a useful reply. However, 0.4 hours is too short a time to do either.

18:33 Reply: Multi-threading sluggishess on Mac? (artifact: 3b10da2bfb user: LarryBrasfield)

It causes compilation of code that can do very short sleeps instead of the longer ones available on virtually all Unixen.

00:20 Reply: SQLite .NET: CommandBuilder performs really bad on tables with a lot of indexes (artifact: 7217dbde29 user: LarryBrasfield)

FWIW, I agree with Warren's comments to your post 3. I would not be so quick to dismiss schema (and corresponding data) restructuring to solve a performance issue. After all, all those "mumble###" columns are bound to be highly similar to each other and subject to a readily automated disassociation into another table. You've probably seen the old saw about a patient telling his doctor that it hurts when he does X, then being advised to stop doing X.

Getting back to your narrowly defined problem: There is clearly no actual querying, updating, inserting or deleting going on in that timed stretch of VB code. If (as you argue) the calls are reaching the SQLite.NET library, I would think that the most work being done then would be for the adapter layer to be creating some prepared statements. If you could just post here the SQL that goes into that process, (as verbose as it will be), that SQL could at least be submitted to the SQLite CLI shell so folks here can do something beyond gaze at VB.NET code and wonder if anybody really expects somebody to get Visual Studio and VB.NET installed just for that purpose. (We see very little VB code here, and I expect familiarity with it is lacking here. The only reason I can read it is past experience; I never install VB.NET because C# is such a clearly superior way of targeting the .NET CLR.)

I think that once somebody determines that, yes, hundreds of columns bog down statement preparation, the response is likely to be that a schema change is needed. I doubt that the code asked to deal with hundreds of columns and a multitude of indices is going to be sped up soon, and probably never will be if that would slow down more reasonable use cases or require more memory for them. I am highly skeptical that getting to a more reasonable schema would be all that difficult for whoever owns it, (unless they are mentally challenged.) Of course, if it is something set by a separate corporate entity, (or even an IT group), it may be cast in concrete for you. (In that case, I hope I do not own any of the company's stock.)

23:26 Reply: ODBC 64bit drivers for windows download (artifact: d34c150422 user: LarryBrasfield)

I guess I can see how some confusion might have arisen here. The OP did mention that he had installed SQLiteStudio, although that has little to do with getting an ODBC driver for SQLite installed or used. So, maybe, that means the OP desires another GUI-driven application to access his SQLite DB(s). If so, saying so would be an improvement.

Your extension is clearly an ODBC client, something which uses the ODBC API and conventions to reach and call into a connection-string-selected ODBC driver. But what the OP has clearly and specifically requested is a "suggestion to download and install odbc drivers to connect to SQLite tables", after mentioning one such driver in particular.

As presented, the OP's problem is that the driver he tried to install failed to install, as shown by a specific error message, which error the OP hopes to alleviate. Perhaps that problem has been solved. Until we see more from the OP, there is not much more to be done for that problem than I suggested earlier.

21:50 Reply: ODBC 64bit drivers for windows download (artifact: 5b11dcdde9 user: LarryBrasfield)

How will your app help the OP to access a SQLite database via ODBC? As far as I can tell, your suggestion is totally unrelated to this thread except for the appearance of the acronym, 'ODBC'.

19:24 Reply: ODBC 64bit drivers for windows download (artifact: b61fc1fcbd user: LarryBrasfield)

I will pretend that your "I" refers to the sqliteodbc_w64.exe process as you ran it and granted its requested privilege elevation, and that it could then either create its installation directory or alter its content. (If that pretense is false, you have successfully dodged my question and avoided the likely useful implications of a direct answer to it.)

I had no trouble whatsoever running Christian Werner's sqliteodbc_w64.exe program a few minutes ago, and it created the said directory and left a bunch of files there, including the same sqlite3odbc.dll which, apparently, could not be written during your installation attempt. This leads me to think you have some unique, local problem. Perhaps you are already running some process that has loaded that DLL, causing it to not be overwritable. If that is the case, you can either find and stop or kill that process, or rename the DLL to something like "delete_me_soon" and retry your installation.

I do not know what "the correct ODBC drivers" are for SQLite, but I can attest that I have found Mr. Werner's driver to be useful over the years since he began publishing it.

18:51 Reply: SQLite .NET: CommandBuilder performs really bad on tables with a lot of indexes (artifact: a79b1dfdad user: LarryBrasfield)

What makes you think that the stretches of code between your timer initialization and various reads of the timer are doing anything whatsoever in the SQLite library or accessing your database? I see nothing, in either your code or the docs for the SQLite.SQLiteDataAdapter class, to make me believe the SQLite library code is even reached during the time those stretches execute.

I must add something else, (just to save some time reaching this issue, which will surely come up): When you design a table with nearly a thousand columns and gobs of indices, do you truly expect to find the DBMS to have been optimized in any way for such an unusual table structure? My expectation would be to find that some name searches are slow for such a use case because the more voluminous code needed for large hash or btree symbol tables is not worth the execution time or code space in the much more common case where tables represent real world relations. To be blunt: Your 900+ column table represents poor schema design.

16:57 Reply: ODBC 64bit drivers for windows download (artifact: ea9f15ac6f user: LarryBrasfield)

Are you running the .exe with enough privilege to create or write in the "C:\Program Files\SQLite ODBC driver for Win64" directory?

16:04 Reply: SELECT question (artifact: 51070d05af user: LarryBrasfield)

(Without diminishing Keith's suggestions:) The plethora of solution approaches is more due to the power of specifying set relationships via queries than SQLite's virtue alone. SQLite, among other DBMS's, accepts SQL for specifying the result sets of query operations, and compiles it to create a program targeting its own dedicated virtual machine which is specialized for querying and modifying the connected or attached DB(s). The flexibility you see arises from the flexibility of (and some redundancy in) SQL, combined with skilled use of it.

00:36 Reply: Nit newly exposed in "How to Compile" (artifact: 31ccf4430a user: LarryBrasfield)

To the extent that compile-and-link example is documentation, your "-lm" addition is part of what I have suggested. The rest is to add a preprocessor definition to also compile the recently added math functions (-DSQLITE_ENABLE_MATH_FUNCTIONS=1), better reflecting the advertised "command to compile a full-featured shell".

03:54 Reply: How to distinguish fatal errors (artifact: 1447c71d56 user: LarryBrasfield)

At the default page size of 2^12 bytes and default page count limit of 2^30 bytes, a database has to reach 2^42 bytes (over 4 Terabytes) to hit your page limit. Yours is a very unusual application if that limit is a legitimate concern. And both of those defaults can be increased.

Your use case, which I can only guess at from your pseudo-code, is most unusual. If the database does get too big for its self-imposed limit, any portion of the query sequence which includes DML, (which I presume it can although your code provides for no parameter substitution), or even DDL, can just fail, doing nothing except to note the failure. I would never have guessed this. And given that such failure is to be accepted, I am still left wondering what "reliably" means.

Your case where the temp store reaches a full condition is strange as well. That is either like a plain "disk is full" (if the temp store is on the same filesystem as the database) or it is something you might alleviate for some types of temp store backing devices. Yet you want it treated like the improbable page limited case, with submitted SQL statements having no effect on the database.

I think you have presented a question of "How do I make my preconceived solution work?" rather than "How do I solve this problem: ...?" My imagination is too limited to devise an actual, real world use case for which your pseudo-code is a solution. For that reason, I feel unqualified to offer advice or suggestions.

22:35 Reply: How to distinguish fatal errors (artifact: c5611cedc0 user: LarryBrasfield)

For "filesystem nearly full" situations, where there are potentially other processes consuming and freeing space on the underlying device(s), you will have the same uncertainty regarding subsequent retries whether or not you are able to get an error code more specific than SQLITE_FULL. Using the OS calls to determine available free space is not going to change that.

In the thread you cite, (and your original post in this thread, I suppose), the term "recoverable" is used differently, and more restrictively, than I interpreted it. Of course there is going to be no recovery if that means succeeding with the same operation where nothing else changes.

Considering the disk full case(s):

As was mentioned in that other thread, recovery will normally require freeing some disk space, something that your program is not likely able to do in a desirable manner. It will need to give up or pause while some more knowledgeable person or program deals with and perhaps alleviates the problem.

Considering the page count limit reached case:

I think that if you have set the page count limit lower than its maximum, with the intention of using that as trip-wire to do some sort of cleanup likely to reduce used page count, then you need not worry about precisely how much larger the page count can get relative to need. Recovery will be to do whatever cleanup of log data, materialized views, caches, and etc. that will get the page count back down to a "usable" level. Even if this is done when additional pages were low and that was not the direct cause of the SQLITE_FULL problem, it will free up disk space also which has a good chance of alleviating the error.

I am somewhat puzzled by your problem at this point. I think some real (or imagined likely) use cases would bring this discussion to a more concrete basis. Your phrase, "reliable way to handle this issue", leads me to wonder what "reliable" can possibly mean in this context. It begs for a clearer definition.

20:36 Post: Nit newly exposed in "How to Compile" (artifact: 0b8ae95a2e user: LarryBrasfield)

At Compiling The Command-Line Interface, the example given as a "command to compile a full-featured shell" does not quite work because there is a call to the log() function within the fts5Bm25GetData() function which creates an unsatisfied reference at link time. I propose this change: gcc -Os -I. -DSQLITE_THREADSAFE=0 -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS5 \ -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_EXPLAIN_COMMENTS \ -DHAVE_USLEEP -DSQLITE_ENABLE_MATH_FUNCTIONS=1 -DHAVE_READLINE=1 \ shell.c sqlite3.c -ldl -lreadline -lncurses -lm -o sqlite3 which builds a CLI shell closer to "full-featured" and cures the missing math library problem.

19:38 Edit reply: sqlite 3.34 Command-line editor (artifact: e58c070cd3 user: LarryBrasfield)

change that to -DHAVE_READLINE=1 .

Also, per the example I cited, add a -lreadline and a -lncurses to the gcc invocation.

19:36 Edit reply: sqlite 3.34 Command-line editor (artifact: 3311be15ad user: LarryBrasfield)

change that to -DHAVE_READLINE=1 .

Also, per the example I cited, add a -lreadline to the gcc invocation.

19:35 Reply: sqlite 3.34 Command-line editor (artifact: 0b886c8694 user: LarryBrasfield)

change that to -DHAVE_READLINE=1 .

19:09 Reply: sqlite 3.34 Command-line editor (artifact: f9d2a453e5 user: LarryBrasfield)

It's not hard to build the shell from the amalgamation. Tips are here, including an example that includes and uses the readline library.

18:55 Reply: How to distinguish fatal errors (artifact: 1d1e8f2e1c user: LarryBrasfield)

It would be a bug if any of the SQLITE_FULL errors were not "recoverable" in the sense that the database continues to have the ACID attributes. Clearing the underlying resource shortfalls should allow the database to be used per its basic API. Not clearing them might lead to success (depending on other system activity) or the same "recoverable" error.

A separate question is how to distinguish among the error's causes. Most operating systems provide a way to discover free space on filesystems, which should allow you to distinguish between cause b and the others. And cause b can be ascertained by using the max_page_count pragma and page_count pragma judiciously. (This latter approach will need to take into account how many pages your failed insert or update was going to use.)

17:34 Reply: sqlite 3.34 Command-line editor (artifact: 492d4f6f16 user: LarryBrasfield)

One solution is to build the shell with HAVE_READLINE preprocessor symbol set to 1, and link with the readline library.

Another solution would be to use control-left_arrow and control-right_arrow instead of the Alt qualified keys. (After all, the Mac has no Alt key, right?)

16:31 Reply: need help!! invalid class typecast (artifact: 46f71ce3ef user: LarryBrasfield)

The TBlobField class represents a variably sized binary field. This is distinctly different from a blob in the database which, once created, has a fixed size. Having fixed size data is one attribute of the TBytesField class. So that's one conceptual problem you need to deal with.

Dealing with it will involve using the CBuilder/Delphi/Lazarus infrastructure already existing to deal with blobs in databases rather than crudely trying to alter the object type in-place.

Another tip: You would do well to learn to use static_cast (or dynamic_cast) to perform compatible type conversions. This is to be contrasted with reinterpret_cast, which is what your C-style cast becomes when there is no meaningful static_cast for the object whose type you want change. It is very rare that a reinterpret_cast is appropriate for pointers to class instances. Hence, your C-style cast is the moral equivalent of wearing a "Kick me!" sign. This is particularly true where you rely on compile-time type checking for program correctness rather than careful study and understanding of library docs. The C-style cast is shorthand for "I know exactly what I'm doing here, so just do what I say." It is a way to generate runtime problems when you do not know exactly what you are doing. The static_cast leaves the compiler an opportunity to respond, "Sorry, that is not sensible."

15:10 Reply: How to cancatenate all lines in table to a single string? (artifact: 7c6fff17d0 user: LarryBrasfield)

Your warning is technically correct. I have found that the ordering of group members to be concatenated can be determined, even while understanding that the means for doing so are outside the set theory basis of the query language. It would be comforting to see an order guarantee for the results of "order by ..." clauses in inner queries.

12:33 Reply: sector flush atomic (artifact: de3283bf8a user: LarryBrasfield)

While it is unclear what you mean by "critical error happens", I can say that you are overlooking critical aspects of block storage devices. One aspect is that at the smallest block level, writes are effectively of whole blocks at once. For non-volatile memory, this is because the block's group of bytes are set to the new value in a single operation. For spinning (or sliding) magnetic media, the group is concatenated with an error check code (sometimes called "CRC") such that the sequence of bits constituting the block will be read as erroneous unless the whole block with its check code is written. Another aspect is that "power loss" is not ordinarily an instantaneous event. (A sledge hammer crashing down upon a circuit board could be an extraordinary event producing an effectively instantaneous loss of power to critical circuits.) Ordinarily, after line power ceases or a battery becomes low enough to indicate imminent power loss, a system has a certain amount of stored energy downstream of the "lost" power source, and that stored energy is sufficient to complete one of those block write operations. (And, for spinning disc drives, there is enough to also move the read/write heads to a place where they will not scrape the magnetic medium as the disc slows to a stop.)

Hence, your worry about writes being abruptly terminated at the byte-by-byte level is overwrought, provided sledge hammers (or equivalents) are not involved.

22:36 Reply: SQLite3 for Teensy 4.1 - dirent.h (artifact: d389b31a7a user: LarryBrasfield)

The test_onefile.c VFS provides the barest VFS, without locking support, just like unix-none. I cannot tell from your initial post (or any other) whether your use cases include multi-threaded or possibly reentrant access. Neither starting point does anything with locking calls, which dooms multi-threaded use cases unless you arrange your own locking outside of the library. For mere file I/O, both are trivially easy to adapt for a different set of underlying APIs, so I think your concern with "easier" may be misplaced.

I would be concerned also with write and rewrite performance. SD storage devices can fail quickly if the system using them keeps rewriting the same block a lot. (I speak as a student of the hard knocks school on this.) You might, depending on the application, do well keep the DB in fast, durable memory and use a cache flushing strategy to reduce wear on the SD memory. That would also largely bypass the speed issues with SD memory devices.

16:28 Edit reply: Bug: MSVC uses intrinsic versions of ceil and floor for floating-point modes precise and fast (diagnosed) (artifact: e4fe8c6ec7 user: LarryBrasfield)

Early today, Richard fixed this problem. Today's pre-release download includes it.

I took a look at the assembler code generated by the VS2019 C compiler [a] for the two wrapper functions added to make addressable code for ceil and floor. Such code is in the math library, (for use when code generation does use the intrinsic forms), reached from those wrappers via a lone jump instruction.

[a. That would be "Microsoft (R) C/C++ Optimizing Compiler Version 19.28.29335 for x64", Keith. ]

16:11 Reply: Bug: MSVC uses intrinsic versions of ceil and floor for floating-point modes precise and fast (diagnosed) (artifact: 73da8b7185 user: LarryBrasfield)

Early today, Richard fixed this problem. Today's pre-release download includes it.

01:02 Edit reply: Bug: MSVC uses intrinsic versions of ceil and floor for floating-point modes precise and fast (diagnosed) (artifact: e207f28f6f user: LarryBrasfield)

The "bug tracker" is not visible outside of the SQLite dev group. Bugs are reported in this forum. Although Richard Hipp has already noticed this thread, it may still be worthwhile for you to edit the thread title to something like: "Bug: ceil and floor function addresses cannot be taken in MSVC (diagnosed)" (I suggest you do it because you, as the thread originator, are privileged to do so.)

I doubt the SQLite developers will regard this as a non-issue. The Microsoft C compiler has been extensively catered to already. This is just more of the same.

More ↓