<p>I try to deploy a MySQL service with a database named <strong>'main'</strong> with a table named <strong>'configs'</strong>. Below are my Kubernetes resources, which includes an SQL init script within a ConfigMap resource. After deploying, I use a forward port to access the MySQL pod, and communicate with the pod via the Python console using the PyMySQL library. The code I use for such communication is indicated below, where I try to insert an item into the <strong>'main.configs'</strong> table. The problem is that, I get an error from PyMySQL saying that the table 'main.configs' doesn't exist (error 1146), although the <strong>'main'</strong> database does. Note that when I use PyMySQL to create a table <strong>'configs'</strong>, it works perfectly. Something seems to prevent the the creation of <strong>'main.configs'</strong> table in my SQL init script in the ConfigMap (but the <strong>'main'</strong> database is created without any issue).</p>
<p><strong>Kubernetes resources:</strong></p>
<pre><code>apiVersion: v1
kind: Secret
metadata:
  name: mysql-secret
type: Opaque
stringData:
  mysql-root-password: abcd1234567
  mysql-user: batman
  mysql-database: main
  mysql-password: abcd1234567
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: mysql-initdb-config
data:  # store initial SQL commands
  init.sql: |
    CREATE USER 'batman'@'%' IDENTIFIED BY 'abcd1234567';
    GRANT ALL PRIVILEGES ON *.* TO 'batman'@'%';
    FLUSH PRIVILEGES;
    
    CREATE DATABASE IF NOT EXISTS main;
    USE main;
    CREATE TABLE configs(
        task_id VARCHAR(124) NOT NULL,
        PRIMARY KEY (task_id)
    ) ENGINE=InnoDB;
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mysql-pv-claim
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 100Mi
---
apiVersion: v1
kind: Service
metadata:
  name: mysql
spec:
  ports:
  - port: 3306
  selector:
    app: mysql
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql
spec:
  selector:
    matchLabels:
      app: mysql
  strategy:
    type: Recreate
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
      - image: mysql:5.6
        name: mysql
        env:
          - name: MYSQL_ROOT_PASSWORD
            valueFrom:
              secretKeyRef:
                name: mysql-secrets
                key: mysql-root-password
          - name: MYSQL_USER
            valueFrom:
              secretKeyRef:
                name: mysql-secrets
                key: mysql-user
          - name: MYSQL_DATABASE
            valueFrom:
              secretKeyRef:
                name: mysql-secrets
                key: mysql-database
          - name: MYSQL_PASSWORD
            valueFrom:
              secretKeyRef:
                name: mysql-secrets
                key: mysql-password
        ports:
          - containerPort: 3306
            name: mysql

        volumeMounts:
          - name: mysql-persistent-storage
            mountPath: /var/lib/mysql
            subPath: &quot;mysql&quot;
          - name: mysql-initdb
            mountPath: /docker-entrypoint-initdb.d  # used to configure our database

      volumes:
        - name: mysql-persistent-storage
          persistentVolumeClaim:
            claimName: mysql-pv-claim
        - name: mysql-initdb
          configMap:
            name: mysql-initdb-config
</code></pre>
<p><strong>PyMySQL code:</strong></p>
<pre><code>import pymysql

host = 'localhost'
port = 8080  # The port you forwarded with kubectl
user = 'batman'
password = 'abcd1234567'
database = 'main'

# Establish a connection
connection = pymysql.connect(
    host=host,
    port=port,
    user=user,
    password=password,
    database=database
)
cursor = connection.cursor()

query = &quot;&quot;&quot;
        INSERT INTO
          configs (task_id)
        VALUES
          ('{}')
        &quot;&quot;&quot;.format(*['id001'])
cursor.execute(query)
connection.commit()
</code></pre>

JrCaspian
<p>There is a minor issue with the Secret definition, where the secret name should match the one used in the Deployment environment variables. The actual issue seems to be in the Python code, specifically in the way you're establishing the connection and forwarding the port.</p>
<p>1 - The '<strong>secretKeyRef</strong>' in the Deployment should match the secret name defined in the <em>Secret</em>. In your '<strong>ConfigMap</strong>', you've defined the secret as '<strong>mysql-secret</strong>'. Update your Deployment script to this:</p>
<pre><code>env:
  - name: MYSQL_ROOT_PASSWORD
    valueFrom:
      secretKeyRef:
        name: mysql-secret
        key: mysql-root-password
  - name: MYSQL_USER
    valueFrom:
      secretKeyRef:
        name: mysql-secret
        key: mysql-user
  - name: MYSQL_DATABASE
    valueFrom:
      secretKeyRef:
        name: mysql-secret
        key: mysql-database
  - name: MYSQL_PASSWORD
    valueFrom:
      secretKeyRef:
        name: mysql-secret
        key: mysql-password
</code></pre>
<p>2 - In your Python code, you're using port '<strong>8080</strong>' for the MySQL connection, but in your Kubernetes resources, the MySQL service is exposed on port '<strong>3306</strong>'. Update the port variable in your Python code to match the correct port:</p>
<pre><code>port = 3306  # The port you forwarded with kubectl
</code></pre>
<p>3 - Ensure that you have properly port-forwarded the MySQL service from your local machine to the Kubernetes cluster using this command:</p>
<pre><code>kubectl port-forward service/mysql 3306:3306
</code></pre>
<p><em><strong>Make sure you are running this command while your Python code is trying to connect to MySQL.</strong></em></p>
<p>4 - After making these changes, test the connection to MySQL and table creation again. If everything is configured correctly, your Python code should be able to connect to the MySQL database and execute the SQL statements from your '<strong>init</strong>' script.</p>
