Archive for the ‘ MySQL ’ Category
If your architecture uses MySQL replication and you make some ETL transformations and you observed some incredible values in autoincrement columns (i.e. the value dramatically increases – by hundred of thousands or event millions) – it means you have just experienced a bug. Very clear description is presented on site http://bugs.mysql.com/. However, in my case […]
### Partitioning: mysql>ALTER TABLE `els_clipping_tester_err` ADD FOREIGN KEY (`clipping_id`) REFERENCES `inf_clipping3`(`CLIP_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT; ERROR 1005 (HY000): Can’t create table ‘inforia.#sql-7f30_17f4dd0’ (errno: 150) Cannot resolve table name close to …. mysql>alter table clipping3 remove partitioing; mysql>ALTER TABLE `els_clipping_tester_err` ADD FOREIGN KEY (`clipping_id`) REFERENCES `inf_clipping3`(`CLIP_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT; Query OK, […]
PL/SQL: I wouldn’t recommend to use the following construction: IF EXISTS (SELECT 1 FROM `table_a` WHERE `id` = `_ID`) THEN UPDATE `table_a` SET `value` = 1 WHERE `id` = `_ID`; ELSE INSERT INTO `table_a` […]; END IF; In my case there was a trigger (on table_a) who additionaly modifed “table_b”. When I changed the engine […]
My aim was to avoid installation of any additional software on database servers (PHP). Simultaneously, a plugin for monitoring Mysql server, I needed to install, required PHP. In order to meet my needs I decided modify a bit the Percona plugin and get the data through http. You need some http server, then configure virtual […]
First of all I use my own script (called quick-confession) which get me info about processors, ram, discs, etc. You can get it here. It contains the instruction how to compile sysbench. I am also used to test the configuration with iozone and filebench. I am using xfs for database storage and tune it as […]
ERROR: Error in Log_event::read_log_event(): ‘Event too big’, data_len: 1635021669, event_type: 116 ERROR: Could not read entry at offset 56474241: Error in log format or read error. The replication broke down cause the binlog file got corrupted. I have found two methods to try to recover some information: 1. MySQL command “show binlog events” – reading […]
Remote Binlog Back-up. Quite similar feature is available since 5.6 #!/bin/bash # # ||||||||||| # ———– # [] [] # | # //|\\ # # The Binlog Patrol # @auth: pb@press-service.com.pl # @background: # Why do not use rotate? # 1. Rotate does not turn off the instance. # 2. Rotate does not know: # […]
For some time I have been thinking that the export of a large database from master into slave w/o a long breake in the production is not possible. In case you profit from LVM, it seems quite simple… I tested the b/m solution with my database which is 233G in size. A simple description of […]
My basic need was to balance to traffic between MySQL Slaves according to the application needs. Due to the MyISAM storage on some databases I let to some applications the delay up to 120 s. (I measured the delay with MySQL command `show slave status`. I created two clusters: 1) Slaves – no delay; 2) […]
When the economy meets the performance the iScsi comes into play! I am using 1Gb ethernet between the target and the initiator (both on CentOs 6.3). The target consists of the 3ware controller with 4 SSD Ocz Vertex 3. So, the backing-store is the device /dev/sda/ (/etc/tgt/targets.conf). Next, Parted and LVM configured on the target […]