mariadb – Broken connection to the MySQL server during handshake after truncating the table

I used the latest version of Heidi SQL over an SSH tunnel to truncate a 2.5 GB table. If I clicked "Cut MSQL", the connection was immediately interrupted and it has not started since then, regardless of whether I restart the server.

I tried to look for a solution, everyone recommends disabling the firewall, which I did and didn't change anything.

When I try to start MYSQL, I get the following error message: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)

Here is the output from systemctl status mariadb.service:

mariadb.service - MariaDB 10.2.30 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf
   Active: failed (Result: exit-code) since Fri 2020-01-17 20:58:40 UTC; 4min 12s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 3484 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS --basedir=/usr $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
  Process: 3271 ExecStartPre=/bin/sh -c ( ! -e /usr/bin/galera_recovery ) && VAR= ||   VAR=`/usr/bin/galera_recovery`; ( $? -eq 0 )   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
  Process: 3268 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 3484 (code=exited, status=1/FAILURE)
   Status: "MariaDB server is down"

Jan 17 20:58:33 server-2 systemd(1): Starting MariaDB 10.2.30 database server...
Jan 17 20:58:34 server-2 mysqld(3484): 2020-01-17 20:58:34 140068938262720 (Note) /usr/sbin/mysqld (mysqld 10.2.30-MariaDB) starting as process 3484 ...
Jan 17 20:58:40 server-2 systemd(1): mariadb.service: main process exited, code=exited, status=1/FAILURE
Jan 17 20:58:40 server-2 systemd(1): Failed to start MariaDB 10.2.30 database server.
Jan 17 20:58:40 server-2 systemd(1): Unit mariadb.service entered failed state.
Jan 17 20:58:40 server-2 systemd(1): mariadb.service failed.

And the issue of journalctl -xe:

-- Subject: Unit session-6.scope has finished start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit session-6.scope has finished starting up.
--
-- The start-up result is done.
Jan 17 21:01:01 server-2 CROND(3798): (root) CMD (run-parts /etc/cron.hourly)
Jan 17 21:01:01 server-2 run-parts(/etc/cron.hourly)(3801): starting 0anacron
Jan 17 21:01:01 server-2 run-parts(/etc/cron.hourly)(3807): finished 0anacron
Jan 17 21:01:46 server-2 sshd(3896): reverse mapping checking getaddrinfo for test-whatup.inet.co.th (203.150.221.195) failed - POSSIBLE BREAK-IN ATTEMPT!
Jan 17 21:01:46 server-2 sshd(3896): pam_unix(sshd:auth): authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=203.150.221.195  user=root
Jan 17 21:01:46 server-2 sshd(3896): pam_succeed_if(sshd:auth): requirement "uid >= 1000" not met by user "root"
Jan 17 21:01:47 server-2 sshd(3900): Invalid user mao from 157.245.243.4 port 57002
Jan 17 21:01:47 server-2 sshd(3900): input_userauth_request: invalid user mao (preauth)
Jan 17 21:01:47 server-2 sshd(3900): pam_unix(sshd:auth): check pass; user unknown
Jan 17 21:01:47 server-2 sshd(3900): pam_unix(sshd:auth): authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=157.245.243.4
Jan 17 21:01:48 server-2 sshd(3896): Failed password for root from 203.150.221.195 port 44978 ssh2
Jan 17 21:01:48 server-2 sshd(3896): Received disconnect from 203.150.221.195 port 44978:11: Bye Bye (preauth)
Jan 17 21:01:48 server-2 sshd(3896): Disconnected from 203.150.221.195 port 44978 (preauth)
Jan 17 21:01:49 server-2 sshd(3900): Failed password for invalid user mao from 157.245.243.4 port 57002 ssh2
Jan 17 21:01:49 server-2 sshd(3900): Received disconnect from 157.245.243.4 port 57002:11: Bye Bye (preauth)
Jan 17 21:01:49 server-2 sshd(3900): Disconnected from 157.245.243.4 port 57002 (preauth)
Jan 17 21:02:21 server-2 sshd(3958): Invalid user ts3 from 41.224.59.78 port 38484
Jan 17 21:02:21 server-2 sshd(3958): input_userauth_request: invalid user ts3 (preauth)
Jan 17 21:02:21 server-2 sshd(3958): pam_unix(sshd:auth): check pass; user unknown
Jan 17 21:02:21 server-2 sshd(3958): pam_unix(sshd:auth): authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=41.224.59.78
Jan 17 21:02:23 server-2 sshd(3958): Failed password for invalid user ts3 from 41.224.59.78 port 38484 ssh2
Jan 17 21:02:23 server-2 sshd(3958): Received disconnect from 41.224.59.78 port 38484:11: Bye Bye (preauth)
Jan 17 21:02:23 server-2 sshd(3958): Disconnected from 41.224.59.78 port 38484 (preauth)
Jan 17 21:02:26 server-2 sshd(3965): Invalid user bill from 172.81.210.86 port 54268
Jan 17 21:02:26 server-2 sshd(3965): input_userauth_request: invalid user bill (preauth)
Jan 17 21:02:26 server-2 sshd(3965): pam_unix(sshd:auth): check pass; user unknown
Jan 17 21:02:26 server-2 sshd(3965): pam_unix(sshd:auth): authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=172.81.210.86
Jan 17 21:02:28 server-2 sshd(3965): Failed password for invalid user bill from 172.81.210.86 port 54268 ssh2
Jan 17 21:02:28 server-2 sshd(3965): Received disconnect from 172.81.210.86 port 54268:11: Bye Bye (preauth)
Jan 17 21:02:28 server-2 sshd(3965): Disconnected from 172.81.210.86 port 54268 (preauth)
Jan 17 21:02:38 server-2 sshd(3979): Invalid user dropbox from 207.154.218.16 port 41892
Jan 17 21:02:38 server-2 sshd(3979): input_userauth_request: invalid user dropbox (preauth)
Jan 17 21:02:38 server-2 sshd(3979): pam_unix(sshd:auth): check pass; user unknown
Jan 17 21:02:38 server-2 sshd(3979): pam_unix(sshd:auth): authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=207.154.218.16
Jan 17 21:02:39 server-2 sshd(3979): Failed password for invalid user dropbox from 207.154.218.16 port 41892 ssh2
Jan 17 21:02:39 server-2 sshd(3979): Received disconnect from 207.154.218.16 port 41892:11: Bye Bye (preauth)
Jan 17 21:02:39 server-2 sshd(3979): Disconnected from 207.154.218.16 port 41892 (preauth)
Jan 17 21:02:58 server-2 sshd(4028): reverse mapping checking getaddrinfo for d1-4-232-143-118-on-nets.com (118.143.232.4) failed - POSSIBLE BREAK-IN ATTEMPT!
Jan 17 21:02:58 server-2 sshd(4028): Invalid user rasa from 118.143.232.4 port 45328
Jan 17 21:02:58 server-2 sshd(4028): input_userauth_request: invalid user rasa (preauth)
Jan 17 21:02:58 server-2 sshd(4028): pam_unix(sshd:auth): check pass; user unknown
Jan 17 21:02:58 server-2 sshd(4028): pam_unix(sshd:auth): authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=118.143.232.4
Jan 17 21:03:00 server-2 sshd(4028): Failed password for invalid user rasa from 118.143.232.4 port 45328 ssh2
Jan 17 21:03:00 server-2 sshd(4028): Received disconnect from 118.143.232.4 port 45328:11: Bye Bye (preauth)
Jan 17 21:03:00 server-2 sshd(4028): Disconnected from 118.143.232.4 port 45328 (preauth)
Jan 17 21:03:22 server-2 sshd(4075): pam_unix(sshd:auth): authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=122.51.198.248  user=root
Jan 17 21:03:22 server-2 sshd(4075): pam_succeed_if(sshd:auth): requirement "uid >= 1000" not met by user "root"
Jan 17 21:03:23 server-2 sshd(4075): Failed password for root from 122.51.198.248 port 35926 ssh2
Jan 17 21:03:23 server-2 sshd(4075): Received disconnect from 122.51.198.248 port 35926:11: Bye Bye (preauth)
Jan 17 21:03:23 server-2 sshd(4075): Disconnected from 122.51.198.248 port 35926 (preauth)
Jan 17 21:03:50 server-2 sshd(4127): pam_unix(sshd:auth): authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=41.224.59.78  user=root
Jan 17 21:03:50 server-2 sshd(4127): pam_succeed_if(sshd:auth): requirement "uid >= 1000" not met by user "root"
Jan 17 21:03:52 server-2 sshd(4127): Failed password for root from 41.224.59.78 port 51712 ssh2
Jan 17 21:03:53 server-2 sshd(4127): Received disconnect from 41.224.59.78 port 51712:11: Bye Bye (preauth)
Jan 17 21:03:53 server-2 sshd(4127): Disconnected from 41.224.59.78 port 51712 (preauth)

What is going on here, why did it happen and how can I fix it?

Can I set up a standard Apache Page / Wildcard name server for shared hosting?

Sorry experts for the seemingly noob question, but I'm stuck:

So we have shared hosting on cpanel with a dedicated IP on example.com. We would like a standard HTML file to be uploaded somewhere, which will be provided for all domains that use ns1.example.com, ns2.example.com as a name server without us making a DNS entry of this domain on our server have to. Is it called the standard Apache site?

To clarify: We registered ns1.example.com and ns2.example.com as name servers (glue data records) by referring them to the dedicated IP of our hosting. What configuration do we have to make on our server so that randomdomain1.com, randomdomain2.com … randomdomain100.com displays the standard file without having to make any entries for randomdomains on our server? Similar to domain parking garages.

Possible?

Connect Java to SQL Server

I want to remotely establish a Java connection (on one PC) with an SQL server (on another PC), I have already done this from Windows to Windows.

But I want to try Ubuntu, Java NetBeans on one PC with Ubuntu and SQL Server on another PC with Windows.

It doesn't allow me to ping the two machines between them, but when I start the program, I am informed that there is an error that no connection can be made to port 1433, which checks whether no instance is occupied or whether a firewall is active.

The firewall has already failed and is still reporting errors, someone can help me.

SQL Server – Query execution sometimes takes 25 seconds

After executing a series of common data access queries, SQL Server 2019 is placed in a state where it takes almost exactly 25 seconds to execute a particular query. Under normal conditions, it takes 200 ms.

If I change the query in a seemingly independent way (ORDER BY, remove some columns from the SELECT list or the AND condition), it will run normally. When I restore the original shape, it takes another 25 seconds.

SQL Server maintains this status for approximately 10 runs of the query. Then it continues as normal. No blocking or deadlock traces in SQL Profiler.

Why could this happen and how can the problem be tracked?

SQL Server – How can I join a row from the left table regardless of how many matches I get from the right table?

I have two tables – one is a data table and the other is a mapping table. I want to put them together but just keep the data from the left table. However, it is possible that the mapping table contains multiple records that match a single record in the data table. I cannot use DISTINCT because the data table may contain identical rows, and I want to keep the same number of rows from this table in the result set.

Here is an example of the data I'm working with:

       DataTable                           MappingTable
+-----+-----+-----+-----+           +------+------+------+------+
| ID1 | ID2 | ID3 | ID1 |           | ID1  | ID2  | ID3  | ID1  |
+-----+-----+-----+-----+           +------+------+------+------+
|  1  |  1  |  1  |  1  |           |  1   | NULL | NULL | NULL |
|  1  |  1  |  1  |  1  |           | NULL | NULL | NULL |  1   |
|  2  |  1  |  1  |  1  |           |  3   |  3   | NULL | NULL |
|  3  |  1  |  1  |  3  |           +------+------+------+------+
|  4  |  1  |  1  |  4  |
|  2  |  2  |  1  |  1  |
|  3  |  2  |  1  |  3  |
|  3  |  3  |  1  |  3  |
|  2  |  1  |  0  |  1  |
|  2  |  1  |  0  |  1  |
|  4  |  3  |  2  |  3  |
+-----+-----+-----+-----+

Below is the shortcut I am using. I wrote a custom function to handle the NULL matching behavior, which I include here as well.

SELECT * FROM DataTable P
JOIN MappingTable M ON dbo.fNullMatchCheckIntS(P.ID1,M.ID1,0,1) = 1
    AND dbo.fNullMatchCheckIntS(P.ID2,M.ID2,0,1) = 1
    AND dbo.fNullMatchCheckIntS(P.ID3,M.ID3,0,1) = 1
    AND dbo.fNullMatchCheckIntS(P.ID4,M.ID4,0,1) = 1

CREATE FUNCTION dbo.fNullMatchCheckIntS (
    @Value1 INT
    ,@Value2 INT
    ,@AutoMatchIfValue1IsNull BIT
    ,@AutoMatchIfValue2IsNull BIT
)
    RETURNS BIT
AS

BEGIN

    DECLARE @Result BIT = 0

    SELECT
        @AutoMatchIfValue1IsNull = ISNULL(@AutoMatchIfValue1IsNull,0)
        ,@AutoMatchIfValue2IsNull = ISNULL(@AutoMatchIfValue2IsNull,0)

    IF
        (@AutoMatchIfValue1IsNull = 1 AND @Value1 IS NULL)
        OR (@AutoMatchIfValue2IsNull = 1 AND @Value2 IS NULL)
        OR @Value1 = @Value2
        OR (@Value1 IS NULL AND @Value2 IS NULL)
    BEGIN
        SET @Result = 1
    END

    RETURN @Result
END

The problem with how the join works is that the first two lines in the DataTable match the first two lines in the MappingTable. The result contains four identical records, but I only want 2. I know I can add an identity. Click the column in the data table, and then use DISTINCT or PARTITION to get the result you're looking for. However, I would like to avoid this route if possible.

EDIT: I found a way to do this with EXISTS, but it looks a little ugly in my opinion. I'm still interested in other answers if someone has an idea. Thanks a lot!

SELECT * FROM DataTable D
WHERE EXISTS (
    SELECT D.ID1, D.ID2, D.ID3, D.ID4
    FROM MappingTable M 
    WHERE dbo.fNullMatchCheckIntS(D.ID1,M.ID1,0,1) = 1
        AND dbo.fNullMatchCheckIntS(D.ID2,M.ID2,0,1) = 1
        AND dbo.fNullMatchCheckIntS(D.ID3,M.ID3,0,1) = 1
        AND dbo.fNullMatchCheckIntS(D.ID4,M.ID4,0,1) = 1
)

gameserver – I made a server-client-TCP connection, but this only works if the server and client are on the same computer. Any ideas?

So I want to start by being an absolute beginner in networking.

I create a game in C ++ and use SFML for networking (both the server and the client are in C ++, SFML). And I successfully created a server-client connection so I can send packets from one to the other. However, this only works if the server and the client are on the same computer (or on the same Internet connection (router)).

What I want to achieve is a server on my computer that can connect to my friends (who are not on the same router) after starting.

I tried to forward the port I'm using from the PC the server is running on, but it doesn't seem to work.

Any ideas?

SQL Server – Are usage statistics for clustered indexes skewed because they are referenced in non-clustered keys?

When using tools like SP_BlitzIndex and viewing usage statistics for indexes. Would the use of clustered indexes be distorted in any way because they are secret keys in all non-clustered indexes and would be read more often than not?

Read usage is high when trying to determine whether a table has the correct clustered index, but when I look at how data is accessed, I don't see many direct links that use the clustered index column that me would make you believe the usage numbers.

tls – Can I use the same truststore and keystore on the client and server?

TL; DR: Is it possible that both sides of an SSL / TLS connection have the same public and private key, as long as this public key is classified as trustworthy by both sides?

Further information, if helpful:

I'm trying to test how I set up the Java application that connects to Kafka 2.1. I use the same keystore and truststore for the Kafka cluster and the Java application, ie both sides of the connection have the same public and private key. I understand that this is not a good practice. I just wanted to check that I understood how to configure things.

I can hire

listeners=PLAINTEXT://localhost:9092,SSL://localhost:9093
# This is the config style for 2.3
# listeners=HTTP://localhost:9092,HTTPS://localhost:9093
ssl.keystore.location=/path/to/kafka.keystore.jks
ssl.keystore.password=test

ssl.truststore.location: /path/to/kafka.truststore.jks
ssl.truststore.password: test

ssl.key.password=test

A number of properties in Kafka to enable TLS / SSL authentication. I am confident that I have created the truststore and the keystore correctly, with the truststore containing the 1 public key that the Java side and the Kafka side use. I did the same on the Java side, roughly after a GitHub example.

I can't connect and am trying to remove options for why. Could this be because both sides of the connection have the same public and private key?

How can you make DDL changes to large tables and cause minimal server and object conflicts?

I work with many large tables (billions to dozens of billions of records) in a database that I recently inherited. I see a few clear DDL changes that would benefit the database use cases, but I find it difficult to implement because the database can only afford minimal conflicts. (If a heavy query runs for more than a minute or two, it must be canceled.)

Even during a maintenance window, these changes would be far too long and would exceed my allotted time (at most 1 hour, since there are actually no free hours).

The changes I want to make include creating indexes, adding permanent columns, creating indexed views, and general index optimization. If there was a way to do one of these operations iteratively and pause between iterations, I could take all the time because I can at least allow other processes to run in between instead of building a backlog.

The only idea that comes to my mind is if I maintain a copy of the database on a separate server where I can make DDL changes and then point my applications to that server. Then update the first server with the DDL changes so that it is in sync. The next time I need to update, I can repeat the process.