NULL Types

 2 NULL types:

A-Values: The value exists but we don’t know what it is.

I-Values: The attribute is irrelevant.

They both have different meanings, but they are represented by the same NULL.

You should remember that when:

• Design a table.
• Design column values.
• Query table.

SAP ASE (sybase) historical monitoring tables

Adaptive Server maintains context information for each client connection that accesses the historical tables, and on each successive query on the table returns only rows that the client has not previously received (read here).

The historical monitoring tables are:

• monErrorLog
• monDeadLock
• monSysStatement
• note: When executing a procedure from a procedure, monSysStatement return a record both to the two procedures.
• monSysSQLText
• monSysPlanText

The maximum number of statement statistics returned can be tuned with statement pipe max messages.

The historical monitoring tables require the enable monitoring, statement statistics active, per object statistics active, statement pipe max messages, and statement pipe active configuration parameters to be enabled.

Each message stored adds one row to the monitoring table. Once all entries in the buffer have been used, new messages overwrite old messages in the buffers, so only the most recent messages are returned.

Filter the result set with a where clause filters from the "statement pipe max messages" number.

select SPID, ErrorMessage from master..monErrorLog

SPID      ErrorMessage

------    --------------------------------------

20        An error from SPID 20

21        An error from SPID 21

(2 rows affected)

 

-- ==========================

-- in a different session:

-- the filter return only one rows

select SPID, ErrorMessage from master..monErrorLog

where SPID=20

SPID ErrorMessage

------ --------------------------------------

20 An error from SPID 20

(1 row affected)

 

--And:

 

-- but the filtered rows was set as "returned" in the previous query and won't be return without the filter now:

select SPID, ErrorMessage from master..monErrorLog

where SPID=21

SPID ErrorMessage

------ --------------------------------------

(0 rows affected)

Install MySQL Server on Windows

Download MySQL Installer for windows and run it.

https://www.mysql.com/downloads/

Check and install requirements for MySQL products, 

Install MySQL

Configure MySQL Server,

Set password for root account, (create another user),

Create a service for MySQL Server,

Apply configuration (= execution of all the above),

Finish.

• If required: Enable Remote connection, see here .

SQL Sever - generate high index fragmentation level

--------------------------------------------------------
-- table creation:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IndexFrag]') AND type in (N'U'))
BEGIN
DROP TABLE [dbo].[IndexFrag]
END
GO
CREATE TABLE [dbo].[IndexFrag]
(
[IndexFragId] [int] NOT NULL identity(1,1),
[IndexFragInt] [int] NOT NULL,
[IndexFragText] [nvarchar](256) NULL,
) ON [PRIMARY]
GO

--------------------------------------------------------
-- data insertion:

INSERT INTO [dbo].[IndexFrag] (IndexFragInt, IndexFragText)
SELECT column_id, [name] from sys.columns;
GO
CREATE NONCLUSTERED INDEX IX_IndexFrag_IndexFragInt ON [dbo].[IndexFrag] ([IndexFragInt]);
GO

--------------------------------------------------------
-- make an operation on the index that will "add" it to sys.dm_db_index_usage_stats

select * from [dbo].[IndexFrag] where IndexFragInt > 5;

DELETE IndexFrag WHERE IndexFragId % 2= 0;
GO

--------------------------------------------------------
-- select:

select iu.database_id,
iu.object_id, OBJECT_NAME(iu.object_id) as TableName,
iu.index_id, x.name as IndexName, x.type_desc,
ips.avg_fragmentation_in_percent,
iu.user_scans, iu.system_scans, iu.user_updates
from sys.dm_db_index_usage_stats iu
INNER JOIN sys.partitions p ON iu.object_id = p.object_id and iu.index_id = p.index_id
INNER JOIN sys.objects o (nolock) ON iu.object_id = o.object_id and o.type='U'
INNER JOIN sys.indexes x  (nolock) ON x.object_id = iu.object_id AND x.index_id = iu.index_id 
and x.type_desc in ('CLUSTERED','NONCLUSTERED')
cross apply sys.dm_db_index_physical_stats (iu.database_id,iu.object_id,iu.index_id,p.partition_number,null) ips
WHERE iu.database_id = db_id('sales')
GO

--------------------------------------------------------
-- increase operations number

declare @MinutesFromSqlStart int, @i int = 0, @Operations int;

SELECT @MinutesFromSqlStart = DATEDIFF(minute, sqlserver_start_time, GETDATE()) FROM sys.dm_os_sys_info;

-- for MSSQL 2005 use this query to get @MinutesFromSqlStart (sys.dm_os_sys_info.sqlserver_start_time doesn't exists before SQL Server 2008)

--SELECT @MinutesFromSqlStart = DATEDIFF(minute, login_time, GETDATE()) --get the sqlserver start time

--FROM sys.dm_exec_sessions WHERE session_id = 1

-- get to operations number Per Day (since instance last restart) bigger than 1:
select @Operations = (cast( cast(1 as decimal(20, 2)) / cast(60 as decimal(20, 2)) * cast(@MinutesFromSqlStart as decimal(20, 2)) as int) + 1) * 2

-- for other operations number:
-- select @Operations = ?

WHILE @i < @Operations
BEGIN
INSERT INTO [dbo].[IndexFrag] (IndexFragInt, IndexFragText)
SELECT TOP 10 column_id, [name] from sys.columns;
SET @i = @i + 1;
END
GO

--------------------------------------------------------

-- select:

select iu.database_id,
iu.object_id, OBJECT_NAME(iu.object_id) as TableName,
iu.index_id, x.name as IndexName, x.type_desc,
ips.avg_fragmentation_in_percent,
iu.user_scans, iu.system_scans, iu.user_updates
from sys.dm_db_index_usage_stats iu
INNER JOIN sys.partitions p ON iu.object_id = p.object_id and iu.index_id = p.index_id
INNER JOIN sys.objects o (nolock) ON iu.object_id = o.object_id and o.type='U'
INNER JOIN sys.indexes x  (nolock) ON x.object_id = iu.object_id AND x.index_id = iu.index_id 
and x.type_desc in ('CLUSTERED','NONCLUSTERED')
cross apply sys.dm_db_index_physical_stats (iu.database_id,iu.object_id,iu.index_id,p.partition_number,null) ips
WHERE iu.database_id = db_id('sales')
GO

SAP RS - The transaction log in database RSSD is almost full

What happened?
Error when trying to start SAP Replication Server.

Error Message:
E. 2020/12/14 21:39:52. ERROR #11061 GLOBAL RS(GLOBAL RS) - generic\sts\stscol.c(2631)

Check the log for error messages from RSSD.
I. 2020/12/14 21:39:52. Message from server: Message: 7415, State 1, Severity 10 -- 'The transaction log in database RS1_RSSD is almost full. Your transaction is being suspended until space is made available in the log.

Reason:
Cause of the error: The transaction lof of the RSSD database is (almost) full.

Solution:

use master

go

sp_helpdb RS1_RSSD  --a check

sp_helpdevice rssd_log --a check

disk resize name = "rssd_log", size = "1500M"

go

alter database RS1_RSSD log on rssd_log = "1500M"

go

SAP RS - Create a new Replication

Before declaring a Replication

We should have:

• Primary database and Replicate database/s.
• Instances were set in sql.ini.
• RSSD database.

Create a primary database

use master

go

 

-- Create devices

disk init name="RepTestPrimaryDdevice", physname="/sybvol01/sap16/data/RepTestPrimaryDdevice1.dat", size="100M"

GO

 

disk init name="RepTestPrimaryDdeviceLog", physname="/sybvol01/sap16/data/RepTestPrimaryDdeviceLog1.dat", size="100M"

GO

 

-- Create a Database

create database RepTestPrimary on RepTestPrimaryDdevice='100M' log on RepTestPrimaryDdeviceLog='100M'

GO

 

use RepTestPrimary

go

-- Create a Segment

sp_addsegment RepTestPrimarySeg, RepTestPrimary, RepTestPrimaryDdevice

GO

Create a replicate database

use master

go

 

-- Create devices

disk init name="RepTestReplicateDdevice", physname="/sybvol01/sap16/data/RepTestReplicateDdevice1.dat", size="100M"

GO

 

disk init name="RepTestReplicateDdeviceLog", physname="/sybvol01/sap16/data/RepTestReplicateDdeviceLog1.dat", size="100M"

GO

 

-- Create a Database

create database RepTestReplicate on RepTestReplicateDdevice='100M' log on RepTestReplicateDdeviceLog='100M'

GO

 

use RepTestReplicate

go

-- Create a Segment

sp_addsegment RepTestReplicateSeg, RepTestReplicate, RepTestReplicateDdevice

GO

Create a replicated table

use RepTestPrimary

go

 

create table dbo.FirstTable (ID numeric(10, 0) not null, IntColumn int null, StrColumn varchar(20) null)

go

alter table dbo.FirstTable add constraint pkFirstTable primary key (ID)

go

 

-- =============================================================

use RepTestReplicate

go

 

create table dbo.FirstTable (ID numeric(10, 0) not null, IntColumn int null, StrColumn varchar(20) null)

go

alter table dbo.FirstTable add constraint pkFirstTable primary key (ID)

go

• The replicated table must have a primary key!

Create connections to the primary and replicated server

cd /sybvol01/sap16rs/REP-16_0/install/

./rs_init

Add a database to the replication system

Choose: "2. Configure a Server product"

Choose: "1. Replication Server"

Choose: "2. Add a database to the replication system"

In windows:

Replication Server Information

Choose: "1. Replication Server Information"

Choose: "1. Replication Server Name"Choose: "RS SA password"

Ctrl-a Accept and Continue

In windows:

Database Information

Add the primary database

Choose: "2. Database Information"

Choose: "1. SQL Server name"
For the primary database: set "5. Will the database be replicated" to "yes".
Ctrl-a Accept and Continue

Choose: "3. SA password"

Choose: "4. Database name"

Choose: "7. Maintenance password"

Choose: "3. Database Replication Agent"

Choose: "2. RS password"

Change RS user to sa.

Ctrl-a Accept and Continue

Ctrl-a Accept and Continue, Approve to run the task.

Execute the Replication Server tasks now? y

Running task: check the SQL Server.

Task succeeded: check the SQL Server.

Running task: verify users and their passwords.

Verified that 'sa' can log into Replication Server 'RSFOG2'.

Verified that 'sa' can log into Replication Server 'RSFOG2'.

Task succeeded: verify users and their passwords.

Running task: check the database.

Verified that database 'RepL16TestPrimary' exists.

Verified that SQL Server 'SERVERNAME' supports replication.

Added maintenance user login 'RepL16TestPrimary_maint' to database 'RepL16TestP

rimary'.

Verified that maintenance user 'RepL16TestPrimary_maint' can log into SQL Serve

r 'SERVERNAME'.

Task succeeded: check the database.

Running task: configure database for primary data.

Loading script 'rs_install_primary.sql' into database 'RepL16TestPrimary'.

.....Done

Loaded script 'rs_install_primary.sql' successfully.

Done

Granting permissions on the lastcommit functions and rs_marker.

Granting permissions on the lastcommit functions.

Granted maintenance user permissions on the lastcommit functions and rs_marker.

Granted replication role to maintenance user

Task succeeded: configure database for primary data.

Running task: configure the Replication Agent.

Task succeeded: configure the Replication Agent.

Running task: set connection to the database.

Adding database 'RepL16TestPrimary' to the replication system.

Successfully executed 'create connection'. Database 'RepL16TestPrimary' is now

managed by Replication Server 'RSFOG2'.

Task succeeded: set connection to the database.

Running task: start the Replication Agent.

Task succeeded: start the Replication Agent.

Configuration completed successfully.

Press <return> to continue.

Add a replicate database

Do the same as for the primary database, with the following changes:

1. For the replicate databases: set "5. Will the database be replicated" to "no".
2. "Database Replication Agent" definition is not required.

Ctrl-a Accept and Continue, Approve to run the task.

Execute the Replication Server tasks now? y

Running task: check the SQL Server.

Task succeeded: check the SQL Server.

Running task: verify users and their passwords.

Verified that 'sa' can log into Replication Server 'RSFOG2'.

Task succeeded: verify users and their passwords.

Running task: check the database.

Verified that database 'RepL16TestReplicate' exists.

Added maintenance user login 'RepL16TestReplicate_maint' to database 'RepL16Tes

tReplicate'.

Verified that maintenance user 'RepL16TestReplicate_maint' can log into SQL Ser

ver 'SERVERNAME'.

Task succeeded: check the database.

Running task: configure database for replicate data.

Loading script 'rs_install_replicate.sql' into database 'RepL16TestReplicate'.

...Done

Loaded script 'rs_install_replicate.sql' successfully.

Done

Granting permissions on the lastcommit functions.

Granted maintenance user permissions on the lastcommit functions.

Granted replication role to maintenance user

Task succeeded: configure database for replicate data.

Running task: set connection to the database.

Adding database 'RepL16TestReplicate' to the replication system.

Successfully executed 'create connection'. Database 'RepL16TestReplicate' is no

w managed by Replication Server 'RSFOG2'.

Task succeeded: set connection to the database.

Configuration completed successfully.

Press <return> to continue.

In windows:

Create replication definition, publication and subscription

Create a replication definition

Replication definition is defined at the primary database.

Connect to the RS:

isql64 -S RSFOG2 -Usa -Ppassword -w2048

create replication definition RepL16TestDef

with primary at SERVERNAME.RepL16TestPrimary

with all tables named 'FirstTable'

(ID numeric,

IntColumn int,

StrColumn varchar(20))

primary key(ID)

replicate minimal columns

go

Expected output:

Replication definition 'RepL16TestDef' is created.

Create a publication

Publication is defined at the primary database.

Connect to the RS:

isql64 -S RSFOG2 -Usa -Ppassword -w2048

create publication RepL16TestPub with primary at SERVERNAME.RepL16TestPrimary

go

Expected output:

-- Publication 'RepL16TestPub' for SERVERNAME.RepL16TestPrimary is created.

Create a subscription

Subscription is defined at the replicate database.

Connect to the RS:

isql64 -S RSFOG2 -Usa -Ppassword -w2048

create subscription RepL16TestSub for RepL16TestDef with replicate at SERVERNAME.RepL16TestReplicate

go

Expected output:

Subscription 'RepL16TestSub' is in the process of being created.

check subscription RepL16TestSub for RepL16TestDef with replicate at SERVERNAME.RepL16TestReplicate

go

Expected output:

Subscription RepL16TestSub is VALID at the replicate.

Subscription RepL16TestSub is VALID at the primary.

Validation and checks for the connections and the definitions

Connect to the RS:

isql64 -S RSFOG2 -Usa -Ppassword -w2048

1> admin who

2> go

Spid Name State Info

---- --------------- -------------------- ------------------------------------------------------------

...

304 DSI EXEC Awaiting Command 109(1) SERVERNAME.RepL16TestPrimary

299 DSI Awaiting Message 109 SERVERNAME.RepL16TestPrimary

301 DIST Awaiting Wakeup 109 SERVERNAME.RepL16TestPrimary

303 SQT Awaiting Wakeup 109:1 DIST SERVERNAME.RepL16TestPrimary

300 SQM Awaiting Message 109:1 SERVERNAME.RepL16TestPrimary

298 SQM Awaiting Message 109:0 SERVERNAME.RepL16TestPrimary

302 REP AGENT Awaiting Command SERVERNAME.RepL16TestPrimary

311 DSI EXEC Awaiting Command 110(1) SERVERNAME.RepL16TestReplicate

310 DSI Awaiting Message 110 SERVERNAME.RepL16TestReplicate

309 SQM Awaiting Message 110:0 SERVERNAME.RepL16TestReplicate

...

Connect to the RSSD in the ASE:

isql64 -S SERVERNAME -Usa -Ppassword -w2048 -X

1> use RSFOG2_RSSD

2> go

1> select * from rs_databases

2> go

dsname dbname dbid dist_status src_status ....

----------------- -------------------- ----------- ----------- ----------- ....

...

SERVERNAME RepL16TestPrimary 109 1 1 ....

SERVERNAME RepL16TestReplicate 110 1 0 ....

...

(8 rows affected)

• rs_helpdb can also be good: exec rs_helpdb.

Add database and table to replication

Connect to the primary database in the ASE:

isql64 -S SERVERNAME -Usa -Ppassword -w2048 -X

1> USE RepL16TestPrimary

2> go

1> -- displays the current replication status of a table:

2> sp_setreptable FirstTable

3> go

The replication status for 'FirstTable' is currently false, owner_off, primary key = 'pkFirstTable'.

(return status = 0)

1> -- add table to replication

2> sp_setreptable FirstTable, true

3> go

The replication status for 'FirstTable' is set to true, owner_off.

(return status = 0)

1> -- displays the current replication status of a table:

2> sp_setreptable FirstTable

3> go

The replication status for 'FirstTable' is currently true, owner_off, primary key = 'pkFirstTable'.

(return status = 0)

1> -- check replication status for database:

2> sp_reptostandby RepL16TestPrimary

3> go

The replication status for database 'RepL16TestPrimary' is 'NONE'.

The replication mode for database 'RepL16TestPrimary' is 'off'.

(return status = 0)

1> -- activate replication status for database

2> sp_reptostandby RepL16TestPrimary, 'all'

3> go

Warning: The execution of the stored procedure sp_reptostandby will take time to process all the tables and all the text/image columns in database 'RepL16TestPrimary'.

The replication status for database 'RepL16TestPrimary' has been set to 'ALL'.

(return status = 0)

1> -- check replication status for database

2> sp_reptostandby RepL16TestPrimary

3> go

The replication status for database 'RepL16TestPrimary' is 'ALL'.

The replication mode for database 'RepL16TestPrimary' is 'off'.

(return status = 0)

Grant permissions to maint user on replicated DB

Connect to the replicate database in the ASE:

isql64 -S SERVERNAME -Usa -Ppassword -w2048 -X

use RepL16TestReplicate

go

GRANT INSERT ON dbo.FirstTable TO RepL16TestReplicate_maint

go

GRANT UPDATE ON dbo.FirstTable TO RepL16TestReplicate_maint

go

GRANT DELETE ON dbo.FirstTable TO RepL16TestReplicate_maint

go

Activate Rep Agent

-- Connect to the primary database and to the RSSD in the ASE:

isql64 -S SERVERNAME -Usa -Ppassword -w2048 -X

1> use RepL16TestPrimary

2> go

1> -- let RepAgent sends changes to the replicate database

2> sp_config_rep_agent RepL16TestPrimary, send_warm_standby_xacts, true

3> go

Parameter_Name Default_Value Config_Value Run_Value

----------------------- ------------- ------------ ---------

send warm standby xacts false true false

(1 row affected)

Replication Agent configuration changed for database 'RepL16TestPrimary'. The changes will take effect the next time the Replication Agent thread is started.

(return status = 0)

1> -- config connect dataserver to the ASE name

2> sp_config_rep_agent RepL16TestPrimary, 'connect dataserver', 'SERVERNAME'

3> go

Parameter_Name Default_Value Config_Value Run_Value

------------------ ------------- ------------ ------------

connect dataserver SERVERNAME SERVERNAME SERVERNAME 

(1 row affected)

Replication Agent configuration changed for database 'RepL16TestPrimary'. The changes will take effect the next time the Replication Agent thread is started.

(return status = 0)

1> sp_config_rep_agent RepL16TestPrimary, 'enable', 'RSFOG2', 'sa', 'password'

2> go

Msg 18382, Level 16, State 1:

Server 'SERVERNAME', Procedure 'sp_config_rep_agent', Line 664:

Database 'RepL16TestPrimary' is already configured to use Replication Agent. Request to enable Replication Agent has been ignored.

(return status = 1)

→ Bebause it already done in rs_init. No problem. 

1> -- stop RepAgent

2> sp_stop_rep_agent RepL16TestPrimary

3> go

The Replication Agent thread for database 'RepL16TestPrimary' is being stopped.

(return status = 0)

1> use RSFOG2_RSSD

2> go

1> rs_zeroltm 'SERVERNAME', 'RepL16TestPrimary'

2> go

Locater has been reset to zero.

(return status = 0)

1> use RepL16TestPrimary

2> go

1> dbcc settrunc(ltm, valid)

2> go

secondary trunc page secondary trunc state dbrepstat generation id database id database name ltl version

-------------------- --------------------- --------- ------------- ----------- ------------------------------ -----------

25757 1 175 0 14 RepL16TestPrimary 760

1> -- truncate the log

2> dump tran RepL16TestPrimary with no_log

3> go

1> -- grant permissions

2> grant role replication_role to sa

3> go

1>

2> -- start RepAgent

3> sp_start_rep_agent RepL16TestPrimary

4> go

Replication Agent thread is started for database 'RepL16TestPrimary'.

(return status = 0)

View information about all definitions and declarations

In the primary database:

sp_config_rep_agent RepL16TestPrimary

go

sp_help_rep_agent "RepL16TestPrimary"

go

sp_displaylogin sa

go

In the Replication Server:

admin who

go

admin who_is_down

go

In RSSD:

rs_helpdbrep DETestDB_repdef, win16, DETestDBPrimary

go

rs_helpcheckrepdef

go

rs_helprep DETestDB_repdef

go

rs_helpdbsub DETestDB_pub_subscript, ubuntu16, DETestDB

go

select * from rs_subscriptions A

go

select * from rs_dbreps B

go

select * from rs_sites --B

go

select * from rs_databases --C

go

Check the replication

Connect to the primary database and to the replicate database in the ASE:

isql64 -S SERVERNAME -Usa -Ppassword -w2048 -X

1. Insert data to the primary table.
2. Check if it was replicated in the replicate database.

1> use RepL16TestPrimary

2> go

1> INSERT INTO dbo.FirstTable ( ID, IntColumn, StrColumn ) VALUES ( 1, 1, 'Str 1' )

2> go

(1 row affected)

1> select * from dbo.FirstTable

2> go

ID IntColumn StrColumn

------------- ----------- --------------------

1 1 Str 1

(1 row affected)

1> use RepL16TestReplicate

2> go

1> select * from dbo.FirstTable

2> go

ID IntColumn StrColumn

------------- ----------- --------------------

1 1 Str 1

(1 row affected)

MySQL troubleshooting - Can’t login remotely

The problem:

Can’t login remotely, can login only from the server itself.

The reason: 

Permissions for remote login are missing.

Solution – Linux:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'second_server’;

Check with:

SELECT host, user, authentication_string  FROM user ;

Solution – Windows:

From MySQL Workbench, on the instance connection, Users and Privilages, choose the user (root) and change “Limit to host matching” to “%”.

MySQL - Change user’s password

mysql>  ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

MySQL - Show databases in the instance

SHOW DATABASES;

SELECT TABLE_SCHEMA, 

COUNT(TABLE_NAME) as tableCount, 

        SUM(Data_Length)/1048576 as tablespaceUsedMB, 

        SUM(Index_Length)/1048576 as indexMB, 

        SUM(DATA_LENGTH+INDEX_LENGTH)/1048576 as totalDatabaseSize 

FROM INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA;

MySQL - Find where the error log is stored

mysql> SHOW VARIABLES LIKE 'log_error’;

Show the values of MySQL system variables

mysql> SHOW VARIABLES;

For specific variables:

mysql> SHOW VARIABLES LIKE 'TEXT';

Variables list is available here:

https://dev.mysql.com/doc/refman/8.0/en/show-variables.html

Restart MySQL service

 sudo service mysql restart

Login/exit to MySQL

Basic login to mysql:

> mysql -u root -p

-u = user

-p = password

-- each command should end with ";"

mysql> select * from sys.sys_config;

Exit from mysql:

mysql> quit

MySQL Shell - Change shell option

shell.options.sandboxDir='C:\Users\IGittler\MySQL\mysql-sandboxes'

MySQL Shell - Switch to other mode

 Switch to other mode in MySQL Shell – by \<mode>

• \sql
• \js
• …

MySQL Shell - Dba.deploySandboxInstance: ERROR: Error creating sandbox: Could not find mysqld executable. Make sure it is on the %PATH% environment variable.

Error message:

MySQL  JS > dba.deploySandboxInstance(3310)

A new MySQL sandbox instance will be created on this host in

C:\Users\IGittler\MySQL\mysql-sandboxes\3310

Warning: Sandbox instances are only suitable for deploying and running on your local machine for testing purposes and are not accessible from external networks.

Please enter a MySQL root password for the new instance: ******

Deploying new MySQL instance...

Dba.deploySandboxInstance: ERROR: Error creating sandbox: Could not find mysqld executable. Make sure it is on the %PATH% environment variable. (RuntimeError)

Solutions:

1. Check path definitions.
2. MySQL Server should be installed on the machine.

MySQL Shell - deploySandboxInstance

 deploySandboxInstance creates a new MySQL Server instance on localhost.

dba.deploySandboxInstance(port[, options])

MySQL  JS > dba.deploySandboxInstance(3310)

MySQL Shell

MySQL Shell provides an interactive code execution mode, where you type code at the MySQL Shell prompt and each entered statement is processed, with the result of the processing printed onscreen.

MySQL Shell is an advanced client and code editor for MySQL Server.

In addition to the provided SQL functionality, MySQL Shell provides scripting capabilities for JavaScript and Python and includes APIs for working with MySQL. 

MySQL Shell is a component that you can install separately.

Install MySQL Shell: 

download from: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install.html

Extract the downloaded zip file.

Start MySQL Shell:

Run \bin\mysqlsh.exe.

MySQL Shell 8.0 documentation:

https://dev.mysql.com/doc/mysql-shell/8.0/en/

MySQL: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

 Error message:

You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 

Solution:

add before the update (/delete) statement:

SET SQL_SAFE_UPDATES=0;

MySQL while loop

 DELIMITER $$

CREATE PROCEDURE doWhileInsertCity2(

    TimesToRun INT

)

BEGIN

    

    DECLARE counter INT DEFAULT 1;

    WHILE counter <= TimesToRun DO

        -- do something ...

        

        SET counter = counter + 1;

    END WHILE;

END$$

DELIMITER ;

execute the loop:

CALL doWhileInsertCity2(15);

MySQL - executing stored procedures - CALL

 CALL MyStoredProcedure();

Call SP with parameters:

CALL MyStoredProcedure(15, '2015-03-03');

MySQL - Create stored procedures

 DELIMITER $$

CREATE PROCEDURE doWhileInsertCity2(

    -- input parameters

)

BEGIN

    

-- do something ...

END$$

DELIMITER ;

MySQL delay

Delay for 20 seconds:

SELECT SLEEP(20);

or:

DO SLEEP(20);

SQL Server: Simulate deadlock

For this deadlock simulation we need to have 2 tables, each one of them with at least one record:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AA]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[AA](

[A] [int] NULL

) ON [PRIMARY]

END

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BB]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[BB](

[B] [int] NULL

) ON [PRIMARY]

END

GO

INSERT INTO AA (A) VALUES (1);

GO

INSERT INTO BB (B) VALUES (2);

GO

----------

Now we will open two sessions:

Session #1	Session #2
BEGIN TRANSACTION; Update   AA Set    A = 3;   WAITFOR DELAY '00:00:03';   Update   BB Set    B = 4;   ROLLBACK TRANSACTION;	BEGIN TRANSACTION;   Update   BB Set    B = 4;   WAITFOR DELAY '00:00:03';   Update   AA Set    A = 3;   ROLLBACK TRANSACTION;

Start execute Session #1 and immediately execute Session #2.

The 2 sessions are waiting for each other: Session #1 lock AA, Session #2 lock BB.

This is a deadlock.

One of them will be chosen (By SQL Server) as a deadlock victim and will be "killed".