Details

Type: Story
Status: Done
Resolution: Done
Fix Version/s: None
Component/s: None
Labels:
None

Story Points:
3
Epic Link:
S17 Qserv Refactoring
Sprint:
DB_S17_2
Team:
Data Access and Database

Description

The WISE data set object ids are strings instead of integers and this is causing a problem with qserv. qserv needs to be modified to handle strings as primary keys for director tables.

Attachments

Issue Links

blocks

DM-9372 Load WISE catalog data in PDAC

Done

relates to

DM-2887 Fix broken IN - it now takes first element only

Done

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Igor Gaponenko added a comment - 09/Mar/17 8:17 PM

Synopsis

To see the problem one had to connect directly to the MySQL/MariaDB service of the PDAC master node and pick one of the object identifiers from the corresponding index table, or just by picking any random key from that table. For example:

SELECT * FROM qservMeta.wise_00__Object LIMIT 10;

+----------------------+---------+------------+

| source_id            | chunkId | subChunkId |

+----------------------+---------+------------+

| 0000m016_ac51-000001 |  112201 |        759 |

| 0000m016_ac51-000003 |  112881 |        138 |

| 0000m016_ac51-000005 |  112201 |        760 |

| 0000m016_ac51-000006 |  112201 |        557 |

| 0000m016_ac51-000008 |  112880 |         11 |

| 0000m016_ac51-000009 |  112200 |        770 |

| 0000m016_ac51-000010 |  112201 |        760 |

| 0000m016_ac51-000011 |  112201 |        691 |

| 0000m016_ac51-000012 |  112201 |        690 |

| 0000m016_ac51-000013 |  112201 |        621 |

+----------------------+---------+------------+

Then one has to connect to the Qservr mysql-proxy service (port 4040) on that node and do:

SELECT COUNT(*) FROM wise_00.Object WHERE source_id='0000m016_ac51-000001';

+----------------+

| SUM(QS1_COUNT) |

+----------------+

|              0 |

+----------------+

1 row in set (0.55 sec)

or:

SELECT COUNT(*) FROM wise_00.Object WHERE source_id IN ('0000m016_ac51-000001');

+----------------+

| SUM(QS1_COUNT) |

+----------------+

|              0 |

+----------------+

1 row in set (0.53 sec)

In contrast with that the full scan query would always return the right object:

SELECT COUNT(*) FROM wise_00.Object WHERE source_id LIKE '0000m016_ac51-000001';

+----------------+

| SUM(QS1_COUNT) |

+----------------+

|              1 |

+----------------+

1 row in set (6 min 22.42 sec)

Unfortunately the performance of that query is very bad because all chunks would need to be scanned.

Show

Igor Gaponenko added a comment - 09/Mar/17 8:17 PM Synopsis To see the problem one had to connect directly to the MySQL/MariaDB service of the PDAC master node and pick one of the object identifiers from the corresponding index table, or just by picking any random key from that table. For example: SELECT * FROM qservMeta.wise_00__Object LIMIT 10; + ----------------------+---------+------------+ | source_id | chunkId | subChunkId | + ----------------------+---------+------------+ | 0000m016_ac51-000001 | 112201 | 759 | | 0000m016_ac51-000003 | 112881 | 138 | | 0000m016_ac51-000005 | 112201 | 760 | | 0000m016_ac51-000006 | 112201 | 557 | | 0000m016_ac51-000008 | 112880 | 11 | | 0000m016_ac51-000009 | 112200 | 770 | | 0000m016_ac51-000010 | 112201 | 760 | | 0000m016_ac51-000011 | 112201 | 691 | | 0000m016_ac51-000012 | 112201 | 690 | | 0000m016_ac51-000013 | 112201 | 621 | + ----------------------+---------+------------+ Then one has to connect to the Qservr mysql-proxy service (port 4040 ) on that node and do: SELECT COUNT (*) FROM wise_00.Object WHERE source_id= '0000m016_ac51-000001' ; + ----------------+ | SUM (QS1_COUNT) | + ----------------+ | 0 | + ----------------+ 1 row in set (0.55 sec) or: SELECT COUNT (*) FROM wise_00.Object WHERE source_id IN ( '0000m016_ac51-000001' ); + ----------------+ | SUM (QS1_COUNT) | + ----------------+ | 0 | + ----------------+ 1 row in set (0.53 sec) In contrast with that the full scan query would always return the right object: SELECT COUNT (*) FROM wise_00.Object WHERE source_id LIKE '0000m016_ac51-000001' ; + ----------------+ | SUM (QS1_COUNT) | + ----------------+ | 1 | + ----------------+ 1 row in set (6 min 22.42 sec) Unfortunately the performance of that query is very bad because all chunks would need to be scanned.

Hide

Permalink

Igor Gaponenko added a comment - 09/Mar/17 8:26 PM - edited

Analyzing the root cause of the problem

It turns out this problem is seen due to a bug introduced in an attempt to implement a fix for:

https://jira.lsstcorp.org/browse/DM-2887

The following change made in the implementation of method:

core/modules/qproc/SecondaryIndex.cc

..

std::string makeLookupSql(...) {

...

was incorrect as it was using a utility which was not meant to be used for generating valid SQL statements. Details on the GitHub changes can be found at:

https://github.com/lsst/qserv/commit/536b7bc8962d25ed6950cf623286a89ef802d26c#diff-e0708a58f2f8cab8cd9f9ad7c7c02bfaR85

The proposed fix was to reimplement the code to generate the correct SQL and to follow requirements of the original ticked ~~DM-2887~~]

Show

Igor Gaponenko added a comment - 09/Mar/17 8:26 PM - edited Analyzing the root cause of the problem It turns out this problem is seen due to a bug introduced in an attempt to implement a fix for: https://jira.lsstcorp.org/browse/DM-2887 The following change made in the implementation of method: core/modules/qproc/SecondaryIndex.cc .. std::string makeLookupSql(...) { ... } was incorrect as it was using a utility which was not meant to be used for generating valid SQL statements. Details on the GitHub changes can be found at: https://github.com/lsst/qserv/commit/536b7bc8962d25ed6950cf623286a89ef802d26c#diff-e0708a58f2f8cab8cd9f9ad7c7c02bfaR85 The proposed fix was to reimplement the code to generate the correct SQL and to follow requirements of the original ticked DM-2887 ]

Hide

Permalink

Igor Gaponenko added a comment - 09/Mar/17 8:29 PM - edited

Testing the fix

Once the new version of the Qserv containers was installed in PADC the following tests ere made to ensure the problematic queries (explained in the Synopsis section above) would work as required. And indeed, they do:

SELECT source_id FROM wise_00.Object WHERE source_id='0000m016_ac51-000001';

+----------------------+

| source_id            |

+----------------------+

| 0000m016_ac51-000001 |

+----------------------+

1 row in set (0.40 sec)

SELECT source_id FROM wise_00.Object WHERE source_id IN ('0000m016_ac51-000001');

+----------------------+

| source_id            |

+----------------------+

| 0000m016_ac51-000001 |

+----------------------+

1 row in set (0.34 sec)

SELECT source_id FROM wise_00.Object WHERE source_id IN ('0075m016_ac51-028478','0000m016_ac51-000001');

+----------------------+

| source_id            |

+----------------------+

| 0000m016_ac51-000001 |

| 0075m016_ac51-028478 |

+----------------------+

2 rows in set (0.35 sec)

Proposing to merge this fix with master.

Show

Igor Gaponenko added a comment - 09/Mar/17 8:29 PM - edited Testing the fix Once the new version of the Qserv containers was installed in PADC the following tests ere made to ensure the problematic queries (explained in the Synopsis section above) would work as required. And indeed, they do: SELECT source_id FROM wise_00.Object WHERE source_id= '0000m016_ac51-000001' ; + ----------------------+ | source_id | + ----------------------+ | 0000m016_ac51-000001 | + ----------------------+ 1 row in set (0.40 sec) SELECT source_id FROM wise_00.Object WHERE source_id IN ( '0000m016_ac51-000001' ); + ----------------------+ | source_id | + ----------------------+ | 0000m016_ac51-000001 | + ----------------------+ 1 row in set (0.34 sec) SELECT source_id FROM wise_00.Object WHERE source_id IN ( '0075m016_ac51-028478' , '0000m016_ac51-000001' ); + ----------------------+ | source_id | + ----------------------+ | 0000m016_ac51-000001 | | 0075m016_ac51-028478 | + ----------------------+ 2 rows in set (0.35 sec) Proposing to merge this fix with master .

Hide

Permalink

Igor Gaponenko added a comment - 10/Mar/17 7:55 PM

I have reviewed proposed changes and they look right to me.

Show

Igor Gaponenko added a comment - 10/Mar/17 7:55 PM I have reviewed proposed changes and they look right to me.

Data Management

WISE object ids are strings and this is causing a problem with qserv

Details

Description

Attachments

Issue Links

Activity

Synopsis

Analyzing the root cause of the problem

Testing the fix

People

Dates

Jenkins