blocked in work because of selecting information_schema

Discussion of open issues, suggestions and bugs regarding database management and administration tools for MySQL
Post Reply
michabbb
Posts: 217
Joined: Mon 21 Nov 2011 02:35
Location: DE
Contact:

blocked in work because of selecting information_schema

Post by michabbb » Thu 18 Jun 2020 19:12

hi alex,

i fully understand, that dbforge wants to select "information_schema" for the schema.
my big problem is: i have some very large tables, that get re-created every X minutes. this sometimes
takes more than 20minutes. so i got stuck more than 1 or 2 minutes when dbforge is trying to fetch the information_schema in exact that moment. this is super annoying ;-((

maybe there is a way - a switch or something - that dbforge fetches the information_schema without getting blocked,
like read uncommitted or something like that.

getting blocked several times the day makes working no fun ;(

so maybe there is an option to do more caching of the schema or fetching schema without getting blocked.
in case the user realizes, that something is missing, there could be a hotkey to fetch schema the regular way,
like now.

thanks,
micha

alexa

Re: blocked in work because of selecting information_schema

Post by alexa » Fri 19 Jun 2020 07:19

You could unselect or increase the value of the 'Automatically refresh local cache every...' field in the 'Text Editor -> Code Completion -> Suggestions' section of the 'Options' window.

Also, you could specify the database in the 'Do not load suggestions for these databases...' field in that options section.

michabbb
Posts: 217
Joined: Mon 21 Nov 2011 02:35
Location: DE
Contact:

Re: blocked in work because of selecting information_schema

Post by michabbb » Fri 19 Jun 2020 11:33

hi alex,

thanks for your feedback. i appreciate your ideas, but please understand, this is not a solution.
the problem is not, how often one side is doing anything, it´s impossible to avoid the situation,
where dbforge selects the schema while a table gets renamed.

i don´t want to miss suggestions, that´s one of dbforges strengths, that why i paid for the software ;)
so of course disabling that is absolutely no option.

a table rename can take several minutes in a very large database.
because of mysqls nature, the meta-data gets locked in that moment.

and that means: i am unable to work with your software. and that is a blocker, I'm sorry.

what we need in that case is very very simple: instead of

Code: Select all

SELECT * FROM information_schema.TABLES 
INNER JOIN information_schema.COLUMNS c
ON TABLES.TABLE_NAME = c.TABLE_NAME
there should be an option in dbforge, to fetch this data "READ UNCOMMITTED":

Code: Select all

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM information_schema.TABLES 
INNER JOIN information_schema.COLUMNS c
ON TABLES.TABLE_NAME = c.TABLE_NAME
in that case, everybody is able to work without any interruptions!
for people working several hours a day with MySQL and dbforge, this can be
very frustrating, if the software forces you to have a break, despite the fact that we should
have more breaks, but you get it ;)

so, when dbforge would have an option like:

Read Schema uncommitted: [X]yes [ ]no (default)

the user would be able to work without any interruptions.

please understand, a heavy big database is always a living object,
many companies use cronjobs to re-create tables all the time for
aggregation or whatever. that is very common thing. but as soon as a table
gets renamed or the meta-data gets blocked, i get blocked too, and that shouldn´t happen.

people who are using my "switch" i am suggestion here, are aware that dbforge "might not get all data"
and something could be missing. but hey, that is better than being forced having a break when you
are in the middle of your daily work.

please forward that issue to your devs, i am sure they will understand that this is not a simple feature request,
it´s an important issue for heavy MySQL admins working with large databases in production.

thanks,
micha

alexa

Re: blocked in work because of selecting information_schema

Post by alexa » Fri 19 Jun 2020 13:50

Thank you for the reply.

We will investigate this case.

michabbb
Posts: 217
Joined: Mon 21 Nov 2011 02:35
Location: DE
Contact:

Re: blocked in work because of selecting information_schema

Post by michabbb » Fri 19 Jun 2020 16:09

thank you!!!

fyi... today while i was watching mytop (the mySQL processlist), i was able to catch these 3 minutes where
a large table got renamed. the regular schema select was "waiting", and so dbforge has its freeze.

then i stopped that query and made the same query with "read uncommitted" again, and no problem,
i get the result without any waiting.

and that´s great ;-) hopefully your people will understand.

and before someone says: working with a possible incomplete schema is not a good idea, i can say:
in my explanations, i mentioned cronjobs, that re-create tables, every X minutes. so.... even i get an old
schema (while reading uncommitted) - i still have all info, because these tables, that get created, renamed,
deleted... whatever... all the time... these tables are always the same.... nothing is really missing.

i am sure, that most people will say: renaming a table shouldn´t take longer that 0,5 seconds, sure...
but these people - i´m very sure - are not working with that many records, i am ;)

thanks,
micha

michabbb
Posts: 217
Joined: Mon 21 Nov 2011 02:35
Location: DE
Contact:

Re: blocked in work because of selecting information_schema

Post by michabbb » Mon 27 Jul 2020 14:08

i know: nobody cares.... just a little example why i got blocked today for 6 minutes (in total)..... ;-(((((((((

Image

opening the "columns" in the tree took me 2 minutes just because a table got created (as every hour)... ;-((((

Alex: reading the "blocked" schema is terrible.... still..... and it would be so easy to fix... just reading that uncommited....

alexa

Re: blocked in work because of selecting information_schema

Post by alexa » Mon 27 Jul 2020 16:00

We will pass it to our developers team for investigation.

michabbb
Posts: 217
Joined: Mon 21 Nov 2011 02:35
Location: DE
Contact:

Re: blocked in work because of selecting information_schema

Post by michabbb » Mon 27 Jul 2020 16:35

thank you. I know it's frustrating reading problems like these. but dbforge is a tool I am using 8 hours a day, so when you wait for Infos, just because a table gets created you have nothing to do with, that makes you crazy... 🙈

michabbb
Posts: 217
Joined: Mon 21 Nov 2011 02:35
Location: DE
Contact:

Re: blocked in work because of selecting information_schema

Post by michabbb » Tue 28 Jul 2020 14:07

hi alex,

pardon, another example: today i have to change several columns (the type),
so after changing the column type, the whole app is frozen.... why? guess:

Image

just because: after every "alter table" within dbforge, there is this schema-select, and of course,
right at the moment when a tables gets created.. and i was stuck for nearly 3 minutes ;-((( again...
but this time dbforge was frozen, because waiting for the schema-select....

super annoying ;-(

i know, you already said you will let your people know, i just wanted to show another worse example
of this effect.

thanks,
micha

michabbb
Posts: 217
Joined: Mon 21 Nov 2011 02:35
Location: DE
Contact:

Re: blocked in work because of selecting information_schema

Post by michabbb » Fri 25 Sep 2020 12:10

just a tiny little reminder:

- problem still exists
- still super annoying
- still blocks working
- still forces me to have a nap

Image

dzhanhira
Devart Team
Posts: 239
Joined: Mon 26 Oct 2020 13:49

Re: blocked in work because of selecting information_schema

Post by dzhanhira » Thu 04 Mar 2021 17:05


michabbb
Posts: 217
Joined: Mon 21 Nov 2011 02:35
Location: DE
Contact:

Re: blocked in work because of selecting information_schema

Post by michabbb » Thu 04 Mar 2021 20:53

dear dzhanhira,

thanks again for your attention and helping me here!
as I mentioned in my email, there seems to be a mariadb related issue:

SET @@SESSION.transaction_isolation = @local_transaction_isolation;
Unknown system variable 'transaction_isolation'

for details please read my mail.

thank you!
❤️

michabbb
Posts: 217
Joined: Mon 21 Nov 2011 02:35
Location: DE
Contact:

Re: blocked in work because of selecting information_schema

Post by michabbb » Tue 28 Sep 2021 09:48

hi,

i am waiting now since months !
i got promises all the time, that this issue will be fixed.

my whole team is suffering !!!
on the customer's database, tables get re-created now every 30 minutes,
so whenever we are doing something at full or half hour, we have to wait 4-6 minutes
because dbforge is frozen - this a joke !!!!

you have a customer on the phone and have to tell him:

OH SORRY - PLEASE CALL ME BACK IN 6 MINUTES - BECAUSE MY "ENTERPRISE" SOFTWARE IS FROZEN - BECAUSE A TABLE GETS CREATED RIGHT NOW


!!!!!!!!!!!!! THIS IS RIDICULOUS !!!!!!!!!

jatyusur
Posts: 1
Joined: Sat 08 Oct 2022 08:02
Contact:

Re: blocked in work because of selecting information_schema

Post by jatyusur » Mon 10 Oct 2022 10:39

I have a blocking issue and need a little bit of help from the experts. My description of the issue is below and if furthur information is needed please let me know.

Our application allows customers to build sql ad-hoc sql statments to query information and to create a table from the result of the query.

dzhanhira
Devart Team
Posts: 239
Joined: Mon 26 Oct 2020 13:49

Re: blocked in work because of selecting information_schema

Post by dzhanhira » Tue 11 Oct 2022 14:48

Dear Micha,

We are really sorry that you have to wait for a fix for a long time.

it was our plan to provide the fix to you earlier, but unfortunately, the war on the territory of Ukraine, where the Devart development team is located, causes certain difficulties.

Rest assured we are working on it. We understand how important it is for you and continue to work as best we can in the current situation.

Post Reply