[ QMariaDB ] [ 10.5.11 ] MariaDB Server (wth PhPMyadmin)

This is the best place for community developers to publish their genius work. Your Apps enrich the QNAP Turbo NAS.
User avatar
OneCD
Guru
Posts: 12010
Joined: Sun Aug 21, 2016 10:48 am
Location: "... there, behind that sofa!"

Re: [ QMariaDB ] [ 10.4.12 ] MariaDB Server (wth PhPMyadmin)

Post by OneCD »

Hi and welcome to the forum. :)
bl4d3 wrote: Sun Jun 14, 2020 7:19 am Hi there,
currently the website https://qnapclub.eu is down and I receive the HTML response status code 500.
Yes, this should be only temporary. The admin is working to restore the repo ASAP.
bl4d3 wrote: Sun Jun 14, 2020 7:19 am If there is no download alternative available, are there instructions to create my own ".qpkg"?
The funny thing about QPKGs is: if you know how to make them, you don't need to make them. :DD

ImageImageImageImageImageImageImageImageImageImageImageImageImageImageImageImageImageImage
Hoddl
First post
Posts: 1
Joined: Mon Oct 05, 2015 10:51 pm

Re: [ QMariaDB ] [ 10.4.12 ] MariaDB Server (wth PhPMyadmin)

Post by Hoddl »

i have QMARIADB installed on my qnap but how can i use this database?
How can I access it via phpmyadmin?

Please help me, I am an absolute beginner...

Thanks
User avatar
QNAP_Stephane
Experience counts
Posts: 4802
Joined: Wed Mar 27, 2013 1:00 am

Re: [ QMariaDB ] [ 10.5.4 ] MariaDB Server (wth PhPMyadmin)

Post by QNAP_Stephane »

updated to 10.5.4 (x64 NAS)
---------------------------------------------------------------------------------------------------------------------------

Find all QPKG in the MyQnap.org repository https://www.myqnap.org
join our discord server for any inquiry related to qpkg - https://discord.gg/4fPxHSWKQW

----------------------------------------------------------------------------------------------------------------------------
bl4d3
Starting out
Posts: 26
Joined: Fri Oct 05, 2012 3:03 am

Re: [ QMariaDB ] [ 10.4.12 ] MariaDB Server (wth PhPMyadmin)

Post by bl4d3 »

Hoddl wrote: Mon Jun 15, 2020 4:32 am i have QMARIADB installed on my qnap but how can i use this database?
How can I access it via phpmyadmin?
Take a look into the "AppCenter".

It depends:
  • If you downloaded the QMariaDB_10.5.4.0._*.qpkg from https://qnapclub.eu/en/qpkg/525 and uploaded it into QNAP's AppCenter by using it's "Install Manually" feature. You will find the app "QMariaDB 10" here in the category "My Apps" in the official "QNAP Store".
  • Or you added the repository offered by Qnapclub by using your individual API key. Please follow the instructions here: https://qnapclub.eu/en/howto/1. You will find the app "QMariaDB 10" in self created app store.
In both cases just click on the MariaDB seal icon and a new tab will open with the address "https://<<your-station's-ip-address:8081/mariadb/".
bl4d3
Starting out
Posts: 26
Joined: Fri Oct 05, 2012 3:03 am

Re: [ QMariaDB ] [ 10.5.4 ] MariaDB Server (wth PhPMyadmin)

Post by bl4d3 »

Hello to all purchasers of the "QMariaDB_10.4.12.0._*.qpkg" and newer,

after the installation of the QMariaDB you might run into several issue, there is no manual for this QPKG and you might wait for a long time to get e-mail support from QNAPclub.eu. The so called "Maker" doing all of this in their spare time. So be aware of this. Hopefully this short manual is a good first aid for those who start from scratch.
This is an inofficial manual as I'm only a customer like you and I don't get paid for my work. Use at your own risk.


Table of contents
1. Annotation
2. Download issues on QNAPclub.eu
3. Prerequisite
4. User login and default password
5. How to change the root's password
6. Secure MariaDB
7. Remote Access via SSH to your station
8. How to fix: "The secret passphrase in configuration (blowfish_secret) is too short."
9. How to fix: "The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated."
10. "latin1_swedish_ci" and co vs. "utf8mb4_unicode_ci"
11. Restart MariaDB
12. Repair "Access Denied for User 'root'@'localhost' (using password : NO)"
13. Full uninstall QMariaDB
14. Glossary


1. Annotation
Please help to support the community by letting me know, if there is a mistake, how to explain in a better way and what is missing. Please don't hesitate and send a message.


2. Download issues on QNAPclub.eu
It doesn't matter if you try to download a QPKG directly from QNAPclub.eu, from the freeware repository or your unique store link. Sometimes, especially with large files, your download will fail because of network errors.
Image
The fix is easy and costs you only patience. After your download has failed, try again and again. You will see, after several tries you will manage your download successfully.


3. Prerequisite
To work in a comfortable way, you must execute this step by step instruction.
  1. Login to your station.
  2. Open the app "File Station".
  3. Navigate to the network share "/Web/mariadb/".
  4. Right click on the folder "mariadb" and select "Properties" from the context menu.
  5. Switch to tab page "Permission".
  6. You can choose between 2 choices now.
    1. Add your personal user profile by clicking on the green plus button. It is the user, which you use to be able to access the station's network shares.
    2. If you know that your user profile is member of the local "administrators" group you must remove the default user profile "admin" by clicking on the red minus button, then click on the green plus button. In the dialog "Select users and groups" select "Local Groups" and check the entry "administrators" in the "RW" column. Click on the button "Add" now. Finally click on the button "Apply". Confirm the success dialog message by clicking on the "OK" button.

4. User login and default password
  • The default user name is "root" and the password is "qnap". Please change the password for security reasons.
  • To open phpMyAdmin open this address "https://<<station's_IP_address>>:8081/mariadb/index.php".

5. How to change the root's password
  1. After login to phpMyAdmin click in the center of the upper toolbar on the button "User accounts".
  2. In the "User accounts overview" table look for "root" in the column "User name". Click on "Edit privileges" in the "Action" column.
  3. Click on the upper button "Change password".
  4. Enter two times the identical password. For security reason a complex password is recommended.
  5. Click on the button "Go" on the lower far right to overtake your changes.

6. Secure MariaDB
  1. Here the step by step instructions on how to remove the unnecessary database.
    Option A
    1. Login to phpMyAdmin.
    2. Click on the upper left toolbar button "Databases".
    3. In the table check the checkbox in the first column of database "test".
    4. Below the table click on the link "Drop".
    5. Click the button "OK" in the confirmation message.
    6. The database "test" is deleted.
    Option B
    1. Login to phpMyAdmin.
    2. Click on "Console" on the far bottom.
    3. Enter the command:

      Code: Select all

      DROP DATABASE test;
    4. Now enter this command:

      Code: Select all

      DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
  2. Remove anonymous users from "User accounts".
    1. Login to phpMyAdmin.
    2. Click on "Console" on the far bottom.
    3. Enter the command:

      Code: Select all

      DELETE FROM mysql.user WHERE User='';
    4. Check if there are users named "ANY" are left.
    5. If left, delete manually by navigating to "User accounts".
    6. Check the checkbox of the user to delete.
    7. Scroll down and click then on the button "Go".
  3. Remove all user accounts, which have empty passwords.
Reload the privileges tables to apply the changes. To do so enter the command "FLUSH PRIVILEGES;"
Restart the QMariaDB service.


7. Remote Access via SSH to your station
You must install, or simply use their portable versions, WinSCP or PuTTY. I recommend you both, because with PuTTY you can restart services on you station and with WinSCP it is much more comfortable to navigate through your station's folders and to edit files. Please be aware to first check in QNAP's "ControlPanel", category "Network & File Services" and sub-category "Telnet /SSH" the checkbox "Allow SSH connection" (used for WinSCP and PuTTY) and "Enable SFTP" (used for WinSCP).


8. How to fix: "The secret passphrase in configuration (blowfish_secret) is too short."
If you see this message, then follow these step by step instructions.
Image
  1. Access your station's network share "/Web/mariadb".
  2. Open the "config.inc.php".
  3. Scroll to the line "$cfg['blowfish_secret'] = '54c4db576cf003.60815636';"
  4. Change the value "54c4db576cf003.60815636" to a random string with alphanumeric and special characters, which is at least 32 characters long.
  5. Save the change.
  6. Re-open the phpMyAdmin page by opening "https://<<station's_IP_address>>:8081/mariadb/index.php".

9. How to fix: "The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated."
If you see this message, then follow these step by step instructions.
Image
  1. After login to phpMyAdmin click inside the footer in that message on the link "Find out why".
  2. You'll see the line "Configuration of pmadb... not OK".
  3. In the footer below click inside the message "Create a database named 'phpmyadmin' and setup the phpMyAdmin configuration storage there." on the link "Create".
    Image
  4. After a few seconds the new database "phpmyadmin" is generated.
  5. You can navigate back to "Home" and the footer message has disappeared.

10. "latin1_swedish_ci" and co vs. "utf8mb4_unicode_ci"
There is a long development history behind MariaDB. If you create new databases it suggests you to use the default character set "latin1_swedish_ci". The development team behind MariaDB failed to implement proper support for UTF-8 for a long time. After several tries and years they come closer and closer to the UTF-8 standard. They finally did with the "utf8mb4_unicode_ci" character set. If your database need to handle characters which aren't latin and you want the best compatibility choose this.
What about "utf16_unicode_ci" and "utf32_unicode_ci"? First UTF-8 has advantages in cases where ASCII characters are used. Both UTF-8 and ASCII encode one character into 8 bits. The disadvantage of a database with UTF-16 or UTF-32 is clear, they both are space-inefficient. For Asian languages you might prefer one of them.
Comparison:
  • UTF-8: Takes 1 to 4 bytes, depending on the characters used.
  • UTF-16: Takes 2 to 4 bytes, depending on the characters used.
  • UTF-32: Takes 4 bytes in spite of the characters used.

11. Restart MariaDB
Option A
  1. Start the "putty.exe".
  2. Select the category "Session" and enter your station's IP address into the input field "Host Name (or IP address)". Leave the port number "22" and select as "Connection type" the option "SSH".
  3. Click below on the button "Open".
  4. The PuTTY console window will open.
  5. Enter at first your administrator's user name "admin", then press the "Enter" key.
  6. Now enter the password of the user "admin", then press the "Enter" key again. You're logged in your station.
  7. Behind the "[~] #" enter "/etc/init.d/QMariaDB.sh restart" and press the "Enter" key, which will stop your QMariaDB in first place and then right after start it again.
  8. To leave the console enter "exit", then press the "Enter" key.
Option B
  1. Login your station and open the "App Center".
  2. In the category "QNAP Store" in the sub-category "My Apps" search for "QMariaDB 10.x".
  3. Click on the blue button with the down arrow and select "Stop".
  4. After a short waiting time this app is stopped and the blue button turns into black.
  5. To start the app again click on the black button "• Start" to start QMariaDB again.

12. Repair "Access Denied for User 'root'@'localhost' (using password : NO)"
In case you're running into big trouble like "mysqli_real_connect(): (HY000/1045): Access denied for user 'root'@'localhost' " on the phpMyAdmin login screen, because you revoked all active privileges from the user root and deleted him afterwards, then here is your fix.
Image
  1. Open WinSCP or PuTTY and navigate to "/share/CE_CACHEDEV1_DATA/.qpkg/QMariaDB".
  2. Open the "my.cnf" text file.
  3. Scroll down to the row "[mysqld]".
  4. Add in one extra row beneath the following line "skip-grant-tables".
  5. Save the change.
  6. Restart QMariaDB.

13. Full uninstall QMariaDB
If an usual uninstall isn't enough, here are the steps to perform a full cleanup of your station.
  1. Login to your station.
  2. Open the "App Center".
  3. In the category "QNAP Store" in the sub-category "My Apps" search for "QMariaDB 10".
  4. Click on the blue button with the down arrow and select "Stop".
  5. After a short waiting time this app is stopped and the blue button turns into black.
  6. Click on the down arrow again and select "Remove".
  7. Open WinSCP or PuTTY and navigate to "/share/CE_CACHEDEV1_DATA/".
  8. Delete the folder "QMariaDB". (This folder contains your databases.)
  9. Navigate to "/tmp".
  10. Delete the file "QMariadDB2.sock".

14. Glossary
  • P
    • PuTTY is a free and open-source terminal emulator, serial console and network file transfer application. It supports several network protocols, including SCP, SSH, Telnet, rlogin, and raw socket connection. Get it from the official website: https://www.chiark.greenend.org.uk/~sgtatham/putty/
  • W
    • WinSCP (Windows Secure Copy) is a free and open-source SFTP, FTP, WebDAV, Amazon S3 and SCP client for Microsoft Windows. Get it from the official website: https://winscp.net
User avatar
QNAP_Stephane
Experience counts
Posts: 4802
Joined: Wed Mar 27, 2013 1:00 am

Re: [ QMariaDB ] [ 10.5.8 ] MariaDB Server (wth PhPMyadmin)

Post by QNAP_Stephane »

updated to 10.5.8 + phpmyadmin
---------------------------------------------------------------------------------------------------------------------------

Find all QPKG in the MyQnap.org repository https://www.myqnap.org
join our discord server for any inquiry related to qpkg - https://discord.gg/4fPxHSWKQW

----------------------------------------------------------------------------------------------------------------------------
mr_hyde
Easy as a breeze
Posts: 409
Joined: Sun Sep 02, 2007 3:51 pm
Location: Germany

Re: [ QMariaDB ] [ 10.5.8 ] MariaDB Server (wth PhPMyadmin)

Post by mr_hyde »

QNAP_Stephane wrote: Tue Jan 12, 2021 3:57 am updated to 10.5.8 + phpmyadmin
Hi,

I was trying to upgrade the x64 version from 10.4.12 on my TS453A. However, the installation stops at @45% with the following message:

Code: Select all

Fehler	2021-01-17	13:50:07	admin	127.0.0.1	App Center	App Installation	[App Center] Failed to install QMariaDB due to data file error.
I downloaded the file again, but still have the same issue. Interestingly, this QPKG file has a size of ~190MB, while the last working version had ~ 890MB

EDIT:
ok, got it now working.

I had to stop and de-install version 10.4.12 prior to installation of the new QPKG.

In addition I had to rename the existing ib_logfile0 ib_logfile1 with the extension .old (or remove them prior to installation of the new qpkg as I received errors.

Code: Select all

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

Regards,
Alex
QNAP TS-453A
4 * Hitachi 3 TB HDD (RAID 5)
FW: 4.5.1 1540
m8ichael
New here
Posts: 4
Joined: Sat Sep 12, 2015 11:11 pm

Re: [ QMariaDB ] [ 10.5.8 ] MariaDB Server (wth PhPMyadmin)

Post by m8ichael »

Hi,

I can't get the database to work properly. I can access the administration, but as soon as an application tries to access the DB from outside the NAS (on port 3308), I get an access denied there. What am I doing wrong?

Michael
mr_hyde
Easy as a breeze
Posts: 409
Joined: Sun Sep 02, 2007 3:51 pm
Location: Germany

Re: [ QMariaDB ] [ 10.5.8 ] MariaDB Server (wth PhPMyadmin)

Post by mr_hyde »

m8ichael wrote: Sat Jan 23, 2021 10:28 pm Hi,

I can't get the database to work properly. I can access the administration, but as soon as an application tries to access the DB from outside the NAS (on port 3308), I get an access denied there. What am I doing wrong?

Michael
You need to change the cofiguration of your my.conf file in the qpkg/QMariaDB directory

search for

Code: Select all

bind_address   = 127.0.0.1
and chang it to: (Iannd a '#' before the statement)

Code: Select all

#bind_address   = 127.0.0.1
Then you should be able to access the database also from other clients than the localhost.
QNAP TS-453A
4 * Hitachi 3 TB HDD (RAID 5)
FW: 4.5.1 1540
m8ichael
New here
Posts: 4
Joined: Sat Sep 12, 2015 11:11 pm

Re: [ QMariaDB ] [ 10.5.8 ] MariaDB Server (wth PhPMyadmin)

Post by m8ichael »

mr_hyde wrote: Sun Jan 24, 2021 6:45 pm
m8ichael wrote: Sat Jan 23, 2021 10:28 pm Hi,

I can't get the database to work properly. I can access the administration, but as soon as an application tries to access the DB from outside the NAS (on port 3308), I get an access denied there. What am I doing wrong?

Michael
You need to change the cofiguration of your my.conf file in the qpkg/QMariaDB directory

search for

Code: Select all

bind_address   = 127.0.0.1
and chang it to: (Iannd a '#' before the statement)

Code: Select all

#bind_address   = 127.0.0.1
Then you should be able to access the database also from other clients than the localhost.
OK, that works! 8)

Thanks!
User avatar
reforo
New here
Posts: 8
Joined: Thu Jan 26, 2017 1:59 am

Re: [ QMariaDB ] [ 10.5.8 ] MariaDB Server (wth PhPMyadmin)

Post by reforo »

Hello good morning, yesterday I bought mariadb, (for some new projects).
The installation does not give any error, but I cannot enter embedded phpmyadmin. This is the error:

mysqli::real_connect(): (HY000/2002): Connection refused
I try to restart qmariadb with this command: /etc/init.d/QMariaDB.sh restart

It doesn't start, and it gives me the following error:

Code: Select all

share QMariaDB exists in your share
DB exists in your share
/etc/init.d/QMariaDB.sh: line 65: wait: pid 15 is not a child of this shell
/etc/init.d/QMariaDB.sh: line 71: wait: pid 15 is not a child of this shell
Changing QMariaDB Custom MariaDB root password to qnap
1
/share/CACHEDEV1_DATA/.qpkg/QMariaDB/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/tmp/QMariadDB2.sock' (111)'
Check that mysqld is running and that the socket: '/tmp/QMariadDB2.sock' exists!
210203 18:02:57 mysqld_safe Logging to '/share/CACHEDEV1_DATA/QMariaDB/qmariadb.err'.
210203 18:02:57 mysqld_safe Starting mysqld daemon with databases from /share/CACHEDEV1_DATA/QMariaDB
2
/share/CACHEDEV1_DATA/.qpkg/QMariaDB/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Lost connection to MySQL server at 'handshake: reading initial communication packet', system error: 104'
I have also seen that adding in the file my.cnf "skip-grant-tables" if you start mariadb this is the console:

Code: Select all

# /etc/init.d/QMariaDB.sh  start
share QMariaDB exists in your share
DB exists in your share
/etc/init.d/QMariaDB.sh: line 65: wait: pid 15 is not a child of this shell
/etc/init.d/QMariaDB.sh: line 71: wait: pid 15 is not a child of this shell
Changing QMariaDB Custom MariaDB root password to qnap
1
/share/CACHEDEV1_DATA/.qpkg/QMariaDB/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/tmp/QMariadDB2.sock' (111)'
Check that mysqld is running and that the socket: '/tmp/QMariadDB2.sock' exists!
210203 18:08:30 mysqld_safe Logging to '/share/CACHEDEV1_DATA/QMariaDB/qmariadb.err'.
210203 18:08:30 mysqld_safe Starting mysqld daemon with databases from /share/CACHEDEV1_DATA/QMariaDB
2
3
/share/CACHEDEV1_DATA/.qpkg/QMariaDB/bin/mysqladmin:
You cannot use 'password' command as mysqld runs
 with grant tables disabled (was started with --skip-grant-tables).
Use: "mysqladmin flush-privileges password '*'" instead
ERROR 1290 (HY000) at line 1: The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement
/etc/init.d/QMariaDB.sh: line 103: wait: pid 5 is not a child of this shell
so if it lets me enter with the user root and the pass qnap, but I do not have all the control of users of phpmyadmin.
If someone could help me.
Sorry for my English, I'm Spanish.
cabaseira
Starting out
Posts: 10
Joined: Sat Feb 29, 2020 4:19 am

Re: [ QMariaDB ] [ 10.5.8 ] MariaDB Server (wth PhPMyadmin)

Post by cabaseira »

mr_hyde wrote: Sun Jan 24, 2021 6:45 pm
You need to change the cofiguration of your my.conf file in the qpkg/QMariaDB directory

search for

Code: Select all

bind_address   = 127.0.0.1
and chang it to: (Iannd a '#' before the statement)

Code: Select all

#bind_address   = 127.0.0.1
Then you should be able to access the database also from other clients than the localhost.
Thanks!!! It worked.
soapbox23
First post
Posts: 1
Joined: Tue Mar 02, 2021 2:15 am

Re: [ QMariaDB ] [ 10.5.8 ] MariaDB Server (wth PhPMyadmin)

Post by soapbox23 »

mr_hyde wrote: Sun Jan 24, 2021 6:45 pm
m8ichael wrote: Sat Jan 23, 2021 10:28 pm Hi,

I can't get the database to work properly. I can access the administration, but as soon as an application tries to access the DB from outside the NAS (on port 3308), I get an access denied there. What am I doing wrong?

Michael
You need to change the cofiguration of your my.conf file in the qpkg/QMariaDB directory

search for

Code: Select all

bind_address   = 127.0.0.1
and chang it to: (Iannd a '#' before the statement)

Code: Select all

#bind_address   = 127.0.0.1
Then you should be able to access the database also from other clients than the localhost.
Sorry for the noobish response, but I cannot for the life of me find the my.conf file, nor a "qpkg" folder anywhere. Can you tell me where this is located so I can make this edit?

EDIT:

Well I'm dumb and I found it. If anyone else is wondering, it's in /share/CACHEDEV1_DATA/.qpkg/QMariaDB and it's called my.cnf. Thanks for the great app!
mr_hyde
Easy as a breeze
Posts: 409
Joined: Sun Sep 02, 2007 3:51 pm
Location: Germany

Re: [ QMariaDB ] [ 10.5.8 ] MariaDB Server (wth PhPMyadmin)

Post by mr_hyde »

soapbox23 wrote: Tue Mar 02, 2021 2:21 am
Sorry for the noobish response, but I cannot for the life of me find the my.conf file, nor a "qpkg" folder anywhere. Can you tell me where this is located so I can make this edit?

EDIT:

Well I'm dumb and I found it. If anyone else is wondering, it's in /share/CACHEDEV1_DATA/.qpkg/QMariaDB and it's called my.cnf. Thanks for the great app!
Sorry, just noticed the post today.

The absolute path my vary with your device (e.g. RAID or not,...). Typically the path is linked to /opt/QMariaDB which should be the same for all devices.
QNAP TS-453A
4 * Hitachi 3 TB HDD (RAID 5)
FW: 4.5.1 1540
User avatar
QNAP_Stephane
Experience counts
Posts: 4802
Joined: Wed Mar 27, 2013 1:00 am

Re: [ QMariaDB ] [ 10.5.9 ] MariaDB Server (wth PhPMyadmin)

Post by QNAP_Stephane »

updated to 10.5.9 and PHPMyAdmin to 5.1.0
---------------------------------------------------------------------------------------------------------------------------

Find all QPKG in the MyQnap.org repository https://www.myqnap.org
join our discord server for any inquiry related to qpkg - https://discord.gg/4fPxHSWKQW

----------------------------------------------------------------------------------------------------------------------------
Post Reply

Return to “Community Apps”