You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@sqoop.apache.org by ZORAIDA HIDALGO SANCHEZ <zo...@tid.es> on 2013/07/03 22:49:07 UTC
MySQLIntegrityConstraintViolationException but not keys duplicated
Hi all,
All our MySQL tables have composite primary keys (8 fields) and when we use sqoop for insert into them(from hive), we are getting this exception:
java.io.IOException: Can't export data, please check task tracker logs
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112)
at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:140)
at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:673)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:331)
at org.apache.hadoop.mapred.Child$4.run(Child.java:268)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:396)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1408)
at org.apache.hadoop.mapred.Child.main(Child.java:262)
Caused by: java.io.IOException: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '33-2013-02-13 23:00:00-Brasil-Play-None-None-None-None- Filmes for key 'PRIMARY
There are no duplicated keys in our Hive tables however. Is there any constraint related to the number of fields that can belong to a PK?
Thanks,
Zoraida.-
________________________________
Este mensaje se dirige exclusivamente a su destinatario. Puede consultar nuestra pol?tica de env?o y recepci?n de correo electr?nico en el enlace situado m?s abajo.
This message is intended exclusively for its addressee. We only send and receive email on the basis of the terms set out at:
http://www.tid.es/ES/PAGINAS/disclaimer.aspx
Re: MySQLIntegrityConstraintViolationException but not keys
duplicated
Posted by Jarek Jarcec Cecho <ja...@apache.org>.
Hi Zoraida,
would you mind sharing the entire Sqoop log generated with parameter --verbose not just the exception stack trace? I would like to check out entire mapreduce job execution. Also what Sqoop version are you using?
Jarcec
On Thu, Jul 04, 2013 at 02:07:59AM +0000, ZORAIDA HIDALGO SANCHEZ wrote:
> Hi Jarcec,
>
> Thanks for your response. I am afraid that the --verbose option does not
> give us more information:
>
> 13/07/04 04:04:16 ERROR mapreduce.AsyncSqlOutputFormat: Got exception in
> update thread:
> com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
> Duplicate entry '63-2013-02-27
> 00:00:00-Brasil-Play-SVoD-ODG-Unknown-Unknown-Unkn' for key 'PRIMARY'
> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
> at
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAcce
> ssorImpl.java:39)
> at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstru
> ctorAccessorImpl.java:27)
> at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
> at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
> at com.mysql.jdbc.Util.getInstance(Util.java:386)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1039)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
> at
> com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:212
> 7)
> at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1362)
> at
> org.apache.sqoop.mapreduce.AsyncSqlOutputFormat$AsyncSqlExecThread.run(Asyn
> cSqlOutputFormat.java:233)
>
>
> The reason why I am suggestion that PK is being truncated is because as
> you can see here: '63-2013-02-27
> 00:00:00-Brasil-Play-SVoD-ODG-Unknown-Unknown-Unkn', Unkn is not finished
> and that can be the reason of why are we getting this
> MySQLIntegrityConstraintViolationException. I have search on MySQL but I
> do not see any hint.
>
> Thanks again,
>
> Zoraida.-
>
> El 03/07/13 17:32, "Jarek Jarcec Cecho" <ja...@apache.org> escribió:
>
> >Hi Zoraida,
> >I don't think that there is any limitation on how many keys can be part
> >of primary key during export operation. Would you mind sharing with us
> >entire Sqoop log generated with parameter --verbose?
> >
> >Jarcec
> >
> >On Wed, Jul 03, 2013 at 08:49:07PM +0000, ZORAIDA HIDALGO SANCHEZ wrote:
> >> Hi all,
> >>
> >> All our MySQL tables have composite primary keys (8 fields) and when
> >>we use sqoop for insert into them(from hive), we are getting this
> >>exception:
> >> java.io.IOException: Can't export data, please check task tracker logs
> >> at
> >>org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112
> >>)
> >> at
> >>org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
> >> at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:140)
> >> at
> >>org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java
> >>:64)
> >> at
> >>org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:673)
> >> at org.apache.hadoop.mapred.MapTask.run(MapTask.java:331)
> >> at org.apache.hadoop.mapred.Child$4.run(Child.java:268)
> >> at java.security.AccessController.doPrivileged(Native Method)
> >> at javax.security.auth.Subject.doAs(Subject.java:396)
> >> at
> >>org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation
> >>.java:1408)
> >> at org.apache.hadoop.mapred.Child.main(Child.java:262)
> >> Caused by: java.io.IOException:
> >>com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationExceptio
> >>n: Duplicate entry '33-2013-02-13
> >>23:00:00-Brasil-Play-None-None-None-None- Filmes for key 'PRIMARY
> >>
> >> There are no duplicated keys in our Hive tables however. Is there any
> >>constraint related to the number of fields that can belong to a PK?
> >> Thanks,
> >>
> >> Zoraida.-
> >>
> >>
> >>
> >> ________________________________
> >>
> >> Este mensaje se dirige exclusivamente a su destinatario. Puede
> >>consultar nuestra pol?tica de env?o y recepci?n de correo electr?nico en
> >>el enlace situado m?s abajo.
> >> This message is intended exclusively for its addressee. We only send
> >>and receive email on the basis of the terms set out at:
> >> http://www.tid.es/ES/PAGINAS/disclaimer.aspx
>
>
> ________________________________
>
> Este mensaje se dirige exclusivamente a su destinatario. Puede consultar nuestra política de envío y recepción de correo electrónico en el enlace situado más abajo.
> This message is intended exclusively for its addressee. We only send and receive email on the basis of the terms set out at:
> http://www.tid.es/ES/PAGINAS/disclaimer.aspx
Re: MySQLIntegrityConstraintViolationException but not keys duplicated
Posted by ZORAIDA HIDALGO SANCHEZ <zo...@tid.es>.
Hi Jarcec,
Thanks for your response. I am afraid that the --verbose option does not
give us more information:
13/07/04 04:04:16 ERROR mapreduce.AsyncSqlOutputFormat: Got exception in
update thread:
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
Duplicate entry '63-2013-02-27
00:00:00-Brasil-Play-SVoD-ODG-Unknown-Unknown-Unkn' for key 'PRIMARY'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAcce
ssorImpl.java:39)
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstru
ctorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1039)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
at
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:212
7)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1362)
at
org.apache.sqoop.mapreduce.AsyncSqlOutputFormat$AsyncSqlExecThread.run(Asyn
cSqlOutputFormat.java:233)
The reason why I am suggestion that PK is being truncated is because as
you can see here: '63-2013-02-27
00:00:00-Brasil-Play-SVoD-ODG-Unknown-Unknown-Unkn', Unkn is not finished
and that can be the reason of why are we getting this
MySQLIntegrityConstraintViolationException. I have search on MySQL but I
do not see any hint.
Thanks again,
Zoraida.-
El 03/07/13 17:32, "Jarek Jarcec Cecho" <ja...@apache.org> escribió:
>Hi Zoraida,
>I don't think that there is any limitation on how many keys can be part
>of primary key during export operation. Would you mind sharing with us
>entire Sqoop log generated with parameter --verbose?
>
>Jarcec
>
>On Wed, Jul 03, 2013 at 08:49:07PM +0000, ZORAIDA HIDALGO SANCHEZ wrote:
>> Hi all,
>>
>> All our MySQL tables have composite primary keys (8 fields) and when
>>we use sqoop for insert into them(from hive), we are getting this
>>exception:
>> java.io.IOException: Can't export data, please check task tracker logs
>> at
>>org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112
>>)
>> at
>>org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
>> at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:140)
>> at
>>org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java
>>:64)
>> at
>>org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:673)
>> at org.apache.hadoop.mapred.MapTask.run(MapTask.java:331)
>> at org.apache.hadoop.mapred.Child$4.run(Child.java:268)
>> at java.security.AccessController.doPrivileged(Native Method)
>> at javax.security.auth.Subject.doAs(Subject.java:396)
>> at
>>org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation
>>.java:1408)
>> at org.apache.hadoop.mapred.Child.main(Child.java:262)
>> Caused by: java.io.IOException:
>>com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationExceptio
>>n: Duplicate entry '33-2013-02-13
>>23:00:00-Brasil-Play-None-None-None-None- Filmes for key 'PRIMARY
>>
>> There are no duplicated keys in our Hive tables however. Is there any
>>constraint related to the number of fields that can belong to a PK?
>> Thanks,
>>
>> Zoraida.-
>>
>>
>>
>> ________________________________
>>
>> Este mensaje se dirige exclusivamente a su destinatario. Puede
>>consultar nuestra pol?tica de env?o y recepci?n de correo electr?nico en
>>el enlace situado m?s abajo.
>> This message is intended exclusively for its addressee. We only send
>>and receive email on the basis of the terms set out at:
>> http://www.tid.es/ES/PAGINAS/disclaimer.aspx
________________________________
Este mensaje se dirige exclusivamente a su destinatario. Puede consultar nuestra política de envío y recepción de correo electrónico en el enlace situado más abajo.
This message is intended exclusively for its addressee. We only send and receive email on the basis of the terms set out at:
http://www.tid.es/ES/PAGINAS/disclaimer.aspx
Re: MySQLIntegrityConstraintViolationException but not keys
duplicated
Posted by Jarek Jarcec Cecho <ja...@apache.org>.
Hi Zoraida,
I don't think that there is any limitation on how many keys can be part of primary key during export operation. Would you mind sharing with us entire Sqoop log generated with parameter --verbose?
Jarcec
On Wed, Jul 03, 2013 at 08:49:07PM +0000, ZORAIDA HIDALGO SANCHEZ wrote:
> Hi all,
>
> All our MySQL tables have composite primary keys (8 fields) and when we use sqoop for insert into them(from hive), we are getting this exception:
> java.io.IOException: Can't export data, please check task tracker logs
> at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:112)
> at org.apache.sqoop.mapreduce.TextExportMapper.map(TextExportMapper.java:39)
> at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:140)
> at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
> at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:673)
> at org.apache.hadoop.mapred.MapTask.run(MapTask.java:331)
> at org.apache.hadoop.mapred.Child$4.run(Child.java:268)
> at java.security.AccessController.doPrivileged(Native Method)
> at javax.security.auth.Subject.doAs(Subject.java:396)
> at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1408)
> at org.apache.hadoop.mapred.Child.main(Child.java:262)
> Caused by: java.io.IOException: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '33-2013-02-13 23:00:00-Brasil-Play-None-None-None-None- Filmes for key 'PRIMARY
>
> There are no duplicated keys in our Hive tables however. Is there any constraint related to the number of fields that can belong to a PK?
> Thanks,
>
> Zoraida.-
>
>
>
> ________________________________
>
> Este mensaje se dirige exclusivamente a su destinatario. Puede consultar nuestra pol?tica de env?o y recepci?n de correo electr?nico en el enlace situado m?s abajo.
> This message is intended exclusively for its addressee. We only send and receive email on the basis of the terms set out at:
> http://www.tid.es/ES/PAGINAS/disclaimer.aspx