ศุกร์, 24 พ.ค. 2013
 
 

SMSKP-PayPal

Donate using PayPal
Amount:
Note:
Note:

PTT Oil Price

Gold Status

SM SKP ADS-1

สมุยสเก็ตอัพ คอมมิวนิตี้
Newsfeeds
Planet MySQL
Planet MySQL - http://www.planetmysql.org/

  • An old note on the Storage Engine API
    Whenever I stick my head into the MySQL storage engine API, I’m reminded of a MySQL User Conference from several years ago now. Specifically, I’m reminded of a slide from an early talk at the MySQL User Conference by Paul McCullagh describing developing PBXT. For “How to write a Storage Engine for MySQL”, it went something like this: Develop basic INSERT (write_row) support – INSERT INTO t1 VALUES (42) Develop full table scan (rnd_init, rnd_next, rnd_end)  - SELECT * from t1 If you’re sane, stop here. A lot of people stop at step 3. It’s a really good place to stop too. It avoids most of the tricky parts that are unexpected, undocumented and unlogical (yes, I’m inventing words here).

  • Non-Deterministic Query in Replication Stream
    You might find a warning like the below in your error log: 130522 17:54:18 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. Statement: INSERT INTO tbl2 SELECT * FROM tbl1 WHERE col IN (417,523) What do MariaDB and MySQL mean with this warning? The server can’t guarantee that this exact query, with STATEMENT based replication, will always yield identical results on the slave. Does that mean that you have to use ROW based (or MIXED) replication? Possibly, but not necessarily. For this type of query, it primarily refers to the fact that without ORDER BY, rows have no order and thus a result set may show up in any order the server decides. Sometimes it’s predictable (depending on storage engine and index use), but that’s not something you want to rely on. You don’t have to ponder that, as an ORDER BY is never harmful. Would ORDER BY col solve the problem? That depends! If col is unique, yes. If col is not unique, then multiple rows could result and they’d still have a non-deterministic order. So in that case you’d need to ORDER BY col,anothercol to make it absolutely deterministic. The same of course applies if the WHERE clause only referred to a single col value: if multiple rows can match, then it’s not unique and it will require an additional column for the sort. There are other query constructs where going to row based or mixed replication is the only way. But, just because the server tells you it can’t safely replicate a query with statement based replication, that doesn’t mean you can’t use statement based replication at all… there might be another way.

  • Zend 6 & Timezones
    Just went through all my PHP testing against a fresh instance of Oracle with Zend Server Community Edition 6, and found these warnings, guess that’s pretty clean for the Oracle part of the installation. I didn’t notice it before because generally I do most of my PHP development against a MySQL database. I should have been configuring the php.ini file routinely. Warning: oci_set_client_info(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in C:\Program Files (x86)\Zend\Apache2\htdocs\Oracle\Db.php on line 47 Warning: oci_set_module_name(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in C:\Program Files (x86)\Zend\Apache2\htdocs\Oracle\Db.php on line 48 Warning: oci_set_action(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in C:\Program Files (x86)\Zend\Apache2\htdocs\Oracle\Db.php on line 69 Turns out Zend 6 doesn’t automatically set the [Date] elements in the php.ini file, which is required for the oci_set_client_info(), oci_set_module_name(), and oci_set_action() functions of the OCI. You can find the php.ini file in the C:\Program Files (x86)\Zend\ZendServer\etc folder on Windows: [Date] ; Defines the default timezone used by the date functions ; http://php.net/date.timezone ;date.timezone =   ; http://php.net/date.default-latitude ;date.default_latitude = 31.7667   ; http://php.net/date.default-longitude ;date.default_longitude = 35.2333   ; http://php.net/date.sunrise-zenith ;date.sunrise_zenith = 90.583333   ; http://php.net/date.sunset-zenith ;date.sunset_zenith = 90.583333 You can find the values for date.timezone here. Update the date.timezone as follows: date.timezone = America/Denver Then, reboot the Zend Server, and it fixes the warning messages.

  • MariaDB 5.5.31 Now Available
    The MariaDB project is pleased to announce the immediate availability of the MariaDB 5.5.31. This is a Stable (GA) release. See the Release Notes and Changelog for detailed information on this release and the What is MariaDB 5.5? page in the AskMonty Knowledgebase for general information about the MariaDB 5.5 series. Download MariaDB 5.5.31 Release Notes Changelog What is MariaDB 5.5? This release is primarily a bug-fix release. Includes MariaDB 5.3.12 and MySQL 5.5.31 This release includes MariaDB 5.3.12 and MySQL 5.5.31. Other Notable Updates Includes XtraDB from Percona Server-5.5.30-rel30.2 Includes an alpha version of the QUERY_CACHE_INFO plugin, that allows to see the content of the query cache via the corresponding INFORMATION_SCHEMA table. MariaDB MSI packages for Windows include the latest HeidiSQL 8.0. Support for FusionIO/directFS atomic writes added (MDEV-4338). See the following for more information: MariaDB Introduces Atomic Writes FusionIO DirectFS atomic write support Fusion-io Introduction Ubuntu 8.04 LTS “Hardy” and 11.10 “Oneiric” Deprecated In accordance with the MariaDB Deprecation Policy the following distributions have reached the end of their support periods (EOL): Ubuntu 8.04 LTS “Hardy” Ubuntu 11.10 “Oneiric” Mint 9 LTS “Isadora” Mint 12 “Lisa” MariaDB 5.5.31) is therefore the last MariaDB release to feature packages for these distros. The repositories for them will remain online until the 5.5.32 release. At that time they will go away. Thanks, and enjoy MariaDB!

  • Experiences with the McAfee MySQL Audit Plugin
    I recently had to do some customer work involving the McAfee MySQL Audit Plugin and would like to share my experience in this post.Auditing user activity in MySQL  has traditionally been challenging. Most data can be obtained from the slow or general log, but this involves a lot of data you don’t need too, and isn’t flexible at all. The specific problem of logging failed connection attempts has been discussed on a previous post in our blog.Starting with 5.1, the new plugin API gives us more flexibility by allowing users to extend the server’s functionality with their own code, and this is what the McAffee plugin does.Installation and configuration are straightforward following the available instructions. The only extra step I had to take was to extract the offsets for the Percona Server version I was using for the test (5.5.28-29.1). This is needed as the plugin needs the offset to some MySQL data structures that, the plugin authors say, aren’t exposed by a consistent API. If you also need to do this, the details are clearly explained here.The plugin writes its output in json format, and supports writing it directly to a file, or to a unix socket, which means you can write a script to listen on this socket and process the audit records as you wish.Performance-wise, I did basic tests on the VM I was working in and didn’t get significant differences between either output option, or between using the plugin or enabling the general log. Bear in mind these were basic tests (just a few mysqlslap runs with increasing levels of concurrency), but initially, I would think the advantage of the plugin is its flexibility, and not its performance, which seems to be on par with having the general log enabled.The flexibility comes from the three variables that can be set to control what is logged by the plugin: - audit_record_cmds : This is the list of commands you want written to the log (all the lists in these variables are comma separated). As pointed here, anything that would generate a write to the general log will be sent to the plugin, and you can control if it gets written on not with this list. I tested this with “connect,Quit” to log successful and failed connections. Yes, it had to be a capital Q in Quit for that to work, and no, my code-fu was not enough to understand why that is the case. Maybe someone more knowledgeable in MySQL internals can enlighten me here. - audit_record_objs : List of database objects (tables, according to the docs) for which you want events written to the log. - audit_whitelist_users : This one is undocumented on the wiki at the time of writing, and is a list of users for which you do not want events written to the log.Just for reference, these are the lines I had to add to my config file for the plugin to work (plus one commented line for switching between file and socket for output): plugin-load=AUDIT=libaudit_plugin.so audit_offsets=6464, 6512, 4072, 4512, 104, 2584 audit_json_file=1 audit_json_socket_name=/tmp/audit.sock #audit_json_socket=1 audit_json_log_file=/var/lib/mysql/audit.log audit_record_cmds=connect,Quit Notice the audit_offsets that I mentioned had to be extracted due to this Percona Server version not being included in the binary.And here’s a few sample output lines generated by the plugin with this configuration:{"msg-type":"activity","date":"1369155747373","thread-id":"6439","query-id":"0","user":"debian-sys-maint","priv_user":"debian-sys-maint","host":"localhost","cmd":"Connect","query":"Connect"} {"msg-type":"activity","date":"1369155747373","thread-id":"6439","query-id":"219309","user":"debian-sys-maint","priv_user":"debian-sys-maint","host":"localhost","cmd":"Quit","query":"Quit"} {"msg-type":"activity","date":"1369155747383","thread-id":"6440","query-id":"0","user":"debian-sys-maint","priv_user":"debian-sys-maint","host":"localhost","cmd":"Connect","query":"Connect"} In conclusion, the plugin API seems to be opening new possibilities of extending MySQL’s behavior in a way that, once set up, is transparent to users, and the McAfee MySQL Audit Plugin is only one of example of what can be achieved with it. It is a very good one for me, since I think proper audit trail support has been an important missing feature on the server, which has made using MySQL in PCI or SOX compliant environments, to name just two, artificially complicated, as one had to rely on too much info (general log) or external help (snort or similar IDS).The post Experiences with the McAfee MySQL Audit Plugin appeared first on MySQL Performance Blog.