#StackBounty: #mysql #select #mariadb-10.2 Why is PDO::ATTR_EMULATE_PREPARES = false causing "MySQL server has gone away" err…

Bounty: 50

I’ve been using PHP PDO with the default setting of ATTR_EMULATE_PREPARES = true and it was working fine.

However, I wanted to be able to select database values as integers instead of having them converted to strings. When I set ATTR_EMULATE_PREPARES = false I was able to get integers, but I started getting these errors in the web error log:

Uncaught PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

The errors are intermittent and I cannot duplicate them at will. I cannot duplicate the problem on my dev system (which has PHP/Apache/MariaDB on the same system). The problem only occurs on my production servers. The production webserver and database server are two separate systems, but are not heavily loaded. I only see errors on the web server, I have not noticed any problems in the database server logs.

I’ve tried adjusting various timeouts and buffer sizes and nothing seems to make any difference. Most timeouts are set to 60 seconds or more, but the problem occurs on pages with queries that take 15 seconds or less. Sometimes they fail with the above error, but then it will work fine when I refresh the page.

It took me a while to figure out what was going on, but I finally remembered I had changed the ATTR_EMULATE_PREPARES setting a few days ago. As soon as I changed it back to true (which is the default), all the problems disappeared.

I tried setting ATTR_STRINGIFY_FETCHES = false and hoped it would let me select integer values, but that setting does not appear to have any effect.

I’m running the following stack: Apache 2.4.18, PHP 7.3.20-1, MariaDB 10.2.27, Ubuntu 16.04.6

I don’t really care about ATTR_EMULATES_PREPARES, I only changed the setting so I could select integer values. Is there any way to do this without causing errors?


Get this bounty!!!

#StackBounty: #mysql #query #select #time How get info about duration of activity in time from MySQL table?

Bounty: 50

I have MySQL table "sessions" with columns bellow. In this table I have information about start and end every session which was iniciated from specific IP. In some cases for technical reasons end time could not be determined so in these cases value is "NULL". Example is bellow. I need get information about duration of activity for each "ip". For example 1.1.1.1 was active for 3 days, 2.2.2.2 for 7 days and etc. Is possible get these information with any SQL query please? Thank you for your help.

Table sessions

| sessions | CREATE TABLE `sessions` (
  `id` char(32) NOT NULL,
  `starttime` datetime NOT NULL,
  `endtime` datetime DEFAULT NULL,
  `sensor` int(4) NOT NULL,
  `ip` varchar(15) NOT NULL DEFAULT '',
  `termsize` varchar(7) DEFAULT NULL,
  `client` int(4) DEFAULT NULL,
  `asnid` int(10) DEFAULT NULL,
  `redirect` varchar(5) DEFAULT NULL,
  `ipid` int(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `starttime` (`starttime`,`sensor`),
  KEY `asnid` (`asnid`),
  KEY `fk_grade_id` (`ipid`),
  KEY `idx_sesip` (`ip`),
  KEY `idx_sescl` (`client`),
  CONSTRAINT `fk_grade_id` FOREIGN KEY (`ipid`) REFERENCES `ipinfo` (`id`),
  CONSTRAINT `sessions_ibfk_1` FOREIGN KEY (`asnid`) REFERENCES `asinfo` (`asnid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Data example:

| id           | starttime           | endtime             | sensor | ip              | termsize | client | asnid | redirect | ipid |
+--------------+---------------------+---------------------+--------+-----------------+----------+--------+-------+----------+------+
| 000002cf3795 | 2018-06-25 13:24:35 | 2018-06-25 13:24:38 |      4 | 1.1.1.1         | NULL     |     89 |  4167 | NULL     | NULL |
| 000007057b0e | 2017-08-12 13:49:36 | 2017-08-12 13:49:45 |      2 | 2.2.2.2         | NULL     |     53 |   851 | NULL     | NULL |
| 000012420d7e | 2019-01-08 19:58:18 | 2019-01-08 19:58:23 |      6 | 3.3.3.3         | NULL     |     53 | 41735 | NULL     | NULL |
| 0000198bf2d6 | 2018-03-22 15:55:59 | NULL                |      4 | 1.1.1.1         | NULL     |   NULL | 18671 | NULL     | NULL |
| 000019ec3c02 | 2017-08-16 21:37:31 | 2017-08-16 21:37:36 |      5 | 2.2.2.2         | NULL     |     90 |   169 | NULL     | NULL |

On the server I use mysql Ver 14.14 Distrib 5.5.62, but I have full export of database on my local system, where I have the latest version of MariaDB.


Get this bounty!!!

#StackBounty: #javascript #jquery #select #bootstrap-modal Select gets double options after submit form modal

Bounty: 50

I got a problem with a bootstrap modal form and dynamic select options.

  • When I open the modal, there is an input and two select options.
  • One select is populating the second after a user check an option from
    the first select.

On the first load, the select options are working ok.
When I submit the form (with Save button) and I reopen the modal, the first select gets the correct options but the second got double options.
When I reload the page everything gets back to normal until I submit the form again.

<

div class=”snippet” data-lang=”js” data-hide=”false” data-console=”true” data-babel=”false”>

<

div class=”snippet-code”>

$(document).ready(function () {
    var table = $('table.setting-groups');
    var table_permissions = $('table.settings-groups-permissions');
    var table_permissions_edit = $('table.settings-groups-permissions-edit');
    var permissions = [];
    var dataArray = [];

    $('#addGroupModal').on('shown.bs.modal', function (e) {
        $('select#permission_category').each(function () {
            var $select = $(this);
            $select.empty().append('<option></option>');

            $.ajax({
                url: $select.attr('data-source')
            }).then(function (options) {
                options.map(function (option) {
                    var $option = $('<option>');
                    $option
                        .val(option[$select.attr('data-valueKey')])
                        .text(option[$select.attr('data-displayKey')]);
                    $select.append($option);
                });
            });
        });

        $('select#permission_category').change(function () {
            var category = $('select#permission_category').val();
            if (category !== '') {
                $('select#permission_category_description').each(function () {
                    var $select = $(this);
                    $select.empty().append('<option></option>');

                    $.ajax({
                        url: $select.attr('data-source'),
                        data: {category: category}
                    }).then(function (options) {
                        options.map(function (option) {
                            var $option = $('<option>');
                            $option
                                .val(option[$select.attr('data-valueKey')])
                                .text(option[$select.attr('data-displayKey')]);
                            $select.append($option);
                        });
                    });
                });
            } else {
                $('select#permission_category_description')[0].empty();
            }
        });

        $('select#permission_category_description').change(function () {
            var category = $('select#permission_category').val();
            var description = $('select#permission_category_description').val();
            var check = $.inArray(description, permissions) > -1;

            if (!check) {
                permissions.push(description);
                table_permissions.DataTable().row.add({
                    "category": category,
                    "description": description
                }).draw();
            }
        });
        // Save permissions
        $('form#add-group').submit(function (e) {
            $('#savebtn').button('loading');
            e.preventDefault();
            var form = $(this);
            // Push table data (permissions) to form for submit
            $('<input>').attr({
                type: 'hidden',
                id: 'permissions',
                name: 'permissions',
                value: permissions
            }).appendTo(form);

            $.ajax({
                url: form.attr('action'),
                type: form.attr('method'),
                data: new FormData(this),
                dataType: 'json',
                processData: false,
                contentType: false,
                success: function (data) {
                    $('#addGroupModal').modal('toggle');
                    swal(
                        'Επιβεβαίωση Καταχώριση',
                        'Η ομάδα δικαιωμάτων καταχωρήθηκε επιτυχώς',
                        'success'
                    ).then(function () {
                        permissions = [];
                        dataArray = [];
                        form[0].reset();
                        $('select#permission_category_description').empty();
                        table_permissions.DataTable().destroy();
                        table.DataTable().ajax.reload();
                    });
                },
                error: function (data) {
                    swal(
                        'Αποτυχία Καταχώρισης',
                        data.responseText,
                        'error'
                    )
                }
            });
        });
        // Remove row when you click X
        table_permissions.on('click', '.delete', function () {
            table_permissions.DataTable().row('.selected').remove().draw(false);
        });
    });

    $('#addGroupModal').on('hide.bs.modal', function (e) {
        // Clean forms / select / datatables / arrays on modal close
        $('select#permission_category_description').empty();
        $('form')[0].reset();
        table_permissions.DataTable().destroy();
        permissions = [];
        dataArray = [];
    });

    $('#editGroupModal').on('shown.bs.modal', function (e) {
        var id = $(e.relatedTarget).data('id');

        $.post('../../custom/json/groups.php?view', {id: id}, function (arr) {
            $('input#group_name').val(arr.description);
            // Get existing permissions from groups
            dataArray = arr.data.map(function (value) {
                return value.description;
            });
        });

        $('select#permission_category_edit').each(function () {
            var $select = $(this);
            $select.empty().append('<option></option>');

            $.ajax({
                url: $select.attr('data-source')
            }).then(function (options) {
                options.map(function (option) {
                    var $option = $('<option>');
                    $option
                        .val(option[$select.attr('data-valueKey')])
                        .text(option[$select.attr('data-displayKey')]);
                    $select.append($option);
                });
            });
        });

        $('select#permission_category_edit').change(function () {
            var category = $('select#permission_category_edit').val();
            if (category !== '') {
                $('select#permission_category_description_edit').each(function () {
                    var $select = $(this);
                    $select.empty().append('<option></option>');

                    $.ajax({
                        url: $select.attr('data-source'),
                        data: {category: category}
                    }).then(function (options) {
                        options.map(function (option) {
                            var $option = $('<option>');
                            $option
                                .val(option[$select.attr('data-valueKey')])
                                .text(option[$select.attr('data-displayKey')]);
                            $select.append($option);
                        });
                    });
                });
            } else {
                $('select#permission_category_description_edit')[0].empty();
            }
        });

        $('select#permission_category_description_edit').change(function () {
            var category = $('select#permission_category_edit').val();
            var description = $('select#permission_category_description_edit').val();
            permissions = dataArray;
            var check = $.inArray(description, permissions) > -1;

            if (!check) {
                permissions.push(description);
                table_permissions_edit.DataTable().row.add({
                    "category": category,
                    "description": description
                }).draw();
            }
        });

        var settingGroupsPermissionsEdit = {
            "destroy": true,
            "processing": true,
            "deferRender": true,
            "responsive": true,
            "select": true ,
            "searching": false,
            "paging": false,
            "info": false,
            "ordering": false,
            "language": {
                "url": "/custom/js/data-tables/Greek.json"
            },
            "ajax": "../../custom/json/groups.php?view&id=" + id,
            "dataSrc": "data",
            "columns": [
                {data: "category", className: "text-center"},
                {data: "description", className: "text-center"},
                {
                    data: null, className: "text-center btn-actions", render: function (data, type, row) {
                        return '<a data-id="' + data.description + '" class="danger p-0 delete"><i class="ft-x font-medium-3 mr-2"></i></a>';
                    }
                }
            ]
        };

        $('table.settings-groups-permissions-edit').DataTable($.extend(true, {}, settingGroupsPermissionsEdit, {}));
        // Save permissions
        $('form#edit-group').submit(function (e) {
            $('#editbtn').button('loading');
            e.preventDefault();
            var form = $(this);
            // Push table data (permissions) to form for submit
            $('<input>').attr({
                type: 'hidden',
                id: 'permissions',
                name: 'permissions',
                value: permissions
            }).appendTo(form);

            $.ajax({
                url: form.attr('action') + '&id=' + id,
                type: form.attr('method'),
                data: new FormData(this),
                dataType: 'json',
                processData: false,
                contentType: false,
                success: function (data) {
                    $('#editGroupModal').modal('toggle');
                    swal(
                        'Επιβεβαίωση Ενημέρωσης',
                        'Η ομάδα δικαιωμάτων ενημερώθηκε επιτυχώς',
                        'success'
                    ).then(function () {
                        permissions = [];
                        dataArray = [];
                        form[0].reset();
                        $('select#permission_category_description_edit').empty();
                        table_permissions_edit.DataTable().destroy();
                        table.DataTable().ajax.reload();
                    });
                },
                error: function (data) {
                    swal(
                        'Αποτυχία Ενημέρωσης',
                        data.responseText,
                        'error'
                    )
                }
            });
        });
        // Remove row when you click X
        table_permissions_edit.on('click', '.delete', function () {
            var row_desc = table_permissions_edit.DataTable().rows('.selected').data().pluck('description')[0];
            permissions = remove(permissions, row_desc); // remove element from posted permissions array
            table_permissions_edit.DataTable().row('.selected').remove().draw();
        });
    });

    $('#editGroupModal').on('hide.bs.modal', function (e) {
        // Clean forms / select / datatables / arrays on modal close
        $('select#permission_category_description_edit').empty();
        $('form')[0].reset();
        table_permissions_edit.DataTable().destroy();
        permissions = [];
        dataArray = [];
    });
    // Delete group
    table.on('click', '.swal-delete', function () {
        var id = $(this).data('id');
        swal({
            title: 'Διαγραφή',
            text: 'Είστε σίγουρος/η οτι θέλετε να διαγράψετε την ομάδα δικαιωμάτων;',
            type: 'warning',
            showCancelButton: true,
            confirmButtonColor: '#0CC27E',
            cancelButtonColor: '#FF586B',
            confirmButtonText: 'Ναι',
            cancelButtonText: 'Όχι'
        }).then(function (isConfirm) {
            if (isConfirm) {
                $.post("../../custom/json/groups.php?delete", {id: id}, function (data) {
                    swal(
                        'Επιβεβαίωση Διαγραφής',
                        'Η ομάδα δικαιωμάτων διαγράφηκε επιτυχώς',
                        'success'
                    ).then(function () {
                        table.DataTable().ajax.reload();
                    });
                });
            }
        }).catch(swal.noop);
    });
    // Remove element from an array
    function remove(array, value) {
        return array.filter(function (element) {
            return element !== value;
        });
    }
});
https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js
<!-- BEGIN ADD GROUP MODAL-->