Re: how things get messed up
Hello John, About 5 years ago, I was asked to write a php app for my department. The app keeps track of graduate school applicants to my department at the university. The main data elements are the scores each professor gives to each applicant. There are only about 400 applicants each year so even with all the personal data, scores, transcripts, etc for each student, it's not much. for the first 2 years, it was under a meg of data. Well, then the selection committee asked me to add something so that if a student e-mailed the department a document, say a paper he'd written or a photo of himself, or whatever, it could be tacked on to the info they saw about him while grading the applicant. So I said, Well, there is only going to be maybe 10 or 20 of those a year. And even if all 400 applicants submit a PDF of a paper they'd written, it would be only 400 docs. 4,000 after 10 years. Yeah, lets just create a documents table in the database and store them in mysql. For the first 2 years, only 2 students sent in documents to attach to their application. I figured I'd wasted my time. Then the next year, the graduate school changed their web application form to allow students to upload documents. Fine, I said, My worst case scenario has already come true. But, well, this is why you plan for the worst case. Then they started taking letters of recommendation as PDF documents. In fact, they started requiring PDF docs. Each student has 3 to 6 letters of recommendation. All in all, I figure we're at about 100 times as many docs in our database as I originally expected and about 10x my worst case scenario. I should either be fired or shot. Maybe fired *then* shot. Actually, its not as bad as all that. I can pretty easily write a perl script to export the docs to files and access them via a network mounted filesystem. After all, saving myself 5 hours of work 5 years ago is worth what? -- maybe 10hours today? It is amazing how often quick dirty turns out just being dirty in the end. Not sure what the problem is really... What are you running into? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how things get messed up
On Thu, Feb 11, 2010 at 8:56 AM, Martijn Tonies m.ton...@upscene.comwrote: Hello John, About 5 years ago, I was asked to write a php app for my department. The app keeps track of graduate school applicants to my department at the university. The main data elements are the scores each professor gives to each applicant. There are only about 400 applicants each year so even with all the personal data, scores, transcripts, etc for each student, it's not much. for the first 2 years, it was under a meg of data. Well, then the selection committee asked me to add something so that if a student e-mailed the department a document, say a paper he'd written or a photo of himself, or whatever, it could be tacked on to the info they saw about him while grading the applicant. So I said, Well, there is only going to be maybe 10 or 20 of those a year. And even if all 400 applicants submit a PDF of a paper they'd written, it would be only 400 docs. 4,000 after 10 years. Yeah, lets just create a documents table in the database and store them in mysql. For the first 2 years, only 2 students sent in documents to attach to their application. I figured I'd wasted my time. Then the next year, the graduate school changed their web application form to allow students to upload documents. Fine, I said, My worst case scenario has already come true. But, well, this is why you plan for the worst case. Then they started taking letters of recommendation as PDF documents. In fact, they started requiring PDF docs. Each student has 3 to 6 letters of recommendation. All in all, I figure we're at about 100 times as many docs in our database as I originally expected and about 10x my worst case scenario. I should either be fired or shot. Maybe fired *then* shot. Actually, its not as bad as all that. I can pretty easily write a perl script to export the docs to files and access them via a network mounted filesystem. After all, saving myself 5 hours of work 5 years ago is worth what? -- maybe 10hours today? It is amazing how often quick dirty turns out just being dirty in the end. Not sure what the problem is really... What are you running into? I think John is just sharing an experience - a lesson learned if you will. With the same spirit in mind, many projects in my work culture begin with a specification of, Just put up anything so our (internal) users can react to it. Talk about vague. Geesh! However, a senior programmer told me years ago that the life of a programmer is often filled with doing, undoing, and redoing. And not enough appreciation for the work involved. I try to keep that in mind. David
RE: how things get messed up
-Original Message- From: Cui Shijun [mailto:rancp...@gmail.com] Sent: Wednesday, February 10, 2010 10:53 PM To: Ilya Kazakevich Cc: John G. Heim; mysql@lists.mysql.com Subject: Re: how things get messed up Hello Ilya, I think store files in DB has no complicated reasons, just for convenience. For example, I might make files automatically be backup-ed, and no budget for an independent backup solution. Thanks. Cui [JS] Storing BLOBs in a table must slow down the inner workings of the database engine. After all, the tables are really disk files under the covers. Can the database engine read only parts of a file record? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: help me out for this scenario
-Original Message- From: MuraliKrishna [mailto:murali_kris...@arthaoptions.com] Sent: Thursday, February 11, 2010 12:52 AM To: mysql@lists.mysql.com Subject: help me out for this scenario I have a table like as follws Emp_id, first_login , second_login [JS] I think you are asking for trouble doing things this way. If life has taught me anything, it's that someday somebody is going to ask for a third_login. You'd be much better off in the long run using a second table for the login times, even if it seems like overkill. In the second table, store the login number and time for each occurrence for each user: Emp_id, login_number, login_time Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: how things get messed up
-Original Message- From: John G. Heim [mailto:jh...@math.wisc.edu] Sent: Wednesday, February 10, 2010 2:09 PM To: mysql@lists.mysql.com Subject: how things get messed up It is amazing how often quick dirty turns out just being dirty in the end. [JS] Hee-hee... How right you are. I've had the pleasure of writing perfect programs whose logic I was unable to understand a week later. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how things get messed up
Hi Ilya, everybody! Ilya Kazakevich wrote: [[...]] IMHO: Storing files in DB is probably bad idea. Here are some advantages of storing files on filesystem: [[...]] We could discuss them individually, but I agree several of your points are valid. The remaining question is which importance you assign to them. And there is only one disadvantage: consistency. When deleting user -- all her data could be deleted automatically using foreign keys, but you have to do it in your app if files are stored externally. Only one ...: consistency: I contradict your use of only, very strongly. Probably that is because I value consistency extremely high, definitely higher than ease of programming and performance. If you keep related data in the same storage mechanism (say, the database), then the chances of dealing with them in a consistent manner improve greatly. This shows when you think about access privileges, referential integrity, export/import, backup/restore, migration to a new system, replication, ... By keeping your data in related tables (nobody forces you to have the images in the person table, using the same ID in two tables and defining referential integrity constraints is sufficient), you have a much better chance to get the application and the handling correct. If your data are valuable (and applicant data are - just consider the costs of a law suit when a rejected applicant goes to court), that correctness is an essential requirement. [[...]] Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how things get messed up
On 2/10/10, Ilya Kazakevich wrote: There was a joke in russian PHP club: why strore images in database? Are you going to have a full text search on them? Yes. That is what EXIF data is for, isn't it? And considering this is about PDFs any inability of a database engine to do a full text search on them surely is a limitation of that database, not a conceptual disqualification of storing binary data in a database. Jochem -- Jochem van Dieten http://jochem.vandieten.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how things get messed up
I think store files in DB has no complicated reasons, just for convenience. For example, I might make files automatically be backup-ed, and no budget for an independent backup solution. Thanks. Cui [JS] Storing BLOBs in a table must slow down the inner workings of the database engine. After all, the tables are really disk files under the covers. Can the database engine read only parts of a file record? Jerry Schwartz Well, there's where things go wrong -- first of all, a database engine can do -anything- it likes with the actual data. For example, the Firebird DBMS stores (longer) Blob data not right there in the record, so whenever you don't request the blob (that is, not selecting it), it ignores it completely and it can go through the file quickly. If BLOB data makes the DBMS slowish by default, it's an implementation issue that needs to be fixed. Conceptually, there's much to say -for- storing binary data in database tables, see the post by Joerg for example. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Help with the World.sql sample database
Could any one lead me to a true link where I can download the world.sql sample database? Thanks.
Re: Help with the World.sql sample database
On 2/11/2010 11:47 AM, kebede teferi wrote: Could any one lead me to a true link where I can download the world.sql sample database? Thanks. http://dev.mysql.com/doc/index-other.html Carlos Proal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with the World.sql sample database
On Feb 11, 2010, at 11:47 AM, kebede teferi wrote: Could any one lead me to a true link where I can download the world.sql sample database? Thanks. Go to: http://dev.mysql.com/doc/ Click on the Other Docs tab to get to: http://dev.mysql.com/doc/index-other.html You'll see an entry for the world database. Direct links are: http://downloads.mysql.com/docs/world.sql.gz http://downloads.mysql.com/docs/world.sql.zip -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: logging of BAD queries
Andy, On Tue, Feb 9, 2010 at 10:27 AM, andy knasinski a...@nrgsoft.com wrote: I've used the general and slow query log in the past, but I am trying to track down some queries from a compiled app that never seem to be hitting the DB server. My guess is that the SQL syntax is bad and never get executed, but I don't see any related queries in the general query log. Does the general log include invalid SQL? Yes, it does. If you're not able to debug the application itself, I would sniff the TCP traffic. Use wireshark or mk-query-digest. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: how things get messed up
[JS] Storing BLOBs in a table must slow down the inner workings of the database engine. After all, the tables are really disk files under the covers. Can the database engine read only parts of a file record? Jerry Schwartz Well, there's where things go wrong -- first of all, a database engine can do -anything- it likes with the actual data. For example, the Firebird DBMS stores (longer) Blob data not right there in the record, so whenever you don't request the blob (that is, not selecting it), it ignores it completely and it can go through the file quickly. [JS] That's an interesting idea that hadn't occurred to me. Essentially, it's creating a dependent table behind your back. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how things get messed up
On Thu, Feb 11, 2010 at 5:24 PM, Jerry Schwartz jschwa...@the-infoshop.comwrote: [JS] Storing BLOBs in a table must slow down the inner workings of the database engine. After all, the tables are really disk files under the covers. Can the database engine read only parts of a file record? They recently invented this funky little thing called random access. No more need to spool your tape forward for ten minutes before finding the data you want. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: how things get messed up
[JS] Storing BLOBs in a table must slow down the inner workings of the database engine. After all, the tables are really disk files under the covers. Can the database engine read only parts of a file record? Jerry Schwartz Well, there's where things go wrong -- first of all, a database engine can do -anything- it likes with the actual data. For example, the Firebird DBMS stores (longer) Blob data not right there in the record, so whenever you don't request the blob (that is, not selecting it), it ignores it completely and it can go through the file quickly. [JS] That's an interesting idea that hadn't occurred to me. Essentially, it's creating a dependent table behind your back. Jerry Schwartz Firebird uses 1 or multiple files per database, not per table, this file has a special blob area, so to speak, and the records include a blob ID. When a client/stored routine selects a blob that isn't available in the record data (stored in between), it then goes to the area where blobs are stored and gets it from there. Works quite well. As I said, a DBMS can do anything it likes with it's data as long as it returns it correctly. I'm surprised there are DBMSses that don't do this, Firebird comes from InterBase comes from Groton Database Systems which was the first with BLOBs and they had the idea right, so it seems ;-) Enjoy the read: http://www.ibphoenix.com/main.nfs?a=ibphoenixpage=ibp_blob_history With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how things get messed up
On Fri, Feb 12, 2010 at 8:05 AM, Martijn Tonies m.ton...@upscene.comwrote: Firebird uses 1 or multiple files per database, not per table, this file has a special blob area, so to speak, and the records include a blob ID. When a client/stored routine selects a blob that isn't available in the record data (stored in between), it then goes to the area where blobs are stored and gets it from there. Works quite well. As I said, a DBMS can do anything it likes with it's data as long as it returns it correctly. As a matter of interest, FaceBook has basically written their own blob engine for the storage of userpics. They basically append every new picture to the end of a huge binary file (probably a raw device, really), and simply put the start and end byte of the picture in an index. Every webserver (you can imagine they have a few) that hasn't got a requested image in it's local cache yet, then simply does a seek() to the start byte and reads the necessary number of bytes to fetch the image. Highly efficient, and very simple. Also of interest is that they never actually clear images from the binary storage - it would be too slow to reclaim the free space, and it's faster and cheaper for them to just keep adding storage. The main performance benefit of this over files-on-disk might well be that you don't have the overhead of a filesystem, and thus no constant open/close operations - the image serving daemon keeps the file open at all times. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: how things get messed up
On Fri, Feb 12, 2010 at 8:27 AM, Martijn Tonies m.ton...@upscene.comwrote: Sounds logical, what's also nice to see, is that even though people here tend to say don't put binaries in the database, apparently Facebook thought it would be nice to do so (for all sorts of reasons) and even took the time to write their own blob storage mechanism ;-) The whole point is that they *aren't*' putting blobs in their database - that has way too much overhead. They're using a custom service that does nothing but read from byte X to byte Y. No concepts of tablespaces, integrity, indices, whatever. The only thing they store in their database, is the start- and end-byte of each image. I doubt they even took it as far as to write a plugin engine - that would again bring too much overhead. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
