0

So I've created a few functions who's purpose is to get the total number of tickets for each order, which may be spread about multiple rows. I am pretty sure I used BEGIN, END and DELIMITERS correctly which was the fixes of other questions on this site. However when i run this SQL query, i get this:

Error
Static analysis:

1 errors were found during analysis.

Missing expression. (near "ON" at position 25)
SQL query: Copy Edit Edit

SET FOREIGN_KEY_CHECKS = ON;

MySQL said: Documentation

#2014 - Commands out of sync; you can't run this command now

Here is my query for reference

DROP PROCEDURE IF EXISTS getTotalTicketsPerOrder;
DROP PROCEDURE IF EXISTS getTotalTicketsBoughtPerOrder;
DELIMITER //
CREATE PROCEDURE getTotalTicketsPerOrder(
    IN pOrderID INT(11),
    OUT pTotalTickets INT(11)
)
BEGIN
    SELECT SUM(ticketsinorder.NoOfTicketsBought)
    INTO pTotalTickets
FROM
    ticketsinorder
WHERE
    ticketsinorder.OrderID = pOrderID ;
END //
DELIMITER ;
DELIMITER //
CREATE PROCEDURE getTotalTicketsBoughtPerOrder(IN idCount INT(11))
BEGIN
    DECLARE counter INT DEFAULT 1;
    WHILE counter <= idCount DO
CALL getTotalTicketsPerOrder(counter, @totalTicketsBought) ;
SELECT @totalTicketsBought ;
SET counter = counter + 1 ;
END WHILE ;
END //
DELIMITER ;
SELECT @idCount := COUNT(orders.OrderID)
FROM orders;
CALL getTotalTicketsBoughtPerOrder(@idCount);

Since I have a feeling some of you will ask, here's the table in question Table Photo

EDIT: I have found the source of the problem (kinda)

So i found that the error message only sends when i include the last line ( CALL getTotalTicketsBoughtPerOrder(@idCount); )

UPDATE: Included error logs

2022-08-22 20:10:26 0 [Note] mysqld.exe: Aria engine: recovery done
2022-08-22 20:10:26 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2022-08-22 20:10:26 0 [Note] InnoDB: Uses event mutexes
2022-08-22 20:10:26 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-08-22 20:10:26 0 [Note] InnoDB: Number of pools: 1
2022-08-22 20:10:26 0 [Note] InnoDB: Using SSE2 crc32 instructions
2022-08-22 20:10:26 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2022-08-22 20:10:26 0 [Note] InnoDB: Completed initialization of buffer pool
2022-08-22 20:10:26 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=2084794
2022-08-22 20:10:26 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2022-08-22 20:10:26 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2022-08-22 20:10:26 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2022-08-22 20:10:26 0 [Note] InnoDB: Setting file 'C:\xampp\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-08-22 20:10:26 0 [Note] InnoDB: File 'C:\xampp\mysql\data\ibtmp1' size is now 12 MB.
2022-08-22 20:10:26 0 [Note] InnoDB: Waiting for purge to start
2022-08-22 20:10:26 0 [Note] InnoDB: 10.4.22 started; log sequence number 2084803; transaction id 4233
2022-08-22 20:10:26 0 [Note] InnoDB: Loading buffer pool(s) from C:\xampp\mysql\data\ib_buffer_pool
2022-08-22 20:10:26 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-08-22 20:10:26 0 [Note] InnoDB: Buffer pool(s) load completed at 220822 20:10:26
2022-08-22 20:10:26 0 [Note] Server socket created on IP: '::'.

UPDATE 2: Specific location of error found. So i did a little digging and realised the cause of the problem was the select statement within the while loop. SELECT @totalTicketsBought ; I'm not sure why that is a problem but it indeed is.

Gavindu
  • 1
  • 2
  • picture aren't allowed see https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question – nbk Aug 21 '22 at 20:06
  • Ah I see, how would I go about adding a table to the post then since I didnt see a table option for formatting? – Gavindu Aug 21 '22 at 20:10
  • i made a fiddle https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=7f15d7752eb96eb85d24de5f58f4873a where your code runs, look into the mysql error log and see if you didn't run out on mempry or space – nbk Aug 21 '22 at 20:12
  • oh thank you! Quick question, where would I access this error log? You see i already changed some of the values in the php.ini file to allow for more storage space (followed these instructions: https://stackoverflow.com/questions/33974492/error-near-on-at-position-25-while-importing-table-for-wordpress-a-foreign ) – Gavindu Aug 21 '22 at 20:19
  • that depends on the os you use, see irf you find the mysql.ini or cnf there must be the location – nbk Aug 21 '22 at 20:30
  • Sorry im pretty new to all of this so i dont quite know what you mean. I'm on windows, but i searched for mysql.ini and that didn't exist and I dont quite know what you mean by cnf @nbk – Gavindu Aug 21 '22 at 20:42
  • 1
    ok mysql "hides" on windows `C:\ProgramData\MySQL\MySQL Server 8.0` there you find an ini file with the configuration in the data folder you find an err file there you find if mysql doesn't like anything. With the error there , you can search for a clue – nbk Aug 21 '22 at 20:57
  • Yeah im really sorry but none of that returned anything useful – Gavindu Aug 22 '22 at 19:21
  • What was in the error log? An update of your question will be good – nbk Aug 22 '22 at 19:24
  • Posted extra error logs and also extra update on where the problem is @nbk – Gavindu Aug 22 '22 at 20:05
  • yes no mysql error, but as i am seeing your error code, the os no ON in your procedure, so your error can't be related to your code – nbk Aug 22 '22 at 20:10
  • Yeah that's whats confusing me the most, because I did the recommended instructions to avoid this, but the problem still exists. Also what the problem is is the select function in the while loop, but thats necessary. – Gavindu Aug 22 '22 at 20:12
  • this is then not the code that runs, have you multiple server – nbk Aug 22 '22 at 20:16
  • at this stage i recommend to enable general log for a short while only, as it consumes lot of space an check which commands are run – nbk Aug 22 '22 at 20:25
  • Nope this is the only server I have. How would I go about enabling general logs? – Gavindu Aug 22 '22 at 20:26
  • please use a search engine https://stackoverflow.com/questions/6479107/how-to-enable-mysql-query-log – nbk Aug 22 '22 at 20:28

0 Answers0