SOFTWARE TESTING

emran

The MySQL installation instructions on ArtifactHub are incomplete, instead follow the official documentations on https://dev.mysql.com/doc/mysql-operator/en/mysql-operator-installation-helm.html which are currently documented as,

helm install my-mysql-operator mysql-operator/mysql-operator \
  --namespace mysql-operator --create-namespace

You have to specify the namespace with -n or --namespace and if it's not created you'll have to either create it or use --create-namespace

emran

Upgrade to at least PostgreSQL v14 and run

GRANT read_all_data, write_all_data TO myuser;

emran

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver16#remarks-for-dml-triggers

An AFTER trigger is run only after the triggering SQL statement has run successfully. This successful execution includes all referential cascade actions and constraint checks associated with the object updated or deleted.

Deleting from ChildTable first, then from ParentTable, will always execute the child trigger before the parent one, because triggers fire per statement, not per transaction or batch.
Cascading foreign-keys you can test quite easily.

CREATE TABLE Parent (id int identity PRIMARY KEY);
CREATE TABLE Child (id int identity PRIMARY KEY, pId int REFERENCES Parent(id) ON DELETE CASCADE);

CREATE TRIGGER trParent ON Parent AFTER DELETE AS
PRINT 'trParent';

DECLARE @i int;

SELECT @i = COUNT() FROM Parent;

PRINT @i;

SELECT @i = COUNT() FROM Child;

PRINT @i;

CREATE TRIGGER trChild ON Child AFTER DELETE AS

PRINT 'trChild';
DECLARE @i int;
SELECT @i = COUNT(*) FROM Parent;
PRINT @i;
SELECT @i = COUNT(*) FROM Child;
PRINT @i;

INSERT Parent DEFAULT VALUES;
INSERT Child(pId) VALUES (SCOPE_IDENTITY());
DELETE Parent;

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=55f40682e31038e65fa374226d36d211

This prints

trChild
0
0
trParent
0
0

So the Child trigger is fired first, then the Parent trigger. But there are no rows in either table before even the first trigger was called. To access the original data you instead use the deleted table in the trigger.

Note that if there are no rows to cascade, it appears the child trigger https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=6af4a818d77b0548175e638c543164e0 .

emran

Years ago, MySQL supported a way to initialize replication the way you're describing. You configure a replica, and basically tell it to suck the current state of data from the source node. I have never used this feature, and it was quickly removed from the product. I can't even find documentation on this feature now. I think it would have to lock the source table to get the initial state of data, so it would have a pretty high risk that someone could cause an outage by accident just by enabling a new replica. I assume that's why the feature vanished.

Recently a plugin called MySQL Clone appears to do the same thing, but it only works with InnoDB tables as far as I can tell. See https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-innodb-cluster-clone-deployment.html

Creating a replica does not need to cause downtime on the source instance. At my last job, we did this frequently to add or repair replicas. We used https://www.percona.com/software/mysql-database/percona-xtrabackup to create a hot backup from the source node, and transferred that backup to the replica server, then started replication using the binary log coordinates recorded with the backup. Once we converted all our clusters to GTID, that got even easier.

But I think your task is even easier than this. You don't need to use replication to convert the storage engine of a table. I would use https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html , a free tool that allows you to use ALTER TABLE on a table with virtually no downtime.

emran

Is it possible to examine the execution plan for a SQL statement executed inside a PLSQL block?

DECLARE
    l_count PLS_INTEGER;
BEGIN
    SELECT COUNT(1) INTO l_count
    FROM foo;
END;
/

For regular SQL I would normally run the following to check the execution plan:

select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

However, this just reports:

NOTE: cannot fetch plan for SQL_ID: 3q0sujncq54wy, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER; 
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

emran

I ran into an error during the install of Microsoft SQL Server 2014 (SP3-CU4-GDR) (KB4583462) - 12.0.6433.1

During the script upgrades to the system database the upgrade ran into:

This is from the SQL error log:

2022-05-22 02:22:52.35 spid11s Error: 515, Severity: 16, State: 2.
2022-05-22 02:22:52.35 spid11s Cannot insert the value NULL into column 'packagedata', table 'msdb.dbo.sysssispackages'; column does not allow nulls. UPDATE fails.

2022-05-22 02:22:52.35 spid11s Error: 912, Severity: 21, State: 2.
2022-05-22 02:22:52.35 spid11s Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 515, state 2, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2022-05-22 02:22:52.35 spid11s Error: 3417, Severity: 21, State: 3.
2022-05-22 02:22:52.35 spid11s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

2022-05-22 02:22:52.35 spid11s SQL Server shutdown has been initiated

emran

The problem seems to be a mis-estimate here:

->  Index Scan using "final_bill_deductionPK" on final_bill_deduction b  (... rows=1 ...) (actual ... rows=8777 ...)
      Index Cond: ((fin_year = '2022-2023'::bpchar) AND (flag = 'N'::bpchar))
      Buffers: shared hit=68502234

If a plain ANALYZE on that table does not help, you could try extended statistics:

CREATE STATISTICS year_flag (dependencies)
   ON fin_year, flag FROM final_bill_deduction;
ANALYZE final_bill_deduction;

emran

Something like:

CREATE VIEW v AS (
    SELECT t.TestRecord_ID
         , ...
         , s1.TestRecord_ID IS NOT NULL AS Has_Subtest_1_Data
         , s2.TestRecord_ID IS NOT NULL AS Has_Subtest_2_Data
         , ...
    FROM TestRecords t
    LEFT JOIN Subtest_1 s1
        ON ...
    LEFT JOIN Subtest_2 s2
        ON ... 
    ...

EDIT: SQL server does not seem to allow for boolean expressions. Added CASE expression

CREATE VIEW [TestRecords_View] AS
SELECT DISTINCT
   [T0].[TestRecord_ID],
CASE WHEN [T1].[TestRecord_ID] IS NOT NULL THEN 1 ELSE 0 END AS Has_Subtest_1_Data,

CASE WHEN [T2].[TestRecord_ID] IS NOT NULL THEN 1 ELSE 0 END AS Has_Subtest_2_Data
FROM [TestRecords] AS [T0]

LEFT OUTER JOIN [Subtest_1] AS [T1]

ON [T0].[TestRecord_ID] = [T1].[TestRecord_ID]

LEFT OUTER JOIN [Subtest_2] AS [T2]

ON [T0].[TestRecord_ID] = [T2].[TestRecord_ID];

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=d5fe0c98711a9071e38e24fd73b1481b

emran

You probably also saw the hint:

HINT: There is a column named "a" in table "qwe", but it cannot be referenced from this part of the query.

You cannot reference a column value that does not exist, yet.

Instead, spell out the value again. Or use a subselect or CTE to provide a only once:

INSERT INTO qwe (a, completed)
VALUES (1000, repeat('0', 1000)::varbit);
INSERT INTO qwe (a, completed)

SELECT a, repeat('0', a)::varbit

FROM  (SELECT 1000) sub(a);

db<>fiddle https://dbfiddle.uk/?rdbms=postgres_14&fiddle=05d049a8a94de40483edb02ac4429706

Also, you need an explicit cast to varbit.

emran

I want to have only one null value for some_value when enabled is false.

A minimalist https://www.postgresql.org/docs/current/indexes-partial.html unique index can do that:

CREATE UNIQUE INDEX a_some_value_not_enabled_uni_idx ON a (1)  -- constant
WHERE some_value IS NULL AND NOT enabled;

This index will hold at most one row.

I want to ensure this null is for all foreign keys. So I can have x*y null values, but only one per couple of (x, y).

A partial https://www.postgresql.org/docs/current/indexes-multicolumn.html unique index:

CREATE UNIQUE INDEX a_special_uni_idx ON a (a_id, b_id)
WHERE some_value IS NULL;

This allows each combination of (a_id, b_id) only once when some_value is null. Note that rows with NULL values in either a_id or b_id evade this restriction. See:

https://stackoverflow.com/a/8289253/939860

I guess you want to allow at most one NULL value in some_value per (a_id, b_id).

I also guess your question is unclear.

emran

@emran

Best posts made by emran

Latest posts made by emran