Hi Jonathan,
It should not be tinyint, because such type is present in mapping code.
I assume MySQL tinyint corresponds to JDBC java.sql.Types
public final static int TINYINT = -6;
I don't have MySQL so can't test directly.
Your table definition looks OK, nothing unusual and exotic.
So hard to predict which type is culprit.
But ExecuteSQL needs some modifications anyway
because currently it does not recognize some rarely used types.
For example VARBINARY, LONGVARBINARY, OTHER, JAVA_OBJECT, ARRAY, BLOB,
CLOB, etc.
How ExecuteSQL should handle type's which can't directly converted to Avro
types?
1. Always try to convert to String type? (we already convert some types to
String)
2. Throw error?
3. what else...?
Current mapping:
for (int i = 1; i <= nrOfColumns; i++) {
switch (meta.getColumnType(i)) {
case CHAR:
case LONGNVARCHAR:
case LONGVARCHAR:
case NCHAR:
case NVARCHAR:
case VARCHAR:
builder.name
(meta.getColumnName(i)).type().stringType().noDefault();
break;
case BOOLEAN:
builder.name
(meta.getColumnName(i)).type().booleanType().noDefault();
break;
case INTEGER:
case SMALLINT:
case TINYINT:
builder.name
(meta.getColumnName(i)).type().intType().noDefault();
break;
case BIGINT:
builder.name
(meta.getColumnName(i)).type().longType().noDefault();
break;
// java.sql.RowId is interface, is seems to be database
// implementation specific, let's convert to String
case ROWID:
builder.name
(meta.getColumnName(i)).type().stringType().noDefault();
break;
case FLOAT:
case REAL:
builder.name
(meta.getColumnName(i)).type().floatType().noDefault();
break;
case DOUBLE:
builder.name
(meta.getColumnName(i)).type().doubleType().noDefault();
break;
// Did not find direct suitable type, need to be
clarified!!!!
case DECIMAL:
case NUMERIC:
builder.name
(meta.getColumnName(i)).type().stringType().noDefault();
break;
// Did not find direct suitable type, need to be
clarified!!!!
case DATE:
case TIME:
case TIMESTAMP:
builder.name
(meta.getColumnName(i)).type().stringType().noDefault();
break;
default:
break;
}
toivo
2015-09-18 16:38 GMT+03:00 Jonathan Lyons <[email protected]>:
> Hi Toivo,
>
> I see. Thanks for the response and the diagnosis. Sorry I didn't post the
> table definition, on the off chance it helps resolve the issue:
>
> CREATE TABLE `users` (
>> `id` int(11) NOT NULL AUTO_INCREMENT,
>> `email` varchar(255) NOT NULL,
>> `password` varchar(255) DEFAULT NULL,
>> `activation_code` varchar(255) DEFAULT NULL,
>> `forgotten_password_code` varchar(255) DEFAULT NULL,
>> `forgotten_password_time` datetime DEFAULT NULL,
>> `created` datetime NOT NULL,
>> `active` tinyint(1) NOT NULL DEFAULT '0',
>> `home_module_id` int(11) DEFAULT NULL,
>> PRIMARY KEY (`id`),
>> UNIQUE KEY `email` (`email`),
>> KEY `home_module_id` (`home_module_id`),
>> CONSTRAINT `users_ibfk_1` FOREIGN KEY (`home_module_id`) REFERENCES
>> `modules` (`id`) ON DELETE SET NULL
>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
>
>
> Could it be 'tinyint?'
>
> On Thu, Sep 17, 2015 at 11:40 AM, Toivo Adams <[email protected]>
> wrote:
>
>> Hi,
>>
>>
>>
>> I was not able to reproduce problem using Derby.
>>
>>
>>
>> *static* String *createPersons* = "create table persons (id integer,
>> name varchar(100), code integer)";
>>
>>
>>
>> *final* String query = "select * from persons";
>>
>>
>>
>> Maybe some data type will cause error?
>>
>> Or is it related somehow how MySQL JDBC driver works?
>>
>>
>>
>> Wait, It’s seem to be bug in createSchema() which creates Arvo schema.
>>
>> When createSchema() will encounter unknown type, no Arvo column info will
>> be created. And Arvo schema has less columns than ResultSet row…
>>
>>
>>
>> I created ticket.
>>
>> https://issues.apache.org/jira/browse/NIFI-972
>>
>>
>>
>> Thanks
>>
>> Toivo
>>
>> 2015-09-17 18:10 GMT+03:00 Toivo Adams <[email protected]>:
>>
>>> Hi,
>>>
>>>
>>>
>>> No, JDBC metadata is used always.
>>>
>>>
>>>
>>> I’ll try to create Junit test to reproduce problem.
>>>
>>> I don’t have MySQL so I will use Derby for testing.
>>>
>>> Hopefully MySQL JDBC driver does not have specific different behavior.
>>>
>>>
>>>
>>> toivo
>>>
>>> 2015-09-17 16:48 GMT+03:00 Jonathan Lyons <[email protected]>:
>>>
>>>> Hi,
>>>>
>>>> Thanks for the response. Indeed it looks like changing the query from:
>>>>
>>>> SELECT * from users
>>>>
>>>>
>>>> to:
>>>>
>>>> SELECT id, email from users
>>>>
>>>>
>>>> causes it to start working. Does the JDBC metadata get dropped when
>>>> using the column wildcard?
>>>>
>>>> Jonathan
>>>>
>>>> On Sat, Sep 12, 2015 at 4:59 AM, Toivo Adams <[email protected]>
>>>> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>>
>>>>>
>>>>> ExecuteSQL generates Avro schema automatically using JDBC metadata
>>>>> from query result.
>>>>>
>>>>> It seems number of columns in generated Avro schema and in row from
>>>>> ResultSet is different.
>>>>>
>>>>>
>>>>>
>>>>> Probably bug in ExecuteSQL.
>>>>>
>>>>> Please can you share your SQL select query and database table
>>>>> definition?
>>>>>
>>>>> And maybe even some sample data which causes the problem?
>>>>>
>>>>>
>>>>>
>>>>> Thanks
>>>>>
>>>>> Toivo
>>>>>
>>>>>
>>>>>
>>>>> 2015-09-11 18:43 GMT+03:00 Jonathan Lyons <[email protected]>:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> Just getting started with NiFi here. I am attempting to run a static
>>>>>> query in MySQL using the ExecuteSQL processor. It is set to run on a 5
>>>>>> second interval. Since ExecuteSQL appears to need an input flow file I'm
>>>>>> using a GenerateFlowFile processor to produce a random file every 5
>>>>>> seconds. Unfortunately, I'm getting a very vague ArrayIndexOutOfBounds
>>>>>> exception when I hit play on the flow:
>>>>>>
>>>>>> java.lang.ArrayIndexOutOfBoundsException: 8
>>>>>>
>>>>>> at
>>>>>> org.apache.avro.generic.GenericData$Record.put(GenericData.java:129)
>>>>>>
>>>>>> at
>>>>>> org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream
>>>>>>
>>>>>> at
>>>>>> org.apache.nifi.processors.standard.ExecuteSQL$1.process(ExecuteSQL.java:141)
>>>>>> ~[na:na]
>>>>>>
>>>>>> Any idea why this is?
>>>>>>
>>>>>> Thanks,
>>>>>> Jonathan
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>
