com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

I’m working on getting my database to talk to my Java programs.

Can someone give me a quick and dirty sample program using the JDBC?

I’m getting a rather stupendous error:

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure 
    The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1122)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2260)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:787)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:357)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
    at java.sql.DriverManager.getConnection(DriverManager.java:582)
    at java.sql.DriverManager.getConnection(DriverManager.java:207)
    at SqlTest.main(SqlTest.java:22)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
    The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1122)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:344)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2181)
    ... 12 more
Caused by: java.net.ConnectException: Connection refused
    at java.net.PlainSocketImpl.socketConnect(Native Method)
    at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
    at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
    at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:432)
    at java.net.Socket.connect(Socket.java:529)
    at java.net.Socket.connect(Socket.java:478)
    at java.net.Socket.<init>(Socket.java:375)
    at java.net.Socket.<init>(Socket.java:218)
    at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:256)
    at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:293)
    ... 13 more

Contents of the test file:

import com.mysql.jdbc.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SqlTest {

    public static void main(String [] args) throws Exception {
        // Class.forName( "com.mysql.jdbc.Driver" ); // do this in init
        // // edit the jdbc url 
        Connection conn = DriverManager.getConnection( 
            "jdbc:mysql://localhost:3306/projects?user=user1&password=123");
        // Statement st = conn.createStatement();
        // ResultSet rs = st.executeQuery( "select * from table" );

        System.out.println("Connected?");
    }
}

Duplicate results in mysql query when doing ajax call

SQL Fiddle if you guys wanna mess around with it

I’m unable to reproduce the issue using the fiddle.

Here is the query you can use in the fiddle

SELECT n.*, ns.notification_id AS is_read FROM notifications n
LEFT OUTER JOIN notification_status ns
ON n.id = ns.notification_id
LEFT JOIN notification_user_role nur
ON n.id = nur.notification_id
WHERE
(
  n.esb_consultant_id = 19291
  OR
  n.esb_consultant_id = 'role'
)
AND nur.user_role_id = 'pl_sso_regional_vice_president'
AND n.creation_date <= NOW()
AND n.expiration_date >= NOW()
ORDER BY n.creation_date DESC, (is_read IS NULL) DESC, n.priority ASC
LIMIT 0, 10

I’ve also placed it lower in this post, but here it’s easier to catch the eyes.


I’ll try to keep this as brief as possible.

I’m working on a notification system. I have 3 tables described below.

I’m trying to get notifications with a LIMIT of 10, paginated, 10 per page(so an OFFSET of 10). I’m using ajax to load the next 10.

They are to be ordered by priority(from 1 to 6, 1 being displayed first, while 6th being displayed last).

All unread notifications must be displayed first(priority still applies), while read notifications must be displayed last(priority still applies).

Notifications are per role. A user can have several roles(thus the need for another table).

The notification_status table described below is used to keep track of which notifications are read.

Which ever notification is NOT in the notification_status table is NOT read. This is very important. I didn’t make this decision. I just have to live with it.


To put it into the big picture lets have an example:

Assume we have 14 notifications:

5 of them will be priority 1, unread.

4 of them will be priority > 1, unread.

3 of them will be priority 1, read.

2 of them will be priority > 1, read.


The expected display order is the following.

5 unread priority 1

4 unread priority > 1

1 read priority 1

ajax starts here since we have 10 per page

2 read priority 1

2 read priority > 1


The table structure is as follows.

notifications

+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| id                | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| type_id           | int(10) unsigned | NO   |     | NULL    |                |
| sticky            | int(10) unsigned | NO   |     | NULL    |                |
| priority          | int(10) unsigned | NO   |     | NULL    |                |
| esb_consultant_id | varchar(40)      | NO   |     |         |                |
| message_id        | varchar(100)     | NO   |     |         |                |
| esb_params        | varchar(255)     | YES  |     |         |                |
| creation_date     | datetime         | YES  |     | NULL    |                |
| expiration_date   | datetime         | YES  |     | NULL    |                |
+-------------------+------------------+------+-----+---------+----------------+

notification_user_role

+-----------------+------------------+------+-----+---------+-------+
| Field           | Type             | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| user_role_id    | varchar(150)     | NO   |     |         |       |
| notification_id | int(10) unsigned | NO   | MUL | NULL    |       |
+-----------------+------------------+------+-----+---------+-------+

notification_status

+-------------------+------------------+------+-----+---------+-------+
| Field             | Type             | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+-------+
| esb_consultant_id | varchar(20)      | NO   |     |         |       |
| notification_id   | int(10) unsigned | NO   | MUL | NULL    |       |
+-------------------+------------------+------+-----+---------+-------+

The query I’m using to retrieve the results:

SELECT n.*, ns.notification_id AS is_read FROM notifications n
LEFT OUTER JOIN notification_status ns
ON n.id = ns.notification_id
LEFT JOIN notification_user_role nur
ON n.id = nur.notification_id
WHERE 
(
  n.esb_consultant_id = :consultant_id 
  OR 
  n.esb_consultant_id = :role_all
)
AND nur.user_role_id = :consultant_role
AND n.creation_date <= NOW()
AND n.expiration_date >= NOW()
ORDER BY n.creation_date DESC, (is_read IS NULL) DESC, n.priority ASC
LIMIT $offset, $limit

$offset is the page times 10 – so if the page is 0(first page) the offset is 0, if the page is 1(first ajax call) the offset is 10 and so on

$limit is the limit, it’s always 10.

:consultant_id is the user id – unique

:role_all is a simple string all. It is used for when certain notifications are for all roles(such as a birthday notification). All users have this notification, regardless of role since all of them have a birthday.


The problem:

Whenever I’m doing an ajax call I get certain notifications that are duplicated. I’ll just post a screenshot of it since it’s easier than drawing it.

Do note that the ajax itself is just part of the way I’m retrieving the results but is not responsible for the duplicates themselvs, I’m absolutely sure. It’s not a display issue either, I’ve double and triple checked that.

Before Ajax

After ajax

What I’ve noticed is that if I were to remove this part

ORDER BY n.creation_date DESC, (is_read IS NULL) DESC, n.priority ASC

from the query. It works fine. No duplicates.

The dump of the query above, with limit removed and order by removed:

Dump

Sorry for the image but it’s easier.


I’m using PHP to query the database.

  public function all($consultant_id, $consultant_role, $offset = 0) {
    $limit = 10;
    $offset = $offset * 10;

    $query = <<<SQL
SELECT n.*, ns.notification_id AS is_read FROM notifications n
LEFT OUTER JOIN notification_status ns
ON n.id = ns.notification_id
LEFT JOIN notification_user_role nur
ON n.id = nur.notification_id
WHERE 
(
  n.esb_consultant_id = :consultant_id 
  OR 
  n.esb_consultant_id = :role_all
)
AND nur.user_role_id = :consultant_role
AND n.creation_date <= NOW()
AND n.expiration_date >= NOW()
ORDER BY n.creation_date DESC, (is_read IS NULL) DESC, n.priority ASC
LIMIT $offset, $limit
SQL;
    $return = $this->connection
      ->query($query
        , [
          ':consultant_role' => $consultant_role,
          ':consultant_id'   => $consultant_id,
          ':role_all'        => NotificationStatus::PL_N_ALL,
        ]
      )->fetchAll(\PDO::FETCH_ASSOC);

    foreach($return as $item) { // this is added simply for display purposes
      echo $item['id'] . '<br>';
    }

    return $return;
  }

The above is a copy+paste of the code used to retrieve the results. The function simply returns the results for displaying, no other magic going around.

The foreach is added to simply display the results in the browser.

Here is the image of the output. Notification 10 is duplicated.

Here is the exact same code only with
ORDER BY n.creation_date DESC, (is_read IS NULL) DESC, n.priority ASC removed. Limit and offset still apply here.


I’m not that good at mysql or sql in general and I’m not sure where the problem itself lies.

Any pointing in the right direction is greatly appreciated. Even workarounds or “hacks” I’m fine with.

Mysql: Count wins but only once per opponent

I’m looking for help using sum() in my SQL query:

Task: Count tournament wins of all players. (one number per player) (battles.result = 1 means Player1 wins)

SELECT members.id, members.name,
            (
                SELECT SUM(battles.result = 1)
                FROM battles
                WHERE members.id = battles.player1 AND battles.result=1 order by battles.gametime
            (   as wins,
FROM members

Next: Only count ONE result per two players.

So if there are multiple results of two players, count only the first result (first gametime).

I’ve already tried using order by battles.player2, but i guess there is a much better solution?

Get all record from one table plus the relative last record of a second table

Situation (simplified):

tableA

id  | id_device
----+----------
... | 10
... | 11
... | 12

tableB

id  | id_device | val
----+-----------+----
... | 10        | 200
... | 10        | 105
... | 10        | 120
... | 11        | 80

Result expected: All the records from tableA, and for each record of tableA, the last relative record on tableB.

id_device | val 
----------+-----
10        | 120
11        | 80
12        | null

I tried to something like this, but I can’t get the last record correctly:

SELECT tableA.*,
       tableB.* 
FROM tableA
LEFT JOIN (
    SELECT id_device,
           val,
           MAX(id) 
    FROM tableB
    GROUP BY id_device
) AS tableB
     ON tableA.id_device = tableB.id_device

Delete woocommerce products associated to categories

I have deleted all the products from my store using these query:

DELETE FROM wp_term_relationships WHERE object_id IN 
(SELECT ID FROM wp_posts WHERE post_type IN ('product','product_variation'));
DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type IN 
('product','product_variation'));
DELETE FROM wp_posts WHERE post_type IN ('product','product_variation');

Then I ran this to delete orphaned postmeta:

DELETE pm
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id[![enter image description here][1]][1]
WHERE wp.ID IS NULL

Even though all the products were delete I still have products associated to the categories:

enter image description here

I don’t want to delete the categories (because it would destroy the vast menu on the website that I have built). I just want to delete the products associated to the categories.

So what I would like to achieve would be this:
enter image description here

How can I do that without messing up the categories itself?

Thank you in advance!

Stored Procedure in MySQL not working on c# page

I have this Stored Procedure Insert_new_user in db MySQL for check existing email address before to register new user in the db table.

When launch manually this SP I don’t have error on MySQL.

Time: 00:00.03

Procedure executed successfully Affected rows: 1

BEGIN 
    DECLARE Z_Name TEXT;
    DECLARE Z_Email TEXT;
    DECLARE Z_COD TEXT DEFAULT NULL;
    DECLARE Z_Mega TEXT;
    DECLARE rValue INT DEFAULT NULL;

   IF EXISTS (SELECT mail_Z_Mega FROM Z_tbl WHERE mail_Z_Mega = Z_Email) THEN
      SET rValue = 1;
   ELSE 
      INSERT INTO Z_tbl(Z_Name, mail_Z_Mega, Z_COD, Z_Mega) VALUES (Z_Name, Z_Email, Z_COD, Z_Mega);
      SET rValue = 0; 
   END IF;
END

Now I need use this SP in a c# page :

protected void RegisterUser(object sender, EventArgs e)
{
    int Id = 0;

    using (OdbcConnection cn =
      new OdbcConnection(ConfigurationManager.ConnectionStrings["Conn"].ConnectionString))
    {
        using (OdbcCommand cmd =
            new OdbcCommand("Insert_new_user", cn))
        {
            try
            {
                using (OdbcDataAdapter sda = 
                    new OdbcDataAdapter())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("param1", txtZ_Name.Text.Trim());
                    cmd.Parameters.AddWithValue("param2", txtZ_Email.Text.Trim());
                    cmd.Parameters.AddWithValue("param3", txtZ_COD.Text.Trim());
                    cmd.Parameters.AddWithValue("param4", ddlZ_Mega.SelectedValue);
                    cn.Open();
                    Id = Convert.ToInt32(cmd.ExecuteScalar());
                    cn.Close();
                }

            }
            catch (Exception ex)
            {
                Response.Write("ERROR: Unable to save data !!</br>" + ex.Message);
            }
            finally
            {

            }
        }
    }
}

The values of form c# are all correct.

But I have MySQL error :

ERROR: Unable to save data !!
ERROR [42000] [MySQL][ODBC 5.2(w) Driver][mysqld-5.0.41-community-nt]
You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near 'Insert_new_user' 
at line 1 

How to do resolve this?

Can you help me?

Thank you in advance for any help, really appreciated.

untypical query, necessary index, optimization

select ip.ns, ip.u from iparts ip where ip.isF = 0 and ((ip.wl < ?1 and ip.wl > ?2) or (ip.wf = 0 and ip.c < ?1 and ip.c > ?2))

ip.isF = 0 – this part is the most selective, 95% of records has isF = 1, but this table will be really big

How to optimize this query?
Could you clarify which index I should add to table – iparts?

Convert MySQL Query to Laravel query

I would to convert mysql query to laravel query.

MySQL:

SELECT * FROM scadenze WHERE created_at IN(SELECT MAX(created_at) FROM scadenze GROUP BY processo_id)

Laravel ( i got an error, Object of class stdClass could not be converted to string):

DB::table('scadenze')->whereIn('created_at', DB::select("( SELECT MAX(created_at) FROM scadenze GROUP BY processo_id )"))->get();

Golang how to use multiple config files for database testing

I have a Go lambda function that use a repository to store a user. The main package calls repository.Store to save a user.
Now on the repository.Store it will decode a toml file and sql.Open a new connection and then insert the user on database.

My issue is on for testing saving to a docker MySQL database, I can’t figure out how to have multiple toml files for each environment. When repository.Store is called it will always use the config file that I have there.

I found that may be possible to have a global sql variable, but also that this is bad. Being from a PHP background I’d Inject the config file, but I can’t see how to do something like that in my code.

main.go:

func Create(request events.APIGatewayProxyRequest) (events.APIGatewayProxyResponse, error) {
    var myUser user.User

    err := json.Unmarshal([]byte(request.Body), &myUser)

    repository.Store(myUser)

    return events.APIGatewayProxyResponse{Body: "OK", StatusCode: 200}, nil
}

func main() {
    lambda.Start(Create)
}

repository:

func Store(user user.User) {
    var conf Config
    _, err := toml.DecodeFile("./config.toml", &conf)

    conString := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s", conf.Database.User,...)

    db, err := sql.Open("mysql", conString)

    // insert
    stmt, err := db.Prepare("INSERT INTO users(username) VALUES username=?")
    _, err = stmt.Exec(user.Username)
}

type Config struct {
    Database database
}

type database struct {
    Server   string
    Port     string
    Database string
    User     string
    Password string
}

Table Adapter error with where clause

I am using a table adapter to query a MySQL database, I have query which works fine mysql workbench.

When I add the query to the table adapter I get the following error

Error in list of function arguments ‘1’ not recognisedandError in list of function arguments ‘0’ not recognisedalsoError in list of function arguments ‘AS’ not recognised

Query

SELECT
 a.*,
 at.AuditTypeName,
 s.StaffForename,
 s.StaffSurname
FROM Audits a LEFT JOIN AuditTypes at ON a.AuditTypeID = at.AuditTypeID
 LEFT JOIN Staff s ON a.StaffID = s.StaffID
WHERE date(a.AuditDate) BETWEEN CAST(DATE_FORMAT(DATE_SUB((DATE_SUB(Now(), INTERVAL 1 MONTH)), INTERVAL 0 DAY) ,'%Y-%m-01') as DATE) AND Now()
ORDER BY a.AuditID DESC