Fresh install of EE4.1.3 and Exception Caught SQLSTATE[42S22] after debug type change
Development and Programming
All was fine until I changed the debug setting from all to admins. I receive below error and can not login to CP. Member login attempts also receive the same error.
on local machine: fresh install of EE4.1.3, mysql 5.6.35, php 7.1.1
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Member_field_id_2.m_field_dt_2' in 'field list':
SELECT exp_members.member_id as Member__member_id, Member_field_id_1.m_field_id_1 as Member__m_field_id_1, Member_field_id_1.m_field_ft_1 as Member__m_field_ft_1, Member_field_id_2.m_field_id_2 as Member__m_field_id_2, Member_field_id_2.m_field_dt_2 as Member__m_field_dt_2, Member_field_id_2.m_field_ft_2 as Member__m_field_ft_2 FROM (`exp_members`) LEFT JOIN `exp_member_data_field_1` AS Member_field_id_1 ON `Member_field_id_1`.`member_id` = `exp_members`.`member_id` LEFT JOIN `exp_member_data_field_2` AS Member_field_id_2 ON `Member_field_id_2`.`member_id` = `exp_members`.`member_id` WHERE `exp_members`.`member_id` IN (4)
www/system/ee/legacy/database/drivers/mysqli/mysqli_connection.php:116
Are you sure this is a fresh install? New installs do not have any custom member fields. Did you install over an existing database but not remove the tables perhaps? This looks like the exp_member_fields has fields defined that don’t actually exist maybe.
I meant “not an upgrade”. Database was new as well. I defined those fields via CP after install.
Can you walk me through the exact steps to reproduce on a fresh install? Thanks!
Sure,
1) I am using Uniserver for local development.
2) Created a new db
3) Installed EE4.1.3. All steps successful
4) Some template creation works via CP
5) Because it was development install, I set the debug for "all" option
6) Few members creation
7) Defined two member fields (date and dd-list)
8) Continued template works (approx 2 weeks) w/o any problems
9) Today I changed the debug type from "all" to "admin" and I received the Exception Caught error immediately after "Save Settings" in tabulated form (not like I posted in my first post). Unfortunately I lost that page.
10) Now the site loads normal. However error shows up when a member tries to login or admin login to CP.Additionally, USER and Low_Variables modules were installed just after install.
Hope this helps
Thanks for the additional detail. I cannot replicate with those steps. Let’s check your database for integrity.
SELECT m_field_name, m_field_type FROM exp_member_fields WHERE m_field_id = 2;And:
SHOW CREATE TABLE exp_member_data_field_2;Results:
mysql> SELECT m_field_name, m_field_type FROM exp_member_fields WHERE m_field_id = 2;
+-------------------+--------------+
| m_field_name | m_field_type |
+-------------------+--------------+
| member_birth_date | date |
+-------------------+--------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE exp_member_data_field_2;
| exp_member_data_field_2 | CREATE TABLE `exp_member_data_field_2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`member_id` int(10) unsigned NOT NULL,
`m_field_id_2` text COLLATE utf8mb4_unicode_ci,
`m_field_ft_2` tinytext COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`id`),
KEY `member_id` (`member_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
1 row in set (0.02 sec)
mysql>Do you have any content in the exp_member_data_field_2 table? You are definitely missing a column there for a date field:
`m_field_dt_2` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,Looks like I can replicate this if the custom member field started life as something other than a date field first, and then was later changed to a date field. You should be able to add that column and have everything back again. Or if there is no data at this point, simply delete that member field and create a new date field.
custom member field started life as something other than a date field first, and then was later changed to a date field
Yes..!!! It was exactly like that: (text -> date) However, I did this change about a week back and didn’t face a problem.
simply delete that member field and create a new date field.
Solved the problem… Now CP is back on.
Thank you Derek
PS: should I be worrying if I need to change the field type in the future for some reason?
I’ve filed a bug report for that change, that should be the only thing that is affected. That said, it should be pretty rare to need to change existing fields to other types. The existing data would not likely be useful to the new data type. So deleting an old field and creating a new one would be just as effective in most cases.
Thanks
I am facing “exception caught” errors again. This time they are coming from memberlist search(es) in custom member fields. Search is done with default member templates (i.e. form at the bottom of memberlist page).
Exception Caught
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'md.m_field_id_5' in 'where clause':
SELECT m.member_id FROM exp_members m, exp_member_data md WHERE m.member_id = md.member_id AND m.group_id NOT IN ('3', '4','2') AND md.m_field_id_5 LIKE '%Atlanta%'
/www/system/ee/legacy/database/drivers/mysqli/mysqli_connection.php:116P.S. to fix the previous error, custom fields were defined via CP and no type-modification done afterwards.
Do you have a stack trace with that error, Tansel? Thanks!
Stack Trace
#0 system\ee\legacy\database\drivers\mysqli\mysqli_driver.php(111): CI_DB_mysqli_connection->query('SELECT m.member...')
#1 system\ee\legacy\database\DB_driver.php(269): CI_DB_mysqli_driver->_execute('SELECT m.member...')
#2 system\ee\legacy\database\DB_driver.php(179): CI_DB_driver->simple_query('SELECT m.member...')
#3 system\ee\EllisLab\Addons\member\mod.member_memberlist.php(1168): CI_DB_driver->query('SELECT m.member...')
#4 system\ee\EllisLab\Addons\member\mod.member.php(1564): Member_memberlist->do_member_search()
#5 system\ee\EllisLab\Addons\member\mod.member.php(365): Member->do_member_search()
#6 system\ee\legacy\libraries\Core.php(589): Member->manager()
#7 system\ee\legacy\controllers\ee.php(62): EE_Core->generate_page()
#8 [internal function]: EE->index()
#9 system\ee\EllisLab\ExpressionEngine\Core\Core.php(240): call_user_func_array(Array, Array)
#10 system\ee\EllisLab\ExpressionEngine\Core\Core.php(109): EllisLab\ExpressionEngine\Core\Core->runController(Array)
#11 system\ee\EllisLab\ExpressionEngine\Boot\boot.php(146): EllisLab\ExpressionEngine\Core\Core->run(Object(EllisLab\ExpressionEngine\Core\Request))
#12 index.php(172): require_once('D:\\bistogram-4\\...')
#12 index.php(172): require_once('D:\\bistogram-4\\...')Thanks Tansel for the extra detail, sorry for the delay, I’ve been out of pocket.