Search the Community
Showing results for tags 'mysql'.
Found 3 results
jbrantley posted a topic in Cool ToolsHey fellow geeks! Like many of you, I used the check boxes to Hide from All Clients and Disable this Client when offboarding and removing old clients from the navigation tree. Since moving to Automate 12, this functionality no longer works. Having several former clients cluttering up the tree in the CC, I started the process of manually deleting clients. This process was slow and frustrating. To combat this, I created a new stored procedure that can be run from script or via SQLyog to simplify this process. The procedure performs the following steps: Checks to ensure no computers remain in the computers table Identifies all locations assigned to the client Deletes network devices, retired assets, location stats, and passwords assigned at the location level then deletes the location. Scans all tables for columns named clientid then deletes any reference to the clientid in these tables. This includes cleaning up references in plugins that the standard delete command misses. This procedure can be run from script using the SQL Execute function with the below SQL statement Substitute the target clientid for X. The same statement can be executed directly from SQLyog, or your favorite MySQL management tool. I've attached a SQL dump that can be executed to add the procedure to your database. Let me know how this works for you! Thanks, Jared Brantley firstname.lastname@example.org Here's the code: DELIMITER $$ USE labtech$$ DROP PROCEDURE IF EXISTS sp_Custom_DeleteClient$$ # Procedure is launched by running CALL sp_Custom_DeleteClient(%clientid%); CREATE PROCEDURE sp_Custom_DeleteClient(IN CLID INT) # Start of procedure. sp_Custom_DeleteClient: BEGIN # NOTE: If this procedure is performed on versions of MySQL earlier than 5.6.3, you may # encounter a 1329 warning for No data - zero rows fetched, selected, or processed. This # is a bug that is resolved in MySQL 5.6.3. The procedure still completes successfully. # Subroutine to verify no agents exist on computers table. SafetyCheck_SUB: BEGIN # Check to ensure that no agents exist in the computers table for ClientID. IF (SELECT COUNT(computerID) FROM computers WHERE clientid = CLID) > 0 THEN # If agents exist, end the procedure now. LEAVE sp_Custom_DeleteClient; # Carry on if no agents exist in the computers table. END IF; # End SafetyCheck subroutine. END SafetyCheck_SUB; /*==============================================================*/ # Subroutine to delete locations assigned to ClientID. DeleteLoc_SUB: BEGIN # Variable representing LocationID. DECLARE LOCID INT(10); # Create variable to end the process. DECLARE FINISH BOOLEAN DEFAULT 0; # Create cursor to grab all LocationsIDs for the ClientID. DECLARE LOCID_CUR CURSOR FOR SELECT locationid FROM locations WHERE clientid = CLID; # SQLSTATE 020000 is returned when we reach the last row, set variable to close loop. DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET FINISH=1; # Here we open the cursor to begin looping through the results. OPEN LOCID_CUR; # Begin looping through results. Del_Location: LOOP # Get locationid from the cursor and insert into LOCID variable. FETCH LOCID_CUR INTO LOCID; # Test FINISH variable to determine if the loop is complete. IF FINISH = 1 THEN LEAVE Del_Location; END IF; # Delete network devices assigned to LocationID. DELETE FROM networkdevices WHERE locationid = LOCID; # Delete retired assets assigned to LocationID. DELETE FROM retiredassets WHERE locationid = LOCID; # Delete location stats assigned to LocationID. DELETE FROM h_locationstats WHERE locationid = LOCID; # Delete passwords assigned at location level. DELETE FROM passwords WHERE locationid = LOCID; # Delete the location. DELETE FROM locations WHERE locationid = LOCID; # End of Del_Location loop. END LOOP; # Close the open CURSOR. CLOSE LOCID_CUR; # End of location deletion subroutine. END DeleteLoc_SUB; /*==============================================================*/ # Subroutine to delete the client. DeleteClient_SUB: BEGIN # Variable to store table names containing a clientid column. DECLARE TBLNAME VARCHAR (50); # Create variable to end the process. DECLARE FINISH BOOLEAN DEFAULT 0; # Create cursor to loop through tables containing a clientid column. # This excludes views and the plugin_webroot_3api table uses the clientid # column for an entirely different purpose. DECLARE TBLNAME_CUR CURSOR FOR SELECT table_name FROM information_schema.columns WHERE column_name='clientid' AND table_name NOT LIKE 'v_%' AND table_name != 'plugin_webroot3_api'; # SQLSTATE 020000 is returned when we reach the last row, set variable to close loop. DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET FINISH = 1; # Here we open the cursor to begin looping through the results. OPEN TBLNAME_CUR; # Loop creation to delete client records. Del_Client: LOOP # Get locationid from the cursor and insert into LOCID variable. FETCH TBLNAME_CUR INTO TBLNAME; # Test FINISH variable to determine if the loop is complete. IF FINISH = 1 THEN LEAVE Del_Client; END IF; # Create a new variable with the populated TBLNAME and CLID variables. SET @s = (SELECT CONCAT('DELETE FROM ',TBLNAME,' WHERE clientid IN (',CLID,');')); # Preapare a delete statement from the above variable. PREPARE stmt FROM @s; # Excute the delete statement EXECUTE stmt; # Dump the statement from memory DEALLOCATE PREPARE stmt; # End of Del_Client loop. END LOOP; # Close the open CURSOR. CLOSE TBLNAME_CUR; # End client deletion subroutine. END DeleteClient_SUB; /*==============================================================*/ # End of procedure. END sp_Custom_DeleteClient $$ DELIMITER; sp_Custom_DeleteClient.sql
The topic sounds good right? Really, this will be a post to address some misconceptions and for me to keep my notes. First off. I AM NOT A MySQL TUNING EXPERT. Please point out any mistakes in what I say below if you know better. Many of you may have heard that MySQL under Windows supports a maximum of 2048 connections. (Well, 2048-(2*Tables) connections). It is shown in the MySQL documentation for versions 5.5-5.7 (and maybe others). Some pages say that this was resolved in MySQL 6 but the changes will not be backported. Other sources say that this limit was removed with MySQL 5.5+. Who do you trust? I trust data. MySQL includes a testing utility, mysqlslap. With some tests you can verify for yourself the concurrent connection limits, and uncover other system limits that may be a factor. First, a review of some my.ini settings to know before testing. max_connections : Recommended to be AgentCount * 3. If this is exceeded, SQL queries will fail with the error "max_connections exceeded" or similar. See https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_connections max_connect_errors : My server had a value of 100. After 100 failed connections without a successful connection, ALL connections from the offending host are blocked for some period of time. I believe mostly working is better than completely not working. Until I learn otherwise, I will suggest that this be set = max_connections. (Yes, this will mostly disable the feature. Is that bad? I'm not sure) See https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_connect_errors open_files_limit = Each open socket (connection) is a file handle. Each table is at least 2 file handles. I am going to suggest that this should be set to max_connections * 3. (That's probably high.. max_connections+tables*2+500 might be closer.. I don't know, but I do know that if this is lower than max_connections or lower than your table count, things will be bad) Well, maybe it wont be bad: mysqld may attempt to allocate more than the requested number of descriptors (if they are available), using the values of max_connections and table_open_cache to estimate whether more descriptors will be needed. See https://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_open-files-limit back_log = The TCP Listen queue size. When MySQL receives a connection, it starts a thread. During this slight delay, additional connections can come in before the threads respond. Unless you expect huge spikes in connections, this does not need to be high. But.. My server had a value of "80" When set to -1, calculates automatically as 50+(Max_connections/5) Max_Connections are typically = 3 x Agent Count. I suspect the calculation may be higher than needed for normal use. So I am going to suggest a Back_Log =AgentCount/5+50. So for 1000 Agents : back_log=250 (Hint: You will want to raise this to the suggested level or higher if you are trying to do testing with a large simultaneous count) See https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_back_log bind-address - Optional, use to control IP addresses the server will listen to. * = All IPv4 and IPv6 Addresses (Default) 0.0.0.0 - All IPv4 addresses - DO NOT DO THIS - I put this entry in to specifically remind myself not to try that again. See https://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_bind-address All MySQL Options Reference - https://dev.mysql.com/doc/refman/5.5/en/mysqld-option-tables.html Before you change ANYTHING to any values I have suggested, please research and become informed yourself, and test to verify results. Now, for testing. (Almost) The last thing to be aware of. TCP Ephemeral Ports and TCP TimeWait. I may explain this more later, for now, know that running out of ports is very bad. Settings to adjust are MaxUserPort and TcpTimedWaitDelay. See https://support.microsoft.com/kb/196271 and http://www.networkjack.info/blog/2008/07/21/windows-server-ephemeral-ports-and-stale-sockets/ Some of the Testing Parameters: (See also https://dev.mysql.com/doc/refman/5.5/en/mysqlslap.html) --concurrency= Number of Concurrent Client sessions (If you want to prove you can reach your max_connections, set this equal to max_connections) --auto-generate-sql-execute-number= Number of queries per client session (Normally would be 3 to create a table, query it, and drop it.) Setting this higher causes each client session to run longer, increasing the odds that you will reach max_connections --iterations= Number of times to repeat test above. Total connections should be concurrency*iterations*(sql-execute-number/detach) --detach= Number of queries to complete before dropping and reconnecting the session Testing Commands: #Kick off Load Test: "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlslap.exe" --user=ADMINUSERorROOT --password="PASSWORD" --host=localhost --auto-generate-sql --auto-generate-sql-execute-number=100 --concurrency=40 --iterations=2 --number-int-cols=2 --number-char-cols=2 -v --auto-generate-sql-load-type=read #Stress a little: "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlslap.exe" --user=ADMINUSERorROOT --password="PASSWORD" --host=localhost --auto-generate-sql --auto-generate-sql-execute-number=100 --concurrency=4000 --iterations=2 --number-int-cols=5 --number-char-cols=2 -v --auto-generate-sql-load-type=read #Break Max Connections: (Set concurrency=2*max_connections) - If back_log is low, connections will rate limit and you won't get all attempts to occur simultaneously. "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlslap.exe" --user=ADMINUSERorROOT --password="PASSWORD" --host=localhost --auto-generate-sql --auto-generate-sql-execute-number=100 --concurrency=8000 --iterations=2 --number-int-cols=5 --number-char-cols=2 -v --auto-generate-sql-load-type=read #Break Ephermeral Ports (Probably): Make each thread drop and reconnect every 10 queries. Should exhaust the ports in TIME_WAIT state "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqlslap.exe" --user=ADMINUSERorROOT --password="PASSWORD" --host=localhost --auto-generate-sql --auto-generate-sql-execute-number=1000 --concurrency=40 --iterations=10 --detach=10 --number-int-cols=2 --number-char-cols=2 -v --auto-generate-sql-load-type=read During testing, to see MySQL Server Stat info run: SHOW STATUS WHERE `variable_name` LIKE '%connect%' AND VALUE <> 0; During testing to see connection information run: cmd /v:on /c "SET "newf=%temp%\netstat-temp-%RANDOM%.tmp"&netstat -an|find ":3306">"!newf!"&echo Established:&type "!newf!"|find /c "ESTABLISHED"&echo Time_Wait:&type "!newf!"|find /c "TIME_WAIT"&echo Other States:&type "!newf!"|find /v "ESTABLISHED"|find /v /c "TIME_WAIT"&del "!newf!">NUL" mysql server tuning my.ini server variable variables setting settings parameter parameters performance optimize
DarrenWhite99 posted a topic in Advanced ConfigurationsAutomate installations provide a number of custom MySQL functions. These helped fill particular needs within the program, but they can help you too!. Suppose you have two version numbers, like VER1=10.3 and VER2=10.2. Trying to compare them directly is a StringComparison and returns only 1 or 0 (True or False). A proper version comparison will tell you if VER1 is LESS than, EQUAL to, or GREATER than VER2. The custom function LT_CompareVersions('VER1','VER2') will do just this. Here is how the function works in practice. If VER1 is greater, the result should be "1", if it is less it should be "-1", and "0" if equal. Evaluating, StringCompare, VersionCompare, ShouldBe '11.34>11', '1', '1', '1' '11.34>11.4', '0', '1', '1' '1.10.0>11.0.0', '0', '-1', '-1' '2.10.0>11.0.0', '1', '-1', '-1' '184.108.40.206>220.127.116.11', '0', '1', '1' You can see that a simple version compare will work with VER1>VER2, but when they get more complex you would get the wrong result each time. Here is an example using the "IF SQL Check" script function. In my test, I want to know if the DiscoveredVersion is greater than my TargetVersion: IF [SQL SELECT (LT_CompareVersions('@DiscoveredVersion@','@TargetVersion@') > 0) AS VerResult] = 1 THEN Jump to :NoUpgradeNeeded