Re: need list of country ISO code to demonyms
I don't have it, sorry. But it took me 10 seconds to copy paste it to an Excel, so I could save it as CSV and import it directly to MySQL. Further on, it'd be as simples as JOIN by name with a table with ISO-to-country-names, which you can fetch in hundreds of places, such as Wikipedia. -NT Em 03-10-2012 22:22, Daevid Vincent escreveu: Anyone have a SQL dump or other programmatically useable map of country ISO codes to demonyms? http://www.geography-site.co.uk/pages/countries/demonyms.html I can parse the strings I suppose there, but that's not quite as accurate, and ripping that out of the HTML page to parse seems painful too. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: need list of country ISO code to demonyms
Ermmm... actually it was done from Firefox to OpenOffice Calc, on Ubuntu.. :-) I just mentioned Excel so you could get the idea... glad I could help, though :-) -NT Em 03-10-2012 23:21, Daevid Vincent escreveu: HA! No $hit! Well isn't that clever. I didn't know that you can highlight a table like that, and paste it into Excel. That darn Microsoft -- they think of everything! :) Thanks for the tip. -Original Message- From: Nuno Tavares [mailto:nuno.tava...@dri.pt] Sent: Wednesday, October 03, 2012 3:10 PM To: mysql@lists.mysql.com Subject: Re: need list of country ISO code to demonyms I don't have it, sorry. But it took me 10 seconds to copy paste it to an Excel, so I could save it as CSV and import it directly to MySQL. Further on, it'd be as simples as JOIN by name with a table with ISO-to-country-names, which you can fetch in hundreds of places, such as Wikipedia. -NT Em 03-10-2012 22:22, Daevid Vincent escreveu: Anyone have a SQL dump or other programmatically useable map of country ISO codes to demonyms? http://www.geography-site.co.uk/pages/countries/demonyms.html I can parse the strings I suppose there, but that's not quite as accurate, and ripping that out of the HTML page to parse seems painful too. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mySQL Query and Report Builder
Hi Don, Have a look at Jasper Reports: http://jasperforge.org/ -NT Em 30-04-2012 17:53, Don Wieland escreveu: Hello, I have a client who needs the ability to do statistical reporting on their mySQL db data. Is there an app that provides an easy UI that will allow my client to build a line item query, specify fields to be include in the result of the query, and then design the way the data will be exported or printed? Graphs would be nice, too. I know I can build this from scratch, but would rather get a hold of something already pre-built (open source or shareware) to save me a bunch of coding time. I appreciate any feedback you can offer! Don -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: big character constant
Halász, not sure if I understood correcly, but you mysql console/client may have different charsets. This means you should set it accordingly. Check this out: mysql show global variables like '%character_set%'; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results| latin1 | | character_set_server | latin1 | | character_set_system | utf8 | +--++ Now compare results: mysql select convert(0xE29C94 using latin1), convert(0xE29C94 using utf8) \G *** 1. row *** convert(0xE29C94 using latin1): ✔ convert(0xE29C94 using utf8): ? 1 row in set (0.00 sec) mysql set names utf8; Query OK, 0 rows affected (0.00 sec) mysql select convert(0xE29C94 using latin1), convert(0xE29C94 using utf8) \G *** 1. row *** convert(0xE29C94 using latin1): ✔ convert(0xE29C94 using utf8): ✔ 1 row in set (0.00 sec) The check mark shows first in the first SELECT, and second on the second SELECT. Not sure if you'll be able to see, it's best you try it yourself. -NT Em 28-03-2012 03:43, Halász Sándor escreveu: 2012/03/27 11:20 -0700, Todd Lyons This seems backwards from what I expected: mysql select convert(0xE29C94 using latin1), convert(0xE29C94 using utf8) \G *** 1. row *** convert(0xE29C94 using latin1): ✔ convert(0xE29C94 using utf8): ? 1 row in set (0.00 sec) I expected the second one to print the check mark and the first one to have the problem because there is no heavy check mark in latin1. What am I missing, why is it printing out the correct value using latin1 but not using utf8 ? Correct value? It does not look correct in this message--but maybe you are using a display that understands UTF8. The output of convert(0xE29C94 using latin1) through the client is three bytes long, sent in order from high to low, and if your display does UTF8 the check will show, and the ASCII box drawing, which with \G is not used, will not line up. Try it after both charset latin1 and charset utf8 . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: One inst has 39 columns- the other 40
Charles,
You should run SELECT @@version on both nodes. It looks like you have
different version' schemas.
-NT
Em 22-03-2012 04:41, Brown, Charles escreveu:
Look man, there has to be someone out there that can tell me why one user table
has 5 extra columns. Is it version related or my sysprog person missed out on a
step. Its hard to believe that this problem is unique to my site.
Hostchar(60)NO PRI
Userchar(16)NO PRI
Passwordchar(41)NO
Select_priv enum('N','Y') NO N
Insert_priv enum('N','Y') NO N
Update_priv enum('N','Y') NO N
Delete_priv enum('N','Y') NO N
Create_priv enum('N','Y') NO N
Drop_priv enum('N','Y') NO N
Reload_priv enum('N','Y') NO N
Shutdown_priv enum('N','Y') NO N
Process_privenum('N','Y') NO N
File_priv enum('N','Y') NO N
Grant_priv enum('N','Y') NO N
References_priv enum('N','Y') NO N
Index_priv enum('N','Y') NO N
Alter_priv enum('N','Y') NO N
Show_db_privenum('N','Y') NO N
Super_priv enum('N','Y') NO N
Create_tmp_table_priv enum('N','Y') NO N
Lock_tables_privenum('N','Y') NO N
Execute_privenum('N','Y') NO N
Repl_slave_priv enum('N','Y') NO N
Repl_client_privenum('N','Y') NO N
Create_view_privenum('N','Y') NO N
Show_view_priv enum('N','Y') NO N
Create_routine_priv enum('N','Y') NO N
Alter_routine_priv enum('N','Y') NO N
Create_user_privenum('N','Y') NO N
Event_priv enum('N','Y') NO N
Trigger_privenum('N','Y') NO N
Create_tablespace_priv enum('N','Y') NO N
ssl_typeenum('','ANY','X509','SPECIFIED') NO
ssl_cipher blobNO
x509_issuer blobNO
x509_subjectblobNO
max_questions int(11) unsignedNO 0
max_updates int(11) unsignedNO 0
max_connections int(11) unsignedNO 0
max_user_connectionsint(11) unsignedNO 0
plugin char(64)YES
authentication_string textYES
Hostchar(60)NO
Userchar(16)NO
Passwordchar(41)NO
Select_priv enum('N','Y') NO N
Insert_priv enum('N','Y') NO N
Update_priv enum('N','Y') NO N
Delete_priv enum('N','Y') NO N
Create_priv enum('N','Y') NO N
Drop_priv enum('N','Y') NO N
Reload_priv enum('N','Y') NO N
Shutdown_priv enum('N','Y') NO N
Process_privenum('N','Y') NO N
File_priv enum('N','Y') NO N
Grant_priv enum('N','Y') NO N
References_priv enum('N','Y') NO N
Index_priv enum('N','Y') NO N
Alter_priv enum('N','Y') NO N
Show_db_privenum('N','Y') NO N
Super_priv enum('N','Y') NO N
Create_tmp_table_priv enum('N','Y') NO N
Lock_tables_privenum('N','Y') NO N
Execute_privenum('N','Y') NO N
Repl_slave_priv enum('N','Y') NO N
Repl_client_privenum('N','Y') NO N
Create_view_privenum('N','Y') NO N
Show_view_priv enum('N','Y') NO N
Create_routine_priv enum('N','Y') NO N
Alter_routine_priv enum('N','Y') NO N
Create_user_privenum('N','Y') NO N
ssl_typeenum('','ANY','X509','SPECIFIED') NO
ssl_cipher blobNO
x509_issuer blobNO
x509_subjectblobNO
max_questions int(11) unsignedNO 0
max_updates int(11) unsignedNO 0
max_connections int(11) unsignedNO 0
Re: Script need for dropping databases
Hi unknown, Have a look at database information_schema.TABLES: SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA='database'; As long as your MySQL version is = 5.1, you don't need a cron script, you can use the MySQL scheduler, create a stored procedure that will run each month. You'll need to use prepared statements, something like this: http://gpshumano.blogs.dri.pt/2009/09/06/automatically-cleaning-up-spam-wordpress-comments/ -NT Em 18-11-2011 08:02, a bv escreveu: Hi, I have a linux box running mysql plus phpmyadmin which has tables getting montly data and when a new month starts a new table is created. I want to store only 2 years of data so when a new month starts i need to drop the table which became the data container of 2 years previous data. So to make this job auto, i need a linux/mysql script which will run on cron etc . Can you please help me for a correct script ? (i cant say im a database guy :-) Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Indexes
Neil, whenever you see multiple fields you'd like to index, you should consider, at least: * The frequency of each query; * The occurrences of the same field in multiple queries; * The cardinality of each field; There is a tool Index Analyzer that may give you some hints, and I think it's maatkit that has a tool to run a query log to find good candidates - I've seen it somewhere, I believe Just remember that idx_a(field_a,field_b) is not the same, and is not considered for use, the same way as idx_b(field_b,field_a). -NT Em 07-10-2011 00:22, Michael Dykman escreveu: Only one index at a time can be used per query, so neither strategy is optimal. You need at look at the queries you intend to run against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.comwrote: Maybe that was a bad example. If the query was name = 'Red' what index should I create ? Should I create a index of all columns used in each query or have a index on individual column ? On 6 Oct 2011, at 17:28, Michael Dykman mdyk...@gmail.com wrote: For the first query, the obvious index on score will give you optimal results. The second query is founded on this phrase: Like '%Red%' and no index will help you there. This is an anti-pattern, I am afraid. The only way your database can satisfy that expression is to test each and every record in the that database (the test itself being expensive as infix finding is iterative). Perhaps you should consider this approach instead: http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil neil.tompk...@googlemail.com neil.tompk...@googlemail.com wrote: Hi, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table ? For example should I have a index on all fields that will be used in a WHERE statement ? Should the indexes be created with multiple fields ? A example of two basic queries SELECT auto_id, name, score FROM test_table WHERE score 10 ORDER BY score DESC SELECT auto_id, name, score FROM test_table WHERE score 10 AND name Like '%Red%' ORDER BY score DESC How many indexes should be created for these two queries ? Thanks, Neil -- - michael dykman - mdyk...@gmail.commdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: very large import
The following page has some nice interesting stuff, assuming you have a reasonable configuration in place (innodb_buffer_pool, etc[1]) http://download.oracle.com/docs/cd/E17952_01/refman-5.5-en/optimizing-innodb-bulk-data-loading.html [1] http://gpshumano.blogs.dri.pt/2009/09/28/importing-wikimedia-dumps/ (second box) -NT Em 03-08-2011 21:24, a.sm...@ukgrid.net escreveu: Quoting supr_star suprstar1...@yahoo.com: Is there any way to speed up this process? by disabling indexes or something? I can't afford to be down for 3 more days... First stop, the mysql documentation: http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to find values which do not return any tuple in IN clause
create table dim (value varchar(10), primary key(value));
insert into dim values ('aaa'),('bbb'),('ccc'),('ddd');
SELECT tablename.fieldname
FROM dim
LEFT JOIN tablename ON tablename.fieldname = dim
WHERE tablename.fieldname IS NULL;
If this is not what intended, I'd suggest you to spend a little more
effort in formulating a more clear question, maybe with an example.
-NT
Em 10-06-2011 23:36, Fahim Mohammad escreveu:
Hi
select * from tablename where fieldname in ('aaa','bbb','ccc','ddd');
return only the successful hit.
How can I know how many out of four ('aaa','bbb','ccc','ddd') resulted in a
miss OR which values do not return any tuple.
Thanks
Fahim
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to find values which do not return any tuple in IN clause
Fahim,
I was not quite sure I understood you question, but looking at what
you're trying to achieve in this example, give a try to my suggestion.
You have to bear in mind that you can only GROUP BY values that indeed
show up in the results. That means that if ('','y') never show
up, they won't show up in the GROUP BY either.
That's why I suggested to use a value table (dim in my example) -
there you will load all the values you want, so you can show then in the
results by means of a LEFT JOIN.
Also, watch out for this:
mysql SELECT city_name, COUNT(*) FROM city WHERE city_name IN
('Nashville','Ashburn','Clarksville', '','y') GROUP BY state_id;
You're grouping by state_id but showing city_name (which you can't
control)... that's dangerous unless you are pretty sure of what you're
doing.
-NT
Em 11-06-2011 01:38, Fahim Mohammad escreveu:
mysql SELECT city_name, COUNT(*) FROM city WHERE city_name IN
('','y') GROUP BY state_id having count(*) = 0;
Empty set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join based upon LIKE
Dear Jerry, I've been silently following this discussion because I've missed the original question. But from your last explanation, now it really looks you have a data quality kind of issue, which is by far related with MySQL. Indeed, in Data Quality, there is *never* a ready solution, because the source is tipically chaotic May I suggest you to explore Google Refine? It seems to be able to address all those issues quite nicely, and the clustering might solve your problem at once. You shall know, however, how to export the tables (or a usable JOIN) as a CSV, see SELECT ... INTO OUTFILE for that. Hope it helps, -NT Em 03-05-2011 21:34, Jerry Schwartz escreveu: My situation is sounds rather simple. All I am doing is matching a spreadsheet of products against our database. My job is to find any matches against existing products and determine which ones are new, which ones are replacements for older products, and which ones just need to have the publication date (and page count, price, whatever) refreshed. Publisher is no problem. What I have for each feed is a title and (most of the time) an ISBN or other identification assigned by the publisher. Matching by product ID is easy (assuming there aren't any mistakes in the current or previous feeds); but the publisher might or might not change the product ID when they update a report. That's why I also run a match by title, and that's where all the trouble comes from. The publisher might or might not include a mix of old and new products in a feed. The publisher might change the title of an existing product, either on purpose or by accident; they might simply be sloppy about their spelling; or (and this is where it is critical) the title might include a reference to some time period such as a year or a quarter. I think we'd better pull the plug on this discussion. It doesn't seem like there's a ready solution. Fortunately our database is small, and most feeds are only a few hundred products. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: shawn wilson [mailto:ag4ve...@gmail.com] Sent: Tuesday, May 03, 2011 4:08 PM Cc: mysql mailing list Subject: Re: Join based upon LIKE I'm actually enjoying this discussion because I have the same type of issue. However, I have done away with trying to do a full text search in favor of making a table with unique fields where all fields should uniquely identify the group. If I get a dupe, I can clean it up. However, like you, they don't want me to mess with the original data. So, what I have is another table with my good data that my table with my unique data refers to. If a bad record is creased, I don't care I just create my relationship to the table of data I know (read think - I rarely look at this stuff) is good. So, I have 4 fields that should be unique for a group. Two chats and two ints. If three of these match a record in the 'good data' table - there's my relationship. If two or less match, I create a new record in my 'good data' table and log the event. (I haven't gotten to the logging part yet though, easy enough just to look sense none of the fields in 'good data' should match) I'm thinking you might have to dig deeper than me to find 'good data' but I think its there. Maybe isbn, name, publisher + address, price, average pages, name of sales person, who you guys pay for the material, etc etc etc. On May 3, 2011 10:59 AM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Jerry Schwartz je...@gii.co.jp I'm not sure that I could easily build a dictionary of non-junk words, since The traditional way is to build a database of junk words. The list tends to be shorter :-) Think and/or/it/the/with/like/... Percentages of mutual and non-mutual words between two titles should be a reasonable indicator of likeness. You could conceivably even assign value to individual words, so polypropylbutanate is more useful than synergy for comparison purposes. All very theoretical, though, I haven't actually done much of it to this level. My experience in data mangling is limited to mostly should-be-fixed-format data like sports results. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ag4ve...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how to generate a data set then join with in on fly?
Hi Ryan. That's a common issue for reporting. This post might have you an idea where to generate those inexistent dates (time slots), just forget about the specific aggregates and partitioning done in there: http://gpshumano.blogs.dri.pt/2009/09/28/finding-for-each-time-interval-how-many-records-are-ocurring-during-that-interval/ -- Nuno Tavares DRI, Consultoria Informática Telef: +351 936 184 086 Shawn Green (MySQL) escreveu: On 1/10/2011 18:51, Ryan Liu wrote: Hi, In MySQL, is that possible to generate a data set join with it on fly (without create an temporary table)? e.g. for a report used by a graphic tool, it requires data in all dates, even it is null. Can I select vacationT.* left join ( all dates d in the past 3 years) on vacationT.`date` = d ? Thanks, Ryan Sorry, no. To do the report I think you are describing will require you to have a table of all dates. Also the date table needs to be on the LEFT side of the LEFT JOIN to be included even if there aren't any matches. SELECT FROM master_date_table LEFT JOIN vacationT ... Or, you can accept the partial list of dates actually stored in the database as accurate and fill in any missing dates when you render it in your report (inside the application). It may be much easier to fill-in those dates when you format the report, have you checked? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: query results group/summed by interval
. +--+---+ | calls | queue_seconds | +--+---+ | 250 | 0.00 | | 28 | 1.00 | | 30 | 2.00 | | 56 | 3.00 | | 23 | 4.00 | | 31 | 5.00 | | 33 | 6.00 | | 50 | 7.00 | | 49 | 8.00 | | 62 | 9.00 | | 74 | 10.00 | ... ... and so on... ... +--+---+ now result should look like this with a 5 seconds interval. +--+---+ | count(*) | queue_seconds | +--+---+ | 250 | 0.00 | | 168 | 5.00 | | 268 | 10.00 | ... ... and so on... ... +--+---+ i would really appreciate your help. Best Regards. -- Ghulam Mustafa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ave...@yahoo-inc.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=cuong.m...@vienthongso.com -- Best Regards, Cuongmc. -- Nguyen Manh Cuong Phong Ky Thuat - Cong ty Vien Thong So - VTC Dien thoai: 0912051542 Gmail : philipscu...@gmail.com YahooMail : philipscu...@yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com -- Nuno Tavares +351 93 618 40 86 dri Consultoria Informatica -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to put table definition into another table using SQL?
create table TableDef like TableX ? -- Nuno Tavares DRI, Consultoria Informática Telef: +351 936 184 086 mos escreveu: I'd like to get the field names and data types of a table, say TableX, and put it into TableDef using nothing but SQL. I know I can list the table definition using Describe Table and then loop through the results and insert the first two columns Field and Type into TableDef, but is there a way to do it using just SQL? Example: Describe TableX: First_NameChar(15) Last_NameChar(20) ... Start_DateDate .. SalaryDouble .. And I'd like TableDef to have these rows: ColNameColType --- -- First_NameChar(15) Last_NameChar(20) Start_DateDate SalaryDouble Is there a way to do this with one SQL statement? I'm really looking for the MySQL internal table where it stores the table definitions. TIA Mike signature.asc Description: OpenPGP digital signature
Re: logging slow queries with time
. Fahrenheit is based on alcohol temperature. Ergo, Fahrenheit is better than Celsius. QED. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=nuno.tava...@dri.pt -- Nuno Tavares +351 93 618 40 86 dri Consultoria Informatica -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
