MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘MySQL 5.7’ Category

MySQL File Privilege

without comments

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.

  1. Using the Windows’ File Explorer, I checked the Uploads directory’s privileges by right clicking the Uploads directory to check the Properties of the directory in the File Explorer dialog box:

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

  1. The Sharing tab indicates that the files are Shared, as shown:

  1. Clicking the Share … button, the files in this directory are shared with Read/Write permissions to Everyone, as shown below.

  1. 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:

  1. I put the avenger.csv file in the following directory pointed to by the secure-file-privs value in the my.ini.

    C:\ProgramData\MySQL\MySQL Server 8.0\Uploads
  2. 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 the avenger 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)
  3. 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.

Written by maclochlainn

September 26th, 2020 at 10:42 am

MySQL 5.7.* and mysqli

without comments

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.

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.

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.

Written by maclochlainn

August 23rd, 2018 at 11:47 am

MySQL on Fedora 27

without comments

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.

Written by maclochlainn

May 16th, 2018 at 2:13 am