MySQL trackDB table
123 views
Skip to first unread message
Andrey Kartashov
Jun 6, 2014, 5:26:24 PM6/6/14
to gen...@soe.ucsc.edu
Hi,
I have local mirror of UCSC genome browser. I have created mm10.trackDb_local table and have included it into hg.conf file. My scripts automatically generates UUID for tables name and I inserted them into mm10.trackDb_local. Everything works fine except sometimes Genome Browser come up with next error:
- table 278E986C_851F_D9AD_B7ED_692939520610_wtrack doesn't exist or hFindTableInfoDb failed
- bedGraphLoadItems: table 278E986C_851F_D9AD_B7ED_692939520610_wtrack only has 0 data columns, must be at least 4
But table exists and has all required column.
I have checked rights, table existence what else I can check to avoid errors like this?
Thanks,
Andrey
Hiram Clawson
Jun 6, 2014, 5:35:48 PM6/6/14
to Andrey Kartashov, gen...@soe.ucsc.edu
Good Afternoon Andrey:
My guess is that the underscore _ character in the table names may be causing trouble.
This character is a special match character in SQL string comparisons.
There may be some of this going on.
http://dev.mysql.com/doc/refman/5.6/en/string-comparison-functions.html
Plus, the UCSC genome browser attaches unique meaning to table names
with _ characters under some circumstances. This may upset the
ability of the browser code from finding the tables.
I would try eliminating the _ character in table names.
--Hiram
My guess is that the underscore _ character in the table names may be causing trouble.
This character is a special match character in SQL string comparisons.
There may be some of this going on.
http://dev.mysql.com/doc/refman/5.6/en/string-comparison-functions.html
Plus, the UCSC genome browser attaches unique meaning to table names
with _ characters under some circumstances. This may upset the
ability of the browser code from finding the tables.
I would try eliminating the _ character in table names.
--Hiram
Andrey Kartashov
Jun 6, 2014, 5:49:34 PM6/6/14
to Hiram Clawson, gen...@soe.ucsc.edu
Hi Hiram,
Thank you for a quick response I will try it right now. I just surprise that this kind of Genome Browser behavior not for all of my tables which are the same format (UUID , replace ‘-‘ to ‘_’, add ‘_wtrack’)
Thank you for a quick response I will try it right now. I just surprise that this kind of Genome Browser behavior not for all of my tables which are the same format (UUID , replace ‘-‘ to ‘_’, add ‘_wtrack’)
Andrey Kartashov
Jun 6, 2014, 6:01:50 PM6/6/14
to Hiram Clawson, gen...@soe.ucsc.edu
Hi Hiram,
It does not work I’ve changed ‘_’ to ‘'
- table 278E986C851FD9ADB7ED692939520610wtrack doesn't exist or hFindTableInfoDb failed
- bedGraphLoadItems: table 278E986C851FD9ADB7ED692939520610wtrack only has 0 data columns, must be at least 4
but describe mm10.278E986C851FD9ADB7ED692939520610wtrack returns
bin smallint(5) unsigned NO
chrom varchar(255) NO
chromStart int(10) unsigned NO
chromEnd int(10) unsigned NO
name varchar(255) NO
Andrey Kartashov
Jun 6, 2014, 6:11:31 PM6/6/14
to Hiram Clawson, gen...@soe.ucsc.edu
The track is listed in the track list when I go to mm10 genome. If I choose ‘full’ -> submit then error occurred. If I just click the title (link goes to hgTrack) I’ve got another error for this particular table
- Can't start query:
SELECT COUNT(*) FROM chr1_278E986C851FD9ADB7ED692939520610wtrack - mySQL error 1146: Table 'mm10.chr1_278E986C851FD9ADB7ED692939520610wtrack' doesn't exist (profile=, host=localhost, db=mm10)
Hiram Clawson
Jun 6, 2014, 6:18:57 PM6/6/14
to Andrey Kartashov, gen...@soe.ucsc.edu
Good Afternoon Andrey:
I'm guessing your scripts are making tracks on the fly:
1. add track table to mm10 database
2. insert row to trackDb_local
You may need to run a mysql 'flush tables' operation after creation
of the tables, and your additions to trackDb_local, to make sure they
are known to all bits of mysql.
Or does the table stay missing forever, or is it intermittent ?
Does it eventually show up and remain working ?
You can turn on JKSQL_TRACE in src/hg/lib/jksql.c monitorInit() function
to see all SQL operations in the apache error log for your genome browser
to perhaps get a hint of what SQL operation is failing.
--Hiram
I'm guessing your scripts are making tracks on the fly:
1. add track table to mm10 database
2. insert row to trackDb_local
You may need to run a mysql 'flush tables' operation after creation
of the tables, and your additions to trackDb_local, to make sure they
are known to all bits of mysql.
Or does the table stay missing forever, or is it intermittent ?
Does it eventually show up and remain working ?
You can turn on JKSQL_TRACE in src/hg/lib/jksql.c monitorInit() function
to see all SQL operations in the apache error log for your genome browser
to perhaps get a hint of what SQL operation is failing.
--Hiram
Andrey Kartashov
Jun 6, 2014, 6:34:53 PM6/6/14
to Hiram Clawson, gen...@soe.ucsc.edu
For me it looks like much easy to recompile then find how to turn on JKSQL_TRACE. If you can give a hint how to setup an environment variable for apache
val = getenv("JKSQL_TRACE");
if ((val != NULL) && sameString(val, "on"))
flags |= JKSQL_TRACE;
val = getenv("JKSQL_PROF");
if ((val != NULL) && sameString(val, "on"))
flags |= JKSQL_PROF;
if (flags != 0)
sqlMonitorEnable(flags);
Hiram Clawson
Jun 6, 2014, 6:35:22 PM6/6/14
to Andrey Kartashov, gen...@soe.ucsc.edu
Your name prefix chr1_ is exactly the type of underscore name that the browser
uses to identify split tables. We have recently eliminated some of this confusion
as of January 2014. Is your genome browser code newer than January this year ?
> For me it looks like much easy to recompile then find how to turn on JKSQL_TRACE. If you can give a hint how to setup an environment variable for apache
Yes, recompile jksql.c to make it easy. I don't know how to set Apache env variables.
You can place them on the URL in your WEB browser:
.../cgi-bin/hgTracks?hgsid=xxx&JKSQL_TRACE=on&otherVars=...
--Hiram
uses to identify split tables. We have recently eliminated some of this confusion
as of January 2014. Is your genome browser code newer than January this year ?
> For me it looks like much easy to recompile then find how to turn on JKSQL_TRACE. If you can give a hint how to setup an environment variable for apache
You can place them on the URL in your WEB browser:
.../cgi-bin/hgTracks?hgsid=xxx&JKSQL_TRACE=on&otherVars=...
--Hiram
Andrey Kartashov
Jun 6, 2014, 6:57:59 PM6/6/14
to Hiram Clawson, gen...@soe.ucsc.edu
I have just updated everything one week ago should be 300v
Andrey Kartashov
Jun 6, 2014, 7:15:12 PM6/6/14
to Hiram Clawson, gen...@soe.ucsc.edu
Hi Hiram,
Hope you are not going to miss friday evening. Finally I have something in error log
[Fri Jun 06 19:07:50.873710 2014] [cgi:error] [pid 93556] [client 10.36.24.15:58975] AH01215: SQL_QUERY 15916 localhost mm10 NOSQLINJ select * from grp_local
[Fri Jun 06 19:07:50.873942 2014] [cgi:error] [pid 93556] [client 10.36.24.15:58975] AH01215: SQL_TIME 15916 localhost mm10 0.000s
[Fri Jun 06 19:07:50.873957 2014] [cgi:error] [pid 93556] [client 10.36.24.15:58975] AH01215: SQL_FETCH 15916 localhost mm10 0.000s
[Fri Jun 06 19:07:50.878532 2014] [cgi:error] [pid 93556] [client 10.36.24.15:58975] AH01215: SQL_QUERY 15916 localhost mm10 NOSQLINJ SELECT 1 FROM 278E986C_851F_D9AD_B7ED_692939520610_wtrack LIMIT 0
[Fri Jun 06 19:07:50.878595 2014] [cgi:error] [pid 93556] [client 10.36.24.15:58975] AH01215: SQL_TIME 15916 localhost mm10 0.000s
[Fri Jun 06 19:07:50.878625 2014] [cgi:error] [pid 93556] [client 10.36.24.15:58975] AH01215: SQL_QUERY 15916 localhost mm10 NOSQLINJ SELECT 1 FROM chr12_278E986C_851F_D9AD_B7ED_692939520610_wtrack LIMIT 0
[Fri Jun 06 19:07:50.878974 2014] [cgi:error] [pid 93556] [client 10.36.24.15:58975] AH01215: SQL_TIME 15916 localhost mm10 0.000s
[Fri Jun 06 19:07:50.879401 2014] [cgi:error] [pid 93556] [client 10.36.24.15:58975] AH01215: [Fri Jun 6 19:07:50 201] [hgTracks] [client 10.36.24.15] [hgsid=4406_ZBsaUmNSwma2z19MFl0] [/cgi-bin/hgTracks?JKSQL_TRACE=on&db=mm10&position=chr12%3A56694976-56714605&hgsid=4406_ZBsaUmNSwma2z19MFl0lfWc9PA8l] table 278E986C_851F_D9AD_B7ED_692939520610_wtrack doesn't exist or hFindTableInfoDb failed[Fri Jun 6 19:07:50 201] [hgTracks] [client 10.36.24.15] [hgsid=4406_ZBsaUmNSwma2z19MFl0] [/cgi-bin/hgTracks?JKSQL_TRACE=on&db=mm10&position=chr12%3A56694976-56714605&hgsid=4406_ZBsaUmNSwma2z19MFl0lfWc9PA8l] bedGraphLoadItems: table 278E986C_851F_D9AD_B7ED_692939520610_wtrack only has 0 data columns, must be at least 4SQL_QUERY 15915 localhost hgcentral NOSQLINJ UPDATE userDb SET contents='clade=mammal&hgtgroup_b5112104-e866-11e3-9546-ac162d784858_close=1&hgtgroup_b511207d-e866-11e3-9546-ac162d784858_close=1&hgtgroup_b5112151-e866-11e3-9546-ac162d784858_close=1&hgtgroup_b5112164-e866-11e3-9546-ac162d784858_close=1&lastPosition=chr12%3A56694976%2D56714605&hgtgroup_b51120de-e866-11e3-9546-ac162d784858_close=1&hgtgroup_b5060c78-e866-11e3-9546-ac162d784858_close=1&trackHubs=&hgtgroup_b5112016-e866-11e3-9546-ac162d784858_close=1&hgtgroup_b51120cc-e866-11e3-9546-ac162d784858_close=1&hgtgroup_b5112069-e866-11e3-9546-ac162d784858_close=1&hgtgroup_b51121af-e866-11e3-9546-ac162d784858_close=1&hgtgroup_varRep_close=0&hgtgroup_b51120f1-e866-11e3-9546-ac162d784858_close=0&pix=1614&org=Mouse&hgtgroup_b511203a-e866-11e3-9546-ac162d784858_close=1&hgtgroup_b5112091-e866-11e3-9546-ac162d784858_close=1&C7888292_EB68_44A5_2C67_2EEFCCE06A49_grp=hide&hgtgroup_rna_close=0&hgtgroup_compGeno_close=0&knownGene.label.gene=1&hgtgroup_regulation_close=0&hgtgroup_b5112052-e866-11e3-9546-ac162d784858_close=1&dinkR=2.0&dinkL=2.0&hgtgroup_b511219d-e866-11e3-9546-ac162d784858_close=1&278E986C_851F_D9AD_B7ED_692939520610_grp=full&hgtgroup_b51121c2-e866-11e3-9546-ac162d784858_close=1&hgHubConnect.destUrl=..%2Fcgi%2Dbin%2FhgTracks&db=mm10&hgt_doJsCommand=&hgtgroup_b51120a6-e866-11e3-9546-ac162d784858_close=1&position=chr12%3A56694976%2D56714605&hgtgroup_b51120b9-e866-11e3-9546-ac162d784858_close=1&hgtgroup_b511212b-e866-11e3-9546-ac162d784858_close=1&JKSQL_TRACE=on&hgtgroup_b5112116-e866-11e3-9546-ac162d784858_close=1&ucscRetroAli2.label.gene=1&r=56714605&l=56694975&hgtgroup_map_close=0&hgtgroup_b5112176-e866-11e3-9546-ac162d784858_close=1&c=chr12&c=chr12&_=1402095144950&refGene.label.gene=1&xenoRefGene.label.gene=1&hgtgroup_genes_close=0&hgtgroup_b5112189-e866-11e3-9546-ac162d784858_close=1',lastUse=now(),useCount=1 where id=46992 and sessionKey='iF81YKIk90GawsgPYLOHMkf3MeB3'
[Fri Jun 06 19:07:50.879424 2014] [cgi:error] [pid 93556] [client 10.36.24.15:58975] AH01215: SQL_TIME 15915 localhost hgcentral 0.000s
[Fri Jun 06 19:07:50.879805 2014] [cgi:error] [pid 93556] [client 10.36.24.15:58975] AH01215: SQL_QUERY 15915 localhost hgcentral NOSQLINJ UPDATE sessionDb SET contents='clade=mammal&hgtgroup_b5112104-e866-11e3-9546-ac162d784858_close=1&hgtgroup_b511207d-e866-11e3-9546-ac162d784858_close=1&hgtgroup_b5112151-e866-11e3-9546-ac162d784858_close=1&hgtgroup_b5112164-e866-11e3-9546-ac162d784858_close=1&lastPosition=chr12%3A56694976%2D56714605&hgtgroup_b51120de-e866-11e3-9546-ac162d784858_close=1&hgtgroup_b5060c78-e866-11e3-9546-ac162d784858_close=1&trackHubs=&hgtgroup_b5112016-e866-11e3-9546-ac162d784858_close=1&hgtgroup_b51120cc-e866-11e3-9546-ac162d784858_close=1&hgtgroup_b5112069-e866-11e3-9546-ac162d784858_close=1&hgtgroup_b51121af-e866-11e3-9546-ac162d784858_close=1&hgtgroup_varRep_close=0&hgtgroup_b51120f1-e866-11e3-9546-ac162d784858_close=0&pix=1614&org=Mouse&hgtgroup_b511203a-e866-11e3-9546-ac162d784858_close=1&hgtgroup_b5112091-e866-11e3-9546-ac162d784858_close=1&C7888292_EB68_44A5_2C67_2EEFCCE06A49_grp=hide&hgtgroup_rna_close=0&hgtgroup_compGeno_close=0&knownGene.label.gene=1&hgtgroup_regulation_close=0&hgtgroup_b5112052-e866-11e3-9546-ac162d784858_close=1&dinkR=2.0&dinkL=2.0&hgtgroup_b511219d-e866-11e3-9546-ac162d784858_close=1&278E986C_851F_D9AD_B7ED_692939520610_grp=full&hgtgroup_b51121c2-e866-11e3-9546-ac162d784858_close=1&hgHubConnect.destUrl=..%2Fcgi%2Dbin%2FhgTracks&db=mm10&hgt_doJsCommand=&hgtgroup_b51120a6-e866-11e3-9546-ac162d784858_close=1&position=chr12%3A56694976%2D56714605&hgtgroup_b51120b9-e866-11e3-9546-ac162d784858_close=1&hgtgroup_b511212b-e866-11e3-9546-ac162d784858_close=1&JKSQL_TRACE=on&hgtgroup_b5112116-e866-11e3-9546-ac162d784858_close=1&ucscRetroAli2.label.gene=1&r=56714605&l=56694975&hgtgroup_map_close=0&hgtgroup_b5112176-e866-11e3-9546-ac162d784858_close=1&c=chr12&c=chr12&_=1402095144950&refGene.label.gene=1&xenoRefGene.label.gene=1&hgtgroup_genes_close=0&hgtgroup_b5112189-e866-11e3-9546-ac162d784858_close=1',lastUse=now(),useCount=20 where id=4406 and sessionKey='ZBsaUmNSwma2z19MFl0lfWc9PA8l'
[Fri Jun 06 19:07:50.879821 2014] [cgi:error] [pid 93556] [client 10.36.24.15:58975] AH01215: SQL_TIME 15915 localhost hgcentral 0.000s
[Fri Jun 06 19:07:50.879831 2014] [cgi:error] [pid 93556] [client 10.36.24.15:58975] AH01215: CGI_TIME: hgTracks: Overall total time: 49 millis
[Fri Jun 06 19:07:50.879838 2014] [cgi:error] [pid 93556] [client 10.36.24.15:58975] AH01215: SQL_DISCONNECT 15914 hgcentral
[Fri Jun 06 19:07:50.879847 2014] [cgi:error] [pid 93556] [client 10.36.24.15:58975] AH01215: SQL_TIME 15914 localhost hgcentral 0.000s
[Fri Jun 06 19:07:50.879854 2014] [cgi:error] [pid 93556] [client 10.36.24.15:58975] AH01215: SQL_DISCONNECT 15915 hgcentral
[Fri Jun 06 19:07:50.879862 2014] [cgi:error] [pid 93556] [client 10.36.24.15:58975] AH01215: SQL_TIME 15915 localhost hgcentral 0.000s
[Fri Jun 06 19:07:50.879869 2014] [cgi:error] [pid 93556] [client 10.36.24.15:58975] AH01215: SQL_DISCONNECT 15916 mm10
[Fri Jun 06 19:07:50.879876 2014] [cgi:error] [pid 93556] [client 10.36.24.15:58975] AH01215: SQL_TIME 15916 localhost mm10 0.000s
[Fri Jun 06 19:07:50.879883 2014] [cgi:error] [pid 93556] [client 10.36.24.15:58975] AH01215: SQL_DISCONNECT 15917 hgFixed
[Fri Jun 06 19:07:50.879891 2014] [cgi:error] [pid 93556] [client 10.36.24.15:58975] AH01215: SQL_TIME 15917 localhost hgFixed 0.000s
Hiram Clawson
Jun 6, 2014, 7:18:34 PM6/6/14
to Andrey Kartashov, gen...@soe.ucsc.edu
Good Afternoon Andrey:
The browser code is stripping your chr12_ prefix from the table name
as it attempts to find the split tables. That might be a bug we can
fix here some time in the future. That would require some investigation
here. In the meantime, eliminate the prefix chr12_ and simply
use the UUID table name you are generating.
--Hiram
The browser code is stripping your chr12_ prefix from the table name
as it attempts to find the split tables. That might be a bug we can
fix here some time in the future. That would require some investigation
here. In the meantime, eliminate the prefix chr12_ and simply
use the UUID table name you are generating.
--Hiram
Andrey Kartashov
Jun 6, 2014, 7:31:39 PM6/6/14
to Hiram Clawson, gen...@soe.ucsc.edu
I can’t eliminate chr12_ it is not in the table name, it comes from position=chr12...
Hiram Clawson
Jun 6, 2014, 7:33:11 PM6/6/14
to Andrey Kartashov, gen...@soe.ucsc.edu
In that case we will have to attempt to recreate this situation here.
I don't know what the problem is.
Does the browser stay in this error state even after you manually
examine the table ?
How is the table created ?
I don't know what the problem is.
Does the browser stay in this error state even after you manually
examine the table ?
How is the table created ?
Andrey Kartashov
Jun 6, 2014, 7:44:50 PM6/6/14
to Hiram Clawson, gen...@soe.ucsc.edu
I have approx 700 tracks created the same way and this is a first time error. I think the problem with this particular table name some how this particular name make this error. I think you can simply rename arbitrary table to this name and error should appear.
select * from mm10.trackDb_local where tablename like '278E986C_851F_D9AD_B7ED_692939520610%’
278E986C_851F_D9AD_B7ED_692939520610_grp H3K4Me1 LPS 00Hrs B cells bed 4 + H3K4Me1 LPS 00Hrs B cells 0 10 30 70 150 30 70 150 0 0 0 b51120f1-e866-11e3-9546-ac162d784858 0
compositeTrack on
group b51120f1-e866-11e3-9546-ac162d784858
track 278E986C_851F_D9AD_B7ED_692939520610_grp
278E986C_851F_D9AD_B7ED_692939520610_wtrack H3K4Me1 LPS 00Hrs B cells bedGraph 4 H3K4Me1 LPS 00Hrs B cells 0 10 30 70 150 30 70 150 0 0 0 b51120f1-e866-11e3-9546-ac162d784858 0 parent 278E986C_851F_D9AD_B7ED_692939520610_grp
track 278E986C_851F_D9AD_B7ED_692939520610_wtrack
autoScale on
windowingFunction maximum
278E986C_851F_D9AD_B7ED_692939520610_islands H3K4Me1 LPS 00Hrs B cells bed 4 + H3K4Me1 LPS 00Hrs B cells 0 10 0 30 100 30 70 150 0 0 0 b51120f1-e866-11e3-9546-ac162d784858 1 parent 278E986C_851F_D9AD_B7ED_692939520610_grp
track 278E986C_851F_D9AD_B7ED_692939520610_islands
visibility dense
Some examples of working tables:
b4f7dfd0_e866_11e3_9546_ac162d784858_grp PS_RNApolII_Reinberg bed 4 + PS_RNApolII_Reinberg 0 10 30 70 150 30 70 150 0 0 0 b5112176-e866-11e3-9546-ac162d784858 0
compositeTrack on
group b5112176-e866-11e3-9546-ac162d784858
track b4f7dfd0_e866_11e3_9546_ac162d784858_grp
b4f7dfd0_e866_11e3_9546_ac162d784858_wtrack PS_RNApolII_Reinberg bedGraph 4 PS_RNApolII_Reinberg 0 10 30 70 150 30 70 150 0 0 0 b5112176-e866-11e3-9546-ac162d784858 0 parent b4f7dfd0_e866_11e3_9546_ac162d784858_grp
track b4f7dfd0_e866_11e3_9546_ac162d784858_wtrack
autoScale on
windowingFunction maximum
b4f7dfd0_e866_11e3_9546_ac162d784858_islands PS_RNApolII_Reinberg bed 4 + PS_RNApolII_Reinberg 0 10 0 30 100 30 70 150 0 0 0 b5112176-e866-11e3-9546-ac162d784858 1 parent b4f7dfd0_e866_11e3_9546_ac162d784858_grp
track b4f7dfd0_e866_11e3_9546_ac162d784858_islands
visibility dense
select * from mm10.trackDb_local where tablename like '278E986C_851F_D9AD_B7ED_692939520610%’
278E986C_851F_D9AD_B7ED_692939520610_grp H3K4Me1 LPS 00Hrs B cells bed 4 + H3K4Me1 LPS 00Hrs B cells 0 10 30 70 150 30 70 150 0 0 0 b51120f1-e866-11e3-9546-ac162d784858 0
compositeTrack on
group b51120f1-e866-11e3-9546-ac162d784858
track 278E986C_851F_D9AD_B7ED_692939520610_grp
278E986C_851F_D9AD_B7ED_692939520610_wtrack H3K4Me1 LPS 00Hrs B cells bedGraph 4 H3K4Me1 LPS 00Hrs B cells 0 10 30 70 150 30 70 150 0 0 0 b51120f1-e866-11e3-9546-ac162d784858 0 parent 278E986C_851F_D9AD_B7ED_692939520610_grp
track 278E986C_851F_D9AD_B7ED_692939520610_wtrack
autoScale on
windowingFunction maximum
278E986C_851F_D9AD_B7ED_692939520610_islands H3K4Me1 LPS 00Hrs B cells bed 4 + H3K4Me1 LPS 00Hrs B cells 0 10 0 30 100 30 70 150 0 0 0 b51120f1-e866-11e3-9546-ac162d784858 1 parent 278E986C_851F_D9AD_B7ED_692939520610_grp
track 278E986C_851F_D9AD_B7ED_692939520610_islands
visibility dense
Some examples of working tables:
b4f7dfd0_e866_11e3_9546_ac162d784858_grp PS_RNApolII_Reinberg bed 4 + PS_RNApolII_Reinberg 0 10 30 70 150 30 70 150 0 0 0 b5112176-e866-11e3-9546-ac162d784858 0
compositeTrack on
group b5112176-e866-11e3-9546-ac162d784858
track b4f7dfd0_e866_11e3_9546_ac162d784858_grp
b4f7dfd0_e866_11e3_9546_ac162d784858_wtrack PS_RNApolII_Reinberg bedGraph 4 PS_RNApolII_Reinberg 0 10 30 70 150 30 70 150 0 0 0 b5112176-e866-11e3-9546-ac162d784858 0 parent b4f7dfd0_e866_11e3_9546_ac162d784858_grp
track b4f7dfd0_e866_11e3_9546_ac162d784858_wtrack
autoScale on
windowingFunction maximum
b4f7dfd0_e866_11e3_9546_ac162d784858_islands PS_RNApolII_Reinberg bed 4 + PS_RNApolII_Reinberg 0 10 0 30 100 30 70 150 0 0 0 b5112176-e866-11e3-9546-ac162d784858 1 parent b4f7dfd0_e866_11e3_9546_ac162d784858_grp
track b4f7dfd0_e866_11e3_9546_ac162d784858_islands
visibility dense
Andrey Kartashov
Jun 6, 2014, 7:45:53 PM6/6/14
to Hiram Clawson, gen...@soe.ucsc.edu
Hiram,
Thank you very much for your attention and time friday evening. Do not hesitate to contact me for any additional info and output in any time.
Thanks,
Andrey
Thank you very much for your attention and time friday evening. Do not hesitate to contact me for any additional info and output in any time.
Thanks,
Andrey
Hiram Clawson
Jun 6, 2014, 9:16:45 PM6/6/14
to Andrey Kartashov, gen...@soe.ucsc.edu
Good Afternoon Andrey:
Something about the table name is causing mysql to choke up.
When I make up the create statement:
CREATE TABLE 278E986C_851F_D9AD_B7ED_692939520610_wtrack (
bin smallint unsigned not null,
chrom varchar(255) not null,
chromStart int unsigned not null,
chromEnd int unsigned not null,
dataValue float not null,
#Indices
INDEX(chrom(6),bin)
)
And ask MySQL to create that table, it responds:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'278E986C_851F_D9AD_B7ED_692939520610_wtrack (
bin smallint unsigned not null,
' at line 1
If I add an extra character in front of the name:
CREATE TABLE E278E986C_851F_D9AD_B7ED_692939520610_wtrack (
bin smallint unsigned not null,
chrom varchar(255) not null,
chromStart int unsigned not null,
chromEnd int unsigned not null,
dataValue float not null,
#Indices
INDEX(chrom(6),bin)
)
Or take off a couple of the first characters, it will construct the table.
I can't tell what the pattern is that is causing trouble, but evidently
MySQL is recognizing something in the name that it doesn't like.
Perhaps the secret NSA back door key or something.
--Hiram
Something about the table name is causing mysql to choke up.
When I make up the create statement:
CREATE TABLE 278E986C_851F_D9AD_B7ED_692939520610_wtrack (
bin smallint unsigned not null,
chrom varchar(255) not null,
chromStart int unsigned not null,
chromEnd int unsigned not null,
dataValue float not null,
#Indices
INDEX(chrom(6),bin)
)
And ask MySQL to create that table, it responds:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'278E986C_851F_D9AD_B7ED_692939520610_wtrack (
bin smallint unsigned not null,
' at line 1
If I add an extra character in front of the name:
CREATE TABLE E278E986C_851F_D9AD_B7ED_692939520610_wtrack (
bin smallint unsigned not null,
chrom varchar(255) not null,
chromStart int unsigned not null,
chromEnd int unsigned not null,
dataValue float not null,
#Indices
INDEX(chrom(6),bin)
)
Or take off a couple of the first characters, it will construct the table.
I can't tell what the pattern is that is causing trouble, but evidently
MySQL is recognizing something in the name that it doesn't like.
Perhaps the secret NSA back door key or something.
--Hiram
Hiram Clawson
Jun 6, 2014, 9:51:25 PM6/6/14
to Andrey Kartashov, gen...@soe.ucsc.edu
I don't see the requirement for any quoting of table names:
http://dev.mysql.com/doc/refman/5.6/en/create-table.html
> The table name can be specified as db_name.tbl_name to create the table
> in a specific database. This works regardless of whether there is a default
> database, assuming that the database exists. If you use quoted identifiers,
> quote the database and table names separately. For example,
> write `mydb`.`mytbl`, not `mydb.mytbl`.
The quotes appear to be optional. Probably required for unusual characters
in the names.
We have no code in the genome browser that adds quotes around
the table names. I guess we have always been within our expected
character set.
HOWEVER, this doesn't explain this current situation. Your name appears
to be perfectly ordinary. I see no unusual characters in the name.
I am very curious what it is that MySQL is objecting to in this seemingly
ordinary name.
On 6/6/14 6:43 PM, Andrey Kartashov wrote:
> Hiram,
>
>
>
>
> On Jun 6, 2014, at 9:37 PM, Andrey Kartashov <por...@porter.st> wrote:
>
>> Sorry you are not precisely right, the syntax to create table in MySQL is:
>>
>> CREATE TABLE `278E986C_851F_D9AD_B7ED_692939520610_wtrack` (
>> bin smallint unsigned not null,
>> chrom varchar(255) not null,
>> chromStart int unsigned not null,
>> chromEnd int unsigned not null,
>> dataValue float not null,
>> #Indices
>> INDEX(chrom(6),bin)
>> )
>>
>> MySQL creates it without any problem.
>> CREATE TABLE `278E986C_851F_D9AD_B7ED_692939520610_wtrack` (
>> bin smallint unsigned not null,
>> chrom varchar(255) not null,
>> chromStart int unsigned not null,
>> chromEnd int unsigned not null,
>> dataValue float not null,
>> #Indices
>> INDEX(chrom(6),bin)
>> )
>>
Hiram Clawson
Jun 6, 2014, 9:53:59 PM6/6/14
to Andrey Kartashov, gen...@soe.ucsc.edu
Here is the schema for names:
http://dev.mysql.com/doc/refman/5.6/en/identifiers.html
On 6/6/14 6:51 PM, Andrey Kartashov wrote:
> Hiram,
>
> The big problem is that this unique identifier is linked with other software, which actually generates them, it is not a problem to change it once or two but if it will be a reproducible error, then something has to be changed.
http://dev.mysql.com/doc/refman/5.6/en/identifiers.html
On 6/6/14 6:51 PM, Andrey Kartashov wrote:
> Hiram,
>
Hiram Clawson
Jun 6, 2014, 10:22:49 PM6/6/14
to Andrey Kartashov, gen...@soe.ucsc.edu
I still need to figure out what is going on. There is nothing
wrong with the name of this table according to the MySQL doc or
the browser software. It should be working, I need to find out
why it is not. I have full debugging MySQL source available here
and can find out in that source what it is complaining about.
--Hiram
On 6/6/14 7:15 PM, Andrey Kartashov wrote:
> If you think I have not read that doc you are wrong. Also, as you mentioned ‘’ are not required but they are not forbidden which is mean that table 278E986C_851F_D9AD_B7ED_692939520610_wtrack can exist in MySQL without any problem.
>
> As I can see from the Genome Browser documentation http://genome.ucsc.edu/goldenPath/help/trackDb/trackDbHub.html
>
> This is the name of the dataset and must be unique within the Genome Browser or dataHub. Typically this is the MySQL table name or remote data file root name (without path or suffix). Must begin with a letter and contain only the following chars: [a-zA-Z0-9-_].
>
> It is not a problem in general to add one letter at front of identifier but also it is allowed in the documentation to have ‘-' and ‘_’ I can say for sure that in the code you have checking if ‘-‘ in the table then the name is not allowed
>
> if (strchr(table,'-')).
> {
> return FALSE; // mysql does not allow tables with dash (-) so it will not be found.
> // hg/lib/hdb.c can generate an invalid table names with dashes while looking for split tables,
> // if the first chrom name has a dash in it. Examples found were: scaffold_0.1-193456 scaffold_0.1-13376 HERVE_a-int 1-1
> // Assembly hubs also may have dashes in chrom names.
> }
>
> So you have to change either documentation or software.
wrong with the name of this table according to the MySQL doc or
the browser software. It should be working, I need to find out
why it is not. I have full debugging MySQL source available here
and can find out in that source what it is complaining about.
--Hiram
On 6/6/14 7:15 PM, Andrey Kartashov wrote:
> If you think I have not read that doc you are wrong. Also, as you mentioned ‘’ are not required but they are not forbidden which is mean that table 278E986C_851F_D9AD_B7ED_692939520610_wtrack can exist in MySQL without any problem.
>
> As I can see from the Genome Browser documentation http://genome.ucsc.edu/goldenPath/help/trackDb/trackDbHub.html
>
> This is the name of the dataset and must be unique within the Genome Browser or dataHub. Typically this is the MySQL table name or remote data file root name (without path or suffix). Must begin with a letter and contain only the following chars: [a-zA-Z0-9-_].
>
> It is not a problem in general to add one letter at front of identifier but also it is allowed in the documentation to have ‘-' and ‘_’ I can say for sure that in the code you have checking if ‘-‘ in the table then the name is not allowed
>
> if (strchr(table,'-')).
> {
> return FALSE; // mysql does not allow tables with dash (-) so it will not be found.
> // hg/lib/hdb.c can generate an invalid table names with dashes while looking for split tables,
> // if the first chrom name has a dash in it. Examples found were: scaffold_0.1-193456 scaffold_0.1-13376 HERVE_a-int 1-1
> // Assembly hubs also may have dashes in chrom names.
> }
>
> So you have to change either documentation or software.
Hiram Clawson
Jun 7, 2014, 2:09:22 AM6/7/14
to Andrey Kartashov, gen...@soe.ucsc.edu
Good Evening Andrey:
It may be happen that the name beginning 278E986 could be interpreted
as a number by MySQL. That would be a problem since numbers are
illegal as names without 'protection' quotes. If this is true, then
a work-around would be to insure your generated names always begin
with a letter instead of numbers.
You may have found one place in the browser code which makes this particular
example work. However, for the general case, this would be a major
overhaul of the browser code to quote all names in all cases throughout
the source tree. And if the code did allow any arbitrary name with
quotes, this would cause down-stream processing difficulties for any
data consumers of ours as surely unusual character codes would begin
to infect the data stream. That doesn't sound like a good way to go.
I'm sure this issue will prompt much discussion at our next engineering
meeting.
--Hiram
http://etutorials.org/SQL/MySQL/Part+I+General+MySQL+Use/Chapter+3.+MySQL+SQL+Syntax+and+Use/MySQL+Naming+Rules/
On 6/6/14 10:06 PM, Andrey Kartashov wrote:
> I solve my problem by adding quotes into hdb.c
> sqlDyStringPrintf(query, "`%s` where %s='%s' and ",
> table, hti->chromField, chrom);
>
> And few quotes into jksql.c
>
> On Jun 6, 2014, at 10:51 PM, Andrey Kartashov <por...@porter.st> wrote:
>
>> Good that we are on the same page. If you need something from me I am still ready to send.
>> Thanks,
>> Andrey
It may be happen that the name beginning 278E986 could be interpreted
as a number by MySQL. That would be a problem since numbers are
illegal as names without 'protection' quotes. If this is true, then
a work-around would be to insure your generated names always begin
with a letter instead of numbers.
You may have found one place in the browser code which makes this particular
example work. However, for the general case, this would be a major
overhaul of the browser code to quote all names in all cases throughout
the source tree. And if the code did allow any arbitrary name with
quotes, this would cause down-stream processing difficulties for any
data consumers of ours as surely unusual character codes would begin
to infect the data stream. That doesn't sound like a good way to go.
I'm sure this issue will prompt much discussion at our next engineering
meeting.
--Hiram
http://etutorials.org/SQL/MySQL/Part+I+General+MySQL+Use/Chapter+3.+MySQL+SQL+Syntax+and+Use/MySQL+Naming+Rules/
On 6/6/14 10:06 PM, Andrey Kartashov wrote:
> I solve my problem by adding quotes into hdb.c
> sqlDyStringPrintf(query, "`%s` where %s='%s' and ",
> table, hti->chromField, chrom);
>
> And few quotes into jksql.c
>
> On Jun 6, 2014, at 10:51 PM, Andrey Kartashov <por...@porter.st> wrote:
>
>> Good that we are on the same page. If you need something from me I am still ready to send.
>> Thanks,
>> Andrey
Andrey Kartashov
Jun 9, 2014, 12:21:37 PM6/9/14
to Hiram Clawson, gen...@soe.ucsc.edu
If you think I have not read that doc you are wrong. Also, as you mentioned ‘’ are not required but they are not forbidden which is mean that table 278E986C_851F_D9AD_B7ED_692939520610_wtrack can exist in MySQL without any problem.
As I can see from the Genome Browser documentation http://genome.ucsc.edu/goldenPath/help/trackDb/trackDbHub.html
This is the name of the dataset and must be unique within the Genome Browser or dataHub. Typically this is the MySQL table name or remote data file root name (without path or suffix). Must begin with a letter and contain only the following chars: [
a-zA-Z0-9-_].It is not a problem in general to add one letter at front of identifier but also it is allowed in the documentation to have ‘-' and ‘_’ I can say for sure that in the code you have checking if ‘-‘ in the table then the name is not allowed
if (strchr(table,'-')).
{
return FALSE; // mysql does not allow tables with dash (-) so it will not be found.
// hg/lib/hdb.c can generate an invalid table names with dashes while looking for split tables,
// if the first chrom name has a dash in it. Examples found were: scaffold_0.1-193456 scaffold_0.1-13376 HERVE_a-int 1-1
// Assembly hubs also may have dashes in chrom names.
}
So you have to change either documentation or software.
Andrey Kartashov
Jun 9, 2014, 12:21:37 PM6/9/14
to Hiram Clawson, gen...@soe.ucsc.edu
Hiram,
The big problem is that this unique identifier is linked with other software, which actually generates them, it is not a problem to change it once or two but if it will be a reproducible error, then something has to be changed.
Thanks,
The big problem is that this unique identifier is linked with other software, which actually generates them, it is not a problem to change it once or two but if it will be a reproducible error, then something has to be changed.
Andrey
On Jun 6, 2014, at 9:43 PM, Andrey Kartashov <por...@porter.st> wrote:
> Hiram,
>
> But probably it can be some kind of work around to add letters at front, but from my experience if tableName is not quoted `` then even in the middle can exist a sequence which will cause an error.
>
>
>
> On Jun 6, 2014, at 9:37 PM, Andrey Kartashov <por...@porter.st> wrote:
>
>> Sorry you are not precisely right, the syntax to create table in MySQL is:
>>
>> CREATE TABLE `278E986C_851F_D9AD_B7ED_692939520610_wtrack` (
>> bin smallint unsigned not null,
>> chrom varchar(255) not null,
>> chromStart int unsigned not null,
>> chromEnd int unsigned not null,
>> dataValue float not null,
>> #Indices
>> INDEX(chrom(6),bin)
>> )
>>
>> bin smallint unsigned not null,
>> chrom varchar(255) not null,
>> chromStart int unsigned not null,
>> chromEnd int unsigned not null,
>> dataValue float not null,
>> #Indices
>> INDEX(chrom(6),bin)
>> )
>>
>> MySQL creates it without any problem.
>>
>>
Andrey Kartashov
Jun 9, 2014, 12:21:37 PM6/9/14
to Hiram Clawson, gen...@soe.ucsc.edu
Sorry you are not precisely right, the syntax to create table in MySQL is:
CREATE TABLE `278E986C_851F_D9AD_B7ED_692939520610_wtrack` (
bin smallint unsigned not null,
chrom varchar(255) not null,
chromStart int unsigned not null,
chromEnd int unsigned not null,
dataValue float not null,
#Indices
INDEX(chrom(6),bin)
)
bin smallint unsigned not null,
chrom varchar(255) not null,
chromStart int unsigned not null,
chromEnd int unsigned not null,
dataValue float not null,
#Indices
INDEX(chrom(6),bin)
)
MySQL creates it without any problem.
Andrey Kartashov
Jun 9, 2014, 12:21:37 PM6/9/14
to Hiram Clawson, gen...@soe.ucsc.edu
I solve my problem by adding quotes into hdb.c
sqlDyStringPrintf(query, "`%s` where %s='%s' and ",
table, hti->chromField, chrom);
And few quotes into jksql.c
On Jun 6, 2014, at 10:51 PM, Andrey Kartashov <por...@porter.st> wrote:
Good that we are on the same page. If you need something from me I am still ready to send.
Thanks,
Andrey
Andrey Kartashov
Jun 9, 2014, 12:21:37 PM6/9/14
to Hiram Clawson, gen...@soe.ucsc.edu
Good that we are on the same page. If you need something from me I am still ready to send.
Thanks,
Andrey
Thanks,
Andrey
Andrey Kartashov
Jun 9, 2014, 12:21:37 PM6/9/14
to Hiram Clawson, gen...@soe.ucsc.edu
Hiram,
But probably it can be some kind of work around to add letters at front, but from my experience if tableName is not quoted `` then even in the middle can exist a sequence which will cause an error.
But probably it can be some kind of work around to add letters at front, but from my experience if tableName is not quoted `` then even in the middle can exist a sequence which will cause an error.
Hiram Clawson
Jun 9, 2014, 4:15:49 PM6/9/14
to Andrey Kartashov, gen...@soe.ucsc.edu
Good Afternoon Andrey:
I have confirmed that MySQL recognizes <digits>[Ee]<digits><any string>
as a number and will not accept it as an unquoted table name.
Something as simple as 1e2 or 1e2xyz is recognized as a number.
The mysql code uses strtoll() to convert the exponent, which stops
its scan when it encounters the following string without error.
We won't be altering the kent code to quote table names.
I haven't looked at the code to see what the database name of 'tracks' means
in the kent code.
--Hiram
On 6/7/14 7:02 AM, Andrey Kartashov wrote:
> Good morning Hiram,
> I am totally agree that quotation is big change. I just want to be sure that if I’ll start UUID from a letter that everything else will work fine. I am going to change the behavior of UUID generator first symbol is a letter.
>
> Also as an idea I think the most often tables that are affected by users are grp and trackDb. The format of the table trackDb is not flexible enough, let say I have another database with just tracks ’tracks’. Why I have it? Because hg19 have a tons of tables and just to distinguish original hg19 with my tracks is a good idea otherwise it is a big mess. So I have on the same MySQL server `hg19` database and `tracks` database, Genome Browser will not understand if I put into ‘tableName’ record like `tracks`.`UUID_wtrack`.
> Tracks in trackDb_something is the most convenient way to share data between users within one department or company, but if you’d like to distinguish tracks between two or more departments just by key in url it is also not possible. Can you discuss this ideas on the meeting and with Dr. Robert Kuhn he was interesting about rsync and replication problem UUID might be a solution.
I have confirmed that MySQL recognizes <digits>[Ee]<digits><any string>
as a number and will not accept it as an unquoted table name.
Something as simple as 1e2 or 1e2xyz is recognized as a number.
The mysql code uses strtoll() to convert the exponent, which stops
its scan when it encounters the following string without error.
We won't be altering the kent code to quote table names.
I haven't looked at the code to see what the database name of 'tracks' means
in the kent code.
--Hiram
On 6/7/14 7:02 AM, Andrey Kartashov wrote:
> Good morning Hiram,
> I am totally agree that quotation is big change. I just want to be sure that if I’ll start UUID from a letter that everything else will work fine. I am going to change the behavior of UUID generator first symbol is a letter.
>
> Also as an idea I think the most often tables that are affected by users are grp and trackDb. The format of the table trackDb is not flexible enough, let say I have another database with just tracks ’tracks’. Why I have it? Because hg19 have a tons of tables and just to distinguish original hg19 with my tracks is a good idea otherwise it is a big mess. So I have on the same MySQL server `hg19` database and `tracks` database, Genome Browser will not understand if I put into ‘tableName’ record like `tracks`.`UUID_wtrack`.
> Tracks in trackDb_something is the most convenient way to share data between users within one department or company, but if you’d like to distinguish tracks between two or more departments just by key in url it is also not possible. Can you discuss this ideas on the meeting and with Dr. Robert Kuhn he was interesting about rsync and replication problem UUID might be a solution.
Reply all
Reply to author
Forward
0 new messages