Mysql get records more then 3 in interval of 1 minute, return group_concat ID

Currently i have this dataset, i need to return grouped ids that are within the range of 60 seconds and have more than 3.

CREATE TABLE test  (   `id` bigint NOT NULL AUTO_INCREMENT,   created_date TIMESTAMP(1) NOT NULL,   origin_url   VARCHAR (200) NOT NULL,   client_session_id VARCHAR (50) NOT NULL,   PRIMARY KEY (`id`),   UNIQUE KEY `UC_PRE_CHAT_TALKID_COL` (`id`) ); 
INSERT INTO test VALUES (1,'2021-01-18 11:02:24.0', 'https://zendes.com/', 'znkjoc3gfth2c3m0t1klii'), (2,'2021-01-18 11:02:35.0', 'https://zendes.com/', 'znkjoc3gfth2c3m0t1klii'), (3,'2021-01-18 11:02:03.0', 'https://zendes.com/', 'znkjoc3gfth2c3m0t1klii'), (4,'2021-01-18 11:11:28.0', 'https://rarara.com/', 'znkjoc3gfth2c3m0t1klii'), (5,'2021-01-18 11:11:36.0', 'https://rarara.com/', 'znkjoc3gfth2c3m0t1klii'), (6,'2021-01-18 11:11:05.0', 'https://rarara.com/', 'znkjoc3gfth2c3m0t1klii'); 

db<>fiddle here

something like this:

ids     origin_url              client_session_id 1,2,3   https://testett.com/    znkjoc3gfth2c3m0t1klii 4,5,6   https://rarara.com/     znkjoc3gfth2c3m0t1klii 

mysql partition by date and id

At the moment I’m partitioning my table daily by

PARTITION BY RANGE COLUMNS(date) 

On a daily basis I load data into that table, I have billions of rows in that table. I was thinking on switch to exchange partition for better performance. However the data is getting from multiple sources at different times. I do however can do exchange partition if I could do partition the table by (date, sourceId).

Is that possible? Do I have to use sub-partitions or is there other ways to solve it?

Can i use a string as an end of line delimeter when importing a csv to mysql?

Very basic question but im struggling with it. When i am importing a csv file to mysql is it possible to use any character or string as an end of line delimeter? If so is it possible from the admin window ‘Import’? Or must i do so in a sql query (doesnt seem logical).

So far i have had issues using everything besides \n and auto in the php my admin window. I tried the € sign i tried ///. Didnt work and got errors relating to an incorrect end of line delimeter.
Any help would be much appreciated. Thank you.

Does mysql “order by” guarantees to give the same order for the same query every time if the sort key is equal?

I’m planning to write a query that sorts the result by a value s and then paginate the result. Let’s say I have ten items that match the query and all the items have the same s. In the first query, I sort the value by s and get the first five items. Then in the next query, I sort the value by s and get the sixth to tenth items. Is it possible that the items that appear on my first query will appear again in my second query, and that some items will not show up in either query?

Mysql: Any way to properly index 3 ENUM columns with the same options ? (A OR B OR C)

I have 3 enum() columns with the same option values inside.
I first tried to use the "set" datatype which originally was meant to do that (hold multiple values from a set) but it seems that datatype isn’t managed for 15+ years and isn’t even supporting an index.
Is there a nice way to index those 3 columns so I can use them in searched without destroying query performance ?

SELECT * FROM TABLE WHERE a=’x’ OR b=’x’ OR c=’x’

I thought about creating a virtual field which uses a boolean logic (&) on the 3 enum field-numbers and combines them into a large number but that’s quite a hack and not nice to maintain.

Has someone solved this sort of task elegantly ? (I do not want to use a support table and JOIN it, I want to stay with a single table)

Handle multiple simultaneous requests Mysql

sorry for this noob question. I do have an application which expects about 5000 users accessing it simultaneously, my current database is running on RDS and for each request, a query is called, it takes about 30 milliseconds to be executed.

The main caveat is when we open multiple connections the CPU database spikes to 100% and the app starts to getting timeout error.

What solution would be possible to handle so many requests?

Architecture:

  • 3 EC2 db.r5.16xlarge(db.t3.large) running behind a load balancer
  • 1 RDS (db.t3.2xlarge) MySql 5.7

enter image description here

Problems with MySQL Table ‘.\mysql\db’

Been having problems with MySQL, it wont launch at all, i just get error messages

Ive tried changing ports and deleting files etc. Nothing i try has worked at all Logs look normal but when i look at windows event viewer i get the following three messages

"mysqld.exe: Table ‘.\mysql\db’ is marked as crashed and last (automatic?) repair failed "

"Fatal error: Can’t open and lock privilege tables: Table ‘.\mysql\db’ is marked as crashed and last (automatic?) repair failed " "Aborting "

How would i fix this issue? All help is appreciated

MYSQL optimization for Magento 2

Im running my magento 2 project on 3 core 8gb ram VM. I did some benchmarks and i see that my DB queries take quite some time to execute. Traffic on this site is almost zero. Mysql is running on 5.7.31-34 Percona Server. Database size 1.7gb. Can anyone spot some obvious mistakes and give me some suggestions ?

2947 queries in 1,057.20ms (average time: 0.36ms) - 2788 queries/second 2912 SELECT - 0 INSERT - 0 UPDATE - 0 DELETE - 0 TRANSACTION   2618 queries in 1,132.66ms (average time: 0.43ms) - 2311 queries/second 2593 SELECT - 0 INSERT - 0 UPDATE - 0 DELETE - 0 TRANSACTION  2915 queries in 1,213.47ms (average time: 0.42ms) - 2402 queries/second 2878 SELECT - 0 INSERT - 0 UPDATE - 0 DELETE - 0 TRANSACTION   620 queries in 235.52ms (average time: 0.38ms) - 2633 queries/second 610 SELECT - 0 INSERT - 0 UPDATE - 0 DELETE - 0 TRANSACTION  3214 queries in 1,930.16ms (average time: 0.60ms) - 1665 queries/second 3179 SELECT - 0 INSERT - 0 UPDATE - 0 DELETE - 0 TRANSACTION 

At moment my.cnf is …

[mysqld] user   = mysql pid-file = /var/run/mysqld/mysqld.pid socket   = /var/run/mysqld/mysqld.sock port   = 3306 basedir    = /usr datadir    = /var/lib/mysql tmpdir   = /tmp lc-messages-dir  = /usr/share/mysql explicit_defaults_for_timestamp   ### MyISAM # key_buffer_size = 16M myisam-recover-options = FORCE,BACKUP  ### SAFETY # innodb = force max_allowed_packet = 250M max_connect_errors = 100000 bind-address = 127.0.0.1 skip-name-resolve  ### LANGUAGE # #init_connect='SET collation_connection = utf8_unicode_ci' #init_connect='SET NAMES utf8' #character-set-server=utf8 #collation-server=utf8_unicode_ci #skip-character-set-client-handshake  ### CACHES AND LIMITS # back_log = 20 interactive_timeout = 7200 wait_timeout = 7200 net_read_timeout = 120 net_write_timeout = 300 sort_buffer_size = 8M read_buffer_size = 8M  read_rnd_buffer_size = 2M join_buffer_size = 16M tmp_table_size = 512M  max_heap_table_size = 512M query_cache_type = 1 query_cache_size = 128M max_connections = 60 thread_cache_size = 32 thread_pool_size = 16 open_files_limit = 65535 table_definition_cache = 8000 table_open_cache = 10000  ### INNODB_ # innodb_thread_concurrency = 0 innodb_lock_wait_timeout = 7200 innodb_flush_method = O_DIRECT innodb_flush_log_at_trx_commit = 2 innodb_log_files_in_group = 2 innodb_log_file_size = 512M innodb_log_buffer_size = 64M innodb_file_per_table = 1 innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_buffer_pool_instances = 2 innodb_buffer_pool_size = 2000M  

MySQL SSL Gives Warnings about big name Certificate Authorities

I have been able to setup my MySQL to use LetsEncrypt certificates with the steps below, unfortunately it is issuing warnings about official certs being self-signed (like from DigiCert, Comodo, etc…), so I wonder if there’s something missing from the configuration. anyways, here’s my ssl configuration:

[mysqld] require_secure_transport = on mysqlx = 0 ssl_capath  = /etc/ssl/certs ssl_ca      = /etc/ssl/certs/lets-encrypt-x3-cross-signed.pem ssl_cert    = /etc/mysql/cert.pem ssl_key     = /etc/mysql/privkey.pem ssl_cipher  = DHE-RSA-AES256-GCM-SHA384 tls_version = TLSv1.2 

the problem

Everything in the ssl_capath comes up as a warning in the startup log (im doing tail -f /var/log/mysql/error.log):

YYYY-MM-DDTHH:mm:ss.SSSSSZ 0 [Warning] [MY-010068] [Server] CA certificate /etc/ssl/certs/SwissSign_Gold_CA_-_G2.pem is self signed. YYYY-MM-DDTHH:mm:ss.SSSSSZ 0 [Warning] [MY-010068] [Server] CA certificate /etc/ssl/certs/Trustwave_Global_ECC_P256_Certification_Authority.pem is self signed. ... 

background

the ssl_ca file is from doing wget https://letsencrypt.org/certs/lets-encrypt-x3-cross-signed.pem -O /etc/ssl/certs/lets-encrypt-x3-cross-signed.pem

the ssl_cert is from getting my LE live subfolder cert.pem file, same with the ssl_key (from LE’s privkey.pem). I’ve also restricted cipher and TLS version, but that’s probably not it.

To verify that everything is indeed working correctly, I have added the following to my client configuration (locally, not on that server):

[mysql] ssl_capath = /etc/ssl/certs 

and this session output:

user@localhost:~$   mysql --ssl-mode=VERIFY_IDENTITY -h mydomain.mytld -u remote -p -e "show variables like '%ssl%'; show session status like '%cipher%';" +-------------------------------------+-------------------------------------------------+ | Variable_name                       | Value                                           | +-------------------------------------+-------------------------------------------------+ | admin_ssl_ca                        |                                                 | | admin_ssl_capath                    |                                                 | | admin_ssl_cert                      |                                                 | | admin_ssl_cipher                    |                                                 | | admin_ssl_crl                       |                                                 | | admin_ssl_crlpath                   |                                                 | | admin_ssl_key                       |                                                 | | have_openssl                        | YES                                             | | have_ssl                            | YES                                             | | performance_schema_show_processlist | OFF                                             | | ssl_ca                              | /etc/ssl/certs/lets-encrypt-x3-cross-signed.pem | | ssl_capath                          | /etc/ssl/certs                                  | | ssl_cert                            | /etc/mysql/cert.pem                             | | ssl_cipher                          | DHE-RSA-AES256-GCM-SHA384                       | | ssl_crl                             |                                                 | | ssl_crlpath                         |                                                 | | ssl_fips_mode                       | OFF                                             | | ssl_key                             | /etc/mysql/privkey.pem                          | +-------------------------------------+-------------------------------------------------+ +--------------------------+------------------------------------------------------------------------------------------------------+ | Variable_name            | Value                                                                                                | +--------------------------+------------------------------------------------------------------------------------------------------+ | Current_tls_cipher       | DHE-RSA-AES256-GCM-SHA384                                                                            | | Current_tls_ciphersuites |                                                                                                      | | Ssl_cipher               | DHE-RSA-AES256-GCM-SHA384                                                                            | | Ssl_cipher_list          | TLS_AES_256_GCM_SHA384:TLS_CHACHA20_POLY1305_SHA256:TLS_AES_128_GCM_SHA256:DHE-RSA-AES256-GCM-SHA384 | +--------------------------+------------------------------------------------------------------------------------------------------+