I recently upgraded this tech blog from PHP 5.6 to 7.0 and stumbled (again) across some old mysql* functions. These were removed in PHP 7.0 and needed to be replaced by either PDO or MySQLi (see Changing from PHP's mysql to myqli - what to look at).
While I fixed most of the code, I forgot the admin part of my blog. Before a new article is inserted into the database, the content/text runs through a function to escape special characters: mysqli_real_escape_string(). From the documentation:
"Escapes special characters in a string for use in an SQL statement, taking into account the current charset of the connection"
Old mysql_real_escape_string allowed to simply use a single variable:
# OLD PHP < 7
$iContent = mysql_real_escape_string($iContent);
But (almost all) mysqli functions require the mysqli connection variable (here $connect), too:
# NEW PHP >= 7
$iContent = mysqli_real_escape_string($connect, $iContent);
Long story short: The content was not inserted into the database and once I fixed the code, I had to delete my prior attempts in the table and manually update the article ID to not leave a gap in between articles. This had a negative impact on the table's auto increment counter.
To better show that, I retrieve the latest article ID:
MariaDB [claudiokuenzler]> select newsid from news order by newsid desc limit 0,1;
+--------+
| newsid |
+--------+
| 790 |
+--------+
1 row in set (0.00 sec)
Yet the auto increment counter was already at 793 for the next insert (I manually deleted 2 entries):
MariaDB [(claudiokuenzler)]> SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'claudiokuenzler' AND TABLE_NAME = 'news';
+----------------+
| AUTO_INCREMENT |
+----------------+
| 793 |
+----------------+
1 row in set (0.00 sec)
Of course I wanted to fix this immediately and luckily I came across this stackoverflow question where user Anshul gave a very good and quick explanation:
Further, in order to reset the AUTO_INCREMENT count, you can immediately issue the following statement.
ALTER TABLE `users` AUTO_INCREMENT = 1;
For MySQLs it will reset the value to MAX(id) + 1.
So I did that:
MariaDB [claudiokuenzler]> ALTER TABLE news AUTO_INCREMENT = 1;
Query OK, 788 rows affected (0.01 sec)
Records: 788 Duplicates: 0 Warnings: 0
And how did this affect the increment counter?
MariaDB [claudiokuenzler]> SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'claudiokuenzler' AND TABLE_NAME = 'news';
+----------------+
| AUTO_INCREMENT |
+----------------+
| 791 |
+----------------+
1 row in set (0.00 sec)
Yes! The next insert will have the next ID of 791. Hurray.
No comments yet.
AWS Android Ansible Apache Apple Atlassian BSD Backup Bash Bluecoat CMS Chef Cloud Coding Consul Containers CouchDB DB DNS Database Databases Docker ELK Elasticsearch Filebeat FreeBSD Galera Git GlusterFS Grafana Graphics HAProxy HTML Hacks Hardware Icinga Influx Internet Java KVM Kibana Kodi Kubernetes LVM LXC Linux Logstash Mac Macintosh Mail MariaDB Minio MongoDB Monitoring Multimedia MySQL NFS Nagios Network Nginx OSSEC OTRS Office PGSQL PHP Perl Personal PostgreSQL Postgres PowerDNS Proxmox Proxy Python Rancher Rant Redis Roundcube SSL Samba Seafile Security Shell SmartOS Solaris Surveillance Systemd TLS Tomcat Ubuntu Unix VMWare VMware Varnish Virtualization Windows Wireless Wordpress Wyse ZFS Zoneminder