Archived entries for Databases

Fixing Broken MySQL Master-Master Replication: Error during XID COMMIT

So you’re stuck with master-master [or possibly circular] replication and that’s unfortunate, but if you’re reading this you’re probably already feeling bad enough about it so let’s get to it.

The error in question from SHOW SLAVE STATUS looks like:

Last_Error: Error during XID COMMIT: failed to update GTID state in mysql.gtid_slave_pos: 1062: Duplicate entry '0-11648' for key 'PRIMARY'

and I’ll also bet that you’ve got a blank Replicate_Ignore_Server_Ids: line in the slave status as well.

What seems to be happening is that statements from one master are getting bounced back to the originating server and mucking up the mySQL internals. In order to fix this you need to tell your masters to ignore statements from themselves.

Continue reading…

Read-Only Elasticsearch Proxy using Nginx

I’ve recently set up an Elasticsearch/Logstash/Kibana cluster for a project and found Elasticsearch’s complete lack of authentication/authorization a bit alarming. I wasn’t exactly exposing these endpoints publicly, but I also didn’t want an inquisitive/careless/mischievous employee deleting documents or indexes.

However, the one exception I did want to make was to allow modifications to documents in the ‘kibana-int’ index so that users could create and modify dashboards.

So I’ve come up with the following Nginx config file for this:

upstream elastic_cluster {
	server elasticsearch-01.fooblah.com:9200;
	server elasticsearch-02.fooblah.com:9200;
}

server {
	listen 9200;
	server_name kibana.fooblah.com;
	client_max_body_size 50m;

	proxy_redirect off;
	proxy_set_header Connection "";
	proxy_set_header X-Real-IP $remote_addr;
	proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
	proxy_set_header Host $http_host;
	proxy_pass_header Access-Control-Allow-Origin;
	proxy_pass_header Access-Control-Allow-Methods;
	proxy_hide_header Access-Control-Allow-Headers;
	add_header Access-Control-Allow-Headers 'X-Requested-With, Content-Type';
	add_header Access-Control-Allow-Credentials true;

	# disallow write/modify to all indices
	location / {
		limit_except GET POST HEAD OPTIONS {
			# you could use auth_basic options here to allow HTTP-Auth
			deny all;
		}
		proxy_pass http://elastic_cluster;
	}
	# re-allow write/modify to kibana-int index for dashboard storage
	location /kibana-int/ {
		proxy_pass http://elastic_cluster;
	}

}

Hopefully someone finds this useful!

Simple PHP PDO Wrapper Class

For the last few years I’ve been using PHP Data Objects [PDO] in all of my projects, mainly for the use of parameterized queries to avoid SQL injection. Now with PHP pushing to remove the old mysql_* functions and get the community into PDO or mySQLi I’ve found myself sharing this code more and more often. So I’m finally posting it up here so I don’t have to keep copy/pasting this after editing out all the stuff that’s specific to me.

As a bonus, this also implements the Singleton design pattern, so you can use a static Mysql_DB::getInstance() function anywhere in you code to grab the instance of the database with very little hassle.
Continue reading…



Copyright © 2009–2010. All rights reserved.

RSS Feed.