Today's Question:  What does your personal desk look like?        GIVE A SHOUT

 MYSQL


  How does MySQL handle DROP TABLE

A few days ago, when executing DROP TABLE in MySQL, all processes including DDL and DML were hung until DROP TABLE was completed. I am confused about this phenomenon.I have reviewed the source codes of MySQL to check how MySQL internally handle DROP TABLE.When user trigger DROP TABLE command:########################MySQL SERVER drop table########################/* Sql_table.cc  delete (drop) tables. */bool mysql_rm_table( )  /*   Execute the drop of a normal or temporary table */  int mysql_rm_table_part2 ()    /* Remove matching tables from the HANDLER&...

4,025 1       MYSQL DROP TABLE


  Work with MySQL character set and collation

For non-English websites, they often have to deal with character set and collation if they want to store data to and read data from databases with other languages. Character set tells the database which kind of character encoding scheme to use to store or read data, collation can be simply understood as a subset of character set, it tells the database how to sort data. We talk about working with character set and collation of MySQL today.  In MySQL, if we want to store Chinese, Japanese or other languages other than English, we may need to set the relative character set for the database, ...

11,163 0       MYSQL CHARACTER SET COLLATION CHINESE QUESTION MARK


  A serious security vulnerability found in MySQL/MariaDB

Recently a serious security vulnerability was found in MySQL/MariaDB. It relates to the access to the database. The issue is described below.When a user connects to MariaDB/MySQL, a token (SHA over a password and a random scramble string) is calculated and compared with the expected value. Because of incorrect casting, it might've happened that the token and the expected value were considered equal, even if the memcmp() returned a non-zero value. In this case MySQL/MariaDB would think that the password is correct, even while it is not.  Because the protocol uses random strings, the probab...

5,414 0       MYSQL BUG PASSWORD FIX MARIADB MEMCMP()


  Create Packed Indexes with MySQL

I am pretty much sure that you are aware of indexes which we are creating on database fields. Indexes are very useful to make your query search faster and improve the site performance. But the fact is that Indexes which we are creating on database tables also require some additional space on your hard disk and if you have not properly created these indexes than some times your index size become more than your actual data size.Now what if you can compress indexes which you have ...

5,787 0       MYSQL COMPRESSED INDEX PACK_KEYS


  Inline IF and CASE statements in MySQL

There are times where running IF statements inside a query can be useful. MySQL provides a simple way to do this through the use of IF and CASE statements.The IF statement takes three arguments; the conditional, the true value and the false value. False and true values may be static values or column values. For example:SELECT IF(score > 100, 100, score) AS scoreFROM exam_resultsthis will return the value in the score column limited to a maximum value of 100. IF statements can also be nested:SELECT IF(score > 100, 100, IF(score < 0, 0, score))FROM exam_resultsCASE statements (s...

8,721 0       SQL CONDITION CASE MYSQK IF


  Steps to connect to MySQL on Windows Command Line

To connect to MySQL database on Windows through Command line, there are some steps to be followed.1. You need to start the MySQL service, you can go to Start->Control Panel->System and Securities->Administrative Tools->Component Service->Services(local), then on the right panel, you can find one service name called MySQL and you should start this service2. Go the the MySQL installation folder, which is something like this : C:\Program Files\MySQL\MySQL Server 5.1. In this folder, find the bin folder.3. Open a command console and change the working directory to C:\Program Fi...

15,563 0       MYSQL COMMAND LINE CONNECTION WINDOW MYSQLD


  How Do I Enable Remote Access To MySQL Database Server?

By default remote access to MySQL database server is disabled for security reasons. However, some time you need to provide remote access to database server from home or a web server. If you want to remotely access to the database server from the web server or home, follow this quick tutorial.MySQL Remote AccessYou need type the following commands which will allow remote connections.Step # 1: Login Using SSH (if server is outside your data center)First, login over ssh to remote MySQL database server:ssh user@mysql.nixcraft.iStep # 2: Edit my.cnf FileOnce connected you need to edit the MySQL ser...

6,166 0       MYSQL REMOTE ACCESS ENABLE HOST OR WEBDOMAIN


  5 Ways to Boost MySQL Scalability

There are a lot of scalability challenges we see with clients over and over. The list could easily include 20, 50 or even 100 items, but we shortened it down to the biggest five issues we see.1. Tune those queriesBy far the biggest bang for your buck is query optimization. Queries can be functionally correct and meet business requirements without being stress tested for high traffic and high load. This is why we often see clients with growing pains, and scalability challenges as their site becomes more popular. This also makes sense. It wouldn't necessarily be a good use of time to tune a quer...

2,311 0       MYSQL METHODS PRACTICE SCALABILITY IMPLEMENTATION