Drill Through with MYSQL fails creatin tables


(Francisco Mendoza) #1

Hello,
Im currently trying to create my profile data into a MySQL data base but getting an error from the MySQL engine because the amount of data im trying to send exceeds MySQL limits, I noticed that DQA tries to create all the varchar fields on this drill through tables as “S_X varchar (400) NULL”, my table contains 86 rows plus all the additional rows added by DQA with varchar of 400 chars generates an error in the innodb engine that has a limit of 65535 bytes.
realistically I don’t need all the varchars of 400 chars, with a value of 50 would be more than enough for my business case, my question is: is there a way to reduce the varchar (400) to lets say varchar (80)??


(Honza Nguyen) #2

Hi Francisco,

for the further investigation, we’d require an entire error log. Could you please share it with us?


(Francisco Mendoza) #3

Hi Thanks for your reply,
here is the log I get:

com.ataccama.dqc.commons.sql.atc.WrappedSqlException: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
while executing CREATE TABLE BRA_GEN_PROF__da659a4f_0_0 (
S_0 datetime NULL,
S_1 varchar (400) NULL,
S_2 int NULL,
S_3 varchar (400) NULL,
S_4 varchar (400) NULL,
S_5 varchar (400) NULL,
S_6 varchar (400) NULL,
S_7 varchar (400) NULL,
S_8 varchar (400) NULL,
S_9 varchar (400) NULL,
S_10 varchar (400) NULL,
S_11 varchar (400) NULL,
S_12 varchar (400) NULL,
S_13 datetime NULL,
S_14 varchar (400) NULL,
S_15 datetime NULL,
S_16 varchar (400) NULL,
S_17 varchar (400) NULL,
S_18 varchar (400) NULL,
S_19 varchar (400) NULL,
S_20 varchar (400) NULL,
S_21 varchar (400) NULL,
S_22 varchar (400) NULL,
S_23 varchar (400) NULL,
S_24 varchar (400) NULL,
S_25 varchar (400) NULL,
S_26 varchar (400) NULL,
S_27 varchar (400) NULL,
S_28 varchar (400) NULL,
S_29 varchar (400) NULL,
S_30 varchar (400) NULL,
S_31 datetime NULL,
S_32 varchar (400) NULL,
S_33 varchar (400) NULL,
S_34 varchar (400) NULL,
S_35 varchar (400) NULL,
S_36 varchar (400) NULL,
S_37 varchar (400) NULL,
S_38 varchar (400) NULL,
S_39 datetime NULL,
S_40 varchar (400) NULL,
S_41 datetime NULL,
S_42 datetime NULL,
S_43 varchar (400) NULL,
S_44 varchar (400) NULL,
S_45 varchar (400) NULL,
S_46 varchar (400) NULL,
S_47 varchar (400) NULL,
S_48 varchar (400) NULL,
S_49 varchar (400) NULL,
S_50 varchar (400) NULL,
S_51 varchar (400) NULL,
S_52 varchar (400) NULL,
S_53 varchar (400) NULL,
S_54 varchar (400) NULL,
S_55 varchar (400) NULL,
S_56 datetime NULL,
S_57 varchar (400) NULL,
S_58 datetime NULL,
S_59 varchar (400) NULL,
S_60 varchar (400) NULL,
S_61 varchar (400) NULL,
S_62 varchar (400) NULL,
S_63 varchar (400) NULL,
S_64 varchar (400) NULL,
S_65 datetime NULL,
S_66 varchar (400) NULL,
S_67 varchar (400) NULL,
S_68 varchar (400) NULL,
S_69 int NULL,
S_70 varchar (400) NULL,
S_71 varchar (400) NULL,
S_72 varchar (400) NULL,
S_73 varchar (400) NULL,
E_74 varchar (400) NULL,
E_75 varchar (400) NULL,
E_76 varchar (400) NULL,
E_77 varchar (400) NULL,
E_78 varchar (400) NULL,
E_79 varchar (400) NULL,
E_80 varchar (400) NULL,
E_81 varchar (400) NULL,
E_82 varchar (400) NULL,
E_83 varchar (400) NULL,
E_84 varchar (400) NULL,
E_85 varchar (400) NULL,
E_86 varchar (400) NULL,
E_87 varchar (400) NULL,
E_88 varchar (400) NULL,
E_89 varchar (400) NULL,
E_90 varchar (400) NULL,
E_91 varchar (400) NULL,
E_92 varchar (400) NULL,
E_93 varchar (400) NULL,
E_94 varchar (400) NULL,
E_95 varchar (400) NULL,
E_96 varchar (400) NULL,
E_97 varchar (400) NULL,
E_98 varchar (400) NULL,
E_99 varchar (400) NULL,
E_100 varchar (400) NULL,
E_101 varchar (400) NULL,
E_102 varchar (400) NULL,
E_103 varchar (400) NULL,
E_104 varchar (400) NULL,
E_105 varchar (400) NULL,
E_106 varchar (400) NULL,
E_107 varchar (400) NULL,
E_108 varchar (400) NULL,
E_109 varchar (400) NULL,
E_110 varchar (400) NULL,
E_111 varchar (400) NULL,
E_112 varchar (400) NULL,
E_113 varchar (400) NULL,
E_114 varchar (400) NULL,
E_115 varchar (400) NULL,
E_116 varchar (400) NULL,
E_117 varchar (400) NULL,
E_118 varchar (400) NULL,
E_119 varchar (400) NULL,
E_120 varchar (400) NULL,
E_121 varchar (400) NULL,
E_122 varchar (400) NULL,
E_123 varchar (400) NULL,
E_124 varchar (400) NULL,
E_125 varchar (400) NULL,
E_126 varchar (400) NULL,
E_127 varchar (400) NULL,
E_128 varchar (400) NULL,
E_129 varchar (400) NULL,
E_130 varchar (400) NULL,
E_131 varchar (400) NULL,
E_132 varchar (400) NULL,
E_133 varchar (400) NULL,
E_134 varchar (400) NULL,
P_135 int NULL,
P_136 int NULL,
P_137 int NULL,
P_138 int NULL,
P_139 int NULL,
P_140 int NULL,
P_141 int NULL,
P_142 int NULL,
P_143 int NULL,
P_144 int NULL,
P_145 int NULL,
P_146 int NULL,
P_147 int NULL,
P_148 int NULL,
P_149 int NULL,
P_150 int NULL,
P_151 int NULL,
P_152 int NULL,
P_153 int NULL,
P_154 int NULL,
P_155 int NULL,
P_156 int NULL,
P_157 int NULL,
P_158 int NULL,
P_159 int NULL,
P_160 int NULL,
P_161 int NULL,
P_162 int NULL,
P_163 int NULL,
P_164 int NULL,
P_165 int NULL,
P_166 int NULL,
P_167 int NULL,
P_168 int NULL,
P_169 int NULL,
P_170 int NULL,
P_171 int NULL,
P_172 int NULL,
P_173 int NULL,
P_174 int NULL,
P_175 int NULL,
P_176 int NULL,
P_177 int NULL,
P_178 int NULL,
P_179 int NULL,
P_180 int NULL,
P_181 int NULL,
P_182 int NULL,
P_183 int NULL,
P_184 int NULL,
P_185 int NULL,
P_186 int NULL,
P_187 int NULL,
P_188 int NULL,
P_189 int NULL,
P_190 bigint NULL,
P_191 bigint NULL,
P_192 bigint NULL,
P_193 bigint NULL,
P_194 bigint NULL,
P_195 bigint NULL,
P_196 bigint NULL,
P_197 bigint NULL,
P_198 bigint NULL,
P_199 bigint NULL,
P_200 bigint NULL,
P_201 bigint NULL,
P_202 bigint NULL,
P_203 bigint NULL,
P_204 bigint NULL,
P_205 bigint NULL,
P_206 bigint NULL,
P_207 bigint NULL,
P_208 bigint NULL,
P_209 bigint NULL,
P_210 bigint NULL,
P_211 bigint NULL,
P_212 bigint NULL,
P_213 bigint NULL,
P_214 bigint NULL,
P_215 bigint NULL,
P_216 bigint NULL,
P_217 bigint NULL,
P_218 bigint NULL,
P_219 bigint NULL,
P_220 bigint NULL,
P_221 bigint NULL,
P_222 bigint NULL,
P_223 bigint NULL,
P_224 bigint NULL,
P_225 bigint NULL,
P_226 bigint NULL,
P_227 bigint NULL,
P_228 bigint NULL,
P_229 bigint NULL,
P_230 bigint NULL,
P_231 bigint NULL,
P_232 bigint NULL,
P_233 bigint NULL,
P_234 bigint NULL,
P_235 bigint NULL,
P_236 bigint NULL,
P_237 bigint NULL,
P_238 bigint NULL,
P_239 bigint NULL,
P_240 bigint NULL,
P_241 bigint NULL,
P_242 bigint NULL,
P_243 bigint NULL,
P_244 bigint NULL,
P_245 bigint NULL,
P_246 bigint NULL,
P_247 bigint NULL,
P_248 bigint NULL,
P_249 bigint NULL,
P_250 bigint NULL,
P_251 bigint NULL,
P_252 bigint NULL,
P_253 bigint NULL,
P_254 bigint NULL,
P_255 bigint NULL,
P_256 bigint NULL,
P_257 bigint NULL,
P_258 bigint NULL,
P_259 bigint NULL,
P_260 bigint NULL,
P_261 bigint NULL,
P_262 bigint NULL,
P_263 bigint NULL
)
at com.ataccama.dqc.commons.util.sql.SqlUtils.wrapException(y:1300)
at com.ataccama.dqc.internal.commons.sql.AtcStatement.executeUpdate(y:3315)
at com.ataccama.dqc.commons.sql.impl.ConnectionImplBase.executeUpdate(y:2005)
at com.ataccama.dqc.io.jdbc.Connector.A(y:3015)
at com.ataccama.dqc.io.jdbc.Connector.createTable(y:3486)
at com.ataccama.dqc.io.jdbc.Connector.createTable(y:3244)
at com.ataccama.dqc.tasks.profiling.common.DrillDownManager.createTable(y:1889)
at com.ataccama.dqc.tasks.profiling.runtime.RollUpWrap.processDrillDown(y:449)
at com.ataccama.dqc.tasks.profiling.ProfilingAlgorithmInstance$_B.B(y:1076)
at com.ataccama.dqc.tasks.profiling.ProfilingAlgorithmInstance$_B.C(y:815)
at com.ataccama.dqc.tasks.profiling.ProfilingAlgorithmInstance.run(y:1090)
at com.ataccama.dqc.processor.internal.runner.ComplexStepNode.runNode(y:202)
at com.ataccama.dqc.processor.internal.runner.B.run(y:2963)
at com.ataccama.dqc.commons.threads.A$_A.run(y:295)
at java.lang.Thread.run(Unknown Source)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2490)
at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1552)
at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2607)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1480)
at com.ataccama.dqc.commons.sql.decorator.StatementDecorator.executeUpdate(y:131)
at com.ataccama.dqc.internal.commons.sql.AtcStatement.executeUpdate(y:1372)
… 13 more
======== Potential cause ========
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2490)
at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1552)
at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2607)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1480)
at com.ataccama.dqc.commons.sql.decorator.StatementDecorator.executeUpdate(y:131)
at com.ataccama.dqc.internal.commons.sql.AtcStatement.executeUpdate(y:1372)
at com.ataccama.dqc.commons.sql.impl.ConnectionImplBase.executeUpdate(y:2005)
at com.ataccama.dqc.io.jdbc.Connector.A(y:3015)
at com.ataccama.dqc.io.jdbc.Connector.createTable(y:3486)
at com.ataccama.dqc.io.jdbc.Connector.createTable(y:3244)
at com.ataccama.dqc.tasks.profiling.common.DrillDownManager.createTable(y:1889)
at com.ataccama.dqc.tasks.profiling.runtime.RollUpWrap.processDrillDown(y:449)
at com.ataccama.dqc.tasks.profiling.ProfilingAlgorithmInstance$_B.B(y:1076)
at com.ataccama.dqc.tasks.profiling.ProfilingAlgorithmInstance$_B.C(y:815)
at com.ataccama.dqc.tasks.profiling.ProfilingAlgorithmInstance.run(y:1090)
at com.ataccama.dqc.processor.internal.runner.ComplexStepNode.runNode(y:202)
at com.ataccama.dqc.processor.internal.runner.B.run(y:2963)
at com.ataccama.dqc.commons.threads.A$_A.run(y:295)
at java.lang.Thread.run(Unknown Source)


the problem seems to be a limitation on MYSQL, I tried reconfiguring MySQL server but no success,
the problem is when attacama tries to create the table for results, since all the fields are varchar of 400 characters due to the number of collumns I have this fails, which is not the case with the Derby engine, but in my business case I need to use MySQL since my prod environment rely on MySQL and need to share results with an application server to show profiling results…

any suggestions to optimize the creation of this tables?


(Honza Nguyen) #4

Hello Francisco,

although InnoDB supports row sizes larger than 65,535 bytes internally, MySQL itself imposes a row-size limit of 65,535 for the combined size of all columns, hence the error - The maximum row size.
The profiling step will always create the columns with defined data types.
There is a workaround - split the input into several inputs and profile them separately. That way, you can overcome the limit when you profile a single input.