SJGP Software

  The Problem We want to gather information about the same data entities, scattered across diverse schemas. How to apply the same SELECT query in each schema and gather the results into a single output? A sample Use Case: we want a list of user login names across all schemas, so we can analyze possible conflicts in moving to a centralized identity management system. MySQL Cross-Schema Reporting Screenshot of results of this process, from MySQL Workbench: Our approach is to create a stored procedure that will walk through all schemas on the server, run the same query to gather the data, and put the collected data into a dedicated table in an administrative schema for future reporting and analysis. First, let's create a new schema for this Reporting exercise, and select it as the schema in use. CREATE DATABASE CustomReportingSPS; USE CustomReportingSPS; Next, create a table that will collect the results of the query as it is run across schemas. CREATE TABLE rpt_active_users (   DB_

The Problem We want to calculate the size of each schema on our database server. We want to do this through SQL, not with a disk-based process. Sample use-case: an established system gives separate schemas to each client. Now it wants to begin charging a small fee based on the amount of space used by each client. MySQL Schema Sizes We can gather this data using the information_schema in MySQL. The "tables" table holds data and index size values for the tables of each schema, so we can group and sum them. We'll also throw in some formatting of the size, alongside the raw large integer of the total size. I've also dropped the ORDER BY clause found in the screenshot, as for my use case above I am not concerned with that ordering of the output. SELECT table_schema 'Database Name',   SUM(data_length + index_length) 'Size in Bytes',   sys.FORMAT_BYTES(SUM(data_length + index_length)) 'Size (Formatted)' FROM information_schema.tables  GROUP BY table_s

The Problem Sometimes we encounter a "Zombie Query" in databases. This may be a deadlocked process or a long-running job whose front-end hit its timeout. Especially in the latter case, the query is chewing up server resources for no purpose. Ad hoc reports built on a poorly indexed data source is a common enough example. MySQL Workbench has a nice, interactive Admin UI for identifying these processes and terminating them. Alas, since we migrated our DB server to AWS RDS database service, the Workbench admin tool no longer does the job of killing the zombie query. The AWS Solution To kill the zombie query in AWS RDS service, we need the process id, and we need to put that into a custom procedure. SHOW PROCESSLIST; -- Get the process number from the ID column CALL mysql.rds_kill_query(1234567); These "memory tickler" posts are quick-hits to refresh my mind about little technical details that arise infrequently enough that I forget in between, and wind up doing an inte

  Find All Foreign Key References In my experience, a well-Normalized database winds up with lots of Foreign Key relationships between the tables. They are a real benefit, bringing so much ease and power in preserving data integrity. Best of all, they pass responsibility for maintaining that integrity to the RDBMS itself, rather than burying it in application code. Looking at one table, it is relatively easy to see its list of Foreign Keys outward, to other tables. Occasionally, though, we need to know what other tables have a Foreign Key defined for a given table. It is rare enough that I usually forget some of the little details in between uses. Rather than needing to do a web search every time, this Tickler post will help to jog my memory. MySQL -- what FKs reference this data? SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'MySchema' AND REFERENCED_TABLE_

Metadata Locks on a Schema An RDBMS will use metadata locking to help it ensure data consistency and to control which concurrent process has access to database objects. Sometimes an application will have business-reasons to handle its own row-level locking. Metadata locking, on the other hand, applies at the level of the whole table, not the individual row. It can also apply to other database objects, such as the whole schema, or a stored procedure or trigger. MySQL In MySQL use the performance_schema and the metadata_locks table to learn about active locks. For example, all locks for a specific DB schema can be listed with: SELECT * FROM metadata_locks  WHERE OBJECT_SCHEMA = 'MySchemaHere'  ORDER BY OBJECT_NAME; MySQL also provides functions that can be used is managing metadata locks. Functions such as: GET_LOCK() IS_FREE_LOCK() RELEASE_LOCK() RELEASE_ALL_LOCKS() Full documentation here . These "memory tickler" posts are quick-hits to refresh my mind about little te

Unix time to Human time I have worked on some applications that are compatible with multiple different relational database systems. But data types often have subtle differences between RDBMSs. As a result, a data model design decision I've inherited (more than once!) is that timestamps would be stored as numbers rather than dates. Specifically as Integer data types to store seconds (or milliseconds) offset from Unix Epoch. The applications usually have no problems with the conversion. But when providing support, humans prefer to think in terms of "Thursday, October 7, 2021 1:49:49 PM" instead of "1633614589" (or "1633614589000") Here is how to convert this data in SQL. MySQL: use the built-in conversion functions from_unixtime() and unix_time() like so: SELECT from_unixtime(myTimeStampInt) FROM mytable  WHERE myTimeStampInt < unix_time('2021-10-01 12:00:00'); And if storing milliseconds instead of seconds, remember to divide by 1000 These

BLOBs in MySQL BLOBs or Binary Large OBjects are a useful datatype in relational databases for certain use-cases. For example storing a profile image for a user of the system. I have also seen databases that use them for storing large amounts of sensitive or encrypted data. Sometimes we need to see the contents of the BLOB. Use a command like this to do so, and remember to choose the character encoding. MySQL: SELECT CONVERT(blob_column USING latin1) FROM mytable; SELECT CONVERT(blob_column USING utf8) FROM mytable; These "memory tickler" posts are quick-hits to refresh my mind about little technical details that arise infrequently enough that I forget in between, and wind up doing an internet search every time. By posting them here, they will be easier to find as needed.)

What is lost to history? My elementary school-aged daughter recently brought home a geography project. They were to take the first letter of their name, and turn it into a map of an island. Elementary school geography lesson My first reaction was, of course, pride at a young child's art work. Her island looks like an interesting place to visit, with lots of green space, and only a few houses. Lots of open water, while still having a safer harbor and an inner lagoon. A zoo. And of course Snoopy, her current passion, has a place on the island. Map skills in 2021? Gradually, though, the lesson struck me as anachronistic. In our modern world, with satellites enabling GPS to know our place in geography within feet or inches, and with online apps that allow easy zoom scaling, rotation, satellite or street-level photos and more, why are they learning about compass roses and map legends? Are those still things that are important to know? And if not, if these and other concepts of paper-bas