I am trying to perform the initial configuration in proxysql and I found that while doing the Configure monitoring, I am unable to find the variable mysql-monitor_username in the global_variables table. The article says that we need to execute the update statement in the mysql database but I don't see that option existing already and I cannot make changes to the information_schema.global_variables table because basically that's a view. Can someone please help me here in completing this step? Reference Link : https://proxysql.com/documentation/ProxySQL-Configuration/

I have a shared database that stores procedures for all the other databases on the same server. This helps maintain one instance of a procedure rather than copying it across all databases. As long as I prepend DBName to all my tables, i.e. SELECT * FROM DBName.Table, I've been able to successfully have a shared repository for procedures. However, I've run into an instance where I need to reference INFORMATION_SCHEMA tables, but the shared database only has tables for that database. Is there a concept of querying INFORMATION_SCHEMA on another database? I'd like to do something like this, but it doesn't work: SELECT * FROM [DBName].INFORMATION_SCHEMA.TABLES

RDBMS: PostgreSQL 9.6 I desire to list the foreign keys for a given table and schema. I am about 90 % there but I also desire to know what options were used to make the fkey. Example on delete restrict or on delete update create or replace function it.table_foreign_keys_getlist(_schema text , _table text) returns TABLE(primary_key_ns text , primary_key_table text , foreign_key_ns text , foreign_key_table text ) language sql as $$ SELECT n1.nspname::TEXT AS primary_key_ns, c1.relname::TEXT AS primary_key_table, n2.nspname::TEXT AS foreign_key_ns, c2.relname::TEXT AS foreign_key_table FROM pg_catalog.pg_constraint c JOIN ONLY pg_catalog.pg_class c1 ON c1.oid = c.confrelid JOIN ONLY pg_catalog.pg_class c2 ON c2.oid = c.conrelid JOIN ONLY pg_catalog.pg_namespace n1 ON n1.oid = c1.relnamespace JOIN ONLY pg_catalog.pg_namespace n2 ON n2.oid = c2.relnamespace WHERE c1.relkind = 'r' AND c.contype = 'f' AND CASE WHEN _schema = '' THEN TRUE ELSE n1.nspname = _schema END AND CASE WHEN _table = '' THEN TRUE ELSE c1.relname = _table END ORDER BY 1,2,3,4; $$;

Since moving to Aurora3 RDS (MySQL 8 based) vs Aurora 2 (MySQL 5.7 based) noticed that execution time of certain triggers and stored functions performance has degraded. Correlation between triggers and functions was the select query on information_schema.table something like SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name LIKE tablename LIMIT 1 RDS Stack has 50+ schemas with close to 2000+ tables in each. Can take > 25s (unloaded system, no other users/connections). Query above run outside of stored function/trigger takes < 0.1s (same as it did in 5.7 stored/trigger). Performs best when single database stack. Can also get better performance by changing the definer of the function/trigger to be schema based user. Unable to reproduce locally on Community Edition of MySQL8.

Microsoft documentation says about db_datareader role that: >db_datareader: Members of the db_datareader fixed database role can read all data from all user tables and views. User objects can exist in any schema except sys and INFORMATION_SCHEMA. The last sentence is confusing for me. I think it means that users whose only role is db_datareader cannot query things inside INFORMATION_SCHEMA, but I made a user with only that role for a database and I successfully ran queries like below on that database:

* FROM INFORMATION_SCHEMA.TABLES

Doesn't this contradict that last sentence? What is the correct interpretation of the last sentence?

To automate the backup process of one of my MySQL databases, I would like to compare the structure of two tables (current version vs old version). Can you think of a query that can compare two tables? Here are some example tables that you can compare. CREATE TABLE product_today ( pname VARCHAR(150), price int, PRIMARY KEY (pname) ); CREATE TABLE product_yesterday ( pname VARCHAR(150), price int, PRIMARY KEY (pname) ); CREATE TABLE product_2days_back ( pname VARCHAR(15), price int, PRIMARY KEY (pname) ); The first two tables have identical structures. The last one is different. I just need to know whether two tables have different structures or not. I'm not interested in the how they differ.

- I have two database servers, local and live. - The local server is running MariaDB 10.11.6, the live server is running 10.11.8. - I have the exact same global user, example@localhost on both the local and live servers. - I ran the exact same SQL query (UPDATE information_schema.TABLES ...) on both servers. - The database/table/column structures are exactly the same. - The query ran 100% perfect on the local server. - I received the following error on the live server: >Access denied for user 'example'@'localhost' to database 'information_schema' - I researched and found the exact same results using SELECT * FROM mysql.global_priv WHERE 'User'='example';. - The user has global permissions, not limited to specific databases on both the local and live servers. - In the mysql.db database/table there is no mention of the example user because again, it has *global permissions*. - In the mysql.db database/table there is no mention of the information_schema database on either server. How do I force MariaDB to allow the example user to execute my UPDATE information_schema.TABLES ... query on the live server?

While attempting to find better ways to parse table schema data, the INFORMATION_SCHEMA articles were tested. There must be way more going on behind the scenes then meets the eye. Using sys.* articles, being more verbose, are all reliably fast. Can anyone explain why using the INFORMATION_SCHEMA articles produce a query plan the size of a Texas roadmap (see paste a plan)? Also, the storing the output of INFORMATION_SCHEMA.TABLES into a #temp or @temp table reduces the query time from 8 minutes to 6 seconds when joined against INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE. Here is a paste the plan -> https://www.brentozar.com/pastetheplan/?id=SyRUqcEd6 The first query takes 2 seconds. The second query takes over 8 minutes. DECLARE @X TABLE(TableName NVARCHAR(300)) INSERT INTO @X SELECT T.TABLE_NAME AS TableName FROM INFORMATION_SCHEMA.TABLES T ----2 Seconds 740 Rows SELECT CU.COLUMN_NAME AS ColumnName, T.TableName AS TableName FROM @X T INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CS ON CS.TABLE_NAME = T.TableName INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU ON CU.CONSTRAINT_NAME = CS.CONSTRAINT_NAME AND CS.CONSTRAINT_TYPE = 'PRIMARY KEY' GO ----8:47 Seconds 740 Rows SELECT CU.COLUMN_NAME AS ColumnName, T.TABLE_NAME AS TableName FROM INFORMATION_SCHEMA.TABLES T INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CS ON CS.TABLE_NAME = T.TABLE_NAME INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU ON CU.CONSTRAINT_NAME = CS.CONSTRAINT_NAME AND CS.CONSTRAINT_TYPE = 'PRIMARY KEY'

I removed (dropped) not used (in fact it was corrupted and that's why not used) table from a MySQL DB. I did it from MySQL Workbench. It was removed and it's not existing here. mysql> SELECT * FROM cacti.data_debug; ERROR 1146 (42S02): Table 'cacti.data_debug' doesn't exist BUT if I query INFORMATION_SCHEMA, I get this: mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name = 'cacti/data_debug'; +----------+------------------+------+--------+-------+-------------+------------+---------------+------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | +----------+------------------+------+--------+-------+-------------+------------+---------------+------------+ | 79055 | cacti/data_debug | 33 | 10 | 6305 | Barracuda | Dynamic | 0 | Single | +----------+------------------+------+--------+-------+-------------+------------+---------------+------------+ I found out that we can only read from INFORMATION_SCHEMA. It's just a list/snapshot of the existing data. I'd like to remove this single "row" as I'm moving to MySQL 8.0 and don't want to drag the mess. On top , Upgrade Checker is complaining about this as Error. How can I make it refreshed/regenerated? Update 1: mysql> SHOW TABLES FROM cacti; +-------------------------------------+ | Tables_in_cacti | +-------------------------------------+ | aggregate_graph_templates | | aggregate_graph_templates_graph | | ... | | color_templates | | colors | | data_input | | data_input_data | | data_input_fields | | data_local | | data_source_profiles | | data_source_profiles_cf | | ... | | version | +-------------------------------------+

We create a table as follows:

CREATE TABLE  "CHANGEWORKFLOWREQUEST" (
  "DATA" CLOB CONSTRAINT "ChangeWorkflowRequest_data" CHECK ("DATA" IS JSON FORMAT JSON) NOT NULL,
  "OPERATION" VARCHAR2(4000 char) NOT NULL,
  "MODELNAME" VARCHAR2(4000 char) NOT NULL,
  "MODELID" VARCHAR2(4000 char) NOT NULL,
  "STATUS" VARCHAR2(4000 char) default 'pending'  NOT NULL,
  "VERIFICATIONSTATUS" VARCHAR2(4000 char),
  "REMARKS" VARCHAR2(4000 char),
  "_VERIFIEDBY" VARCHAR2(4000 char),
  "_MODIFIERS" CLOB CONSTRAINT "ChangeWorkflowRequest_3245" CHECK ("_MODIFIERS" IS JSON FORMAT JSON),
  "CORRELATIONID" VARCHAR2(4000 char),
  "ID" varchar2(50) default sys_guid() not null,
  "WORKFLOWINSTANCEID" VARCHAR2(4000 char),
  "_OLDVERSION" VARCHAR2(256 char),
  "_VERSION" VARCHAR2(256 char) NOT NULL,
  "_NEWVERSION" VARCHAR2(256 char),
  "_PARENTVERSION" VARCHAR2(256 char),
  PRIMARY KEY("ID")
)

Note: _OLDVERSION is defined as VARCHAR2(256 CHAR). But when querying from all_tab_columns we are getting a slightly different result. Below is the query:

SELECT column_name AS "column",
       data_type   AS "type",
       nullable    AS "nullable",
       data_length AS "dataLength",
       CASE char_used
         WHEN 'C' THEN 1
         WHEN 'B' THEN 0
         ELSE NULL
       END         AS "charUsed"
FROM   all_tab_columns
WHERE  owner = 'JOHN'
       AND table_name = 'CHANGEWORKFLOWREQUEST';

This is the data it has returned (in csv):

"column","type","nullable","dataLength","charUsed"
"_OLDVERSION","VARCHAR2","Y",1024,1
"_VERSION","VARCHAR2","N",1024,1
"_NEWVERSION","VARCHAR2","Y",1024,1
"_PARENTVERSION","VARCHAR2","Y",1024,1
"DATA","CLOB","N",4000,
"OPERATION","VARCHAR2","N",4000,1
"MODELNAME","VARCHAR2","N",4000,1
"MODELID","VARCHAR2","N",4000,1
"STATUS","VARCHAR2","N",4000,1
"VERIFICATIONSTATUS","VARCHAR2","Y",4000,1
"REMARKS","VARCHAR2","Y",4000,1
"_VERIFIEDBY","VARCHAR2","Y",4000,1
"_MODIFIERS","CLOB","Y",4000,
"CORRELATIONID","VARCHAR2","Y",4000,1
"ID","VARCHAR2","N",50,0
"WORKFLOWINSTANCEID","VARCHAR2","Y",4000,1

Note: _OLDVERSION has length 1024. The expected value here is 256. Is the query meant to fetch this information incorrect or insufficient?

I've got a stored procedure that copies all tables from one database to another but steps through each table individually (because of queries). I need to add to the stored procedure to copy all views from the original database but I'm not totally sure how to loop through the results of this query: SHOW FULL TABLES IN stations WHERE TABLE_TYPE LIKE 'VIEW'; Stored procedure: USE station; DROP PROCEDURE IF EXISTS updateStation; DELIMITER // CREATE PROCEDURE updateStation (IN stationName VARCHAR(255)) BEGIN # stationList DROP TABLE IF EXISTS station.stationList; CREATE TABLE station.stationList LIKE stations.stationList; INSERT INTO station.stationList SELECT s.* FROM stations.stationList s WHERE s.hostName = stationName; # pinTypes DROP TABLE IF EXISTS pinTypes; CREATE TABLE station.pinTypes LIKE stations.pinTypes; INSERT INTO station.pinTypes SELECT p.* FROM stations.pinTypes p; # pinModes DROP TABLE IF EXISTS pinModes; CREATE TABLE station.pinModes LIKE stations.pinModes; INSERT INTO station.pinModes SELECT p.* FROM stations.pinModes p; # Copy All Views FOR SELECT TABLES IN stations WHERE TABLE_TYPE LIKE 'VIEW' DROP TABLE IF EXISTS ?; CREATE TABLE station.? LIKE stations.?; END // DELIMITER ; Perhaps something like this? : DECLARE cur CURSOR FOR SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'stations' AND ENGINE IS NULL; DECLARE done INT DEFAULT 0; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur; read_loop: LOOP FETCH cur INTO tableName; IF done THEN LEAVE read_loop; END IF; DROP TABLE IF EXISTS 'station'.tableName; CREATE TABLE 'station'.tableName LIKE 'stations'.tableName; END LOOP; CLOSE cur;

I have created this tbl_cities using the code below:

CREATE TABLE IF NOT EXISTS public.tbl_Cities
  (City_ID SERIAL NOT NULL,
   City_Name VARCHAR(100) NOT NULL,
   City_State_Region VARCHAR(100),
   ID_Country INT NOT NULL,

   CONSTRAINT PK_City_ID PRIMARY KEY (City_ID));

Now I want to query **the exact same order of this columns and their datatype,** in order to make sure, what are the column names and what is the real column order (without scrolling back to the CREATE TABLE statement). In context of the INSERT statements I'm about to write and execute. To learn this info I execute:

SELECT column_name, data_type FROM information_schema.columns
WHERE table_name = 'tbl_cities';

But the output I get is in different order, than the columns exist in reality. I've also tried to order the columns, but the only parameter I know is ORDER BY column_name. In this very case it results in the correct order in which the columns exist in reality.

SELECT column_name, data_type FROM information_schema.columns
WHERE table_name = 'tbl_cities'
ORDER BY column_name;

My **question** is: What is the correct syntax to achieve the **real order in which the columns are in the table,** in case ordering them alphabetically would return different result/order. I have found this question , but it concerns SQL Server and I need to do this on PostgreSQL v14.7. **I'm open to different answers** using not only information_schema.columns but also any kind of this other thing:

SELECT * FROM pg_catalog.pg_tables;

SELECT * 
FROM information_schema.TABLE_CONSTRAINTS 
WHERE CONSTRAINT_SCHEMA = DATABASE() 
  AND CONSTRAINT_NAME   = 'FK_FOLDER_FILTER_CONTRACT_CATEGORY_CONTRACT_CATEGORY' 
  AND CONSTRAINT_TYPE   = 'FOREIGN KEY';

The above query takes around 2 mins to execute is there any other alternate query or how can we optimize this query

I have a user with an access to several databases. In **MariaDB 10.6.11** I cannot use the following command to list all accessible databases:

SHOW DATABASES;
/* SQL Error (1227): Access denied; you need (at least one of) the SHOW DATABASES privilege(s) for this operation */

On the other hand, I can use this:

SELECT SCHEMA_NAME FROM information_schema.SCHEMATA ORDER BY SCHEMA_NAME;

On a different server with **MariaDB 10.5.18** "SHOW DATABASES" works fine, even I think users on both servers have the same privileges: MariaDB 10.6 user (not allowed to SHOW DATABASES):

SHOW GRANTS FOR 'dev-all'@'localhost';
GRANT USAGE ON *.* TO dev-all@localhost IDENTIFIED BY PASSWORD '...'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON dev-___.* TO dev-all@localhost WITH GRANT OPTION

MariaDB 10.5 user (allowed to SHOW DATABASES):

SHOW GRANTS FOR 'dev-all'@'localhost';
GRANT USAGE ON *.* TO dev-all@localhost IDENTIFIED BY PASSWORD '...'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON dev-___.* TO dev-all@localhost WITH GRANT OPTION

Is it caused by different MariaDB versions? Or am I missing something else? What is the point of not allowing a user to list all of their databases? How can I fix this issue on MariaDB 10.6?

There is a column created_at in most of tables. I would like to get the details if that field exists in the table and it is not indexed. Is there any thing can be done with the information_schema to get this checked? I'm using MySQL 5.6

I have to create a database from scratch for the first time and I am not sure which system I should try. I will have specific IP addresses, IP ranges, domain names, host names which will be the key for a given customer. I want my database to be filterable by the customer's key. Each key will have a variety of different forms of data associated with it. For example, a key may have a list of open ports, any identified services, etc. Sometimes a key will be a website, and the data associated with it may be cookie names, URLs, and parameters. If I later find out that a customer's parameter is vulnerable, I want to be able to search through all of the customer's for that vulnerability. What database system should I try using? What would be the most flexible and efficient? I don't want to try a database that isn't for my scheme.

What is the right way to design schema for self Nested or many to many relationship for single Table Table:Products - id - name - status Need to Match/save Products with related Products. ex: Related products for product 1 is 3,4 product 2 is 5,6 product 3 is 1 product 4 is 1 product 5 is 2 product 6 is 2

We use Trino (https://trino.io/) to connect to HDFS. I discovered that the data in the information_schema tables, for example: select * from information_schema.columns clz where clz.table_catalog = ‘hive’ and clz.table_schema = ‘’ and clz.table_name = ‘’ doesn’t always match up with what I get if I run show tables from [schema] show columns in [schema].[table] etc. It seems that the *show tables*/*show columns* commands pretty much always match up with what I see if I run the hadoop command (hadoop fs -ls ...) to show the contents of the hdfs folder. So I’m trying to figure out: - why the information_schema doesn’t give the same results as show tables/show columns/etc. - if there is a way to refresh/update information_schema to make it current Thank you.

I would like to audit my database to check which users have been granted individual EXECUTE permission to which procedure. I would like to use this information to be able to save GRANTs when changing (DROP+CREATE) the definition of some procedure in order to restore them after. How can I query the INFORMATION_SCHEMA or MYSQL schemas to retrieve that information? **Update** I'm interested in a solution working on MySQL 5.6+. For granular security, users of our datase are not granted an EXECUTE permission on the whole schema but on specific stored procedures. Answers on schema-level permissions are irrelevant.

Is there any way of calculating the number of bytes occupied by the table? I know that you can get some information from information_schema.tables but that information is not accurate enough. What actually required is the number of bytes according to the definition of the table for **InnoDB only** and collation could also be considered as **utf-8-general-ci**. For example, a table test is as following

create table test   (
   col1 varchar(25),
   col2 int,
   col3 varchar(3),
   col4 char(15),
   col5 datetime    
);

I would require to know the total row size that can be accumulated in one row, according to the types of columns in the table. Found a some sort of similar solution for MS SQL Server, but need its MySQL version