MySQL is claiming all of my memory when it’s only using a few gigs

So, I have been having an issue with memory being "buffered" or "active", it seems my MariaDB installation is just marking all the memory on my system as it’s own? I don’t know for sure what is going on but it will show that I have almost no memory free but 24Gb available on free -wh ? I need to know if this is an issue and if yes please tell me what settings I need to change.

My system has 32GB of RAM and it’s constantly having to use SWAP because of this memory being buffered/active (swappiness is set to 1). Please check my server/mariadb settings below.

free -wh
mariadb.conf
smem -p
innodb engine status
cat /proc/meminfo
htop

MYSQL: Query the last conversion on chat messages and sort by latest message

I am having issue on query sorting of last convo messages of each users. Here is my current record on my 2 tables:

enter image description here

enter image description here

I want to achieve below result:

enter image description here

My current query is:

 SELECT `messages`.*, `users`.`username`, `u2`.`username` as `to_username`,  CASE WHEN to_user_id = 1     THEN `users`.`username`     ELSE u2.username  END as participant  FROM `messages`   LEFT JOIN `users` ON `messages`.`from_user_id`=`users`.`id`   LEFT JOIN `users` as `u2` ON `messages`.`to_user_id`=`u2`.`id`   INNER JOIN (SELECT max(id) as lastmsgId FROM messages where to_user_id = 1 or from_user_id =1 GROUP BY to_user_id) m2 ON `messages`.`id`=`m2`.`lastmsgId`   GROUP BY participant    ORDER BY `messages`.`created_at` DESC 

And the result of my query is: enter image description here

The 4th and 5th message is not sorted properly I am expecting to display the 4th message instead of 5th message. It displays the older message instead of latest one. I couldn’t find the solution on how will be display the right sorting.

Any help and suggestion is very much appreciated.

Migrate MySQL spatial data to PostgreSQL(PostGIS)

We have a web system that uses MySQL spatial databases. We want to migrate from MySQL to PostgreSQL. Our database has geometry and point data types, 21 tables and its size is 1.6GB.

I’ve been looking for methods to do it. I have found some tools that help you to migrate. However most of them do not support spatial data like https://github.com/philipsoutham/py-mysql2pgsql.

I also took a look over https://gis.stackexchange.com/questions/104081/how-to-migrate-spatial-tables-from-mssql-to-postgis. I have just seen this post. I haven’t tried it yet, I don’t know if it could work for MySQL. Furthermore, I’d like to do it using DB managers reather than Qgis or ArcGis.

Problem with connect mysql databases of remote

I’m getting error with connect remote mysql database. Firstly;

1-) I use windows 10 machine and my remote server also windows 10 machine.

2-) My database at locale at remote windows machine with root.

I tried so many things but enter image description here and

ı took error picture enter image description here

Also I CREATE USER user but again ı have a problem. How can ı fixed and connect remote database

Please help me?

How to add a volume to current in use MySQL database

related to the problem I faced here No space left on device, I want to adopt a long term solution about the point no space left on device. One of the action includes to add a volume to the machine in which the database runs (MySQL 8, Ubuntu 20.04 and Digital Ocean provider).

I would like to know which one is safest way and rules to follow in order to avoid loosing reference and/or data when I will add a volume on the machine. Considering that this database is running in production without any replication.

Thank you in advance for the support

MySQL – Call a View inside a Stored Procedure

Let’s say, I have a table as follows

tbl_1

id             name                 status               usertype                         flag ================================================================================================ 1              Name 1                  1                    2                               0 2              Name 2                  1                    3                               0 3              Name 3                  1                    1                               0 4              Name 4                  1                    4                               0 5              Name 5                  1                    5                               0  

HTML PAGE

<select id="type" name="type">    <option value="1">1</option> <option value="2">2</option> <option value="3">3</option> <option value="4">4</option> <option value="5">5</option>  </select> 

My requirement is that I need to get all the data from table where the usertype is selected from the html page by the user (could be 1, 2, 3,4,5, etc.)

I want to create a view, and call that inside stored procedure

I cannot pass an argument inside of a View. So, I was told to create a stored procedure, and call the view inside it. The problem is, I don’t know how to do it.

XAMPP MySQL service crash after reboot

Operating System: Window 10 64bit Video: https://youtu.be/HodTJxphn94

When I run mysql, it immediately exits. I think this happens every 3 months. Is there any way to solve this?

12:10:51  [mysql]   Attempting to start MySQL app... 12:10:52  [mysql]   Status change detected: running 12:10:54  [mysql]   Status change detected: stopped 12:10:54  [mysql]   Error: MySQL shutdown unexpectedly. 12:10:54  [mysql]   This may be due to a blocked port, missing dependencies,  12:10:54  [mysql]   improper privileges, a crash, or a shutdown by another method. 12:10:54  [mysql]   Press the Logs button to view error logs and check 12:10:54  [mysql]   the Windows Event Viewer for more clues 12:10:54  [mysql]   If you need more help, copy and post this 12:10:54  [mysql]   entire log window on the forums 

Log

InnoDB: using atomic writes. [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions [Note] InnoDB: Uses event mutexes [Note] InnoDB: Compressed tables use zlib 1.2.11 [Note] InnoDB: Number of pools: 1 [Note] InnoDB: Using SSE2 crc32 instructions [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M [Note] InnoDB: Completed initialization of buffer pool [Note] InnoDB: 128 out of 128 rollback segments are active. [Note] InnoDB: Creating shared tablespace for temporary tables [Note] InnoDB: Setting file 'C:\xampp\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... [Note] InnoDB: File 'C:\xampp\mysql\data\ibtmp1' size is now 12 MB. [Note] InnoDB: Waiting for purge to start [Note] InnoDB: 10.4.17 started; log sequence number 44926144; transaction id 195314 [Note] InnoDB: Loading buffer pool(s) from C:\xampp\mysql\data\ib_buffer_pool [Note] Plugin 'FEEDBACK' is disabled. [Note] Server socket created on IP: '::'.``` 

MySQL illegal mix of collations, ASCII to UTF-8

I have a MySQL 8.0 table like this, which is UTF-8 apart from one field, which holds an ASCII UUID that doesn’t need UTF-8 overhead:

CREATE TABLE `things` (   `id` bigint unsigned NOT NULL AUTO_INCREMENT,   `uuid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,   `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,   `created_at` timestamp NULL DEFAULT NULL,   `updated_at` timestamp NULL DEFAULT NULL,   PRIMARY KEY (`id`),   UNIQUE KEY `things_uuid_unique` (`uuid`) ) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 

and I’m getting query failures like this:

SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (ascii_bin,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation '=' (SQL: select * from `things` where (`uuid` = 1abb9e11-4f00-4904-988e-233a3c0ce411) limit 1) 

My connection is using utf8mb4 and utf8mb4_unicode_ci, the other fields, table, and database are using the same collation, and my scripts themselves are also UTF-8, though that makes no practical difference here since it’s just ASCII. This particular query uses a string literal containing only 7-bit ASCII chars, not a field name or variable.

I don’t understand why this fails, since (unlike ISO-8859 charsets), ASCII is UTF-8 compatible; there is no ASCII string that is not also a valid UTF-8 string, so why wouldn’t this be coercible? Is it something to do with ci vs bin? I can’t solve this by switching the connection to ASCII because the real queries use multiple fields, some of which are UTF-8.

I know that I can force the comparison using:

select * from `things` where (`uuid` = BINARY '1abb9e11-4f00-4904-988e-233a3c0ce411') 

But that also seems unnecessary, and not something I’ve needed to do before, in the same circumstances. I guess worst case I could switch the UUID field to UTF-8, but that offends my developer sensibilities!

It’s doubly frustrating as I know I’ve used this pattern successfully many times before!

Help on improving the query performance of MySQL table

My team handles a tool that automatically detects and categorizes images.We have a Mysql DB (InnoDB engine) used by our tool in production where we store information about each image processed.

The table is poorly designed by someone long before I joined the team. It was all working well as there was very less data in Db till now. Recently we launched the tool to a wider audience and the Db is now having huge data. The ‘select’ query speed is very slow and takes days to get result.

I am not an expert in Databases. Please help on suggestions to improve the performance. I am thinking of options like creating indexes. We cannot have downtime for the system.Th Db is write heavy (around 200 insertion per second). I am afraid of creating table locks if i try out index creation.

Table schema: 6 columns , all Varchar, no primary Key, no indexes

Mysql Router not sending Write request to R/W instance

I am doing InnoDB Cluster Group Replication for the first time. I stuck at the last step Mysql Router. Mysql Router is configured with bootstrapping, But the main issue is MySQL router does not send the write request to the Primary R/W instance after failover. After failover primary become node instance and the Error says The MySQL server is running with the –super-read-only option so it cannot execute this statement

I am following this tutorial https://severalnines.com/database-blog/mysql-innodb-cluster-80-complete-deployment-walk-through-part-one.

Thanks in advance