Re: storing .tar files in mysql
Personally, I would have to disagree. I have just completed a year-long uni project whereby we built some software that stored massive images in InnoDB tables. We found that the performance was (at very worst) comparable to the alternative method. Additionally, this method is better on many levels. You can have a sealed server and you protect yourself from users who like playing with things (like filenames). Regards, Chris On Sat, 2003-12-13 at 08:13, Neil Watson wrote: On Fri, Dec 12, 2003 at 02:54:44PM -0600, [EMAIL PROTECTED] wrote: I am new to mysql and I was wondering if someone could point me in the right direction on how to store .tar and .tar.gz (bzip2) files inside a mysql database. I have googled to try and find some help there but most I believe the data type you are looking for is blob. However, you would be better off using your row to point to a file located on the hard drive instead of actually in the database. I can't recall the technical details but, your performance will be much better that way. -- Neil Watson | Gentoo Linux Network Administrator | Uptime 1 day http://watson-wilson.ca | 2.4.23 AMD Athlon(tm) MP 2000+ x 2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0 won't compile on SCO OpenServer 5
Description: Latest source code from the BK source repository for 4.0 doesn't compile on SCO OpenServer 5. Error is undefined reference to pthread_key_delete in libmysys.a(my_thr_init.o). It appears that recent modifications make use of pthread_key_delete. FSU-pthreads 3.5 library doesnot appear to have this function. Not sure about FSU-pthreads 3.9, but last time I tried to compile MySQL with it, I got many errors so I reverted back to 3.5. How-To-Repeat: ./configure --without-named-z-libs --with-low-memory --with-unix-socket-path=/var/lib/mysql/mysql-4.0.12.sock --localstatedir=/var/lib/mysql --prefix=/usr --exec-prefix=/usr --program-suffix=-4.0.12 --with-openssl --with-tcp-port=4012 --without-innodb gmake Fix: Submitter-Id: [EMAIL PROTECTED] Originator: Organization: Silk Systems Inc. MySQL support: none Synopsis: FSU-pthreads library doesn't have pthread_key_delete Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.14 (Source distribution) C compiler:2.95.2 C++ compiler: 2.95.2 Environment: Intel x86, SCO UNIX Openserver 5.0.4, SCO UNIX,zlib 1.4,FSU-pthreads 3.5 System: SCO_SV sysdev 3.2 2 i386 Some paths: /usr/bin/perl /bin/make /usr/local/bin/gmake /usr/local/bin/gcc /bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/i386-pc-sco3.2v5.0.5/2.95.2/specs gcc version 2.95.2 19991024 (release) Compilation info: CC='gcc' CFLAGS=' ' CXX='c++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root sys 36 May 19 1998 /lib/libc.a - /opt/K/SCO/unixds/5.1.0Ha/lib/libc.a lrwxrwxrwx 1 root sys 37 May 19 1998 /lib/libc.so - /opt/K/SCO/unixds/5.1.0Ha/lib/libc.so lrwxrwxrwx 1 root sys 40 May 19 1998 /usr/lib/libc.a - /opt/K/SCO/unixds/5.1.0Ha/usr/lib/libc.a lrwxrwxrwx 1 root sys 41 May 19 1998 /usr/lib/libc.so - /opt/K/SCO/unixds/5.1.0Ha/usr/lib/libc.so lrwxrwxrwx 1 root root 41 May 13 1998 /usr/lib/libc.so.1 - /opt/K/SCO/Unix/5.0.4Eb/usr/lib/libc.so.1 Configure command: ../configure --without-named-z-libs --with-low-memory --with-unix-socket-path=/var/lib/mysql/mysql-4.0.12.sock --localstatedir=/var/lib/mysql --prefix=/usr --exec-prefix=/usr --program-suffix=-4.0.12 --with-openssl --with-tcp-port=4012 --with-innodb Perl: This is perl, version 5.004_04 built for i486-pc-sco3.2v5.0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: storing .tar files in mysql
Chris Nolan wrote: Personally, I would have to disagree. I have just completed a year-long uni project whereby we built some software that stored massive images in InnoDB tables. We found that the performance was (at very worst) comparable to the alternative method. Additionally, this method is better on many levels. You can have a sealed server and you protect yourself from users who like playing with things (like filenames). On Sat, 2003-12-13 at 08:13, Neil Watson wrote: I believe the data type you are looking for is blob. However, you would be better off using your row to point to a file located on the hard drive instead of actually in the database. I can't recall the technical details but, your performance will be much better that way. I'd agree with Neil. I've never believed in that, surely it's just causing overhead for MySQL. Surely far better to have the files managed by the file system? That's what it's there for. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: storing .tar files in mysql
you could very well do that, and frankly that is how alot of websites work. Yep, including one I run. That site has to generate img and a href links for visitors, and it seems far easier to return /pics/imagefoo.jpg then the image itself and decide how to embed that into the page. But storing the actual binary file, weither it is a .jpg or .tar, allows your application/website/whatever to be independent of your files location on the server. Is this really such a problem? When I migrated the above-mentioned website, I had some minor path issues which resolved with symbolic links. If your path for images changes, make a symlink to simulate where it used to be; or use an UPDATE statement to change the path prefix on the images in the database. If you really want to be clever, use a single-table row for the prefix (/home/foo/myimages) and another table for the actually names (foo.jpg) and just update the prefix when location changes. This way, when you select a specific row, you will have the binary... regardless of path locations. It also makes data organization cleaner as well. If you decide you don't need a binary anymore and delete it from the database, it will be gone. A cron job which does a SELECT imagename FROM imagetable, then compares to the directory, and removes the non-exisiting images, would also work. Or you could write a database trigger to call out and delete the image when the row was deleted. I do admit it's less nice than being able to just drop the image from the table, but you also lose the ability to manipulate the content on the filesystem level. For example, in your .tar example, how are you going to view the contents of one of those .tar files? You'll have to SELECT it out of the database, write it somewhere, then view it; if you want to update it, you have to SELECT it out, edit it, and UPDATE the table... where as just storing the path will not, which can get pretty ugly. I hope that helps Works like a charm for us. The downsides I see to storing in the database are: * More overhead reading and writing from the database * Much much larger database sizes. For me it's easy to backup and restore my database. If my 2GB+ of images were IN the database, well, that would be a different story. * I can schedule backups of the database, and filesystem content, at different times; this reduces the impact if something goes wrong during a backup. Of course, I do see the advantages of embedding the images, tar files, etc into the database; I just think it has enough issues of it's own that I would caution against doing it. Here's another idea to chew on: My full-time employer runs over a thousand sites for newspapers across the country. We have image names embedded into the database, not the data. Why? Well, we store images on NAS (Network Attached Storage) devices. We have several of them, and use custom scripts to keep the content on them the same. When we have a failure on one, we simply update a piece of code which defines which server to read from. Now, if we put those images into the database, we'd have a few issue: * Our database size would grow far, far beyond the current size, which is over 60GB. * Restores to the database would be much slower, and in our case, if we have a failure, we'd rather get everything up fast and then fix images then wait longer and get both. Time == money to our customers. * Instead of returning the filename to the application software (ASP and CF in our case), you'd return the whole image, and that's going to be a huge drain on server CPU. We'd need something like 2x the CPU power we have now, with many more high-speed disks. (and 15K RPM isn't cheap!) * Replication from the publisher to the subscribers would be much more intensive and require more bandwith and CPU power, again driving our hardware needs up. You could argue that we'd loose the need for the NASes. I haven't done a comparison on how the pricing would work. That's my .02$ and a then some. Joshua Thomas dan Joshua Thomas wrote: Can I ask why? Why not define a char(50) (or whatever size) with the relative or complete path to the .tar file? Storing it in your database would create huge row sizes. Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, December 12, 2003 3:55 PM To: [EMAIL PROTECTED] Subject: storing .tar files in mysql Hi all, I am new to mysql and I was wondering if someone could point me in the right direction on how to store .tar and .tar.gz (bzip2) files inside a mysql database. I have googled to try and find some help there but most of the hits come back with binary image files. I have gone thru the mysql tutorial and I can
Re: storing .tar files in mysql
Really? In both cases, it's just bits on a disk. In the case where you don't have access to a shared file repository for your client apps, you haven't got the option of just storing paths. Regards, Chris On Sat, 2003-12-13 at 23:30, Sime wrote: Chris Nolan wrote: Personally, I would have to disagree. I have just completed a year-long uni project whereby we built some software that stored massive images in InnoDB tables. We found that the performance was (at very worst) comparable to the alternative method. Additionally, this method is better on many levels. You can have a sealed server and you protect yourself from users who like playing with things (like filenames). On Sat, 2003-12-13 at 08:13, Neil Watson wrote: I believe the data type you are looking for is blob. However, you would be better off using your row to point to a file located on the hard drive instead of actually in the database. I can't recall the technical details but, your performance will be much better that way. I'd agree with Neil. I've never believed in that, surely it's just causing overhead for MySQL. Surely far better to have the files managed by the file system? That's what it's there for. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: storing .tar files in mysql
Talk about a decent reply! For web site stuff, having control over everything is pretty much a requirement (if you want to do anything non-trivial). The way that I serve images from the database is by parsing URLs and I've found that the performance is very good. The fact that I can move stuff around at will (say, move a DB server to another host) without having to worry about breaking things is also reassuring. In non-web stuff though, the story changes. Trying to ensure that my clients don't bugger around with the shares and things that are setup in their offices is difficult. Everything being in the database removes one more problem that can occur and I get security for images and other binary data to boot (critical in the medical world). Further, it's one less point of failure in the solution. There are lots of arguments either way. :-) Best regards, Chris On Sat, 2003-12-13 at 23:33, Joshua Thomas wrote: you could very well do that, and frankly that is how alot of websites work. Yep, including one I run. That site has to generate img and a href links for visitors, and it seems far easier to return /pics/imagefoo.jpg then the image itself and decide how to embed that into the page. But storing the actual binary file, weither it is a .jpg or .tar, allows your application/website/whatever to be independent of your files location on the server. Is this really such a problem? When I migrated the above-mentioned website, I had some minor path issues which resolved with symbolic links. If your path for images changes, make a symlink to simulate where it used to be; or use an UPDATE statement to change the path prefix on the images in the database. If you really want to be clever, use a single-table row for the prefix (/home/foo/myimages) and another table for the actually names (foo.jpg) and just update the prefix when location changes. This way, when you select a specific row, you will have the binary... regardless of path locations. It also makes data organization cleaner as well. If you decide you don't need a binary anymore and delete it from the database, it will be gone. A cron job which does a SELECT imagename FROM imagetable, then compares to the directory, and removes the non-exisiting images, would also work. Or you could write a database trigger to call out and delete the image when the row was deleted. I do admit it's less nice than being able to just drop the image from the table, but you also lose the ability to manipulate the content on the filesystem level. For example, in your .tar example, how are you going to view the contents of one of those .tar files? You'll have to SELECT it out of the database, write it somewhere, then view it; if you want to update it, you have to SELECT it out, edit it, and UPDATE the table... where as just storing the path will not, which can get pretty ugly. I hope that helps Works like a charm for us. The downsides I see to storing in the database are: * More overhead reading and writing from the database * Much much larger database sizes. For me it's easy to backup and restore my database. If my 2GB+ of images were IN the database, well, that would be a different story. * I can schedule backups of the database, and filesystem content, at different times; this reduces the impact if something goes wrong during a backup. Of course, I do see the advantages of embedding the images, tar files, etc into the database; I just think it has enough issues of it's own that I would caution against doing it. Here's another idea to chew on: My full-time employer runs over a thousand sites for newspapers across the country. We have image names embedded into the database, not the data. Why? Well, we store images on NAS (Network Attached Storage) devices. We have several of them, and use custom scripts to keep the content on them the same. When we have a failure on one, we simply update a piece of code which defines which server to read from. Now, if we put those images into the database, we'd have a few issue: * Our database size would grow far, far beyond the current size, which is over 60GB. * Restores to the database would be much slower, and in our case, if we have a failure, we'd rather get everything up fast and then fix images then wait longer and get both. Time == money to our customers. * Instead of returning the filename to the application software (ASP and CF in our case), you'd return the whole image, and that's going to be a huge drain on server CPU. We'd need something like 2x the CPU power we have now, with many more high-speed disks. (and 15K RPM isn't cheap!) * Replication from the publisher to the subscribers would be much more intensive and require more bandwith and CPU power, again driving our hardware needs up. You could argue that we'd loose the need for the NASes. I haven't done a comparison on how the pricing
RE: storing .tar files in mysql
Forgot something in my other reply. With the NAS - what's to say that MySQL's retrieval and network protocol is not more efficient than whatever is running on your NAS boxes? Conversely, MySQL's current 16 MB per transfer limitation may very well not allow it to act in this role at all. Ah, the wonders of open discussion! Best regards, Chris On Sat, 2003-12-13 at 23:33, Joshua Thomas wrote: you could very well do that, and frankly that is how alot of websites work. Yep, including one I run. That site has to generate img and a href links for visitors, and it seems far easier to return /pics/imagefoo.jpg then the image itself and decide how to embed that into the page. But storing the actual binary file, weither it is a .jpg or .tar, allows your application/website/whatever to be independent of your files location on the server. Is this really such a problem? When I migrated the above-mentioned website, I had some minor path issues which resolved with symbolic links. If your path for images changes, make a symlink to simulate where it used to be; or use an UPDATE statement to change the path prefix on the images in the database. If you really want to be clever, use a single-table row for the prefix (/home/foo/myimages) and another table for the actually names (foo.jpg) and just update the prefix when location changes. This way, when you select a specific row, you will have the binary... regardless of path locations. It also makes data organization cleaner as well. If you decide you don't need a binary anymore and delete it from the database, it will be gone. A cron job which does a SELECT imagename FROM imagetable, then compares to the directory, and removes the non-exisiting images, would also work. Or you could write a database trigger to call out and delete the image when the row was deleted. I do admit it's less nice than being able to just drop the image from the table, but you also lose the ability to manipulate the content on the filesystem level. For example, in your .tar example, how are you going to view the contents of one of those .tar files? You'll have to SELECT it out of the database, write it somewhere, then view it; if you want to update it, you have to SELECT it out, edit it, and UPDATE the table... where as just storing the path will not, which can get pretty ugly. I hope that helps Works like a charm for us. The downsides I see to storing in the database are: * More overhead reading and writing from the database * Much much larger database sizes. For me it's easy to backup and restore my database. If my 2GB+ of images were IN the database, well, that would be a different story. * I can schedule backups of the database, and filesystem content, at different times; this reduces the impact if something goes wrong during a backup. Of course, I do see the advantages of embedding the images, tar files, etc into the database; I just think it has enough issues of it's own that I would caution against doing it. Here's another idea to chew on: My full-time employer runs over a thousand sites for newspapers across the country. We have image names embedded into the database, not the data. Why? Well, we store images on NAS (Network Attached Storage) devices. We have several of them, and use custom scripts to keep the content on them the same. When we have a failure on one, we simply update a piece of code which defines which server to read from. Now, if we put those images into the database, we'd have a few issue: * Our database size would grow far, far beyond the current size, which is over 60GB. * Restores to the database would be much slower, and in our case, if we have a failure, we'd rather get everything up fast and then fix images then wait longer and get both. Time == money to our customers. * Instead of returning the filename to the application software (ASP and CF in our case), you'd return the whole image, and that's going to be a huge drain on server CPU. We'd need something like 2x the CPU power we have now, with many more high-speed disks. (and 15K RPM isn't cheap!) * Replication from the publisher to the subscribers would be much more intensive and require more bandwith and CPU power, again driving our hardware needs up. You could argue that we'd loose the need for the NASes. I haven't done a comparison on how the pricing would work. That's my .02$ and a then some. Joshua Thomas dan Joshua Thomas wrote: Can I ask why? Why not define a char(50) (or whatever size) with the relative or complete path to the .tar file? Storing it in your database would create huge row sizes. Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra ---
RE: storing .tar files in mysql
With the NAS - what's to say that MySQL's retrieval and network protocol is not more efficient than whatever is running on your NAS boxes? Well, currently we work like so: Client - Webserver/Application Server - Database The database returns file names to the application/webserver (yes, we're not fully three-tiered) which returns to the client, and the client must then generate more calls for images: Client - Webserver - Image Storage So I have 1 database call and several addtl HTTP calls for each page call. At our stage, the bandwith from the NAS to the webservers are not a limiting factor. If we put everything into the database, then I have each call for an image go to the database (unless there is a better method?), so I have several database calls and several HTTP calls for each primary page. Granted, this is simplified, but it seems like more overhead. What's the best practice method to retrieve images stored in a database for webserving? Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- Conversely, MySQL's current 16 MB per transfer limitation may very well not allow it to act in this role at all. Ah, the wonders of open discussion! Best regards, Chris On Sat, 2003-12-13 at 23:33, Joshua Thomas wrote: you could very well do that, and frankly that is how alot of websites work. Yep, including one I run. That site has to generate img and a href links for visitors, and it seems far easier to return /pics/imagefoo.jpg then the image itself and decide how to embed that into the page. But storing the actual binary file, weither it is a .jpg or .tar, allows your application/website/whatever to be independent of your files location on the server. Is this really such a problem? When I migrated the above-mentioned website, I had some minor path issues which resolved with symbolic links. If your path for images changes, make a symlink to simulate where it used to be; or use an UPDATE statement to change the path prefix on the images in the database. If you really want to be clever, use a single-table row for the prefix (/home/foo/myimages) and another table for the actually names (foo.jpg) and just update the prefix when location changes. This way, when you select a specific row, you will have the binary... regardless of path locations. It also makes data organization cleaner as well. If you decide you don't need a binary anymore and delete it from the database, it will be gone. A cron job which does a SELECT imagename FROM imagetable, then compares to the directory, and removes the non-exisiting images, would also work. Or you could write a database trigger to call out and delete the image when the row was deleted. I do admit it's less nice than being able to just drop the image from the table, but you also lose the ability to manipulate the content on the filesystem level. For example, in your .tar example, how are you going to view the contents of one of those .tar files? You'll have to SELECT it out of the database, write it somewhere, then view it; if you want to update it, you have to SELECT it out, edit it, and UPDATE the table... where as just storing the path will not, which can get pretty ugly. I hope that helps Works like a charm for us. The downsides I see to storing in the database are: * More overhead reading and writing from the database * Much much larger database sizes. For me it's easy to backup and restore my database. If my 2GB+ of images were IN the database, well, that would be a different story. * I can schedule backups of the database, and filesystem content, at different times; this reduces the impact if something goes wrong during a backup. Of course, I do see the advantages of embedding the images, tar files, etc into the database; I just think it has enough issues of it's own that I would caution against doing it. Here's another idea to chew on: My full-time employer runs over a thousand sites for newspapers across the country. We have image names embedded into the database, not the data. Why? Well, we store images on NAS (Network Attached Storage) devices. We have several of them, and use custom scripts to keep the content on them the same. When we have a failure on one, we simply update a piece of code which defines which server to read from. Now, if we put those images into the database, we'd have a few issue: * Our database size would grow far, far beyond the current size, which is over 60GB. * Restores to the database would be much slower, and in our case, if we have a failure, we'd rather get everything up fast and then fix
Re: new install - command prompt doesn't work
i have done that C:\mysql but it will say 'mysql' is not recognized as an internal or external command, operable program or batch file there must be something else i'm missing... Paul DuBois [EMAIL PROTECTED] wrote: At 18:21 -0800 12/12/03, Betta Jazzy Brown wrote: I have installed MySql on my PC and I was trying to run the program by going to the command prompt typing: C:\net start mysql it says The MySql service has started successfully. the problem is that the command prompt doesn't read mysql but still reads C:\ What is the problem here and how can I correct it... There is no problem. The server has started successfully. But you need to *connect* to the server using a client program. Try running mysql from the C prompt, for example: C:\ mysql i have installed MySql Database Server Standard Clients (4.0) and MySql Control Center and I'm using Win2K OS... -- As well...what specifically can I use MySql for? I have a website that I'm trying to learn a lot of things through to make my resume more beefy and to make myself more marketable...What do you all suggest? Thanks, b-jazzy -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/
Re: new install - command prompt doesn't work
Try c:\mysql\bin\mysql.exe If that works, then put c:\mysql\bin the system's path. - Original Message - From: Betta Jazzy Brown [EMAIL PROTECTED] To: Paul DuBois [EMAIL PROTECTED]; Betta Jazzy Brown [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, December 13, 2003 8:48 AM Subject: Re: new install - command prompt doesn't work i have done that C:\mysql but it will say 'mysql' is not recognized as an internal or external command, operable program or batch file there must be something else i'm missing... Paul DuBois [EMAIL PROTECTED] wrote: At 18:21 -0800 12/12/03, Betta Jazzy Brown wrote: I have installed MySql on my PC and I was trying to run the program by going to the command prompt typing: C:\net start mysql it says The MySql service has started successfully. the problem is that the command prompt doesn't read mysql but still reads C:\ What is the problem here and how can I correct it... There is no problem. The server has started successfully. But you need to *connect* to the server using a client program. Try running mysql from the C prompt, for example: C:\ mysql i have installed MySql Database Server Standard Clients (4.0) and MySql Control Center and I'm using Win2K OS... -- As well...what specifically can I use MySql for? I have a website that I'm trying to learn a lot of things through to make my resume more beefy and to make myself more marketable...What do you all suggest? Thanks, b-jazzy -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: new install - command prompt doesn't work
have you changed the directory to the mysql/bin directory? normally when you get this error you're not in the correct directory. if mysql is in C:\mysql you'll need to be in the c:\mysql\bin and then excecute C:\mysql HTH Jeff Betta Jazzy Brown [EMAIL PROTECTED]To: Paul DuBois [EMAIL PROTECTED], Betta Jazzy Brown [EMAIL PROTECTED], ybrown.com [EMAIL PROTECTED] cc: 12/13/2003 09:48 Subject: Re: new install - command prompt doesn't work AM i have done that C:\mysql but it will say 'mysql' is not recognized as an internal or external command, operable program or batch file there must be something else i'm missing... Paul DuBois [EMAIL PROTECTED] wrote: At 18:21 -0800 12/12/03, Betta Jazzy Brown wrote: I have installed MySql on my PC and I was trying to run the program by going to the command prompt typing: C:\net start mysql it says The MySql service has started successfully. the problem is that the command prompt doesn't read mysql but still reads C:\ What is the problem here and how can I correct it... There is no problem. The server has started successfully. But you need to *connect* to the server using a client program. Try running mysql from the C prompt, for example: C:\ mysql i have installed MySql Database Server Standard Clients (4.0) and MySql Control Center and I'm using Win2K OS... -- As well...what specifically can I use MySql for? I have a website that I'm trying to learn a lot of things through to make my resume more beefy and to make myself more marketable...What do you all suggest? Thanks, b-jazzy -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: new install - command prompt doesn't work
KEWL...that worked... one more question... to put it into the system's path, do i just type at the command prompt C:\put c:\mysql\bin ??? if not...how do i do that??? Gerald R. Jensen [EMAIL PROTECTED] wrote: Try c:\mysql\bin\mysql.exe If that works, then put c:\mysql\bin the system's path. - Original Message - From: Betta Jazzy Brown To: Paul DuBois ; Betta Jazzy Brown ; Sent: Saturday, December 13, 2003 8:48 AM Subject: Re: new install - command prompt doesn't work i have done that C:\mysql but it will say 'mysql' is not recognized as an internal or external command, operable program or batch file there must be something else i'm missing... Paul DuBois wrote: At 18:21 -0800 12/12/03, Betta Jazzy Brown wrote: I have installed MySql on my PC and I was trying to run the program by going to the command prompt typing: C:\net start mysql it says The MySql service has started successfully. the problem is that the command prompt doesn't read mysql but still reads C:\ What is the problem here and how can I correct it... There is no problem. The server has started successfully. But you need to *connect* to the server using a client program. Try running mysql from the C prompt, for example: C:\ mysql i have installed MySql Database Server Standard Clients (4.0) and MySql Control Center and I'm using Win2K OS... -- As well...what specifically can I use MySql for? I have a website that I'm trying to learn a lot of things through to make my resume more beefy and to make myself more marketable...What do you all suggest? Thanks, b-jazzy -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/
Re: new install - command prompt doesn't work
nevermind...i figured it out :oD thanks for all the help... MANY MANY MANY more questions to come -b-jazzy Betta Jazzy Brown [EMAIL PROTECTED] wrote: KEWL...that worked... one more question... to put it into the system's path, do i just type at the command prompt C:\put c:\mysql\bin ??? if not...how do i do that??? Gerald R. Jensen wrote: Try c:\mysql\bin\mysql.exe If that works, then put c:\mysql\bin the system's path. - Original Message - From: Betta Jazzy Brown To: Paul DuBois ; Betta Jazzy Brown ; Sent: Saturday, December 13, 2003 8:48 AM Subject: Re: new install - command prompt doesn't work i have done that C:\mysql but it will say 'mysql' is not recognized as an internal or external command, operable program or batch file there must be something else i'm missing... Paul DuBois wrote: At 18:21 -0800 12/12/03, Betta Jazzy Brown wrote: I have installed MySql on my PC and I was trying to run the program by going to the command prompt typing: C:\net start mysql it says The MySql service has started successfully. the problem is that the command prompt doesn't read mysql but still reads C:\ What is the problem here and how can I correct it... There is no problem. The server has started successfully. But you need to *connect* to the server using a client program. Try running mysql from the C prompt, for example: C:\ mysql i have installed MySql Database Server Standard Clients (4.0) and MySql Control Center and I'm using Win2K OS... -- As well...what specifically can I use MySql for? I have a website that I'm trying to learn a lot of things through to make my resume more beefy and to make myself more marketable...What do you all suggest? Thanks, b-jazzy -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/
Re: new install - command prompt doesn't work
At 6:48 -0800 12/13/03, Betta Jazzy Brown wrote: i have done that C:\mysql but it will say 'mysql' is not recognized as an internal or external command, operable program or batch file there must be something else i'm missing... This is not a MySQL issue. It's a PATH issue. Either set your PATH to include the directory where the MySQL programs are located (likely C:\mysql\bin, though that depends on your installation), or invoke mysql from within that directory, or invoke mysql using its full pathname. Setting your PATH is the best option, because then you can invoke MySQL programs from within any directory and the command interpreter will find them. Paul DuBois [EMAIL PROTECTED] wrote: At 18:21 -0800 12/12/03, Betta Jazzy Brown wrote: I have installed MySql on my PC and I was trying to run the program by going to the command prompt typing: C:\net start mysql it says The MySql service has started successfully. the problem is that the command prompt doesn't read mysql but still reads C:\ What is the problem here and how can I correct it... There is no problem. The server has started successfully. But you need to *connect* to the server using a client program. Try running mysql from the C prompt, for example: C:\ mysql i have installed MySql Database Server Standard Clients (4.0) and MySql Control Center and I'm using Win2K OS... -- As well...what specifically can I use MySql for? I have a website that I'm trying to learn a lot of things through to make my resume more beefy and to make myself more marketable...What do you all suggest? Thanks, b-jazzy -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: storing .tar files in mysql
This page has sample article/code how to store any type/size of file in mysql.. Depending on the appliation it could be a good idea (such as revision control or something) http://php.dreamwerx.net/forums/viewtopic.php?t=6 On Fri, 12 Dec 2003 [EMAIL PROTECTED] wrote: I am working with a project on sourceforge http://leopard.sourceforge.net and this is one of the package management stratagies we are thinking about trying. As I said I have almost no experience with mysql so I open to any and all suggestions. Very good points being made about the size of the rows. Thanks for the quick responses :-) Jake Walters Can I ask why? Why not define a char(50) (or whatever size) with the relative or complete path to the .tar file? Storing it in your database would create huge row sizes. Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra --- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, December 12, 2003 3:55 PM To: [EMAIL PROTECTED] Subject: storing .tar files in mysql Hi all, I am new to mysql and I was wondering if someone could point me in the right direction on how to store .tar and .tar.gz (bzip2) files inside a mysql database. I have googled to try and find some help there but most of the hits come back with binary image files. I have gone thru the mysql tutorial and I can create the database and tables, but I can't seem to insert the .tar file properly...Any pointers would be appreicated... Thanks, Jake -- 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 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: storing .tar files in mysql
I'd agree with chris. I've got a ton of data/files in mysql for years now and no problems... The thruput in/out is increadible if you implement the storage handler correctly. Plus it gives you certain advantages such as security/scalability/etc... With storing the files on disk, the files need to be on, or be accessable by the webserver directly via filesystems... This way you need to connect to a mysql server (ideally a different box) using a username/password.. It's a debate that will go on forever ;) On Sat, 13 Dec 2003, Sime wrote: Chris Nolan wrote: Personally, I would have to disagree. I have just completed a year-long uni project whereby we built some software that stored massive images in InnoDB tables. We found that the performance was (at very worst) comparable to the alternative method. Additionally, this method is better on many levels. You can have a sealed server and you protect yourself from users who like playing with things (like filenames). On Sat, 2003-12-13 at 08:13, Neil Watson wrote: I believe the data type you are looking for is blob. However, you would be better off using your row to point to a file located on the hard drive instead of actually in the database. I can't recall the technical details but, your performance will be much better that way. I'd agree with Neil. I've never believed in that, surely it's just causing overhead for MySQL. Surely far better to have the files managed by the file system? That's what it's there for. -- 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: storing .tar files in mysql
16MB? you mean the max packet per query limit? If your storing data in huge/large blob then you are making a big mistake in my opinion and taking a huge performance hit... I've got files over 1GB in size in mysql now.. they went in and out at almost filesystem speed... On Sun, 14 Dec 2003, Chris Nolan wrote: Forgot something in my other reply. With the NAS - what's to say that MySQL's retrieval and network protocol is not more efficient than whatever is running on your NAS boxes? Conversely, MySQL's current 16 MB per transfer limitation may very well not allow it to act in this role at all. Ah, the wonders of open discussion! Best regards, Chris On Sat, 2003-12-13 at 23:33, Joshua Thomas wrote: you could very well do that, and frankly that is how alot of websites work. Yep, including one I run. That site has to generate img and a href links for visitors, and it seems far easier to return /pics/imagefoo.jpg then the image itself and decide how to embed that into the page. But storing the actual binary file, weither it is a .jpg or .tar, allows your application/website/whatever to be independent of your files location on the server. Is this really such a problem? When I migrated the above-mentioned website, I had some minor path issues which resolved with symbolic links. If your path for images changes, make a symlink to simulate where it used to be; or use an UPDATE statement to change the path prefix on the images in the database. If you really want to be clever, use a single-table row for the prefix (/home/foo/myimages) and another table for the actually names (foo.jpg) and just update the prefix when location changes. This way, when you select a specific row, you will have the binary... regardless of path locations. It also makes data organization cleaner as well. If you decide you don't need a binary anymore and delete it from the database, it will be gone. A cron job which does a SELECT imagename FROM imagetable, then compares to the directory, and removes the non-exisiting images, would also work. Or you could write a database trigger to call out and delete the image when the row was deleted. I do admit it's less nice than being able to just drop the image from the table, but you also lose the ability to manipulate the content on the filesystem level. For example, in your .tar example, how are you going to view the contents of one of those .tar files? You'll have to SELECT it out of the database, write it somewhere, then view it; if you want to update it, you have to SELECT it out, edit it, and UPDATE the table... where as just storing the path will not, which can get pretty ugly. I hope that helps Works like a charm for us. The downsides I see to storing in the database are: * More overhead reading and writing from the database * Much much larger database sizes. For me it's easy to backup and restore my database. If my 2GB+ of images were IN the database, well, that would be a different story. * I can schedule backups of the database, and filesystem content, at different times; this reduces the impact if something goes wrong during a backup. Of course, I do see the advantages of embedding the images, tar files, etc into the database; I just think it has enough issues of it's own that I would caution against doing it. Here's another idea to chew on: My full-time employer runs over a thousand sites for newspapers across the country. We have image names embedded into the database, not the data. Why? Well, we store images on NAS (Network Attached Storage) devices. We have several of them, and use custom scripts to keep the content on them the same. When we have a failure on one, we simply update a piece of code which defines which server to read from. Now, if we put those images into the database, we'd have a few issue: * Our database size would grow far, far beyond the current size, which is over 60GB. * Restores to the database would be much slower, and in our case, if we have a failure, we'd rather get everything up fast and then fix images then wait longer and get both. Time == money to our customers. * Instead of returning the filename to the application software (ASP and CF in our case), you'd return the whole image, and that's going to be a huge drain on server CPU. We'd need something like 2x the CPU power we have now, with many more high-speed disks. (and 15K RPM isn't cheap!) * Replication from the publisher to the subscribers would be much more intensive and require more bandwith and CPU power, again driving our hardware needs up. You could argue that we'd loose the need for the NASes. I haven't done a comparison on how the pricing would work. That's my .02$ and a then some. Joshua Thomas dan Joshua Thomas wrote: Can I ask why? Why not
RE: storing .tar files in mysql
True initially... What I've done is use a java appserver frontend (orion) that's a caching server.. It gets the request, checks if it has the image in it's memory cache, if so serves it, otherwise goes to the backend and gets it, stores in memory cache, serves it.. Very fast and aleviates alot of redundant queries.. You can also set an expire time, etc on the content.. I've seen a PHP implementation of this aswell storing the files in /tmp and checking their timestamp to see when to refresh... You could perhaps use something like squid or something to cache/proxy images aswell.. never tried it though.. good luck.. On Sat, 13 Dec 2003, Joshua Thomas wrote: If we put everything into the database, then I have each call for an image go to the database (unless there is a better method?), so I have several database calls and several HTTP calls for each primary page. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: new install - command prompt doesn't work
ok...i set the path properly... in the command line, i did this: C:\C:\mysql\bin;C:\WINNT;C:\WINNT\COMMAND then, i am able to just type in mysql and it will begin... BUT...after i close the command prompt, once i reopen it, and type in mysql, it goes and says that mysql is not a command or recognizable... is there something that i'm still missing? Paul DuBois [EMAIL PROTECTED] wrote: At 6:48 -0800 12/13/03, Betta Jazzy Brown wrote: i have done that C:\mysql but it will say 'mysql' is not recognized as an internal or external command, operable program or batch file there must be something else i'm missing... This is not a MySQL issue. It's a PATH issue. Either set your PATH to include the directory where the MySQL programs are located (likely C:\mysql\bin, though that depends on your installation), or invoke mysql from within that directory, or invoke mysql using its full pathname. Setting your PATH is the best option, because then you can invoke MySQL programs from within any directory and the command interpreter will find them. Paul DuBois wrote: At 18:21 -0800 12/12/03, Betta Jazzy Brown wrote: I have installed MySql on my PC and I was trying to run the program by going to the command prompt typing: C:\net start mysql it says The MySql service has started successfully. the problem is that the command prompt doesn't read mysql but still reads C:\ What is the problem here and how can I correct it... There is no problem. The server has started successfully. But you need to *connect* to the server using a client program. Try running mysql from the C prompt, for example: C:\ mysql i have installed MySql Database Server Standard Clients (4.0) and MySql Control Center and I'm using Win2K OS... -- As well...what specifically can I use MySql for? I have a website that I'm trying to learn a lot of things through to make my resume more beefy and to make myself more marketable...What do you all suggest? Thanks, b-jazzy -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/
RE: new install - command prompt doesn't work
If you are using Windows 2000, do the following to set the path. If you have Windows NT, the steps are similar, but slightly different after the System icon part (I can't remember exactly). START - Settings - Control Panel - System icon - Advanced tab - Environment Variables button Then, in the System Variables section, double-click on the Path entry. A little dialog should appear so you can edit the Path entry. In the Variable Value field, add the following (the semicolon separate each individual path, so you need it): ;c:\mysql\bin Now click on the OK button in each of those dialogs to save the info. Now you shouldn't have a problem the next time you open a command prompt window and type in 'mysql' to run the mysql client application. Bob Loeffler :) -Original Message- From: Betta Jazzy Brown [mailto:[EMAIL PROTECTED] Sent: Saturday, December 13, 2003 12:10 PM To: [EMAIL PROTECTED] Subject: Re: new install - command prompt doesn't work ok...i set the path properly... in the command line, i did this: C:\C:\mysql\bin;C:\WINNT;C:\WINNT\COMMAND then, i am able to just type in mysql and it will begin... BUT...after i close the command prompt, once i reopen it, and type in mysql, it goes and says that mysql is not a command or recognizable... is there something that i'm still missing? Paul DuBois [EMAIL PROTECTED] wrote: At 6:48 -0800 12/13/03, Betta Jazzy Brown wrote: i have done that C:\mysql but it will say 'mysql' is not recognized as an internal or external command, operable program or batch file there must be something else i'm missing... This is not a MySQL issue. It's a PATH issue. Either set your PATH to include the directory where the MySQL programs are located (likely C:\mysql\bin, though that depends on your installation), or invoke mysql from within that directory, or invoke mysql using its full pathname. Setting your PATH is the best option, because then you can invoke MySQL programs from within any directory and the command interpreter will find them. Paul DuBois wrote: At 18:21 -0800 12/12/03, Betta Jazzy Brown wrote: I have installed MySql on my PC and I was trying to run the program by going to the command prompt typing: C:\net start mysql it says The MySql service has started successfully. the problem is that the command prompt doesn't read mysql but still reads C:\ What is the problem here and how can I correct it... There is no problem. The server has started successfully. But you need to *connect* to the server using a client program. Try running mysql from the C prompt, for example: C:\ mysql i have installed MySql Database Server Standard Clients (4.0) and MySql Control Center and I'm using Win2K OS... -- As well...what specifically can I use MySql for? I have a website that I'm trying to learn a lot of things through to make my resume more beefy and to make myself more marketable...What do you all suggest? Thanks, b-jazzy -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: new install - command prompt doesn't work
Why don't you check the Help utility for your particular operatiing system? This is not a MySQL issue, and there are several ways you can accomplish it depending on which version of the Microsoft Windows O/S you have. - Original Message - From: Betta Jazzy Brown [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, December 13, 2003 1:09 PM Subject: Re: new install - command prompt doesn't work ok...i set the path properly... in the command line, i did this: C:\C:\mysql\bin;C:\WINNT;C:\WINNT\COMMAND then, i am able to just type in mysql and it will begin... BUT...after i close the command prompt, once i reopen it, and type in mysql, it goes and says that mysql is not a command or recognizable... is there something that i'm still missing? Paul DuBois [EMAIL PROTECTED] wrote: At 6:48 -0800 12/13/03, Betta Jazzy Brown wrote: i have done that C:\mysql but it will say 'mysql' is not recognized as an internal or external command, operable program or batch file there must be something else i'm missing... This is not a MySQL issue. It's a PATH issue. Either set your PATH to include the directory where the MySQL programs are located (likely C:\mysql\bin, though that depends on your installation), or invoke mysql from within that directory, or invoke mysql using its full pathname. Setting your PATH is the best option, because then you can invoke MySQL programs from within any directory and the command interpreter will find them. Paul DuBois wrote: At 18:21 -0800 12/12/03, Betta Jazzy Brown wrote: I have installed MySql on my PC and I was trying to run the program by going to the command prompt typing: C:\net start mysql it says The MySql service has started successfully. the problem is that the command prompt doesn't read mysql but still reads C:\ What is the problem here and how can I correct it... There is no problem. The server has started successfully. But you need to *connect* to the server using a client program. Try running mysql from the C prompt, for example: C:\ mysql i have installed MySql Database Server Standard Clients (4.0) and MySql Control Center and I'm using Win2K OS... -- As well...what specifically can I use MySql for? I have a website that I'm trying to learn a lot of things through to make my resume more beefy and to make myself more marketable...What do you all suggest? Thanks, b-jazzy -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Questions about MySQL implementation
Hi, On Sat, 13 Dec 2003, Chris Nolan wrote: 1. We all know that InnoDB can be backed up hot (by various means). I know that there are a few MS SQL Server (ick) and DB2 lovers in the group I'll be meeting with this week. I also know that these two databases do a form of online backup. Given that they are not multiversioned, how on earth do they actually provide this functionality? I guess this ties in with how they implement the READ REPEATABLE isolatation level. Any comments on implementation, performance and other info would be gladly received! I believe they handle it by doing a dirty read first of the database, that is while other people are writing to the files they just copy it. Then they use the undo/redo logs in order to correct it, just as if a crash occured. I could be wrong here, I am not an expert on those databases. 2. I've been told on good authority (by persons on this fine list) that Sybase and PostgreSQL (and, from personal experience, SQLBase) support ROLLBACK of DDL statements such as DROP TABLE, ALTER TABLE, RENAME TABLE etc. From what I can gather, neither BDB nor InnoDB do this. Does anyone know what sort of technical challenges making the above statements undoable involve over and above INSERT, DELETE and UPDATE statements? Would this functionality be something that MySQL AB / Innobase Oy would be interested in developing should it be sponsored? First it isn't that common to want to undo these. I would ask yourself first, why do you want to do this? Generally these commands are not run on production servers. The only time that I could see them being useful is if you are running an upgrade of your schema to switch to a newer version of some application you wrote. In this case you would want to take a backup before this was done anyways. Of course if you are really really interested, you should contact [EMAIL PROTECTED] to get more details about cost and effort. I suspect that it is more than just a little effort, but I could be entirely mistaken. 3. At the moment, the MySQL API seems to have a size limit of 16 MB for data sent over the wire (I have seen that the MySQL 4.1 libraries allow for sending information in chunks along with prepared statements). I take it the best method of inserting greater amounts of data into a column would be by first writing the file somewhere on the database server and using LOAD DATA? Any comments on this of any type from the learned populace of MySQL users? :-) No. MySQL 3.23 has the 16M limit for max_allowed_packet. In MySQL 4.0 it can be setup to around 1G of size. Keep in mind that moving around 1G rows however will take a lot of overhead in the form of memory (both on the client and the server). 4. In a DB server that has 2 physical disks running MySQL 4.1.1 Alpha and utilising the multiple table space feature of InnoDB, what distribution of files (ibdata, log files, individual table space files) is likely to result in the best performace? Any insights of similar type for using MyISAM tables? Obviously splitting up usage across many disks is a good thing (if you don't have a RAID system which effectively does this for you...) Where you put things depends on whether you are going for more performance or for more reliablity. For reliability the Binary Update logs should generally go on their own disk. That way you can still recover your data if the main hdd stops working. This is true with both MyISAM and InnoDB. For performance with InnoDB the InnoDB log files should be moved to a seperate disk if possible as well. Then if you still have disks left over you can begin moving individual tables to seperate disks as well. With only two regular hdds it is a tough decision. For reliability you should keep the binary update logs and the data on seperate hdds. However then you don't really gain much performance, so it is tempting to move the InnoDB logs or spread the tables across both drives. For performance the InnoDB logs ideally will be on a different harddrive especially if innodb_flush_logs_at_tx_commit is set to 1. Hope that helps some! Regards, Harrison -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't read dir after my rsync goof
Hi -- 24 hours ago I did a 'mysqlhotcopy' of a working database, then inadverntently did an 'rsync' copy of that copy, _over_ the working database tables. Today, mysql is saying Can't read dir ./thedatabase/' when I start up a conversation. :( Meanwhile, Perl scripts have been working on that database and giving the appearance that they've been succeeding -- but it looks to me like no transaction has been recorded to the tables on disk, in the past 24 hours. Two questions, then: - How (where?) do I address mysql's inability to read dir for these database tables? (Looks like it's trying to find them in the place I had mysqlhotcopy place its copies, instead of in the place where they really are, /var/lib/mysqld/thedatabase/.) - Is there (he asked, hopefully) a 'buffer' mysql has been keeping during the last 24 hours, that might be holding transactions that were not written to the tables on disk, but still could be written to those tables now...? Thanks kindly, -- -- Jeff -- http://www.wellnow.com There's nothing left in the world to prove. All that's worth doing is to love one another, using whatever means are available to serve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
(Oh) Can't read dir because root owns it (duh)
On Sat, Dec 13, 2003 at 06:39:08PM -0500, Jeff Gordon wrote: 24 hours ago I did a 'mysqlhotcopy' of a working database, then inadverntently did an 'rsync' copy of that copy, _over_ the working database tables. Today, mysql is saying Can't read dir ./thedatabase/' when I start up a conversation. :( ...and the reason is because: 'root' owns the dir in question (though not the files it contains). Changing that now Leaving the one question: - Is there (he asked, hopefully) a 'buffer' mysql has been keeping during the last 24 hours, that might be holding transactions that were not written to the tables on disk, but still could be written to those tables now...? Thanks kindly, -- -- Jeff -- http://www.wellnow.com There's nothing left in the world to prove. All that's worth doing is to love one another, using whatever means are available to serve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie question re: openoffice file stored in mysql via web
I have an openoffice document that is a contract. It has several fields that need to be filled at the time of signing. I want to have a form that is accessible form a webpage, that will automatically put the filled data into the contract and then save it in the mysql database under the contract signers record, so that we have an electronic copy of the contract and can do searches against it. I will be keeping alot more information in the various tables also, but this is one of the things I want to do. Any guidance, reading, hints, will be very much appreciated. Right now all the tables are MyISAM type. Troy
Viewing the last few records in a table
Hello all, Still a newbie with MySQL, I am running version 4.01 and a Linux box. We are writing information into the tables at a regular rate approx 20 time per hour. with 22 rows of information. What I am trying to work out is how to read the last 22 rows of information that has been written to the Database. Select (count(*)-22) from Database will return me the record number 22 numbers from the very last record. This is great but when I try and incoperate the (count(*)-22) into a query I get responce about grouping. Just wondering if any one else has had a need to return the last few rows in a table that might be able to shed some light on the subject ! regards Matthew Richardson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lost connection to MySQL server during query - pls help
Hi Vanessa, I don't think I saw a reply to this... You can just reconnect to MySQL if you get this error. :-) Trying to send the query a second or third time may also make the client try to reconnect again. Hope that helps. Matt - Original Message - From: Kiky Sent: Friday, December 05, 2003 12:14 AM Subject: Lost connection to MySQL server during query - pls help Hi Guys, I have a problem with Error 2013 - Lost connection to MySQL server during query I'm using mysql 3.23.41 under Linux Mandrake, and mysql 4.0.15 under Win XP Pro. They turn out to have the same error. My problem is: I have a Java program which is actually a thread to send emails, so it keeps running all the time. When it's time to send emails, it will access MySQL database. Based on my wait_timeout in mysql, I think the connection closes after 8 hrs. If it's the time to send emails, and mysql has already closed the connection, the first connection attemp will throw an error Error 2013 - Lost connection to MySQL server during query * Is there a way that I can avoid this error? Or to make the connection keeps open all the the time? Any help / suggestions will be very much appreciated :) Thank you in advance. Rgds, Vanessa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie question
Hi Peter, You can probably safely have at least 1000-2000 tables in a single database. Hope that helps. Matt - Original Message - From: peter Sent: Friday, November 28, 2003 12:03 PM Subject: newbie question Hi I am a webdesigner/hosting reseller my question is this: I am hosting various different CMS attached to mysql databases and hope to host more in the future How many different tables can I safely store in the same database? I am currently storing tables from three different CMS on the same database with different prefix's Can I keep adding more? is there a significant performance issue with multiple sites using the same database? I'm not really that up to speed on mysql or databases in general so.. any thoughts? thanks Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary tables rights
Hi Alejandro, Yeah, this issue has come up before. It's not possible to GRANT DROP on temp tables without GRANTing DROP on the whole database. The temp tables will be dropped when the client disconnects you know, right? And if you want to empty the table or reuse it, you should be able to TRUNCATE it, I think. And actually, if you can TRUNCATE the other tables (if the DELETE privilege allows it), isn't that just as bad as DROPping them? :-) Matt - Original Message - From: adburne To: [EMAIL PROTECTED] Sent: Friday, December 12, 2003 11:31 AM Subject: Temporary tables rights Hi, I'm granting users to use temporary tables as: GRANT CREATE TEMPORARY TABLES ON db1.* TO user1; and having grants on many other tables as: GRANT SELECT, INSERT, UPDATE, DELETE ON db1.table1 TO user1; GRANT SELECT, INSERT, UPDATE, DELETE ON db1.table2 TO user1; but how make this work CREATE TEMPORARY TABLE tmp1 AS SELECT * FROM table1; ok SELECT * FROM tmp1; Error: select command denied to user: [EMAIL PROTECTED] for table tmp1 also: DROP TABLE tmp1; Error: drop command denied to user: [EMAIL PROTECTED] for table tmp1 I don't want grant select and drop global privilege over db1 but I want use temporary tables, there is a way to do this work? Alejandro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE .. ORDER BY
Hi Chris, I don't know exactly what you mean by ALTER being as good as OPTIMIZE... But yes, an ALTER that recreates the data file (as ALTER ... ORDER BY does) will defragment the data file too. However, OPTIMIZE also analyzes the key distribution (I don't know if it's remembered after an ALTER or not...) and sorts the index pages (but that should be done pretty well I think when the index is rebuilt during ALTER). To summarize, if you just want to reclaim deleted rows, ALTER ... ORDER BY is enough. If you want to make sure everything else is done, use OPTIMIZE too afterwards. :-) Hope that helps. Matt - Original Message - From: Chris Elsworth Sent: Wednesday, December 10, 2003 12:49 PM Subject: ALTER TABLE .. ORDER BY Hello, Just a quickie. Does anyone know if issueing an ALTER TABLE t ORDER BY c is as good as an OPTIMIZE TABLE if I know the order I'll mostly be sorting with? Does the ALTER TABLE operation reclaimed deleted rows, for example? Or should I OPTIMIZE and then ALTER? Cheers :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Viewing the last few records in a table
AFAIK databases like mysql usually dont (and cant) guarantee that they will maintain the order of rows the same way they were inserted - it's that whole 'relational' thing, methinks. the official way would be to simply add a timestamp field (which gets filled automagically every time you insert something into that table), and order by that. hacking up a little perl-script that simply iterates over all rows, and inserts a row number would be trivial, and should usually work, but mysql is not obligated to maintain that order (i think) cheers, M. Matthew Richardson wrote: Hello all, Still a newbie with MySQL, I am running version 4.01 and a Linux box. We are writing information into the tables at a regular rate approx 20 time per hour. with 22 rows of information. What I am trying to work out is how to read the last 22 rows of information that has been written to the Database. Select (count(*)-22) from Database will return me the record number 22 numbers from the very last record. This is great but when I try and incoperate the (count(*)-22) into a query I get responce about grouping. Just wondering if any one else has had a need to return the last few rows in a table that might be able to shed some light on the subject ! regards Matthew Richardson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
this newbies project :-)
Hey everyone :-) This is my first post to the list and I would like to thank everyone for this great resource. I'm relatively new to mySQL and a novice at PHP, but here is a description of what I am trying to do. My objective is to create a database that will provide variables for a series of forms. These variables would be conditional based on a few series of simple drop down menu based questions. Here is a particular sequence; 1st) What era of military is this roster for? a) Modern b) WWI/WWII Based on a)'s choice they would get this list of variables to choose from; Spanish Marines: Japanese Military: US Airforce: US Army: US Marines: US Navy: Based on b)'s choice they would get this list of variables to choose from; WW2 British Army: WW2 G-Kreigsmarine: WW2 G-Luftwaffe: WW2 G-Waffen-SS: WW2 G-Wehrmacht: WW2 Red Army AirForce: WW2 Royal Air Force: WW2 Royal Navy: WW2 US Army AirForce: WW2 US Army: WW2 US Navy: Now for any of these choices I have a list of associated ranks which depending on which choice is made could be as little as 20 but as many as 30 variables to choose from. For example, here is a list of equal world ranks based on the U.S. equivalent of (E-1) or standard enlisted men. ENLISTED PRIVATES (E-1) Private Spanish Marines:Soldado Japanese Military: Nitto Hei US Airforce:Airman Basic US Army:Private (E-1) US Marines: Private US Navy:Seaman Recruit WW2 British Army: Private or Sapper WW2 G-Kreigsmarine: Matrosengefrieter WW2 G-Luftwaffe:Flieger or Gefrieter WW2 G-Waffen-SS:SS-Schutze WW2 G-Wehrmacht:Schutze (after Nov. 1942: Grenadier) WW2 Red Army AirForce: Krasnoarmeyets WW2 Royal Air Force:Aircraftsman 2nd Class WW2 Royal Navy: Ordinary Seaman WW2 US Army AirForce: Private WW2 US Army:Private WW2 US Navy:Seaman Recruit So, as you can see that there is a lot of menu driven choices and this is only one tier of ranks of 25 or 30 that must be created. Plus I have an image directory with all the associated ribbons for each of these ranks on each level. For that though I will refer to the directory where the image resides rather then dragging the speed of the DB down to call on the images. Having never created a database of this size I am looking for some guidance/help on what might be a way to best approach or someway to get the info in by batching a CSV file or something like that. ENLISTED PRIVATES (E-1) Private Spanish Marines: Soldado Japanese Military: Nitto Hei US Airforce: Airman Basic US Army: Private (E-1) US Marines: Private US Navy: Seaman Recruit WW2 British Army: Private or Sapper WW2 G-Kreigsmarine: Matrosengefrieter WW2 G-Luftwaffe: Flieger or Gefrieter WW2 G-Waffen-SS: SS-Schutze WW2 G-Wehrmacht: Schutze (after Nov. 1942: Grenadier) WW2 Red Army AirForce: Krasnoarmeyets WW2 Royal Air Force: Aircraftsman 2nd Class WW2 Royal Navy: Ordinary Seaman WW2 US Army AirForce: Private WW2 US Army: Private WW2 US Navy: Seaman Recruit I have a few resellers accounts and would be willing trade some web-space/bandwidth for some direct help on this. Please email me direct if can give some time to this. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Questions about MySQL implementation
Hi! Thanks for the detailed reply! Regarding the hot backup method that the other guys use, sounds like a dodgy method of doing anything to be honest. It would have to have a fairly decent performance hit... Regarding the rollback of ALTER, DROP and RENAME statements, the main use that I've seen for it is upgrades of custom software. It's handy should an ALTER TABLE fail or similar. That's the sole reason that one developer I know of deploys SQLBase to their clients. Thanks again! Regards, Chris On Sun, 2003-12-14 at 08:00, Harrison Fisk wrote: Hi, On Sat, 13 Dec 2003, Chris Nolan wrote: 1. We all know that InnoDB can be backed up hot (by various means). I know that there are a few MS SQL Server (ick) and DB2 lovers in the group I'll be meeting with this week. I also know that these two databases do a form of online backup. Given that they are not multiversioned, how on earth do they actually provide this functionality? I guess this ties in with how they implement the READ REPEATABLE isolatation level. Any comments on implementation, performance and other info would be gladly received! I believe they handle it by doing a dirty read first of the database, that is while other people are writing to the files they just copy it. Then they use the undo/redo logs in order to correct it, just as if a crash occured. I could be wrong here, I am not an expert on those databases. 2. I've been told on good authority (by persons on this fine list) that Sybase and PostgreSQL (and, from personal experience, SQLBase) support ROLLBACK of DDL statements such as DROP TABLE, ALTER TABLE, RENAME TABLE etc. From what I can gather, neither BDB nor InnoDB do this. Does anyone know what sort of technical challenges making the above statements undoable involve over and above INSERT, DELETE and UPDATE statements? Would this functionality be something that MySQL AB / Innobase Oy would be interested in developing should it be sponsored? First it isn't that common to want to undo these. I would ask yourself first, why do you want to do this? Generally these commands are not run on production servers. The only time that I could see them being useful is if you are running an upgrade of your schema to switch to a newer version of some application you wrote. In this case you would want to take a backup before this was done anyways. Of course if you are really really interested, you should contact [EMAIL PROTECTED] to get more details about cost and effort. I suspect that it is more than just a little effort, but I could be entirely mistaken. 3. At the moment, the MySQL API seems to have a size limit of 16 MB for data sent over the wire (I have seen that the MySQL 4.1 libraries allow for sending information in chunks along with prepared statements). I take it the best method of inserting greater amounts of data into a column would be by first writing the file somewhere on the database server and using LOAD DATA? Any comments on this of any type from the learned populace of MySQL users? :-) No. MySQL 3.23 has the 16M limit for max_allowed_packet. In MySQL 4.0 it can be setup to around 1G of size. Keep in mind that moving around 1G rows however will take a lot of overhead in the form of memory (both on the client and the server). 4. In a DB server that has 2 physical disks running MySQL 4.1.1 Alpha and utilising the multiple table space feature of InnoDB, what distribution of files (ibdata, log files, individual table space files) is likely to result in the best performace? Any insights of similar type for using MyISAM tables? Obviously splitting up usage across many disks is a good thing (if you don't have a RAID system which effectively does this for you...) Where you put things depends on whether you are going for more performance or for more reliablity. For reliability the Binary Update logs should generally go on their own disk. That way you can still recover your data if the main hdd stops working. This is true with both MyISAM and InnoDB. For performance with InnoDB the InnoDB log files should be moved to a seperate disk if possible as well. Then if you still have disks left over you can begin moving individual tables to seperate disks as well. With only two regular hdds it is a tough decision. For reliability you should keep the binary update logs and the data on seperate hdds. However then you don't really gain much performance, so it is tempting to move the InnoDB logs or spread the tables across both drives. For performance the InnoDB logs ideally will be on a different harddrive especially if innodb_flush_logs_at_tx_commit is set to 1. Hope that helps some! Regards, Harrison -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Viewing the last few records in a table
Hi! There are plenty of funky ways to do this. :-) The easiest and fastest way would simply need an AUTO_INCREMENT column on your table. Then, you might be able to do something like this (with MIGHT being the operative word): SELECT * FROM table ORDER BY auto_inc_column DESC LIMIT 22; I've just tested this on a very large table and it gives the required results, but probably in the reverse order to what you want. You could easily reverse the order of rows in whichever language you're accessing the DB with. Hope this helps! Regards, Chris On Sun, 2003-12-14 at 13:06, Moritz von Schweinitz wrote: AFAIK databases like mysql usually dont (and cant) guarantee that they will maintain the order of rows the same way they were inserted - it's that whole 'relational' thing, methinks. the official way would be to simply add a timestamp field (which gets filled automagically every time you insert something into that table), and order by that. hacking up a little perl-script that simply iterates over all rows, and inserts a row number would be trivial, and should usually work, but mysql is not obligated to maintain that order (i think) cheers, M. Matthew Richardson wrote: Hello all, Still a newbie with MySQL, I am running version 4.01 and a Linux box. We are writing information into the tables at a regular rate approx 20 time per hour. with 22 rows of information. What I am trying to work out is how to read the last 22 rows of information that has been written to the Database. Select (count(*)-22) from Database will return me the record number 22 numbers from the very last record. This is great but when I try and incoperate the (count(*)-22) into a query I get responce about grouping. Just wondering if any one else has had a need to return the last few rows in a table that might be able to shed some light on the subject ! regards Matthew Richardson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Questions about MySQL implementation
On Sun, Dec 14, 2003 at 02:08:07PM +1100, Chris Nolan wrote: Hi! Thanks for the detailed reply! Regarding the hot backup method that the other guys use, sounds like a dodgy method of doing anything to be honest. It would have to have a fairly decent performance hit... It's going to have a performance hit, sure. That's a lot of disk I/O to be doing. As for as being dodgy, I don't think so. They know the internals of their systems and have a provden technique for making usable backups. SUre, it's different but that doesn't mean it's bad. Regarding the rollback of ALTER, DROP and RENAME statements, the main use that I've seen for it is upgrades of custom software. It's handy should an ALTER TABLE fail or similar. That's the sole reason that one developer I know of deploys SQLBase to their clients. Yeah, if you've ever accidentally done a DROP TABLE on the wrong database, you know why it's useful to be able to roll 'em back. :-( Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
