Planet MySQL

Planet MySQL - https://planet.mysql.com
  1. Configuring an ODBC Driver Manager on Windows, macOS, and Linux

    This article explains how to install and configure the ODBC Driver Manager on Windows, macOS, and Linux. Also, it shows how to configure the ODBC data source name. Contents What is ODBC Driver Manager? How to Install ODBC Driver Manager Installing and Configuring an ODBC Driver Manager on Windows Installing and Configuring an ODBC Driver […] The post Configuring an ODBC Driver Manager on Windows, macOS, and Linux appeared first on Devart Blog.
  2. The MySQL Track at the Southern California Linux Expo 2022

     Yes, SCaLE, or the Southern California Linux Expo is back. Friday is the MySQL track with eight presentations. A big thanks to all the presenters and we hope to see you at the LAX Hilton July 29th!MySQL 8.0 New FeaturesDave StokesAudience:EveryoneTopic:MySQLMySQL now features quarterly releases that come with new features.  So if you do not know about hash joins, replica set, JSON document validation, or other new features then you need to be in this presentation.!Room:CarmelTime:Friday, July 29, 2022 - 10:00 to 11:00State of MySQL Security in 2022Alexander RubinAudience:DeveloperTopic:MySQLIn this talk we will look into the state of MySQL Security in 2022. We will cover the new security focused features available in MySQL (and MySQL Compatible variants) as well as share best practices to consider for secure MySQL deployment.Room:CarmelTime:Friday, July 29, 2022 - 11:00 to 12:00Forward to the Past: Point in time recovery for MySQLPep PlaAudience:IntermediateTopic:MySQLDisasters happen. Somebody runs a process that renders the data in your database unusable, inconsistent, or just deleted. Wouldn't it be great to be able to recover your database to the state just before the disaster happened?In this talk we will learn the basics of Point in time recovery for MySQL:- Disasters happen.- You need three things.- I know what you did last transaction: the binary logs.- I need some backup.- The quest for the third thing: find the last transaction before the disaster.- Putting all together the traditional way.- Make it faster using replication.- What you can't recover, or at least you can't easily recover: the transactions executed after the disaster.Room:CarmelTime:Friday, July 29, 2022 - 13:00 to 14:00Query Optimization 101 in MySQLMatthew BoehmQueries are essential to retrieving data from your database. How do you know if your queries are performing well? What is an index, and how does it help access the data? We will answer these questions and many more as we take our first steps into the realm of query optimization.Room:CarmelTime:Friday, July 29, 2022 - 14:00 to 15:00I need some (MySQL) backup!Pep PlaAudience:IntermediateTopic:MySQL"If you have a hammer, all you see is nails." also known as the Law of the Instrument describes the cognitive bias that involves over-reliance on a familiar tool. To protect MySQL data, a DBA must have a toolbox full of the proper utensils. To know only one approach is not enough.In this talk, we will cover the following topics:- Why your backup strategy is probably wrong?- Backups: Cold, hot... and warm?- The right answer is not always the logical one.- Replication is not the solution.- Cold backups: rsync, snapshots...- Warm backup using rsync.- The logical song:   - Mysqldump.   - Mydumper.   - Mysqlpump.   - MySQL shell.- Getting physical:   - Percona XtraBackup: my swiss army knife.   - MySQL Enterprise Backup.- Point-in-time recovery.- Don't try this at prod: Test your backups.Room:CarmelTime:Friday, July 29, 2022 - 15:00 to 16:00The Great Migration: Galera Cluster to InnoDB ClusterMichael MarxTopic:MySQLSince the introduction of replication in MySQL, users have been trying to automate the promotion of a replica to a primary as well as automating the failover of TCP connections from one database server to another in the event of a database failure: planned or unplanned. For over a decade, users and organizations have designed various types of solutions to achieve this. Though, many of these solutions were problematic. Eventually, a solution was introduced to the community that clustered the MySQL Server. This product is known as Percona XtraDB Cluster and uses a technology called Galera to achieve high availability.To compete with this product, MySQL eventual introduced cluster for MySQL, known as InnoDB Cluster. This is the official high availability solution for and from MySQL. It utilizes some of the same principles as Percona XtraDB Cluster, but it includes a mysql shell with an API that makes setting up, configuring, and monitor the InnoDB Cluster much easier than Galera. In addition, the InnoDB Cluster includes a proxy called router that allows for automatic failover of TCP connections if a node in the cluster becomes unavailable.Through this presentation the InnoDB Cluster will be explored in detail as well as simple instructions on how to move from Percona XtraDB Cluster to MySQL InnoDB Cluster.Room:CarmelTime:Friday, July 29, 2022 - 16:00 to 17:00The Great Migration: Galera Cluster to InnoDB ClusterMichael MarxTopic:MySQLSince the introduction of replication in MySQL, users have been trying to automate the promotion of a replica to a primary as well as automating the failover of TCP connections from one database server to another in the event of a database failure: planned or unplanned. For over a decade, users and organizations have designed various types of solutions to achieve this. Though, many of these solutions were problematic. Eventually, a solution was introduced to the community that clustered the MySQL Server. This product is known as Percona XtraDB Cluster and uses a technology called Galera to achieve high availability.To compete with this product, MySQL eventual introduced cluster for MySQL, known as InnoDB Cluster. This is the official high availability solution for and from MySQL. It utilizes some of the same principles as Percona XtraDB Cluster, but it includes a mysql shell with an API that makes setting up, configuring, and monitor the InnoDB Cluster much easier than Galera. In addition, the InnoDB Cluster includes a proxy called router that allows for automatic failover of TCP connections if a node in the cluster becomes unavailable.Through this presentation the InnoDB Cluster will be explored in detail as well as simple instructions on how to move from Percona XtraDB Cluster to MySQL InnoDB Cluster.Room:CarmelTime:Friday, July 29, 2022 - 16:00 to 17:00MySQL IndexesDave StokesAudience:EveryoneTopic:MySQLNobody complains where the database is fast.  But adding one index speeds things up.  But the second index makes things worse?  There is a lot of mythology about indexes in the MySQL area and this talk will show that getting performance out of an index is simple engineering and not magic.  You will walk out of this session know how indexes are built, how to use them to provide better information to the query optimizer, and how to make sure that what you are doing is a positive change and not hurting your instance performance.Room:CarmelTime:Friday, July 29, 2022 - 17:00 to 18:00All opinions expressed in this blog are those of Dave Stokes who is actually amazed to find anyone else agreeing with him
  3. Digital Signatures: Another Layer of Data Protection in Percona Server for MySQL

    Imagine you need to design an online system for storing documents on a per-user basis where nobody, including database administrators, would be able to change the content of those documents without being noticed by document owners. In Percona Server for MySQL 8.0.28-20, we added a new component called Encryption UDFs – an open-source alternative to MySQL Enterprise Encryption that allows users to access a number of low-level OpenSSL encryption primitives directly from MySQL. This includes calculating digests (with a great variety of hash functions), asymmetric key generation (RSA, DSA), asymmetric encryption/decryption for RSA, and calculating/verifying digital signatures (RSA, DSA) as well as primitives for working with Diffie-Hellman (DH) key exchange algorithm. Prerequisites In contrast to MySQL Enterprise Encryption, to make the functions available, users of Percona Server for MySQL do not have to register them manually withCREATE FUNCTION … SONAME …for each individual function. All they have to do is invokeINSTALL COMPONENT 'file://component_encryption_udf' Schema definition Now, let us define a simple data schema in which we will store all the info required for our online document storage service. It will have two tables: user and document.CREATE TABLE user( id INT UNSIGNED NOT NULL AUTO_INCREMENT, login VARCHAR(128) NOT NULL, key_type VARCHAR(16) NOT NULL, public_key_pem TEXT NOT NULL, PRIMARY KEY(id), UNIQUE(login) );Here,id – unique user identifier (numerical, for internal usage).login – unique user identifier (lexical, for public usage).key_type – type of the asymmetric key generated by the user (currently, either RSA or DSA).public_key_pem – public component of the asymmetric key generated by the user in PEM format (“–––––BEGIN PUBLIC KEY–––––”).CREATE TABLE document( id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_ref INT UNSIGNED NOT NULL, name VARCHAR(128) NOT NULL, content BLOB NOT NULL, digest_type VARCHAR(16) NOT NULL, signature VARBINARY(2048) NOT NULL, PRIMARY KEY(id), FOREIGN KEY (user_ref) REFERENCES user(id), UNIQUE (user_ref, name) );Here,id – unique document identifier (numerical, internal).user_ref – the ID of the user who owns the document.name – a name under which the document is stored for the user_ref user.content – binary object that holds document content.digest_type – the name of the hash function used to calculate the digital signature on content.signature – digital signature calculated by signing the digest value (calculated with digest_type hash function on content) with a private key that belongs to user_ref. User registration So, the first step in registering a new user would be to generate an asymmetric key pair. Although in this example we will be using the RSA algorithm, our system is flexible enough and allows us to use DSA keys as well. Important notice: for our use case, private keys must always be generated on the client machine and never leave local secure storage. Only public keys are allowed to be transferred via the network. To generate an RSA private key, the user can run the standard openssl utility.openssl genrsa -out private_key.pem 4096The content of the generated 4096-bit RSA private key in PEM format will be written to the private_key.pem file. Next, we need to extract the public component from the generated key.openssl rsa -in private_key.pem -pubout -out public_key.pemThe extracted 4096-bit RSA public key in PEM format will be written to the public_key.pem file.SET @public_key = '<public_key_pem_content>';Here, <public_key_pem_content> is the content of the public_key.pem file (a public key in PEM format with the “–––––BEGIN PUBLIC KEY–––––” header). Just for the simplicity of this blog post (Once again, never use this approach in a real system in production), RSA / DSA keys can be generated by the Percona Server:SET @algorithm = 'RSA'; SET @private_key = create_asymmetric_priv_key(@algorithm, 4096); /* never do this in production */ SET @public_key = create_asymmetric_pub_key(@algorithm, @private_key); /* never do this in production */Now, when we have a public key, the user registration is straightforward.INSERT INTO user VALUES(DEFAULT, 'alice', 'RSA', @public_key);Again, in production, @public_key must be set to the content of the public_key.pem generated locally (with openssl utility, for instance) rather than generated on the server. Uploading documents When a user wants to upload a new file to our online document storage, the first step would be to calculate its digital signature. For instance, if we are going to upload a local file called secure_data.doc and we want to use, say, SHA256 as a hash function to calculate digest before signing with a previously generated 4096-bit RSA private key, execute the followingopenssl dgst -sha256 -sign private_key.pem -out secure_data.binsig secure_data.docThe signature in binary format will be written to the secure_data.binsig. In order to simplify copying the content of this file to SQL statements, let us also convert this signature to HEX format. We will be using the xxd utility to perform this operation (please notice that on some Linux distributions this utility is a part of the vim-common package).xxd -p -u -c0 secure_data.binsig secure_data.hexsigThe signature in HEX format will be written to the secure_data.hexsig file. After that, the user is supposed to call the upload_document() stored procedure.CALL upload_document('alice', 'secure_data.doc', <file_content>, 'SHA256', UNHEX(<file_signature_hex>), @upload_status);Here,alice – name of the document owner.secure_data.doc – a name under which the document will be stored.<file_content> – the content of the local secure_data.doc passed as binary data.SHA256 – the name of the hash function used to calculate the file digest.<file_signature_hex> – the file signature in HEX format (the content of the secure_data.hexsig file). On the server upload_document() stored routine should do the following. First, it needs to find @user_ref, @key_type, and @public_key_pem in the user table for the provided owner’s login (alice).SELECT id, key_type, public_key_pem INTO user_id, user_key_type, user_public_key_pem FROM user WHERE login = user_login;Second, it needs to calculate message digest @digest using the provided hash function name (SHA256) for the provided file data (file_content).SET digest = create_digest(digest_type, file_content);Then, the server code needs to verify the file signature provided by the user (file_signature) with the public key associated with the file owner (alice).SET verification_result = asymmetric_verify( user_key_type, digest, file_signature, user_public_key_pem, digest_type);After that, only if verification_result is equal to 1, we confirm the identity of the document owner and insert a new record into the document table.INSERT INTO document VALUES(DEFAULT, @user_ref, 'secure_data.doc', file_content, 'SHA256', file_signature);Here is how upload_document() may look likeCREATE PROCEDURE upload_document( user_login VARCHAR(128), file_name VARCHAR(128), file_content BLOB, digest_type VARCHAR(16), file_signature VARBINARY(2048), OUT status INT) L_return: BEGIN DECLARE success INT DEFAULT 0; DECLARE error_login_not_found INT DEFAULT 1; DECLARE error_verification_failed INT DEFAULT 2; DECLARE user_id INT UNSIGNED DEFAULT 0; DECLARE user_key_type VARCHAR(16) DEFAULT NULL; DECLARE user_public_key_pem TEXT DEFAULT NULL; DECLARE verification_result INT DEFAULT 0; DECLARE digest VARBINARY(64) DEFAULT NULL; SELECT id, key_type, public_key_pem INTO user_id, user_key_type, user_public_key_pem FROM user WHERE login = user_login; IF user_id = 0 THEN SET status = error_login_not_found; LEAVE l_return; END IF; SET digest = create_digest(digest_type, file_content); SET verification_result = asymmetric_verify( user_key_type, digest, file_signature, user_public_key_pem, digest_type) IF verification_result = 0 THEN SET status = error_verification_failed; LEAVE l_return; END IF; INSERT INTO document VALUES( DEFAULT, user_id, file_name, file_content, digest_type, file_signature); SET status = success; END Downloading documents and verifying their integrity In order to download a file from our online document storage, the first step will be getting its content along with digital signature metadata.CALL download_document('alice', 'secure_data.doc', @downloaded_content, @downloaded_digest_type, @downloaded_signature, @download_status);Here,alice – name of the document owner.secure_data.doc – the name of the file we want to download.@downloaded_content – the content of the downloaded file will be put in this variable.@downloaded_digest_type – the name of the hash function used to calculate file digest for this file will be put in this variable.@downloaded_signature – the digital signature of the downloaded file will be put in this variable. On the server, the download_document() stored routine should do the following. First, it needs to find @user_ref, @key_type, and @public_key_pem in the user table for the provided owner’s login (alice).SELECT id, key_type, public_key_pem INTO user_id, user_key_type, user_public_key_pem FROM user WHERE login = user_login;Second, it needs to get the file content and digital signature metadata.SELECT id, content, digest_type, signature INTO file_id, file_content, file_digest_type, file_signature FROM document WHERE user_ref = user_id AND name = file_name;After that, we calculate the digest of the file_content using the file_digest_type hash function.SET digest = create_digest(file_digest_type, file_content);And finally, we verify the integrity of the document:SET verification_result = asymmetric_verify( user_key_type, digest, file_signature, user_public_key_pem, file_digest_type);Only if verification_result is equal to 1, do we confirm the integrity of the document and return successful status. Here is how download_document() may look likeCREATE PROCEDURE download_document( user_login VARCHAR(128), file_name VARCHAR(128) OUT file_content BLOB, OUT file_digest_type VARCHAR(16), OUT file_signature VARBINARY(2048), OUT status INT) L_return: BEGIN DECLARE success INT DEFAULT 0; DECLARE error_login_not_found INT DEFAULT 1; DECLARE error_file_not_found INT DEFAULT 2; DECLARE error_verification_failed INT DEFAULT 3; DECLARE user_id INT UNSIGNED DEFAULT 0; DECLARE user_key_type VARCHAR(16) DEFAULT NULL; DECLARE user_public_key_pem TEXT DEFAULT NULL; DECLARE verification_result INT DEFAULT 0; DECLARE file_id INT UNSIGNED DEFAULT 0; DECLARE digest VARBINARY(64) DEFAULT NULL; SELECT id, key_type, public_key_pem INTO user_id, user_key_type, user_public_key_pem FROM user WHERE login = user_login; IF user_id = 0 THEN SET status = error_login_not_found; LEAVE l_return; END IF; SELECT id, content, digest_type, signature INTO file_id, file_content, file_digest_type, file_signature FROM document WHERE user_ref = user_id AND name = file_name; IF file_id = 0 THEN SET status = error_file_not_found; LEAVE l_return; END IF; SET digest = create_digest(file_digest_type, file_content); SET verification_result = asymmetric_verify( user_key_type, digest, file_signature, user_public_key_pem, file_digest_type); IF verification_result = 0 THEN SET status = error_verification_failed; LEAVE l_return; END IF; SET status = success; ENDAlthough we included a digital signature verification code into the download_document() routine, it does not guarantee that the end-user (caller of the download_document() routine) will get the unmodified document. This code was added only as an additional step to detect integrity violations at earlier stages. The real digital signature verification must be performed on the client-side, not inside Percona Server. Basically, after calling download_document(), we need to save the content of the @downloaded_content output variable to a local file (say, downloaded.doc). In addition, the content of the @downloaded_signature in HEX format (HEX(@downloaded_signature)) must be saved into a local file as well (say, downloaded.hexsig). After that, we can convert the signature in HEX format into binary form.xxd -r -p -c0 downloaded.hexsig downloaded.binsigA digital signature in binary form will be written to the downloaded.binsig file. Now, all we have to do is verify the digital signature:openssl dgst -sha256 -verify public_key.pem -signature downloaded.binsig downloaded.docAnd only if we see the desired Verified OK status line, we can be sure that the document we just downloaded has not been modified. Conclusion To begin with, I would like to highlight that a real production-ready online document storage system is far more complicated than the one we just described. Probably because it does not use the OpenSSL command-line utility to perform cryptographic operations on the client-side. Moreover, it takes into consideration a number of other security aspects that are outside the scope of this blog post. Nevertheless, I still hope that the example with digital signatures shown here helped to convince you that asymmetric cryptography is not rocket science and with the help of Encryption UDFs component for Percona Server for MySQL can be indeed easy and straightforward. Check out the full documentation.
  4. Are some MySQL wait events actually CPU time ?

    Sure, wait event times in any database will include some CPU time, though ideally it should be minimal. Waits should be instrumented as close to the off CPU wait event as possible, i.e. if I’m doing an IO, then I want the wait to cover just the read call: start_wait('my_read_wait'); pread(... ); end_wait('my_read_wait'); So my function that sets up the read will do some CPU that could get caught under the cover of the wait itself and the call to pread will use some CPU but most of the time should be waiting for the actual I/O if the I/O is coming from storage. start_wait('my_lock'); semtimedop(...); end_wait('my_lock'); On MySQL I’ve noticed that average active sessions on CPU are often well below what the host reports as CPU utilization. For example if I have one connection on CPU a 100% of the time on a 2 vCPU host then CPU Utilization should be around 50%.  There might be some other CPU activity by other processes but if the database is the only thing on the host and I only have one connection active then this is a pretty good approximation. One connection on CPU 100% of the time is the same a 1 average active session  (AAS) on CPU or 1 AAS on CPU. On MySQL systems I’d often see AAS on CPU say at an average of 10% where as the CPU Utilization of the host is 50% or even 100%. Finally I ran a test today where I had 2 sessions on a 2 vCPU host running a join using the same table 3 times and no index and no predicate filter. That basically makes the SQL have to do N * N * N lookups where N is the number of rows in the the table. In this case the table fit into memory so this should be a pure CPU operation and yes the host OS reported 100% CPU.  but low and behold in my case it came out ZERO AAS on CPU and 2 AAS on the wait ‘wait/io/table/sql/handler’. On MySQL I am constantly seeing ‘wait/io/table/sql/handler’ in the top waits and happening when the SQL running should just be  reading buffers in memory. Here is an example: CREATE TABLE seed ( id INT AUTO_INCREMENT PRIMARY KEY, val INT); insert into seed(val) values (1); insert into seed(val) select val from seed; /* 2 */ insert into seed(val) select val from seed; /* 4 */ insert into seed(val) select val from seed; /* 8 */ insert into seed(val) select val from seed; /* 16 */ insert into seed(val) select val from seed; /* 32 */ insert into seed(val) select val from seed; /* 64 */ insert into seed(val) select val from seed; /* 128 */ insert into seed(val) select val from seed; /* 256 */ insert into seed(val) select val from seed; /* 512 */ insert into seed(val) select val from seed; /* 1024 */ insert into seed(val) select val from seed; /* 2048 */ insert into seed(val) select val from seed; /* 4096 */ insert into seed(val) select val from seed; /* 8192 */ insert into seed(val) select val from seed; /* 16384 */ create table seed1 as select * from seed; create table seed2 as select * from seed; Then run the following query by a couple of sessions. select count(*) from seed a, seed1 b, seed2 c where a.id=b.id and a.id=c.id;   Here is the Performance Insights dashboard in Amazon RDS. CPU Utilizaiton goes from about 5% to 100% and DB Load goes from 0 to 2 with almost all load on ‘wait/io/table/sql/handler’ Now if we track down the IO waits. we can see it’s wrapped around fetch row without actually seeing if the fetch is doing an I/O or reading from cache: https://github.com/mysql/mysql-server/blob/5.7/sql/handler.cc#L3047 MYSQL_TABLE_IO_WAIT(PSI_TABLE_FETCH_ROW, active_index, result, { result= index_read_map(buf, key, keypart_map, find_flag); }) if (!result && m_update_generated_read_fields) { result= update_generated_read_fields(buf, table, active_index); m_update_generated_read_fields= false; } DBUG_RETURN(result); } index_read_map: https://github.com/mysql/mysql-server/blob/5.7/sql/handler.h#L2819 virtual int index_read_map(uchar * buf, const uchar * key, key_part_map keypart_map, enum ha_rkey_function find_flag) { uint key_len= calculate_key_len(table, active_index, keypart_map); return index_read(buf, key, key_len, find_flag); } calculate_key_len: https://github.com/mysql/mysql-server/blob/5.7/sql/handler.cc#L3047 uint calculate_key_len(TABLE *table, uint key, key_part_map keypart_map) { /* works only with key prefixes */ assert(((keypart_map + 1) & keypart_map) == 0); KEY *key_info= table->key_info + key; KEY_PART_INFO *key_part= key_info->key_part; KEY_PART_INFO *end_key_part= key_part + actual_key_parts(key_info); uint length= 0; while (key_part < end_key_part && keypart_map) { length+= key_part->store_length; keypart_map >>= 1; key_part++; } return length; } IO Waits also are instrumented at IO waits MYSQL_TABLE_IO_WAIT(PSI_TABLE_FETCH_ROW, MAX_KEY, result, { result= rnd_pos(buf, pos); }) MYSQL_TABLE_IO_WAIT(PSI_TABLE_FETCH_ROW, active_index, result, { result= index_read_map(buf, key, keypart_map, find_flag); }) MYSQL_TABLE_IO_WAIT(PSI_TABLE_FETCH_ROW, index, result, { result= index_read_idx_map(buf, index, key, keypart_map, find_flag); }) MYSQL_TABLE_IO_WAIT(PSI_TABLE_FETCH_ROW, active_index, result, { result= index_next(buf); }) MYSQL_TABLE_IO_WAIT(PSI_TABLE_FETCH_ROW, active_index, result, { result= index_prev(buf); }) MYSQL_TABLE_IO_WAIT(PSI_TABLE_FETCH_ROW, active_index, result, { result= index_first(buf); }) MYSQL_TABLE_IO_WAIT(PSI_TABLE_FETCH_ROW, active_index, result, { result= index_last(buf); }) MYSQL_TABLE_IO_WAIT(PSI_TABLE_FETCH_ROW, active_index, result, { result= index_next_same(buf, key, keylen); }) MYSQL_TABLE_IO_WAIT(PSI_TABLE_WRITE_ROW, MAX_KEY, error, { error= write_row(buf); }) MYSQL_TABLE_IO_WAIT(PSI_TABLE_UPDATE_ROW, active_index, error, { error= update_row(old_data, new_data);}) MYSQL_TABLE_IO_WAIT(PSI_TABLE_DELETE_ROW, active_index, error, { error= delete_row(buf);}) MYSQL_TABLE_IO_WAIT https://github.com/mysql/mysql-server/blob/8d8c986e5716e38cb776b627a8eee9e92241b4ce/sql/handler.cc #define MYSQL_TABLE_IO_WAIT(OP, INDEX, RESULT, PAYLOAD) ... case PSI_BATCH_MODE_STARTING: { m_psi_locker = PSI_TABLE_CALL(start_table_io_wait) PSI_TABLE_CALL include/mysql/psi/mysql_table.h #define PSI_TABLE_CALL(M) psi_table_service->M psi_table_service https://github.com/mysql/mysql-server/blob/8d8c986e5716e38cb776b627a8eee9e92241b4ce/mysys/psi_noop.cc#L536 static struct PSI_table_locker *start_table_lock_wait_noop( struct PSI_table_locker_state *, struct PSI_table *, enum PSI_table_lock_operation, ulong, const char *, uint) { return nullptr; } ... static PSI_table_service_t psi_table_noop = { get_table_share_noop, release_table_share_noop, drop_table_share_noop, open_table_noop, unbind_table_noop, rebind_table_noop, close_table_noop, start_table_io_wait_noop, end_table_io_wait_noop, start_table_lock_wait_noop, end_table_lock_wait_noop, unlock_table_noop}; PSI_table_service_t *psi_table_service = &psi_table_noop; Reference https://fritshoogland.wordpress.com/2012/04/26/getting-to-know-oracle-wait-events-in-linux/
  5. MySQL SELECT and WHERE Clause Column Existence

    I’m sharing another tip for anyone who wants to learn more about MySQL and how to use it. This post covers an example of the differences in columns specified in the WHERE clause but not the SELECT clause and vice-versa. Image by Drizzt_Do_Urden from Pixabay  The Newsletter for PHP and MySQL Developers Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter. Columns named in the WHERE clause but not the SELECT clause Any column used as part of a WHERE clause conditional filter does not have to be listed in the SELECT column list. However, it should be present in the FROM clause named table. See the following example queries for clarity. SELECT category_id, last_updateFROM categoryWHERE name = 'Action'; You can see that the WHERE clause uses the conditional filter, name = ‘Action’, which is perfectly valid, although the SELECT statement list does not include the ‘name’ column. Get your brand, product, or service the attention it needs with affordable classified ad placement in the OpenLampTech newsletter. Thank you for your support! Non-existent Column in the WHERE Clause Yet, as shown in the next example query, you cannot specify a non-existent column name in the WHERE clause conditional filter or you will get an ‘Unknown column’ error: SELECT name, category_id, last_updateFROM categoryWHERE first_name = 'Action';Error Code: 1054. Unknown column 'first_name' in 'where clause' On the same token, a column listed in the SELECT list that does not exist in the named FROM clause table also returns an error: SELECT first_name, category_id, last_updateFROM categoryWHERE  name = 'Action';Error Code: 1054. Unknown column 'first_name' in 'field list' Stay tuned for more MySQL and PHP tips and content. Like what you have read? See anything incorrect? Please comment below and thank you for reading!!! A Call To Action! Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients. To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…) Be sure and visit the “Best Of” page for a collection of my best blog posts. Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. How can I help you? Free MySQL Query Syntax Mantra PDF cheat sheet. Remember the query syntax order with this mantra.Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Get your brand, product, or service the attention it needs with affordable classified ad placement in the OpenLampTech newsletter.Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.5 Truths I’ve Come To Realize As a Self-taught DeveloperDiscover premium MySQL learning material over in my Kofi Shop today!Disclosure: Some of the services and product links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission. The Newsletter for PHP and MySQL Developers Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter. Get your brand, product, or service the attention it needs with affordable classified ad placement in the OpenLampTech newsletter. Thank you for your support! The post MySQL SELECT and WHERE Clause Column Existence appeared first on Digital Owl's Prose.

Upcoming Events

Visitors

We have 4 guests and no members online