Odd MySQL Error.

[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)
);

[lat9]

Yep, good call. I was looking at a zc158a database.

[g2ktcf]

I just had this issue pop up AGAIN (4th time) and I can confirm that XAMPP was NOT running at the time as I had to start it in order to check my install of OPC into my local ZC 2.0 test.