If you’re getting “ERROR 1222 (21000): The used SELECT statements have a different number of columns” when using the UNION clause in a MySQL query, it’s probably because the number of columns returned by each SELECT statement is different.
For example, the first SELECT statement might return two columns, but the second SELECT statement returns three.
To fix this issue, make sure both SELECT statements return the same number of columns.
Example of Error
Here’s an example of code that produces the error:
SELECT TeacherName FROM Teachers
UNION
SELECT StudentId, StudentName FROM Students;Result:
ERROR 1222 (21000): The used SELECT statements have a different number of columns
Here, the first SELECT statement returns one column (TeacherName), but the second SELECT statement returns two columns (StudentId and StudentName).
Solution
The way to fix this issue is to ensure both SELECT statements return the same number of columns
So using the above example, we can either remove the extra column from our second SELECT statement:
SELECT TeacherName FROM Teachers
UNION
SELECT StudentName FROM Students;Result:
+-------------+ | TeacherName | +-------------+ | Warren | | Ben | | Cathy | | Bill | | Faye | | Jet | | Spike | | Ein | +-------------+
Or we can add another column to the first SELECT statement:
SELECT TeacherId, TeacherName FROM Teachers
UNION
SELECT StudentId, StudentName FROM Students;Result:
+-----------+-------------+ | TeacherId | TeacherName | +-----------+-------------+ | 1 | Warren | | 2 | Ben | | 3 | Cathy | | 4 | Cathy | | 5 | Bill | | 6 | Bill | | 1 | Faye | | 2 | Jet | | 3 | Spike | | 4 | Ein | | 5 | Warren | +-----------+-------------+
Be mindful that you can get different results depending on which option you choose. This is because UNION returns distinct rows by default. When we add another column, there’s a possibility that a previously duplicate row now becomes a unique row, depending on the value in the extra column.
We can also use UNION ALL, which returns duplicate values:
SELECT TeacherId, TeacherName FROM Teachers
UNION ALL
SELECT StudentId, StudentName FROM Students;Result:
+-----------+-------------+ | TeacherId | TeacherName | +-----------+-------------+ | 1 | Warren | | 2 | Ben | | 3 | Cathy | | 4 | Cathy | | 5 | Bill | | 6 | Bill | | 1 | Faye | | 2 | Jet | | 3 | Spike | | 4 | Ein | | 5 | Warren | | 6 | Bill | +-----------+-------------+
Beside from the answer given by @omg-ponies; I just want to add that this error also occur in variable assignment. In my case I used an insert; associated with that insert was a trigger. I mistakenly assign different number of fields to different number of variables. Below is my case details.
INSERT INTO tab1 (event, eventTypeID, fromDate, toDate, remarks)
-> SELECT event, eventTypeID,
-> fromDate, toDate, remarks FROM rrp group by trainingCode;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
So you see I got this error by issuing an insert statement instead of union statement. My case difference were
-
I issued a bulk insert sql
i.e. insert into tab1 (field, …) as select field, … from tab2
-
tab2 had an on insert trigger; this trigger basically decline duplicates
It turns out that I had an error in the trigger. I fetch record based on new input data and assigned them in incorrect number of variables.
DELIMITER @@
DROP TRIGGER trgInsertTrigger @@
CREATE TRIGGER trgInsertTrigger
BEFORE INSERT ON training
FOR EACH ROW
BEGIN
SET @recs = 0;
SET @trgID = 0;
SET @trgDescID = 0;
SET @trgDesc = '';
SET @district = '';
SET @msg = '';
SELECT COUNT(*), t.trainingID, td.trgDescID, td.trgDescName, t.trgDistrictID
INTO @recs, @trgID, @trgDescID, @proj, @trgDesc, @district
from training as t
left join trainingDistrict as tdist on t.trainingID = tdist.trainingID
left join trgDesc as td on t.trgDescID = td.trgDescID
WHERE
t.trgDescID = NEW.trgDescID
AND t.venue = NEW.venue
AND t.fromDate = NEW.fromDate
AND t.toDate = NEW.toDate
AND t.gender = NEW.gender
AND t.totalParticipants = NEW.totalParticipants
AND t.districtIDs = NEW.districtIDs;
IF @recs > 0 THEN
SET @msg = CONCAT('Error: Duplicate Training: previous ID ', CAST(@trgID AS CHAR CHARACTER SET utf8) COLLATE utf8_bin);
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;
END IF;
END @@
DELIMITER ;
As you can see i am fetching 5 fields but assigning them in 6 var. (My fault totally I forgot to delete the variable after editing.
In order to fix “ERROR 1222 (21000): The used SELECT statements have a different number of columns” while we are using UNION clause we need to understand why we get this error. We used union between 2 select queries. So both select queries were not selecting same number of columns. That’s how we got this error. Here is how to fix this error:
SELECT EmployeeName FROM Employees
UNION
SELECT CompanyId, CompanyName FROM Companies;First select query is selecting “EmployeeName” and second one is selecting CompanyId and CompanyName. Clearly the number of selected columns is not the same. So here is the correct query that will fix the error.
SELECT EmployeeName FROM Employees
UNION
SELECT CompanyName FROM Companies;Or we can also select two columns with UNION clause.
SELECT EmployeeId,EmployeeName FROM Employees
UNION
SELECT CompanyId,CompanyName FROM Companies;Also UNION clause can only select distinct row. This means that duplicate rows will not be selected. In order to select duplicate we need to use “UNION ALL” clause.
SELECT EmployeeId,EmployeeName FROM Employees
UNION ALL
SELECT CompanyId,CompanyName FROM Companies;For any issues while fixing this error feel free to drop a comment below.
The SELECT statements I used have varying column counts. Below is the query I entered. This error message about «different number of columns» occurs because you cannot create a table with varying column counts.
Table of contents
- Error Code: 1222. The used SELECT statements have a different number of columns
- #1222 — The used SELECT statements have a different number of columns
- Sql error: 1222, sqlstate: s00051 lock request timeout happening in websphere only (never happens in jboss)
- What is error 1222 in SQL Server?
- What does MSG 1222 mean?
- What does error 1222 severity 16 state 18 mean?
- Is the Windows Server 2012 failover cluster event ID 1222 logged?
Error Code: 1222. The used SELECT statements have a different number of columns
Question:
I have made several attempts, using various approaches such as joins, but I have encountered difficulties in inserting data from three different MySQL tables into a single table. The most recent attempt resulted in the following error: Error Code: 1222. The utilized
SELECT statements
table has a different number of columns. Below is the query that I used. (As a newcomer to coding, I am still learning MySQL and would greatly appreciate any assistance. Thank you.)
INSERT INTO cases (person_id, last_name, first_name, date_applied)
SELECT person_id, last_name, first_name,
date_applied FROM person UNION SELECT (program_name) FROM program
UNION SELECT (status) FROM casestatus
Solution:
The
SELECT statement
preceding the
UNION
keyword chooses 4 columns, whereas the SELECT statement following the
UNION
keyword chooses only 1 column. It is not possible to construct a
UNION
with a varying number of columns, which is why you are receiving this error message regarding the «different number of columns».
Update Stats Error — Msg 1222, Lock request time out, Msg 1222, Lock request time out period exceeded Command: UPDATE STATISTICS [BIDS].[dbo].[WorkQ] [_WA_Sys_00000006_151B244E] Msg 50000, Level 16, State 1, Server BIDSSql, Procedure CommandExecute, Line 167 Msg 1222, Lock request time out period exceeded.
How to resolve NFS Mount ‘Error 1222’ under Windows
How to resolve NFS Mount
‘Error 1222’
under Windows 10 Pro?Helpful? Please support me on Patreon: https://www.patreon.com/roelvandepaarWith thanks & praise …
#1222 — The used SELECT statements have a different number of columns
Question:
What is the reason behind receiving a #1222 —
the used select statements have a different number of columns
error? I am attempting to fetch wall posts from both the user’s friends and the user themselves.
SELECT u.id AS pid, b2.id AS id, b2.message AS message, b2.date AS date FROM
(
(
SELECT b.id AS id, b.pid AS pid, b.message AS message, b.date AS date FROM
wall_posts AS b
JOIN Friends AS f ON f.id = b.pid
WHERE f.buddy_id = '1' AND f.status = 'b'
ORDER BY date DESC
LIMIT 0, 10
)
UNION
(
SELECT * FROM
wall_posts
WHERE pid = '1'
ORDER BY date DESC
LIMIT 0, 10
)
ORDER BY date DESC
LIMIT 0, 10
) AS b2
JOIN Users AS u
ON b2.pid = u.id
WHERE u.banned='0' AND u.email_activated='1'
ORDER BY date DESC
LIMIT 0, 10
The structure of the wall_posts table is represented by the following codes:
id
,
date
,
privacy
,
pid
,
uid
,
message
.
The structure of the Friends table appears as
Fid
,
id
,
buddy_id
,
invite_up_date
, and
status
.
The acronym pid refers to profile id. I am uncertain about the current situation.
Solution 1:
The initial statement in the UNION yields a result with four columns.
SELECT b.id AS id,
b.pid AS pid,
b.message AS message,
b.date AS date
FROM wall_posts AS b
The second result is six, as the asterisk expands to encompass all columns from
WALL_POSTS
.
SELECT b.id,
b.date,
b.privacy,
b.pid.
b.uid message
FROM wall_posts AS b
The operators
UNION
and
UNION ALL
have the following requirement:
- All the statements comprising the UNION’d query have an equal number of columns.
- Matching data types are required for each position/column.
Use:
FROM ((SELECT b.id AS id,
b.pid AS pid,
b.message AS message,
b.date AS date
FROM wall_posts AS b
JOIN Friends AS f ON f.id = b.pid
WHERE f.buddy_id = '1' AND f.status = 'b'
ORDER BY date DESC
LIMIT 0, 10)
UNION
(SELECT id,
pid,
message,
date
FROM wall_posts
WHERE pid = '1'
ORDER BY date DESC
LIMIT 0, 10))
Solution 2:
You cannot combine the data from a 4-column relation (
UNION
,
id
,
pid
, and
message
) with a 6-column relation (
*
= the 6 columns of
wall_posts
) in SQL as it is not allowed.
Solution 3:
(
SELECT b.id AS id, b.pid AS pid, b.message AS message, b.date AS date FROM
wall_posts AS b
JOIN Friends AS f ON f.id = b.pid
WHERE f.buddy_id = '1' AND f.status = 'b'
ORDER BY date DESC
LIMIT 0, 10
)
UNION
(
SELECT id, pid , message , date
FROM
wall_posts
WHERE pid = '1'
ORDER BY date DESC
LIMIT 0, 10
)
Match the selection of 4 in the first query with the selection of 6 in the second query.
Solution 4:
«Rephrased MSDTHOT»:
In addition to @omg-ponies’ response, I would like to mention that this issue can also arise during variable assignment. In my situation, I utilized an insert operation which had an associated trigger. However, I made the mistake of assigning a different number of fields to a different number of variables. Below, I have provided the specific details of my case.
INSERT INTO tab1 (event, eventTypeID, fromDate, toDate, remarks)
-> SELECT event, eventTypeID,
-> fromDate, toDate, remarks FROM rrp group by trainingCode;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
As a result of using an insert statement rather than a union statement, I encountered this error. The discrepancy in my case was the cause.
-
<p>
I issued a bulk insert sql
</p>To clarify, the statement «insert into tab1 (field, …) as select field, … from tab2» can be used to insert data from tab2 into tab1.
-
Tab2 featured an on-insert trigger that effectively prevented the creation of duplicate entries.
There was an error in the trigger where I fetched records using the new
input data
and mistakenly assigned them to an incorrect number of variables.
DELIMITER @@
DROP TRIGGER trgInsertTrigger @@
CREATE TRIGGER trgInsertTrigger
BEFORE INSERT ON training
FOR EACH ROW
BEGIN
SET @recs = 0;
SET @trgID = 0;
SET @trgDescID = 0;
SET @trgDesc = '';
SET @district = '';
SET @msg = '';
SELECT COUNT(*), t.trainingID, td.trgDescID, td.trgDescName, t.trgDistrictID
INTO @recs, @trgID, @trgDescID, @proj, @trgDesc, @district
from training as t
left join trainingDistrict as tdist on t.trainingID = tdist.trainingID
left join trgDesc as td on t.trgDescID = td.trgDescID
WHERE
t.trgDescID = NEW.trgDescID
AND t.venue = NEW.venue
AND t.fromDate = NEW.fromDate
AND t.toDate = NEW.toDate
AND t.gender = NEW.gender
AND t.totalParticipants = NEW.totalParticipants
AND t.districtIDs = NEW.districtIDs;
IF @recs > 0 THEN
SET @msg = CONCAT('Error: Duplicate Training: previous ID ', CAST(@trgID AS CHAR CHARACTER SET utf8) COLLATE utf8_bin);
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;
END IF;
END @@
DELIMITER ;
I apologize for the oversight, as I mistakenly forgot to delete the variable after editing, resulting in assigning 5 fields to 6 variables.
Error: 1222, If it is a production database, you will need to wait until you have a maintenance window. Also, in my opinion, changes in a production database should be carried out through T-SQL scripts which has been tested in test environments first, and not through point-and-click UIs.
Sql error: 1222, sqlstate: s00051 lock request timeout happening in websphere only (never happens in jboss)
Question:
If websphere and
SQL Server
are used, the application is encountering the following error.
An SQL error with code 1222 and SQL state S00051 occurred, causing the
Lock request time
to be unavailable.
The application works properly without any error being thrown when I use Jboss.
Here is the setup I use for my web application’s development environment.
- In 2012, there was an issue with the XA DataSource in WebSphere, as mentioned above.
- The combination of Jboss, SQL Server 2012, and XA DataSource is functioning properly without any errors.
- The WebSphere, Oracle, and XA DataSource are functioning properly without any errors.
I believe my web application is functioning properly as it operates without issues in a different environment. However, when utilizing WebSphere and SQL Server, a problem arises. Could someone please assist me in configuring XA DataSource in WebSphere for SQL Server 2012?
Looking forward to your help!
Just so you know, here is the entire stacktrace for your reference.
[2/24/17 15:35:33:404 SGT] 00000029 SystemOut O ==> DEBUG hibernate.SQL[109] - update TX_USER_DETAILS set GEN_NO=?, PERSON_ID=?, BIO_ID=?, ENCKEY_NAME=?, LOC_ID=?, PERSON_NAME=?, EMAIL=?, MOBILE=?, DID=?, USER_TYPE=?, ACCT_STATE=?, ACTION_BY=?, CREATE_BY=?, CREATE_DATE=?, CREATE_WSTN=?, UPDATE_BY=?, UPDATE_DATE=?, UPDATE_WSTN=?, OR_VER=?, FP_NO=?, CALLER_INFO=?, PWD=?, PWDCHKSUM=?, BIO_TYPE=?, PTHRESHOLD=? where BUSINESS_ID=?
[2/24/17 15:35:33:404 SGT] 00000029 SystemOut O Hibernate: update TX_USER_DETAILS set GEN_NO=?, PERSON_ID=?, BIO_ID=?, ENCKEY_NAME=?, LOC_ID=?, PERSON_NAME=?, EMAIL=?, MOBILE=?, DID=?, USER_TYPE=?, ACCT_STATE=?, ACTION_BY=?, CREATE_BY=?, CREATE_DATE=?, CREATE_WSTN=?, UPDATE_BY=?, UPDATE_DATE=?, UPDATE_WSTN=?, OR_VER=?, FP_NO=?, CALLER_INFO=?, PWD=?, PWDCHKSUM=?, BIO_TYPE=?, PTHRESHOLD=? where BUSINESS_ID=?
[2/24/17 15:35:35:450 SGT] 00000029 FfdcProvider W com.ibm.ws.ffdc.impl.FfdcProvider logIncident FFDC1003I: FFDC Incident emitted on /home/wasapps/IBM/profiles/AppSrv01/logs/ffdc/epid_app1_7cce7cce_17.02.24_15.35.35.41335956.txt com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteBatch 749
[2/24/17 15:35:35:478 SGT] 00000029 FfdcProvider W com.ibm.ws.ffdc.impl.FfdcProvider logIncident FFDC1003I: FFDC Incident emitted on /home/wasapps/IBM/profiles/AppSrv01/logs/ffdc/epid_app1_7cce7cce_17.02.24_15.35.35.45035957.txt com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.executeBatch 357
[2/24/17 15:35:35:482 SGT] 00000029 SystemOut O ==> WARN spi.SqlExceptionHelper[144] - SQL Error: 1222, SQLState: S00051
[2/24/17 15:35:35:483 SGT] 00000029 SystemOut O ==> ERROR spi.SqlExceptionHelper[146] - Lock request time out period exceeded.
[2/24/17 15:35:35:486 SGT] 00000029 SystemOut O ==> ERROR internal.BatchingBatch[137] - HHH000315: Exception executing batch [could not execute batch]
[2/24/17 15:35:35:488 SGT] 00000029 SystemOut O ==> ERROR internal.SynchronizationCallbackCoordinatorNonTrackingImpl[179] - HHH000346: Error during managed flush [could not execute batch]
[2/24/17 15:35:35:489 SGT] 00000029 RegisteredSyn E WTRN0074E: Exception caught from before_completion synchronization operation: org.hibernate.exception.LockTimeoutException: could not execute batch
at org.hibernate.dialect.SQLServer2005Dialect$1.convert(SQLServer2005Dialect.java:116)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:132)
at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.doExecuteBatch(BatchingBatch.java:111)
at org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl.execute(AbstractBatchImpl.java:163)
at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.executeBatch(JdbcCoordinatorImpl.java:226)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:482)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:349)
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350)
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56)
at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1222)
at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:425)
at org.hibernate.engine.transaction.synchronization.internal.SynchronizationCallbackCoordinatorNonTrackingImpl.beforeCompletion(SynchronizationCallbackCoordinatorNonTrackingImpl.java:110)
at org.hibernate.engine.transaction.synchronization.internal.RegisteredSynchronization.beforeCompletion(RegisteredSynchronization.java:50)
at org.hibernate.engine.transaction.jta.platform.internal.WebSphereExtendedJtaPlatform$TransactionManagerAdapter$TransactionAdapter$1.invoke(WebSphereExtendedJtaPlatform.java:175)
at $Proxy48.beforeCompletion(Unknown Source)
at com.ibm.ws.jtaextensions.SynchronizationCallbackWrapper.beforeCompletion(SynchronizationCallbackWrapper.java:66)
at com.ibm.tx.jta.RegisteredSyncs.coreDistributeBefore(RegisteredSyncs.java:289)
at com.ibm.ws.tx.jta.RegisteredSyncs.distributeBefore(RegisteredSyncs.java:150)
at com.ibm.ws.tx.jta.TransactionImpl.prePrepare(TransactionImpl.java:2353)
at com.ibm.ws.tx.jta.TransactionImpl.stage1CommitProcessing(TransactionImpl.java:559)
at com.ibm.tx.jta.TransactionImpl.processCommit(TransactionImpl.java:991)
at com.ibm.tx.jta.TransactionImpl.commit(TransactionImpl.java:925)
at com.ibm.ws.tx.jta.TranManagerImpl.commit(TranManagerImpl.java:436)
at com.ibm.tx.jta.TranManagerSet.commit(TranManagerSet.java:161)
at com.ibm.ws.uow.UOWManagerImpl.uowCommit(UOWManagerImpl.java:1237)
at com.ibm.ws.uow.UOWManagerImpl.uowEnd(UOWManagerImpl.java:1207)
at com.ibm.ws.uow.UOWManagerImpl.runUnderNewUOW(UOWManagerImpl.java:1143)
at com.ibm.ws.uow.UOWManagerImpl.runUnderUOW(UOWManagerImpl.java:630)
at org.springframework.transaction.jta.WebSphereUowTransactionManager.execute(WebSphereUowTransactionManager.java:290)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:279)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:644)
at com.nec.asia.epid.web.service.impl.SubmitBiometricForCommitUpdateCallbackWithoutDupCheck$$EnhancerBySpringCGLIB$$70b52a47.call()
at com.nec.asia.epid.web.service.impl.SubmitBiometricRequestHandlerImpl.handle(SubmitBiometricRequestHandlerImpl.java:298)
at com.nec.asia.epid.web.service.impl.SubmitBiometricRequestHandlerImpl.handle(SubmitBiometricRequestHandlerImpl.java:55)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:45)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
at java.lang.reflect.Method.invoke(Method.java:599)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
at org.springframework.transaction.interceptor.TransactionAspectSupport$1.doInTransaction(TransactionAspectSupport.java:285)
at org.springframework.transaction.jta.WebSphereUowTransactionManager$UOWActionAdapter.run(WebSphereUowTransactionManager.java:347)
at com.ibm.ws.uow.UOWManagerImpl.runUnderNewUOW(UOWManagerImpl.java:1116)
at com.ibm.ws.uow.UOWManagerImpl.runUnderUOW(UOWManagerImpl.java:630)
at org.springframework.transaction.jta.WebSphereUowTransactionManager.execute(WebSphereUowTransactionManager.java:290)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:279)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
at $Proxy199.handle(Unknown Source)
at com.nec.asia.epid.web.api.impl.EpidWebserviceImpl.submitBiometric(EpidWebserviceImpl.java:133)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:45)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
at java.lang.reflect.Method.invoke(Method.java:599)
at org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:180)
at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:96)
at org.apache.cxf.jaxws.AbstractJAXWSMethodInvoker.invoke(AbstractJAXWSMethodInvoker.java:178)
at org.apache.cxf.jaxws.JAXWSMethodInvoker.invoke(JAXWSMethodInvoker.java:68)
at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:75)
at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:58)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:452)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:314)
at java.util.concurrent.FutureTask.run(FutureTask.java:149)
at org.apache.cxf.workqueue.SynchronousExecutor.execute(SynchronousExecutor.java:37)
at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:107)
at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:272)
at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:121)
at org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:239)
at org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:248)
at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:222)
at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:153)
at org.apache.cxf.transport.servlet.CXFNonSpringServlet.invoke(CXFNonSpringServlet.java:167)
at org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:286)
at org.apache.cxf.transport.servlet.AbstractHTTPServlet.doPost(AbstractHTTPServlet.java:206)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:738)
at org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:262)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1661)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:944)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:507)
at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:181)
at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:91)
at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:878)
at com.ibm.ws.webcontainer.WSWebContainer.handleRequest(WSWebContainer.java:1592)
at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:191)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:453)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewRequest(HttpInboundLink.java:515)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.processRequest(HttpInboundLink.java:306)
at com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpICLReadCallback.java:84)
at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:175)
at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:138)
at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:204)
at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:775)
at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:905)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1660)
Caused by: java.sql.BatchUpdateException: Lock request time out period exceeded.
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1178)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteBatch(WSJdbcPreparedStatement.java:1016)
at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.executeBatch(WSJdbcStatement.java:822)
at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:123)
... 99 more
Solution 1:
It is important to note a difference regarding locking in WebSphere Application Server’s default transaction isolation level compared to most JDBC drivers. For Microsoft SQL Server, the default in WebSphere Application Server is Connection.TRANSACTION_REPEATABLE_READ, rather than Connection.TRANSACTION_READ_COMMITTED. On the other hand, both WebSphere Application Server and Oracle database default to Connection.TRANSACTION_READ_COMMITTED. This variation may explain why you observe no difference for Oracle database but do notice a distinction for SQL Server. Fortunately, you can easily adjust the default configuration to align with the behavior of other application servers.
When utilizing Liberty, ensure that the isolationLevel is configured on the dataSource.
To configure the webSphereDefaultIsolationLevel on the data source, if you are using traditional.
Solution 2:
I would like to express my gratitude to njr for providing a perfect solution. After searching for a solution for the past two weeks, I finally found success by implementing nrj’s solution. Once again, thank you so much. I greatly appreciate your assistance and I give you the highest score.
In terms of locking, it’s important to note that WebSphere Application Server and most JDBC drivers have different default transaction isolation levels. For Microsoft SQL Server, WebSphere Application Server defaults to Connection.TRANSACTION_REPEATABLE_READ instead of Connection.TRANSACTION_READ_COMMITTED. On the other hand, both WebSphere Application Server and Oracle database default to Connection.TRANSACTION_READ_COMMITTED, which could explain why you observe no difference for Oracle but notice a difference for SQL Server. You can easily configure the default setting to align with the behavior of other application servers.
When utilizing Liberty, ensure that the isolationLevel is configured on the dataSource.
SQL Server Error 1222, RESTORE could not start database ‘Oasis’. Could not open new database ‘Oasis’. CREATE DATABASE is aborted. (.Net SqlClient Data Provider) Hello Chris, I wonder about «RESTORE». Are you trying to create a new, empty database or are you trying to restore a backup as a new database?
In the creation of the MySQL stored procedure reported an error like this, the error description:
1222 - The used SELECT statements have a different number of columns
Prompt query column and target column are inconsistent
My SQL statement is like this
DROP PROCEDURE Pfood_price_count;
DELIMITER //
CREATE PROCEDURE Pfood_price_count(IN price_info1 FLOAT,IN price_info2 FLOAT,OUT count INT)
BEGIN
SELECT COUNT(*), SUM(price) INTO count FROM food WHERE price BETWEEN price_info1 AND price_info2;
END;//
DELIMITER ;
CALL Pfood_price_count(2, 18, @cnt);
select @cnt;
After exploration, it was found that two columns of data were queried, but I only gave a variable count to receive, so this error occurred. Change the variable to two executions.
The completed sql statement looks like this:
DELIMITER //
CREATE PROCEDURE Pfood_price_count(IN price_info1 FLOAT,IN price_info2 FLOAT,OUT count INT)
BEGIN
SELECT COUNT(*), SUM(price) INTO count,@sm FROM food WHERE price BETWEEN price_info1 AND price_info2;
END;//
DELIMITER ;
CALL Pfood_price_count(2, 18, @cnt);
select @cnt;
to sum up:
When querying, the number of query results should be the same as the number of receive functions.