Re: New to MySQL on Linux
Thanks, Joshua - just the sort of info I needed. Off to find a more recent distro Cheers Terry - Original Message - On Friday 11 February 2005 09:15, Terry Riley said something like: Having inherited an elderly PIII/500MHz box with an 8Gb SCSI disk, that had an apparently unusable XP SP2 OS on it, I decided to wipe the disk and install my first Linux instead, using an ancient RedHat 7.3 distribution. First suggestion: get something recent: Suse 9.2, Mandrake 10.1, Fedora Core 3, the latest Debian. A distro that old will have major security (and probably usability issues). Now the question: If I'm only using this as a database (no development) on RH7.3, which is the preferred download? I am confused by the plethora of options available for Linux. Just need something that is relatively simple to install (either 4.1.9 or 5.0.x). I would doubt the current MySQL RPM's would support something as old as RH 7.3. If you install something recent, there will be recent versions of MySQL (Mandrake even has 5.0 in the contrib section, I would assume Fedora would too. You will have to intstall the server portion, and probably the client portion. You then can use the MySQL GUI tools to admin the box from a Windows machine. Using something like Mandrake or Fedora, their installer tools will resolve all the dependencies for you. Hope that gets you started a little. If you need more detail, feel free to ask. j- k- -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
join query
Hello, I made little board, where all postings are in one table: Field Type --- topic_id int(6) unsigned topic_pid int(6) unsigned authorvarchar(50) mail varchar(255) posting_time timestamp(14) ipvarchar(15) body text reg_user tinyint(1) topic_pid shows what kind of topic it is, if 0 then its new thread else reply for exist one. Now i want to sort threads by last posting_time but because i havent mysql 4.1 in server then i cant use sub query. Is it possible to get it with joins and if yes then how? -- Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
instable behaviour of mysql
Hi again mysql-listers regarding the problem i mentionned this morning: for a description check out my message of this morning. in the meantime i have found out 1. how to reproduce the problem 2. how to work around the problem 1. how to reproduce: DROP TABLE afir_accounts ; DROP TABLE afir_contacts ; DROP TABLE afir_knowledge ; DROP TABLE afir_tasks ; DROP TABLE afir_timer ; DELETE FROM participants WHERE ident = 'afir' ; DELETE FROM part_val WHERE ident = 'afir' ; REVOKE all ON afir_accounts FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_contacts FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_knowledge FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_tasks FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_timer FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_files FROM 'afir'@'myhost.tld' ; REVOKE select ON participants FROM 'afir'@'myhost.tld' ; REVOKE select,update ON part_val FROM 'afir'@'myhost.tld' ; REVOKE GRANT OPTION ON *.* FROM 'afir'@'myhost.tld' ; DROP USER 'afir'@'myhost.tld'; you may substitute whatever string for afir. after such a sequence of commands the root-userid i described in my message earlier today cannot be used to access the mysql db anymore. you get the error message host myhost.tld is not allowed to access this mysql server. 2. how to work around i just added FLUSH PRIVILEGES ; to the above sequence, and everything works well. this command, however, according to mysql docu, should not be necessary after such a sequence of commands. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow query, how can i imporve it?
On Fri, Feb 11, 2005 at 10:45:46AM -0500, [EMAIL PROTECTED] wrote: Normally I do not reply to myself but I just realized that in my previous response I confused COUNT(*) (which is slow for InnoDB because it always does a table scan to resolve the version lock of each and every row) with Hello all, You just reminded me about this, I've been meaning to ask; are there any plans to fix this for InnoDB? It seems like quite a serious omission that InnoDB doesn't keep an accurate internal row count. Are there technical reasons why this isn't done, or is it in the TODO for any time soon? It's really one of the biggest things stopping me from switching wholly to InnoDB :( -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is this a good scheme for EcoRegions?
I already have a series of tables defining the world's continents, oceans, nations, states, etc. Actually, it's just two tables. Type defines the kinds of divisions, like this: TABLE: TYPE (ID) (Type) pla | planet kon | continent oce | ocean nat | nation sta | state pro | province Table famarea lists geographic names, identifying each by ID and type and matching them to their parents... TABLE: FAMAREA (ID)(Name) (Type) (Parent) us-al | Alabama | sta | usa afr | Africa | kon | ear (Earth) alb | Albania | nat | eur arc | Arctic Ocean | oce | oce (Oceania) Now I want to incorporate ecological regions. They include the eight REALMS the world is divided into, fourteen major BIOMES and over 800 ECOLOGICAL REGIONS. I can easily define these three types in my Types table: TABLE: TYPE (ID) (Type) kon | continent rea | realm bio | biome eco | ecoregion But then I quickly get confused because ecological regions don't follow national or even continental borders. Adding a table similar to famarea, simply listing each realm, biome and ecological region and including parents, would probably be too complex. My instinct is to first create three separate tables, listing the realms, biomes and ecoregions, respectively. I could also add parents to the EcoRealms table, since there are only 8 realms, and none have more than two parents: TABLE: ECOREALMS (ID) (Name) (Parent 1) (Parent 2) R-PA | Palearctic | Eurasia | Africa R-NA | Nearctic | North America | (NULL) I think I would then list two KINDS of parents for ecoregions, the biome and realm: TABLE: ECOREGIONS (ID)(Name) (Biome) (Realm) NA010 | Great Plains | B-5 | R-NA NA011 | Tallgrass Prairies | B-5 | R-NA AT023 | Steppe | B-5 | R-PA NA10 | Boreal Forest | B-2 | R-NA For example, the table above identifies the Great Plains, Tallgrass Prairies and Steppe as temperate grasslands (biome B-5). However, it matches the first two with NORTH AMERICAN grasslands, with steppes matched to Eurasian grasslands. If this is OK so far, then I need to figure out what to do with biomes, each of which can have several continents (or realms) as parents. Should I just add extra lines for extra parents, like this?: TABLE: ECOBIOMES (ID) (Name) (Parent) B-5 | Temperate Grasslands | R-NA (North America) B-5 | Temperate Grasslands | R-PA (Eurasia) ...or should I just make a table that simply lists the biomes and ID's, then make a fourth table that matches the biomes to their parents, like this?: B-5 | R-NA (North America) B-5 | R-PA (Eurasia) Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing ArcView Shapefile into a Database
I want to import some data into my database, but it's in an ArcView Shapefile, which I've never worked with. Does anyone know what kind of software I can use to access this data? Actually, I'd probably import it into a spreadsheeet first, then save it as a csv file and import it into my database. Thanks. __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing ArcView Shapefile into a Database
David - Here is a link to search results on http://www.esri.com. http://search.esri.com/results.cfm? h=10ho=0q=arcview+shapefilesa.x=26sa.y=11 ESRI is the leader in GIS software and the maker of the application, ArcView. Check out the link for the first search result on the link I gave you. The first result is entitled ESRI Shapefile Technical Description . It's a pdf whitepaper, and should get you started. Also, here is GRASS (http://openosx.com/grass/), the opensource alternative to ArcView... Cheers...James On Feb 12, 2005, at 10:01 AM, David Blomstrom wrote: I want to import some data into my database, but it's in an ArcView Shapefile, which I've never worked with. Does anyone know what kind of software I can use to access this data? Actually, I'd probably import it into a spreadsheeet first, then save it as a csv file and import it into my database. Thanks. __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- 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: Importing ArcView Shapefile into a Database
Hi David, I want to import some data into my database, but it's in an ArcView Shapefile, which I've never worked with. Does anyone know what kind of software I can use to access this data? Actually, I'd probably import it into a spreadsheeet first, then save it as a csv file and import it into my database. I've been working on a library called libmygis which is able to read varoius types of GIS data files (mainly Shapefiles for now). You're in luck. I've recently gotten a working version of mysqlgisimport which is able to take a SHP/SHX/DBF set and turn it into SQL directly to be imported. You can grab the source code of libmygis at: http://jcole.us/software/libmygis/ If you have any questions or need help getting it to work (hey, it's new!) please feel free to drop me a line. Same goes for feature requests. :) Regards, Jeremy -- Jeremy Cole Technical Yahoo - MySQL (Database) Geek Desk: 408 349 5104 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing ArcView Shapefile into a Database
Aha - that's why I like to ask questions on newsgroups rather than get secondhand news from Google! :) This sounds really cool. One question, though...when you talk about downloading the source code, are you saying your program is for Linux only? As much as I hate Microsoft, I'm still stuck with Windows XP. Actually, someone gave me a computer with a dual boot hard drive and SUSE installed, but I haven't had time to learn how to use it yet. Thanks for the tip! --- Jeremy Cole [EMAIL PROTECTED] wrote: Hi David, I want to import some data into my database, but it's in an ArcView Shapefile, which I've never worked with. Does anyone know what kind of software I can use to access this data? Actually, I'd probably import it into a spreadsheeet first, then save it as a csv file and import it into my database. I've been working on a library called libmygis which is able to read varoius types of GIS data files (mainly Shapefiles for now). You're in luck. I've recently gotten a working version of mysqlgisimport which is able to take a SHP/SHX/DBF set and turn it into SQL directly to be imported. You can grab the source code of libmygis at: http://jcole.us/software/libmygis/ If you have any questions or need help getting it to work (hey, it's new!) please feel free to drop me a line. Same goes for feature requests. :) Regards, Jeremy -- Jeremy Cole Technical Yahoo - MySQL (Database) Geek Desk: 408 349 5104 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing ArcView Shapefile into a Database
Hi David, This sounds really cool. One question, though...when you talk about downloading the source code, are you saying your program is for Linux only? As much as I hate Microsoft, I'm still stuck with Windows XP. I developed it on Linux, and haven't ever tried to compile it on Windows, as I don't have a Windows compiler suite handy. It would probably mostly compile on Windows. ;) Regards, Jeremy -- Jeremy Cole Technical Yahoo - MySQL (Database) Geek Desk: 408 349 5104 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow query, how can i imporve it?
Chris Elsworth wrote: On Fri, Feb 11, 2005 at 10:45:46AM -0500, [EMAIL PROTECTED] wrote: Normally I do not reply to myself but I just realized that in my previous response I confused COUNT(*) (which is slow for InnoDB because it always does a table scan to resolve the version lock of each and every row) with Hello all, You just reminded me about this, I've been meaning to ask; are there any plans to fix this for InnoDB? It seems like quite a serious omission that InnoDB doesn't keep an accurate internal row count. Are there technical reasons why this isn't done, or is it in the TODO for any time soon? It's really one of the biggest things stopping me from switching wholly to InnoDB :( Shawn answered this in his previous message. InnoDb uses versioning locks on it's records, that makes it practically impossible to determine exactly how many records are available to any user at any one time. This improves concurrency but makes COUNT(*) hard to compute quickly. In other words, yes, there is a technical reason why an accurate internal row count cannot be kept for InnoDB tables, because the answer to COUNT(*) is effectively connection specific. If I have added 12 rows but not yet committed, and Shawn has added 23 rows but not yet committed, then you, Shawn, and I each get different answers for COUNT(*). Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
certain content is causing an error with INSERT - please help
I'm ripping hair out, here's the problem...I'm trying to insert content
cited below into a field and it's causing this error, ie. there's
content i just cannot insert into the DB an it's causing the following
error message:
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 's
Edition).
Here's the content that will not insert:
iHow to Get It: A Guide to Defense - Related Information Resources/i
(DTIC, 2002; Librarian's Edition). A reference published by the Defense
Technical Information Service. The inspiration for this document.
I'm using PHP so I run the code below (note I think magic quotes might
be enabled on my server so the mysql_real_escape_string in the code
below might never be runned) but in any event it is escaped with:
if (!get_magic_quotes_gpc()) {
$descrip_field = mysql_real_escape_string($descrip_field);
}
I do the same escape for the title field tooThen futher down in the
code actual INSERT code is:
mysql_query(INSERT INTO howto.page (title, descrip) VALUES
('$title_field', '$descrip_field'))
or die (Can't connect because .mysql_error());
$page_id1 = mysql_insert_id();
if ($page_id1 == '0' ) { die ('died : page_id1=0'); }
What's wrong??? It inserts for everything else OK, but just won't insert
when i try with the content i cited?
Thanks,
Lee G.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing ArcView Shapefile into a Database
Sweet! Thank you Jeremy! -James On Feb 12, 2005, at 3:52 PM, Jeremy Cole wrote: Hi David, I want to import some data into my database, but it's in an ArcView Shapefile, which I've never worked with. Does anyone know what kind of software I can use to access this data? Actually, I'd probably import it into a spreadsheeet first, then save it as a csv file and import it into my database. I've been working on a library called libmygis which is able to read varoius types of GIS data files (mainly Shapefiles for now). You're in luck. I've recently gotten a working version of mysqlgisimport which is able to take a SHP/SHX/DBF set and turn it into SQL directly to be imported. You can grab the source code of libmygis at: http://jcole.us/software/libmygis/ If you have any questions or need help getting it to work (hey, it's new!) please feel free to drop me a line. Same goes for feature requests. :) Regards, Jeremy -- Jeremy Cole Technical Yahoo - MySQL (Database) Geek Desk: 408 349 5104 -- 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]
addendum - certain content is causing an error with INSERT ....
I fixed it. But I'm not clear on why. The following code ie. taking the
content and blatently putting the content through addslashes():
$descrip_field=addslashes($descrip_field);
Fixed it. But the code:
if (!get_magic_quotes_gpc()) {
$descrip_field = mysql_real_escape_string($descrip_field);
}
Did *not* fix it. So, to simplify this. Why would addslashes work and
the other code (which I see often as a recommend way to escape) not
work? This is a PHP question I suppose but I wanted to end the thread.
Sorry if I did not supply enough info up front for you to support.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing ArcView Shapefile into a Database
Hi, Sweet! Thank you Jeremy! FYI, make sure you grab the libmygis-0.3.tar.gz or later instead of any other version. I had some silly mistakes that made the DBF file required instead of optional. I've also ported to Mac OS X in 0.3. Regards, Jeremy -- Jeremy Cole Technical Yahoo - MySQL (Database) Geek Desk: 408 349 5104 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: join query
[EMAIL PROTECTED] wrote: Hello, I made little board, where all postings are in one table: Field Type --- topic_id int(6) unsigned topic_pid int(6) unsigned authorvarchar(50) mail varchar(255) posting_time timestamp(14) ipvarchar(15) body text reg_user tinyint(1) topic_pid shows what kind of topic it is, if 0 then its new thread else reply for exist one. Now i want to sort threads by last posting_time but because i havent mysql 4.1 in server then i cant use sub query. Is it possible to get it with joins and if yes then how? -- Lauri You need to break it down into two steps: First get the latest posting times, holding the result in a temporary table, then get all the posts properly ordered. Something like this (I'm using 'board' as the name of your table): CREATE TEMPORARY TABLE pt ( topic_pid int unsigned, latesttimestamp index (latest, topic_pid)); LOCK TABLES board READ; INSERT INTO pt SELECT topic_pid, MAX(posting_time) AS latest FROM board GROUP BY topic_pid; SELECT board.topic_id, board.topic_pid, board.author, board.posting_time FROM pt JOIN board USING (topic_pid) ORDER BY pt.latest DESC, pt.topic_pid DESC, board.posting_time DESC; UNLOCK TABLES; DROP TABLE tmp; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: certain content is causing an error with INSERT - please help
leegold wrote:
I'm ripping hair out, here's the problem...I'm trying to insert content
cited below into a field and it's causing this error, ie. there's
content i just cannot insert into the DB an it's causing the following
error message:
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 's
Edition).
Here's the content that will not insert:
iHow to Get It: A Guide to Defense - Related Information Resources/i
(DTIC, 2002; Librarian's Edition). A reference published by the Defense
Technical Information Service. The inspiration for this document.
Without special treatment the single quote (apostrophe) in Librarian's
terminates the string, leaving the rest as nonsense SQL.
I'm using PHP so I run the code below (note I think magic quotes might
be enabled on my server so the mysql_real_escape_string in the code
below might never be runned) but in any event it is escaped with:
if (!get_magic_quotes_gpc()) {
$descrip_field = mysql_real_escape_string($descrip_field);
}
The gpc stands for Get/Post/Cookie. I'd guess that $descrip_field didn't
come directly from a Get, Post, or Cookie, so magic_quotes didn't affect it.
In that case, you have to escape it by hand with mysql_real_escape_string,
but you don't do that because magic_quotes_gpc is on. I think this is a
good example of why magic_quotes is a bad idea. Better to turn it off and
then *always* process your strings.
I do the same escape for the title field tooThen futher down in the
code actual INSERT code is:
mysql_query(INSERT INTO howto.page (title, descrip) VALUES
('$title_field', '$descrip_field'))
or die (Can't connect because .mysql_error());
$page_id1 = mysql_insert_id();
if ($page_id1 == '0' ) { die ('died : page_id1=0'); }
It is good that you are checking for errors and printing them when you get
them, but often the error comes from the SQL statement not being what you
think it is (as happened here, I think). You would stand a better chance of
catching that if you included the actual SQL in the error message. I would
use something like
$query = INSERT INTO howto.page (title, descrip)
VALUES ('$title_field', '$descrip_field');
mysql_query($query) or die (Query: .$query.\n failed with error:
.mysql_error().\n);
That way, you see the actual query that was sent, as well as the error from
mysql.
What's wrong??? It inserts for everything else OK, but just won't insert
when i try with the content i cited?
Thanks,
Lee G.
Then leegold also wrote:
I fixed it. But I'm not clear on why. The following code ie. taking the
content and blatantly putting the content through addslashes():
$descrip_field=addslashes($descrip_field);
Fixed it. But the code:
if (!get_magic_quotes_gpc()) {
$descrip_field = mysql_real_escape_string($descrip_field);
}
As I said, turn off magic_quotes, then use
$descrip_field = mysql_real_escape_string($descrip_field);
Did *not* fix it. So, to simplify this. Why would addslashes work and
the other code (which I see often as a recommended way to escape) not
work? This is a PHP question I suppose but I wanted to end the thread.
Sorry if I did not supply enough info up front for you to support.
mysql_real_escape_string() should work. The problem is that it wasn't being
called when it needed to be.
See http://us2.php.net/manual/en/function.mysql-real-escape-string.php and
http://us2.php.net/manual/en/function.get-magic-quotes-gpc.php.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Total Counts, Multi-Report Questions
I have a request for a list of database entries Select x,y,z, etc that is now working OK. I have several questions, however. Here they are: 1.. Can I add a total line at the bottom of the report (ex: Total Selected: 23) for the total number of entries that are listed in the report? 2.. I want to print the same information but sorted in a different order on a second page of the report. Can I do that in one Select statement? 3.. If I need multiple select statements, is there a delimiter that says This is the end of my first select statement - the next thing you see is going to be a new select statement, so that all the necessary pages can be printed together in one job? Thanks, Sue
