Re: InnoDB: Thousands of Tables or Hundreds of Databases?
Thanks for your comments Mike. The largest table contains 48 columns (objects), the second largest 20 columns (users) and all the rest are less than 10 columns. The instance sizes range from 10MB to 1GB. Transactions and row locking are required. Most queries are updates, followed by writes, then reads (application mostly uses memcached and other forms of caching for reads). I have since thought of having 1 table type per database, resulting in 'only' ~30 database instances; this would be 'easier' to maintain, and each database (containing 1 table type) could be optimised for its ratio of reading : writing : updating. However, this approach would require a LOT of work to re-write the application's database layer. What approach would be best? Thanks again, Michael On Tue, Feb 10, 2009 at 6:01 AM, mos mo...@fastmail.fm wrote: At 05:03 PM 2/9/2009, Michael Addyman wrote: Dear Geniuses, I have an application requiring ~30 InnoDB tables, which needs to scale up to at least 500 application instances (500 instances * ~30 tables = 15,000 tables). Some of the questions people are going to ask are: How large are each of the 30 tables? # of rows and physical size? What's the reason for having 500 separate application instances? Security? You're using InnoDb because you need transactions? Row locking? What percentage of the queries will be updates compared to reads? Discussions in the archives suggest I would be better off having independent databases for each of the application instances (i.e. 500 databases). However, it seems this would be much more difficult/expensive to manage/replicate/cluster than a single large database containing 15,000 tables. Storing all the data from all the application instances in ~30 large tables is not possible. Please could you give me your recommendations and experience? By creating 500 separate instances you are of course creating 500x the amount of work. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=michael.addy...@gmail.com
Re: using Unix soft links
On Mon, Feb 9, 2009 at 6:10 PM, Lucio Chiappetti lu...@lambrate.inaf.itwrote: I used to make Unix soft links of mysql tables back on mysql 3.23. [...] But I wonder if there is anything intrinsically wrong in the usage of soft-links, or whether this might be an indication instead of hardware problems with our machine or disks ! In short, the mysql server will cache the metadata of the tables, and, thus, for xxx and xxxdup separately. Any modification to xxx will not immediately come throught in xxxdup, but depending on the updates to xxx, you might suddenly have to read data from the file and end up with not quite what you expected. Thus, you see 'corruption' in xxxdup. You run a repair on it, which for some reason modifies the file, so now the cached metadata for xxx is incorrect. Rinse and repeat until all your data is fucked over. You really want to do this with views, or triggers to update the duplicate table, or, if all else fails, scheduled 'create table as select' statements. -- Celsius is based on water temperature. Fahrenheit is based on alcohol temperature. Ergo, Fahrenheit is better than Celsius. QED.
Re: InnoDB: Thousands of Tables or Hundreds of Databases?
Thanks for your comments Mike. The largest table contains 48 columns (objects), the second largest 20 columns (users) and all the rest are less than 10 columns. The instance sizes range from 10MB to 1GB. Transactions and row locking are required. Most queries are updates, followed by writes, then reads (application mostly uses memcached and other forms of caching for reads). I have since thought of having 1 table type per database, resulting in 'only' ~30 databases; this would be 'easier' to maintain, and each database (containing 1 table type) could be optimised for its ratio of reading : writing : updating. However, this approach would require a LOT of work to re-write the application's database layer. What approach would be best? Thanks again, Michael On Tue, Feb 10, 2009 at 6:01 AM, mos mo...@fastmail.fm wrote: At 05:03 PM 2/9/2009, Michael Addyman wrote: Dear Geniuses, I have an application requiring ~30 InnoDB tables, which needs to scale up to at least 500 application instances (500 instances * ~30 tables = 15,000 tables). Some of the questions people are going to ask are: How large are each of the 30 tables? # of rows and physical size? What's the reason for having 500 separate application instances? Security? You're using InnoDb because you need transactions? Row locking? What percentage of the queries will be updates compared to reads? Discussions in the archives suggest I would be better off having independent databases for each of the application instances (i.e. 500 databases). However, it seems this would be much more difficult/expensive to manage/replicate/cluster than a single large database containing 15,000 tables. Storing all the data from all the application instances in ~30 large tables is not possible. Please could you give me your recommendations and experience? By creating 500 separate instances you are of course creating 500x the amount of work. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=michael.addy...@gmail.com
Re: MySQL View
On Mon, Feb 9, 2009 at 3:41 PM, Steven Buehler wrote: Ok, I just saw a post about using view's in mysql. I tried to look it up and found how to use it, but my question is: what is a view and why would you use it? The problem with any definition of an object in a database is that there are multiple definitions. Usually on the one hand you have the definition from abstract relational theory, and on the other hand you have the definition from actual working databases. So I am not going to bother with a definition, I will try to explain how a view works internally inside database code. The easiest way to understand a view is to consider a view as a macro that gets expanded during the execution of every query that references that view in its FROM. Lets take for example the view that your DBA has defined for you using: CREATE VIEW x AS SELECT * FROM y INNER JOIN z ON y.id = z.id; Then you query that view with the query: SELECT a FROM x; What the database will do for you behind the scenes is expand your usage of the view. In effect, the database will replace x with its definition. So your query SELECT a FROM x; gets expanded to: SELECT a FROM (SELECT * FROM y INNER JOIN z ON y.id = z.id); Notice that I have done nothing but replace x with its definition between parenthesis. And this results in a valid query that can be executed. And that is exactly what the database will do. It will do this substitution and then it will run the result of that substitution as if it were the query that you submitted. Obviously a bit more will go on behind the scenes to handle things like permissions and optimizations (especially if you get to databases that have more functionality then MySQL), but this is really all there is to it. A view is a simple macro that assigns an alias to a select statement, and when you reference that alias the select statement will get substituted back in. 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: InnoDB: Thousands of Tables or Hundreds of Databases?
Thanks for your comments Mike. 1. The largest table has 48 columns, the second largest 20 columns, and the remainder less than 10 columns. 2. Each application instance (~30 tables) is between 50MB and 1GB. 3. Application instances are separate for many reasons including infrastructure/scaling flexibility and security. 4. Transactions and row locking are required. 5. Mostly writes, closely followed by updates, then reads (out-of-database caching handles most reads) I have now thought of having 1 table type per database (i.e. ~30 databases). This would be easier and cheaper to manage than hundreds of databases, and would also allow databases to be finely tuned to the table type, size, workload and writes : updates : reads ratio. However, re-developing the database layer to achieve this looks incredibly difficult. An easy solution would be to have ~100 instances per database, resulting in ~3000 tables per database, and ~5 database clusters. I think my final suggestion is the most suitable. What would your recommendations be? Many thanks Michael. On Tue, Feb 10, 2009 at 6:01 AM, mos mo...@fastmail.fm wrote: At 05:03 PM 2/9/2009, Michael Addyman wrote: Dear Geniuses, I have an application requiring ~30 InnoDB tables, which needs to scale up to at least 500 application instances (500 instances * ~30 tables = 15,000 tables). Some of the questions people are going to ask are: How large are each of the 30 tables? # of rows and physical size? What's the reason for having 500 separate application instances? Security? You're using InnoDb because you need transactions? Row locking? What percentage of the queries will be updates compared to reads? Discussions in the archives suggest I would be better off having independent databases for each of the application instances (i.e. 500 databases). However, it seems this would be much more difficult/expensive to manage/replicate/cluster than a single large database containing 15,000 tables. Storing all the data from all the application instances in ~30 large tables is not possible. Please could you give me your recommendations and experience? By creating 500 separate instances you are of course creating 500x the amount of work. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=michael.addy...@gmail.com
Re: InnoDB: Thousands of Tables or Hundreds of Databases?
On Tue, Feb 10, 2009 at 12:39 PM, Michael Addyman michael.addy...@googlemail.com wrote: I have now thought of having 1 table type per database (i.e. ~30 databases). This would be easier and cheaper to manage than hundreds of databases, and would also allow databases to be finely tuned to the table type, size, workload and writes : updates : reads ratio. However, re-developing the database layer to achieve this looks incredibly difficult. I didn't quite get that first time round, but I think I do, now. How about using triggers and views or stored procedures to insert/select the username in a field in each table ? Assuming you have a separate user per instance, that might solve a lot of problems :-) -- Celsius is based on water temperature. Fahrenheit is based on alcohol temperature. Ergo, Fahrenheit is better than Celsius. QED.
Re: InnoDB: Thousands of Tables or Hundreds of Databases?
I think it would be good to think about scaling a bit more. What if your requirements change from 500 application instances to 5000 instances? It is good to go with a solution now that can easily scale over to multiple servers. Also, it would probably be good if you could move databases over to other database servers when they require more (or less) peformance. From these two requirements I would at least put every instance in it's own database. That way, if you develop an easy way to move a database over to another server easily and automated, you have an extremely scalable approach. Then, you could have each database server tuned the same way and just kind of load balance them by moving databases from one server to another. If you don't want to change the application's configuration every time a database moves to another server, you could look at a simple MySQL proxy installation to hide the server a database is on from the client application. These are my 2 cents :) Walter OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Tue, Feb 10, 2009 at 12:39 PM, Michael Addyman michael.addy...@googlemail.com wrote: Thanks for your comments Mike. 1. The largest table has 48 columns, the second largest 20 columns, and the remainder less than 10 columns. 2. Each application instance (~30 tables) is between 50MB and 1GB. 3. Application instances are separate for many reasons including infrastructure/scaling flexibility and security. 4. Transactions and row locking are required. 5. Mostly writes, closely followed by updates, then reads (out-of-database caching handles most reads) I have now thought of having 1 table type per database (i.e. ~30 databases). This would be easier and cheaper to manage than hundreds of databases, and would also allow databases to be finely tuned to the table type, size, workload and writes : updates : reads ratio. However, re-developing the database layer to achieve this looks incredibly difficult. An easy solution would be to have ~100 instances per database, resulting in ~3000 tables per database, and ~5 database clusters. I think my final suggestion is the most suitable. What would your recommendations be? Many thanks Michael. On Tue, Feb 10, 2009 at 6:01 AM, mos mo...@fastmail.fm wrote: At 05:03 PM 2/9/2009, Michael Addyman wrote: Dear Geniuses, I have an application requiring ~30 InnoDB tables, which needs to scale up to at least 500 application instances (500 instances * ~30 tables = 15,000 tables). Some of the questions people are going to ask are: How large are each of the 30 tables? # of rows and physical size? What's the reason for having 500 separate application instances? Security? You're using InnoDb because you need transactions? Row locking? What percentage of the queries will be updates compared to reads? Discussions in the archives suggest I would be better off having independent databases for each of the application instances (i.e. 500 databases). However, it seems this would be much more difficult/expensive to manage/replicate/cluster than a single large database containing 15,000 tables. Storing all the data from all the application instances in ~30 large tables is not possible. Please could you give me your recommendations and experience? By creating 500 separate instances you are of course creating 500x the amount of work. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=michael.addy...@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
Re: InnoDB: Thousands of Tables or Hundreds of Databases?
Walter, this is exactly why we went for separate application instances initially - it is the most flexible solution for scaling. However, we have since discovered that it's actually a lot more work to manage than we anticipated! We would love to continue using separate application instances (allowing us to move instances around depending on load), but setting up and maintaining replication is very time consuming. Does anyone know of any scripts / tools to ease replication / clustering setup / administration / maintenance? Thanks again, Michael On Tue, Feb 10, 2009 at 11:48 AM, Walter Heck li...@olindata.com wrote: I think it would be good to think about scaling a bit more. What if your requirements change from 500 application instances to 5000 instances? It is good to go with a solution now that can easily scale over to multiple servers. Also, it would probably be good if you could move databases over to other database servers when they require more (or less) peformance. From these two requirements I would at least put every instance in it's own database. That way, if you develop an easy way to move a database over to another server easily and automated, you have an extremely scalable approach. Then, you could have each database server tuned the same way and just kind of load balance them by moving databases from one server to another. If you don't want to change the application's configuration every time a database moves to another server, you could look at a simple MySQL proxy installation to hide the server a database is on from the client application. These are my 2 cents :) Walter OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Tue, Feb 10, 2009 at 12:39 PM, Michael Addyman michael.addy...@googlemail.com wrote: Thanks for your comments Mike. 1. The largest table has 48 columns, the second largest 20 columns, and the remainder less than 10 columns. 2. Each application instance (~30 tables) is between 50MB and 1GB. 3. Application instances are separate for many reasons including infrastructure/scaling flexibility and security. 4. Transactions and row locking are required. 5. Mostly writes, closely followed by updates, then reads (out-of-database caching handles most reads) I have now thought of having 1 table type per database (i.e. ~30 databases). This would be easier and cheaper to manage than hundreds of databases, and would also allow databases to be finely tuned to the table type, size, workload and writes : updates : reads ratio. However, re-developing the database layer to achieve this looks incredibly difficult. An easy solution would be to have ~100 instances per database, resulting in ~3000 tables per database, and ~5 database clusters. I think my final suggestion is the most suitable. What would your recommendations be? Many thanks Michael. On Tue, Feb 10, 2009 at 6:01 AM, mos mo...@fastmail.fm wrote: At 05:03 PM 2/9/2009, Michael Addyman wrote: Dear Geniuses, I have an application requiring ~30 InnoDB tables, which needs to scale up to at least 500 application instances (500 instances * ~30 tables = 15,000 tables). Some of the questions people are going to ask are: How large are each of the 30 tables? # of rows and physical size? What's the reason for having 500 separate application instances? Security? You're using InnoDb because you need transactions? Row locking? What percentage of the queries will be updates compared to reads? Discussions in the archives suggest I would be better off having independent databases for each of the application instances (i.e. 500 databases). However, it seems this would be much more difficult/expensive to manage/replicate/cluster than a single large database containing 15,000 tables. Storing all the data from all the application instances in ~30 large tables is not possible. Please could you give me your recommendations and experience? By creating 500 separate instances you are of course creating 500x the amount of work. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=michael.addy...@gmail.com
Re: InnoDB: Thousands of Tables or Hundreds of Databases?
Johan, we considered this approach but concluded it would require too much re-development (more than just the database layer). Thanks anyway. On Tue, Feb 10, 2009 at 11:47 AM, Johan De Meersman vegiv...@tuxera.bewrote: On Tue, Feb 10, 2009 at 12:39 PM, Michael Addyman michael.addy...@googlemail.com wrote: I have now thought of having 1 table type per database (i.e. ~30 databases). This would be easier and cheaper to manage than hundreds of databases, and would also allow databases to be finely tuned to the table type, size, workload and writes : updates : reads ratio. However, re-developing the database layer to achieve this looks incredibly difficult. I didn't quite get that first time round, but I think I do, now. How about using triggers and views or stored procedures to insert/select the username in a field in each table ? Assuming you have a separate user per instance, that might solve a lot of problems :-) -- Celsius is based on water temperature. Fahrenheit is based on alcohol temperature. Ergo, Fahrenheit is better than Celsius. QED.
RE: MySQL View
-Original Message- From: Jochem van Dieten [mailto:joch...@gmail.com] Sent: Tuesday, February 10, 2009 5:10 AM To: mysql@lists.mysql.com Subject: Re: MySQL View On Mon, Feb 9, 2009 at 3:41 PM, Steven Buehler wrote: Ok, I just saw a post about using view's in mysql. I tried to look it up and found how to use it, but my question is: what is a view and why would you use it? The problem with any definition of an object in a database is that there are multiple definitions. Usually on the one hand you have the definition from abstract relational theory, and on the other hand you have the definition from actual working databases. So I am not going to bother with a definition, I will try to explain how a view works internally inside database code. The easiest way to understand a view is to consider a view as a macro that gets expanded during the execution of every query that references that view in its FROM. Lets take for example the view that your DBA has defined for you using: CREATE VIEW x AS SELECT * FROM y INNER JOIN z ON y.id = z.id; Then you query that view with the query: SELECT a FROM x; What the database will do for you behind the scenes is expand your usage of the view. In effect, the database will replace x with its definition. So your query SELECT a FROM x; gets expanded to: SELECT a FROM (SELECT * FROM y INNER JOIN z ON y.id = z.id); Notice that I have done nothing but replace x with its definition between parenthesis. And this results in a valid query that can be executed. And that is exactly what the database will do. It will do this substitution and then it will run the result of that substitution as if it were the query that you submitted. Obviously a bit more will go on behind the scenes to handle things like permissions and optimizations (especially if you get to databases that have more functionality then MySQL), but this is really all there is to it. A view is a simple macro that assigns an alias to a select statement, and when you reference that alias the select statement will get substituted back in. Jochem, Not sure about the other poster, but this helps explain it to me. If I understand you correctly, if I have multiple tables with many columns in them, but have several queries that need to pull only a few columns from each and put them together, it is probably best to create a view to do this so that I don't have to keep running joins in my queries? Even if I reboot the computer, the view will still be there when it comes back up too? Thanks Steve -- 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 5.1.30 MyISAM table corrupts when it hits 1GB
Make sure you check you ulimit on file - I have hit that on AIX more than once. On Mon, Feb 9, 2009 at 2:42 PM, JD King jeff_d_k...@hotmail.com wrote: I am running MySQL 5.1.30 on AIX 5.3. When a table reaches 1GB the table gets marked as corrupt. Is there a setting that limits table size to 1GB? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=naclos...@gmail.com -- Andy Carlson --- Gamecube:$150,PSO:$50,Broadband Adapter: $35, Hunters License: $8.95/month, The feeling of seeing the red box with the item you want in it:Priceless.
Re: InnoDB: Thousands of Tables or Hundreds of Databases?
Hooray! http://code.google.com/p/mysql-master-master/ Am I crazy to be considering replicating 500+ databases? I think so... On Tue, Feb 10, 2009 at 12:11 PM, Michael Addyman michael.addy...@googlemail.com wrote: Walter, this is exactly why we went for separate application instances initially - it is the most flexible solution for scaling. However, we have since discovered that it's actually a lot more work to manage than we anticipated! We would love to continue using separate application instances (allowing us to move instances around depending on load), but setting up and maintaining replication is very time consuming. Does anyone know of any scripts / tools to ease replication / clustering setup / administration / maintenance? Thanks again, Michael On Tue, Feb 10, 2009 at 11:48 AM, Walter Heck li...@olindata.com wrote: I think it would be good to think about scaling a bit more. What if your requirements change from 500 application instances to 5000 instances? It is good to go with a solution now that can easily scale over to multiple servers. Also, it would probably be good if you could move databases over to other database servers when they require more (or less) peformance. From these two requirements I would at least put every instance in it's own database. That way, if you develop an easy way to move a database over to another server easily and automated, you have an extremely scalable approach. Then, you could have each database server tuned the same way and just kind of load balance them by moving databases from one server to another. If you don't want to change the application's configuration every time a database moves to another server, you could look at a simple MySQL proxy installation to hide the server a database is on from the client application. These are my 2 cents :) Walter OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Tue, Feb 10, 2009 at 12:39 PM, Michael Addyman michael.addy...@googlemail.com wrote: Thanks for your comments Mike. 1. The largest table has 48 columns, the second largest 20 columns, and the remainder less than 10 columns. 2. Each application instance (~30 tables) is between 50MB and 1GB. 3. Application instances are separate for many reasons including infrastructure/scaling flexibility and security. 4. Transactions and row locking are required. 5. Mostly writes, closely followed by updates, then reads (out-of-database caching handles most reads) I have now thought of having 1 table type per database (i.e. ~30 databases). This would be easier and cheaper to manage than hundreds of databases, and would also allow databases to be finely tuned to the table type, size, workload and writes : updates : reads ratio. However, re-developing the database layer to achieve this looks incredibly difficult. An easy solution would be to have ~100 instances per database, resulting in ~3000 tables per database, and ~5 database clusters. I think my final suggestion is the most suitable. What would your recommendations be? Many thanks Michael. On Tue, Feb 10, 2009 at 6:01 AM, mos mo...@fastmail.fm wrote: At 05:03 PM 2/9/2009, Michael Addyman wrote: Dear Geniuses, I have an application requiring ~30 InnoDB tables, which needs to scale up to at least 500 application instances (500 instances * ~30 tables = 15,000 tables). Some of the questions people are going to ask are: How large are each of the 30 tables? # of rows and physical size? What's the reason for having 500 separate application instances? Security? You're using InnoDb because you need transactions? Row locking? What percentage of the queries will be updates compared to reads? Discussions in the archives suggest I would be better off having independent databases for each of the application instances (i.e. 500 databases). However, it seems this would be much more difficult/expensive to manage/replicate/cluster than a single large database containing 15,000 tables. Storing all the data from all the application instances in ~30 large tables is not possible. Please could you give me your recommendations and experience? By creating 500 separate instances you are of course creating 500x the amount of work. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=michael.addy...@gmail.com
Re: InnoDB: Thousands of Tables or Hundreds of Databases?
On Tue, Feb 10, 2009 at 2:57 PM, Michael Addyman michael.addy...@googlemail.com wrote: Hooray! http://code.google.com/p/mysql-master-master/ Am I crazy to be considering replicating 500+ databases? I think so... I don't think the number of databases is an issue - the main point is the amount of activity on them, I'd say. As long as you have a good pipe to your slave (as in, gigabit crossed or so :-) ) I don't think you'll run into trouble. -- Celsius is based on water temperature. Fahrenheit is based on alcohol temperature. Ergo, Fahrenheit is better than Celsius. QED.
mysql optimization tips
Hi All, I'm not sure if this question is fine, I'm new to the list and I just have one very important question. Can anyone help me suggest the right optimization for our company's server. Our current server is a Dell PowerEdge 1900 a QuadCore with 1TB total (Raid 1) of HD space and 4G RAM. The company is 300 seater callcenter with around 2000 Transactions (inserts/updates internally and externally) per day. Any help would be much appreciated. Here is our current mysql config file. [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-bdb sort_buffer_size=128M query_cache_size=8M key_buffer=256M join_buffer_size=128M #to be changed to lesser than 2M based on this site #http://www.mysqlperformanceblog.com/2007/09/17/mysql-what-read_buffer_size-value-is-optimal/ read_buffer_size=2M read_rnd_buffer_size=2M max_allowed_packet=4M table_cache=256 thread_cache_size=64 old_passwords=1 # increase the max_connection and connect_timeout max_connections=600 max_connect_errors=20 connect_timeout=60 # set the option for starting mysqld log_slow_queries=/var/log/mysqld.slow.log log-bin=localhost-bin sysdate-is-now [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [myisamchk] key_buffer=128M sort_buffer=64M read_buffer=16M write_buffer=16M Thanks, Mon
Re: InnoDB: Thousands of Tables or Hundreds of Databases?
We'll continue to use many replication clusters of course. And yes, we use bonded gigabit ethernet. I stumbled across Dolphin Express today - if only there were a cheap alternative! Thanks for the reassurance! On Tue, Feb 10, 2009 at 2:21 PM, Johan De Meersman vegiv...@tuxera.bewrote: On Tue, Feb 10, 2009 at 2:57 PM, Michael Addyman michael.addy...@googlemail.com wrote: Hooray! http://code.google.com/p/mysql-master-master/ Am I crazy to be considering replicating 500+ databases? I think so... I don't think the number of databases is an issue - the main point is the amount of activity on them, I'd say. As long as you have a good pipe to your slave (as in, gigabit crossed or so :-) ) I don't think you'll run into trouble. -- Celsius is based on water temperature. Fahrenheit is based on alcohol temperature. Ergo, Fahrenheit is better than Celsius. QED.
Re: db setup - correction
Peter Brawley wrote: PJ, As I understand it, I have one table books it lists all the info for the book other than the author(s) or the categories ; for these I need an authors table and a category table... I'll continue with the authors as categories will surely be the same thing. BTW, I cannot use ISBN as PK since there are books without ISBN that are older than ISBN itself. And worse, some publishers re-use ISBM#s. In general, any PK dependency on the outside world is to be avoided unless the dependency guarantees uniqueness as robustly as the internal auto_increment facility. So, there is no author or category field in the books table, right? Right. Are you saying that the id PK of books, authors and books_authors are all the same? Yikes no. Each is entirely independent. But what about foreign keys? Don't I need that to find the relationships between the books, the authors and the categories? After all, isn't this a relational db? If so, I can't use the default engine (MyISAM) which does not support FK. So, if I have to use foreign keys, I have to change to INNODB, right? Where things go awry is in how to keep track of all this? Especially, when I have to enter the information in the main table, which is books. Every time I have a new listing I have to enter the main book info in the books table, the authors in the authors table and the rest in books_authors table... not to mention the categories - I don't suppose there is a simple solution to this? You write a standard master-detail form, which usually has a single form at top for the parent row, and a browsing multi-row form below for entry of multiple child rows. PB PJ wrote: Peter Brawley wrote: PJ Why do I need a third table? The clue is author (could be 2 or more) in your books column list. It is incorrect to store two author names or IDs in one column. The relationship is 1:many. So you need a bridge table: books(id PK, etc...) authors(id PK, etc...) books_authors(id PK, bid references books(id),...,aid references authors(id), listed_order smallint, etc...) Now one book with multiple authors has one books_authors row for each of its authors, and you retrieve book author info with a simple join. I did review normalization - I had read it before; it is a little clearer now, but so, now I'm getting very very confused... As I understand it, I have one table books it lists all the info for the book other than the author(s) or the categories ; for these I need an authors table and a category table... I'll continue with the authors as categories will surely be the same thing. BTW, I cannot use ISBN as PK since there are books without ISBN that are older than ISBN itself. So, there is no author or category field in the books table, right? Are you saying that the id PK of books, authors and books_authors are all the same? The authors table would have the fields auth_id, first_name, last_name. The books_authors table would have its own fields - id PK, bid, aid and listed_order (which would indicate iin which order to display the authors (?)) Where things go awry is in how to keep track of all this? Especially, when I have to enter the information in the main table, which is books. Every time I have a new listing I have to enter the main book info in the books table, the authors in the authors table and the rest in books_authors table... not to mention the categories - I don't suppose there is a simple solution to this? From the looks of things I have to create some kind of php input function or group of functions to come up with a page with the fields necessary to enter all the data and then store the data in mySql. And to retrieve the information its a heap of functions to gather and populate a page with the info from mySql... PB - PJ wrote: Olaf Stein wrote: Just about the authors You need a separate table for them and then an table linking authors and books. You lose me here... :-( Why do I need a third table? I may have 2 or three books with 3 authors, quite a few with 2 authors and many with just 1 author. I can't see creating an extra table for every book that has more than 1 author... ? ? ? ? And wouldn't it be the same thing for the categories? Isn't the relationship between the author field in the books table and the authors table done by an foreign key? So you have table books, authors and rel_books_authors where rel_books authors has 3 entries for a book with 3 authors just using the book id and the author is's Olaf On 2/9/09 10:25 AM, PJ af.gour...@videotron.ca wrote: being a newbie to mysql, I'm a little confused about how to deal with the following: I am creating a database of books and have come down to this - table for books (books) including fields for id (primary key, auto-incr.) title author (could be 2 or more) category
Re: InnoDB: Thousands of Tables or Hundreds of Databases?
Hi Michael, On Mon, Feb 9, 2009 at 6:03 PM, Michael Addyman michael.addy...@googlemail.com wrote: Dear Geniuses, I have an application requiring ~30 InnoDB tables, which needs to scale up to at least 500 application instances (500 instances * ~30 tables = 15,000 tables). Discussions in the archives suggest I would be better off having independent databases for each of the application instances (i.e. 500 databases). However, it seems this would be much more difficult/expensive to manage/replicate/cluster than a single large database containing 15,000 tables. Storing all the data from all the application instances in ~30 large tables is not possible. Please could you give me your recommendations and experience? Many thanks, Michael This is not an easy question to answer without knowing a lot about your application's workload, which I suspect you will not be able to provide information on until you actually get some load. When you get a lot of InnoDB tables, one thing I can tell you is that the stock InnoDB will chew up a lot of memory for the data dictionary. We've recently patched InnoDB to alleviate this: http://www.percona.com/docs/wiki/patches:innodb_dict_size_limit If I were you I'd just go with your best educated guess, and when you get enough load to measure (not enough that you think you're going to be in trouble soon -- don't wait that long), call for expert help to find the most expensive parts of the app, and decide which are going to be hard to scale. It's usually difficult to predict in advance, but if you get some non-trivial load, you can then measure and have plenty of time to do something about what you find out. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB: Thousands of Tables or Hundreds of Databases?
I'll take that on board. Thanks for your advice, mysql-master-master, Maatkit, mysqlperformanceblog, your patches and community support! On Tue, Feb 10, 2009 at 3:54 PM, Baron Schwartz ba...@xaprb.com wrote: Hi Michael, On Mon, Feb 9, 2009 at 6:03 PM, Michael Addyman michael.addy...@googlemail.com wrote: Dear Geniuses, I have an application requiring ~30 InnoDB tables, which needs to scale up to at least 500 application instances (500 instances * ~30 tables = 15,000 tables). Discussions in the archives suggest I would be better off having independent databases for each of the application instances (i.e. 500 databases). However, it seems this would be much more difficult/expensive to manage/replicate/cluster than a single large database containing 15,000 tables. Storing all the data from all the application instances in ~30 large tables is not possible. Please could you give me your recommendations and experience? Many thanks, Michael This is not an easy question to answer without knowing a lot about your application's workload, which I suspect you will not be able to provide information on until you actually get some load. When you get a lot of InnoDB tables, one thing I can tell you is that the stock InnoDB will chew up a lot of memory for the data dictionary. We've recently patched InnoDB to alleviate this: http://www.percona.com/docs/wiki/patches:innodb_dict_size_limit If I were you I'd just go with your best educated guess, and when you get enough load to measure (not enough that you think you're going to be in trouble soon -- don't wait that long), call for expert help to find the most expensive parts of the app, and decide which are going to be hard to scale. It's usually difficult to predict in advance, but if you get some non-trivial load, you can then measure and have plenty of time to do something about what you find out. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html
Re: db setup - correction
PJ But what about foreign keys? Don't I need that to find the relationships between the books, the authors and the categories? After all, isn't this a relational db? If so, I can't use the default engine (MyISAM) which does not support FK. So, if I have to use foreign keys, I have to change to INNODB, right? Engine choice is another issue. InnoDB properly isolates FK enforcement in the database. The MyISAM tradeoff (for speed) is that you have to implement FK logic in code. For normalisation, however, you need the A-B-AB setup no matter what engine you use. PB - PJ wrote: Peter Brawley wrote: PJ, As I understand it, I have one table books it lists all the info for the book other than the author(s) or the categories ; for these I need an authors table and a category table... I'll continue with the authors as categories will surely be the same thing. BTW, I cannot use ISBN as PK since there are books without ISBN that are older than ISBN itself. And worse, some publishers re-use ISBM#s. In general, any PK dependency on the outside world is to be avoided unless the dependency guarantees uniqueness as robustly as the internal auto_increment facility. So, there is no author or category field in the books table, right? Right. Are you saying that the id PK of books, authors and books_authors are all the same? Yikes no. Each is entirely independent. But what about foreign keys? Don't I need that to find the relationships between the books, the authors and the categories? After all, isn't this a relational db? If so, I can't use the default engine (MyISAM) which does not support FK. So, if I have to use foreign keys, I have to change to INNODB, right? Where things go awry is in how to keep track of all this? Especially, when I have to enter the information in the main table, which is books. Every time I have a new listing I have to enter the main book info in the books table, the authors in the authors table and the rest in books_authors table... not to mention the categories - I don't suppose there is a simple solution to this? You write a standard master-detail form, which usually has a single form at top for the parent row, and a browsing multi-row form below for entry of multiple child rows. PB PJ wrote: Peter Brawley wrote: PJ Why do I need a third table? The clue is author (could be 2 or more) in your books column list. It is incorrect to store two author names or IDs in one column. The relationship is 1:many. So you need a bridge table: books(id PK, etc...) authors(id PK, etc...) books_authors(id PK, bid references books(id),...,aid references authors(id), listed_order smallint, etc...) Now one book with multiple authors has one books_authors row for each of its authors, and you retrieve book author info with a simple join. I did review normalization - I had read it before; it is a little clearer now, but so, now I'm getting very very confused... As I understand it, I have one table books it lists all the info for the book other than the author(s) or the categories ; for these I need an authors table and a category table... I'll continue with the authors as categories will surely be the same thing. BTW, I cannot use ISBN as PK since there are books without ISBN that are older than ISBN itself. So, there is no author or category field in the books table, right? Are you saying that the id PK of books, authors and books_authors are all the same? The authors table would have the fields auth_id, first_name, last_name. The books_authors table would have its own fields - id PK, bid, aid and listed_order (which would indicate iin which order to display the authors (?)) Where things go awry is in how to keep track of all this? Especially, when I have to enter the information in the main table, which is books. Every time I have a new listing I have to enter the main book info in the books table, the authors in the authors table and the rest in books_authors table... not to mention the categories - I don't suppose there is a simple solution to this? From the looks of things I have to create some kind of php input function or group of functions to come up with a page with the fields necessary to enter all the data and then store the data in mySql. And to retrieve the information its a heap of functions to gather and populate a page with the info from mySql... PB - PJ wrote: Olaf Stein wrote: Just about the authors You need a separate table for them and then an table linking authors and books. You lose me here... :-( Why do I need a third table? I may have 2 or three books with 3 authors, quite a few with 2 authors and many with just 1 author. I can't see creating an extra table for every book that has more than 1 author... ? ? ? ? And wouldn't it be the same thing for the
Re: InnoDB: Thousands of Tables or Hundreds of Databases?
At 04:30 AM 2/10/2009, you wrote: Thanks for your comments Mike. The largest table contains 48 columns (objects), the second largest 20 columns (users) and all the rest are less than 10 columns. The instance sizes range from 10MB to 1GB. Transactions and row locking are required. Most queries are updates, followed by writes, then reads (application mostly uses memcached and other forms of caching for reads). I have since thought of having 1 table type per database, resulting in 'only' ~30 databases; this would be 'easier' to maintain, and each database (containing 1 table type) could be optimised for its ratio of reading : writing : updating. However, this approach would require a LOT of work to re-write the application's database layer. What approach would be best? Michael, Does the saying between a rock and a hard place sound familiar? :-) I feel you're going to have to create a test suite to benchmark both solutions thoroughly before you start on the application code. You're going to find pro's and con's with both designs but after benchmarking you're going to know which one performs better both from a speed viewpoint and maintenance viewpoint. The more time you spend testing the design, the more confidence you'll have that it works and the less chance of throwing it away and starting over later on down the road. Then you'll also be able to present to your client some hard facts about each design. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: InnoDB: Thousands of Tables or Hundreds of Databases?
I vote for 1 table per TableType this will keep your DB schema consistent with Architecture Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Date: Tue, 10 Feb 2009 11:03:46 -0600 To: mysql@lists.mysql.com From: mo...@fastmail.fm Subject: Re: InnoDB: Thousands of Tables or Hundreds of Databases? At 04:30 AM 2/10/2009, you wrote: Thanks for your comments Mike. The largest table contains 48 columns (objects), the second largest 20 columns (users) and all the rest are less than 10 columns. The instance sizes range from 10MB to 1GB. Transactions and row locking are required. Most queries are updates, followed by writes, then reads (application mostly uses memcached and other forms of caching for reads). I have since thought of having 1 table type per database, resulting in 'only' ~30 databases; this would be 'easier' to maintain, and each database (containing 1 table type) could be optimised for its ratio of reading : writing : updating. However, this approach would require a LOT of work to re-write the application's database layer. What approach would be best? Michael, Does the saying between a rock and a hard place sound familiar? :-) I feel you're going to have to create a test suite to benchmark both solutions thoroughly before you start on the application code. You're going to find pro's and con's with both designs but after benchmarking you're going to know which one performs better both from a speed viewpoint and maintenance viewpoint. The more time you spend testing the design, the more confidence you'll have that it works and the less chance of throwing it away and starting over later on down the road. Then you'll also be able to present to your client some hard facts about each design. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Windows Live™: Keep your life in sync. http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_allup_howitworks_022009
MySQL University session on Feb 12: Using DTrace with MySQL
Using DTrace with MySQL http://forge.mysql.com/wiki/Using_DTrace_with_MySQL This Thursday (February 12th, 14:00 UTC), MC Brown will give a MySQL University session on Using DTrace with MySQL. MC has been involved not just with documenting DTrace but also with DTrace development; see his recent blog post for details: http://coalface.mcslp.com/2009/02/09/dtrace-in-mysql-documentation-and-a-mysql-university-session/ For MySQL University sessions, point your browser to this page: http://webmeeting.dimdim.com/portal/JoinForm.action?confKey=mysqluniversity You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, and more.) MySQL University is a free educational online program for engineers/developers. MySQL University sessions are open to anyone, not just Sun employees. Sessions are recorded (slides and audio), so if you can't attend the live session you can look at the recording anytime after the session. Here's the schedule for the upcoming weeks (see http://forge.mysql.com/wiki/MySQL_University for a better format of this list): February 12, 2008 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) Using DTrace with MySQL MC Brown February 19, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) Developing MySQL on Solaris MC Brown Trond Norbye February 26, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) Backing up MySQL using file system snapshotsLenz Grimmer March 5, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow)Good Coding Style Konstantin Osipov March 12, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) MySQL and ZFS MC Brown Cheers, Stefan -- *** Sun Microsystems GmbHStefan Hinz Sonnenallee 1Manager Documentation, Database Group 85551 Kirchheim-Heimstetten Phone: +49-30-82702940 Germany Fax: +49-30-82702941 http://www.sun.de/mysql mailto: stefan.h...@sun.com Amtsgericht Muenchen: HRB161028 Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Query Help
I keep hacking at this but haven't been able to get it right yet. I have two tables Userinfo contains a login, User's Name, Group Name Log contains login, host, datetime of last login What I need to do is return user information (userinfo.name/groupname) of users that have logged into a particular host last. i.e. A user can log into any of the hosts but I want to know which one they were on last. Each time they log the username, host and datetime of login are recorded in the log table. I've been able to return the last login to the host but I'm at a loss to get the list of all users that used the host last or all users that have ever logged into that host.
Re: db setup - correction
Peter Brawley wrote: PJ But what about foreign keys? Don't I need that to find the relationships between the books, the authors and the categories? After all, isn't this a relational db? If so, I can't use the default engine (MyISAM) which does not support FK. So, if I have to use foreign keys, I have to change to INNODB, right? Engine choice is another issue. InnoDB properly isolates FK enforcement in the database. The MyISAM tradeoff (for speed) is that you have to implement FK logic in code. Forgive my naiveté, but how do you do that? For normalisation, however, you need the A-B-AB setup no matter what engine you use. What is simpler? BTW, I have set up an EER diagram using MySQL Workbench... and exported this sql script... does it make sense? Strange that it created two instances to create the table book_author??? Maybe a glitch in Workbench -- - -- Table `language` -- - CREATE TABLE IF NOT EXISTS `language` ( `id` INT NOT NULL AUTO_INCREMENT , `language` VARCHAR(7) NOT NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; -- - -- Table `books` -- - CREATE TABLE IF NOT EXISTS `books` ( `id` SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT , `title` VARCHAR(148) NULL , `sub_title` VARCHAR(90) NULL , `descr` TINYTEXT NULL , `comment` TEXT NULL , `bk_cover` VARCHAR(32) NULL , `publish_date` YEAR NULL , `ISBN` BIGINT(13) NULL , `language_id` INT NULL , PRIMARY KEY (`id`) , INDEX `fk_books_language` (`language_id` ASC) , CONSTRAINT `fk_books_language` FOREIGN KEY (`language_id` ) REFERENCES `biblane`.`language` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- - -- Table `authors` -- - CREATE TABLE IF NOT EXISTS `authors` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `first_name` VARCHAR(32) NULL , `last_name` VARCHAR(45) NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; -- - -- Table `categories` -- - CREATE TABLE IF NOT EXISTS `categories` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `category` VARCHAR(70) NOT NULL , PRIMARY KEY (`id`) ) ENGINE = MyISAM; -- - -- Table `sellers` -- - CREATE TABLE IF NOT EXISTS `sellers` ( `id` INT NOT NULL AUTO_INCREMENT , `seller_link` VARCHAR(128) NULL , `seller_img` VARCHAR(45) NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; -- - -- Table `publishers` -- - CREATE TABLE IF NOT EXISTS `publishers` ( `id` INT NOT NULL AUTO_INCREMENT , `publisher` VARCHAR(72) NOT NULL , `pub_link` NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; -- - -- Table `book_author` -- - CREATE TABLE IF NOT EXISTS `book_author` ( `books_id` SMALLINT(4) UNSIGNED NOT NULL , `author_id` INT UNSIGNED NOT NULL , `list_order` TINYINT(1) NULL , PRIMARY KEY (`books_id`, `author_id`) , INDEX `fk_book_author_books` (`books_id` ASC) , INDEX `fk_book_author_authors` (`author_id` ASC) , CONSTRAINT `fk_book_author_books` FOREIGN KEY (`books_id` ) REFERENCES `biblane`.`books` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_book_author_authors` FOREIGN KEY (`author_id` ) REFERENCES `biblane`.`authors` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- - -- Table `book_author` -- - CREATE TABLE IF NOT EXISTS `book_author` ( `books_id` SMALLINT(4) UNSIGNED NOT NULL , `author_id` INT UNSIGNED NOT NULL , `list_order` TINYINT(1) NULL , PRIMARY KEY (`books_id`, `author_id`) , INDEX `fk_book_author_books` (`books_id` ASC) , INDEX `fk_book_author_authors` (`author_id` ASC) , CONSTRAINT `fk_book_author_books` FOREIGN KEY (`books_id` ) REFERENCES `biblane`.`books` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_book_author_authors` FOREIGN KEY (`author_id` ) REFERENCES `biblane`.`authors` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- - -- Table `book_publisher` -- - CREATE TABLE IF NOT EXISTS `book_publisher` ( `books_id` SMALLINT(4) UNSIGNED NOT NULL , `publishers_id` INT NOT NULL , PRIMARY KEY (`books_id`,
Re: db setup - correction
PJ PBEngine choice is another issue. InnoDB properly isolates FK enforcement in the database. The MyISAM tradeoff (for speed) is that you have to implement FK logic in code. PJForgive my naiveté, but how do you do that? You write application code to implement the equivalent of ON DELETE CASCADE | SET NULL | ... when a parent row is deleted. For normalisation, however, you need the A-B-AB setup no matter what engine you use. PJWhat is simpler? Using InnoDB. Workbench gave you a model with two book_authors tables? I suggest asking about it in the Workbench forum. PB - PJ wrote: Peter Brawley wrote: PJ But what about foreign keys? Don't I need that to find the relationships between the books, the authors and the categories? After all, isn't this a relational db? If so, I can't use the default engine (MyISAM) which does not support FK. So, if I have to use foreign keys, I have to change to INNODB, right? Engine choice is another issue. InnoDB properly isolates FK enforcement in the database. The MyISAM tradeoff (for speed) is that you have to implement FK logic in code. Forgive my naiveté, but how do you do that? For normalisation, however, you need the A-B-AB setup no matter what engine you use. What is simpler? BTW, I have set up an EER diagram using MySQL Workbench... and exported this sql script... does it make sense? Strange that it created two instances to create the table book_author??? Maybe a glitch in Workbench -- - -- Table `language` -- - CREATE TABLE IF NOT EXISTS `language` ( `id` INT NOT NULL AUTO_INCREMENT , `language` VARCHAR(7) NOT NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; -- - -- Table `books` -- - CREATE TABLE IF NOT EXISTS `books` ( `id` SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT , `title` VARCHAR(148) NULL , `sub_title` VARCHAR(90) NULL , `descr` TINYTEXT NULL , `comment` TEXT NULL , `bk_cover` VARCHAR(32) NULL , `publish_date` YEAR NULL , `ISBN` BIGINT(13) NULL , `language_id` INT NULL , PRIMARY KEY (`id`) , INDEX `fk_books_language` (`language_id` ASC) , CONSTRAINT `fk_books_language` FOREIGN KEY (`language_id` ) REFERENCES `biblane`.`language` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- - -- Table `authors` -- - CREATE TABLE IF NOT EXISTS `authors` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `first_name` VARCHAR(32) NULL , `last_name` VARCHAR(45) NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; -- - -- Table `categories` -- - CREATE TABLE IF NOT EXISTS `categories` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `category` VARCHAR(70) NOT NULL , PRIMARY KEY (`id`) ) ENGINE = MyISAM; -- - -- Table `sellers` -- - CREATE TABLE IF NOT EXISTS `sellers` ( `id` INT NOT NULL AUTO_INCREMENT , `seller_link` VARCHAR(128) NULL , `seller_img` VARCHAR(45) NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; -- - -- Table `publishers` -- - CREATE TABLE IF NOT EXISTS `publishers` ( `id` INT NOT NULL AUTO_INCREMENT , `publisher` VARCHAR(72) NOT NULL , `pub_link` NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; -- - -- Table `book_author` -- - CREATE TABLE IF NOT EXISTS `book_author` ( `books_id` SMALLINT(4) UNSIGNED NOT NULL , `author_id` INT UNSIGNED NOT NULL , `list_order` TINYINT(1) NULL , PRIMARY KEY (`books_id`, `author_id`) , INDEX `fk_book_author_books` (`books_id` ASC) , INDEX `fk_book_author_authors` (`author_id` ASC) , CONSTRAINT `fk_book_author_books` FOREIGN KEY (`books_id` ) REFERENCES `biblane`.`books` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_book_author_authors` FOREIGN KEY (`author_id` ) REFERENCES `biblane`.`authors` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- - -- Table `book_author` -- - CREATE TABLE IF NOT EXISTS `book_author` ( `books_id` SMALLINT(4) UNSIGNED NOT NULL , `author_id` INT UNSIGNED NOT NULL , `list_order` TINYINT(1) NULL , PRIMARY KEY (`books_id`, `author_id`) , INDEX `fk_book_author_books` (`books_id` ASC) , INDEX `fk_book_author_authors` (`author_id` ASC) , CONSTRAINT
Re: db setup - correction
PJ af.gour...@videotron.ca wrote on 02/10/2009 12:44:04 PM: -- - -- Table `books` -- - CREATE TABLE IF NOT EXISTS `books` ( `id` SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT , `title` VARCHAR(148) NULL , `sub_title` VARCHAR(90) NULL , `descr` TINYTEXT NULL , `comment` TEXT NULL , `bk_cover` VARCHAR(32) NULL , `publish_date` YEAR NULL , `ISBN` BIGINT(13) NULL , `language_id` INT NULL , PRIMARY KEY (`id`) , INDEX `fk_books_language` (`language_id` ASC) , CONSTRAINT `fk_books_language` FOREIGN KEY (`language_id` ) REFERENCES `biblane`.`language` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; May I make one sugggestion? I noticed that the books.id column is defined as SMALLINT UNSIGNED. Unless your database is going to stay quite small, that is really going to limit the number of books. This column is used as a FK in a number of your other tables and if you later on have to change the data type to make it bigger, you'll have to change all the related tables. If I remember correctly, I had to drop all the FK constraints that referenced this column, do the alter tables, and then recreate the FK constraints. Save yourself the hassle and make it at least an Integer, if not a BIGINT (unsigned). http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html Donna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Size limitation of user variable?
I am trying to put the result of a function that returns MEDIUMTEXT into a user variable in my procedure. I haven't attempted to push the limits of the MEDIUMTEXT size, but wonder if the user variable can even handle this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Query Help
Thanks for the input! That is close to what I need, however not exactly. It will give me the last time a user logged into the host in question but I want to prune users who have since logged into a different host. Basically find out how many users are logged into a given host or who are not currently logged in but have not logged into a different host since they logged out of the target. For perspective the host is a wireless access point with fixed clients. A host that hasn't logged in somewhere else we can (somewhat) safely assume is still pointed at the AP in question but is power off, or has its connection interrupted for some other reason. If they have logged in somewhere else we assume that the hardware was moved to a new location and installed there. The query I came up with for some reason doesn't seem to correctly order the dates so if they have logged into the host in question the information is returned. This produces too many results as some of those users have since migrated to a different access point. -Original Message- From: Andrew Wallo [mailto:theme...@microneil.com] Sent: Tuesday, February 10, 2009 12:05 PM To: Ben Wiechman Subject: Re: Query Help Select login, SQL MAX(DateTime) from Log groupby Log.Login ( Gives you the most recent login for a user - on any host. ) Select login, SQLMax(DateTime) from Log groupby Log.Login, Host ORDER BY HOST ASC, DATETIME DESC (Should give you the largest, i.e. most recent, date for each user on each host, oganized by host, in descending login order. I think... ) - Original Message - From: Ben Wiechman b...@meltel.com To: mysql@lists.mysql.com Sent: Tuesday, February 10, 2009 12:31 PM Subject: Query Help I keep hacking at this but haven't been able to get it right yet. I have two tables Userinfo contains a login, User's Name, Group Name Log contains login, host, datetime of last login What I need to do is return user information (userinfo.name/groupname) of users that have logged into a particular host last. i.e. A user can log into any of the hosts but I want to know which one they were on last. Each time they log the username, host and datetime of login are recorded in the log table. I've been able to return the last login to the host but I'm at a loss to get the list of all users that used the host last or all users that have ever logged into that host. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB: Thousands of Tables or Hundreds of Databases?
Martin, I'm guessing you mean 1 database per table type. On Tue, Feb 10, 2009 at 5:17 PM, Martin Gainty mgai...@hotmail.com wrote: I vote for 1 table per TableType this will keep your DB schema consistent with Architecture Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Date: Tue, 10 Feb 2009 11:03:46 -0600 To: mysql@lists.mysql.com From: mo...@fastmail.fm Subject: Re: InnoDB: Thousands of Tables or Hundreds of Databases? At 04:30 AM 2/10/2009, you wrote: Thanks for your comments Mike. The largest table contains 48 columns (objects), the second largest 20 columns (users) and all the rest are less than 10 columns. The instance sizes range from 10MB to 1GB. Transactions and row locking are required. Most queries are updates, followed by writes, then reads (application mostly uses memcached and other forms of caching for reads). I have since thought of having 1 table type per database, resulting in 'only' ~30 databases; this would be 'easier' to maintain, and each database (containing 1 table type) could be optimised for its ratio of reading : writing : updating. However, this approach would require a LOT of work to re-write the application's database layer. What approach would be best? Michael, Does the saying between a rock and a hard place sound familiar? :-) I feel you're going to have to create a test suite to benchmark both solutions thoroughly before you start on the application code. You're going to find pro's and con's with both designs but after benchmarking you're going to know which one performs better both from a speed viewpoint and maintenance viewpoint. The more time you spend testing the design, the more confidence you'll have that it works and the less chance of throwing it away and starting over later on down the road. Then you'll also be able to present to your client some hard facts about each design. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Windows Live™: Keep your life in sync. http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_allup_howitworks_022009
RE: Query Help
Alright to reply to myself I can return the information but have been unable to return the last row... instead it always returns the first row. Tried max, tried min, tried converting the datetime to a timestamp with the same results... mysql SELECT da_userinfo.UserName, da_userinfo.Name, radacct.AcctStartTime as LoginTime, radacct.AcctStopTime as LogoutTime - FROM radacct, da_userinfo - WHERE da_userinfo.Username = radacct.Username - AND `NASIPAddress` = '172.17.6.100' - GROUP BY radacct.`UserName` - HAVING MAX( radacct.`AcctStartTime` ); +--+-+-+ -+ | UserName | Name| LoginTime | LogoutTime | +--+-+-+ -+ | 0010E70A8004 | User1 | 2009-02-09 09:16:24 | 2009-02-10 04:42:08 | | 0010E70A80A8 | User2 | 2009-02-09 14:31:20 | 2009-02-10 09:57:42 | | 0010E70A812D | User3 | 2009-02-09 13:19:51 | 2009-02-10 07:43:08 | | 0010E70A8336 | User4 | 2009-02-05 14:10:41 | 2009-02-10 02:36:41 | | 0010E70A833B | User5 | 2009-02-06 17:45:15 | 2009-02-09 20:27:21 | | 0010E72A2258 | User6| 2009-01-29 13:40:42 | 2009-01-29 14:03:29 | | 0010E72A2957 | User7 | 2009-02-09 15:16:06 | 2009-02-09 16:02:56 | | 0010E72AA91C | User8 | 2009-02-03 09:45:36 | 2009-02-10 07:41:22 | | 0010E7C2F6AF | 07B | 2009-01-28 16:13:44 | 2009-01-28 16:15:43 | +--+-+-+ -+ 16 rows in set (0.00 sec) mysql select UserName,NASIPAddress as Host,AcctStartTime as LoginTime,AcctStopTime as LogoutTime from radacct where UserName = '0010E70A8004'; +--+--+-+-+ | UserName | Host | LoginTime | LogoutTime | +--+--+-+-+ | 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:16:24 | 2009-02-10 04:42:08 | | 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:17:02 | 2009-02-10 04:42:08 | | 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:17:12 | 2009-02-10 04:42:08 | | 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:18:30 | 2009-02-10 04:42:08 | | 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:39:21 | 2009-02-10 04:42:08 | | 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:44:42 | 2009-02-10 04:42:08 | | 0010E70A8004 | 172.17.6.100 | 2009-02-09 10:12:34 | 2009-02-10 04:42:08 | | 0010E70A8004 | 172.17.6.100 | 2009-02-09 10:14:05 | 2009-02-10 04:42:08 | | 0010E70A8004 | 172.17.6.100 | 2009-02-09 10:18:51 | 2009-02-10 04:42:08 | | 0010E70A8004 | 172.17.6.100 | 2009-02-10 04:42:08 | -00-00 00:00:00 | +--+--+-+-+ 10 rows in set (0.00 sec) mysql select UserName,NASIPAddress as Host,AcctStartTime as LoginTime,AcctStopTime as LogoutTime from radacct where UserName = '0010E70A8004' HAVING MAX( AcctStartTime ); +--+--+-+-+ | UserName | Host | LoginTime | LogoutTime | +--+--+-+-+ | 0010E70A8004 | 172.17.6.100 | 2009-02-09 09:16:24 | 2009-02-10 04:42:08 | +--+--+-+-+ 1 row in set (0.00 sec) -Original Message- From: Ben Wiechman [mailto:b...@meltel.com] Sent: Tuesday, February 10, 2009 11:32 AM To: mysql@lists.mysql.com Subject: Query Help I keep hacking at this but haven't been able to get it right yet. I have two tables Userinfo contains a login, User's Name, Group Name Log contains login, host, datetime of last login What I need to do is return user information (userinfo.name/groupname) of users that have logged into a particular host last. i.e. A user can log into any of the hosts but I want to know which one they were on last. Each time they log the username, host and datetime of login are recorded in the log table. I've been able to return the last login to the host but I'm at a loss to get the list of all users that used the host last or all users that have ever logged into that host. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Query Help
Ben Wiechman b...@meltel.com wrote on 02/10/2009 01:30:14 PM: Thanks for the input! That is close to what I need, however not exactly. It will give me the last time a user logged into the host in question but I want to prune users who have since logged into a different host. Basically find out how many users are logged into a given host or who are not currently logged in but have not logged into a different host since they logged out of the target. Figure out the last time each user logged in to any host: SELECT login, MAX(datetime)as lastlogindate FROM Log GROUP BY login So use that query as a derived table to get the rest of the info (untested SQL): SELECT Userinfo.Username, Userinfo.GroupName, Log.hostname, Log.datetime FROM Userinfo INNER JOIN (SELECT login, MAX(datetime)as lastlogindate FROM Log GROUP BY login) AS lastlogin ON Userinfo.login=lastlogin.login INNER JOIN Log ON lastlogin.login=Log.login AND lastlogin.lastlogindate=Log.datetime Hope that helps. Donna -- 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 View
On Tue, Feb 10, 2009 at 1:47 PM, Steven Buehler wrote: From: Jochem van Dieten: What the database will do for you behind the scenes is expand your usage of the view. In effect, the database will replace x with its definition. So your query SELECT a FROM x; gets expanded to: SELECT a FROM (SELECT * FROM y INNER JOIN z ON y.id = z.id); Not sure about the other poster, but this helps explain it to me. If I understand you correctly, if I have multiple tables with many columns in them, but have several queries that need to pull only a few columns from each and put them together, it is probably best to create a view to do this so that I don't have to keep running joins in my queries? No. I am explicitly not saying how you should use views. I am just telling you how they work. But to give you some examples of how you could use views (I am still not saying how you should use views): 1. Use views to replace repetitive elements in queries. If you have lots of queries that perform the same join or filter, put it in a view. That has no semantic value, but you save yourself some typing. 2. Use views to manage permissions. If people have only access to a subset of the data, revoke their permissions on the table and define a view that has exactly the data that they have access to. Then give them permissions on the view. 3. Use views to define new schema elements that have meaning. If you have a normalized schema an invoice may be spread over a dozen tables (customer, invoice, invoiceline, item, price, shipping, payment, account etc.). You can define a view with all the proper joins and filters that groups that together so you get all the data at once. (Some people may argue that this is the same as no. 1, but I think it is an important distinction that the view represents an actual object: an invoice as you print and send them.) 4. your great view usage here Even if I reboot the computer, the view will still be there when it comes back up too? Yes, views are persitent. 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: Query Help
Awesome... that works. Had to add a where clause to limit it to a specific host. The explain for that looks... interesting. Thanks -Original Message- From: ddevaudre...@intellicare.com [mailto:ddevaudre...@intellicare.com] Sent: Tuesday, February 10, 2009 12:47 PM To: Ben Wiechman Cc: mysql@lists.mysql.com Subject: RE: Query Help Ben Wiechman b...@meltel.com wrote on 02/10/2009 01:30:14 PM: Thanks for the input! That is close to what I need, however not exactly. It will give me the last time a user logged into the host in question but I want to prune users who have since logged into a different host. Basically find out how many users are logged into a given host or who are not currently logged in but have not logged into a different host since they logged out of the target. Figure out the last time each user logged in to any host: SELECT login, MAX(datetime)as lastlogindate FROM Log GROUP BY login So use that query as a derived table to get the rest of the info (untested SQL): SELECT Userinfo.Username, Userinfo.GroupName, Log.hostname, Log.datetime FROM Userinfo INNER JOIN (SELECT login, MAX(datetime)as lastlogindate FROM Log GROUP BY login) AS lastlogin ON Userinfo.login=lastlogin.login INNER JOIN Log ON lastlogin.login=Log.login AND lastlogin.lastlogindate=Log.datetime Hope that helps. Donna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to get Error Number and Error Message
I know that all of the prgramming interfaces have the ability to issue a Function Call to get error number for the most recently invoked MySQL function and the error message for the most recently invoked MySQL function such as in C using *mysql_errno() etc. BUT * Surely there is a way to get the SAME info via a SQL statment or function I know one can do a ** select @@error_count as errorcount; to get the COUNT of errors, Surely there is SOMETHING similar to get the latest ERROR NUMBER and latest ERROR MESSAGE, but I have looked everywhere. Has anyone seen a way to do this THANKS a LOT for any and all help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Re: Re: MySQL 5.1.31 PostInstall Script Error
Thanks Claudio,
That helped. I had 32 bit one and was using 64 bit package. Thanks again for
your help.
Bunti.
The only thing I can tell you is you are using 64bit package instead of the
32bit one.
Issue this /usr/bin/isainfo kv
and see if you have 32 or 64 bit architecture os.
In case download the right one!
Solaris 8 (SPARC, 32-bit)
Cheers
Claudio
2009/2/9 bunti bunti1...@cooltoad.com
Yes Claudio,
It is correct. I am using Solaris 8 on Sparc 64bit package.
My server is : SunOS 5.8 Generic_108528-18 sun4u sparc SUNW,Ultra-250
If I am using the wrong package, could you please guide me to the correct one?
Bunti.
Hi, I just try to guess because you don't post much information about
your platform.
My impression is that you are using the wrong binaries,
probably after the common copy procedure of the solaris package manager
the post installation script is the first package-related program.
From what I see here you are using a Solaris 8 on Sparc 64bit Cpu, is
it right?
Claudio
bunti wrote:
Hi
I am doing investigation to determine whether we can use MySQL in our
project.
But I got error message when I try to install MySQL on Solaris server.
Could anyone please help me?
First I uncompressed with gunzip, then I used pkgadd to install, and I got
the following error message in postinstall scripts but still got
installation
successful message.
Server : Solaris 5.8
MySql : 5.1.31
Package used : mysql-5.1.31-solaris8-sparc-64bit.pkg.gz
## Executing postinstall script.
/opt/mysql/mysql/bin/my_print_defaults: syntax error at line 1:
`^?ELF^B^B^A^B+^A^A^AZ\200@' unexpected
/opt/mysql/mysql/bin/mysqld:
@^d}\33...@8^e@!^...@^a@^A^X^A^X^C^D^AX^Y^A^E^Ac]\245c]\245^P^A^Gc]\250^As]\25
0^^^G\330^^\240\260^P^B^Gc}(^As}(^B:
not found
/opt/mysql/mysql/bin/mysqld:
^?ELF^B^B^A^B+^A^A^[\343/usr/lib/sparcv9/ld.so.1e!e!^A^B^C^F: not found
/opt/mysql/mysql/bin/mysqld: ^L^N^O^P^S^U^W^Z^[^]^^^_!#: not found
/opt/mysql/mysql/bin/mysqld:
*+,.013679;=@BCDGIJMNPQRSTUWXY[\]^_`bcfgijlmnorstuwx{}^?\200\202\204\205\207
\210\211\214\215\216\221\224\227\231\232\235\237\240\242\243\245\246\247\250\2
51\253\254\255\257\260\262\263\264\267\270\273\274\276\277\300\302\303\304\305
\306\307\310\312\314\315\316\317\320\321\322\325\326\327\331\332\334\335\336\3
37\340\342\343\345\346\350\353\354\356\360\361\364\366\367\371\375\376\377^A^A
^A^B^A^C^A^E^A^F^A^H^A^J^A^K^A^L^A^P^A^S^A^T^A^U^A^W^A^Y^A^[^A^\^A^^^A^_^A
^A!^A#^A^A^A*^A+^A/^A0^A1^A2^A3^A4^A7^A8^A:^A: not found
/opt/mysql/mysql/bin/mysqld: ^A: not found
/opt/mysql/mysql/bin/mysqld:
^B}^B~^B\202^B\203^B\204^B\205^B\206^B\207^B\210^B\211^B\212^B\215^B\217^B\220
^B\221^B\222^B\223^B\225^B\226^B\230^B\233^B\235^B\237^B\241^B\242^B\243^B\245
^B\246^B\247^B\250^B\252^B\254^B\255^B\260^B\261^B\262^B\263^B\265^B\270^B\272
^B\276^B\277^B\300^B\302^B\304^B\305^B\307^B\312^B\315^B\317^B\320^B\323^B\324
^B\326^B\330^B\331^B\333^B\334^B\335^B\337^B\342^B\344^B\345^B\346^B\351^B\352
^B\354^B\355^B\357^B\361^B\362^B\364^B\365^B\367^B\371^B\372^B\375^B\376^B\377
^C^B^C^C^C^D^C^E^C^F^C^H^C:
not found
/opt/mysql/mysql/bin/mysqld: syntax error at line 8: `)' unexpected
Installation of system tables failed! Examine the logs in
/var/lib/mysql for more information.
You can try to start the mysqld daemon with:
shell /opt/mysql/mysql/bin/mysqld --skip-grant
Broken Pipe
and use the command line tool /opt/mysql/mysql/bin/mysql
to connect to the mysql database and look at the grant tables:
shell /opt/mysql/mysql/bin/mysql -u root mysql
mysql show tables
Try 'mysqld --help' if you have problems with paths. Using --log
gives you a log in /var/lib/mysql that may be helpful.
The latest information about MySQL is available on the web at
http://www.mysql.com/. Please consult the MySQL manual section
'Problems running mysql_install_db', and the manual section that
/opt/mysql/mysql/bin/mysqld:
^Aa^Ab^Ac^Af^Ag^Ah^Ai^Aj^Ak^Al^Am^An^Aq^Ar^At^Av^Aw^Ay^Az^A{^A: not found
describes problems on your OS. Another information source are the
MySQL email archives available at http://lists.mysql.com/.
Please check all of the above before mailing us! And remember, if
you do mail us, you MUST use the /opt/mysql/mysql/scripts/mysqlbug script!
/opt/mysql/mysql/bin/mysqld:
^A}^A\201^A\203^A\204^A\205^A\206^A\207^A\213^A\214^A\217^A\220^A\222^A\223^A\
224^A\226^A\227^A\230^A\233^A\234^A\235^A\237^A\241^A\243^A\244^A\245^A\246^A\
247^A\250^A\252^A\255^A\256^A\260^A\262^A\263^A\264^A\267^A\270^A\271^A\273^A\
275^A\300^A\301^A\302^A\303^A\305^A\306^A\310^A\311^A\312^A\315^A\317^A\320^A\
321^A\322^A\324^A\325^A\330^A\333^A\334^A\335^A\337^A\340^A\341^A\342^A\343^A\
Re: Size limitation of user variable?
On Tue, Feb 10, 2009 at 1:29 PM, Cantwell, Bryan
bcantw...@firescope.com wrote:
I am trying to put the result of a function that returns MEDIUMTEXT into
a user variable in my procedure. I haven't attempted to push the limits
of the MEDIUMTEXT size, but wonder if the user variable can even handle
this?
The REPEAT() function helps here:
mysql set @var := repeat('a', 1024 * 1024);
Query OK, 0 rows affected (0.05 sec)
mysql select length(@var);
+--+
| length(@var) |
+--+
| 1048576 |
+--+
1 row in set (0.01 sec)
So it accepts a mebibyte, let's see if we can notch that up :)
mysql set @var := repeat('a', 1024 * 1024 * 1024);
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql show warnings;
+-+--+--+
| Level | Code | Message
|
+-+--+--+
| Warning | 1301 | Result of repeat() was larger than
max_allowed_packet (16777216) - truncated |
+-+--+--+
1 row in set (0.00 sec)
--
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Re: Re: MySQL 5.1.31 PostInstall Script Error
My pleasure, Bunti!
let me know if you need any help, I worked ten years on Sun Solaris, and six
of these with a rambling MySQL on it,
I always compiled my MySQL release, and enjoyed so much Solaris, it is the
most stable operating system I ever worked with,
very good choice!
Cheers
Claudio
2009/2/10 bunti bunti1...@cooltoad.com
Thanks Claudio,
That helped. I had 32 bit one and was using 64 bit package. Thanks again
for
your help.
Bunti.
The only thing I can tell you is you are using 64bit package instead of
the
32bit one.
Issue this /usr/bin/isainfo –kv
and see if you have 32 or 64 bit architecture os.
In case download the right one!
Solaris 8 (SPARC, 32-bit)
Cheers
Claudio
2009/2/9 bunti bunti1...@cooltoad.com
Yes Claudio,
It is correct. I am using Solaris 8 on Sparc 64bit package.
My server is : SunOS 5.8 Generic_108528-18 sun4u sparc SUNW,Ultra-250
If I am using the wrong package, could you please guide me to the correct
one?
Bunti.
Hi, I just try to guess because you don't post much information about
your platform.
My impression is that you are using the wrong binaries,
probably after the common copy procedure of the solaris package manager
the post installation script is the first package-related program.
From what I see here you are using a Solaris 8 on Sparc 64bit Cpu, is
it right?
Claudio
bunti wrote:
Hi
I am doing investigation to determine whether we can use MySQL in our
project.
But I got error message when I try to install MySQL on Solaris
server.
Could anyone please help me?
First I uncompressed with gunzip, then I used pkgadd to install, and
I got
the following error message in postinstall scripts but still got
installation
successful message.
Server : Solaris 5.8
MySql : 5.1.31
Package used : mysql-5.1.31-solaris8-sparc-64bit.pkg.gz
## Executing postinstall script.
/opt/mysql/mysql/bin/my_print_defaults: syntax error at line 1:
`^?ELF^B^B^A^B+^A^A^AZ\200@' unexpected
/opt/mysql/mysql/bin/mysqld:
@^d}\33...@8^e@!^...@^a@
^A^X^A^X^C^D^AX^Y^A^E^Ac]\245c]\245^P^A^Gc]\250^As]\25
0^^^G\330^^\240\260^P^B^Gc}(^As}(^B:
not found
/opt/mysql/mysql/bin/mysqld:
^?ELF^B^B^A^B+^A^A^[\343/usr/lib/sparcv9/ld.so.1e!e!^A^B^C^F: not
found
/opt/mysql/mysql/bin/mysqld: ^L^N^O^P^S^U^W^Z^[^]^^^_!#: not found
/opt/mysql/mysql/bin/mysqld:
*+,.013679;=@BCDGIJMNPQRSTUWXY[\]^_`bcfgijlmnorstuwx{}^?\200\202\204\205\207
\210\211\214\215\216\221\224\227\231\232\235\237\240\242\243\245\246\247\250\2
51\253\254\255\257\260\262\263\264\267\270\273\274\276\277\300\302\303\304\305
\306\307\310\312\314\315\316\317\320\321\322\325\326\327\331\332\334\335\336\3
37\340\342\343\345\346\350\353\354\356\360\361\364\366\367\371\375\376\377^A^A
^A^B^A^C^A^E^A^F^A^H^A^J^A^K^A^L^A^P^A^S^A^T^A^U^A^W^A^Y^A^[^A^\^A^^^A^_^A
^A!^A#^A^A^A*^A+^A/^A0^A1^A2^A3^A4^A7^A8^A:^A: not found
/opt/mysql/mysql/bin/mysqld: ^A: not found
/opt/mysql/mysql/bin/mysqld:
^B}^B~^B\202^B\203^B\204^B\205^B\206^B\207^B\210^B\211^B\212^B\215^B\217^B\220
^B\221^B\222^B\223^B\225^B\226^B\230^B\233^B\235^B\237^B\241^B\242^B\243^B\245
^B\246^B\247^B\250^B\252^B\254^B\255^B\260^B\261^B\262^B\263^B\265^B\270^B\272
^B\276^B\277^B\300^B\302^B\304^B\305^B\307^B\312^B\315^B\317^B\320^B\323^B\324
^B\326^B\330^B\331^B\333^B\334^B\335^B\337^B\342^B\344^B\345^B\346^B\351^B\352
^B\354^B\355^B\357^B\361^B\362^B\364^B\365^B\367^B\371^B\372^B\375^B\376^B\377
^C^B^C^C^C^D^C^E^C^F^C^H^C:
not found
/opt/mysql/mysql/bin/mysqld: syntax error at line 8: `)' unexpected
Installation of system tables failed! Examine the logs in
/var/lib/mysql for more information.
You can try to start the mysqld daemon with:
shell /opt/mysql/mysql/bin/mysqld --skip-grant
Broken Pipe
and use the command line tool /opt/mysql/mysql/bin/mysql
to connect to the mysql database and look at the grant tables:
shell /opt/mysql/mysql/bin/mysql -u root mysql
mysql show tables
Try 'mysqld --help' if you have problems with paths. Using --log
gives you a log in /var/lib/mysql that may be helpful.
The latest information about MySQL is available on the web at
http://www.mysql.com/. Please consult the MySQL manual section
'Problems running mysql_install_db', and the manual section that
/opt/mysql/mysql/bin/mysqld:
^Aa^Ab^Ac^Af^Ag^Ah^Ai^Aj^Ak^Al^Am^An^Aq^Ar^At^Av^Aw^Ay^Az^A{^A: not
found
describes problems on your OS. Another information source are the
MySQL email archives available at http://lists.mysql.com/.
Please check all of the above before mailing us! And remember, if
you do mail us, you MUST use the /opt/mysql/mysql/scripts/mysqlbug
script!
Re: db setup - correction
ddevaudre...@intellicare.com wrote: PJ af.gour...@videotron.ca wrote on 02/10/2009 12:44:04 PM: -- - -- Table `books` -- - CREATE TABLE IF NOT EXISTS `books` ( `id` SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT , `title` VARCHAR(148) NULL , `sub_title` VARCHAR(90) NULL , `descr` TINYTEXT NULL , `comment` TEXT NULL , `bk_cover` VARCHAR(32) NULL , `publish_date` YEAR NULL , `ISBN` BIGINT(13) NULL , `language_id` INT NULL , PRIMARY KEY (`id`) , INDEX `fk_books_language` (`language_id` ASC) , CONSTRAINT `fk_books_language` FOREIGN KEY (`language_id` ) REFERENCES `biblane`.`language` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; May I make one sugggestion? I noticed that the books.id column is defined as SMALLINT UNSIGNED. Unless your database is going to stay quite small, that is really going to limit the number of books. This column is used as a FK in a number of your other tables and if you later on have to change the data type to make it bigger, you'll have to change all the related tables. If I remember correctly, I had to drop all the FK constraints that referenced this column, do the alter tables, and then recreate the FK constraints. Save yourself the hassle and make it at least an Integer, if not a BIGINT (unsigned). http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html Donna Thanks for the suggestion. I thought that 32 thousand would probably be enough for books on Egyptology... :-) But you're probably right. I don't imagine the site will ever reach that many, but.. And I'm still struggling to understand how I'm going to deal with inputting all the information ... it looks like I'll have to do some kind of php function to enter all the information in one page and then retrieve it to display it on another... -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.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: mysql optimization tips
Hi, On Tue, Feb 10, 2009 at 9:25 AM, monloi perez mlp_fol...@yahoo.com wrote: Hi All, I'm not sure if this question is fine, I'm new to the list and I just have one very important question. Can anyone help me suggest the right optimization for our company's server. I suggest High Performance MySQL, Second Edition :-) There's no way to really give any good advice without knowing what you use the server for. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- 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 optimization tips
On Tue, Feb 10, 2009 at 7:23 PM, Baron Schwartz ba...@xaprb.com wrote: Hi, On Tue, Feb 10, 2009 at 9:25 AM, monloi perez mlp_fol...@yahoo.com wrote: Hi All, I'm not sure if this question is fine, I'm new to the list and I just have one very important question. Can anyone help me suggest the right optimization for our company's server. I suggest High Performance MySQL, Second Edition :-) There's no way to really give any good advice without knowing what you use the server for. I enthusiastically second that recommendation -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- 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 optimization tips
At 06:44 PM 2/10/2009, Michael Dykman wrote: On Tue, Feb 10, 2009 at 7:23 PM, Baron Schwartz ba...@xaprb.com wrote: Hi, On Tue, Feb 10, 2009 at 9:25 AM, monloi perez mlp_fol...@yahoo.com wrote: Hi All, I'm not sure if this question is fine, I'm new to the list and I just have one very important question. Can anyone help me suggest the right optimization for our company's server. I suggest High Performance MySQL, Second Edition :-) There's no way to really give any good advice without knowing what you use the server for. I enthusiastically second that recommendation You can read a few pages from High Performance MySQL 1st edition at http://books.google.ca/books?id=sgMvu2uZXlsCprintsec=frontcoverdq=mysql+high+performance#PPP1,M1 Google Books also has excerpts from other MySQL books. Mike -- 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 optimization tips
You can read a few pages from High Performance MySQL 1st edition at http://books.google.ca/books?id=sgMvu2uZXlsCprintsec=frontcoverdq=mysql+high+performance#PPP1,M1 Google Books also has excerpts from other MySQL books. You can also get the Query Optimization chapter online as a PDF from http://www.highperfmysql.com Why didn't I think of that before :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL Magazine -- Winter 2009 issue released
Hey everyone, This is one of the largest issues so far. In addition, it has some of the best content we have ever had. The articles are all in-depth with some exciting new information: * *Introduction to XtraDB*: an overview of the new XtraDB storage engine along with benchmarks and information about planned future improvements * *Changes in the MySQL DBA and Developer Exams for Version 5.1*: what’s coming in the new exams covering MySQL Server 5.1 * *Covering Indexes in MySQL*: how to create indexes that optimize query execution * *PBXT’s Coder’s Guide*: going in-depth on how you can work with the code for the new PBXT storage engine * *Coding Corner*: Peter’s regular column continues his look at transaction time validity It is available for download, along with all previous back issues, at http://www.mysqlzine.net. thanks, Keith -- Editor MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to get Error Number and Error Message 2nd Try
I know that all of the prgramming interfaces have the ability to issue a Function Call to get error number for the most recently invoked MySQL function and the error message for the most recently invoked MySQL function such as in C using *mysql_errno() etc. BUT * Surely there is a way to get the SAME info via a SQL statment or function I know one can do a ** select @@error_count as errorcount; to get the COUNT of errors, Surely there is SOMETHING similar to get the latest ERROR NUMBER and latest ERROR MESSAGE, but I have looked everywhere. Has anyone seen a way to do this THANKS a LOT for any and all help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
