Sunsick's Tech Blog: May 2020

Friday, 29 May 2020

[SQL Server] SQL Server in Single-User Mode

[SQL Server] SQL Server in Single-User Mode

When to use?

SQL Server is damaged and can't start normally.
There's a SQL Server trigger and everybody is blocked to login.
You want to do some maintenance work, but you don't want to connect to the server without configuring firewalls or something.

Prerequisite

You are able to connect to the server via RDP.
You are an administrator of the server.
There's a SQL Server trigger and everybody is blocked to login.
You want to do some maintenance work, but you don't want to connect to the server without configuring the firewall or anything.

How to configure

with SQL Server Configuration Manager

Launch SQL Server Configuration Manager

Open SQL Server, Select Properties

Go to "Startup Parameters" and add "-m"

Check and "OK"

Restart SQL Server service

with Command Prompt

Check Service Name, (It's MSSQL$SQL2017 in this case)

use "Net stop" to stop service

net stop MSSQL$SQL2017

use "Net start" to start service with /m option

net start MSSQL$SQL2017 /m

Test

Now you will see the following error message when you're trying to connect to the server

Connect to SQL Server

SQL Server Management Studio

Now, you try to connect to the server as a local administrator

Somehow, you can't login to the server even if you're a local administrator

This is because "Object Explorer" takes a session already.

So, change the environment: Open new query window at Startup

"OK" and close all SSMS

Restart SQL Server

Launch SSMS manually with the options

"C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\ssms"

-S .\SQL2017 -d Master -E

Now it works and you're the only one.

Of course, you can't open another a new query window

SQLCMD

Connect to the server via sqlcmd

sqlcmd -S .\SQL2017 -d Master -E

References

Start SQL Server in Single-User Mode:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/start-sql-server-in-single-user-mode?view=sql-server-ver15
SSMS - Options (Environment - Startup page):
https://docs.microsoft.com/en-us/sql/ssms/menu-help/options-environment-startup-page?view=sql-server-ver15

Thursday, 28 May 2020

[MySQL] Zabbix Error - Access denied : SHOW SLAVE STATUS

[MySQL] Zabbix Error - Access denied : SHOW SLAVE STATUS

How to grant a privilege to zabbix user for "SHOW SLAVE STATUS"

When you install zabbix on MySQL, you might see the following error message.

2017-01-01 01:01:01 zabbix[zabbix] @ localhost [] ERROR 1227: Access denied;

 you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation : SHOW SLAVE STATUS

Now, let's check the privileges.

SELECT USER, HOST, SUPER_PRIV, REPL_CLIENT_PRIV 
FROM mysql.user
WHERE USER = 'zabbix';

zabbix user does not have "REPL_CLIENT_"PRIV".

Let's grant the replication client permission to zabbix user.
Method #1. GRANT. (Recommendation)

GRANT REPLICATION CLIENT ON *.* TO 'zabbix'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'zabbix'@'127.0.0.1';

Method #2. UPDATE mysql.user Table

UPDATE mysql.user
SET
  REPL_CLIENT_PRIV = 'Y'
WHERE USER = 'zabbix';

Now, check the privileges again.

SELECT USER, HOST, SUPER_PRIV, REPL_CLIENT_PRIV 
FROM mysql.user
WHERE USER = 'zabbix';

That's it.

It will work.

Monday, 18 May 2020

[SQL Server] Implement Oracle "START WITH ~ CONNECT BY" clause

[SQL Server] Implement Oracle "START WITH ~ CONNECT BY" clause

# Oracle

SELECT   LEVEL, CATE_ID, LTRIM (SYS_CONNECT_BY_PATH (CATE_NAME, ' > '), ' > ') || ' > ' AS CATE_NAME
      FROM TBL_CATEGORY
START WITH CATE_ID = 1
CONNECT BY PRIOR CATE_ID = PARENT_CATE_ID

# SQL Server

WITH CTE_TABLE
AS
(
       SELECT 1 AS LEVEL, A.CATE_ID, PARENT_CATE_ID, CATE_NAME, CONVERT(VARCHAR(100), CAST(A.CATE_NAME as VARCHAR(100)) + ' > ') AS CATE_NAME
       FROM dbo.TBL_CATEGORY A
       WHERE A.CATE_ID = 1

       UNION ALL

       SELECT LEVEL + 1, M.CATE_ID, M.PARENT_CATE_ID, M.CATE_NAME, CONVERT(VARCHAR(100), N.CATE_NAME + ' > ' + CAST(M.CATE_NAME AS VARCHAR(100)) + ' > ' ) AS CATE_NAME
       FROM dbo.TBL_CATEGORY M JOIN CTE_TABLE N ON M.PARENT_CATE_ID = N.CATE_ID
)
SELECT * FROM CTE_TABLE
;

Sunday, 17 May 2020

[SQL Server] Display VLF(Virtual Log File) per database

[SQL Server] Display VLF(Virtual Log File) per database

Check Log Information for a single database

DBCC LOGINFO(AdventureWorks2016)

Check Log Information for all databases

CREATE TABLE #eachDB(
RecoveryUnitId INT
, FileID INT
, FileSize BIGINT
, StartOffset BIGINT
, FSeqNo BIGINT
, [Status] BIGINT
, Parity BIGINT
, CreateLSN NUMERIC(38)
);

CREATE TABLE #summary(
Database_Name sysname
, VLF_count INT
, Log_File_count INT
);

EXEC sp_MSforeachdb N'Use [?];
Insert Into #eachDB
Exec sp_executeSQL N''DBCC LogInfo(?)'';
Insert Into #summary
Select DB_Name(), Count(*), Count(Distinct FileID)
From #eachDB;
Truncate Table #eachDB;'

-- Check Resut
SELECT *
FROM #summary
ORDER BY VLF_count DESC;

-- Drop temp tables
DROP TABLE #eachDB;
DROP TABLE #summary;

[SQL Server] Email validation function (using regular expressions)

[SQL Server] Email validation function (using regular expressions)

Create a user-defined function

CREATE FUNCTION [dbo].UFN_ValidEmailCheck
(
       @EMail VARCHAR(255)
)
RETURNS BIT
AS
BEGIN
       DECLARE @IsValid BIT
       SET @IsValid = 0    
       IF @EMail IS NOT NULL
       BEGIN
             SET @EMail = LOWER(@EMail)

             IF @EMail like '[a-z,0-9,_,-]%@[a-z,0-9,_,-]%.[a-z][a-z]%'
                    AND @EMail NOT like '%@%@%'
                    AND CHARINDEX('.@',@EMail) = 0
                    AND CHARINDEX('..',@EMail) = 0
                    AND CHARINDEX(',',@EMail) = 0
                    AND RIGHT(@EMail,1) between 'a' AND 'z'
             BEGIN
                    SET @IsValid = 1
             END
       END

       RETURN @IsValid
END

Test 01.

1	SELECT dbo.UFN_ValidEmailCheck('wwwwaaaa@aaabbcc.com') AS ValidEmail

ValidEmail
----------
1

(1 row affected)

Test 02.

1	SELECT dbo.UFN_ValidEmailCheck('^!afvb@bbbbb.com') AS ValidEmail

ValidEmail
----------
0

(1 row affected)

Test 03.

1	SELECT dbo.UFN_ValidEmailCheck('a.b.c@aaa.bbcc.com') AS ValidEmail

ValidEmail
----------
1

(1 row affected)

Test 04.

1	SELECT dbo.UFN_ValidEmailCheck('a.b.c@.aaa.bbcc.com') AS ValidEmail

ValidEmail
----------
0

(1 row affected)

Saturday, 16 May 2020

[MariaDB] How to change MariaDB error log from syslog to separated log file

[MariaDB] How to change MariaDB error log from syslog to separated log file

On my.cnf, you can see the message like this.

# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.

Therefore, if you want to see MariaDB Error Log, then you need to check syslog.

For e.g.

$ cat /var/log/syslog | grep "mysql"

$ cat /var/log/syslog | grep "mariadb"

But, you can store error log to a certain file instead of syslog by editing "/etc/mysql/conf.d/mysqld_safe_syslog.cnf".

$ sudo vi /etc/mysql/conf.d/mysqld_safe_syslog.cnf

[mysqld_safe]
skip_log_error
#syslog

Then, specify the error log file path on my.cnf.

$ vi /etc/mysql/my.cnf

log_error = /var/log/mysql/error.log

Restart MariaDB.
Then error log will be stored into /var/log/mysql/error.log.

Reference.
https://mariadb.com/kb/en/mariadb/error-log/

Thursday, 14 May 2020

[SQL Server] DB Size Check Script - Simple

Script

USE tempdb
GO

DECLARE @dbname SYSNAME;
SET @dbname = NULL;
IF @dbname IS NOT NULL
   AND @dbname NOT IN(SELECT name
                      FROM master.dbo.sysdatabases)
    BEGIN
        RAISERROR('You''re just one step away from the results. please verify the database name is correct and try again. www.sqlServer-Training.com', 16, 1);
END;
SET NOCOUNT ON;
IF EXISTS
(
    SELECT TOP 1 1  FROM tempdb..sysobjects
    WHERE name LIKE '#sizeinfo%' AND type = 'u'
)
DROP TABLE #sizeinfo;
;

CREATE TABLE #sizeinfo
(db_name         VARCHAR(100) NOT NULL PRIMARY KEY CLUSTERED , 
 total           DEC(7, 1), 
 data            DEC(7, 1), 
 data_used       DEC(7, 1), 
 [data (%)]      DEC(7, 1), 
 data_free       DEC(7, 1), 
 [data_free (%)] DEC(7, 1), 
 log             DEC(7, 1), 
 log_used        DEC(7, 1), 
 [log (%)]       DEC(7, 1), 
 log_free        DEC(7, 1), 
 [log_free (%)]  DEC(7, 1), 
 STATUS          DEC(7, 1)
);

SET NOCOUNT ON;
INSERT INTO #sizeinfo
(db_name, 
 log, 
 [log (%)], 
 STATUS
)
EXEC ('dbcc sqlperf(logspace)
with no_infomsgs');
PRINT '';
PRINT '';

IF @dbname IS NULL
    DECLARE dbname CURSOR
    FOR SELECT name
        FROM master.dbo.sysdatabases
        WHERE NOT STATUS = 32
              AND NOT STATUS = 512
     AND name <> 'AdventureWorks2014'
        ORDER BY name ASC;
    ELSE
    IF @dbname IS NOT NULL
        BEGIN
            DELETE FROM #sizeinfo
            WHERE db_name <> @dbname;
            DECLARE dbname CURSOR
            FOR SELECT name
                FROM master.dbo.sysdatabases
                WHERE NOT STATUS = 32
                      AND NOT STATUS = 512
                      AND name = @dbname
       AND name <> 'AdventureWorks2014'
    END;
OPEN dbname;
FETCH NEXT FROM dbname INTO @dbname;
WHILE @@fetch_status = 0
BEGIN
 ----- adding .0 at the end of interger to avoid divide by zero error
 EXEC (' use ['+@dbname+'] declare @total dec(7,1),
  @data dec (7, 1),
  @data_used dec (7, 1),
  @data_percent dec (7, 1),
  @data_free dec (7, 1),
  @data_free_percent dec (7, 1),
  @log dec (7, 1),
  @log_used dec (7, 1),
  @log_used_percent dec (7, 1),
  @log_free dec (7, 1),
  @log_free_percent dec (7, 1)
  set @total = (select sum(convert(dec(15),size)) from sysfiles) * 8192.0 /1048576.0
  set @data = (select sum(size) from sysfiles where (status & 64 = 0))* 8192.0 / 1048576.0
  set @data_used = (select sum(convert(dec(15),reserved)) from sysindexes
  where indid in (0, 1, 255)) * 8192.0 / 1048576.0
  set
  @data_percent = (@data_used * 100.0 / @data)
  set @data_free = (@data - @data_used)
  set @data_free_percent = (@data_free * 100.0 / @data
  )
  set @log = (select log from #sizeinfo where db_name = '''+@dbname+''')
  set @log_used_percent = (select [log (%)] from #sizeinfo where db_name ='''+@dbname+''')
  set @log_used = @log * @log_used_percent / 100.0
  set @log_free = @log - @log_used
  set @log_free_percent =@log_free * 100.0 / @log
  update #sizeinfo set total = @total,
 
  data = @data ,
  data_used = @data_used,
  [data (%)] = @data_percent,
  data_free = @data_free,
  [data_free (%)] = @data_free_percent,
  log_used = @log_used,
  log_free = @log_free,
  [log_free (%)] = @log_free_percent
  where db_name = '''+@dbname+'''');
FETCH NEXT FROM dbname INTO @dbname;
    END;
CLOSE dbname;
DEALLOCATE dbname;

IF(
 (
  SELECT COUNT(*)
  FROM #sizeinfo
 ) <> 1)
    SELECT @@servername AS 'ServerName', 
           db_name, 
           total, 
           data, 
           data_used, 
           [data (%)], 
           data_free, 
           [data_free (%)], 
           log, 
           log_used, 
           [log (%)], 
           log_free, 
           [log_free (%)]
    FROM #sizeinfo
    ORDER BY db_name ASC;
    ELSE
    SELECT @@servername AS 'ServerName', 
           db_name, 
           total, 
           data, 
           data_used, 
           [data (%)], 
           data_free, 
           [data_free (%)], 
           log, 
           log_used, 
           [log (%)], 
           log_free, 
           [log_free (%)]
    FROM #sizeinfo;

DROP TABLE #sizeinfo;

Result example

[MariaDB] How to fix unused shared library of user defined function error.

[MariaDB(MySQL)] How to fix unused shared library of user-defined function error.
e.g. Error related to libmy_json_udf_path.so (json user function)

Error Messages on errorlog file.

[ERROR] Can't open shared library 'libmy_json_udf_path.so' (errno: 0, cannot open shared object file: No such file or directory)

Check mysql.func table.

select * from mysql.func;

Delete data on mysql.func and mysql.install_jsonudfs.

delete from mysql.func
where dl = 'libmy_json_udf_path.so';

DROP PROCEDURE IF EXISTS mysql.install_jsonudfs;

Restart MySQL(MariaDB).

Wednesday, 13 May 2020

[MySQL] Concat string, Split string

[MySQL] Concat string, Split string

* Concat strings with a delimiter
GROUP_CONCAT

DROP TABLE IF EXISTS group_concat_test;
CREATE TABLE group_concat_test
(
  SEQ INT NOT NULL AUTO_INCREMENT,
  CITY VARCHAR(20),
  PRIMARY KEY `pk_group_concat_test` (SEQ)
) ENGINE = MEMORY;

INSERT INTO group_concat_test (CITY) VALUES ('Luxembourg') ,('London'), ('Seoul'), ('Paris');

1 2	SELECT GROUP_CONCAT(CITY) AS ALL_CITY_NAMES FROM group_concat_test;

+-------------------------------+
| ALL_CITY_NAMES                |
+-------------------------------+
| Luxembourg,London,Seoul,Paris |
+-------------------------------+
1 row in set (0.00 sec)

1 2	SELECT GROUP_CONCAT(CITY SEPARATOR '#') AS ALL_CITY_NAMES FROM group_concat_test;

+-------------------------------+
| ALL_CITY_NAMES                |
+-------------------------------+
| Luxembourg#London#Seoul#Paris |
+-------------------------------+
1 row in set (0.00 sec)

1 2	SELECT GROUP_CONCAT(CITY SEPARATOR '$') AS ALL_CITY_NAMES FROM group_concat_test;

+-------------------------------+
| ALL_CITY_NAMES                |
+-------------------------------+
| London$Luxembourg$Paris$Seoul |
+-------------------------------+
1 row in set (0.00 sec)

1	DROP TABLE IF EXISTS group_concat_test;

* Split string array using a specific delimiter
# Create a procedure, Call a procedure

DELIMITER $$
DROP PROCEDURE IF EXISTS usp_split_array_test$$
CREATE PROCEDURE usp_split_array_test(
  iValueArray   VARCHAR(100) # Input array value ex) 'value1, value2, value3'
  ,iDelimiter   CHAR(1)      # Delimiter
)
BEGIN
  DECLARE vValueArry VARCHAR(100);
  DECLARE vValue VARCHAR(10);
  DECLARE vDelimiter CHAR(1);

  SET vDelimiter := iDelimiter; #Variable for delimiter
  IF IFNULL(vDelimiter,'') = '' THEN
    SET vDelimiter := ',';
  END IF;
  
  SET vValueArry := iValueArray; #Variable for split work
  IF RIGHT(vValueArry,1) != vDelimiter THEN
    SET vValueArry := CONCAT(vValueArry,vDelimiter); # 'value1, value2, value3' --> 'value1, value2, value3,'
  END IF;
  

  DROP TEMPORARY TABLE IF EXISTS temp_split_array;
  CREATE TEMPORARY TABLE temp_split_array
  (
    SEQ INT NOT NULL AUTO_INCREMENT,
    VALUE VARCHAR(20),
    PRIMARY KEY `PK_temp_split_array` (SEQ)
  ) ENGINE = MEMORY;

  #SET @vValueArry := 'value1, value2, value3,'
  WHILE (LOCATE(vDelimiter, vValueArry) > 0)
  DO
      SET vValue := LEFT(vValueArry, LOCATE(vDelimiter,vValueArry) - 1);    
      SET vValueArry := SUBSTRING(vValueArry, LOCATE(vDelimiter,vValueArry) + 1);
      INSERT INTO temp_split_array (SEQ, VALUE)
      VALUES (null, vValue);
  END WHILE;

  SELECT * FROM temp_split_array;

  DROP TEMPORARY TABLE IF EXISTS temp_split_array;
END$$
DELIMITER ;

1	CALL usp_split_array_test('Luxembourg,London,Seoul,Paris', ',');

+-----+------------+
| SEQ | VALUE      |
+-----+------------+
|   1 | Luxembourg |
|   2 | London     |
|   3 | Seoul      |
|   4 | Paris      |
+-----+------------+
4 rows in set (0.00 sec)

1	CALL usp_split_array_test('Luxembourg;London;Seoul;Paris', ';');

+-----+------------+
| SEQ | VALUE      |
+-----+------------+
|   1 | Luxembourg |
|   2 | London     |
|   3 | Seoul      |
|   4 | Paris      |
+-----+------------+
4 rows in set (0.01 sec)

Monday, 11 May 2020

[SQL Server] Display Table size details

[SQL Server] Display Table size details

Script

SELECT SCHEMA_NAME(tbl.schema_id) AS [Schema], 
       tbl.name, 
       COALESCE(
(
    SELECT pr.name
    FROM sys.database_principals pr WITH(NOLOCK)
    WHERE pr.principal_id = tbl.principal_id
), SCHEMA_NAME(tbl.schema_id)) AS [Owner], 
       tbl.max_column_id_used AS [Columns], 
       CAST(CASE idx.index_id
                WHEN 1
                THEN 1
                ELSE 0
            END AS BIT) AS [HasClusIdx], 
       COALESCE(
(
    SELECT SUM(spart.ROWS)
    FROM sys.partitions spart WITH(NOLOCK)
    WHERE spart.object_id = tbl.object_id
          AND spart.index_id < 2
), 0) AS [RowCount], 
       COALESCE(
(
    SELECT CAST(v.low / 1024.0 AS FLOAT) * SUM(a.used_pages - CASE
                                                                  WHEN a.TYPE <> 1
                                                                  THEN a.used_pages
                                                                  WHEN p.index_id < 2
                                                                  THEN a.data_pages
                                                                  ELSE 0
                                                              END)
    FROM sys.indexes AS i WITH(NOLOCK)
         JOIN sys.partitions AS p WITH(NOLOCK) ON p.object_id = i.object_id
                                                  AND p.index_id = i.index_id
         JOIN sys.allocation_units AS a WITH(NOLOCK) ON a.container_id = p.partition_id
    WHERE i.object_id = tbl.object_id
), 0.0) / 1024 AS [IndexMB], 
       COALESCE(
(
    SELECT CAST(v.low / 1024.0 AS FLOAT) * SUM(CASE
                                                   WHEN a.TYPE <> 1
                                                   THEN a.used_pages
                                                   WHEN p.index_id < 2
                                                   THEN a.data_pages
                                                   ELSE 0
                                               END)
    FROM sys.indexes AS i WITH(NOLOCK)
         JOIN sys.partitions AS p WITH(NOLOCK) ON p.object_id = i.object_id
                                                  AND p.index_id = i.index_id
         JOIN sys.allocation_units AS a WITH(NOLOCK) ON a.container_id = p.partition_id
    WHERE i.object_id = tbl.object_id
), 0.0) / 1024 AS [DataMB], 
       tbl.create_date, 
       tbl.modify_date
FROM sys.tables AS tbl WITH(NOLOCK)
     INNER JOIN sys.indexes AS idx WITH(NOLOCK) ON(idx.object_id = tbl.object_id
                                                   AND idx.index_id < 2)
     INNER JOIN MASTER.dbo.spt_values v WITH(NOLOCK) ON(v.NUMBER = 1
                                                        AND v.TYPE = 'E')

--WHERE tbl.Name like '%tablename%' 

ORDER BY DataMB DESC;

Result example