Odd MySQL Error.

[g2ktcf]

I have XAMPP running 1.5.8a on PHP 8.2....

Odd thing happened with MariaDB. I got an "expected error" that seemed to be a well documented problem - see attached image. I followed the instructions to use the backup data to get around this and keep MySQL running. BUT, I lost all my users (found when ZC showed a problem connecting to the database). I recreated the user and it still did not work and nothing seem to indicate why.

So, I renamed my install folder back to zc_install and got back into the "Clean Install" process. This allowed me to find out that I mistyped the user name! So, I canceled that install and corrected the issue with the database user name. Now I am getting "WARNING: An Error occurred, please let us know!" I have access to the database and the only thing I really MUST save are my config settings for Bootstrap which I have exported to a CSV file just in case.

I can only guess that when I started the clean install process, I messed something up. Any thoughts? I have lots of tweaks that I do not want to lose.

Thanks
Chris

[lat9]

I've seen that in the past, too, when good-ol' Windows decides to push an update any the MySql process isn't shut down properly.

Can you navigate to your localhost/phpmyadmin? If so, do any of the tables in your database show as "in use" in the "Collation" column when you're viewing all the tables in the database?

[g2ktcf]

I've seen that in the past, too, when good-ol' Windows decides to push an update any the MySql process isn't shut down properly.

Can you navigate to your localhost/phpmyadmin? If so, do any of the tables in your database show as "in use" in the "Collation" column when you're viewing all the tables in the database?

yes, I am in phpmyadmin but the only thing in any of the Collation columns is utf8mb4_general. Here is a screenshot of the table page.

[g2ktcf]

I've seen that in the past, too, when good-ol' Windows decides to push an update any the MySql process isn't shut down properly.

Can you navigate to your localhost/phpmyadmin? If so, do any of the tables in your database show as "in use" in the "Collation" column when you're viewing all the tables in the database?

AH!...I left XAMPP open for a while. From now on, I will only run it when I am using it. I never found a "reason" for it..only 3 or 4 different versions of attempting to fix the issue.

[g2ktcf]

I believe I just fixed this. I dug into the error logs for ZC and the last one said that the user did not have delete access. Sure enough, I missed checking off that box in phpmyadmin. Fixed that one thing and both the store and the admin started working fine.

Thanks!

[lat9]

I believe I just fixed this. I dug into the error logs for ZC and the last one said that the user did not have delete access. Sure enough, I missed checking off that box in phpmyadmin. Fixed that one thing and both the store and the admin started working fine.

Thanks!

Appreciate the post-back with the underlying issue and the correction!

[g2ktcf]

Well, this error happened again. Today there are four tables marked "in use" and I am getting the following error log and "and an error has occurred for both my 2.0 and my 1.5.8a installs.

[14-Apr-2024 14:36:19 Europe/Berlin] Request URI: /, IP address: 127.0.0.1, Language id not set
#0 [internal function]: zen_debug_error_handler()
#1 C:\xampp\htdocs\ZC200RC1\includes\classes\db\mysql\query_factory.php(714): trigger_error()
#2 C:\xampp\htdocs\ZC200RC1\includes\classes\db\mysql\query_factory.php(659): queryFactory->show_error()
#3 C:\xampp\htdocs\ZC200RC1\includes\classes\db\mysql\query_factory.php(299): queryFactory->set_error()
#4 C:\xampp\htdocs\ZC200RC1\includes\classes\SessionHandler.php(66): queryFactory->Execute()
#5 [internal function]: Zencart\SessionHandler->read()
#6 C:\xampp\htdocs\ZC200RC1\includes\functions\sessions.php(48): session_start()
#7 C:\xampp\htdocs\ZC200RC1\includes\init_includes\init_sessions.php(106): zen_session_start()
#8 C:\xampp\htdocs\ZC200RC1\includes\autoload_func.php(40): require_once('C:\\xampp\\htdocs...')
#9 C:\xampp\htdocs\ZC200RC1\includes\application_top.php(253): require('C:\\xampp\\htdocs...')
#10 C:\xampp\htdocs\ZC200RC1\index.php(25): require('C:\\xampp\\htdocs...')
--> PHP Fatal error: MySQL error 1932: Table 'zc20.sessions' doesn't exist in engine :: SELECT value
                FROM sessions
                WHERE sesskey = 'he342vjlmssjtfnf6ui97tj8kh'
                AND expiry > '1713098179' ==> (as called by) C:\xampp\htdocs\ZC200RC1\includes\classes\SessionHandler.php on line 66 <== in C:\xampp\htdocs\ZC200RC1\includes\classes\db\mysql\query_factory.php on line 714.

[lat9]

Yep, I've seen that too on my XAMPP installation when MySQL isn't shut down properly. Any tables that are of Type InnoDB are easily corrupted and show "In use" in their Collation column when viewing all tables in the database.

In your localhost phpMyAdmin, click on the database name to view all tables in the database. For a standard installation, there are three tables that are created with an InnoDB type: customers_to_groups, customer_groups and sessions.

If any of those tables show In use, view the /{xampp-directory}/mysql/data/{database-name} directory. For each of the In use tables, you'll see two files: {table-name}.frm and {table-name}.ibd. Delete them both.

Stop and then re-Start MySQL via the XAMPP console. Navigate to the localhost phpMyAdmin, click on the database's name (to select it) and enter the following in the SQL tab's textarea input

CREATE TABLE IF NOT EXISTS customer_groups (
  group_id int UNSIGNED NOT NULL AUTO_INCREMENT,
  group_name varchar(191) NOT NULL,
  group_comment varchar(255),
  date_added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (group_id),
  UNIQUE KEY idx_groupname_zen (group_name)
);

CREATE TABLE IF NOT EXISTS customers_to_groups (
  id int UNSIGNED NOT NULL AUTO_INCREMENT,
  group_id int UNSIGNED NOT NULL,
  customer_id int UNSIGNED NOT NULL,
  date_added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY idx_custid_groupid_zen (customer_id, group_id),
  KEY idx_groupid_custid_zen (group_id, customer_id)
);

CREATE TABLE IF NOT EXISTS sessions (
  sesskey varchar(191) NOT NULL default '',
  expiry int(11) unsigned NOT NULL default 0,
  value mediumblob NOT NULL,
  PRIMARY KEY  (sesskey)
) ENGINE=InnoDB;

[g2ktcf]

exactly what I am seeing EXCEPT I also have coupon_referrers in that same state.

[g2ktcf]

Yep, I've seen that too on my XAMPP installation when MySQL isn't shut down properly. Any tables that are of Type InnoDB are easily corrupted and show "In use" in their Collation column when viewing all tables in the database.

In your localhost phpMyAdmin, click on the database name to view all tables in the database. For a standard installation, there are three tables that are created with an InnoDB type: customers_to_groups, customer_groups and sessions.

If any of those tables show In use, view the /{xampp-directory}/mysql/data/{database-name} directory. For each of the In use tables, you'll see two files: {table-name}.frm and {table-name}.ibd. Delete them both.

Stop and then re-Start MySQL via the XAMPP console. Navigate to the localhost phpMyAdmin, click on the database's name (to select it) and enter the following in the SQL tab's textarea input

CREATE TABLE IF NOT EXISTS customer_groups (
  group_id int UNSIGNED NOT NULL AUTO_INCREMENT,
  group_name varchar(191) NOT NULL,
  group_comment varchar(255),
  date_added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (group_id),
  UNIQUE KEY idx_groupname_zen (group_name)
);

CREATE TABLE IF NOT EXISTS customers_to_groups (
  id int UNSIGNED NOT NULL AUTO_INCREMENT,
  group_id int UNSIGNED NOT NULL,
  customer_id int UNSIGNED NOT NULL,
  date_added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY idx_custid_groupid_zen (customer_id, group_id),
  KEY idx_groupid_custid_zen (group_id, customer_id)
);

CREATE TABLE IF NOT EXISTS sessions (
  sesskey varchar(191) NOT NULL default '',
  expiry int(11) unsigned NOT NULL default 0,
  value mediumblob NOT NULL,
  PRIMARY KEY  (sesskey)
) ENGINE=InnoDB;

Based on this and the install files I believe I should add the below to account for the coupon_referrers addition?

CREATE TABLE IF NOT EXISTS coupon_referrers (
  referrer_id int(11) NOT NULL AUTO_INCREMENT,
  referrer_domain varchar(64) NOT NULL,
  coupon_id INT(11) NOT NULL,
  date_added timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (referrer_id),
  UNIQUE KEY idx_referrer_domain_zen (referrer_domain),
  KEY idx_refcoupon_id_zen (coupon_id)
);