Custom Maintenance/Bulk Processing Operations

I was thinking that a nice feature to have would be 'custom maintenance' or 'bulk table processing.' Allow me to explain.

Today I had to merge the contents of two databases, with hundreds of tables. I did it using:

INSERT IGNORE database1.table SELECT * FROM database2.table

But there isn't any way to automate that process, so I had to rely on a combination of HeidiSQL, Excel, and Word, along with some very complicated search and replace operations to build the list of queries to handle all 200+ tables. Thank goodness I didn't have to type it all out.

What would be really cool is if HeidiSQL had a way to do this automatically. On the Maintenance screen perhaps? Where, for the above operation, I could enter:

INSERT IGNORE database1.%table% SELECT * FROM database2.%table%

...and HeidiSQL would perform all of the replacements and execute that queries for each of the selected tables.

Does that make sense?

Yes, understand what you mean. Well, that's very special stuff I think. How's that:

SELECT CONCAT('INSERT IGNORE INTO db1.`', t.TABLE_NAME, '` SELECT * FROM db2.`', t.TABLE_NAME, '`')
FROM information_schema.`TABLES` AS t
WHERE t.TABLE_SCHEMA='db2'

Then, copy grid rows as semicolon delimited text and paste that into a new query tab.

Yeah, that would work... I don't feel confident in my employees' ability to do something that complicated, though.

Having a new tab on the Table Tools screen that could do this for me would be awesome. It's essentially what you're doing with the Bulk Table Editor, but allowing the user to specify the queries that are run.

I think I don't need to add GUI support for all kinds of exitic operations - there is mostly some trick to do it per SQL queries.

"I don't feel confident in my employees' ability to do something that complicated, though."

I guess it might be time to find some new employees, then. Why employ someone you have so little confidence in (the given suggestion doesn't seem complicated or difficult)?

Because we're a small company and can only afford to hire inexperienced college students. Not everybody can afford to hire $100,000/yr database experts.

What's the chance that I could add this myself? I am a Delphi programmer.

(Disclaimer - I'm in no way associated with HeidiSQL nor do I think this problem in any way relates to the application you are using to run the resulting SQL, in this case HeidiSQL)

My apologies if I seem combative, but being a small company is little excuse to take employees you have little confidence in and put them in charge of something as critical as managing any production databases you might have (salary is no excuse, not only have I worked for pittance I am presently responsible for hiring across numerous areas of IT; finding good help is hard, but I rarely find salary to be the primary stumbling block).

Shelving the issue for the moment, it is entirely possible I do not fully understand your initial request, but based on what you've provided... I'm assuming you are trying to take two databases with identical table structures and trying to merge them into one (while ignoring any errors such as clobbering any unique values), is that accurate?

I'm not exactly sure what approach you are taking, bit if you are using both Excel and Word (especially Word) I can somewhat safely say that you are "doing it wrong". I think the problem here is less that the SQL GUI you're using can't do such a thing as it is that you are using the wrong tools in the first place. Might I suggest Vi (if you'd like an example, please provide me an example set of databases/tables and I can provide the steps to produce the necessary output)?

Let me know, thanks. I don't mean to step on any toes, I just feel that it's appropriate to use the right tool for the job (and in this case, I'm not sure the SQL GUI client is the right tool).

First of all, you can't pretend to know anything about my company or what we are doing. We are currently supporting 400 servers of over 200 tables each, with another 10 servers coming online every week. It's a hugely overwhelming task. The support fee we charge our customers doesn't financially justify hiring an experienced support staff, but we do need to move data around. At $9/hr we can't hire anyone with meaningful database experience when the primary job requirement is good people skills. People with good people skills generally don't have strong technical skills. I dare you to find anyone you trust doing the above operation that will work for $9/hr, especially if they have the demeanor and personality to be friendly with customers on the phone.

Our development staff is more than overwhelmed with creating the product we sell rather than spending time trying to develop an internal tool to recreate the features of HeidiSQL. In the past the author has been at least somewhat responsive to reasonable feature requests, and this one certainly seems reasonable. It is much easier (for anyone, not just our staff) to enter "INSERT INTO database1.%table% SELECT * FROM database2.%table%" than the complicated hack provided above, (which, incidentally, wouldn't have worked anyway because it would have choked on the first non-table entry in information_schema).

We teach a little bit of SQL to our employees, but teaching them to understand 'information_schema,' using the CONCAT function, and a complicated copy/paste procedure is beyond the scope of what we are willing to asking them to do. SELECTs, INSERTS, and UPDATEs they understand. I'm not asking for this feature because I don't trust them... I trust them implicitly. But asking them to perform this type of database operation without the proper tool is out of the question.

I still believe this feature request is reasonable. How often have you wanted to do something to all of your tables that isn't currently supported by HeidiSQL? It happens to me all of the time. I felt like I was being reasonable by asking, but instead I'm being attacked by not only the author but another user of the software.

Calm down, noone attacked you, just a bit harsh discussion. You should be prepared for such arguments if you request new features in a software used by many other users.

Well, feel free to grab the sources and compile Heidi for you, it's OpenSource. Only I won't add this stuff to the official version, as said above.

If the changes aren't incorporated, it's almost pointless for me to make them, because the next time the product is updated my version is out-of-date.

Are you against including a feature that others might find useful?

No, of course not. But I made very bad experiences in just overtaking patches from outside. Up to now I only got ugly code, unfriendly developers and long discussions about usability. Nothing I want to do again for the moment. Feel free to try and convince me that you're doing it in a different way. But you will find that it's hard to convince me :)

@djdjohnson - Well, I'm new around here, so I'll do my best not to get into a flame war with you here, but let me reply to a few things you mentioned. For clarity, I'm not trying to be argumentative with you here, but hopefully to provide some value via constructive criticism, please take my two cents for whatever they are worth.

1) There are numerous glaring red flags with the sort of business model you presently have, not the least of which being that it sounds suspiciously like your product is scaling rapidly but was not properly designed to scale. This sort of problem is only going to get worse, not better, and your developers would be wise to revisit this issue sooner rather than later (because at some point it will become unmanageable).

2) This goes along with #1, and you can shrug this off with a similar "you don't know anything about our business" sort of statement, but you should really take it to heart: You are hiring the wrong people, or at the very least you are asking the wrong people to work on the wrong tasks. There is a large distinction between customer service and technical support. I appreciate the need for a small company to employ as few resources as possible and to ask these resources to wear many hats (I've worked for companies as small as 7 employees and have worn every hat from customer service, technical support, engineering, sales, and marketing), but hiring 3 people who don't meet your needs at $9/hr is far less efficient in the long run than hiring a single resources for $27/hr who can automate these processes and competently handle your business needs.

3) Along the lines of "the right people for the right job", it is another gigantic red flag that your business is employing highly inexperienced resources and providing them full access to production databases (and, worse yet, asking them to be in charge of merging these databases).

4) It is important to sometimes take a step back and ensure you are using the right tool for the job at hand. In this specific case, I do not believe that adding additional bloat to your SQL client to perform a very specific (and otherwise very uncommon) task is the right approach. In this case a simple script or stored procedure can do what you want just as easily (and is a much more appropriate avenue for you to take). As an example, I submit the following (it may not be exactly what you need, but you can tweak it as needed), it took me about 15 minutes to throw together and cost you substantially less than your typical $9/hr (i.e. $0/hr).

Bear in mind, this is only one way to accomplish your task, but deploying a stored procedure such as this would allow your support staff to merely be trained in executing said stored procedure. The stored procedure is at the end, hopefully the SQL example is self explanatory.

DROP DATABASE IF EXISTS `test1`;
CREATE DATABASE `test1`;
USE `test1`;
DROP TABLE IF EXISTS `testTable`;
CREATE TABLE `testTable` (
`id` INT(10) UNSIGNED NOT NULL,
`data` VARCHAR(10),
PRIMARY KEY (`id`)
);
INSERT INTO `testTable` VALUES
(1, 'test1 A'),
(2, 'test1 B'),
(3, 'test1 C')
;
DROP DATABASE IF EXISTS `test2`;
CREATE DATABASE `test2`;
USE `test2`;
DROP TABLE IF EXISTS `testTable`;
CREATE TABLE `testTable` (
`id` INT(10) UNSIGNED NOT NULL,
`data` VARCHAR(10),
PRIMARY KEY (`id`)
);
CREATE VIEW `testView` AS SELECT * FROM testTable;
INSERT INTO `testTable` VALUES
(1, 'test2 A'),
(4, 'test2 D'),
(5, 'test2 E')
;
DROP PROCEDURE IF EXISTS mergeDB;
DELIMITER //
CREATE PROCEDURE mergeDB (IN mergeDest VARCHAR(255), IN mergeSource VARCHAR(255))
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE tableName VARCHAR(255);
DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA=mergeSource AND TABLE_TYPE='BASE TABLE';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
tableLoop: LOOP
FETCH cur INTO tableName;
IF done THEN
LEAVE tableLoop;
END IF;
SET @stmt = CONCAT('INSERT IGNORE INTO ', mergeDest, '.', tableName, ' SELECT * FROM ', mergeSource, '.', tableName);
PREPARE cmd FROM @stmt;
EXECUTE cmd;
END LOOP;
END //
DELIMITER ;
CALL mergeDB('test1','test2');
SELECT * from test1.testTable;

We're doing the best we can with what we have. Our technical staff is incredibly skilled... we're all just way too overburdened with other "has to be done right now" tasks. Things will, and already are, changing. We're already building Tier 2 and 3 support layers, and we can teach them more technical tasks, taking away the most technical from front-line support. I fully agree that the way we are doing things right now is not ideal. But for the time being we don't have much of a choice. We have great employees... everybody is being stretched way too thin for a while longer while things are expanding so crazy fast. When growth is exponential it's next to impossible to keep up no matter how good or how much staff you have.

Back to my original point, though... I was asking for an easy way in HeidiSQL to perform the same task over and over on a collection of tables. Your stored procedure looks like it will work great for this one particular task, but there are plenty of other reasons to have a feature to repeatedly do the same thing over and over. HeidiSQL already has this capability for changing table engines, collation, checking and repairing, etc. Just no way for the user to perform their own SQL. If anse feels that this isn't valuable to anyone else, I guess either my developers or I will step in and create something, but I thought that this might be useful to others as well, not just my organization. If we can use it, and it saves time and effort, it seems like it might be valuable to others as well.

You certainly wouldn't have to use the feature. If you don't like it you could just ignore it. It shouldn't get in anyone's way if implemented. I'm just looking for an easier way to automate a repeatable task in a much easier way than what we have to do now. Typing a single INSERT statement into the Table Tools dialog is much easier than creating the function above... or recreating it again and again each time a repeatable task comes up. I think most people would agree on that. And this particular application is only one in a billion possible uses.