DBD::MariaDB 1.23 not updating cardinality in MariaDB 10.6.5

[vrpprasad]

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.

[vrpprasad]

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.

[vrpprasad]

No. It is not Perl issue. same code is working fine with other MariaDB driver versions. Only 1.23 seems to have some issue. Ideally, it should show 100 immediately if database is having 100.