I tried to execute an SQL file in mysql. The file contained a CREATE PROCEDURE statement and the procedure contents:
DELIMITER %%
DROP PROCEDURE IF EXISTS `regmed`.`Add_Order`%%
CREATE DEFINER=`ME`@`localhost` PROCEDURE `regmed`.`Add_Order`(
p_medication VARCHAR(50), -- required, if numeric is treated as the primary key
p_who VARCHAR(10), -- required
p_order DATE, -- optional, default current date
p_receive DATE, -- optional
p_reorder DATE, -- optional
p_runout DATE)
BEGIN
DECLARE error_message varchar(200);
DECLARE new_receive DATE;
DECLARE medicationID INT;
DECLARE mnum INT;
DECLARE mperday INT;
DECLARE run DATE;
DECLARE new_run DATE;
DECLARE new_reorder DATE;
DECLARE days INT;
DECLARE a_medID INT DEFAULT null;
/* Set up error handler */
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
@sqlstate = RETURNED_SQLSTATE,
@errno = MYSQL_ERRNO,
@text = MESSAGE_TEXT;
-- SET @error_message =' ';
SET error_message = CONCAT('Error: ', @errno, ' (', @sqlstate, '): ', @text);
SELECT error_message AS 'Error';
END;
/* Enable debug */
SET @enabled = TRUE;
IF p_order IS NULL THEN
SET p_order = CURRENT_DATE();
END IF;
call debug_msg(@enabled, CONCAT( 'Order date now = ', p_order));
SET new_receive = p_receive;
IF p_receive IS NULL THEN
SET new_receive = DATE_ADD(p_order, INTERVAL 4 DAY);
END IF;
/* Get medication details */
/* The first arg may be the medID (numeric) or medication name (varchar) */
set a_medID = (case when p_medication regexp '^[0-9]+$' then cast(p_medication as unsigned integer) end);
IF a_medID IS NULL OR a_medID = 0 THEN
select id, num, perday into medicationID, mnum, mperday
from Medication where medname = p_medication and who = p_who;
ELSE
select id, num, perday into medicationID, mnum, mperday
from Medication where id = a_medID;
END IF;
/* Get previous runout_date */
select runout_date into run
from Orders
where medID = medicationID and runout_date is not null
order by runout_date desc limit 1;
IF run IS NULL THEN
SET run = p_order;
END IF;
/* Calc next dates only if not given */
IF mperday > 0 THEN
SET days = mnum / mperday;
SET new_run = p_runout;
IF p_runout IS NULL THEN
SET new_run = DATE_ADD(run, INTERVAL days DAY);
END IF;
SET new_reorder = p_reorder;
IF p_reorder IS NULL THEN
SET new_reorder = DATE_SUB(new_run, INTERVAL 14 DAY);
END IF;
END IF;
/* Insert new row */
insert into Orders
(medID, order_date, reorder_date, receive_date, runout_date)
values (medicationID, p_order, new_reorder, new_receive, new_run);
END%%
DELIMITER ;
mysql> source /home/MY_LOGIN_NAME/Documents/MORE_DIRECTORIES/MY_SPL_NAME.sql;
mysql failed with the error:
Failed to open file '/home/MY_LOGIN_NAME/Documents/MORE_DIRECTORIES/MY_SPL_NAME.sql', error 2
I know that errno 2 is ‘file not found’, so I checked the spelling of the path carefully and found no errors. I copy-&-pasted the path into an ls command to prove that the file did, in fact, exist:
ls -l /home/MY_LOGIN_NAME/Documents/MORE_DIRECTORIES/MY_SPL_NAME.sql
The file DOES exist. The permissions were good (664). The only thing I could think of was that the path was too long, at 81 characters.
Repetitively, I moved the file up a level and retried, until it worked. When it worked, it showed there was nothing wrong with my file, or its permissions.
I measured the path lengths by copying-&-pasting into another window, with wc -c
echo "/home/MY_LOGIN_NAME/Documents/MORE_DIRECTORIES/MY_SPL_NAME.sql" | wc -c
and found:
First attempt: 81 characters
Last failure: 56 characters
Worked: 47 characters
Is there any restriction on the length of mysql commands?
Linux Mint 22.3 with bash 5.2.21(1)-release
mysql Version 8.4.13 for Linux on x86_64 (MySQL Community Server – GPL) (brand new, installed today).
Answers to early comments
One directory has a space, and that was my first suspicion. Even when that component had been eliminated, it still failed.
There are no links in this path.
