Month: January 2017

FOSDEM talks

I will be heading to Brussels on Friday for FOSDEM.

On Friday, February 3rd, I will attend the Pre-FOSDEM MySQL Day where I will give two talks:

How Booking.com avoids and deals with replication lag (at 12:05),
Monitoring Booking.com without looking at MySQL (at 15:30).

(A summary of those talks can be found in Le Fred’s blog.)

Then, on Saturday, February 4th, I have a talk in the MySQL

New course: Introduction to Query Store

Our first new course of the year, and our 50th online training course with Pluralsight!! Erin’s latest Pluralsight course has been published – SQL Server: Introduction to Query Store – and is just over three hours long. It’s based on her very popular user group and conference session, but much expanded with lots of cool demos. The modules are: Introduction […]

The post New course: Introduction to Query Store appeared first on Paul S. Randal.

Profiling MyRocks with perf: Good Old Bug #68079 Use Case

Almost a year ago I’ve got really interested in MyRocks and built MySQL from Facebook that provides it from source. Since that time I build it from fresh sources few times per week (as I’ve described in that post) and once in a while try to work with it and study some details or use cases. Today I’d like to discuss one of them that I’ve recently studied with perf profiler.This is not only because I am going to talk about applying profilers to all kinds and forks of MySQL at FOSDEM 2017 MySQL & Friends Devroom this week. It seems profilers is the only way to study or troubleshoot MyRocks performance problems (if any) at the moment (when provided status variables and SHOW ENGINE ROCKSDB STATUS etc are not enough), as Facebook’s MySQL does NOT have Performance Schema compiled in by default:mysql> show engines;+————+———+—————————————————————-+————–+——+————+| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |+————+———+—————————————————————-+————–+——+————+| ROCKSDB    | DEFAULT | RocksDB storage engine                                         | YES          | YES  | YES        || MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         || MyISAM     | YES     | MyISAM storage engine                                          | NO           | NO   | NO         || BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         || CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         || MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         || ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         || FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       || InnoDB     | NO      | Supports transactions, row-level locking, and foreign keys     | NULL         | NULL | NULL       |+————+———+—————————————————————-+————–+——+————+9 rows in set (0.00 sec)This may change when MyRocks builds from Percona or MariaDB will appear as ready for common use, but for now perf is a way to go, and I like it!I do not remember how exactly I’ve come up with the idea to compare MyRocks scalability to InnoDB scalability on my old QuadXeon box (now running Fedora 25):[openxs@fc23 mysql-server]$ pt-summary# Percona Toolkit System Summary Report ######################        Date | 2017-01-30 11:32:53 UTC (local TZ: EET +0200)    Hostname | fc23      Uptime |  4:02,  3 users,  load average: 3.47, 2.12, 1.27    Platform | Linux     Release | Fedora release 25 (Twenty Five)      Kernel | 4.9.5-200.fc25.x86_64Architecture | CPU = 64-bit, OS = 64-bit   Threading | NPTL 2.24     SELinux | Enforcing Virtualized | No virtualization detected# Processor ##################################################  Processors | physical = 1, cores = 4, virtual = 4, hyperthreading = no      Speeds | 4×2499.000      Models | 4xIntel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz      Caches | 4×2048 KB# Memory #####################################################       Total | 7.8G        Free | 2.9G        Used | physical = 1.0G, swap allocated = 0.0, swap used = 0.0, virtual = 1.0G      Shared | 176.0M     Buffers | 3.9G      Caches | 6.2G       Dirty | 208 kB     UsedRSS | 2.5G  Swappiness | 60 DirtyPolicy | 20, 10 DirtyStatus | 0, 0…for the famous use case presented in Bug #68079. Scalability problems identified there cause a lot of work by Oracle engineers, and a lot of changes in MySQL 5.6.x and 5.7.x (so natable that MariaDB decided to use only those from 5.7 in the upcoming MariaDB 10.2, see MDEV-10476). I’ve already mentioned this bug probably in every my public talk about MySQL since 2013, so one day I decided to check how the fix helped for recent InnoDB and then compare to MyRocks performance with all default settings on the same hardware for different number of concurrent threads.So, one day in April 2016 I took a dump of tables involved from the bug, replaced InnoDB with ROCKSDB there, added explicit CHARSET=utf8 COLLATE=utf8_bin clauses, and loaded the dump into Facebook’s MySQL 5.6 built from source. I’ve ended up with the following:mysql> show create table task\G*************************** 1. row ***************************       Table: taskCreate Table: CREATE TABLE `task` (  `sys_id` char(32) COLLATE utf8_bin NOT NULL DEFAULT ”,  `u_root_cause` char(32) COLLATE utf8_bin DEFAULT NULL,  `u_business_impact_description` mediumtext COLLATE utf8_bin,  `u_business_impact_category` mediumtext COLLATE utf8_bin,  PRIMARY KEY (`sys_id`)) ENGINE=ROCKSDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin1 row in set (0.00 sec)mysql> show create table incident\G*************************** 1. row ***************************       Table: incidentCreate Table: CREATE TABLE `incident` (  `sys_id` char(32) COLLATE utf8_bin NOT NULL DEFAULT ”,  `category` varchar(40) COLLATE utf8_bin DEFAULT NULL,  PRIMARY KEY (`sys_id`),  KEY `incident_category` (`category`)) ENGINE=ROCKSDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin1 row in set (0.00 sec)mysql> show table status like ‘task’\G*************************** 1. row ***************************           Name: task         Engine: ROCKSDB        Version: 10     Row_format: Dynamic           Rows: 8292 Avg_row_length: 69    Data_length: 573991…1 row in set (0.00 sec)mysql> show table status like ‘incident’\G*************************** 1. row ***************************           Name: incident         Engine: ROCKSDB        Version: 10     Row_format: Dynamic           Rows: 8192 Avg_row_length: 87    Data_length: 719091Max_data_length: 0   Index_length: 956624…1 row in set (0.00 sec)The tables are simple and small. Then I tried to study how well this query scales when number of threads it is running from is increasing:mysql> explain select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category; +—-+————-+———-+——–+—————————+——————-+———+———————-+——+————-+| id | select_type | table    | type   | possible_keys             | key               | key_len | ref                  | rows | Extra       |+—-+————-+———-+——–+—————————+——————-+———+———————-+——+————-+|  1 | SIMPLE      | incident | index  | PRIMARY,incident_category | incident_category | 123     | NULL                 | 8192 | Using index ||  1 | SIMPLE      | task     | eq_ref | PRIMARY                   | PRIMARY           | 96      | test.incident.sys_id |    1 | Using index |+—-+————-+———-+——–+—————————+——————-+———+———————-+——+————-+2 rows in set (0.00 sec)For this I’ve used mysqlslap and commands like the following:[openxs@fc23 fb56]$ bin/mysqlslap -uroot –iterations=10 –concurrency=1 –create-schema=test –no-drop –number-of-queries=1000 –query=’select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category’with different –concurrency values: 1, 2, 4, 8, 10, 16, 32 (on a box with 4 cores). Before the fix InnoDB had scalability problems even with concurrency N, where N is the number of cores. After the fix it scaled way better in this test.You can find the summary of results in this Google spreadsheet, but here is a chart from it: The X axis has log scale and shows number of concurrent threads running. The Y axis is the average number of problematic queries executed per second. “inner” and “straight” refer to INNER JOIN vs STRAIGHT_JOIN queries presented above.”MyRocks” on this chart means ROCKSDB in MySQL from Facebook built from source corresponding to the following commit:[openxs@fc23 mysql-5.6]$ git log -1commit 6b4ba98698182868800f31e0d303f1e16026efdf…Date:   Sat Jan 28 13:51:47 2017 -0800while “InnoDB 5.7” means InnoDB from Oracle MySQL 5.7.17, the following commit specifically:[openxs@fc23 mysql-server]$ git log -1commit 23032807537d8dd8ee4ec1c4d40f0633cd4e12f9…Date:   Mon Nov 28 16:48:20 2016 +0530For this post I wonder why the difference in favor of “straight” query is so big starting from 4 concurrent threads in case of MyRocks? I’ve profiled both queries running via mysqlslap as follows:[openxs@fc23 ~]$ perf –versionperf version 4.9.6.200.fc25.x86_64.g51a0[openxs@fc23 ~]$ sudo perf record -a[sudo] password for openxs:^C[ perf record: Woken up 335 times to write data ][ perf record: Captured and wrote 84.913 MB perf.data (1825548 samples) ]Profiler was started, then mysqlslap command executed and Ctrl-C was pressed when it produced the results.The result for STRAIGHT_JOIN queries was the following (up to 1% of “overhead”):[openxs@fc23 ~]$ sudo perf report –stdio | more…# Total Lost Samples: 0## Samples: 1M of event ‘cycles:p’# Event count (approx.): 62748127726390## Overhead  Command          Shared Object                     Symbol…     5.32%  my-oneconnectio  libc-2.24.so                      [.] __memcmp_sse4_1     4.68%  my-oneconnectio  libc-2.24.so                      [.] __memcpy_ssse3     4.04%  my-oneconnectio  mysqld                            [.] rocksdb::BlockIter::Seek     3.33%  my-oneconnectio  mysqld                            [.] my_strnxfrm_unicode     3.11%  my-oneconnectio  mysqld                            [.] rocksdb::BlockBasedTable::Get     2.96%  my-oneconnectio  mysqld                            [.] myrocks::Rdb_pk_comparator::Compare     2.54%  my-oneconnectio  mysqld                            [.] rocksdb::BlockIter::BinarySeek     2.14%  my-oneconnectio  mysqld                            [.] rocksdb::InternalKeyComparator::Compare     2.00%  my-oneconnectio  mysqld                            [.] rocksdb::StatisticsImpl::recordTick     1.99%  my-oneconnectio  mysqld                            [.] rocksdb::MergingIterator::Next     1.95%  my-oneconnectio  mysqld                            [.] rocksdb::HistogramStat::Add     1.51%  my-oneconnectio  mysqld                            [.] join_read_key     1.51%  my-oneconnectio  mysqld                            [.] myrocks::rdb_unpack_utf8_str     1.41%  my-oneconnectio  mysqld                            [.] myrocks::Rdb_key_def::unpack_record     1.36%  my-oneconnectio  mysqld                            [.] sub_select     1.32%  my-oneconnectio  mysqld                            [.] my_uni_utf8     1.29%  my-oneconnectio  mysqld                            [.] rocksdb::Version::Get     1.22%  my-oneconnectio  libc-2.24.so                      [.] _int_malloc     1.22%  my-oneconnectio  mysqld                            [.] rocksdb::TableCache::Get     1.22%  my-oneconnectio  mysqld                            [.] rocksdb::BlockIter::Next     1.21%  my-oneconnectio  mysqld                            [.] rocksdb::ThreadLocalPtr::Get     1.18%  my-oneconnectio  mysqld                            [.] rocksdb::DBIter::FindNextUserEntryInternal     1.18%  my-oneconnectio  mysqld                            [.] rocksdb::(anonymous namespace)::FilePicker::GetNextFile     1.12%  my-oneconnectio  libc-2.24.so                      [.] malloc     1.07%  my-oneconnectio  mysqld                            [.] evaluate_join_record     1.07%  my-oneconnectio  mysqld                            [.] myrocks::Rdb_key_def::pack_index_tuple     1.01%  my-oneconnectio  mysqld                            [.] key_restoreThe result for INNER JOIN queries was the following (up to 1% of “overhead”):[openxs@fc23 ~]$ sudo perf report –stdio | more…# Total Lost Samples: 0## Samples: 1M of event ‘cycles:p’# Event count (approx.): 162704428520300## Overhead  Command          Shared Object                  Symbol…#     5.90%  my-oneconnectio  libc-2.24.so                   [.] __memcpy_ssse3     4.38%  my-oneconnectio  libpthread-2.24.so             [.] pthread_mutex_lock     3.69%  my-oneconnectio  libc-2.24.so                   [.] __memcmp_sse4_1     3.58%  my-oneconnectio  mysqld                         [.] rocksdb::BlockIter::Seek     2.53%  my-oneconnectio  libpthread-2.24.so             [.] pthread_mutex_unlock     2.47%  my-oneconnectio  mysqld                         [.] rocksdb::StatisticsImpl::recordTick     2.35%  my-oneconnectio  mysqld                         [.] rocksdb::BlockBasedTable::Get     2.28%  my-oneconnectio  mysqld                         [.] my_strnxfrm_unicode     2.08%  my-oneconnectio  mysqld                         [.] rocksdb::BlockIter::BinarySeek     1.84%  my-oneconnectio  mysqld                         [.] rocksdb::HistogramStat::Add     1.84%  my-oneconnectio  mysqld                         [.] rocksdb::Version::Get     1.71%  my-oneconnectio  mysqld                         [.] myrocks::rdb_unpack_binary_or_utf8_varchar_space_pad     1.69%  my-oneconnectio  mysqld                         [.] rocksdb::LRUCacheShard::Lookup     1.67%  my-oneconnectio  mysqld                         [.] my_uni_utf8     1.61%  my-oneconnectio  mysqld                         [.] rocksdb::InternalKeyComparator::Compare     1.52%  my-oneconnectio  mysqld                         [.] myrocks::Rdb_pk_comparator::Compare     1.47%  my-oneconnectio  mysqld                         [.] rocksdb::(anonymous namespace)::FilePicker::GetNextFile     1.23%  my-oneconnectio  mysqld                         [.] rocksdb::BlockIter::Next     1.15%  my-oneconnectio  mysqld                         [.] rocksdb::MergingIterator::Next     1.10%  my-oneconnectio  mysqld                         [.] join_read_key     1.09%  my-oneconnectio  mysqld                         [.] rocksdb::Block::NewIterator     1.04%  my-oneconnectio  mysqld                         [.] rocksdb::TableCache::GetI am not an expert in MyRocks at all, so I will not even try to interpret these results (maybe in some later post, one day. I’ve just highlighted what I consider a real difference in the second (INNER JOIN) case. Note pthread_mutex_lock/pthread_mutex_unlock calls, for example that are NOT present in “more than 1%” output for STRAIGHT_JOIN case.I’d be happy to get any comments on the above. I also plan to share more/raw data from perf some day later, when I decide where to put them for public access (we speak about text outputs megabytes in size).I’ve captured profiles with callgraphs as well:[openxs@fc23 ~]$ sudo perf record -ag^C[ perf record: Woken up 565 times to write data ][ perf record: Captured and wrote 142.410 MB perf.data (644176 samples) ]Again, full trees are huge and require real expert to be analyzed properly.New perf report option (-g flat, available since kernels 4.4+) gave nice backtraces like this:           40.20%                handle_one_connection                do_handle_one_connection                dispatch_command                mysql_parse                mysql_execute_command                execute_sqlcom_select                handle_select                mysql_select                JOIN::exec                sub_select                evaluate_join_record                sub_select                join_read_key                myrocks::ha_rocksdb::index_read_map_impl                myrocks::ha_rocksdb::get_row_by_rowid                myrocks::Rdb_transaction_impl::get                rocksdb::TransactionBaseImpl::Get                rocksdb::WriteBatchWithIndex::GetFromBatchAndDB                rocksdb::DBImpl::Get                rocksdb::DBImpl::GetImpl                rocksdb::Version::Getthat show how data are read in MyRocks. You can get similar backtraces with pt-pmp as well, but you’ll see only how many times specific backtrace was noted, not what was the related overhead. Also, performance impact from pt-pmp would be very notable in this case.Time to stop it seems, the post is already long. One day I’ll continue, as there are many phenomena highlighted by this very simple, but famous, use case. Stay tuned!

What’s New in Laravel 5.4

If you’ve been following Scotch for a while, you heard a lot about Laravel. Some months ago, Laravel 5.3 was released. It introduced new features like Mailables.
Yesterday, the 25th of January, Laravel 5.4 was released.

It comes with no surprise that it brought a lot of new and exciting features to Laravel, and for the remainder of this article, we shall go through the new features.
New Middlewares
With the release of 5.4, two new middlewares are now shipped with the framework. If you don’t know what middlewares are, check out Understanding Laravel Middlewares.
The middlewares are:
Trim Strings Middleware
Just as the name implies, this middleware trims extra spaces from request data. For example, a user submits their email through a form on your website and mistakenly types in some extra space after the email.
This middleware will automatically trim whitespace, so if a user submits something like this.
// ‘demo@example.org ‘
Because of the existence of this middleware (\Illuminate\Foundation\Http\Middleware\TrimStrings::class) in App/Kernel.php, it gets converted into.
// ‘demo@example.org’
Convert Empty Strings to Null
Still, as the name implies, this middleware converts empty strings to null. So if a user submits an empty form instead of getting ” it gets converted to null.
Higher order messages
This is perhaps one of my favourite feature ever added to Laravel. It much easier to show than explain.
Imagine we have a collection of blog posts and we want to perform an operation on each item in the collection. Normally, we would do this.
Learn about Collections in Laravel.
// Loop through all the posts and schedule them for sharing to twitter
$posts->each(function ($post) {
return $post->schedule(TWITTER);
});
With High order messages in Laravel, we can simplify the above code into this.
$posts->each->schedule(TWITTER);
Yes, this is now a possibility. It doesn’t just stop here either, we can take this a bit further and chain them. Here, another scenario.
/**
* Loop through all the posts and reject any archived post
* Then for the remaining posts, schedule each of them to twitter.
*/
$posts->reject(function ($post) {
return $post->archived;
})->each(function ($post) {
return $post->schedule(TWITTER);
});
With high order messages, we can simplify the above code into this.
$posts->reject->archived->each->schedule(TWITTER);
Want to see how it’s done, check out this commit. This is simply mindblowing

From Elixir to Mix
Laravel Elixir provided us with a gulp wrapper that made development and packaging of assets easier.

The next version of Laravel Elixir is changing the underlying system and will be built on Webpack, instead of Gulp. This will replace the plugin ecosystem, and because of such a significant change, it was time to rename the package.

Also, Laravel Mix comes with a new helper function called mix() that serves as a replacement to the former elixir() function.
If you have a project still using Elixir, you can continue using it, as it is still supported and not going anywhere anytime soon.
Fluent Routing
The Route facade in Laravel is now fluent. Previously, if we wanted to name a route, we would do this.
Route::get(‘user/{id}/profile’, function ($id) {
//
})->name(‘profile’);
Now, we can do this.
Route::name(‘profile’)->get(‘user/{id}/profile’, function ($id) {
// some closure action…
});
We could register a route name and a middleware
Route::name(‘users.index’)->middleware(‘auth’)->get(‘users’, function () {
// some closure action…
});
Registering a middleware with a route prefix and group
Route::middleware(‘auth’)->prefix(‘api’)->group(function () {
// register some routes…
});
Registering a middleware to a resource controller
Route::middleware(‘auth’)->resource(‘photo’, ‘PhotoController’);
Components and Slots
Components and slots allow us to simplify HTML elements into reusable areas.
In our application, we usually have components like modals, notification panel etc. We can now define them as components and reuse them.
To create a component, we simply create a new blade file and refer to it using.
@component(‘path.to.blade.file’)
Modal text go in here
@endcomponent
You can read all about components and slots here.
Real-Time Facades

Facades provide a “static” interface to classes that are available in the application’s service container. Laravel ships with many facades which provide access to almost all of Laravel’s features. Laravel facades serve as “static proxies” to underlying classes in the service container, providing the benefit of a terse, expressive syntax while maintaining more testability and flexibility than traditional static methods.

One example that comes to mind is Auth. In laravel, we access an authenticated user’s data by doing Auth::user(). This Auth class is a facade.
Now in Laravel 5.4, we can create facades on the fly. All we need do is namespace class on the Facade namespace and we can use the class as a facade.
namespace Facade\App\User;
Doing this will automatically convert our User model into a facade.
Laravel dusk

Laravel Dusk provides an expressive, easy-to-use browser automation and testing API. By default, Dusk does not require you to install JDK or Selenium on your machine. Instead, Dusk uses a standalone ChromeDriver installation. However, you are free to utilise any other Selenium compatible driver you wish.

Basically, it means we can test our Laravel code like we would in a web browser. Currently, Laravel uses browser-kit for testing.
The problem with browser-kit is that it is limited, and Dusk serves as an alternative with more features.
Dusk can detect AJAX etc. A full-length article on Dusk is on the way, stay tuned.
Fix: Specified key was too long error

Laravel 5.4 made a change to the default database character set, and it’s now utf8mb4 which includes support for storing emojis. This only affects new applications and as long as you are running MySQL v5.7.7 and higher you do not need to do anything.
For those running MariaDB or older versions of MySQL you may hit this error when trying to run migrations:
[Illuminate\Database\QueryException]
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table users add unique users_email_unique(email))

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
As outlined in the Migrations guide to fix this all you have to do is edit your AppServiceProvider.php file and inside the boot method set a default string length:
use Illuminate\Support\Facades\Schema;

public function boot()
{
Schema::defaultStringLength(191);
}
Conclusion
Some features not explained above include
Resourceful Controller with Model Boilerplate: in simple terms, it is route model bindings for controllers.
JSON Based Language Files: this means that translation for text in Laravel can now be done with JSON files instead of PHP files. Learn More.
Markdown Mailables: this allows us to build Mailables in laravel using the Markdown syntax.
Map Eloquent Events to Dedicated Classes. Here is a video that further explains this.
If something was omitted, please let us know in the comment. Also, there is a free video series on Laracasts that shows What’s New in Laravel 5.4.

Docker Security Vulnerability CVE-2016-9962

CVE-2016-9962Docker 1.12.6 was released to address CVE-2016-9962. CVE-2016-9962 is a serious vulnerability with RunC. Quoting the coreos page (linked above): “RunC allowed additional container processes via runc exec to be ptraced by the pid 1 of the container. This allows the main processes of the container, if running as root, to gain access to file-descriptors of these new […]

Installing and Configuring Oracle GoldenGate Veridata 12c

This note demonstrates how to install and configure Oracle GoldenGate Veridata 12c both server as well as agent. At a high level the steps include: Install Veridata Server Create the GoldenGate Veridata Repository Schema using RCU Configure WebLogic domain for Oracle GoldenGate Veridata Start Admin and Managed Servers Create the You need to be logged […]

Installing and Configuring Oracle GoldenGate Veridata 12c

This note demonstrates how to install and configure Oracle GoldenGate Veridata 12c both server as well as agent. At a high level the steps include: Install Veridata Server Create the GoldenGate Veridata Repository Schema using RCU Configure WebLogic domain for Oracle GoldenGate Veridata Start Admin and Managed Servers Create the VERIDATA_ADMIN user Launch and test […]

MySQL Reporting using AutoSQL Tool

Three ways to schedule a MySQL query
If you walk through any office you see people working in Excel. With MySQL for Excel (https://www.mysql.com/why-mysql/windows/excel/) you can already let them pull information from Excel themselves. However, in some cases it saves a lot of time if they don’t have to pull the information, but it’s pushed automatically.
Exception lists are the best examples of queries you want to push to users. If you have a query with occasional results, you don’t want to check for this every day. In this case you just want to receive a mail if there are any results. Eg a list of stuck invoices which can’t be processed automatically.
How can we do this?
1. Using the MySQL Event Scheduler
The MySQL Event scheduler can be used to run a query on a predefined schedule and output the results in CSV format.
Let’s write a simple query to output stuck invoices to a CSV file (which can be imported with Excel).

SELECT * FROM erp.invoice WHERE status = ‘stuck’
INTO OUTFILE ‘c:\\DailyReports\\stuck_invoices.csv’
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’;

Note that you can specify some formatting options of the file like the separator and the line terminator. Choosing the options above will allow Excel to open the file without problems.
Now let’s create a new MySQL Event to have it run every day at 9 AM:

CREATE
EVENT
erp.daily_invoice_stock
ON SCHEDULE AT ‘2017-01-29 09:00:00’ + INTERVAL 1 day
DO
SELECT * FROM erp.invoice WHERE status = ‘stuck’
INTO OUTFILE ‘c:\\DailyReports\\stuck_invoices.csv’
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’;

Creating events in MySQL does requires the ‘EVENT’ privilege on the schema in which the event is created.
2. Scripting
Using command line tools you can do a lot. The main benefit from using the events is that you don’t need specific database permissions to create them. Instead you could create a simple batch script and schedule it using the Windows Task Scheduler.
Create a file C:\invoice_stuck.sql with the query you want to schedule:

SELECT * FROM erp.invoice WHERE status = ‘stuck’
INTO OUTFILE ‘c:\\DailyReports\\stuck_invoices.csv’
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’;

Create a batch script c:\execute_query.cmd with the following contents:

mysql.exe –u query_exec –p my_password <c:\invoice_stuck.sql

Now schedule this batch file with Windows Task Scheduler to run at any interval.
3. Using a commercial application like AutoSQL
There are several 3rd party tools to do the same and more. The major benefits are:
 No special MySQL database privileges needed to create events (like with scripting)
 Outputting to genuine Excel format (dates, numbers and columns nicely formatted)
 Option to email the results
 Easy for any user to setup
Here we will use AutoSQL (http://www.autosql.net), a simple single purpose tool to schedule queries and specify the output. It actually creates action lists which can be scheduled by Windows Task Scheduler.
AutoSQL uses an ODBC connection to connect to a data source. Therefore you need to have the MySQL ODBC driver installed (https://dev.mysql.com/downloads/connector/odbc/) and setup an ODBC connection (https://www.youtube.com/watch?v=K3GZidOwGmM).
Setup the query
Now choose the MySQL ODBC connection in the ‘Get DSN’ Window and copy the SQL query in the query window:Specifying the

Specifying the output
On the ‘Output’ tab we can specify that we want to output it to an Excel and send an email with the file attached. Here you can also specify you only want to send it if there are more than 0 results.

After you tested it, you can click on ‘Get Background Command’. This copies the background execution command to the clipboard. You can use this in Windows Task Scheduler to actually execute the command on a schedule.

Conclusion

If you need to schedule a query and have no specific needs on the output format using the Event scheduler from MySQLor a simple script is perfect. However the output options are limited (CSV) and it is not really user friendly for the average user.
If you need better output and you are not into heavy scripting, take a look at the commercial options available.
 
 

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569