Month: July 2017

Webinar Wednesday August 2, 2017: MySQL Disk Encryption with LUKS

MySQL Disk EncryptionJoin Percona’s, Senior Architect, Matthew Boehm as he presents MySQL Disk Encryption with LUKS on Wednesday, August 2, 2017, at 1:00 pm PDT / 4:00 pm EDT (UTC-7). Register Now Clients require strong security measures for PCI, HIPAA or PHI. You must encrypt MySQL “at rest” to satisfy the data managed under these standards. InnoDB’s built-in […]

What The Heck Is A Cloud Learning Subscription and Why Would I Want It?

Did you know that Oracle Certification is included in many of our new Oracle Cloud Learning Subscriptions?  

Learning Subscriptions that contain certification exams allow you to directly register for and take an exam wherever and whenever using your own computer.  This is done via online remote proctoring, making it more convenient than ever to enjoy the benefits of Oracle Certification. 

These benefits include expanded knowledge and skills, increased credibility, and more productivity. Training and certification make you better at what you do!

But what IS an Oracle Cloud Learning Subscription?

An Oracle Cloud Learning Subscription is an all-digital learning solution offering 24/7 access to complete sets of high definition training videos for anyone on your team seeking training. Whether you or your team are new to Cloud and need to ramp up, or you find yourself or your employees needing to stay current as applications are constantly changed and revised, Oracle Cloud Learning Subscriptions deliver. Offering a continuous learning system that ramps you up on new technologies and offers enhanced training with each new product release, Oracle Cloud Learning Subscriptions help you keep pace with current technologies.

Select a category below to find training and certification:

LEARN MORE

RELATED CONTENT

Getting Started with Oracle Cloud Learning Subscriptions

Maximize Your Cloud Investment with Oracle’s Unlimited Cloud Learning Subscription (UCLS)

How We Encrypt Data In MySQL With Go

A SaaS product needs to use security measures you might not ordinarily use in an on-premises solution. In particular, it’s important that all sensitive data be secured. Encryption plays an important role in information security. At VividCortex, we encrypt data in-flight and at-rest, so your sensitive data is never exposed.
We use Go and MySQL extensively at VividCortex and thought other Go programmers might be interested to see how we’ve integrated encryption into our services layer (APIs). (And if you’d like to learn more about programming with Go in general, please take a look at our free ebook The Ultimate Guide to Building Database-Driven Apps with Go.)
Image Source
Encryption Techniques
At a high level, you can think of two kinds of data encryption inside of MySQL or any similar data store. I’ll oversimplify for purposes of illustration. You can:

Store the data in MySQL as normal, but encrypt the container that holds MySQL. Usually this means storing MySQL’s data on an encrypted disk volume. The protection? Broadly speaking, if someone gains access to a backup disk, they can’t see your data.
Encrypt the data before sending it to MySQL. In this case the security boundary is pushed out further: even if someone gets access to the server, and can run SQL commands, they can’t see your data.

Each of these has advantages and disadvantages. These include ease of use, programmer overhead, ability to inspect (e.g. recovering from backups), searchability and indexability, and so on. There are a lot of things to consider here. Just a few:

Will data be exposed if backups are unencrypted? (Our backups are encrypted, by the way.)
Are sensitive values possibly in cleartext in query logs?
Will sensitive values be visible in status commands like SHOW FULL PROCESSLIST?

At VividCortex we err on the side of safety and security, rather than favoring convenience. There’s a fairly simple question that does a pretty good job of illustrating our goal: if someone succeeds in a SQL injection attack against our databases, will they see any sensitive data in cleartext? The answer needs to be “no.” This is a higher standard than on-disk encryption. It means that someone has to get access to the keys for the particular data they’re trying to decrypt, in order to see anything other than garbage. And those keys are not present or accessible on the database servers in any form, not even in-memory.
Making It Convenient
Convenience is important. If it’s too hard to do encryption, there’s an increased risk that it won’t be done. Fortunately, Go’s elegant interfaces for the database/sql package make the burden transparent to the programmer!
We learned how to do this from Jason Moiron’s excellent blog post on the Valuer and Scanner interfaces. Please read that if you haven’t yet.
To implement transparent encryption and decryption, we created a custom data type that implements the Valuer and Scanner interfaces. The implementation is straightforward and quite similar to Jason’s example of compressing and decompressing, except that we used encryption libraries instead.
Now our code is incredibly simple to use with encrypted values. All we do is define a variable of our custom type. For example, instead of
var password string
err = rows.Scan(&password)

We simply use
var password EncryptedValue
err = rows.Scan(&password)

It’s similarly simple to insert values encrypted into the database. Magic! This is why I often say that Go’s design, although it seems minimalistic at first, is actually very advanced and powerful.

“Go feels under-engineered because it only solves real problems.” Exactly. http://t.co/18LhLT0ALB #golang — VividCortex (@VividCortex)
September 18, 2014

Nuts And Bolts
The code is small. The exact details of all the code are not all that important for this blog post; much of it is about things that are out of scope here. The gist of it, though, is that we store values as byte arrays:

The first byte is an indicator of the version of our encryption algorithm used, so there’s a clear migration path for changes.
The next four bytes indicate which key we used to encrypt this value, so we have 4 billion possible keys.
The rest is the encrypted payload.

We can even change this in the future. For example, we can switch on the first byte’s value, if we want, to determine whether the key ID is in the next 4 bytes, or if it’s something more, such as the next 8 bytes. So we can easily expand the number of keys we can indicate. We can also, if we ever hit version 255, use that to indicate that the version number continues in the next byte. This is a standard trick used, among other places, by the MySQL wire protocol.
The result is that we have a simple and future-proof way to encrypt values.
Alternative Approaches
In addition to the approaches we’ve mentioned, there are several others. There are commercial projects designed to help ease the encryption and decryption techniques you might otherwise wrap around MySQL and perhaps fumble in some ways. There are encryption functions inside of MySQL—but educate yourself about those before using them. There are others, too, but you should be able to find all you need with a search.
Conclusions
By using Go’s built-in interfaces, we created a solution for transparently encrypting values in our database so that it’s never in the database in cleartext, either on-disk or in-memory. The code is easy for programmers to use, which improves our security posture automatically. All sensitive data gets encrypted in-flight and at-rest, and an attacker would have to have extensive access to our systems (an SQL injection wouldn’t suffice) to be able to decrypt the data.
We highly recommend that you use the standard Go interfaces for the power they give you. And please, ask your SaaS providers, including us, hard questions about security and how it’s implemented. Every service needs to be secure to make the Internet a safer place.
If you liked this and want to learn more about Go, you might also like our webinar about developing database-driven apps with Go and MySQL. Click below to watch a recording.

Post Updated 7/31/2017

How We Encrypt Data In MySQL With Go

A SaaS product needs to use security measures you might not ordinarily use in an on-premises solution. In particular, it’s important that all sensitive data be secured. Encryption plays an important role in information security. At VividCortex, we encrypt data in-flight and at-rest, so your sensitive data is never exposed.
We use Go and MySQL extensively at VividCortex and thought other Go programmers might be interested to see how we’ve integrated encryption into our services layer (APIs). (And if you’d like to learn more about programming with Go in general, please take a look at our free ebook The Ultimate Guide to Building Database-Driven Apps with Go.)
Image Source
Encryption Techniques
At a high level, you can think of two kinds of data encryption inside of MySQL or any similar data store. I’ll oversimplify for purposes of illustration. You can:

Store the data in MySQL as normal, but encrypt the container that holds MySQL. Usually this means storing MySQL’s data on an encrypted disk volume. The protection? Broadly speaking, if someone gains access to a backup disk, they can’t see your data.
Encrypt the data before sending it to MySQL. In this case the security boundary is pushed out further: even if someone gets access to the server, and can run SQL commands, they can’t see your data.

Each of these has advantages and disadvantages. These include ease of use, programmer overhead, ability to inspect (e.g. recovering from backups), searchability and indexability, and so on. There are a lot of things to consider here. Just a few:

Will data be exposed if backups are unencrypted? (Our backups are encrypted, by the way.)
Are sensitive values possibly in cleartext in query logs?
Will sensitive values be visible in status commands like SHOW FULL PROCESSLIST?

At VividCortex we err on the side of safety and security, rather than favoring convenience. There’s a fairly simple question that does a pretty good job of illustrating our goal: if someone succeeds in a SQL injection attack against our databases, will they see any sensitive data in cleartext? The answer needs to be “no.” This is a higher standard than on-disk encryption. It means that someone has to get access to the keys for the particular data they’re trying to decrypt, in order to see anything other than garbage. And those keys are not present or accessible on the database servers in any form, not even in-memory.
Making It Convenient
Convenience is important. If it’s too hard to do encryption, there’s an increased risk that it won’t be done. Fortunately, Go’s elegant interfaces for the database/sql package make the burden transparent to the programmer!
We learned how to do this from Jason Moiron’s excellent blog post on the Valuer and Scanner interfaces. Please read that if you haven’t yet.
To implement transparent encryption and decryption, we created a custom data type that implements the Valuer and Scanner interfaces. The implementation is straightforward and quite similar to Jason’s example of compressing and decompressing, except that we used encryption libraries instead.
Now our code is incredibly simple to use with encrypted values. All we do is define a variable of our custom type. For example, instead of
var password string
err = rows.Scan(&password)

We simply use
var password EncryptedValue
err = rows.Scan(&password)

It’s similarly simple to insert values encrypted into the database. Magic! This is why I often say that Go’s design, although it seems minimalistic at first, is actually very advanced and powerful.

“Go feels under-engineered because it only solves real problems.” Exactly. http://t.co/18LhLT0ALB #golang — VividCortex (@VividCortex)
September 18, 2014

Nuts And Bolts
The code is small. The exact details of all the code are not all that important for this blog post; much of it is about things that are out of scope here. The gist of it, though, is that we store values as byte arrays:

The first byte is an indicator of the version of our encryption algorithm used, so there’s a clear migration path for changes.
The next four bytes indicate which key we used to encrypt this value, so we have 4 billion possible keys.
The rest is the encrypted payload.

We can even change this in the future. For example, we can switch on the first byte’s value, if we want, to determine whether the key ID is in the next 4 bytes, or if it’s something more, such as the next 8 bytes. So we can easily expand the number of keys we can indicate. We can also, if we ever hit version 255, use that to indicate that the version number continues in the next byte. This is a standard trick used, among other places, by the MySQL wire protocol.
The result is that we have a simple and future-proof way to encrypt values.
Alternative Approaches
In addition to the approaches we’ve mentioned, there are several others. There are commercial projects designed to help ease the encryption and decryption techniques you might otherwise wrap around MySQL and perhaps fumble in some ways. There are encryption functions inside of MySQL—but educate yourself about those before using them. There are others, too, but you should be able to find all you need with a search.
Conclusions
By using Go’s built-in interfaces, we created a solution for transparently encrypting values in our database so that it’s never in the database in cleartext, either on-disk or in-memory. The code is easy for programmers to use, which improves our security posture automatically. All sensitive data gets encrypted in-flight and at-rest, and an attacker would have to have extensive access to our systems (an SQL injection wouldn’t suffice) to be able to decrypt the data.
We highly recommend that you use the standard Go interfaces for the power they give you. And please, ask your SaaS providers, including us, hard questions about security and how it’s implemented. Every service needs to be secure to make the Internet a safer place.
If you liked this and want to learn more about Go, you might also like our webinar about developing database-driven apps with Go and MySQL. Click below to watch a recording.

Post Updated 7/31/2017

What’s New in MariaDB Connector/C 3.0

What’s New in MariaDB Connector/C 3.0
RalfGebhardt

Mon, 07/31/2017 – 05:41

We are pleased to announce the general availability (GA) of MariaDB Connector/C 3.0. MariaDB Connector/C 3.0.2 is the newest version of MariaDB Connector/C. This release is compatible with MariaDB Connector/C 2.3 – no code changes necessary to upgrade.

MariaDB Connector/C 3.0 includes new security enhancements, plugins and API functions.

Security

In addition to OpenSSL, MariaDB Connector/C 3.0 now supports:

GnuTLS
Windows SChannel: removes dependencies on external libraries
Windows SChannel: becomes the default for SSL on Windows
TLSv1.1 and TLSv1.2 support
Passphrase protected private keys

Plugins

All plugins can either be linked statically or built as shared objects (or dynamic link libraries on Windows)
Pluggable Virtual IO (PVIO) for communication via socket, named pipe and shared memory
Connection plugins, e.g for Aurora failover or replication (master write, slave read)
Remote IO plugin, which allows to access remote files (via http, https, ftp, ldap, …)
Trace plugin (for analyzing and dumping network traffic)
New GSSAPI authentication plugin

New API Functions

MariaDB Connector/C 3.0 is introducing the following new API functions:

Bulk operations (array binding) for prepared statements (insert, update, delete).
support for extended client/server capabilities (requires MariaDB 10.2 or newer)

mariadb_get_charset_by_name and mariadb_get_charset_by_nr, which return charset information for a given internal number or name of character set. These functions have been previously used internally by MariaDB Connector/ODBC and are now exported, so they can be used also within plugins.

mariadb_stmt_execute_direct prepares and executes in one step (mainly used by MariaDB ODBC driver)

mariadb_cancel aborts a connection immediately by making all subsequent read/write operations fail

mysql_get_option and mysql_get_optionv (variable argument list) for obtaining option values for a given connection.

mysql_reconnect which was used internally before (if the option MYSQL_OPT_RECONNECT was set) is now part of the API and can be used by applications and plugins to re-establish a failing connection

mysql_reset_connection resets the current connection and clears session state

mysql_stmt_warning_count returns warnings per statement
Functions for obtaining session state changes:

mysql_session_track_get_first
mysql_session_track_get_next

Added tls_version support for SChannel. tls_version has to be specified via mysql_options(mysql, MARIADB_OPT_TLS_VERSION, …)

 

Download the MariaDB Connector now and learn about the newest evolution of MariaDB Connector/C 3.0.

Download Knowledge Base

MariaDB Releases

We are pleased to announce the general availability (GA) of MariaDB Connector/C 3.0. MariaDB Connector/C 3.0.2 is the newest version of MariaDB Connector/C. This release is compatible with MariaDB Connector/C 2.3

Login
or
Register
to post comments

Cassandra Query Language

After installing Cassandra and reading Cassandra The Definitive Guide, it struck me that I should learn a bit more about the Cassandra Query Language (CQL). So, after I setup a single-node environment and created a .bashcassandra environment file to connect as a student user to the Cassandra instance:

# Add the Java and JRE paths to the $PATH environments.
export set PATH=$PATH:/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.45.x86_64:/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.45.x86_64/jre

# Add the $JAVA_HOME and $JRE_HOME environment variables.
export set JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.45.x86_64/
export set JRE_HOME=/usr

Having started Cassandra as the cassandra user, I connected to the Cassandra Query Language Shell (cqlsh) to learn how to write CQL. You can find the basic structure of the Cassandra Query Language (CQL) on the Apache Cassandra website. I also discovered that CQL by itself can’t let you join tables without using Apache SparkSQL. Apache SparkSQL adds the ability to perform CQL joins in Cassandra, and became available in 2015.
I also learned you can’t use a CREATE OR REPLACE command when you change certain aspects of User-Defined Functions (UDFs). You actually need to drop any UDF before you change RETURNS NULL ON NULL INPUT clause to a CALLED ON NULL INPUT clause or vice versa. You can’t embed Java that connects to database without using the cassandra-java-driver-2.0.2 driver.
You connect to the cqlsh like this:

cqlsh

Here’s my script that creates Cassandra keyspace, which is more or less a database. You use the USE command to connect to the keyspace or database, like you would in MySQL. You do not have sequences in Cassandra because they’re not a good fit for a distributed architecture. Cassandra does not support a native procedural extension like relational databases. You must create User-defined functions (UDFs) by embedding the logic in Java.
This script does the following:

Creates a keyspace

Uses the keyspace

Conditionally drops tables and functions
Creates two tables
Inserts data into the two tables
Queries data from the tables

I also included a call to a UDF inside a query in two of the examples. One of the queries demonstrates how to return a JSON structure from a query. To simplify things and provide clarification of the scripts behaviors, the details are outlined below.

The first segment of the script creates the keyspace, changes the scope to use the keyspace, conditionally drop tables, create tables, and insert values into the tables:

/* Create a keyspace in Cassandra, which is like a database
in MySQL or a schema in Oracle. */
CREATE KEYSPACE IF NOT EXISTS student
WITH REPLICATION = {
‘class’:’SimpleStrategy’
,’replication_factor’: 1 }
AND DURABLE_WRITES = true;

/* Use the keyspace or connect to the database. */
USE student;

/* Drop the member table from the student keyspace. */
DROP TABLE IF EXISTS member;

/* Create a member table in the student keyspace. */
CREATE TABLE member
( member_number VARCHAR
, member_type VARCHAR
, credit_card_number VARCHAR
, credit_card_type VARCHAR
, PRIMARY KEY ( member_number ));

/* Conditionally drop the contact table from the student keyspace. */
DROP TABLE IF EXISTS contact;

/* Create a contact table in the student keyspace. */
CREATE TABLE contact
( contact_number VARCHAR
, contact_type VARCHAR
, first_name VARCHAR
, middle_name VARCHAR
, last_name VARCHAR
, member_number VARCHAR
, PRIMARY KEY ( contact_number ));

/* Insert a row into the member table. */
INSERT INTO member
( member_number, member_type, credit_card_number, credit_card_type )
VALUES
(‘SFO-12345′,’GROUP’,’2222-4444-5555-6666′,’VISA’);

/* Insert a row into the contact table. */
INSERT INTO contact
( contact_number, contact_type, first_name, middle_name, last_name, member_number )
VALUES
(‘CUS_00001′,’FAMILY’,’Barry’, NULL,’Allen’,’SFO-12345′);

/* Insert a row into the contact table. */
INSERT INTO contact
( contact_number, contact_type, first_name, middle_name, last_name, member_number )
VALUES
(‘CUS_00002′,’FAMILY’,’Iris’, NULL,’West-Allen’,’SFO-12345′);

/* Insert a row into the member table. */
INSERT INTO member
( member_number, member_type, credit_card_number, credit_card_type )
VALUES
(‘SFO-12346′,’GROUP’,’3333-8888-9999-2222′,’VISA’);

/* Insert a row into the contact table. */
INSERT INTO contact
( contact_number, contact_type, first_name, middle_name, last_name, member_number )
VALUES
(‘CUS_00003′,’FAMILY’,’Caitlin’,’Marie’,’Snow’,’SFO-12346′);

The following queries the member table:

/* Select all columns from the member table. */
SELECT * FROM member;

It returns the following:

member_number | credit_card_number | credit_card_type | member_type
—————+———————+——————+————-
SFO-12345 | 2222-4444-5555-6666 | VISA | GROUP
SFO-12346 | 3333-8888-9999-2222 | VISA | GROUP

Create a concatenate User-defined function (UDF) for Cassandra. The first step requires you to edit the cassandra.yaml file, which you find in the /etc/cassandra/default.conf directory. There is a single parameter that you need to edit, and it is the enable_user_defined_functions parameter. By default the parameter is set to false, and you need to enable it to create UDFs.

After you make the edit, the cassandra.yaml file should look like this:

# If unset, all GC Pauses greater than gc_log_threshold_in_ms will log at
# INFO level
# UDFs (user defined functions) are disabled by default.
# As of Cassandra 3.0 there is a sandbox in place that should prevent execution of evil code.
enable_user_defined_functions: true

After you make the change, you can create your own UDF. The following UDF formats the first, middle, and last name so there’s only one whitespace between the first and last name when there middle name value is null.
This type of function must use a CALLED ON NULL INPUT clause in lieu of a RETURNS NULL ON NULL INPUT clause. The latter would force the function to return a null value if any one of the parameters were null.

/* Drop the concatenate function because a replace disallows changing a
RETURNS NULL ON NULL INPUT with a CALLED ON NULL INPUT without raising
an “89: InvalidRequest” exception. */
DROP FUNCTION concatenate;

/* Create a user-defined function to concatenate names. */
CREATE OR REPLACE FUNCTION concatenate (first_name VARCHAR, middle_name VARCHAR, last_name VARCHAR)
CALLED ON NULL INPUT
RETURNS VARCHAR
LANGUAGE java
AS $$
/* Concatenate first and last names when middle name is null, and
first, middle, and last names when middle name is not null. */
String name;

/* Check for null middle name. */
if (middle_name == null) {
name = first_name + ” ” + last_name; }
else {
name = first_name + ” ” + middle_name + ” ” + last_name; }

return name;
$$;

Query the values from the contact table with the UDF function in the SELECT-list:

/* Query the contact information. */
SELECT member_number
, contact_number
, contact_type
, concatenate(first_name, middle_name, last_name) AS full_name
FROM contact;

It returns the following:

member_number | contact_number | contact_type | full_name
—————+—————-+————–+——————–
SFO-12345 | CUS_00001 | FAMILY | Barry Allen
SFO-12345 | CUS_00002 | FAMILY | Iris West-Allen
SFO-12346 | CUS_00003 | FAMILY | Caitlin Marie Snow

Query the values from the contact table with a JSON format:

/* Query the contact information and return in a JSON format. */
SELECT JSON
contact_number
, contact_type
, concatenate(first_name, middle_name, last_name) AS full_name
FROM contact;

It returns the following:

[json]
————————————————————————————————-
{“contact_number”: “CUS_00001”, “contact_type”: “FAMILY”, “full_name”: “Barry Allen”}
{“contact_number”: “CUS_00002”, “contact_type”: “FAMILY”, “full_name”: “Iris West-Allen”}
{“contact_number”: “CUS_00003”, “contact_type”: “FAMILY”, “full_name”: “Caitlin Marie Snow”}

You can call the script from a relative directory inside cqlsh, like this:

source ‘cstudent.cql’

At the end of the day, the concept of adding and removing nodes is attractive. Though, the lack of normal relational mechanics and narrowly supported set of CQL semantics leaves me with open questions. For example, is clustering without a coordinator really valuable enough to settle for eventual, or tunable, consistency with such a narrowly scoped query language?
As always, I hope this helps those looking for a quick how-to on Cassandra.

How to Find Processlist Thread id in gdb

I was involved in a discussion on some complex MySQL-related problem where we had to study backtraces of all threads in gdb (produced by the thread apply all bt command if you ever forgets this) in a hope to find out why MySQL hangs. In the process the question appeared on how to find the thread id for each thread to match it against previous collected outputs of SHOW PROCESSLIST. and SHOW ENGINE INNODB STATUS.I assumed I know the answer, as I had to find this out recently enough for this blog post (and before that for the real customer case). The idea is simple. Find a frame where function has a parameter of THD * type (usually named thd), like this:#10 0x0000000000cb47fe in do_command (thd=0x7f32512b7000)    at /usr/src/debug/percona-server-5.7.18-15/percona-server-5.7.18-15/sql/sql_parse.cc:960and check thread_id item of this structure.In that my blog post it looked as simple as just referring to thd of do_command’s frame without even checking much:(gdb) thread 2[Switching to thread 2 (Thread 0x7f7f5ce02b00 (LWP 9232))]#0  pthread_cond_timedwait@@GLIBC_2.3.2 ()    at ../nptl/sysdeps/unix/sysv/linux/x86_64/pthread_cond_timedwait.S:238238     ../nptl/sysdeps/unix/sysv/linux/x86_64/pthread_cond_timedwait.S: No such file or directory.(gdb) p do_command::thd->thread_id$9 = 14I prefer to double check my suggestions before making them, so I immediately tried this with my CentOS 6.9 VM running recent Percona Server 5.7.x by default since that times when I worked at Percona:[root@centos ~]# gdb -p `pidof mysqld`GNU gdb (GDB) Red Hat Enterprise Linux (7.2-92.el6)…Loaded symbols for /usr/lib64/mysql/plugin/tokudb_backup.so0x00007f550ad35383 in poll () from /lib64/libc.so.6Missing separate debuginfos, use: debuginfo-install glibc-2.12-1.209.el6_9.2.x86_64 jemalloc-3.6.0-1.el6.x86_64 keyutils-libs-1.4-5.el6.x86_64 krb5-libs-1.10.3-65.el6.x86_64 libaio-0.3.107-10.el6.x86_64 libcom_err-1.41.12-23.el6.x86_64 libgcc-4.4.7-18.el6.x86_64 libselinux-2.0.94-7.el6.x86_64 libstdc++-4.4.7-18.el6.x86_64 nss-softokn-freebl-3.14.3-23.3.el6_8.x86_64 numactl-2.0.9-2.el6.x86_64 openssl-1.0.1e-57.el6.x86_64 zlib-1.2.3-29.el6.x86_64(gdb) thread 1[Switching to thread 1 (Thread 0x7f550d2b2820 (LWP 1978))]#0  0x00007f550ad35383 in poll () from /lib64/libc.so.6(gdb) p do_command::thd->thread_idNo frame is currently executing in block do_command(THD*).(gdb) thread 2[Switching to thread 2 (Thread 0x7f54d837b700 (LWP 2183))]#0  0x00007f550ad35383 in poll () from /lib64/libc.so.6(gdb) p do_command::thd->thread_idCannot take address of method thread_id.(gdb) call do_command::thd->thread_id()Cannot evaluate function — may be inlinedAs you can see I started to check threads one by one and apply that good old trick. Thread 1 had no frame executing do_command(), but I did not gave up and proceeded to the next thread, as I knew I had at least one active connection (I checked the output of SHOW PROCESSLIST). There I had a surprise, no way to get thread_id of thd. I used tab completion, so I know that thread_id (variable or method) exists, but attempt to call it also failed as you can see.This is a problem with using gdb-based “tricks” over the code that evolves/changes in time. Last time I used p do_command::thd->thread_id it was for MariaDB 10.1.x probably, and the item was there. But in MySQL 5.7 (and all forks based on it) there were many code changes, so we should be ready to changes in unexpected places.I had not added more comments on finding thread id to that discussion, made a note to myself and then, later, decided to check the source code of MySQL 5.7 (I did not have Percona 5.7 one at hand, but they hardly differs in such basic details) to find out what had changed in the THD structure so that thread_id is not longer just a variable. I expect to see the structure defined in sql/sql_class.h from the past, but grep will help to find this out even if it’s no longer the case:[root@centos mysql-server]# grep -n “class THD” sql/*.hsql/debug_sync.h:27:class THD;sql/derror.h:24:class THD;sql/event_data_objects.h:40:class THD;…sql/sql_class.h:1412:class THD :public MDL_context_owner,sql/sql_class.h:4175:    raise_error() or raise_warning() methods provided by class THD.sql/sql_cmd.h:25:class THD;… I found the following there:class THD :public MDL_context_owner,           public Query_arena,           public Open_tables_state{…private:  my_thread_id  m_thread_id;public:…  /**    Assign a value to m_thread_id by calling    Global_THD_manager::get_new_thread_id().  */  void set_new_thread_id();  my_thread_id thread_id() const { return m_thread_id; }…So, in MySQL 5.7 thread_id() is, indeed, a method that was inlined, and essentially it returns private m_thread_id item. Benefits of C++… I had highlighted Global_THD_manager singleton also as during my next gdb sessions I had found out that simple global list of threads is also gone and in 5.7 everything is done via that Global_THD_manager. This is a topic for some other post, though.At least now I know what to do in gdb:…(gdb) thread 7[Switching to thread 7 (Thread 0x7f54d8236700 (LWP 2275))]#0  0x00007f550ad35383 in poll () from /lib64/libc.so.6(gdb) p do_command::thd->m_thread_id$1 = 86(gdb) p do_command::thd->m_main_security_ctx$3 = {m_user = {m_ptr = 0x7f5500fdaf90 “myuser”, m_length = 6,    m_charset = 0x1ded640, m_alloced_length = 8, m_is_alloced = true},  m_host = {m_ptr = 0x7f54d98ab090 “localhost”, m_length = 9,    m_charset = 0x1ded640, m_alloced_length = 16, m_is_alloced = true},  m_ip = {m_ptr = 0x7f54f0eb0210 “127.0.0.1”, m_length = 9,    m_charset = 0x1ded640, m_alloced_length = 16, m_is_alloced = true},  m_host_or_ip = {m_ptr = 0x7f54d98ab090 “localhost”, m_length = 9,    m_charset = 0x1ded640, m_alloced_length = 0, m_is_alloced = false},  m_external_user = {m_ptr = 0x15167ab “”, m_length = 0,    m_charset = 0x1ded640, m_alloced_length = 0, m_is_alloced = false},  m_priv_user = “myuser”, ’00’ <repeats 89 times>, m_priv_user_length = 6,  m_proxy_user = ’00’ <repeats 161 times>, m_proxy_user_length = 0,  m_priv_host = “localhost”, ’00’ <repeats 51 times>,  m_priv_host_length = 9, m_master_access = 1589248, m_db_access = 0,  m_password_expired = false}…So, I know that specific thread  7 was for a session with Id 86 in the output of SHOW PROCESSLIST, and (from m_main_security_ctx, also a new name for old things in 5.7) I know it was a session of myuser connecting locally.To summarize, there were notable changes in MySQL 5.7 in THD structure and threads management-related code in general, so make sure to re-check your “old gdb tricks” when you start working with 5.7. Reading the code helps.Unfortunately (for gdb beginners like me) a lot of C++ approaches were introduced, including singletons, iterators based on templates instead of simple double linked lists etc, so one has to work hard to adapt to these. I hope to discuss some of my further findings and new “C++ specific” and “MySQL 5.7 specific” approaches studying MySQL in gdb in my upcoming posts.

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