[MDEV-4881] Pivot throws an error - JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0.3
Fix Version/s: None
Component/s: None
Labels:
- connect-engine
Environment:
Ubuntu/Precise/LXContainer

Description

I promisse Im going to make a break playing with Connect. But as it is a very promising SE
I did the example from https://kb.askmonty.org/en/connect-table-types-pivot-table-type/

MariaDB [test]> select @@query_cache_type;
+--------------------+
| @@query_cache_type |
+--------------------+
| OFF                |
+--------------------+
1 row in set (0.00 sec)

MariaDB [test]> select who, week, what, sum(amount) from expenses
    ->        group by who, week, what; 
+-------+------+------+-------------+
| who   | week | what | sum(amount) |
+-------+------+------+-------------+
| Beth  |    3 | Beer |          16 |
| Beth  |    4 | Beer |          15 |
| Beth  |    4 | Food |          17 |
| Beth  |    5 | Beer |          20 |
| Beth  |    5 | Food |          12 |
| Janet |    3 | Beer |          18 |
| Janet |    3 | Car  |          19 |
| Janet |    3 | Food |          18 |
| Janet |    4 | Car  |          17 |
| Janet |    5 | Beer |          33 |
| Janet |    5 | Car  |          12 |
| Janet |    5 | Food |          12 |
| Joe   |    3 | Beer |          18 |
| Joe   |    3 | Car  |          20 |
| Joe   |    3 | Food |          31 |
| Joe   |    4 | Beer |          49 |
| Joe   |    4 | Food |          34 |
| Joe   |    5 | Beer |          14 |
| Joe   |    5 | Food |          12 |
+-------+------+------+-------------+
19 rows in set (0.00 sec)


MariaDB [test]> create table pivex
    -> engine=connect table_type=pivot tabname=expenses;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> select * from pivex;
ERROR 1296 (HY000): Got error 174 'Cannot access test.expenses' from CONNECT

Other CONNECT table are still working

MariaDB [test]> select * from federatedconnect limit 10;
+----+------+
| id | id2  |
+----+------+
|  5 | NULL |
|  1 |    4 |
|  2 |    5 |
|  3 |    5 |
|  4 |    5 |
| 11 |   11 |
| 12 |   11 |
| 13 |   13 |
| 14 |   20 |
| 15 |   20 |
+----+------+
10 rows in set (0.01 sec)

So being an ignorant doing the query again:

MariaDB [test]> select * from pivex;
ERROR 2013 (HY000): Lost connection to MySQL server during query

This at least let the server restart and gave some messages into the error log

Aug 10 00:07:57 localhost mysqld: This could be because you hit a bug. It is also possible that this binary
Aug 10 00:07:57 localhost mysqld: or one of the libraries it was linked against is corrupt, improperly built,
Aug 10 00:07:57 localhost mysqld: or misconfigured. This error can also be caused by malfunctioning hardware.
Aug 10 00:07:57 localhost mysqld:
Aug 10 00:07:57 localhost mysqld: To report this bug, see http://kb.askmonty.org/en/reporting-bugs
Aug 10 00:07:57 localhost mysqld:
Aug 10 00:07:57 localhost mysqld: We will try our best to scrape up some info that will hopefully help
Aug 10 00:07:57 localhost mysqld: diagnose the problem, but since we have already crashed,
Aug 10 00:07:57 localhost mysqld: something is definitely wrong and this may fail.
Aug 10 00:07:57 localhost mysqld:
Aug 10 00:07:57 localhost mysqld: Server version: 10.0.3-MariaDB-1~precise-log
Aug 10 00:07:57 localhost mysqld: key_buffer_size=134217728
Aug 10 00:07:57 localhost mysqld: read_buffer_size=2097152
Aug 10 00:07:57 localhost mysqld: max_used_connections=3
Aug 10 00:07:57 localhost mysqld: max_threads=102
Aug 10 00:07:57 localhost mysqld: thread_count=2
Aug 10 00:07:57 localhost mysqld: It is possible that mysqld could use up to
Aug 10 00:07:57 localhost mysqld: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 759757 K  bytes of memory
Aug 10 00:07:57 localhost mysqld: Hope that's ok; if not, decrease some variables in the equation.
Aug 10 00:07:57 localhost mysqld:
Aug 10 00:07:57 localhost mysqld: Thread pointer: 0x0x7f6d21e37588
Aug 10 00:07:57 localhost mysqld: Attempting backtrace. You can use the following information to find out
Aug 10 00:07:57 localhost mysqld: where mysqld died. If you see no messages after this, something went
Aug 10 00:07:57 localhost mysqld: terribly wrong...
Aug 10 00:07:57 localhost mysqld: stack_bottom = 0x7f6d1f77ee50 thread_stack 0x48000
Aug 10 00:07:57 localhost mysqld: ??:0(??)[0x7f6d201116fb]
Aug 10 00:07:57 localhost mysqld: ??:0(??)[0x7f6d1fce5b31]
Aug 10 00:07:57 localhost mysqld: ??:0(??)[0x7f6d1ed87cb0]
Aug 10 00:07:57 localhost mysqld: ??:0(??)[0x7f6cf9f39794]
Aug 10 00:07:57 localhost mysqld: ??:0(??)[0x7f6cf9f3a74c]
Aug 10 00:07:57 localhost mysqld: ??:0(??)[0x7f6d1fcefe5c]
Aug 10 00:07:57 localhost mysqld: ??:0(??)[0x7f6d1fdab3cd]
Aug 10 00:07:57 localhost mysqld: ??:0(??)[0x7f6d1fdabd90]
Aug 10 00:07:57 localhost mysqld: ??:0(??)[0x7f6d1fb48269]
Aug 10 00:07:57 localhost mysqld: ??:0(??)[0x7f6d1fb51c9a]
Aug 10 00:07:57 localhost mysqld: ??:0(??)[0x7f6d1fb859e1]
Aug 10 00:07:57 localhost mysqld: ??:0(??)[0x7f6d1fb8d2c2]
Aug 10 00:07:57 localhost mysqld: ??:0(??)[0x7f6d1fb93bca]
Aug 10 00:07:57 localhost mysqld: ??:0(??)[0x7f6d1fb962b1]
Aug 10 00:07:57 localhost mysqld: ??:0(??)[0x7f6d1fc619a3]
Aug 10 00:07:57 localhost mysqld: ??:0(??)[0x7f6d1fc61b27]
Aug 10 00:07:57 localhost mysqld: ??:0(??)[0x7f6d1ed7fe9a]
Aug 10 00:07:57 localhost mysqld: ??:0(??)[0x7f6d1da3accd]
Aug 10 00:07:57 localhost mysqld: 
Aug 10 00:07:57 localhost mysqld: Trying to get some variables.
Aug 10 00:07:57 localhost mysqld: Some pointers may be invalid and cause the dump to abort.
Aug 10 00:07:57 localhost mysqld: Query (0x7f6cd40049e0): is an invalid pointer
Aug 10 00:07:57 localhost mysqld: Connection ID (thread ID): 36
Aug 10 00:07:57 localhost mysqld: Status: NOT_KILLED
Aug 10 00:07:57 localhost mysqld: 
Aug 10 00:07:57 localhost mysqld: Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off,exists_to_in=off
Aug 10 00:07:57 localhost mysqld: 
Aug 10 00:07:57 localhost mysqld: The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
Aug 10 00:07:57 localhost mysqld: information that should help you find out what is causing the crash.
Aug 10 00:07:57 localhost mysqld_safe: Number of processes running now: 0
Aug 10 00:07:57 localhost mysqld_safe: mysqld restarted
Aug 10 00:07:57 localhost mysqld: /usr/sbin/mysqld: Query cache is disabled (resize or similar command in progress); repeat this command later
Aug 10 00:07:57 localhost mysqld: 130810  0:07:57 InnoDB: The InnoDB memory heap is disabled
Aug 10 00:07:57 localhost mysqld: 130810  0:07:57 InnoDB: Mutexes and rw_locks use GCC atomic builtins
Aug 10 00:07:57 localhost mysqld: 130810  0:07:57 InnoDB: Compressed tables use zlib 1.2.3.4
Aug 10 00:07:57 localhost mysqld: 130810  0:07:57 InnoDB: Using Linux native AIO
Aug 10 00:07:57 localhost mysqld: 130810  0:07:57 InnoDB: CPU supports crc32 instructions
Aug 10 00:07:57 localhost mysqld: 130810  0:07:57 InnoDB: Initializing buffer pool, size = 256.0M
Aug 10 00:07:57 localhost mysqld: 130810  0:07:57 InnoDB: Completed initialization of buffer pool
Aug 10 00:07:57 localhost mysqld: 130810  0:07:57 InnoDB: highest supported file format is Barracuda.
Aug 10 00:07:57 localhost mysqld: InnoDB: The log sequence number in ibdata files does not match
Aug 10 00:07:57 localhost mysqld: InnoDB: the log sequence number in the ib_logfiles!
Aug 10 00:07:57 localhost mysqld: 130810  0:07:57  InnoDB: Database was not shut down normally!
Aug 10 00:07:57 localhost mysqld: InnoDB: Starting crash recovery.
Aug 10 00:07:57 localhost mysqld: InnoDB: Reading tablespace information from the .ibd files...
Aug 10 00:07:57 localhost mysqld: InnoDB: Restoring possible half-written data pages from the doublewrite
Aug 10 00:07:57 localhost mysqld: InnoDB: buffer...
Aug 10 00:07:57 localhost mysqld: InnoDB: Last MySQL binlog file position 0 2279, file name /var/log/mysql/mariadb-bin.000039
Aug 10 00:07:57 localhost mysqld: 130810  0:07:57 InnoDB: 128 rollback segment(s) are active.
Aug 10 00:07:57 localhost mysqld: 130810  0:07:57 InnoDB: 10.0.3-MariaDB started; log sequence number 1803222
Aug 10 00:07:57 localhost mysqld: 130810  0:07:57 [Note] Plugin 'FEEDBACK' is disabled.
Aug 10 00:07:57 localhost mysqld: 130810  0:07:57 [Note] CONNECT: connect.ini=/var/lib/mysql/connect.ini
Aug 10 00:07:57 localhost mysqld: profile file /var/lib/mysql/connect.ini not found
Aug 10 00:07:57 localhost mysqld: 130810  0:07:57 [Note] Recovering after a crash using /var/log/mysql/mariadb-bin
Aug 10 00:07:57 localhost mysqld: 130810  0:07:57 [Note] Starting crash recovery...
Aug 10 00:07:57 localhost mysqld: 130810  0:07:57 [Note] Crash recovery finished.
Aug 10 00:07:57 localhost mysqld: 130810  0:07:57 [Note] Server socket created on IP: '0.0.0.0'.
Aug 10 00:07:57 localhost mysqld: 130810  0:07:57 [Note] Event Scheduler: Loaded 0 events
Aug 10 00:07:57 localhost mysqld: 130810  0:07:57 [Note] Reading of all Master_info entries succeded
Aug 10 00:07:57 localhost mysqld: 130810  0:07:57 [Note] Added new Master_info '' to hash table
Aug 10 00:07:57 localhost mysqld: 130810  0:07:57 [Note] /usr/sbin/mysqld: ready for connections.
Aug 10 00:07:57 localhost mysqld: Version: '10.0.3-MariaDB-1~precise-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution

If you want I can build a kvm host an run another test.
Regards
Erkab

Gliffy Diagrams

Attachments

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

erkan yanar added a comment - 2013-08-10 05:14

Did the tests on a KVM also. Same results.
Regards
Erkan

Show

erkan yanar added a comment - 2013-08-10 05:14 Did the tests on a KVM also. Same results. Regards Erkan

Hide

Permalink

Olivier Bertrand added a comment - 2013-08-10 18:36

Running the last version of CONNECT SE (revno 3781) I cannot reproduce this neither on Windows nor on Linux ubuntu. Did you try to update your program?

Show

Olivier Bertrand added a comment - 2013-08-10 18:36 Running the last version of CONNECT SE (revno 3781) I cannot reproduce this neither on Windows nor on Linux ubuntu. Did you try to update your program?

Hide

Permalink

erkan yanar added a comment - 2013-08-11 06:16

Great, Im working on a build.

Show

erkan yanar added a comment - 2013-08-11 06:16 Great, Im working on a build.

Hide

Permalink

erkan yanar added a comment - 2013-08-11 06:26

 CREATE TABLE `expenses` (
  `who` char(10) DEFAULT NULL,
  `week` int(11) DEFAULT NULL,
  `what` char(10) DEFAULT NULL,
  `amount` decimal(5,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 
.....

MariaDB [test]> create table pivex2
    -> engine=connect table_type=pivot tabname=expenses
    -> option_list='PivotCol=Week,Function=AVG';
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> select * from pivex2;
ERROR 1296 (HY000): Got error 174 'Cannot access test.expenses' from CONNECT

Error thrown from mysqld

Cannot access test.expenses
OpenTable: Cannot access test.expenses

While starting the new build I saw something new:

130811  3:19:02 [Note] CONNECT: connect.ini=/usr/local/mysql/data/connect.ini
profile file /usr/local/mysql/data/connect.ini not found

I don't have any documentation about this.

Regards
Erkan

Show

erkan yanar added a comment - 2013-08-11 06:26 CREATE TABLE `expenses` ( `who` char(10) DEFAULT NULL, `week` int(11) DEFAULT NULL, `what` char(10) DEFAULT NULL, `amount` decimal(5,0) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ..... MariaDB [test]> create table pivex2 -> engine=connect table_type=pivot tabname=expenses -> option_list='PivotCol=Week,Function=AVG'; Query OK, 0 rows affected (0.01 sec) MariaDB [test]> select * from pivex2; ERROR 1296 (HY000): Got error 174 'Cannot access test.expenses' from CONNECT Error thrown from mysqld Cannot access test.expenses OpenTable: Cannot access test.expenses While starting the new build I saw something new: 130811 3:19:02 [Note] CONNECT: connect.ini=/usr/local/mysql/data/connect.ini profile file /usr/local/mysql/data/connect.ini not found I don't have any documentation about this. Regards Erkan

Hide

Permalink

Olivier Bertrand added a comment - 2013-08-11 15:34

Thanks Erkan for giving more details on this error. I could not reproduce until I could make the test with the expenses tables as you created it.
There was two bugs: not recognizing properly the DECIMAL type of the amount column (handled by CONNECT as double) and not resetting a CONNECT member in case of error.
I also made CONNECT issuing a more descriptive error message when a table cannot be accessed.
Fixed as revno 3782.

Show

Olivier Bertrand added a comment - 2013-08-11 15:34 Thanks Erkan for giving more details on this error. I could not reproduce until I could make the test with the expenses tables as you created it. There was two bugs: not recognizing properly the DECIMAL type of the amount column (handled by CONNECT as double) and not resetting a CONNECT member in case of error. I also made CONNECT issuing a more descriptive error message when a table cannot be accessed. Fixed as revno 3782.

Pivot throws an error

Details

Description

Gliffy Diagrams

Attachments

Activity

People

Dates

Time Tracking

Agile