Re: Hardware Raid and 2 Gig Limit
THanks everyone for all your help! Steve - Original Message - From: Pete Harlan [EMAIL PROTECTED] To: David T-G [EMAIL PROTECTED] Cc: mysql users [EMAIL PROTECTED]; Steve Vernon [EMAIL PROTECTED] Sent: Friday, October 31, 2003 4:16 PM Subject: Re: Hardware Raid and 2 Gig Limit On Fri, Oct 31, 2003 at 05:44:02AM -0500, David T-G wrote: % % Does the 2 Gig file size limit on Linux get broken when I have a hardware % raid controller? The limit applies only to ext2 filesystems, and not all of them at that; ext3 and reiserfs (and others) can happily write much larger files. The 2GB filesize limit was due to glibc and the linux kernel, not the ext2 filesystem. Any linux distro from the past year or so should be able to handle 2GB files on any filesystem. Hardware raid is invisible to Linux, so won't affect the maximum usable filesize. --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hardware Raid and 2 Gig Limit
Hello, Does the 2 Gig file size limit on Linux get broken when I have a hardware raid controller? My ISP says I don't need the raid option activated on MySQL. Thanks, Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hardware Raid and 2 Gig Limit
Hiya! Thanks for the quick reply!!! But dosen't it make more sense to have 20 0.5 Gig files rather than one 10 Gig file? I know you can split files, but basically because we have raid I trust it to a point. But I would like to make backup's. It costs a lot to have our server provider to do backup's themselves. We can't afford replication and backups at the moment. So I plan to stop MySQL. Copy the database files to a temp directory. THen download them at my leisure. Oh and I suppose I need to start MySQL then! ;-) The backup will be to Windows 2000, and I want to be able to use the local copy of the files. So if I have smaller files, I can download them, and keep a local copy. Theres no way I want to start downloading a 10 Gig file from the server. Ok I can do it, I have a download manager etc, Iv'e downloaded bigger. But I guess it must be a massive strain on the server sending the data, and we dont have that fast an internet connection so it's easier. We only have 512 broadband and it sometimes messes up files larger than 2 Gig. The download manager realises there is a problem, seems to backtrack or something, so takes ages. Cable is not available where I live. If we go beyond 10 Gigs, it just increases the complexity. Do you or anyone know of any serious speed differences between having one file or 20 smaller files for one table? With one files, isn't there a worry if it gets corrupted you loose the lot? A couple of years ago I looked into big tables for myself and everyone said you need the raid option in MySQL. Now I'm not sure! Thanks, Steve - Original Message - From: David T-G [EMAIL PROTECTED] To: mysql users [EMAIL PROTECTED] Cc: Steve Vernon [EMAIL PROTECTED] Sent: Friday, October 31, 2003 10:44 AM Subject: Re: Hardware Raid and 2 Gig Limit -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Steve -- ...and then Steve Vernon said... % % Hello, Hi! % % Does the 2 Gig file size limit on Linux get broken when I have a hardware % raid controller? The limit applies only to ext2 filesystems, and not all of them at that; ext3 and reiserfs (and others) can happily write much larger files. % % My ISP says I don't need the raid option activated on MySQL. I would generally agree. I can't imagine an ISP not using a journalled filesystem such as those above. More to the point, though, the mysql raid option has nothing to do with disk RAID; they are completely separate. % % Thanks, % % Steve HTH HAND :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE/ojzyGb7uCXufRwARAtEmAJ9i3oIMbLTA4yq8koPcEOUwD7SpRwCfe+bn rvzVjuD8tIxO8AVj3jp02CI= =QdfG -END PGP SIGNATURE- -- 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: Hardware Raid and 2 Gig Limit
Hiya! Thanks for the help!!! Do RSync like big files? Or does it prefer smaller files? I see it supports larger than 2 Gig files, but, Ive not seen any speed comparrisons etc. E.g. one big file, or 10 small ones for example. THANKS! Steve - Original Message - From: Peter Lovatt [EMAIL PROTECTED] To: Steve Vernon [EMAIL PROTECTED]; Mysql List [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, October 31, 2003 12:17 PM Subject: RE: Hardware Raid and 2 Gig Limit Hi We have a similar challenge. Offsite backups with huge amounts of data, without spending a fortune. We have a local Linux box and the remote server both running rsync. http://samba.anu.edu.au/rsync/ Rsync is brilliant because it only updates file sectors that have changed. We would only use it if we can stop MySql but then synchronising takes minutes (1MB broadband connection), particularly using compression. If you cannot add your own server software MySql files also compress very well (gzip 90%+), but this means some server load. HTH Peter --- Excellence in internet and open source software --- Sunmaia Birmingham UK www.sunmaia.net tel. 0121-242-1473 International +44-121-242-1473 --- -Original Message- From: Steve Vernon [mailto:[EMAIL PROTECTED] Sent: 31 October 2003 12:00 To: Mysql List Cc: [EMAIL PROTECTED] Subject: Re: Hardware Raid and 2 Gig Limit Hiya! Thanks for the quick reply!!! But dosen't it make more sense to have 20 0.5 Gig files rather than one 10 Gig file? I know you can split files, but basically because we have raid I trust it to a point. But I would like to make backup's. It costs a lot to have our server provider to do backup's themselves. We can't afford replication and backups at the moment. So I plan to stop MySQL. Copy the database files to a temp directory. THen download them at my leisure. Oh and I suppose I need to start MySQL then! ;-) The backup will be to Windows 2000, and I want to be able to use the local copy of the files. So if I have smaller files, I can download them, and keep a local copy. Theres no way I want to start downloading a 10 Gig file from the server. Ok I can do it, I have a download manager etc, Iv'e downloaded bigger. But I guess it must be a massive strain on the server sending the data, and we dont have that fast an internet connection so it's easier. We only have 512 broadband and it sometimes messes up files larger than 2 Gig. The download manager realises there is a problem, seems to backtrack or something, so takes ages. Cable is not available where I live. If we go beyond 10 Gigs, it just increases the complexity. Do you or anyone know of any serious speed differences between having one file or 20 smaller files for one table? With one files, isn't there a worry if it gets corrupted you loose the lot? A couple of years ago I looked into big tables for myself and everyone said you need the raid option in MySQL. Now I'm not sure! Thanks, Steve - Original Message - From: David T-G [EMAIL PROTECTED] To: mysql users [EMAIL PROTECTED] Cc: Steve Vernon [EMAIL PROTECTED] Sent: Friday, October 31, 2003 10:44 AM Subject: Re: Hardware Raid and 2 Gig Limit -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Steve -- ...and then Steve Vernon said... % % Hello, Hi! % % Does the 2 Gig file size limit on Linux get broken when I have a hardware % raid controller? The limit applies only to ext2 filesystems, and not all of them at that; ext3 and reiserfs (and others) can happily write much larger files. % % My ISP says I don't need the raid option activated on MySQL. I would generally agree. I can't imagine an ISP not using a journalled filesystem such as those above. More to the point, though, the mysql raid option has nothing to do with disk RAID; they are completely separate. % % Thanks, % % Steve HTH HAND :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE/ojzyGb7uCXufRwARAtEmAJ9i3oIMbLTA4yq8koPcEOUwD7SpRwCfe+bn rvzVjuD8tIxO8AVj3jp02CI= =QdfG -END PGP SIGNATURE- -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql
date_format function - German
Hello, I have read and searched the internet, but I cannot find much on getting mysql to return German and other languages months. Is there codes you use? I really would prefer to use the English MySQL (if there is such a one) as I don't know much German, and need the error messages in English! I suppose I could return the month as a number, and then output the text from that, but this seems a bit tacky! Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Double Select?!
Hiya, Any help would be appreciated! Due to various hardware requirements, on our exiting server space is a problem not speed. I store a lot of details of members, and have worked out that I will save space by storing lastname's, firstname's etc in a seperate table. e.g. lastname table is id and lastname field. firstname table is id and firstname field My members table then has a field called lastnameid, firstnameid etc. So I can do: SELECT firstname, lastname FROM members, firstname, lastname WHERE firstnameid=firstname.id AND lastnameid=lastname.id So as an extension I am whating to include middle names, so in the members table I have a collum called middlenameid, how do I do this with a SELECT? Thanks, Steve SQL cos I have to include it! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Year Lists
Thanks for the email... Brilliant idea, but it does not give 0 for rows without a project for a year. It gives 1 for these rows. As a test idea, I removed the group by and count, and instead of count outputted the start and end year. What that gave me was say for 1953 where there was 3 projects, 3 lines with the start and end year for those projects. But for a year without a project gives start and end year as null. e.g. (not true examle) yearstartyearendyear 1901NULLNULL 1953 19521954 1953 19511956 195319501980 I suppose I need to do a where to remove null values, but I cant work out where it goes! Thanks, ;-) Steve XX Sounds to me like you'll need to join for that -- and list all the years you are interested in in another table. Try this? SELECT y.Year, count(*) FROM Year as y LEFT JOIN projects ON project.startyear=y.Year AND project.endyear=y.Year GROUP BY y.Year; This will give you a count of 0 for any years without a project (Almost 100% certain =] ). But don't blame me if the query is grossly inefficient! Nick Elliott - Original Message - From: Steve Vernon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 31, 2002 10:44 AM Subject: Re: Year Lists Martin, Thanks for the reply. Good plan but at the moment my query is like (had to alter it slightly as it was in PHP and multiple lines, so dont take it as working). SELECT projects.name, project.endyear, project.startyear FROM projects WHERE projects.startyear = '(INPUT THE YEAR HERE)(projects.leaveyear = '(YEAR HERE AS WELL)' || projects.leaveyear = '' ) LIMIT 5000 What you suggest basically will give a count on start year (or end year if altered), which I have implemented and working for ended year. Say a project started in 1920 and another in 1921 and they both finish in 1925. I would expect to get this: 19201 Project 19212 Projects 19222 Projects 19232 Projects 19242 Projects 19252 Projects 19260 Projects (or no line at all ideally). up to 2003 Ideally if a year does not have any projects it would not be listed. Any more help would be great, or I could be wrong and you could have the answer! Steve On Tue, 2002-12-31 at 14:53, Steve Vernon wrote: Hiya, I have a database about projects in a company, they all have a start year and end year. If the projects have not ended then they get a end year of . I have made a SQL command, given a year, works out with projects are running in that year. The company I am doing this for, wants basically a drop down box which says (1950- 8 Projects) and such like. Now with the current command I have it would mean 92or so SQL commands as the company has records back to 1910. Is there a way to do this in one command? I have searched all the mannual and I can work out something similair to a for loop in SQL. Would variables help? Hi Steve, You want to use select group by, something like: select start_year, count(*) from projects group by start_year; == Martin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Year Lists
Hiya, I have a database about projects in a company, they all have a start year and end year. If the projects have not ended then they get a end year of . I have made a SQL command, given a year, works out with projects are running in that year. The company I am doing this for, wants basically a drop down box which says (1950- 8 Projects) and such like. Now with the current command I have it would mean 92or so SQL commands as the company has records back to 1910. Is there a way to do this in one command? I have searched all the mannual and I can work out something similair to a for loop in SQL. Would variables help? Any help would be greatly appreciated. Have a great New Year. Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Year Lists
Martin, Thanks for the reply. Good plan but at the moment my query is like (had to alter it slightly as it was in PHP and multiple lines, so dont take it as working). SELECT projects.name, project.endyear, project.startyear FROM projects WHERE projects.startyear = '(INPUT THE YEAR HERE)(projects.leaveyear = '(YEAR HERE AS WELL)' || projects.leaveyear = '' ) LIMIT 5000 What you suggest basically will give a count on start year (or end year if altered), which I have implemented and working for ended year. Say a project started in 1920 and another in 1921 and they both finish in 1925. I would expect to get this: 19201 Project 19212 Projects 19222 Projects 19232 Projects 19242 Projects 19252 Projects 19260 Projects (or no line at all ideally). up to 2003 Ideally if a year does not have any projects it would not be listed. Any more help would be great, or I could be wrong and you could have the answer! Steve On Tue, 2002-12-31 at 14:53, Steve Vernon wrote: Hiya, I have a database about projects in a company, they all have a start year and end year. If the projects have not ended then they get a end year of . I have made a SQL command, given a year, works out with projects are running in that year. The company I am doing this for, wants basically a drop down box which says (1950- 8 Projects) and such like. Now with the current command I have it would mean 92or so SQL commands as the company has records back to 1910. Is there a way to do this in one command? I have searched all the mannual and I can work out something similair to a for loop in SQL. Would variables help? Hi Steve, You want to use select group by, something like: select start_year, count(*) from projects group by start_year; == Martin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
DISTINCT on one collum
MySQL as the filter stupidly does not match SELECT Hiya, How do you get a disctinct value from one collumn only? I am basically using it to construct a dynamic menu, and need the menu names at a level. Items are in one table, like CATEGORY, SUBCATEGORY, SUBSUBCATEGORY, PAGE, ID I could just use SELECT DISCTINCT subcategory FROM placetype WHERE category='Random' But if this subcategory does not have any daughters then I would prefer to do: SELECT DISCTINCT subcategory, page, id FROM placetype WHERE category='Random' But this returns multiple versions becauyse of how distinct works. Thanks, Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: unicode encoding
Hiya, Have you got a rough timescale as to when 4.1 will come out? If not then is there a way to simulate Unicode in MySQL? THanks, Steve XX - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: i'm just missing something
There is a mysql database with a user table in it. Add a new user, then reload the database! Remember to password the password. And it will work! I been tring to read the docs on the website, and tried looking it up, but i'm getting confused. I am trying to make a database and a user. to make the database i use mysql create database dbname; that seems to work. but how do i make a user for it? _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
large Scale Storage
Hello, I am working on a website for a client which is basically biological database, and some have pictures, I am not allowed to give more out. It will be stored in MySQL. Basically the client says there are about 500 million different items of data, which each holds about 10 small text fields. There are no logical splits in the data. So I cant just split it over multiple servers, with one server holding type a etc. There are also multiple tables, which link together. To be honest I want to do this myself, although I have never done a website over multiple servers, or basically got to the limit over using one server. Are there any resources or links or helpful comments on how to do this please? How much will one server take? Is there a way to make multiple Linux servers act like one database as it should be faster this way? I am planning to use multiple Linux servers, how much would I need? Thanks! Steve - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php