Not always running the startup.sql script

[expired user #6254]'s profile image [expired user #6254] posted 9 years ago in Creating a connection Permalink

I have a very long-running HeiSQL instance that stays up for weeks. I am setting the time_zone variable in a startup.sql script. However sometimes, usually on the first connection of the morning after having timed out its connection overnight, I am finding that my startup.sql isn't being executed:

/* Connection to 127.0.0.1 closed at 2015-12-17 11:25:07 */
/* Connecting to 127.0.0.1 via MySQL (TCP/IP), username vSimLauncher, using password: Yes ... */
SELECT CONNECTION_ID();
/* Connected. Thread-ID: 16 */
/* Unknown character set: 'utf8mb4' */
/* Characterset: utf8 */
SHOW STATUS;
SHOW VARIABLES;
USE `vsimlauncher`;
SHOW DATABASES;
SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='vsimlauncher';
SHOW TABLE STATUS FROM `vsimlauncher`;
SHOW FUNCTION STATUS WHERE `Db`='vsimlauncher';
SHOW PROCEDURE STATUS WHERE `Db`='vsimlauncher';
SHOW TRIGGERS FROM `vsimlauncher`;
SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='information_schema';
SHOW TABLE STATUS FROM `information_schema`;
SHOW FUNCTION STATUS WHERE `Db`='information_schema';
SHOW PROCEDURE STATUS WHERE `Db`='information_schema';
SHOW TRIGGERS FROM `information_schema`;
SHOW EVENTS FROM `information_schema`;
SELECT *, EVENT_SCHEMA AS `Db`, EVENT_NAME AS `Name` FROM information_schema.`EVENTS` WHERE `EVENT_SCHEMA`='vsimlauncher';
SHOW CREATE TABLE `vsimlauncher`.`ports`;
SHOW COLLATION;
/* Entering session "vSimLauncher" */
SELECT  `id`,  `vm_guest_id`,  `vm_guest_port`,  `dynamic`,  `port_in_use`,  `reserved`,  `course_id`,  `course_instance`,  `vsim_id`,  `vsim_port_num`,  `start_datetime`,  `runid`,  `pid`,  `browsing`,  `healthy_datetime` FROM `vsimlauncher`.`ports` ORDER BY `vm_guest_id` ASC, `vm_guest_port` ASC LIMIT 1000;
SHOW CREATE TABLE `vsimlauncher`.`ports`;
SELECT  `id`,  `vm_guest_id`,  `vm_guest_port`,  `dynamic`,  `port_in_use`,  `reserved`,  `course_id`,  `course_instance`,  `vsim_id`,  `vsim_port_num`,  `start_datetime`,  `runid`,  `pid`,  `browsing`,  `healthy_datetime` FROM `vsimlauncher`.`ports` ORDER BY `vm_guest_id` ASC, `vm_guest_port` ASC LIMIT 1000;
SHOW CREATE TABLE `vsimlauncher`.`ports`;
SELECT  `id`,  `vm_guest_id`,  `vm_guest_port`,  `dynamic`,  `port_in_use`,  `reserved`,  `course_id`,  `course_instance`,  `vsim_id`,  `vsim_port_num`,  `start_datetime`,  `runid`,  `pid`,  `browsing`,  `healthy_datetime` FROM `vsimlauncher`.`ports` ORDER BY `vm_guest_id` ASC, `vm_guest_port` ASC LIMIT 1000;
SHOW CREATE TABLE `vsimlauncher`.`ports`;
SELECT  `id`,  `vm_guest_id`,  `vm_guest_port`,  `dynamic`,  `port_in_use`,  `reserved`,  `course_id`,  `course_instance`,  `vsim_id`,  `vsim_port_num`,  `start_datetime`,  `runid`,  `pid`,  `browsing`,  `healthy_datetime` FROM `vsimlauncher`.`ports` ORDER BY `vm_guest_id` ASC, `vm_guest_port` ASC LIMIT 1000;
SHOW CREATE TABLE `vsimlauncher`.`ports`;

I haven't been able to isolate the cases where it does and doesn't work. It may be related to the fact that I have 'automatically reconnect to last used sessions on startup' enabled.

ansgar's profile image ansgar posted 9 years ago Permalink

That automatically reconnect... setting does not disable firing the startup script, so it must be something else. If you have specified a script file which HeidiSQL cannot find or read, Heidi would show an error message saying Startup script file not found: xyz

[expired user #6254]'s profile image [expired user #6254] posted 9 years ago Permalink

Well it doesn't do that, and the script does work most of the time. I don't know what else I can do to isolate it. Maybe you should try leaving one running overnight and see what happens your end.

ansgar's profile image ansgar posted 9 years ago Permalink

I guess you are mixing the automatic reconnect after having been disconnected with a "normal" session connect. When you leave Heidi opened over night, your session connection is left open and the next day you see this session disconnected by a red x icon in the tree. Then you click on some database which triggers an automatic reconnect. In such cases, HeidiSQL does not trigger the startup-script. Is that what you mean?

[expired user #6254]'s profile image [expired user #6254] posted 9 years ago Permalink

Even simpler than that. I have a specific table open and I am watching it for updates. I leave it there overnight and just refresh it with F5.

[expired user #6254]'s profile image [expired user #6254] posted 9 years ago Permalink
The actual sequence of events is:

/* Connection to 127.0.0.1 closed at 2015-12-19 14:20:54 */
/* Connecting to 127.0.0.1 via MySQL (TCP/IP), username vSimLauncher, using password: Yes ... */
SELECT CONNECTION_ID();
/* Connected. Thread-ID: 21 */
/* Unknown character set: 'utf8mb4' */
/* Characterset: utf8 */
SHOW STATUS;
SHOW VARIABLES;
USE `vsimlauncher`;
SHOW DATABASES;
SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='vsimlauncher';
SHOW TABLE STATUS FROM `vsimlauncher`;
SHOW FUNCTION STATUS WHERE `Db`='vsimlauncher';
SHOW PROCEDURE STATUS WHERE `Db`='vsimlauncher';
SHOW TRIGGERS FROM `vsimlauncher`;
SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='information_schema';
SHOW TABLE STATUS FROM `information_schema`;
SHOW FUNCTION STATUS WHERE `Db`='information_schema';
SHOW PROCEDURE STATUS WHERE `Db`='information_schema';
SHOW TRIGGERS FROM `information_schema`;
SHOW EVENTS FROM `information_schema`;
SELECT *, EVENT_SCHEMA AS `Db`, EVENT_NAME AS `Name` FROM information_schema.`EVENTS` WHERE `EVENT_SCHEMA`='vsimlauncher';
SHOW CREATE TABLE `vsimlauncher`.`ports`;
SHOW COLLATION;
/* Entering session "vSimLauncher" */
SELECT  `id`,  `vm_guest_id`,  `vm_guest_port`,  `dynamic`,  `port_in_use`,  `reserved`,  `course_id`,  `course_instance`,  `vsim_id`,  `vsim_port_num`,  `start_datetime`,  `runid`,  `pid`,  `browsing`,  `healthy_datetime` FROM `vsimlauncher`.`ports` ORDER BY `vm_guest_id` ASC, `vm_guest_port` ASC LIMIT 1000;
SHOW CREATE TABLE `vsimlauncher`.`ports`;
SELECT  `id`,  `vm_guest_id`,  `vm_guest_port`,  `dynamic`,  `port_in_use`,  `reserved`,  `course_id`,  `course_instance`,  `vsim_id`,  `vsim_port_num`,  `start_datetime`,  `runid`,  `pid`,  `browsing`,  `healthy_datetime` FROM `vsimlauncher`.`ports` ORDER BY `vm_guest_id` ASC, `vm_guest_port` ASC LIMIT 1000;
SHOW CREATE TABLE `vsimlauncher`.`ports`;
ansgar's profile image ansgar posted 9 years ago Permalink

That's the same thing I was talking about - by hitting F5, you are triggering an automatic reconnect.

[expired user #6254]'s profile image [expired user #6254] posted 9 years ago Permalink

I understand that but why isn't my script being run?

ansgar's profile image ansgar posted 9 years ago Permalink

Because this is a silent reconnect. Nothing the user triggered knowingly. Does that make sense?

[expired user #6254]'s profile image [expired user #6254] posted 9 years ago Permalink

No it doesn't actually. I want my timezone to be US/Pacific in every session and I've set up a startup.sql accordingly. If it isn't read in some circumstances I would regard that as a bug.

ansgar's profile image ansgar posted 9 years ago Permalink

Guess you're right. Most startup scripts probably do something with the session, so it's a must to execute that on a reconnect too.

[expired user #6254]'s profile image [expired user #6254] posted 9 years ago Permalink

Maybe there should be a session.sql for session-related stuff, and startup.sql executed once.

MichaelCurrie's profile image MichaelCurrie posted 12 months ago Permalink

I have exactly the same issue, sadly 8 years later: silent reconnects do not execute the startup script, so my session time zone is left incorrect. As I can see from SHOW SESSION VARIABLES LIKE 'time_zone';

My workaround is to uncheck in Preferences -> General the "Automatically reconnect to previously opened sessions on startup". I believe this will stop the silent reconnects.

ansgar's profile image ansgar posted 12 months ago Permalink

"Automatically reconnect to previously opened sessions on startup". I believe this will stop the silent reconnects.

No, this will just not connect to your previous connections when you run heidisql.exe. This has nothing to do with the silent reconnects.

Please login to leave a reply, or register at first.