MDEV-24093: Detect during mysql_upgrade if type_mysql_json.so is needed and load it

a. The change makes `mariadb-upgrade` detect if `MYSQL_JSON` data type is needed.
b. Install the data type if it's not installed.
c. Uninstalls the data type once finished.
d. Create `.opt` and `.inc` files `have_type_mysql_json` and adapt the
tests

Reviewed by: vicentiu@mariadb.org

10 files changed, 440 insertions, 14 deletions

diff --git a/client/mysql_upgrade.c b/client/mysql_upgrade.c
index 666947d7b6c..55afdbf9671 100644
--- a/client/mysql_upgrade.c
+++ b/client/mysql_upgrade.c
@@ -51,6 +51,7 @@ static my_bool upgrade_from_mysql;
 
 static DYNAMIC_STRING ds_args;
 static DYNAMIC_STRING conn_args;
+static DYNAMIC_STRING ds_plugin_data_types;
 
 static char *opt_password= 0;
 static char *opt_plugin_dir= 0, *opt_default_auth= 0;
@@ -187,6 +188,7 @@ static void free_used_memory(void)
 
   dynstr_free(&ds_args);
   dynstr_free(&conn_args);
+  dynstr_free(&ds_plugin_data_types);
   if (cnf_file_path)
     my_delete(cnf_file_path, MYF(MY_WME));
 }
@@ -965,6 +967,73 @@ static my_bool from_before_10_1()
 }
 
 
+static void uninstall_plugins(void)
+{
+  if (ds_plugin_data_types.length)
+  {
+    char *plugins= ds_plugin_data_types.str;
+    char *next= get_line(plugins);
+    char buff[512];
+    while(*plugins)
+    {
+      if (next[-1] == '\n')
+        next[-1]= 0;
+      verbose("uninstalling plugin for %s data type", plugins);
+      strxnmov(buff, sizeof(buff)-1, "UNINSTALL SONAME ", plugins,"", NULL);
+      run_query(buff, NULL, TRUE);
+      plugins= next;
+      next= get_line(next);
+    }
+  }
+}
+/**
+  @brief     Install plugins for missing data types
+  @details   Check for entries with "Unknown data type" in I_S.TABLES,
+             try to load plugins for these tables if available (MDEV-24093)
+
+  @return    Operation status
+    @retval  TRUE  - error
+    @retval  FALSE - success
+*/
+static int install_used_plugin_data_types(void)
+{
+  DYNAMIC_STRING ds_result;
+  const char *query = "SELECT table_comment FROM information_schema.tables"
+                      " WHERE table_comment LIKE 'Unknown data type: %'";
+  if (init_dynamic_string(&ds_result, "", 512, 512))
+    die("Out of memory");
+  run_query(query, &ds_result, TRUE);
+
+  if (ds_result.length)
+  {
+    char *line= ds_result.str;
+    char *next= get_line(line);
+    while(*line)
+    {
+      if (next[-1] == '\n')
+        next[-1]= 0;
+      if (strstr(line, "'MYSQL_JSON'"))
+      {
+        verbose("installing plugin for MYSQL_JSON data type");
+        if(!run_query("INSTALL SONAME 'type_mysql_json'", NULL, TRUE))
+        {
+          dynstr_append(&ds_plugin_data_types, "'type_mysql_json'");
+          dynstr_append(&ds_plugin_data_types, "\n");
+          break;
+        }
+        else
+        {
+          fprintf(stderr, "... can't %s\n", "INSTALL SONAME 'type_mysql_json'");
+          return 1;
+        }
+      }
+      line= next;
+      next= get_line(next);
+    }
+  }
+  dynstr_free(&ds_result);
+  return 0;
+}
 /*
   Check for entries with "Unknown storage engine" in I_S.TABLES,
   try to load plugins for these tables if available (MDEV-11942)
@@ -1218,7 +1287,8 @@ int main(int argc, char **argv)
   }
 
   if (init_dynamic_string(&ds_args, "", 512, 256) ||
-      init_dynamic_string(&conn_args, "", 512, 256))
+      init_dynamic_string(&conn_args, "", 512, 256) ||
+      init_dynamic_string(&ds_plugin_data_types, "", 512, 256))
     die("Out of memory");
 
   if (handle_options(&argc, &argv, my_long_options, get_one_option))
@@ -1281,6 +1351,7 @@ int main(int argc, char **argv)
   */
   if (run_mysqlcheck_upgrade(TRUE) ||
       install_used_engines() ||
+      install_used_plugin_data_types() ||
       run_mysqlcheck_views() ||
       run_sql_fix_privilege_tables() ||
       run_mysqlcheck_fixnames() ||
@@ -1288,6 +1359,7 @@ int main(int argc, char **argv)
       check_slave_repositories())
     die("Upgrade failed" );
 
+  uninstall_plugins();
   verbose("Phase %d/%d: Running 'FLUSH PRIVILEGES'", ++phase, phases_total);
   if (run_query("FLUSH PRIVILEGES", NULL, TRUE))
     die("Upgrade failed" );
diff --git a/mysql-test/include/have_type_mysql_json.inc b/mysql-test/include/have_type_mysql_json.inc
new file mode 100644
index 00000000000..bea1f52cb61
--- /dev/null
+++ b/mysql-test/include/have_type_mysql_json.inc
@@ -0,0 +1,3 @@
+if (!$TYPE_MYSQL_JSON_SO) {
+  skip Need MYSQL_JSON plugin;
+}
diff --git a/mysql-test/include/have_type_mysql_json.opt b/mysql-test/include/have_type_mysql_json.opt
new file mode 100644
index 00000000000..0676b7832cf
--- /dev/null
+++ b/mysql-test/include/have_type_mysql_json.opt
@@ -0,0 +1,2 @@
+--loose-type_mysql_json
+--plugin-load-add=$TYPE_MYSQL_JSON_SO
diff --git a/mysql-test/main/mysql_json_mysql_upgrade.result b/mysql-test/main/mysql_json_mysql_upgrade.result
new file mode 100644
index 00000000000..05863bb3591
--- /dev/null
+++ b/mysql-test/main/mysql_json_mysql_upgrade.result
@@ -0,0 +1,198 @@
+#
+# MDEV-24093: Detect during mysql_upgrade if type_mysql_json.so 
+# is needed and load it
+#
+SET NAMES utf8;
+show create table mysql_json_test;
+ERROR HY000: Unknown data type: 'MYSQL_JSON'
+Phase 1/7: Checking and upgrading mysql database
+Processing databases
+mysql
+mysql.column_stats                                 OK
+mysql.columns_priv                                 OK
+mysql.db                                           OK
+mysql.event                                        OK
+mysql.func                                         OK
+mysql.global_priv                                  OK
+mysql.gtid_slave_pos                               OK
+mysql.help_category                                OK
+mysql.help_keyword                                 OK
+mysql.help_relation                                OK
+mysql.help_topic                                   OK
+mysql.index_stats                                  OK
+mysql.innodb_index_stats
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+mysql.innodb_table_stats
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+mysql.plugin                                       OK
+mysql.proc                                         OK
+mysql.procs_priv                                   OK
+mysql.proxies_priv                                 OK
+mysql.roles_mapping                                OK
+mysql.servers                                      OK
+mysql.table_stats                                  OK
+mysql.tables_priv                                  OK
+mysql.time_zone                                    OK
+mysql.time_zone_leap_second                        OK
+mysql.time_zone_name                               OK
+mysql.time_zone_transition                         OK
+mysql.time_zone_transition_type                    OK
+mysql.transaction_registry
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+
+Repairing tables
+mysql.innodb_index_stats
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+mysql.innodb_table_stats
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+mysql.transaction_registry
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+Phase 2/7: Installing used storage engines... Skipped
+installing plugin for MYSQL_JSON data type
+Phase 3/7: Fixing views
+mysql.user                                         OK
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
+Processing databases
+information_schema
+mtr
+mtr.global_suppressions                            OK
+mtr.test_suppressions                              OK
+performance_schema
+test
+test.mysql_json_test                               Needs upgrade
+test.mysql_json_test_big                           Needs upgrade
+
+Repairing tables
+test.mysql_json_test                               OK
+test.mysql_json_test_big                           OK
+uninstalling plugin for 'type_mysql_json' data type
+Phase 7/7: Running 'FLUSH PRIVILEGES'
+OK
+show create table mysql_json_test;
+Table	Create Table
+mysql_json_test	CREATE TABLE `mysql_json_test` (
+  `description` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `expected` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `actual` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
+select * from mysql_json_test;
+description	expected	actual
+Raw integers as JSON	0	0
+Raw integers as JSON	-127	-127
+Raw integers as JSON	128	128
+Raw integers as JSON	32767	32767
+Raw integers as JSON	-32768	-32768
+Raw integers as JSON	65535	65535
+Raw integers as JSON	65536	65536
+Raw integers as JSON	-2147483648	-2147483648
+Raw integers as JSON	2147483647	2147483647
+Raw integers as JSON	4294967295	4294967295
+Raw integers as JSON	-9223372036854775807	-9223372036854775807
+Raw integers as JSON	9223372036854775807	9223372036854775807
+Raw integers as JSON	18446744073709551615	18446744073709551615
+Raw doubles as JSON	3.14	3.14
+Raw doubles as JSON	-5678.987	-5678.987
+Raw doubles as JSON	-2.2250738585072014e-308	-2.2250738585072014e-308
+Raw doubles as JSON	2.2250738585072014e-308	2.2250738585072014e-308
+Simple JSON test	{"key1": "val1", "key2": "val2"}	{"key1": "val1", "key2": "val2"}
+Raw doubles as JSON	0.0	0.0
+Simple Array as Value	{"a": [1, 2], "b": ["x", "y"]}	{"a": [1, 2], "b": ["x", "y"]}
+Simple Array as Base Key	[1, 2, 3, 4, 5, [], "a", "b", "c"]	[1, 2, 3, 4, 5, [], "a", "b", "c"]
+GeoJSON	{"type": "MultiPoint", "coordinates": [[1, 1], [2, 2], [3, 3]]}	{"type": "MultiPoint", "coordinates": [[1, 1], [2, 2], [3, 3]]}
+GeoJSON	{"type": "LineString", "coordinates": [[0, 5], [5, 10], [10, 15]]}	{"type": "LineString", "coordinates": [[0, 5], [5, 10], [10, 15]]}
+GeoJSON	{"type": "GeometryCollection", "geometries": []}	{"type": "GeometryCollection", "geometries": []}
+GeoJSON	{"type": "Point", "coordinates": [11.1111, 12.22222]}	{"type": "Point", "coordinates": [11.1111, 12.22222]}
+Opaque Types: opaque_mysql_type_set	"b,c"	"b,c"
+Opaque Types: opaque_mysql_type_enum	"b"	"b"
+Opaque Types: opaque_mysql_type_date	"2015-01-15"	"2015-01-15"
+Opaque Types: opaque_mysql_type_time	"23:24:25.000000"	"23:24:25.000000"
+Opaque Types: opaque_mysql_type_datetime	"2015-01-15 23:24:25.000000"	"2015-01-15 23:24:25.000000"
+Opaque Types: opaque_mysql_type_geom	{"type": "Point", "coordinates": [1, 1]}	{"type": "Point", "coordinates": [1, 1]}
+Opaque Types: opaque_mysql_type_bit	"base64:type16:yv4="	"base64:type16:yv4="
+Opaque Types: opaque_mysql_type_year	"base64:type13:MjAxOQ=="	"base64:type13:MjAxOQ=="
+Opaque Types: opaque_mysql_type_blob	"base64:type252:yv66vg=="	"base64:type252:yv66vg=="
+Opaque Types: opaque_mysql_type_longblob	"base64:type251:yv66vg=="	"base64:type251:yv66vg=="
+Opaque Types: opaque_mysql_type_mediumblob	"base64:type250:yv66vg=="	"base64:type250:yv66vg=="
+Opaque Types: opaque_mysql_type_tinyblob	"base64:type249:yv66vg=="	"base64:type249:yv66vg=="
+Opaque Types: opaque_mysql_type_varchar	"base64:type15:Zm9v"	"base64:type15:Zm9v"
+DateTime as Raw Value: 	"2015-01-15 23:24:25.000000"	"2015-01-15 23:24:25.000000"
+Opaque Types: opaque_mysql_type_varbinary	"base64:type15:YWJj"	"base64:type15:YWJj"
+Opaque Types: opaque_mysql_type_binary	"base64:type254:YWJjAAAAAAAAAA=="	"base64:type254:YWJjAAAAAAAAAA=="
+DateTime as Raw Value: 	"23:24:25.000000"	"23:24:25.000000"
+DateTime as Raw Value: 	"2015-01-15"	"2015-01-15"
+DateTime as Raw Value: 	"2015-01-15 23:24:25.000000"	"2015-01-15 23:24:25.000000"
+UTF8 Characters: 	{"Person": "EMP", "details": {"Name": "Anel Husaković - test: đžšćč"}}	{"Person": "EMP", "details": {"Name": "Anel Husaković - test: đžšćč"}}
+UTF8 Characters: 	"Anel Husaković - test: đžšćč"	"Anel Husaković - test: đžšćč"
+UTF8 Characters: 	{"Name": "Anel Husaković - test: đžšćč"}	{"Name": "Anel Husaković - test: đžšćč"}
+UTF8 Characters: 	{"details": {"Name": "Anel Husaković - test: đžšćč"}, "\"Anel Husaković - test: đžšćč\"": "EMP"}	{"details": {"Name": "Anel Husaković - test: đžšćč"}, "\"Anel Husaković - test: đžšćč\"": "EMP"}
+Special Characters: 	{"{": "}"}	{"{": "}"}
+Special Characters: 	"key1 - with \" val "	"key1 - with \" val "
+Special Characters: 	{"key1 and \n\"key2\"": "val1\t val2"}	{"key1 and \n\"key2\"": "val1\t val2"}
+Special Characters: 	"'"	"'"
+Special Characters: 	"q"	"q"
+Special Characters: 	{"[": "]"}	{"[": "]"}
+Special Characters: 	{"{": "}"}	{"{": "}"}
+Empty JSON Object/Array: 	[]	[]
+Special Characters: 	"some_string"	"some_string"
+Special Characters: 	"'"	"'"
+Special Characters: 	"\""	"\""
+Special Characters: 	""	""
+Special Characters: 	"'"	"'"
+Special Characters: 	"''"	"''"
+Empty JSON Object/Array: 	{}	{}
+Special Characters: 	"f"	"f"
+Special Characters: 	"\\"	"\\"
+Special Characters: 	"\n"	"\n"
+Special Characters: 	"\f"	"\f"
+Special Characters: 	"\t"	"\t"
+Special Characters: 	"\r"	"\r"
+Special Characters: 	"\b"	"\b"
+Special Characters: 	"\\b"	"\\b"
+Special Characters: 	{"key \n key": "val \n val"}	{"key \n key": "val \n val"}
+Special Characters: 	{"key \f key": "val \f val"}	{"key \f key": "val \f val"}
+Special Characters: 	{"key \t key": "val \t val"}	{"key \t key": "val \t val"}
+Special Characters: 	{"key \r key": "val \r val"}	{"key \r key": "val \r val"}
+Special Characters: 	{"key \b key": "val \b val"}	{"key \b key": "val \b val"}
+Special Characters: 	{"key \\0 key": "val \n val"}	{"key \\0 key": "val \n val"}
+Special Characters: 	{"key \\ key": "val \\ val"}	{"key \\ key": "val \\ val"}
+Special Characters: 	{"key \" key": "val \" val"}	{"key \" key": "val \" val"}
+Special Characters: 	{"key ' key": "val ' val"}	{"key ' key": "val ' val"}
+Special Characters: 	{"key \\Z key": "val ' val"}	{"key \\Z key": "val ' val"}
+Special Characters: 	["a \f b", "c \f d"]	["a \f b", "c \f d"]
+Special Characters: 	["a \t b", "c \t d"]	["a \t b", "c \t d"]
+Special Characters: 	["a \r b", "c \r d"]	["a \r b", "c \r d"]
+Special Characters: 	["a \b b", "c \b d"]	["a \b b", "c \b d"]
+Special Characters: 	["a \\ b", "c \\ d"]	["a \\ b", "c \\ d"]
+Special Characters: 	["a \" b", "c \" d"]	["a \" b", "c \" d"]
+Special Characters: 	["a ' b", "c ' d"]	["a ' b", "c ' d"]
+Special String Cases: 	{"": ""}	{"": ""}
+Special String Cases: 	[""]	[""]
+Raw LITERALS: 	true	true
+Raw LITERALS: 	false	false
+Raw LITERALS: 	null	null
+JSON LITERALS: 	{"val": true}	{"val": true}
+JSON LITERALS: 	{"val": false}	{"val": false}
+JSON LITERALS: 	{"val": null}	{"val": null}
+Timestamp as RawValue	"2019-12-26 19:56:03.000000"	"2019-12-26 19:56:03.000000"
+Array LITERALS: 	["prefix", null, "suffix", 1]	["prefix", null, "suffix", 1]
+Array LITERALS: 	["prefix", false, "suffix", 1]	["prefix", false, "suffix", 1]
+Array LITERALS: 	["prefix", true, "suffix", 1]	["prefix", true, "suffix", 1]
+show create table mysql_json_test_big;
+Table	Create Table
+mysql_json_test_big	CREATE TABLE `mysql_json_test_big` (
+  `description` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `expected` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `actual` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
+select * from mysql.plugin;
+name	dl
+drop table mysql_json_test;
+drop table mysql_json_test_big;
diff --git a/mysql-test/main/mysql_json_mysql_upgrade.test b/mysql-test/main/mysql_json_mysql_upgrade.test
new file mode 100644
index 00000000000..20f8ae588b7
--- /dev/null
+++ b/mysql-test/main/mysql_json_mysql_upgrade.test
@@ -0,0 +1,32 @@
+--echo #
+--echo # MDEV-24093: Detect during mysql_upgrade if type_mysql_json.so 
+--echo # is needed and load it
+--echo #
+
+-- source include/have_utf8.inc
+-- source include/mysql_upgrade_preparation.inc
+
+SET NAMES utf8;
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+
+--copy_file std_data/mysql_json/mysql_json_test.frm $MYSQLD_DATADIR/test/mysql_json_test.frm
+--copy_file std_data/mysql_json/mysql_json_test.MYI $MYSQLD_DATADIR/test/mysql_json_test.MYI
+--copy_file std_data/mysql_json/mysql_json_test.MYD $MYSQLD_DATADIR/test/mysql_json_test.MYD
+
+--copy_file std_data/mysql_json/mysql_json_test_big.frm $MYSQLD_DATADIR/test/mysql_json_test_big.frm
+--copy_file std_data/mysql_json/mysql_json_test_big.MYI $MYSQLD_DATADIR/test/mysql_json_test_big.MYI
+--copy_file std_data/mysql_json/mysql_json_test_big.MYD $MYSQLD_DATADIR/test/mysql_json_test_big.MYD
+
+--error ER_UNKNOWN_DATA_TYPE
+show create table mysql_json_test;
+
+--exec $MYSQL_UPGRADE --force 2>&1
+--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
+
+show create table mysql_json_test;
+select * from mysql_json_test;
+show create table mysql_json_test_big;
+select * from mysql.plugin;
+drop table mysql_json_test;
+drop table mysql_json_test_big;
diff --git a/mysql-test/main/mysql_json_mysql_upgrade_with_plugin_loaded.result b/mysql-test/main/mysql_json_mysql_upgrade_with_plugin_loaded.result
new file mode 100644
index 00000000000..0b2e7af84eb
--- /dev/null
+++ b/mysql-test/main/mysql_json_mysql_upgrade_with_plugin_loaded.result
@@ -0,0 +1,95 @@
+#
+# MDEV-24093: Detect during mysql_upgrade if type_mysql_json.so 
+# is needed and load it
+#
+SET NAMES utf8;
+call mtr.add_suppression("Table rebuild required");
+show create table mysql_json_test;
+ERROR HY000: Table rebuild required. Please do "ALTER TABLE `test.mysql_json_test` FORCE" or dump/reload to fix it!
+Phase 1/7: Checking and upgrading mysql database
+Processing databases
+mysql
+mysql.column_stats                                 OK
+mysql.columns_priv                                 OK
+mysql.db                                           OK
+mysql.event                                        OK
+mysql.func                                         OK
+mysql.global_priv                                  OK
+mysql.gtid_slave_pos                               OK
+mysql.help_category                                OK
+mysql.help_keyword                                 OK
+mysql.help_relation                                OK
+mysql.help_topic                                   OK
+mysql.index_stats                                  OK
+mysql.innodb_index_stats
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+mysql.innodb_table_stats
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+mysql.plugin                                       OK
+mysql.proc                                         OK
+mysql.procs_priv                                   OK
+mysql.proxies_priv                                 OK
+mysql.roles_mapping                                OK
+mysql.servers                                      OK
+mysql.table_stats                                  OK
+mysql.tables_priv                                  OK
+mysql.time_zone                                    OK
+mysql.time_zone_leap_second                        OK
+mysql.time_zone_name                               OK
+mysql.time_zone_transition                         OK
+mysql.time_zone_transition_type                    OK
+mysql.transaction_registry
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+
+Repairing tables
+mysql.innodb_index_stats
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+mysql.innodb_table_stats
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+mysql.transaction_registry
+Error    : Unknown storage engine 'InnoDB'
+error    : Corrupt
+Phase 2/7: Installing used storage engines... Skipped
+Phase 3/7: Fixing views
+mysql.user                                         OK
+Phase 4/7: Running 'mysql_fix_privilege_tables'
+Phase 5/7: Fixing table and database names
+Phase 6/7: Checking and upgrading tables
+Processing databases
+information_schema
+mtr
+mtr.global_suppressions                            OK
+mtr.test_suppressions                              OK
+performance_schema
+test
+test.mysql_json_test                               Needs upgrade
+test.mysql_json_test_big                           Needs upgrade
+
+Repairing tables
+test.mysql_json_test                               OK
+test.mysql_json_test_big                           OK
+Phase 7/7: Running 'FLUSH PRIVILEGES'
+OK
+show create table mysql_json_test;
+Table	Create Table
+mysql_json_test	CREATE TABLE `mysql_json_test` (
+  `description` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `expected` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `actual` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
+show create table mysql_json_test_big;
+Table	Create Table
+mysql_json_test_big	CREATE TABLE `mysql_json_test_big` (
+  `description` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `expected` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
+  `actual` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
+select * from mysql.plugin;
+name	dl
+drop table mysql_json_test;
+drop table mysql_json_test_big;
diff --git a/mysql-test/main/mysql_json_mysql_upgrade_with_plugin_loaded.test b/mysql-test/main/mysql_json_mysql_upgrade_with_plugin_loaded.test
new file mode 100644
index 00000000000..f3e9c2e539a
--- /dev/null
+++ b/mysql-test/main/mysql_json_mysql_upgrade_with_plugin_loaded.test
@@ -0,0 +1,35 @@
+--echo #
+--echo # MDEV-24093: Detect during mysql_upgrade if type_mysql_json.so 
+--echo # is needed and load it
+--echo #
+# Let's now load plugin first
+-- source include/have_utf8.inc
+-- source include/have_type_mysql_json.inc
+-- source include/mysql_upgrade_preparation.inc
+
+SET NAMES utf8;
+call mtr.add_suppression("Table rebuild required");
+
+let $MYSQLD_DATADIR= `select @@datadir`;
+
+--copy_file std_data/mysql_json/mysql_json_test.frm $MYSQLD_DATADIR/test/mysql_json_test.frm
+--copy_file std_data/mysql_json/mysql_json_test.MYI $MYSQLD_DATADIR/test/mysql_json_test.MYI
+--copy_file std_data/mysql_json/mysql_json_test.MYD $MYSQLD_DATADIR/test/mysql_json_test.MYD
+
+--copy_file std_data/mysql_json/mysql_json_test_big.frm $MYSQLD_DATADIR/test/mysql_json_test_big.frm
+--copy_file std_data/mysql_json/mysql_json_test_big.MYI $MYSQLD_DATADIR/test/mysql_json_test_big.MYI
+--copy_file std_data/mysql_json/mysql_json_test_big.MYD $MYSQLD_DATADIR/test/mysql_json_test_big.MYD
+
+# In the previous example (mysql_json_mysql_upgrade.test) 
+# instead of ER_TABLE_NEEDS_REBUILD we had ER_UNKNOWN_DATA_TYPE
+--error ER_TABLE_NEEDS_REBUILD
+show create table mysql_json_test;
+
+--exec $MYSQL_UPGRADE --force 2>&1
+--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
+
+show create table mysql_json_test;
+show create table mysql_json_test_big;
+select * from mysql.plugin;
+drop table mysql_json_test;
+drop table mysql_json_test_big;
diff --git a/mysql-test/main/mysql_json_table_recreate.test b/mysql-test/main/mysql_json_table_recreate.test
index de1e9202985..a399b546591 100644
--- a/mysql-test/main/mysql_json_table_recreate.test
+++ b/mysql-test/main/mysql_json_table_recreate.test
@@ -1,8 +1,5 @@
 --source include/have_utf8.inc
-
-if (!$TYPE_MYSQL_JSON_SO) {
-  skip Need MYSQL_JSON plugin;
-}
+--source include/have_type_mysql_json.inc
 
 --echo #
 --echo # The following test takes 2 tables containing a JSON column and attempts
diff --git a/mysql-test/main/mysql_upgrade_mysql_json_datatype.result b/mysql-test/main/mysql_upgrade_mysql_json_datatype.result
index 604ef2bc0df..25bc70c9074 100644
--- a/mysql-test/main/mysql_upgrade_mysql_json_datatype.result
+++ b/mysql-test/main/mysql_upgrade_mysql_json_datatype.result
@@ -3,7 +3,6 @@ call mtr.add_suppression("is marked as crashed");
 call mtr.add_suppression("Checking");
 SET NAMES utf8;
 set sql_mode="";
-install soname 'type_mysql_json';
 show create table tempty;
 ERROR HY000: Table rebuild required. Please do "ALTER TABLE `test.tempty` FORCE" or dump/reload to fix it!
 show create table mysql_json_test;
@@ -103,4 +102,3 @@ Total_Number_of_Tests	Succesful_Tests	String_is_valid_JSON
 drop table tempty;
 drop table mysql_json_test;
 drop table mysql_json_test_big;
-uninstall soname 'type_mysql_json';
diff --git a/mysql-test/main/mysql_upgrade_mysql_json_datatype.test b/mysql-test/main/mysql_upgrade_mysql_json_datatype.test
index 0363b2d0dc1..13d8ff5754a 100644
--- a/mysql-test/main/mysql_upgrade_mysql_json_datatype.test
+++ b/mysql-test/main/mysql_upgrade_mysql_json_datatype.test
@@ -1,10 +1,7 @@
 -- source include/mysql_upgrade_preparation.inc
 -- source include/have_working_dns.inc
 -- source include/have_innodb.inc
-
-if (!$TYPE_MYSQL_JSON_SO) {
-  skip Need MYSQL_JSON plugin;
-}
+-- source include/have_type_mysql_json.inc
 
 call mtr.add_suppression("Table rebuild required");
 call mtr.add_suppression("is marked as crashed");
@@ -28,8 +25,6 @@ SET NAMES utf8;
 
 set sql_mode="";
 
-install soname 'type_mysql_json';
-
 --error ER_TABLE_NEEDS_REBUILD
 show create table tempty;
 --error ER_TABLE_NEEDS_REBUILD
@@ -63,5 +58,4 @@ drop table tempty;
 drop table mysql_json_test;
 drop table mysql_json_test_big;
 
-uninstall soname 'type_mysql_json';
 --remove_file $MYSQLD_DATADIR/mysql_upgrade_info