小飞鱼通达二开 处理PDO连接数据库时的Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]报错

之前在小飞鱼本机上开发的程序,使用了PDO方式连接mysql数据库,今天在部署到服务器上时居然不显示数据,查找日志文件出现下面的信息:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared

开始以为是这个机器上的PDO没有配置开放,试着调了下php.ini文件中的pdo选项,重启服务后问题没有得到解决。

期间试了一下其他页面的程序,发现有的页面上时可以直接显示数据的,唯独这个页面有问题,而这个页面里用到了分页、视图这些,经过进一步查找资料,发现是视图里使用了左连接,查询mysql的cache参数:

SHOW VARIABLES LIKE '%table_open_cache%';
SHOW VARIABLES LIKE '%table_definition_cache%';

发现有一个cache设置的比较小,调整一下:

SET GLOBAL table_open_cache=16384;
SET GLOBAL table_definition_cache=16384;

执行查询后,数据可以正常显示了。

发布了348 篇原创文章 · 获赞 112 · 访问量 126万+
展开阅读全文

如何修复PHP中的“Uncaught PDOException:SQLSTATE [22007]”致命错误

04-28

<div class="post-text" itemprop="text"> <p>I have problem with add new data to database using PDO. I have a form where there are several fields. If I leave an empty field then an error pops up. The problem occurred when mysql was updated on the server. On another server and locally the problem does not appear. Setting the server in all forms to default values of 0 in the fields will be very difficult.</p> <p>Database version: 10.3.13-MariaDB-100.cba.1-log - Debian testing</p> <p>Please, check if you do not complete all the fields and generate an error.</p> <p>Is it ok when I leave empty input then error generated?</p> <p>PHP Code:</p> <pre><code>$config = require_once 'config.php'; try { $db = new PDO( "mysql:host={$config['host']};dbname={$config['database']};charset=utf8", $config['user'],$config['password'], [PDO::ATTR_EMULATE_PREPARES=>false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]); } catch(PDOException $error) { echo $error->getMessage(); exit('Database error!'); } if (isset($_POST['dodaj'])) { $pole1 = $_POST['pole1']; $pole2 = $_POST['pole2']; $pole3 = $_POST['pole3']; $pole4 = $_POST['pole4']; $query = $db->prepare("INSERT into pola VALUES (:pole1, :pole2, :pole3, :pole4)"); $query->bindValue(':pole1', $pole1, PDO::PARAM_INT); $query->bindValue(':pole2', $pole2, PDO::PARAM_INT); $query->bindValue(':pole3', $pole3, PDO::PARAM_INT); $query->bindValue(':pole4', $pole4, PDO::PARAM_INT); $query->execute(); } </code></pre> <p>HTML code:</p> <pre><code><form method="post" action="#"> <input type="text" id="pole1" name="pole1"> <input type="text" id="pole2" name="pole2"> <input type="text" id="pole3" name="pole3"> <input type="text" id="pole4" name="pole4"> <input type="submit" name="dodaj" value="dodaj"> </form> </code></pre> <p>Database:</p> <p>pole1 | pole2 | pole3 | pole4</p> <blockquote> <p>Fatal error: Uncaught PDOException: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: '' for column <code>********</code>.<code>pola</code>.<code>pole3</code> at row 1 in ************************new 1.php:18 Stack trace: #0 ********************new 1.php(18): PDOStatement->execute() #1 {main} thrown in ******************************new 1.php on line 18</p> </blockquote> </div> 问答

使用PDO和Ubuntu无法正确处理TCP连接

09-21

<div class="post-text" itemprop="text"> <p>Let me briefly explain you my problem.</p> <p>The architecture(for the problem) is as follow:</p> <ul> <li>1 Ubuntu 16.04 with Apache 2.4 server; php-fpm 7.2 and Laravel</li> <li>1 Ubuntu 16.04 with MariaDB v.15.2 distrib 10.2.14 server</li> </ul> <p>The problem that started from couple of days is that web server is throwing this error:</p> <blockquote> <p>SQLSTATE[HY000] [2002] Cannot assign requested address</p> </blockquote> <p>What I've found so far:</p> <p>On MariaDB</p> <ul> <li>~30-40 concurrent connections <blockquote> <p>netstat -nat | awk '{print $6}' | sort | uniq -c | sort -n</p> </blockquote></li> <li>shows average ~800-900 connections in <strong>TIME_WAIT</strong> status</li> </ul> <p>So far everything seems legit. But on Apache server the findings are as follow:</p> <blockquote> <p>netstat -nat | awk '{print $6}' | sort | uniq -c | sort -n</p> </blockquote> <ul> <li>~10-12k connections in TIME_WAIT status</li> </ul> <p>When they get > 28k I receive the error mentioned above. That's because the server has ~28k free ports. (range 32768 - 61000). I've changed the range to 10000 - 63000 yesterday and I've changed waiting in TIME_WAIT status to 5 seconds,so far the error stopped. But this is not <strong>real solution</strong> of the problem.</p> <p>Questions that I have in mind are:</p> <ol> <li><p>Does php PDO start DB connection for every user and open TCP connection for every query to DB and reuse created DB connection? If this is true, is it possible to fix this without additional web server ?</p></li> <li><p>Why and how there is such big difference between the connections in TIME_WAIT status on Apache and MariaDB ? Is it a PDO problem that is not closing connections properly ?</p></li> </ol> <p>Thanks in advance.</p> <p>P.S.: The connections to MariaDB are not persistent</p> </div> 问答

Docker,PHP PDO和单个MySQL容器(无法通过容器名称连接

04-12

<div class="post-text" itemprop="text"> <p>I've run into an interesting issue where there seems to be a difference between the <code>docker run</code> command and using <code>docker-compose</code>.</p> <p>I have two Docker containers, one is an Apache website with PHP, the other a MySQL. I use the following commands to run the containers:</p> <p>Website:</p> <pre><code>docker run -p 8080:80 -d website_local </code></pre> <p>MySQL: </p> <pre><code>docker run -e MYSQL_ROOT_PASSWORD=RamaLamaDingDong --name=mysql5725 -d mysql:5.7.25 </code></pre> <p>Looking at the Docker ecosystem I can see them both running:</p> <pre><code>$ docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 849c421e750b website_local "/usr/sbin/apache2ct…" 19 minutes ago Up 19 minutes 0.0.0.0:8080->80/tcp pedantic_tesla ccebba95693b mysql:5.7.25 "docker-entrypoint.s…" 23 minutes ago Up 23 minutes 3306/tcp, 33060/tcp mysql5725 </code></pre> <p>I have setup a PDO connection to the database, using the name for the MySQL Docker container:</p> <p>PHP: (the <code>USER</code> and <code>PASS</code> are correct and yes, I know I shouldn't use the root's credentials)</p> <pre><code>try { $dbh = new PDO('mysql:host=mysql5725;dbname=grocery;charset=utf8', USER, PASS); $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch(PDOException $e) { echo $e->getMessage(); $errorCode = $e->getCode(); } </code></pre> <p>When attempting to connect to the database I get the following error:</p> <blockquote> <p>SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: Name or service not known</p> </blockquote> <p>If I replace <code>host</code> in the connections string with the Docker IP address (172.17.0.3) of the container it connects properly.</p> <p>Now it gets interesting. If I bring up the containers using Docker Compose:</p> <pre><code>version: '3' services: db: image: mysql:5.7.25 container_name: mysql5725 environment: MYSQL_ROOT_PASSWORD: RamaLamaDingDong ports: - "3306:3306" web: image: website_local:latest container_name: website_local depends_on: - db volumes: - ./website/www:/var/www/html/ ports: - "8080:80" </code></pre> <p>The PHP function connects correctly using the name of the Docker container (<code>mysql5725</code>), but cannot connect using the IP address of the Docker container.</p> <p>Many times in a testing environment I only want to stop and rebuild certain containers, especially when dealing with more than two Docker images. I should only have to use the resource's name when making a connection because there is no guarantee the network will assign the same IP addresses to containers every time.</p> <p>Why should these two methods of launching Docker containers produce different results? Is there a way to 'normalize' this, so connecting by name will work no matter how the container is launched?</p> </div> 问答

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 精致技术 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览