Archive for the ‘MySQL 5.7’ Category
MySQL File Privilege
While preparing a set of student instructions to create a MySQL 8 (8.0.21) Windows 10 instance I found an error with LOAD
command and the --secure-file_priv
variable set in the my.ini
file. After granting the global FILE
permission to the previously provisioned student
user:
GRANT FILE ON *.* TO 'student'@'localhost'; |
Any attempt to run the following command failed:
LOAD DATA INFILE 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\avenger.csv' INTO TABLE avenger FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'; |
and, raise this error message:
ERROR: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement |
The following covers my exploration to try and fix this error without removing a designated directory for secure file uploads. While MySQL 5.7 contains the request for he server-file-priv
variable, there is nothing in the MySQL System Server Variables document on how to troubleshoot the server-file-priv
variable when set. Somehow, I think there should be some mention of how to resolve this error without unsetting the server-file-privy
variable.
I checked and fixed all Windows 10 sharing and read-write privileges on the secure-file-priv
designated directory. They Windows 10 settings allowed for global sharing and both read and write privileges, but the LOAD
command failed to load the file contents from the authorized Uploads
directory.
The MySQL FILE
privilege is a global privilege to read and write files on the local server. MySQL 8 installation on Windows 10 sets the following directory as the target for uploading files in the my.ini
file:
# Secure File Priv. secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads" |
You can find the setting in the C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
file. You can find this value without referencing the my.ini
file by querying the data:
show variables like 'secure_file_priv'; |
A new installation should return:
+------------------+------------------------------------------------+ | Variable_name | Value | +------------------+------------------------------------------------+ | secure_file_priv | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ | +------------------+------------------------------------------------+ 1 row in set (0.2253 sec) |
You can find the my.ini
file in the C:\ProgramData\MySQL\MySQL Server 8.0
directory of a standard Windows 10 file system.
- Using the Windows’ File Explorer, I checked the
Uploads
directory’s privileges by right clicking theUploads
directory to check the Properties of the directory in the File Explorer dialog box:
- The General tab indicates that the files are Read-only, as shown:
I unchecked the Read-only checkbox. Then, I retested it with the same negative results.
- Clicking the Share … button, the files in this directory are shared with Read/Write permissions to Everyone, as shown below.
- The Security tab indicates that the files Everyone has Full control of the files in this directory, as shown:
Unfortunately, with all these set appropriately the secure-file-priv
variable appears to block reading files from the designated secure directory. It appeared that I may have to remove the secure-file-priv
setting from the my.ini
file and reboot the server. Then, I found my error in the SQL LOAD
command. I wasn’t backquoting the backslashes.
The only way that the LOAD
command would work required the following steps:
- I put the
avenger.csv
file in the following directory pointed to by thesecure-file-privs
value in themy.ini
.C:\ProgramData\MySQL\MySQL Server 8.0\Uploads
- Updated the SQL
LOAD
statement to backquote the backslashes:LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\avenger.csv' INTO TABLE avenger FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n';
The
LOAD
command worked and put the CSV file contents into theavenger
table, as shown in the query results below:+------------+------------+------------+-------------------+ | avenger_id | first_name | last_name | character_name | +------------+------------+------------+-------------------+ | 1 | 'Anthony' | 'Stark' | 'Iron Man' | | 2 | 'Thor' | 'Odinson' | 'God of Thunder' | | 3 | 'Steven' | 'Rogers' | 'Captain America' | | 4 | 'Bruce' | 'Banner' | 'Hulk' | | 5 | 'Clinton' | 'Barton' | 'Hawkeye' | | 6 | 'Natasha' | 'Romanoff' | 'Black Widow' | +------------+------------+------------+-------------------+ 6 rows in set (0.0005 sec)
I got in a rush and over thought it. However, this is how you make it work. Naturally, you can point the secure-file-privs
variable to another location of your choice.
I should also note that MySQL is smart enough to change forward slashes to backslashes in the Windows OS. That means you could also use the following SQL LOAD
statement:
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/avenger.csv' INTO TABLE avenger FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '/' LINES TERMINATED BY '\r\n'; |
As always, I hope this helps those looking for a solution.
MySQL 5.7.* and mysqli
After installing MySQL 5.7.22 and PHP 7.1.17 on Fedora 27, you need to install the mysqli
library. You need to verify if the mysqli
library is installed. You can do that with the following mysqli_check.php
program:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | <html> <header> <title>Check mysqli Install</title> </header> <body> <?php if (!function_exists('mysqli_init') && !extension_loaded('mysqli')) { print 'mysqli not installed.'; } else { print 'mysqli installed.'; } ?> </script> </body> </html> |
You test preceding PHP program with the following URL in a browser:
http://localhost/mysqli_check.php |
If the mysqli
program isn’t installed, you can install it as follows by opening the yum
interactive shell:
[root@localhost html]# yum shell Last metadata expiration check: 1:26:46 ago on Wed 22 Aug 2018 08:05:50 PM MDT. > remove php-mysql No match for argument: php-mysql Error: No packages marked for removal. > install php-mysqlnd > run ================================================================================================ Package Arch Version Repository Size ================================================================================================ Installing: php-mysqlnd x86_64 7.1.20-1.fc27 updates 246 k Upgrading: php x86_64 7.1.20-1.fc27 updates 2.8 M php-cli x86_64 7.1.20-1.fc27 updates 4.2 M php-common x86_64 7.1.20-1.fc27 updates 1.0 M php-fpm x86_64 7.1.20-1.fc27 updates 1.5 M php-json x86_64 7.1.20-1.fc27 updates 73 k php-pdo x86_64 7.1.20-1.fc27 updates 138 k php-pgsql x86_64 7.1.20-1.fc27 updates 135 k Transaction Summary ================================================================================================ Install 1 Package Upgrade 7 Packages Total download size: 10 M Is this ok [y/N]: y |
After you type y and the return key, you should see a detailed log of the installation. Click the link below to see the yum
installation log detail.
Display detailed console log →
Downloading Packages: (1/8): php-pdo-7.1.20-1.fc27.x86_64.rpm 214 kB/s | 138 kB 00:00 (2/8): php-mysqlnd-7.1.20-1.fc27.x86_64.rpm 325 kB/s | 246 kB 00:00 (3/8): php-json-7.1.20-1.fc27.x86_64.rpm 342 kB/s | 73 kB 00:00 (4/8): php-pgsql-7.1.20-1.fc27.x86_64.rpm 1.1 MB/s | 135 kB 00:00 (5/8): php-common-7.1.20-1.fc27.x86_64.rpm 1.0 MB/s | 1.0 MB 00:01 (6/8): php-7.1.20-1.fc27.x86_64.rpm 3.7 MB/s | 2.8 MB 00:00 (7/8): php-fpm-7.1.20-1.fc27.x86_64.rpm 1.6 MB/s | 1.5 MB 00:00 (8/8): php-cli-7.1.20-1.fc27.x86_64.rpm 3.7 MB/s | 4.2 MB 00:01 ------------------------------------------------------------------------------------------------ Total 4.2 MB/s | 10 MB 00:02 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Running scriptlet: php-json-7.1.20-1.fc27.x86_64 1/1 Upgrading : php-json-7.1.20-1.fc27.x86_64 1/15 Upgrading : php-common-7.1.20-1.fc27.x86_64 2/15 Upgrading : php-pdo-7.1.20-1.fc27.x86_64 3/15 Upgrading : php-cli-7.1.20-1.fc27.x86_64 4/15 Upgrading : php-7.1.20-1.fc27.x86_64 5/15 Installing : php-mysqlnd-7.1.20-1.fc27.x86_64 6/15 Upgrading : php-pgsql-7.1.20-1.fc27.x86_64 7/15 Upgrading : php-fpm-7.1.20-1.fc27.x86_64 8/15 Running scriptlet: php-fpm-7.1.20-1.fc27.x86_64 8/15 Cleanup : php-7.1.17-1.fc27.x86_64 9/15 Cleanup : php-cli-7.1.17-1.fc27.x86_64 10/15 Running scriptlet: php-fpm-7.1.17-1.fc27.x86_64 11/15 Cleanup : php-fpm-7.1.17-1.fc27.x86_64 11/15 Running scriptlet: php-fpm-7.1.17-1.fc27.x86_64 11/15 Cleanup : php-pgsql-7.1.17-1.fc27.x86_64 12/15 Cleanup : php-pdo-7.1.17-1.fc27.x86_64 13/15 Cleanup : php-common-7.1.17-1.fc27.x86_64 14/15 Cleanup : php-json-7.1.17-1.fc27.x86_64 15/15 Running scriptlet: php-json-7.1.17-1.fc27.x86_64 15/15 Running as unit: run-ra7f965317617476a93de3931549ab242.service Running as unit: run-r1272914e525d42798b0c3a76d4e2ba67.service Verifying : php-mysqlnd-7.1.20-1.fc27.x86_64 1/15 Verifying : php-pdo-7.1.20-1.fc27.x86_64 2/15 Verifying : php-common-7.1.20-1.fc27.x86_64 3/15 Verifying : php-json-7.1.20-1.fc27.x86_64 4/15 Verifying : php-pgsql-7.1.20-1.fc27.x86_64 5/15 Verifying : php-fpm-7.1.20-1.fc27.x86_64 6/15 Verifying : php-cli-7.1.20-1.fc27.x86_64 7/15 Verifying : php-7.1.20-1.fc27.x86_64 8/15 Verifying : php-cli-7.1.17-1.fc27.x86_64 9/15 Verifying : php-common-7.1.17-1.fc27.x86_64 10/15 Verifying : php-fpm-7.1.17-1.fc27.x86_64 11/15 Verifying : php-json-7.1.17-1.fc27.x86_64 12/15 Verifying : php-pdo-7.1.17-1.fc27.x86_64 13/15 Verifying : php-pgsql-7.1.17-1.fc27.x86_64 14/15 Verifying : php-7.1.17-1.fc27.x86_64 15/15 Installed: php-mysqlnd.x86_64 7.1.20-1.fc27 Upgraded: php.x86_64 7.1.20-1.fc27 php-cli.x86_64 7.1.20-1.fc27 php-common.x86_64 7.1.20-1.fc27 php-fpm.x86_64 7.1.20-1.fc27 php-json.x86_64 7.1.20-1.fc27 php-pdo.x86_64 7.1.20-1.fc27 php-pgsql.x86_64 7.1.20-1.fc27 Last metadata expiration check: 2:02:29 ago on Wed 22 Aug 2018 08:05:50 PM MDT. |
After you install the mysqli
library, you exit the yum
interactive shell with the quit
command as shown:
> quit Leaving Shell The downloaded packages were saved in cache until the next successful transaction. You can remove cached packages by executing 'dnf clean packages'. |
You can now retest by re-running the mysqli_check.php program with the following URL:
http://localhost/mysqli_check.php |
Image processing is not generally installed by default. You should use the following yum
command to install the PHP Image processing library:
yum install -y php-gd |
Or, you can use dnf
(Dandified yum
), like:
dnf install -y php-gd |
Click the link below to see the yum
installation log detail.
Display detailed console log →
Dependencies resolved. ================================================================================================ Package Arch Version Repository Size ================================================================================================ Installing: php-gd x86_64 7.1.20-1.fc27 updates 89 k Transaction Summary ================================================================================================ Install 1 Package Total download size: 89 k Installed size: 200 k Is this ok [y/N]: y Downloading Packages: php-gd-7.1.20-1.fc27.x86_64.rpm 96 kB/s | 89 kB 00:00 ------------------------------------------------------------------------------------------------ Total 55 kB/s | 89 kB 00:01 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Installing : php-gd-7.1.20-1.fc27.x86_64 1/1 Verifying : php-gd-7.1.20-1.fc27.x86_64 1/1 Installed: php-gd.x86_64 7.1.20-1.fc27 Complete! |
If you encounter an error trying to render an image like this:
Call to undefined function imagecreatefromstring() in ... |
The php-gd
package is not enabled. You can verify the contents of the php-gd
package with the following rpm
command on Fedora or CentOS:
rpm -ql php-gd |
On PHP 7.1, it should return:
/etc/php-zts.d/20-gd.ini /etc/php.d/20-gd.ini /usr/lib/.build-id /usr/lib/.build-id/50 /usr/lib/.build-id/50/11f0ec947836c6b0d325084841c05255197131 /usr/lib/.build-id/b0/10bf6f48ca6c0710dcc5777c07059b2acece77 /usr/lib64/php-zts/modules/gd.so /usr/lib64/php/modules/gd.so |
Then, you might choose to follow some obsolete note from ten or more years ago to include gd.so
in your /etc/php.ini
file. That’s not necessary.
The most common reason for incurring this error is tied to migrating old PHP 5 code forward. Sometimes folks used logic like the following to print a Portable Network Graphics (png
) file stored natively in a MySQL BLOB
column:
header('Content-Type: image/x-png'); imagepng(imagecreatefromstring($image)); |
If it was stored as a Portable Network Graphics (png) file, all you needed was:
header('Content-Type: image/x-png'); print $image; |
As always, I hope this helps those looking for a solution.
MySQL on Fedora 27
While updating my class image to Fedora 27, I noticed that it installed the Akonadi Server. The documentation on the Akonadi server lacked some straightforward documentation. It also offered a bundled set of software that limited how to approach MySQL development.
So, I removed all those packages with the following syntax:
dnf remove `rpm -qa | grep akonadi` |
After removing those Akonadi packages, I installed the MySQL Community Edition from the Fedora repo with this syntax:
yum install -y community-mysql* |
Having installed MySQL Community Edition, I started the service with this command:
sudo service mysql start |
Then, I ran the mysql_secure_installation script to secure the installation:
mysql_secure_installation |
The script set the root
user’s password, remove the anonymous user, disallow remote root
login, and remove the test databases. Then, I verified connecting to the MySQL database with the following syntax:
mysql -uroot -ppassword |
I enabled the MySQL Service to start with each reboot of the Fedora instance. I used the following command:
systemctl enable mysqld.service |
It creates the following link:
ln -s '/etc/systemd/system/multi-user.target.wants/mysqld.service' '/usr/lib/systemd/system/mysqld.service' |
Now, I need to install and configure Apache, PHP, and upgrade Oracle Database 11g XE’s APEX 4 to 5.