#StackBounty: #php #mysql Dynamically created multi select box values not inserted correctly

Bounty: 50

<table>
<tr>
    <td><select class="form-control selectpicker" data-live-search="true" name="reson[]" required="required">
            <option>--Select--</option>
            <option value="1">AAA</option>
            <option value="2">BBB</option>
            <option value="3">CCC</option>
            <option value="4">DDD</option>
            <option value="5">EEE</option>
        </select>
    </td>
    <td>
        <select class="form-control selectpicker" data-live-search="true" name="service[]" id="service" multiple="multiple">
            <option>--Select--</option>
            <option value="1">List 1</option>
            <option value="2">List 2</option>
            <option value="3">List 3</option>
            <option value="4">List 4</option>
            <option value="5">List 5</option>
            <option value="6">List 6</option>
        </select>
    </td>
    <td>
        <input type="text" class="form-control" name="name[]" placeholder="Name" />
    </td>
</tr>
<tr>
    <td><select class="form-control selectpicker" data-live-search="true" name="reson[]" required="required">
            <option>--Select--</option>
            <option value="1">AAA</option>
            <option value="2">BBB</option>
            <option value="3">CCC</option>
            <option value="4">DDD</option>
            <option value="5">EEE</option>
        </select>
    </td>
    <td>
        <select class="form-control selectpicker" data-live-search="true" name="service[]" id="service" multiple="multiple">
            <option>--Select--</option>
            <option value="1">List 1</option>
            <option value="2">List 2</option>
            <option value="3">List 3</option>
            <option value="4">List 4</option>
            <option value="5">List 5</option>
            <option value="6">List 6</option>
        </select>
    </td>
    <td>
        <input type="text" class="form-control" name="name[]" placeholder="Name" />
    </td>
</tr>
<tr>
    <td><select class="form-control selectpicker" data-live-search="true" name="reson[]" required="required">
            <option>--Select--</option>
            <option value="1">AAA</option>
            <option value="2">BBB</option>
            <option value="3">CCC</option>
            <option value="4">DDD</option>
            <option value="5">EEE</option>
        </select>
    </td>
    <td>
        <select class="form-control selectpicker" data-live-search="true" name="service[]" id="service" multiple="multiple">
            <option>--Select--</option>
            <option value="1">List 1</option>
            <option value="2">List 2</option>
            <option value="3">List 3</option>
            <option value="4">List 4</option>
            <option value="5">List 5</option>
            <option value="6">List 6</option>
        </select>
    </td>
    <td>
        <input type="text" class="form-control" name="name[]" placeholder="Name" />
    </td>
</tr>
</table>

This table rows are generated dynamicaly, its input field values are passed as an array and one of the select box is multiselect

Here is my php code

<?php
extract($_POST);
foreach ($reson as $id => $value) {
    $resona = ($reson[$id]);
    $namep = ($name[$id]);
    $rsid = $ob->insert_data('tbl_reson',array("reson" => $resona, "name" => $namep), true);
    foreach ($service as $ii => $valu) {
        $r_service = ($service[$ii]);
        $ob->insert_data('tbl_service',array("reson_id" => $rsid, "service" => $r_service));
    }
}
?>

Suppose here we have 3 rows and I select two multiple option from first row and three options from second row and four options from third row.

And when inserted into the DB, the selected options become same for all rows (All the options selected in multiselect are grouped into one array and saved into each field).

  • First table

    ------------------------------
        id  |   resn    |   name
    ------------------------------
        1   |   1       |   Test
        2   |   2       |   aaa
        3   |   3       |   bbb
    ------------------------------
    
  • Second Table

    --------------------------------    
        id  |   resnid  |   service
    --------------------------------
        1   |   1       |       1
        2   |   1       |       2
        3   |   2       |       3
        4   |   2       |       4
        5   |   2       |       5
        6   |   3       |       6
    
  • Second Table Current list

    -------------------------------- 
        id  |   resnid  |   service
    --------------------------------
        1   |   1       |       1
        2   |   1       |       2
        3   |   1       |       3
        4   |   1       |       4
        5   |   1       |       5
        6   |   1       |       6
        7   |   2       |       1
        8   |   2       |       2
        9   |   2       |       3
        10  |   2       |       4
        11  |   2       |       5
        12  |   2       |       6
    
  • etc…

But what I need is to insert reson[] and name[] in one table and service in another table based on last inserted id of the first table

Please Help to achieve this.


Get this bounty!!!

#StackBounty: #java #mysql #apache-spark #jdbc #amazon-s3 Converting mysql table to spark dataset is very slow compared to same from cs…

Bounty: 50

I have csv file in Amazon s3 with is 62mb in size (114 000 rows). I am converting it into spark dataset, and taking first 500 rows from it. Code is as follow;

DataFrameReader df = new DataFrameReader(spark).format("csv").option("header", true);
Dataset<Row> set=df.load("s3n://"+this.accessId.replace(""", "")+":"+this.accessToken.replace(""", "")+"@"+this.bucketName.replace(""", "")+"/"+this.filePath.replace(""", "")+"");

 set.take(500)

The whole operation takes 20 to 30 sec.

Now I am trying the same but rather using csv I am using mySQL table with 119 000 rows. MySQL server is in amazon ec2. Code is as follow;

String url ="jdbc:mysql://"+this.hostName+":3306/"+this.dataBaseName+"?user="+this.userName+"&password="+this.password;

SparkSession spark=StartSpark.getSparkSession();

SQLContext sc = spark.sqlContext();

DataFrameReader df = new DataFrameReader(spark).format("csv").option("header", true);
Dataset<Row> set = sc
            .read()
            .option("url", url)
            .option("dbtable", this.tableName)
            .option("driver","com.mysql.jdbc.Driver")
            .format("jdbc")
            .load();
set.take(500);

This is taking 5 to 10 minutes.
I am running spark inside jvm. Using same configuration in both cases.

My issue is not how to decrease the required time as I know in ideal case spark will run in cluster but what I can not understand is why this big time difference in the above two case?


Get this bounty!!!

#StackBounty: #sql #mysql List users, ordered by accuracy of soccer match predictions

Bounty: 100

I have a database filled with predictions of soccer matches. I need a solution to calculate the rankings from the database. There are 2 rankings: one for the entire season (playday=0) and one for each matchday (called playday in the code).

I have 3 tables:

  1. matches
  2. predictions
  3. predictions_points

To give you a better insight in the database, here’s some example data:

matches: contains soccer matches information.

+----------+--------------+---------------------+------------+-----------+----------------+--------------+-----------------+--------------+-----------------+
| match_id | match_status |   match_datetime    | match_info | league_id | league_playday | home_team_id | home_team_score | away_team_id | away_team_score |
+----------+--------------+---------------------+------------+-----------+----------------+--------------+-----------------+--------------+-----------------+
|        1 |            3 | 2016-07-29 20:30:00 |            |         1 |              1 |            1 |               0 |            2 |               2 |
|        2 |            3 | 2016-07-30 18:00:00 |            |         1 |              1 |            5 |               1 |            4 |               2 |
|        3 |            3 | 2016-07-30 20:00:00 |            |         1 |              1 |            3 |               1 |            6 |               0 |
|        4 |            3 | 2016-07-30 20:00:00 |            |         1 |              1 |            7 |               3 |            8 |               0 |
+----------+--------------+---------------------+------------+-----------+----------------+--------------+-----------------+--------------+-----------------+

predictions: contains users predictions and the amount of points received per prediction.

+---------------+----------+---------+-----------------+-----------------+--------------------+
| prediction_id | match_id | user_id | home_team_score | away_team_score | predictions_points |
+---------------+----------+---------+-----------------+-----------------+--------------------+
|             1 |        1 |       1 |               0 |               1 |                  1 |
|             2 |        2 |       1 |               1 |               2 |                  3 |
|             3 |        3 |       1 |               2 |               0 |                  1 |
|             4 |        4 |       1 |               2 |               0 |                  1 |
|             5 |        1 |       2 |               0 |               2 |                  3 |
|             6 |        2 |       2 |               1 |               2 |                  3 |
|             7 |        3 |       2 |               1 |               0 |                  3 |
|             8 |        4 |       2 |               0 |               0 |                  0 |
+---------------+----------+---------+-----------------+-----------------+--------------------+

predictions_points contains the points per playday (or entire season when playday = 0) and the ranking (which we can not use for the query).

+-----------+---------+-----------+----------------+---------------------+---------------+
| points_id | user_id | league_id | league_playday | league_user_ranking | points_amount |
+-----------+---------+-----------+----------------+---------------------+---------------+
|         1 |       1 |         1 |              0 |                   2 |            51 |
|         2 |       2 |         1 |              0 |                   1 |            59 |
|         3 |       1 |         1 |              1 |                   2 |             6 |
|         4 |       2 |         1 |              1 |                   1 |             9 |
+-----------+---------+-----------+----------------+---------------------+---------------+

If there is a draw (in amount of points between users), I want to order them based on the amount of predictions they had 100% correct (they earned 1 point for a prediction with wrong score but correct win/draw/loss – and earned at least 3 points for a correct score).

(Please note that the league_user_ranking field from the predictions_points table gets updated based on the result set of this query. So we can not use it for the query.)

The following query works, but I feel like there’s room for improvement:

     SELECT *, (
        SELECT COUNT(*) FROM predictions p
            INNER JOIN matches m
            ON m.match_id = p.match_id
        WHERE p.user_id=p_p.user_id 
        AND (m.league_playday=p_p.league_playday OR p_p.league_playday=0)
        AND p.prediction_points>=3
     ) AS correctpredictions_count
     FROM
     predictions_points p_p
     WHERE
     p_p.league_id=:league_id
     ORDER BY
     p_p.league_playday ASC, p_p.points_amount DESC, correctpredictions_count DESC

UPDATE/EDIT: I see that my question got bumped to the homepage. I am live-testing the code with 15 other soccer enthousiasts based on the results of the current Belgian soccer season. At the moment, this query takes about 10 seconds on a database with 3000 predictions (15 users, 8 matches per playday, 30 playdays) on a Raspberry Pi 3 running Raspbian Lite.

Expected result set:

    +-----------+---------+-----------+----------------+---------------------+---------------+--------------------------+
    | points_id | user_id | league_id | league_playday | league_user_ranking | points_amount | correctpredictions_count |
    +-----------+---------+-----------+----------------+---------------------+---------------+--------------------------+
    |         2 |       2 |         1 |              0 |                   1 |            59 |                        7 |
    |         1 |       1 |         1 |              0 |                   2 |            51 |                        6 |
    |         4 |       2 |         1 |              1 |                   1 |             9 |                        2 |
    |         3 |       1 |         1 |              1 |                   2 |             6 |                        1 |
    |         5 |       1 |         1 |              2 |                   1 |             7 |                        2 |
    |         6 |       2 |         1 |              2 |                   2 |             7 |                        1 |
    +-----------+---------+-----------+----------------+---------------------+---------------+--------------------------+


Get this bounty!!!

#StackBounty: #mysql #mariadb #sharding #maxscale #proxysql ProxySQL equivalent of MaxScale schemarouter

Bounty: 50

background

My employer developed a web application we provide on software-as-a-service terms to our customers. To allow for multiple customers with a huge mass of data to be stored in a database, we chose to let the application create a schema per tenant. So if we had 5 customers we had something along the lines of

mysql> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| tenant_1           |
| tenant_2           |
| tenant_3           |
| tenant_4           |
| tenant_5           |
+--------------------+

what we have/do now

For now we are good with this and only run a mariadb galera cluster of three nodes behind a maxscale readconnroute balancer. But we will eventually hit a barrier, where adding nodes to this cluster won’t do, because the overall data size won’t fit on disk and/or the amount of tables will kill performance.

To keep the complexity of the applications database layer low, our devs would like us to handle the routing transparent from the viewpoint of the application: they want the application to just to talk to one “server” and not care about where which tenant is located physically.

To expand our application cluster to multiple mariadb galera clusters we could use maxscales schemarouter which exposes all schemas on all connected sub-clusters as if there was only one server. This fits perfectly into our devs expectations.

Now, a few months ago ProxySQL entered the scenery of database proxies and claims better performance paired with greater flexibility among other stuff.

actual question

We can route queries based on hard-coded schema names, but would refrain from doing so as this would mean to create/update them each time a tenant is created/deleted.

How could we replicate the dynamic behaviour of maxscales schemarouter with proxysql query rules, if at all?


Get this bounty!!!

#StackBounty: Repeating the same function in a query

Bounty: 100

In the below query, there are repeated calculations such as the three calls to SUM(p.amount). Does MySQL re-calculate for each function call or is there some kind of memoization optimization under the hood? If not, how can this kind of query be optimized for maximum performance?

It seems like it would be faster after the first calculation to get the next one by the alias name, total_payemnts, but that just throws an error.

SELECT LEFT(c.last_name, 1) AS 'last_names',
    SUM(p.amount) AS 'total_payments', 
    COUNT(p.rental_id) AS 'num_rentals',
    SUM(p.amount) / COUNT(p.rental_id) AS 'avg_pay'
FROM customer c
JOIN payment p ON p.customer_id = c.customer_id
GROUP BY LEFT(c.last_name, 1)
ORDER BY SUM(p.amount) DESC;

This query runs on the MySQL Sakila sample database.


Get this bounty!!!

Apache Commons DbUtils Mini Wrapper

This is a very small DB Connector code in Java as a wrapper class to Apache DBUtils.

The Commons DbUtils library is a small set of classes designed to make working with JDBC easier. JDBC resource cleanup code is mundane, error prone work so these classes abstract out all of the cleanup tasks from your code leaving you with what you really wanted to do with JDBC in the first place: query and update data.

Some of the advantages of using DbUtils are:

  • No possibility for resource leaks. Correct JDBC coding isn’t difficult but it is time-consuming and tedious. This often leads to connection leaks that may be difficult to track down.
  • Cleaner, clearer persistence code. The amount of code needed to persist data in a database is drastically reduced. The remaining code clearly expresses your intention without being cluttered with resource cleanup.
  • Automatically populate Java Bean properties from Result Sets. You don’t need to manually copy column values into bean instances by calling setter methods. Each row of the Result Set can be represented by one fully populated bean instance.

DbUtils is designed to be:

  • Small – you should be able to understand the whole package in a short amount of time.
  • Transparent – DbUtils doesn’t do any magic behind the scenes. You give it a query, it executes it and cleans up for you.
  • Fast – You don’t need to create a million temporary objects to work with DbUtils.

DbUtils is not:

  • An Object/Relational bridge – there are plenty of good O/R tools already. DbUtils is for developers looking to use JDBC without all the mundane pieces.
  • A Data Access Object (DAO) framework – DbUtils can be used to build a DAO framework though.
  • An object oriented abstraction of general database objects like a Table, Column, or Primary Key.
  • A heavyweight framework of any kind – the goal here is to be a straightforward and easy to use JDBC helper library.

Wrapper: