#StackBounty: #mariadb #windows MariaDB starts from cmd prompt but not as service

Bounty: 50

Attempting to start MariaDB as a service results in an error that says:

Windows could not start the MariaDB service on Local Computer

Error 1053: The service did not respond to the start or control
request in a timely fashion.

The error log does not show any errors after that start attempt but the server is not started. The error log looks completely normal and contains:

InnoDB: using atomic writes.
2021-02-17  7:05:16 0 [Note] InnoDB: Uses event mutexes
2021-02-17  7:05:16 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-02-17  7:05:16 0 [Note] InnoDB: Number of pools: 1
2021-02-17  7:05:16 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2021-02-17  7:05:16 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
2021-02-17  7:05:16 0 [Note] InnoDB: Completed initialization of buffer pool
2021-02-17  7:05:16 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=1051746172,1051746172
2021-02-17  7:05:16 0 [Note] InnoDB: 128 rollback segments are active.
2021-02-17  7:05:16 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2021-02-17  7:05:16 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-02-17  7:05:16 0 [Note] InnoDB: Setting file '.ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-02-17  7:05:16 0 [Note] InnoDB: File '.ibtmp1' size is now 12 MB.
2021-02-17  7:05:16 0 [Note] InnoDB: 10.5.8 started; log sequence number 1051746283; transaction id 199153
2021-02-17  7:05:16 0 [Note] Plugin 'FEEDBACK' is disabled.
2021-02-17  7:05:16 0 [Note] InnoDB: Loading buffer pool(s) from c:ZZ-MariaDB_Dataib_buffer_pool
2021-02-17  7:05:16 0 [Note] Server socket created on IP: '::'.
2021-02-17  7:05:16 0 [Note] Reading of all Master_info entries succeeded
2021-02-17  7:05:16 0 [Note] Added new Master_info '' to hash table
2021-02-17  7:05:16 0 [Note] c:/mariadb/bin/mysqld: ready for connections.
Version: '10.5.8-MariaDB'  socket: ''  port: 3306  mariadb.org binary distribution
2021-02-17  7:05:17 0 [Note] InnoDB: Buffer pool(s) load completed at 210217  7:05:17

The INI file being used contains:

[mysqld]
datadir=c:/ZZ-MariaDB_Data
character-set-server=utf8mb4
collation_server = 'utf8mb4_unicode_ci'
default-storage-engine=InnoDB
[client]
plugin-dir=c:/MariaDB/lib/plugin

The Path to executable setting in Services is c:/mariadb/bin/mysqld --defaults-file=C:/ZZ-MariaDB_Data/my.ini.

If I run that from a command prompt the server starts properly and everything works as expected. Things only fail when I try to start it from Services.


Get this bounty!!!

#StackBounty: #mariadb #database-backup #galera How can I use garbd+mariabackup to back up a MariaDB/Galera cluster?

Bounty: 100

I’m migrating a Galera cluster from MySQL to MariaDB 10.3.27, and as part of the process I’d like to change the backup method from the somewhat fiddly (and blocking) garbd+rsync to garbd+mariabackup. Changing the SST method to mariabackup worked fine from the start, so I suppose the nodes are set up correctly, but the way I call garbd is wrong:

garbd --address 'gcomm://10.2.96.145:4567,10.2.96.146:4567?gmcast.listen_addr=tcp://0.0.0.0:4444' --group m1t-shared-mariadb --donor m1t-shared-db-5 --sst mariabackup

Port 4444 is copy/pasted from documentation, I think it pertains to the local garbd’s listening port? Anyway it doesn’t matter whether I use 4444 or the rest of the cluster’s 4567.

On the donor node I see:

2021-01-25 17:34:26 0 [Note] WSREP: Member 0.0 (garb) requested state transfer from 'm1t-shared-db-5'. Selected 3.0 (m1t-shared-db-5)(SYNCED) as donor.
2021-01-25 17:34:26 0 [Note] WSREP: Shifting SYNCED -> DONOR/DESYNCED (TO: 19280263)
2021-01-25 17:34:26 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2021-01-25 17:34:26 0 [Note] WSREP: Running: 'wsrep_sst_mariabackup --role 'donor' --address '' --socket '/var/lib/mysql/mysql.sock' --datadir '/data/mysql/' --gtid 'c12b9eef-1780-11eb-bac6-da564ff9aacc:19280263' --gtid-domain-id '0' --mysqld-args --basedir=/usr'
2021-01-25 17:34:26 2 [Note] WSREP: sst_donor_thread signaled with 0
2021-01-25 17:34:26 0 [Note] WSREP: 0.0 (garb): State transfer from 3.0 (m1t-shared-db-5) complete.
WSREP_SST: [INFO] Streaming with xbstream (20210125 17:34:26.604)
WSREP_SST: [INFO] Using socat as streamer (20210125 17:34:26.606)
WSREP_SST: [ERROR] Upgrade joiner to 5.6.21 or higher for backup locks support (20210125 17:34:26.609)
WSREP_SST: [ERROR] The joiner is not supported for this version of donor (20210125 17:34:26.611)
WSREP_SST: [ERROR] Cleanup after exit with status:93 (20210125 17:34:26.613)
WSREP_SST: [INFO] Cleaning up temporary directories (20210125 17:34:26.617)
2021-01-25 17:34:26 0 [ERROR] WSREP: Failed to read from: wsrep_sst_mariabackup --role 'donor' --address '' --socket '/var/lib/mysql/mysql.sock' --datadir '/data/mysql/' --gtid 'c12b9eef-1780-11eb-bac6-da564ff9aacc:19280263' --gtid-domain-id '0' --mysqld-args --basedir=/usr
2021-01-25 17:34:26 0 [ERROR] WSREP: Process completed with error: wsrep_sst_mariabackup --role 'donor' --address '' --socket '/var/lib/mysql/mysql.sock' --datadir '/data/mysql/' --gtid 'c12b9eef-1780-11eb-bac6-da564ff9aacc:19280263' --gtid-domain-id '0' --mysqld-args --basedir=/usr: 93 (Protocol not supported)

The error Upgrade joiner to 5.6.21 or higher for backup locks support seems to be a red herring stemming from the fact that the required version is parsed from the --address parameter and that’s conspicuously empty.

Grasping for clues I took to reading MariaDB’s sql/wsrep_sst.cc file to find out how --address is filled before calling the wsrep_sst_* plugins. If the wsrep_sst_receive_address variable is set to anything but "AUTO", it’s supposed to just use that; otherwise it uses wsrep_node_address or, failing that, some guessing procedure (and yes, that’s just as documented). However, that doesn’t seem to work. --address is always empty, no matter what I set wsrep_sst_receive_address to. Also, an error should be logged if wsrep_sst_receive_address is "AUTO" and wsrep_node_address is not a valid IP address. I tried to provoke that to find out what code path is taken but wasn’t able to either.

Any idea what’s going wrong here?


Get this bounty!!!

#StackBounty: #mysql #query-performance #join #mariadb Slow performance when joining a small table and filtering out on a non-key colum…

Bounty: 100

I am fairly new to MariaDB and I am struggling with one issue that I cannot get to the bottom of it. This is the query:

SELECT SQL_NO_CACHE STRAIGHT_JOIN
    `c`.`Name` AS `CategoryName`, 
    `c`.`UrlSlug` AS `CategorySlug`, 
    `n`.`Description`, 
    IF(n.OriginalImageUrl IS NOT NULL, n.OriginalImageUrl, s.LogoUrl) AS `ImageUrl`, 
    `n`.`Link`, 
    `n`.`PublishedOn`, 
    `s`.`Name` AS `SourceName`, 
    `s`.`Url` AS `SourceWebsite`, 
   s.UrlSlug AS SourceUrlSlug,
    `n`.`Title`
FROM `NewsItems` AS `n`
INNER JOIN `NewsSources` AS `s` ON `n`.`NewsSourceId` = `s`.`Id`
LEFT JOIN `Categories` AS `c` ON `n`.`CategoryId` = `c`.`CategoryId`
WHERE s.UrlSlug = 'slug'
#WHERE s.Id = 52
ORDER BY `n`.`PublishedOn` DESC
LIMIT 50

NewsSources is a table with about 40 rows and NewsItems has ~1 million. Each news item belongs to one source and one source can have many items. I’m trying to get all items for a source identified by URL slug of the source.

  1. In case when I use STRAIGHT_JOIN and when I query for a source that has lots of news items, the query returns immediately.
    However, if I query for a source that has low number of items (~100) OR if I query for a URL slug that doesn’t belong to any source (result set is 0 rows), the query runs for 12 seconds.

  2. In case when I remove STRAIGHT_JOIN, I see the opposite performance from the first case – it runs really slow when I query for a news source with many items and returns immediately for sources with low number of items or result set is 0, because the URL slug doesn’t belong to any news source.

  3. In case when I query by news source ID (the commented out WHERE s.Id = 52), the result comes immediately, regardless of whether there are lots of items for that source or 0 items for that source.

I want to point out again that the NewsSources table contains only about 40 rows.

Here is the analyzer results for the query above: Explain Analyzer

What can I do to make this query to run fast always?

Here are tables and indexes definitions:

-- --------------------------------------------------------
-- Server version:               10.4.13-MariaDB-1:10.4.13+maria~bionic - mariadb.org binary distribution
-- Server OS:                    debian-linux-gnu
-- --------------------------------------------------------

-- Dumping structure for table Categories
CREATE TABLE IF NOT EXISTS `Categories` (
  `CategoryId` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Description` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `UrlSlug` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `CreatedOn` datetime(6) NOT NULL,
  `ModifiedOn` datetime(6) NOT NULL,
  PRIMARY KEY (`CategoryId`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Dumping structure for table NewsItems
CREATE TABLE IF NOT EXISTS `NewsItems` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `NewsSourceId` int(11) NOT NULL,
  `Title` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Link` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Description` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `PublishedOn` datetime(6) NOT NULL,
  `GlobalId` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `CategoryId` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `IX_NewsItems_CategoryId` (`CategoryId`),
  KEY `IX_NewsItems_NewsSourceId_GlobalId` (`NewsSourceId`,`GlobalId`),
  KEY `IX_NewsItems_PublishedOn` (`PublishedOn`),
  KEY `IX_NewsItems_NewsSourceId` (`NewsSourceId`),
  FULLTEXT KEY `Title` (`Title`,`Description`),
  CONSTRAINT `FK_NewsItems_Categories_CategoryId` FOREIGN KEY (`CategoryId`) REFERENCES `Categories` (`CategoryId`),
  CONSTRAINT `FK_NewsItems_NewsSources_NewsSourceId` FOREIGN KEY (`NewsSourceId`) REFERENCES `NewsSources` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=649802 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Dumping structure for table NewsSources
CREATE TABLE IF NOT EXISTS `NewsSources` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Url` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
  `UrlSlug` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `LogoUrl` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


Get this bounty!!!

#StackBounty: #mysql #laravel #eloquent #mariadb Laravel Eloquent order by field as 1,2,3,4,1,2,3,4

Bounty: 250

I have goods table

good_link, parent_link, name
sdf-sdfg   ffff         rock    
utyruuur   ffff         qwe     
gfhdfggg   dddd         paper   
sdfghvcx   eeee         water   
ncvbcxvb   dddd         tree    
dsgfdsg    zzzz         sdff
sdfsdff    zzzz         fdgdf
sdfgdgg    zzzz         sdfsdf
dsvfdgg    zzzz         ssdfgr
brtyfgh    zzzz         fgdfgdf

How can I order data by parent_link to have data like

ffff
dddd
eeee
zzzz
ffff
dddd
zzzz
zzzz
zzzz
zzzz

so all goods goes one after another but always with different parent_link(previous row parent_link != next row parent_link, and ordering A-Z has no difference)?


Get this bounty!!!

#StackBounty: #centos #mariadb Can't change the datadir for MariaDB mysql files

Bounty: 50

This is CentOS 8.1 and MariaDB 10.3 database server.

I have followed the directions I’ve seen in several places to relocate the datadir from the default of /var/lib/mysql to /home/mysql. /home is the RAID.

I’ve made the change in /etc/my.cnf putting this before the !include stuff, but it appears to be still pointing to /var/lib/mysql.

When I have done this, mysql complains and it won’t bring let my run the mysql command.

I have looked in /etc/my.cnf.d and see the default is still there in mariadb-server.cnf.

I see this message in the log and wasn’t sure if this was related or not?

Mar 11 11:58:44 myserver systemd[1]: Starting MariaDB 10.3 database server...

Mar 11 11:58:44 myserver mysql-prepare-db-dir[6349]: **Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.**

Mar 11 11:58:44 myserver mysql-prepare-db-dir[6349]: **If this is not the case, make sure the /var/lib/mysql is empty before running mysql-prepare-db-dir.**

I have tried running mysql-prepare-db-dir, but the same messages appear in the logs.


Get this bounty!!!

#StackBounty: #centos #mariadb Can't change the datadir for MariaDB mysql files

Bounty: 50

This is CentOS 8.1 and MariaDB 10.3 database server.

I have followed the directions I’ve seen in several places to relocate the datadir from the default of /var/lib/mysql to /home/mysql. /home is the RAID.

I’ve made the change in /etc/my.cnf putting this before the !include stuff, but it appears to be still pointing to /var/lib/mysql.

When I have done this, mysql complains and it won’t bring let my run the mysql command.

I have looked in /etc/my.cnf.d and see the default is still there in mariadb-server.cnf.

I see this message in the log and wasn’t sure if this was related or not?

Mar 11 11:58:44 myserver systemd[1]: Starting MariaDB 10.3 database server...

Mar 11 11:58:44 myserver mysql-prepare-db-dir[6349]: **Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.**

Mar 11 11:58:44 myserver mysql-prepare-db-dir[6349]: **If this is not the case, make sure the /var/lib/mysql is empty before running mysql-prepare-db-dir.**

I have tried running mysql-prepare-db-dir, but the same messages appear in the logs.


Get this bounty!!!

#StackBounty: #centos #mariadb Can't change the datadir for MariaDB mysql files

Bounty: 50

This is CentOS 8.1 and MariaDB 10.3 database server.

I have followed the directions I’ve seen in several places to relocate the datadir from the default of /var/lib/mysql to /home/mysql. /home is the RAID.

I’ve made the change in /etc/my.cnf putting this before the !include stuff, but it appears to be still pointing to /var/lib/mysql.

When I have done this, mysql complains and it won’t bring let my run the mysql command.

I have looked in /etc/my.cnf.d and see the default is still there in mariadb-server.cnf.

I see this message in the log and wasn’t sure if this was related or not?

Mar 11 11:58:44 myserver systemd[1]: Starting MariaDB 10.3 database server...

Mar 11 11:58:44 myserver mysql-prepare-db-dir[6349]: **Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.**

Mar 11 11:58:44 myserver mysql-prepare-db-dir[6349]: **If this is not the case, make sure the /var/lib/mysql is empty before running mysql-prepare-db-dir.**

I have tried running mysql-prepare-db-dir, but the same messages appear in the logs.


Get this bounty!!!

#StackBounty: #centos #mariadb Can't change the datadir for MariaDB mysql files

Bounty: 50

This is CentOS 8.1 and MariaDB 10.3 database server.

I have followed the directions I’ve seen in several places to relocate the datadir from the default of /var/lib/mysql to /home/mysql. /home is the RAID.

I’ve made the change in /etc/my.cnf putting this before the !include stuff, but it appears to be still pointing to /var/lib/mysql.

When I have done this, mysql complains and it won’t bring let my run the mysql command.

I have looked in /etc/my.cnf.d and see the default is still there in mariadb-server.cnf.

I see this message in the log and wasn’t sure if this was related or not?

Mar 11 11:58:44 myserver systemd[1]: Starting MariaDB 10.3 database server...

Mar 11 11:58:44 myserver mysql-prepare-db-dir[6349]: **Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.**

Mar 11 11:58:44 myserver mysql-prepare-db-dir[6349]: **If this is not the case, make sure the /var/lib/mysql is empty before running mysql-prepare-db-dir.**

I have tried running mysql-prepare-db-dir, but the same messages appear in the logs.


Get this bounty!!!

#StackBounty: #centos #mariadb Can't change the datadir for MariaDB mysql files

Bounty: 50

This is CentOS 8.1 and MariaDB 10.3 database server.

I have followed the directions I’ve seen in several places to relocate the datadir from the default of /var/lib/mysql to /home/mysql. /home is the RAID.

I’ve made the change in /etc/my.cnf putting this before the !include stuff, but it appears to be still pointing to /var/lib/mysql.

When I have done this, mysql complains and it won’t bring let my run the mysql command.

I have looked in /etc/my.cnf.d and see the default is still there in mariadb-server.cnf.

I see this message in the log and wasn’t sure if this was related or not?

Mar 11 11:58:44 myserver systemd[1]: Starting MariaDB 10.3 database server...

Mar 11 11:58:44 myserver mysql-prepare-db-dir[6349]: **Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.**

Mar 11 11:58:44 myserver mysql-prepare-db-dir[6349]: **If this is not the case, make sure the /var/lib/mysql is empty before running mysql-prepare-db-dir.**

I have tried running mysql-prepare-db-dir, but the same messages appear in the logs.


Get this bounty!!!

#StackBounty: #centos #mariadb Can't change the datadir for MariaDB mysql files

Bounty: 50

This is CentOS 8.1 and MariaDB 10.3 database server.

I have followed the directions I’ve seen in several places to relocate the datadir from the default of /var/lib/mysql to /home/mysql. /home is the RAID.

I’ve made the change in /etc/my.cnf putting this before the !include stuff, but it appears to be still pointing to /var/lib/mysql.

When I have done this, mysql complains and it won’t bring let my run the mysql command.

I have looked in /etc/my.cnf.d and see the default is still there in mariadb-server.cnf.

I see this message in the log and wasn’t sure if this was related or not?

Mar 11 11:58:44 myserver systemd[1]: Starting MariaDB 10.3 database server...

Mar 11 11:58:44 myserver mysql-prepare-db-dir[6349]: **Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.**

Mar 11 11:58:44 myserver mysql-prepare-db-dir[6349]: **If this is not the case, make sure the /var/lib/mysql is empty before running mysql-prepare-db-dir.**

I have tried running mysql-prepare-db-dir, but the same messages appear in the logs.


Get this bounty!!!