Archived entries for mysql

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…

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…

Calculating the Number of Weekdays in a Given Interval

Not having the most to do on a given workday at this new job I’ve spent quite a bit of time on StackOverflow.com answering questions, mostly in the PHP and mySQL tags. Usually it’s just playing “find the missing semicolon” or “type the function name into the documentation site’s search”, but once in a while something interesting gets asked. This question really got me going last week, and I was more than a little bummed that it didn’t get much attention, so I’m posting it here.

The question was asked under the mySQL tag, so it’s written a little funky to be able to be calculated for each row in a result set. [just replace @start and @end with the appropriate field names] But this can also be easily translated into other languages with the following notes:

  1. mySQL’s DATEDIFF() function is not inclusive, hence adding 1 to the result.
  2. mySQL’s DAYOFWEEK() function returns values 1 [Sunday] through 7 [Saturday].
  3. mySQL’s IF() function is IF( [logical expression], [value if true], [value if false]).
SET @start  = '2012-09-30';
SET @end    = '2012-11-03';

SELECT
    @raw_days   := DATEDIFF(@end, @start)+1 'raw_days',
    @full_weeks := FLOOR(@raw_days / 7) 'full_weeks',
    @odd_days   := @raw_days - @full_weeks * 7 'odd_days',
    @wday_start := DAYOFWEEK(@start) 'wday_start',
    @wday_end   := DAYOFWEEK(@end) 'wday_end',
    @weekend_intrusion  := @wday_start + @odd_days 'weekend_intrusion',
    @extra_weekends     :=
        IF(@wday_start = 1, IF(@odd_days = 0, 0, 1),
            IF(@weekend_intrusion > 7, 2,
                IF(@weekend_intrusion > 6, 1, 0)
            )
        ) 'extra_weekends',
    @total_weekends     := @full_weeks * 2 + @extra_weekends 'total_weekends',
    @total_workdays     := @raw_days - @total_weekends 'total_workdays'

Results:

+----------+------------+----------+------------+----------+-------------------+----------------+----------------+----------------+
| raw_days | full_weeks | odd_days | wday_start | wday_end | weekend_intrusion | extra_weekends | total_weekends | total_workdays |
+----------+------------+----------+------------+----------+-------------------+----------------+----------------+----------------+
|       34 |          4 |        6 |          1 |        6 |                 7 |              1 |              9 |             25 |
+----------+------------+----------+------------+----------+-------------------+----------------+----------------+----------------+

Try it yourself on SQLfiddle.com.



Copyright © 2009–2010. All rights reserved.

RSS Feed.