0

I am using MariaDB (v10.4.11) and executing sql's via HeidiSQL (v11.3.0.6295).

I have a few tables set up, and I can edit some of them just fine, using either sqls or UI provided by HeidiSQL.

But, two of tables are not editable nor deletable. I tried to edit column names, delete columns, and even delete the entire tables via sqls and ui, but all failed. HeidiSQL crashes, or I have to stop the sql execution manually.

sqls I used are:

UPDATE table1
SET col1 = 'a'
WHERE col2 = 'b';

DROP TABLE table1;

When I execute above sqls on other tables, they work just fine. When I execute the same sqls above on those two tables, HeidiSQL crashes (no response).

I created a backup schema (same tables and same structures), and I could edit all the tables in the backup schema until someone else edited those tables. A co-worker inserted some data into those tables via python, and after that, the same thing happens. I cannot add new columns nor drop tables.

Another co-worker and I inserted into other tables and they are editable.

I looked into processlist and I killed queries that I made. If I kill all the queries (except root), will I be able to edit those tables?

beginnercoder
  • 81
  • 9
  • Based on that info might not be enough. What HeidiSQL and MariaDB versions? ... does HeidiSQL have an error log? – Paul T. Dec 03 '21 at 01:52
  • @Paul T. I updated my question, but I think I found why. I still don't know the fix though. – beginnercoder Dec 06 '21 at 23:29
  • sounds like either reserved names or constraints. we'd need to see the table schema – Garr Godfrey Dec 06 '21 at 23:39
  • @Garr Godfrey co-worker uses dbeaver and I am using heidisql. Could that be a problem? I think whenever she connects to DB via dbeaver, it seems I cannot edit those two tables. – beginnercoder Dec 07 '21 at 00:24
  • seems unlikely unless it is changing schema privileges. Your user account may not have DROP TABLE rights to tables you don't create – Garr Godfrey Dec 08 '21 at 00:25
  • @Garr Godfrey I created all the tables, and my co-worker is inserting data via python. Co-worker asked for an additional column so I was trying to add it, but nothing worked. Just now, I tried to add column to one of two tables via dbeaver on co-worker's computer, and the sql froze, and I could not get the data from that table from my computer via heidisql. So, I think it is either privileges or two different programs crashing each other (which I think is highly unlikely)? – beginnercoder Dec 08 '21 at 04:23
  • I'm not familiar with either of those two interfaces. Why not use the default mariadb client? https://mariadb.com/kb/en/mariadb-command-line-client/ – Garr Godfrey Dec 08 '21 at 21:25
  • @Garr Godfrey thanks for the suggestion. I will look into that. But, after this incident, I think sticking to one interface (be it heidisql, dbeaver, or default mariadb client) is a good idea since it seems like cross-interface is kind of big deal. I will test the default client out. – beginnercoder Dec 09 '21 at 00:10
  • Let us know what you find out. It sounds like a bug in Heidisql, and perhaps in dbeaver as well. They are all basic front ends that just send SQL text to the server, but perhaps dbeaver locks tables and heidisql hangs waiting for lock to be released – Garr Godfrey Dec 09 '21 at 02:07
  • This issue may be related: https://stackoverflow.com/questions/50906793/dbeaver-table-locking/50910428 if coworker is running sql statements and not committing them, heidisql will hang waiting for the table to unlock. Probably eventually time out, but depends on settings – Garr Godfrey Dec 09 '21 at 02:10
  • @Garr Godfrey Thank you. As soon as I find out why and how I fixed this issue, I will post. In the meantime, I will check out the link you sent. Thanks! – beginnercoder Dec 10 '21 at 05:26

0 Answers0