search

Optware / Optware-ng

279 stars 52 forks source link

Mysql #28

Closed LeeWiu closed 8 years ago

LeeWiu commented 8 years ago

Can't start mysql, modified some lines but still can't connect:

sed -i 's/\/bin\/hostname/cat \/proc\/sys\/kernel\/hostname/g' "/opt/etc/init.d/S70mysqld"
sed -i 's/\/bin\/hostname/cat \/proc\/sys\/kernel\/hostname/g' "/opt/bin/mysql_install_db"
sed -i 's/\/bin\/hostname/cat \/proc\/sys\/kernel\/hostname/g' "/opt/bin/mysqld_safe"

But can't log as root, asking me for a password

mysqld_safe --skip-grant-tables

With mysql5, I get

Starting MySQL. ERROR! Manager of pid-file quit without updating file.
alllexx88 commented 8 years ago

I don't know what's wrong with mysql, but on my test platform it's crashing when trying to create privileges table. Also crashing when issuing simple commands, like SELECT user,host,password FROM mysql.user; Maybe, it's too old and something in its code doesn't work properly when compiled using gcc 5.2.0 --- just a hunch. It probably makes sense to drop mysql 4.1.22 and upgrade it to something recent. I'll look into it when I have the time.

alllexx88 commented 8 years ago

I've managed to port mysql 5.7.9 to Optware-ng. It appears to work fine, at least, I'm able to connect and execute simple sql queries on my ARMv7 router. Binaries will arrive a bit later, since I also need to bump some mysql-dependent packages, not to mention building mysql for other targets (mysql 5.7.9 takes much longer to build than old mysql versions).

alllexx88 commented 8 years ago

Binaries uploaded. It turned out that starting from 5.7.5 mysql requires gcc builtin atomics, some of which aren't available for some Optware-ng archs, so we currently have two flavors of mysql: -5.7.9 (armv7 and i686 targets) -5.7.4(mipsel, armv5 and ppc-603e)

I tested both 5.7.9 and 5.7.4 on armv7 and mipsel routers, respectively, and they work fine. Just had to make sure to have swap space, since mysqld is rather memory-consuming.

LeeWiu commented 8 years ago

Thanks, you are fast, I can't find v5.7 just v4.1 and 5.0

alllexx88 commented 8 years ago

5.7.4 is now the default version, it's called mysql, replacing 4.1. I plan to remove mysql5 package a bit later to avoid confusion.

LeeWiu commented 8 years ago

Can't start it

151212 00:28:08 mysqld_safe Starting mysqld daemon with databases from /opt/var/lib/mysql
2015-12-11T22:28:40.870807Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-12-11T22:28:40.938026Z 0 [Note] /opt/bin/mysqld (mysqld 5.7.4-m14) starting as process 25042 ...
2015-12-11T22:28:41.236612Z 0 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'mysql.plugin' doesn't exist
2015-12-11T22:28:41.324976Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2015-12-11T22:28:41.400237Z 0 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2015-12-11T22:28:41.400592Z 0 [Note] InnoDB: Mutexes uses sys mutexes and rw_locks use InnoDB's own implementation
2015-12-11T22:28:41.400914Z 0 [Note] InnoDB: Uses system mutexes
2015-12-11T22:28:41.401236Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2015-12-11T22:28:41.892829Z 0 [Note] InnoDB: Number of pools: 1
2015-12-11T22:28:42.229904Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2015-12-11T22:28:42.234218Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128.0M, instances = 1
2015-12-11T22:28:42.247902Z 0 [ERROR] InnoDB: mmap(136314880 bytes) failed; errno 12
2015-12-11T22:28:42.251150Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2015-12-11T22:28:42.251517Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2015-12-11T22:28:42.251833Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
22:28:42 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.

key_buffer_size=8388608
read_buffer_size=262144
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 57854 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

151212 00:28:42 mysqld_safe mysqld from pid file /opt/var/lib/mysql/RT-AC66U.pid ended
alllexx88 commented 8 years ago

It looks to me that you have database 'leftovers' left from previous mysql version(s) (4.1 or 5.0), which weren't properly initialized. Try this:

rm -rf /opt/var/lib/mysql/*
/opt/lib/ipkg/info/mysql.postinst

This will clean the 'leftovers' and trigger mysql initialization.

LeeWiu commented 8 years ago

Clean optware-ng install

sed -i 's/\/`hostname`/`cat \/proc\/sys\/kernel\/hostname`/g' "/opt/support-files/mysql.server"

Error

151212 14:58:33 mysqld_safe Starting mysqld daemon with databases from /opt/var/lib/mysql
2015-12-12T12:58:57.464352Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-12-12T12:58:57.556402Z 0 [Note] /opt/bin/mysqld (mysqld 5.7.4-m14) starting as process 9635 ...
2015-12-12T12:58:57.844886Z 0 [Warning] Buffered warning: Performance schema disabled (reason: init failed).
2015-12-12T12:58:58.322346Z 0 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'mysql.plugin' doesn't exist
2015-12-12T12:58:58.423501Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2015-12-12T12:58:58.488635Z 0 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2015-12-12T12:58:58.488996Z 0 [Note] InnoDB: Mutexes uses sys mutexes and rw_locks use InnoDB's own implementation
2015-12-12T12:58:58.489318Z 0 [Note] InnoDB: Uses system mutexes
2015-12-12T12:58:58.489638Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2015-12-12T12:58:58.581235Z 0 [Note] InnoDB: Number of pools: 1
2015-12-12T12:58:58.639563Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2015-12-12T12:58:58.644008Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128.0M, instances = 1
2015-12-12T12:58:58.833788Z 0 [Note] InnoDB: Completed initialization of buffer pool
2015-12-12T12:58:58.997205Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2015-12-12T12:58:59.013788Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 49463
2015-12-12T12:58:59.014147Z 0 [Note] InnoDB: Database was not shutdown normally!
2015-12-12T12:58:59.014472Z 0 [Note] InnoDB: Starting crash recovery.
2015-12-12T12:58:59.014794Z 0 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-12-12T12:58:59.015470Z 0 [Note] InnoDB: Restoring possible half-written data pages 
2015-12-12T12:58:59.015879Z 0 [Note] InnoDB: from the doublewrite buffer...
2015-12-12T12:58:59.438943Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1207147
2015-12-12T12:58:59.739752Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
2015-12-12T12:59:00.289503Z 0 [Note] InnoDB: Apply batch completed
2015-12-12T12:59:00.491021Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2015-12-12T12:59:00.491376Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2015-12-12T12:59:00.491977Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2015-12-12T12:59:01.167891Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2015-12-12T12:59:01.181897Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2015-12-12T12:59:01.182265Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2015-12-12T12:59:01.799170Z 0 [Note] InnoDB: Waiting for purge to start
2015-12-12T12:59:01.864095Z 0 [Note] InnoDB: 5.7.4 started; log sequence number 1207147
2015-12-12T12:59:01.929222Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
151212 14:59:02 mysqld_safe mysqld from pid file /opt/var/lib/mysql/RT-AC66U.pid ended

Tried to create db

mysql_install_db --force
-sh: mysql_install_db: not found
find -name mysql_install_db
./optware-ng/scripts/mysql_install_db
-sh: ./optware-ng/scripts/mysql_install_db: not found
alllexx88 commented 8 years ago

There's an error in postinst script in command that should install privilege tables:

/opt/scripts/mysql_install_db --datadir=/opt/var/lib/mysql  --lc-messages-dir=/opt/share/english

it should be:

/opt/scripts/mysql_install_db --basedir=/opt --datadir=/opt/var/lib/mysql  --lc-messages-dir=/opt/share/english

Run it manually, I'll fix it soon. Together with hostname (how odd it's not present in your firmware).

P.S. all those '/' escapes are meaningless, you can do this with sed (that is, use any other symbol as delimiter in replace 's' command):

sed -i 's|/`hostname`|`cat /proc/sys/kernel/hostname`|g' "/opt/support-files/mysql.server"
alllexx88 commented 8 years ago

Fixed: https://github.com/alllexx88/Optware-ng/commit/383653c97a429c64e385c5a3d74a338c7758fe87

LeeWiu commented 8 years ago

Clean install and same error, now I seen mysql requires "perl" package, installed and another error

151213 11:31:15 mysqld_safe Starting mysqld daemon with databases from /opt/var/lib/mysql
2015-12-13T09:31:42.076259Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-12-13T09:31:42.202878Z 0 [Note] /opt/bin/mysqld (mysqld 5.7.4-m14) starting as process 4819 ...
2015-12-13T09:31:42.693192Z 0 [Warning] Buffered warning: Performance schema disabled (reason: init failed).
2015-12-13T09:31:42.753936Z 0 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'mysql.plugin' doesn't exist
2015-12-13T09:31:42.839874Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2015-12-13T09:31:42.981588Z 0 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2015-12-13T09:31:42.981944Z 0 [Note] InnoDB: Mutexes uses sys mutexes and rw_locks use InnoDB's own implementation
2015-12-13T09:31:42.982273Z 0 [Note] InnoDB: Uses system mutexes
2015-12-13T09:31:42.982596Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2015-12-13T09:31:43.114615Z 0 [Note] InnoDB: Number of pools: 1
2015-12-13T09:31:43.194296Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2015-12-13T09:31:43.198638Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128.0M, instances = 1
2015-12-13T09:31:43.406082Z 0 [Note] InnoDB: Completed initialization of buffer pool
2015-12-13T09:31:43.494218Z 0 [Note] InnoDB: The first innodb_system_tablespace data file 'ibdata1' did not exist. A new tablespace will be created!
2015-12-13T09:31:43.494887Z 0 [Note] InnoDB: Setting file './ibdata1' size to 12 MB. Physically writing the file full; Please wait ...
2015-12-13T09:31:44.843800Z 0 [Note] InnoDB: File './ibdata1' size is now 12 MB.
2015-12-13T09:31:44.869736Z 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2015-12-13T09:31:49.264112Z 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2015-12-13T09:31:54.463837Z 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2015-12-13T09:31:54.464428Z 0 [Warning] InnoDB: New log files created, LSN=45781
2015-12-13T09:31:54.464817Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2015-12-13T09:31:54.483862Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2015-12-13T09:31:55.696468Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2015-12-13T09:31:55.698929Z 0 [Note] InnoDB: Doublewrite buffer not found: creating new
2015-12-13T09:31:56.293447Z 0 [Note] InnoDB: Doublewrite buffer created
2015-12-13T09:31:56.375226Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2015-12-13T09:31:56.375590Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2015-12-13T09:31:57.434586Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2015-12-13T09:31:57.703863Z 0 [Note] InnoDB: Foreign key constraint system tables created
2015-12-13T09:31:57.704395Z 0 [Note] InnoDB: Creating tablespace and datafile system tables.
2015-12-13T09:31:57.714071Z 0 [Note] InnoDB: Tablespace and datafile system tables created.
2015-12-13T09:31:57.714790Z 0 [Note] InnoDB: Waiting for purge to start
2015-12-13T09:31:57.784167Z 0 [Note] InnoDB: 5.7.4 started; log sequence number 0
2015-12-13T09:31:57.829655Z 0 [ERROR] [FATAL] InnoDB: pthread_create returned 11
2015-12-13 11:31:57 0x2aab0330  InnoDB: Assertion failure in thread 715850544 in file ha_innodb.cc line 16775
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
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.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
09:31:57 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.

key_buffer_size=8388608
read_buffer_size=262144
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 57854 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

151213 11:31:58 mysqld_safe mysqld from pid file /opt/var/lib/mysql/RT-AC66U.pid ended

Tried /opt/scripts/mysql_install_db --force but first should modify: from #!/usr/bin/perl to #!/opt/bin/perl from .bin/my_print_defaults to /opt/bin/my_print_defaults

Now another error, /opt/scripts/mysql_install_db --force

FATAL ERROR: Could not find my-default.cnf

If you compiled from source, you need to run 'make install' to
copy the software into the correct location ready for operation.

If you are using a binary release, you must either be at the top
level of the extracted archive, or pass the --basedir option
pointing to that location.
alllexx88 commented 8 years ago

Thanks for find about perl. mysql_install_db used to be a shell script, I didn't notice it switched to perl (in my firmware /usr/bin/perl is a link to /opt/bin/perl, so it gave no error for me, since I have perl installed).

If you launch mysql_install_db manually, you must specify additional parameters:

/opt/scripts/mysql_install_db --basedir=/opt --datadir=/opt/var/lib/mysql  --lc-messages-dir=/opt/share/english

The "[ERROR] [FATAL] InnoDB: pthread_create returned 11" error most likely means you've run out of memory, and need to setup swap partition or file:

 ... the number 11 stands for EAGAIN which in turn means "The system lacked the necessary resources to create another thread, or the system-imposed limit on the total number of threads in a process PTHREAD_THREADS_MAX would be exceeded.

(see here)

LeeWiu commented 8 years ago

Ok, all fine until I want to login as root

mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

And when I start it I get

my_print_defaults: [Warning] World-writable config file '/opt/my.cnf' is ignored
alllexx88 commented 8 years ago

If you initialized mysql using mysql_install_db (which should be done by postinst script automatically), the one-time password was written here: /root/.mysql_secret. Login and change it using mysqladmin:

/opt/bin/mysqladmin -u root password -p

it will first ask for current password (from /root/.mysql_secret), and then will prompt for new one.

alllexx88 commented 8 years ago

As for this error:

my_print_defaults: [Warning] World-writable config file '/opt/my.cnf' is ignored

This is clearly a permissions error. Mysql config cannot be writable for all (permissions for 'all users' cannot be *1*, e.g., 6 or 7). If, apart from /opt/etc/my.cnf that comes with the package, you want to use /opt/my.cnf as well (you probably created it yourself), run chmod 644 /opt/my.cnf. /opt/my.cnf doesn't indeed come with Optware-ng:

root@unknown:/tmp/home/root# ipkg files mysql|grep my.cnf
/opt/etc/my.cnf
LeeWiu commented 8 years ago

I think we are passed 90% :) Another clean install, error

Configuring mysql
my_print_defaults: [Warning] World-writable config file '/opt/my.cnf' is ignored                                                               .
Installing MySQL system tables...2015-12-14T22:22:42.855057Z 0 [ERROR] Aborting

my_print_defaults: [Warning] World-writable config file '/opt/my.cnf' is ignored                                                               .
Starting MySQL................ ERROR! The server quit without updating PID file                                                                (/opt/var/lib/mysql/RT-AC66U.pid).
postinst script returned status 1
ERROR: mysql.postinst returned 1
Configuring ncurses
update-alternatives: Linking //opt/bin/clear to /opt/bin/ncurses-clear
Configuring perl
Configuring zlib
Successfully terminated.

In /opt/var/mysql/.err

151215 00:22:46 mysqld_safe Starting mysqld daemon with databases from /opt/var/lib/mysql
mysqld: [Warning] World-writable config file '/opt/my.cnf' is ignored.
2015-12-14T22:23:03.912794Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-12-14T22:23:04.070073Z 0 [Note] /opt/bin/mysqld (mysqld 5.7.4-m14) starting as process 14792 ...
2015-12-14T22:23:04.331233Z 0 [Warning] Buffered warning: Performance schema disabled (reason: init failed).
2015-12-14T22:23:04.361350Z 0 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'mysql.plugin' doesn't exist
2015-12-14T22:23:04.453794Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2015-12-14T22:23:04.520176Z 0 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2015-12-14T22:23:04.520692Z 0 [Note] InnoDB: Mutexes uses sys mutexes and rw_locks use InnoDB's own implementation
2015-12-14T22:23:04.521015Z 0 [Note] InnoDB: Uses system mutexes
2015-12-14T22:23:04.521335Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2015-12-14T22:23:04.604091Z 0 [Note] InnoDB: Number of pools: 1
2015-12-14T22:23:04.640871Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2015-12-14T22:23:04.645171Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128.0M, instances = 1
2015-12-14T22:23:04.849113Z 0 [Note] InnoDB: Completed initialization of buffer pool
2015-12-14T22:23:05.066004Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2015-12-14T22:23:05.370300Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2015-12-14T22:23:05.370887Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2015-12-14T22:23:06.057064Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2015-12-14T22:23:06.094781Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2015-12-14T22:23:06.095151Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2015-12-14T22:23:06.200811Z 0 [Note] InnoDB: Waiting for purge to start
2015-12-14T22:23:06.263477Z 0 [Note] InnoDB: 5.7.4 started; log sequence number 1207087
2015-12-14T22:23:06.312284Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 3f82dea6-a2b1-11e5-b269-50465d5e1b60.
2015-12-14T22:23:06.576884Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
151215 00:23:07 mysqld_safe mysqld from pid file /opt/var/lib/mysql/RT-AC66U.pid ended
151215 00:26:00 mysqld_safe Starting mysqld daemon with databases from /opt/var/lib/mysql
chmod 644 /opt/my.cnf

Now I run /opt/scripts/mysql_install_db --basedir=/opt --datadir=/opt/var/lib/mysql --lc-messages-dir=/opt/share/english and it's all fine Start mysql

/opt/etc/init.d/S70mysqld start

Time to know generated password

awk 'NR==2' /root/.mysql_secret

Run mysql -u root -p And change password

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

Some questions: 1) Mysql installer script can create and move to /opt/etc/mysql/... all directories and files from /opt? like data docs include man scripts sql-bench support-files & COPYING INSTALL-BINARY my.cnf README? 2) chmod 644 .../my.cnf? 3) Mysql installer script can create files to /opt/share/mysql instead /opt/share? 4) And maybe automatically change from generated password to universal password "optware-ng"? The last one is not important at all

alllexx88 commented 8 years ago

I will remove COPYING INSTALL-BINARY and README files, which were automatically installed, but /opt/my.cnf doesn't come from mysql package:

root@unknown:/tmp/home/root# ipkg files mysql|grep my.cnf
/opt/etc/my.cnf

From what I can gather, it's being created automatically by the mysql_install_db script. Adding --keep-my-cnf switch skips this. Some other files are used by mysqld binary and are expected to be where they are, and I wasn't able to find where exactly paths are hardcoded in mysql-5.7.9, so I chose to let it be.

As for root password, it looks like adding --skip-random-passwords option to /opt/scripts/mysql_install_db creates root account with no password at all. I guess it's better this way, that's what I do for 5.7.9, anyway.

To sum up, I'm going to do the following:

  1. Remove COPYING INSTALL-BINARY and README files and docs dir
  2. Move /opt/man to /opt/share/man
  3. Move include to include/mysql
  4. Update postinst script to use /opt/scripts/mysql_install_db --basedir=/opt --datadir=/opt/var/lib/mysql --lc-messages-dir=/opt/share/english --skip-random-passwords --keep-my-cnf
LeeWiu commented 8 years ago

Mysql installs and start fine, thanks :+1: Only /opt/share is full of mysql files and directories, can't install to opt/share/mysql? But I have a really big issue, when I reboot router, mysql trying to start but not enough memory and I have created a 512 mb swap file

2015-12-16T23:48:05.801850Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2015-12-16T23:48:05.829702Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2015-12-16T23:48:05.830475Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
23:48:05 UTC - mysqld got signal 11 ;

Can we add a delay for about 60 seconds? /opt/etc/init.d/S70mysqld it's just a symlink to ../../support-files/mysql.server Maybe this is only on mipsel routers...

LeeWiu commented 8 years ago

Adding sleep time will not solve this because after reboot, mysql will not start at all, only if I do a clean install :(

alllexx88 commented 8 years ago

You have to issue swapon <swap_file> after each reboot. I've just rebooted my mipsel router, and mysql starts just fine:

root@unknown:/tmp/mnt/sda1# swapon swap
root@unknown:/tmp/mnt/sda1# free -k
             total         used         free       shared      buffers
Mem:        255724        25720       230004            0         4000
-/+ buffers:              21720       234004
Swap:       524280            0       524280
root@unknown:/tmp/mnt/sda1# /opt/etc/init.d/S70mysqld start
Starting MySQL SUCCESS!
root@unknown:/tmp/mnt/sda1# mysqladmin version
mysqladmin  Ver 8.42 Distrib 5.7.4-m14, for Linux on mipsel
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.7.4-m14
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /tmp/mysql.sock
Uptime:                 15 sec

Threads: 1  Questions: 2  Slow queries: 0  Opens: 93  Flush tables: 1  Open tables: 86  Queries per second avg: 0.133
root@unknown:/tmp/mnt/sda1#

Though, on my Asus RT-N66U mysql-5.7.4 works even without swap file. And after reboot it still works OK. That [ERROR] InnoDB: Cannot allocate memory for the buffer pool clearly indicates that you've run out of memory, so I can only assume you forgot to enable swap area using swapon.

As for /opt/share contents, mysql uses these files during initialization, and during mysqld runtime too (I think). Therefore, if I move them, I also need to patch mysql somehow to be able to find its files, but I was unable to find how exactly to do that with mysql-5.7.9 (didn't even try for 5.7.4).

LeeWiu commented 8 years ago

Something wrong with the installer, did you modified it since last time? Clean optware-ng install Reboot router Install mysql

ASUSWRT-Merlin RT-AC66U_3.0.0.4 Mon Nov  2 14:59:04 UTC 2015
admin@RT-AC66U:/tmp/home/root# free
             total         used         free       shared      buffers
Mem:        239532        62296       177236            0         8104
-/+ buffers:              54192       185340
Swap:       524280            0       524280
admin@RT-AC66U:/tmp/home/root# ipkg install mysql
Installing mysql (5.7.4-4) to /opt/...
Downloading http://optware-ng.zyxmon.org/buildroot-mipsel-ng/mysql_5.7.4-4_mipsel.ipk
Installing zlib (1.2.8-2) to /opt/...
Downloading http://optware-ng.zyxmon.org/buildroot-mipsel-ng/zlib_1.2.8-2_mipsel.ipk
Installing ncurses (5.7-4) to /opt/...
Downloading http://optware-ng.zyxmon.org/buildroot-mipsel-ng/ncurses_5.7-4_mipsel.ipk
Installing libevent (2.0.20-1) to /opt/...
Downloading http://optware-ng.zyxmon.org/buildroot-mipsel-ng/libevent_2.0.20-1_mipsel.ipk
Installing libstdc++ (6.0.21-6) to /opt/...
Downloading http://optware-ng.zyxmon.org/buildroot-mipsel-ng/libstdc++_6.0.21-6_mipsel.ipk
Installing perl (5.20.1-3) to /opt/...
Downloading http://optware-ng.zyxmon.org/buildroot-mipsel-ng/perl_5.20.1-3_mipsel.ipk
Installing libdb (5.3.21-1) to /opt/...
Downloading http://optware-ng.zyxmon.org/buildroot-mipsel-ng/libdb_5.3.21-1_mipsel.ipk
Installing gdbm (1.8.3-4) to /opt/...
Downloading http://optware-ng.zyxmon.org/buildroot-mipsel-ng/gdbm_1.8.3-4_mipsel.ipk
Configuring gdbm
Configuring libdb
Configuring libevent
Configuring libstdc++
Configuring mysql
Installing MySQL system tables...OK

Filling help tables...OK

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

  /opt/bin/mysqladmin -u root password 'new-password'

See the manual for more instructions.

You can start the MySQL daemon with:

  cd . ; /opt/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

  cd mysql-test ; perl mysql-test-run.pl

Please report any problems at http://bugs.mysql.com/

The latest information about MySQL is available on the web at

  http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

=====================================================
After mysqld starts, don't forget to execute
/opt/bin/mysqladmin password
to set root password
=====================================================
Starting MySQL........................ ERROR! The server quit without updating PID file (/opt/var/lib/mysql/RT-AC66U.pid).
postinst script returned status 1
ERROR: mysql.postinst returned 1
Configuring ncurses
update-alternatives: Linking //opt/bin/clear to /opt/bin/ncurses-clear
Configuring perl
Configuring zlib
Successfully terminated.
admin@RT-AC66U:/tmp/home/root# ps | grep mysql
 1552 admin     1416 S    grep mysql
admin@RT-AC66U:/tmp/home/root#
admin@RT-AC66U:/tmp/home/root# mysqladmin version
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists!
alllexx88 commented 8 years ago

No, I haven't modified it, and it works fine on my router:

root@unknown:/tmp/home/root# ipkg remove mysql
Removing package mysql from /opt/...
killall: mysqld: no process killed
Successfully terminated.
root@unknown:/tmp/home/root# rm -rf /opt/var/lib/mysql/*
root@unknown:/tmp/mnt/sda1# ipkg install mysql
Installing mysql (5.7.4-4) to /opt/...
Downloading http://192.168.1.15/public/optware/buildroot-mipsel-ng/mysql_5.7.4-4_mipsel.ipk
Configuring mysql
Installing MySQL system tables...OK

Filling help tables...OK

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

  /opt/bin/mysqladmin -u root password 'new-password'

See the manual for more instructions.

You can start the MySQL daemon with:

  cd . ; /opt/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

  cd mysql-test ; perl mysql-test-run.pl

Please report any problems at http://bugs.mysql.com/

The latest information about MySQL is available on the web at

  http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

=====================================================
After mysqld starts, don't forget to execute
/opt/bin/mysqladmin password
to set root password
=====================================================
Starting MySQL............................... SUCCESS!
Successfully terminated.
root@unknown:/tmp/mnt/sda1# mysqladmin version
mysqladmin  Ver 8.42 Distrib 5.7.4-m14, for Linux on mipsel
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.7.4-m14
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /tmp/mysql.sock
Uptime:                 1 min 24 sec

Threads: 1  Questions: 2  Slow queries: 0  Opens: 93  Flush tables: 1  Open tables: 86  Queries per second avg: 0.023

Try to launch it in the foreground to see the error message:

/opt/bin/mysqld
LeeWiu commented 8 years ago

Same error, I don't think my router's ram is faulty

admin@RT-AC66U:/tmp/mnt/sda1/optware-ng# /opt/bin/mysqld
2015-12-18T16:54:24.750458Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-12-18T16:54:24.825035Z 0 [Note] /opt/bin/mysqld (mysqld 5.7.4-m14) starting as process 4213 ...
2015-12-18T16:54:25.101742Z 0 [Note] Plugin 'FEDERATED' is disabled.
2015-12-18T16:54:25.430483Z 0 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2015-12-18T16:54:25.433469Z 0 [Note] InnoDB: Mutexes uses sys mutexes and rw_locks use InnoDB's own implementation
2015-12-18T16:54:25.436465Z 0 [Note] InnoDB: Uses system mutexes
2015-12-18T16:54:25.439402Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2015-12-18T16:54:25.541553Z 0 [Note] InnoDB: Number of pools: 1
2015-12-18T16:54:25.951146Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2015-12-18T16:54:26.089798Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128.0M, instances = 1
2015-12-18T16:54:26.097297Z 0 [ERROR] InnoDB: mmap(136314880 bytes) failed; errno 12
2015-12-18T16:54:26.103163Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2015-12-18T16:54:26.106449Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2015-12-18T16:54:26.109449Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
16:54:26 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=8388608
read_buffer_size=262144
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 57854 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

admin@RT-AC66U:/tmp/mnt/sda1/optware-ng#

Do you still have the previous ipk which worked for me?

alllexx88 commented 8 years ago

I don't delete previous versions from the server, they're just not indexed: http://optware-ng.zyxmon.org/buildroot-mipsel-ng/ Try previous versions if you like, but that

Mysql installs and start fine, thanks

comment was actually after the last update

alllexx88 commented 8 years ago

The error you have is due to insufficient memory. Here's an exactly the same issue on stackoverflow: http://stackoverflow.com/questions/10284532/amazon-ec2-mysql-aborting-start-because-innodb-mmap-x-bytes-failed-errno-12 and the solution is adding some more swap storage. This line

2015-12-18T16:54:26.097297Z 0 [ERROR] InnoDB: mmap(136314880 bytes) failed; errno 12

means that system function mmap failed to allocate 130MB. This means that either there's not enough memory, or something in your system is terribly wrong in addressing/adding swap storage

LeeWiu commented 8 years ago

After many, many tries, I realised that 512MB swap are not enough but 1GB yes. Thanks

alllexx88 commented 8 years ago

512MB not enough? Well, it's possible. In almost idle state mysqld is eating up more than 350MB on my RT-N66U. But still odd. P.S. The latest binary upgrade broke things a little. I've fixed this and am compiling right now. P.P.S. For mipsel, fixed version already uploaded

LeeWiu commented 8 years ago

Thanks, after restarting mysql, I get this error

2015-12-19T16:08:06.394888Z 0 [Note] InnoDB: Waiting for purge to start
2015-12-19T16:08:06.473740Z 0 [ERROR] InnoDB: Tablespace size stored in header is 4864 pages, but the sum of data file sizes is only 768 pages
2015-12-19T16:08:06.474104Z 0 [ERROR] InnoDB: Cannot start InnoDB. The tail of the system tablespace is missing. Have you edited innodb_data_file_path in my.cnf in an InnoDB: inappropriate way, removing ibdata files from there? You can set innodb_force_recovery=1 in my.cnf to force InnoDB: a startup if you are trying to recover a badly corrupt database.
2015-12-19 18:08:06 0x5c3264f0  InnoDB: Assertion failure in thread 1546806512 in file buf0flu.cc line 2735
InnoDB: Failing assertion: srv_get_active_thread_type() == SRV_NONE
InnoDB: We intentionally generate a memory trap.

So I added in /opt/etc/my.cnf

innodb_force_recovery=1

And it's ok

LeeWiu commented 8 years ago

Actually it's not ok, if I want to edit some value with phpmyadmin, get error: Operation not allowed when innodb_force_recovery > 1

alllexx88 commented 8 years ago

This error indicates that your database got corrupted. You can either try to recover it (google for it) or if it doesn't contain anything important, reinitialize:

/opt/bin/mysql_install_db --basedir=/opt --datadir=/opt/var/lib/mysql --lc-messages-dir=/opt/share/mysql/english --skip-random-passwords --keep-my-cnf --force

If this happens again, you should try to look for the possible causes, e.g. by checking out the logs.

At least I can say it doesn't happen to me with a freshly initialized database after mysql restart:

root@unknown:/tmp# /opt/bin/mysql_install_db --basedir=/opt --datadir=/opt/var/lib/mysql --lc-messages-dir=/opt/share/mysql/english --skip-random-passwords --keep-my-cnf --force
Installing MySQL system tables...OK

Filling help tables...OK

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

  /opt/bin/mysqladmin -u root password 'new-password'

See the manual for more instructions.

You can start the MySQL daemon with:

  cd . ; /opt/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

  cd mysql-test ; perl mysql-test-run.pl

Please report any problems at http://bugs.mysql.com/

The latest information about MySQL is available on the web at

  http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

root@unknown:/tmp# /opt/etc/init.d/S70mysqld start
Starting MySQL....................................... SUCCESS!
root@unknown:/tmp# mysqladmin version
mysqladmin  Ver 8.42 Distrib 5.7.4-m14, for Linux on mipsel
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.7.4-m14
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /tmp/mysql.sock
Uptime:                 31 sec

Threads: 1  Questions: 2  Slow queries: 0  Opens: 93  Flush tables: 1  Open tables: 86  Queries per second avg: 0.064
root@unknown:/tmp# /opt/etc/init.d/S70mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL................................... SUCCESS!
root@unknown:/tmp# mysqladmin version
mysqladmin  Ver 8.42 Distrib 5.7.4-m14, for Linux on mipsel
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.7.4-m14
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /tmp/mysql.sock
Uptime:                 15 sec

Threads: 1  Questions: 2  Slow queries: 0  Opens: 93  Flush tables: 1  Open tables: 86  Queries per second avg: 0.133
alllexx88 commented 8 years ago

Needless to say, you shouldn't need to set

innodb_force_recovery=1

in the config file. This option is needed during crash recovery only.

LeeWiu commented 8 years ago

I'm going crazy with this errors, now installing and restarting is fine after increased swap to 1Gb but after installing osticket and restarted mysql I get the same error

2015-12-19T16:08:06.394888Z 0 [Note] InnoDB: Waiting for purge to start
2015-12-19T16:08:06.473740Z 0 [ERROR] InnoDB: Tablespace size stored in header is 4864 pages, but the sum of data file sizes is only 768 pages
2015-12-19T16:08:06.474104Z 0 [ERROR] InnoDB: Cannot start InnoDB. The tail of the system tablespace is missing. Have you edited innodb_data_file_path in my.cnf in an InnoDB: inappropriate way, removing ibdata files from there? You can set innodb_force_recovery=1 in my.cnf to force InnoDB: a startup if you are trying to recover a badly corrupt database.
2015-12-19 18:08:06 0x5c3264f0  InnoDB: Assertion failure in thread 1546806512 in file buf0flu.cc line 2735
InnoDB: Failing assertion: srv_get_active_thread_type() == SRV_NONE
InnoDB: We intentionally generate a memory trap.

Dunno what to do anymore

alllexx88 commented 8 years ago

Well, 5.7.4 may be buggy, since it's still rather old. I looked a bit deeper into why 5.7.9 won't build for mipsel arch, and it's cause 64 bit atomics aren't possible for this arch. There's something I want to try, if I succeed, I'll make a test build for you to test it if you don't mind. But don't hold your breath: it'll take some time.

LeeWiu commented 8 years ago

Of course I want to try, waiting patiently...

alllexx88 commented 8 years ago

http://optware-ng.zyxmon.org/test/buildroot-mipsel-ng/mysql_5.7.9-5_mipsel.ipk Remember that starting from 5.7.6 initialization is done using mysqld binary:

/opt/bin/mysqld --initialize-insecure

What I've done was to replace unsupported 64bit atomic operations with non-atomic variants. If it proves to be stable (relying on you to test), I will push it to the repos for all targets that currently have 5.7.4.

alllexx88 commented 8 years ago

Also, if you wish to test using php mysql(i) extension, please issue this command

ln -s libmysqlclient.so.20 /opt/lib/libmysqlclient.so.18

This is needed, since libmysqlclient soname changed from libmysqlclient.so.18 in 5.7.4 to libmysqlclient.so.20 in 5.7.9. I will upgrade php-mysql package (and some others) to be linked properly if 5.7.9 works well.

LeeWiu commented 8 years ago

It's installing fine, restarting but when osticket trying to create tables I get errors

151221 08:22:26 mysqld_safe Starting mysqld daemon with databases from /opt/var/lib/mysql
2015-12-21T06:22:27.461414Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-12-21T06:22:27.462071Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2015-12-21T06:22:27.462476Z 0 [Note] /opt/bin/mysqld (mysqld 5.7.9) starting as process 14913 ...
2015-12-21T06:22:27.655691Z 0 [Note] InnoDB: PUNCH HOLE support not available
2015-12-21T06:22:27.656141Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-12-21T06:22:27.656498Z 0 [Note] InnoDB: Uses event mutexes
2015-12-21T06:22:27.656847Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2015-12-21T06:22:27.657192Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2015-12-21T06:22:27.661402Z 0 [Note] InnoDB: Number of pools: 1
2015-12-21T06:22:27.670206Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2015-12-21T06:22:57.066866Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2015-12-21T06:22:57.611234Z 0 [Note] InnoDB: Completed initialization of buffer pool
2015-12-21T06:22:58.230743Z 0 [Note] InnoDB: page_cleaner coordinator priority: -20
2015-12-21T06:22:59.539004Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2015-12-21T06:23:02.961381Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2015-12-21T06:23:03.053320Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2015-12-21T06:23:04.869360Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2015-12-21T06:23:04.910819Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2015-12-21T06:23:04.911223Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2015-12-21T06:23:04.913696Z 0 [Note] InnoDB: Waiting for purge to start
2015-12-21T06:23:04.979356Z 0 [Note] InnoDB: Waiting for purge to start
2015-12-21T06:23:05.041933Z 0 [Note] InnoDB: Waiting for purge to start
2015-12-21T06:23:05.099347Z 0 [Note] InnoDB: Waiting for purge to start
2015-12-21T06:23:05.159319Z 0 [Note] InnoDB: Waiting for purge to start
2015-12-21T06:23:05.238934Z 0 [Note] InnoDB: Waiting for purge to start
2015-12-21T06:23:05.299273Z 0 [Note] InnoDB: Waiting for purge to start
2015-12-21T06:23:05.370331Z 0 [Note] InnoDB: Waiting for purge to start
2015-12-21T06:23:05.454964Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 7551ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
2015-12-21T06:23:05.564628Z 0 [Note] InnoDB: 5.7.9 started; log sequence number 2472083
2015-12-21T06:23:05.697871Z 0 [Note] InnoDB: not started
2015-12-21T06:23:05.743805Z 0 [Note] InnoDB: Loading buffer pool(s) from /tmp/mnt/sda1/optware-ng/var/lib/mysql/ib_buffer_pool
2015-12-21T06:23:06.456305Z 0 [Note] Plugin 'FEDERATED' is disabled.
2015-12-21T06:23:06.822963Z 0 [Note] InnoDB: Buffer pool(s) load completed at 151221  8:23:06
2015-12-21T06:23:07.342325Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2015-12-21T06:23:07.342687Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2015-12-21T06:23:07.842128Z 0 [Warning] CA certificate ca.pem is self signed.
2015-12-21T06:23:07.843072Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2015-12-21T06:23:09.538255Z 0 [Note] Event Scheduler: Loaded 0 events
2015-12-21T06:23:09.590864Z 0 [Note] /opt/bin/mysqld: ready for connections.
Version: '5.7.9'  socket: '/tmp/mysql.sock'  port: 0  Optware-ng distribution 5.7.9-5
2015-12-21T06:27:15.786559Z 0 [Note] Giving 0 client threads a chance to die gracefully
2015-12-21T06:27:15.817502Z 0 [Note] Shutting down slave threads
2015-12-21T06:27:15.842830Z 0 [Note] Forcefully disconnecting 0 remaining clients
2015-12-21T06:27:15.882205Z 0 [Note] Event Scheduler: Purging the queue. 0 events
2015-12-21T06:27:15.904863Z 0 [Note] Binlog end
2015-12-21T06:27:16.020595Z 0 [Note] Shutting down plugin 'ngram'
2015-12-21T06:27:16.032348Z 0 [Note] Shutting down plugin 'partition'
2015-12-21T06:27:16.032827Z 0 [Note] Shutting down plugin 'ARCHIVE'
2015-12-21T06:27:16.033304Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2015-12-21T06:27:16.033619Z 0 [Note] Shutting down plugin 'CSV'
2015-12-21T06:27:16.033931Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2015-12-21T06:27:16.034240Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2015-12-21T06:27:16.034542Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2015-12-21T06:27:16.034848Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2015-12-21T06:27:16.035151Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2015-12-21T06:27:16.035452Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2015-12-21T06:27:16.035755Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2015-12-21T06:27:16.036060Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2015-12-21T06:27:16.036363Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2015-12-21T06:27:16.036668Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2015-12-21T06:27:16.036973Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2015-12-21T06:27:16.037275Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2015-12-21T06:27:16.037580Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2015-12-21T06:27:16.037881Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2015-12-21T06:27:16.038184Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2015-12-21T06:27:16.038489Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2015-12-21T06:27:16.038797Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2015-12-21T06:27:16.039097Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2015-12-21T06:27:16.039399Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2015-12-21T06:27:16.039701Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2015-12-21T06:27:16.040007Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2015-12-21T06:27:16.040309Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2015-12-21T06:27:16.040611Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2015-12-21T06:27:16.040914Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2015-12-21T06:27:16.041216Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2015-12-21T06:27:16.041520Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2015-12-21T06:27:16.041823Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2015-12-21T06:27:16.042124Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2015-12-21T06:27:16.042428Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2015-12-21T06:27:16.042730Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2015-12-21T06:27:16.043032Z 0 [Note] Shutting down plugin 'InnoDB'
2015-12-21T06:27:16.052982Z 0 [Note] InnoDB: FTS optimize thread exiting.
2015-12-21T06:27:16.066227Z 0 [Note] InnoDB: Starting shutdown...
2015-12-21T06:27:16.174115Z 0 [Note] InnoDB: Dumping buffer pool(s) to /tmp/mnt/sda1/optware-ng/var/lib/mysql/ib_buffer_pool
2015-12-21T06:27:16.175694Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 151221  8:27:16
2015-12-21T06:27:26.647312Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2472111
2015-12-21T06:27:26.753178Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2015-12-21T06:27:26.753551Z 0 [Note] Shutting down plugin 'MEMORY'
2015-12-21T06:27:26.770681Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2015-12-21T06:27:26.771164Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2015-12-21T06:27:26.771676Z 0 [Note] Shutting down plugin 'MyISAM'
2015-12-21T06:27:26.772031Z 0 [Note] Shutting down plugin 'sha256_password'
2015-12-21T06:27:26.772336Z 0 [Note] Shutting down plugin 'mysql_native_password'
2015-12-21T06:27:26.814873Z 0 [Note] Shutting down plugin 'binlog'
2015-12-21T06:27:26.992423Z 0 [Note] /opt/bin/mysqld: Shutdown complete

151221 08:27:27 mysqld_safe mysqld from pid file /opt/var/lib/mysql/RT-AC66U.pid ended
151221 08:27:31 mysqld_safe Starting mysqld daemon with databases from /opt/var/lib/mysql
2015-12-21T06:27:32.191820Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-12-21T06:27:32.220298Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2015-12-21T06:27:32.220722Z 0 [Note] /opt/bin/mysqld (mysqld 5.7.9) starting as process 16176 ...
2015-12-21T06:27:32.429259Z 0 [Note] InnoDB: PUNCH HOLE support not available
2015-12-21T06:27:32.429729Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-12-21T06:27:32.430102Z 0 [Note] InnoDB: Uses event mutexes
2015-12-21T06:27:32.430469Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2015-12-21T06:27:32.430834Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2015-12-21T06:27:32.434904Z 0 [Note] InnoDB: Number of pools: 1
2015-12-21T06:27:32.451628Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2015-12-21T06:28:04.354609Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2015-12-21T06:28:04.672876Z 0 [Note] InnoDB: Completed initialization of buffer pool
2015-12-21T06:28:05.016035Z 0 [Note] InnoDB: page_cleaner coordinator priority: -20
2015-12-21T06:28:05.712371Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2015-12-21T06:28:06.599925Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2015-12-21T06:28:06.600643Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2015-12-21T06:28:08.052597Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2015-12-21T06:28:08.068411Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2015-12-21T06:28:08.068810Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2015-12-21T06:28:08.144352Z 0 [Note] InnoDB: Waiting for purge to start
2015-12-21T06:28:08.469075Z 0 [Note] InnoDB: Waiting for purge to start
2015-12-21T06:28:08.523231Z 0 [Note] InnoDB: Waiting for purge to start
2015-12-21T06:28:08.583192Z 0 [Note] InnoDB: Waiting for purge to start
2015-12-21T06:28:08.644511Z 0 [Note] InnoDB: 5.7.9 started; log sequence number 2472111
2015-12-21T06:28:08.659922Z 0 [Note] InnoDB: Loading buffer pool(s) from /tmp/mnt/sda1/optware-ng/var/lib/mysql/ib_buffer_pool
2015-12-21T06:28:08.714888Z 0 [Note] InnoDB: not started
2015-12-21T06:28:08.859442Z 0 [Note] Plugin 'FEDERATED' is disabled.
2015-12-21T06:28:12.658397Z 0 [Note] InnoDB: Buffer pool(s) load completed at 151221  8:28:12
2015-12-21T06:28:12.720897Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2015-12-21T06:28:12.721248Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2015-12-21T06:28:13.200443Z 0 [Warning] CA certificate ca.pem is self signed.
2015-12-21T06:28:13.201186Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2015-12-21T06:28:14.850119Z 0 [Note] Event Scheduler: Loaded 0 events
2015-12-21T06:28:14.851613Z 0 [Note] /opt/bin/mysqld: ready for connections.
Version: '5.7.9'  socket: '/tmp/mysql.sock'  port: 0  Optware-ng distribution 5.7.9-5
2015-12-21T06:28:56.657201Z 2 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file ./osticket/ost_api_key.ibd, desired size 32768 bytes. Operating system error number 89. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/ operating-system-error-codes.html
2015-12-21T06:28:56.664514Z 2 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file ./osticket/ost_api_key.ibd, desired size 16384 bytes. Operating system error number 89. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/ operating-system-error-codes.html
alllexx88 commented 8 years ago

I don't see any issues. This 

posix_fallocate(): Failed to preallocate data for file

error is OK. posix_fallocate isn't supported by ext filesystems: it was introduced to support FusionIO HW/Firmware. I get these errors with ext2/3/4 when initializing, but mysql still initializes fine. Have you checked whether OSTicket works?

LeeWiu commented 8 years ago

As soon as osticket starting install, I get a blank web page and a lot of

InnoDB: posix_fallocate(): Failed to preallocate data for file ./osticket/ost_api_key.ibd, desired size 16384 bytes. Operating system error number 89. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/ operating-system-error-codes.html

in .err http://pastebin.com/Kb7nChzd

alllexx88 commented 8 years ago

To rule out posix_fallocate, I can compile with '-DNO_FALLOCATE' flag. To see if the issue indeed lies in posix_fallocate.

alllexx88 commented 8 years ago

This is not a mysql issue you're having with osticket, but more likely a php issue. I successfully installed osticket on armv7, with lots of InnoDB: posix_fallocate(): Failed to preallocate data for file errors during installation, yet it's working fine now. I remember I wasn't able to get owncloud8 to work on mipsel for some reason. I also tried to install osticket on mipsel router, and got the same issue after filling all the information (db and user, etc.). This looks to be the same error with php dying for some reason. It is a serious issue, but most likely not related to mysql.

php indeed is crashing now when trying to process index.php:

root@unknown:/opt/www/osticket# php index.php
Segmentation fault

I will try to debug it, hopefully I'll find a solution, unlike the last time

LeeWiu commented 8 years ago

Yes, it's strange, but with previous mysql version php not crashing, only db get corrupted.

alllexx88 commented 8 years ago

Huh, the issue here was due to php mysqli extension compiled against previous (5.7.4) mysql version. It was just a matter of recompiling. I got osTicket to install and work OK on my mipsel router. Please test it yourself (mysql 5.7.9 and fixed php are now in repos).

LeeWiu commented 8 years ago

Sorry for the late reply, YOU DID IT, thanks :+1: Would be great if you compile with '-DNO_FALLOCATE' flag to not show all this errors.

alllexx88 commented 8 years ago

I'm hesitant to compile with '-DNO_FALLOCATE' flag, since posix_fallocate() is good to be used with SSDs. As I recently learnt, posix_fallocate() is implemented using fallocate() system call, and, as the fallocate(1) program man page says:

As of the Linux Kernel v2.6.31, the fallocate system call is supported
by the btrfs, ext4, ocfs2, and xfs filesystems.

So, in theory, Optware-ng can be used with ext4 formatted SSD, where fallocate() is highly recommended, so we don't want to disable it. As for filesystems that don't support this call, posix_fallocate() is used only in 'O_DIRECT' innodb_flush_method (which is triggered during db creation regardless of the settings), and if it fails it falls back to 'fsync' (which is the default), which doesn't use posix_fallocate(). After looking into the sourcecode, innodb seems to check whether filesystem supports posix_fallocate() before using it, however this check always falsely returns 'true' for some reason, and uclibc-ng, unlike glibc, doesn't emulate this function in this case (and this may be a good thing, since glibc man states this emulation is inefficient and with possible race condition).

LeeWiu commented 8 years ago

This was a good explanation, so there is no solution to suppress messages, it's ok 👌, at least mysql working fine :)

LeeWiu commented 8 years ago

https://www.hqt.ro/how-to-install-mysql-server-phpmyadmin-through-optware-ng/ :+1:

alllexx88 commented 8 years ago

Great, thanks! You can try nginx now too. I haven't tested it in combination with php, but it starts OK now.

damonp16 commented 8 years ago

I have the same issue as described in the latest post on

https://www.hqt.ro/how-to-install-mysql-server-phpmyadmin-through-optware-ng/

I get the exact same error just with ac87u.pid

alllexx88 commented 8 years ago

@damonp16 You probably mean this error:

2016-03-20T06:19:17.382374Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.

This can be easily fixed by removing data directory rm -rf /opt/var/lib/mysql, and then re-installing mysql with ipkg install mysql --force-reinstall.

In the future, please provide error info in your posts, rather than giving a link: having to dig through unrelated messages in search of your error is rather discouraging. Also, better create new issues, rather than hijacking old closed ones.