I’m active on multiple platforms (mail, slack, forums, …) and often, when people are looking for help, the first 5 or 10 questions are always the same:
- which version of MySQL are you running ?
- which OS ?
- is it in the cloud ?
- which provider ?
- are you using replication ?
- GTIDs ?
- ….
I’ve added to MySQL Shell Plugin repository and plugin called support which provides an output that user can share when looking for MySQL help.
Usually, I’m focusing only in MySQL 8.0, but this plugin works with older versions too (don’t try MySQL 3.23…. it should be compatible from 5.6).
Let’s see an output if I run it locally:
[fred@fedora ~] $ mysqlsh root@localhost -e "support.fetchInfo()"
========================================
Report for fedora - Fri 2021-10-15 10:57
========================================
MySQL Version: MySQL Community Server - GPL (8.0.26) - x86_64
Dataset:
|-- Data: 177.73 MiB
|-- Indexes: 32.19 MiB
|-- Total Size: 209.92 MiB
Engines Used:
|-- InnoDB: 43 table(s) (0.20G)
|-- MyISAM: 2 table(s) (0.00G)
Tables without PK: 12
InnoDB Buffer Pool:
|-- BP Size: 128.00 MiB
|-- BP Instance(s): 1
|-- BP filled at : 16.0%
|-- Disk Read Ratio: 1.12%
InnoDB Logs:
|-- File Size: 48.00 MiB
|-- Nb of Files: 2
|-- Checkpoint Info: 0 bytes / 96.00 MiB
|-- CheckPointAge: 0.0%
|-- Flush at Commit: 1
|-- Redo Log: ON
Adaptive Hash Index:
|-- AHI Enabled: ON
|-- AHI Parts: 8
|-- AHI Ratio: 50.06
Global Variables Configured:
|-- GLOBAL: 4
|-- PERSISTED: 4
|-- DYNAMIC: 2
Replication Information:
|-- Binlog Checksum: CRC32
|-- Binlog Encryption: 0
|-- Binlog Format: ROW
|-- Binlog Row Image: FULL
|-- Binlog Row Metadata: MINIMAL
|-- Binlog Trx Dependency Tracking: COMMIT_ORDER
|-- GTID Mode: ON
|-- Log Bin: 1
|-- Log Bin Basename: /var/lib/mysql/binlog
|-- Server Id: 1
|-- Sync Binlog: 1
|-- Trx Write Set Extraction: XXHASH64
Flush Commands: 1
User Defined Routines:
|-- sys: 1
|-- mds: 7
|-- query_rewrite: 1
Authentication Plugins:
|-- caching_sha2_password: 13 user(s)
|-- sha256_password: 1 user(s)
|-- mysql_no_login: 1 user(s)
MDS Incompatible Privileges: 6 user(s)
As you can see, this is very easy and provides already valuable information.
Operating System
As the server is running on my local machine, I can also, provide some information regarding the system. By default this is disabled. Let’s try it (I use the Python mode this time):
[fred@fedora ~] $ mysqlsh root@localhost --py -e "support.fetch_info(os=True)"
========================================
Report for fedora - Fri 2021-10-15 11:02
========================================
Operating System: Linux (MySQL built on Linux)
Version: 5.14.11-200.fc34.x86_64
Architecture: x86_64
Processor: Intel(R) Core(TM) i5-4570 CPU @ 3.20GHz
CPU Core(s): 4
Total Memory: 31Gi
Memory Used: 10Gi
Memory Free: 10Gi
Filesystem Cache: 10Gi
Total Swap: 8.0Gi
Swap Used: 0B
Swap Free: 8.0Gi
Swappiness: 60
Datadir: /var/lib/mysql/
|-- Physical drive: /dev/mapper/luks-500325a0-4b9e-4ad2-83a9-fecadbec02e1
|-- Size: 1.9T
|-- Used (10%): 171G
|-- Free: 1.7T
|-- Mount point: /
|-- Filesystem: btrfs
|-- Mount attributes: rw,relatime,compress=zstd:1,ssd,space_cache,subvolid=256,subvol=/home
innodb_data_home_dir: None
innodb_log_group_home_dir: ./
innodb_temp_tablespaces_dir: ./#innodb_temp/
innodb_tmpdir: None
tmpdir: /var/tmp
MySQL Version: MySQL Community Server - GPL (8.0.26) - x86_64
Dataset:
|-- Data: 177.73 MiB
|-- Indexes: 32.19 MiB
|-- Total Size: 209.92 MiB
Engines Used:
|-- InnoDB: 43 table(s) (0.20G)
|-- MyISAM: 2 table(s) (0.00G)
Tables without PK: 12
InnoDB Buffer Pool:
|-- BP Size: 128.00 MiB
|-- BP Instance(s): 1
|-- BP filled at : 16.0%
|-- Disk Read Ratio: 1.07%
InnoDB Logs:
|-- File Size: 48.00 MiB
|-- Nb of Files: 2
|-- Checkpoint Info: 0 bytes / 96.00 MiB
|-- CheckPointAge: 0.0%
|-- Flush at Commit: 1
|-- Redo Log: ON
Adaptive Hash Index:
|-- AHI Enabled: ON
|-- AHI Parts: 8
|-- AHI Ratio: 50.37
Global Variables Configured:
|-- GLOBAL: 4
|-- PERSISTED: 4
|-- DYNAMIC: 2
Replication Information:
|-- Binlog Checksum: CRC32
|-- Binlog Encryption: 0
|-- Binlog Format: ROW
|-- Binlog Row Image: FULL
|-- Binlog Row Metadata: MINIMAL
|-- Binlog Trx Dependency Tracking: COMMIT_ORDER
|-- GTID Mode: ON
|-- Log Bin: 1
|-- Log Bin Basename: /var/lib/mysql/binlog
|-- Server Id: 1
|-- Sync Binlog: 1
|-- Trx Write Set Extraction: XXHASH64
Flush Commands: 1
User Defined Routines:
|-- sys: 1
|-- mds: 7
|-- query_rewrite: 1
Authentication Plugins:
|-- caching_sha2_password: 13 user(s)
|-- sha256_password: 1 user(s)
|-- mysql_no_login: 1 user(s)
MDS Incompatible Privileges: 6 user(s)
More ?
The plugin can also provide more information when possible. Like details and advice:
JS > \h support
NAME
support - Getting Information useful for requesting help.
DESCRIPTION
A collection of methods useful when requesting help such as support or
Community Slack and Forums
FUNCTIONS
fetchInfo([mysql][, os][, advices][, details][, session])
Fetch info from the system.
help([member])
Provides help about this object and it's members
Advices can be very useful when for example planing to migrate to MySQL Database Service.
Let’s have a look when using the plugin with AWS Aurora:
[fred@fedora ~] $ mysqlsh admin@aurora57.cluster-cy6iecpgwnkz.eu-west-1.rds.amazonaws.com --py -e "support.fetch_info(advices=True)"
==============================================
Report for ip-10-4-1-81 - Fri 2021-10-15 12:05
==============================================
MySQL Version: MySQL Community Server (GPL) (5.7.12) - x86_64 (AWS RDS)
Aurora: 2.07.2
Your MySQL version might be old or not fully supported by this tool!
Dataset:
|-- Data: 9.02 MiB
|-- Indexes: 112.00 KiB
|-- Total Size: 9.12 MiB
Engines Used:
Tables without PK: 4
It's not recommended to have tables without Primary Key
InnoDB Buffer Pool:
|-- BP Size: 7.79 GiB
|-- BP Instance(s): 2
|-- BP filled at : 1.0%
|-- Disk Read Ratio: 0.08%
InnoDB Logs:
|-- File Size: 48.00 MiB
|-- Nb of Files: 2
|-- Total Size: 96.00 MiB
|-- Flush at Commit: 1
Adaptive Hash Index:
|-- AHI Enabled: OFF
|-- AHI Parts: 8
|-- AHI Ratio: 0.0
Replication Information:
|-- Binlog Checksum: CRC32
|-- Binlog Format: ROW
|-- Binlog Row Image: FULL
|-- GTID Mode: OFF_PERMISSIVE
You should enable GTIDs !
|-- Log Bin: 0
You should enable binary logs !
|-- Log Bin Basename: None
|-- Server Id: 340517295
|-- Sync Binlog: 1
|-- Trx Write Set Extraction: OFF
Flush Commands: 0
Extra tables in mysql schema: 19
Extra tables in mysql schema are not supported in MDS
User Defined Routines:
|-- mysql: 32
Custom routines in mysql schema are not supported in MDS
Authentication Plugins:
|-- mysql_native_password: 3 user(s)
MDS Incompatible Privileges: 2 user(s)
admin@% has a privilege not supported in MDS: RELOAD
admin@% has a privilege not supported in MDS: LOAD FROM S3
admin@% has a privilege not supported in MDS: SELECT INTO S3
admin@% has a privilege not supported in MDS: INVOKE LAMBDA
admin@% has a privilege not supported in MDS: INVOKE SAGEMAKER
admin@% has a privilege not supported in MDS: INVOKE COMPREHEND
rdsadmin@localhost has a privilege not supported in MDS: ALL PRIVILEGES
Conclusion
From now when you have a question related to MySQL and some more info is required, you know an easy way to provide such info.
If you plan to migrate to OCI MySQL Database Service and use MDS Inbound Replication, the plugin provides you some information regarding the basics limitations but I encourage you to check those in the manual.
If you like the tool, please contribute, I’m sure there are plenty nice and useful stuff that can be added to it.
Enjoy MySQL and MySQL Shell !
[…] Get system information […]
02242024
Hello Lefred,
I just migrated from Joomla 3.10 to Joomla 4 & 5 and I got these two error messages. Do you have an idea where I could find these errors? I’ll appreciate it. Thank you.
Joseph Lariosa
“You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘ AND `null` = ‘NO” at line 1”
And
“There are tables not up to date!”
Hi, are you using MySQL ? And if so which version ?
Thanks to your response, Lefred.
No. I am not using MySQL.
I am using MySQLi.
Joseph Lariosa
MySQLi is the driver, I mean, which database version are you using ?
Dear Lefred,
I don’t know. Maybe MariaDB: 10.5.
But right now, I am not going to pursue further the problem of solving the disappearance of Articles in Joomla 5.0.3 because it turned out the disappearance was caused by the lack of Joomla content editors, like JCE Editor and TinyMCE, in my Joomla Content, after the migration from Joomla 3 to Joomla 4.
After I installed the JCE Editor, the texts of Articles have re-appeared.
Thank you for your time to discuss my problem. I will now consider this matter closed.
Pay attention that MariaDB is not MySQL. They are not drop-in replacement anymore.