DBD::MariaDB 1.23 not updating cardinality in MariaDB 10.6.5

[reachvrpp]

It was observed that DBD::MariaDB 1.23 seems not updating cardinality in MariadB 10.6.5. Once we replace 1.23 binaries with 1.21, everything seems started working.

[choroba]

Could you provide more details? Code sample demonstrating the problem would be the best.

[reachvrpp]

sorry for late reply. Sample Perl script you can try: Please change the username,password and port as needed. basically create any table and insert data then check cardinality.

#!/usr/bin/env perl

use warnings;
use strict;
use DBI;

#change and with db credentials
use constant dbuser => '';
use constant dbpassword => '';
use constant noofmessages => 100;

my $mariadb_socket = "<mysql.sock>";
my $dbname = 'testdb';
my $tablename = 'messages';

my ($cardhash, $sth, $stmt ,$dbh, $sql);

#create db
createdb();

#create table with no. of messages
createtable();

#getcardinality before inserting messages
getCardinality();

#insert number of messages
addmesseges();

#getcardinality after inserting messages
getCardinality();

#drop the db
dropdb();

#get cardinality of specified table
sub getCardinality {
my $dbh = dbconnect($dbname);

eval {
    $stmt = "show indexes from $tablename";
    $sth = $dbh->prepare($stmt); 
    $sth->execute() or die "error executing query: " . $sth->errstr();
    while ( my $row = $sth->fetchrow_hashref() ) {
        $cardhash->{'id'} = $row->{'Cardinality'} if($row->{'Column_name'} eq 'id');
    }
};
if ($@) {
    $sth->finish() if $sth;
    print "failed: " . $@ . "\n";
}
$sth->finish();
$dbh->disconnect(); 
print "cardinality of id column: $cardhash->{'id'}\n";

}

#insert messages
sub addmesseges {
$dbh = dbconnect($dbname);
$sql = "INSERT INTO messages (message_text) VALUES (?)";
$sth = $dbh->prepare($sql) or die "Failed to prepare insert statement: " . $dbh->errstr();
for(my $i=1;$i<=noofmessages();$i++) {
$sth->execute("Test Message $i") or die "Failed to insert message: " . $dbh->errstr();
}
$sth->finish();
$dbh->disconnect();
print "messages inserted successfully.\n";
}

#create table
sub createtable {
$dbh = dbconnect($dbname);
$sql = "CREATE TABLE IF NOT EXISTS $tablename (
id INT AUTO_INCREMENT PRIMARY KEY,
message_text TEXT
)";
my $rows_affected = $dbh->do($sql) or die "Failed to create table: " . $dbh->errstr();

if (defined $rows_affected) {
    print "table $tablename created successfully.\n";
} else {
    print "failed: " . $dbh->errstr . "\n";
}    
$dbh->disconnect();    

}

#drop the db if it exists
sub dropdb {
$dbh = dbconnect($dbname);
$sql = "DROP DATABASE IF EXISTS $dbname";
my $rows_affected = $dbh->do($sql) or die "Error dropping db: " . $dbh->errstr();
if (defined $rows_affected) {
print "Database $dbname dropped successfully.\n";
} else {
print "failed: " . $dbh->errstr . "\n";
}
$dbh->disconnect();
}

#create the database
sub createdb {
#using the default database (mysql) for the connection to create a new database.
$dbh = dbconnect('mysql');
$sql = "CREATE DATABASE IF NOT EXISTS $dbname";
my $rows_affected = $dbh->do($sql) or die "Error creating database: " . $dbh->errstr();
if (defined $rows_affected) {
print "Database $dbname created successfully.\n";
} else {
print "failed: " . $dbh->errstr() . "\n";
}
$dbh->disconnect();
}

#connect database
sub dbconnect {
my ($dbname) = @_;
my $dsn = "DBI:MariaDB:database=$dbname;mariadb_socket=$mariadb_socket";
$dbh = DBI->connect( $dsn, dbuser(), dbpassword(), { RaiseError => 1, AutoCommit => 1, PrintError => 0 } ) or die $DBI::errstr;
return $dbh;
}

[choroba]

I'm not sure it's Perl related. I tried the following bash script:

#! /bin/bash
{
    cat <<EOF 
USE test;
CREATE TABLE messages (id INT AUTO_INCREMENT PRIMARY KEY, message_text TEXT);
SHOW INDEXES FROM messages;
EOF
    for i in {1..100} ; do
        echo "INSERT INTO messages (message_text) VALUES ('message $i');"
    done

    cat <<EOF 
SHOW INDEXES FROM messages;
DROP TABLE messages;
EOF

} | mariadb

The output shows

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	CardinalitySub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
messages	0	PRIMARY	1	id	A	0	NULL	NULL		BTREE		NO
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	CardinalitySub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
messages	0	PRIMARY	1	id	A	4	NULL	NULL		BTREE		NO

i.e. the cardinality is 4, which is the same as what your Perl script shows me.
Manually inspecting the cardinality in mariadb shows 100, though.