Sql error state 42s22

If you’re getting an error that reads something like “ERROR 1054 (42S22): Unknown column ‘tab.ColName’ in ‘on clause”” in MariaDB, here are three likely causes:

If you’re getting an error that reads something like “ERROR 1054 (42S22): Unknown column ‘tab.ColName’ in ‘on clause”” in MariaDB, here are three likely causes:

  • The column doesn’t exist.
  • You’re trying to reference an aliased column by its column name.
  • Or it could be the other way around. You could be referencing the column with an alias that was never declared.

If a column has an alias, then you must use that alias when referencing it in any ON clause when doing a join against two or more tables. Conversely, if you reference a column by an alias, then you need to ensure that the alias is actually declared in the first place.

Example 1

Here’s an example of code that produces the error:

SELECT 
    c.CatId,
    c.CatName
FROM Cats c
INNER JOIN Dogs d
ON c.DogName = d.DogName;

Result:

ERROR 1054 (42S22): Unknown column 'c.DogName' in 'on clause'

Here I accidentally used c.DogName in the ON clause when I meant to use c.CatName.

In this case, the fix is simple. Use the correct column name:

SELECT 
    c.CatId,
    c.CatName
FROM Cats c
INNER JOIN Dogs d
ON c.CatName = d.DogName;

Example 2

Here’s another example of code that produces the error:

SELECT 
    CatId,
    CatName
FROM Cats
INNER JOIN Dogs d
ON c.CatName = d.DogName;

Result:

ERROR 1054 (42S22): Unknown column 'c.CatName' in 'on clause'

Here I referenced a non-existent alias in the ON clause. I used c.CatName to refer to the CatName column in the Cats table. The only problem is that the Cats table doesn’t have an alias.

To fix this issue, all we have to do is provide an alias for the Cats table:

SELECT 
    CatId,
    CatName
FROM Cats c
INNER JOIN Dogs d
ON c.CatName = d.DogName;

Alternatively, we could remove all references to the alias, and just use the full table name:

SELECT 
    CatId,
    CatName
FROM Cats
INNER JOIN Dogs
ON Cats.CatName = Dogs.DogName;

One thing I should point out is that, in this example we didn’t prefix the column names in the SELECT list with the alias. If we had done that, we would have seen the same error, but with a slightly different message:

SELECT 
    c.CatId,
    c.CatName
FROM Cats
INNER JOIN Dogs d
ON c.CatName = d.DogName;

Result:

ERROR 1054 (42S22): Unknown column 'c.CatId' in 'field list'

In this case, it detected the unknown columns in the field list before it found the one in the ON clause. Either way, the solution is the same.

Example 3

Here’s another example of code that produces the error:

SELECT 
    c.CatId,
    c.CatName
FROM Cats c
INNER JOIN Dogs d
ON Cats.CatName = d.DogName;

Result:

ERROR 1054 (42S22): Unknown column 'Cats.CatName' in 'on clause'

In this case, an alias was declared for the Cats table, but I didn’t use that alias in the ON clause.

The solution here, is to use the alias instead of the table name:

SELECT 
    c.CatId,
    c.CatName
FROM Cats c
INNER JOIN Dogs d
ON c.CatName = d.DogName;

Источник

SQL Error: 1054, SQLState: 42S22 this is the Error caused by the missing field, first attach the Error log:

2020-03-04 10:30:00. 221 WARN [pool – 8 – thread – 1] [org. Hibernate. Engine. JDBC. Spi. SqlExceptionHelper. Java: 127] – SQL Error: 1054, SQLState: 42 s22
the 2020-03-04 10:30:00. 221 ERROR [pool – 8 – thread – 1] [org. Hibernate. Engine. JDBC. Spi. SqlExceptionHelper. Java: 129] – Unknown column ‘markcardex0_. Art_service_time’ in ‘field List ‘
the 2020-03-04 10:30:00. 225 ERROR [pool – 8 – thread – 1] [org. Springframework. Scheduling. Support. TaskUtils $LoggingErrorHandler. Java: 95] – Unexpected ERROR occurred in scheduled task.
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; Nested exception is org. Hibernate. Exception….

…

under Caused by: org. Hibernate. Exception. SQLGrammarException: could not extract the ResultSet
… 24 common frames omitted
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column ‘markcardex0_. Art_service_time’ in ‘the field list’
the at sun, reflect the NativeConstructorAccessorImpl. NewInstance0 (Native Method)
the at Sun. Reflect. NativeConstructorAccessorImpl. NewInstance (NativeConstructorAccessorImpl. Java: 62)
the at Sun. Reflect. DelegatingConstructorAccessorImpl. NewInstance (DelegatingConstructorAccessorImpl. Java: 45)
the at Java lang. Reflect. Constructor. NewInstance (423) Constructor. Java:
the at Com. Mysql. JDBC. Util. HandleNewInstance Util. Java: (411)
at the mysql. JDBC. Util. GetInstance (Util. Java: 386)
at the mysql. JDBC. SQLError. CreateSQLException (SQLError. Java: 1053)
the at Com. Mysql. JDBC. MysqlIO. CheckErrorPacket MysqlIO. Java: (4074)
at the mysql. JDBC. MysqlIO. CheckErrorPacket (MysqlIO. Java: 4006)
at the mysql. JDBC. MysqlIO. SendCommand (MysqlIO. Java: 2468)
At com. Mysql. JDBC. MysqlIO. SqlQueryDirect (MysqlIO. Java: 2629)
at the mysql. JDBC. ConnectionImpl. ExecSQL (ConnectionImpl. Java: 2719)
the at . Com. Mysql. JDBC PreparedStatement. ExecuteInternal (PreparedStatement. Java: 2155)
at the mysql.. JDBC PreparedStatement. ExecuteQuery (2318) a PreparedStatement. Java:
the at Com. Alibaba. Druid. Filter. FilterChainImpl. PreparedStatement_executeQuery (FilterChainImpl. Java: 2714)
the at Com. Alibaba. Druid. Filter. FilterEventAdapter. PreparedStatement_executeQuery (FilterEventAdapter. Java: 465)
the at Com. Alibaba. Druid. Filter. FilterChainImpl. PreparedStatement_executeQuery (FilterChainImpl. Java: 2711)
the at Com. Alibaba. Druid. Proxy. JDBC. PreparedStatementProxyImpl. ExecuteQuery (PreparedStatementProxyImpl. Java: 145)
the at Com. Alibaba. Druid. Pool. DruidPooledPreparedStatement. ExecuteQuery (DruidPooledPreparedStatement. Java: 227)
the at Org. Hibernate. Engine. JDBC. Internal. ResultSetReturnImpl. Extract (ResultSetReturnImpl. Java: 70)
… 60 common frames omitted

error occurs mainly because the variables in the entity class do not match the classes in the database table, which is a headache, especially when the data is complex. I used to be stupid, one by one, but now I can sum up a simple method:

1. Look at the reason for this error: Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column ‘markcardex0_. Art_service_time ‘in ‘field List’ is Unknown column ‘markcardex0_. Art_service_time ‘in ‘field list’ is Unknown column’ mark_service_time ‘in ‘field list’ Find –> Find In Path) quickly search artServiceTime, Find the entity class that defines the variable artServiceTime, click In, as follows:

2. After entering the entity class corresponding to this variable, you can see the database Table corresponding to this entity through the annotation @table (name = “uk_markcard_xxxx”) :

opens the database and verifies the existence of this attribute in the table through the query, as follows:

as shown in the figure above, we found the same error as the console, at this time we add the corresponding entity variable property on the table, note:

(1) attribute type must be the same as the entity variable type

(2) MySQL makes no case difference, but if the variable in the entity class is named aaaBcc, the attribute name in the database should be aaa_bcc, not

Read More:

Источник

VirtualMan

2 / 2 / 1

Регистрация: 21.07.2016

Сообщений: 46

1

26.08.2017, 16:36. Показов 2782. Ответов 2

Метки нет (Все метки)

Ошибка: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘surname’ in ‘field list’

Не могу понять в чем ошибка.
var_dump($_POST) перед INSERT выдает
array(8) {
[«firstname»]=>
string(4) «name»
[«surname»]=>
string(3) «sur»
[«age»]=>
string(3) «age»
[«email»]=>
string(4) «mail»
[«city»]=>
string(4) «city»
[«language»]=>
string(4) «lang»
[«login»]=>
string(7) «user888»
[«password»]=>
string(4) «pass»
}

PHP 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

require_once(__DIR__ . '/includes/connectdb.inc.php');
 
function addUser($pdo)
{
    $sql = "INSERT INTO users (`firstname`, `surname`, `age`, `email`, `city`, `language`, `login`, `password`) ";
    $sql .= "VALUES (:firstname, :surname, :age, :email, :city, :language, :login, :password)";
    // --- Простая отладка ---
    echo '<pre>';
    var_dump($_POST);
    echo '</pre>';
    try {
        $s = $pdo->prepare($sql);
        $s->execute($_POST);
    } catch (PDOException $e) {
        echo 'Ошибка: ' . $e->getMessage();
        exit();
    }
    return;
}
 
if (!empty($_POST)) {
    array_pop($_POST);
    addUser($pdo);
    $id = $pdo->lastInsertId();
    echo "Запись успешно сохранена в БД под ID: $id<br>";
}
 
?>
 
<form action='' method='post'>
    <input name='firstname' type='text' autofocus required placeholder="Введите имя"><br>
    <input name='surname' type='text' required placeholder="Введите фамилию"><br>
    <input name='age' type='text' required placeholder="Введите возраст"><br>
    <input name='email' type='text' required placeholder="Введите email"><br>
    <input name='city' type='text' required placeholder="Введите город"><br>
    <input name='language' type='text' required placeholder="Введите язык"><br>
    <input name='login' type='text' required placeholder="Введите логин"><br>
    <input name='password' type='password' required placeholder="Введите пароль"><br>
    <input name='passwordConfirm' type='password' required placeholder="Введите пароль повторно"><br><br>
    <input type='submit' value='Отправить'>
</form>

Структура БД
id int(11) Автоматическое приращение
firstname varchar(32) NULL
lastname varchar(64) NULL
age varchar(4) NULL
email varchar(128) NULL
city varchar(32) NULL
language varchar(32) NULL
login varchar(32)
password varchar(32)

__________________
Помощь в написании контрольных, курсовых и дипломных работ, диссертаций здесь



0

Native x86

Эксперт Hardware

5175 / 3022 / 874

Регистрация: 13.02.2013

Сообщений: 9,635

26.08.2017, 16:40

2

Цитата
Сообщение от VirtualMan
Посмотреть сообщение

Unknown column ‘surname’ in ‘field list’

Вам перевести на русский?



0

2 / 2 / 1

Регистрация: 21.07.2016

Сообщений: 46

26.08.2017, 16:45

 [ТС]

3

Вопрос снят, ошибся в названии столбца



0
Источник

I own a Magento CE 1.7.0.x based website where I keep getting variants of the following error (usually after the daily morning site reindex):

» SQL ERROR: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘main_table.data’ in ‘field list’

SQL QUERY: SELECT DISTINCT `main_table`.`data`, `main_table`.`lifetime`, `main_table`.`expire`, `main_table`.`priority`, `additional_table`.*, IFNULL(al.value, main_table.frontend_label) AS `store_label` FROM `eav_attribute` AS `main_table`

 INNER JOIN `catalog_eav_attribute` AS `additional_table` ON additional_table.attribute_id = main_table.attribute_id

 LEFT JOIN `eav_attribute_label` AS `al` ON al.attribute_id = main_table.attribute_id AND al.store_id = 1 WHERE (main_table.entity_type_id

 (see full error below)»

I have a website down detector set up so that I can know immediately when the site is unavailable. However it obviously doesn’t detect whenever the site is inoperative due to errors such as “SQL ERROR: SQLSTATE[42S22]”. That means that my team must be regularly monitoring manually (!!!) if the site is indeed working appropriately (by clicking on product and category links)!

Usually we solve the error by just cleaning the cache or performing a new reindex.

My questions are the following:

1 — Is there a way of routinely perform a detection of this type of errors in Magento so that — if it occurs — a cache cleaning (or a site reindex) is immediately run and an alert is sent to the webmaster?

2 – If such an error is detected, is there a way of it not be shown to the person accessing the site? That is, if the error is detected, is it possible to immediately display a message (“We’ll be back soon”) while the cache is being cleaned or the site reindexed?

I will be grateful for any help you can provide.

Thank you!

Full error example:

SQL ERROR: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘main_table.data’ in ‘field list’SQL QUERY: SELECT DISTINCT `main_table`.`data`, `main_table`.`lifetime`, `main_table`.`expire`, `main_table`.`priority`, `additional_table`.*, IFNULL(al.value, main_table.frontend_label) AS `store_label` FROM `eav_attribute` AS `main_table` INNER JOIN `catalog_eav_attribute` AS `additional_table` ON additional_table.attribute_id = main_table.attribute_id LEFT JOIN `eav_attribute_label` AS `al` ON al.attribute_id = main_table.attribute_id AND al.store_id = 1 WHERE (main_table.entity_type_id = 4) AND (additional_table.is_filterable > 0)

Trace:#0 /var/www/vhosts/DOMAIN.com/httpdocs/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)#1 /var/www/vhosts/DOMAIN.com/httpdocs/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)#2 /var/www/vhosts/DOMAIN.com/httpdocs/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)#3 /var/www/vhosts/DOMAIN.com/httpdocs/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(‘SELECT DISTINCT…’, Array)#4 /var/www/vhosts/DOMAIN.com/httpdocs/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query(‘SELECT DISTINCT…’, Array)#5 /var/www/vhosts/DOMAIN.com/httpdocs/lib/Zend/Db/Adapter/Abstract.php(734): Varien_Db_Adapter_Pdo_Mysql->query(‘SELECT DISTINCT…’, Array)#6 /var/www/vhosts/DOMAIN.com/httpdocs/lib/Varien/Data/Collection/Db.php(734): Zend_Db_Adapter_Abstract->fetchAll(‘SELECT DISTINCT…’, Array)#7 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/core/Mage/Core/Model/Resource/Db/Collection/Abstract.php(521): Varien_Data_Collection_Db->_fetchAll(‘SELECT DISTINCT…’, Array)#8 /var/www/vhosts/DOMAIN.com/httpdocs/lib/Varien/Data/Collection/Db.php(566): Mage_Core_Model_Resource_Db_Collection_Abstract->getData()#9 /var/www/vhosts/DOMAIN.com/httpdocs/lib/Varien/Data/Collection.php(741): Varien_Data_Collection_Db->load()#10 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/local/Mana/Core/Helper/Data.php(286): Varien_Data_Collection->getIterator()#11 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/local/Mana/Filters/Model/Filter2.php(36): Mana_Core_Helper_Data->collectionFind(Object(Mage_Catalog_Model_Resource_Product_Attribute_Collection), ‘attribute_code’, ‘produto_configu…’)#12 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/local/Mana/Filters/Block/View.php(83): Mana_Filters_Model_Filter2->getAttribute()#13 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/local/Mana/Core/Helper/Layout.php(31): Mana_Filters_Block_View->delayedPrepareLayout()#14 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/local/Mana/Core/Model/Observer.php(96): Mana_Core_Helper_Layout->prepareDelayedLayoutBlocks()#15 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/core/Mage/Core/Model/App.php(1338): Mana_Core_Model_Observer->postProcessBlocks(Object(Varien_Event_Observer))#16 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/core/Mage/Core/Model/App.php(1317): Mage_Core_Model_App->_callObserverMethod(Object(Mana_Core_Model_Observer), ‘postProcessBloc…’, Object(Varien_Event_Observer))#17 /var/www/vhosts/DOMAIN.com/httpdocs/app/Mage.php(447): Mage_Core_Model_App->dispatchEvent(‘controller_acti…’, Array)#18 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/core/Mage/Core/Controller/Varien/Action.php(351): Mage::dispatchEvent(‘controller_acti…’, Array)#19 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/core/Mage/Catalog/controllers/CategoryController.php(146): Mage_Core_Controller_Varien_Action->generateLayoutBlocks()#20 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/core/Mage/Core/Controller/Varien/Action.php(419): Mage_Catalog_CategoryController->viewAction()#21 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(254): Mage_Core_Controller_Varien_Action->dispatch(‘view’)#22 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))#23 /var/www/vhosts/DOMAIN.com/httpdocs/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()#24 /var/www/vhosts/DOMAIN.com/httpdocs/app/Mage.php(683): Mage_Core_Model_App->run(Array)#25 /var/www/vhosts/DOMAIN.com/DOMAIN.pt/index.php(68): Mage::run(‘pt’, ‘store’)#26 {main}

Источник

Понравилась статья? Поделить с друзьями:
  • Sql error ora 00984 употребление столбца здесь недопустимо 00984 00000 column not allowed here
  • Sql error code 1822
  • Sql error code 1205
  • Sql error code 1055
  • Sql error code 104 token unknown