Android Programming – How to Solve MySQLInterfaceError (Python, MySQL, Ubuntu)

When it came to developing an Android app with the support of a LAMP server, I required a substantial amount of data in MySQL to conduct numerous intriguing experiments, employing Python scripts. What’s captivating about creating a Linux server and utilizing it as a backend system to drive an Android app is that it not only facilitates my learning of Kotlin but also offers ample opportunities to enhance my Linux administration skills and gain a deeper understanding of MySQL database mechanisms.

This is a Python script I made that allows me to insert a large amount of data from a CSV file into the database.

import csv
import mysql.connector

# Set the following variables to match your environment
MYSQL_USER = "user_name"
MYSQL_PASSWORD = "password"
MYSQL_DATABASE = "db_name"
MYSQL_TABLE = "user_spending"
CSV_FILE = "/var/lib/mysql-files/demo.csv"

# Connect to MySQL
conn = mysql.connector.connect(
    user=MYSQL_USER,
    password=MYSQL_PASSWORD,
    database=MYSQL_DATABASE,
)
cursor = conn.cursor()

# Create the LOAD DATA INFILE statement
LOAD_DATA_INFILE_STATEMENT = f"LOAD DATA INFILE '{CSV_FILE}' REPLACE INTO TABLE {MYSQL_TABLE} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'"


# Execute the LOAD DATA INFILE statement
cursor.execute(LOAD_DATA_INFILE_STATEMENT)

# Commit the changes
conn.commit()

# Close the cursor and connection
cursor.close()
conn.close()

When I execute the above Python script, I’ve got this error:

Traceback (most recent call last):
  File "/home/user_name/.local/lib/python3.10/site-packages/mysql/connector/connection_cext.py", line 633, in cmd_query
    self._cmysql.query(
_mysql_connector.MySQLInterfaceError: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Solution:

Move the CSV file to a permitted directory: The --secure-file-priv option restricts file operations to a specific directory. You can move your CSV file to a directory allowed by this option, typically located in the MySQL data directory. You can check the current value of --secure-file-priv using the MySQL command SHOW VARIABLES LIKE 'secure_file_priv';.

And when I run the below command, this is the directory, I’ve got.

SHOW VARIABLES LIKE 'secure_file_priv';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

sudo and cd

So, to take a look at what’s inside of the mysql-files directory, I run the below command, but nothing happened… why???

sudo -s cd /var/lib/mysql-files/

The reason nothing happens when you run sudo -s cd /var/lib/mysql-files/ is that the cd command is executed in a subshell created by sudo -s, and once that subshell exits, the current directory reverts to what it was before.

To change to the /var/lib/mysql-files/ directory with elevated privileges, you should use the cd command directly in the elevated shell session created by sudo -s:

sudo -s
cd /var/lib/mysql-files/

This sequence of commands will open a new shell session with superuser privileges and then change the directory to /var/lib/mysql-files/ within that shell session.

Remember to use exit to exit the elevated shell session once you’re done working in that directory.

Afterthoughts:

Then, when I moved the CSV file to the mysql-files and executed the Python script, the MySQLInterfaceError magically disappeared.

Ever since I started building the Android app, it has evolved into something more than just Kotlin and Android. Instead, it encompasses a wide range of practical skills that hold significant value in the real world. This experience is all about continuous daily learning and growth.

Keep learning, keep smiling. Happy coding!!