OurSQL Episode 191: Common Schema, part 1_MySQL
News
In the news, the OurSQL podcast will change to a monthly format after episode 200. We love doing the podcast, but it is very time consuming and production is expensive. We will still bring quality episodes, they will just be spaced further apart.
Common Schema
Common SchemaGoogle Code page
Installation sectionof the common schema documentation.
- Process Routines- stored routines managing query, session and process information & workflow
- session_unique_id()
- query_latptime()
- query_runtime()
- this_query()
- throttle()- some good examples here
- General Routines- stored routines mostly dealing with checksums
- crc64()- returns a crc64 checksum of the data given
- query_checksum()- return a checksum of the results of the query specified
- random_hash()
- shorttime_to_seconds- converts human readable "short times" like 1h, 45m, and so on to a number of seconds
- Query Analysis Routines- takes in the name of a schema and an object (event, routine, SQL statement or view) and returns a summary of the objects the given object involves, the type and schema for each dependent object
- get_event_dependencies()- for events
- get_routine_dependencies()- for stored functions and stored procedures
- get_sql_dependencies()- for SQL statements
- get_view_dependencies()- for views
- Schema Analysis Routines
- table_exists()
- table_rotate()- rotate a table in logrotate format
- Security Routines
- duplicate_grantee()- create a new user with the same privileges as an existing user
- grant_access()- grant SELECT and EXECUTE on common_schema items to the user listed
- match_grantee()- match a specified user@host with an entry in the mysql.user table
For example, from PROCESSLIST:
SELECT id, common_schema.mysql_grantee(user, host) FROM information_schema.processlist; - mysql_grantee()- return a qualified, valid mysql user from the information given
- killall()
- security_audit()- find unsecure accounts
- Date and Time Routines
- start_of_hour()
- start_of_week()
- start_of_week_sunday()
- start_of_month()
- start_of_quarter()
- start_of_year()
- is_datetime()
- easter_day()
- Charting Routines
- line_chart()
- google_line_chart()
Example of get_routine_dependencies():
CALL common_schema.get_routine_dependencies( 'common_schema', 'get_routine_dependencies');
+-------------+--------------------+-----------+------+
| schema_name | object_name | object_type | action |
+-------------+--------------------+-----------+------+
| common_schema | get_sql_dependencies | procedure | call |
| mysql | proc | table | select |
+-------------+--------------------+-----------+------+
2 rows in set (0.07 sec)
Common Schema risks
Ear Candy
In this week's ear candy we discussreplication challenges with GTIDs.
At the movies
This week in at the movies, we present a video on how toInsert Data Into MySQL Database using jQuery + AJAX + PHP
Where you can see us
Sheeri will be at theJuly Boston MySQL User Groupon Monday, July 14th, where we'll have David Berube talking about "Improving Your Database Designs with Practical Database Theory"..
Gerry will be at theJuly Seattle MySQL User Groupon Monday, July 14th.
Sheeri will be attending theTDWI Executive Summitin Boston from Sunday July 20th through Wednesday July 23rd.
Gerry will be attendingOSCon 2014from Sunday July 20th through Thursday July 24th.
Feedback
Facebook group
Google+ page
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter:@oursqlcast
orTweet about @oursqlcast
Trackback URL for this post:
http://technocation.org/trackback/1323