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
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.
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!
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.
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
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.
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
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.
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.