First thing's first!

So all I'm trying to do is connect to Hive and add raw block data into my database, and it is surprisingly more difficult than one would expect!

First off, I tried storing the data using MySQL's JSON variable... because after all it is already a JSON to begin with. However, this kept giving me an error due to weird formatting issues. Because the string that contains the JSON block is massive... it's hard to see what the problem is. Said I was missing a comma or a bracket somewhere, and their are like over a hundred commas and brackets in every block.

I "fixed" this issue by avoiding the MySQL JSON variable altogether and started using MEDIUMTEXT instead. After all, the entire point of JSON is that it's just a string that you can parse back into an object later after you take it out of storage.

"Fun" fact about MySQL text objects:

Since TEXT objects are not stored in the server’s memory, they require data overhead for retrieval.

This is actually preferable because I'm only trying to store this data as a backup in case I need it later. I wouldn't want it clogging up ram waiting to be accessed for no reason.

So I've got some actual real-life blocks in there.

That's cool!

What's not cool are the random errors!

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'm sure they'd post.\n\nWe need content, but I know that the buzz guys are workin' at line 1

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's merit. I don't know her but thanks to you guys here on hive I can see her in a' at line 1

I assumed this bug was due to people simply posting the ' character to the blockchain. It was confusing MySQL into not knowing where the string ended. Normally in this case you'd need to "escape" the character... confirmed on https://stackoverflow.com/

The apostrophe, or single quote, is a special character in SQL that specifies the beginning and end of string data. This means that to use it as part of your literal string data you need to escape the special character. With a single quote this is typically accomplished by doubling your quote. (Two single quote characters, not double-quote instead of a single quote.)

Normally you escape characters with a backslash \ but in this case you just double up on the single quote... weird. I'm sure there's a reason... but I don't care :D

What I do care about is how to fix the problem... if I go around escaping random characters from the data I would hope to avoid accidentally adding random data to a block that shouldn't be there. Eh whatever I'll figure it out.

Bug #3

Error: ER_TRUNCATED_WRONG_VALUE_FOR_FIELD: Incorrect string value: '\xF0\x9F\x98\x8D\xF0\x9F...' for column 'block_text' at row 1

I imagine this is due to some characters being outside utf8...

Damn Korean characters! DAMN! :D

I set my database up to process UTF-8 characters and I'm fairly certain all those Korean posts you see out there do not fall under this standard. Guess I'll have to change it. Anyone know what a full node (Condenser) is using?

Conclusion

In programming, even the most basic thing often has unexpected issues. In this case I thought adding raw block data to my database tables would be one of the simplest things ever. Clearly, it isn't. At this point I'm wasting my time because this data is only meant as a backup so I wouldn't have to ask a full node for the same information twice. I'm technically not even going to use it unless I need the raw data for some reason, and even if I did I could just ask a full node to give it to me again. Sometimes it pays to be inefficient, just like blockchain technology itself. Live and learn.

Return from ERROR! to edicted's Web3 Blog