HiveSQL is under (unexpected) maintenance

in HiveDevs11 months ago (edited)

This first of March 2022 will not have been a day like the others!

TLDR; The Hive blockchain has exceeded the number of 2,147,483,647 transactions!

For many of you, the above number may not represent anything special. But for computer scientists, it is the maximum positive value of a 32-bit signed integer.

And guess what, the blockchain hitting this limit caused HiveSQL to shut down and put itself into maintenance.

A bit of history

I designed the HiveSQL database almost 6 years ago. When doing so, I had no idea that we would reach such a number of transactions so quickly. For you (as for me at that time), this number seems quite inaccessible.

But that was without taking into account the recent dazzling success of Splinterlands and the growing number of applications that are developed on top of the Hive blockchain, which in itself is good news.

Several tables in the HiveSQL database store the various operations performed on the blockchain with the transaction number and block number in which they were included.

To store the transaction number, I chose to use a 32-bit integer (4 bytes). This means that storing the 2 billion transaction references consumes at least 8,590 GB of disk space. Add to that the indexes and foreign keys, you quickly find yourself with ~30GB required to store that data.

If I had chosen a 64-bit integer (8 bytes), thus allowing 9,223,372,036,854,775,807 transactions to be referenced, it would not only have required double the storage space, but it would also have had a negative impact on the performance of the database.

Remember that six years ago, high-performance, high-capacity NVMe drives were uncommon and extremely expensive.

The level of performance of HiveSQL, even when it is hammered by several thousand queries every day, has always been a concern of mine and that is what makes it successful today.

As always in these cases, choices had to be made!

Back to yesterday ...

You will no doubt have understood what happened yesterday: once the limit of the number of transactions was reached, the blockchain data injector simply stopped!

In itself, nothing too serious since it is designed to react in this way in the event of an error, in order to avoid injecting incorrect data and corrupting the integrity of the database.

Except that to be able to restart it, you have to modify the schema of several tables and that these sometimes contain several billion records.

I won't go into all the details but, although it seems very simple to do (just change some fields size from 32 bits to 64 bits), it is an extremely complex job that requires:

  • a lot of caution so as not to corrupt existing data,
  • total and exclusive access to the infrastructure resources,
  • additional resources, especially in terms of storage space (HiveSQL database uses over to 3TB of disk space),
  • a recovery solution and the certainty of being able to rollback if anything goes wrong,
  • a lot of time and patience!

HiveSQL in maintenance mode

This is why yesterday afternoon, as soon as I understood the reasons for the stoppage of the data injector and that I considered the actions to be taken to restart as soon as possible, I decided to put HiveSQL under (unplanned) maintenance and to block all access to the database, including read-only.

I didn't do it happily because I know how much some of you, analysts or application developers rely on the availability of this service, just like me.

Over the past 6 years, I have always been proud to be able to claim an availability rate of over 99%. The last maintenance that required downtime was more than a year and a half ago. Each time it happened, I made sure to minimize the effect and duration of the maintenance carried out.

When back?

I slept very little last night (barely two short hours). I take advantage of a waiting time during a long-running operation to write this post.

I apologize for this unexpected interruption and the late feedback on what is happening. But as already said above, choices have to be made and priorities set.

If all goes well, I hope that the services will be available again at the end of the day (no promise). It could be shorter or longer.

In any case, I will notify you as soon as this maintenance is over. Any communication and support related to this maintenance will be done on HiveSQL Discord Channel

Thank you for understanding.

PS: A few hours before reaching the fateful number of transactions that caused HiveSQL to stop, my Hive API node crashed! Is this called the law of series? As I said in the introduction: a f**** day like no other!

Check out my apps and services

Vote for me as a witness


2,147,483,647 transactions. I am impressed. As you said, this is not too complicated to fix but care has to be taken... Good luck...

PS: A few hours before reaching the fateful number of transactions that caused HiveSQL to stop, my Hive API node crashed! Is this called the law of series? As I said in the introduction: a f**** day like no other!

Let's see... The French president will speak tonight (you are based in France, aren't you?)... The day has still the possibility to get even worse...

2,147,483,647 transactions. I am impressed

And these are transactions only. Given that a transaction can contain multiple operations, it gives you an idea of how active our blockchain is. Maybe it's something I will have to compute once HiveSQL is back running.

you are based in France, aren't you?

No, I'm not ;)

The day has still the possibility to get even worse...

I have been mentioning yesterday, the 1st of March. That day is now over.
Hopefully today will stay better 🙈🙉🙊

Maybe it's something I will have to compute once HiveSQL is back running.

Please do so. That would be an interesting piece of information.


PS: I thought you were living close by. Nevermind ;)

Ah, that explains the outage. You are not the first person to get caught out like that. Numbers can get bigger than you anticipate and decisions need to be taken on what you store. I hope the Hive back end can cope with much higher numbers.

I do run some scripts that use HiveSQL, but they will just have to wait a while. No big problem.

Thanks for providing this service. It means a lot to us.


Greetings, @arcange, I hope you are well. I write you because every time I post, an unpleasant message automatically appeared from the user @ gangstalking denouncing an alleged technological terrorism from and I notice that it is appearing to a lot of users, in each post. Is there a way to block it or will we have to continue supporting it? He wrote this: "The people doing V2K want me to believe it is this lady @ battleaxe Investigate what she has been up to for 5 years. Its the next step to stopping this. Make her prove where she has been for 5 years or where she is now. She is involved deeply with @ fyrstikken and his group. Her discord is Battleaxe#1003. I cant say she is the one directly doing the v2k. Make her prove it. They have tried to kill me and are still trying to kill me. I bet nobody does anything at all. Make @ battleaxe prove it. I bet she wont. They want me to believe the v2k in me is being broadcasted from her location. @ battleaxe what is your location? "

 11 months ago Reveal Comment

Thank you for tirelessly working to get HiveSQL back up and running!

Thanks for the info!


PIZZA Holders sent $PIZZA tips in this post's comments:
@steevc(2/5) tipped @arcange (x1)

Please vote for pizza.witness!

Thanks for the update - not a simple task to migrate from 32bit

I can't even pretend do understand anything other than it's a lot and big and you're doing a lot - as always. And, thank you for doing that and for letting us know.

Hope you get it done soon - not least so that you can get some sleep!

Thank You for providing a detailed explanation. Looking forward to your next notification.


Congratulations, @theguruasia You Successfully Shared 0.300 WINEX With @arcange.
You Earned 0.300 WINEX As Curation Reward.
You Utilized 3/3 Successful Calls.


Contact Us : WINEX Token Discord Channel
WINEX Current Market Price : 0.360

Swap Your Hive <=> Swap.Hive With Industry Lowest Fee (0.1%) : Click This Link
Read Latest Updates Or Contact Us

Yikes, that's not a fun issue to run into.

All I Can take from this is that you work a lot! Thanks for that. But, it's a good thing though, isn't it, I mean, reaching that huge number of transactions means a lot of users are involved with the block chain, so the hive keeps growing and that's a good thing... Just rambling, sorry! Hope you get your sleep hours back soon!

I dont understand any of that but definitely appreciate you sorting it out....if we were running it things would be a right mess...good work!

I appreciate what you are doing with HiveSQL and a short outage is fine. It's not like the project is going anywhere and it will be back before we know it.

Posted Using LeoFinance Beta

On the bright side, it shows the popularity is growing. 😁
Again, highly appreciate your high-quality work, and having the public database available definitely contributes to the growth of the Hive network!

Thank you for all that you do for the blockchain! 💜

thanks for this information

Thanks for all the work you put into getting and keeping HiveSQL running 👍

Really appreciate what you do for Hive and maybe why HiveBuzz was down.

Posted Using LeoFinance Beta

Congratulations @arcange! You received a personal badge!

You raised your Hive Power every day of the month! Enjoy better curation reward and more to say in governance.
Participate in the next Power Up month to get another one!

You can view your badges on your board and compare yourself to others in the Ranking

Check out the last post from @hivebuzz:

Our Hive Power Delegations to the February PUM Winners
Today is the beginning of a new Hive Power Up Month!
Hive Power Up Day - March 1st 2022
 11 months ago Reveal Comment
 11 months ago Reveal Comment