-------- Forwarded Message -------- Subject: Re: [QSERV-L] LOADER: support for multi-column PK Date: Thu, 18 Dec 2014 21:49:57 -0800 From: Fabrice Jammes <[log in to unmask]> To: Salnikov, Andrei A. <[log in to unmask]> Hi Andy, Sorry for all these troubles, but as you told I put lot of stress on data-loader ;-) All is on my laptop at SLAC so I can't give log to you now. I may have a wrong configurations on my machine as its in development state, so I want to double-check everything tomorrow. Here's the list: - CSS/DROP DATABASE isn't performed by loader, whereas the loader run CSS/CREATE DATABASE This prevents the tester/developer to change default-overlap when re-running a test, for example. In the short-term I propose to run qserv-admin DROP DATABASE at the beginning of a test case? In the long-term, could DROP/CREATE DATABASE be performed by the same high-level command in MySQL/CSS for consistency? - It seems dirXXX fields are required in cfg file to feed CSS. I suppress them from cfg file between two runs of a same test, and I'not sure that the loader delete their values. I'll check tomorrow, and if indeed there is no deletion I'll send you some logs. - id and part.id are not required by the partitioner but the duplicator. Andy, are they required by the loader if no duplication is performed? I've observed that id seems to be required by the loader or it fails, I'll check again tomorrow and let you know. - I'll comment a configuration file to enhances parameters required by partitioner/duplicator/css loader/mysql loader. Would it be possible to have some modularity and run the loader step by step in order to easily track error? - For now it seems id field is used by the loader, but "id = deepSourceId" crash the loading of table AvgForcedPhotYearly (whose PK is: (deepSourceId, year)): [DEBUG] Loader: query: INSERT INTO qservTest_case03_qserv__AvgForcedPhotYearly SELECT deepSourceId, chunkId, subChunkId FROM qservTest_case03_qserv.AvgForcedPhotYearly_6995 [DEBUG] Loader: Deleting directory: /home/qserv/qserv-run/2014_12/tmp/loader_chunks/AvgForcedPhotYearly/tmplGJJTj [DEBUG] Loader: Deleting directory: /home/qserv/qserv-run/2014_12/tmp/loader_chunks/AvgForcedPhotYearly [CRITICAL] root: Exception occured: (1062, "Duplicate entry '1398579058967235' for key 'PRIMARY'") Traceback (most recent call last): File "/home/qserv/src/qserv/bin/qserv-data-loader.py", line 249, in <module> sys.exit(loader.run()) File "/home/qserv/src/qserv/bin/qserv-data-loader.py", line 217, in run self.loader.load(self.args.database, self.args.table, self.args.schema, self.args.data) File "/home/qserv/src/qserv/lib/python/lsst/qserv/admin/dataLoader.py", line 147, in load return self._run(database, table, schema, data) File "/home/qserv/src/qserv/lib/python/lsst/qserv/admin/dataLoader.py", line 188, in _run self._makeIndex(database, table) File "/home/qserv/src/qserv/lib/python/lsst/qserv/admin/dataLoader.py", line 710, in _makeIndex cursor.execute(q) File "build/bdist.linux-x86_64/egg/MySQLdb/cursors.py", line 174, in execute self.errorhandler(self, exc, value) File "build/bdist.linux-x86_64/egg/MySQLdb/connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue IntegrityError: (1062, "Duplicate entry '1398579058967235' for key 'PRIMARY'") -- 2014-12-18 18:08:15,190 CRITICAL Error code returned by command : qserv-data-loader.py --verbose-all -vvv --config=/home/qserv/src/qserv_testdata/datasets/case03/data/common.cfg --css-remove --user=root --password=changeme --socket=/home/qserv/qserv-run/2014_12/var/lib/mysql/mysql.sock --empty-chunks=/home/qserv/qserv-run/2014_12/var/lib/qserv/empty_qservTest_case03_qserv.txt --delete-tables --config=/home/qserv/src/qserv_testdata/datasets/case03/data/AvgForcedPhotYearly.cfg --chunks-dir=/home/qserv/qserv-run/2014_12/tmp/loader_chunks/AvgForcedPhotYearly qservTest_case03_qserv AvgForcedPhotYearly /home/qserv/src/qserv_testdata/datasets/case03/data/AvgForcedPhotYearly.sql /home/qserv/src/qserv_testdata/datasets/case03/data/AvgForcedPhotYearly.txt.gz Le 18/12/2014 19:44, Salnikov, Andrei A. a écrit : > Hi Fabrice, > > can you send me the log file with full debug (--verbose-all -v -v options)? > "--css-remove" is supposed to do the same thing as qserv-admin's "DROP TABLE". > What do you see that tells you that it's not working? > > Cheers, > Andy > > > Fabrice Jammes wrote on 2014-12-18: >> On 12/18/2014 06:38 PM, Fabrice Jammes wrote: >>> My mistake, >>> >>> Indeed, these params are correct in css but not in cfg files, I have >>> removed them for test purpose and to be compliant with original >>> examples. >>> >>> Andy, it seems the loader doesn't remove css data before reloading >>> although I provided "--css-remove" option, >>> Shall I remove everything in the db/css using "DROP TABLE" in >>> qserv-admin.py, before launching the data-loader? >>> >>> Thanks, >>> >>> Fabrice >>> >>> >>> On 12/18/2014 06:26 PM, Fabrice Jammes wrote: >>>> It seems it is: >>>> >>>> Database changed >>>> mysql> show tables; >>>> +----------------------------------+ >>>> | Tables_in_qservTest_case01_qserv | >>>> +----------------------------------+ >>>> | Filter | >>>> | LeapSeconds | >>>> | Logs | >>>> | Object | >>>> | ObjectFullOverlap_6630 | >>>> | ObjectFullOverlap_6631 | >>>> | ObjectFullOverlap_6800 | >>>> | ObjectFullOverlap_6801 | >>>> | ObjectFullOverlap_6968 | >>>> | ObjectFullOverlap_6970 | >>>> | ObjectFullOverlap_6971 | >>>> | ObjectFullOverlap_7138 | >>>> | ObjectFullOverlap_7140 | >>>> | ObjectFullOverlap_7308 | >>>> | ObjectFullOverlap_7478 | >>>> | ObjectFullOverlap_7648 | >>>> | Object_1234567890 | >>>> | Object_6630 | >>>> | Object_6631 | >>>> | Object_6800 | >>>> | Object_6801 | >>>> | Object_6968 | >>>> | Object_6970 | >>>> | Object_6971 | >>>> | Object_7138 | >>>> | Object_7140 | >>>> | Object_7308 | >>>> | Object_7310 | >>>> | Object_7478 | >>>> | Object_7648 | >>>> | RefSrcMatch | >>>> | Science_Ccd_Exposure | >>>> | SimRefObject | >>>> | Source | >>>> | Source_1234567890 | >>>> | Source_6630 | >>>> | Source_6631 | >>>> | Source_6800 | >>>> | Source_6801 | >>>> | Source_6968 | >>>> | Source_6970 | >>>> | Source_6971 | >>>> | Source_7138 | >>>> | Source_7140 | >>>> | Source_7308 | >>>> | Source_7310 | >>>> | Source_7478 | >>>> | Source_7648 | >>>> | sdqa_Metric | >>>> +----------------------------------+ >>>> 49 rows in set (0,00 sec) >>>> >>>> >>>> On 12/18/2014 06:20 PM, Serge Monkewitz wrote: >>>>> On Dec 18, 2014, at 6:12 PM, Fabrice Jammes >>>>> <[log in to unmask]> wrote: >>>>> >>>>>> DeepSource si a view, shouldn't it be RunDeepSource the director >>>>>> table? >>>>> In the Winter2013 release, yes it is. But RunDeepSource contains a >>>>> bunch of extra columns and uses pipeline column naming conventions, >>>>> so I just dumped from the view instead. Note I had to do other >>>>> things to make it work in qserv (IIRC I changed the type of BIT >>>>> columns to TINYINT). Unless the goal is to duplicate the plain mysql >>>>> setup from stripe 82 data as closely as possible, let's not get hung >>>>> up on these details. >>>>> >>>>>> Furthermore, it seems dirTable and dirColName are optional for now, >>>>>> isn't it? Andy doesn't set it in test case01 and 02 and it works. >>>>> If you don't specify them though, the query analysis code will not >>>>> let you join between related partitioned tables. Is the data in test >>>>> case01/02 being partitioned at all? >>>>> >>>>> Serge >>>>> >>>>> ###################################################################### >>>>> ## >>>>> >>>>> Use REPLY-ALL to reply to list >>>>> >>>>> To unsubscribe from the QSERV-L list, click the following link: >>>>> https://listserv.slac.stanford.edu/cgi-bin/wa?SUBED1=QSERV-L&A=1 >> ######################################################################## >> Use REPLY-ALL to reply to list >> >> To unsubscribe from the QSERV-L list, click the following link: >> https://listserv.slac.stanford.edu/cgi-bin/wa?SUBED1=QSERV-L&A=1 > > > ######################################################################## > Use REPLY-ALL to reply to list > > To unsubscribe from the QSERV-L list, click the following link: > https://listserv.slac.stanford.edu/cgi-bin/wa?SUBED1=QSERV-L&A=1 ######################################################################## Use REPLY-ALL to reply to list To unsubscribe from the QSERV-L list, click the following link: https://listserv.slac.stanford.edu/cgi-bin/wa?SUBED1=QSERV-L&A=1