Itgenclr000 NullReferenceException

[sda]

Finaly I also tried this as described here: evaluate-native-sql-statements-from-invantive-psql :

My TEMP@inmemorystorage table is already created; containing few records (not in conflict in regards to ID (Key, unique) generated by the stmt below)

begin
for r in (execute native 'SELECT * FROM tbllog WHERE ID BETWEEN 1000 AND 2000' DATACONTAINER 'catserver')
loop
  insert into TEMP@InMemoryStorage
  (ID, BeginVerbruik, EindVerbruik, GastID, LocatieID, KaartNummer, Result, BeginDatumTijd, EindDatumTijd)
  values (r.ID, r.BeginVerbruik, r.EindVerbruik, r.GastID, r.LocatieID, r.KaartNummer, r.Result, r.BeginDatumTijd, r.EindDatumTijd);
end loop;
end;

but I get an error:

itgenclr000:
A required value is missing or a value is incorrectly assumed to be present always.

Tried Bulk insert also.

Also it seems that the LIMIT instruction as

SELECT * from tblog@catserver LIMIT 10

is not passed in the query to the container but rather being done at Invantive Query SQL side (my guess given the time of query execution).

All I am trying to achieve is to:

1. do a first synchronize from a source Mysql to destination SQLServer table with 1.3 millions of records.
2. then do a recurring scheduled scripted update of the destination table with only new created records in the source.

It seems that the source MySQL server cuts the payload to an approx 385K records on each request due to some limitations on the server (execution time, buffer size etc…) that I cannot change, not being under my hands. As a consequence step (1) needs to be sliced in batch of not more than 385K records.

[forums]

Does the error itgenclr000 occur on release 22.0 or on the BETA 22.1?

[sda]

Version : Query Tool 22.0.455 PROD+3713

[forums]

Can you please try again using release 22.0.480?

The problem could not be reproduced using:

create table TEMP@InMemoryStorage as select * from DB.tbllog

begin
for r in (execute native 'SELECT * FROM DB.tbllog WHERE ID BETWEEN 1000 AND 2000' DATACONTAINER 'MySql')
loop
  insert into TEMP@InMemoryStorage
  (ID, BeginVerbruik, EindVerbruik, GastID, LocatieID, KaartNummer, Result, BeginDatumTijd, EindDatumTijd)
  values (r.ID, r.BeginVerbruik, r.EindVerbruik, r.GastID, r.LocatieID, r.KaartNummer, r.Result, r.BeginDatumTijd, r.EindDatumTijd);
end loop;
end;

[sda]

Hi good morning,
the issue is still present on my environnement after upgrading to 22.0.480

[forums]

Please make sure the table tbllog exists in the data container with alias catserver. It might only be available in catrepl.

In the release 22.0.482 and later, a more appropriate error itgenclr121 will be shown:

itgenclr121
Invalid object name ‘tbllog’.

[sda]

Thanks, catserver container exists and works.

I tried another way by replacing the insert into that may cause some issues by a simple dbms_output.put_line:

select * from tbllog@catserver WHERE ID BETWEEN 1 AND 1000

begin
for r in (execute native 'SELECT * FROM tbllog WHERE ID BETWEEN 1 AND 1000' DATACONTAINER 'catserver')
loop
  dbms_output.put_line('Value n1: ' || r.ID);
  --insert into TEMP@InMemoryStorage
  --(ID, BeginVerbruik, EindVerbruik, GastID, LocatieID, KaartNummer, Result, BeginDatumTijd, EindDatumTijd)
  --values (r.ID, r.BeginVerbruik, r.EindVerbruik, r.GastID, r.LocatieID, r.KaartNummer, r.Result, r.BeginDatumTijd, r.EindDatumTijd);
end loop;
end;

1rst statement works fine and get results.

2nd statement gets itgenclr000

[sda]

I must add about a strange behaviour with the Mysql container.

with a simple select * from mytable@mysql I sometimes get the same itgenclr000 error.
Then I execute the statement a second time , then it works.

This appears when :

• accessing for the first time to a never query-ed table.
• sometimes when changing from one table to another table in the same Mysql container

[forums]

The problem with itgenclr000 will keep occurring in 22.0.480 in various ways due to the nature of the interim compilation. During compilation, a partial evaluation can be done based upon various characteristics. Best is to try again on the next release.

However, an itgenclr000 should not occur on select * from mytable@mysql.

Can you add the details (Open details in error window) in case it occurs again?

[sda]

Yes sure, here it is:

• now on Query Tool 22.0.482,
• exited Query Tool,
• launched Query Tool again, opened connections (2 containers, SQL Server : @catrepl and MySQL @catserver),
• executed first query:

create or replace table tblresultcodes@catrepl as select * from tblresultcodes@catserver;

with result OK (22 rows affected)

• executed second query:

create or replace table tbllegionella@catrepl as select * from tbllegionella@catserver;

Then error rises:

itgenclr000:
A required value is missing or a value is incorrectly assumed to be present always.
A required value is missing or a value is incorrectly assumed to be present always.
Message ID: 26e651cf-b776-4653-b771-63dcd16c0e20
Occurred (UTC): 04/01/2023 12:12:14
Retrieval object metadata for 'TBLLEGIONELLA’System.NullReferenceException à MySql.Data.MySqlClient.ISSchemaProvider.GetTable(String sql) à MySql.Data.MySqlClient.ISSchemaProvider.GetColumns(String restrictions) à MySql.Data.MySqlClient.ISSchemaProvider.GetSchemaInternal(String collection, String restrictions) à MySql.Data.MySqlClient.SchemaProvider.GetSchema(String collection, String restrictions) à MySql.Data.MySqlClient.MySqlConnection.GetSchema(String collectionName, String restrictionValues) à Invantive.Data.Providers.MySql.MySqlProvider.GetObjectMetadata(GlobalState owner, ExecutionOptions executionOptions, Connection connection, IEnumerable1 objectSpecifications, Boolean raiseErrorIfNotFound)— Fin de la trace de la pile à partir de l’emplacement précédent au niveau duquel l’exception a été levée — à System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() à Invantive.Basics.ErrorConstants.ThrowOneOrMoreExceptions(GlobalState owner, ExecutionOptions executionOptions, IEnumerable1 exceptions) à Invantive.Data.Providers.MySql.MySqlProvider.GetObjectMetadata(GlobalState owner, ExecutionOptions executionOptions, Connection connection, IEnumerable1 objectSpecifications, Boolean raiseErrorIfNotFound) à Invantive.Data.AnsiSqlProviderBase.GetObjectMetadata(GlobalState owner, ExecutionOptions executionOptions, ObjectSpecification objectSpecification, Boolean raiseErrorIfNotFound)— Fin de la trace de la pile à partir de l’emplacement précédent au niveau duquel l’exception a été levée — à System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() à Invantive.Data.AnsiSqlProviderBase.GetObjectMetadata(GlobalState owner, ExecutionOptions executionOptions, ObjectSpecification objectSpecification, Boolean raiseErrorIfNotFound)— Fin de la trace de la pile à partir de l’emplacement précédent au niveau duquel l’exception a été levée — à System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() à Invantive.Data.AnsiSqlProviderBase.GetObjectMetadata(GlobalState owner, ExecutionOptions executionOptions, ObjectSpecification objectSpecification, Boolean raiseErrorIfNotFound) à Invantive.Sql.V1.DataSourceOrFunctionTree.OnExecute(GlobalState owner, ExecutionOptions executionOptions, ParameterList parameters, ExpressionParser expressionParser) à Invantive.Sql.V1.QueryTree.Execute(GlobalState owner, ExecutionOptions executionOptions, ParameterList parameters, ExpressionParser expressionParser) à Invantive.Sql.V1.SelectStatementTree.OnExecute(GlobalState owner, ExecutionOptions executionOptions, ParameterList parameters, ExpressionParser expressionParser) à Invantive.Sql.V1.QueryTree.Execute(GlobalState owner, ExecutionOptions executionOptions, ParameterList parameters, ExpressionParser expressionParser) à Invantive.Sql.V1.DdlStatementTree.OnExecute(GlobalState owner, ExecutionOptions executionOptions, ParameterList parameters, ExpressionParser expressionParser) à Invantive.Sql.V1.QueryTree.Execute(GlobalState owner, ExecutionOptions executionOptions, ParameterList parameters, ExpressionParser expressionParser) à Invantive.Sql.V1.QueryPlan.ForcePlan(GlobalState owner, ExecutionOptions executionOptions) à Invantive.Sql.V1.QueryPlan.Fetch(GlobalState owner, ExecutionOptions executionOptions, String sqlStatement) à Invantive.Data.ConnectionManager.EG.L(QueryPlan ) à System.Linq.Enumerable.WhereSelectArrayIterator2.MoveNext() à System.Linq.Buffer1…ctor(IEnumerable1 source) à System.Linq.Enumerable.ToArray[TSource](IEnumerable1 source) à Invantive.Data.ConnectionManager.ExecuteAndCombineQueryPlansV1(GlobalState owner, ExecutionOptions executionOptions, QueryPlan queryPlans, String sqlStatement, ParameterList parameters) à Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)— Fin de la trace de la pile à partir de l’emplacement précédent au niveau duquel l’exception a été levée — à System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() à Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule) à Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)— Fin de la trace de la pile à partir de l’emplacement précédent au niveau duquel l’exception a été levée — à System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() à Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule) à Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters) à Invantive.Producer.Windows.Forms.QueryToolResults.AZQ(GlobalState , ExecutionOptions , IProgressNotifier , String , ParameterList , Boolean , Boolean , Boolean , Boolean ) dans c:\smoke\ws-22.0\Invantive.Producer\src\Invantive.Producer.Windows.Forms\QueryTool\QueryToolTraceViewModel.cs:ligne 17 à Invantive.Producer.Windows.Forms.QueryToolResults.UZQ(GlobalState , ExecutionOptions , String , ParameterList ) dans c:\smoke\ws-22.0\Invantive.Producer\src\Invantive.Producer.Windows.Forms\QueryTool\QueryToolDbmsOutputViewModel.cs:ligne 135

• executing again second query:

create or replace table tbllegionella@catrepl as select * from tbllegionella@catserver;

then it works (11 rows affected)

[sda]

I also tried the following with DATACONTAINER ‘catserver’ being mysql

Declare
  l_value varchar2;
begin
  execute native 'SELECT MAX(ID) FROM tbllog' into l_value DATACONTAINER 'catserver' ;
  dbms_output.put_line('Value: ' || l_value);
end;

in that case I get the error below.

I tried the same query on SqlServer, it works fine.

itgenase302: Data reader not set.
Message ID: 64672898-e79c-4488-8d78-f65e51382cf9Occurred (UTC): 04/01/2023 12:28:55Declare l_value varchar2;begin execute native ‘SELECT MAX(ID) FROM tbllog’ into l_value DATACONTAINER ‘catserver’ ; dbms_output.put_line('Value: ’ || l_value);end;ValidationException à Invantive.Basics.ValidationException…ctor(GlobalState owner, ExecutionOptions executionOptions, String messageCode, String errorMessage, String kindRequest, String localStackTrace, String nk, Exception innerException, Boolean inheritMessageCodeWhenPresent, Nullable1 uid, Boolean isRecoverable, String poolIdentityId, String url) à Invantive.Data.AnsiSqlProviderBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String loggingTableName) à Invantive.Sql.V1.PSqlParser.PassthroughSqlActionTableNative(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String sqlStatement, IDataProvider provider, ParameterList parameters, ISparseArray variablesRow, DatabaseColumn[] variablesInScope, DatabaseColumn[] intoVariables) à LambaExecutePSqlTree42(Closure , GlobalState , ExecutionOptions , IQueryStatePerExecution , ISparseArray , ISparseArray ) à Invantive.Sql.V1.PSqlStatementTree.<Fetch>d__29.MoveNext() à Invantive.Data.CompressedEnumerable1.d__10.MoveNext() à Invantive.Sql.V1.FirehoseResultSet.CX.MoveNext() à Invantive.Sql.V1.QueryPlan.WW.MoveNext() à Invantive.Data.ExtensionMethods.STR1.KTR.MoveNext() à System.Collections.Generic.List1…ctor(IEnumerable1 collection) à System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) à Invantive.Data.ConnectionManager.EG.L(QueryPlan ) à System.Linq.Enumerable.WhereSelectArrayIterator2.MoveNext() à System.Linq.Buffer1…ctor(IEnumerable1 source) à System.Linq.Enumerable.ToArray[TSource](IEnumerable1 source) à Invantive.Data.ConnectionManager.ExecuteAndCombineQueryPlansV1(GlobalState owner, ExecutionOptions executionOptions, QueryPlan queryPlans, String sqlStatement, ParameterList parameters) à Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule) à Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule) à Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters) à Invantive.Producer.Windows.Forms.QueryToolResults.AZQ(GlobalState , ExecutionOptions , IProgressNotifier , String , ParameterList , Boolean , Boolean , Boolean , Boolean ) à Invantive.Producer.Windows.Forms.QueryToolResults.UZQ(GlobalState , ExecutionOptions , String , ParameterList ) à Invantive.Producer.Windows.Forms.QueryToolResults.OB.G() à System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) à System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) à System.Threading.ThreadHelper.ThreadStart() à Invantive.Data.AnsiSqlProviderBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String loggingTableName)— Fin de la trace de la pile à partir de l’emplacement précédent au niveau duquel l’exception a été levée — à System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() à Invantive.Data.AnsiSqlProviderBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String loggingTableName) à Invantive.Sql.V1.PSqlParser.PassthroughSqlActionTableNative(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String sqlStatement, IDataProvider provider, ParameterList parameters, ISparseArray variablesRow, DatabaseColumn variablesInScope, DatabaseColumn intoVariables) à LambaExecutePSqlTree42(Closure , GlobalState , ExecutionOptions , IQueryStatePerExecution , ISparseArray , ISparseArray ) à Invantive.Sql.V1.PSqlStatementTree.d__29.MoveNext() à Invantive.Data.CompressedEnumerable1.<GetEnumerator>d__10.MoveNext() à Invantive.Sql.V1.FirehoseResultSet.CX.MoveNext() à Invantive.Sql.V1.QueryPlan.WW.MoveNext() à Invantive.Data.ExtensionMethods.STR1.KTR.MoveNext() à System.Collections.Generic.List1..ctor(IEnumerable1 collection) à System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) à Invantive.Data.ConnectionManager.EG.L(QueryPlan ) à System.Linq.Enumerable.WhereSelectArrayIterator2.MoveNext() à System.Linq.Buffer1..ctor(IEnumerable1 source) à System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source) à Invantive.Data.ConnectionManager.ExecuteAndCombineQueryPlansV1(GlobalState owner, ExecutionOptions executionOptions, QueryPlan queryPlans, String sqlStatement, ParameterList parameters) à Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)— Fin de la trace de la pile à partir de l’emplacement précédent au niveau duquel l’exception a été levée — à System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() à Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule) à Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)— Fin de la trace de la pile à partir de l’emplacement précédent au niveau duquel l’exception a été levée — à System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() à Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule) à Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters) à Invantive.Producer.Windows.Forms.QueryToolResults.AZQ(GlobalState , ExecutionOptions , IProgressNotifier , String , ParameterList , Boolean , Boolean , Boolean , Boolean ) dans c:\smoke\ws-22.0\Invantive.Producer\src\Invantive.Producer.Windows.Forms\QueryTool\QueryToolTraceViewModel.cs:ligne 17 à Invantive.Producer.Windows.Forms.QueryToolResults.UZQ(GlobalState , ExecutionOptions , String , ParameterList ) dans c:\smoke\ws-22.0\Invantive.Producer\src\Invantive.Producer.Windows.Forms\QueryTool\QueryToolDbmsOutputViewModel.cs:ligne 135

[forums]

What version of MySQL is it? Cq. when was it released?

The NullReferenceException error seems related to a bug in an ancient MySQL server version. Starting release 24.0, a check on MySQL-version will be done to ensure it is a version supported by the driver:

Engine	Version	Release date
MySQL	5.7.x	21 October 2015
MySQL	8.0.x	19 April 2018
MariaDB	10.2.x	18 April 2016
MariaDB	10.3.x	16 April 2017
MariaDB	10.4.x	9 November 2018
MariaDB	10.5.x	3 December 2019
MariaDB	10.6.x	26 April 2021
MariaDB	10.7.x	17 September 2021
MariaDB	10.8.x	21 September 2021
MariaDB	10.9.x	23 March 2022
MariaDB	10.10.x	21 June 2022
MariaDB	10.11.x	26 September 2022
MariaDB	11.0.x	27 December 2022

Further compatibility information can be found at:

• https://dev.mysql.com/doc/connector-net/en/connector-net-versions.html
• https://mariadb.com/kb/en/innodb-versions/

[sda]

The MySQL server version is 5.1.39. Seems out of band

[forums]

That probably explains the problem. The bug was solved in 2010 and MySQL server version 5.1.39 was released September 4, 2009.

The NullReferenceException boils up from a library maintained by MySQL (now Oracle Corp.) and this library only covers support for 5.7 and 8.0. There is no supported library available anymore that covers 5.1.

It is recommended to upgrade to a more recent MySQL version like 8.0.x.

[sda]

Hi,
after the upgrade of MySql to 8.0.18 from the supplier, the issue seems to have disappeared indeed. thanks