CSV import
I want to import data from a CSV file in a table. MySql admin doesn't support import from CSV files. Format of data is different from structure of table. Is there any app/snippet written for this task in .net , which I can modify according to my need. -- Sharique uddin Ahmed Farooqui (C++/C# Developer, IT Consultant) A revolution is about to begin. A world is about to change. And you and me are the initiator.
Re: Table compression with write (append) support
At 12:31a -0400 on 28 May 2007, Dan Nelson wrote: In the last episode (May 27), Yves Goergen said: I'm thinking about using a MySQL table to store an Apache access log and do statistics on it. Currently all access log files are stored as files and compressed by day. Older log files are compressed by month, with bzip2. This gives a very good compression ratio, since there's a lot of repetition in those files. If I store all that in a regular table, it would be several gigabytes large. So I'm looking for a way to compress the database table but still be able to append new rows. As the nature of a log file, it is not required to alter previous data. It could only be useful to delete older rows. Do you know something for that? You want the ARCHIVE storage engine. http://dev.mysql.com/doc/refman/5.0/en/archive-storage-engine.html Huh. This is the first I've heard of the archive engine. Cool! However, I'm curious how the compression offered by OPTIMIZE TABLE and the zlib library would compare to denormalization of the log schema. In particular, I imagine a lot of the HTTP requests would be the same, so you could create a table to store the requested URLs, and then have a second table with the timestamp and foreign key relationship into the first. Depending on how wide the original rows are and how often they're requested, I imagine you could get quite a savings. Anything else that's repeated as well? IP's? Return codes? Would be curious about the results if you were able to implement both. Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: restore one database.
Hi Geoffroy, Very true, restore depends on the kind of backup we do. I was just wondering if mysql has any option to restore just one database from the mysqldump having all the database. So, as of now mysql does not provided option to just restore just one database from dump having all database..Right? regards anandkl On 5/28/07, Geoffroy Cogniaux [EMAIL PROTECTED] wrote: Hi Ananda, Recovery strategy depends essentially of the backup strategy you choose. I think that if you want be able to proceed to a restoration, database per database, you should separate their backups. It can simply be done with this kind of script: for db in (`echo 'show databases;' | mysql -u user --password=pwd | grep -v ^Database$`); do mysqldump -u user --password=pwd $db /mybackupdir/$db.bak ; done; If you want to have all in one file, use tar after your backup: cd /mybackupdir tar -czf mybackup.tar.gz *.bak rm -fr *.bak Best regards, Geof. -Message d'origine- De: Ananda Kumar [mailto:[EMAIL PROTECTED] Envoyé: dimanche 27 mai 2007 13:52 À: John Meyer Cc: MySQL General Objet: Re: restore one database. Hi All, I think my question was not understood. All the database are important. Now that one of the database is accidently dropped, can i restore from that single database from my dump and use the bin log and recover till AS OF NOW. regards anandkl On 5/27/07, John Meyer [EMAIL PROTECTED] wrote: Ananda Kumar wrote: Hi Pelle, I dont have enough space on any other storage, so i was thinking if we would just restore one database from dump that would save lot of time , rather than restoring all the database. regards anandkl Well, if only one database is important enough to back up, then yes it will. But if you have multiple databases that you are actively using then you'll need to back them all up. You don't necessarily need to use an all databases dump, though. -- The NCP Revue -- http://www.ncprevue.com/blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
row lock
My client got a strange problem today. Mysql returned err no 1205. lock wait timeout exceeded. why? this error occured while updating the record. This error isproduced for a single record. I have checked if that record is in use while updating etc., but this record is not in use. I wait for some time again try to complete ithe operation. but isn't. Then I duplicated the record with new rimery key Id and then deleted the first record. Then changwd the new Id to old id. and it worked well. I can't understand why theabove error occuerred and the solution to it. can you please help and give the details. Thanks and regards cpk -- Keep your Environment clean and green. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CSV import
Try SQLYOG software Thanks Regards, Sandesh Nair -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sharique uddin Ahmed Farooqui Sent: Monday, May 28, 2007 11:56 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: CSV import I want to import data from a CSV file in a table. MySql admin doesn't support import from CSV files. Format of data is different from structure of table. Is there any app/snippet written for this task in .net , which I can modify according to my need. -- Sharique uddin Ahmed Farooqui (C++/C# Developer, IT Consultant) A revolution is about to begin. A world is about to change. And you and me are the initiator. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: restore one database.
Yes it is. This is why I suggest dumping db per db. Bye. -Message d'origine- De : Ananda Kumar [mailto:[EMAIL PROTECTED] Envoyé : lundi 28 mai 2007 10:41 À : Geoffroy Cogniaux Cc : John Meyer; MySQL General Objet : Re: restore one database. Hi Geoffroy, Very true, restore depends on the kind of backup we do. I was just wondering if mysql has any option to restore just one database from the mysqldump having all the database. So, as of now mysql does not provided option to just restore just one database from dump having all database..Right? regards anandkl On 5/28/07, Geoffroy Cogniaux [EMAIL PROTECTED] wrote: Hi Ananda, Recovery strategy depends essentially of the backup strategy you choose. I think that if you want be able to proceed to a restoration, database per database, you should separate their backups. It can simply be done with this kind of script: for db in (`echo 'show databases;' | mysql -u user --password=pwd | grep -v ^Database$`); do mysqldump -u user --password=pwd $db /mybackupdir/$db.bak ; done; If you want to have all in one file, use tar after your backup: cd /mybackupdir tar -czf mybackup.tar.gz *.bak rm -fr *.bak Best regards, Geof. -Message d'origine- De: Ananda Kumar [mailto:[EMAIL PROTECTED] Envoyé: dimanche 27 mai 2007 13:52 À: John Meyer Cc: MySQL General Objet: Re: restore one database. Hi All, I think my question was not understood. All the database are important. Now that one of the database is accidently dropped, can i restore from that single database from my dump and use the bin log and recover till AS OF NOW. regards anandkl On 5/27/07, John Meyer [EMAIL PROTECTED] wrote: Ananda Kumar wrote: Hi Pelle, I dont have enough space on any other storage, so i was thinking if we would just restore one database from dump that would save lot of time , rather than restoring all the database. regards anandkl Well, if only one database is important enough to back up, then yes it will. But if you have multiple databases that you are actively using then you'll need to back them all up. You don't necessarily need to use an all databases dump, though. -- The NCP Revue -- http://www.ncprevue.com/blog -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table compression with write (append) support
On 28.05.2007 09:06 CE(S)T, Kevin Hunter wrote: At 12:31a -0400 on 28 May 2007, Dan Nelson wrote: You want the ARCHIVE storage engine. http://dev.mysql.com/doc/refman/5.0/en/archive-storage-engine.html Hm, it doesn't support deleting rows and it cannot use indexes. So doing statistics on them (which can be a little more complex than counting rows within a timespan, which is why I wanted to use an SQL database) could get quite resource demanding. In particular, I imagine a lot of the HTTP requests would be the same, so you could create a table to store the requested URLs, and then have a second table with the timestamp and foreign key relationship into the first. Interesting idea. Inserting would be more work to find the already present dictionary rows. Also, URLs sometimes contain things like session IDs. They're probably not of interest for my use but it's not always easy to detect them for removal. I could also parse user agent strings for easier evaluation, but this takes me the possibility to add support for newer browsers at a later time. (Well, I could update the database from the original access log files when I've updated the UA parser.) IP addresses (IPv4) and especially return codes (which can be mapped to a 1-byte value) are probably not worth the reference. Data size values should be too distributed for this. How large is a row reference? 4 bytes? -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CSV import
Hi, It can be done easily with PhpMyAdmin, but it is not in .net 2007/5/28, Sharique uddin Ahmed Farooqui [EMAIL PROTECTED]: I want to import data from a CSV file in a table. MySql admin doesn't support import from CSV files. Format of data is different from structure of table. Is there any app/snippet written for this task in .net , which I can modify according to my need. -- Sharique uddin Ahmed Farooqui (C++/C# Developer, IT Consultant) A revolution is about to begin. A world is about to change. And you and me are the initiator.
Re: Database design
Thanks for the reply.
I've spent some time considering your suggestions and have tried to
normalise the data further. I'm not sure whether I've gone from one extreme
to the other though - instead of 2 tables, I've now got 17 tables!!
I've included the code I used after the following info (see below)
I created separate tables for categories, category_type, codes, colours,
sizes, descriptions, suppliers, title and garment type. I also created a
garments table. Since I found most of the abovementioned tables to be of a
many-to-many relationship with regard to the garments table, I thought I had
to create look-up or linking tables (not sure what the correct term is
here), so I created tables like garment_to_code and garment_to_colour etc.
in order to show the relationship between the particular tables and the
garments table.
I'm still not sure if I'm on the right track - the garments table doesn't
quite look right. I mean I kind of thought garments was the main entity and
then colour, size, description etc. were attributes of this entity, so I
thought they should somehow be included within the garments table but I
didn't know how to do that because of the many-to-many relationships.
I chose the inclusion of the different foreign keys within the tables based
on how I wanted the CMS to function (also so I could query the database
successfully for use in flash), eg. I wanted the Insert new garment item
page of the CMS to include the following functionality:
Based on which supplier is clicked on, the category menu dropdown would be
populated with any category that particular supplier has, therefore I
thought I'd need to include supplier_id as a foreign key in the categories
table. Same with Garment Type - include supplier_id in garment_type table.
Once a garment type is clicked on, the code, description, colour and size
menus get populated with info relevant to the garment type and supplier,
therefore code, description, size and colour would need to have both
supplier_id and garment_type_id as foreign keys in their respective tables.
Sorry for the long drawnout explanation above, but just wondered if someone
could comment on how they think I've progressed with this, since my first
email - just so I know I'm not completely off track!
CREATE TABLE `garments` (
`garment_id` smallint(5) unsigned NOT NULL auto_increment,
`supplier_id` smallint(5) unsigned NOT NULL,
`image` enum('y','n') NOT NULL,
`swatch_image` enum('y','n') NOT NULL,
`extra_info` varchar(50) default NULL,
PRIMARY KEY (`garment_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `categories` (
`cat_id` smallint(5) unsigned NOT NULL auto_increment,
`supplier_id` smallint(5) unsigned NOT NULL,
`cat_type_id` smallint(5) unsigned NOT NULL,
`category` varchar(30) NOT NULL,
PRIMARY KEY (`cat_id`),
UNIQUE KEY `category` (`category`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `category_type` (
`cat_type_id` smallint(5) unsigned NOT NULL auto_increment,
`cat_type` varchar(20) NOT NULL,
PRIMARY KEY (`cat_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `codes` (
`code_id` smallint(5) unsigned NOT NULL auto_increment,
`supplier_id` smallint(5) unsigned NOT NULL,
`type_id` smallint(5) unsigned NOT NULL,
`code` varchar(20) NOT NULL,
PRIMARY KEY (`code_id`),
UNIQUE KEY `code` (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `colours` (
`colour_id` smallint(5) unsigned NOT NULL auto_increment,
`supplier_id` smallint(5) unsigned NOT NULL,
`type_id` smallint(5) unsigned NOT NULL,
`colour` varchar(20) NOT NULL,
PRIMARY KEY (`colour_id`),
UNIQUE KEY `colour` (`colour`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `description` (
`desc_id` smallint(5) unsigned NOT NULL auto_increment,
`supplier_id` smallint(5) unsigned NOT NULL,
`type_id` smallint(5) unsigned NOT NULL,
`description` varchar(70) NOT NULL,
PRIMARY KEY (`desc_id`),
UNIQUE KEY `description` (`description`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `garment_to_category` (
`garment_id` smallint(5) unsigned NOT NULL,
`cat_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `garment_to_category_type` (
`garment_id` smallint(5) unsigned NOT NULL,
`category_type_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`category_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `garment_to_code` (
`garment_id` smallint(5) unsigned NOT NULL,
`code_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`code_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `garment_to_colour` (
`garment_id` smallint(5) unsigned NOT NULL,
`colour_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`garment_id`,`colour_id`)
) ENGINE=MyISAM
Re: Table compression with write (append) support
Yves Goergen wrote: On 28.05.2007 09:06 CE(S)T, Kevin Hunter wrote: At 12:31a -0400 on 28 May 2007, Dan Nelson wrote: You want the ARCHIVE storage engine. http://dev.mysql.com/doc/refman/5.0/en/archive-storage-engine.html Hm, it doesn't support deleting rows and it cannot use indexes. So doing statistics on them (which can be a little more complex than counting rows within a timespan, which is why I wanted to use an SQL database) could get quite resource demanding. Another option might be to use compressed MyISAM tables, which you create with myisampack. Suppose you create a new table every day, and after you start inserting into the new table, you compress yesterday's file. Then you could use the MERGE storage engine to provide a view over all the tables as though they are one. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: row lock
Hi, C K wrote: My client got a strange problem today. Mysql returned err no 1205. lock wait timeout exceeded. why? this error occured while updating the record. This error isproduced for a single record. I have checked if that record is in use while updating etc., but this record is not in use. I wait for some time again try to complete ithe operation. but isn't. Then I duplicated the record with new rimery key Id and then deleted the first record. Then changwd the new Id to old id. and it worked well. I can't understand why theabove error occuerred and the solution to it. can you please help and give the details. THis depends very much on the storage engine the table uses. How do you know the record was not in use? I designed innotop (http://innotop.sourceforge.net/projects/innotop) to help debug these situations. Cheers Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CSV import
Both PhpMyAdmin and SQLYOG doesn't support customised mapping. My cvs data structure is diff from mysql table. I just want to import values for one field only. Previously I was using Mysql Front but it crashes. Also this s/w very old and discontinued. Why mysql doesn't implement it in MysqlAdmin? -- Sharique uddin Ahmed Farooqui (C++/C# Developer, IT Consultant) A revolution is about to begin. A world is about to change. And you and me are the initiator. On 5/28/07, Geoffroy Cogniaux [EMAIL PROTECTED] wrote: Hi, It can be done easily with PhpMyAdmin, but it is not in .net 2007/5/28, Sharique uddin Ahmed Farooqui [EMAIL PROTECTED]: I want to import data from a CSV file in a table. MySql admin doesn't support import from CSV files. Format of data is different from structure of table. Is there any app/snippet written for this task in .net , which I can modify according to my need. -- Sharique uddin Ahmed Farooqui (C++/C# Developer, IT Consultant) A revolution is about to begin. A world is about to change. And you and me are the initiator.
Re: CSV import
Sharique uddin Ahmed Farooqui wrote: Both PhpMyAdmin and SQLYOG doesn't support customised mapping. My cvs data structure is diff from mysql table. I just want to import values for one field only. Previously I was using Mysql Front but it crashes. Also this s/w very old and discontinued. Why mysql doesn't implement it in MysqlAdmin? Perhaps you can use LOAD DATA INFILE? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tellico and MySQL
John Meyer wrote: I'm still searching online, but does anybody know of a script that will input a tellico database into MySQL? I had to look up what tellico is. Given that it uses XML, it should not be too hard to write such a script. I did something similar for GnuCash. It's ironic that the Tellico front page says he developed it because he couldn't find any tools that didn't use a SQL backend! Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General Questions About Indexes
Hi John, John Kebbel wrote: INDEXES - A Science AND an Art I've been continuing to look for answers to my own questions. I've found a few ... I meant to write back and try to help, but got busy with other things. You have found some good answers for yourself. Q1. What good does it do to store the primary key or a unique key if you're normally SELECTing columns that don't use that primary or unique key? As you can see, it only makes sense to index those fields you use in the WHERE clause. http://www.databasejournal.com/features/mysql/article.php/10897_1382791_2 Generally true, but there can be some advantages to indexing other columns too. A common case is a covering index. For example, SELECT col1 FROM tbl WHERE col2=11. If you have an index on col2, it will help the RDBMS find entries quickly. This process works as you would expect: it looks in the index B-tree for entries with value 11. Now it has to do what's called a bookmark lookup, to find the actual row in the table, and retrieve col1 from it. However, if you have an index on (col2, col1) you can retrieve the value from the index, without needing to go look for the row in the table. This index covers the query. Order of columns is important here. In practice, this can be an enormous advantage. I wrote an article about this, with possibly helpful diagrams, here: http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/ Q2. Does a SELECT statement look at an index before it looks at a table? Before we repair the table structure above, let me tell you about a most important little secret for anyone serious about optimizing their queries: EXPLAIN. EXPLAIN shows (explains!) how your queries are being used. By putting it before a SELECT, you can see whether indexes are being used properly, and what kind of join is being performed... http://www.databasejournal.com/features/mysql/article.php/10897_1382791_1 Yes, and it goes further than that; the server maintains statistics about the indexes, and uses them to estimate the cost of various query execution plans. You could spend months learning how this works, all the fine points of various optimization strategies, etc. (Not that I'm an expert on it, I just know there's a lot to it). Q3. Are JOINs where the real timesaving occurs and SELECTs just a peripheral issue muddying the water? In MySQL, Paul DuBois writes: Index columns that you search for, not columns you select ... [t]he best candidate columns for indexing are the columns that appear in your WHERE clause or columns named in join clauses. Many kinds of operations may benefit from indexes. Besides SELECT and JOIN, they can be used for GROUP BY, ORDER BY, DISTINCT, finding rows to UPDATE or DELETE, and are necessary for lots of other things like foreign keys. The trade-off is cost and space to maintain them. When you change data, the indexes have to be updated too. Q4. What about non-unique indexes? Is the structure of a non-unique index file similar to the index in the back of a book, the phrase you're searching for plus a list of row numbers (page numbers for a book) where that phrase is found? I haven't found the answer to this question, but I did find: Indexes work best for columns with unique values, and most poorly with columns that have many duplicate values Paul DuBois, MySQL I think this might be two questions. I'm not sure -- there may be more subtleties than this -- but I think a UNIQUE index is like any other index, except the server knows to check for duplicate values and throw an error. Most indexes in MySQL are B-trees, though there are specialized indexes for some things (hash, spatial, RTREE, fulltext). Another thing you're touching on here is the selectivity of the index. This is the degree to which a row in the table is uniquely identified by an index entry. If you index a column with all one value, the selectivity is terrible; the opposite end of the spectrum is a UNIQUE index, which has perfect selectivity (each row is uniquely identified by an index entry). Q5. Is an item in an index tied to a memory address (like a pointer in C ++) where the indexed data appears inside the larger memory area staked out by the table? This is implementation-dependent. Hopefully the diagrams in the article I linked above will help explain. There is always some kind of pointer from the index to the row, but how it works is different from one system to the next. (There are some exotic kinds of storage engines that may not have conventional indexes, and will work completely differently, but the pointer idea applies well to all the MySQL-supplied engines, as far as I know). Q6. As for memory, when you choose a database inside the mysql client, are all the tables within that database read into
Re: Strange result from multiple JOIN
Dave Dyer wrote: I'm trying to construct a join, but the effect I want seems to be impossible to achieve. In this schema, the uid field is unique in the players table, but not in the ranking table (one player per uid, multiple rankings per player) I want to select player names and rankings for a particilar game SELECT player_name,players.uid,e_mail,ranking.value from participant left join players left join ranking on participant.pid = players.uid on participant.pid = ranking.uid where tid='7' and ranking.variation='zertz' This works perfectly if the ranking table actually exists, but if there is no ranking table, the entire row is filtered out by the and ranking.variation='zertz' since that is nulled the join. If I make the where clause where tid='7' and (ranking.variation='zertz' or ranking.variation is null) Instead of getting one row per UID, I get many. Appently instead of getting N rows (one per uid) I get N^2 rows, N per UID. Is there a trick I'm missing? Is this a join bug? I doubt it's a bug. I think you have probably created a bug by creating a cross join without realizing it. Try to begin with just one table, then add just one LEFT JOIN, then another. I am a bit confused by some of your terminology, such as if there is no ranking table. If there's no ranking table, the server will throw a no such table error and refuse to execute the statement. I think you mean if the table is empty. You may be confusing yourself with your JOIN syntax, which I would have said is not even legal, but I just tried it and it doesn't complain :-) But, without trying it myself, may I suggest you try something more like this: SELECT player_name,players.uid,e_mail,ranking.value from participant left join players on participant.pid = players.uid left join ranking on participant.pid = ranking.uid where tid='7' and ranking.variation='zertz' Notice I place the ON clause right after each JOIN. I think this is probably going to remove the cross-join you might have written without knowing it. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table compression with write (append) support
At 5:45a -0400 on 28 May 2007, Yves Goergen wrote: On 28.05.2007 09:06 CE(S)T, Kevin Hunter wrote: In particular, I imagine a lot of the HTTP requests would be the same, so you could create a table to store the requested URLs, and then have a second table with the timestamp and foreign key relationship into the first. Interesting idea. Inserting would be more work to find the already present dictionary rows. My guess is not /that/ much work, since you should only have a known and relatively small set in this dictionary, it'd basically be cached the whole time. But, that's my guess. Haven't tried it. Practice and theory . . . Also, URLs sometimes contain things like session IDs. They're probably not of interest for my use but it's not always easy to detect them for removal. Really? Why wouldn't it be easy to detect them? You presumably know what variable you're looking for in the URL string, and applying a simple regex search-and-replace . . . ? IP addresses (IPv4) and especially return codes (which can be mapped to a 1-byte value) are probably not worth the reference. Data size values should be too distributed for this. Well, presumably, you'd normalize that part of the table. That is, rather than include multiple foreign keys in your data rows, you'd create a cartesian product of the the return codes with the dictionary table. You'd have a slightly more bloated dictionary, but depending on the number of requests the site(s) get(s), the aggregation would more than make up for it. I could also parse user agent strings for easier evaluation, but this takes me the possibility to add support for newer browsers at a later time. (Well, I could update the database from the original access log files when I've updated the UA parser.) Same thought. If you've only a known set of UA strings, you could normalize them with the dictionary table as well. How large is a row reference? 4 bytes? I don't know, I'm fairly new to MySQL. I suppose it'd also matter on the type of index. Anyone more knowledgeable wanna pipe up? Well. Whatever method works for your needs, cool. I'm going to check out both MYISAMPACK and ARCHIVE. I was unaware of those. Thanks list! Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Exporting Data To File and Can't Create/Write to File
I am doing the following: echo 'SELECT * FROM jobs WHERE id = 1 INTO OUTFILE /Users/jkopanas/Sites/test_this_out.csv;' | mysql -uroot backlog_development And I get the following error: ERROR 1 (HY000) at line 1: Can't create/write to file '/Users/jkopanas/Sites/test_this_out.csv' (Errcode: 13) I found this on mysql: http://dev.mysql.com/doc/refman/5.0/en/cannot-create.html I checked to see if root had File_priv and he/she does. I am developing this on Mac OS X but I am deploying on a linux box. Any input? :-) -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting Data To File and Can't Create/Write to File
Hi, John Kopanas wrote: I am doing the following: echo 'SELECT * FROM jobs WHERE id = 1 INTO OUTFILE /Users/jkopanas/Sites/test_this_out.csv;' | mysql -uroot backlog_development And I get the following error: ERROR 1 (HY000) at line 1: Can't create/write to file '/Users/jkopanas/Sites/test_this_out.csv' (Errcode: 13) I found this on mysql: http://dev.mysql.com/doc/refman/5.0/en/cannot-create.html I checked to see if root had File_priv and he/she does. I am developing this on Mac OS X but I am deploying on a linux box. Does /Users/jkopanas/Sites/ exist? Does the user MySQL runs as have permission to write to it? Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table compression with write (append) support
On 28.05.2007 18:34 CE(S)T, Kevin Hunter wrote: At 5:45a -0400 on 28 May 2007, Yves Goergen wrote: Also, URLs sometimes contain things like session IDs. They're probably not of interest for my use but it's not always easy to detect them for removal. Really? Why wouldn't it be easy to detect them? You presumably know what variable you're looking for in the URL string, and applying a simple regex search-and-replace . . . ? I don't control what applications run on that web server. Same thought. If you've only a known set of UA strings, you could normalize them with the dictionary table as well. Well, I don't know (in advance) what's all running around out there... -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CSV import
Both PhpMyAdmin and SQLYOG doesn't support customised mapping. My cvs data structure is diff from mysql table. I just want to import values for one field only. Database Workbench does, see www.upscene.com Previously I was using Mysql Front but it crashes. Also this s/w very old and discontinued. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting Data To File and Can't Create/Write to File
Directory existed but I did not have the right permissions on the directory for the mysql user. Thanks my friend. :-) On 5/28/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, John Kopanas wrote: I am doing the following: echo 'SELECT * FROM jobs WHERE id = 1 INTO OUTFILE /Users/jkopanas/Sites/test_this_out.csv;' | mysql -uroot backlog_development And I get the following error: ERROR 1 (HY000) at line 1: Can't create/write to file '/Users/jkopanas/Sites/test_this_out.csv' (Errcode: 13) I found this on mysql: http://dev.mysql.com/doc/refman/5.0/en/cannot-create.html I checked to see if root had File_priv and he/she does. I am developing this on Mac OS X but I am deploying on a linux box. Does /Users/jkopanas/Sites/ exist? Does the user MySQL runs as have permission to write to it? Baron -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange result from multiple JOIN
Thanks, it turns out you are exactly right. I rewrote the query to keep the on immediately following the left join and it now works as I wish. I'll have to read up on cross join, but if there is a mysql bug here, it is that the parser that what I wrote as left join was turned into a cross join. -- the second on clause in a row ought to have triggered a complaint. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange result from multiple JOIN
Dave Dyer wrote: Thanks, it turns out you are exactly right. I rewrote the query to keep the on immediately following the left join and it now works as I wish. I'll have to read up on cross join, but if there is a mysql bug here, it is that the parser that what I wrote as left join was turned into a cross join. -- the second on clause in a row ought to have triggered a complaint. Yes, I too thought that should be an error. I have tried to figure out whether it's illegal to have two in a row or to omit an ON or USING clause, but I can't convince myself completely by referring to http://dev.mysql.com/doc/refman/5.0/en/join.html. I'm not sure I am reading it correctly. But it does seem the parser should reject it. If you want to pursue this further I won't duplicate your effort, but I'm happy to dig deeper on it and see if there is a bug. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
block privileges
Hi, I need to know if it's possible block things like store procedures, triggers, etc in MySQL 5.0 using grant statement. Some users can't uses this stuff. If someone knows please help me. Regards, -- Ricardo Conrado Serafim DBA Júnior (MySQL) URANET Projetos e Sistemas www.uranet.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
selecting the last and second last id's
My primary id is an auto incrementing table and I want to return the highest and second highest id. Any ideas?
Re: selecting the last and second last id's
At 04:00 PM 5/28/2007, [EMAIL PROTECTED] wrote: My primary id is an auto incrementing table and I want to return the highest and second highest id. Any ideas? You should of course have an index on Rcd_Id: Select rcd_id from mytable order by rcd_id desc limit 2 mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting the last and second last id's
Hi! [EMAIL PROTECTED] wrote: My primary id is an auto incrementing table and I want to return the highest and second highest id. Any ideas? The simplest answer I can think of is SELECT id FROM tbl ORDER BY id DESC LIMIT 2; This general problem, and its variations, has many solutions, which you can read about here if you are interested: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ Cheers Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: block privileges
http://dev.mysql.com/doc/refman/5.0/en/revoke.html You can use: REVOKE ALTER ROUTINE, CREATE ROUTINE, EXECUTE ON * TO USER; Anoop On 5/28/07, Ricardo Conrado Serafim [EMAIL PROTECTED] wrote: Hi, I need to know if it's possible block things like store procedures, triggers, etc in MySQL 5.0 using grant statement. Some users can't uses this stuff. If someone knows please help me. Regards, -- Ricardo Conrado Serafim DBA Júnior (MySQL) URANET Projetos e Sistemas www.uranet.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
