Replace mairadb with mysql please help

My router is a netgear 3700v2 - I have a 32G thumbdrive plugged into the usb port (and configured as /mnt/sda) my intention is to setup this router as a small mysql server. The database is less than 50MB after 6 months of use in it's current situation so the 32G drive should be more than enough to store it for a long time.

That being said - I've been trying fruitlessly over the past two weeks to get mariadb to function - I've gone thru everything from storage errors, to memory errors, to it saying files are corrupt. It just won't start and that's fine with me. HOWEVER since mariadb is the default DB i'm having a hard time finding any resource anywhere that will give me a straight answer on what I need to do to ditch maria and go to a regular MYSQL database.

I understand that mariadb is a drop in replacement - I've tried it before on several linux boxes and almost always have some sort of issue. So please don't try to sell me on keeping it. I just want a straight answer on how to go to mysql

Thanks

Huh...default DB for what?

You extract your mariadb databases as SQL files/statements. You will then re-load those SQL files/statements into mysql.

:confused:

...I have no clue what you're talking about; but the information to convert is above.

Sorry, not going to give you the answer you like as it works fine on my system. you need to upgrade your database via an upgrade script (no biggy) though if you want to convert an existing db to mariadb. And another thing is that mariadb uses its own user these days so you'll need to give the mariadb user permissions to the database files.

Evenually Mysql will die as the developers of it left Mysql after Oracle buying it. They continued work on Mariadb and it is now much more advanced as Mysql has ever been. pretty much everyone made the switch to Mariadb.

export and reimport is not even perse needed. There is an upgrade script you can run from the mysql client.

when you type opkg install mysql-server it defaults to mariadb (that's what i meant by being the default database)

The problem i'm having is that mariadb won't even start and run - so being able to convert the database to maria isn't even an issue yet.

  • Then, where did you run this database sucessfully?
  • Just backup the DB/statements on the device where you were last running it, then you'll at least have the backup

Then, you can troubleshoot the package.

most recent attempt -

innoDB: assertion failure in thread xxxxxxxx in the file ut0mem.cc line 105
innodb: failing assertion: ret || !assert_on_error
innodb: we intentionally generate a memory trap.
innodb: submit a detailed bug report to https://jira.mariadb.org/
innodb: if you get repeated assertion failures or crashes, even
innodb: immediately after the mysqld startup, there may be
innodb: corruption in the innodb tablespace.  Please refer to 
innodb: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
innodb: abour forcing recovery.
aborted

I have gone to the page - went to level 6 recovery and nothing!

Any thoughts!

lets understand the situation.
is this a clean setup (fresh router install without keeping settings, never setup any mysql or mariadb database before), or were you running mysql with that database before on that router and are now confronted with an unvoluntairy upgrade?

either case, what build of openwrt are you currently running and what version of mariadb are we talking about?

I am running it currently on a linux mint server using mysql not mariadb - it is a single standalone unit with the database and the end-user software on one machine - my plan is to migrate the database over to the router and add additional terminals (cash registers) in the future.

So i have the database backed up, and i'm not playing with the "live" system. Trying to build this and make it function correctly then I'll replace what's there.

1 Like

most recent attempt:

innodb: plugin initialization aborted at mariadb-10.1.37/storage/xtradb/srv/srv0start.cc [2970] with error Generic error
error - plugin innodb init function returned error
error - plugin innodb registration as storage engine failed
mysqld out of memory
killed

I looked up your router, but 64MB of ram is not much. my guess is that is the issue.
you can bring the memory usage of mariadb down a bit by adding:

performance_schema = off

in the /etc/mysql/my.cnf file, under the [mysqld] section. But that might still not be enough.

Can you post the content of your my.cnf here? wondering what the key buffer setting is for your config.

1 Like

Looks like malloc issue. Is it possible you ran out of ram? On a 128 MByte RAM router I can't start mariadb. The only way to get it to run there is to setup swap. With 1 gig swap partition on a dongle it works fine.

2 Likes
mysqld out of memory

Classic :slight_smile:

1 Like

I run it with zero swap on a 512mb machine, and there it serves a 5GB database just fine.

Hello johnnysl,

Nice to know, thanks :slight_smile:

Regards,
Seb

Seems to virtually allocate about 400MB of ram, but actually only reserves 47MB.
Removing the performance schema like i wrote above should help to take the usage down even further.
Likely that in combination with a swap file on the same USB disk planned for the database could fix the issue.

I turned off performance schema - deleted the data and log files and i'm trying to restart it now - its currently creating the new files (tried to restart without deleting the files but it gave me an error they were corrupted and since there's nothing in them yet i just deleted them)

i'll update once it's done and also share the my.cnf contents

could you post the info for how to setup the swap file please

I would suggest re-partitioning your USB drive to add a swap area (you may even wish to make a separate thread for this).

See: https://openwrt.org/docs/guide-user/storage/fstab

(Also, this is going to wear significantly on your USB drive.)

1 Like

or just test it first with a regular swap file before creating a real swap partition.
If used heavily, It indeed causes wear and you might want to invest into something that can handle that wear better.

this is a first guide how to setup a 128MB swapfile on /mnt/usb (assuming that is the mountpoint of your usbdrive)
I also assume the drive is formatted in ext4 or something similar, native to the OS.

  1. check current free memory and if swap is used
root@Linksys:/# free
             total       used       free     shared    buffers     cached
Mem:        510940     457340      53600       5096      87348     157520
-/+ buffers/cache:     212472     298468
Swap:            0          0          0

nope, no swap...

  1. create a swap file
root@Linksys:/# dd if=/dev/zero of=/mnt/usb/swapfile bs=1M count=128
root@Linksys:/# ls -alh /mnt/usb/swapfile
-rw-r--r--    1 root     root      128.0M Dec 11 23:21 /mnt/usb/swapfile
  1. set it up for swap usage, and turn swap on for it
root@Linksys:/# mkswap /mnt/usb/swapfile
Setting up swapspace version 1, size = 134213632 bytes
root@Linksys:/# swapon /mnt/usb/swapfile
  1. check if the system now has a swap file
root@Linksys:/# free
             total       used       free     shared    buffers     cached
Mem:        510940     457432      53508       5096      87336     157512
-/+ buffers/cache:     212584     298356
Swap:       131068          0     131068

jup, now we have 128MB of swap.

This will not survive a reboot however; swap on will need to be run again on each boot, before the major memory eaters (like mariadb) are started. If above works, then we can discuss that bit; allthough i'd likely rather invest in a router with better memory on board (512MB+) and internal space for an EMMC or SSD card; so that the usb stick doesn't get stolen easily.

ok so when it finally came back to being responsive - it failed out with the message from earlier

here's the my.cnf file contents: (only showing the stuff i changed)
key_buffer_size = 16K
max_allowed_packet = 1M
thread_stack = 128K
thread cache_size = 8
sort_buffer_size = 64K
innodb_use_native_aio = 0
innodb_buffer_pool_size=20M
performance_schema = off

Johnnysi - I tired doing the swap file for testing like you showed - free reported as you show with 0 in the swap - when i get the the dd line - it takes about 2-3 minutes and then comes back and says no space left on device - SO no i'm going to go re-partition the drive and free up some space on it

Starting to think it may just be easier to get a different router - only using this one cuz i had it laying around.