Bypasses read_only
Bypasses init_connect
Can Disable binary logging
Change configuration dynamically
No reserved connection

User Permissions
This is how a user should be created, granting only the required permissions to a given schema.

CREATE USER goodguy@localhost IDENTIFIED BY ’sakila’;
GRANT CREATE,SELECT,INSERT,UPDATE,DELETE ON odtug.* TO goodguy@localhost;

This is what is commonly seen.

CREATE USER superman@’%';
GRANT ALL ON *.* TO superman@’%';

Bypasses read_only
Many MySQL replication environments rely on ensuring the MySQL slave is consistent with the master. Did you know that an application can bypass this security when read_only=true is used?

$ mysql -ugoodguy -psakila odtug
mysql> insert into test1(id) values(1);
ERROR 1290 (HY000): The MySQL server is running with the –read-only option so it cannot execute this statement

$ mysql -usuperman odtug
mysql> insert into test1(id) values(1);
Query OK, 1 row affected (0.01 sec)

GRANT ALL is bad for data consistency.
Bybasses init_connect
A common practices used for UTF8 communications is to use the init_connect configuration variable.

#my.cnf
[client]
init_connect=SET NAMES utf8

$ mysql -ugoodguy -psakila odtug

mysql> SHOW SESSION VARIABLES LIKE ‘ch%’;
+————————–+———-+
| Variable_name | Value |
+————————–+———-+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
+————————–+———-+

$ mysql -usuperman odtug

mysql> SHOW SESSION VARIABLES LIKE ‘character%’;
+————————–+———-+
| Variable_name | Value |
+————————–+———-+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
+————————–+———-+

GRANT ALL is bad for data integrity.
Disables Binary Logging.

$ mysql -usuperman odtug

mysql> SHOW MASTER STATUS;
+——————-+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————-+———-+————–+——————+
| binary-log.000001 | 354 | | |
+——————-+———-+————–+——————+

mysql> DROP TABLE time_zone_leap_second;
mysql> SET SQL_LOG_BIN=0;
mysql> DROP TABLE time_zone_name;
mysql> SET SQL_LOG_BIN=1;
mysql> DROP TABLE time_zone_transition;
mysql> SHOW MASTER STATUS;
+——————-+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————-+———-+————–+——————+
| binary-log.000001 | 674 | | |
+——————-+———-+————–+——————+

$ mysqlbinlog binary-log.000001 –start-position=354 –stop-position=674

# at 354
#100604 18:00:08 server id 1 end_log_pos 450 Query thread_id=1 exec_time=0 error_code=0
use mysql/*!*/;
SET TIMESTAMP=1275688808/*!*/;
DROP TABLE time_zone_leap_second
/*!*/;
# at 579
#100604 18:04:31 server id 1 end_log_pos 674 Query thread_id=2 exec_time=0 error_code=0
use mysql/*!*/;
SET TIMESTAMP=1275689071/*!*/;
DROP TABLE time_zone_transition
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;

Should that statement be run on MySQL Slaves?
Is the binary log used for any level of auditing?
GRANT ALL is bad for slave consistency.
The reserved connection
MySQL reserved one connection for an administrator to be able to login to a server. For example.

$ mysql -uroot

mysql> show global variables like ‘max_connections’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 3 |
+—————–+——-+
1 row in set (0.07 sec)

mysql> show global status like ‘threads_connected’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_connected | 4 |
+——————-+——-+

mysql> SHOW PROCESSLIST;
+—-+——+———–+——-+———+——+————+—————
| Id | User | Host | db | Command | Time | State | Info
+—-+——+———–+——-+———+——+————+—————
| 13 | good | localhost | odtug | Query | 144 | User sleep | UPDATE test1 …
| 14 | good | localhost | odtug | Query | 116 | Locked | select * from test1
| 15 | good | localhost | odtug | Query | 89 | Locked | select * from test1
| 15 | root | localhost | odtug | Query | 89 | Locked | SHOW PROCESSLIST

However if all application users are already using the SUPER privilege, the administrator will get.

$ mysql -uroot
ERROR 1040 (HY000): Too many connections

Yesterday, the Zend Framework team tagged the first development milestone of
Zend Framework 2.0 (2.0.0dev1). It is immediately downloadable from the Zend
Framework servers:

ZendFramework 2.0.0dev1 zip package
ZendFramework 2.0.0dev1 tar.gz package

NOTE! This release is not considered of production quality, and is
released solely to provide a development snapshot for purposes of testing
and research. Use at your own risk.

I want to share a problem that we had in our project. We were doing a real-time “Profits and Loss” server (P&L). The server sends stock updates to all the users subscribed to the stocks, basically as Google Finance or Yahoo Finance.

SIMPLE IMPLEMENTATION

I will used a basic approach (no aggregation and no optimization) to explain the problem that we had with Serialization and Hessian.

The server always keep the last value on the stocks, because when a user ask for a quote we want to send back as soon as possible the
last value (from the cache) that we had on that stock).

We kept the stock prices (BID and ASK) in a simple object : SymbolSerializable.

When a update is received for a stock, we update the values in his SymbolSerializable and send back the updated values to all
client subscribed to this stock.

...

symbol.setBid(update.getBid());
symbol.setAsk(update.getAsk());

...

for(Client client : clientList){
    client.sendUpdate(symbol);
}

....

It can’t be more simpler than that. The client will received each updates on his stocks.

The problems that we had was surprising. The clients were receiving the always the same price on a stock !

WHY ?

here an example :

stock : ABC

Updates :

1 : bid=10.25$, ask=10.50$
2 : bid=11.50$, ask=11.75$
3 : bid=12.00$, ask=12.15$

and the clients received :

1 : bid=10.25$, ask=10.50$
2 : bid=10.25$, ask=10.50$
3 : bid=10.25$, ask=10.50$

To debug our server we printed the values sent to the client, and we saw in the server’s logs that the values were corrects.

value sent to client :
bid=10.25$, ask=10.50$

value sent to client :
bid=11.50$, ask=11.75$

value sent to client :
bid=12.00$, ask=12.15$

Everything look fine, why it doesn’t work on the client side ?

INVESTIGATION/SOLUTION

The only thing that could cause the problem were our Serialization in the server.

I’ll show you four different test cases. See the implementations below.

• #1 : Reference implementation using Serializable (failed)
• #2 : Changed Serializable for Externalizable interface (failed)
• #3 : Serializable using new (passed)
• #4 : Serializable using reset (passed)

Here the Serializable pojo

package ca.sebastiendionne.demo.model;

import java.io.Serializable;

public class SymbolSerializable implements Serializable {

    private static final long serialVersionUID = 7853892880704628717L;

    Double bid = null;
    Double ask = null;

    public SymbolSerializable(){
    }

    public Double getBid() {
        return bid;
    }
    public void setBid(Double bid) {
        this.bid = bid;
    }
    public Double getAsk() {
        return ask;
    }
    public void setAsk(Double ask) {
        this.ask = ask;
    }

    @Override
    public String toString() {
        return "bid = " + bid + "  ask=" + ask;
    }

}

Here the Externalizable pojo

package ca.sebastiendionne.demo.model;

import java.io.Externalizable;
import java.io.IOException;
import java.io.ObjectInput;
import java.io.ObjectOutput;

public class SymbolExternalizable implements Externalizable {
    private static final long serialVersionUID = 1853892880704628717L;

    Double bid = null;
    Double ask = null;

    public SymbolExternalizable(){

    }

    public Double getBid() {
        return bid;
    }
    public void setBid(Double bid) {
        this.bid = bid;
    }
    public Double getAsk() {
        return ask;
    }
    public void setAsk(Double ask) {
        this.ask = ask;
    }

    @Override
    public String toString() {
        return "bid = " + bid + "  ask=" + ask;
    }

    @Override
    public void readExternal(ObjectInput in) throws IOException, ClassNotFoundException {
        bid = (Double)in.readObject();
        ask = (Double)in.readObject();
    }

    @Override
    public void writeExternal(ObjectOutput out) throws IOException {
        out.writeObject(bid);
        out.writeObject(ask);
    }

}

For the test cases I use a for loop to create Symbol and serialized on the hard drive and unserialized them back.

Here the code for the test case #1 (used as reference)

package ca.sebastiendionne.demo;

import java.io.EOFException;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;

import ca.sebastiendionne.demo.model.SymbolSerializable;

public class SerializationFailed {

    /**
     * @param args
     */
    public static void main(String[] args) throws Exception {

        ObjectOutputStream oos = new ObjectOutputStream(new FileOutputStream(new File("test.ser"))); 

        SymbolSerializable symbol = new SymbolSerializable();

        // dummy values
        symbol.setAsk(new Double(-1));
        symbol.setBid(new Double(-1));

        for(int i=0;i<10;i++){
            // update values
            symbol.setAsk(new Double(i));
            symbol.setBid(new Double(i));

            oos.writeObject(symbol);
            oos.flush();
        }

        oos.flush();
        oos.close();

        // read

        ObjectInputStream ois = new ObjectInputStream(new FileInputStream(new File("test.ser")));

        try {
            Object obj = null;
            while((obj = ois.readObject())!=null){
                System.out.println((SymbolSerializable)obj);
            }
        } catch (EOFException e) {
            System.out.println("All items read");
        } catch(Exception e){
            e.printStackTrace();
        } finally {
            ois.close();
        }

    }

}

Here the code for the test case #2

package ca.sebastiendionne.demo;

import java.io.EOFException;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;

import ca.sebastiendionne.demo.model.SymbolExternalizable;

public class SerializationFailed2 {

    /**
     * @param args
     */
    public static void main(String[] args) throws Exception {

        ObjectOutputStream oos = new ObjectOutputStream(new FileOutputStream(new File("test.ser"))); 

        SymbolExternalizable symbol = new SymbolExternalizable();

        // dummy values
        symbol.setAsk(new Double(-1));
        symbol.setBid(new Double(-1));

        for(int i=0;i<10;i++){
            // update values
            symbol.setAsk(new Double(i));
            symbol.setBid(new Double(i));

            oos.writeObject(symbol);
            oos.flush();
        }

        oos.flush();
        oos.close();

        // read

        ObjectInputStream ois = new ObjectInputStream(new FileInputStream(new File("test.ser")));

        try {
            Object obj = null;
            while((obj = ois.readObject())!=null){
                System.out.println((SymbolExternalizable)obj);
            }
        } catch (EOFException e) {
            System.out.println("All items read");
        } catch(Exception e){
            e.printStackTrace();
        } finally {
            ois.close();
        }

    }

}

Here the code for the test case #3

package ca.sebastiendionne.demo;

import java.io.EOFException;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;

import ca.sebastiendionne.demo.model.SymbolSerializable;

public class SerializationWithNew {

    /**
     * @param args
     */
    public static void main(String[] args) throws Exception {

        ObjectOutputStream oos = new ObjectOutputStream(new FileOutputStream(new File("test.ser"))); 

        SymbolSerializable symbol = new SymbolSerializable();

        // dummy values
        symbol.setAsk(new Double(-1));
        symbol.setBid(new Double(-1));

        for(int i=0;i<10;i++){
            // update values
            symbol.setAsk(new Double(i));
            symbol.setBid(new Double(i));

            SymbolSerializable symbol2 = new SymbolSerializable();
            symbol2.setAsk(symbol.getAsk());
            symbol2.setBid(symbol.getBid());

            oos.writeObject(symbol2);
            oos.flush();
        }

        oos.flush();
        oos.close();

        // read

        ObjectInputStream ois = new ObjectInputStream(new FileInputStream(new File("test.ser")));

        try {
            Object obj = null;
            while((obj = ois.readObject())!=null){
                System.out.println((SymbolSerializable)obj);
            }
        } catch (EOFException e) {
            System.out.println("All items read");
        } catch(Exception e){
            e.printStackTrace();
        } finally {
            ois.close();
        }

    }

}

Here the code for the test case #4

package ca.sebastiendionne.demo;

import java.io.EOFException;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;

import ca.sebastiendionne.demo.model.SymbolSerializable;

public class SerializationWithReset {

    /**
     * @param args
     */
    public static void main(String[] args) throws Exception {

        ObjectOutputStream oos = new ObjectOutputStream(new FileOutputStream(new File("test.ser"))); 

        SymbolSerializable symbol = new SymbolSerializable();

        // dummy values
        symbol.setAsk(new Double(-1));
        symbol.setBid(new Double(-1));

        for(int i=0;i<10;i++){
            // update values
            symbol.setAsk(new Double(i));
            symbol.setBid(new Double(i));

            oos.writeObject(symbol);
            oos.flush();
            oos.reset(); // magic line
        }

        oos.flush();
        oos.close();

        // read

        ObjectInputStream ois = new ObjectInputStream(new FileInputStream(new File("test.ser")));

        try {
            Object obj = null;
            while((obj = ois.readObject())!=null){
                System.out.println((SymbolSerializable)obj);
            }
        } catch (EOFException e) {
            System.out.println("All items read");
        } catch(Exception e){
            e.printStackTrace();
        } finally {
            ois.close();
        }

    }

}

I can't say what is the performance impact if we use reset() on each updates sent. It is better to use new or reset ?

ionice -c 2 cp -R /mnt/mysql_snapshot /mnt/backup/daily/20100719/

Are you running out of space?
Monitor snapshot’s allocated size: if there’s just one snapshot, do it like this:

Every 10.0s: lvdisplay | grep Allocated????????????????????????????????????????????????????????????????????????????????????????????????????????????????? Mon Jul 19 09:51:29 2010

Allocated to snapshot? 3.63%

Don’t let it reach 100%.
Avoid running out of space
To make sure you don’t run out of snapshot allocated size, stop all administrative scripts.

Are you running your weekly purging of old data? You will be writing a lot of pages, and all will have to fit in the snapshot.
Building your reports? You may be creating large temporary tables; make sure these are not on the snapshot volume.
Rebuilding your Sphinx fulltext index? Make sure it is not on the snapshot volume, or postpone till after backup.