The latest from the MySQL community

Today i am going to share with you how to create CRUD Application with pagination using JQuery Ajax in Codeigniter 3CRUD is a basic step of any Core Language or framework CRUD stand for Create Read Update and Delete So in this post we will learn insert update delete in codeigniter using jquery

Today i am going to share with you how to create CRUD Application with pagination using JQuery Ajax in Codeigniter 3CRUD is a basic step of any Core Language or framework CRUD stand for Create Read Update and Delete So in this post we will learn insert update delete in codeigniter using jquery

Whenever we want a query’s results sorted, you may think of using the clause “order by.” You are correct: most of the time, MySQL will return the results in expected order with “order by.”

Be careful, though. Sometimes MySQL may return results in the non-deterministic order even with “order by.”

Indeed, if a query is ordered by a non-unique column, it may return results in an unexpected order. I tested the below example on MySQL 5.1.73, 5.5.54 and 5.6.19 and got the same result. However, when I applied the same example on MySQL 5.7.17, it returned the results in an unexpected order differently.

Follow me step-by-step and see how MySQL returns results in a non-deterministic order. Step 1-4 is for MySQL 5.1.73, 5.5.54 and 5.6.19, Step 5 is for MySQL 5.7.17. After the example, I will explain the reason behind this output.

Step 1. Create the table as …

Updated 2/10/2017

If you've been watching the evolution of database technologies over the past few years, you've seen how quickly JSON has quickly cemented its position in major database servers. Due to its use in the web front-end, JSON has overtaken XML in APIs, and it’s spread through all the layers in the stack one step at a time.

Most major databases supported XML in some fashion for a while, too, but developer uptake wasn’t universal. JSON adoption among developers is nearly universal today, however. (The king is dead, long live the king!) But how good is JSON support in the databases we know and love? We’ll do a comparison in this blog post.

…

MySQL Extensions and Deviations

This section I am going to cover material relating to the extensions and deviations of MySQL, an administrator should have the basic knowledge of the following even if he is not going to be developing

• How MySQL extends the SQL language
• Data Types
• Indexes
• Stored routines, triggers and events
• Views
• Transactions

MySQL does not completely follow the SQL standard (ANSI/ISO SQL:2003 standard) and has it's own extensions and deviations. The reason for the extensions is to make the system easy to use.

We start with some of the rules of the SQL language which relate to the following

• Comments and portability
• Case-sensitivity
• Escape characters
• Naming limitations
• Quoting
• Time zones
• Character sets …

MySQL Caching

To make queries faster to respond is to use caching, a cache stores frequently used data in a place that is local where is can be accessed faster. Cached data can be stored either on a computers local hard disk or in the computers internal memory. For example a web browser stores text, images and other objects of recently viewed web pages locally on your hard drive, thus the next time you visit the web and the information is still the same, the browser will retrieve the text, image, etc from the local hard disk instead of having to go out on the internet to get them which is slower. This means that the first time you retrieve data it will always be slower than when you retrieve it the second time as the data will be cached.

• getting the text, picture, etc from the internet is the slowest way to get that information and takes the longest for the web page to load
• getting the text, …

What is binlog?

In MySQL the binary log record the events that, when ever the changes happens in the databases. (eg.create,delete,update,insert)

   What is binlog formats?

The format that has been used to record the changes of data in binlog.

What are the their types?

There are three types of binlog formats.

1) Statement

2) Row

3) Mixed

1) Statement 

It is the default binlog format for MySQL5.6. It records the events in SQL statement in binlog to read easily with mysqlbinlog. The binlog does not grow so fast than row format.
Faster to recover from a backup.  how it works?
· set the binlog format to statement if you use older version of MySQL 5.6 in my.cnf file.

 · restart MySQL   · create a database example india. …

MySQL STORAGE ENGINES

What is storage engine?

       In MySQL the datas are stored as files in any one of the types in storage engines. MySQL supports pluggable storage engines that we can use any types of engine belongs to your data.There are two types of storage engines in MySQL  Transactional(The data can be modified in engines) and non-transactional(It can only fetch the data from engines). The default storage engine for MySQL prior to version 5.5 was MyISAM. For MySQL 5.5 and later, the default storage engine is InnoDB.

Types of Storage Engine

1.MyISAM

2.InnoDB

3.Merge

4.Memory

5.Blackhole

6.Archive

7.CSV

8.Federated

1.MyISAM

       MyISAM is the oldest storage …

Locking User Accounts in MySQL

For SOX Compliance when an employee, contractor, or vendor leaves your company their account needs to be locked, expired, or removed.  Some of these people may return, and their Account may have many complicated permissions.  In these cases, it would be better to “Lock” their Account while they are no longer doing business with your company. 
However, MySQL does not have the ability to “Lock” or “Expire” a User Account.   The following is a simple procedure to “Lock” and “Unlock” a MySQL Account.

Typically, the one-way encrypted hash value of a users password is stored in a password field  in an user account table.  MySQL supports three builtin one-way hashed functions, PASSWORD(), MD5() and SHA1().  It is recommended not to directly use the PASSWORD() function. Reason, is that prior to PHP 4.1 version, the …

MySQL – When an Optimize Fails
When using the MyISAM engine, you may need to run an “optimize” to regain space and increase performance.  What do you need to know BEFORE you start the “optimize” command.

The first thing to know is the “optimize” creates a Temporary file in the database datadir, (see mr.TMD).  The problem is there is only 16GB of free space with the mr.MYD and mr.MYI are almost 40GB.  There is one more thing you need to know.  MySQL uses tmpdir at the end of the “optimize” which can be a problem if there is not enough space.

SERVER1:/db/mysql/data/stats>ls -lt kh*                                                                   -rw-rw---- 1 pm pmapp 18163175424 Mar 23 …