I have a simple update query of which I get error, saying that there is something wrong with my syntax, but I have noe idea what it could be:

$sql = "UPDATE Leela SET Group = '$Groupname', Color = '$Brickcolor' WHERE Id = '$Id'";

if (!mysqli_query($conn, $sql)) {

echo "Error: 1010, contact technical support" . $sql . "<br>" . mysqli_error($conn);

}

In the database:

Group: varchar(18)

Color: char(1)

Id: bigint(12) auto increment

Thanks in advance.

Hi,

I am stuck in a situation with using MariaDB v11.4.3 server community edition on my Windows Server 2022 under my AWS ec2 instance.

After installation of the msi from GUI using default values for almost all the properties, I could see the MariaDB service running proper under Windows Services.

When I try connecting to MariaDB from the cmd line, it gives an error like this :

ERROR 2013 (HY000): Lost connection to server at 'sending authentication information', system error: 0

https://preview.redd.it/uz3xwl6wfn3e1.png?width=707&format=png&auto=webp&s=14cb30dad40863244dd765683305c8d44eca73a0

I have checked my firewall rules and I could not see anything different which might block the port 3306.

The interesting thing is that when I try using HeidiSQL to connect, it works like a charm.

And another weird behavior is that the cmd line works all right with MariaDB v10.11 and I am observing this issue only on v11.4 stream.

Please let me know if I am missing to configure anything which results in this behavior.

Thanks

How to best handle the situation. I have a 5 node cluster with Galera. I have a network maintenance incoming where 3 out of 5 nodes will be having network disconnected for 20 or 30 minutes in 2 hours span.
Problem is that i don't know if node 1 will recover until node 2 is disconnected and so on.
I want to avoid split brain scenarios or cluster instability.
Is the best scenario to stop node 4 and 5 before maintenance. Let Galera settle as 3 node cluster. And then let the one remaining node resync after the maintenance. After all is stable rejoin node 4 and 5.

I've been seeing a ton of these warnings in mysql_error log, a literal ton of them, from various websites on the server and various databases, mostly Wordpress, and they all look like this:

WARNING 1366: Incorrect integer value: '' for column...
WARNING 1292: Truncated incorrect INTEGER value: ''...
WARNING 1364: Field 'attackLogTime' doesn't have a default value :...
WARNING 1292: Truncated incorrect DECIMAL value: '' ...

And they are referring to well know Wordpress plugins like Wordfence and such, which I would assume would have their coding down pat and correct to avoid any obvious mysql errors. But since these are simply warnings, how do I turn these off, or ignore these "incorrect integer / decimal value / no default value" issues so that I can actually have a usable mysql_error log and find more important issues in there that need my attention?

I'm on MariaDB 11.4, and here is my.cnf configuration as well, with log_warnings set to 0 as well:

[mysqld]
default_storage_engine              = InnoDB
datadir                             = /var/lib/mysql
tmpdir                              = /var/lib/mysql/tmp
log-error                           = /var/lib/mysql/mysql_errors.log
slow_query_log_file                 = /var/lib/mysql/mysql_slow_queries.log
sql-mode                            = "NO_ENGINE_SUBSTITUTION"
pid-file                            = /var/lib/mysql/mysql.pid
socket                              = /var/lib/mysql/mysql.sock
symbolic-links                      = 0
max_allowed_packet                  = 256M
max_connections                     = 500
max_connect_errors                  = 20
open_files_limit                    = 50000
wait_timeout                        = 90
connect_timeout                     = 90
interactive_timeout                 = 90
tmp_table_size                      = 128M
max_heap_table_size                 = 128M
max_statement_time                  = 180
innodb_strict_mode                  = 0
log_warnings                        = 0
back_log                            = 512

slow_query_log                      = 1
long_query_time                     = 5

table_open_cache                    = 15K
table_definition_cache              = 15K
table_open_cache_instances          = 16
query_cache_type                    = 0
query_cache_size                    = 0
query_cache_limit                   = 1M #8M
thread_cache_size                   = 100 #256
thread_handling                     = pool-of-threads
key_buffer_size                     = 64M #256M
join_buffer_size                    = 20M

# [InnoDB]
innodb_buffer_pool_size             = 20G
innodb_log_file_size                = 5G
innodb_file_per_table               = 1

# [Misc]
concurrent_insert                   = 1
local_infile                        = 1
group_concat_max_len                = 102400
innodb_file_per_table               = 1

innodb_monitor_enable               = all
log_slow_verbosity                  = query_plan,explain
performance_schema                  = ON

# [Custom]
collation-server                    = utf8mb4_unicode_ci
init-connect                        = 'SET NAMES utf8mb4'
character-set-server                = utf8mb4
table_open_cache_instances          = 16

sql-error-log-size-limit            = 50M
sql-error-log-rotate                = 1
sql-error-log-filename              = mysql_query_errors.log

[mysqldump]
max_allowed_packet                  = 256M

[myisamchk]
bind-address                        = 127.0.0.1
max_allowed_packet                  = 50M #256M

And no, I can't just go into every website hosted on the server and every plugin and every application and "fix" their code or mysql structure... it would take forever, as it spans dozens of sites and plugins, and even then, IF I could do that, it would get overwritten with their next update which I have zero control over.

That's not a feasible solution, so I am asking for suggestions on how else I can turn these warnings off?

Is there a certain format I should turn these tables to, certain mode I should set up for MariaDB to skip over these errors, is there a list of "ignore" errors I can specify so that these are not logged...?

These are driving me nuts, as they are 99.99% of all errors being logged in the mysql_error log, and finding the ACTUAL errors that should be fixed among these "warnings" is literally impossible.

HELP!

I’m trying to compare MariaDB in terms of memory usage for specific queries. My goal is to execute a query multiple times on the database, record the memory usage for each execution, and then calculate the average memory usage for comparison.

I’m running MariaDB in a Docker containers.

What I’m looking for:

• Is there a built-in tool, query, or setting in MariaDB to monitor the memory usage of a specific query?
• If not, are there any recommended third-party tools or Docker configurations that could help me measure memory usage accurately?
• Any tips on how to account for caching or other factors that could skew the results?

I’ve tried looking into some performance monitoring tools, but they didn't provide query-specific memory usage. Any help or guidance would be greatly appreciated!

Thanks in advance!

OK I honestly looked at a zillion similar threads on SO that refer to this error, but none seem to describe my situation or provide a solution.

I have a MariaDB 11.3.2 server. It is working normally, and users are able to access whatever they're supposed to. I am able to log in via CLI as root@localhost using a password. (I am on the DB machine when doing this.) However when I create a new database foo (this works) and a new user foo (this also works), I am unable to grant that user any permissions for that database. The error I see is

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'foo'

When I "show grants for 'root'@'localhost'", it looks good:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, BINLOG MONITOR, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, DELETE HISTORY, SET USER, FEDERATED ADMIN, CONNECTION ADMIN, READ_ONLY ADMIN, REPLICATION SLAVE ADMIN, REPLICATION MASTER ADMIN, BINLOG ADMIN, BINLOG REPLAY, SLAVE MONITOR ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*aaaaaa' WITH GRANT OPTION

I am able to run mysql_secure_installation, it accepts my password, allows me to change it, no issue. But that doesn't solve the above.

The plugin is set to password, not usix_socket:

SELECT User, Host, plugin FROM mysql.user where user = 'root';
+------+-----------+-----------------------+
| User | Host      | plugin                |
+------+-----------+-----------------------+
| root | localhost | mysql_native_password |
| root | 127.0.0.1 | mysql_native_password |
| root | ::1       | mysql_native_password |
| root | 10.0.0.%  | mysql_native_password |
+------+-----------+-----------------------+
4 rows in set (0.003 sec)

What am I missing?

Has anyone deployed a solution in Kubernetes? There seems to be no solution around with ColumnStore, just the docker images.

I can't find any helm chart or any deployment example. The mariadb-operator has no reference to columnstore.

Hi,

I have a MariaDb database that I manipulate through python. I am able to perform all the functions (CRUD) except for Transaction rollbacks.. Below is the class I use with a demo prog in __main__

from inspect import getsourcefile
from os.path import abspath
import os
import sys
import mysql.connector
from datetime import datetime

# Solution defined modules
import PBKDF2
import AES
import credentials


class Database:
    """ Class defines the MariaDB table associated with PassList
    
        It reflects all the methods that are used in the table
    """    
    def __init__(self, database):
        self.host = credentials.host
        self.user = credentials.user
        self.password = credentials.password
        self.database = database
        self.connection = None
        self.cursor = None
        
        
    def connect(self):
        self.connection = mysql.connector.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            database=self.database
        )
        self.cursor = self.connection.cursor()


    def disconnect(self):
        self.cursor.close()
        self.connection.close()


    def transaction_start(self):
        self.cursor.execute("SET autocommit=0")
        self.cursor.execute("START TRANSACTION")
        self.connection.commit()


    def transaction_commit(self):
        self.cursor.execute("COMMIT")
        self.cursor.execute("SET autocommit=1")
        self.connection.commit()


    def transaction_rollback(self):
        self.cursor.execute("ROLLBACK")
        self.cursor.execute("SET autocommit=1")
        self.connection.commit()

    def insert(self, table, columns, values):
        query = f"INSERT INTO {table} ({', '.join(columns)}) VALUES ({', '.join(['%s'] * len(columns))})"
        self.cursor.execute(query, values)
        self.connection.commit()
       

    def replace(self, table, columns, values):
        query = f"REPLACE INTO {table} ({', '.join(columns)}) VALUES ({', '.join(['%s'] * len(columns))})"
        self.cursor.execute(query, values)
        self.connection.commit()


    def recexists(self, table, column, value):
        """Returns whether a record exists.
        Args:
            table (string): The table to be queries
            column (string): The field that is being queried
            value (string): The value that is being searched

        Returns:
            Boolean: Found or not found
        """        
        query = f"SELECT COUNT(*) FROM {table} WHERE {column} = '{value}' LIMIT 1"
        self.cursor.execute(query)
        records = self.cursor.fetchall()
        return records[0][0]
    

    def read(self, table, columns=None, where=None):
        if columns is None:
            columns = '*'
        query = f"SELECT {columns} FROM {table}"
        if where is not None:
            query += f" WHERE {where}"
        self.cursor.execute(query)
        return self.cursor.fetchall()


    def update(self, table, set_columns, values, where=None):
        set_query = ', '.join([f"{column} = %s" for column in set_columns])
        query = f"UPDATE {table} SET {set_query} "
        if where is not None:
            query += f" WHERE {where}"
        self.cursor.execute(query, values)
        self.connection.commit()
 

    def delete(self, table, where=None):
        query = f"DELETE FROM {table}"
        if where is not None:
            query += f" WHERE {where}"
        self.cursor.execute(query)
        self.connection.commit()

    
    def connectionOK(self):
        """
            Return a string if the connection cannot be established
            otherwise null
        """
        try:
            with mysql.connector.connect(
                host=self.host,
                user=self.user,
                password=self.password,
            ) as connection:
                connection.close()
        except mysql.connector.Error as e:
            return e


    def executeSQL(self, sql_cmd):
        '''
        Execute the passed SQL command
        '''
        try: 
            self.cursor.execute(sql_cmd)
            self.connection.commit()
            return None
        except mysql.connector.Error as e:
            return e

            
 
if __name__ == "__main__":
    bsms = Database('bsms')
    
    connectOK = bsms.connectionOK()
    if connectOK is not None:
        sys.exit(connectOK)
        
    bsms.connect()
    
    bsms.transaction_start()
    bsms.insert("SMSQue",["PassList_id", "sender", "destination", "message", "que_dttm"],[1, "sender", "12345678901", "message", datetime.now()])
    
    bsms.transaction_rollback()
    

    bsms.disconnect()

H folks,

I have a database for my NGINX Reverse Proxy, for some reason it got corrupted with "Tablespace is missing for table" The first Google result did not recovered the table without any error.

Unfortunatly it seems, that my backups where already purged, because I've used the Proxymanager last time, long time ago

Hello Experts ,

i am going on fixing a legacy design and wanting to migrate all DB instances from C:\ProgramFiles to another Drive (D:\)

for that i am chainging the value of the datadir within the my.ini file and want to cop / move the whole directory to the D:\ Drive

after a bit of searching i found that i can edit the registry key for the said service and have it pointing to another my.ini file which is an exact copy of the old file but just residing somewhere else. but when i do that i get an error and the service can't start.

Old value of ImagePath

"C:\Program Files\MariaDB 10.11\bin\mysqld.exe" "--defaults-file=C:\Program Files\MariaDB 10.11\instances_data\test_db_3\my.ini" "MySQL_test_db_3"

New value of ImagePath

"C:\Program Files\MariaDB 10.11\bin\mysqld.exe" "--defaults-file=D:\MariaDB_instances\test_db_3\my.ini" "MySQL_test_db_3"

- I have set the exact permissions on the folder in the new destination using get-acl | set-acl command

Get-Acl -Path "C:\Program Files\MariaDB 10.11\instances_data\test_db_3" | Set-Acl -Path "D:\MariaDB_instances\test_db_3"

however i always end up with the error

https://preview.redd.it/0xbwaxzlon1e1.png?width=480&format=png&auto=webp&s=8cb55688de6803383404bb29f4776a3eed46dad7

If i re-edit the Registry value to point to the old my.ini file , the service starts right away

Can someone help me ?

Hi I have a mariadb cluster (4 VMs) with MaxScale and today I make the backup of all the databases (600) using a pod (kubernetes) and MySQL dump. I would like to switch to an incremental backup and I would like to understand how to do it.. I’m not a DB Admin… Do you have some links for me?

Hello,

For various reasons I still use mha with MariaDB, and find it works well except for gtid support.

I noticed https://github.com/charlesdirk/MariaDB-MHA which seems to have been "developed under the umbrella of Google Summer of Code 2016 with the MariaDB Foundation" according to the github page.

However, I could not get it to ever work, especially as it doesn't have instructions.

Does anyone know if MariaDB-MHA project works?

I am running a test environment on windows server and want to upgrade from 10.3 to 11.4 .

I have successfully took back up and incremental backup and its quite straight forward .

but the problem happened to me with Resotre

I have tried the restore option with --copy-back but it gives me error

Original data directory C:/Program Files/MariaDB 10.3/data is not empty!

- i have stopped the corresponding services to the instance i am trying to restore

- the my.ini config file is pointing that the datadir is in a different location that is mentioned in the error message .

- the location of the backup files is at C:\Maria_Backup

- syntax of the command i am using

mariabackup --copy-back --host=hostname.local --port=3330 --user=root --password= --target-dir=C:\Maria_Backup

Any help with what i am doing wrong ?

I know this is old news but is this still an issue?

I found example code with both. mySQL seems to be better documented which, for me is very important.

Thanks

Hi,

I have a sp called FileRec. I tested that it works by calling the function from within HeidiSQL and verifying that the record gets added.:

CALL \FileRec`('TEST98', '54321', '[me@mine.com](mailto:me@mine.com)', '9876543', '10.0001', '11.9876')`

I want to call the same sp from within python and borrowed this code:

def invoke_file_rec(loginname, password, mobile, email):
    try:
        # Establish the database connection
        connection = mariadb.connect(
            host=credentials.host,
            database='bsms',
            user=credentials.user,
            password=credentials.password
        )
        
        cursor = connection.cursor()
        # Prepare the stored procedure call
        result = cursor.callproc('FileRec', [loginname, password, mobile, email, 0, 0])
            
   
    except Error as e:
        print(f"Error: {e}")
    finally:
        cursor.close()
        connection.close()
        print("MariaDB connection is closed")

# Example usage
invoke_file_rec('TEST999', 'example_password', 'example@example.com', '1234567890')

Code executes with no errors but no record gets added.

What am I missing?

Thanks

Will MariaDB 11.5 get LTS like 11.4 or will be a skipped version anyone knows ? I have a few hundreds machines to update, and thinking about if I should install 11.5 or 11.4 for a better future proof.

As the title says, after a recent Fedora update, mariadb was upgraded tp 10.11, I noticed in the journal that the mysql_upgrade was needed to be run. Now in the past, I have done this with not issues, except maybe having to run the command twice one time, but for the most part the use of the database has been trouble free.

So I ran the sudo mysql_upgrade -u root -p but this time and every other time I run it I get:

Major version upgrade detected from 10.5.21-MariaDB to 10.11.9-MariaDB. Check required!

Phase 1/8: Checking and upgrading mysql database

Processing databases
mysql
mysql.column_stats OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.global_priv OK
mysql.gtid_slave_pos OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.index_stats OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.roles_mapping OK
mysql.servers OK
mysql.table_stats OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.transaction_registry OK

Phase 2/8: Installing used storage engines... Skipped

Phase 3/8: Running 'mysql_fix_privilege_tables'

ERROR 1408 (HY000) at line 437: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.

FATAL ERROR: Upgrade failed

The database starts and seems to be working as expected, but I don't want to leave this is an inconsistent state. I do regular backups of all the created databases, but I don't backup all the uses and passwords, which would be a pain to recreate.

What do you think would be the best course of action, I was doing some reading about forcing the update, but unsure what the safest course of action, any advice would be appreciated

As a sub-point:

I also noticed that I have two symlinks, (never notice this before), to the same startup file, both created at the same time '2023-08-06 22:47'

/etc/systemd/system/mysql.service -> /usr/lib/systemd/system/mariadb.service
/etc/systemd/system/mysqld.service -> /usr/lib/systemd/system/mariadb.service

Is this normal?

I'm running TrueNAS Core and have nextcloud along with mariadb installed within a freebsd jail.

I've been using mariadb for probably 3-4 years without issue however now nextcloud is telling me to upgrade versions. I'm currently running: Ver 15.1 Distrib 10.4.28-MariaDB, for FreeBSD13.1. Nextcloud is recommending 10.6-14.1.

I've started with the instructions to upgrade from 10.4 to 10.6 listed here: https://mariadb.com/docs/server/service-management/upgrades/community-server/release-series-cs10-6/

I've actually run into problems with just creating the backup using the command;

$ sudo mariabackup --user=root --password=mysql --backup --target-dir=.

I get the following:

Segmentation fault

Segmentation Faults are never good. Any suggestion?

So I'm TRYING to set up maxscale with galara cluster. To have multiple maxscale servers.

When I try to set up the mariadb monitor it won't let me because we already have a galara cluster monitor

2024-11-04 06:08:36 error : Server 'server3' is already monitored by 'MariaDB-Monitor', cannot add it to another monitor.

2024-11-04 06:08:36 error : Server 'server4' is already monitored by 'MariaDB-Monitor', cannot add it to another monitor.

2024-11-04 06:08:36 error : Server 'server5' is already monitored by 'MariaDB-Monitor', cannot add it to another monitor.

2024-11-04 06:08:36 error : Failed to create monitor 'Galera-Monitor'.

2024-11-04 06:08:36 error : 1 errors were encountered while processing configuration.

2024-11-04 06:08:36 alert : Failed to process the MaxScale configuration file /etc/maxscale.cnf.

2024-11-04 06:08:36 notice : MaxScale is shutting down.

2024-11-04 06:08:36 notice : Stopped MaxScale REST API

2024-11-04 06:08:36 notice : All workers have shut down.

2024-11-04 06:08:36 notice : MaxScale shutdown completed.

Which means I can't set

cooperative_monitoring_locks

because that has to be in the mariadb-monitor section

Also - groan, it seems half the documentation has white spaces in it which it doesn't like.

[maxscale]
threads=auto

[server1]
type=server
address=10.0.10.101
port=3306
protocol=MariaDBBackend

[server2]
type=server
address=10.0.10.128
port=3306
protocol=MariaDBBackend

[server3]
type=server
address=10.0.10.123
port=3306
protocol=MariaDBBackend

[server4]
type=server
address=10.0.10.104
port=3306
protocol=MariaDBBackend

[server5]
type=server
address=10.0.10.107
port=3306
protocol=MariaDBBackend

[Galera-Monitor]
type=monitor
module=galeramon
servers=server1,server2,server3,server4,server5
user=(username)
password=(password)
monitor_interval=2000ms

[connection-router]
type = service
router = readconnroute
servers = server1,server2,server3,server4,server5
user = (username)
password = (password)
router_options = synced

[connection-router-listener]
type = listener
service = connection-router
protocol = MariaDBClient
port = 3306

This webinar will take place on Nov 21st at 12 PM. It will include: how to assess and plan a migration, MariaDB’s built-in Oracle compatibility, best practices for QA, testing and data migration, etc. To register, follow this link.

https://go.mariadb.com/Oraclemigration-2024-11-21_Registration-LP.html

I want to enable bin logs on mariadb server. It is a part of 4 node cluster. I read on the web about ruining galera sync if you enable bin logs. I need bin logs for replication to another server. Can someone tell me if this is possible and how?

I'm trying to connect to MariaDB from c# application. I tried Mysqlconnector and MySql.Data nuget packages. I created simple app base on tutorial:

string connectionString = "Server=xxx;Port=3306;User ID=xxx;Password=xxx;Database=xxx";

using (var connection = new MySqlConnection(connectionString))
{
}

I get exception:

I was trying to debug this package and i found out that this exception is thrown on establishing connection to DB even before credentials are used for authentication.

I found informations about configuration of user on Server but im not owning this MariaDB server.

https://mariadb.com/kb/en/pluggable-authentication-overview/

Also one of my team member was able to create java app and connect with same user without any problems

regards

Does anyone know what the "Segment" option is for in the "Add Node" page for clusters in Galera Manager?

It appears to only accept a value from 0 to 255. Unsure what it does, though, and can't seem to find any pages with info about it.

I'm assuming that maybe it's just a way to organize nodes in Galera Manager, to basically group different nodes that are part of a cluster. For example, one segment for nodes on a specific server, or a segment for nodes in a datacenter, etc. But if that was the case, why limit "Segment" to only 0-255 rather than being more descriptive?

Anyhow, would appreciate if anyone can fill me in on what this "Segment" setting does when adding a node.

Thanks

Earlier today I installed my first test Galera Cluster in LXD containers on my laptop. MariaDB 10.11 & Ubuntu 22.04.

In Galera Manager desktop the 3 nodes I made show as "SYNCED." Near the bottom right of the node icons is a red dot on each. When I hover over that with my mouse the text "Node's agent is offline" displays.

I can't seem to figure out what this "Node's agent is offline" (while all nodes are SYNCED) is all about. I've searched the docs, various support channels, forums & via Google. Haven't been able to find anything relevant that explains what this is.

If anyone knows, can you please explain or link me to a page that explains what is going on with "Node's agent is offline" and what needs to be done to correct it?

Thanks

And here's a screenshot of what I'm seeing:

https://preview.redd.it/gv5a5kjf4twd1.png?width=360&format=png&auto=webp&s=63aed0d995471517c37be5ac3d331fe8e60c8dd7

I've just noticed that my server allows me to run a query like:

SELECT sku,SUM(qty) FROM stock

without specifying any grouping. It returns one line with the sku the same as from SELECT sku FROM stock LIMIT 1, and with SUM(qty) being a sum of the entire quantity column, as you'd expect.

SELECT @@SQL_MODE returns:

ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Am I missing something here?