Skip to content

TEXT values show as BLOB #1731

Closed
Closed

Description

@ap-Codkelden

Details for the issue

What did you do?

Just had compiled 3.11 from sources, opened my database and execute an query.

What did you expect to see?

image

Values in the res_text field actually is text and have a TEXT type accordingly. With previous version 3.10 looks as shown above.

What did you see instead?

But with 3.11 I see BLOB in the for some values, both in the Browse Data tab and in the Execute SQL tab result pane. SQLite returns typeof() as 'text'.

image

Useful extra information

A lot of res_text values in my database starts with a newline symbol \n. First screenshot in What did you expect to see? section was made in 3.11 too, but after manually removing leading newline \n symbol from res_text for values with doc_id 59867564 and 67412037. Trim with query ...set res_text=trim(res_text) has no effect.

Also I made a sample of my data as SQL dump, which I upload to Pastebin.

What operating system are you using?

  • Windows: ( version: ___ )
  • Linux: ( distro: Arch Linux )
  • Mac OS: ( version: ___ )
  • Other: ___

What is your DB4S version?

  • 3.11.0
  • 3.11.0-alpha1 or 3.11.0-beta*
  • 3.10.1
  • Other: ___

Did you also

It seems looks like #16: Long TEXT values show as (BLOB) since version 3.1.0,
but BLOB mark appears not depends on the length of text.

Regardless of all above, new version is much faster, thank you :)

Activity

  1. chrisjlocke commented on Feb 7, 2019

    @chrisjlocke
    Member

    I believe the first chunk of the field is looked at to determine whether its text or a blob - the actual fieldtype itself is not looked at. I assume its that which is mis-identifying your textfields.
    In the 'Edit Cell' pane, can you change from 'Blob' to 'text' ?

  2. ap-Codkelden commented on Feb 7, 2019

    @ap-Codkelden
    Author

    @chrisjlocke

    In the 'Edit Cell' pane, can you change from 'Blob' to 'text' ?

    Yes, I tried, but it has no effect («Текст» is for "Text").

    image

    Also as I mentioned above, bulk removing of \n with trim() has no effect too.

    UPD.:

    I assume its that which is mis-identifying your textfields.

    But 3.10 understand my textfields correctly—I downgrade to 3.10 and open the same database.

  3. mgrojo commented on Feb 7, 2019

    @mgrojo
    Member

    Out text detection test is failing for these cases. One case is the leading line-feed that you've discovered. Another row seems to have problems with these characters: № and » (row with Id 43046774). But copying only those two characters to a new cell does not raise the issue. Only copying the whole cell to another cell does, but as soon as you delete those two, the text in the cell is shown.

    The test consists of converting the first 512 characters to Unicode and then back to utf-8. If the conversion is losless, we regard the data as text. Either the Qt encoding has some bug, or that conversion cannot be done in Unicode for all cases.

    The responsible function is:

    bool isTextOnly(QByteArray data, const QString& encoding, bool quickTest)

  4. mgrojo commented on Feb 8, 2019

    @mgrojo
    Member

    The automatic switching of the Cell editor to text means that the non-quick version of the test is working well. The problem is related to truncating the first 512 bytes. That is problably messing up the UTF-8 encoding for multibyte characters.

  5. added
    bugConfirmed bugs or reports that are very likely to be bugs.
    on Feb 8, 2019
  6. self-assigned this
    on Feb 9, 2019
  7. mgrojo commented on Feb 9, 2019

    @mgrojo
    Member

    @ap-Codkelden I have a solution but I'd like to test it for performance and for some encodings before commiting it. Since you have compiled from source, if you'd like to try, you could apply the attached patch and report whether it works for your whole database (the sample you upladed is working).

    issue_1731.patch.txt

    Another problem that can be seen in your screenshots is #1658. Could you report which Qt version have you used? It can be found in the About dialog.

  8. added a commit that references this issue on Feb 9, 2019
  9. mgrojo commented on Feb 9, 2019

    @mgrojo
    Member

    @ap-Codkelden The fix is already in the master branch, so you can simply pull it. According to callgrind, it is also quicker than the previous approach.

  10. added this to the 3.11.1 milestone on Feb 9, 2019
  11. ap-Codkelden commented on Feb 9, 2019

    @ap-Codkelden
    Author

    @mgrojo Thank you!

    I have apply your patch, it works perfectly for me. On my database with only 36.5K records I haven't seen any slowdowns.

    Regarding to #1658 I report that my Qt version is 5.12.1.

  12. mgrojo commented on Feb 9, 2019

    @mgrojo
    Member

    Thanks. We can close this issue, then.

    Regarding to #1658 I report that my Qt version is 5.12.1.

    That matches my expectation. Qt 5.12.0 and 5.12.1 have this problem.

  13. justinclift commented on Feb 9, 2019

    @justinclift
    Member

    @mgrojo Want to apply the commit to the v3.11.x branch too? 😄

  14. 6 remaining items

  15. mgrojo commented on Feb 28, 2019

    @mgrojo
    Member

    Hi, I have similar issue with @ap-Codkelden. I am a newbie to how to use GitHub so please bear with me. I want to report that my text column have English texts and texts that have symbols above the letters (It seems like a European language but not sure which one).

    That language is German and the product name is "Gummy bear, little gummy bear" 🐻 😄

    But the texts that have symbols above the letters are seen as BLOB in the cells. I can see the actual wording at the right small window of my DB Browser for SQLite. Here I attach the screenshot what I meant.

    This is working as expected, because the character encoding is not the one expected by the application (UTF-8), but ISO-8859-1. The hex editor shows text in this encoding (because, contrary to UTF-8, all characters are one byte wide, so it's better for showing the text representation of the hex-dump, which also uses byte boundaries).

    The way to tell DB4S which encoding to use is clicking with the right mouse button over the column header and choosing 'Set encoding'. In your case, select ISO-8859-1. You can find more details about how DB4S behaves regarding encoding in #1461 (comment)

    This is my email address singmin_92@hotmail.com. Don't hesitate to ask me more in details about the issues as I am not sure how to do proper issue reporting and solution. I would appreciate if you guys can guide me how to convert these BLOB texts to the actual words that have symbols in it. Thank you. :) By the way, I am rushing for this assignment completion and your help would mean a lot to me. @chrisjlocke @mgrojo @justinclift @Shouqun

    You'll receive a notification about the updates in this issue, since you've commented on it. Luck with your assignment.

  16. justinclift commented on Feb 28, 2019

    @justinclift
    Member

    Ahhh, the Bertelsmann Data Science Program. Looked through the course contents for that a while back... looks like mostly basic stats stuff. Is that how it's been so far? 😄

  17. singminhong commented on Mar 1, 2019

    @singminhong

    Thank you @mgrojo. :) I am able to change the encoding to ISO-8859-1 for a selected column header in Browse Data. But the results produced still give BLOB. Here I attach what I meant.
    bug_symboltextappearasblob_2
    Any idea how to change the output to show the exact text according to their own language?
    Please ignore the query as I am halfway coding and encountering some errors.

    Another question about the errors, do you guys know where is the proper platform to ask for answers and helps to check up my query code in SQLite? Is it StackOverflow or any other platform? I have an error to solve here: I could not get the max value of the last column DateG using MAX(ROW_NUMBER() ...). It says misuse of window function ROW_NUMBER().
    @chrisjlocke @mgrojo @justinclift @Shouqun
    @justinclift :( It is not easy for me when I am stuck at one place for a long time, especially SQL query. I did not have a strong foundation in that prior to the Program.

  18. justinclift commented on Mar 1, 2019

    @justinclift
    Member

    Another question about the errors, do you guys know where is the proper platform to ask for answers and helps to check up my query code in SQLite?

    Definitely the official SQLite Users mailing list:

        http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

    There are lots of people on there, including the SQLite developers themselves. They're pretty helpful. 😄

  19. justinclift commented on Mar 1, 2019

    @justinclift
    Member

    It is not easy for me ...

    Oops, that's not what I was meaning. Keep at it. 😄

  20. skerit commented on Jul 2, 2019

    @skerit

    I'm using 3.11.2 on Linux Manjaro, but I still have this issue.

    image

  21. chrisjlocke commented on Jul 2, 2019

    @chrisjlocke
    Member

    Can you supply the database at all? With all fields recognised as blobs, looks 'suspicious'. Are you using any funky language and/or any encoding?

  22. skerit commented on Jul 2, 2019

    @skerit

    Sure I can.
    It's a database made by Ultrastar Deluxe, it just contains a lot of scores for karaoke sessions :)
    It was made back in 2009, I believe.

    Ultrastar.zip

  23. mgrojo commented on Jul 2, 2019

    @mgrojo
    Member

    The data is null terminated. I'd say that is not a correctly encoded UTF-8 string. The workaround is to define a custom display format and use the quote function on the Artist and Title columns. This is precisely what the views scores_* are doing, so I guess someone has faced the same problem as us and created those views to display the data.

  24. skerit commented on Jul 2, 2019

    @skerit

    Yeah, I created those views a long time ago

  25. paul1149 commented on Jan 13, 2022

    @paul1149

    I'm having the same problem here.

    First, do I have the correct program? It's called DB Browser for SQLite, but it points to sqlitebrowser.org.

    I have database modules to a Bible program. I run a simple command on them, such as:

    update content set data = replace(data,'Open Sans Semibold', 'DejaVu Sans');

    And about 10% of the Topics are converted to BLOBs, unreadable by the Bible program. This mean a lot of data is being lost.

    I'm on MX Linux 19.4, built of Debian and with a KDE desktop. Version 3.11.2.
    DB Browser says:

    Built for x86_64-little_endian-lp64, running on x86_64
    Qt Version 5.11.3
    SQLite Version 3.33.0
    

    Thanks

  26. chrisjlocke commented on Jan 13, 2022

    @chrisjlocke
    Member

    update content set data = replace(data,'Open Sans Semibold', 'DejaVu Sans');

    Obviously without looking at the data or the result, that /looks/ OK, and I can't see why it would be turning it into BLOBs.
    Can you delete all the data in the table apart from a couple of rows that have the BLOBs and post that database? Any chance of a 'before' for those couple of rows too, so we could replicate it here on the same database? Rather than post it publicly, you could just email it to me - issue1731@chrisjlocke [dot] co.uk

  27. paul1149 commented on Jan 18, 2022

    @paul1149

    Thank you for this. I will create a small database, and then edit it until the problem manifests. I'm not sure when this will happen, but I won't forget.

  28. chrisjlocke commented on Jan 18, 2022

    @chrisjlocke
    Member

    Thanks for the email.
    The data has clearly been mangled.

    image

    This is simply RTF text, and should be human readable, albeit not easy to understand as it contains RTF control codes.
    The top line is on multiple lines and contains black diamonds, which is never a good sign. There are also no { characters like the lower line. This data has been corrupted and I don't see how the REPLACE command would have done this. This has occurred before this step.
    You mentioned in the email about a corrupted database, which you don't mention in your post above. I would suspect the database has been corrupted prior to your actions.
    Without looking at other parts of the database, I would suspect other fields would also be corrupted, so this database should be discarded. The data cannot be recovered or used with certainty for being valid.

  29. paul1149 commented on Jan 20, 2022

    @paul1149

    Thanks again, Chris. Just to follow up for the next guy, I realized that I could use the same csv sheet to rescue the lost data. It's a lot of work, though libreoffice macros help, but I'm saving over 99% of it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

bugConfirmed bugs or reports that are very likely to be bugs.

Type

No type

Projects

No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions