您的当前位置：首页 OurSQLEpisode191:CommonSchema,part1_MySQL

OurSQLEpisode191:CommonSchema,part1_MySQL

来源：华拓科技网

This week we start talking about common schema by Shlomi Noach. Ear Candy is some caveats about GTIDs and replication, and At the Movies is using jQuery, AJAX and PHP to insert data into 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

crc()- returns a crc 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

Example of get_routine_dependencies():

CALL common_schema.get_routine_dependencies( 'common_schema', 'get_routine_dependencies');

+-------------+--------------------+-----------+------+

+-------------+--------------------+-----------+------+

+-------------+--------------------+-----------+------+

2 rows in set (0.07 sec)

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()

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

group

Google+ page

e-mail: podcast at technocation.org

voicemail using phone/Skype: +1-617-674-2369

:@oursqlcast

orTweet about @oursqlcast

Trackback URL for this post:

http://technocation.org/trackback/1323