#StackBounty: #php #mysql #osx #mysqli #dump PHP Warning: "MySQL server has gone away" after MySQL dump in macOS Terminal

Bounty: 50

I am using a script to download remote MySQL databases (gzip) and extract them directly to my MacBook’s MySQL 5.7 (not MAMP) server.
It works fine, but as a side effect PHP (MAMP) loses the MySQL connection with the warning

PHP Warning:  mysqli::__construct(): MySQL server has gone away in ...

This happens after roundabout a dozen databases oder bigger files (can’t reproduce). The MySQL pane in System Preferences says running (green), but PHP obviously lost it.

I played around with my.cnf and set

[mysqld]
max_allowed_packet=128M

or even 800M, but it doesn’t seem to change anything.

When I manually stop and start MySQL in System Preferences, PHP continues to work normal again.

Any ideas?


Get this bounty!!!

#StackBounty: #mysql #ruby-on-rails Rails 5 HABTM from multiple databases works in mysql command but error in Rails

Bounty: 50

I am building an rails 5 app that connects to 2 different databases (dbA & dbB). My databases are on the same database host.

I want to make a wishlist. Pretty easy when using the same DB, but I am stuck with an “interesting” error.

This is what the databases look like:

enter image description here

the models are as follow:

user.rb

class User < ApplicationRecord
  has_one :wishlist, dependent: :destroy
end

wishlist.rb

class Wishlist < ApplicationRecord
  belongs_to :user
  # has_and_belongs_to_many :wines
  # The above did not work
  # so I had to revert to has_many through
  has_many :wines_wishlists
  has_many :wines, through: :wines_wishlists
end

wines_wishlist.rb

class WinesWishlist < ApplicationRecord
  belongs_to :wine
  belongs_to :wishlist

  def self.table_name_prefix
    "dbA_#{Rails.env}."
  end
  # I added the above to prevent ActiveRecord from
  # looking for the table in the wrong database
end

wine.rb (legacy model)

class Wine < ApplicationRecord
  self.abstract_class = true
  establish_connection LEGACY_DB 
  # LEGACY_DB is the legacy database connection info from a yaml file
  # located in config.

  def self.table_name_prefix
    "dbB_#{Rails.env}."
  end
end

This is quite straigth forward IMHO. Now the interresting error:

When I try the following :

user = User.last
user.wishlist.wines

It works on my local machine in development. It doesn’t work on my staging server! When I try in the rails console, I get this:

ActiveRecord::StatementInvalid: Mysql2::Error: Table 'dbA_staging.wines_wishlists' doesn't exist: SELECT  `dbB_staging`.`wines`.* FROM `dbB_staging`.`wines` INNER JOIN `dbA_staging`.`wines_wishlists` ON `dbB_staging`.`wines`.`id` = `dbA_staging`.`wines_wishlists`.`wine_id` WHERE `dbA_staging`.`wines_wishlists`.`wishlist_id` = 1

This is the expected SQL.

user.wishlist.wines.to_sql
=> "SELECT  `dbB_staging`.`wines`.* FROM `dbB_staging`.`wines` INNER JOIN `dbA_staging`.`wines_wishlists` ON `dbB_staging`.`wines`.`id` = `dbA_staging`.`wines_wishlists`.`wine_id` WHERE `dbA_staging`.`wines_wishlists`.`wishlist_id` = 1"

Even better, when I try the same SQL in rails db on my staging machine, it works!! It doesn’t work in rails even though the SQL is correct, but it works in mysql command line.

I based my code on the following article and made some research, but I can’t seem to figure out how to go around this problem.

I am using (same for development and staging):

  • Rails 5.1.1
  • ruby 2.4.0p0
  • mysql 5.6.34 (staging)
  • mysql 5.7.17 (development)

Any help would be greatly appreciated!


Get this bounty!!!

#StackBounty: #php #mysql #loops #object Determining which objects are or are not linked to the main root object

Bounty: 50

I am trying to navigate through a bunch of objects with links to other objects. I want to start with the lowest id number (the root object) and navigate through each of the objects based on connected links. Some of the object links will loop back to previous objects, so I want to make sure I look at each one only once otherwise I will get stuck in an infinite loop. I also want to be able to tell which objects cannot be accessed by navigating through the links beginning at the first link.

The tables in my database look like this:

Object Table:

+----+---------+
| id | title   |
+----+---------+
|  1 | Apple   |
|  3 | Carrot  |
|  4 | Dill    |
|  5 | Egg     |
|  6 | Fred    |
|  7 | Goat    |
|  8 | Harry   |
|  9 | Igloo   |
| 10 | Jason   |
| 11 | Klaus   |
| 12 | Banana  |
| 15 | Oyster1 |
| 16 | Oyster2 |
+----+---------+

Object_Links Table:

+----+---------+--------------+
| id |  obj_id |  obj_link_id |
+----+---------+--------------+
|  1 |       1 |           12 |
|  2 |       1 |            5 |
|  3 |       3 |            1 |
|  4 |       3 |           12 |
|  5 |       3 |            3 |
|  6 |       4 |            1 |
|  7 |       4 |            5 |
|  8 |       5 |            6 |
|  9 |       6 |            7 |
| 10 |       7 |            7 |
| 11 |       7 |            8 |
| 12 |       9 |           12 |
| 13 |       9 |            5 |
| 14 |      10 |            1 |
| 15 |      10 |            5 |
| 16 |      10 |            8 |
| 17 |      11 |            1 |
| 18 |      11 |            5 |
| 19 |      11 |           10 |
| 20 |      12 |            3 |
| 21 |      15 |           16 |
| 22 |      16 |           15 |
+----+---------+--------------+

So, from the table you can see that object 1 has links to both objects 12 and 5.

My SQL query looks like this:

select  object.id, title, obj_link_id
    from  object
    left join  object_links  ON object.id = object_links.object_id
    order by  object.id 

which gives the table:

+----+---------+--------------+
| id | title   |  obj_link_id |
+----+---------+--------------+
|  1 | Apple   |           12 |
|  1 | Apple   |            5 |
|  3 | Carrot  |            1 |
|  3 | Carrot  |           12 |
|  3 | Carrot  |            3 |
|  4 | Dill    |            1 |
|  4 | Dill    |            5 |
|  5 | Egg     |            6 |
|  6 | Fred    |            7 |
|  7 | Goat    |            7 |
|  7 | Goat    |            8 |
|  8 | Harry   |         NULL |
|  9 | Igloo   |           12 |
|  9 | Igloo   |            5 |
| 10 | Jason   |            1 |
| 10 | Jason   |            5 |
| 10 | Jason   |            8 |
| 11 | Klaus   |            1 |
| 11 | Klaus   |            5 |
| 11 | Klaus   |           10 |
| 12 | Banana  |            3 |
| 15 | Oyster1 |           16 |
| 16 | Oyster2 |           15 |
+----+---------+--------------+

In PHP I am using:

$objects = $stmt->fetchAll(PDO::FETCH_CLASS);

I wasn’t sure whether there was a better way to fetch these for my purposes so am open to suggestions.

A print_r($objects) yields:

Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [title] => Apple
            [obj_link_id] => 12
        )

    [1] => stdClass Object
        (
            [id] => 1
            [title] => Apple
            [obj_link_id] => 5
        )

    [2] => stdClass Object
        (
            [id] => 3
            [title] => Carrot
            [obj_link_id] => 1
        )

    [3] => stdClass Object
        (
            [id] => 3
            [title] => Carrot
            [obj_link_id] => 12
        )

    [4] => stdClass Object
        (
            [id] => 3
            [title] => Carrot
            [obj_link_id] => 3
        )

    [5] => stdClass Object
        (
            [id] => 4
            [title] => Dill
            [obj_link_id] => 1
        )

    [6] => stdClass Object
        (
            [id] => 4
            [title] => Dill
            [obj_link_id] => 5
        )

    [7] => stdClass Object
        (
            [id] => 5
            [title] => Egg
            [obj_link_id] => 6
        )

    [8] => stdClass Object
        (
            [id] => 6
            [title] => Fred
            [obj_link_id] => 7
        )

    [9] => stdClass Object
        (
            [id] => 7
            [title] => Goat
            [obj_link_id] => 7
        )

    [10] => stdClass Object
        (
            [id] => 7
            [title] => Goat
            [obj_link_id] => 8
        )

    [11] => stdClass Object
        (
            [id] => 8
            [title] => Harry
            [obj_link_id] =>
        )

    [12] => stdClass Object
        (
            [id] => 9
            [title] => Igloo
            [obj_link_id] => 12
        )

    [13] => stdClass Object
        (
            [id] => 9
            [title] => Igloo
            [obj_link_id] => 5
        )

    [14] => stdClass Object
        (
            [id] => 10
            [title] => Jason
            [obj_link_id] => 1
        )

    [15] => stdClass Object
        (
            [id] => 10
            [title] => Jason
            [obj_link_id] => 5
        )

    [16] => stdClass Object
        (
            [id] => 10
            [title] => Jason
            [obj_link_id] => 8
        )

    [17] => stdClass Object
        (
            [id] => 11
            [title] => Klaus
            [obj_link_id] => 1
        )

    [18] => stdClass Object
        (
            [id] => 11
            [title] => Klaus
            [obj_link_id] => 5
        )

    [19] => stdClass Object
        (
            [id] => 11
            [title] => Klaus
            [obj_link_id] => 10
        )

    [20] => stdClass Object
        (
            [id] => 12
            [title] => Banana
            [obj_link_id] => 3
        )

    [21] => stdClass Object
        (
            [id] => 15
            [title] => Oyster1
            [obj_link_id] => 16
        )

    [22] => stdClass Object
        (
            [id] => 16
            [title] => Oyster2
            [obj_link_id] => 15
        )

)

Please note, that the number in the brackets is just the array index, not the object id number, so don’t let the index throw you off.

I am trying to find a way to determine which are the linked and which are the unlinked objects. Based on the above scenario the objects should be separated as follows:

**Linked:**

    Apple
    Banana
    Carrot
    Egg
    Fred
    Goat
    Harry

**Not Linked:**

    Dill
    Igloo
    Jason
    Klaus
    Oyster1
    Oyster2

My main question:

How can I create a loop in PHP to loop through a structure like this especially when each object can have multiple links? Ultimately I would like to produce two collections of objects, one containing the linked objects and one containing the unlinked objects. A sample collection might look like this:

stdClass Object
(
  [LinkedElements] => stdClass Object
    (
      [1] => stdClass Object
        (
          [id] => 1
          [name] => Apple
          [link] => Array
            (
              [0] => 14
              [1] => 5
            )

        )

      [14] => stdClass Object
        (
          [id] => 14
          [name] => Banana
          [link] => Array
            (
              [0] => 3
            )

        )

      [3] => stdClass Object
        (
          [id] => 3
          [name] => Carrot
          [link] => Array
            (
              [0] => 1
              [1] => 14
              [2] => 3
            )

        )

      [5] => stdClass Object
        (
          [id] => 5
          [name] => Egg
          [link] => Array
            (
              [0] => 6
            )

        )

      [6] => stdClass Object
        (
          [id] => 6
          [name] => Fred
          [link] => Array
            (
              [0] => 7
            )

        )

      [7] => stdClass Object
        (
          [id] => 7
          [name] => Goat
          [link] => Array
            (
              [0] => 7
              [1] => 8
            )

        )

      [8] => stdClass Object
        (
          [id] => 8
          [name] => Harry
        )

    )

  [UnLinkedElements] => stdClass Object
    (
      [4] => stdClass Object
        (
          [id] => 4
          [name] => Dill
          [link] => Array
            (
              [0] => 1
              [1] => 5
            )

        )

      [9] => stdClass Object
        (
          [id] => 9
          [name] => Igloo
          [link] => Array
            (
              [0] => 14
              [1] => 5
            )

        )

      [10] => stdClass Object
        (
          [id] => 10
          [name] => Jason
          [link] => Array
            (
              [0] => 1
              [1] => 5
              [2] => 8
            )

        )

      [11] => stdClass Object
        (
          [id] => 11
          [name] => Klaus
          [link] => Array
            (
              [0] => 1
              [1] => 5
              [2] => 10
            )

        )

      [15] => stdClass Object
        (
          [id] => 15
          [name] => Oyster1
          [link] => Array
            (
              [0] => 16
            )

        )

      [16] => stdClass Object
        (
          [id] => 16
          [name] => Oyster2
          [link] => Array
            (
              [0] => 15
            )

        )

    )

)

Please note:

  • Navigation is done from object to link, not the other way around.
  • It is okay to have an object point to itself (as object 7 does).
  • The above sample structure (underneath my main question) is a suggestion only and I am open to other suggestions.
  • Disclaimer: This question is based on another question I previously asked.
    In my original question I manually created the test objects, but I was not able to pull them out of my database in this fashion.


Get this bounty!!!

#StackBounty: #mysql #datetime #overlapping MySQL calculate overlapping period excluding overlapping rules

Bounty: 50

I would like to calculate the period overlap in hours based on special rules.
I created a test setup, that works as long as the rules themselves don’t overlap.

Example non overlapping rules:

    id  ort_name          ort_type  time_start  time_end  factor  
------  ----------------  --------  ----------  --------  --------
     1  Overtime evening  daily     18:00:00    23:59:59      1.20
     2  Overtime Night    daily     00:00:00    05:00:00      1.20

When the rules do overlap I need to solve this in our programming language, but I was looking for a database only solution.

Example overlapping rules:

    id  ort_name              ort_type  time_start  time_end  factor  
------  --------------------  --------  ----------  --------  --------
     1  Overtime evening      daily     18:00:00    23:59:59      1.20
     2  Overtime Night        daily     00:00:00    05:00:00      1.20
     3  Overtime Night Extra  daily     01:00:00    03:00:00      1.30

In the below example the users Kevin and Michael don’t have overlapping rules and the result is as expected. The user Derrick is affected by rule (id) 1, 2 and 3 (overlapping rules)

Example query:

SELECT
    OC.ort_rule_id
    , ORT.factor
    , S.user_name
    , S.shift_id
    , S.date_start AS shift_start
    , S.date_end AS shift_end
    , ROUND((TIMESTAMPDIFF(SECOND, S.date_start, S.date_end) / 3600), 2) AS shift_duration_hours
    , GREATEST(OC.ort_date_start, S.date_start) AS overlap_start
    , LEAST(OC.ort_date_end, S.date_end) AS overlap_end
    , ROUND((TIMESTAMPDIFF(SECOND, GREATEST(OC.ort_date_start, S.date_start), LEAST(OC.ort_date_end, S.date_end)) / 3600), 2) AS overlap_duration_hours 
FROM
    shifts AS S    
INNER JOIN ort_calendar AS OC 
        ON S.date_end > OC.ort_date_start 
        AND S.date_start < OC.ort_date_end
INNER JOIN ort_rule AS ORT
    ON OC.ort_rule_id = ORT.id
WHERE
    S.date_start >= '2014-12-01 00:00:00'
AND
    S.date_end < '2014-12-01 00:00:00' + INTERVAL 1 MONTH
ORDER BY
    S.user_name ASC, S.date_start ASC, overlap_start ASC, ORT.factor DESC

Output:

ort_rule_id  factor  user_name  shift_id  shift_start          shift_end            shift_duration_hours  overlap_start        overlap_end          overlap_duration_hours  
-----------  ------  ---------  --------  -------------------  -------------------  --------------------  -------------------  -------------------  ------------------------
          1    1.20  Derrick           8  2014-12-09 15:00:00  2014-12-10 06:12:27  15.21                 2014-12-09 18:00:00  2014-12-09 23:59:59  6.00                    
          2    1.20  Derrick           8  2014-12-09 15:00:00  2014-12-10 06:12:27  15.21                 2014-12-10 00:00:00  2014-12-10 05:00:00  5.00                    
          3    1.30  Derrick           8  2014-12-09 15:00:00  2014-12-10 06:12:27  15.21                 2014-12-10 01:00:00  2014-12-10 03:00:00  2.00                    
          1    1.20  Kevin             2  2014-12-09 15:00:00  2014-12-10 00:00:00  9.00                  2014-12-09 18:00:00  2014-12-09 23:59:59  6.00                    
          1    1.20  Michael           7  2014-12-09 15:00:00  2014-12-10 00:30:00  9.50                  2014-12-09 18:00:00  2014-12-09 23:59:59  6.00                    
          2    1.20  Michael           7  2014-12-09 15:00:00  2014-12-10 00:30:00  9.50                  2014-12-10 00:00:00  2014-12-10 00:30:00  0.50                    

I would like the output of my query to take into account the overlapping rules. The rule with the highest factor gets precedence above a rule with a lower factor.
The output of my query should be:

ort_rule_id  factor  user_name  shift_id  shift_start          shift_end            shift_duration_hours  overlap_start        overlap_end          overlap_duration_hours  
-----------  ------  ---------  --------  -------------------  -------------------  --------------------  -------------------  -------------------  ------------------------
          1    1.20  Derrick           8  2014-12-09 15:00:00  2014-12-10 06:12:27  15.21                 2014-12-09 18:00:00  2014-12-09 23:59:59  6.00                    
          2    1.20  Derrick           8  2014-12-09 15:00:00  2014-12-10 06:12:27  15.21                 2014-12-10 00:00:00  2014-12-10 01:00:00  1.00                    
          3    1.30  Derrick           8  2014-12-09 15:00:00  2014-12-10 06:12:27  15.21                 2014-12-10 01:00:00  2014-12-10 03:00:00  2.00                    
          2    1.20  Derrick           8  2014-12-09 15:00:00  2014-12-10 06:12:27  15.21                 2014-12-10 03:00:00  2014-12-10 05:00:00  2.00                    
          1    1.20  Kevin             2  2014-12-09 15:00:00  2014-12-10 00:00:00  9.00                  2014-12-09 18:00:00  2014-12-09 23:59:59  6.00                    
          1    1.20  Michael           7  2014-12-09 15:00:00  2014-12-10 00:30:00  9.50                  2014-12-09 18:00:00  2014-12-09 23:59:59  6.00                    
          2    1.20  Michael           7  2014-12-09 15:00:00  2014-12-10 00:30:00  9.50                  2014-12-10 00:00:00  2014-12-10 00:30:00  0.50     

===========

DDL

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*Table structure for table `ort_calendar` */

DROP TABLE IF EXISTS `ort_calendar`;

CREATE TABLE `ort_calendar` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ort_rule_id` int(10) unsigned NOT NULL,
  `ort_date_start` datetime DEFAULT NULL,
  `ort_date_end` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ort_calendar_unique` (`ort_rule_id`,`ort_date_start`,`ort_date_end`),
  KEY `ort_date_start_idx` (`ort_date_start`),
  KEY `ort_date_end_idx` (`ort_date_end`),
  CONSTRAINT `ort_period_ort_rule_fk` FOREIGN KEY (`ort_rule_id`) REFERENCES `ort_rule` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=783 DEFAULT CHARSET=utf8;

/*Data for the table `ort_calendar` */

insert  into `ort_calendar`(`id`,`ort_rule_id`,`ort_date_start`,`ort_date_end`) values (6,1,'2014-12-08 18:00:00','2014-12-08 23:59:59'),(58,1,'2014-12-09 18:00:00','2014-12-09 23:59:59'),(110,1,'2014-12-10 18:00:00','2014-12-10 23:59:59'),(266,2,'2014-12-09 00:00:00','2014-12-09 05:00:00'),(318,2,'2014-12-10 00:00:00','2014-12-10 05:00:00'),(527,3,'2014-12-09 01:00:00','2014-12-09 03:00:00'),(579,3,'2014-12-10 01:00:00','2014-12-10 03:00:00');

/*Table structure for table `ort_rule` */

DROP TABLE IF EXISTS `ort_rule`;

CREATE TABLE `ort_rule` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ort_name` varchar(80) NOT NULL,
  `ort_type` enum('daily','yearly') NOT NULL,
  `time_start` time NOT NULL,
  `time_end` time NOT NULL,
  `factor` double(5,2) NOT NULL,
  `deleted_by` int(10) unsigned DEFAULT NULL,
  `deleted_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ort_name_idx` (`ort_name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Data for the table `ort_rule` */

insert  into `ort_rule`(`id`,`ort_name`,`ort_type`,`time_start`,`time_end`,`factor`,`deleted_by`,`deleted_date`) values (1,'Overtime evening','daily','18:00:00','23:59:59',1.20,NULL,NULL),(2,'Overtime Night','daily','00:00:00','05:00:00',1.20,NULL,NULL),(3,'Overtime Night Extra','daily','01:00:00','03:00:00',1.30,NULL,NULL);

/*Table structure for table `shifts` */

DROP TABLE IF EXISTS `shifts`;

CREATE TABLE `shifts` (
  `shift_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `date_start` datetime NOT NULL,
  `date_end` datetime NOT NULL,
  `pause` tinyint(3) DEFAULT NULL,
  PRIMARY KEY (`shift_id`),
  KEY `idx_date_start` (`date_start`),
  KEY `idx_date_end` (`date_end`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*Data for the table `shifts` */

insert  into `shifts`(`shift_id`,`user_name`,`date_start`,`date_end`,`pause`) values (1,'Franck','2014-12-08 08:00:00','2014-12-08 17:00:00',30),(2,'Kevin','2014-12-09 15:00:00','2014-12-10 00:00:00',60),(3,'Michael','2014-12-10 08:00:00','2014-12-10 17:00:00',60),(4,'Franck','2014-12-31 08:00:00','2014-12-31 17:00:00',90),(5,'Franck','2015-01-01 08:00:00','2015-01-01 17:00:00',30),(6,'Franck','2014-12-07 08:00:00','2014-12-07 17:00:00',30),(7,'Michael','2014-12-09 15:00:00','2014-12-10 00:30:00',30),(8,'Derrick','2014-12-09 15:00:00','2014-12-10 06:12:27',30);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;


Get this bounty!!!

#StackBounty: #mysql #backup #mysql-5.6 #percona #xtrabackup Percona xtrabackup transaction log corrupted

Bounty: 50

I am attempting to prepare and restore a full backup taken through xtrabackup via a scheduling script called surrogate.

I have tarred and gzipped the backup, transferred to a local machine running the same setup (aside from a slight version difference in xtrabackup – 2.2.12 on source, 2.3.9 local).

Upon extracting I am running the following command to prepare the backup for restore:

innobackupex --use-memory=1G --apply-log /home/matthew/full_2017-08-13_0700/

This is responding:

  innobackupex version 2.3.9 based on MySQL server 5.6.24 Linux (x86_64) (revision id: fde0e3e)
  xtrabackup: cd to /home/matthew/full_2017-08-13_0700/
  xtrabackup: This target seems to be not prepared yet.
  xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(661464509389)
  xtrabackup: using the following InnoDB configuration for recovery:
  xtrabackup:   innodb_data_home_dir = ./
  xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
  xtrabackup:   innodb_log_group_home_dir = ./
  xtrabackup:   innodb_log_files_in_group = 1
  xtrabackup:   innodb_log_file_size = 2097152
  xtrabackup: using the following InnoDB configuration for recovery:
  xtrabackup:   innodb_data_home_dir = ./
  xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
  xtrabackup:   innodb_log_group_home_dir = ./
  xtrabackup:   innodb_log_files_in_group = 1
  xtrabackup:   innodb_log_file_size = 2097152
  xtrabackup: Starting InnoDB instance for recovery.
  xtrabackup: Using 1073741824 bytes for buffer pool (set by --use-memory parameter)
  InnoDB: Using atomics to ref count buffer pool pages
  InnoDB: The InnoDB memory heap is disabled
  InnoDB: Mutexes and rw_locks use GCC atomic builtins
  InnoDB: Memory barrier is not used
  InnoDB: Compressed tables use zlib 1.2.8
  InnoDB: Using CPU crc32 instructions
  InnoDB: Initializing buffer pool, size = 1.0G
  InnoDB: Completed initialization of buffer pool
  InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
  InnoDB: Setting file ./ibdata1 size to 12 MB
  InnoDB: Database physically writes the file full: wait...
  InnoDB: Setting log file ./ib_logfile101 size to 2 MB
  InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
  InnoDB: New log files created, LSN=45781
  InnoDB: Doublewrite buffer not found: creating new
  InnoDB: Doublewrite buffer created
  InnoDB: 1 rollback segment(s) are active.
  InnoDB: Waiting for purge to start
  InnoDB: 5.6.24 started; log sequence number 0

  xtrabackup: error: The transaction log file is corrupted.
  xtrabackup: error: The log was not applied to the intended LSN!
  xtrabackup: Log applied to lsn 0
  xtrabackup: The intended lsn is 661464919534

Any idea what this is and how I overcome it?


Get this bounty!!!

#StackBounty: #mysql #migration #mysqldump Issues after schemas migration from mysql 4 to mysql 5.6

Bounty: 50

Recently I’ve migrated some schemas from mysql 4 instance into an instance of mysql 5.6 by mysqldump and import operations. After some syntax adjustments, I could import the dump file successfully.

After the import, I went over the log file and i found some issues. Does anyone know what is that and how to fix it?

InnoDB: Error in pars0opt.cc: table cars/cars_ftr_a has prefix_len != 0
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_BEING_DELETED'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_BEING_DELETED_CACHE'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_CONFIG'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_DELETED'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_DELETED_CACHE'
InnoDB: in InnoDB data dictionary has unknown flags 50.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_1'
InnoDB: in InnoDB data dictionary has unknown flags 40.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_2'
InnoDB: in InnoDB data dictionary has unknown flags 40.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_3'
InnoDB: in InnoDB data dictionary has unknown flags 40.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_4'
InnoDB: in InnoDB data dictionary has unknown flags 40.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_5'
InnoDB: in InnoDB data dictionary has unknown flags 40.
2017-08-16 17:14:15 7fd3081c4700  InnoDB: Warning: table 'cars/FTS_00000000000002cb_000000000000040c_INDEX_6'
InnoDB: in InnoDB data dictionary has unknown flags 40.


Get this bounty!!!

#StackBounty: #mysql #subquery #left-join Mysql subquery much faster than join

Bounty: 50

I have the following queries which both return the same result and row count:

select * from (
               select UNIX_TIMESTAMP(network_time) * 1000 as epoch_network_datetime, 
                      hbrl.business_rule_id, 
                      display_advertiser_id, 
                      hbrl.campaign_id, 
                      truncate(sum(coalesce(hbrl.ad_spend_network, 0))/100000.0, 2) as demand_ad_spend_network, 
                      sum(coalesce(hbrl.ad_view, 0)) as demand_ad_view, 
                      sum(coalesce(hbrl.ad_click, 0)) as demand_ad_click, 
                      truncate(coalesce(case when sum(hbrl.ad_view) = 0 then 0 else 100*sum(hbrl.ad_click)/sum(hbrl.ad_view) end, 0), 2) as ctr_percent, 
                      truncate(coalesce(case when sum(hbrl.ad_view) = 0 then 0 else sum(hbrl.ad_spend_network)/100.0/sum(hbrl.ad_view) end, 0), 2) as ecpm,
                      truncate(coalesce(case when sum(hbrl.ad_click) = 0 then 0 else sum(hbrl.ad_spend_network)/100000.0/sum(hbrl.ad_click) end, 0), 2) as ecpc 
               from hourly_business_rule_level hbrl
               where (publisher_network_id = 31534) 
               and network_time between str_to_date('2017-08-13 17:00:00.000000', '%Y-%m-%d %H:%i:%S.%f') and str_to_date('2017-08-14 16:59:59.999000', '%Y-%m-%d %H:%i:%S.%f') 
               and (network_time IS NOT NULL and display_advertiser_id > 0)
               group by network_time, hbrl.campaign_id, hbrl.business_rule_id
               having demand_ad_spend_network > 0
               OR demand_ad_view > 0
               OR demand_ad_click > 0
               OR ctr_percent > 0
               OR ecpm > 0
               OR ecpc > 0
               order by epoch_network_datetime) as atb
       left join dim_demand demand on atb.display_advertiser_id = demand.advertiser_dsp_id 
       and atb.campaign_id = demand.campaign_id 
       and atb.business_rule_id = demand.business_rule_id 

ran explain extended, and these are the results:

+----+-------------+----------------------------+------+-------------------------------------------------------------------------------+---------+---------+-----------------+---------+----------+----------------------------------------------+
| id | select_type | table                      | type | possible_keys                                                                 | key     | key_len | ref             | rows    | filtered | Extra                                        |
+----+-------------+----------------------------+------+-------------------------------------------------------------------------------+---------+---------+-----------------+---------+----------+----------------------------------------------+
|  1 | PRIMARY     | <derived2>                 | ALL  | NULL                                                                          | NULL    | NULL    | NULL            | 1451739 |   100.00 | NULL                                         |
|  1 | PRIMARY     | demand                     | ref  | PRIMARY,join_index                                                            | PRIMARY | 4       | atb.campaign_id |       1 |   100.00 | Using where                                  |
|  2 | DERIVED     | hourly_business_rule_level | ALL  | _hourly_business_rule_level_supply_idx,_hourly_business_rule_level_demand_idx | NULL    | NULL    | NULL            | 1494447 |    97.14 | Using where; Using temporary; Using filesort |
+----+-------------+----------------------------+------+-------------------------------------------------------------------------------+---------+---------+-----------------+---------+----------+----------------------------------------------+

and the other is:

select UNIX_TIMESTAMP(network_time) * 1000 as epoch_network_datetime, 
       hbrl.business_rule_id, 
       display_advertiser_id, 
       hbrl.campaign_id, 
       truncate(sum(coalesce(hbrl.ad_spend_network, 0))/100000.0, 2) as demand_ad_spend_network, 
       sum(coalesce(hbrl.ad_view, 0)) as demand_ad_view, 
       sum(coalesce(hbrl.ad_click, 0)) as demand_ad_click, 
       truncate(coalesce(case when sum(hbrl.ad_view) = 0 then 0 else 100*sum(hbrl.ad_click)/sum(hbrl.ad_view) end, 0), 2) as ctr_percent, 
       truncate(coalesce(case when sum(hbrl.ad_view) = 0 then 0 else sum(hbrl.ad_spend_network)/100.0/sum(hbrl.ad_view) end, 0), 2) as ecpm, 
       truncate(coalesce(case when sum(hbrl.ad_click) = 0 then 0 else sum(hbrl.ad_spend_network)/100000.0/sum(hbrl.ad_click) end, 0), 2) as ecpc 
from hourly_business_rule_level hbrl
join dim_demand demand on hbrl.display_advertiser_id = demand.advertiser_dsp_id 
and hbrl.campaign_id = demand.campaign_id 
and hbrl.business_rule_id = demand.business_rule_id 
where (publisher_network_id = 31534) 
and network_time between str_to_date('2017-08-13 17:00:00.000000', '%Y-%m-%d %H:%i:%S.%f') and str_to_date('2017-08-14 16:59:59.999000', '%Y-%m-%d %H:%i:%S.%f') 
and (network_time IS NOT NULL and display_advertiser_id > 0)
group by network_time, hbrl.campaign_id, hbrl.business_rule_id
having demand_ad_spend_network > 0
OR demand_ad_view > 0
OR demand_ad_click > 0 
OR ctr_percent > 0
OR ecpm > 0
OR ecpc > 0
order by epoch_network_datetime;

and these are the results for the second query:

+----+-------------+----------------------------+------+-------------------------------------------------------------------------------+---------+---------+---------------------------------------------------------------+---------+----------+----------------------------------------------+
| id | select_type | table                      | type | possible_keys                                                                 | key     | key_len | ref                                                           | rows    | filtered | Extra                                        |
+----+-------------+----------------------------+------+-------------------------------------------------------------------------------+---------+---------+---------------------------------------------------------------+---------+----------+----------------------------------------------+
|  1 | SIMPLE      | hourly_business_rule_level | ALL  | _hourly_business_rule_level_supply_idx,_hourly_business_rule_level_demand_idx | NULL    | NULL    | NULL                                                          | 1494447 |    97.14 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | demand                     | ref  | PRIMARY,join_index                                                            | PRIMARY | 4       | my6sense_datawarehouse.hourly_business_rule_level.campaign_id |       1 |   100.00 | Using where; Using index                     |
+----+-------------+----------------------------+------+-------------------------------------------------------------------------------+---------+---------+---------------------------------------------------------------+---------+----------+----------------------------------------------+

the first one takes about 2 seconds while the second one takes over 2 minutes!

why is the second query taking so long?
what am I missing here?

thanks.


Get this bounty!!!

#StackBounty: #mysql Trying to return all columns of child records for a parent record

Bounty: 50

I was searching for a solution by which I could get all the child records for a parent record. I found a solution that meet my needs as shown here

Only problem is that the above solution is concatenating the IDs.

Current Resultset

It is comma separated of ID column with values = 2,3,4

Expected Output

ID    Name    ParentID
2      2nd       1
3      3rd       1
4      4th       2

I tried below code.

SELECT @pv:=
(SELECT * FROM tblreport WHERE ParentID IN (@pv)) AS lv FROM tblreport
JOIN
(SELECT @pv:=2)tmp
WHERE ParentID IN (@pv)

and got an error message : Operand should contain 1 column(s)

My sample SQL Fiddle


Get this bounty!!!

#StackBounty: #mysql Can't connect to local MySQL server through socket (From time to time)

Bounty: 50

I have a LAMP stack setup. Occasionally, I get the following error message when I open some page from the browser:

Error creating the connection!: SQLSTATE[HY000] [2002] Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

I think the server was configured correctly. The problem happens about every two months. Every time when I reboot the Linux server, or restart mysql, the problem was gone. I want to fix this problem permanently. Can anyone give me some idea?

Much appreciated.


Get this bounty!!!

#StackBounty: #mysql mysql copy and insert rows in same table with new primary key and sequnce number

Bounty: 50

I have multiple table with primary key, data columns and batch no.

I require procedure which dynamically create statement to copy rows from table and insert into same table by changing auto increment value batch no.

Original Table

enter image description here

mysql query should copy records of batch 1 and paste them as batch 10 as below

enter image description here

I have tried to write query but it is coping all records, also it gets failed timeout for large no of records.

CREATE PROCEDURE `duplicateRows`(_schemaName text, _tableName text, _omitColumns text, var_run_seq_no int,var_old_run_seq_no int)
    BEGIN
SELECT IF(TRIM(_omitColumns) <> '', CONCAT('id', ',', TRIM(_omitColumns),'batch_no'), CONCAT('id',',batch_no')) INTO @omitColumns;

SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.columns 
WHERE 
table_schema = _schemaName 
AND 
table_name = _tableName 
AND 
FIND_IN_SET(COLUMN_NAME,@omitColumns) = 0 
ORDER BY ORDINAL_POSITION INTO @columns;

SET @sql = CONCAT('INSERT INTO ', _tableName, '(', @columns, ',batch_no)',
  'SELECT ', @columns, ',', var_batch_no,
  ' FROM ', _schemaName, '.', _tableName);

-- select @sql;
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
END


Get this bounty!!!