subject:"Re\: \[Maria\-discuss\] Maria\-db refuses to start"

On Fri, Dec 9, 2022 at 2:43 AM Reindl Harald  wrote:

> > If you're concerned about database corruption then you need to start off
> > by having multiple copies of the data available in an online state if
> > you want to recover from corrupt data without doing a backup.
>
> completly different topic
>
> > This is
> > what ZFS does with RAID - detects which block is corrupt and then uses
> > other data blocks, including parity, to rebuild the corrupt block.
>
> completly different layers
>
> > MariaDB isn't designed for that. I'm not even sure if there is any
> > database that's designed for that, including SQL Server (see below for
> > more.)
>
> the topic was "Some of us run MariaDB on file systems that do their own
> block checksumming, and thus run innodb_checksum_algorithm=none" where
> you mix two completly independent layers and now we have two morons

Virtually all data corruption that occurs happens in the disk layer,
not the database internals layer. This is the reality you seem to be
struggling to grasp.
If address the problem of corruption in the disk layer, the usefulness
of additionally checking in a place that is only useful for internals
debugging is vanishingly narrow.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

On Fri, Dec 9, 2022 at 2:40 AM Reindl Harald  wrote:

> >>> 2.2) Database doesn't crash because the damage merely corrupts a
> >>> single value but the record structure remains sound.
> >>>
> >>> So it is that 2.2) point where the InnoDB checksum gives you anything
> >>
> >> moron it don't matter if you find it useful - the whole point was that
> >> you pretended the filesystem can do the same with it's checksums which
> >> is nonsense
> >
> > You are conveniently ignoring the fact that in the vast majority of
> > cases what InnoDB checksums will catch is silent disk corruption
> > rather than database internals corruption.
>
> i ignore nothing but filesystem corruption is still a different topic
>
> > So the one narrow edge case you are clinging to as the full
> > justification of your abusive behaviour and delusions of grandeur are
> > a tiny fraction of a percent of the errors that will cause InnoDB
> > checksums to fail - and outside that narrow edge case all of the rest
> > of them will be caught and handled better at layers below the database
> > itself.
>
> the topic was and still is "Some of us run MariaDB on file systems that
> do their own block checksumming, and thus run
> innodb_checksum_algorithm=none" where you mix two completly independent
> layers
>
> > So either you are arguing in bad faith, or you really are extensively
> > ignorant of typical failure patterns.
>
> the topic was and still is "Some of us run MariaDB on file systems that
> do their own block checksumming, and thus run
> innodb_checksum_algorithm=none" where you mix two completly independent
> layers

They are different layers, but 99.9%+ of corruption that InnoDB
checksums ever detects occur in the storage layer, not in the database
internals layer.
So in terms of the overall picture of the corruption cause probability
landscape which you seem to be struggling to see, you are about 0.1%
correct. I'll grant you that.

I'll go as far as hazarding a guess that InnoDB checkums were
originally added with the main motivation of detecting disk corruption
rather than internals debugging. Unfortunately, the code tree back in
3.23.24 (as far as I can tell the release where InnoDB was merged)
doesn't seem to contain any annotations on the subject that might shed
light on the original motivation.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start





Am 08.12.22 um 23:53 schrieb martin doc:
If you're concerned about database corruption then you need to start off 
by having multiple copies of the data available in an online state if 
you want to recover from corrupt data without doing a backup.


completly different topic

This is 
what ZFS does with RAID - detects which block is corrupt and then uses 
other data blocks, including parity, to rebuild the corrupt block. 


completly different layers

MariaDB isn't designed for that. I'm not even sure if there is any 
database that's designed for that, including SQL Server (see below for 
more.)


the topic was "Some of us run MariaDB on file systems that do their own 
block checksumming, and thus run innodb_checksum_algorithm=none" where 
you mix two completly independent layers and now we have two morons


___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start





Am 08.12.22 um 23:55 schrieb Gordan Bobic:

On Fri, Dec 9, 2022 at 12:31 AM Reindl Harald  wrote:


2.2) Database doesn't crash because the damage merely corrupts a
single value but the record structure remains sound.

So it is that 2.2) point where the InnoDB checksum gives you anything


moron it don't matter if you find it useful - the whole point was that
you pretended the filesystem can do the same with it's checksums which
is nonsense


You are conveniently ignoring the fact that in the vast majority of
cases what InnoDB checksums will catch is silent disk corruption
rather than database internals corruption.


i ignore nothing but filesystem corruption is still a different topic


So the one narrow edge case you are clinging to as the full
justification of your abusive behaviour and delusions of grandeur are
a tiny fraction of a percent of the errors that will cause InnoDB
checksums to fail - and outside that narrow edge case all of the rest
of them will be caught and handled better at layers below the database
itself.


the topic was and still is "Some of us run MariaDB on file systems that 
do their own block checksumming, and thus run 
innodb_checksum_algorithm=none" where you mix two completly independent 
layers



So either you are arguing in bad faith, or you really are extensively
ignorant of typical failure patterns.


the topic was and still is "Some of us run MariaDB on file systems that 
do their own block checksumming, and thus run 
innodb_checksum_algorithm=none" where you mix two completly independent 
layers


___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

On Fri, Dec 9, 2022 at 12:31 AM Reindl Harald  wrote:

> > 2.2) Database doesn't crash because the damage merely corrupts a
> > single value but the record structure remains sound.
> >
> > So it is that 2.2) point where the InnoDB checksum gives you anything
>
> moron it don't matter if you find it useful - the whole point was that
> you pretended the filesystem can do the same with it's checksums which
> is nonsense

You are conveniently ignoring the fact that in the vast majority of
cases what InnoDB checksums will catch is silent disk corruption
rather than database internals corruption.
So the one narrow edge case you are clinging to as the full
justification of your abusive behaviour and delusions of grandeur are
a tiny fraction of a percent of the errors that will cause InnoDB
checksums to fail - and outside that narrow edge case all of the rest
of them will be caught and handled better at layers below the database
itself.
So either you are arguing in bad faith, or you really are extensively
ignorant of typical failure patterns.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-12-08 Thread martin doc

If you're concerned about database corruption then you need to start off by
having multiple copies of the data available in an online state if you want to
recover from corrupt data without doing a backup. This is what ZFS does with
RAID - detects which block is corrupt and then uses other data blocks,
including parity, to rebuild the corrupt block. MariaDB isn't designed for
that. I'm not even sure if there is any database that's designed for that,
including SQL Server (see below for more.)

If you want to protect against silent data corruption now there are some
alternatives:

1) implement record level checksums that you calculate and verify. This would
require adding a "checksum" field to all of your tables. Maybe hide them with
stored procedures. This is the best way for you to detect silent data
corruption to your data. It won't fix data corruption.

2) implement application logic to always store data in 2+ databases and write
an application to verify that all copies of the data are the same (think of
this as being the application of a ZFS scrub.) E.g. Using Microsoft Witness
Server's with mirror'd SQL databases:

https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-witness?view=sql-server-ver16

The above (1) and (2) won't prevent metadata that the database uses to hold
your data from being corrupted. That'd require changes to the page table
structures used by InnoDB, etc. On top of that, putting checksums in the page
tables wouldn't necessarily detect corruption to your data that happens before
it gets included in the page's checksum calculation. If you want to detect
corruption of data that is returned over a TCP connection to a remote database
then you need record level checksums - see (1) above.

You might even decide to do both (1) and (2) - add checksums to your tables and
develop your application to always store (INSERT) all data in 2 different
databases (potentially on different servers) and develop the application to
query the other database/server if there's a checksum failure when returning
data from the first source.

Having multiple databases on different servers also protects you from problems
with upgrades, etc, as long as your procedures validate each database is sound
before making them "live".

You have to take ownership of the risk for only keeping one copy of critical
data. You have to take ownership of the risk for not keeping current backups of
your database.

Data resiliency requires effort. If you don't put in any effort then you won't
get much in the way of resiliency from corruption. That btw includes good
practice for doing ugprades, shutdowns, etc.

MariaDB has as much resiliency to silent data corruption as you put in effort
to mitigate it.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start





Am 08.12.22 um 22:32 schrieb Gordan Bobic:

On Thu, Dec 8, 2022 at 10:59 PM Reindl Harald  wrote:

What is the net benefit of detecting said error? The way I see it, the
options are:
1) MariaDB detects and error, crashes out
2) MariaDB doesn't detect an error, ingests garbage, crashes out


silent data corruption


At what layer? If at anything below, up to and including the file
system level, ZFS will detect and correct it for you.
MariaDB can at best only detect it for you. So catching it at a lower
level wins.


The only way an error will creep in without the error checking FS
spotting it is:
1) manually corrupting the block by writing garbage over it
2) MariaDB generated a duff block and wrote it out
3) Some other hardware failure corrupted the block in MariaDB memory,
just before writing it to the file system

If any of the latter set happens, the data is toast anyway.
If the former set happens, the data is toast anyway.


silent data corruption


At what layer? 


on the database layer itself


If at anything below, up to and including the file
system level, ZFS will detect and correct it for you


are you really that dumb that you don't understand that the filesystem 
don't matter here?



MariaDB can at best only detect it for you. So catching it at a lower
level wins.


that layer can't do anything about databse internals


moron the files are fine from the viewpoint of the filessystem


At what layer? 


the database layer moron


If at anything below, up to and including the file
system level, ZFS will detect and correct it for you


different layer moron


moron the files are fine from the viewpoint of the filessystem


AGAIN: THE FILESYSTEM CAN'T DO ANYTHING BECAUSE IT'S NOT AFFECTED


So your argument is that the page checksum is there to tell you that
your data is corrupted because of either:
1) data was corrupted by the database itself, or
2) a superuser overwriting the block on the disk


and it's impressive how long it takes until you mororn understand such
basics


And in that case what exactly will the ckecksum do for you?


let you notice you have a problem and need backups


What do you gain from it? The error message that the block is
corrupted just before the database crashes?


know that your data are damaged instead growing silent corruption


2) InnoDB has no checksums. Database steps on the duff block.
2.1) Database crashes during a query. You get the query and tables
accessed during a stack trace.
You run a check table and it finds a corrupted table.
You restore it from backup


and without that over time you have no backup without the problem and 
even if you have one nobody wants a backup from months ago not containg 
any recent data



2.2) Database doesn't crash because the damage merely corrupts a
single value but the record structure remains sound.

So it is that 2.2) point where the InnoDB checksum gives you anything


moron it don't matter if you find it useful - the whole point was that 
you pretended the filesystem can do the same with it's checksums which 
is nonsense


___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

On Thu, Dec 8, 2022 at 10:59 PM Reindl Harald  wrote:
>
> the next reply-all clown boy i am subsribed so you can just respond
> to the list

You sure whine a lot about inconsequential things.

> Am 08.12.22 um 21:13 schrieb Gordan Bobic:
> > On Thu, Dec 8, 2022 at 9:42 PM Reindl Harald  wrote:
> >>
> >> Am 08.12.22 um 18:59 schrieb Gordan Bobic:
> >>> On Thu, Dec 8, 2022 at 7:28 PM Reindl Harald  
> >>> wrote:
>  MariaDB does the same as the filesystem
>  InnoDB in fact is more ore less a FS on top of a FS
> >>>
> >>> So why do it at both levels?
> >>
> >> because the FS layer can't detect MariaDB errors?
> >
> > What is the net benefit of detecting said error? The way I see it, the
> > options are:
> > 1) MariaDB detects and error, crashes out
> > 2) MariaDB doesn't detect an error, ingests garbage, crashes out
>
> silent data corruption

At what layer? If at anything below, up to and including the file
system level, ZFS will detect and correct it for you.
MariaDB can at best only detect it for you. So catching it at a lower
level wins.

> > The only way an error will creep in without the error checking FS
> > spotting it is:
> > 1) manually corrupting the block by writing garbage over it
> > 2) MariaDB generated a duff block and wrote it out
> > 3) Some other hardware failure corrupted the block in MariaDB memory,
> > just before writing it to the file system
> >
> > If any of the latter set happens, the data is toast anyway.
> > If the former set happens, the data is toast anyway.
>
> silent data corruption

At what layer? If at anything below, up to and including the file
system level, ZFS will detect and correct it for you.
MariaDB can at best only detect it for you. So catching it at a lower
level wins.

> > Sure it's nice to get an error that confirms that your data is
> > corrupted, but that won't bring it back
> silent data corruption
>
> > Shift it down to a layer where at least a subset of the problemspace
> > can be fixed and we have a net gain in at least some cases.
>
> different world

A far better world that you seem have a hard time accepting exists.

> >>> And what makes doing it at MariaDB level
> >>> in any way better than doing it somewhere else?
> >>
> >> which magic should do it somewehre else?
> >
> > If a file system is in control of data mirroring and checksumming
> > every 16KB block, then if the data is corrupted on disk, file system
> > will detect it on a read and fetch a good copy from an uncorrupted
> > mirror.
>
> moron the files are fine from the viewpoint of the filessystem

At what layer? If at anything below, up to and including the file
system level, ZFS will detect and correct it for you.
MariaDB can at best only detect it for you. So catching it at a lower
level wins.
I am talking about total cumulative data integrity up to and including
what MariaDB reads from disks.
What layer in the stack, exactly, are you trying to fight "silent data
corruption" you mentioned above?

> > Application never knows something went wrong, file system replaces the
> > corrupted block on the other disk and everything carries on
> > uninterrupted.
>
> moron the files are fine from the viewpoint of the filessystem
>
> >> AGAIN: THE FILESYSTEM CAN'T DO ANYTHING BECAUSE IT'S NOT AFFECTED
> >
> > So your argument is that the page checksum is there to tell you that
> > your data is corrupted because of either:
> > 1) data was corrupted by the database itself, or
> > 2) a superuser overwriting the block on the disk
>
> and it's impressive how long it takes until you mororn understand such
> basics

And in that case what exactly will the ckecksum do for you?
What do you gain from it? The error message that the block is
corrupted just before the database crashes?

> > In either case, you are not getting the data back either way the the
> > database will stop working.
>
> yes, but it's a difference if you have silent data corruption detectet
> months later when clean backups are gone because retention times
>
> > So what is the point? I'd rather have the error fixable
>
> moron your filesystem can't fix that type of error
>
> that's why "Some of us run MariaDB on file systems that do their own
> block checksumming, and thus run innodb_checksum_algorithm=none" was an
> idiots response
>
> a different response would have been "i don't think that i need innodb
> checksums and want to disable it" but you moron pretended your
> filesystem does the same

Let's look at the scenarios here, assuming a block gets corrupted:
1) InnoDB has checksums.
1.1) Database doesn't step on the duff block, error doesn't get
detected. Until long after your backup retention expired.
1.2) Data corruption happens in memory before the checksum is
calculated. Checksum is based on corrupted data and doesn't help you.

2) InnoDB has no checksums. Database steps on the duff block.
2.1) Database crashes during a query. You get the query and tables
accessed during a stack trace.
You run a check table and it finds a

Re: [Maria-discuss] Maria-db refuses to start

the next reply-all clown boy i am subsribed so you can just respond 
to the list


Am 08.12.22 um 21:13 schrieb Gordan Bobic:

On Thu, Dec 8, 2022 at 9:42 PM Reindl Harald  wrote:


Am 08.12.22 um 18:59 schrieb Gordan Bobic:

On Thu, Dec 8, 2022 at 7:28 PM Reindl Harald  wrote:

MariaDB does the same as the filesystem
InnoDB in fact is more ore less a FS on top of a FS


So why do it at both levels?


because the FS layer can't detect MariaDB errors?


What is the net benefit of detecting said error? The way I see it, the
options are:
1) MariaDB detects and error, crashes out
2) MariaDB doesn't detect an error, ingests garbage, crashes out


silent data corruption


The only way an error will creep in without the error checking FS
spotting it is:
1) manually corrupting the block by writing garbage over it
2) MariaDB generated a duff block and wrote it out
3) Some other hardware failure corrupted the block in MariaDB memory,
just before writing it to the file system

If any of the latter set happens, the data is toast anyway.
If the former set happens, the data is toast anyway.


silent data corruption


Sure it's nice to get an error that confirms that your data is
corrupted, but that won't bring it back

silent data corruption


Shift it down to a layer where at least a subset of the problemspace
can be fixed and we have a net gain in at least some cases.


different world


And what makes doing it at MariaDB level
in any way better than doing it somewhere else?


which magic should do it somewehre else?


If a file system is in control of data mirroring and checksumming
every 16KB block, then if the data is corrupted on disk, file system
will detect it on a read and fetch a good copy from an uncorrupted
mirror.


moron the files are fine from the viewpoint of the filessystem


Application never knows something went wrong, file system replaces the
corrupted block on the other disk and everything carries on
uninterrupted.


moron the files are fine from the viewpoint of the filessystem


AGAIN: THE FILESYSTEM CAN'T DO ANYTHING BECAUSE IT'S NOT AFFECTED


So your argument is that the page checksum is there to tell you that
your data is corrupted because of either:
1) data was corrupted by the database itself, or
2) a superuser overwriting the block on the disk


and it's impressive how long it takes until you mororn understand such 
basics



In either case, you are not getting the data back either way the the
database will stop working.


yes, but it's a difference if you have silent data corruption detectet 
months later when clean backups are gone because retention times



So what is the point? I'd rather have the error fixable


moron your filesystem can't fix that type of error

that's why "Some of us run MariaDB on file systems that do their own 
block checksumming, and thus run innodb_checksum_algorithm=none" was an 
idiots response


a different response would have been "i don't think that i need innodb 
checksums and want to disable it" but you moron pretended your 
filesystem does the same



than have the
knowledge that I have an error I can do nothing about


moron your filesystem can't fix that type of error and you won't be 
aware that you have a problem not matter what FS you are using



___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

On Thu, Dec 8, 2022 at 9:42 PM Reindl Harald  wrote:
>
>
>
> Am 08.12.22 um 18:59 schrieb Gordan Bobic:
> > On Thu, Dec 8, 2022 at 7:28 PM Reindl Harald  wrote:
> >> MariaDB does the same as the filesystem
> >> InnoDB in fact is more ore less a FS on top of a FS
> >
> > So why do it at both levels?
>
> because the FS layer can't detect MariaDB errors?

What is the net benefit of detecting said error? The way I see it, the
options are:
1) MariaDB detects and error, crashes out
2) MariaDB doesn't detect an error, ingests garbage, crashes out

The only way an error will creep in without the error checking FS
spotting it is:
1) manually corrupting the block by writing garbage over it
2) MariaDB generated a duff block and wrote it out
3) Some other hardware failure corrupted the block in MariaDB memory,
just before writing it to the file system

If any of the latter set happens, the data is toast anyway.
If the former set happens, the data is toast anyway.

Sure it's nice to get an error that confirms that your data is
corrupted, but that won't bring it back.
Shift it down to a layer where at least a subset of the problemspace
can be fixed and we have a net gain in at least some cases.

> > And what makes doing it at MariaDB level
> > in any way better than doing it somewhere else?
>
> which magic should do it somewehre else?

If a file system is in control of data mirroring and checksumming
every 16KB block, then if the data is corrupted on disk, file system
will detect it on a read and fetch a good copy from an uncorrupted
mirror.
Application never knows something went wrong, file system replaces the
corrupted block on the other disk and everything carries on
uninterrupted.

> >> "Some of us run MariaDB on file systems that do their own block
> >> checksumming, and thus run innodb_checksum_algorithm=none" makes you
> >> looking like a fool - period
> >>
> >> are you dumb or why don't you understand that the filesystem is a
> >> completly different layer and has no clue about the data itself?
> >
> > Are you too dumb to understand that if a block is corrupted at InnoDB
> > level MariaDB can't do anyting to fix it, but if a block is corrupted
> > at lower level, ZFS can fix it from redundantly stored data and
> > MariaDB never gets to ingest a corrupted block in the first place?
>
> it can at least fail early instead work with corrupted data
>
> > If you disagree, please describe a scenario in which an InnoDB page
> > checksum does anything useful if the file system it is on has built in
> > block checksumming and data redundancy.
> INNDOB CHECKSUMS DETECT DATA CORRUPTION WITHIN MARIADB NOT CAUSED BY ANY
> FILESYSTEM ISSUE AT ALL
>
> the filesystem can't do that with it's block checksumming and data
> redundancy because there is *nothing wrong* for the view of the FS layer
>
> one is for consistency of the database
> one is for consistency of the underlying filesystem
>
> two worlds and i simply don't get why people not understanding such
> basics  work in the IT but to top that talking nonsense on mailing-lists
>
> AGAIN: THE FILESYSTEM CAN'T DO ANYTHING BECAUSE IT'S NOT AFFECTED

So your argument is that the page checksum is there to tell you that
your data is corrupted because of either:
1) data was corrupted by the database itself, or
2) a superuser overwriting the block on the disk

In either case, you are not getting the data back either way the the
database will stop working.
So what is the point? I'd rather have the error fixable than have the
knowledge that I have an error I can do nothing about.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start





Am 08.12.22 um 18:59 schrieb Gordan Bobic:

On Thu, Dec 8, 2022 at 7:28 PM Reindl Harald  wrote:

MariaDB does the same as the filesystem
InnoDB in fact is more ore less a FS on top of a FS


So why do it at both levels? 


because the FS layer can't detect MariaDB errors?


And what makes doing it at MariaDB level
in any way better than doing it somewhere else?


which magic should do it somewehre else?


"Some of us run MariaDB on file systems that do their own block
checksumming, and thus run innodb_checksum_algorithm=none" makes you
looking like a fool - period

are you dumb or why don't you understand that the filesystem is a
completly different layer and has no clue about the data itself?


Are you too dumb to understand that if a block is corrupted at InnoDB
level MariaDB can't do anyting to fix it, but if a block is corrupted
at lower level, ZFS can fix it from redundantly stored data and
MariaDB never gets to ingest a corrupted block in the first place?


it can at least fail early instead work with corrupted data


If you disagree, please describe a scenario in which an InnoDB page
checksum does anything useful if the file system it is on has built in
block checksumming and data redundancy.
INNDOB CHECKSUMS DETECT DATA CORRUPTION WITHIN MARIADB NOT CAUSED BY ANY 
FILESYSTEM ISSUE AT ALL


the filesystem can't do that with it's block checksumming and data 
redundancy because there is *nothing wrong* for the view of the FS layer


one is for consistency of the database
one is for consistency of the underlying filesystem

two worlds and i simply don't get why people not understanding such 
basics  work in the IT but to top that talking nonsense on mailing-lists


AGAIN: THE FILESYSTEM CAN'T DO ANYTHING BECAUSE IT'S NOT AFFECTED

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-12-08 Thread Jogchum Reitsma


Op 08-12-2022 om 18:59 schreef Gordan Bobic:

On Thu, Dec 8, 2022 at 7:28 PM Reindl Harald  wrote:


MariaDB Server 10.4 introduced a new file format
innodb_checksum_algorithm=full_crc32, and MariaDB Server 10.5 made it
the default. Any files that were created when that setting is active
are guaranteed to write any unused bytes as zeroes. It also fixes a
peculiar design decision that some bytes of the page are not covered
by any checksum, and that a page is considered valid if any of the
non-full_crc32 checksums happen to produce a match. This includes the
magic 0xdeadbeef for innodb_checksum_algorithm=none.

Maybe we should consider eventually deprecating write support for the
non-full_crc32 format, to force a fresh start.

Please don't. Some of us run MariaDB on file systems that do their own
block checksumming, and thus run innodb_checksum_algorithm=none

that's nonsense - when mariadb writes wrong data in it's files no
filesystem can magically fix that

MariaDB can't fix it either. And if that is what happened, there is no
benefit to duplicating the effort.

MariaDB does the same as the filesystem
InnoDB in fact is more ore less a FS on top of a FS

So why do it at both levels? And what makes doing it at MariaDB level
in any way better than doing it somewhere else?


you need to understand what innodb checksums are and then it's logical
that the file-system layer is a completly different world

https://dba.stackexchange.com/questions/171708/what-is-an-innodb-page-checksum

You need to understand that properly thought out and sensibly written
file systems (which is, granted, pretty rare, I know of a total of 1)
implicitly prevent torn pages from being possible.
So the checksum and the doublewrite are completely redundant in such
cases, and can be safely disabled.

"Some of us run MariaDB on file systems that do their own block
checksumming, and thus run innodb_checksum_algorithm=none" makes you
looking like a fool - period

are you dumb or why don't you understand that the filesystem is a
completly different layer and has no clue about the data itself?

Are you too dumb to understand that if a block is corrupted at InnoDB
level MariaDB can't do anyting to fix it, but if a block is corrupted
at lower level, ZFS can fix it from redundantly stored data and
MariaDB never gets to ingest a corrupted block in the first place?
If you disagree, please describe a scenario in which an InnoDB page
checksum does anything useful if the file system it is on has built in
block checksumming and data redundancy.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp


I don't understand much of this discussion, but  take it it's not meant 
for me...


I take it it does not change anything for the steps I described in my 
last mail. to get a functioning database again..


regards, Jogchum


___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

On Thu, Dec 8, 2022 at 7:28 PM Reindl Harald  wrote:

>  MariaDB Server 10.4 introduced a new file format
>  innodb_checksum_algorithm=full_crc32, and MariaDB Server 10.5 made it
>  the default. Any files that were created when that setting is active
>  are guaranteed to write any unused bytes as zeroes. It also fixes a
>  peculiar design decision that some bytes of the page are not covered
>  by any checksum, and that a page is considered valid if any of the
>  non-full_crc32 checksums happen to produce a match. This includes the
>  magic 0xdeadbeef for innodb_checksum_algorithm=none.
> 
>  Maybe we should consider eventually deprecating write support for the
>  non-full_crc32 format, to force a fresh start.
> >>>
> >>> Please don't. Some of us run MariaDB on file systems that do their own
> >>> block checksumming, and thus run innodb_checksum_algorithm=none
> >>
> >> that's nonsense - when mariadb writes wrong data in it's files no
> >> filesystem can magically fix that
> >
> > MariaDB can't fix it either. And if that is what happened, there is no
> > benefit to duplicating the effort.
>
> MariaDB does the same as the filesystem
> InnoDB in fact is more ore less a FS on top of a FS

So why do it at both levels? And what makes doing it at MariaDB level
in any way better than doing it somewhere else?

> >> you need to understand what innodb checksums are and then it's logical
> >> that the file-system layer is a completly different world
> >>
> >> https://dba.stackexchange.com/questions/171708/what-is-an-innodb-page-checksum
> >
> > You need to understand that properly thought out and sensibly written
> > file systems (which is, granted, pretty rare, I know of a total of 1)
> > implicitly prevent torn pages from being possible.
> > So the checksum and the doublewrite are completely redundant in such
> > cases, and can be safely disabled.
>
> "Some of us run MariaDB on file systems that do their own block
> checksumming, and thus run innodb_checksum_algorithm=none" makes you
> looking like a fool - period
>
> are you dumb or why don't you understand that the filesystem is a
> completly different layer and has no clue about the data itself?

Are you too dumb to understand that if a block is corrupted at InnoDB
level MariaDB can't do anyting to fix it, but if a block is corrupted
at lower level, ZFS can fix it from redundantly stored data and
MariaDB never gets to ingest a corrupted block in the first place?
If you disagree, please describe a scenario in which an InnoDB page
checksum does anything useful if the file system it is on has built in
block checksumming and data redundancy.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start





Am 08.12.22 um 18:08 schrieb Gordan Bobic:

On Thu, Dec 8, 2022 at 7:02 PM Reindl Harald  wrote:


MariaDB Server 10.4 introduced a new file format
innodb_checksum_algorithm=full_crc32, and MariaDB Server 10.5 made it
the default. Any files that were created when that setting is active
are guaranteed to write any unused bytes as zeroes. It also fixes a
peculiar design decision that some bytes of the page are not covered
by any checksum, and that a page is considered valid if any of the
non-full_crc32 checksums happen to produce a match. This includes the
magic 0xdeadbeef for innodb_checksum_algorithm=none.

Maybe we should consider eventually deprecating write support for the
non-full_crc32 format, to force a fresh start.


Please don't. Some of us run MariaDB on file systems that do their own
block checksumming, and thus run innodb_checksum_algorithm=none


that's nonsense - when mariadb writes wrong data in it's files no
filesystem can magically fix that


MariaDB can't fix it either. And if that is what happened, there is no
benefit to duplicating the effort.


MariaDB does the same as the filesystem
InnoDB in fact is more ore less a FS on top of a FS


you need to understand what innodb checksums are and then it's logical
that the file-system layer is a completly different world

https://dba.stackexchange.com/questions/171708/what-is-an-innodb-page-checksum


You need to understand that properly thought out and sensibly written
file systems (which is, granted, pretty rare, I know of a total of 1)
implicitly prevent torn pages from being possible.
So the checksum and the doublewrite are completely redundant in such
cases, and can be safely disabled.


are you dumb or why don't you understand that the filesystem is a 
completly different layer and has no clue about the data itself?


"Some of us run MariaDB on file systems that do their own block 
checksumming, and thus run innodb_checksum_algorithm=none" makes you 
looking like a fool - period



___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

On Thu, Dec 8, 2022 at 7:02 PM Reindl Harald  wrote:

> >> MariaDB Server 10.4 introduced a new file format
> >> innodb_checksum_algorithm=full_crc32, and MariaDB Server 10.5 made it
> >> the default. Any files that were created when that setting is active
> >> are guaranteed to write any unused bytes as zeroes. It also fixes a
> >> peculiar design decision that some bytes of the page are not covered
> >> by any checksum, and that a page is considered valid if any of the
> >> non-full_crc32 checksums happen to produce a match. This includes the
> >> magic 0xdeadbeef for innodb_checksum_algorithm=none.
> >>
> >> Maybe we should consider eventually deprecating write support for the
> >> non-full_crc32 format, to force a fresh start.
> >
> > Please don't. Some of us run MariaDB on file systems that do their own
> > block checksumming, and thus run innodb_checksum_algorithm=none
>
> that's nonsense - when mariadb writes wrong data in it's files no
> filesystem can magically fix that

MariaDB can't fix it either. And if that is what happened, there is no
benefit to duplicating the effort.

> you need to understand what innodb checksums are and then it's logical
> that the file-system layer is a completly different world
>
> https://dba.stackexchange.com/questions/171708/what-is-an-innodb-page-checksum

You need to understand that properly thought out and sensibly written
file systems (which is, granted, pretty rare, I know of a total of 1)
implicitly prevent torn pages from being possible.
So the checksum and the doublewrite are completely redundant in such
cases, and can be safely disabled.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

On Thu, Dec 8, 2022 at 6:59 PM Reindl Harald  wrote:
>
>
>
> Am 08.12.22 um 17:51 schrieb Gordan Bobic:
> > On Thu, Dec 8, 2022 at 6:43 PM Reindl Harald  wrote:
> >
> >> and when you are at it get rid of "ib_logfile0" and "ib_logfile1" which
> >> hold data even in file-per-table mode you can't cleanup from crap caused
> >> by a crash 13 years ago
> >
> > How can that happen when every once in a while upgrades require a full
> > clean shutdown with innodb_fast_shutdown=0 so that the ib_logfile*
> > have to be rebuild due to an on-disk format change?
>
> in my whole life from MySQL 5.0 (the frist time i built mysql with
> innodb support at all) to MariaDB 10.3 i didn't do anything then
> upgrade, restart service and be done

I don't have an explanation for that, short of pure luck, in the fact
that the log was naturally empty because everything had been flushed
to disk at the point you upgraded.
You should play the lottery more often if your luck is that good. Mine
certainly isn't.

> > More to the point, if that is a concern, why can you not shut down
> > cleanly with innodb_fast_shutdown=0, rm the files, and let MariaDB
> > re-create them completely clean and empty?
>
> don't ask me why that shit can't be removed automatically over 13 years
> - the reference to './dbmail/#sql2-704-271.ibd' lives somewhere in the
> "global tablespace" which shouldn't exist at all

I have seen phantom InnoDB tables like that before, but never ones
that coiuldn't be cleared with a simple "drop table".
Does it show up in innodb_sys_tables or innodb_sys_tablespaces?

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start




Am 08.12.22 um 17:47 schrieb Gordan Bobic:

On Thu, Dec 8, 2022 at 6:25 PM Marko Mäkelä  wrote:


MariaDB Server 10.4 introduced a new file format
innodb_checksum_algorithm=full_crc32, and MariaDB Server 10.5 made it
the default. Any files that were created when that setting is active
are guaranteed to write any unused bytes as zeroes. It also fixes a
peculiar design decision that some bytes of the page are not covered
by any checksum, and that a page is considered valid if any of the
non-full_crc32 checksums happen to produce a match. This includes the
magic 0xdeadbeef for innodb_checksum_algorithm=none.

Maybe we should consider eventually deprecating write support for the
non-full_crc32 format, to force a fresh start.


Please don't. Some of us run MariaDB on file systems that do their own
block checksumming, and thus run innodb_checksum_algorithm=none


that's nonsense - when mariadb writes wrong data in it's files no 
filesystem can magically fix that


you need to understand what innodb checksums are and then it's logical 
that the file-system layer is a completly different world


https://dba.stackexchange.com/questions/171708/what-is-an-innodb-page-checksum


and/or have databases many terabytes in size. With terabyte size
databases, doing a mysqldump+restore is realistically _never_ going to
happen.


that's is the real problem - and hence i expect from software in 2022 
that it's able to read it's old data and create new initialized 
data-files at runtime


___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start





Am 08.12.22 um 17:51 schrieb Gordan Bobic:

On Thu, Dec 8, 2022 at 6:43 PM Reindl Harald  wrote:


and when you are at it get rid of "ib_logfile0" and "ib_logfile1" which
hold data even in file-per-table mode you can't cleanup from crap caused
by a crash 13 years ago


How can that happen when every once in a while upgrades require a full
clean shutdown with innodb_fast_shutdown=0 so that the ib_logfile*
have to be rebuild due to an on-disk format change?


in my whole life from MySQL 5.0 (the frist time i built mysql with 
innodb support at all) to MariaDB 10.3 i didn't do anything then 
upgrade, restart service and be done



More to the point, if that is a concern, why can you not shut down
cleanly with innodb_fast_shutdown=0, rm the files, and let MariaDB
re-create them completely clean and empty?


don't ask me why that shit can't be removed automatically over 13 years 
- the reference to './dbmail/#sql2-704-271.ibd' lives somewhere in the 
"global tablespace" which shouldn't exist at all


# mysqld to syslog and skip known erroes from crash 2009
if $programname == 'mysqld' then {
 :msg, contains, "[ERROR] InnoDB: Cannot open datafile for read-only: 
'./dbmail/#sql2-704-271.ibd'" stop
 :msg, contains, "[ERROR] InnoDB: Could not find a valid tablespace 
file for ``dbmail`.`#sql2-704-271``" stop
 :msg, contains, "[ERROR] InnoDB: If you are installing InnoDB, 
remember that you must create directories yourself, InnoDB does not 
create them" stop
 :msg, contains, "[ERROR] InnoDB: Operating system error number 2 in a 
file operation" stop
 :msg, contains, "[ERROR] InnoDB: The error means the system cannot 
find the path specified" stop
 :programname, isequal, "mysqld" 
-/Volumes/dune/www-servers/_logs/mysql_error.log

 :programname, isequal, "mysqld" stop
}

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

On Thu, Dec 8, 2022 at 6:43 PM Reindl Harald  wrote:

> and when you are at it get rid of "ib_logfile0" and "ib_logfile1" which
> hold data even in file-per-table mode you can't cleanup from crap caused
> by a crash 13 years ago

How can that happen when every once in a while upgrades require a full
clean shutdown with innodb_fast_shutdown=0 so that the ib_logfile*
have to be rebuild due to an on-disk format change?
More to the point, if that is a concern, why can you not shut down
cleanly with innodb_fast_shutdown=0, rm the files, and let MariaDB
re-create them completely clean and empty?

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

On Thu, Dec 8, 2022 at 6:25 PM Marko Mäkelä  wrote:

> MariaDB Server 10.4 introduced a new file format
> innodb_checksum_algorithm=full_crc32, and MariaDB Server 10.5 made it
> the default. Any files that were created when that setting is active
> are guaranteed to write any unused bytes as zeroes. It also fixes a
> peculiar design decision that some bytes of the page are not covered
> by any checksum, and that a page is considered valid if any of the
> non-full_crc32 checksums happen to produce a match. This includes the
> magic 0xdeadbeef for innodb_checksum_algorithm=none.
>
> Maybe we should consider eventually deprecating write support for the
> non-full_crc32 format, to force a fresh start.

Please don't. Some of us run MariaDB on file systems that do their own
block checksumming, and thus run innodb_checksum_algorithm=none,
and/or have databases many terabytes in size. With terabyte size
databases, doing a mysqldump+restore is realistically _never_ going to
happen.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start




Am 08.12.22 um 17:22 schrieb Marko Mäkelä:

On Thu, Dec 8, 2022 at 5:41 PM Reindl Harald  wrote:


I think you mean mysql, not postgresql...


no, i mean postgresql, that piece of crap breaking for years after
dist-upgrades because you need to do dump+restore at every version
change - real fun because you need to remember about the dump *before*


The other side of the coin is a questionable InnoDB "optimization"
that was finally fixed in MySQL 5.1.48 (in 2010) due to an external
bug report. Heikki Tuuri firmly believed that zeroing out pages when
they are initialized burns too many CPU cycles, so it is better to
just write whatever garbage to any unused bytes in data pages. He
actually forbade me to fix it back in 2004 or 2005, and forbade me to
spend any time to prove what kind of bad things could happen. (Back
then, even the FIL_PAGE_TYPE was written uninitialized to anything
other than B-tree index pages. So, if the page type field said
FIL_PAGE_INDEX, you could not say if it actually was an index page.)


WTF - but that don't change anything - you just can't dump+restore multi 
GB large databases in production environments and i expect from a new 
software version that it simply can read it's older data



I have tried to keep the implications in mind, but there has been at
least one serious bug regarding this:
https://jira.mariadb.org/browse/MDEV-27800


"cool" the only server where i care about InnoDB at all dates back to 2009


If a database with InnoDB had been originally initialized before MySQL
5.1.48 and you kept upgrading the binary files, you could have a
similar ticking time-bomb in some other data structure, which might be
forgotten when a future minor change to the data file format is made.
This would not be caught in normal upgrade tests, which would
initialize a database using a previous server version (say, 10.2) and
then upgrade to 10.3. To catch something like this, you would have to
initialize and populate the database in MySQL 5.1.47 or earlier, with
a suitable usage pattern that actually causes some unused to contain
suitably unlucky garbage bytes. (On a freshly initialized database
they could easily be 0.)


p - when you can read the data for a dump you could re-create the 
innodb-files like due "alter/optimize table" online


and when you are at it get rid of "ib_logfile0" and "ib_logfile1" which 
hold data even in file-per-table mode you can't cleanup from crap caused 
by a crash 13 years ago



MariaDB Server 10.4 introduced a new file format
innodb_checksum_algorithm=full_crc32, and MariaDB Server 10.5 made it
the default. Any files that were created when that setting is active
are guaranteed to write any unused bytes as zeroes. It also fixes a
peculiar design decision that some bytes of the page are not covered
by any checksum, and that a page is considered valid if any of the
non-full_crc32 checksums happen to produce a match. This includes the
magic 0xdeadbeef for innodb_checksum_algorithm=none.


and what happens over the long with data that has no checksums at all 
because it was possible to completly disable them in the past?



Maybe we should consider eventually deprecating write support for the
non-full_crc32 format, to force a fresh start.



p - when you can read the data for a dump you could re-create the 
innodb-files like due "alter/optimize table" online


and when you are at it get rid of "ib_logfile0" and "ib_logfile1" which 
hold data even in file-per-table mode you can't cleanup from crap caused 
by a crash 13 years ago



___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-12-08 Thread Marko Mäkelä

On Thu, Dec 8, 2022 at 5:41 PM Reindl Harald  wrote:
> > 
> > I think you mean mysql, not postgresql...
>
> no, i mean postgresql, that piece of crap breaking for years after
> dist-upgrades because you need to do dump+restore at every version
> change - real fun because you need to remember about the dump *before*

The other side of the coin is a questionable InnoDB "optimization"
that was finally fixed in MySQL 5.1.48 (in 2010) due to an external
bug report. Heikki Tuuri firmly believed that zeroing out pages when
they are initialized burns too many CPU cycles, so it is better to
just write whatever garbage to any unused bytes in data pages. He
actually forbade me to fix it back in 2004 or 2005, and forbade me to
spend any time to prove what kind of bad things could happen. (Back
then, even the FIL_PAGE_TYPE was written uninitialized to anything
other than B-tree index pages. So, if the page type field said
FIL_PAGE_INDEX, you could not say if it actually was an index page.)

I have tried to keep the implications in mind, but there has been at
least one serious bug regarding this:
https://jira.mariadb.org/browse/MDEV-27800

If a database with InnoDB had been originally initialized before MySQL
5.1.48 and you kept upgrading the binary files, you could have a
similar ticking time-bomb in some other data structure, which might be
forgotten when a future minor change to the data file format is made.
This would not be caught in normal upgrade tests, which would
initialize a database using a previous server version (say, 10.2) and
then upgrade to 10.3. To catch something like this, you would have to
initialize and populate the database in MySQL 5.1.47 or earlier, with
a suitable usage pattern that actually causes some unused to contain
suitably unlucky garbage bytes. (On a freshly initialized database
they could easily be 0.)

MariaDB Server 10.4 introduced a new file format
innodb_checksum_algorithm=full_crc32, and MariaDB Server 10.5 made it
the default. Any files that were created when that setting is active
are guaranteed to write any unused bytes as zeroes. It also fixes a
peculiar design decision that some bytes of the page are not covered
by any checksum, and that a page is considered valid if any of the
non-full_crc32 checksums happen to produce a match. This includes the
magic 0xdeadbeef for innodb_checksum_algorithm=none.

Maybe we should consider eventually deprecating write support for the
non-full_crc32 format, to force a fresh start.

Note: We still spend effort to allow in-place binary upgrades. My last
example of that was https://jira.mariadb.org/browse/MDEV-29694,
removing one of the innovative InnoDB features: the change buffer
a.k.a. insert buffer, because it has been the source of many
hard-to-debug index corruption bugs.

With best regards,

Marko
-- 
Marko Mäkelä, Lead Developer InnoDB
MariaDB Corporation

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start




Am 08.12.22 um 16:29 schrieb Jogchum Reitsma:

Hi Reindl,

Op 08-12-2022 om 08:31 schreef Reindl Harald:
FIRST: can you stop using "rewrite-all" on mailing-lists? when the 
useless offlist-copy is faster the dupes-filter on our server is 
supressing the list-copy and you break my reply-list button in 
thunderbird
OK, I'm sorry for that. But on some other mailing lists people ask 
explicitly to use reply all, so it's not always easy to do it the right 
way. But as you can see this goes only to the list.


I agree with you that this thread should com to an end, so I followed 
your advice, and dumped all my own tables, one by one, in 
.sql files. Checked them, are OK.



I think you mean mysql, not postgresql...


no, i mean postgresql, that piece of crap breaking for years after 
dist-upgrades because you need to do dump+restore at every version 
change - real fun because you need to remember about the dump *before*


I could dump table mysql as user root. The tables information_schema and 
performance_schema I could not dump. I suppose that is no problem?


performance_schema is not really a table


If indeed that is no problem, I think the following steps are necessary:

- give ctrl-\ to the command running the 10.7.7 dbserver to gracefully 
stop it

- umount the --bind mount to my database files
- rename the directory where my database files are stored,
- create a new, empty datadir in my home directory
   ( I know that is not standard; we discussed it in this thread 
before.but I have several reasons to want it there.

   After all, it has worked fine this way for some 20 years
   Of course I always can mount --bind it to a more standard place, if 
that is necessary or preferable)
- delete the file structures created by untarring 
/usr/local/mariadb-10.6.10-linux-systemd-x86_64.tar.gz and 
/usr/local/mariadb-10.7.7-linux-systemd-x86_64.tar.gz

- uninstall the mariadb package from the OS
- remove /run/mysql (holds the lock file)
- move /var/log/mysql to a place in my home system

Have I forgotten something?


man locate
man updatedb

there souldn't be any mysql/mariadb stuff on the machine except your 
backups and files provided by client libraries which are pretty sure 
deps of other packages


After that, re-install the package from my OS. I would think the install 
process creates an empty database,  which I can populate with the dumps 
just created.


i guess yes, otherwise 
https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/data-directory-initialization.html


___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-12-08 Thread Jogchum Reitsma


Hi Reindl,

Op 08-12-2022 om 08:31 schreef Reindl Harald:
FIRST: can you stop using "rewrite-all" on mailing-lists? when the 
useless offlist-copy is faster the dupes-filter on our server is 
supressing the list-copy and you break my reply-list button in 
thunderbird
OK, I'm sorry for that. But on some other mailing lists people ask 
explicitly to use reply all, so it's not always easy to do it the right 
way. But as you can see this goes only to the list.


I agree with you that this thread should com to an end, so I followed 
your advice, and dumped all my own tables, one by one, in 
.sql files. Checked them, are OK.




I think you mean mysql, not postgresql...

I could dump table mysql as user root. The tables information_schema and 
performance_schema I could not dump. I suppose that is no problem?


If indeed that is no problem, I think the following steps are necessary:

- give ctrl-\ to the command running the 10.7.7 dbserver to gracefully 
stop it

- umount the --bind mount to my database files
- rename the directory where my database files are stored,
- create a new, empty datadir in my home directory
  ( I know that is not standard; we discussed it in this thread 
before.but I have several reasons to want it there.

  After all, it has worked fine this way for some 20 years
  Of course I always can mount --bind it to a more standard place, if  
that is necessary or preferable)
- delete the file structures created by untarring 
/usr/local/mariadb-10.6.10-linux-systemd-x86_64.tar.gz and 
/usr/local/mariadb-10.7.7-linux-systemd-x86_64.tar.gz

- uninstall the mariadb package from the OS
- remove /run/mysql (holds the lock file)
- move /var/log/mysql to a place in my home system

Have I forgotten something?

After that, re-install the package from my OS. I would think the install 
process creates an empty database,  which I can populate with the dumps 
just created.


Thanks for all your replies, and sorry for the misunderstandings that 
made it so hard to help me. Of course that's never been my intention, I 
hope you can understand that.



that way this thread could come to an end


Hopefully it does...

regards Jogchum



___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp




___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-12-07 Thread Reindl Harald

FIRST: can you stop using "rewrite-all" on mailing-lists? when the 
useless offlist-copy is faster the dupes-filter on our server is 
supressing the list-copy and you break my reply-list button in thunderbird


Am 08.12.22 um 08:13 schrieb Jogchum Reitsma:

Op 07-12-2022 om 23:14 schreef Reindl Harald:

if it's a different one maybe permissions are a problem (again)
Well, as a normal user. But as I stated, there is no problem with *this* 
command: this works, and I get a functioning db-system.


why are you making it so hard to help you - is it THE DAMNED SAME USER 
the service would run as - in case "as normal user" means your ordianry 
login user it's not the same the systemd-service would run as


After that, I tried to start the version that comes with the OS 
(OpenSuse Thumbleweed) trough systemctl start mariadb, without success.


so the problem is still not really solved?

if that's the case for the sake of god dump your data as soon as you 
get the server running, 

As I said, I can get the server running, only not through systemd


no - you mixing a manually started completly diffrent binary versus a 
systemd-started with another binary running under a different user with 
pretty likely different versions


common sense says: too much differences at one time

remove all nonsense and mysql-data from the system, start from scratch 
and import the dumps as it's normal for postgresql


that way this thread could come to an end

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-12-07 Thread Jogchum Reitsma


Op 07-12-2022 om 23:14 schreef Reindl Harald:



Am 07.12.22 um 16:53 schrieb Jogchum Reitsma:

Op 07-12-2022 om 12:15 schreef Reindl Harald:



Am 07.12.22 um 10:27 schrieb Jogchum Reitsma:

Sorry, my bad: of course I should have started mysqld, not mysql.


besides that you should notice when you try to start a non-existing 
service what is really bad: you randomly mix "mysqld", "mysql" and 
"mariadb" as service name in your posts


in the worst case they are really existing but doing different things

Starting 10.7.7 works, and indeed recreates ib_logfile0. All my 
databases are there, no corruption AFAICS.


Shut it down with kill -15, and the logfile reports a normal shutdown


you shouldn't use "kill" at all fro shutdown when you start 
something as systemd-unit


I  started the he 10.7.7 version not through systemd, but by issuing 
/usr/local/mariadb-10.7.7-linux-systemd-x86_64/bin/mysqld in the 
terminal, as advised earlier in this conversation.


as which user?

if it's a different one maybe permissions are a problem (again)
Well, as a normal user. But as I stated, there is no problem with *this* 
command: this works, and I get a functioning db-system.


"Sorry, my bad: of course I should have started mysqld, not mysql" 
should have been obvious - i mean a sevrer is a background process 
versus an interactive sql shell

That is what I meant to say, I inadvertently forgot the 'd' after 'mysql'.



This way I managed to get a working db-server, and access to my data.

I stopped that by SIGQUIT, and this gives a correct shutdown.


15 is SIGTERM, lucky you didn't find the number for SIGQUIT

I always use the numbers, not the variant in words.


After that, I tried to start the version that comes with the OS 
(OpenSuse Thumbleweed) trough systemctl start mariadb, without success.


so the problem is still not really solved?

if that's the case for the sake of god dump your data as soon as you 
get the server running, 

As I said, I can get the server running, only not through systemd
remove all nonsense and mysql-data from the system, start from scratch 
and import the dumps as it's normal for postgresql


my mysql-data dirs date back to 2002 and where used on MacOS, Linux 
and Widows over the past years, so strange when mysql/mariadb acts 
that strange, but so be it



Hope this clarifies the steps taken


that whole thread is a mess

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp




___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-12-07 Thread Reindl Harald




Am 07.12.22 um 16:53 schrieb Jogchum Reitsma:

Op 07-12-2022 om 12:15 schreef Reindl Harald:



Am 07.12.22 um 10:27 schrieb Jogchum Reitsma:

Sorry, my bad: of course I should have started mysqld, not mysql.


besides that you should notice when you try to start a non-existing 
service what is really bad: you randomly mix "mysqld", "mysql" and 
"mariadb" as service name in your posts


in the worst case they are really existing but doing different things

Starting 10.7.7 works, and indeed recreates ib_logfile0. All my 
databases are there, no corruption AFAICS.


Shut it down with kill -15, and the logfile reports a normal shutdown


you shouldn't use "kill" at all fro shutdown when you start something 
as systemd-unit


I  started the he 10.7.7 version not through systemd, but by issuing 
/usr/local/mariadb-10.7.7-linux-systemd-x86_64/bin/mysqld in the 
terminal, as advised earlier in this conversation.


as which user?

if it's a different one maybe permissions are a problem (again)

"Sorry, my bad: of course I should have started mysqld, not mysql" 
should have been obvious - i mean a sevrer is a background process 
versus an interactive sql shell


This way I managed to 
get a working db-server, and access to my data.


I stopped that by SIGQUIT, and this gives a correct shutdown.


15 is SIGTERM, lucky you didn't find the number for SIGQUIT

After that, I tried to start the version that comes with the OS 
(OpenSuse Thumbleweed) trough systemctl start mariadb, without success.


so the problem is still not really solved?

if that's the case for the sake of god dump your data as soon as you get 
the server running, remove all nonsense and mysql-data from the system, 
start from scratch and import the dumps as it's normal for postgresql


my mysql-data dirs date back to 2002 and where used on MacOS, Linux and 
Widows over the past years, so strange when mysql/mariadb acts that 
strange, but so be it



Hope this clarifies the steps taken


that whole thread is a mess

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-12-07 Thread Jogchum Reitsma


Op 07-12-2022 om 12:15 schreef Reindl Harald:



Am 07.12.22 um 10:27 schrieb Jogchum Reitsma:

Sorry, my bad: of course I should have started mysqld, not mysql.


besides that you should notice when you try to start a non-existing 
service what is really bad: you randomly mix "mysqld", "mysql" and 
"mariadb" as service name in your posts


in the worst case they are really existing but doing different things

Starting 10.7.7 works, and indeed recreates ib_logfile0. All my 
databases are there, no corruption AFAICS.


Shut it down with kill -15, and the logfile reports a normal shutdown


you shouldn't use "kill" at all fro shutdown when you start something 
as systemd-unit


___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp


Hi Reindl,

I  started the he 10.7.7 version not through systemd, but by issuing 
/usr/local/mariadb-10.7.7-linux-systemd-x86_64/bin/mysqld in the 
terminal, as advised earlier in this conversation. This way I managed to 
get a working db-server, and access to my data.


I stopped that by SIGQUIT, and this gives a correct shutdown.

After that, I tried to start the version that comes with the OS 
(OpenSuse Thumbleweed) trough systemctl start mariadb, without success.


Hope this clarifies the steps taken.

regards, Jogchum
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-12-07 Thread Reindl Harald





Am 07.12.22 um 10:27 schrieb Jogchum Reitsma:

Sorry, my bad: of course I should have started mysqld, not mysql.


besides that you should notice when you try to start a non-existing 
service what is really bad: you randomly mix "mysqld", "mysql" and 
"mariadb" as service name in your posts


in the worst case they are really existing but doing different things

Starting 10.7.7 works, and indeed recreates ib_logfile0. All my 
databases are there, no corruption AFAICS.


Shut it down with kill -15, and the logfile reports a normal shutdown


you shouldn't use "kill" at all fro shutdown when you start something as 
systemd-unit


___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-12-07 Thread Jogchum Reitsma


Op 06-12-2022 om 16:43 schreef Jogchum Reitsma:

Op 16-11-2022 om 01:49 schreef Daniel Black:

<...>

Read Marko's email again.

10.8+ won't start without a ib_logfile0. This is what not starting looks like.

Start it with at 10.7 version which will recreate ib_logfile0, and if
you're lucky, you maybe won't have corrupted data because of this.
Then try 10.8+.

I just correctedhttps://wiki.archlinux.org/title/MariaDB.

If you see other sites saying delete ib_logfile0, please share your
experience with them

___
Mailing list:https://launchpad.net/~maria-discuss
Post to :maria-discuss@lists.launchpad.net
Unsubscribe :https://launchpad.net/~maria-discuss
More help   :https://help.launchpad.net/ListHelp


Hi,

So I downloaded and installed 10.7.7.

Bu now I get

/usr/local/mariadb-10.7.7-linux-systemd-x86_64/bin/mysql
ERROR 2002 (HY000): Can't connect to local server through socket 
'/var/run/mysql/mysql.sock'


For now I have chmod /var/run/mysql to 777, so is writable by me when 
I try to start 10.7.7. But indeed the directory is empty.


In both /etc/my.cnf as ~/.my.cnf the socket is set to 
/var/run/mysql/mysql.sock


Hope you have some ideas to solve this one...

regards, Jogchum


Sorry, my bad: of course I should have started mysqld, not mysql.

Starting 10.7.7 works, and indeed recreates ib_logfile0. All my 
databases are there, no corruption AFAICS.


Shut it down with kill -15, and the logfile reports a normal shutdown:

#

cat mysqld.log
2022-12-07  9:29:20 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
2022-12-07  9:29:20 0 [Note] InnoDB: Number of transaction pools: 1
2022-12-07  9:29:20 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2022-12-07  9:29:20 0 [Note] InnoDB: Using Linux native AIO
2022-12-07  9:29:20 0 [Note] InnoDB: Initializing buffer pool, total 
size = 134217728, chunk size = 134217728
2022-12-07  9:29:20 0 [Note] InnoDB: Completed initialization of buffer 
pool

2022-12-07  9:29:21 0 [Note] InnoDB: 128 rollback segments are active.
2022-12-07  9:29:21 0 [Note] InnoDB: Creating shared tablespace for 
temporary tables
2022-12-07  9:29:21 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 
MB. Physically writing the file full; Please wait ...

2022-12-07  9:29:21 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2022-12-07  9:29:21 0 [Note] InnoDB: 10.7.7 started; log sequence number 
578191915; transaction id 2935782
2022-12-07  9:29:21 0 [Note] InnoDB: Loading buffer pool(s) from 
/var/lib/mysql/ib_buffer_pool

2022-12-07  9:29:21 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-12-07  9:29:21 0 [Warning] 'innodb-file-format' was removed. It 
does nothing now and exists only for compatibility with old my.cnf files.

2022-12-07  9:29:22 0 [Note] Server socket created on IP: '0.0.0.0'.
2022-12-07  9:29:22 0 [Note] 
/usr/local/mariadb-10.7.7-linux-systemd-x86_64/bin/mysqld: ready for 
connections.
Version: '10.7.7-MariaDB-log'  socket: '/var/run/mysql/mysql.sock' 
 port: 3306  MariaDB Server
2022-12-07  9:29:23 0 [Note] InnoDB: Buffer pool(s) load completed at 
221207  9:29:23
2022-12-07  9:39:23 0 [Note] 
/usr/local/mariadb-10.7.7-linux-systemd-x86_64/bin/mysqld (initiated by: 
unknown): Normal shutdown

2022-12-07  9:39:23 0 [Note] InnoDB: FTS optimize thread exiting.
2022-12-07  9:39:24 0 [Note] InnoDB: Starting shutdown...
2022-12-07  9:39:24 0 [Note] InnoDB: Dumping buffer pool(s) to 
/var/lib/mysql/ib_buffer_pool
2022-12-07  9:39:24 0 [Note] InnoDB: Restricted to 2028 pages due to 
innodb_buf_pool_dump_pct=25
2022-12-07  9:39:24 0 [Note] InnoDB: Buffer pool(s) dump completed at 
221207  9:39:24
2022-12-07  9:39:24 0 [Note] InnoDB: Removed temporary tablespace data 
file: "./ibtmp1"
2022-12-07  9:39:24 0 [Note] InnoDB: Shutdown completed; log sequence 
number 578191927; transaction id 2935784
2022-12-07  9:39:24 0 [Note] 
/usr/local/mariadb-10.7.7-linux-systemd-x86_64/bin/mysqld: Shutdown complete


###

So far so good.

But after that, still no luck in starting the server the regular way, 
i.e. with systemctl start mariadb.


##

systemctl start mariadb

Job for mariadb.service failed because the control process exited with 
error code.
See "systemctl status mariadb.service" and "journalctl -xeu 
mariadb.service" for details


###

systemctl status mariadb.service
×mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; 
preset: disabled)

   Drop-In: /etc/systemd/system/mariadb.service.d
└─override.conf
Active: failed(Result: exit-code) since Wed 2022-12-07 09:52:46 
CET; 1min 51s ago

  Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
   Process: 93046 ExecStartPre=/usr/libexec/mysql/mysql-systemd-helper 
install (code=exited, status=0/SUCCESS)
   Process: 93059

Re: [Maria-discuss] Maria-db refuses to start

2022-12-06 Thread Jogchum Reitsma


Op 16-11-2022 om 01:49 schreef Daniel Black:

The log from this failure (/var/log/mysqld.log):

2022-11-10 20:22:34 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
2022-11-10 20:22:34 0 [Note] InnoDB: Number of transaction pools: 1
2022-11-10 20:22:34 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2022-11-10 20:22:34 0 [Note] InnoDB: Using Linux native AIO
2022-11-10 20:22:34 0 [Note] InnoDB: Initializing buffer pool, total size = 
128.000MiB, chunk size = 2.000MiB
2022-11-10 20:22:34 0 [Note] InnoDB: Completed initialization of buffer pool
2022-11-10 20:22:34 0 [ERROR] InnoDB: File ./ib_logfile0 was not found
2022-11-10 20:22:34 0 [ERROR] InnoDB: Plugin initialization aborted with error 
Generic error
2022-11-10 20:22:34 0 [Note] InnoDB: Starting shutdown...
2022-11-10 20:22:35 0 [ERROR] Plugin 'InnoDB' init function returned error.
2022-11-10 20:22:35 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE 
failed.
2022-11-10 20:22:35 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-11-10 20:22:35 0 [Warning] 'innodb-file-format' was removed. It does 
nothing now and exists only for compatibility with old my.cnf files.
2022-11-10 20:22:35 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2022-11-10 20:22:35 0 [ERROR] Aborting

##

No idea how to solve this, again...

Read Marko's email again.

10.8+ won't start without a ib_logfile0. This is what not starting looks like.

Start it with at 10.7 version which will recreate ib_logfile0, and if
you're lucky, you maybe won't have corrupted data because of this.
Then try 10.8+.

I just correctedhttps://wiki.archlinux.org/title/MariaDB.

If you see other sites saying delete ib_logfile0, please share your
experience with them

___
Mailing list:https://launchpad.net/~maria-discuss
Post to :maria-discuss@lists.launchpad.net
Unsubscribe :https://launchpad.net/~maria-discuss
More help   :https://help.launchpad.net/ListHelp


Hi,

So I downloaded and installed 10.7.7.

Bu now I get

/usr/local/mariadb-10.7.7-linux-systemd-x86_64/bin/mysql
ERROR 2002 (HY000): Can't connect to local server through socket 
'/var/run/mysql/mysql.sock'


For now I have chmod /var/run/mysql to 777, so is writable by me when I 
try to start 10.7.7. But indeed the directory is empty.


In both /etc/my.cnf as ~/.my.cnf the socket is set to 
/var/run/mysql/mysql.sock


Hope you have some ideas to solve this one...

regards, Jogchum

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-11-16 Thread Jogchum Reitsma


Op 16-11-2022 om 01:49 schreef Daniel Black:

Read Marko's email again.

10.8+ won't start without a ib_logfile0. This is what not starting looks like.

Start it with at 10.7 version which will recreate ib_logfile0, and if
you're lucky, you maybe won't have corrupted data because of this.
Then try 10.8+.

I just corrected https://wiki.archlinux.org/title/MariaDB.

If you see other sites saying delete ib_logfile0, please share your
experience with them

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp


OK thanks for your feedback.

The coming two weeks I'll be very busy with other projects, so it will 
take a time before I can get into this again.


AFAICS from  running the 10.6 version, the data is still good, and I've 
made a copy of the data-dir (besides of course normal back-ups).


It seems to me this version stuff is too sensitive: why won't 10.8  open 
a database which is running fine in 10.6? Should be some backwards 
compatibility, shouldn't it?


Any case, I get a lot of help here, so thanks for that!

regards, Jogchum



___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-11-15 Thread Daniel Black

> The log from this failure (/var/log/mysqld.log):
>
> 2022-11-10 20:22:34 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
> 2022-11-10 20:22:34 0 [Note] InnoDB: Number of transaction pools: 1
> 2022-11-10 20:22:34 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
> 2022-11-10 20:22:34 0 [Note] InnoDB: Using Linux native AIO
> 2022-11-10 20:22:34 0 [Note] InnoDB: Initializing buffer pool, total size = 
> 128.000MiB, chunk size = 2.000MiB
> 2022-11-10 20:22:34 0 [Note] InnoDB: Completed initialization of buffer pool
> 2022-11-10 20:22:34 0 [ERROR] InnoDB: File ./ib_logfile0 was not found
> 2022-11-10 20:22:34 0 [ERROR] InnoDB: Plugin initialization aborted with 
> error Generic error
> 2022-11-10 20:22:34 0 [Note] InnoDB: Starting shutdown...
> 2022-11-10 20:22:35 0 [ERROR] Plugin 'InnoDB' init function returned error.
> 2022-11-10 20:22:35 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE 
> ENGINE failed.
> 2022-11-10 20:22:35 0 [Note] Plugin 'FEEDBACK' is disabled.
> 2022-11-10 20:22:35 0 [Warning] 'innodb-file-format' was removed. It does 
> nothing now and exists only for compatibility with old my.cnf files.
> 2022-11-10 20:22:35 0 [ERROR] Unknown/unsupported storage engine: InnoDB
> 2022-11-10 20:22:35 0 [ERROR] Aborting
>
> ##
>
> No idea how to solve this, again...

Read Marko's email again.

10.8+ won't start without a ib_logfile0. This is what not starting looks like.

Start it with at 10.7 version which will recreate ib_logfile0, and if
you're lucky, you maybe won't have corrupted data because of this.
Then try 10.8+.

I just corrected https://wiki.archlinux.org/title/MariaDB.

If you see other sites saying delete ib_logfile0, please share your
experience with them

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-11-10 Thread Reindl Harald

PLEASE don't use "reply-all" on mailing-lists, you break "reply-list" 
for capable users!


Am 10.11.22 um 20:36 schrieb Jogchum Reitsma:

Op 10-11-2022 om 13:39 schreef Reindl Harald:
for the sake of god open "/usr/lib/systemd/system/mariadb.service" and 
remove or comment out the "ExecStartPre" until your problems are solved


"ExecStartPre=/usr/libexec/mysql/mysql-systemd-helper upgrade 
(code=exited, status=1/FAILURE)" is some distribution sepcific nonsense
I have no idea what these "install" and "upgrade " ExecStartPre do, but 
I commented them  out, did a systemctl daemon-reload, and issued 
systemctl start mariadb.service again.


Again with no luck:

systemctl status mariadb.service
×mariadb.service - MariaDB database server
 Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; 
preset: disabled)

    Drop-In: /etc/systemd/system/mariadb.service.d
 └─override.conf
 Active: failed(Result: exit-code) since Thu 2022-11-10 20:22:35 
CET; 17s ago

   Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
    Process: 35754 ExecStart=/usr/libexec/mysql/mysql-systemd-helper 
start (code=exited, status=1/FAILURE)

   Main PID: 35754 (code=exited, status=1/FAILURE)
 Status: "MariaDB server is down"


"ExecStart=/usr/libexec/mysql/mysql-systemd-helper" is still some 
distribution crap


throw away that crap by create a unit in 
"/etc/systemd/system/mariadb.service" which will completly override the 
distro stuff


* there is no need for helpers
* there is no need for mysqld_safe and other nonsense
* systemd can babysit services for years
* that below is my mysqld/mriadb unit for years

"Type=notify" should work unless some moron compiled the binary, 
"Type=simple" would need helper scripts if services are ordered after 
the database


"After=network-up.service" should be replaced by whatever your 
distribution is using to start the network, as for most of the critical 
stuff i simply refuse to use the distribution nonsense and replaced 
newtork/iptables/ipset by my own services


"AmbientCapabilities=CAP_IPC_LOCK CAP_SYS_NICE" as well as "User=mysql" 
and "Group=mysql" are they key that the service can be started from the 
first second as the intended user (port 3306 don't need super user 
privileges) and all that crap around with all it's indirection makes 
debugging impossible and introduces security holes (there was a CVE in 
context mysqld_safe a few years ago)


minimize the crap used to start services to what is *really* needed

---

[Unit]
Description=MariaDB Database
After=network-up.service
Before=crond.service
ConditionPathExists=/etc/my.cnf

[Service]
Type=notify
KillMode=process
KillSignal=SIGTERM
SendSIGKILL=no

User=mysql
Group=mysql

ExecStart=/usr/libexec/mysqld --defaults-file=/etc/my.cnf 
--pid-file=/dev/null

Environment="LANG=C.UTF-8"
Restart=always
RestartSec=1
TimeoutSec=300
LimitNOFILE=infinity
LimitMEMLOCK=infinity
OOMScoreAdjust=-1000
TasksMax=2048

AmbientCapabilities=CAP_IPC_LOCK CAP_SYS_NICE
CapabilityBoundingSet=CAP_IPC_LOCK CAP_SYS_NICE
LockPersonality=yes
MemoryDenyWriteExecute=yes
NoNewPrivileges=yes
PrivateDevices=yes
PrivateTmp=yes
ProtectClock=yes
ProtectControlGroups=yes
ProtectHome=yes
ProtectHostname=yes
ProtectKernelLogs=yes
ProtectKernelModules=yes
ProtectKernelTunables=yes
RestrictAddressFamilies=AF_UNIX AF_LOCAL AF_INET AF_INET6
RestrictNamespaces=yes
RestrictSUIDSGID=yes
SystemCallArchitectures=native
UMask=077

[Install]
WantedBy=multi-user.target

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-11-10 Thread Jogchum Reitsma


Op 10-11-2022 om 13:39 schreef Reindl Harald:



Am 10.11.22 um 12:06 schrieb Jogchum Reitsma:

Op 08-11-2022 om 22:54 schreef Daniel Black:

On Wed, Nov 9, 2022 at 12:01 AM Jogchum Reitsma
  wrote:

<...>
The directory /var/run/mysql doesn't exist


2022-11-08 13:37:33 0 [ERROR] Do you already have another server 
running on socket: /var/run/mysql/mysql.sock ?

This guess of an error should be fixed.


Well, in the meantime I have a running database, thanks to all the 
help I get here.


But I'm not quite out of trouble yet.

The last problem with starting 
/usr/local/mariadb-10.6.10-linux-systemd-x86_64/bin/mariadbd was 
solved by chmod 777 /var/run/mysql; this was needed because I start 
mariadb as a plain user for the moment. chmod on /var/log/msql gave 
me logging >

Once again I'm at a loss here - what causes mysqld not to start?

Any further help dearly appreciated


either "in the meantime I have a running database" or "what causes 
mysqld not to start" are possible


Maybe I did not express myself clearly enough:

1) "in the meantime I have a running database": I can have a running 
database, but only by sarting an older version (10.6.10) in an 
unorthodox way, i.e. by issuing 
/usr/local/mariadb-10.6.10-linux-systemd-x86_64/bin/mariadbd as a normal 
user.
Of course that's not what I want as a definitive solution, but this way 
I can get to my data.


2) "what causes mysqld not to start": starting the mariadb version that 
is supplied by my distro, thedefault way, i.e. by issuing systemctl 
start mariadb.service, des not work, and gives the errors I pasted in my 
previous message.


I hope this clarifies the what I meant to say.


and when it comes to "chmod on /var/log/msql gave me logging" show them

for the sake of god open "/usr/lib/systemd/system/mariadb.service" and 
remove or comment out the "ExecStartPre" until your problems are solved


"ExecStartPre=/usr/libexec/mysql/mysql-systemd-helper upgrade 
(code=exited, status=1/FAILURE)" is some distribution sepcific nonsense
I have no idea what these "install" and "upgrade " ExecStartPre do, but 
I commented them  out, did a systemctl daemon-reload, and issued 
systemctl start mariadb.service again.


Again with no luck:

systemctl status mariadb.service
×mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; 
preset: disabled)

   Drop-In: /etc/systemd/system/mariadb.service.d
└─override.conf
Active: failed(Result: exit-code) since Thu 2022-11-10 20:22:35 
CET; 17s ago

  Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
   Process: 35754 ExecStart=/usr/libexec/mysql/mysql-systemd-helper 
start (code=exited, status=1/FAILURE)

  Main PID: 35754 (code=exited, status=1/FAILURE)
Status: "MariaDB server is down"

   CPU: 80ms

##

journalctl -xeu mariadb.service

nov 10 20:20:51 linux-mkay systemd[1]: Starting MariaDB database server...
░░ Subject: A start job for unit mariadb.service has begun execution
░░Defined-By: systemd
░░Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░
░░A start job for unit mariadb.service has begun execution.
░░
░░The job identifier is 702208.
nov 10 20:20:51 linux-mkay mysql-systemd-helper[35509]: Checking MySQL 
configuration for obsolete options...
nov 10 20:20:51 linux-mkay mysql-systemd-helper[35509]: Trying to run 
upgrade of MySQL databases...
nov 10 20:20:51 linux-mkay mysql-systemd-helper[35509]: Stale files from 
previous upgrade detected, cleaned them up
nov 10 20:20:51 linux-mkay mysql-systemd-helper[35509]: Running 
protected MySQL...
nov 10 20:20:51 linux-mkay mysql-systemd-helper[35509]: Waiting for 
MySQL to start
nov 10 20:20:51 linux-mkay mysql-systemd-helper[35533]: 2022-11-10 
20:20:51 0 [Note] /usr/sbin/mysqld (server 10.9.3-MariaDB) starting as 
process 35533 ...

nov 10 20:21:51 linux-mkay mysql-systemd-helper[35509]: MySQL is still dead
nov 10 20:21:51 linux-mkay mysql-systemd-helper[35509]: MySQL didn't 
start, can't continue
nov 10 20:21:51 linux-mkay systemd[1]: mariadb.service: Control process 
exited, code=exited, status=1/FAILURE

░░ Subject: Unit process exited
░░Defined-By: systemd
░░Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░
░░An ExecStartPre= process belonging to unit mariadb.service has exited.
░░
░░The process' exit code is 'exited' and its exit status is 1.
nov 10 20:21:51 linux-mkay systemd[1]: mariadb.service: Failed with 
result 'exit-code'.

░░ Subject: Unit failed
░░Defined-By: systemd
░░Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░
░░The unit mariadb.service has entered the 'failed' state with result 
'exit-code'.
nov 10 20:21:51 linux-mkay systemd[1]: Failed to start MariaDB database 
server.

░░ Subject: A start job for unit mariadb.service has failed
░░Defined-By: systemd
░░Support:

Re: [Maria-discuss] Maria-db refuses to start

2022-11-10 Thread Reindl Harald





Am 10.11.22 um 12:06 schrieb Jogchum Reitsma:

Op 08-11-2022 om 22:54 schreef Daniel Black:

On Wed, Nov 9, 2022 at 12:01 AM Jogchum Reitsma
  wrote:

<...>
The directory /var/run/mysql doesn't exist


2022-11-08 13:37:33 0 [ERROR] Do you already have another server running on 
socket: /var/run/mysql/mysql.sock ?

This guess of an error should be fixed.


Well, in the meantime I have a running database, thanks to all the help 
I get here.


But I'm not quite out of trouble yet.

The last problem with starting 
/usr/local/mariadb-10.6.10-linux-systemd-x86_64/bin/mariadbd was solved 
by chmod 777 /var/run/mysql; this was needed because I start mariadb as 
a plain user for the moment. chmod on /var/log/msql gave me logging >

Once again I'm at a loss here - what causes mysqld not to start?

Any further help dearly appreciated


either "in the meantime I have a running database" or "what causes 
mysqld not to start" are possible and when it comes to "chmod on 
/var/log/msql gave me logging" show them


for the sake of god open "/usr/lib/systemd/system/mariadb.service" and 
remove or comment out the "ExecStartPre" until your problems are solved


"ExecStartPre=/usr/libexec/mysql/mysql-systemd-helper upgrade 
(code=exited, status=1/FAILURE)" is some distribution sepcific nonsense


in the real world it's enoug to ExecStart mysqld as user:ggroup mysql 
and skip all that nonsense including mysql_safe




___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-11-10 Thread Jogchum Reitsma


Op 08-11-2022 om 22:54 schreef Daniel Black:

On Wed, Nov 9, 2022 at 12:01 AM Jogchum Reitsma
  wrote:

<...>
The directory /var/run/mysql doesn't exist


2022-11-08 13:37:33 0 [ERROR] Do you already have another server running on 
socket: /var/run/mysql/mysql.sock ?

This guess of an error should be fixed.


Well, in the meantime I have a running database, thanks to all the help 
I get here.


But I'm not quite out of trouble yet.

The last problem with starting 
/usr/local/mariadb-10.6.10-linux-systemd-x86_64/bin/mariadbd was solved 
by chmod 777 /var/run/mysql; this was needed because I start mariadb as 
a plain user for the moment. chmod on /var/log/msql gave me logging, too.


All temporarily of course, it should be reverted to it's default state 
once everything runs normally.

But for now, I have a running database.

Alas, after stopping it with ctrl-\, I tried systemctl start 
mariadb.service (in the meantime the Thumbleweed version of mariadb is 
at 10.9.x)

But with no luck. It lasted a while, then came with

Job for mariadb.service failed because the control process exited with 
error code.
See "systemctl status mariadb.service" and "journalctl -xeu 
mariadb.service" for details.




systemctl status mariadb.service
×mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; 
preset: disabled)

   Drop-In: /etc/systemd/system/mariadb.service.d
└─override.conf
Active: failed(Result: exit-code) since Thu 2022-11-10 10:31:15 
CET; 52min ago

  Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
   Process: 7671 ExecStartPre=/usr/libexec/mysql/mysql-systemd-helper 
install (code=exited, status=0/SUCCESS)
   Process: 7683 ExecStartPre=/usr/libexec/mysql/mysql-systemd-helper 
upgrade (code=exited, status=1/FAILURE)

   CPU: 446ms

nov 10 10:30:15 linux-mkay mysql-systemd-helper[7683]: Trying to run 
upgrade of MySQL databases...
nov 10 10:30:15 linux-mkay mysql-systemd-helper[7683]: Stale files from 
previous upgrade detected, cleaned them up
nov 10 10:30:15 linux-mkay mysql-systemd-helper[7683]: Running protected 
MySQL...
nov 10 10:30:15 linux-mkay mysql-systemd-helper[7683]: Waiting for MySQL 
to start
nov 10 10:30:15 linux-mkay mysql-systemd-helper[7707]: 2022-11-10 
10:30:15 0 [Note] /usr/sbin/mysqld (server 10.9.3-MariaDB) starting as 
process 7707 ...

nov 10 10:31:15 linux-mkay mysql-systemd-helper[7683]: MySQL is still dead
nov 10 10:31:15 linux-mkay mysql-systemd-helper[7683]: MySQL didn't 
start, can't continue
nov 10 10:31:15 linux-mkay systemd[1]: mariadb.service: Control process 
exited, code=exited, status=1/FAILURE
nov 10 10:31:15 linux-mkay systemd[1]: mariadb.service: Failed with 
result 'exit-code'.
nov 10 10:31:15 linux-mkay systemd[1]: Failed to start MariaDB database 
server.




journalctl -xeu mariadb.service

nov 10 10:30:15 linux-mkay systemd[1]: Starting MariaDB database server...
░░ Subject: A start job for unit mariadb.service has begun execution
░░Defined-By: systemd
░░Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░
░░A start job for unit mariadb.service has begun execution.
░░
░░The job identifier is 558086.
nov 10 10:30:15 linux-mkay mysql-systemd-helper[7683]: Checking MySQL 
configuration for obsolete options...
nov 10 10:30:15 linux-mkay mysql-systemd-helper[7683]: Trying to run 
upgrade of MySQL databases...
nov 10 10:30:15 linux-mkay mysql-systemd-helper[7683]: Stale files from 
previous upgrade detected, cleaned them up
nov 10 10:30:15 linux-mkay mysql-systemd-helper[7683]: Running protected 
MySQL...
nov 10 10:30:15 linux-mkay mysql-systemd-helper[7683]: Waiting for MySQL 
to start
nov 10 10:30:15 linux-mkay mysql-systemd-helper[7707]: 2022-11-10 
10:30:15 0 [Note] /usr/sbin/mysqld (server 10.9.3-MariaDB) starting as 
process 7707 ...

nov 10 10:31:15 linux-mkay mysql-systemd-helper[7683]: MySQL is still dead
nov 10 10:31:15 linux-mkay mysql-systemd-helper[7683]: MySQL didn't 
start, can't continue
nov 10 10:31:15 linux-mkay systemd[1]: mariadb.service: Control process 
exited, code=exited, status=1/FAILURE

░░ Subject: Unit process exited
░░Defined-By: systemd
░░Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░
░░An ExecStartPre= process belonging to unit mariadb.service has exited.
░░
░░The process' exit code is 'exited' and its exit status is 1.
nov 10 10:31:15 linux-mkay systemd[1]: mariadb.service: Failed with 
result 'exit-code'.

░░ Subject: Unit failed
░░Defined-By: systemd
░░Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░
░░The unit mariadb.service has entered the 'failed' state with result 
'exit-code'.
nov 10 10:31:15 linux-mkay systemd[1]: Failed to start MariaDB database 
server.

░░

Re: [Maria-discuss] Maria-db refuses to start

2022-11-09 Thread Jogchum Reitsma


Op 08-11-2022 om 22:54 schreef Daniel Black:

On Wed, Nov 9, 2022 at 12:01 AM Jogchum Reitsma
 wrote:

Op 08-11-2022 om 13:04 schreef Marko Mäkelä:

Hi Jogchum,

On Tue, Nov 8, 2022 at 1:21 PM Jogchum Reitsma
 wrote:
[...]

2022-11-08 12:06:17 0 [Warning] 'innodb-file-format' was removed. It does 
nothing now and exists only for compatibility with old my.cnf files.
(repeated several times)
2022-11-08 12:06:17 0 [ERROR] 
/usr/local/mariadb-10.6.10-linux-systemd-x86_64/bin/mariadbd: unknown option 
'--skip-locking'
2022-11-08 12:06:17 0 [ERROR] Aborting

Was that the end of the output? No shutdown message from InnoDB, like this?

2022-11-08 14:03:03 0 [Note] InnoDB: Shutdown completed; log sequence
number 2150107; transaction id 3085

If there was no output like that, you should remove any line that says
"skip_locking" or "skip-locking" from the configuration file and
retry.

Marko

Yes, that was indeed the end of the output.

The skip-locking statement was not in /etc/my.cnf, but inmy.cnf in my home dir. 
Commented it out and now this complaint disappeared.

But now I get



2022-11-08 13:37:32 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
2022-11-08 13:37:32 0 [Note] InnoDB: Number of pools: 1
2022-11-08 13:37:32 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2022-11-08 13:37:32 0 [Note] InnoDB: Using Linux native AIO
2022-11-08 13:37:32 0 [Note] InnoDB: Initializing buffer pool, total size = 
134217728, chunk size = 134217728
2022-11-08 13:37:32 0 [Note] InnoDB: Completed initialization of buffer pool
2022-11-08 13:37:32 0 [Note] InnoDB: 128 rollback segments are active.
2022-11-08 13:37:32 0 [Note] InnoDB: Creating shared tablespace for temporary 
tables
2022-11-08 13:37:32 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. 
Physically writing the file full; Please wait ...
2022-11-08 13:37:32 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2022-11-08 13:37:32 0 [Note] InnoDB: 10.6.10 started; log sequence number 
578191791; transaction id 2935782
2022-11-08 13:37:32 0 [Note] InnoDB: Loading buffer pool(s) from 
/home/jogchum/mysql/ib_buffer_pool
2022-11-08 13:37:32 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-11-08 13:37:32 0 [Warning] 'innodb-file-format' was removed. It does 
nothing now and exists only for compatibility with old my.cnf files.
2022-11-08 13:37:33 0 [Note] Server socket created on IP: '0.0.0.0'.
2022-11-08 13:37:33 0 [ERROR] Can't start server : Bind on unix socket: No such 
file or directory

The directory /var/run/mysql doesn't exist
Not mentioned yet, the rights on /var/run/mysql are 755, so no problem 
there too.
Only thing remarkable to me is that it has one file, called 
"protecteddir.", owned by root:root. No idea if this is of any 
significance to the problem




2022-11-08 13:37:33 0 [ERROR] Do you already have another server running on 
socket: /var/run/mysql/mysql.sock ?

This guess of an error should be fixed.




___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-11-08 Thread Jogchum Reitsma


Op 08-11-2022 om 22:54 schreef Daniel Black:

On Wed, Nov 9, 2022 at 12:01 AM Jogchum Reitsma
 wrote:

<...>


2022-11-08 13:37:32 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
2022-11-08 13:37:32 0 [Note] InnoDB: Number of pools: 1
2022-11-08 13:37:32 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2022-11-08 13:37:32 0 [Note] InnoDB: Using Linux native AIO
2022-11-08 13:37:32 0 [Note] InnoDB: Initializing buffer pool, total size = 
134217728, chunk size = 134217728
2022-11-08 13:37:32 0 [Note] InnoDB: Completed initialization of buffer pool
2022-11-08 13:37:32 0 [Note] InnoDB: 128 rollback segments are active.
2022-11-08 13:37:32 0 [Note] InnoDB: Creating shared tablespace for temporary 
tables
2022-11-08 13:37:32 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. 
Physically writing the file full; Please wait ...
2022-11-08 13:37:32 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2022-11-08 13:37:32 0 [Note] InnoDB: 10.6.10 started; log sequence number 
578191791; transaction id 2935782
2022-11-08 13:37:32 0 [Note] InnoDB: Loading buffer pool(s) from 
/home/jogchum/mysql/ib_buffer_pool
2022-11-08 13:37:32 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-11-08 13:37:32 0 [Warning] 'innodb-file-format' was removed. It does 
nothing now and exists only for compatibility with old my.cnf files.
2022-11-08 13:37:33 0 [Note] Server socket created on IP: '0.0.0.0'.
2022-11-08 13:37:33 0 [ERROR] Can't start server : Bind on unix socket: No such 
file or directory

The directory /var/run/mysql doesn't exist

It does exist in my case, and has mysql:msql as owner:group




2022-11-08 13:37:33 0 [ERROR] Do you already have another server running on 
socket: /var/run/mysql/mysql.sock ?

This guess of an error should be fixed.




___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-11-08 Thread Daniel Black

On Wed, Nov 9, 2022 at 12:01 AM Jogchum Reitsma
 wrote:
>
> Op 08-11-2022 om 13:04 schreef Marko Mäkelä:
>
> Hi Jogchum,
>
> On Tue, Nov 8, 2022 at 1:21 PM Jogchum Reitsma
>  wrote:
> [...]
>
> 2022-11-08 12:06:17 0 [Warning] 'innodb-file-format' was removed. It does 
> nothing now and exists only for compatibility with old my.cnf files.
> (repeated several times)
> 2022-11-08 12:06:17 0 [ERROR] 
> /usr/local/mariadb-10.6.10-linux-systemd-x86_64/bin/mariadbd: unknown option 
> '--skip-locking'
> 2022-11-08 12:06:17 0 [ERROR] Aborting
>
> Was that the end of the output? No shutdown message from InnoDB, like this?
>
> 2022-11-08 14:03:03 0 [Note] InnoDB: Shutdown completed; log sequence
> number 2150107; transaction id 3085
>
> If there was no output like that, you should remove any line that says
> "skip_locking" or "skip-locking" from the configuration file and
> retry.
>
> Marko
>
> Yes, that was indeed the end of the output.
>
> The skip-locking statement was not in /etc/my.cnf, but inmy.cnf in my home 
> dir. Commented it out and now this complaint disappeared.
>
> But now I get
>
> 
>
> 2022-11-08 13:37:32 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
> 2022-11-08 13:37:32 0 [Note] InnoDB: Number of pools: 1
> 2022-11-08 13:37:32 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
> 2022-11-08 13:37:32 0 [Note] InnoDB: Using Linux native AIO
> 2022-11-08 13:37:32 0 [Note] InnoDB: Initializing buffer pool, total size = 
> 134217728, chunk size = 134217728
> 2022-11-08 13:37:32 0 [Note] InnoDB: Completed initialization of buffer pool
> 2022-11-08 13:37:32 0 [Note] InnoDB: 128 rollback segments are active.
> 2022-11-08 13:37:32 0 [Note] InnoDB: Creating shared tablespace for temporary 
> tables
> 2022-11-08 13:37:32 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. 
> Physically writing the file full; Please wait ...
> 2022-11-08 13:37:32 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
> 2022-11-08 13:37:32 0 [Note] InnoDB: 10.6.10 started; log sequence number 
> 578191791; transaction id 2935782
> 2022-11-08 13:37:32 0 [Note] InnoDB: Loading buffer pool(s) from 
> /home/jogchum/mysql/ib_buffer_pool
> 2022-11-08 13:37:32 0 [Note] Plugin 'FEEDBACK' is disabled.
> 2022-11-08 13:37:32 0 [Warning] 'innodb-file-format' was removed. It does 
> nothing now and exists only for compatibility with old my.cnf files.
> 2022-11-08 13:37:33 0 [Note] Server socket created on IP: '0.0.0.0'.
> 2022-11-08 13:37:33 0 [ERROR] Can't start server : Bind on unix socket: No 
> such file or directory

The directory /var/run/mysql doesn't exist


> 2022-11-08 13:37:33 0 [ERROR] Do you already have another server running on 
> socket: /var/run/mysql/mysql.sock ?

This guess of an error should be fixed.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-11-08 Thread Marko Mäkelä

Hi Jogchum,

I hope that someone else on this list can help you further with this.

One thing that you could try is to install the newer (10.8+) MariaDB
server package and see if InnoDB in the 10.6.10 happened to perform a
clean shutdown anyway. I do not see any messages about InnoDB crash
recovery, so it could be the case. If the upgrade still fails, you
will have to try to get the 10.6 server to start up and shut down.

Best regards,

Marko

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-11-08 Thread Jogchum Reitsma


Op 08-11-2022 om 13:04 schreef Marko Mäkelä:

Hi Jogchum,

On Tue, Nov 8, 2022 at 1:21 PM Jogchum Reitsma
  wrote:
[...]

2022-11-08 12:06:17 0 [Warning] 'innodb-file-format' was removed. It does 
nothing now and exists only for compatibility with old my.cnf files.
(repeated several times)
2022-11-08 12:06:17 0 [ERROR] 
/usr/local/mariadb-10.6.10-linux-systemd-x86_64/bin/mariadbd: unknown option 
'--skip-locking'
2022-11-08 12:06:17 0 [ERROR] Aborting

Was that the end of the output? No shutdown message from InnoDB, like this?

2022-11-08 14:03:03 0 [Note] InnoDB: Shutdown completed; log sequence
number 2150107; transaction id 3085

If there was no output like that, you should remove any line that says
"skip_locking" or "skip-locking" from the configuration file and
retry.

Marko


Yes, that was indeed the end of the output.

The skip-locking statement was not in /etc/my.cnf, but inmy.cnf in my 
home dir. Commented it out and now this complaint disappeared.


But now I get



2022-11-08 13:37:32 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
2022-11-08 13:37:32 0 [Note] InnoDB: Number of pools: 1
2022-11-08 13:37:32 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2022-11-08 13:37:32 0 [Note] InnoDB: Using Linux native AIO
2022-11-08 13:37:32 0 [Note] InnoDB: Initializing buffer pool, total 
size = 134217728, chunk size = 134217728
2022-11-08 13:37:32 0 [Note] InnoDB: Completed initialization of buffer 
pool

2022-11-08 13:37:32 0 [Note] InnoDB: 128 rollback segments are active.
2022-11-08 13:37:32 0 [Note] InnoDB: Creating shared tablespace for 
temporary tables
2022-11-08 13:37:32 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 
MB. Physically writing the file full; Please wait ...

2022-11-08 13:37:32 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2022-11-08 13:37:32 0 [Note] InnoDB: 10.6.10 started; log sequence 
number 578191791; transaction id 2935782
2022-11-08 13:37:32 0 [Note] InnoDB: Loading buffer pool(s) from 
/home/jogchum/mysql/ib_buffer_pool

2022-11-08 13:37:32 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-11-08 13:37:32 0 [Warning] 'innodb-file-format' was removed. It 
does nothing now and exists only for compatibility with old my.cnf files.

2022-11-08 13:37:33 0 [Note] Server socket created on IP: '0.0.0.0'.
2022-11-08 13:37:33 0 [ERROR] Can't start server : Bind on unix socket: 
No such file or directory
2022-11-08 13:37:33 0 [ERROR] Do you already have another server running 
on socket: /var/run/mysql/mysql.sock ?

2022-11-08 13:37:33 0 [ERROR] Aborting
Warning: Memory not freed: 280

##

(again no further output).

/var/run/mysql/mysql.sock odoes not exeist, and "pgrep mariadbd", "pgrep 
mysql" and "pgrep mysqld" show nothing, so no serer is running anymore.


ss -a | grep 3306 or
ss -a | grep /var/run/mysql

also gives nothing, so it seems there are no processes using that port.

We're getting closer it seems, but not quite there yet...

regards, Jogchum
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-11-08 Thread Jogchum Reitsma


Sent it by accident only to Marko

Op 08-11-2022 om 11:43 schreef Marko Mäkelä:

Hi Jogchum,

Sorry, I did not notice that you had a question for me.

Why do I understand that so well .. :-)

<...>
Yes, the steps look roughly correct, possibly except for the part of
shutting down the 10.6.10 server.

As far as the server is concerned, SIGTERM (signal 15) or SIGQUIT
should initiate a shutdown.

I would think that on systemd, the preferred way to start and stop
services would be the following:

systemctl start mariadb
systemctl stop mariadb

Systemd could actually take the role of mysqld_safe.

It could be simplest to start the server directly as "mariadbd" so
that nothing will keep restarting it after you initiated the shutdown.
If you run the process in the foreground, then ctrl-\ should be
SIGQUIT and it should initiate a shutdown.

You'd better check with "pgrep mariadbd" or "pgrep mysqld" that
nothing is running, both before starting and after shutting down the
server.

I will clarify the error message. For 10.3 and 10.4, it will look like this:
https://github.com/MariaDB/server/commit/9ac8be4e2980aa995117147e39ae5b7ad79fc980
Starting with 10.5, it may additionally suggest to use a version not
later than MariaDB 10.4.
Starting with 10.8, it may additionally suggest to use a version not
later than MariaDB 10.7.

With best regards,

Marko Mäkelä


Hi Marko,

Thanks a lot!

What happened so far:

On first start of the server by executing 
"/usr/local/mariadb-10.6.10-linux-systemd-x86_64/bin/mariadbd"as 
non-root from the command line,


it complained "Can't read dir of '/etc/my.cnf.d"; indeed that dir did 
not exist.


So I created it as root, and copied /etc/my.cnf to it.

Restarting mariadbd the same way, I got



/usr/local/mariadb-10.6.10-linux-systemd-x86_64/bin/mariadbd
Warning: skipping '!includedir /etc/my.cnf.d' directive as maximum 
includerecursion level was reached in file /etc/my.cnf.d/my.cnf at line 124
2022-11-08 12:06:16 0 [Note] 
/usr/local/mariadb-10.6.10-linux-systemd-x86_64/bin/mariadbd (server 
10.6.10-MariaDB-log) starting as process 93785 ...
/usr/local/mariadb-10.6.10-linux-systemd-x86_64/bin/mariadbd: Can't 
create file '/var/log/mysql/mysqld.log' (errno: 13 "Permission denied")

2022-11-08 12:06:16 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
2022-11-08 12:06:16 0 [Note] InnoDB: Number of pools: 1
2022-11-08 12:06:16 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2022-11-08 12:06:16 0 [Note] InnoDB: Using Linux native AIO
2022-11-08 12:06:16 0 [Note] InnoDB: Initializing buffer pool, total 
size = 134217728, chunk size = 134217728
2022-11-08 12:06:16 0 [Note] InnoDB: Completed initialization of buffer 
pool
2022-11-08 12:06:16 0 [Note] InnoDB: Starting crash recovery from 
checkpoint LSN=578191755,578191755
2022-11-08 12:06:17 0 [Note] InnoDB: Last binlog file 
'./mysql-bin.67', position 8811365

2022-11-08 12:06:17 0 [Note] InnoDB: 128 rollback segments are active.
2022-11-08 12:06:17 0 [Note] InnoDB: Removed temporary tablespace data 
file: "./ibtmp1"
2022-11-08 12:06:17 0 [Note] InnoDB: Creating shared tablespace for 
temporary tables
2022-11-08 12:06:17 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 
MB. Physically writing the file full; Please wait ...

2022-11-08 12:06:17 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2022-11-08 12:06:17 0 [Note] InnoDB: 10.6.10 started; log sequence 
number 578191767; transaction id 2935782

2022-11-08 12:06:17 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-11-08 12:06:17 0 [Note] InnoDB: Loading buffer pool(s) from 
/home/jogchum/mysql/ib_buffer_pool
2022-11-08 12:06:17 0 [Warning] 'innodb-file-format' was removed. It 
does nothing now and exists only for compatibility with old my.cnf files.

(repeated several times)
2022-11-08 12:06:17 0 [ERROR] 
/usr/local/mariadb-10.6.10-linux-systemd-x86_64/bin/mariadbd: unknown 
option '--skip-locking'

2022-11-08 12:06:17 0 [ERROR] Aborting



I'm not quite sure if this would be enough to do the final steps (i.e. 
remove the 10.6.10 version, and install the Tumbleweed version).

From the fact that innodb reports a crash recovery, I would say yes

But the last errors make me somewhat unsure?

regards, Jogchum
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-11-08 Thread Marko Mäkelä

Hi Jogchum,

Sorry, I did not notice that you had a question for me.

On Sun, Oct 16, 2022 at 11:02 AM Jogchum Reitsma
 wrote:
>> Op 14-10-2022 om 12:45 schreef Marko Mäkelä:
>>
>>Hi Jogchum,
>>
>>On Wed, Oct 12, 2022 at 4:30 PM Jogchum Reitsma
>> wrote:
>>[...]
>>
>>When trying to start it manually, I got the message
>
>>[ERROR] InnoDB: Upgrade after a crash is not supported. The redo log was
>>created with MariaDB 10.5.8.
>
>>Searching that message, I saw a post on
>>https://bbs.archlinux.org/viewtopic.php?id=259364
>
> <...>
>
>>The correct advice is to install MariaDB Server (any version between
>>10.5 and 10.7 should do), shut down the database, and then upgrade to
>>10.8.
>>
>>Before 10.5 (MDEV-12353), the last time the redo log format was
>>changed was in MariaDB Server 10.2. If you got that error message when
>>attempting to upgrade from something older than 10.5, then you should
>>use the exact same major version of MariaDB to perform the shutdown,
>>because there were some changes to the redo log format in each major
>>release between 10.1 and 10.5.
>>
>>I hope that someone can post a correction to that forum.
[...]
> I uninstalled the mariadb version from opensuse Tumbleweed,
> and downloaded and installed mariadb-10.6.10-linux-systemd-x86_64 (init 
> system systemd)in /usr/local, according to the steps explained in the 
> INSTALL_BINARY document.
>
> My assumption of the next steps to take is
>
> - create /usr/local/mysql/data
> - chown:chgrp it to mysql:mysql
> - in /etc/my.cnf, set /usr/local/mysql/data being the data directory (with 
> opensuse, that is normally /var/lib/mysql, by the way)
> - do a mount --bind from my real data directory to /usr/local/mysql/data
> - then start the database with /usr/local/bin/mysqld_safe --user=mysql &
>
> Are the steps correct?
>
> If so, I would think the next thing to do is to stop the server with kill -15 
> , remove the /usr/local/mariadb-10.6.10-linux-systemd-x86_64 
> directory,
> and install the current TW-version.

Yes, the steps look roughly correct, possibly except for the part of
shutting down the 10.6.10 server.

As far as the server is concerned, SIGTERM (signal 15) or SIGQUIT
should initiate a shutdown.

I would think that on systemd, the preferred way to start and stop
services would be the following:

systemctl start mariadb
systemctl stop mariadb

Systemd could actually take the role of mysqld_safe.

It could be simplest to start the server directly as "mariadbd" so
that nothing will keep restarting it after you initiated the shutdown.
If you run the process in the foreground, then ctrl-\ should be
SIGQUIT and it should initiate a shutdown.

You'd better check with "pgrep mariadbd" or "pgrep mysqld" that
nothing is running, both before starting and after shutting down the
server.

I will clarify the error message. For 10.3 and 10.4, it will look like this:
https://github.com/MariaDB/server/commit/9ac8be4e2980aa995117147e39ae5b7ad79fc980
Starting with 10.5, it may additionally suggest to use a version not
later than MariaDB 10.4.
Starting with 10.8, it may additionally suggest to use a version not
later than MariaDB 10.7.

With best regards,

Marko Mäkelä
-- 
Marko Mäkelä, Lead Developer InnoDB
MariaDB Corporation

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-10-16 Thread Jogchum Reitsma


Op 14-10-2022 om 12:45 schreef Marko Mäkelä:

Hi Jogchum,

On Wed, Oct 12, 2022 at 4:30 PM Jogchum Reitsma
  wrote:
[...]

When trying to start it manually, I got the message

[ERROR] InnoDB: Upgrade after a crash is not supported. The redo log was
created with MariaDB 10.5.8.

Searching that message, I saw a post on
https://bbs.archlinux.org/viewtopic.php?id=259364

<...>

The correct advice is to install MariaDB Server (any version between
10.5 and 10.7 should do), shut down the database, and then upgrade to
10.8.

Before 10.5 (MDEV-12353), the last time the redo log format was
changed was in MariaDB Server 10.2. If you got that error message when
attempting to upgrade from something older than 10.5, then you should
use the exact same major version of MariaDB to perform the shutdown,
because there were some changes to the redo log format in each major
release between 10.1 and 10.5.

I hope that someone can post a correction to that forum.

With best regards,

Marko


Hi Marko,

I uninstalled the mariadb version from opensuse Tumbleweed,
and downloaded and installed mariadb-10.6.10-linux-systemd-x86_64 (init 
system systemd)in /usr/local, according to the steps explained in the 
INSTALL_BINARY document.


My assumption of the next steps to take is

- create /usr/local/mysql/data
- chown:chgrp it to mysql:mysql
- in /etc/my.cnf, set /usr/local/mysql/data being the data directory 
(with opensuse, that is normally /var/lib/mysql, by the way)

- do a mount --bind from my real data directory to /usr/local/mysql/data
- then start the database with /usr/local/bin/mysqld_safe --user=mysql &

Are the steps correct?

If so, I would think the next thing to do is to stop the server with 
kill -15 , remove the 
/usr/local/mariadb-10.6.10-linux-systemd-x86_64 directory,

and install the current TW-version.

After which things should be normal again...

best regards, Jogchum
___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-10-14 Thread Marko Mäkelä

Hi Jogchum,

On Wed, Oct 12, 2022 at 4:30 PM Jogchum Reitsma
 wrote:
[...]
> When trying to start it manually, I got the message
>
> [ERROR] InnoDB: Upgrade after a crash is not supported. The redo log was
> created with MariaDB 10.5.8.
>
> Searching that message, I saw a post on
> https://bbs.archlinux.org/viewtopic.php?id=259364

The advice given there is plain wrong, because it will cause the
contents of the write-ahead log to be ignored. The 10.8 startup
failure already proves that the log is nonempty, that is, some changes
must be applied. It will result in a database whose state does not
correspond to a single given point of time. Some pages are older, some
newer. That approach could cause various crashes, potentially a long
time afterwards. Many of the crashes on corrupted InnoDB data were
recently fixed in MDEV-13542.

I changed the InnoDB redo log format in MariaDB Server 10.8 in
MDEV-14425. At the same time, in
https://jira.mariadb.org/browse/MDEV-27199 I implemented a check that
prevents corruption if such incorrect and dangerous "advice" is being
followed.

The correct advice is to install MariaDB Server (any version between
10.5 and 10.7 should do), shut down the database, and then upgrade to
10.8.

Before 10.5 (MDEV-12353), the last time the redo log format was
changed was in MariaDB Server 10.2. If you got that error message when
attempting to upgrade from something older than 10.5, then you should
use the exact same major version of MariaDB to perform the shutdown,
because there were some changes to the redo log format in each major
release between 10.1 and 10.5.

I hope that someone can post a correction to that forum.

With best regards,

Marko
-- 
Marko Mäkelä, Lead Developer InnoDB
MariaDB Corporation

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-10-14 Thread Jogchum Reitsma

I'm not sure my previous reply to this message came through, (maybe 
withheld because of html-format) so I give it a try again.

Anyway, my problem is not solved yet.
Funny thing is I don't see my own messages in this conversation (I'm on 
Thunderbird)..


Op 13-10-2022 om 15:36 schreef Reindl Harald:



Am 13.10.22 um 15:29 schrieb Jogchum Reitsma:

Op 13-10-2022 om 15:10 schreef Reindl Harald:
There I have plenty of room in a raid6 configuration, where the 
default location is much smaller, and populated by the infamous 
(well, with me at least) btrfs filesystem.


you can put the data in /home/mysql, make a bind-mount from 
/var/lib/mysql to /home/mysql and change the config to use 
/var/lib/mysql


OK, so, to be quite sure, the command then is

mount --bind  /var/lib/mysql

yes, the only difference to any other mount is type "bind"

my /etc/fstab for /home while "/mnt/data/home" is the phyiscal 
location on a 4 TB RAID


/mnt/data/home /home  none  bind

i don't create bind mounts manually because you can mount, unmount and 
comment out them in fstab like every other mountpoint and i perfer 
them to survive reboots



OK, I've done so, and now

l /var/lib/mysql

reads, no surprise, the contents of the original db location.

In /etc/my.cnf I changed the location to /var/lib/mysql.

But, starting the database server still fails with the same error and 
warning messages:


okt 13 16:35:15 linux-mkay systemd[1]: Starting MariaDB database server...
░░ Subject: A start job for unit mariadb.service has begun execution
░░ Defined-By: systemd
░░ Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░
░░ A start job for unit mariadb.service has begun execution.
░░
░░ The job identifier is 6638394.
okt 13 16:35:15 linux-mkay mysql-systemd-helper[92738]: 2022-10-13 
16:35:15 0 [Note] /usr/sbin/mysqld (server 10.8.3-MariaDB) starting as 
process 92738 ...
okt 13 16:35:15 linux-mkay mysql-systemd-helper[92738]: 2022-10-13 
16:35:15 0 [Warning] Can't create test file 
/var/lib/mysql/linux-mkay.lower-test

okt 13 16:35:15 linux-mkay mysql-systemd-helper[92738]: [90B blob data]
okt 13 16:35:15 linux-mkay mysql-systemd-helper[92738]: 2022-10-13 
16:35:15 0 [ERROR] Aborting
okt 13 16:35:15 linux-mkay systemd[1]: mariadb.service: Main process 
exited, code=exited, status=1/FAILURE

░░ Subject: Unit process exited
░░ Defined-By: systemd
░░ Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░
░░ An ExecStart= process belonging to unit mariadb.service has exited.
░░
░░ The process' exit code is 'exited' and its exit status is 1.
okt 13 16:35:15 linux-mkay systemd[1]: mariadb.service: Failed with 
result 'exit-code'.

░░ Subject: Unit failed
░░ Defined-By: systemd
░░ Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░
░░ The unit mariadb.service has entered the 'failed' state with result 
'exit-code'.
okt 13 16:35:15 linux-mkay systemd[1]: Failed to start MariaDB database 
server.

░░ Subject: A start job for unit mariadb.service has failed
░░ Defined-By: systemd
░░ Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░
░░ A start job for unit mariadb.service has finished with a failure.
░░
░░ The job identifier is 6638394 and the job result is failed.


So nothing is gained so far...

regards, Jogchum


___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-10-13 Thread Antony Stone

On Thursday 13 October 2022 at 16:36:41, Jogchum Reitsma wrote:

> >> mount --bind  /var/lib/mysql

> OK, I've done so, and now
> 
> l /var/lib/mysql
> 
> reads, no surprise, the contents of the original db location.
> 
> In /etc/my.cnf I changed the location to /var/lib/mysql.
> 
> But, starting the database server still fails with the same error and
> warning messages:

Have you tried setting the "alternative location" of your MariaDB files to some 
directory under /var/lib instead of trying to put them somewhere under /home?

Antony.

-- 
BASIC is to computer languages what Roman numerals are to arithmetic.

   Please reply to the list;
 please *don't* CC me.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start


Op 13-10-2022 om 15:36 schreef Reindl Harald:



Am 13.10.22 um 15:29 schrieb Jogchum Reitsma:

Op 13-10-2022 om 15:10 schreef Reindl Harald:
There I have plenty of room in a raid6 configuration, where the 
default location is much smaller, and populated by the infamous 
(well, with me at least) btrfs filesystem.


you can put the data in /home/mysql, make a bind-mount from 
/var/lib/mysql to /home/mysql and change the config to use 
/var/lib/mysql


OK, so, to be quite sure, the command then is

mount --bind  /var/lib/mysql

yes, the only difference to any other mount is type "bind"


OK, I've done so, and now

l /var/lib/mysql

reads, no surprise, the contents of the original db location.

In /etc/my.cnf I changed the location to /var/lib/mysql.

But, starting the database server still fails with the same error and 
warning messages:


okt 13 16:35:15 linux-mkay systemd[1]: Starting MariaDB database server...
░░ Subject: A start job for unit mariadb.service has begun execution
░░Defined-By: systemd
░░Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░
░░A start job for unit mariadb.service has begun execution.
░░
░░The job identifier is 6638394.
okt 13 16:35:15 linux-mkay mysql-systemd-helper[92738]: 2022-10-13 
16:35:15 0 [Note] /usr/sbin/mysqld (server 10.8.3-MariaDB) starting as 
process 92738 ...
okt 13 16:35:15 linux-mkay mysql-systemd-helper[92738]: 2022-10-13 
16:35:15 0 [Warning] Can't create test file 
/var/lib/mysql/linux-mkay.lower-test

okt 13 16:35:15 linux-mkay mysql-systemd-helper[92738]: [90B blob data]
okt 13 16:35:15 linux-mkay mysql-systemd-helper[92738]: 2022-10-13 
16:35:15 0 [ERROR] Aborting
okt 13 16:35:15 linux-mkay systemd[1]: mariadb.service: Main process 
exited, code=exited, status=1/FAILURE

░░ Subject: Unit process exited
░░Defined-By: systemd
░░Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░
░░An ExecStart= process belonging to unit mariadb.service has exited.
░░
░░The process' exit code is 'exited' and its exit status is 1.
okt 13 16:35:15 linux-mkay systemd[1]: mariadb.service: Failed with 
result 'exit-code'.

░░ Subject: Unit failed
░░Defined-By: systemd
░░Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░
░░The unit mariadb.service has entered the 'failed' state with result 
'exit-code'.
okt 13 16:35:15 linux-mkay systemd[1]: Failed to start MariaDB database 
server.

░░ Subject: A start job for unit mariadb.service has failed
░░Defined-By: systemd
░░Support: https://lists.freedesktop.org/mailman/listinfo/systemd-devel
░░
░░A start job for unit mariadb.service has finished with a failure.
░░
░░The job identifier is 6638394 and the job result is failed.


So nothing is gained so far...

regards, Jogchum



my /etc/fstab for /home while "/mnt/data/home" is the phyiscal 
location on a 4 TB RAID


/mnt/data/home /home  none  bind

i don't create bind mounts manually because you can mount, unmount and 
comment out them in fstab like every other mountpoint and i perfer 
them to survive reboots


___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start





Am 13.10.22 um 15:29 schrieb Jogchum Reitsma:

Op 13-10-2022 om 15:10 schreef Reindl Harald:
There I have plenty of room in a raid6 configuration, where the 
default location is much smaller, and populated by the infamous 
(well, with me at least) btrfs filesystem.


you can put the data in /home/mysql, make a bind-mount from 
/var/lib/mysql to /home/mysql and change the config to use /var/lib/mysql


OK, so, to be quite sure, the command then is

mount --bind  /var/lib/mysql

yes, the only difference to any other mount is type "bind"

my /etc/fstab for /home while "/mnt/data/home" is the phyiscal location 
on a 4 TB RAID


/mnt/data/home /home  none  bind

i don't create bind mounts manually because you can mount, unmount and 
comment out them in fstab like every other mountpoint and i perfer them 
to survive reboots



___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start


Op 13-10-2022 om 15:10 schreef Reindl Harald:



Am 13.10.22 um 14:54 schrieb Jogchum Reitsma:
frankly why don#t you move the datadir out of your suerhome and 
change the path to the datadir in the config?
25 years ago I decided that the data is only useful to me, so why 
place it anywhere else than in my home directory? After all, that's 
where all my personal files reside, so why treat this data otherwise?


but database files are not normal user files and can't be backuped 
while the service is running in a safe way like your personal document 
files


so it makes little sense to handle different beasts identical
OK, I get that. A philosophical issue in my feeling: what is more 
important, who's the owner of the (content of the) files, vs. what 
nature are they?



There I have plenty of room in a raid6 configuration, where the 
default location is much smaller, and populated by the infamous 
(well, with me at least) btrfs filesystem.


you can put the data in /home/mysql, make a bind-mount from 
/var/lib/mysql to /home/mysql and change the config to use /var/lib/mysql


OK, so, to be quite sure, the command then is

mount --bind  /var/lib/mysql



voila, ProtectHome no longer hits because from the viewpoint of 
systemd and mariadb the datadir isn't below /home


https://unix.stackexchange.com/questions/198590/what-is-a-bind-mount

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp




___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start

2022-10-13 Thread Antony Stone

On Thursday 13 October 2022 at 15:22:01, Jogchum Reitsma wrote:

> I did that, and now the database starts with de db location set to the
> new one.
> 
> But when I change it in /etc/my.cnf to the original (after making a copy
> of that location), tha server crashes again, with the same error messages.

What are you actually trying to achieve here?

What problem do you have with running MariaDB with the database files in the 
standard location?

Antony.

-- 
Schrödinger's rule of data integrity: the condition of any backup is unknown 
until a restore is attempted.

   Please reply to the list;
 please *don't* CC me.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start


Op 13-10-2022 om 14:34 schreef Reindl Harald:



Am 13.10.22 um 14:16 schrieb Jogchum Reitsma:

Op 13-10-2022 om 01:46 schreef Daniel Black:

In systemd ProtectHome=read-only is the default.

systemctl edit mariadb.service and add:
[Service]
ProtectHome=false

>

When I read /usr/lib/systemd/system/mariadb.service it contains

# Prevent accessing /home, /root and /run/user
ProtectHome=true

I know hardly anything about systemd, so not user waht I should do here?
in the open "/etc/systemd/system/mariadb.service.d/override.conf" add 
what was told above


[Service]
ProtectHome=false
I did that, and now the database starts with de db location set to the 
new one.


But when I change it in /etc/my.cnf to the original (after making a copy 
of that location), tha server crashes again, with the same error messages.


Maybe I should copy the databases in the original location to the new 
one, but probably not all of them? Just of my own databases?


regards, Jogchum





___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp




___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start





Am 13.10.22 um 14:54 schrieb Jogchum Reitsma:
frankly why don#t you move the datadir out of your suerhome and change 
the path to the datadir in the config?
25 years ago I decided that the data is only useful to me, so why place 
it anywhere else than in my home directory? After all, that's where all 
my personal files reside, so why treat this data otherwise?


but database files are not normal user files and can't be backuped while 
the service is running in a safe way like your personal document files


so it makes little sense to handle different beasts identical

There I have plenty of room in a raid6 configuration, where the default 
location is much smaller, and populated by the infamous (well, with me 
at least) btrfs filesystem.


you can put the data in /home/mysql, make a bind-mount from 
/var/lib/mysql to /home/mysql and change the config to use /var/lib/mysql


voila, ProtectHome no longer hits because from the viewpoint of systemd 
and mariadb the datadir isn't below /home


https://unix.stackexchange.com/questions/198590/what-is-a-bind-mount

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start


Op 13-10-2022 om 14:34 schreef Reindl Harald:



Am 13.10.22 um 14:16 schrieb Jogchum Reitsma:

Op 13-10-2022 om 01:46 schreef Daniel Black:

On Thu, Oct 13, 2022 at 12:27 AM Jogchum Reitsma
  wrote:


Anyone an idea why /home/jogchum/mysql_recover/linux-mkay.lower-test
can't be created and how to solve that?

In systemd ProtectHome=read-only is the default.

systemctl edit mariadb.service and add:
[Service]
ProtectHome=false

>

When I read /usr/lib/systemd/system/mariadb.service it contains

# Prevent accessing /home, /root and /run/user
ProtectHome=true

I know hardly anything about systemd, so not user waht I should do here?
in the open "/etc/systemd/system/mariadb.service.d/override.conf" add 
what was told above


[Service]
ProtectHome=false

OK,thanks, I 'll give it a try


and "Prevent accessing /home, /root and /run/user" as comment in 
system services should tell you something


frankly why don#t you move the datadir out of your suerhome and change 
the path to the datadir in the config?
25 years ago I decided that the data is only useful to me, so why place 
it anywhere else than in my home directory? After all, that's where all 
my personal files reside, so why treat this data otherwise?


There I have plenty of room in a raid6 configuration, where the default 
location is much smaller, and populated by the infamous (well, with me 
at least) btrfs filesystem.


If I were convinced relocation would solve the problem, I could do that, 
but I have not seen that in the discussion so forth.


Anyway, thanks for thinking with me - I may not agree with you in 
everything, but be sure I appreciate your replies!


regards, Jogchum



___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp




___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start


Op 13-10-2022 om 14:08 schreef Reindl Harald:



Am 13.10.22 um 13:55 schrieb Jogchum Reitsma:

Op 13-10-2022 om 12:59 schreef Reindl Harald:


All files and dir's under /home/jogchum/mysql_recover have 
mysql:mysql as owner:group


and your userhome is 755 
which is the default, when opensuse creates a new user. So, if I 
understand you right,  that's wrong in your opinion?


plain wrong but i don#t even know the default of my Fedora machines 
given that the last time i saw an os-installer was in 2011 thanks to 
RAID and backups
Well, not only on (re-)install, but also when you create a new user via 
Yast, the default is 755.


as well as systemd namespaces and SELinux allowing nosense like 
storing the databasedir in a userhome?


Why is that nonsense? After all, it's not a company database we're 
talking about, just


and how does that justify doing something different than anybody else 
out there?
I don't think I have to justify that here, after all, it sits some 25 
years on the place it is now (which is *not* 
/home/jogchum/mysql_recover, I explain that below). Never was a problem.


- some addresses of friends and family, mainly used for sending 
Christmas cards,

- some metadata about video's I recorded,
- some data about the yield of our solar panels.

Nothing secret in that.


i wonder if the friends see that this relaxed too, in europe there is 
more undestanding of privacy and data security as in the USA...
Yes, and in the Netherlands even more than in some other 
EU-countries.But to read it for others than me, my wife and son, one 
should hack one's way into my system. Which is not impossible, of 
course, but tin that case I think the damage will be more than a few 
addresses being read.


just don't do that - and be it only because it's dumb set your 
userhome to chmod 755 which means any 644 file can be read by anyone


As said, I did not chmod it to 755, it's the opensuse default (and 
maybe other distro's too, dunno). And anyone in my family is allowed 
to read anything on our systems.


More important, you don't say anything about how I could start the 
database?


dunno but this part of the logs is looking bad - on a updated machine 
that mustnt't have started at all and indicates for me the damage was 
that large that your installation looked like a fresh one


okt 02 21:26:55 linux-mkay mysql-systemd-helper[45372]: Creating MySQL 
privilege database...
okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: Two 
all-privilege accounts were created.
okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: One is 
root@localhost, it has no password, but you need to
okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: be system 
'root' user to connect. Use, for example, sudo mysql
okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: The second is 
mysql@localhost, it has no password either, but
okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: you need to be 
the system 'mysql' user to connect.


Yes, it was a fresh installation, because I gave a new location, 
/home/jogchum/mysql_recover, before starting the db. As said in my first 
post, this was a solution that worked for someone with the same problem:


roughly:

- change the location of the db in /etc/my.cnf
- start the database server, which will create the default environment 
in the new location

- copy the existing database files to that new locatioN
- restart the database server.

In my case, this recipe fails in the second step: the new location is 
populated, but the server crashes immediately.
Journalctl show only a warning regarding not being able to create a test 
file:
Warning] Can't create test file 
/home/jogchum/mysql_recover/linux-mkay.lower-test


which, according to Sergei, should not be a cause for the server to 
crash.And ideed, it's only a warning, no error.


So, it still is a riddle why the server won't start.

regards, Jogchum



___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp


___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start





Am 13.10.22 um 14:16 schrieb Jogchum Reitsma:

Op 13-10-2022 om 01:46 schreef Daniel Black:

On Thu, Oct 13, 2022 at 12:27 AM Jogchum Reitsma
  wrote:


Anyone an idea why /home/jogchum/mysql_recover/linux-mkay.lower-test
can't be created and how to solve that?

In systemd ProtectHome=read-only is the default.

systemctl edit mariadb.service and add:
[Service]
ProtectHome=false

>

When I read /usr/lib/systemd/system/mariadb.service it contains

# Prevent accessing /home, /root and /run/user
ProtectHome=true

I know hardly anything about systemd, so not user waht I should do here?
in the open "/etc/systemd/system/mariadb.service.d/override.conf" add 
what was told above


[Service]
ProtectHome=false

and "Prevent accessing /home, /root and /run/user" as comment in system 
services should tell you something


frankly why don#t you move the datadir out of your suerhome and change 
the path to the datadir in the config?


___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start


Op 13-10-2022 om 01:46 schreef Daniel Black:

On Thu, Oct 13, 2022 at 12:27 AM Jogchum Reitsma
  wrote:


Anyone an idea why /home/jogchum/mysql_recover/linux-mkay.lower-test
can't be created and how to solve that?

In systemd ProtectHome=read-only is the default.

systemctl edit mariadb.service and add:
[Service]
ProtectHome=false


If I issue that command, it translates to

nano/etc/systemd/system/mariadb.service.d/.#override.conf5a67e18e4e904390

and  the first lines of that file read

## Editing /etc/systemd/system/mariadb.service.d/override.conf
### Anything between here and the comment below will become the new contents of 
the file



### Lines below this comment will be discarded

When I read /usr/lib/systemd/system/mariadb.service it contains

# Prevent accessing /home, /root and /run/user
ProtectHome=true

I know hardly anything about systemd, so not user waht I should do here?

regards, Jogchum




https://mariadb.com/kb/en/systemd/#useful-systemd-options

You may also need to selinux relabel the new directory:

sudo semanage fcontext -a -t mysqld_db_t "/home/jogchum/mysql_recover(/.*)?"
sudo restorecon -Rv /home/jogchum/mysql_recover

https://mariadb.com/kb/en/selinux/#setting-the-file-context-for-the-data-directory


___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start




Am 13.10.22 um 13:55 schrieb Jogchum Reitsma:

Op 13-10-2022 om 12:59 schreef Reindl Harald:


All files and dir's under /home/jogchum/mysql_recover have mysql:mysql 
as owner:group


and your userhome is 755 
which is the default, when opensuse creates a new user. So, if I 
understand you right,  that's wrong in your opinion?


plain wrong but i don#t even know the default of my Fedora machines 
given that the last time i saw an os-installer was in 2011 thanks to 
RAID and backups


as well as systemd namespaces and SELinux allowing nosense like 
storing the databasedir in a userhome?


Why is that nonsense? After all, it's not a company database we're 
talking about, just


and how does that justify doing something different than anybody else 
out there?


- some addresses of friends and family, mainly used for sending 
Christmas cards,

- some metadata about video's I recorded,
- some data about the yield of our solar panels.

Nothing secret in that.


i wonder if the friends see that this relaxed too, in europe there is 
more undestanding of privacy and data security as in the USA...


just don't do that - and be it only because it's dumb set your 
userhome to chmod 755 which means any 644 file can be read by anyone


As said, I did not chmod it to 755, it's the opensuse default (and maybe 
other distro's too, dunno). And anyone in my family is allowed to read 
anything on our systems.


More important, you don't say anything about how I could start the 
database?


dunno but this part of the logs is looking bad - on a updated machine 
that mustnt't have started at all and indicates for me the damage was 
that large that your installation looked like a fresh one


okt 02 21:26:55 linux-mkay mysql-systemd-helper[45372]: Creating MySQL 
privilege database...
okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: Two 
all-privilege accounts were created.
okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: One is 
root@localhost, it has no password, but you need to
okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: be system 'root' 
user to connect. Use, for example, sudo mysql
okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: The second is 
mysql@localhost, it has no password either, but
okt 02 21:27:17 linux-mkay mysql-systemd-helper[45384]: you need to be 
the system 'mysql' user to connect.


___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start


Op 13-10-2022 om 12:59 schreef Reindl Harald:


All files and dir's under /home/jogchum/mysql_recover have mysql:mysql 
as owner:group


and your userhome is 755 
which is the default, when opensuse creates a new user. So, if I 
understand you right,  that's wrong in your opinion?
as well as systemd namespaces and SELinux allowing nosense like 
storing the databasedir in a userhome?


Why is that nonsense? After all, it's not a company database we're 
talking about, just


- some addresses of friends and family, mainly used for sending 
Christmas cards,

- some metadata about video's I recorded,
- some data about the yield of our solar panels.

Nothing secret in that.



just don't do that - and be it only because it's dumb set your 
userhome to chmod 755 which means any 644 file can be read by anyone


As said, I did not chmod it to 755, it's the opensuse default (and maybe 
other distro's too, dunno). And anyone in my family is allowed to read 
anything on our systems.


More important, you don't say anything about how I could start the 
database?


regards, Jogchum



___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp




___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start





Am 13.10.22 um 12:14 schrieb Jogchum Reitsma:

Op 12-10-2022 om 16:48 schreef Sergei Golubchik:

Hi, Jogchum,

Anyone an idea why /home/jogchum/mysql_recover/linux-mkay.lower-test
can't be created and how to solve that?

permission problem, perhaps?
All files and dir's under /home/jogchum/mysql_recover have mysql:mysql 
as owner:group


and your userhome is 755 as well as systemd namespaces and SELinux 
allowing nosense like storing the databasedir in a userhome?


just don't do that - and be it only because it's dumb set your userhome 
to chmod 755 which means any 644 file can be read by anyone


___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Maria-db refuses to start