Category: MySQL-MariaDB

Handy Query : List of MySQL Tables Without Primary Key

Posted on April 11, 2019April 12, 2019 by Mikael HOUNDEGNON

Here is my favorite handy query to get the list of tables in MySQL without Primary Key :

USE INFORMATION_SCHEMA;
SELECT TABLES.table_schema, TABLES.table_name
FROM 	TABLES LEFT JOIN KEY_COLUMN_USAGE AS c  ON ( TABLES.TABLE_NAME = c.TABLE_NAME AND c.CONSTRAINT_SCHEMA = TABLES.TABLE_SCHEMA AND c.constraint_name = 'PRIMARY')
WHERE 	TABLES.table_schema not in ('information_schema','performance_schema','mysql')
AND 	TABLES.table_type = 'BASE TABLE'
AND 	c.constraint_name IS NULL;

USE INFORMATION_SCHEMA;

SELECT TABLES.table_schema, TABLES.table_name

FROM TABLES LEFT JOIN KEY_COLUMN_USAGE AS c ON ( TABLES.TABLE_NAME = c.TABLE_NAME AND c.CONSTRAINT_SCHEMA = TABLES.TABLE_SCHEMA AND c.constraint_name = 'PRIMARY')

WHERE TABLES.table_schema not in ('information_schema','performance_schema','mysql')

AND TABLES.table_type = 'BASE TABLE'

AND c.constraint_name IS NULL;

This

Optimizing MySQL queries where clause contains function by using Generated Columns

Posted on March 29, 2019March 29, 2019 by Shuo Wang

Description: As we know, we can’t create a function-based index on MySQL, not like PostgreSQL (https://www.postgresql.org/docs/current/indexes-expressional.html). Usually, we should avoid

Pay attention when using MySQL User-Defined Variables

Posted on November 28, 2018December 5, 2018 by Shuo Wang

Recently, i used a user-defined variable to store some values, and it showed an unexpected result. I updated rows using ‘where a

How to get the network packets between MySQL client and server?

Posted on November 15, 2018December 6, 2018 by Shuo Wang

Sometimes when we get ERROR messages such as the communication failure, MySQL server not responds your requests, or we

What you should know before changing MySQL max_connections variable

Posted on November 2, 2018December 3, 2018 by Mikael HOUNDEGNON

Have you tried connecting to your MySQL server only to get the annoying error, “too many connections” ? This

Tips when using ‘replace into’ and ‘insert … on duplicate key update…’ in MySQL

Posted on October 31, 2018November 28, 2018 by Shuo Wang

Sometimes when we want to insert rows into a table which contains duplicate keys, then we consider using ‘replace

MySQL Schema comparison between PROD and UAT/DEV Servers

Posted on October 26, 2018November 27, 2018 by Mahendra Singh Bisht

Hello Folks, Today I’ll be writing an article on how to do schema comparison of MySQL database between 2

Installation of MySQL Enterprise Monitor

Posted on October 25, 2018November 26, 2018 by Engr. Mohammad Rizwan Yasin

MySQL Enterprise Monitor is a tool that register several MySQL Instances and provide complete details i.e. performance graphs and

MySQL Auditing

Posted on October 24, 2018November 26, 2018 by Engr. Mohammad Rizwan Yasin

MySQL Community Edition: MySQL Community Edition having feature of auditing but I cannot having feature filter the users To

About SQL Server and Comparison with Other Relational Database Management Systems

Posted on October 22, 2018November 26, 2018 by dbtut

SQL Server is a relational database management system (RDBMS) that Microsoft has provided to us. You can find a