Category Archives: Database

Solution: received end packet from server, apparent master shutdown

If you are getting the below error in your mysql (slave) logs:
090723 21:46:40 [Note] Slave: received end packet from server, apparent master shutdown:
090723 21:46:40 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.000012' at postion 106

Then, it means the slave is sharing an ID with another slave/node in the replication setup. Check the server ID to make sure it is unique.

Using iTerm with Elasticfox on OS X

Elasticfox plugin for firefox on OS X uses Terminal.app by default(to SSH into the EC2 instances). If you would like to use iTerm instead of Terminal.app:

1. Create a script (say ec2-ssh.osascript) in your home directory. This is an applescript.

on run argv
  tell application "iTerm"
    activate
    set cmd to "ssh -i " & item 1 of argv & " " & item 2 of argv
    set myterm to the last terminal
    tell myterm
      launch session "Default Session"
      tell the last session
        write text the cmd
      end tell
    end tell
  end tell
end run

2. Edit ElasticFox properties. It should look like the following:

capture 1.png

So, every time you try to SSH, it would use iTerm instead of Terminal.app

Using Tcpdump for MySQL query logging

If you are a privileged user (i.e. root), you can use tcpdump on a database server to take a peek into a network stream and filter for packets that go to MySQL. Those packets contain queries. Here’s a quick one-liner :
# tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
if (defined $q) { print "$qn"; }
$q=$_;
} else {
$_ =~ s/^[ t]+//; $q.=" $_";
}
}'

Thanks to: http://www.mysqlperformanceblog.com/2008/11/07/poor-mans-query-logging/

MySQL 5.0 has been released

MySQL 5.0 delivers dozens of new enterprise features, including:

* Stored Procedures and SQL Functions — to embed business logic in the database and improve performance;
* Triggers — to enforce complex business rules at the database level;
* Views — to ensure protection of sensitive information;
* Cursors — to allow easier database development and reference of large datasets;
* Information Schema — to provide easy access to metadata;
* XA Distributed Transactions — supports complex transactions across multiple databases in heterogeneous environments;
* SQL Mode — provides server-enforced data integrity for new and existing data;
* New Federated and Archive Storage Engines — MySQL’s unique pluggable storage engine architecture allows greater flexibility, functionality and performance by making it easy to swap database engines in and out, based on users’ application requirements;
* New Migration Toolkit — A new graphical toolkit that completely migrates all data and objects from Oracle, Microsoft SQL Server, Microsoft Access and other database platforms to MySQL;
* Instance Manager — new management assistant that allows remote starting/stopping of any MySQL Server, as well as remote editing of configuration files, reading of error and query logs, and more;
* Updated Connectors and Visual Tools — new high-performance versions of MySQL’s ODBC, Java and .NET database drivers are now available, along with updated versions of the MySQL Query Browser and MySQL Administrator.

MySQL 5.0

PHPMyAdmin – Client does not support authentication protocol requested by server

The Problem

PHPMyAdmin has been setup to use “cookie” authentication, but when you try to log into phpmyadmin, it returns:


"Client does not support authentication protocol requested by server; consider upgrading MySQL client"

The Fix

This is because, the mysqlclient installed on the box does not use the same authentication protocol that the mysqlserver is using. You can either upgrade the client, or reset the root password using this


SET PASSWORD FOR user@localhost = OLD_PASSWORD('password');

Sybase: master device size is too large

When using srvbuild in sybase to create the master database device and sybsystemprocs device I got the following error message: The given master device size is too
large.
Sybase Srvbuild Error
The error was so ,since, there was so much free space on the file system that it overflowed the datatype ASE used to store that value.
I created a dumb file, a very large file using dd:

 dd if=/dev/zero of=/sybase-system/bigfatdumbfile.dat count=10000000000000 

This solved the issue.

MySQL :: How to Reset a Forgotten Root Password

If you have set a root password, but forgot what it was, you can set a new password with the following procedure:
1. Take down the mysqld server by sending a kill (not kill-9) to the mysqld server. The pid is stored in a `.pid’ file, which is normally in the MySQL database directory:
shell> kill `cat /mysql-data-directory/hostname.pid`
You must be either the Unix root user or the same user mysqld runs as to do this.

2. Restart mysqld with the –skip-grant-tables option.

3. Set a new password with the mysqladmin password command:
shell> mysqladmin -u root password ‘mynewpassword’

4. Now you can either stop mysqld and restart it normally, or just load the privilege tables with:
shell> mysqladmin -h hostname flush-privileges

5. After this, you should be able to connect using the new password.

Alternatively, you can set the new password using the mysql client:

1. Take down and restart mysqld with the –skip-grant-tables option as described above.

2. Connect to the mysqld server with:
shell> mysql -u root mysql

3. Issue the following commands in the mysql client:
mysql> UPDATE user SET Password=PASSWORD(‘mynewpassword’) WHERE User=’root’;
mysql> FLUSH PRIVILEGES;

4. After this, you should be able to connect using the new password.

5. You can now stop mysqld and restart it normally.