Using MySQL Connector/Python X (mysqlx module)

This post is about simple usage of mysqlx module i.e X Plugin with latest Connector/Python DMR.
The version of Python Connector is 8.0.4.

Installing:

wget https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-8.0.4-0.1.dmr.el7.x86_64.rpm
sudo yum install mysql-connector-python-8.0.4-0.1.dmr.el7.x86_64.rpm

Sample Python code:

# Connecting to MySQL and working with a Session
import mysqlx

# Connect to a dedicated MySQL server
session = mysqlx.get_session({
    'host': 'localhost',
    'port': 33060,
    'user': 'bakux',
    'password': 'Baku12345',
    'ssl-mode': mysqlx.SSLMode.DISABLED
})

schema = session.get_schema('generated_columns_test')

# Create 'my_collection' in schema
schema.create_collection('my_collection')

# Get 'my_collection' from schema
collection = schema.get_collection('my_collection')

assert(True == collection.exists_in_database())

# You can also add multiple documents at once
collection.add({'_id': '2', 'name': 'Sakila', 'age': 15},
            {'_id': '3', 'name': 'Jack', 'age': 15},
            {'_id': '4', 'name': 'Clare', 'age': 37}).execute()

collection.remove('_id = 1').execute()

assert(3 == collection.count())

The code is quite clean so basically we have created a collection and inserted some data.

From MySQL client side the structure of this “table”==collection:

CREATE TABLE `my_collection` (
  `doc` json DEFAULT NULL,
  `_id` varchar(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,'$._id'))) STORED NOT NULL,
  PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

The collection has “stored generated column” _id which is extracted from doc column.

select * from my_collection;
+-------------------------------------------+-----+
| doc                                       | _id |
+-------------------------------------------+-----+
| {"_id": "2", "age": 15, "name": "Sakila"} | 2   |
| {"_id": "3", "age": 15, "name": "Jack"}   | 3   |
| {"_id": "4", "age": 37, "name": "Clare"}  | 4   |
+-------------------------------------------+-----+
3 rows in set (0.00 sec)

This default behavior restricted my tests on other storage engines. Because it is impossible to alter the engine of this default “collection”.

ERROR 3106 (HY000): 'Specified storage engine' is not supported for generated columns.

BTW, while testing, found some bugs and reported:

https://bugs.mysql.com/bug.php?id=87472
https://bugs.mysql.com/bug.php?id=87475
https://bugs.mysql.com/bug.php?id=87492
https://bugs.mysql.com/bug.php?id=87493

MySQL Sandbox, creating test environment for Group Replication plugin

Today’s topic is about how to test Group Replication plugin in easy way, with using MySQL Sandbox
But firstly we must compile MySQL with Group Replication plugin, refer to previous topic -> Compiling Group Replication Plugin

Who did not use Sandbox before, please refer to official link and read a bit more. It is wonderful tool for installing, testing new things with MySQL. Especially if you have new MySQL release to test and want to install 3 or 5 instances, just use MySQL Sandbox and it will do this work for you.

In our condition, we have already compiled MySQL with our plugin. So we have source folder in -> /home/sh/Sandboxes/mysql-group-replication-0.5.0-dmr/BIN

With MySQL Sandbox there is a wonderful tool, named make_sandbox_from_source.
Let’s see options available with this tool:

sh@shrzayev:~$ make_sandbox_from_source --help
    The MySQL Sandbox,  version 3.1.00
    (C) 2006-2015 Giuseppe Maxia

*** sandbox_type missing
Syntax: /usr/local/bin/make_sandbox_from_source source_directory sandbox_type [options]

source directory is where you have successfully
    run ./configure && make

sandbox_type is one of 
    single
    replication
    circular
    multiple

options is anything that is needed by the sandbox
    application of your choice

We want to test Group Replication plugin, so we need multiple option it will install 3 nodes of same version of MySQL by default. So after running we will get on our hand 3 nodes of Group Replication enabled MySQL.
We must just specify the source folder as follows:

sh@shrzayev:~$ make_sandbox_from_source /home/sh/Sandboxes/mysql-group-replication-0.5.0-dmr/BIN multiple
can't find executable script 'make_binary_distribution'

Again with appending BUILD folder:

sh@shrzayev:~$ make_sandbox_from_source /home/sh/Sandboxes/mysql-group-replication-0.5.0-dmr/BIN/BUILD multiple
can't find executable script 'make_binary_distribution'

No success.
If you go on and list BUILD folder:

sh@shrzayev:~/Sandboxes/mysql-group-replication-0.5.0-dmr/BIN/BUILD$ ls
mysql-server  mysql-server.build  mysql-server.inst

There is a mysql-server.build folder which we will append and run again:

sh@shrzayev:~$ make_sandbox_from_source /home/sh/Sandboxes/mysql-group-replication-0.5.0-dmr/BIN/BUILD/mysql-server.build/ multiple
can't find a version in Makefile

Error has changed -> can’t find a version in Makefile
After searching i found a BUG report(LP BUG 853764) related to this issue and found a workaround. We should add MYSQL_NO_DASH_VERSION option in Makefile:

sh@shrzayev:~/Sandboxes/mysql-group-replication-0.5.0-dmr/BIN/BUILD$ cd mysql-server.build/
sh@shrzayev:~/Sandboxes/mysql-group-replication-0.5.0-dmr/BIN/BUILD/mysql-server.build$ vim Makefile

And put MYSQL_NO_DASH_VERSION=5.7.8 (for me) anywhere in Makefile, save and exit. Then rerun:

sh@shrzayev:~$ make_sandbox_from_source /home/sh/Sandboxes/mysql-group-replication-0.5.0-dmr/BIN/BUILD/mysql-server.build/ multiple
no old tarball found
CPack: Create package using TGZ
CPack: Install projects
CPack: - Run preinstall target for: MySQL
CPack: - Install project: MySQL
CPack: Create package
CPack: - package: /home/sh/Sandboxes/mysql-group-replication-0.5.0-dmr/BIN/BUILD/mysql-server.build/mysql-5.7.8-rc-linux-x86_64.tar.gz generated.
installing node 1
installing node 2
installing node 3
group directory installed in $HOME/sandboxes/multi_msb_mysql-5_7_8

That’s it we have 3 nodes and we are ready to test plugin.
Check for node1 for eg:

node1 [localhost] {msandbox} ((none)) > select @@plugin_dir;
+-----------------------------------------------------------------------------------------------------+
| @@plugin_dir                                                                                        |
+-----------------------------------------------------------------------------------------------------+
| /home/sh/Sandboxes/mysql-group-replication-0.5.0-dmr/BIN/BUILD/mysql-server.build/5.7.8/lib/plugin/ |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

sh@shrzayev:~$ ls /home/sh/Sandboxes/mysql-group-replication-0.5.0-dmr/BIN/BUILD/mysql-server.build/5.7.8/lib/plugin/ | grep group_
group_replication_license
group_replication.so

We have our plugin in plugin directory.
In the subsequent topics i will talk about activating and using group replication plugin. Again, we are using Ubuntu 14.04 with MySQL 5.7.8-rc2 and mysql-group-replication-0.5.0-dmr.

The origin of “Assertion Failed” errors in MySQL

Recently, we have discussed BUGs with our local community members. Where the half off people said that, they have seen “Assertion Failure” errors in error log. And the question was what is this at all?

In general “assert” is a code portion put by developer to handle certain things.

Well, i am not a core MySQL developer and my C/C++ knowledge is limited to simple calculator 🙂 But i will try to explain where all these “Assertion Failure” errors come up.
The exact reason of failing Assertion/crash should be known by core developers. I know how to crash, they know how to fix. You help developers to reproduce a crash filling a bug report, by uploading test cases or maybe the core dump, backtrace etc.
Say, for eg, if you try to create a view after getting “full disk” error you will crash MySQL. See related BUG report -> #76827

While testing full disk conditions, if we try to import huge dump, we will see:

2015-04-24 09:07:52 7faf8bcab700 InnoDB: Error: Write to file ./sales/sales.ibd failed at offset 247463936.
InnoDB: 1048576 bytes should have been written, only 299008 were written.
InnoDB: Operating system error number 11.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 11 means 'Resource temporarily unavailable'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
2015-04-24 09:07:52 28213 [ERROR] /opt/mysql/bin/mysqld: The table 'sales' is full
2015-04-24 09:08:13 28213 [ERROR] /opt/mysql/bin/mysqld: The table 'sales' is full
2015-04-24 09:08:27 28213 [ERROR] /opt/mysql/bin/mysqld: The table 'sales' is full

After detecting Full Disk error, if you try to create view:

mysql> create view f as select * from sales;
ERROR 2013 (HY000): Lost connection to MySQL server during query

From error log:

mysqld: /root/mysql-5.6.24/mysys/mf_iocache.c:1799: my_b_flush_io_cache: Assertion `info->end_of_file == inline_mysql_file_tell("/root/mysql-5.6.24/mysys/mf_iocache.c", 1799, info->file, (myf) (0))' failed.
13:13:48 UTC - mysqld got signal 6 ;

So that’s all, from error log it obvious that there is a file named: /root/mysql-5.6.24/mysys/mf_iocache.c and on line 1799 there is an assert inside my_b_flush_io_cache function.
If you go ahead and open up this line you will see something like:

else
     {
        info->end_of_file+=(info->write_pos-info->append_read_pos);
       DBUG_ASSERT(info->end_of_file == mysql_file_tell(info->file, MYF(0)));
     }

For now let’s pause here and introduce new things, such as what is “Optimized”, “Debug” and “Valgrind” builds of MySQL. Please watch this wonderfull video recorded by QA expert Roel Van de Paar after you will learn about newly intoduced topics. -> MySQL QA Episode 11

In general the “Optimized” MySQL is a GA version of MySQL released by vendors. It is production ready and it is working as fast as possible. So there is no “unnecessary” codes inside this build.
The “Debug” build is for debugging purpose and there is a DEBUG instrumentation code portions inside source code.
in “Valgrind” build, there are “Debug” + “Valgrind” instrumentation codes inside source code.

So above we saw DBUG_ASSERT(info->end_of_file == mysql_file_tell(info->file, MYF(0))); -> It means that this “assert” code will be shown only with “Debug” build. You will not see this code in “Optimized” MySQL source code.

Ok, let’s go on. As we have mentioned “assert” code is written by developer to handle several conditions. It might be for eg, developer decides that, if variable named num will be equal to NULL something weird is happened, terminate the program at that point.

Let’s write a simple code with our very own “assert”. Here is our assert_test.c file:

#include <stdio.h>      /* printf */
#include <assert.h>     /* assert */

void check_number(int myInt) {
  assert (myInt!=NULL);
  printf ("%dn",myInt);
}

int main ()
{
  int a=10;
  int b = NULL;

  check_number (a);
  check_number (b);

  return 0;
}

We have put an “assert” ensuring that myInt will never be “NULL”.
Compile source code:

    gcc assert_test.c -o assert_test

And run:

sh@shrzayev:~$ ./assert_test
10
assert_test: assert_test.c:5: check_number: Assertion `myInt!=((void *)0)' failed.
Aborted (core dumped)

So as you see, the same error comes up with our little code.
We have assert_test.c file and inside check_number function at line 5 there is an “assert” code which is failed.

I Hope have explained this point. Thank you for reading.

Installing MySQL from source – CMAKE issues

Today’s topic is related primarily to compiling MySQL from source using CMAKE and what kind of issues we encounter during this task. We want to install MySQL 5.6.19 with Debug+Valgrind etc. On CentOS 7 here are my dependency packages start point:

[root@centos-base ~]# yum install zlib zlib-devel openssl openssl-devel valgrind valgrind-devel cmake gcc cpp ncurses ncurses-devel

Here is my CMAKE command:

[root@centos-base mysql-5.6.19]# cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql-5.6.19 -DMYSQL_DATADIR=/var/lib/mysql -DSYSCONFDIR=/opt/mysql-5.6.19 -DWITH_SSL=system -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/opt/mysql-5.6.19/mysqld.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_DEBUG=1 -DCOMPILATION_COMMENT="Shahriyar Rzayev's CentOS MySQL-5.6.19" -DOPTIMIZER_TRACE=1 -DWITH_ZLIB=system -DWITH_VALGRIND=1 -DCMAKE_C_FLAGS=-DHAVE_purify -DCMAKE_CXX_FLAGS=-DHAVE_purify

If you try to run this command first ERROR will be:

CMake Error: CMAKE_CXX_COMPILER not set, after EnableLanguage
CMake Error: Internal CMake error, TryCompile configure of cmake failed
-- Performing Test HAVE_PEERCRED - Failed
-- Library mysqlclient depends on OSLIBS -lpthread;/usr/lib64/libz.so;m;/usr/lib64/libssl.so;/usr/lib64/libcrypto.so;dl
-- Googlemock was not found. gtest-based unit tests will be disabled. You can run cmake . -DENABLE_DOWNLOADS=1 to automatically download and build required components from source.

Googlemock was not found -> to resolve this issue add -DENABLE_DOWNLOADS=1 to CMAKE command.

After you will see, it will download necessary package:

-- Library mysqlclient depends on OSLIBS -lpthread;/usr/lib64/libz.so;m;/usr/lib64/libssl.so;/usr/lib64/libcrypto.so;dl
-- Successfully downloaded http://googlemock.googlecode.com/files/gmock-1.6.0.zip to /root/mysql-5.6.19/source_downloads

Second issue you will likely see:

CMake Error: CMAKE_CXX_COMPILER not set, after EnableLanguage
CMake Error: Internal CMake error, TryCompile configure of cmake failed

The problem is missing gcc-c++ package, so install it:

[root@centos-base ~]# yum install gcc-c++

Third one is a Warning: Bison executable not found in PATH. Again just install bison package:

[root@centos-base ~]# yum install bison

After all we should say that following packages should be installed on server prior to compiling MySQL:

[root@centos-base ~]# yum install zlib zlib-devel openssl openssl-devel valgrind valgrind-devel cmake gcc cpp ncurses ncurses-devel bison gcc-c++

After getting success message:

-- Configuring done
-- Generating done
-- Build files have been written to: /root/mysql-5.6.19

Just run make & make install

##################################################################

UPDATE to topic

5.7.8-rc

With MySQL 5.7.8-rc you will likely hit error like:

-- MySQL currently requires boost_1_58_0

CMake Error at cmake/boost.cmake:76 (MESSAGE):
  You can download it with -DDOWNLOAD_BOOST=1 -DWITH_BOOST=<directory>

To resolve this issue add following to CMAKE command:

cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql-5.7.8-rc -DMYSQL_DATADIR=/opt/mysql-5.7.8-rc/datadir -DSYSCONFDIR=/opt/mysql-5.7.8-rc -DWITH_SSL=system -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/opt/mysql-5.7.8-rc/mysqld.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_DEBUG=1 -DCOMPILATION_COMMENT="Shahriyar Rzayev's CentOS MySQL-5.7.8-rc" -DOPTIMIZER_TRACE=1 -DWITH_ZLIB=system -DWITH_VALGRIND=1 -DCMAKE_C_FLAGS=-DHAVE_purify -DCMAKE_CXX_FLAGS=-DHAVE_purify -DENABLE_DOWNLOADS=1 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/opt/mysql-5.7.8-rc

You will see something like:

-- Packaging as: mysql-5.7.8-rc-Linux-x86_64
-- Downloading boost_1_58_0.tar.gz to /opt/mysql-5.7.8-rc
-- [download 0% complete]
-- [download 1% complete]
-- [download 2% complete]
-- [download 3% complete]
-- [download 4% complete]
-- [download 5% complete]

Unknown column ‘smth’ in ‘field list’ -> Oldie but goodie error

There is an error which is definitely new to people which have encountered such situation at first time.
It was reported a long time before:
#1689 -> 28 Oct 2003 4:46
#1478 -> 4 Oct 2003 10:58
#7501 -> 23 Dec 2004 5:35

Error is -> ERROR 1054 (42S22): Unknown column ‘c2’ in ‘field list’
The Place where you can encounter this error is something similar below:

mysql> show create table t1;
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `c1` varchar(85) DEFAULT NULL
) /*!50100 TABLESPACE innodb_file_per_table */ ENGINE=InnoDB DEFAULT CHARSET=utf8 |

If you try to insert data with wrong column name:

mysql> INSERT INTO t1(id, c2) VALUES (0, 'xxxxx');
ERROR 1054 (42S22): Unknown column 'c2' in 'field list'

But recently there was a question in our local community related to this error.
Suddenly i remembered this old but still good error 🙂
The reason was forgotten single quotes:

mysql> INSERT INTO t1(id, c1) VALUES (0, BEA10273D8);
ERROR 1054 (42S22): Unknown column 'BEA10273D8' in 'field list'

i wrote a simple Python script simulating this error(Showing application use case):

import mysql.connector
import uuid


cnx = mysql.connector.connect(user='all', password='12345',
                              host='192.168.1.190',
                              database='tst',
                              port='3306')

cursor = cnx.cursor()



def my_random_string(string_length=10):
    """Returns a random string of length string_length."""
    random = str(uuid.uuid4()) # Convert UUID format to a Python string.
    random = random.upper() # Make all characters uppercase.
    random = random.replace("-","") # Remove the UUID '-'.
    return random[0:string_length] # Return the random string.



sql = "INSERT INTO t1(id, c1) VALUES ({}, {})"

for i in range(10):

    ids = i
    strings = my_random_string(10)

    sql2 = sql.format(ids, strings)
    print sql2
    cursor.execute(sql2)



cnx.commit()
cursor.close()
cnx.close()

If you try to run:

sh@sh--work:~$ python insert2_577.py
INSERT INTO t1(id, c1) VALUES (0, BEA10273D8)
Traceback (most recent call last):
  File "insert2_577.py", line 32, in <module>
    cursor.execute(sql2)
  File "/usr/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 491, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 683, in cmd_query
    statement))
  File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 601, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'BEA10273D8' in 'field list'

Not so confusing!
Just do not forget to put necessary single quotes (”).

VALIDATE PASSWORD PLUGIN with mysql_secure_installation in 5.7.7-rc

While testing installation steps with MySQL 5.7.7-rc, surely you will find much more improvements, changes, deprecated functionality and etc.

The interesting one is activating VALIDATE PASSWORD PLUGIN via mysql_secure_installation script. Which we use by mean “securing” MySQL installations.

I will write a separate topic about MySQL 5.7.7-rc installation steps from source, with related BUG reports.

So after first run:

[root@centos7_vm mysql]# bin/mysql_secure_installation --socket=/opt/mysql/datadir/mysqld-new.sock
Securing the MySQL server deployment.
Connecting to MySQL server using password in '/root/.mysql_secret'

If you notice, now script trying to connect to MySQL using a temporary password which is generated for root@localhost and logged into hidden .mysql_secret file.

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: 1
Using existing root password.

Estimated strength of the password: 100 

From now we have an option to activate our password policy on the fly after fresh installation.
If you try to give a password such 12345 it will fail at password policy check:

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

New password: 

Re-enter new password: 

Estimated strength of the password: 25 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
 ... Failed! Error: Your password does not satisfy the current policy requirements

The fail reason is strength of password is equal to 25 , where it should be 100.
After giving a proper password:

New password: 

Re-enter new 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

The other parts of output should be familiar:

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! 

MySQL Optimizer Tracer usage case with count(*)

What is Optimizer Trace? After reading topic about Optimizer Tracer by [Morgan Tocker][1] decided to test it. From [Optimizer Trace and EXPLAIN FORMAT=JSON in 5.7][2]: Optimizer trace is a new diagnostic tool introduced in MySQL 5.6 to show how the optimizer is working internally. It is similar to EXPLAIN, with a few notable differences: It doesn’t just show the intended execution plan, it shows the alternative choices. You enable the optimizer trace, then you run the actual query. It is far more verbose in its output. For understanding goal of article please read previous one about related verified optimizer BUG: [Playing with count() optimizer work][3] ** We have 2 queries: **select count() from sales; select count(*) from sales where sales_id > 0; Firstly let’s get explain plan for query with JSON format and as regular:

       -- JSON 
          mysql> explain format=json select count(*) from sales; |
          { "query_block": 
          { "select_id": 1, 
          "table": { "table_name": "sales", 
                     "access_type":  "index", 
                     "key": "sales_cust_idx", 
                     "used_key_parts": [ "CUSTOMER_ID" ] 
                    /*  used_key_parts */, 
                    "key_length": "4", 
                    "rows": 2489938, 
                    "filtered": 100,
                     "using_index": true }  
            /* table */ } 
           /* query_block */ } 

        mysql> explain select count(*) from salesG 
        *************************** 1. row *************************** 
        id: 1 
        select_type: SIMPLE 
        table: sales 
        type: index 
        possible_keys: NULL 
        key: sales_cust_idx 
        key_len: 4 
        ref: NULL 
        rows: 2489938 
        Extra: Using index 1 row in set (0.00 sec) 

Second query:

       -- JSON  
       mysql> explain format=json select count(*) from sales where sales_id > 0G         *************************** 1. row *************************** 
    EXPLAIN: 
    { "query_block": 
    { "select_id": 1, 
    "table": 
    { "table_name": "sales", 
      "access_type": "range",
      "possible_keys": [ "PRIMARY" ], "key": "PRIMARY", 
      "used_key_parts": [ "SALES_ID" ], "key_length": "4", 
      "rows": 1244969, 
      "filtered": 100, 
      "using_index": true, 
     "attached_condition": "(`sales`.`sales`.`SALES_ID` > 0)" } } } 


     mysql> explain select count(*) from sales where sales_id > 0G 
     *************************** 1. row *************************** 
     id: 1 
     select_type: SIMPLE 
     table: sales 
     type: range 
     possible_keys: PRIMARY 
     key: PRIMARY 
     key_len: 4 
     ref: NULL 
     rows: 1244969 
     Extra: Using where; Using index 1 row in set (0.00 sec)  

From Explain plan it is obvious that, first query will use “Index Scan”, second will use “Range Scan + Index”. First query will use, sales_cust_idx in customer_id column, second query will use primary key in sales_id column. From first view, there now difference between queries, but optimizer estimates half of rows when attaching sales_id > 0 condition. See related BUG: [#68814][4] Now let’s examine problem with Optimizer Tracer. So before running query you should enable optimizer trace:

      SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
      SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000; 

After run first query:

      mysql> select count(*) from sales; 
      +----------+ 
      | count(*) |
      +----------+
      | 2500003 |
      +----------+ 
     1 row in set (0.58 sec) 

Query to OPTIMIZER_TRACE table from information_schema:

     mysql> select query, trace from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
     select count(*) from sales | 
    { "steps": 
   [ { "join_preparation": 
    { "select#": 1, 
    "steps": [ { "expanded_query": "/* select#1 */ select count(0) AS `count(*)` from `sales`" } ] /* steps */ }
    /* join_preparation */ },
    { "join_optimization": 
       { "select#": 1, 
         "steps": [ { "table_dependencies": [ { "table": "`sales`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] 
    /* depends_on_map_bits */ } ] 
    /* table_dependencies */ }, 
      { "rows_estimation": 
           [ { "table": "`sales`", "table_scan": { "rows": 2489938, "cost": 10347 }
          /* table_scan */ } ] 
          /* rows_estimation */ }, 
           { "considered_execution_plans": [ { "plan_prefix": [ ] 
           /* plan_prefix */,
           "table": "`sales`", 
           "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "rows": 2.49e6, "cost": 508335, "chosen": true } ] 
          /* considered_access_paths */ } 
          /* best_access_path */, 
         "cost_for_plan": 508335,
         "rows_for_plan": 2.49e6,
         "chosen": true } ]
         /* considered_execution_plans */ },
        { "attaching_conditions_to_tables": { "original_condition": null,  "attached_conditions_computation": [ ] 
        /* attached_conditions_computation */,
       "attached_conditions_summary": [ { "table": "`sales`", "attached": null } ] 
        /* attached_conditions_summary */ } 
        /* attaching_conditions_to_tables */ },
       { "refine_plan": [ { "table": "`sales`", "access_type": "index_scan" } ] 
        /* refine_plan */ } ] 
        /* steps */ } 
        /* join_optimization */ },
        { "join_execution": { "select#": 1, "steps": [ ] 
        /* steps */ } 
        /* join_execution */ } ] 
        /* steps */ 

Interesting part for query 1 is -> “cost_for_plan”: 508335, “rows_for_plan”: 2.49e6, “chosen”: true Cost is 508335, rows for plan is 2.49e6 = 2490000 rows, is roughly equal to explain plan estimation.
Now second query:

     mysql> select count(*) from sales where sales_id > 0;
     +----------+ 
     | count(*) |
     +----------+ 
     | 2500003 |
     +----------+ 1 row in set (1.18 sec) 

Query to OPTIMIZER_TRACE:

     mysql> select query, trace from INFORMATION_SCHEMA.OPTIMIZER_TRACE; 
     select count(*) from sales where sales_id > 0 | 
     { "steps": [ { "join_preparation": 
        { "select#": 1, 
          "steps": [ { "expanded_query": "/* select#1 */ select count(0) AS `count(*)` from `sales` where (`sales`.`SALES_ID` > 0)" } ] 
        /* steps */ } 
        /* join_preparation */ },
       { "join_optimization": 
          { "select#": 1, 
            "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`sales`.`SALES_ID` > 0)", "steps": [ { "transformation": "equality_propagation", 
           "resulting_condition": "(`sales`.`SALES_ID` > 0)" }, 
          { "transformation": "constant_propagation", "resulting_condition": "(`sales`.`SALES_ID` > 0)" }, 
          { "transformation": "trivial_condition_removal", "resulting_condition": "(`sales`.`SALES_ID` > 0)" } ] 
         /* steps */ } 
         /* condition_processing */ }, 
        { "table_dependencies": [ { "table": "`sales`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] 
        /* depends_on_map_bits */ } ] 
        /* table_dependencies */ }, 
       { "ref_optimizer_key_uses": [ ] 
        /* ref_optimizer_key_uses */ },
       { "rows_estimation": [ { "table": "`sales`", "range_analysis": { "table_scan": {  "rows": 2489938, "cost": 508337 } 
       /* table_scan */, 
       "potential_range_indices": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "SALES_ID" ] 
      /* key_parts */ }, 
      { "index": "sales_cust_idx", "usable": false, "cause": "not_applicable" } ] 
      /* potential_range_indices */,
     "best_covering_index_scan": { "index": "sales_cust_idx", "cost": 500418, "chosen": true } 
      /* best_covering_index_scan */,
      "setup_range_conditions": [ ] 
      /* setup_range_conditions */, 
      "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } 
      /* group_index_range */, 
      "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "0 < SALES_ID" ] 
     /* ranges */, 
     "index_dives_for_eq_ranges": true, 
     "rowid_ordered": true, 
     "using_mrr": false, "index_only": true, "rows": 1244969, "cost": 251364, "chosen": true } ] 
    /* range_scan_alternatives */,
   "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" }  /* analyzing_roworder_intersect */ } 
    /* analyzing_range_alternatives */,
   "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "PRIMARY", "rows": 1244969, "ranges": [ "0 < SALES_ID" ] 
   /* ranges */ } 
   /* range_access_plan */, 
   "rows_for_plan": 1244969, "cost_for_plan": 251364, "chosen": true } 
   /* chosen_range_access_summary */ } 
   /* range_analysis */ } ] 
   /* rows_estimation */ },
    { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`sales`", "best_access_path": { "considered_access_paths": [ { "access_type": "range", "rows": 1.24e6, "cost": 500357, "chosen": true } ] 
    /* considered_access_paths */ } 
    /* best_access_path */, 
    "cost_for_plan": 500357, "rows_for_plan": 1.24e6, "chosen": true } ] 
    /* considered_execution_plans */ },
    { "attaching_conditions_to_tables": { "original_condition": "(`sales`.`SALES_ID` > 0)", "attached_conditions_computation": [ ] 
   /* attached_conditions_computation */,
   "attached_conditions_summary": [ { "table": "`sales`", "attached": "(`sales`.`SALES_ID` > 0)" } ] 
   /* attached_conditions_summary */ } 
   /* attaching_conditions_to_tables */ }, 
   { "refine_plan": [ { "table": "`sales`", "access_type": "range" } ] /* refine_plan */ } ] 
   /* steps */ } 
   /* join_optimization */ }, 
  { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ 

It is much more complicated with second query and due to lacking documentation for all output, i am looking for explanations from experts.
First thing is, it says in “potential_range_indices” that “index”: “sales_cust_index” is not usable:

      "potential_range_indices": 
      [ { "index": "PRIMARY", "usable": true, "key_parts": [ "SALES_ID" ] 
      /* key_parts */ },
      { "index": "sales_cust_idx", "usable": false, "cause": "not_applicable" } ] 

But in “best_covering_index_scan”, “index”: “sales_cust_idx” is marked as “chosen”:true

       "best_covering_index_scan": { "index": "sales_cust_idx", "cost": 500418, "chosen": true } 

Second thing is, in “range_scan_alternatives” and,

        "analyzing_range_alternatives": 
        { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "0 < SALES_ID" ]  
        /* ranges */, 
       "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": true, "rows": 1244969, "cost": 251364, "chosen": true } 

in “chosen_range_access_summary”, “rows_for_plan” is 1244969 and “cost_for_plan” is 251364

       "chosen_range_access_summary": 
       { "range_access_plan": 
        { "type": "range_scan", "index": "PRIMARY", "rows": 1244969, "ranges": [ "0 < SALES_ID" ] 
         /* ranges */ } 
         /* range_access_plan */, 
        "rows_for_plan": 1244969, "cost_for_plan": 251364, "chosen": true } 

But for final “best_access_path” “cost_for_plan” is increased to 500357 and “rows_for_plan” is 1.24e6 = 1240000:

       "best_access_path": { "considered_access_paths": [ { "access_type": "range", "rows": 1.24e6, "cost": 500357, "chosen": true } ] 
       /* considered_access_paths */ } 
       /* best_access_path */, 
       "cost_for_plan": 500357, "rows_for_plan": 1.24e6, "chosen": true }

Third thing is that, sales_id > 0 is rewritten to 0 < sales_id

      "ranges": [ "0 < SALES_ID" ] 

*** After explanations from community this article will be updated ***

http://www.tocker.ca/
http://www.tocker.ca/2015/05/25/optimizer-trace-and-explain-formatjson-in-5-7.html
https://mysql.az/playing-with-count-optimizer-work/
http://bugs.mysql.com/bug.php?id=68814