How can I get LOAD DATA LOCAL INFILE to work for MySQL 8.0.21 on a Windows machine?

I’ve created a file: 'C:\fff.txt' which contains data for my database table. Then I’m trying to load data from that file like this:

LOAD DATA LOCAL INFILE 'C:\fff.txt' INTO TABLE fff LINES TERMINATED BY '\r\n';  

but I get the following error all the time:

ERROR 2 <HY000>: File 'C:\fff.txt' not found (OS errno 2 - No such file or directory) 

I’ve added local_infile=1 to [mysql] and [mysqld] sections of my.ini file. When I type: SHOW GLOBAL VARIABLES LIKE 'local_infile'; I can see that it’s set to 'ON'. Is there a way to solve this problem?

MYSQL TIMESTAMP when adding DATE_FORMAT then the output is blank, PHP conflict?

Usingthe following shortcode function, I have the code building a table by querying the DB. That all works fine. BUT when I add any type of formatting to the TimeStamp column such as DATE_FORMAT or TIME the output for that column only goes blank. No errors are in the console to look at. I am trying to extract only the time and put it in a 12 hour format instead of the typical TIMESTAMP format of date and time in the 24hr set up.

Is there a conflict with some of the PHP that causes it not to display the timestamp?

 function test2() {         global $  wpdb;     $  results = $  wpdb->get_results("SELECT `name`, `partysize`, `phonenumber`, `emailaddress`, DATE_FORMAT(`Time_stamp`, '%h %i' ), `Wait`, `currentstatus` FROM mytablename m RIGHT JOIN (SELECT wdt_ID, CONCAT(ROUND(Time_to_sec(TIMEDIFF (NOW() ,`Time_stamp`))/60,0), ' Min') AS Wait FROM mytablename) as t on m.wdt_ID = t.wdt_ID WHERE Time_stamp >= date_sub(now(),interval 3 hour)  ");          if(!empty($  results))         {             echo "<table width='100%' border='0' style='display:inline-table'>             <th>Name</th>             <th>Party Size</th>             <th>Phone Number</th>             <th>Email Address</th>             <th>Time Stamp</th>             <th>Status</th>             <th>Wait</th>             <th>Action</th>     ";             echo "<tbody>";                   foreach($  results as $  row){                  echo "<tr>";                 //echo "<td>" . $  row->wdt_ID . "</td>";                 echo "<td>" . $  row->name . "</td>";                 echo "<td>" . $  row->partysize . "</td>";                 echo "<td>" . $  row->phonenumber . "</td>";                 echo "<td>" . $  row->emailaddress . "</td>";                 echo "<td>" . $  row->Time_stamp . "</td>";                 echo "<td id='tdid_".$  row->wdt_ID."'>" . $  row->currentstatus . "</td>";                 echo "<td>" . $  row->Wait . "</td>";                 echo '<td> <a href="javascript:void(0)" class="button seated-btn" onclick="seatclick('.$  row->wdt_ID.')" data-id="'.$  row->wdt_ID.'"></a></td>';                 echo "</tr>";             }             echo "</tbody>";             echo "</table>";          }     }     add_shortcode('test2', 'test2'); 

Near identical MySQL deployments behaving very different – High CPU Usage problem

So I have five identical websites, running on five machines provisioned in the same way. The only thing that differs between these installations are the language files and the languages of the text stored in MySQL tables.

Four of them have no problems what so ever. One is struggling a LOT under the same or somewhat less load than the other four.

I cannot understand why this is.

Things I’ve done so far:

  1. Checked slow queries. All queries uses indexes and are in the realm of 0.0008 Sec execution time i.e. very fast
  2. I’ve noticed that the thing that causes most trouble for this MySQL instance is UPDATE and INSERT, so much so, I’ve turned off the UPDATE’s that were there, for this instance. Bear in mind that these UPDATE’s doesn’t cause a blip on the other servers.
  3. Tried to eliminate external factors i.e. noisy neighbours (moved host) etc.

Worth noticing is that the machines are deployed the same i.e. a vanilla Debian 10 installation with a LEMP stack, nothing out of the ordinary at all.

Still, the problem persists. I can see the load of the machine struggling to keep under 1.00. The other machines are in the 0.10 – 0.20 range all the time.

Looking at CPU for the MySQL process on this machine (with 2 CPU cores as the other machines have as well) it is quite often above 100%. The other machines are never – EVER – over 60% for the MySQL process.

So, any help is much appreciated.

Please do let me know if you need me to run a command that you need to see the output from in order to help.

Thanks.

EDIT Spelling and clarifications

Mysql says it failed to start but is up anyway?

I’me running MySQL 8 under Ubuntu 20.04 WSL2

I’m starting MySQL with sudo service mysql restart

Output is:

* Stopping MySQL database server mysqld      [ok] * Starting MySQL database server mysqld      [fail] 

But when I now run sudo service mysql status, I get:

 * /usr/bin/mysqladmin  Ver 8.0.21-0ubuntu0.20.04.4 for Linux on x86_64 ((Ubuntu)) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.  Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.  Server version          8.0.21-0ubuntu0.20.04.4 Protocol version        10 Connection              Localhost via UNIX socket UNIX socket             /var/run/mysqld/mysqld.sock Uptime:                 45 sec  Threads: 2  Questions: 4  Slow queries: 0  Opens: 430  Flush tables: 3  Open tables: 27  Queries per second avg: 0.088 

Error log is:

2020-09-11T22:15:33.048074Z 11 [System] [MY-013172] [Server] Received SHUTDOWN from user debian-sys-maint. Shutting down mysqld (Version: 8.0.21-0ubuntu0.20.04.4). 2020-09-11T22:15:33.136150Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21-0ubuntu0.20.04.4)  (Ubuntu). 2020-09-11T22:15:33.231510Z mysqld_safe mysqld from pid file /var/lib/mysql/LAPTOP-77E86FL8.pid ended 2020-09-11T22:15:34.784720Z mysqld_safe Logging to '/var/log/mysql/error.log'. 2020-09-11T22:15:35.002180Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 2020-09-11T22:15:35.073728Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8161) 2020-09-11T22:15:35.073742Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 4000) 2020-09-11T22:15:35.392127Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.21-0ubuntu0.20.04.4) starting as process 6495 2020-09-11T22:15:35.412902Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2020-09-11T22:15:35.992356Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2020-09-11T22:15:36.261631Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock 2020-09-11T22:15:36.404215Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2020-09-11T22:15:36.404659Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. 2020-09-11T22:15:36.456568Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.21-0ubuntu0.20.04.4'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu). 

My issue is that sudo service mysql restart returns a non-zero status code.

What may I check or look for?

Thank you!

Sync MySQL schemas bettwen diferent databases

I have a couple of databases that have the exact same structure than my "master" database, but after a couple of months, the developers did small changes (like, add another collum, change a datatype to fix an error, etc) and they forgot to replicate it overall databases.

After some research, I found out that mysqldiff could generate the queries to update the databases left behind. But since I am using MySQL 8, it doesn’t work (ERROR: Query failed. 1146 (42S02): Table ‘mysql.proc’ doesn’t exist), and the project (mysql-utilities) looks like got abandoned (the last commit is from 2016)…

Does anyone know a free alternative for mysqldiff? I just need to sync the databases, not the data. I am asking because most questions that I looked up where a little bit old.

Mysql server issue after cPanel WHM migration

I have a very serious issue. No online suggestions are not working. I have upgraded by cPanel/ WHM in Godaddy VPS and after that MySQL is not working. Following is the error message.

ERROR! MySQL server PID file could not be found! Starting MySQL../usr/bin/mysqld_safe: line 183: 4266 Killed nohup /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –plugin-dir=/usr/lib64/mysql/plugin –user=mysql –log-error=ip-192-169-216-254.secureserver.net.err –open-files-limit=11000 –pid-file=/var/lib/mysql/ip-192-169-216-254.secureserver.net.pid < /dev/null > /dev/null 2>&1 ERROR! The server quit without updating PID file (/var/lib/mysql/ip-192-169-216-254.secureserver.net.pid).

I know there are lots of suggestion on the net. But none of them are working.

Turning columns result set into independant rows in MySQL

I am pretty new to this and have been struggling over one issue.

I have a result set which for each Opportunity in a table (id as primary key), provides a date for a first cashflow (DFCFW) as a column and the 10 following columns (CFW1, CFW2, …., CFW10) being the 10 possible cashflows for each of the 10 following years, expected as anniversary dates of the first cashflow.

I would like to create a view which displays, for all the opportunities, three columns: opportunity.id, date of the cashflow, cashflow; there should be 10 records for each opportunity.

Any suggestion how to achieve this ?

Thank you so much

Fred

MySQL performance issue with ST_Contains not using spatial index

We are having what seems to be a fairly large mysql performance issue on trying to run a fairly simple update statement. We have a table(1.8mil) with houses that contains a Lat+Long geometry point column(geo), and then a table(6k) that has a list of schools with a boundary geometry polygon column(boundary). We have spatial indexes on both, we are trying to set the school’s id, that contains the point, to the house table with the update. The update is taking 1 hour and 47 minutes to update 1.6mil records. In other systems I have used in my paste experience, something like that would take just a few minutes. Any recommendations?

I have posted this same question in the GIS SE site as well, as it is very much a GIS & DBA question.

CREATE TABLE houses (   ID int PRIMARY KEY NOT NULL,   Latitude float DEFAULT NULL,   Longitude float DEFAULT NULL,   geo point GENERATED ALWAYS AS (st_srid(point(ifnull(`Longitude`,0),ifnull(`Latitude`, 0)),4326)) STORED NOT NULL,   SPATIAL INDEX spidx_houses(geo) ) ENGINE = INNODB, CHARACTER SET utf8mb4, COLLATE utf8mb4_0900_ai_ci;  CREATE TABLE schoolBound (   ID int PRIMARY KEY NOT NULL,   BOUNDARY GEOMETRY NOT NULL,   reference VARCHAR(200) DEFAULT NULL,   type bigint DEFAULT NULL,   INDEX idx_reference(reference),   INDEX idx_type(type),   SPATIAL INDEX spidx_schoolBound(BOUNDARY) ) ENGINE = INNODB, CHARACTER SET utf8mb4, COLLATE utf8mb4_0900_ai_ci;  
-- type 4 means it's a elementary Update houses hs     INNER JOIN schoolBound AS sb ON ST_Contains(sb.boundary, hs.geo) AND sb.type = 4 SET hs.elementary_nces_code = sb.reference 

The explain seems to show that it is not going to use the spatial index for schoolBound.

+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                                          | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+------------------------------------------------+ |  1 | SIMPLE      | sb    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6078 |    10.00 | Using where                                    | |  1 | UPDATE      | hs    | NULL       | ALL  | spidx_houses  | NULL | NULL    | NULL | 1856567 |   100.00 | Range checked for each record (index map: 0x4) | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+------------------------------------------------+ 

MySQL password rotation: Using a single user to change other user passwords

I’m currently working on setting up a password rotation strategy for an AWS Aurora/MySQL based application.

My plan was to use a strategy like this…

  • Application usernames/passwords stored in AWS SSM encrypted parameters.
  • Application servers have access to retrieve only their credentials from SSM. Restricted by environment (staging, production etc.)
  • Lambda configured to run periodically to change passwords in MySQL and store the new values in SSM. Lambda to authenticate with the database using AWS IAM roles, rather than using a password.

The last bit is the bit I’m not sure about. This configuration would require the lambda role/user to have permission to change the passwords for all of the other application users.

Is this a reasonable way to do it, from a security perspective? Since the lambda mysql user will use an IAM role rather than a password, this should retrict it’s use to only authorised roles.

The alternative would be to not have a special db user for the lambda to login, but rather to have the lambda function retreive each users credentials from SSM, and then login as each user to change it’s password.

Either way the lambda is going to need to have access to each user.

Assuming I can carefully retrieve access to the "lambda_user" in MySQL, are there any other glaring issues with having a user have authority to change other users passwords?

Also, just to clarify, these are application users, not regular human type users who will be using these credentials.