Reset a MySQL root password

Reset a MySQL root password

  • Last updated on: 2016-06-13
  • Authored by: Rackspace Support

The MySQL root password allows the root user to have full access to the MySQL database. You must have (Linux) root or (Windows) Administrator access to the Cloud Server to reset the MySQL root password.

Note: The Cloud Server (Linux) root or (Windows) Administrator account password is not the same as the MySQL password. The Cloud Server password allows access to the server. The MySQL root password allows access only to the MySQL database.

Use the following steps to reset a MySQL root password by using the command line interface.

Stop the MySQL service

(Ubuntu and Debian) Run the following command:

sudo /etc/init.d/mysql stop

(CentOS, Fedora, and Red Hat Enterprise Linux) Run the following command:

sudo /etc/init.d/mysqld stop

Start MySQL without a password

Run the following command. The ampersand (&) at the end of the command is required.

sudo mysqld_safe --skip-grant-tables &

Connect to MySQL

Run the following command:

mysql -uroot

Set a new MySQL root password

Run the following command:

use mysql;

update user set password=PASSWORD("mynewpassword") where User='root';

flush privileges;

quit

Stop and start the MySQL service

(Ubuntu and Debian) Run the following commands:

sudo /etc/init.d/mysql stop
...
sudo /etc/init.d/mysql start

(CentOS, Fedora, and Red Hat Enterprise Linux) Run the following commands:

sudo /etc/init.d/mysqld stop
...
sudo /etc/init.d/mysqld start

Log in to the database

Test the new password by logging in to the database.

mysql -u root -p

You are prompted for your new password.

Yii 1.1: Drills : Search by a HAS_MANY relation in Yii 1.1

Sometimes we get lost trying to search by a HAS_MANY relation using CActiveRecord or CActiveDataProvider in Yii 1.1. This article is a series of drills that try to describe the practical techniques of searching by a HAS_MANY relation.

Note: Note that this article is Yii 1.1 specific. For Yii 2, please read the new article – Drills : Search by a HAS_MANY relation in Yii 2.0 that is completely rewritten for Yii 2.0.

Relation

Two entities are sometimes connected with a relation of 1:N. Or we may say that 1:N is the only possible relation between 2 entities as long as we are in the RDB world. 1:1 relation is just a particular kind of 1:N where N is always assumed to be 1 at the maximum. And N:N relation can be considered as a combination of two 1:N relations.

Yii supports this 1:N relation in CActiveRecord as BELONGS_TO and HAS_MANY relations. 1:N relation seen from the side of N is BELONG_TO, and from the side of 1 it is HAS_MANY.

BELONGS_TO relation is fairly easy. There’s not so much to talk about it. But HAS_MANY can be very tough sometimes.

Now, let’s construct an example of 1:N relation.

Example of HAS_MANY

Think about blog posts and their authors. It’s a relation of “An Author has many Posts” and “A Post belongs to an Author” at the same time.

/**
 * Author model
 * @property integer $id
 * @property integer $name Author's name
...
 */
class Author extends CActiveRecord
{
    ...
    public function relations()
    {
        return array(
            'posts' => array(self::HAS_MANY, 'Post', 'author_id');
        );
    }
    ...
/**
 * Post model
 * @property integer $id
 * @property integer $author_id FK to Author's id
 * @property integer $title Title of Post
...
 */
class Post extends CActiveRecord
{
    ...
    public function relations()
    {
        return array(
            'author' => array(self::BELONGS_TO, 'Author', 'author_id');
        );
    }
    ...

We are going to solve the possible use cases of search regarding this example.

Task #1

Show all posts that has a word in post title

I want to start with an easy one. Let’s retrieve all the posts that has a cirtain word in their title.

public static function GetPostsByTitle($searchWord)
{
    // query criteria
    $criteria = new CDbCriteria();
    // compare title
    $criteria->compare('title', $searchWord, true);
    // find
    $posts = Post::model()->findAll($criteria);
    // show
    foreach($posts as $post)
    {
        echo "Title = " . $post->title . "\n";
    }
}

Ah, it was too easy. And no relation is involved in this task.

OK, let’s move on to the next.

Task #2

Show all posts with their authors that has a certain word in post title

Now we have to retrieve the authors’ name, too.

public static function GetPostsWithAuthorByTitle($searchWord)
{
    // query criteria
    $criteria = new CDbCriteria();
    // with Author model
    $criteria->with = array('author');
    // compare title
    $criteria->compare('t.title', $searchWord, true);
    // find all posts
    $posts = Post::model()->findAll($criteria);
    // show all posts
    foreach($posts as $post)
    {
        echo "Title = " . $post->title . "\n";
        echo "Author = " . $post->author->name . "\n";
    }
}

Well, you may say “You don’t have to do it. Just add a line to echo $post->author->name to the code of the 1st task.”

Yes, you are right. Post model has author relation, so all you have to do to retrieve its Author is just $post->author. How convenient is it! It’s so-called lazy loading approach.

But the lazy loading approach has a drawback in this case, because you have to execute one query for retrieving an entire array of Posts plus every one query per each Post for retrieving its author. It will end up in 1+N queries to be executed.

Here we are retrieveing Posts and their Authors at the same time by specifying CDbCriteria::with. It enables you to do the job with a single query that joins the related tables. This is so-called eager loading approach.

The eager loading is preferrable in this particular case, because it’s more effective. But, you have to note, it is not always so.

Please note that you have to disambiguate the column name with table aliases when you have joined the tables using with. We use the fixed alias of t for the main table. And the alias for the related table is usually the same as the relation name.

So you can write like this when you want to search by Author’s name.

...
    // compare Author's name
    $criteria->compare('author.name', $searchName, true);
    ...

Quite simple, isn’t it? I do love CActiveRecord of Yii. It’s quite convenient.

Huh? Who said “It’s just a BELONGS_TO relation. Yeah, it’s simple. I know.”?

Yes, you are right, definitely. Let’s move on to the next task where we will deal with HAS_MANY.

Task #3

Show all authors who has at least one post that has a certain word in post title

Now we will retrieve the authors, not the posts.

public static function GetAuthorsByPostTitle($searchWord)
{
    // query criteria
    $criteria = new CDbCriteria();
    // with Post model
    $criteria->with = array('posts');
    // compare title
    $criteria->compare('posts.title', $searchWord, true);
    // find all authors
    $authors = Author::model()->findAll($criteria);
    // show all authors
    foreach($authors as $author)
    {
        echo "Author = " . $author->name . "\n";
    }
}

Umm, is it OK? Seems OK, but looks too simple. We don’t care. Let’s move on.

Task #4

Show all authors with his/her all posts who has at least one post that has a certain word in post title

Hmmm. Just a small addition to the 3rd task. Something like this?

Wrong Answer

public static function GetAuthorsWithPostsByPostTitle($searchWord)
{
    // query criteria
    $criteria = new CDbCriteria();
    // with Post model
    $criteria->with = array('posts');
    // compare title
    $criteria->compare('posts.title', $searchWord, true);
    // find all authors
    $authors = Author::model()->findAll($criteria);
    // show all authors and his/her posts
    foreach($authors as $author)
    {
        echo "Author = " . $author->name . "\n";
        foreach($author->posts as $post)
        {
            echo "Post = " . $post->title . "\n";
        }
    }
}

BOOP!! You’ve got hooked.

Why?

If the task had been “Show all authors with his/her relevant posts who has at least one post that has a certain word in post title”, then it would have been the answer. But the task is “with his/her all posts“. Your answer doesn’t show the posts which don’t have that certain word in their titles.

Because you are comparing the post titles, the posts without the certain word in their titles are omitted from the query results.

We want to do the lazy loading of the posts in order to retrieve all the posts, but at the same time we need to join the post table using with in order to compare the post title.

How to solve this dilemma?

Answer

public static function GetAuthorsWithPostsByPostTitle($searchWord)
{
    // query criteria
    $criteria = new CDbCriteria();
    // join Post model (without selecting)
    $criteria->with = array(
        'posts' => array(
            'select' => false,
        ),
    );
    // compare title
    $criteria->compare('posts.title', $searchWord, true);
    // find all authors
    $authors = Author::model()->findAll($criteria);
    // show all authors and his/her posts
    foreach($authors as $author)
    {
        echo "Author = " . $author->name . "\n";
        foreach($author->posts as $post)
        {
            echo "Post = " . $post->title . "\n";
        }
    }
}

You can join the table without fetching its data by specifying select property to false in the definition of the relation.

Now you can do the lazy loading of Posts by this trick.

Please take note that the definition of the relation can be dynamically changed on the fly as you see in the code above. It overrides the definition of the relation declared in relations() method.

Now, let’s move on to the next one. I tell you, it is a difficult one, in fact.

Task #5

Show top 5 authors in the order of name with his/her all posts who has at least one post that has a certain word in post title

OK, so we need to add “LIMIT” and “ORDER”. Going to try with this one.

Trial #1

public static function GetTop5AuthorsWithPostsByPostTitle($searchWord)
{
    // query criteria
    $criteria = new CDbCriteria();
    // with Post model
    $criteria->with = array('posts');
    // compare title
    $criteria->compare('posts.title', $searchWord, true);
    // order by author name
    $criteria->order = 't.name ASC';
    // limit to 5 authors
    $criteria->limit = 5;
    // find all authors
    $authors = Author::model()->findAll($criteria);
    // show all authors and his/her posts
    foreach($authors as $author)
    {
        echo "Author = " . $author->name . "\n";
        foreach($author->posts as $post)
        {
            echo "Post = " . $post->title . "\n";
        }
    }
}

But this will end in an error as long as $searchWord is not empty. Yii will say “There’s no column likeposts.title“. Huh? We have set ‘posts’ to ‘with’! Why is it?

The guide says:

Guide : By default, Yii uses eager loading, i.e., generating a single SQL statement, except when LIMIT is applied to the primary model.

It means that if LIMIT is applied to the primary model, then lazy loading will be used. This rule has been applied to our code above, and the query was executed without joining the author table. So, what to do then?

The guide proceeds to say:

Guide : We can set the together option in the relation declarations to be true to force a single SQL statement even when LIMIT is used.

OK. So we will modify the code to:

Trial #2

...
    // force to join Post
    $criteria->with = array(
        'posts' => array(
            'together' => true,
        ),
    );
    ...

What about this? Seems OK. You will not see the error anymore.

But, alas, you will get the strange output like this:

[search word = foo]
Author = Andy
    Post = Don't use foo
    Post = Use yoo for foo
Author = Ben
    Post = foo is great
    Post = I love foo
Author = Charlie
    Post = What's foo?
[end]

We want to show 5 authors, but the list ends where the count of posts sums up to 5.

OK. Then we will use ‘select’ => false trick again.

Trial #3

...
    // force to join Post (without selecting)
    $criteria->with = array(
        'posts' => array(
            'together' => true,
            'select' => false,
        ),
    );
    ...

But it still doesn’t work. It will show the results like this:

[search word = foo]
Author = Andy
    Post = Don't use foo
    Post = Use yoo for foo
    Post = Don't use bar
    Post = Use yar for bar
Author = Ben
    Post = foo is great
    Post = I love foo
    Post = I also love bar
Author = Charlie
    Post = What's foo?
    Post = What's bar?
[end]

It is because LIMIT is not applied to the primary table, but to the virtually constructed table that is the result of joining. It’s no use complaining about this behavior, because that’s how the query works in RDB.

But we won’t give up. Let’s try grouping then.

Trial #4

...
    // force to join Post (without selecting)
    $criteria->with = array(
        'posts' => array(
            'together' => true,
            'select' => false,
        ),
    );
    ...
    // group by Author's id
    $criteria->group = 't.id';
    ...

Wow, great! It works!

To sum it up:

Answer

public static function GetTop5AuthorsWithPostsByPostTitle($searchWord)
{
    // query criteria
    $criteria = new CDbCriteria();
    // force to join Post (without selecting)
    $criteria->with = array(
        'posts' => array(
            'together' => true,
            'select' => false,
        ),
    );
    // compare title
    $criteria->compare('posts.title', $searchWord, true);
    // group by Author's id
    $criteria->group = 't.id';
    // order by author name
    $criteria->order = 't.name ASC';
    // limit to 5 authors
    $criteria->limit = 5;
    // find all authors
    $authors = Author::model()->findAll($criteria);
    // show all authors and his/her posts
    foreach($authors as $author)
    {
        echo "Author = " . $author->name . "\n";
        foreach($author->posts as $post)
        {
            echo "Post = " . $post->title . "\n";
        }
    }
}

Notice : Unfortunately, this trick seems to work only with MySQL, which has an extended implementation of GROUP BY.

Now, here is the last task.

Task #6

Show top 5 authors in the order of name with his/her relevant posts who has at least one post that has a certain word in post title

Probably the filtering in lazy loading should be the only answer. I cant’t think of another solution.

Example of Answer

public static function GetTop5AuthorsWithPostsByPostTitle($searchWord)
{
    // query criteria
    $criteria = new CDbCriteria();
    // force to join Post (without selecting)
    $criteria->with = array(
        'posts' => array(
            'together' => true,
            'select' => false,
        ),
    );
    // compare title
    $criteria->compare('posts.title', $searchWord, true);
    // group by Author's id
    $criteria->group = 't.id';
    // order by author name
    $criteria->order = 't.name ASC';
    // limit to 5 authors
    $criteria->limit = 5;
    // find all authors
    $authors = Author::model()->findAll($criteria);
    // show all authors and his/her posts
    foreach($authors as $author)
    {
        echo "Author = " . $author->name . "\n";
        // lazy loading posts with filtering
        $filteredPosts = $author->posts(
            array(
                'condition' => 'title LIKE :search_word',
                'params' => array(
                    ':search_word' => '%' . $searchWord . '%',
                ),
            )
        );
        foreach($filteredPosts as $post)
        {
            echo "Post = " . $post->title . "\n";
        }
    }
}

It might not looks very elegant, but I think it’s a decent solution without any hacky tricks.

The key point of the answer is that you can dynamically define the query options of the relation in lazy loading.

The guide says:

Guide : Dynamic query options can also be used when using the lazy loading approach to perform relational query. To do so, we should call a method whose name is the same as the relation name and pass the dynamic query options as the method parameter.

Conclusion and Notice

Well, we’ve managed to accomplish all the tasks without getting lost.

It has been a little surprise to me, because I didn’t expect it. Originally, my intention for this article was just to show the dilemma in the search by a HAS_MANY and the reason for it.

CActiveRecord of Yii has been more powerful than I have imagined.

CActiveDataProvider

Although we didn’t take up CActiveDataProvider here, the basic concepts we discussed are also applicable to it. You should note that LIMIT is usually there to confuse you, because CActiveDataProvider is normally used with CPagination.

The Guide

“The guide” in this article refers to “The Definitive Guide to Yii”, particularly the section of “Relational Active Record”.

The Definitive Guide to Yii : Relational Active Record

It’s not quite easy to understand all the content of it, esspecially when you are new to Yii. But you MUST read it. I can also recommend to those people who feel at home with Yii to read it over once in a while. It’s worth the time.

UPDATE: Using a dedicated relation for searching

So, this is a very important update to this article.

By specifying a dedicated relation for searching, we can join the same table independently for filtering and fetching data.

For instance, the answer to the task #4 could be written like the following:

Optimized Answer to Task #4

public function relations()
{
    return array(
        'posts' => array(self::HAS_MANY, 'Post', 'author_id'),
        'posts_search' => array(self::HAS_MANY, 'Post', 'author_id'),
    );
}
 
public static function GetAuthorsWithPostsByPostTitle($searchWord)
{
    // query criteria
    $criteria = new CDbCriteria();
    // join Post model (one for fetching data, the other for filtering)
    $criteria->with = array(
        'posts' => array(  // this is for fetching data
            'together' => false,
        ),
        'posts_search' => array(  // this is for filtering
            'select' => false,
            'together' => true,
        ),
    );
    // compare title
    $criteria->compare('posts_search.title', $searchWord, true);
    // find all authors with his/her posts
    $authors = Author::model()->findAll($criteria);
    // show all authors and his/her posts
    foreach($authors as $author)
    {
        echo "Author = " . $author->name . "\n";
        foreach($author->posts as $post)  // no queries executed here !!
        {
            echo "Post = " . $post->title . "\n";
        }
    }
}

The difference between the original answer and the optimized one lies in the performance.

There were 1 + N queries executed in the original answer, because every $author->posts would trigger a query to fetch the posts.

But there are only 2 queries here. There’s no query executed for each $author->posts, because all the posts have been fetched in findAll. Yes, findAll executes 2 queries: one for the relations with ‘together’, and the other for those without ‘together’. (For filtering in the 2nd query, Yii will use the primary keys fetched in the 1st query in IN condition.)

Likewise, the answers to the task #5 and #6 could be optimized like the following:

Optimized Answer to Task #5

public static function GetTop5AuthorsWithPostsByPostTitle($searchWord)
{
    // query criteria
    $criteria = new CDbCriteria();
    // join Post model (one for fetching data, the other for filtering)
    $criteria->with = array(
        'posts' => array(  // this is for fetching data
            'together' => false,
        ),
        'posts_search' => array(  // this is for filtering
            'select' => false,
            'together' => true,
        ),
    );
    // compare title
    $criteria->compare('posts_search.title', $searchWord, true);
    // group by Author's id
    $criteria->group = 't.id';
    // order by author name
    $criteria->order = 't.name ASC';
    // limit to 5 authors
    $criteria->limit = 5;
    // find all authors with his/her posts
    $authors = Author::model()->findAll($criteria);
    // show all authors and his/her posts
    foreach($authors as $author)
    {
        echo "Author = " . $author->name . "\n";
        foreach($author->posts as $post)  // no queries executed here !!
        {
            echo "Post = " . $post->title . "\n";
        }
    }
}

Optimized Answer to Task #6

public static function GetTop5AuthorsWithPostsByPostTitle($searchWord)
{
    // query criteria
    $criteria = new CDbCriteria();
    // join Post model (one for fetching data, the other for filtering)
    $criteria->with = array(
        'posts' => array(  // this is for fetching data
            'together' => false,
            'condition' => 'posts.title LIKE :search_word',
            'params' => array(
                ':search_word' => '%' . $searchWord . '%',
            ),
        ),
        'posts_search' => array(  // this is for filtering
            'select' => false,
            'together' => true,
        ),
    );
    // compare title
    $criteria->compare('posts.title', $searchWord, true);
    // group by Author's id
    $criteria->group = 't.id';
    // order by author name
    $criteria->order = 't.name ASC';
    // limit to 5 authors
    $criteria->limit = 5;
    // find all authors
    $authors = Author::model()->findAll($criteria);
    // show all authors and his/her posts
    foreach($authors as $author)
    {
        echo "Author = " . $author->name . "\n";
        foreach($author->posts as $post)  // no queries executed here !!
        {
            echo "Post = " . $post->title . "\n";
        }
    }
}

Relational Active Record

Relational Active Record

We have already seen how to use Active Record (AR) to select data from a single database table. In this section, we describe how to use AR to join several related database tables and bring back the joint data set.

In order to use relational AR, it is recommended that primary-foreign key constraints are declared for tables that need to be joined. The constraints will help to keep the consistency and integrity of the relational data.

For simplicity, we will use the database schema shown in the following entity-relationship (ER) diagram to illustrate examples in this section.

ER Diagram

ER Diagram

Info: Support for foreign key constraints varies in different DBMS. SQLite 3.6.19 or prior does not support foreign key constraints, but you can still declare the constraints when creating tables. MySQL’s MyISAM engine does not support foreign keys at all.

1. Declaring Relationship

Before we use AR to perform relational query, we need to let AR know how one AR class is related with another.

Relationship between two AR classes is directly associated with the relationship between the database tables represented by the AR classes. From database point of view, a relationship between two tables A and B has three types: one-to-many (e.g. tbl_user and tbl_post), one-to-one (e.g. tbl_user and tbl_profile) and many-to-many (e.g. tbl_category and tbl_post). In AR, there are four types of relationships:

  • BELONGS_TO: if the relationship between table A and B is one-to-many, then B belongs to A (e.g. Postbelongs to User);
  • HAS_MANY: if the relationship between table A and B is one-to-many, then A has many B (e.g. User has many Post);
  • HAS_ONE: this is special case of HAS_MANY where A has at most one B (e.g. User has at most oneProfile);
  • MANY_MANY: this corresponds to the many-to-many relationship in database. An associative table is needed to break a many-to-many relationship into one-to-many relationships, as most DBMS do not support many-to-many relationship directly. In our example database schema, the tbl_post_categoryserves for this purpose. In AR terminology, we can explain MANY_MANY as the combination of BELONGS_TOand HAS_MANY. For example, Post belongs to many Category and Category has many Post.

There is fifth special type which performs aggregational queries on the related records – it’s called STAT. Please refer to the Statistical Query section for more details.

Declaring relationship in AR involves overriding the relations() method of CActiveRecord. The method returns an array of relationship configurations. Each array element represents a single relationship with the following format:

'VarName'=>array('RelationType', 'ClassName', 'ForeignKey', ...additional options)

where VarName is the name of the relationship; RelationType specifies the type of the relationship, which can be one of the four constants: self::BELONGS_TO, self::HAS_ONE, self::HAS_MANY and self::MANY_MANY;ClassName is the name of the AR class related to this AR class; and ForeignKey specifies the foreign key(s) involved in the relationship. Additional options can be specified at the end for each relationship (to be described later).

The following code shows how we declare the relationships for the User and Post classes.

class Post extends CActiveRecord
{
    ......
 
    public function relations()
    {
        return array(
            'author'=>array(self::BELONGS_TO, 'User', 'author_id'),
            'categories'=>array(self::MANY_MANY, 'Category',
                'tbl_post_category(post_id, category_id)'),
        );
    }
}
 
class User extends CActiveRecord
{
    ......
 
    public function relations()
    {
        return array(
            'posts'=>array(self::HAS_MANY, 'Post', 'author_id'),
            'profile'=>array(self::HAS_ONE, 'Profile', 'owner_id'),
        );
    }
}

Info: A foreign key may be composite, consisting of two or more columns. In this case, we should concatenate the names of the foreign key columns and separate them with commas or an array such as array('key1','key2'). In case you need to specify custom PK->FK association you can define it as array('fk'=>'pk'). For composite keys it will bearray('fk_c1'=>'pk_c1','fk_c2'=>'pk_c2'). For MANY_MANY relationship type, the associative table name must also be specified in the foreign key. For example, the categories relationship inPost is specified with the foreign key tbl_post_category(post_id, category_id). The declaration of relationships in an AR class implicitly adds a property to the class for each relationship. After a relational query is performed, the corresponding property will be populated with the related AR instance(s). For example, if $author represents a User AR instance, we can use$author->posts to access its related Post instances.

2. Performing Relational Query

The simplest way of performing relational query is by reading a relational property of an AR instance. If the property is not accessed previously, a relational query will be initiated, which joins the two related tables and filters with the primary key of the current AR instance. The query result will be saved to the property as instance(s) of the related AR class. This is known as the lazy loading approach, i.e., the relational query is performed only when the related objects are initially accessed. The example below shows how to use this approach:

// retrieve the post whose ID is 10
$post=Post::model()->findByPk(10);
// retrieve the post's author: a relational query will be performed here
$author=$post->author;

Info: If there is no related instance for a relationship, the corresponding property could be either null or an empty array. For BELONGS_TO and HAS_ONE relationships, the result is null; for HAS_MANYand MANY_MANY, it is an empty array. Note that the HAS_MANY and MANY_MANY relationships return arrays of objects, you will need to loop through the results before trying to access any properties. Otherwise, you may receive “Trying to get property of non-object” errors.

The lazy loading approach is very convenient to use, but it is not efficient in some scenarios. For example, if we want to access the author information for N posts, using the lazy approach would involve executing N join queries. We should resort to the so-called eager loading approach under this circumstance.

The eager loading approach retrieves the related AR instances together with the main AR instance(s). This is accomplished by using the with() method together with one of the find or findAll methods in AR. For example,

$posts=Post::model()->with('author')->findAll();

The above code will return an array of Post instances. Unlike the lazy approach, the author property in eachPost instance is already populated with the related User instance before we access the property. Instead of executing a join query for each post, the eager loading approach brings back all posts together with their authors in a single join query!

We can specify multiple relationship names in the with() method and the eager loading approach will bring them back all in one shot. For example, the following code will bring back posts together with their authors and categories:

$posts=Post::model()->with('author','categories')->findAll();

We can also do nested eager loading. Instead of a list of relationship names, we pass in a hierarchical representation of relationship names to the with() method, like the following,

$posts=Post::model()->with(
    'author.profile',
    'author.posts',
    'categories')->findAll();

The above example will bring back all posts together with their author and categories. It will also bring back each author’s profile and posts.

Eager loading may also be executed by specifying the CDbCriteria::with property, like the following:

$criteria=new CDbCriteria;
$criteria->with=array(
    'author.profile',
    'author.posts',
    'categories',
);
$posts=Post::model()->findAll($criteria);

or

$posts=Post::model()->findAll(array(
    'with'=>array(
        'author.profile',
        'author.posts',
        'categories',
    )
));

Sometimes we need to perform query using relation but don’t want to get related models. Let’s assume we have Users who posted many Posts. Post can be published but also can be in a draft state. This is determined by published field in the post model. Now we need to get all users who have published posts and we are not interested in posts themselves. This can be achieved the following way:

$users=User::model()->with(array(
    'posts'=>array(
        // we don't want to select posts
        'select'=>false,
        // but want to get only users with published posts
        'joinType'=>'INNER JOIN',
        'condition'=>'posts.published=1',
    ),
))->findAll();

4. Relational Query Options

We mentioned that additional options can be specified in relationship declaration. These options, specified as name-value pairs, are used to customize the relational query. They are summarized as below.

  • select: a list of columns to be selected for the related AR class. It defaults to ‘*’, meaning all columns. Column names referenced in this option should be disambiguated.
  • condition: the WHERE clause. It defaults to empty. Column names referenced in this option should be disambiguated.
  • params: the parameters to be bound to the generated SQL statement. This should be given as an array of name-value pairs.
  • on: the ON clause. The condition specified here will be appended to the joining condition using the ANDoperator. Column names referenced in this option should be disambiguated. This option does not apply toMANY_MANY relations.
  • order: the ORDER BY clause. It defaults to empty. Column names referenced in this option should be disambiguated.
  • with: a list of child related objects that should be loaded together with this object. Be aware that using this option inappropriately may form an infinite relation loop.
  • joinType: type of join for this relationship. It defaults to LEFT OUTER JOIN.
  • alias: the alias for the table associated with this relationship. It defaults to null, meaning the table alias is the same as the relation name.
  • together: whether the table associated with this relationship should be forced to join together with the primary table and other tables. This option is only meaningful for HAS_MANY and MANY_MANY relations. If this option is set false, the table associated with the HAS_MANY or MANY_MANY relation will be joined with the primary table in a separate SQL query, which may improve the overall query performance since less duplicated data is returned. If this option is set true, the associated table will always be joined with the primary table in a single SQL query, even if the primary table is paginated. If this option is not set, the associated table will be joined with the primary table in a single SQL query only when the primary table is not paginated. For more details, see the section “Relational Query Performance”.
  • join: the extra JOIN clause. It defaults to empty. This option has been available since version 1.1.3.
  • joinOptions: the property for setting post-JOIN operations such as USE INDEX. String typed value can be used with JOINs for HAS_MANY and MANY_MANY relations, while array typed value designed to be used only with MANY_MANY relations. First array element will be used for junction table JOIN and second array element will be used for target table JOIN. This option has been available since version 1.1.15.
  • group: the GROUP BY clause. It defaults to empty. Column names referenced in this option should be disambiguated.
  • having: the HAVING clause. It defaults to empty. Column names referenced in this option should be disambiguated.
  • index: the name of the column whose values should be used as keys of the array that stores related objects. Without setting this option, an related object array would use zero-based integer index. This option can only be set for HAS_MANY and MANY_MANY relations.
  • scopes: scopes to apply. In case of a single scope can be used like 'scopes'=>'scopeName', in case of multiple scopes can be used like 'scopes'=>array('scopeName1','scopeName2'). This option has been available since version 1.1.9.

In addition, the following options are available for certain relationships during lazy loading:

  • limit: limit of the rows to be selected. This option does NOT apply to BELONGS_TO relation.
  • offset: offset of the rows to be selected. This option does NOT apply to BELONGS_TO relation.
  • through: name of the model’s relation that will be used as a bridge when getting related data. This option has been available since version 1.1.7 where it can be used for HAS_ONE and HAS_MANY. Since 1.1.14 it can be used for BELONGS_TO as well.

Below we modify the posts relationship declaration in the User by including some of the above options:

class User extends CActiveRecord
{
    public function relations()
    {
        return array(
            'posts'=>array(self::HAS_MANY, 'Post', 'author_id',
                            'order'=>'posts.create_time DESC',
                            'with'=>'categories'),
            'profile'=>array(self::HAS_ONE, 'Profile', 'owner_id'),
        );
    }
}

Now if we access $author->posts, we would obtain the author’s posts sorted according to their creation time in descending order. Each post instance also has its categories loaded.

Note: when using eager loading such relation options as ‘order’, ‘group’, ‘having’, ‘limit’ and ‘offset’ will be ignored. You should setup such parameters at the main model criteria level if you wish them to be applied.

5. Disambiguating Column Names

When a column name appears in two or more tables being joined together, it needs to be disambiguated. This is done by prefixing the column name with its table’s alias name.

In relational AR query, the alias name for the primary table is fixed as t, while the alias name for a relational table is the same as the corresponding relation name by default. For example, in the following statement, the alias name for Post and Comment is t and comments, respectively:

$posts=Post::model()->with('comments')->findAll();

Now assume both Post and Comment have a column called create_time indicating the creation time of a post or comment, and we would like to fetch posts together with their comments by ordering first the posts’ creation time and then the comments’ creation time. We need to disambiguate the create_time column like the following:

$posts=Post::model()->with('comments')->findAll(array(
    'order'=>'t.create_time, comments.create_time'
));

Tip: The default alias of a related table is the name of the relation. Please note that if you’re using relation from within another relation the alias will be the former relation name only and will not be prefixed with the parent relation. For example, the alias for ‘author.group’ relation is ‘group’, not ‘author.group’.

$posts=Post::model()->with('author', 'author.group')->findAll(array(
  'order'=>'group.name, author.name, t.title'
));

You can avoid the collision of tables’ aliases by specifying the alias property of the relation.

$comments=Comment::model()->with(array(
  'author',
  'post',
  'post.author'=>array('alias'=>'p_author')))->findAll(array(
  'order'=>'author.name, p_author.name, post.title'
));

6. Dynamic Relational Query Options

We can use dynamic relational query options in both with() and the with option. The dynamic options will overwrite existing options as specified in the relations() method. For example, with the above User model, if we want to use eager loading approach to bring back posts belonging to an author in ascending order (theorder option in the relation specification is descending order), we can do the following:

User::model()->with(array(
    'posts'=>array('order'=>'posts.create_time ASC'),
    'profile',
))->findAll();

Dynamic query options can also be used when using the lazy loading approach to perform relational query. To do so, we should call a method whose name is the same as the relation name and pass the dynamic query options as the method parameter. For example, the following code returns a user’s posts whose status is 1:

$user=User::model()->findByPk(1);
$posts=$user->posts(array('condition'=>'status=1'));

7. Relational Query Performance

As we described above, the eager loading approach is mainly used in the scenario when we need to access many related objects. It generates a big complex SQL statement by joining all needed tables. A big SQL statement is preferrable in many cases since it simplifies filtering based on a column in a related table. It may not be efficient in some cases, however.

Consider an example where we need to find the latest posts together with their comments. Assuming each post has 10 comments, using a single big SQL statement, we will bring back a lot of redundant post data since each post will be repeated for every comment it has. Now let’s try another approach: we first query for the latest posts, and then query for their comments. In this new approach, we need to execute two SQL statements. The benefit is that there is no redundancy in the query results.

So which approach is more efficient? There is no absolute answer. Executing a single big SQL statement may be more efficient because it causes less overhead in DBMS for parsing and executing the SQL statements. On the other hand, using the single SQL statement, we end up with more redundant data and thus need more time to read and process them.

For this reason, Yii provides the together query option so that we choose between the two approaches as needed. By default, Yii uses eager loading, i.e., generating a single SQL statement, except when LIMIT is applied to the primary model. We can set the together option in the relation declarations to be true to force a single SQL statement even when LIMIT is used. Setting it to false will result in some of tables will be joined in separate SQL statements. For example, in order to use separate SQL statements to query for the latest posts with their comments, we can declare the comments relation in Post class as follows,

public function relations()
{
    return array(
        'comments' => array(self::HAS_MANY, 'Comment', 'post_id',
                        'together'=>false),
    );
}

We can also dynamically set this option when we perform the eager loading:

$posts = Post::model()->with(
            array('comments'=>array(
                'together'=>false
            ))
        )->findAll();

8. Statistical Query

Besides the relational query described above, Yii also supports the so-called statistical query (or aggregational query). It refers to retrieving the aggregational information about the related objects, such as the number of comments for each post, the average rating for each product, etc. Statistical query can only be performed for objects related in HAS_MANY (e.g. a post has many comments) or MANY_MANY (e.g. a post belongs to many categories and a category has many posts).

Performing statistical query is very similar to performing relation query as we described before. We first need to declare the statistical query in the relations() method of CActiveRecord like we do with relational query.

class Post extends CActiveRecord
{
    public function relations()
    {
        return array(
            'commentCount'=>array(self::STAT, 'Comment', 'post_id'),
            'categoryCount'=>array(
                self::STAT, 'Category', 'post_category(post_id, category_id)'
            ),
        );
    }
}

In the above, we declare two statistical queries: commentCount calculates the number of comments belonging to a post, and categoryCount calculates the number of categories that a post belongs to. Note that the relationship between Post and Comment is HAS_MANY, while the relationship between Post and Category isMANY_MANY (with the joining table post_category). As we can see, the declaration is very similar to those relations we described in earlier subsections. The only difference is that the relation type is STAT here.

With the above declaration, we can retrieve the number of comments for a post using the expression $post->commentCount. When we access this property for the first time, a SQL statement will be executed implicitly to retrieve the corresponding result. As we already know, this is the so-called lazy loading approach. We can also use the eager loading approach if we need to determine the comment count for multiple posts:

$posts=Post::model()->with('commentCount', 'categoryCount')->findAll();

The above statement will execute three SQLs to bring back all posts together with their comment counts and category counts. Using the lazy loading approach, we would end up with 2*N+1 SQL queries if there are Nposts.

By default, a statistical query will calculate the COUNT expression (and thus the comment count and category count in the above example). We can customize it by specifying additional options when we declare it inrelations(). The available options are summarized as below.

  • select: the statistical expression. Defaults to COUNT(*), meaning the count of child objects.
  • defaultValue: the value to be assigned to those records that do not receive a statistical query result. For example, if a post does not have any comments, its commentCount would receive this value. The default value for this option is 0.
  • condition: the WHERE clause. It defaults to empty.
  • params: the parameters to be bound to the generated SQL statement. This should be given as an array of name-value pairs.
  • order: the ORDER BY clause. It defaults to empty.
  • group: the GROUP BY clause. It defaults to empty.
  • having: the HAVING clause. It defaults to empty.

9. Relational Query with Named Scopes

Relational query can also be performed in combination with named scopes. It comes in two forms. In the first form, named scopes are applied to the main model. In the second form, named scopes are applied to the related models.

The following code shows how to apply named scopes to the main model.

$posts=Post::model()->published()->recently()->with('comments')->findAll();

This is very similar to non-relational queries. The only difference is that we have the with() call after the named-scope chain. This query would bring back recently published posts together with their comments.

And the following code shows how to apply named scopes to the related models.

$posts=Post::model()->with('comments:recently:approved')->findAll();
// or since 1.1.7
$posts=Post::model()->with(array(
    'comments'=>array(
        'scopes'=>array('recently','approved')
    ),
))->findAll();
// or since 1.1.7
$posts=Post::model()->findAll(array(
    'with'=>array(
        'comments'=>array(
            'scopes'=>array('recently','approved')
        ),
    ),
));

The above query will bring back all posts together with their approved comments. Note that comments refers to the relation name, while recently and approved refer to two named scopes declared in the Comment model class. The relation name and the named scopes should be separated by colons.

Occasionally you may need to retrieve a scoped relationship using a lazy-loading approach, instead of the normal eager loading method shown above. In that case, the following syntax will do what you need:

// note the repetition of the relationship name, which is necessary
$approvedComments = $post->comments('comments:approved');

Named scopes can also be specified in the with option of the relational rules declared inCActiveRecord::relations(). In the following example, if we access $user->posts, it would bring back allapproved comments of the posts.

class User extends CActiveRecord
{
    public function relations()
    {
        return array(
            'posts'=>array(self::HAS_MANY, 'Post', 'author_id',
                'with'=>'comments:approved'),
        );
    }
}
 
// or since 1.1.7
class User extends CActiveRecord
{
    public function relations()
    {
        return array(
            'posts'=>array(self::HAS_MANY, 'Post', 'author_id',
                'with'=>array(
                    'comments'=>array(
                        'scopes'=>'approved'
                    ),
                ),
            ),
        );
    }
}

Note: Before 1.1.7 named scopes applied to related models must be specified in CActiveRecord::scopes. As a result, they cannot be parameterized.

Since 1.1.7 it’s possible to pass parameters for relational named scopes. For example, if you have scope named rated in the Post that accepts minimum rating of post, you can use it from User the following way:

$users=User::model()->findAll(array(
    'with'=>array(
        'posts'=>array(
            'scopes'=>array(
                'rated'=>5,
            ),
        ),
    ),
));
 
class Post extends CActiveRecord
{
    ......
 
    public function rated($rating)
    {
        $this->getDbCriteria()->mergeWith(array(
            'condition'=>'rating=:rating',
            'params'=>array(':rating'=>$rating),
        ));
        return $this;
    }
 
    ......
}

10. Relational Query with through

When using through, relation definition should look like the following:

'comments'=>array(self::HAS_MANY,'Comment',array('key1'=>'key2'),'through'=>'posts'),

In the above array('key1'=>'key2'):

  • key1 is a key defined in relation specified in through (posts is this case).
  • key2 is a key defined in a model relation points to (Comment in this case).

through can be used with HAS_ONE, BELONGS_TO and HAS_MANY relations.

HAS_MANY through

HAS_MANY through ER

HAS_MANY through ER

An example of HAS_MANY with through is getting users from a particular group when users are assigned to groups via roles.

A bit more complex example is getting all comments for all users of a particular group. In this case we have to use several relations with through in a single model:

class Group extends CActiveRecord
{
    ...
    public function relations()
    {
        return array(
            'roles'=>array(self::HAS_MANY,'Role','group_id'),
            'users'=>array(
                self::HAS_MANY,'User',array('user_id'=>'id'),'through'=>'roles'
            ),
            'comments'=>array(
                self::HAS_MANY,'Comment',array('id'=>'user_id'),'through'=>'users'
            ),
        );
    }
}

Usage examples

// get all groups with all corresponding users
$groups=Group::model()->with('users')->findAll();
 
// get all groups with all corresponding users and roles
$groups=Group::model()->with('roles','users')->findAll();
 
// get all users and roles where group ID is 1
$group=Group::model()->findByPk(1);
$users=$group->users;
$roles=$group->roles;
 
// get all comments where group ID is 1
$group=Group::model()->findByPk(1);
$comments=$group->comments;

HAS_ONE through

HAS_ONE through ER

HAS_ONE through ER

An example of using HAS_ONE with through is getting user address where user is bound to address using profile. All these entities (user, profile, and address) do have corresponding models:

class User extends CActiveRecord
{
    ...
    public function relations()
    {
        return array(
            'profile'=>array(self::HAS_ONE,'Profile','user_id'),
            'address'=>array(
                self::HAS_ONE,'Address',array('id'=>'profile_id'),
                    'through'=>'profile'
            ),
        );
    }
}

Usage examples

// get address of a user whose ID is 1
$user=User::model()->findByPk(1);
$address=$user->address;

through on self

through can be used for a model bound to itself using a bridge model. In our case it’s a user mentoring other users:

through self ER

through self ER

That’s how we can define relations for this case:

class User extends CActiveRecord
{
    ...
    public function relations()
    {
        return array(
            'mentorships'=>array(
                self::HAS_MANY,'Mentorship','teacher_id','joinType'=>'INNER JOIN'
            ),
            'students'=>array(
                self::HAS_MANY,'User',array('student_id'=>'id'),
                    'through'=>'mentorships','joinType'=>'INNER JOIN'
            ),
        );
    }
}

Usage examples

// get all students taught by teacher whose ID is 1
$teacher=User::model()->findByPk(1);
$students=$teacher->students;

11. Post-JOIN operations

Since 1.1.15 additional post-JOIN operations could be set. CBaseActiveRelation::$joinOptions has been added. Consider we have the following models and relations:

class User extends CActiveRecord
{
    public function relations()
    {
        return array(
            'posts' => array(self::HAS_MANY, 'Post', 'user_id'),
        );
    }
}
 
class Post extends CActiveRecord
{
    public function relations()
    {
        return array(
            'user' => array(self::BELONGS_TO, 'User', 'user_id'),
            'tags' => array(self::MANY_MANY, 'Tag', '{{post_tag}}(post_id, tag_id)'),
        );
    }
}
 
class Tag extends CActiveRecord
{
    public function relations()
    {
        return array(
            'posts' => array(self::MANY_MANY, 'Post', '{{post_tag}}(tag_id, post_id)'),
        );
    }
}

Query code examples with USE INDEX clauses:

$users=User::model()->findAll(array(
    'select'=>'t.id,t.name',
    'with'=>array(
        'posts'=>array(
            'alias'=>'p',
            'select'=>'p.id,p.title',
            'joinOptions'=>'USE INDEX(post__user)',
        ),
    ),
));
 
$posts=Post::model()->findAll(array(
    'select'=>'t.id,t.title',
    'with'=>array(
        'tags'=>array(
            'alias'=>'a',
            'select'=>'a.id,a.name',
            'joinOptions'=>'USE INDEX(post_tag__tag) USE INDEX(post_tag__post)',
        ),
    ),
));
 
$posts=Post::model()->findAll(array(
    'select'=>'t.id,t.title',
    'with'=>array(
        'tags'=>array(
            'alias'=>'a',
            'select'=>'a.id,a.name',
            'joinOptions'=>array(
                'USE INDEX(post_tag__tag) USE INDEX(post_tag__post)',
                'USE INDEX(tag__name)',
            ),
        ),
    ),
));

Code above should generate following MySQL queries respectively:

SELECT
    `t`.`id` AS `t0_c0`, `t`.`name` AS `t0_c1`,
    `p`.`id` AS `t1_c0`, `p`.`title` AS `t1_c2`
FROM `tbl_user` `t`
LEFT OUTER JOIN `tbl_post` `p`
    USE INDEX(post__user) ON (`p`.`user_id`=`t`.`id`);
 
SELECT
    `t`.`id` AS `t0_c0`, `t`.`title` AS `t0_c2`,
    `a`.`id` AS `t1_c0`, `a`.`name` AS `t1_c1`
FROM `tbl_post` `t`
LEFT OUTER JOIN `tbl_post_tag` `tags_a`
    USE INDEX(post_tag__tag) USE INDEX(post_tag__post) ON (`t`.`id`=`tags_a`.`post_id`)
LEFT OUTER JOIN `tbl_tag` `a` ON (`a`.`id`=`tags_a`.`tag_id`);
 
SELECT
    `t`.`id` AS `t0_c0`, `t`.`title` AS `t0_c2`,
    `a`.`id` AS `t1_c0`, `a`.`name` AS `t1_c1`
FROM `tbl_post` `t`
LEFT OUTER JOIN `tbl_post_tag` `tags_a`
    USE INDEX(post_tag__tag) USE INDEX(post_tag__post) ON (`t`.`id`=`tags_a`.`post_id`)
LEFT OUTER JOIN `tbl_tag` `a`
    USE INDEX(tag__name) ON (`a`.`id`=`tags_a`.`tag_id`);

The $joinOptions query option could also be set in relation declarations as follows:

class Post extends CActiveRecord
{
    public function relations()
    {
        return array(
            'user' => array(self::BELONGS_TO, 'User', 'user_id'),
            'tags' => array(self::MANY_MANY, 'Tag', '{{post_tag}}(post_id, tag_id)',
                'joinOptions' => array(
                    'USE INDEX(post_tag__tag) USE INDEX(post_tag__post)',
                    'USE INDEX(tag__name)',
                ),
            ),
        );
    }
}

Total 12 comments

#19191report it

KRANTHI KUMAR at 2015/04/09 09:30am
Use of together = true
$post = Post::model()->with(
            array(
                'comments' => array(
                        'together' => true,
                        'select'   => 'content,id',
                        'joinType' => 'INNER JOIN ',
                        'order'    => 'comments.id DESC',
                ),
            )
            )->findAll();

then only one query executing like below

SELECT `t`.`id` AS `t0_c0`, `t`.`title` AS `t0_c1`, `t`.`content` AS `t0_c2`, `t`.`tags` AS `t0_c3`,
 `t`.`status` AS `t0_c4`, `t`.`create_time` AS `t0_c5`, `t`.`update_time` AS `t0_c6`,
  `t`.`author_id` AS `t0_c7`, `comments`.`content` AS `t1_c1`, `comments`.`id` AS `t1_c0`
 
   FROM `tbl_post` `t`
    INNER JOIN
                `tbl_comment` `comments` ON (`comments`.`post_id`=`t`.`id`)
    WHERE (comments.status=2)
   ORDER BY
     t.create_time DESC,
     comments.id DESC,
     comments.create_time DESC
   LIMIT 20

IF I CHANGE VALUE OF together = false then executing 2 queries .

1) SELECT `t`.`id` AS `t0_c0`, `t`.`title` AS `t0_c1`, `t`.`content` AS `t0_c2`, `t`.`tags` AS `t0_c3`, `t`.`status` AS `t0_c4`, `t`.`create_time` AS `t0_c5`, `t`.`update_time` AS `t0_c6`, `t`.`author_id` AS `t0_c7` FROM `tbl_post` `t`  ORDER BY  t.create_time DESC LIMIT 20;
 
 2) SELECT `t`.`id` AS `t0_c0`, `comments`.`content` AS `t1_c1`, `comments`.`id` AS `t1_c0` FROM `tbl_post` `t` INNER JOIN  `tbl_comment` `comments` ON (`comments`.`post_id`=`t`.`id`)  WHERE (`t`.`id` IN (22, 5, 21, 1, 2)) AND (comments.status=2) ORDER BY comments.id DESC, comments.create_time DESC;

Static call versus Singleton call in PHP

Introduction

In the past several months I’ve been working with a rather large application built with symfony. I noticed that symfony makes heavy use of the Singleton pattern (other frameworks, like Zend do that too); everywhere in the code there were pieces like this one:

<?php
// ...
sfSomething::getInstance();
// ...
?>

I know that in more than half of the situations, you can write your code using plain static classes, with some initialize() method, as an alternative to writing singletons. For example, this is a dummy Singleton:

<?php
class DummySingleton {
    private function __construct(){}
    private function __clone(){}
    public static function getInstance(){
        if(self::$__instance == NULL) self::$__instance = new DummySingleton;
        return self::$__instance;
    }
    public function foo(){
        echo 'I am DummySingleton::foo()';
    }
}
?>

Now this is a completely useless class, but it suits our purpose of illustrating the Singleton. Notice the amount of code needed by the Singleton pattern. Except the foo() method, all the code in the class makes sure you have only one instance at any time during the execution.

Now let’s write a static class that does the same thing as the Singleton:

<?php
class DummyStatic {
    static public function foo(){
        echo 'I am DummyStatic::foo()';
    }
}
?>

This is much cleaner, as we don’t need the extra code the Singleton needs, and can focus on our task at hand.

Performance comparison

Let’s compare the performance of the two approaches. I’ve written a small test script that looks like this:

<?php

/**
* A singleton class
*/
class TestSingleton {
    // singleton code
    private static $__instance = NULL;
    private function __construct(){}
    private function __clone(){}
    static public function getInstance(){
        if(self::$__instance == NULL) self::$__instance = new TestSingleton;
        return self::$__instance;
    }

    // our actual code
    public $val = 0;
    public function test(){
        for($i=0;$i<30;$i++) $this->val += $i;
    }
}
/**
* a plain static class (all members are static)
*/
class TestStatic {
    static public $val = 0;
    static public function test(){
        for($i=0;$i<30;$i++) self::$val += $i;
    }
}
// how many runs
$runs = 500000;
// benchmarking Singleton
$start = microtime(TRUE);
for($i=0;$i<$runs;$i++) TestSingleton::getInstance()->test();
$run1 = microtime(TRUE) - $start;
// benchmarking static
$start = microtime(TRUE);
for($i=0;$i<$runs;$i++) TestStatic::test();
$run2 = microtime(TRUE) - $start;

echo '<pre>';
echo 'test singleton: '.number_format($run1, 8)." s\n";
echo 'test static:    '.number_format($run2, 8).' s';
?>

Basicly, I put together the two types of classes. Both have a method called test(), which does some arithmetic operations, just to have something that spends some execution time.

I’ve ran this script for various values for the $runs variable: 100, 1k 10k, 100k, 200k, 300k, 500k and 1M.

Test results

Number of runs Singleton call time (s) Static call time (s)
100 0.004005 0.001511
1,000 0.018872 0.014552
10,000 0.174744 0.141820
100,000 1.643465 1.431564
200,000 3.277334 2.812432
300,000 5.079388 4.419048
500,000 8.086555 6.841494
1,000,000 16.189018 13.696728

I have also done some spreadsheet magic, and generated this chart:

As you can see, for a relatively small number of runs (<1k), the Static code is significantly faster than the Singleton, an than it stabilizes arround 15% faster than Singleton, as I expected. This is because every function/method call involves some operations (symbol lookup, stack manipulation etc.), and each call to the Singleton method, in fact, also calls the getInstance() static method.

Conclusion

It may not be that obvious that making extensive use of Singletons has this kind of side effect; however, if your code has more that 100 or 1,000 calls to some getInstance() method of a Singleton class, you might want to consider caching the reference to the object it returns, or even refactoring the code to use only static method calls.

You might say that you need an object because you do stuff in the constructor. That can be easily achieved with some kind of static initialize() method, that should be called once in your code, just before usage. If you have some auto loading mechanism in place, you could call it just after loading the class, for example, if you want to automate the initialization process. But keep in mind that this is not a 100% replacement for Singletons; you need an object if you want to serialize/unserialize it (for caching, some RPC call, etc.).

Update.

Tested with Facebook’s HPHP compiler:

I’ve tested the script using the HPHP compiler, and the results are spectacular. While keeping the same time ratio between the Singleton and Static calls, what stroke me is the huge difference (HPHP is ~ 200 times faster):

Number of calls Time spent (Apache) Time spent (HPHP)
Apache Singleton call Apache Static call HPHP Singleton call HPHP Static call
100 0.004005 0.001511 0.00001502 0.00000906
1,000 0.018872 0.014552 0.00008988 0.00007486
10,000 0.174744 0.141820 0.00075102 0.00063801
100,000 1.643465 1.431564 0.00829983 0.00795388
200,000 3.277334 2.812432 0.01839614 0.01339102
300,000 5.079388 4.419048 0.02502608 0.01932502
500,000 8.086555 6.841494 0.04114008 0.03280401
1,000,000 16.189018 13.696728 0.07872796 0.06373119

Happy coding.

Bài 5: RealTime Index với Sphinx Search

Nếu các bạn mới bắt đầu làm quen với Sphinx Search qua 4 bài trước thì chắc hẳn bạn cũng nhận ra khuyết điểm của Sphinx là không thể Real Time Index đồng bộ với Database được. Do đó, những cột dữ liệu vừa được thêm vào trong Database thì không xuất hiện trong kết quả tìm kiếm cũng như những cột dữ liệu đã bị xóa đi lại vẫn xuất hiện cho đến khi chúng ta reindex lại. Chúng ta cũng có thể cấu hình reindex thường xuyên nhưng việc làm này khiến server phải chạy thường xuyên và gây tốn tài nguyên hệ thống. Để khắc phục, tôi sẽ hướng dẫn bạn khắc phục vấn đề này mà vẫn tối ưu server:

1. Giới thiệu về Delta Index
  • Delta Index là một Index phụ cho Index chính, chỉ dùng để Index các dữ liệu mới thêm vào và xóa đi các dữ liệu đã xóa.
  • Ý tưởng ở đây là sử dụng “hai index và 2 nguồn dữ liệu”. Ví dụ áp dụng với 1 diễn đàn có 1.000.000 bài viết, mỗi ngày có 1000 bài viết mới thì Index chính sẽ dùng dể index những thứ ít thay đổi – 1.000.000 bài viết cũ. Còn Index Delta sẽ dùng để index những bài viết mới. Ta có thể thấy áp dụng mô hình này mang lại lợi ích khi Delta Index là một index nhỏ, có thể reindex thường xuyên mà không gây tốn quá nhiều tài nguyên server.
2. Cấu hình 1 Delta Index

Đầu tiên, ta tạo Table lưu ID dữ liệu lớn nhất mà Index chính đã Index (để Delta Index chỉ Index những dữ liệu mới) bằng câu lệnh SQL:

   CREATE TABLE `file_counter` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `max_id` int(11) NOT NULL,
        PRIMARY KEY (`id`)
   ) 
   ENGINE=InnoDB DEFAULT CHARSET=utf8;

Sau đó ta tạo bằng để lưu ID những dữ liệu đã bị xóa:

  CREATE TABLE `deleted_file` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `file_id` int(11) DEFAULT NULL,
        PRIMARY KEY (`id`)
  ) 
  ENGINE=InnoDB DEFAULT CHARSET=utf8;

Để tự động nhập ID những dữ liệu bị xóa, ta thêm Trigger sau vào Table chính bằng SQL:

   CREATE TRIGGER `link_del` BEFORE DELETE ON `link` FOR EACH ROW
   BEGIN
       INSERT INTO deleted_file (file_id) VALUES(OLD.id);
   END;;

Quá trình chuẩn bị đã xong, ta tiến hành chỉnh file config của Sphinx /etc/sphinx/sphinx.conf (Tôi chỉ trình bày những phần chính thay đổi, những phần khác bạn thêm vào cho phù hợp nhé):

source main
{
   sql_query_pre = SET NAMES utf8
   sql_query_pre = REPLACE INTO file_counter SELECT 1, MAX(id) FROM tên_table
   sql_query = SELECT col1, col2, col3 FROM tên_table WHERE id <= (SELECT max_id FROM file_counter WHERE id=1)
}

source delta : main
{
   sql_query_pre = SET NAMES utf8
   sql_query_killlist   = SELECT file_id FROM deleted_file
   sql_query = SELECT col1, col2, col3 FROM tên_table   WHERE id > (SELECT max_id FROM file_counter WHERE id=1)
}

index main
{
   source = main
   path = /path/to/main
}

index delta : main
{
   source = delta
   path = /path/to/delta
}

Ta có thể thấy theo config này, Main sẽ Index và lưu ID max nó đã index vào table file_counter. Còn Delta chỉ Index những ID lớn hơn ID mà Main đã Index. Ngoài ra, Delta sẽ lấy những ID dữ liệu đã xóa trong bảng deleted_file và xóa bỏ khỏi kết quả tìm kiếm (sql_query_killlist). Và để sử dụng, ta dùng cron_job để cấu hình reindex lại mỗi Index sao cho hợp lí, khi tôi cấu hình sphinx để sử dụng cho công cụ tìm kiếm file này thì tôi reindex Main hàng giờ và reindex lại Delta mỗi 5 phút.

Câu lệnh reindex Delta:

indexer --rotate delta

Câu lệnh reindex Main

indexer --rotate all

Vậy là xong rồi đấy, Bạn hãy tận tưởng công cụ tìm kiếm siêu nhanh của mình nào.

Bài 4: Cài đặt và sử dụng Sphinx Tool – Quản lí Sphinx trên giao diện Web

Đối với nhiều bạn không thành thạo về lập trình cũng như quản lí Serverthì công việc tạo index, chỉnh cài đặt,… là một điều khó khăn. Thật may mắn với chúng ta là Sphinx đã phát triển Sphinx Tool để giúp người dùng quản lí dễ dàng Sphinx trên nền giao diện Web trực quan. Nào chúng ta hãy cùng nhau tìm hiểu và sử dụng nó nhé!

  • Đầu tiên truy cập https://tools.sphinxsearch.com/ và nhập đầy đủ thông tin.Đăng kí SphinxTool
  • Sau khi đăng kí và xác minh địa chỉ email, ta đăng nhập vào Sphinxtại: https://tools.sphinxsearch.com/user/login
  • Tiếp theo chọn “Add new managed node” như trong hình:Thêm New managed node
  • Làm theo hướng dẫn để cài đặt SphinxAgent tùy theo từng hệ điều hành. Ở đây, tôi hướng dẫn cài đặt trên Centos 6:
    SPH_API_KEY=your_key_here bash -c "$(curl -s https://tools.sphinxsearch.com/downloads/latest/install-manager)"
    

    Sau khi đã cài đặt thành công, tiến hành khởi động sphinxagent:

    service sphinxagent start
    

    Lưu ý: Đôi lúc sphinxagent đã chạy nhưng SphinxTool lại báo Node Offline, bạn hãy khởi động lại bằng:

    service sphinxagent restart
    

    Xong xuôi, trong SphinxTool vào Nodes -> Select Node vừa cài (tên theo hostname) và bạn đã có thể xem dữ liệu, cấu hình, test tìm kiếm, thay đổi/thêm/xóa index,… Sphinx ngay trên nền web!
    Giao diện Sphinx Tool

Bài 3: Cấu hình và sử dụng sphinx cơ bản

Chúng ta cùng cấu hình và tiến hành chạy thử Sphinx lần đầu tiên nào!

Đầu tiên ta sẽ cấu hình hệ thống searchd của sphinx:
searchd {
    listen                 = 9306:mysql41
    log                    = /var/log/sphinx/searchd.log
    query_log              = /var/log/sphinx/query.log
    read_timeout           = 5
    max_children           = 30
    pid_file               = /var/run/sphinx/searchd.pid
    seamless_rotate        = 1
    preopen_indexes        = 1
    unlink_old             = 1
    workers                = threads 
    binlog_path            = /var/lib/sphinx
}

Với cấu hình này, khi sử dụng chúng ta sẽ kết nối với Sphinx thông qua cổng 9306 và dưới extension mysql41 (kết nối giống hệt như với MYSQL nên rất tiện)

Cấu trúc của một hệ thống tìm kiếm của sphinx bao gồm:
  • Source: Tức nguồn dữ liệu, thông thường là MYSQL, phần này sẽ bao gồm những lệnh kết nối tới MYSQL và câu lệnh để xuất dữ liệu ra.
  • Index: Cấu hình cách Index các dữ liệu từ Source.
Cấu hình 1 source:
source tên_source
{
     type             = mysql
     sql_host         = MYSQL_HOST
     sql_user         = MYSQL_USER
     sql_pass         = PASSWORD
     sql_db           = MYSQL_DATABASE
     sql_port         = 3306
     sql_query_pre    = SET CHARACTER_SET_RESULTS=utf8
     sql_query_pre    = SET NAMES utf8
     sql_query        = SELECT id, col1, col2, col3, col4, col5 FROM table
     sql_field_string = col1
     sql_attr_string  = col2
     sql_attr_uint    = col3
     sql_attr_float   = col4
     sql_attr_timestamp = col5
}

Các thuộc tính:

  • sql_query_pre: Các câu lệnh được thực hiện trước khi chạy câu lệnh lấy dữ liệu chính (sql_query).
  • sql_query: Câu lệnh chính lấy dữ liệu (lưu ý phải có cột ID).
  • sql_field_*: Là những cột dữ liệu vừa dùng để index search, vừa dùng lưu dữ liệu.
  • sql_attr_*: Là những cột dữ liệu chỉ lưu lại, không index

Các kiểu dữ liệu trong sphinx:

  • string: Kiểu chuỗi
  • uint: Kiểu số nguyên
  • float: Kiểu số thực
  • timestamp: Kiểu thời gian

Lưu ý: Các cột dữ liệu được sử dụng dưới sql_feild_*sql_attr_* phải có trong kết quả từ câu lệnh chính sql_query.

Cấu hình 1 index:
index tên_index
{
    source             = tên_source
    path               = /var/lib/sphinx/file
    docinfo            = extern
    min_word_len       = 1
    min_prefix_len     = 0
    charset_table      = CHARSERT_TABLE
}

Các thuộc tính:

  • source: Tên sourrce mà bạn đã cấu hình.
  • path: Đường dẫn lưu file index.
  • min_word_len: Dộ dài nhỏ nhất một đơn vị index.
  • charset_table: Các bảng mã để index đúng, tôi khuyên dùng bảng mã sau: https://yabeow.com/content/images/sphinx_charset.txt
Chạy thử:
  • Trước hết cho sphinx reindex lại: indexer --rotate tên_index (reindex 1 index cụ thể) hoặc indexer --rotate all (reindex lại toàn bộ)
  • Sau đó tiến hành kết nối đến sphinx bằng các ứng dụng web (Phpmyadmin, PHP, ASP, Adminer,…) giống hệt như kết nối với Mysql. Ví dụ thử chạy bằng PHP:
    <?php
       $term = "phần mềm";
       $sphinx = new mysqli("127.0.0.1", "", "", "", 9306);
       $query  = "SELECT * FROM tên_index WHERE MATCH('\"$term\"/1')";
       $data =  $sphinx->query($query);
       $data = $data->fetch_all;
       print_r($data);
    ?>
    
  • Nếu trên màn hình xuất hiện một đống rối rắm, thì bạn đã thành công rồi đấy. Tiếp tục nghiên cứu chuyên sâu về 1 câu lệnh trong sphinx nào!
Một lệnh truy vấn (Query) trong Sphinx có dạng:
SELECT 
 các_cột [col1, col2, select_expr,...] 
 FROM tên_index [index1, index2,...]  
 WHERE điều_kiện [MATCH(term), somecol = somevalue,...] 
 ORDER BY [somecol, expr,... ASC|DESC] 
 LIMIT [OFFSET, ROWCOUNT] 
 OPTION opt_name = opt_value
  • Phần OPTION có những giá trị hay dùng sau:
      • maxmatches: Số kết quả tối đa cho mỗi câu lệnh.
  • maxquerytime: Thời gian tìm kiếm tối đa (ms).
    • ranker: Các chế độ để xếp hạng các kết quả tìm kiếm: ‘proximitybm25′, ‘bm25′, ‘none’, ‘wordcount’, ‘proximity’, ‘matchany’, ‘fieldmask’, ‘sph04′, ‘expr’, hoặc ‘export’.

Như vậy là xong rồi đấy. bạn chỉ cần xây dựng câu truy vấn Sphinx (Query) cho phù hợp với mục đích tìm kiếm của mình và sử dụng nó như mysql cho ứng dụng của mình là xong!

Bài 2: Cài đặt và bắt đầu làm quen với Sphinx trên Centos

Đầu tiên, việc chúng ta cần làm tất nhiên là cài đặt (không cài đặt làm sao mà sử dụng được :D).

Trong bài này, tôi sẽ hướng dẫn cài đặt Sphinx và sử dụng một số chức năng cơ bản của nó trên môi trường Linux (ở đây là Centos).

1. Cài đặt:

Cách 1: – Đăng nhập vào Centos với quyền root và gõ lệnh:

yum install sphinx

Cách 2: Nếu sử dụng cách 1 không đc, bạn hãy sử dụng cách sau:

wget http://sphinxsearch.com/files/sphinx-2.0.9-1.rhel6.x86_64.rpm
yum localinstall sphinx-2.0.9-1.rhel6.x86_64.rpm

Như vậy là đã cài đặt xong, thật dễ dàng phải không nào, tiếp theo tôi sẽ hướng dẫn bạn cách sử dụng nó.

2. Cách sử dụng:

Để khởi động/tắt dịch vụ Sphinx, ta sử dụng lệnh:

service searchd start/restart

Để cấu hình tự động Sphinx khởi động cùng server, ta sử dụng:

chkconfig searchd on

File config chính của Sphinx:

/etc/sphinx/sphinx.conf

Để reindex tại các index:

indexer --all --rotate

Trong đó:
--all: reindex toàn bộ index. Có thể thay thế giá trị này bằng một hoặc nhiều tên index để chỉ reindex lại các index đó. Vd: indexer sanpham –rotate

--rotate: Dùng để gửi tín hiệu đến service searchd nhằm “recatch” lại file index (chỉ sử dụng khi service searchd đang chạy)

  • Lưu ý: reindex không làm ảnh hưởng hay gián đoạn tới chức năngsearch của Sphinx nên có thể reindex thường xuyên tùy theo nhu cầu.

Xem tiếp bài viết: Cấu hình một Index Sphinx

Bài 1: Giới thiệu về Sphinx và tại sao phải dùng nó?

Trong bài viết đầu của loạt bài Sphinx, tôi sẽ giới thiệu về nó và tại sao tôi cũng như các bạn phải dùng nó.

Giới thiệu về Sphinx:

1. Sphinx là gì?
  • Sphinx là một full-text search engine tìm kiếm dữ liệu mạnh mẽ nhất được viết bằng ngôn ngữ C++ và có thể chạy trên hầu hết các hệ điều hành hiện nay (Linux, Windows, Mac, Solaris v.v).
2. Sphinx dùng để làm gì?
  • Sphinx dùng để thay thế Full text searchLike của Mysql. Giúp giảm tải và tối ưu hiệu quả tìm kiếm khi database trở nên quá lớn.

Nhân tiện nói về thêm một chút, Sphinx là con nhân sư trong thần thoại Hy Lạp, Ai Cập. Trong thần thoại Hy Lạp nhân sư được cho là canh gác cổng vào thành phố Thebes, đưa ra câu đố cho bất kỳ ai muốn vào thành, những người không trả lời được sẽ bị nhân sư bóp cổ và ăn thịt >:)

3. Tại sao tôi lại sử dụng Sphinx:
  • Trong quá trình xây dựng một công cụ tìm kiếm file tôi đã bắt gặp vấn đề khá đau đầu đó là – hệ thống CSDL (ở đây là MYSQL) – đã trở nên quá tải và không đáp ứng được nhu cầu vì số lượng rows quá nhiều (lên tới hàng triệu) và cuối cùng dẫn tới truy vấn tìm kiếm lâu cũng như không đạt hiệu quả cao. Do đó, tôi đã mày mò và áp dụng Sphinx với kết quả thực sự còn đáp ứng ngoài nhu cầu của web: tìm kiếm cực nhanh, giảm tải cho hệ thống CSDL (khi search không cần connect tới MYSQL), kết quả trả về chính xác,…

Tuy nhiên, hạn chế của Sphinx là chưa được áp dụng nhiều tại Việt Nam, chưa có nhiều tài liệu tiếng Việt cho anh em tham khảo, khiến việc cài đặt và sử dụng trở nên rất khó khăn. Vì vậy, tôi đã mạnh dạn xây dựng chuyên mục này này và chia sẻ kinh nghiệm cho anh em cùng tham khảo. Trình viết còn thấp, kinh nghiệm không nhiều, có gì sai sót mong bỏ quá cho :) .

Bài 2: Cài đặt Sphinx trên CentOS

Bài 3: Cấu hình và sử dụng Sphinx cơ bản

Bài 4: Cài đặt và sử dụng Sphinx Tool – Quản lí Sphinx trên giao diện Web

Bài 5: Real Time Index với Sphinx

How to Install Latest MySQL 5.7.9 on RHEL/CentOS 7/6/5 and Fedora 23/22/21

MySQL is an open source free relational database management system (RDBMS) released under GNU (General Public License). It is used to run multiple databases on any single server by providing multi-user access to each created database.

Install Latest MySQL in CentOS

This article will walk through you the process of installing and updating latest MySQL 5.7.9 version on RHEL/CentOS 7/6/5 and Fedora 23/22/21 using MySQL Yum repository via YUM utility.

Step 1: Adding the MySQL Yum Repository

1. We will use official MySQL Yum software repository, which will provides RPM packages for installing the latest version of MySQL server, client, MySQL Utilities, MySQL Workbench, Connector/ODBC, and Connector/Python for the RHEL/CentOS 7/6/5 and Fedora 23-21.

Important: These instructions only works on fresh installation of MySQL on the server, if there is already a MySQL installed using a third-party-distributed RPM package, then I recommend you to upgrade or replace the installed MySQL package using the MySQL Yum Repository”.

Before Upgrading or Replacing old MySQL package, don’t forget to take all important databases backup and configuration files.

2. Now download and add the following MySQL Yum repository to your respective Linux distribution system’s repository list to install the latest version of MySQL (i.e. 5.7.9 released on 21 October 2015).

--------------- On RHEL/CentOS 7 ---------------
# wget http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm
--------------- On RHEL/CentOS 6 ---------------
# wget http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm
--------------- On RHEL/CentOS 5 ---------------
# wget http://dev.mysql.com/get/mysql57-community-release-el5-7.noarch.rpm
--------------- On Fedora 23 ---------------
# wget http://dev.mysql.com/get/mysql57-community-release-fc23-7.noarch.rpm
--------------- On Fedora 22 ---------------
# wget http://dev.mysql.com/get/mysql57-community-release-fc22-7.noarch.rpm
--------------- On Fedora 21 ---------------
# wget http://dev.mysql.com/get/mysql57-community-release-fc21-7.noarch.rpm

3. After downloading the package for your Linux platform, now install the downloaded package with the following command.

--------------- On RHEL/CentOS 7 ---------------
# yum localinstall mysql57-community-release-el7-7.noarch.rpm
--------------- On RHEL/CentOS 6 ---------------
# yum localinstall mysql57-community-release-el6-7.noarch.rpm
--------------- On RHEL/CentOS 5 ---------------
# yum localinstall mysql57-community-release-el5-7.noarch.rpm
--------------- On Fedora 23 ---------------
# dnf localinstall mysql57-community-release-fc23-7.noarch.rpm
--------------- On Fedora 22 ---------------
# dnf localinstall mysql57-community-release-fc22-7.noarch.rpm
--------------- On Fedora 21 ---------------
# yum localinstall mysql57-community-release-fc21-7.noarch.rpm

The above installation command adds the MySQL Yum repository to system’s repository list and downloads the GnuPG key to verify the integrity of the packages.

4. You can verify that the MySQL Yum repository has been added successfully by using following command.

# yum repolist enabled | grep "mysql.*-community.*"
# dnf repolist enabled | grep "mysql.*-community.*"      [On Fedora 22+ versions]

Verify MySQL Yum Repository

Step 2: Installing Latest MySQL Version

5. Install latest version of MySQL (currently 5.7) using the following command.

# yum install mysql-community-server
# dnf install mysql-community-server      [On Fedora 22+ versions]

The above command installs all the needed packages for MySQL server mysql-community-server, mysql-community-client, mysql-community-common and mysql-community-libs.

Step 3: Installing MySQL Release Series

6. You can also install different MySQL version using different sub-repositories of MySQL Community Server. The sub-repository for the recent MySQL series (currently MySQL 5.7) is activated by default, and the sub-repositories for all other versions (for example, the MySQL 5.6 or 5.5 series) are deactivated by default.

To install specific version from specific sub-repository, you can use --enable or --disable options using yum-config-manager or dnf config-manager as shown:

# yum-config-manager --disable mysql57-community
# yum-config-manager --enable mysql56-community
------------------ Fedora 22+ Versions ------------------
# dnf config-manager --disable mysql57-community
# dnf config-manager --enable mysql56-community

Step 4: Starting the MySQL Server

7. After successful installation of MySQL, it’s time to start the MySQL server with the following command:

# service mysqld start

You can verify the status of the MySQL server with the help of following command.

# service mysqld status

This is the sample output of running MySQL under my CentOS 7 box.

Redirecting to /bin/systemctl status  mysqld.service
mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled)
   Active: active (running) since Thu 2015-10-29 05:15:19 EDT; 4min 5s ago
  Process: 5314 ExecStart=/usr/sbin/mysqld --daemonize $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 5298 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 5317 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─5317 /usr/sbin/mysqld --daemonize

Oct 29 05:15:19 localhost.localdomain systemd[1]: Started MySQL Server.

Check Mysql Status

8. Now finally verify the installed MySQL version using following command.

# mysql --version

mysql  Ver 14.14 Distrib 5.7.9, for Linux (x86_64) using  EditLine wrapper

Check MySQL Installed Version

Step 5: Securing the MySQL Installation

9. The command mysql_secure_installation allows you to secure your MySQL installation by performing important settings like setting the root password, removing anonymous users, removing root login, and so on.

Note: MySQL version 5.7 or higher generates a temporary random password in /var/log/mysqld.log after installation.

Use below command to see the password before running mysql secure command.

# grep 'temporary password' /var/log/mysqld.log

Once you know the password you can now run following command to secure your MySQL installation.

# mysql_secure_installation

Note: Enter new Root password means your temporary password from file /var/log/mysqld.log.

Now follow the onscreen instructions carefully, for reference see the output of the above command below.

Sample Output
Securing the MySQL server deployment.

Enter password for user root: Enter New Root Password

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
Using existing password for root.

Estimated strength of the password: 50 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y

New password: Set New MySQL Password

Re-enter new password: Re-enter New MySQL Password

Estimated strength of the password: 100 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done! 

Step 6: Connecting to MySQL Server

10. Connecting to newly installed MySQL server by providing username and password.

# mysql -u root -p

Sample Output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.7.9 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

Connect to MySQL Server

Step 7: Updating MySQL with Yum

11. Besides fresh installation, you can also do updates for MySQL products and components with the help of following command.

# yum update mysql-server
# dnf update mysql-server       [On Fedora 22+ versions]

Update MySQL Version

When new updates are available for MySQL, it will auto install them, if not you will get a message saying NO packages marked for updates.

That’s it, you’ve successfully installed MySQL 5.7.9 on your system. If you’re having any trouble installing feel free to use our comment section for solutions.