Re: 2 servers 1 common data base
hello, you can not simultaneously fix two deamons to a data directory. a mysql daemon always has exclusive access to the data directory. Am 10.06.2010 07:09, schrieb camelia botez: and use the same data bas -- SP data GmbH T 06131 218111 F 06131 218112 E schackenb...@termindoc.de W www.termindoc.de Unser Impressum finden Sie unter http://www.termindoc.de/Impressum.htm Alle Willenserklärungen der SP data GmbH bedürfen zu ihrer Wirksamkeit der Schriftform versehen mit zwei Originalunterschriften. Kommunikation über E-mail Bei der Kommunikation über E-mail ist nicht in jedem Fall auszuschliessen, dass Dritte unbefugt Kenntnis von den versandten Informationen nehmen. Soweit Sie per E-mail mit uns Kontakt aufnehmen, nehmen wir an, dass Ihnen diese Risiken bekannt und Sie dennoch damit einverstanden sind, dass wir Ihnen per E-mail antworten. Anderenfalls bitten wir Sie, uns einen anderen Kommunikationsweg zu benennen. Für viele der Dateien, die Sie von uns erhalten, benötigen Sie zum Betrachten den Acrobat Reader, den Sie hier erhalten können. http://www.adobe.de/products/acrobat/readstep2.html GF: Benedikt Schackenberg und Christian Peiter AG Sitz: Mainz am RheinHandelsregister: HR-B 8608 Umsatzsteuer ID: DE126633578 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 2 servers 1 common data base
Yups. If you want to have two active servers, you'll need to set up master-master replication and give each server it's own datastore. Note that there's a load of caveats if you want to write to both servers, though - read up on the documentation. 2010/6/10 Benedikt Schackenberg schackenb...@termindoc.de hello, you can not simultaneously fix two deamons to a data directory. a mysql daemon always has exclusive access to the data directory. Am 10.06.2010 07:09, schrieb camelia botez: and use the same data bas -- SP data GmbH T 06131 218111 F 06131 218112 E schackenb...@termindoc.de W www.termindoc.de Unser Impressum finden Sie unter http://www.termindoc.de/Impressum.htm Alle Willenserklärungen der SP data GmbH bedürfen zu ihrer Wirksamkeit der Schriftform versehen mit zwei Originalunterschriften. Kommunikation über E-mail Bei der Kommunikation über E-mail ist nicht in jedem Fall auszuschliessen, dass Dritte unbefugt Kenntnis von den versandten Informationen nehmen. Soweit Sie per E-mail mit uns Kontakt aufnehmen, nehmen wir an, dass Ihnen diese Risiken bekannt und Sie dennoch damit einverstanden sind, dass wir Ihnen per E-mail antworten. Anderenfalls bitten wir Sie, uns einen anderen Kommunikationsweg zu benennen. Für viele der Dateien, die Sie von uns erhalten, benötigen Sie zum Betrachten den Acrobat Reader, den Sie hier erhalten können. http://www.adobe.de/products/acrobat/readstep2.html GF: Benedikt Schackenberg und Christian Peiter AG Sitz: Mainz am RheinHandelsregister: HR-B 8608 Umsatzsteuer ID: DE126633578 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- 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 to setup replication - MySQL 5.0.x - Migration and new databases
Am 08.06.10 12:05, schrieb Rob Wultsch: On Mon, Jun 7, 2010 at 11:59 PM, Götz Reinicke - IT-Koordinator goetz.reini...@filmakademie.de wrote: Hi, we do have different LAMP systems and recently I started to put some mysql databases on one, new master server. (RedHat, Fredora, MySQL 4.x - 5.0.xx) MySQL 4.X is EOL. I strongly suggest not using it for new projects, if you have the option. If possible, MySQL 5.1 is recommended, Thanks, I'm aware of that. The 4.x servers are old and should be migrated to the recent redhat EL released mysql 5.0.x. I did this by exporting some databases with mysqldump and importing tham on the new server. Now I'd like to add a slave mysqlserver and so I started to read some docs from the web and manuals from addison-wesley but some questions do remain or occur. What is the best way to copy the databases from the master to the slave? I thought that I can shut down the master and copie the database directory to the slave and than go on with the config, restarting the servers, etc. Doing so, do I have to lock any InnoDB tables or anything else? (May be I missunderstand some docs...) Perhaps I am misunderstanding what you are doing, but shutting down the master instance will make it inaccessible until it is restarted. That would be no problem. Please read http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html . That document has the basics right, other than snapshoting. In terms of getting a snapshot, if you have a innodb only instance* (which is good idea), and can stop ddl commands, you can use mysqldump with the master-data and single-transaction flags in order to take a non-blocking dump suitable for replication use. For MyISAM only instances FLUSH TABLES WITH READ LOCK can be used. The easiest way to make a snapshot is to shut down the master instance and make a copy of the data files. When you restart the master make note of which binary log file it starts to write to. *Other than the mysql schema, of course. So far I only copied a few databases from the different servers to the new master. The second big question is: How to add new databases to the master after sucessfully running a master-slave-setup? Will the new database be copied/created on the slave automatically? Or do I have to create tham twice? New databases will be automatically created. Once you have the Master setup with binary logging you can inspect what it will have have the slave execute by using the mysqlbinlog command on the log files or the SHOW EVENT (http://dev.mysql.com/doc/refman/5.1/en/show-events.html) syntax. Thanks for your suggestion. I'll try that. Regards - Götz -- Götz Reinicke IT-Koordinator Tel. +49 7141 969 420 Fax +49 7141 969 55 420 E-Mail goetz.reini...@filmakademie.de Filmakademie Baden-Württemberg GmbH Akademiehof 10 71638 Ludwigsburg www.filmakademie.de Eintragung Amtsgericht Stuttgart HRB 205016 Vorsitzende des Aufsichtsrats: Prof. Dr. Claudia Hübner Geschäftsführer: Prof. Thomas Schadt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 2 servers 1 common data base
On Thu, Jun 10, 2010 at 07:09, camelia botez camelia.bo...@weizmann.ac.il wrote: What can be done to run on both servers mysqld simultaneously and use the same data base? You are probably asking the wrong question here. Let's take a step back and ask you another question: What is it you want to achieve on a non-technical level? My gutfeeling tells me you want to have a HA-setup, so that when your database server dies, another one will take over. If that is the case, there's a bunch of tools that can help you do that. You could use classic replication up to a certain point, but active-passive master-master is probably more like what you want to achieve. For that, you can use a tool like MMM (http://mysql-mmm.org) for instance, which will make your life much easier. hope this helps! === Walter Heck Engineer @ Open Query (http://openquery.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: 2 servers 1 common data base
Hi! camelia botez wrote: We have 2 mysql servers - one active , second standby. The data base is on nsf storage file system mounted on the active server. We want to turn on active the second server and to be able to use both servers with the same nfs mounted data base. NFS may be good for many purposes, but using it for database storage is not among them. Rather than write from scratch, I'll quote my own postings to this list of 2009-Aug-7 and 2009-Aug-10: | I would *never* use NFS storage for any DBMS (except for some testing): | NFS access is slower than local disk access, and it adds more components | to the critical path. So your operations get slower, reliability | decreases, and (in case of any trouble) analysis becomes more difficult. | | I cannot imagine any setup where you have a machine strong enough to run | your DBMS on it, but not capable of driving sufficient local disks. | | The typical argument for having centralized disks serving many machines | is based on economies of scale (huge disks), flexibility (partitioning), | and centralized management (RAID replacement, backup). | There may be some merit to this in a specialized setup (SAN systems - | I'm not convinced of them, but don't claim expert knowledge about them), | but IMO not using general-purpose machines and NFS. | | Whatever the economical advantages during normal operation may be, you | should not forget the huge costs you would incur if any in-between | component breaks and your database stops operating. | This may be tolerable for some applications, depending on the required | availability, but simply intolerable for others. | ... my main objection against using NFS for database storage is not | performance, it is complexity: | If your database server does not use local disks but NFS, then the | network between the database server and the NFS server as well as that | server suddenly become essential components for your database setup. | As any component may fail, you increase the risk to your DB. | | You may reduce the individual risk by selecting better hardware, dual | controllers, dual cabling, mirrored machines, ... as much as you like, | the result will still be higher complexity and higher risks than if you | had applied similar enhancements to your database server and its local | disks. Regarding to your technical question: Just now when I try to start mysqld on the second server I get an error that says data base cannot be opened is locked by another mysql instance. What can be done to run on both servers mysqld simultaneously and use the same data base? One of the technical limitations of many NFS implementations is locking: The concept of NFS is to be a stateless system (on the NFS server), and that is not compatible with supporting file locks. So it may be that NFS denies a lock request from the remote machine, or you may have a NFS implementation that supports locking, and the request is denied because the local instance already holds a lock. Running two MySQL servers simultaneously on the same data files is even worse than trying NFS: An instance of the MySQL server assumes it is the only entity that accesses the data files, and manipulating overlapping data from two instances is a sure way to damage the data structures. So rather than trying to overcome that hurdle, you should be glad it is protecting you. Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz Amtsgericht 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 to setup replication - MySQL 5.0.x - Migration and new databases
Hi all! Götz Reinicke - IT-Koordinator wrote: Am 08.06.10 12:05, schrieb Rob Wultsch: On Mon, Jun 7, 2010 at 11:59 PM, Götz Reinicke - IT-Koordinator goetz.reini...@filmakademie.de wrote: Hi, we do have different LAMP systems and recently I started to put some mysql databases on one, new master server. (RedHat, Fredora, MySQL 4.x - 5.0.xx) MySQL 4.X is EOL. I strongly suggest not using it for new projects, if you have the option. If possible, MySQL 5.1 is recommended, Thanks, I'm aware of that. The 4.x servers are old and should be migrated to the recent redhat EL released mysql 5.0.x. Even 5.0 should be just an intermediate step, given that it is in extended support only. You didn't specify whether you are with a customer having a contract for extended support or not - if not, you cannot be assured to get fixes even for severe bugs, should they become known in 5.0. [[...]] Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz Amtsgericht 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: 2 servers 1 common data base
On Thu, Jun 10, 2010 at 10:27 AM, Joerg Bruehe joerg.bru...@sun.com wrote: | There may be some merit to this in a specialized setup (SAN systems - | I'm not convinced of them, but don't claim expert knowledge about them), As a slight aside, I'd like to offer you two major advantages of SAN systems: 1. More spindles, thus lower latency 2. Thin provisioning - akin to sparse files, you only lock the space you really use, not what you reserve. Both are of course at their most useful in large environments. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
substring query
I am looking for some guidance on creating a substring query. I have a column that stores a path to a file. I would like to extract that file extension and that is it and display it on my results. However, the paths are different lengths and some extensions are 3 letter and some are 4, eq 'html'. The only common they all have is the period before the extension. Anyone created a nested substring query that can do what I am looking to do? Thanks, Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: substring query
[snip] I am looking for some guidance on creating a substring query. I have a column that stores a path to a file. I would like to extract that file extension and that is it and display it on my results. However, the paths are different lengths and some extensions are 3 letter and some are 4, eq 'html'. The only common they all have is the period before the extension. Anyone created a nested substring query that can do what I am looking to do? [/snip] From the manual - http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_su bstr SELECT SUBSTRING('myString', -3) The result would be 'ing' in this case. Sub your string for myString -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: substring query
On 10/06/2010 16:55, Aaron Savage wrote: I am looking for some guidance on creating a substring query. I have a column that stores a path to a file. I would like to extract that file extension and that is it and display it on my results. However, the paths are different lengths and some extensions are 3 letter and some are 4, eq 'html'. The only common they all have is the period before the extension. Anyone created a nested substring query that can do what I am looking to do? SUBSTRING_INDEX should do what you want. SELECT SUBSTRING_INDEX('myfile.path','.',-1) = 'path' SELECT SUBSTRING_INDEX('myfile.pth','.',-1) = 'pth' or, in a version that's closer to real life usage: SELECT SUBSTRING_INDEX(myfield,'.',-1) from mytable http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index Mark -- http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: substring query
Thanks Guys for you insights. It may be a little more complicated then I made it out to be. I have tried this select substring_index(myfiled,'.',-2) from mytable. This has gotten me to a good starting point. But I still have two problems. After the extension there is a space and more wording. I want to cut that off. Also, some paths do not have an extension and I am trying to ignore those. So simply. I am just trying to pull out the file extension but there were some conditions I did not list. -Aaron SUBSTRING_INDEX should do what you want. SELECT SUBSTRING_INDEX('myfile.path','.',-1) = 'path' SELECT SUBSTRING_INDEX('myfile.pth','.',-1) = 'pth' or, in a version that's closer to real life usage: SELECT SUBSTRING_INDEX(myfield,'.',-1) from mytable http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index Mark -- http://mark.goodge.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: substring query
[snip] It may be a little more complicated then I made it out to be. I am just trying to pull out the file extension but there were some conditions I did not list. [/snip] Thank you for that update, would have been good to have from the start. SELECT SUBSTRING_INDEX('my.doc','.',-1) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: substring query
Sorry Jay, Here is what I came up with. select substring(substring_index(myfile,'.',-2),1,4) AS MyColumn from mydatabase group by MyColumn; That appears to yield what I need. I just need to filter out the results that do not have an extension. -Aaron On Thu, Jun 10, 2010 at 9:28 AM, Jay Blanchard jblanch...@pocket.com wrote: Thank you for that update, would have been good to have from the start. SELECT SUBSTRING_INDEX('my.doc','.',-1) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: substring query
[snip] Here is what I came up with. select substring(substring_index(myfile,'.',-2),1,4) AS MyColumn from mydatabase group by MyColumn; That appears to yield what I need. I just need to filter out the results that do not have an extension. [/snip] You can exclude results that do not have a period in them if this is the only period -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
No Need to Create Constraints?
I'm trying to create a database, using code generated by MySQL Workbench 5.2.21 RC. I'm running into this strange issue: ERROR 1005 (HY000): Can't create table 'nxdb.#sql-a6_3b' (errno: 150) Database was created using utf8 as charset, collation utf8_general_ci. Original code generated by Workbench: CREATE TABLE IF NOT EXISTS `nxdb`.`User_Role` ( `user_id` VARCHAR(35) NOT NULL , `role_id` VARCHAR(35) NOT NULL , PRIMARY KEY (`user_id`, `role_id`) , CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `nxdb`.`Users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `role_id` FOREIGN KEY (`role_id`) REFERENCES `nxdb`.`NXRoles` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB; (Note, tables Users and NXRoles were already created). This query didn't work. So, I stripped down to the basics to get the table created: CREATE TABLE IF NOT EXISTS `nxdb`.`User_Role` ( `user_id` VARCHAR(35) NOT NULL , `role_id` VARCHAR(35) NOT NULL , PRIMARY KEY (`user_id`, `role_id`) ) ENGINE = InnoDB; Then, I tried running this: ALTER TABLE `User_Role` ADD CONSTRAINT `role_id` FOREIGN KEY (`role_id`) REFERENCES `NXRoles` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE; And got this error message: ERROR 1005 (HY000): Can't create table 'nxdb.#sql-a6_3b' (errno: 150) When I check the structure of User_Role in phpMyAdmin (couldn't see a way to do this in Workbench), this is what I'm seeing: ActionKeynameTypeUniquePackedFieldCardinality CollationNullComment PRIMARYBTREEYesNouser_id0A role_id0A So, is there no need for me to actually add these constraints? -- Lola J. Lee Beno LinkedIn: http://www.linkedin.com/in/lolajleebeno Facebook: http://www.facebook.com/profile.php?id=714355583 Blog: http://www.lolajl.net/blog/
Re: WHERE clause from AS result
You can use an Alias in ORDER BY but not in WHERE clauses. Keith On Thu, 2010-06-10 at 16:38 -0400, Steven Staples wrote: Ok, I have done it before, where I have used the AS result in an ORDER BY, but now, I can't figure out why I can't use it in a WHERE clause? SELECT `email`, (SELECT CONCAT(`phone_pref`, '-', `phone_suff`) FROM `pnums` WHERE `id`=`usertable`.`id`) AS pnum FROM `usertable` WHERE pnum LIKE '555-12%'; It gives me this error: Error Code : 1054 Unknown column 'pnum' in 'where clause' Any ideas? Steven Staples Keith J. Clark Business ManagerOwner The BookwormWaterloo Hosting Quality Used Books Complete Web Hosting Provider www.k-wbookworm.com www.waterloohosting.com sa...@k-wbookworm.com sa...@waterloohosting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
WHERE clause from AS result
Ok, I have done it before, where I have used the AS result in an ORDER BY, but now, I can't figure out why I can't use it in a WHERE clause? SELECT `email`, (SELECT CONCAT(`phone_pref`, '-', `phone_suff`) FROM `pnums` WHERE `id`=`usertable`.`id`) AS pnum FROM `usertable` WHERE pnum LIKE '555-12%'; It gives me this error: Error Code : 1054 Unknown column 'pnum' in 'where clause' Any ideas? Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: WHERE clause from AS result
On 6/10/2010 4:38 PM, Steven Staples wrote: Ok, I have done it before, where I have used the AS result in an ORDER BY, but now, I can't figure out why I can't use it in a WHERE clause? SELECT `email`, (SELECT CONCAT(`phone_pref`, '-', `phone_suff`) FROM `pnums` WHERE `id`=`usertable`.`id`) AS pnum FROM `usertable` WHERE pnum LIKE '555-12%'; It gives me this error: Error Code : 1054 Unknown column 'pnum' in 'where clause' It has to do with the order in which things happen in the query. The results of the subquery are computed in the FROM...WHERE... part of the query. There is no way that the results could be named so that the WHERE clause could handle them. This is why aliases are available for use in the clauses processed after the WHERE clause - the GROUP BY and HAVING clauses. Try this as an alternative: SELECT `email`, (SELECT CONCAT(`phone_pref`, '-', `phone_suff`) FROM `pnums` WHERE `id`=`usertable`.`id`) AS pnum FROM `usertable` HAVING pnum LIKE '555-12%'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: No Need to Create Constraints?
On 6/10/2010 6:38 PM, Lola Lee Beno wrote: I'm trying to create a database, using code generated by MySQL Workbench 5.2.21 RC. I'm running into this strange issue: ERROR 1005 (HY000): Can't create table 'nxdb.#sql-a6_3b' (errno: 150) Database was created using utf8 as charset, collation utf8_general_ci. Original code generated by Workbench: CREATE TABLE IF NOT EXISTS `nxdb`.`User_Role` ( `user_id` VARCHAR(35) NOT NULL , `role_id` VARCHAR(35) NOT NULL , PRIMARY KEY (`user_id`, `role_id`) , CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `nxdb`.`Users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `role_id` FOREIGN KEY (`role_id`) REFERENCES `nxdb`.`NXRoles` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB; (Note, tables Users and NXRoles were already created). This query didn't work. So, I stripped down to the basics to get the table created: CREATE TABLE IF NOT EXISTS `nxdb`.`User_Role` ( `user_id` VARCHAR(35) NOT NULL , `role_id` VARCHAR(35) NOT NULL , PRIMARY KEY (`user_id`, `role_id`) ) ENGINE = InnoDB; Then, I tried running this: ALTER TABLE `User_Role` ADD CONSTRAINT `role_id` FOREIGN KEY (`role_id`) REFERENCES `NXRoles` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE; And got this error message: ERROR 1005 (HY000): Can't create table 'nxdb.#sql-a6_3b' (errno: 150) When I check the structure of User_Role in phpMyAdmin (couldn't see a way to do this in Workbench), this is what I'm seeing: ActionKeynameTypeUniquePackedFieldCardinality CollationNullComment PRIMARYBTREEYesNouser_id0A role_id0A So, is there no need for me to actually add these constraints? For more details about the error 150 code, check the SHOW ENGINE INNODB STATUS report. -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL For Huge Collections
Hello all, I am new to MySQL and am exploring the possibility of using it for my work. I have about ~300,000 e-books, each about 100 pages long. I am first going to extract each chapter from each e-book and then basically store an e-book as a collection of chapters. A chapter could of course be arbitrarily long depending on the book. My questions are: (1) Can MySQL handle data of this size? (2) How can I store text (contents) of each chapter? What data type will be appropriate? longtext? (3) I only envision running queries to extract a specific chapter from a specific e-book (say extract the chapter titled ABC from e-book number XYZ (or e-book titled XYZ)). Can MySQL handle these types of queries well on data of this size? (4) What are the benefits/drawbacks of using MySQL compared to using XML databases? I look forward to help on this topic. Many thanks in advance. Andy
Re: MySQL For Huge Collections
On 6/10/2010 10:16 PM, Andy wrote: Hello all, I am new to MySQL and am exploring the possibility of using it for my work. I have about ~300,000 e-books, each about 100 pages long. I am first going to extract each chapter from each e-book and then basically store an e-book as a collection of chapters. A chapter could of course be arbitrarily long depending on the book. My questions are: (1) Can MySQL handle data of this size? (2) How can I store text (contents) of each chapter? What data type will be appropriate? longtext? (3) I only envision running queries to extract a specific chapter from a specific e-book (say extract the chapter titled ABC from e-book number XYZ (or e-book titled XYZ)). Can MySQL handle these types of queries well on data of this size? (4) What are the benefits/drawbacks of using MySQL compared to using XML databases? I look forward to help on this topic. Many thanks in advance. Andy Always pick the right tool for the job. MySQL may not be the best tool for serving up eBook contents. However if you want to index and locate contents based on various parameters, then it may be a good fit for the purpose. Your simple queries would best be handled by a basic web server or FTP server because you seem to want http://your.site.here/ABC/xyz where ABC is your book and xyz is your chapter. Those types of technology are VERY well suited for managing the repetitive streaming and distribution of large binary objects (chapter files) like you might encounter with an eBook content delivery system. -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL For Huge Collections
Usually, you better use a NAS for such purpose. Database is designed to store highly transactional, record oriented storage that needs fast access... You can look for any Enterprise content management systems that rest its storage on a scalable NAS, with file virtualization in the long run. thanks On Fri, Jun 11, 2010 at 8:04 AM, SHAWN L.GREEN shawn.l.gr...@oracle.com wrote: On 6/10/2010 10:16 PM, Andy wrote: Hello all, I am new to MySQL and am exploring the possibility of using it for my work. I have about ~300,000 e-books, each about 100 pages long. I am first going to extract each chapter from each e-book and then basically store an e-book as a collection of chapters. A chapter could of course be arbitrarily long depending on the book. My questions are: (1) Can MySQL handle data of this size? (2) How can I store text (contents) of each chapter? What data type will be appropriate? longtext? (3) I only envision running queries to extract a specific chapter from a specific e-book (say extract the chapter titled ABC from e-book number XYZ (or e-book titled XYZ)). Can MySQL handle these types of queries well on data of this size? (4) What are the benefits/drawbacks of using MySQL compared to using XML databases? I look forward to help on this topic. Many thanks in advance. Andy Always pick the right tool for the job. MySQL may not be the best tool for serving up eBook contents. However if you want to index and locate contents based on various parameters, then it may be a good fit for the purpose. Your simple queries would best be handled by a basic web server or FTP server because you seem to want http://your.site.here/ABC/xyz where ABC is your book and xyz is your chapter. Those types of technology are VERY well suited for managing the repetitive streaming and distribution of large binary objects (chapter files) like you might encounter with an eBook content delivery system. -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=peterchack...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org