Struggling with stored procedures for Columstore after migration from InfiniDB

[Jared Karlow]

Hey All,

Running into a bit of an odd issue.  

We have an instance of Columnstore running that has replaced an ancient instance of community InfiniDB.  So far, it works great for pretty much everything - the only problem we're running into is with SSRS.  

We have a set of stored procedures called from SSRS over ODBC to populate data in reports.  Individually, every single one of these stored procedures runs in less than a second. When they are called over ODBC, however, they are incredibly slow, stalling in the processlist at 'copying to tmp table'.  

Even stranger is that this problem doesn't occur in our old community infinidb setup.  

I'm completely lost as to what could cause procedures that are individually fast to return differently simply because of how they are called, so I'm assuming I configured something wrong when setting up the new server instance, but I haven't the foggiest as to what.  

[David Thompson]

Is it possible for you to provide a simplified test case or the stored procedure code / schema? Are the source and destination tables both columnstore?

 

There could be any number of reasons and a lot has changed from the mysql front end version of infinidb to mariadb 10.1.

 

Also to clarify you mean that the procedures run ‘fast’ when run from the mysql client but ‘slow’ when the same call is made remotely over ODBC? 

 

What ODBC connector are you using too?

 

David.

--
You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.
To post to this group, send email to mariadb-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/ed2a8d00-c8f7-4cbf-ab69-af582c8aebcd%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jared Karlow]

The procedures, when run on their own, run in ~.75 seconds.  

When run as part of the report process(over ODBC), they take multiple minutes.  

The stored procedures are all read-only - they are reading static columnstore tables returning data via OBDC to SSRS and into a report.  There are two columnstore databases - one (automated_reports) with some generic data, and a separate one with state crash data.  I've attached the schemas for each of these databases.  The automated_reports tables are fairly small, the crash data tables are around ~13-18 million records each. 

Those two databases sit on one ubuntu server, called remotely over ODBC to generate reports in SSRS.  

Here's an example of one of the procs, there's around ~20 that are called for any particular report:

CREATE DEFINER=`cmisst`@`%` PROCEDURE `DRUG_crashes_by_county_ym4_part_1`(in inputyear int)
BEGIN
select
        county_id,
        county_name,
        case when t.total is null then 0 else t.total end as total,
        case when t.fatal is null then 0 else t.fatal end as fatal,
        case when t.injury is null then 0 else t.injury end as injury,
        case when t.no_injury is null then 0 else t.no_injury end as no_injury,
        case when t.interstate is null then 0 else t.interstate end as interstate,
        case when t.us_route is null then 0 else t.us_route end as us_route,
        case when t.state_route is null then 0 else t.state_route end as state_route,
        case when t.local_street is null then 0 else t.local_street end as local_street,
        case when t.drinking_involved is null then 0 else t.drinking_involved end as drinking_involved,
        case when t.drug_involved is null then 0 else t.drug_involved end as drug_involved,
        case when t.deer_involved is null then 0 else t.deer_involved end as deer_involved,
        case when t.persons_killed is null then 0 else t.persons_killed end as persons_killed,
        case when t.persons_injured is null then 0 else t.persons_injured end as persons_injured


from automated_reports.mi_county


left join(


        select
                county,
                count(distinct crash.id) as total,
                count(distinct(if(worst_injury_in_accident = 1, crash.id, null))) as fatal,
                count(distinct(if(worst_injury_in_accident in(2,3,4), crash.id, null))) as injury,
                count(distinct(if(worst_injury_in_accident = 5, crash.id, null))) as no_injury,
                count(distinct(if(highway_class in (1,4), crash.id, null))) as interstate,
                count(distinct(if(highway_class in (2,5), crash.id, null))) as us_route,
                count(distinct(if(highway_class in (3,6), crash.id, null))) as state_route,
                count(distinct(if(highway_class in (7,8,9), crash.id, null))) as local_street,
                count(distinct(if(crash_drinking = 1, crash.id, null))) as drinking_involved,
                count(distinct(if(crash_drug_use = 1, crash.id, null))) as drug_involved,
                count(distinct(if(crash_deer_involv_assoc = 1, crash.id, null))) as deer_involved,
                count(distinct(if(person_degree_of_injury = 1, person.id, null))) as persons_killed,
                count(distinct(if(person_degree_of_injury in (2,3,4), person.id, null))) as persons_injured


        from crash
        left join vehicle on crash.id = vehicle.crash_id
        left join person on person.vehicle_id = vehicle.id


        where accident_year = inputyear - 4 AND test_result_drug BETWEEN 600 AND 699 and traffic_unit_type = 1 and county IN (SELECT county_id FROM automated_reports.mi_county WHERE county_id between 1 and 42)




        group by county
) t on t.county=county_id


where county_id IN ( SELECT county_id FROM automated_reports.mi_county WHERE county_id between 1 and 42)
;

This procedure (while far from optomized) runs in .75 seconds when called via command line, but hangs for minutes when called as part of the report process on 'copying to tmp table'.  On the server we're migrating from (another ubuntu server running the old infinidb instance with the same schemas and procedures) the procedures also run quickly, but the report is completed in a matter of minutes, with most of the time going into the rendering on the SSRS side.  

Something is clearly happening; here's the output from top on the db server as the queries are (still) running:

top - 23:18:38 up 28 days, 12:15,  7 users,  load average: 2.22, 2.05, 1.99
Tasks: 207 total,   2 running, 205 sleeping,   0 stopped,   0 zombie
%Cpu(s): 43.2 us,  2.1 sy,  0.0 ni, 54.1 id,  0.0 wa,  0.0 hi,  0.6 si,  0.0 st
KiB Mem : 16432536 total,   240132 free,  7825688 used,  8366716 buff/cache
KiB Swap: 16777212 total,  8899612 free,  7877600 used.  8083120 avail Mem


  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
30923 root      19  -1 11.187g 4.509g   8528 S  89.7 28.8 629:59.00 PrimProc
30668 mysql     20   0 6225392 1.790g  11696 S  83.7 11.4   1142:06 mysqld
30993 root      19  -1 2451628 948460   7620 S   8.6  5.8 109:48.38 ExeMgr
30783 root      20   0 1599016  10816   4056 S   0.7  0.1   7:29.56 controller+
    7 root      20   0       0      0      0 S   0.3  0.0  20:49.94 rcu_sched
  415 root      20   0   44056  10608   8320 S   0.3  0.1   3:13.15 systemd-jo+
 1087 syslog    20   0  256396   3348   1896 S   0.3  0.0   0:46.27 rsyslogd

Here's the running process status:

MariaDB [mi_04_15_vin2016_d]> show full processlist;
+-------+--------+-----------------------+--------------------+---------+------+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| Id    | User   | Host                  | db                 | Command | Time | State                | Info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | Progress |
+-------+--------+-----------------------+--------------------+---------+------+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| 17608 | root   | localhost             | mi_04_15_vin2016_d | Query   |    0 | init                 | show full processlist                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |    0.000 |
| 18314 | cmisst | 141.213.173.210:49910 | mi_04_15_vin2016_d | Execute |   64 | Copying to tmp table | SELECT
        time_period_name,
        case when t.crashes_ym4 is null then 0 else t.crashes_ym4 end as crashes_ym4,
        case when t.fatal_ym4 is null then 0 else t.fatal_ym4 end as fatal_ym4,
        case when t.crashes_ym3 is null then 0 else t.crashes_ym3 end as crashes_ym3,
        case when t.fatal_ym3 is null then 0 else t.fatal_ym3 end as fatal_ym3,
        case when t.crashes_ym2 is null then 0 else t.crashes_ym2 end as crashes_ym2,
        case when t.fatal_ym2 is null then 0 else t.fatal_ym2 end as fatal_ym2,
        case when t.crashes_ym1 is null then 0 else t.crashes_ym1 end as crashes_ym1,
        case when t.fatal_ym1 is null then 0 else t.fatal_ym1 end as fatal_ym1,
        case when t.crashes_y is null then 0 else t.crashes_y end as crashes_y,
        case when t.fatal_y is null then 0 else t.fatal_y end as fatal_y
FROM automated_reports.time_period_list


left join(
        select
                CASE
                        WHEN time_of_day IN (0,1,2) THEN 1
                        WHEN time_of_day IN (3,4,5) THEN 2
                        WHEN time_of_day IN (6,7,8) THEN 3
                        WHEN time_of_day IN (9,10,11) THEN 4
                        WHEN time_of_day IN (12,13,14) THEN 5
                        WHEN time_of_day IN (15,16,17) THEN 6
                        WHEN time_of_day IN (18,19,20) THEN 7
                        WHEN time_of_day IN (21,22,23) THEN 8
                        ELSE 9
                        END AS time_period,
                sum(case when accident_year= ( NAME_CONST('inputyear',2011)-4) then 1 else 0 end) AS crashes_ym4,
                sum(case when accident_year= ( NAME_CONST('inputyear',2011)-4) and worst_injury_in_accident= 1 then 1 else 0 end) AS fatal_ym4,
                sum(case when accident_year= ( NAME_CONST('inputyear',2011)-3) then 1 else 0 end) AS crashes_ym3,
                sum(case when accident_year= ( NAME_CONST('inputyear',2011)-3) and worst_injury_in_accident= 1 then 1 else 0 end) AS fatal_ym3,
                sum(case when accident_year= ( NAME_CONST('inputyear',2011)-2) then 1 else 0 end) AS crashes_ym2,
                sum(case when accident_year= ( NAME_CONST('inputyear',2011)-2) and worst_injury_in_accident= 1 then 1 else 0 end) AS fatal_ym2,
                sum(case when accident_year= ( NAME_CONST('inputyear',2011)-1) then 1 else 0 end) AS crashes_ym1,
                sum(case when accident_year= ( NAME_CONST('inputyear',2011)-1) and worst_injury_in_accident= 1 then 1 else 0 end) AS fatal_ym1,
                sum(case when accident_year=  NAME_CONST('inputyear',2011) then 1 else 0 end) AS crashes_y,
                sum(case when accident_year=  NAME_CONST('inputyear',2011) and worst_injury_in_accident= 1 then 1 else 0 end) AS fatal_y


        from crash
        join vehicle on crash.id = vehicle.crash_id
        WHERE crash_deer_involv_assoc= 1 and test_result_drug BETWEEN 600 AND 699 and traffic_unit_type = 1
        group by CASE
                WHEN time_of_day IN (0,1,2) THEN 1
                WHEN time_of_day IN (3,4,5) THEN 2
                WHEN time_of_day IN (6,7,8) THEN 3
                WHEN time_of_day IN (9,10,11) THEN 4
                WHEN time_of_day IN (12,13,14) THEN 5
                WHEN time_of_day IN (15,16,17) THEN 6
                WHEN time_of_day IN (18,19,20) THEN 7
                WHEN time_of_day IN (21,22,23) THEN 8
                ELSE 9
                END
) t on t.time_period=time_period_id


UNION ALL


        select
                'Fake Total' as time_period_name,
                0 as crashes_ym4,
                0 as fatal_ym4,
                0 as crashes_ym3,
                0 as fatal_ym3,
                0 as crashes_ym2,
                0 as fatal_ym2,
                0 as crashes_ym1,
                0 as fatal_ym1,
                0 as crashes_y,
                0 as fatal_y


order by field(
        time_period_name,
        'Midnight - 2:59 AM',
        '3:00 AM - 5:59 AM',
        '6:00 AM - 8:59 AM',
        '9:00 AM - 11:59 AM',
        'Noon - 2:59 PM',
        '3:00 PM - 5:59 PM',
        '6:00 PM - 8:59 PM',
        '9:00 PM - 11:59 PM',
        'Unknown',
        'Fake Total') |    0.000 |
+-------+--------+-----------------------+--------------------+---------+------+----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+

It does eventually finish; it does take forever though.  For what it's worth, I started a test run on a report when I first posted to here, and it's still not yet completed.  It finished on the original server in under ~5 minutes. 

On Tuesday, March 28, 2017 at 8:30:15 PM UTC-4, David Thompson wrote:

Is it possible for you to provide a simplified test case or the stored procedure code / schema? Are the source and destination tables both columnstore?

 

There could be any number of reasons and a lot has changed from the mysql front end version of infinidb to mariadb 10.1.

 

Also to clarify you mean that the procedures run ‘fast’ when run from the mysql client but ‘slow’ when the same call is made remotely over ODBC? 

 

What ODBC connector are you using too?

 

David.

 

From: mariadb-c...@googlegroups.com [mailto:mariadb-c...@googlegroups.com] On Behalf Of Jared Karlow
Sent: Tuesday, March 28, 2017 12:32 PM
To: MariaDB ColumnStore <mariadb-c...@googlegroups.com>
Subject: Struggling with stored procedures for Columstore after migration from InfiniDB

 

Hey All,

 

Running into a bit of an odd issue.  

 

We have an instance of Columnstore running that has replaced an ancient instance of community InfiniDB.  So far, it works great for pretty much everything - the only problem we're running into is with SSRS.  

 

We have a set of stored procedures called from SSRS over ODBC to populate data in reports.  Individually, every single one of these stored procedures runs in less than a second. When they are called over ODBC, however, they are incredibly slow, stalling in the processlist at 'copying to tmp table'.  

 

Even stranger is that this problem doesn't occur in our old community infinidb setup.  

 

I'm completely lost as to what could cause procedures that are individually fast to return differently simply because of how they are called, so I'm assuming I configured something wrong when setting up the new server instance, but I haven't the foggiest as to what.  

--
You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columnstore+unsub...@googlegroups.com.

[David Thompson]

I’m not able to reproduce locally with a similar test case (basically comparing a straight select over ODBC vs a stored proc containing that select over ODBC).

 

It’s possible that this is still environmental somehow so here are some thoughts on trying to narrow down where the issue is:

• I assume the infinidb server and columnstore servers are on different physical servers?
• Can you compare running the stored procedure vs embedded query over ODBC from the windows machine? This would help me determine if it’s really the stored procedure packaging which I don’t think it is.
• Can you trying using JDBC from the same machine, e.g. with squirrel. Does this behave the same way?
• What exact odbc driver are you using in each case?
• Are you using a hostname or ip address in the odbc connection? If using a hostname try ip.
• I assume it is the same windows client machine in each case?
• What is the ping time to both machines from the windows client?

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.

To post to this group, send email to mariadb-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/ed2a8d00-c8f7-4cbf-ab69-af582c8aebcd%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.

To post to this group, send email to mariadb-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/c09daba3-7251-4837-afc7-0e85272c0b52%40googlegroups.com.

[Jared Karlow]

Some quick answers while I run the other tests -

The infinidb server and columnstore servers are different VMs operating in the same VM environment.  They happen to be provisioned on different physical servers, but are provisioned with equivalent resources.  They are addressed using DNS; though changing to use the IP yields the same results.  

Ping time to both servers is ~1-3 ms from the windows clients - i've been testing from various windows clients (both the same in each case) through various network connections both on-site and remote with the same results.   

The MariaDB ODBC driver I was using was 3.0, the mysql driver version is 5.3.7 - both have the same results.  

Testing the other query stuff - will reply shortly.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columnstore+unsub...@googlegroups.com.

To post to this group, send email to mariadb-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/ed2a8d00-c8f7-4cbf-ab69-af582c8aebcd%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columnstore+unsub...@googlegroups.com.

[Jared Karlow]

Also, for what it's worth, we have a ubuntu webserver that sends similar queries to the MariaDB server using PHP/Mysqli without any issues.  I threw together a test script to call stored procs over Mysqli as well with no issues.

        case when t.persons_injured is null then 0</s

[Jared Karlow]

May not make a difference, but when calling the procedures from the windows client using a direct connection as well, I also am not seeing the query delays (i.e. using mysql workbench).  Still throwing together a non-SRSS test, will have the results for that shortly.

[David Thompson]

Well that’s good in that it narrows it down to ODBC or SRSS I think..

--

You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.

To post to this group, send email to mariadb-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/36e637af-701e-45a5-b3e9-d85c74c6bd94%40googlegroups.com.

[David Thompson]

Can you install wireshark on the windows client and capture network traffic driven by SSRs to see if that highlights any particular protocol operations as being slow or different between infinidb and columnstore?

 

From: mariadb-c...@googlegroups.com [mailto:mariadb-c...@googlegroups.com] On Behalf Of Jared Karlow

Sent: Wednesday, March 29, 2017 11:05 AM

--

You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.

To post to this group, send email to mariadb-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/36e637af-701e-45a5-b3e9-d85c74c6bd94%40googlegroups.com.

[Jared Karlow]

Tried replacing the stored proc contents with the query text and got an instant result.  Whatever the problem is, it's specifically a stored procedure call over SSRS as the identical manual query works instantly where the stored procedure call takes multiple minutes to return a result.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columnstore+unsub...@googlegroups.com.

[Jared Karlow]

Will do on wireshark.  I'll update when I have a result there. 

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columnstore+unsub...@googlegroups.com.

[Jared Karlow]

Wireshark conversation start from MariaDB server:

V

...SELECT SPECIFIC_SCHEMA AS PROCEDURE_CAT, NULL AS PROCEDURE_SCHEM, SPECIFIC_NAME PROCEDURE_NAME, PARAMETER_NAME COLUMN_NAME, CASE PARAMETER_MODE   WHEN 'IN' THEN 1  WHEN 'OUT' THEN 4  WHEN 'INOUT' THEN 2  ELSE IF(PARAMETER_MODE IS NULL, 5, 0)END COLUMN_TYPE, CASE DATA_TYPE  WHEN 'bit' THEN @dt:= IF(NUMERIC_PRECISION=1,(-7), (-2))  WHEN 'tinyint' THEN @dt:=(-6)  WHEN 'smallint' THEN @dt:=5  WHEN 'year' THEN 5  WHEN 'mediumint' THEN @dt:=4  WHEN 'int' THEN @dt:=4  WHEN 'bigint' THEN @dt:=(-5)  WHEN 'blob' THEN @dt:=(-4)  WHEN 'tinyblob' THEN @dt:=(-4)  WHEN 'mediumblob' THEN @dt:=(-4)  WHEN 'longblob' THEN @dt:=(-4)  WHEN 'text' THEN @dt:=(-1)  WHEN 'tinytext' THEN @dt:=(-1)  WHEN 'mediumtext' THEN @dt:=(-1)  WHEN 'longtext' THEN @dt:=(-1)  WHEN 'decimal' THEN @dt:=3  WHEN 'float' THEN @dt:=7  WHEN 'double' THEN @dt:=8  WHEN 'binary' THEN @dt:=(-2)  WHEN 'varbinary' THEN @dt:=(-3)  WHEN 'char' THEN @dt:=1  WHEN 'enum' THEN @dt:=1  WHEN 'set' THEN @dt:=1  WHEN 'varchar' THEN @dt:=12  WHEN 'date' THEN @dt:=91  WHEN 'time' THEN @dt:=92  WHEN 'datetime' THEN @dt:=93  WHEN 'timestamp' THEN @dt:=93  ELSE @dt:=(-4)END AS DATA_TYPE, DATA_TYPE TYPE_NAME, CASE  WHEN DATA_TYPE = 'bit' THEN @ColSize:=CAST(((NUMERIC_PRECISION + 7) / 8) AS SIGNED)   WHEN DATA_TYPE in ('tinyint', 'smallint', 'year', 'mediumint', 'int','bigint', 'decimal', 'double') THEN @ColSize:=NUMERIC_PRECISION   WHEN DATA_TYPE = 'float' THEN @ColSize:=7  WHEN DATA_TYPE='date' THEN @ColSize:=10  WHEN DATA_TYPE='time' THEN @ColSize:=8  WHEN DATA_TYPE in ('timestamp', 'datetime') THEN @ColSize:=19   ELSE @ColSize:=CHARACTER_MAXIMUM_LENGTH END  AS COLUMN_SIZE, CAST(CASE @dt  WHEN (-7) THEN 1   WHEN (-6) THEN 1   WHEN 5 THEN 2   WHEN 4 THEN IF(DATA_TYPE='mediumint',3,4)   WHEN (-5) THEN 20   WHEN 7 THEN 4   WHEN 6 THEN 8   WHEN 8 THEN 8   WHEN 91 THEN 6   WHEN 92 THEN 6   WHEN 93 THEN 16   WHEN (-11) THEN 16   WHEN 3 THEN @ColSize + 2   WHEN (-2) THEN IF(DATA_TYPE='bit', CAST(((NUMERIC_PRECISION + 7) / 8) AS SIGNED), CHARACTER_OCTET_LENGTH)   ELSE CHARACTER_OCTET_LENGTH END  AS SIGNED) AS BUFFER_LENGTH, NUMERIC_SCALE DECIMAL_DIGITS, 10 NUM_PREC_RADIX,2 NULLABLE,NULL REMARKS, NULL COLUMN_DEF,CASE  WHEN DATA_TYPE = 'date' THEN 9  WHEN DATA_TYPE = 'time' THEN 10  WHEN DATA_TYPE = 'datetime' THEN 9  WHEN DATA_TYPE = 'timestamp' THEN 11 ELSE @dt END AS SQL_DATA_TYPE, NULL SQL_DATETIME_SUB,CHARACTER_OCTET_LENGTH CHAR_OCTET_LENGTH, ORDINAL_POSITION, 'YES' IS_NULLABLE FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_SCHEMA LIKE DATABASE() AND SPECIFIC_NAME LIKE 'call DRUG_5yt_weekday_weekend(2011);'  ORDER BY SPECIFIC_SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION................X....def.information_schema

PARAMETERS

PARAMETERS

PROCEDURE_CAT.SPECIFIC_SCHEMA.!...........%....def....PROCEDURE_SCHEM..?...........W....def.information_schema

PARAMETERS

PARAMETERS.PROCEDURE_NAME

SPECIFIC_NAME.!...........U....def.information_schema

PARAMETERS

PARAMETERS.COLUMN_NAME.PARAMETER_NAME.!...........!....def....COLUMN_TYPE..?................def...      DATA_TYPE..?...........N....def.information_schema

PARAMETERS

PARAMETERS      TYPE_NAME       DATA_TYPE.!...........!..       .def....COLUMN_SIZE..?...........#..

.def...

BUFFER_LENGTH..?...........W....def.information_schema

PARAMETERS

PARAMETERS.DECIMAL_DIGITS

NUMERIC_SCALE.?...........$....def....NUM_PREC_RADIX..?..............

.def....NULLABLE..?................def....REMARKS..?........... ....def...

COLUMN_DEF..?...........#....def...

SQL_DATA_TYPE..!...........&....def....SQL_DATETIME_SUB..?...........c....def.information_schema

PARAMETERS

PARAMETERS.CHAR_OCTET_LENGTH.CHARACTER_OCTET_LENGTH.?...........\....def.information_schema

PARAMETERS

PARAMETERS.ORDINAL_POSITION.ORDINAL_POSITION.?...........!....def....IS_NULLABLE..!.    ..................

..................X....def.information_schema

PARAMETERS

PARAMETERS

PROCEDURE_CAT.SPECIFIC_SCHEMA.!...........%....def....PROCEDURE_SCHEM..?...........W....def.information_schema

PARAMETERS

PARAMETERS.PROCEDURE_NAME

SPECIFIC_NAME.!...........U....def.information_schema

PARAMETERS

PARAMETERS.COLUMN_NAME.PARAMETER_NAME.!...........!....def....COLUMN_TYPE..?................def...      DATA_TYPE..?...........N....def.information_schema

PARAMETERS

PARAMETERS      TYPE_NAME       DATA_TYPE.!...........!..       .def....COLUMN_SIZE..?...........#..

.def...

BUFFER_LENGTH..?...........W....def.information_schema

PARAMETERS

PARAMETERS.DECIMAL_DIGITS

NUMERIC_SCALE.?...........$....def....NUM_PREC_RADIX..?..............

.def....NULLABLE..?................def....REMARKS..?........... ....def...

COLUMN_DEF..?...........#....def...

SQL_DATA_TYPE..!...........&....def....SQL_DATETIME_SUB..?...........c....def.information_schema

PARAMETERS

PARAMETERS.CHAR_OCTET_LENGTH.CHARACTER_OCTET_LENGTH.?...........\....def.information_schema

PARAMETERS

PARAMETERS.ORDINAL_POSITION.ORDINAL_POSITION.?...........!....def....IS_NULLABLE..!.    ................"........"..............................%....call DRUG_5yt_weekday_weekend(2011);................

.............

Old server (mysql ODBC):

.....SELECT name, CONCAT(IF(length(returns)>0, CONCAT('RETURN_VALUE ', returns, if(length(param_list)>0, ',', '')),''), param_list),db, type FROM mysql.proc WHERE Db=DATABASE() AND name LIKE 'DRUG_5yt_weekday_weekend(2011);' ORDER BY Db, name.....+....def.mysql.proc.proc.name.name.!.......@........def...qCONCAT(IF(length(returns)>0, CONCAT('RETURN_VALUE ', returns, if(length(param_list)>0, ',', '')),''), param_list)..?...........'....def.mysql.proc.proc.db.db.S.......@...+....def.mysql.proc.proc.type.type.!.......Q..........................set @@sql_select_limit=DEFAULT...........7....call automated_reports.DRUG_5yt_weekday_weekend(2011);.....M....def.infinidb_vtable.$vtable_6893.$vtable_6893.day_type.day_type...@.........S....def.infinidb_vtable.$vtable_6893.$vtable_6893.crashes_ym4.crashes_ym4.?...........S....def.infinidb_vtable.$vtable_6893.$vtable_6893.crashes_ym3.crashes_ym3.?...........S....def.infinidb_vtable.$vtable_6893.$vtable_6893.crashes_ym2.crashes_ym2.?...........S....def.infinidb_vtable.$vtable_6893.$vtable_6893.crashes_ym1.crashes_ym1.?...........O....def.infinidb_vtable.$vtable_6893.$vtable_6893      crashes_y       crashes_y.?...........O....def.infinidb_vtable.$vtable_6893.$vtable_6893        fatal_ym4       fatal_ym4.?...........O..       .def.infinidb_vtable.$vtable_6893.$vtable_6893  fatal_ym3       fatal_ym3.?...........O..

.def.infinidb_vtable.$vtable_6893.$vtable_6893  fatal_ym2       fatal_ym2.?...........O....def.infinidb_vtable.$vtable_6893.$vtable_6893        fatal_ym1       fatal_ym1.?...........K....def.infinidb_vtable.$vtable_6893.$vtable_6893.fatal_y.fatal_y.?..............

...".&....Weekday.19.33.20.55.42.18.33.20.54.37&....Weekend.12.13.20.29.26.12.13.18.29.26.......".

For what its worth, the requests before the stored proc call happen basically instantly, the delay happens after the 'call' packet goes out. 

[David Thompson]

Do you have the wireshark files available, it might be easier for me to compare?

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.

To post to this group, send email to mariadb-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/36e637af-701e-45a5-b3e9-d85c74c6bd94%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.

To post to this group, send email to mariadb-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/0e2b4c35-2d3a-4ff8-8d1f-52f90e6410d9%40googlegroups.com.

[Jared Karlow]

That would have made more sense.  

I've attached them here.  

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columnstore+unsub...@googlegroups.com.

To post to this group, send email to mariadb-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/36e637af-701e-45a5-b3e9-d85c74c6bd94%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columnstore+unsub...@googlegroups.com.

[David Thompson]

Can you check /var/log/mariadb/columnstore/debug.log for around the time of the stored proc call.  Do you see something like:

Mar 29 14:37:51 centos ExeMgr[112782]: 51.763501 |127|0|0| D 16 CAL0041: Start SQL statement: ; |test|

Mar 29 14:37:51 centos ExeMgr[112782]: 51.779998 |127|0|0| D 16 CAL0042: End SQL statement

 

A number of times, if so then I’ve reproduced what you are seeing. Not sure why I couldn’t before.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.

To post to this group, send email to mariadb-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/36e637af-701e-45a5-b3e9-d85c74c6bd94%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.

To post to this group, send email to mariadb-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/0e2b4c35-2d3a-4ff8-8d1f-52f90e6410d9%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.

To post to this group, send email to mariadb-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/a612b800-58af-4fc6-8e4b-f64288a65a5d%40googlegroups.com.

[Jared Karlow]

Yeah, here's what I'm seeing:

Mar 29 19:20:39 cmisst-db ExeMgr[30993]: 39.030925 |18990|0|0| D 16 CAL0042: End SQL statement

Mar 29 19:20:39 cmisst-db messagequeue[30993]: 39.032004 |0|0|0| W 31 CAL0071: InetStreamSocket::read: EOF during readToMagic: socket read error: Success; InetStreamSocket: sd: 27 inet: 127.0.0.1 port: 37664; Will retry.

Mar 29 19:20:39 cmisst-db ExeMgr[30993]: 39.032173 |18990|0|0| D 16 CAL0041: Start SQL statement: ; |mi_04_15_vin2016_d|

Mar 29 19:20:45 cmisst-db messagequeue[30993]: 45.951424 |0|0|0| W 31 CAL0071: InetStreamSocket::read: EOF during readToMagic: socket read error: Success; InetStreamSocket: sd: 25 inet: 127.0.0.1 port: 37694; Will retry.

Mar 29 19:20:45 cmisst-db ExeMgr[30993]: 45.951648 |18990|0|0| D 16 CAL0042: End SQL statement

Mar 29 19:20:45 cmisst-db messagequeue[30993]: 45.952008 |0|0|0| W 31 CAL0071: InetStreamSocket::read: EOF during readToMagic: socket read error: Success; InetStreamSocket: sd: 25 inet: 127.0.0.1 port: 37694; Will retry.

Mar 29 19:20:45 cmisst-db ExeMgr[30993]: 45.953131 |18990|0|0| D 16 CAL0041: Start SQL statement: ; |mi_04_15_vin2016_d|

Mar 29 19:20:46 cmisst-db messagequeue[30993]: 46.763050 |0|0|0| W 31 CAL0071: InetStreamSocket::read: EOF during readToMagic: socket read error: Success; InetStreamSocket: sd: 18 inet: 127.0.0.1 port: 37696; Will retry.

Mar 29 19:20:46 cmisst-db ExeMgr[30993]: 46.763746 |18990|0|0| D 16 CAL0042: End SQL statement

Mar 29 19:20:46 cmisst-db messagequeue[30993]: 46.764464 |0|0|0| W 31 CAL0071: InetStreamSocket::read: EOF during readToMagic: socket read error: Success; InetStreamSocket: sd: 18 inet: 127.0.0.1 port: 37696; Will retry.

Mar 29 19:20:46 cmisst-db ExeMgr[30993]: 46.765196 |18990|0|0| D 16 CAL0041: Start SQL statement: ; |mi_04_15_vin2016_d|

Mar 29 19:20:55 cmisst-db messagequeue[30993]: 55.408719 |0|0|0| W 31 CAL0071: InetStreamSocket::read: EOF during readToMagic: socket read error: Success; InetStreamSocket: sd: 28 inet: 127.0.0.1 port: 37728; Will retry.

Mar 29 19:20:55 cmisst-db ExeMgr[30993]: 55.409249 |18990|0|0| D 16 CAL0042: End SQL statement

Mar 29 19:20:55 cmisst-db ExeMgr[30993]: 55.409719 |18990|0|0| D 16 CAL0041: Start SQL statement: ; |mi_04_15_vin2016_d|

Mar 29 19:20:55 cmisst-db messagequeue[30993]: 55.410136 |0|0|0| W 31 CAL0071: InetStreamSocket::read: EOF during readToMagic: socket read error: Success; InetStreamSocket: sd: 28 inet: 127.0.0.1 port: 37728; Will retry.

Mar 29 19:20:56 cmisst-db messagequeue[30993]: 56.255417 |0|0|0| W 31 CAL0071: InetStreamSocket::read: EOF during readToMagic: socket read error: Success; InetStreamSocket: sd: 27 inet: 127.0.0.1 port: 37738; Will retry.

Mar 29 19:20:56 cmisst-db ExeMgr[30993]: 56.256165 |18990|0|0| D 16 CAL0042: End SQL statement

Mar 29 19:20:56 cmisst-db ExeMgr[30993]: 56.256763 |18990|0|0| D 16 CAL0041: Start SQL statement: ; |mi_04_15_vin2016_d|

Mar 29 19:20:56 cmisst-db messagequeue[30993]: 56.257097 |0|0|0| W 31 CAL0071: InetStreamSocket::read: EOF during readToMagic: socket read error: Success; InetStreamSocket: sd: 27 inet: 127.0.0.1 port: 37738; Will retry.

Mar 29 19:20:57 cmisst-db messagequeue[30993]: 57.082176 |0|0|0| W 31 CAL0071: InetStreamSocket::read: EOF during readToMagic: socket read error: Success; InetStreamSocket: sd: 25 inet: 127.0.0.1 port: 37730; Will retry.

Mar 29 19:20:57 cmisst-db ExeMgr[30993]: 57.082675 |18990|0|0| D 16 CAL0042: End SQL statement

Mar 29 19:20:57 cmisst-db messagequeue[30993]: 57.083721 |0|0|0| W 31 CAL0071: InetStreamSocket::read: EOF during readToMagic: socket read error: Success; InetStreamSocket: sd: 25 inet: 127.0.0.1 port: 37730; Will retry.

Mar 29 19:20:57 cmisst-db ExeMgr[30993]: 57.083735 |18990|0|0| D 16 CAL0041: Start SQL statement: ; |mi_04_15_vin2016_d|

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columnstore+unsub...@googlegroups.com.

To post to this group, send email to mariadb-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/36e637af-701e-45a5-b3e9-d85c74c6bd94%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columnstore+unsub...@googlegroups.com.

To post to this group, send email to mariadb-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/0e2b4c35-2d3a-4ff8-8d1f-52f90e6410d9%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columnstore+unsub...@googlegroups.com.

[David Thompson]

Can you try configuring your windows side to use the mysql 5.3 odbc driver for connecting to columnstore and see if that solves the problem for now? 

 

By default the mariadb odbc driver always uses prepared statements internally and this seems to be the trigger point on the columnstore server in conjunction with the stored procedure call.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.

To post to this group, send email to mariadb-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/36e637af-701e-45a5-b3e9-d85c74c6bd94%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.

To post to this group, send email to mariadb-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/0e2b4c35-2d3a-4ff8-8d1f-52f90e6410d9%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.

To post to this group, send email to mariadb-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/a612b800-58af-4fc6-8e4b-f64288a65a5d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.

To post to this group, send email to mariadb-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/92b2ac63-a0b7-40bf-978d-487699192242%40googlegroups.com.

[Jared Karlow]

It doesn't. 

It looks like it's not so much the stored procedure call as it is the parameter handling.  When I make the proc call with something passed as a parameter (rather than the parameter hard coded into the query text) that's when it starts behaving this way, using both drivers.  

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columnstore+unsub...@googlegroups.com.

To post to this group, send email to mariadb-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/36e637af-701e-45a5-b3e9-d85c74c6bd94%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columnstore+unsub...@googlegroups.com.

To post to this group, send email to mariadb-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/0e2b4c35-2d3a-4ff8-8d1f-52f90e6410d9%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columnstore+unsub...@googlegroups.com.

To post to this group, send email to mariadb-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/a612b800-58af-4fc6-8e4b-f64288a65a5d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columnstore+unsub...@googlegroups.com.

[Jared Karlow]

That said, editing the connection string to contain the following:

no_ssps = 1 (disables server side prepared statements)

Fixed that issue.

Now onto debugging the rest of the report code.  Thanks for your help! I'll edit the original post with the current workaround.

It doesn't. 

V

 

...SELECT SPECIFIC_SCHEMA AS PROCEDURE_CAT, NULL AS PROCEDURE_SCHEM, SPECIFIC_NAME PROCEDURE_NAME, PARAMETER_NAME COLUMN_NAME, CASE PARAMETER_MODE   WHEN 'IN' THEN 1  WHEN 'OUT' THEN 4  WHEN 'INOUT' THEN 2  ELSE IF(PARAMETER_MODE IS NULL, 5, 0)END COLUMN_TYPE, CASE DATA_TYPE  WHEN 'bit' THEN @dt:= IF(NUMERIC_PRECISION=1,(-7), (-2))  WHEN 'tinyint' THEN @dt:=(-6)  WHEN 'smallint' THEN @dt:=5  WHEN 'year' THEN 5  WHEN 'mediumint' THEN @dt:=4  WHEN 'int' THEN @dt:=4  WHEN 'bigint' THEN @dt:=(-5)  WHEN 'blob' THEN @dt:=(-4)  WHEN 'tinyblob' THEN @dt:=(-4)  WHEN 'mediumblob' THEN @dt:=(-4)  WHEN 'longblob' THEN @dt:=(-4)  WHEN 'text' THEN @dt:=(-1)  WHEN 'tinytext' THEN @dt:=(-1)  WHEN 'mediumtext' THEN @dt:=(-1)  WHEN 'longtext' THEN @dt:=(-1)  WHEN 'decimal' THEN @dt:=3  WHEN 'float' THEN @dt:=7  WHEN 'double' THEN @dt:=8  WHEN 'binary' THEN @dt:=(-2)  WHEN 'varbinary' THEN @dt:=(-3)  WHEN 'char' THEN @dt:=1  WHEN 'enum' THEN @dt:=1  WHEN 'set' THEN @dt:=1  WHEN 'varchar' THEN @dt:=12  WHEN 'date' THEN @dt:=91  WHEN 'time' THEN @dt:=92  WHEN 'datetime' THEN @dt:=93  WHEN 'timestamp' THEN @dt:=93  ELSE @dt:=(-4)END AS DATA_TYPE, DATA_TYPE TYPE_NAME, CASE  WHEN DATA_TYPE = 'bit' THEN @ColSize:=CAST(((NUMERIC_PRECISION + 7) / <span style="font-size:10.0pt;font-family:"Cour

[Jared Karlow]

FIX for this issue:

add no_ssps = 1 to the connection string to suppress the forced generation of server-side prepared statements.  

[Jared Karlow]

Actually, I lied.  This is inserting USE INDEX() into all of the table joins in any of the stored procedures, which is causing syntax errors on any stored procedure with a table join.  Going to see if there's a way to suppress that behavior.  

[Jared Karlow]

Specifically, it's any inner join specifically - it doesn't seem to be doing this for any other table joins.

[Jared Karlow]

For what its worth, this seems to be a separate issue unrelated to this.  

It seems this is in the stored procedure handling - it's breaking every other time when called even when not queried over ODBC.  

[Andrew Hutchings]

Hi Jared,

Please allow me to step in here.

I think the new problem is due to a bug fix I made which is intended to
disable indexes when a query uses ColumnStore with non-ColumnStore
engines so that the full query is pushed down to the engine. This aids
cross-engine joins. My guess is I made it too broad.

Do you happen to have an example so I can reproduce this and come up
with a fix (as well as a workaround)?

In the mean time I'm looking into a fix for the original problem without
you having to workaround it.

Kind Regards
Andrew

> --
> You received this message because you are subscribed to the Google
> Groups "MariaDB ColumnStore" group.
> To unsubscribe from this group and stop receiving emails from it, send

> an email to mariadb-columns...@googlegroups.com
> <mailto:mariadb-columns...@googlegroups.com>.

> To post to this group, send email to
> mariadb-c...@googlegroups.com

> <mailto:mariadb-c...@googlegroups.com>.

> To view this discussion on the web visit

> https://groups.google.com/d/msgid/mariadb-columnstore/3c08be17-58fb-412e-bbc4-873410bcc2b4%40googlegroups.com
> <https://groups.google.com/d/msgid/mariadb-columnstore/3c08be17-58fb-412e-bbc4-873410bcc2b4%40googlegroups.com?utm_medium=email&utm_source=footer>.

> For more options, visit https://groups.google.com/d/optout.

--

Andrew Hutchings (LinuxJedi)
Senior Software Engineer, MariaDB

DON’T MISS
M|17
April 11 - 12, 2017
The Conrad Hotel
New York City
https://m17.mariadb.com/

[Jared Karlow]

Sure.  Using the schemas attached earlier, here's the proc that's breaking.  

The absolutely perplexing thing is that the proc works every other time or thereabouts.  Half the time it works, half the time it throws a syntax error with the USE INDEX() bit.  

CREATE PROCEDURE `DRUG_vehicles_in_crashes`(in inputyear int)

BEGIN

select

        vehicle_type_name,

        case when tttt.vehicles is null then 0 else tttt.vehicles end as vehicles,

        case when tttt.percent_total is null then 0 else tttt.percent_total end as percent_total,

        case when tttt.fatal is null then 0 else tttt.fatal end as fatal,

        case when tttt.percent_fatal is null then 0 else tttt.percent_fatal end as percent_fatal,

        case when tttt.injury is null then 0 else tttt.injury end as injury,

        case when tttt.pdo_crash is null then 0 else tttt.pdo_crash end as pdo_crash

from automated_reports.vehicle_type_list

left join(

        select

                vehicle_type,

                vehicles,

                convert(100 * vehicles/total_vehicles, decimal(4,1)) as percent_total,

                fatal,

                convert(100 * fatal/total_fatal, decimal(4,1)) as percent_fatal,

                injury,

                pdo_crash

        from(

                select

                        vehicle_type,

                        1 as dummy,

                        count(vehicle.id) as vehicles,

                        count(if(worst_injury_in_accident = 1, 1, null)) as fatal,

                        count(if(worst_injury_in_accident IN (2,3,4), 1, null)) as injury,

                        count(if(worst_injury_in_accident = 5, 1, null)) as pdo_crash

                from vehicle

                left join crash on crash.id = vehicle.crash_id

                where accident_year = inputyear AND test_result_drug BETWEEN 600 AND 699 and traffic_unit_type = 1

                group by vehicle_type

        ) as t

        join (

                select

                        1 as dummy,

                        count(vehicle.id) as total_vehicles

                from vehicle

                left join crash on crash.id = vehicle.crash_id

                where accident_year = inputyear  AND test_result_drug BETWEEN 600 AND 699 and traffic_unit_type = 1

        ) as tt on t.dummy = tt.dummy

        join (

                select

                        1 as dummy,

                        count(vehicle.id) as total_fatal

                from vehicle

                left join crash on crash.id = vehicle.crash_id

                where accident_year = inputyear  AND test_result_drug BETWEEN 600 AND 699 and worst_injury_in_accident = 1 and traffic_unit_type = 1

        ) as ttt on t.dummy = ttt.dummy

) tttt on tttt.vehicle_type=vehicle_type_id

order by field(vehicle_type_name, 'Passenger car & station wagon', 'Van & motorhome', 'Pickup truck', 'Small truck under 10,000 lbs. GVWR', 'Cycle', 'Moped', 'Go Cart', 'Snowmobile', 'Off-Road Vehicle (ORV) & All-Terrain Vehicle (ATV)', 'Other', 'Truck/bus over 10,000 lbs.', 'Unknown');

END

                from vehicle

                left join crash on crash.id = vehicle.crash_id

                where accident_year = inputyear  AND test_result_drug BETWEEN 600 AND 699 and traffic_unit_type = 1

        ) as tt on t.dummy = tt.dummy

        join (

                select

                        1 as dummy,

                        count(vehicle.id) as total_fatal

                from vehicle

                left join crash on crash.id = vehicle.crash_id

                where accident_year = inputyear  AND test_result_drug BETWEEN 600 AND 699 and worst_injury_in_accident = 1 and traffic_unit_type = 1

        ) as ttt on t.dummy = ttt.dummy

) tttt on tttt.vehicle_type=vehicle_type_id

order by field(vehicle_type_name, 'Passenger car & station wagon', 'Van & motorhome', 'Pickup truck', 'Small truck under 10,000 lbs. GVWR', 'Cycle', 'Moped', 'Go Cart', 'Snowmobile', 'Off-R

END 

> an email to mariadb-columnstore+unsub...@googlegroups.com
> <mailto:mariadb-columnstore+unsub...@googlegroups.com>.

[Andrew Hutchings]

Hi Jared,

Many thanks, I was able to easily reproduce this and have created a
ticket for it:

https://jira.mariadb.org/browse/MCOL-652

I also have a fix ready I'm testing now.

Unfortunately I can't think of a good workaround at the moment. If I
come up with something I'll let you know.

Kind Regards
Andrew

> > an email to mariadb-columns...@googlegroups.com
> <javascript:>
> > <mailto:mariadb-columns...@googlegroups.com
> <javascript:>>.

> > To post to this group, send email to

> > mariadb-c...@googlegroups.com <javascript:>
> > <mailto:mariadb-c...@googlegroups.com <javascript:>>.

> > To view this discussion on the web visit
> >
> https://groups.google.com/d/msgid/mariadb-columnstore/3c08be17-58fb-412e-bbc4-873410bcc2b4%40googlegroups.com
> <https://groups.google.com/d/msgid/mariadb-columnstore/3c08be17-58fb-412e-bbc4-873410bcc2b4%40googlegroups.com>
>
> >

> <https://groups.google.com/d/msgid/mariadb-columnstore/3c08be17-58fb-412e-bbc4-873410bcc2b4%40googlegroups.com?utm_medium=email&utm_source=footer

> <https://groups.google.com/d/msgid/mariadb-columnstore/3c08be17-58fb-412e-bbc4-873410bcc2b4%40googlegroups.com?utm_medium=email&utm_source=footer>>.
>
> > For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

>
> --
> Andrew Hutchings (LinuxJedi)
> Senior Software Engineer, MariaDB
>
> DON’T MISS
> M|17
> April 11 - 12, 2017
> The Conrad Hotel
> New York City
> https://m17.mariadb.com/
>

> --
> You received this message because you are subscribed to the Google
> Groups "MariaDB ColumnStore" group.
> To unsubscribe from this group and stop receiving emails from it, send

> an email to mariadb-columns...@googlegroups.com
> <mailto:mariadb-columns...@googlegroups.com>.

> To post to this group, send email to
> mariadb-c...@googlegroups.com
> <mailto:mariadb-c...@googlegroups.com>.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/mariadb-columnstore/6d39d744-334f-4d8f-b8b4-58b2f83bb3df%40googlegroups.com
> <https://groups.google.com/d/msgid/mariadb-columnstore/6d39d744-334f-4d8f-b8b4-58b2f83bb3df%40googlegroups.com?utm_medium=email&utm_source=footer>.

[Jared Karlow]

Do you have a rough estimate of when the version with the fix will be released?

>     > an email to mariadb-columnstore+unsub...@googlegroups.com
>     <javascript:>
>     > <mailto:mariadb-columnstore+unsub...@googlegroups.com

>     <javascript:>>.
>     > To post to this group, send email to
>     > mariadb-c...@googlegroups.com <javascript:>
>     > <mailto:mariadb-c...@googlegroups.com <javascript:>>.
>     > To view this discussion on the web visit
>     >
>     https://groups.google.com/d/msgid/mariadb-columnstore/3c08be17-58fb-412e-bbc4-873410bcc2b4%40googlegroups.com
>     <https://groups.google.com/d/msgid/mariadb-columnstore/3c08be17-58fb-412e-bbc4-873410bcc2b4%40googlegroups.com>
>
>     >
>     <https://groups.google.com/d/msgid/mariadb-columnstore/3c08be17-58fb-412e-bbc4-873410bcc2b4%40googlegroups.com?utm_medium=email&utm_source=footer
>     <https://groups.google.com/d/msgid/mariadb-columnstore/3c08be17-58fb-412e-bbc4-873410bcc2b4%40googlegroups.com?utm_medium=email&utm_source=footer>>.
>
>     > For more options, visit https://groups.google.com/d/optout
>     <https://groups.google.com/d/optout>.
>
>     --
>     Andrew Hutchings (LinuxJedi)
>     Senior Software Engineer, MariaDB
>
>     DON’T MISS
>     M|17
>     April 11 - 12, 2017
>     The Conrad Hotel
>     New York City
>     https://m17.mariadb.com/
>
> --
> You received this message because you are subscribed to the Google
> Groups "MariaDB ColumnStore" group.
> To unsubscribe from this group and stop receiving emails from it, send

> an email to mariadb-columnstore+unsub...@googlegroups.com
> <mailto:mariadb-columnstore+unsub...@googlegroups.com>.

[David Thompson]

We are still finalizing that but I’d plan on sometime in the first half of May. If we have a new 10.1 server release we may do this a little earlier.

 

If you need it earlier, once it’s fixed, its not all that hard to do your own build from our develop-1.0 branch if you are comfortable with that?

 

David.

 

From: mariadb-c...@googlegroups.com [mailto:mariadb-c...@googlegroups.com] On Behalf Of Jared Karlow
Sent: Friday, March 31, 2017 1:47 PM
To: MariaDB ColumnStore <mariadb-c...@googlegroups.com>
Subject: Re: Struggling with stored procedures for Columstore after migration from InfiniDB

 

Do you have a rough estimate of when the version with the fix will be released?

>     > an email to mariadb-columns...@googlegroups.com
>     <javascript:>
>     > <mailto:mariadb-columns...@googlegroups.com

>     <javascript:>>.
>     > To post to this group, send email to
>     > mariadb-c...@googlegroups.com <javascript:>
>     > <mailto:mariadb-c...@googlegroups.com <javascript:>>.
>     > To view this discussion on the web visit
>     >
>     https://groups.google.com/d/msgid/mariadb-columnstore/3c08be17-58fb-412e-bbc4-873410bcc2b4%40googlegroups.com
>     <https://groups.google.com/d/msgid/mariadb-columnstore/3c08be17-58fb-412e-bbc4-873410bcc2b4%40googlegroups.com>
>
>     >
>     <https://groups.google.com/d/msgid/mariadb-columnstore/3c08be17-58fb-412e-bbc4-873410bcc2b4%40googlegroups.com?utm_medium=email&utm_source=footer
>     <https://groups.google.com/d/msgid/mariadb-columnstore/3c08be17-58fb-412e-bbc4-873410bcc2b4%40googlegroups.com?utm_medium=email&utm_source=footer>>.
>
>     > For more options, visit https://groups.google.com/d/optout
>     <https://groups.google.com/d/optout>.
>
>     --
>     Andrew Hutchings (LinuxJedi)
>     Senior Software Engineer, MariaDB
>
>     DON’T MISS
>     M|17
>     April 11 - 12, 2017
>     The Conrad Hotel
>     New York City
>     https://m17.mariadb.com/
>
> --
> You received this message because you are subscribed to the Google
> Groups "MariaDB ColumnStore" group.
> To unsubscribe from this group and stop receiving emails from it, send

> an email to mariadb-columns...@googlegroups.com
> <mailto:mariadb-columns...@googlegroups.com>.

> To post to this group, send email to
> mariadb-c...@googlegroups.com
> <mailto:mariadb-c...@googlegroups.com>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/mariadb-columnstore/6d39d744-334f-4d8f-b8b4-58b2f83bb3df%40googlegroups.com
> <https://groups.google.com/d/msgid/mariadb-columnstore/6d39d744-334f-4d8f-b8b4-58b2f83bb3df%40googlegroups.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.

--
Andrew Hutchings (LinuxJedi)
Senior Software Engineer, MariaDB

DON’T MISS
M|17
April 11 - 12, 2017
The Conrad Hotel
New York City
https://m17.mariadb.com/

--

You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.
To post to this group, send email to mariadb-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/8c21d499-acc8-463c-8666-ba0a56ce89ab%40googlegroups.com.

[David Thompson]

Hi Jared, we are currently thinking of around early May for our 1.0.9 release. We have just verified the 2 bug fixes (https://jira.mariadb.org/browse/MCOL-650, https://jira.mariadb.org/browse/MCOL-652) on our develop-1.0 branch so if you are capable of doing a custom build you could verify now.

 

David.

 

From: mariadb-c...@googlegroups.com [mailto:mariadb-c...@googlegroups.com] On Behalf Of Jared Karlow
Sent: Friday, March 31, 2017 1:47 PM
To: MariaDB ColumnStore <mariadb-c...@googlegroups.com>
Subject: Re: Struggling with stored procedures for Columstore after migration from InfiniDB

 

Do you have a rough estimate of when the version with the fix will be released?

>     > an email to mariadb-columns...@googlegroups.com
>     <javascript:>
>     > <mailto:mariadb-columns...@googlegroups.com

>     <javascript:>>.
>     > To post to this group, send email to
>     > mariadb-c...@googlegroups.com <javascript:>
>     > <mailto:mariadb-c...@googlegroups.com <javascript:>>.
>     > To view this discussion on the web visit
>     >
>     https://groups.google.com/d/msgid/mariadb-columnstore/3c08be17-58fb-412e-bbc4-873410bcc2b4%40googlegroups.com
>     <https://groups.google.com/d/msgid/mariadb-columnstore/3c08be17-58fb-412e-bbc4-873410bcc2b4%40googlegroups.com>
>
>     >
>     <https://groups.google.com/d/msgid/mariadb-columnstore/3c08be17-58fb-412e-bbc4-873410bcc2b4%40googlegroups.com?utm_medium=email&utm_source=footer
>     <https://groups.google.com/d/msgid/mariadb-columnstore/3c08be17-58fb-412e-bbc4-873410bcc2b4%40googlegroups.com?utm_medium=email&utm_source=footer>>.
>
>     > For more options, visit https://groups.google.com/d/optout
>     <https://groups.google.com/d/optout>.
>
>     --
>     Andrew Hutchings (LinuxJedi)
>     Senior Software Engineer, MariaDB
>
>     DON’T MISS
>     M|17
>     April 11 - 12, 2017
>     The Conrad Hotel
>     New York City
>     https://m17.mariadb.com/
>
> --
> You received this message because you are subscribed to the Google
> Groups "MariaDB ColumnStore" group.
> To unsubscribe from this group and stop receiving emails from it, send

> an email to mariadb-columns...@googlegroups.com
> <mailto:mariadb-columns...@googlegroups.com>.

> To post to this group, send email to
> mariadb-c...@googlegroups.com
> <mailto:mariadb-c...@googlegroups.com>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/mariadb-columnstore/6d39d744-334f-4d8f-b8b4-58b2f83bb3df%40googlegroups.com
> <https://groups.google.com/d/msgid/mariadb-columnstore/6d39d744-334f-4d8f-b8b4-58b2f83bb3df%40googlegroups.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.

--
Andrew Hutchings (LinuxJedi)
Senior Software Engineer, MariaDB

DON’T MISS
M|17
April 11 - 12, 2017
The Conrad Hotel
New York City
https://m17.mariadb.com/

--

You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.
To post to this group, send email to mariadb-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/8c21d499-acc8-463c-8666-ba0a56ce89ab%40googlegroups.com.