MySQL

Rollback is an operation, which changes the current state of the transaction to the previous state. Undo logs are generally required if we want to roll back any of the uncommitted transactions and it plays a major role in Isolation. For any ... [More] changes made during a transaction, it must be stored priorly, because they are required if we choose to roll back the transaction. Entries are made in undo logs when data modifications are done. If a transaction modifies data with SQL commands, It will create discrete undo logs for each operation. Once a transaction is committed MySQL is free to purge the undo logs created in that transaction.  To know more about undo logs, you can check our previous blogs on overview to undo logs. Usually, the Rollback process will take more time than the original operation. Because It is a single-threaded process. Transaction flow diagramLet’s consider a table sbtest1 with 100 million records.  I am deleting the 30 Million records based on the id<=30000000 conditions. So in between, I forcefully terminated the delete operation. mysql> show processlist;+—-+—————–+———–+———+———+——+————————+—————————————-+| Id | User            | Host      | db      | Command | Time | State                  | Info                                   |+—-+—————–+———–+———+———+——+————————+—————————————-+|  5 | event_scheduler | localhost | NULL    | Daemon  | 6669 | Waiting on empty queue | NULL                                   || 16 | root            | localhost | sb_test | Query   |    0 | init                   | show processlist                       || 17 | root            | localhost | sb_test | Query   |  669 | updating               | delete from sbtest1 where id<=30000000 |+—-+—————–+———–+———+———+——+————————+—————————————-+3 rows in set (0.01 sec)mysql> kill 17;Query OK, 0 rows affected (0.26 sec)As we could see in the processlist, the killed transaction is currently in a rollback state. The rollback operation is performed with the help of undo logs. mysql> show processlist;+—-+—————–+———–+———+———+——+————————+—————————————-+| Id | User            | Host      | db      | Command | Time | State                  | Info                                   |+—-+—————–+———–+———+———+——+————————+—————————————-+|  5 | event_scheduler | localhost | NULL    | Daemon  | 7306 | Waiting on empty queue | NULL                                   || 17 | root            | localhost | sb_test | Killed  |  704 | query end              | delete from sbtest1 where id<=30000000 || 18 | root            | localhost | sb_test | Sleep   |  626 |                        | NULL                                   || 19 | root            | localhost | NULL    | Query   |    0 | init                   | show processlist                       |+—-+—————–+———–+———+———+——+————————+—————————————-+4 rows in set (0.00 sec)Now, To calculate the estimation of the rollback process, I have fetched the undo log entries and killed transaction of thread id from information_schema.innodb_trx table output with an interval of 60 secs. The minimum setting 5-sec interval would be helpful to calculate the accurate estimation time of rollback. mysql> pager grep -e “trx_mysql_thread_id: 17” -e trx_rows_modifiedPAGER set to ‘grep -e “trx_mysql_thread_id: 17” -e trx_rows_modified’mysql> select * from information_schema.innodb_trx\G select sleep(60); select * from information_schema.innodb_trx\G         trx_rows_modified: 0       trx_mysql_thread_id: 17         trx_rows_modified: 184602302 rows in set (0.26 sec)1 row in set (1 min 0.31 sec)       trx_mysql_thread_id: 17         trx_rows_modified: 171699271 row in set (0.09 sec)mysql> \nPAGER set to stdoutmysql> select SEC_TO_TIME(round((17169927*60)/(18460230–17169927))) as ‘Estimation Time of Rollback’;+—————————–+| Estimation Time of Rollback |+—————————–+| 00:13:18                     |+—————————–+1 row in set (0.18 sec)From the above-shared logs, we could see that It will take 13 mins 18 secs more to complete the rollback operation. To Make the above estimation process into more simple, I have created the RollbackTimeCalc function. DELIMITER $$CREATE FUNCTION RollbackTimeCalc(processID INT, timeInterval INT)RETURNS VARCHAR(225)DETERMINISTICBEGIN   DECLARE RollbackModifiedBeforeInterval INT;   DECLARE RollbackModifiedAfterInterval INT;  DECLARE RollbackPendingRows INT;   DECLARE Result varchar(20);            SELECT trx_rows_modified INTO RollbackModifiedBeforeInterval from information_schema.innodb_trx where trx_mysql_thread_id = processID and trx_state = ‘ROLLING BACK’;      do sleep(timeInterval);      SELECT trx_rows_modified INTO RollbackModifiedAfterInterval from information_schema.innodb_trx where trx_mysql_thread_id = processID and trx_state = ‘ROLLING BACK’;        set Result=SEC_TO_TIME(round((RollbackModifiedAfterInterval*timeInterval)/(RollbackModifiedBeforeInterval-RollbackModifiedAfterInterval)));        SELECT trx_rows_modified INTO RollbackPendingRows from information_schema.innodb_trx where trx_mysql_thread_id = processID and trx_state = ‘ROLLING BACK’;        RETURN(CONCAT(‘Estimation Time of Rollback : ‘, Result, ‘ Pending rows to rollback ‘, RollbackPendingRows));END$$DELIMITER ;We have to pass two parameters to the functions  Select RollbackTimeCalc(x,y); Where  x is the processlist id of the killed transaction, y is the time interval of undo log entries ​​mysql> select RollbackTimeCalc(18,5);+———————————————————————————————————-+| RollbackTimeCalc(18,5)                                                   |+———————————————————————————————————+| Estimation Time of Rollback: 00:06:09 Pending rows to rollback 10341861 |+———————————————————————————————————-+1 row in set (5.37 sec)With the above-created function, we could easily estimate the approximate time of rollback operations is 06 mins 09 sec.  Note: 1)Committed transactions cannot be rolled back.  2)Chunk the transactions into smaller ones. Heavy deletes and heavy updates can be handled via tools like pt-archiver and oak-chunk-update. 3)It is better to set autocommit=0 if we are performing major changes in the databases. and don’t forget to add autocommit=1 at the end of the transaction. [Less]

Hey hey! Glad you are here for this week’s issue of OpenLampTech, the newsletter for PHP/MySQL developers. You are going to love what’s in this publication. I’m sharing some of my personal Developer stories which have never been published online ... [More] anywhere else before. Enjoy! Do you need to learn MySQL? Just starting and confused about how to query a table and get the data you need? I am creating premium MySQL Beginners content for this specific reason. To help those who want to learn the basics of MySQL but don’t know where to start. Learn more about the premium blog posts as I develop and release them. Tell me more! Image by Gerd Altmann from Pixabay There is so much content in this issue of OpenLampTech, so you are in for a treat! In this issue we have articles covering: My in-depth interview for the Real Tough Candy website.WordPress plugin database tablesParsing data with SQLAJAX and PHP in WordPressA series of CodeIgniter articles to ignite your learning.A resourceful PHP-related tweetAnd tons of more goodies in the Extra Stuff section (my personal fav).Get your own subscription to the OpenLampTech newsletter publication and you will never have to wait on me to repost here again… The Newsletter for PHP and MySQL Developers Enjoy these past issues of the OpenLampTech newsletter from the archives: OpenLampTech – MySQL SHOW TABLESOpenLampTech issue #11 – MySQL LAG() Window functionOpenLampTech issue #3 – 5 MySQL String functions 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? Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.I enjoy reading Refind: The essence of the web, every morning in your inbox. Subscribe for free. Help me get a premium subscription by signing up yourself with my referral link.Grab a free pack of mobile Creator wallpapers.Just getting started or wanting to learn MySQL? Find out about my premium blog posts and MySQL Beginner Series here.Disclosure: Some of the services and products 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 The post OpenLampTech issue #12 – My RTC Interview appeared first on Digital Owl's Prose. [Less]

In this blog , let understand about key business benefit of using MySQL Database Service.before that let's understandWhat is MySQL database service (MDS)?MySQL Database Service is a fully managed Oracle Cloud Infrastructure native service, 100% ... [More] developed, managed, and supported by the MySQL team in Oracle.Oracle automates all tasks such as high availability, backup and recovery, database and operating system patching,version upgrades and so on. You are responsible solely for managing your data, schema designs, and access policies.MDS is only MySQL managed service with MySQL Enterprise Edition and using integrated massively-scalable query accelerator with Heatwave. More info:- https://www.oracle.com/mysql/Why is MySQL Database Service (MDS) ? Understanding Key Business benefit with MySQL Database Service(MDS) with Heatwave.List of Business benefit of using MySQL Database Service (MDS)  ¨    Extreme Performance with HeatWave HeatWave is a distributed, scalable, shared-nothing, in-memory, hybrid columnar, query processing engine designed for extreme performance for MySQL database service (MDS). It is enabled when you add a HeatWave cluster to a MySQL DB System.  A HeatWave cluster includes a MySQL DB System node and two or more HeatWave nodes. The MySQL DB System node has a HeatWave plugin that is responsible for cluster management, loading data into the HeatWave cluster, query scheduling, and returning query results to the MySQL DB System. HeatWave nodes store data in memory and process analytics queries.  More info:- https://dev.mysql.com/doc/heatwave/en/heatwave-introduction.html   Advantage of using HeatWave to accelerate the performance of MySQL Database   Without HeatWave   With HeatWave ETL(Extract Transform Load) Required No ETL(Extract Transform Load) Required   Application level changes required in syntax and semantics No Change in Application Multiple databases for OLAP and OLTP workloads Single MySQL Database Service for all applications (OLTP , OLAP) Can be prone to Data loss and inconsistency   No chance of data loss because data intact with MySQL database service(MDS). Lower Performance   Extreme Performance -          5400x faster than Amazon RDS -          1400x faster than Amazon Aurora -          6.5x Faster than Amazon Redshift Higher Cost Cheaper Cost -          2/3 cost of Amazon RDS -          ½ cost of Amazon Aurora -          ½ cost of Amazon Redshift Without HeatWaveWith HeatwavePerformance benchmark available:- https://www.mysql.com/why-mysql/benchmarks/¨   MDS HA(High Availability) MDS HA is single clicked high availability solution which ensure fault tolerant system with automatic failover and zero data loss.  Key business benefit of MDS HA include ·       Single Click High Availability ·       Automatic Failover ·       Increase Uptime ·       Zero Data Loss during failure event ·       Application will never experience down time due to MySQL node failures.  Key product benefit of MDS HA include ·       Built on Group Replication -          Provides distributed state machine replication with strong coordination between servers. -          Servers automatically coordinate using Paxos based protocol ·       Plugin that enables you to create elastic, highly-available, fault-tolerant replication topologies. ·       Proven MySQL technology (binary log & replication) ·       Developed and Supported by MySQL Team More info:- https://dev.mysql.com/doc/refman/8.0/en/group-replication.html  ¨   MDS Security Features and Compliance In Today’s digital world data is most valuable asset and database security has become top priority for the stakeholders. If you lose your data, it can have serious impact in the business, possibly we may loss the brand. That is why we needed to ensure that whatever in our databases, it should be fully protected. Stay secure with MDS, the latest MySQL security fixes from the MySQL team are automatically applied to limit your exposure to security vulnerabilities.  Also, MDS is the only public cloud service built on MySQL Enterprise Edition which comes with comprehensive enterprise security features like ·       Transparent Data Encryption(TDE) :- -          Data at rest encryption -          AES 256 encryption ·       MySQL Enterprise Encryption -          Data at rest encryption -          AES 256 encryption ·       MySQL Data Masking -          De-identify , Anoymize Sensitive data -          Reduce risk of data breach ·       MySQL Enterprise Firewall -          Block SQL injection attacks -          Intrusion detection ·       MySQL Enterprise Audit -          Tracking the use of database records -          Monitoring each operation on data   More info:- https://dev.mysql.com/doc/refman/8.0/en/mysql-enterprise-security.html  ¨   MDS Cost Less than AWS RDS , Aurora Amazon RDS is ~266% more expensive than MySQL Database Service for standard compute shapes. In fact OCI(compute, storage, networking) is also cheaper with better underlying hardware.   In case you wanted to know in advance how much MDS cost you , please enter specific details in the cost estimator tool here:- https://www.oracle.com/cloud/costestimator.html      ¨   MySQL Enterprise Support – 24x7 from MySQL team MySQL Technical Support Services provide direct access to our expert MySQL Support engineers who are ready to assist you in the development, deployment, and management of MySQL applications.  A vast majority of the problems the MySQL Support Engineers encounter, they have seen before. So an issue that could take several weeks for your staff to research and resolve, may be solved in a matter of hours by the MySQL Support team. •       Largest MySQL engineering and support organization •       Backed by the MySQL developers •       World-class support, in 29 languages •       Hot fixes & maintenance releases •       Consultative support More info:- https://www.mysql.com/support/    Conclusion Oracle MySQL Database Service is a fully managed database service that lets developers quickly develop and deploy secure, cloud native applications using the world’s most popular open source database. MySQL Database Service is the only MySQL cloud service with an integrated, high performance, in-memory query accelerator—HeatWave.  It enables customers to run sophisticated analytics directly against their operational MySQL databases-eliminating the need for complex, time-consuming, and expensive data movement and integration with a separate analytics database. HeatWave accelerates MySQL performance by orders of magnitude for analytics and mixed workloads. Optimized for Oracle Cloud Infrastructure (OCI), MySQL Database Service is 100% built, managed, and supported by the OCI and MySQL engineering teams. [Less]

In the first part of this short series, we went through the build of a full Clusterset architecture, which included 2 full clusters (3 members each) and an additional cluster with one member, which can serve different purposes. With the MySQL ... [More] ClusterSet, there are new capabilities with the router instances. These features enable valuable options for users of… Read More » [Less]

Percona XtraDB Cluster (PXC) offers a great deal of flexibility when it comes to the state transfer (SST) options (used when a new node is automatically provisioned with data). For many environments, on-the-fly compression capability gives great ... [More] benefits of saving network bandwidth during the process of sending sometimes terabytes of data. The usual choice for compression here is a built-in Percona XtraBackup compress option (using qpress internally), or options compressor/decompressor for the compression tool of choice. In the second case, the popular option is the gzip or its multi-threaded version pigz, which offers a better compression rate than qpress. In this writeup, I would like to mention another important compression alternative, which is gaining good popularity recently – zstd. I decided to do a simple test of various SST settings in terms of compression method and number of parallel threads. Note that my test is limited to basically one hardware scenario and a generic mix of TPCC and sysbench data.  The specs of my test box, which I tested with PXC 8.0.25: 2x Qemu-KVM VMs, each has 6GB RAM, 8 vCPUs (i7 11th gen), disk storage on a fast NVMe drive, and 1Gbps virtual network link. Therefore, my goal is only to give some hints and encourage to test various options, as the potential benefit may be quite significant in some environments. In order to set particular compression, I used the following configuration options, where x means a number of parallel threads.  No compression [sst] backup_threads=x qpress used internally by XtraBackup [sst] backup_threads=x [xtrabackup] compress parallel=x compress-threads=x qpress [sst] compressor='qpress -io -Tx 1' decompressor='qpress -dio' backup_threads=x [xtrabackup] parallel=x pigz [sst] compressor='pigz -px' decompressor='pigz -px -d' backup_threads=x [xtrabackup] parallel=x zstd [sst] compressor='zstd -1 -Tx' decompressor='zstd -d -Tx' backup_threads=x [xtrabackup] parallel=xOn each SST test, I measured the complete time of starting the new node, network data received bytes during the SST process by the donor, and data written to the joiner’s disk. Here are the results: SST time in seconds Threads No compression qpress built-in qpress gzip (pigz) zstd 1 102 156 130 976 118 2 92 123 112 474 92 4 85 106 109 258 95 8 86 99 109 182 97 Data received by the joiner during SST [MB] No compression qpress built-in qpress gzip (pigz) zstd 20762 6122 6138 4041 4148 Data written by the joiner to disk during SST [MB] 20683 26515 20683 20684 20683 And some graphical views for convenience: In this test case, the small gain of using multiple threads with no compression or with lightweight compression is due to the fact that the network link and disk IO became the bottleneck faster than the CPU. The test shows how bad regarding CPU utilization gzip is compared to other compression methods, as CPU was the main bottleneck even with 8 threads here. Quite excellent results came with zstd, which while offering the same good compression rate as gzip, completely outperforms it in terms of CPU utilization, and all of that with the lowest compression level of “1”! One thing that needs clarification is the difference between the two methods using qpress (quicklz) compression. When using the compress option for Percona XtraBackup, the tool first compresses each file and sends it with .qp suffix to the joiner. Then, the joiner has to decompress those files before it can prepare the backup. Therefore, it is always a more expensive one as requires more disk space during the process. Any real-life examples of introducing better compression methods are very welcome in the comments! I wonder if zstd turns out to be as effective in your real use cases. [Less]

If you want to set up a MySQL database to store data for websites, blogs, or applications, you can use a Docker container. It’s relatively simple to do. And like shuffling a deck of cards, reversing a string in Python, or writing a recursive ... [More] function, it’s a suitable exercise for beginners just starting out with MySQL and backend web development. Let’s take a look at exactly what a Docker container is, and how you can use it to set up MySQL. Image by VLADGRIN on Shutterstock What Is a Docker Container? A Docker container is a self-contained software development environment that includes all of the necessary components to run an application. This can include the application code, libraries, and other dependencies. Containers are isolated from one another and from the host operating system, so they can’t interfere with or access other containers or the host OS. This makes them ideal for packaging and deploying applications. Docker containers are created using a Docker image. This is simply a template for creating a container. You can create your own Docker images or use images that others have created. When you create a container from a Docker image, you can customize it by adding your own files, settings, and applications. In this tutorial, we will be looking at how to set up MySQL in a Docker container. Please note that commands might be different based on the OS installed. This example was made using an Arch Linux-based operating system and MySQL 8.0. Step 1. Install Docker If you haven’t already, install Docker. First, ensure that the OS install is up to date with the following command in a new terminal window. $ sudo pacman -Syu Once it has been verified that the system is up to date, install Docker. $ sudo pacman -S docker Step 2. Install MySQL in Docker Start the Docker service and run the command “Docker images” to check if MySQL is currently installed. [root@docker ~]# docker images The Newsletter for PHP and MySQL Developers Step 3. Start a MySQL Server Instance MySQL can be run as an instance. Use -d to run the container in detached mode and name the Docker container. MySQL uses the port 3600:3306 by default. Be sure to set the password for the database. For this example, it’s simply set to “password.” [root@docker ~]# docker container run -d --name mysql1 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password mysql:latest As mysql:latest is not available locally, this command will begin to download the latest version of MySQL available. Check with the command “Docker ps” that the MySQL container is running. [root@docker ~]# docker ps This command should show the container ID, and the ports the MySQL container is running on. Sample output: CONTAINER ID IMAGE NAMES COMMAND CREATED STATUS PORTS 6bcd1d829631 mysql:latest "docker-entrypoint..." 5 seconds ago Up 3 seconds 0.0.0:3306->3306/tcp, 33060/tcp Step 4. SSH into MySQL Container Now, we’ll need to SSH into the MySQL container with the “Docker exec” command, which is a command that runs new commands in a running container. We have verified in Step 2 that the container is running. After this, we will need to log into MySQL to create a new database. [root@docker ~]# docker exec -it mysqldb bash Log into the MySQL container using the password created when the container was made in Step 2, here our password is “password.” Also, ensure there is no space after -p since everything after -p is part of the inputted password. [root@6bcd1d829631]# mysql -u root -ppassword When issuing this command you’ll commonly encounter a warning that using a password on the command line is insecure. To get around this warning message, use the mysql_config_editor tools. Step 5. Make a Database Now that we are in MySQL, use the command “show databases” to list all of the available databases. mysql> show databases; Now create a database with the “create database” command and a name for the database. For this example, we’ll use the name “newdatabasename.” mysql> create database newdatabasename If we use the command “show databases” the new database should now appear in the list. See the below sample output. +------------------------------+ | Database | +------------------------------+ | information_schema | | mysql | | newdatabasename | | performance_schema | | sys | +------------------------------+ This database can easily be deleted with the command “drop database” followed by the name of the database. mysql> drop database newdatabasename; Step 6. Install MySQL Workbench MySQL Workbench is a graphical tool for administration purposes, and it’s available for most operating systems. Be sure to install the version that matches your OS. It can be downloaded from the MySQL website. Note that not all versions of Workbench and MySQL are compatible, so using the most recent versions will help remove compatibility issues. Conclusion Docker lets you run isolated containers to improve application performance while MySQL is extremely scalable and commonly used for database management. Together, they’re a great combo for web developers starting to work with backend web development. If you want to get started with MySQL and Docker, follow the six steps outlined in the post and visit Docker Hub for the latest MySQL images.  Also, subscribe to Digital Owl’s Prose to receive notifications of new blog posts by email when they are published. Feel free to submit suggestions for future articles! Author Bio Jody Dascalu Jody is a freelance writer in the technology niche. She studied in Canada and earned a Bachelor of Engineering. Jody has over five years of progressive supply chain work experience and is currently employed as a business analyst. As an avid reader, she loves to research upcoming technologies and is an expert on a variety of topics. How can I help you? Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.I enjoy reading Refind: The essence of the web, every morning in your inbox. Subscribe for free. Help me get a premium subscription by signing up yourself with my referral link.Grab a free pack of mobile Creator wallpapers.Just getting started or wanting to learn MySQL? Find out about my premium blog posts and MySQL Beginner Series here.The Newsletter for PHP and MySQL Developers Disclosure: Some of the services and products 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 post How to Set Up MySQL in a Docker Container appeared first on Digital Owl's Prose. [Less]

In SQL, the WHERE clause filters rows returned by the FROM clause table in SELECT queries using one or more search condition filters. Oftentimes, in application development, we accept user input values which in turn, are the WHERE clause conditional ... [More] filters against the table columns. In this post, I am covering the CodeIgniter 4 where() function and parameter binding for safer filtering in SELECT queries. Continue reading and see examples in MySQL… Get tailored articles with Refind delivered each day in your inbox. Refind is part of my daily reading habit. Make it part of yours by using my referral link. At no additional cost to you, I will be eligible for a premium subscription with more sign-ups from my link. “The essence of the web, every morning in your inbox. Subscribe for free“ Self-Promotion: If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like! The Newsletter for PHP and MySQL Developers Housekeeping and data used For the examples in this post, I am using a mock ’employees’ table having this data and structure: Current data in the employee’s table. Table structure for the employee’s table. I’m also using this Query Builder connection instance to the ’employees’ table for the query examples in this post: $db = \Config\Database::connect();$builder = $db->table('employees'); I’ll share the exact queries that are sent to the server via logging information from the $db->getLastQuery() method for a better understanding of each of the query examples. If you haven’t used this method yet, I highly recommend using it to understand the executed queries. I wrote all about it in the post, CodeIgniter 4 Query Helper $db->getLastQuery() method for SQL prototyping. CodeIgniter 4 Query Builder where(): Key values There are several different ways we can provide parameters to the CodeIgniter 4 where() function. One of the most straightforward is a simple key/value pair. Simply specify a target table column as the first parameter and a corresponding value to filter against that column as the second parameter: $id = 3;$employee = $builder->select('first_name') ->where('id', $id) ->get()>getResult(); The above builder pattern returns this SELECT query as logged (not shown) by $db->getLastQuery(): Select query using where() function and key/value pair. Notice that this particular CodeIgniter 4 where() function key/value structure results in an equality comparison using the = comparison operator. This is the default unless you use a different comparison operator in the key parameter. CodeIgniter 4 Query Builder where(): Custom key values This next alternative where() function parameter syntax provides a great deal of flexibility for the key/value structure, allowing the actual comparison operator to be part of the key itself in the first parameter. $id = 3;$employee = $builder->select('first_name, last_name') ->where('id <', $id) ->get()->getResult(); We can see the key, ‘id <‘ uses the less-than comparison operator, resulting in a SELECT query filtering for rows with an ‘id’ column value of less than 3 as shown in this executed MySQL query code: Select query from where() function using custom key/value parameter structure. Chaining one or more additional where() function calls forms an AND logical operator type of query: $id = 3;$first_name = 'Jimmy';$employee = $builder->select('first_name, last_name') ->where('id <', $id) ->where('first_name <>', $first_name) ->get()->getResult(); (Note: Additional where() function chaining is valid in all forms of the parameter structure and is not limited to only custom key/value filtering.) Get your WordPress website or web application up and running with a hosting plan over on Hostinger. I use Hostinger hosting for all of my personal web development applications and projects. The pricing tiers are some of the best in the industry and the service is unparallel to any other. Get your hosting now using my referral link. At absolutely no extra cost to you, I will receive a commission should you purchase one of Hostinger’s hosting plans through this link. Thank you! CodeIgniter 4 Query Builder where(): Associative array The CodeIgniter 4 where() function parameter structure can use an associative array for the filtering conditional. Create an associative array with key/value pairings and pass that array as a parameter in the where() function call. Again, the key is a column in the table we are filtering on: $data_array = array('id' => $id);$employee = $builder->select('first_name, last_name') ->where($data_array) ->get()->getResult(); The associative array can have multiple key/value pairs, including the comparison operator itself as part of the key. Notice in this next example, the <> (not equals to) comparison operator is part of the ‘first_name’ key in the 2nd element of the $data_array associative array: $id = 3;$first_name = 'Jimmy';$data_array = array('id' => $id, 'first_name <>' => $first_name);$employee = $builder->select('first_name, last_name') ->where($data_array) ->get()->getResult(); Support my blog and content with a donation to my Tip Jar. Thank you so much! Tip Jar CodeIgniter 4 Query Builder where(): Custom string The last parameter structure we will visit is creating your own custom string for the CodeIgniter 4 where() function. As noted in the where() function documentation, you must manually escape any user-provided input when creating custom query strings for the where() function. In the where() function examples up to this point, the input parameters have been automatically escaped. However, when you write custom query strings as the where() function parameter, the input is not automatically escaped. Here is an example using the $db->escape() method to escape the $first_name variable value of ‘John’: $first_name = $db->escape('John');$where_clause = "`first_name` = {$first_name}";$employee = $builder->select('first_name, last_name') ->where($where_clause) ->get()->getResult(); Custom query string for the where clause. With several options to build up the WHERE clause conditionals for your queries using the where() function, CodeIgniter provides a flexible means to retrieve the exact data you need. Similar content If you enjoyed the content in this post, you may also like any one of these related articles: How To Retrieve MySQL Last Insert ID in CodeIgniter 4MySQL Aggregate Query using CodeIgniter’s Query BuilderEnable File Attachment with CodeIgniter 4 form helperAre you a Medium member? If so, receive an email notification each time I publish a blog post if you prefer the Medium platform. Not a member? No worries! Use my sign-up link (I will get a commission at no extra cost to you) and join. I really enjoy reading all the great content there and I know you will too!!! As always, if you have any questions or see any mistakes in the code, please let me know via the comments. Constructive comments help me provide accurate blog posts and are much appreciated. Thank you for reading. 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? Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.I enjoy reading Refind: The essence of the web, every morning in your inbox. Subscribe for free. Help me get a premium subscription by signing up yourself with my referral link.Grab a free pack of mobile Creator wallpapers.Just getting started or wanting to learn MySQL? Find out about my premium blog posts and MySQL Beginner Series here.The Newsletter for PHP and MySQL Developers Disclosure: Some of the services and products 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 post CodeIgniter 4 Query Builder where() function and parameter structure with MySQL appeared first on Digital Owl's Prose. [Less]

This post is the eleven one of a series of articles on extending MySQL with the Component Infrastructure, the list above will be updated as new articles are published: Extending MySQL using the Component Infrastructure – part 1Extending MySQL using ... [More] the Component Infrastructure – part 2: building the serverExtending MySQL using the Component Infrastructure – part 3: component servicesExtending MySQL using the Component Infrastructure – part 4: error loggingExtending MySQL using the Component Infrastructure – part 5: privilegesExtending MySQL using the Component Infrastructure – part 6: functionsExtending MySQL using the Component Infrastructure – part 7: messages to usersExtending MySQL using the Component Infrastructure – part 8: linking a third party libraryExtending MySQL using the Component Infrastructure – part 9: adding a new functionExtending MySQL using the Component Infrastructure – part 10: status variablesExtending MySQL using the Component Infrastructure – part 11: performance_schema tableWhat a journey through this incredible MySQL Component Infrastructure to create our extension to MySQL 8.0. This article deals with another important piece and perhaps the most complex piece for us to implement: a new table in Performance_Schema. This time I won’t include the full code in the post itself, just a few parts as the code is getting large. The full source is available on GitHub: https://github.com/lefred/mysql-component-viruscan. Of course, the Component Infrastructure provides the service we need to create that pfs table: psf_plugin_table. As usual, we need to add the include file and the required services like this in scan.h: This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters Show hidden characters #include extern REQUIRES_SERVICE_PLACEHOLDER(pfs_plugin_table); extern REQUIRES_SERVICE_PLACEHOLDER_AS(pfs_plugin_column_integer_v1, pfs_integer); extern REQUIRES_SERVICE_PLACEHOLDER_AS(pfs_plugin_column_string_v1, pfs_string); extern REQUIRES_SERVICE_PLACEHOLDER_AS(pfs_plugin_column_timestamp_v2, pfs_timestamp); view raw 01.h hosted with ❤ by GitHub You may have noticed that we also have to possibility to rename a service using REQUIRES_SERVICE_PLACEHOLDER_AS(), which I did for the services handling the datatypes used in our table. I also decided to place the majority of the code dedicated of the new Performance_Schema table in its own file (scan_pfs.cc), so it’s easier to read the code and also to illustrate how to combine multiple source files together during compilation: we just need to add the file in our CMakeLists.txt we already created: View this gist on GitHub The definition of our table is the following: CREATE TABLE `viruscan_matches` ( `LOGGED` timestamp NULL DEFAULT NULL, `VIRUS` varchar(100) DEFAULT NULL, `USER` varchar(32) DEFAULT NULL, `HOST` varchar(255) DEFAULT NULL, `CLAMVERSION` varchar(10) DEFAULT NULL, `SIGNATURES` int DEFAULT NULL ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci As soon as we create all our functions (in scan_pfs.cc) to handle the population of the table, the delete and the parsing, we modify our user function used to scan the data (viruscan_udf in scan.cc) to retrieve some security context information (the user and the host) and if a virus is found, we add an entry in the array buffer used to generate the pfs table (line 282 in scan.cc). For the example, I defined the maximum size of my table to 10 records (that might be too short, I would suggest to increase it), in scan.h on line 69. The array buffer is used as a circular buffer. When there is new entry, if we reached the maximum amount of allowed records in our table, the first record is replaced. See line 70 in scan_pfs.c. Let’s see how it looks like: Now when we keep scanning data with a virus, we can see that as we reached the maximum records in our table, the first record gets replaced: It’s then advised to always order our records by the timestamp if we want a sorted overview. This is another example after we updated the signatures (freshclam): As you can see in scan_pfs.cc, when we read a column from a Performance_Schema table, we need to handle correctly the type of data stored in the column. Not all MySQL traditional datatypes are available and we need to use a component service to display the right value. This is where we use the short renamed services like pfs_string (see line 183). Our table is a read only table. So we only need functions to open/close the table, read records and handle the position. See lines 222 to 231. Conclusion I would like to thank Joro again for checking and helping me with the component services and also a special thanks to Marc Alff (Mister Performance Schema) for introducing me to the best practices and for cleaning up the code. Happy coding and as usual, enjoy MySQL ! [Less]

Wow! Eleven issues so far! Are you ready for your weekly source of original and curated PHP/MySQL content? Ready to learn and grow as a backend developer? Well, you are in luck because OpenLampTech, the newsletter for PHP/MySQL developers, is out and ... [More] ready for you. Dig in!!! Do you need to learn MySQL? Just starting and confused about how to query a table and get the data you need? I am creating premium MySQL Beginners content for this specific reason. To help those who want to learn the basics of MySQL but don’t know where to start. Learn more about the premium blog posts as I develop and release them. Tell me more! Image by janjf93 from Pixabay  Get tailored articles with Refind delivered each day in your inbox. Refind is part of my daily reading habit. Make it part of yours by using my referral link. At no additional cost to you, I will be eligible for a premium subscription with more sign-ups from my link. “The essence of the web, every morning in your inbox. Subscribe for free“ This week’s issue is filled with fantastic content. We are looking at: jQuery in WordPressCodeIgniter 4 design patternsMySQL LAG() window functionWordPress form builder pluginsFull site editing in WordPressand much more…Don’t wait on me to repost the OpenLampTech newsletter here. Get it hot off the press when it is published. Join the community through the form below: The Newsletter for PHP and MySQL Developers Support my content with any spare change you have by tossing it in my Tip Jar. Thank you! Tip Jar Previous OpenLampTech deep-dive featured pieces Visit any one of the monthly deep-dive issues below for more great reading OpenLamp Tech issue #3 – 5 MySQL String Functions You Should KnowOpenLamp.tech Issue #7 – MySQL Metadata with CodeIgniter 4How can I help you? Are you thinking of starting up a blog? I use WordPress for my blog. Let’s both save money on the plans offered. Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.I enjoy reading Refind: The essence of the web, every morning in your inbox. Subscribe for free. Help me get a premium subscription by signing up yourself with my referral link.Grab a free pack of mobile Creator wallpapers.Just getting started or wanting to learn MySQL? Find out about my premium blog posts and MySQL Beginner Series here.Disclosure: Some of the services and products 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 The post OpenLampTech issue #11 – MySQL LAG() appeared first on Digital Owl's Prose. [Less]

Recently we got a question about how one can perform a minor version upgrade of a Galera Cluster, and today we just want to say that it is reasonably straightforward. While upgrades are a feature of Galera Manager that we are most excited to see come ... [More] soon, today, doing it manually on the command line is quite simple! In this post, we will go through upgrading a 9-node geographically spread cluster, from MySQL 8.0.23 to 8.0.26. We also have extensive documentation: Upgrading Galera Cluster. We have 3 nodes each in Singapore, London and San Francisco, making the cluster comprise of a total of 9 nodes, as we can confirm by executing: show status like 'wsrep_cluster_size';. Since we installed all this via the RPM package manager on CentOS 7 by adding the YUM repository to the configuration, it was really just a case of executing: yum -y update. Of course, we planned this update for the 3 nodes in San Francisco first, node by node (i.e. a rolling upgrade), as to not take down multiple nodes at the same time. Then we repeated the process in London, and finally we did so in Singapore. The order does not matter, as long as the upgrades happen with a plan. This was an upgrade that did not stop transactions during the time either – the database continued serving. What yum update does is that it does install MySQL and the Galera wsrep provider for us, and as we weren’t planning to make any changes to the configuration files, we just let the process run automatically. There are hooks in the RPM package to also ensure that mysql_upgrade is run automatically. Naturally, during the upgrade, we would see that the cluster size would reduce from 9 nodes to 8 nodes temporarily, but this is expected. Configuration of the gcache was large enough to ensure that during the upgrade process, there would still be an IST. Some select text below to verify that all nodes are updated: mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.23 | +-----------+ 1 row in set (0.00 sec) mysql> show status like 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 9 | +--------------------+-------+ 1 row in set (0.00 sec) Updating : mysql-wsrep-server-8.0.26- [###################### ] 67/258 Cleanup : mysql-wsrep-common-8.0.23-26.6.el7.x86_64 240/258 mysql> show status like 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 8 | +--------------------+-------+ 1 row in set (0.01 sec) mysql> select * from mysql.wsrep_cluster_members; +--------------------------------------+--------------------------------------+--------------+-----------------------+ | node_uuid | cluster_uuid | node_name | node_incoming_address | +--------------------------------------+--------------------------------------+--------------+-----------------------+ | 10ff4e95-dfd8-11eb-9cd8-7ed190657de9 | d04a1319-d89d-11eb-86c2-3fe756007e90 | galerasf-03 | AUTO | | 113a4bb9-8010-11ec-bbe1-c38d9738c824 | d04a1319-d89d-11eb-86c2-3fe756007e90 | galerasf-02 | AUTO | | 4d13b29e-dfcd-11eb-8108-133e852022df | d04a1319-d89d-11eb-86c2-3fe756007e90 | galera-03 | AUTO | | 4ec352d5-e00a-11eb-93c3-4746e95e5d89 | d04a1319-d89d-11eb-86c2-3fe756007e90 | galerasf | AUTO | | 6644b74f-dfcd-11eb-82cf-ca20c5363b91 | d04a1319-d89d-11eb-86c2-3fe756007e90 | galera-02 | AUTO | | 7a7edaa1-dfcd-11eb-b5e1-23b77330d3d5 | d04a1319-d89d-11eb-86c2-3fe756007e90 | galera | AUTO | | a5cc9925-dfd7-11eb-9831-b76bc19aa3a4 | d04a1319-d89d-11eb-86c2-3fe756007e90 | galeralon-02 | AUTO | | c0078cbd-dfd7-11eb-8877-7e671c4d3a89 | d04a1319-d89d-11eb-86c2-3fe756007e90 | galeralon-03 | AUTO | | e7fe42fd-dfce-11eb-9675-36d358e01b8d | d04a1319-d89d-11eb-86c2-3fe756007e90 | galeralon | AUTO | +--------------------------------------+--------------------------------------+--------------+-----------------------+ 9 rows in set (0.00 sec) There you have it, upgrading a minor version is truly quite simple, as long as you’re using package management. There needs to be no downtime during such an upgrade. [Less]