Comments (7)
You can reproduce a similar error following these steps:
- open DB Browser 3.13.0-rc1
- create new database
- run sql " PRAGMA encoding='UTF-16le'; "
- create a table
- close DB Browser
- double click the database just created
The problem seems to be the schema saved in UTF16. Please note that I can create database in UTF16 (le/be) with DB Browser but I can't read it if I close/reopen the file. If the database is created with another tool, I can open UTF-16le databases with DB Browser 3.12.2 but not with 3.13.0-rc1
from sqlitebrowser.
from sqlitebrowser.
Do you have the file open with different applications at the same time or do you see any other files with a similar name inside the directory? The message seems to indicate so.
from sqlitebrowser.
I tried on another computer with Windows 11 but the result is the same. I don't have any other file in folder. I don't have any other application running. This database (db2) has 1 table and 1 index. If you try to open it with 3.13.0-rc1 with the "Open Database" button, you don't have the alarm but tables and indexes are zero (0) instead if one (1).
If you open the database by double clicking on it, you see the alarm and have zero (as before) on table and indexes. The database is not empty.
from sqlitebrowser.
I can reproduce this using your database file.
I've never seen this error before.
Trying to attach it to a known-working database also generates an error.
Works OK in the command-line sqlite3 though. No data - just one 'users' table, is that right?
from sqlitebrowser.
Yes, it is right. The database belongs to a commercial application that use SQLite. I removed all tables and indexes to be sure that issue was not related to database data or foreign checks. I added 1 table 'users' only for test with no data. I had never seen the text encoding error: maybe the root cause is a database setting. I'm able to read the database data from C# with system.data.sqlite library (like sqlite3 command-line)
from sqlitebrowser.
DB4S can open UTF-16le (and UTF16-be) just fine, but it fails to create a new database with a non-default encoding.
Reproduction
- Create database using sqlite3 CLI:
- Run
sqlite3 cli.db "PRAGMA encoding='UTF-16le'; CREATE TABLE t ( i INTEGER );"
- Run
- Create database using DB4s
- New database:
db4s.db
- Close the table wizard
- Execute SQL: PRAGMA encoding='UTF-16le'; CREATE TABLE t ( i INTEGER );
- Close database
- New database:
- Both sqlite3 and DB4S can open
cli.db
(created by sqlite3) - Neither sqlite3 nor DB4S can open
db4s.db
(created by DB4S). sqlite3 sayserror: malformed database schema (t)
If you compare the contents of the two databases (using a hex editor) and lookup the byte offsets in the SQLite Docs, you will see that they are almost equal, except for these headers fields
cli.db |
db4s.db |
|
---|---|---|
The database text encoding. | 2 (utf-16le) | 1 (utf-8) |
File change counter. | 1 | 3 |
The schema cookie. | 1 | 3 |
The version-valid-for number. | 1 | 3 |
Both files contain UTF-16-encoded text, but the broken one claims that it is UTF-8. The increased file change counter indicates that DB4S has performed some extra operations on the file.
Cause
In DB4S, the DBBrowserDB::create()
function does not simply create a fresh database, but instead
- creates a new database
- creates a new table (
CREATE TABLE notempty (id integer primary key);
) - drops that table (
DROP TABLE notempty;
) - closes the database (so the file is actually written)
- open the database file again
BUT: Once you have created something in the SQLite database, you cannot change its encoding anymore.
Fix
Remove the hidden SQL operations from the creation function:
diff --git a/src/sqlitedb.cpp b/src/sqlitedb.cpp
index 37f1e6fe..6d297cc2 100644
--- a/src/sqlitedb.cpp
+++ b/src/sqlitedb.cpp
@@ -678,9 +677,0 @@ bool DBBrowserDB::create ( const QString & db)
- // force sqlite3 do write proper file header
- // if we don't create and drop the table we might end up
- // with a 0 byte file, if the user cancels the create table dialog
- {
- NoStructureUpdateChecks nup(*this);
- executeSQL("CREATE TABLE notempty (id integer primary key);", false, false);
- executeSQL("DROP TABLE notempty;", false, false);
- }
-
After that, DB4S will create exactly the same database as the sqlite3 command, allowing you to change the encoding, if you close the table creation dialog.
Obviously, this would re-introduce the issue of 0-byte files being created.
from sqlitebrowser.
Related Issues (20)
- [Feature]: Comment Line Shortcut key for "Execute SQL" tab and Shortcut Key selection screen HOT 3
- [Feature]: auto reopen last database HOT 9
- No data returned from SQL query HOT 2
- [Bug]: Can't run nightly build on macOS 12.7.4 HOT 7
- [Bug]: External drives cannot be opened on Ubuntu HOT 3
- [Bug]: Execute SQL Console overlaps text HOT 2
- [Feature]: Allow NOT operator '!' in front of filters
- [Bug]: Find and Replace fails to replace all finds HOT 1
- [Bug]: Copy/paste results of SQL select hangs / freeze program HOT 3
- [Feature]: Plot - change axis to log scale
- [Bug]: When you start the program, an error message appears. HOT 7
- [Bug] The "File -> Export -> Database to SQL File ..." doesn't export the values in the "sqlite_sequence" table HOT 2
- [Bug]: Program crashes when executing SQL query HOT 2
- [Bug]: crashed HOT 2
- [Bug]: Under specific conditions Filter can't keep the value
- Unicode text display mode for database cells
- [Bug]: The 'sqlean' library does not work on Intel CPUs HOT 2
- [Bug]: Unable to set custom date format in browser HOT 2
- [Bug]: Adding whitespace to query triggers error HOT 5
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sqlitebrowser.