Newsfeeds
Planet MySQL
Planet MySQL - https://planet.mysql.com

  • Fun with Bugs #72 - On MySQL Bug Reports I am Subscribed to, Part IX
    I've subscribed to more than 60 new bug reports since my previous post in this series. It means that I'd need 4-5 posts to cover all new subscriptions and reasons behind them. I still plan to write about most of the bug reports I was interested in recently, but for this post I decided to pick up only MySQL 8.0 regression bugs and pay special attention to those that could be handled better or faster by Oracle engineers, as well as those handled perfectly.The initial reason for this special attention originally was Bug #93085 - "Stall when concurrently execute create/alter user with flush privilege", that caused a lot of interesting Twitter discussions. It took some time, comments (in the bug report and in social media) and pressure from MySQL Community (including yours truly) to get it accepted as a real (regression!) bug to work on, and got "Verified". Unfortunately too often recently I see more time spent on arguing that something is not a bug or can not be reproduced, or is an example of improper usage of some MySQL feature etc instead of simply checking how things worked before MySQL 8.0 and how this changed, to worse.Another example of "interesting" approach to bugs in MySQL 8.0 is Bug #93102 - "I_S queries don't return column names in the same case as requested.". It's indeed a duplicate of old and well known Bug #84456 - "column names in metadata appearing as uppercase when selecting from I_S" reported at early 8.0 development stage by Shane Bester from Oracle and community user (see Bug #85947). Still, it was decided NOT to fix it and tell users to rely on workaround, while this breaks application compatibility and is a regression.Take a look at Bug #92998 - "After finishing the backup with mysqldump, mysql crashes and restarts" also. It ended up in "Unsupported" status, with some statements that "Dumping and restoring data between different 8.0 releases is not supported". This can be classified as a regression by itself. What I miss is a link to the manual saying it's not supported (was not able to find it in 5 minutes) any explanation of crash and restart - supported or not, running mysqldump should NOT cause server restarts in a general case. I think this bug report could end up in many statuses, but of them all "Unsupported" is hardly correct. This my photo is far from ideal and can be criticized from different points of view, but there is no point to argue with the fact that it shows clouds in the sky. I wish the fact that MySQL 8.0 GA releases still have regression bugs is accepted with less arguing and more attention. Now let me continue with a list of recently reported regression bugs in MySQL 8.0 that were handled mostly properly: Bug #93215 - "Case statement use in conditional with sub_part index regression in 8.0". MySQL of versions < 8.0 (and MariaDB 10.3.7) work as expected also. The bug was verified fast, but it still misses explicit "regression" tag. Bug #93214 - "LIMIT is applied before HAVING when you have a subquery". The bug was "Verified" quickly, but I still miss the exact 8.0.x version(s) affected and the results of checking with older versions. I strongly suspect it's a regression, as MariaDB 10.3.7 provides expected result: MariaDB [test]> CREATE TABLE test (id INT PRIMARY KEY, value INT);Query OK, 0 rows affected (0.510 sec)MariaDB [test]> INSERT INTO test VALUES (1, 99), (2,98), (3, 97);Query OK, 3 rows affected (0.057 sec)Records: 3  Duplicates: 0  Warnings: 0MariaDB [test]> SELECT t1.id, (SELECT t2.value FROM test t2 WHERE t1.id = t2.id) AS sub_value FROM test t1 HAVING sub_value = 99 ORDER BY value LIMIT 1;+----+-----------+| id | sub_value |+----+-----------+|  1 |        99 |+----+-----------+1 row in set (0.116 sec) Bug #93170 - "undo truncation in 8.0.13 is not crash safe". The bug was quickly verified (after all, it's a failure of existing innodb_undo.truncate_recover MTR test case), but had not got "regression" tag. I am still not sure how it was missed during regular testing and ended up in the MySQL 8.0.13 release. Bug #93147 - "Upgrade to 8.0.13 from 8.0.11 fails". In pre-8.0 releases there was no strict need to update to every intermediate minor version, so it's also a regression of a kind for any production DBA. Bug #92979 - "MySQL 8.0 performance degradation on INSERT with foreign_key_checks=0". This is a verified performance regression comparing to MySQL 5.7, but "regression" tag is still missing.  To summarize, there are some regressions noted by community users recently in MySQL 8.0 GA releases. Some of them were demonstrated with simple test cases, so it's strange they were not noted by Oracle's QA. What's worse, it seems some of Oracle engineers are not ready to accept the fact that the best ever MySQL 8.0 GA release they worked on may get some things done incorrectly and worse than before, so they seem to waste time on useless discussions that everything is OK, work as expected and nothing can be done differently.  I also see some processed and verified bug reports without detailed check for regressions presented to users or even with "regression" tag NOT added when needed.I hope this is not going to become a new trend. I wish all community bug reports and features of MySQL get as much attention and detailed study from Oracle employees as (far from perfect) JSON support in MariaDB...

  • Vue Laravel CRUD Example Tutorial From Scratch
    Vue Laravel CRUD Example Tutorial From Scratch is today’s leading topic.  Laravel is the PHP’s fastest growing Framework with scalability and flexibility. VueJS is the fastest growing Front end Library in the Javascript community. Laravel is providing VueJS support out of the box.  For this demo, we will not create separate projects for the frontend because Laravel provides the best support for Vue.js. So, we will build the Vue components inside the Laravel’s assets folder. We will create a Single Page Application(SPA) using the Laravel and Vue.js. In this SPA, you can create a post, edit the post, read the post and delete the post. We will create Laravel API Backend. Vue Laravel CRUD Example Tutorial Now, first, we need to install the Laravel application. So let us install new Laravel by the following command. #1: Install Laravel 5.7 laravel new vuelaravelcrud   Now, go inside the project folder and install the frontend dependencies using the following command. npm install Also, open the project in your editor. I am using Visual Studio Code. code . Setup the database configuration inside the .env file. DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=vuecrud DB_USERNAME=root DB_PASSWORD=root Save the file, and now your backend can connect the MySQL database.  Now, start the compiling assets using the following command. npm run dev   We can also run the following command to compile the assets as you write the new code or modify the existing code. npm run watch #2: Install Vue dependency and edit configurations. Type the following command to install the vue-router and vue-axios dependencies. The vue-router is used for routing our Vue.js application to use the different components and the vue-axios for sending the network request to the server. npm install vue-router vue-axios --save Now, we need to change the app.js file inside resources  >> js folder. Modify with the following code inside the app.js file. // App.js require('./bootstrap'); window.Vue = require('vue'); import VueRouter from 'vue-router'; Vue.use(VueRouter); import VueAxios from 'vue-axios'; import axios from 'axios'; Vue.use(VueAxios, axios); Vue.component('example-component', require('./components/ExampleComponent.vue')); const router = new VueRouter({ mode: 'history'}); const app = new Vue(Vue.util.extend({ router })).$mount('#app'); Here, we have configured the vue-router and vue-axios library. Now, inside the resources >> views folder, create one file called the post.blade.php file. Add the following code inside the post.blade.php file. <!doctype html> <html lang="{{ str_replace('_', '-', app()->getLocale()) }}"> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> <title>Laravel</title> <link href="https://fonts.googleapis.com/css?family=Nunito:200,600" rel="stylesheet" type="text/css"> <link href="/{{ mix('css/app.css') }}" type="text/css" rel="stylesheet" /> <meta name="csrf-token" value="{{ csrf_token() }}" /> </head> <body> <div id="app"> <example-component></example-component> </div> <script src="/{{ mix('js/app.js') }}" type="text/javascript"></script> </body> </html> Now, change the route inside the routes >> web.php file. We are building the Single Page Application using Laravel and Vue. So we need to define the following route, where we can push any route and then it will give the correct route without any error. Otherwise, it will give the 404 error because, in Laravel, you have not defined any route, it is the Vue where you have defined the various component route. Due to the following code, now you can be able to use Laravel route proxy as a Vue route, and you can display the Vue components depending on the current URL. <?php Route::get('/{any}', function () { return view('post'); })->where('any', '.*'); Save the file and go to the browser and see the result. You can see that we have successfully integrated the Vue component in our Laravel application. #3: Create Vue Components Inside the resources >>  js folder, create a folder called components and inside that folder, create four following vue components. HomeComponent.vue CreateComponent.vue EditComponent.vue IndexComponent.vue // HomeComponent.vue <template> <div class="row justify-content-center"> <div class="col-md-8"> <div class="card card-default"> <div class="card-header">Home Component</div> <div class="card-body"> I'm the Home Component component. </div> </div> </div> </div> </template> <script> export default { mounted() { console.log('Component mounted.') } } </script> // CreateComponent.vue <template> <div class="row justify-content-center"> <div class="col-md-8"> <div class="card card-default"> <div class="card-header">Create Component</div> <div class="card-body"> I'm the Create Component component. </div> </div> </div> </div> </template> <script> export default { mounted() { console.log('Component mounted.') } } </script> // EditComponent.vue <template> <div class="row justify-content-center"> <div class="col-md-8"> <div class="card card-default"> <div class="card-header">Edit Component</div> <div class="card-body"> I'm an Edit component. </div> </div> </div> </div> </template> <script> export default { mounted() { console.log('Example Component mounted.') } } </script> // IndexComponent.vue <template> <div class="row justify-content-center"> <div class="col-md-8"> <div class="card card-default"> <div class="card-header">Index Component</div> <div class="card-body"> I'm an Index component. </div> </div> </div> </div> </template> <script> export default { mounted() { console.log('Index Component mounted.') } } </script> #4: Configure the vue-router Inside the app.js file, write the following code. // app.js require('./bootstrap'); window.Vue = require('vue'); import VueRouter from 'vue-router'; Vue.use(VueRouter); import VueAxios from 'vue-axios'; import axios from 'axios'; import App from './App.vue'; Vue.use(VueAxios, axios); import HomeComponent from './components/HomeComponent.vue'; import CreateComponent from './components/CreateComponent.vue'; import IndexComponent from './components/IndexComponent.vue'; import EditComponent from './components/EditComponent.vue'; const routes = [ { name: 'home', path: '/', component: HomeComponent }, { name: 'create', path: '/create', component: CreateComponent }, { name: 'posts', path: '/posts', component: IndexComponent }, { name: 'edit', path: '/edit/:id', component: EditComponent } ]; const router = new VueRouter({ mode: 'history', routes: routes}); const app = new Vue(Vue.util.extend({ router }, App)).$mount('#app'); Here, we have imported the four components and also defined the routes for our application. Then we have created a router object and passed it to our Vue application. Now, create one more vue component inside the resources >> js folder called App.vue and add the following code in it. // App.vue <template> <div class="container"> <div> <transition name="fade"> <router-view></router-view> </transition> </div> </div> </template> <style> .fade-enter-active, .fade-leave-active { transition: opacity .5s } .fade-enter, .fade-leave-active { opacity: 0 } </style> <script> export default{ } </script> Here, we have defined our router- view. It is the directive that will render the component according to current URL. So, if our URL is /create, then it will present the CreateComponent on the webpage. Save the file, and if your npm run watch is not running, then you might need to compile again and then go to the browser and go to these URLs for testing and see if the project is working or not. http://vuelaravelcrud.test/create http://vuelaravelcrud.test/posts http://vuelaravelcrud.test/edit/21 If you are getting the exact component, then you are perfectly fine and if not then check out for the error on the terminal as well as the console panel in the browser. #5: Create a Navigation bar Write the following code inside the App.vue file. I have added the navbar. // App.vue <template> <div class="container"> <nav class="navbar navbar-expand-sm bg-dark navbar-dark"> <ul class="navbar-nav"> <li class="nav-item"> <router-link to="/" class="nav-link">Home</router-link> </li> <li class="nav-item"> <router-link to="/create" class="nav-link">Create Post</router-link> </li> <li class="nav-item"> <router-link to="/posts" class="nav-link">Posts</router-link> </li> </ul> </nav><br /> <transition name="fade"> <router-view></router-view> </transition> </div> </template> <style> .fade-enter-active, .fade-leave-active { transition: opacity .5s } .fade-enter, .fade-leave-active { opacity: 0 } </style> <script> export default{ } </script> Save the file and see the output on the browser.   #6: Create a Form Write the following code inside the CreateComponent.vue file. We will add the Bootstrap Form to create a post. // CreatePost.vue <template> <div> <h1>Create A Post</h1> <form @submit.prevent="addPost"> <div class="row"> <div class="col-md-6"> <div class="form-group"> <label>Post Title:</label> <input type="text" class="form-control" v-model="post.title"> </div> </div> </div> <div class="row"> <div class="col-md-6"> <div class="form-group"> <label>Post Body:</label> <textarea class="form-control" v-model="post.body" rows="5"></textarea> </div> </div> </div><br /> <div class="form-group"> <button class="btn btn-primary">Create</button> </div> </form> </div> </template> <script> export default { data(){ return { post:{} } }, methods: { addPost(){ console.log(this.post); } } } </script> So, we have taken the two fields. Post Title and Post Body. We have made one method called addPost().  So, when a user submits the form, we will get the input inside the addPost() method. From then, we will send a POST request to the Laravel server and to save the data into the database. I am skipping the validation of each field because this article is getting long and long. So we will do it in another post. Save the file and go to this URL: http://vuelaravelcrud.test/create or /create. You can see the form like below.   #7: Create a Laravel Backend The primary purpose of the Laravel Framework in this example is to build a backend API. So, first, we will create a schema for post table. Also, we need a Post model. So let’s create both using the following command. php artisan make:model Post -m Now, write the following schema inside [timestamp]create_posts_table.php file. public function up() { Schema::create('posts', function (Blueprint $table) { $table->increments('id'); $table->string('title'); $table->text('body'); $table->timestamps(); }); } Migrate the database using the following command. php artisan migrate Next, inside the Post.php model, write the following code to prevent the mass assignment exception. <?php // Post.php namespace App; use Illuminate\Database\Eloquent\Model; class Post extends Model { protected $fillable = ['title', 'body']; } Also, create a controller using the following command. php artisan make:controller PostController We are using Laravel Resource Collection for API development. So let us create a Laravel Resource Collection using the following command. php artisan make:resource PostCollection When building an API, you may need a transformation layer that sits between your Eloquent models and the JSON responses that are returned to your application’s users. Laravel’s resource classes allow you to expressively and quickly transform your models and model collections into JSON. The PostCollection resource is generated inside app >> Http >>Resources >> PostCollection.php file. <?php // PostCollection.php namespace App\Http\Resources; use Illuminate\Http\Resources\Json\ResourceCollection; class PostCollection extends ResourceCollection { /** * Transform the resource collection into an array. * * @param \Illuminate\Http\Request $request * @return array */ public function toArray($request) { return parent::toArray($request); } } #8: Define the CRUD operations First, we define the function that stores the data inside the MySQL database. <?php // PostController.php namespace App\Http\Controllers; use Illuminate\Http\Request; use App\Http\Resources\PostCollection; use App\Post; class PostController extends Controller { public function store(Request $request) { $post = new Post([ 'title' => $request->get('title'), 'body' => $request->get('body') ]); $post->save(); return response()->json('success'); } } Now, write the edit, update, index, and delete functions as well. <?php // PostController.php namespace App\Http\Controllers; use Illuminate\Http\Request; use App\Http\Resources\PostCollection; use App\Post; class PostController extends Controller { public function store(Request $request) { $post = new Post([ 'title' => $request->get('title'), 'body' => $request->get('body') ]); $post->save(); return response()->json('successfully added'); } public function index() { return new PostCollection(Post::all()); } public function edit($id) { $post = Post::find($id); return response()->json($post); } public function update($id, Request $request) { $post = Post::find($id); $post->update($request->all()); return response()->json('successfully updated'); } public function delete($id) { $post = Post::find($id); $post->delete(); return response()->json('successfully deleted'); } } #9: Define the api routes Now, we need to define the API routes inside the routes >> api.php file. <?php // api.php use Illuminate\Http\Request; Route::middleware('auth:api')->get('/user', function (Request $request) { return $request->user(); }); Route::post('/post/create', 'PostController@store'); Route::get('/post/edit/{id}', 'PostController@edit'); Route::post('/post/update/{id}', 'PostController@update'); Route::delete('/post/delete/{id}', 'PostController@delete'); Route::get('/posts', 'PostController@index'); #10: Use Axios to send a network request Now, we have created the backend. Next step is to send a POST request to the Laravel development server. Write the following code inside the CreateComponent.vue file’s addPost() function. // CreateComponent.vue addPost(){ let uri = 'http://vuelaravelcrud.test/api/post/create'; this.axios.post(uri, this.post).then((response) => { this.$router.push({name: 'posts'}); }); } So, it will post a request to the server’s api point with the data and the server saves the data. Now, display the posts. So let us edit the IndexComponent.vue file. // IndexComponent.vue <template> <div> <h1>Posts</h1> <div class="row"> <div class="col-md-10"></div> <div class="col-md-2"> <router-link :to="{ name: 'create' }" class="btn btn-primary">Create Post</router-link> </div> </div><br /> <table class="table table-hover"> <thead> <tr> <th>ID</th> <th>Item Name</th> <th>Item Price</th> <th>Actions</th> </tr> </thead> <tbody> <tr v-for="post in posts" :key="post.id"> <td>{{ post.id }}</td> <td>{{ post.title }}</td> <td>{{ post.body }}</td> <td><router-link :to="{name: 'edit', params: { id: post.id }}" class="btn btn-primary">Edit</router-link></td> <td><button class="btn btn-danger">Delete</button></td> </tr> </tbody> </table> </div> </template> <script> export default { data() { return { posts: [] } }, created() { let uri = 'http://vuelaravelcrud.test/api/posts'; this.axios.get(uri).then(response => { this.posts = response.data.data; }); } } </script> Here, when the component is created, we send the network request to fetch the data. Now, here, we need to write the data because axios is by default wrap our backend data posts with data and we are using the Resource Collection. So it will also wrap the data in the data key. So to get the actual data, we need to write like response.data.data. If we have not used axios then we might write the response.data, but axios also has backend data template like response.data, and that is why we need to write the response.data.data. #11: Send edit and update request Now, when our edit component loads, we need to fetch the data from the server to display the existing data. Then, after changing the data in the textbox and textarea, we hit the update button and we call the updatePost() function to send a post request to the server to update the data. // EditComponent.vue <template> <div> <h1>Edit Post</h1> <form @submit.prevent="updatePost"> <div class="row"> <div class="col-md-6"> <div class="form-group"> <label>Post Title:</label> <input type="text" class="form-control" v-model="post.title"> </div> </div> </div> <div class="row"> <div class="col-md-6"> <div class="form-group"> <label>Post Body:</label> <textarea class="form-control" v-model="post.body" rows="5"></textarea> </div> </div> </div><br /> <div class="form-group"> <button class="btn btn-primary">Update</button> </div> </form> </div> </template> <script> export default { data() { return { post: {} } }, created() { let uri = `http://vuelaravelcrud.test/api/post/edit/${this.$route.params.id}`; this.axios.get(uri).then((response) => { this.post = response.data; }); }, methods: { updatePost() { let uri = `http://vuelaravelcrud.test/api/post/update/${this.$route.params.id}`; this.axios.post(uri, this.post).then((response) => { this.$router.push({name: 'posts'}); }); } } } </script> Now, you can try to edit the data and update the form, and you can see that we can now update the data. #12: Delete the data Now, the only remaining thing is to delete or remove the data from the database. So, let us write the final code inside the IndexComponent.vue file. // IndexComponent.vue <template> <div> <h1>Posts</h1> <div class="row"> <div class="col-md-10"></div> <div class="col-md-2"> <router-link :to="{ name: 'create' }" class="btn btn-primary">Create Post</router-link> </div> </div><br /> <table class="table table-hover"> <thead> <tr> <th>ID</th> <th>Item Name</th> <th>Item Price</th> <th>Actions</th> </tr> </thead> <tbody> <tr v-for="post in posts" :key="post.id"> <td>{{ post.id }}</td> <td>{{ post.title }}</td> <td>{{ post.body }}</td> <td><router-link :to="{name: 'edit', params: { id: post.id }}" class="btn btn-primary">Edit</router-link></td> <td><button class="btn btn-danger" @click.prevent="deletePost(post.id)">Delete</button></td> </tr> </tbody> </table> </div> </template> <script> export default { data() { return { posts: [] } }, created() { let uri = 'http://vuelaravelcrud.test/api/posts'; this.axios.get(uri).then(response => { this.posts = response.data.data; }); }, methods: { deletePost(id) { let uri = `http://vuelaravelcrud.test/api/post/delete/${id}`; this.axios.delete(uri).then(response => { this.posts.splice(this.posts.indexOf(id), 1); }); } } } </script> So, here, we have taken the click event in vue to grab the id of the current post and send that id to remove the data from the database as well as remove the data from the array at the client side using the splice function as well. Finally, Vue Laravel CRUD Example Tutorial From Scratch is over. Thanks for taking. You can use the following Github Code for reference. Github Code The post Vue Laravel CRUD Example Tutorial From Scratch appeared first on AppDividend.

  • Visualizing the MySQL Bug Tide (2018 edition)
    I've updated the bug tide graph I made in 2016 with today's data. The source code and details are here.

  • Migrating to Amazon Aurora: Optimize for Binary Log Replication
    In this Checklist for Success series, we will discuss reducing unknowns when hosting in the cloud using and migrating to Amazon Aurora. These tips might also apply to other database as a service (DBaaS) offerings. In our previous article, we discussed the importance of continuous query performance analysis, especially in Amazon Aurora where there is less diagnostic visibility compared to running on EC2 or on-premise. Aside from uptime though, we need a lot more from our data, and we definitely cannot isolate it in Aurora. Next on our checklist is that at one point or another, we will need to use asynchronous replication. Amazon Aurora has an excellent reputation for absorbing intense amounts of writes, and for many cases where you need an asynchronous replica, any replica can have potential issues catching up. Different Clusters for Different Workloads Critical workloads and datasets cannot rely on a single copy of their data. With Amazon Aurora, predictable performance means avoiding mixing workloads within your production cluster. While read heavy workloads might fit easily into read-replicas, reporting or analytics workloads might not be a good fit to execute on your main cluster where read-what-you-write profiles are normally found. You can either delegate this on a separate asynchronous replica as separate Amazon Aurora cluster, or another that runs on an EC2 instance (as an example). This is also true if, say, your analytics or reporting workload generate a significant amount of disk IOPs. Amazon Aurora IO bills operations per million. You might save some money running disk-heavy analytics operations on a replica running on an i3 instance with a local NVMe for example. Similarly, running an async replica on an EC2 instance or on-premise allows you to take your own independent backups, or just an extra level of redundancy. Multi-Threaded Replication It is a known fact that MySQL asynchronous replication performance is subject to some limitations. The biggest one is that, by default, it is single-threaded. MySQL 5.6 introduced multi-threaded replication at one database per thread. This did not apply to the majority of use cases, as workloads vary per database and therefore create an imbalance. With MySQL 5.7 (Aurora 2.0), there have been additional improvements such as an alternative algorithm in parallelizing thread execution that depends on certain behaviors regarding how acting primary servers write binary log entries. With that said, certain multi-threaded replication variables (transaction_write_set_extraction) require that the binlog format is set to ROW. This might sound counter-intuitive because the ROW binlog format actually can increase the replication workload. While ROW format reduces the ambiguity from potentially non-deterministic statements that could cause a replica to drift and become inconsistent, critical operations (schema changes) and optimizations (MTS) requires that you use ROW binlog format. It should be apparent by now that the only reasonable path forward to improving asynchronous replication is via the multi-threaded approach. Along with that, there is the need for ROW binlog format. Any design effort should always include this fact if async replication lag is considered a risk. For the basics, configuration options like slave_compressed_protocol  and binlog_row_image can reduce network churn. In the deep end, reducing dataset hotspots, ensuring tables have PRIMARY KEYs and embracing multi-threaded optimization can also go a long way. Additional Benefits While running certain read-heavy queries on an async replica, or ensuring you have access to physical datafiles are common use cases, being able to switch to another location (region) or just simply another cluster might also be necessary for some instances. Adding or dropping a column/index on a large table can be achieved with either pt-online-schema-change or gh-ost. But for cases where time is a constraint, applying schema changes on an asynchronous cluster and switching to that sometimes pays for itself. Configuration changes or upgrades that require a cluster restart can take seconds or even minutes. Wouldn’t it be nice if you already had a cluster with all these changes ready to take over at a moment’s notice? Or even fail back to the original if there was an unforeseen issue? Stay “tuned” for part three. Meanwhile, we’d like to hear your success stories in Amazon Aurora in the comments below! Don’t forget to come by and see us at AWS re:Invent, November 26-30, 2018 in booth 1605! Percona CEO Peter Zaitsev will deliver a keynote on MySQL High Availability & Disaster Recovery, Tuesday, November 27 at 1:45 PM – 2:45 PM in the Bellagio Hotel, Level 1, Gauguin 2

  • MySQL: when will the password of my users expire ?
    Has you may already know, in MySQL it is possible to set an expiration period for passwords. It’s not always obvious to see when the password will expire. And most of the time if you don’t pay much attention you will get something like this: ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. Let me share you a small query that check for how long your password is still valid in case it was created with an expiration delay or if the global default_password_lifetime is set on the server (from MySQL 5.7.4 to 5.7.10 it was set to 360 by default)! select user, password_last_changed, concat( cast( IFNULL(password_lifetime, @@default_password_lifetime) as signed) + cast(datediff(password_last_changed, now()) as signed), " days") expires_in from mysql.user where cast( IFNULL(password_lifetime, @@default_password_lifetime) as signed) + cast(datediff(password_last_changed, now()) as signed) >= 0 and user not like 'mysql.%'; Let’s see the result when default_password_lifetime is set to 0 (actual default): +-------+-----------------------+------------+ | user | password_last_changed | expires_in | +-------+-----------------------+------------+ | test | 2018-11-16 13:12:11 | 2 days | | test2 | 2018-11-10 13:16:44 | 4 days | +-------+-----------------------+------------+ Now let’s change that global variable: mysql> set global default_password_lifetime=20; +-------+-----------------------+------------+ | user | password_last_changed | expires_in | +-------+-----------------------+------------+ | fred | 2018-11-12 21:59:56 | 16 days | | test | 2018-11-16 13:12:11 | 2 days | | test2 | 2018-11-10 13:16:44 | 4 days | +-------+-----------------------+------------+ That was the easy useful query of the day

Copyright © 2018 Global Relief Center. All Rights Reserved.