emGee Software Solutions Custom Database Applications

Share this

Web Technologies

Introduction to Angular Grid

Echo JS - Sun, 05/05/2019 - 11:27
Categories: Web Technologies

MySQL8 Cluster and Networking Problems

Planet MySQL - Sun, 05/05/2019 - 09:25

This is my second post on the topic of reliable MySQL8 cluster. Hopefully the last one as things look much brighter now. In my previous post about MySQL8 and building an InnoDB cluster, I’ve described the overall design of the KeyChest backend. This has been running well for a couple of weeks till a network

The post MySQL8 Cluster and Networking Problems appeared first on Magic of Security.

Categories: Web Technologies

Combination of React and D3

Echo JS - Sat, 05/04/2019 - 08:00
Categories: Web Technologies

(MySQL) Logged and Loaded: authentication_ldap_simple_log_status or authentication_ldap_sasl_log_status not effective?

Planet MySQL - Sat, 05/04/2019 - 03:09

A quick one in case anyone else hits the same problem as I encountered.  The documentation for authentication_ldap_sasl_log_status and  authentication_ldap_simple_log_status states these variables can be set to a value between 1 and 5 to control the types of messages logged.

If you set them and still find nothing is logged then sett log_error_verbosity to it’s maximum value of 3 and you should find the messages are output to the error log as expected.

Thats all!

Categories: Web Technologies

Finding Tables without Primary Key

Planet MySQL - Sat, 05/04/2019 - 02:15
Having a primary key defined for each user table is best practice for performance in InnoDB. And when using Group Replication or InnoDB Cluster for automatic high availability it is even mandatory. So it is wise to check if you have tables running without primary key. You can identify these tables by running:
SELECT t.table_schema, t.table_name FROM tables AS t         LEFT JOIN key_column_usage AS c          ON (t.table_name = c.table_name AND             c.constraint_schema = t.table_schema AND             c.constraint_name = 'PRIMARY' )     WHERE t.table_schema NOT IN ("mysql", "information_schema",                                  "performance_schema", "sys")           AND c.constraint_name IS NULL          AND t.table_type = "BASE TABLE"; 
And if you want to make life even easier, you can add this as a report to the sys schema:
CREATE VIEW sys.schema_tables_without_pk AS SELECT t.table_schema, t.table_name FROM tables AS t         LEFT JOIN key_column_usage AS c          ON (t.table_name = c.table_name AND             c.constraint_schema = t.table_schema AND             c.constraint_name = 'PRIMARY' )     WHERE t.table_schema NOT IN ("mysql", "information_schema",                                  "performance_schema", "sys")           AND c.constraint_name IS NULL          AND t.table_type = "BASE TABLE"; 
It is easy to detect but a little more challenging to solve. You need to consider your application and potential load when adding a new primary key. One solution is to add a new auto_increment column. In many cases this might help already. In other cases you already have a natural primary key in your table definition, It's just not defined as such.
To add auto_increment columns to all affected tables (which I do not recommend without thinking about it and testing first!), you can use the beauty of the Python mode in MySQL Shell:
$ mysqlsh root@localhost:33060 --py

MySQL Shell 8.0.16


Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates.

Other names may be trademarks of their respective owners.


Type '\help' or '\?' for help; '\quit' to exit.

Creating a session to 'root@localhost:39010'

Fetching schema names for autocompletion... Press ^C to stop.

Your MySQL connection id is 762 (X protocol)

Server version: 8.0.16-commercial MySQL Enterprise Server - Commercial

No default schema selected; type \use <schema> to set one.


MySQL  localhost:33060+ ssl  Py >l=session.get_schema("sys").get_table("schema_tables_without_pk").select().execute().fetch_all()

MySQL  localhost:33060+ ssl  Py >for val in l: session.sql("ALTER TABLE "+val[0]+"."+val[1]+" ADD COLUMN (__id int unsigned auto_increment PRIMARY KEY)");


Categories: Web Technologies

Pages