dongyan5706 2019-01-08 11:27
浏览 102

从PHP脚本调用mySQL存储过程 - 没有错误但没有执行?

  • UPDATE AT THE END

I'm following this Automatic Partition Maintenance in MySQL tutorial, which details a generic method for removing and adding mySQL table partitions based on date ranges.

The idea is that you can jettison older table data automatically after a certain length of time, and create new table partitions for current data as needed.

However, since my site will likely be hosted on a "shared" provider package, it seems likely that mySQL events will be unavailable to me.

So I'm cross fertilizing the Stored Procedures described in the first tutorial, with an alternative method of invoking them using the method detailed in this Stack Overflow answer, with some modifications: Partition maintainance script for Mysql

On my local test machine, I want to run the PHP script as a CRON job from Webmin.

When I run the Stored Procedures from Adminer (which has similar functionality to phpMyAdmin), using the mySQL test database, they execute as expected - partitions are deleted, and the whole process takes a couple of minutes to complete.

However, when I run my modified PHP script from Webmin as a CRON job, nothing seems to happen. There are no errors, but the script returns immediately with "OK".

Similarly, when I run the script from my LAMP machine's shell, it immediately returns with "OK".

This is the PHP script:

#!/usr/bin/env php
<?php
$connection = mysqli_connect('localhost', 'my_username', 'my_password', 'employees');
$result = mysqli_query($connection, "CALL perform_partition_maintenance('employees', 'titles', 3, 216, 5)") or die('Query fail: ' . mysqli_error($connection));
if ($result)
  echo "OK";
else
  echo "FAIL";
mysqli_close($connection);

I'd be very grateful for any suggestions about where I might be going wrong.


UPDATE

In line with Nick's suggestion, I've been adding a lot of debug statements. I went a slightly different route because it was a bit easier to do - lots of new "into outfile" statements.

But what I've observed has baffled me. A small segment of the Stored Procedure is below:

OPEN cur1;
    read_loop: LOOP
      FETCH cur1 INTO current_partition_name;
      IF done THEN
         LEAVE read_loop;
      END IF;

      IF ! @first AND p_seconds_to_sleep > 0 THEN
        SELECT CONCAT('Sleeping for ', p_seconds_to_sleep, ' seconds');
        SELECT SLEEP(p_seconds_to_sleep);
      END IF;

      SELECT CONCAT('Dropping partition: ', current_partition_name);

      ...

      SET @first = FALSE;
    END LOOP;
  CLOSE cur1;

This is all taken, unmodified, from the web tutorial at Geoff Montee's page, and works flawlessly in other contexts (i.e., within Adminer, from the sql console - just not in combination with a PHP script). However, when I comment out the line that says:

SELECT CONCAT('Dropping partition: ', current_partition_name);

Everything works just fine, but the script chokes when I put that line back in. I can't make any sense of this. Particularly since - in testing - I'm writing out "current_partition_name" into a file on disk for the first three iterations of the loop, and referencing the string in that situation doesn't cause any issues. It's very odd.

This other (apparently unresolved) stackoverflow question sounds somewhat similar.

  • 写回答

1条回答 默认 最新

  • duanchui1955 2019-01-30 01:56
    关注

    Belatedly, I've become aware that partitioning is not available when a table has foreign keys. I'm not sure how I missed this fundamental detail when I was first exploring partitioning as an option.

    It's very unfortunate because it renders the entire exercise redundant. I'll have to investigate some sort of a solution involving conventional table deletes, with all of the associated headaches.


    Separately, I'm not closer to understanding why commenting out that particular line from Geoff Montee's Stored Procedure was pivotal in allowing the function to run successfully when invoked from PHP. I'd be tempted to put it down to an interpreter bug (I'm running mySQL 5.5.62 in my test environment), but as mentioned previously, the Stored Procedure executes flawlessly when initiated from Adminer.

    评论

报告相同问题?

悬赏问题

  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)
  • ¥20 怎么在stm32门禁成品上增加查询记录功能