Re: I forgot the admin password
Hello Nils, sorry if I sounded perhaps unfriendly. That was not against you. Imagine that many people read the list (+20.000), some which read this which might take your word for granted and just do as you told ;-). No worries, I should have stated that --in this particular case-- (to me) this would be a solution for the problem. Obviously, in a working environment, this wouldn't be a correct answers and I stand fully corrected on that :-) With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connect from oracle to MYSQL.
what about : http://dev.mysql.com/doc/mysql/en/dsn-on-unix.html 2005/9/22, Ananda Kumar [EMAIL PROTECTED]: Hi Pooly, I know your busy, but please help me, i need to submit this by today for the testing team. If you dont mind can you please guide to any url or documentation where i can find the complete steps to connect from oracle 8.1.7.4 on sun 5.8 to mysql 4.1.14 on Lunix fedaro with innodb engine Thanks in advance. regards anandkl On 9/21/05, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Pooly, Thanks for the help. I did that , where to find odbc.ini file. This pkg does not create this file. regards anandkl On 9/21/05, Pooly [EMAIL PROTECTED] wrote: 2005/9/21, Ananda Kumar [EMAIL PROTECTED]: Hi Friends, Can you please help me on this. regards anandkl -- Forwarded message -- From: Ananda Kumar [EMAIL PROTECTED] Date: Sep 20, 2005 9:46 PM Subject: connect from oracle to MYSQL. To: mysql@lists.mysql.com Hi All, Can you please help me in connecting from oracle database to mysql database. I am trying to setup the hetrogenious service provided by oracle, but i am not able to complete all the steps. I am failing at this step *cp MyODBC-3.51.06-sun-solaris2.8-sparc/libmyodbc3* lib* # *mv etc/odbc.ini etc/odbc.ini.backup* # *cp MyODBC-3.51.06-sun-solaris2.8-sparc /odbc.ini etc* # *cd lib* # *rm libmyodbc3.so libmyodbc3_r.so* # *ln -s libmyodbc3-3.51.06.so http://libmyodbc3-3.51.06.so/ libmyodbc3.so * # *ln -s libmyodbc3_r- 3.51.06.so http://3.51.06.so/ libmyodbc3_r.so* ** *I am not seeing libmyodbc files in MyODBC-3.51.06-sun-solaris2.8-sparcdirectory. I am seeing only this files * MyODBC-3.51.10-sun-solaris2.8-sparc.pkg README. #pkgadd -d MyODBC-3.51.10-sun-solaris2.8-sparc.pkg #more README ? I am using solaris2.8 for oracle database and mysql 4.1 on linux fedaro. regards anandkl -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connect from oracle to MYSQL.
Hi Pooly, I tried this also, its not working. Also the odbc.ini file created by the package is empty. I found the same entires in /usr/etc/myodbc3-32.template file. So i copied the contents of this and created an odbc.ini file. After doing this isql is not connecting to MYSQL database.. regards anandkl On 9/22/05, Pooly [EMAIL PROTECTED] wrote: what about : http://dev.mysql.com/doc/mysql/en/dsn-on-unix.html 2005/9/22, Ananda Kumar [EMAIL PROTECTED]: Hi Pooly, I know your busy, but please help me, i need to submit this by today for the testing team. If you dont mind can you please guide to any url or documentation where i can find the complete steps to connect from oracle 8.1.7.4http://8.1.7.4on sun 5.8 to mysql 4.1.14 on Lunix fedaro with innodb engine Thanks in advance. regards anandkl On 9/21/05, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Pooly, Thanks for the help. I did that , where to find odbc.ini file. This pkg does not create this file. regards anandkl On 9/21/05, Pooly [EMAIL PROTECTED] wrote: 2005/9/21, Ananda Kumar [EMAIL PROTECTED]: Hi Friends, Can you please help me on this. regards anandkl -- Forwarded message -- From: Ananda Kumar [EMAIL PROTECTED] Date: Sep 20, 2005 9:46 PM Subject: connect from oracle to MYSQL. To: mysql@lists.mysql.com Hi All, Can you please help me in connecting from oracle database to mysql database. I am trying to setup the hetrogenious service provided by oracle, but i am not able to complete all the steps. I am failing at this step *cp MyODBC-3.51.06-sun-solaris2.8-sparc/libmyodbc3* lib* # *mv etc/odbc.ini etc/odbc.ini.backup* # *cp MyODBC-3.51.06-sun-solaris2.8-sparc /odbc.ini etc* # *cd lib* # *rm libmyodbc3.so libmyodbc3_r.so* # *ln -s libmyodbc3-3.51.06.so http://libmyodbc3-3.51.06.so http://libmyodbc3-3.51.06.so/ libmyodbc3.so * # *ln -s libmyodbc3_r- 3.51.06.so http://3.51.06.so http://3.51.06.so/ libmyodbc3_r.so* ** *I am not seeing libmyodbc files in MyODBC-3.51.06-sun-solaris2.8-sparcdirectory. I am seeing only this files * MyODBC-3.51.10-sun-solaris2.8-sparc.pkg README. #pkgadd -d MyODBC-3.51.10-sun-solaris2.8-sparc.pkg #more README ? I am using solaris2.8 for oracle database and mysql 4.1 on linux fedaro. regards anandkl -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
good database design
I need links about good database design information for high loaded web sites... regards, okan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
I need links about good database design information for high loaded web sites... A database design should start with the logical data-related requirements, not with performance related issues. IMO, of course. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: good database design
I disagree completely. I prefer to have regard to the statement of requirement, which in this case is a concern over performance. If following conventional design rules creates performance issues, then performance related issues come first when considering design. In times long since gone by (I am showing my age here) client side message response times were written into contracts. Design had to take into account performance issues. With very high loaded web-sites as in this case, a little time spent on lateral thinking can make a big difference and save costs in the long run, and keeps customers happy. Tim Hayes MYdbPAL - www.it-map.com -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: 22 September 2005 09:02 To: mysql@lists.mysql.com Subject: Re: good database design I need links about good database design information for high loaded web sites... A database design should start with the logical data-related requirements, not with performance related issues. IMO, of course. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
Hi, Please reply to the list and not to me personally only. I want to explain my condition. I have a web site that habe 110onlne users at same time. But cpu usage is 2.00/2.00 (p4 3.0ghzHT) I think my database design is horrible because of this high cpu load. Did you do an analysis to come to this conclusion? Is it really MySQL that's hogging your CPU? Did you analyse what queries were bringing the server down? SO I want to learn something about good database design. Can you suggest any thing to me? Read a book, any book, that describes the normal forms. After that, analyse your queries and their plans and see if any indices are needed. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
Hello Tim, I disagree completely. I prefer to have regard to the statement of requirement, which in this case is a concern over performance. If following conventional design rules creates performance issues, then performance related issues come first when considering design. Given that the OP did not state that there were any issues with an existing website, logical requirements come first. Period. No discussion ;) In times long since gone by (I am showing my age here) client side message response times were written into contracts. Design had to take into account performance issues. With very high loaded web-sites as in this case, a little time spent on lateral thinking can make a big difference and save costs in the long run, and keeps customers happy. It also depends heavily on the tasks of the application. In any case, if this is a read/write application, I would still say that logical requirements should go first. If this is a read only application, do whatever you want. Given that - usually - data is pretty much the most important thing inside an application, it should be logically correct. Both you and me know that any denormalization or other performance tweaks can result into inconsistent data and should be avoided like the plague if possible. If this is the customers own server and everything is logical correct but there are some performance problems, I'd say: throw more hardware at it. Obviously, this makes sense --after-- tweaks to the database engine caching etc etc... Hardware is cheap(ish). If you can control it, do so. Denormalization is dangerous. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
sorry for wrong reply:( And Did you do an analysis to come to this conclusion? Is it really MySQL that's hogging your CPU? Did you analyse what queries were bringing the server down? The senteces above are my big problem. How can I be sure about the quesries making my server down Please, any link, any info, any word is important forme. I can't find the right start point. Thanks OKAN - Original Message - From: Martijn Tonies [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, September 22, 2005 11:25 AM Subject: Re: good database design Hi, Please reply to the list and not to me personally only. I want to explain my condition. I have a web site that habe 110onlne users at same time. But cpu usage is 2.00/2.00 (p4 3.0ghzHT) I think my database design is horrible because of this high cpu load. Did you do an analysis to come to this conclusion? Is it really MySQL that's hogging your CPU? Did you analyse what queries were bringing the server down? SO I want to learn something about good database design. Can you suggest any thing to me? Read a book, any book, that describes the normal forms. After that, analyse your queries and their plans and see if any indices are needed. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
Tim Hayes wrote: I disagree completely. I prefer to have regard to the statement of requirement, which in this case is a concern over performance. If following conventional design rules creates performance issues, then performance related issues come first when considering design. - personally, I would consider integrity, and then reliability, above performance. But then 80% of any performance hit is in the application code. Design a database that gives you confidence in the data it stores first and foremost. - ian -- +---+ | Ian Sales Database Administrator | | | | All your database are belong to us | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: good database design
My 2 cents.. Before you actually start worrying about the performance tuning of database parameters or hardware required for the DB, you should make sure that you have designed the database properly by taking care of all aspects like normalisation, denormalisation (??). If you don't take care of these logical design aspects in the early stages properly, these things will prove you very costly in the long run. Th easy and recommended way to do it is .Draw an E-R diagram .Do any normalization. .Identify proper datatypes for the table creation. .Identify and add proper indexes. .And now actually you should start worrying abt the DB Tuning and harware requirements. sujay -Original Message- From: Ian Sales (DBA) [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 2:17 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: good database design Tim Hayes wrote: I disagree completely. I prefer to have regard to the statement of requirement, which in this case is a concern over performance. If following conventional design rules creates performance issues, then performance related issues come first when considering design. - personally, I would consider integrity, and then reliability, above performance. But then 80% of any performance hit is in the application code. Design a database that gives you confidence in the data it stores first and foremost. - ian -- +---+ | Ian Sales Database Administrator | | | | All your database are belong to us | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: good database design
This is an interesting subject area. In a data warehousing environment, one tends to adopt table structures such as snowflake layouts which lead to improved performance. Createing a perfect normalised database design may well lead to performance issues. The more joins you have, by far the worse the performance. You may need to consider horizontal or vertical table splits. You may need to consider replicating certain data in child tables to avoid joins. I am not saying you do not need to carry out data analysis and gain a full and first hand understanding of the data structures. It is just that when it comes to online performance, sometimes you have to break the rules. -Original Message- From: Sujay Koduri [mailto:[EMAIL PROTECTED] Sent: 22 September 2005 09:58 To: Ian Sales (DBA); [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: good database design My 2 cents.. Before you actually start worrying about the performance tuning of database parameters or hardware required for the DB, you should make sure that you have designed the database properly by taking care of all aspects like normalisation, denormalisation (??). If you don't take care of these logical design aspects in the early stages properly, these things will prove you very costly in the long run. Th easy and recommended way to do it is .Draw an E-R diagram .Do any normalization. .Identify proper datatypes for the table creation. .Identify and add proper indexes. .And now actually you should start worrying abt the DB Tuning and harware requirements. sujay -Original Message- From: Ian Sales (DBA) [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 2:17 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: good database design Tim Hayes wrote: I disagree completely. I prefer to have regard to the statement of requirement, which in this case is a concern over performance. If following conventional design rules creates performance issues, then performance related issues come first when considering design. - personally, I would consider integrity, and then reliability, above performance. But then 80% of any performance hit is in the application code. Design a database that gives you confidence in the data it stores first and foremost. - ian -- +---+ | Ian Sales Database Administrator | | | | All your database are belong to us | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
Martijn Tonies wrote: Given that the OP did not state that there were any issues with an existing website, logical requirements come first. Period. No discussion ;) Logical requirements may come first, but may be overruled later by requirements caused by performance issues or system limitations. If your logic designed a large type of primary key, you may run into problems with InnoDB tables. The PK is stored with the data and other indexes refer to the PK (and not directly to the data as is the case with MyISAM). So a large PK will increase the table size (data + indexes) and may thus lead to performance issues when the database does not fit in memory anymore, or when the buffers,etc. hit the memory limits on your system. A very complex model may lead to queries with more than 31 JOINs, which is not possible with MySQL without modifying the source and recompiling it (and even then the limit seems to be 63). In any case, if this is a read/write application, I would still say that logical requirements should go first. If this is a read only application, do whatever you want. Logic may come first in the time line, but may be overruled by other requirements. Finding people who celebrate their birthday today (or this week) may become a very slow task if you only use a logical data field. Denormalisation by using extra fields for particular tasks is a completely logical solution in this case. If this is the customers own server and everything is logical correct but there are some performance problems, I'd say: throw more hardware at it. Obviously, this makes sense --after-- tweaks to the database engine caching etc etc... Hardware is cheap(ish). If you can control it, do so. Throwing hardware at it is not always a good solution. You know better than that. The customer better not find out that the application could very well run on the original server with a few tweaks as you call them, and that he appears to have lost a lot of money for new hardware and all the time needed to get the new server running in the configuration that you suggested... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
Hi, Given that the OP did not state that there were any issues with an existing website, logical requirements come first. Period. No discussion ;) Logical requirements may come first, but may be overruled later by requirements caused by performance issues or system limitations. Which is what I said :-) If your logic designed a large type of primary key, you may run into problems with InnoDB tables. The PK is stored with the data and other indexes refer to the PK (and not directly to the data as is the case with MyISAM). So a large PK will increase the table size (data + indexes) and may thus lead to performance issues when the database does not fit in memory anymore, or when the buffers,etc. hit the memory limits on your system. A very complex model may lead to queries with more than 31 JOINs, which is not possible with MySQL without modifying the source and recompiling it (and even then the limit seems to be 63). Obviously, the MySQL guys should be bugged about this... In any case, if this is a read/write application, I would still say that logical requirements should go first. If this is a read only application, do whatever you want. Logic may come first in the time line, but may be overruled by other requirements. Finding people who celebrate their birthday today (or this week) may become a very slow task if you only use a logical data field. Denormalisation by using extra fields for particular tasks is a completely logical solution in this case. If this is the customers own server and everything is logical correct but there are some performance problems, I'd say: throw more hardware at it. Obviously, this makes sense --after-- tweaks to the database engine caching etc etc... Hardware is cheap(ish). If you can control it, do so. Throwing hardware at it is not always a good solution. You know better than that. The customer better not find out that the application could very well run on the original server with a few tweaks as you call them, and that he appears to have lost a lot of money for new hardware and all the time needed to get the new server running in the configuration that you suggested... Did you read my paragraph about throwing hardware? No offence, but I stated several times that the logical data requirements should come first, in design. After that, tweak the server, after that, if possible, throw more hardware at it. Now, if this doesn't cut it, you might get into denormalization or other things that make your application run faster... With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: good database design
This is an interesting subject area. In a data warehousing environment, one tends to adopt table structures such as snowflake layouts which lead to improved performance. Createing a perfect normalised database design may well lead to performance issues. If this is the case, go bug the database vendors :-) ... they should give us systems that work properly ... The more joins you have, by far the worse the performance. You may That's a pretty bold statement... need to consider horizontal or vertical table splits. You may need to consider replicating certain data in child tables to avoid joins. I am not saying you do not need to carry out data analysis and gain a full and first hand understanding of the data structures. It is just that when it comes to online performance, sometimes you have to break the rules. But still: logical first, performance later... If at all. I once joined a team that had a running Oracle database and an application on top of it. We were having performance problems and there was the 2 seconds of max response time requirement in the contract. We tweaked Oracle (not particularly the fastest beast on the block), we threw hardware at it. Both options worked... for a while. Next, we denormalized, I believe, 2 joins. Yes, it worked on fetching data - the response time was alright. But it complicated our application, the database design and the stored procedures using it... Not a particular pleasant experience. Then again... years later, I realized that the design should have been different (better logical structure) and these problems would have been avoided... Pity we couldn't do that part again... Learned a lot though. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: good database design
This is what I am also saying. The effects of a bad logical DB design will effect you the most only in the long term. In the earlier stages you always trust your own design and always look for additional h/w resources to improve the performance. But in the long term you will realize that there is something other than adding h/w you have to do. That's when we actually realise the mistakes we have done in logical design phase. suhay -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 3:13 PM To: mysql@lists.mysql.com Subject: Re: good database design This is an interesting subject area. In a data warehousing environment, one tends to adopt table structures such as snowflake layouts which lead to improved performance. Createing a perfect normalised database design may well lead to performance issues. If this is the case, go bug the database vendors :-) ... they should give us systems that work properly ... The more joins you have, by far the worse the performance. You may That's a pretty bold statement... need to consider horizontal or vertical table splits. You may need to consider replicating certain data in child tables to avoid joins. I am not saying you do not need to carry out data analysis and gain a full and first hand understanding of the data structures. It is just that when it comes to online performance, sometimes you have to break the rules. But still: logical first, performance later... If at all. I once joined a team that had a running Oracle database and an application on top of it. We were having performance problems and there was the 2 seconds of max response time requirement in the contract. We tweaked Oracle (not particularly the fastest beast on the block), we threw hardware at it. Both options worked... for a while. Next, we denormalized, I believe, 2 joins. Yes, it worked on fetching data - the response time was alright. But it complicated our application, the database design and the stored procedures using it... Not a particular pleasant experience. Then again... years later, I realized that the design should have been different (better logical structure) and these problems would have been avoided... Pity we couldn't do that part again... Learned a lot though. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: good database design
I agree totaly to what Sujay Koduri writes : http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html My 2 cents.. Before you actually start worrying about the performance tuning of database parameters or hardware required for the DB, you should make sure that you have designed the database properly by taking care of all aspects like normalisation, denormalisation (??). If you don't take care of these logical design aspects in the early stages properly, these things will prove you very costly in the long run. Th easy and recommended way to do it is .Draw an E-R diagram .Do any normalization. .Identify proper datatypes for the table creation. .Identify and add proper indexes. .And now actually you should start worrying abt the DB Tuning and harware requirements. sujay -Original Message- From: Ian Sales (DBA) [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 2:17 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: good database design Tim Hayes wrote: I disagree completely. I prefer to have regard to the statement of requirement, which in this case is a concern over performance. If following conventional design rules creates performance issues, then performance related issues come first when considering design. - personally, I would consider integrity, and then reliability, above performance. But then 80% of any performance hit is in the application code. Design a database that gives you confidence in the data it stores first and foremost. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
embedded server on win CE
I have searched the internet and scanned the manual, but have not found any pointers to using mysql or its embedded server on a windows CE device. A few quick questions which might seem to be trivial to you but will help me go ahead: 1. Can I use embedded server in a win CE device application? If yes, which binary should I use? 2. In order to connect to a mysql server on desktop from a device using TCP\IP, what do I need to have on the device -- which connectors/ binaries etc? 3. Pointers to help documents or white papers that highlight this, if any . Thanks!! - Yahoo! for Good Click here to donate to the Hurricane Katrina relief effort.
Fulltext behavior in 3.23.58
... this one is peculiar, and I *do* hope there's a workaround:
Using MySQL 3.23.58 and cannot upgrade. When running SELECT * FROM tblname
WHERE match(a) against('very_common_word') LIMIT 1 the whole table is scanned
just to give me no results at all. The very common word *does* appear, and
appears in more than 50% of the lines.
However, I would think that there is no need to scan the whole table just for
that. In MySQL4 the result comes out much quicker, but still very slowly.
To contrast, if I use a_non_existent_word instead of a_very_common_word I
get 0 rows immeidately. If I use an_existing_but_not_common_word I get 1 row
quickly: not immeidately as a non-existing row, but not so slowly as a very
common word.
Questions:
1) Why the different behavior between MySQL 3 and 4 ?
2) How to circumnavigate in MySQL 3?
tia,
Nitzan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext behavior in 3.23.58
Nitzan,
In the unlikely event that you can recompile but not upgrade, you could add
your common words to the full text stopword list in myisam/ft_static.c and
rebuild mysql.
Otherwise, this might be helpful
http://lists.mysql.com/mysql/132649
Andy
-Original Message-
From: nitzan shaked [mailto:[EMAIL PROTECTED]
Sent: 22 September 2005 14:57
To: mysql@lists.mysql.com
Subject: Fulltext behavior in 3.23.58
... this one is peculiar, and I *do* hope there's a workaround:
Using MySQL 3.23.58 and cannot upgrade. When running SELECT * FROM
tblname WHERE match(a) against('very_common_word') LIMIT 1 the whole
table is scanned just to give me no results at all. The very common word
*does* appear, and appears in more than 50% of the lines.
However, I would think that there is no need to scan the whole table just
for that. In MySQL4 the result comes out much quicker, but still very
slowly.
To contrast, if I use a_non_existent_word instead of
a_very_common_word I get 0 rows immeidately. If I use
an_existing_but_not_common_word I get 1 row quickly: not immeidately as
a non-existing row, but not so slowly as a very common word.
Questions:
1) Why the different behavior between MySQL 3 and 4 ?
2) How to circumnavigate in MySQL 3?
tia,
Nitzan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM to InnoDB
Hey all, I've got a production database that made up of all MyISAM tables. I'd like to change some of the more heavily written to tables to InnoDB to take advantage of the record level locking and thus improve write performance of our applications. I currently have a second db server that is replicating from the current production system but not in production yet. I'd like to try to convert it to InnoDB. MySQL version is 4.0.16. It it as symple as just issuing the modify table query or are there problems I should be aware of when doing this? Also are there known problems replicating from A - B - A (circular replication) when A had Table1= InnoDB and B has Table1=MyISAM? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM to InnoDB
You will need to make sure you have innodb configured in the my.cnf file and you have enough space built for it in the shared table space. InnoDB also needs it's own memory pool, so make sure you give it enough memory. For day to day issues there is no problem doing innodb/myisam replication, with a couple of small caveats... an ALTER TABLE would replicate and thus... may change the table type from myisam to innodb or vice versa depending on which server the ALTER TABLE came from. To go with that the original conversion from myisam to InnoDB would also need to be done in such a way as to not be replicated. Remember that an ALTER TABLE that could have an impact could be as simple as adding or dropping an index... although usually very simple alter table statements like that can be done without defining the table engine, some GUIs may however insert that for you on even the simplest ALTER TABLE commands. Best Regards, Bruce On Sep 22, 2005, at 7:59 AM, Jeff wrote: Hey all, I've got a production database that made up of all MyISAM tables. I'd like to change some of the more heavily written to tables to InnoDB to take advantage of the record level locking and thus improve write performance of our applications. I currently have a second db server that is replicating from the current production system but not in production yet. I'd like to try to convert it to InnoDB. MySQL version is 4.0.16. It it as symple as just issuing the modify table query or are there problems I should be aware of when doing this? Also are there known problems replicating from A - B - A (circular replication) when A had Table1= InnoDB and B has Table1=MyISAM? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Circular Replication
On Sep 21, 2005, at 5:23 AM, Jeff wrote: I am interested in how you go about doing a delayed replication to protect against operator error. We've already fallen victim to that situation here. The long story short is we use the fact that MySQL has the ability to run the SQL thread and the IO thread of replication separately, and control them individually. In practice we use cron and a whole bunch of scripts to stop the I/O thread (the one reading from the master) most of the time, and manage when the SQL thread replicates... eg at 4:00 cron stops the SQL thread. At 4:01 we start the I/O thread (this can read a lot of changes very quickly from the master, so only need a short time to catch up with all the changes). At 4:05 we stop the I/ O thread. Then we wait a few minutes to give ourselves a buffer... then finally at 4:15 we start the SQL thread and repeat the cycle every two hours. The upshot is at the small end we are 10 minutes behind (the time between we stop I/O at 4:05 and the time when we start SQL at 4:15), and at the long end we are 2 hours behind (at 4:07 for example the last query that the SQL thread could have executed came from the master at 2:05). Our scripts are a little more complicated to marry into our monitoring system without setting off alerts that replication has stopped and so on (and of course the machine that runs this speaks to many masters using many instances of MySQL, so we need to manage this for every instance of MySQL). We also manage things to allow an emergency stop by having the scripts do an existence check on a specific file, and if the file isn't there don't start any replication processes. We then have a stop script which tells the instances to stop whatever they are doing and deletes the file. At that point replication can't resume until we replace the file manually - we tie that emergency script to a TCP port and hey presto... in the event of an emergency all someone needs to do is hit the right tcp port on the server (telnet to it, hit it with a browser, anything that will cause the port to see some activity) and all the replication comes to a stop. Also as part of our 2 hourly cycle we do a lot of binary log flushing on the slave and the masters, so if we ever need to roll back we can roll back to a specific point in time and only have to deal with fixing problems in the logs form that point in time onwards. if an operator error gets by before we can stop we can go to yesterdays backup and only execute those binary logs from before the incident, and then deal with the issue in question. This process has reduced our downtime in the event of a total database corruption from four hours to recover from yesterdays data and be missing everything since, to 30 minutes and be only missing the data since the last 2 hourly roll over. And it doesn't take long to dump the last set of binary logs to a text file, find and fix/ remove the corrupting command and apply that whole log into the database, effectively giving us almost zero lost data and back online in no time (although when clients are screaming even 30 minutes feels like an eternity). This is all of course so much better than the four hour downtime we had before this system. And there are side benefits... for example backups are easier to do because the data isn't being changed except for a few minutes every 2 hours. Instead of co-ordinating timing scripts and locking tables and doing dumps and so on we can do simple file system duplication of the data directories. Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Web-based reporting tool?
This seems to be a common question, but answers aren't that common... What are people using as a web-based reporting tool? I'd like to add a user-facing interface to allow users to run pre-defined reports (in which they may need to enter data, like Start Date and End Date) against their data. I've done a bunch of Google searching, and there's always Crystal Reports. That is cost-prohibitive currently, though I was discussing this with them. However, there are issues where we may want to split our current database server into a larger number of servers, and then the license issues crop up again. What else is good and reliable? I'm looking at QLR Manager, looked at Agata, downloaded a number of other programs to find out they are intended for running on the desktop (as opposed to being a web-based app). I'm not overly concerned with language, either, although the database server is currently a Windows box. I'm using PHP for some stuff, but could run Java, etc. if needed for the right software. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Memory usage question
We very recently began replicating data from a master to a slave and since doing that we've noticed that most of the RAM in the machine 2 GB is being used with very little (relatively) free (12MB - 50MB). I've looked at several forums and have done some web searches to see if there was any mention of this but haven't seen anything. Is this a normal occurrence? Is there some kind of tuning that I can do to free up more memory? Thanks for listening. Steve
RE: Web-based reporting tool?
-Original Message- From: Warrick Wilson [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 12:09 To: mysql@lists.mysql.com Subject: Web-based reporting tool? This seems to be a common question, but answers aren't that common... What are people using as a web-based reporting tool? I'd like to add a user-facing interface to allow users to run pre-defined reports (in which they may need to enter data, like Start Date and End Date) against their data. I've done a bunch of Google searching, and there's always Crystal Reports. That is cost-prohibitive currently, though I was discussing this with them. However, there are issues where we may want to split our current database server into a larger number of servers, and then the license issues crop up again. What else is good and reliable? I'm looking at QLR Manager, looked at Agata, downloaded a number of other programs to find out they are intended for running on the desktop (as opposed to being a web-based app). I'm not overly concerned with language, either, although the database server is currently a Windows box. I'm using PHP for some stuff, but could run Java, etc. if needed for the right software. Thanks. If they're pre defined reports that just require date ranges or simple arguments then why not simply build a php website that Has a these reports on them. You can use simple forms to collect the criteria from the user and then generate the report. I use this extensively in my company for distributing information. No licensing to worry about and and exporting the reports to a Spreadsheet with a download button is fairly easy as well. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory usage question
In the last episode (Sep 22), Blumenkrantz, Steve said: We very recently began replicating data from a master to a slave and since doing that we've noticed that most of the RAM in the machine 2 GB is being used with very little (relatively) free (12MB - 50MB). I've looked at several forums and have done some web searches to see if there was any mention of this but haven't seen anything. Is this a normal occurrence? Is there some kind of tuning that I can do to free up more memory? It's normal. Free memory is wasted memory. The OS will use what memory is not allocated by processes as disk cache. A better indicator that you are low on memory is high swap usage and swapin/swapouts per second. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Web-based reporting tool? (UNCLASSIFIED)
Classification: UNCLASSIFIED Caveats: NONE Well this is probably getting off topic, but I didn't start it ;). And I know there's a lot of expertise on the list... So, one of the items mentioned by Warrick dealt with snip [ pre-defined reports (in which they may need to enter data, like Start Date and End Date) against their data.] end snip We use php for this functionality, but it's kind of klugey (sp?) -- the user has 6 pull down menus to deal with ( month, date, year, for both the start and end dates). Anybody know of a sexier way to implement this functionality? Michael Kidwell -Original Message- From: Jeff [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 1:56 PM To: mysql@lists.mysql.com Subject: RE: Web-based reporting tool? -Original Message- From: Warrick Wilson [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 12:09 To: mysql@lists.mysql.com Subject: Web-based reporting tool? This seems to be a common question, but answers aren't that common... What are people using as a web-based reporting tool? I'd like to add a user-facing interface to allow users to run pre-defined reports (in which they may need to enter data, like Start Date and End Date) against their data. I've done a bunch of Google searching, and there's always Crystal Reports. That is cost-prohibitive currently, though I was discussing this with them. However, there are issues where we may want to split our current database server into a larger number of servers, and then the license issues crop up again. What else is good and reliable? I'm looking at QLR Manager, looked at Agata, downloaded a number of other programs to find out they are intended for running on the desktop (as opposed to being a web-based app). I'm not overly concerned with language, either, although the database server is currently a Windows box. I'm using PHP for some stuff, but could run Java, etc. if needed for the right software. Thanks. If they're pre defined reports that just require date ranges or simple arguments then why not simply build a php website that Has a these reports on them. You can use simple forms to collect the criteria from the user and then generate the report. I use this extensively in my company for distributing information. No licensing to worry about and and exporting the reports to a Spreadsheet with a download button is fairly easy as well. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Classification: UNCLASSIFIED Caveats: NONE -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Web-based reporting tool?
If you're considering Java, how about looking at Jasper? http://jasperreports.sourceforge.net/ Wiebe -Original Message- From: Warrick Wilson [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 9:09 AM To: mysql@lists.mysql.com Subject: Web-based reporting tool? This seems to be a common question, but answers aren't that common... What are people using as a web-based reporting tool? I'd like to add a user-facing interface to allow users to run pre-defined reports (in which they may need to enter data, like Start Date and End Date) against their data. I've done a bunch of Google searching, and there's always Crystal Reports. That is cost-prohibitive currently, though I was discussing this with them. However, there are issues where we may want to split our current database server into a larger number of servers, and then the license issues crop up again. What else is good and reliable? I'm looking at QLR Manager, looked at Agata, downloaded a number of other programs to find out they are intended for running on the desktop (as opposed to being a web-based app). I'm not overly concerned with language, either, although the database server is currently a Windows box. I'm using PHP for some stuff, but could run Java, etc. if needed for the right software. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM to InnoDB
-Original Message- From: Bruce Dembecki [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 11:41 To: Jeff Cc: mysql@lists.mysql.com Subject: Re: MyISAM to InnoDB You will need to make sure you have innodb configured in the my.cnf file and you have enough space built for it in the shared table space. InnoDB also needs it's own memory pool, so make sure you give it enough memory. For day to day issues there is no problem doing innodb/myisam replication, with a couple of small caveats... an ALTER TABLE would replicate and thus... may change the table type from myisam to innodb or vice versa depending on which server the ALTER TABLE came from. To go with that the original conversion from myisam to InnoDB would also need to be done in such a way as to not be replicated. True, is there a way to tell a slave to not replicate certain queries like alter table or would I need to get creative and stop replication and all writes to the main database, then issue the alter table statement, then restart replication with a set global slave_sql_skip_counter=1 so that it skips the alter statemtent? Remember that an ALTER TABLE that could have an impact could be as simple as adding or dropping an index... although usually very simple alter table statements like that can be done without defining the table engine, some GUIs may however insert that for you on even the simplest ALTER TABLE commands. If I understand what you're saying here, some MySQL front end gui software will add onto any Alter table statement you submit a statement specifying the type of table like myisam automatically. So if you used that gui and tried to issue an alter statement to say add an index to a InnoDB table it would add on a table type = MyISAM and cause havoc? Normally I don't rely on gui tools to do my serious quiries like altering tables or adding indexes etc. I'll do them logging directly into mysql server on the linux box itself. In this case there shouldn't be a problem correct? Best Regards, Bruce On Sep 22, 2005, at 7:59 AM, Jeff wrote: Hey all, I've got a production database that made up of all MyISAM tables. I'd like to change some of the more heavily written to tables to InnoDB to take advantage of the record level locking and thus improve write performance of our applications. I currently have a second db server that is replicating from the current production system but not in production yet. I'd like to try to convert it to InnoDB. MySQL version is 4.0.16. It it as symple as just issuing the modify table query or are there problems I should be aware of when doing this? Also are there known problems replicating from A - B - A (circular replication) when A had Table1= InnoDB and B has Table1=MyISAM? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Avg row length is varying a lot from oracle to MySQL
hi ,, we are converting our oracle DB to MySQL DB. One problem i see is that the abg row length in MySQL is much higher compared to that of Oracle. In oracle it is around 180 bytes and in MySQL it is around 686 bytes. So as a result, MySQL is taking more space to store the same number of records. Can someone please explain me if this is the intended behaviour or i am missing out something. I am also including the o/p of desc table_name of the same table on both the databases. This is a bit urgent. So any help is greatly appreciated. ID NOT NULL VARCHAR2(50) H0 NUMBER H1 NUMBER H2 NUMBER H3 NUMBER H4 NUMBER H5 NUMBER H6 NUMBER H7 NUMBER H8 NUMBER H9 NUMBER H10 NUMBER H11 NUMBER H12 NUMBER H13 NUMBER H14 NUMBER H15 NUMBER H16 NUMBER H17 NUMBER H18 NUMBER H19 NUMBER H20 NUMBER H21 NUMBER H22 NUMBER H23 NUMBER D1 NUMBER D2 NUMBER D3 NUMBER D4 NUMBER D5 NUMBER D6 NUMBER D7 NUMBER D8 NUMBER D9 NUMBER D10 NUMBER D11 NUMBER D12 NUMBER D13 NUMBER D14 NUMBER D15 NUMBER D16 NUMBER D17 NUMBER D18 NUMBER D19 NUMBER D20 NUMBER D21 NUMBER D22 NUMBER D23 NUMBER D24 NUMBER D25 NUMBER D26 NUMBER D27 NUMBER D28 NUMBER D29 NUMBER D30 NUMBER D31 NUMBER D32 NUMBER D33 NUMBER D34 NUMBER D35 NUMBER D36 NUMBER D37 NUMBER D38 NUMBER D39 NUMBER D40 NUMBER UPDATE_SECS NUMBER B_UPDATE_SECS NUMBER B1 NUMBER B2 NUMBER B3 NUMBER B4 NUMBER B5 NUMBER B6 NUMBER B7 NUMBER B8 NUMBER B9 NUMBER B10 NUMBER DATE_ADDED DATE DATE_MODIFIED DATE UPDATED DATE ORIGINAL VARCHAR2(50) COUNT NUMBER(10) IPADDR VARCHAR2(16) HI NUMBER IM VARCHAR2(15) ST VARCHAR2(20) BS NUMBER USERID NUMBER(10) PAGE NUMBER URL VARCHAR2(150) DESCRIPTION VARCHAR2(100) TAG VARCHAR2(4) NH NUMBER REFRESH NUMBER POPULATE VARCHAR2(6) LERY VARCHAR2(1) LIST VARCHAR2(1) LITE VARCHAR2(1) STING_ID VARCHAR2(20) YN VARCHAR2(1) RY_ID VARCHAR2(9) RATED VARCHAR2(1) CREATED DATE In mysql +---+-+--+-+ ---+---+ | Field | Type | Null | Key | Default | Extra | +---+-+--+-+ ---+---+ | id | varchar(50) | | PRI | | | | H0 | tinyint(3) unsigned | YES | | NULL | | | H1 | tinyint(3) unsigned | YES | | NULL | | | H2 | tinyint(3) unsigned | YES | | NULL | | | H3 | tinyint(3) unsigned | YES | | NULL | | | H4 | tinyint(3) unsigned | YES | | NULL | | | H5 | tinyint(3) unsigned | YES | | NULL | | | H6 | tinyint(3) unsigned | YES | | NULL | | | H7 | tinyint(3) unsigned | YES | | NULL | | | H8 | tinyint(3) unsigned | YES | | NULL | | | H9 | tinyint(3) unsigned | YES | | NULL | | | H10 | tinyint(3) unsigned | YES | | NULL | | | H11 | tinyint(3) unsigned | YES | | NULL | | | H12 | tinyint(3) unsigned | YES | | NULL | | | H13 | tinyint(3) unsigned | YES | | NULL | | | H14 | tinyint(3) unsigned | YES | | NULL | | | H15 | tinyint(3) unsigned | YES | | NULL | | | H16 | tinyint(3) unsigned | YES | | NULL | | | H17 | tinyint(3) unsigned | YES | | NULL | | | H18 | tinyint(3) unsigned | YES | | NULL | | | H19 | tinyint(3) unsigned | YES | | NULL | | | H20 | tinyint(3) unsigned | YES | | NULL | | | H21 | tinyint(3) unsigned | YES | | NULL | | | H22 | tinyint(3) unsigned | YES | | NULL | | | H23 | tinyint(3) unsigned | YES | | NULL | | | D1 | tinyint(4) | YES | | NULL | | | D2 | tinyint(4) | YES | | NULL | | | D3 | tinyint(4) | YES | | NULL | | | D4 | tinyint(4) | YES | | NULL | | | D5 | tinyint(4) | YES | | NULL | | | D6 | tinyint(4) | YES | | NULL | | | D7 | tinyint(4) | YES | | NULL | | | D8 | tinyint(4) | YES | | NULL | | | D9 | tinyint(4) | YES | | NULL | | | D10 | tinyint(4) | YES | | NULL | | | D11 | tinyint(4) | YES | | NULL | | | D12 | tinyint(4) | YES | | NULL | | | D13 | tinyint(4) | YES | | NULL | | | D14 | tinyint(4) | YES | | NULL | | | D15 | tinyint(4) | YES | | NULL | | | D16 | tinyint(4) | YES | | NULL | | | D17 | tinyint(4) | YES | | NULL | | | D18 | tinyint(4) | YES | | NULL | | | D19 | tinyint(4) | YES | | NULL | | | D20 | tinyint(4) | YES | | NULL | | | D21 | tinyint(4) | YES | | NULL | | | D22 | tinyint(4) | YES | | NULL | | | D23 | tinyint(4) | YES | | NULL | | | D24 | tinyint(4) | YES | | NULL | | | D25 | tinyint(4) | YES | | NULL | | | D26 | tinyint(4) | YES | | NULL | | | D27 | tinyint(4) | YES | | NULL | | | D28 | tinyint(4) | YES | | NULL | | | D29 | tinyint(4) | YES | | NULL | | | D30 | tinyint(4) | YES | | NULL | | | D31 | tinyint(4) | YES | | NULL | | | D32 | tinyint(4) | YES | | NULL | | | D33 | tinyint(4) | YES | | NULL | | | D34 | tinyint(4) | YES | | NULL | | | D35 | tinyint(4) | YES | | NULL | | | D36 | tinyint(4) | YES | | NULL | | | D37 | tinyint(4) | YES | | NULL | | | D38 | tinyint(4) | YES | | NULL | | | D39 | tinyint(4) | YES | | NULL | | | D40 | tinyint(4) | YES | | NULL | | | UPDATE_SECS | int(10)
RE: Avg row length is varying a lot from oracle to MySQL
-Original Message- From: Sujay Koduri [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 15:23 To: mysql@lists.mysql.com Subject: Avg row length is varying a lot from oracle to MySQL hi ,, we are converting our oracle DB to MySQL DB. One problem i see is that the abg row length in MySQL is much higher compared to that of Oracle. In oracle it is around 180 bytes and in MySQL it is around 686 bytes. So as a result, MySQL is taking more space to store the same number of records. Can someone please explain me if this is the intended behaviour or i am missing out something. I am also including the o/p of desc table_name of the same table on both the databases. Probably do to the way the two database store data and how much space they reserve for specific column types. For a way to calculate row size see this link. Relize you must also calculate the size of all indexes. http://dev.mysql.com/doc/mysql/en/storage-requirements.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Avg row length is varying a lot from oracle to MySQL
Each row in the table takes around 600 bytes, taking every thing into consideration and assuming every field is used to its maximum bytes. But the major portion of this 600 bytes are composed of varchar's (100 + 150 + 50 + 16 + 50 + 20 + 9..) Out of these 400 bytes we generally use only 40 to 50 bytes. Most of them are reserved for future uses. So strictly speaking even including the space taken by the indexes, the avg length should not come more than 250 bytes. sujay -Original Message- From: Jeff [mailto:[EMAIL PROTECTED] Sent: Friday, September 23, 2005 1:12 AM To: mysql@lists.mysql.com Subject: RE: Avg row length is varying a lot from oracle to MySQL -Original Message- From: Sujay Koduri [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 15:23 To: mysql@lists.mysql.com Subject: Avg row length is varying a lot from oracle to MySQL hi ,, we are converting our oracle DB to MySQL DB. One problem i see is that the abg row length in MySQL is much higher compared to that of Oracle. In oracle it is around 180 bytes and in MySQL it is around 686 bytes. So as a result, MySQL is taking more space to store the same number of records. Can someone please explain me if this is the intended behaviour or i am missing out something. I am also including the o/p of desc table_name of the same table on both the databases. Probably do to the way the two database store data and how much space they reserve for specific column types. For a way to calculate row size see this link. Relize you must also calculate the size of all indexes. http://dev.mysql.com/doc/mysql/en/storage-requirements.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM to InnoDB
On Sep 22, 2005, at 11:46 AM, Jeff wrote: True, is there a way to tell a slave to not replicate certain queries like alter table or would I need to get creative and stop replication and all writes to the main database, then issue the alter table statement, then restart replication with a set global slave_sql_skip_counter=1 so that it skips the alter statemtent? On a single connection use: SET SQL_LOG_BIN = 0 It's a connection variable, the default is 1, which means queries altering the data get written to the bin log... changing this to 0 means data altering commands from this specific connection do not get written to the binary log... It's best not to leave a connection lying around with this setting because it's the sort of thing you forget about and later end up with data inconsistencies. However short term use by turning it off, doing your thing, and turning it on again usually works without trouble... eg: SET SQL_LOG_BIN = 0; ALTER TABLE some stuff here; SET SQL_LOG_BIN = 1; Not all users have permission to issue such a command. If I understand what you're saying here, some MySQL front end gui software will add onto any Alter table statement you submit a statement specifying the type of table like myisam automatically. So if you used that gui and tried to issue an alter statement to say add an index to a InnoDB table it would add on a table type = MyISAM and cause havoc? Normally I don't rely on gui tools to do my serious quiries like altering tables or adding indexes etc. I'll do them logging directly into mysql server on the linux box itself. In this case there shouldn't be a problem correct? Some GUI's take simple steps and write them out into their full long SQL format... whereas adding a table's engine or type to an alter table is optional in MySQL, officially it is suppose to be there... so some GUI's put it there... typically if you haven't told it to change the table type it will just use whatever table type it is now... but the end result in the binary log will still go to the other server and potentially change something there. There shouldn't be a problem using the mysql command line client... but I'm going to emphasize shouldn't here... when you have two different table types on master and slave you need to be **really** sure you don't mess that up. Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM to InnoDB
Jeff wrote: True, is there a way to tell a slave to not replicate certain queries like alter table or would I need to get creative and stop replication and all writes to the main database, then issue the alter table statement, then restart replication with a set global slave_sql_skip_counter=1 so that it skips the alter statemtent? There's a much easier way - issue the statement SET SQL_LOG_BIN = 0; before issuing any ALTER TABLE statements. This will cause all statements for the duration of that session to not be written to the binlog. See http://dev.mysql.com/doc/mysql/en/set-option.html for more information. You may want to look at a few pages in the docs, for information about InnoDB / MyISAM differences. If your code relies on one table type (or features only available with that table type, like transactions for InnoDB or SELECT COUNT(*) for MyISAM), you may run into some problems. Here are a couple links to try to help. http://dev.mysql.com/doc/mysql/en/innodb-and-mysql-replication.html http://dev.mysql.com/doc/mysql/en/converting-tables-to-innodb.html http://dev.mysql.com/doc/mysql/en/innodb-auto-increment-column.html http://dev.mysql.com/doc/mysql/en/replication-features.html (towards the bottom it talks about replication of transactions and MyISAM engine) Best Regards, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP - Group_Concat broken after update
Bump! Ed Reed [EMAIL PROTECTED] 9/21/05 2:09:58 PM I just upgraded from 4.1.11 to 4.1.14a and now Group_Concat returns garbage. In the previous version Group_Concat returned a text string and after the upgrade they are returning a blob.
RE: Avg row length is varying a lot from oracle to MySQL
Each row in the table takes around 600 bytes, taking every thing into consideration and assuming every field is used to its maximum bytes. But the major portion of this 600 bytes are composed of varchar's (100 + 150 + 50 + 16 + 50 + 20 + 9..) Out of these 400 bytes we generally use only 40 to 50 bytes. Most of them are reserved for future uses. So strictly speaking even including the space taken by the indexes, the avg length should not come more than 250 bytes. If you have a varchar(50) but usually only use 10 in those fields MySQL still counts the unused 40 for the total byte count of the row, so you must count them. An empty varchar(50) field still uses 50 bytes. Also, I believe text and blob fields are always counted as 255 bytes regardless of your settings. Ordered indexes are 10bytes per column (in the index) per row. I believe there is also some paging overhead so generally take your calculated row size and multiply by 1.1. sujay -Original Message- From: Jeff [mailto:[EMAIL PROTECTED] Sent: Friday, September 23, 2005 1:12 AM To: mysql@lists.mysql.com Subject: RE: Avg row length is varying a lot from oracle to MySQL -Original Message- From: Sujay Koduri [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 15:23 To: mysql@lists.mysql.com Subject: Avg row length is varying a lot from oracle to MySQL hi ,, we are converting our oracle DB to MySQL DB. One problem i see is that the abg row length in MySQL is much higher compared to that of Oracle. In oracle it is around 180 bytes and in MySQL it is around 686 bytes. So as a result, MySQL is taking more space to store the same number of records. Can someone please explain me if this is the intended behaviour or i am missing out something. I am also including the o/p of desc table_name of the same table on both the databases. Probably do to the way the two database store data and how much space they reserve for specific column types. For a way to calculate row size see this link. Relize you must also calculate the size of all indexes. http://dev.mysql.com/doc/mysql/en/storage-requirements.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM to InnoDB
-Original Message- From: Devananda [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 16:14 To: Jeff Cc: mysql@lists.mysql.com Subject: Re: MyISAM to InnoDB Jeff wrote: True, is there a way to tell a slave to not replicate certain queries like alter table or would I need to get creative and stop replication and all writes to the main database, then issue the alter table statement, then restart replication with a set global slave_sql_skip_counter=1 so that it skips the alter statemtent? There's a much easier way - issue the statement SET SQL_LOG_BIN = 0; before issuing any ALTER TABLE statements. This will cause all statements for the duration of that session to not be written to the binlog. See http://dev.mysql.com/doc/mysql/en/set-option.html for more information. First off, thanks for the help to you and Bruce both! When you say here, for the duration of that session does that mean that only queries I issue with my connection skip the binlog? Or do all queries during that time skip the binlog. In other words, when I SET SQL_LOG_BIN = 0; should I first stop all applications writing to the database to prevent missing data in the slaves? You may want to look at a few pages in the docs, for information about InnoDB / MyISAM differences. If your code relies on one table type (or features only available with that table type, like transactions for InnoDB or SELECT COUNT(*) for MyISAM), you may run into some problems. Here are a couple links to try to help. http://dev.mysql.com/doc/mysql/en/innodb-and-mysql-replication .html http://dev.mysql.com/doc/mysql/en/converting-tables-to-innodb.html http://dev.mysql.com/doc/mysql/en/innodb-auto-increment-column.html http://dev.mysql.com/doc/mysql/en/replication-features.html (towards the bottom it talks about replication of transactions and MyISAM engine) Best Regards, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1 on 64bit Fedora Core 3
Hello, I have a Fedora Core 3 installation on an Intel 64bit processor. I need PHP MySQL 4.1. The OS installed is the 64bit version of FC3. PHP 4.3.9 is installed by FC3, but this has been upgraded to 4.3.11 and works OK. I have upgraded MySQL from 3.23 to 4.1 and I have installed the shared library. (MySQL-shared-4.1.14-0.glibc23.x86_64.rpm) The problem is that PHP complains that it cannot find 'libmysqlclient.so.10'. This file should be in the shared library, but it is not. Can someone please help? The 32bit installations contain the required files, but the 64bit version will only look in the 64bit folders. thx, Paul Taylor
Re: MyISAM to InnoDB
Jeff wrote: -Original Message- From: Devananda [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 16:14 To: Jeff Cc: mysql@lists.mysql.com Subject: Re: MyISAM to InnoDB Jeff wrote: True, is there a way to tell a slave to not replicate certain queries like alter table or would I need to get creative and stop replication and all writes to the main database, then issue the alter table statement, then restart replication with a set global slave_sql_skip_counter=1 so that it skips the alter statemtent? There's a much easier way - issue the statement SET SQL_LOG_BIN = 0; before issuing any ALTER TABLE statements. This will cause all statements for the duration of that session to not be written to the binlog. See http://dev.mysql.com/doc/mysql/en/set-option.html for more information. First off, thanks for the help to you and Bruce both! You're quite welcome, Jeff :) When you say here, for the duration of that session does that mean that only queries I issue with my connection skip the binlog? Or do all queries during that time skip the binlog. In other words, when I SET SQL_LOG_BIN = 0; should I first stop all applications writing to the database to prevent missing data in the slaves? It only affects that connection. Bruce wrote a response at about the same time I did; his covers this topic as well. SQL_LOG_BIN is a session variable, meaning that it only affects the current session (connection). So, any applications running at the same time will not be affected by a change to this variable, and if you close your client and reconnect, you will have to set the variable again. As Bruce suggested, it's best to set it only when you need it and unset it immediately afterwards (as a precaution against operator error, not because it affects the server). I do want to point out that while the commands you issue (after setting SQL_LOG_BIN to 0) will not be written to the binlog (thus will not run on any slave reading from this server), they may affect other running processes on the server. If, for example, you run an ALTER TABLE on a table currently in MyISAM format, the table will be locked and all processes running on that server that read from / write to that table will wait until that ALTER finishes. Setting SQL_LOG_BIN to 0 doesn't affect this in any way - it _only_ affects whether statements from that specific session are recorded in the binary log. Side question - you've stated that you are planning to migrate to InnoDB, but you haven't said anything to the list about how much data you have. Just be aware that it can take a lot of time and disk space for MySQL to transfer all your data from one format to the other (of course depending on how much data you have) and if anything goes wrong during that time, the results will probably not be what you expect, or want. I would advise you to at least investigate an alternate approach if you have a lot of data - take the server you are going to migrate out of the 'cluster' and make sure it is not processing any data / no clients are connecting to it; dump all your data to text files, preferably separating your data definition statements (ie CREATE TABLE statements) from your actual data; modify the CREATE statements to specify the InnoDB engine; lastly load all the data from the text files into MySQL, and bring this server back into the 'cluster'. If you don't have a _lot_ of data, then it may not be worth all that work. Of course, a lot is subjective; I'd say, based purely on my own experiences with this, that if you are going to migrate 1G of data, you will probably be better off exporting / alter the text files / importing. If you have 10's or 100's of G of data, I would strongly recommend that you do it this way. And regardless of how much data you have, it is, IMHO, safer to export/import. If you're interested, I would be happy to talk more about a method to automate this process over many tables / lots of data. Best Regards, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lost connection to MySQL server during query when calling stored procedure
Greg 'groggy' Lehey wrote: On Thursday, 22 September 2005 at 17:06:32 +1200, Jasper Bryant-Greene wrote: Jasper Bryant-Greene wrote: Sometimes when I execute this stored procedure ... I get the error Lost connection to MySQL server during query. I am using MySQL 5.0.12-beta-log on Gentoo Linux x86. This only seems to happen when accessing MySQL from PHP's MySQLi API, not when accessing it using the mysql command-line client. Is this a bug I should report to MySQL or to PHP? I'm not able to test other APIs at the moment. If the server dies, that's a MySQL problem. Take a look at http://bugs.mysql.com/ for information on how to report it. In particular, the server logs should give some information. Thing is, even though I get that error, the server keeps running and accepting other connections and responding to queries fine. My server log shows absolutely nothing after server startup, no matter how many times that stored procedure fails. I'm about to recompile with the debug USE flag (I'm on Gentoo) to try to track down the problem; once I've done that I'll file a bug. -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_real_escape_string question
hi... from the docs... it appears the the mysql_real_escape_string function (from php) is used to backslash a set of chars in the string for insertion into the mysql db... however, given that the '/' is mysql, vs ansi, i was wondering if there's a way to force it to use the ansi standard ' instead of the /. this is in a php app, and i'd like to be as standard as possible in the event i need to change dbs.. thanks -bruce [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_real_escape_string question
bruce wrote: from the docs... it appears the the mysql_real_escape_string function (from php) is used to backslash a set of chars in the string for insertion into the mysql db... however, given that the '/' is mysql, vs ansi, i was wondering if there's a way to force it to use the ansi standard ' instead of the /. this is in a php app, and i'd like to be as standard as possible in the event i need to change dbs.. Assuming you're using a database abstraction layer if you're worried about needing to change DBs, it should handle the different quoting formats between DBs. i.e. when you are using MySQL the layer should use mysql_real_escape_string; if you are using another DB it should use that DB's function. This was answered for you on the PHP list. -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1 on 64bit Fedora Core 3
The OS installed is the 64bit version of FC3. I have upgraded MySQL from 3.23 to 4.1 and I have installed the shared library. (MySQL-shared-4.1.14-0.glibc23.x86_64.rpm) The problem is that PHP complains that it cannot find 'libmysqlclient.so.10'. I think you need the shared-compat libraries - it appears that they are no provided on mysql.com As an alternative, consider using Fedora Core 4 which has Mysql 4.1 in there natively. The following might also be helpful (not all info in there is Opteron-specific): http://hashmysql.org/index.php?title=Opteron_HOWTO Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1 on 64bit Fedora Core 3
The following might also be helpful (not all info in there is Opteron-specific): http://hashmysql.org/index.php?title=Opteron_HOWTO Argh - should have read my own entry on that wiki page. If you use PHP v4.3 (as installed with FC3) then it requires the MySQL v3.23 interface libraries (aka libmysqlclient.so.10). By removing the pre-installed MySQL it will remove these libraries. As of 31 May 2005 For some reason these were not compiled into the MySQL 4.1.12 shared library RPMs as downloaded from the MySQL site. However, they are available from any Fedora mirror in the FC4 testing area. You will need to install a package called mysqlclient10-3.23.58-6.x86_64.rpm - I found such a package at my local mirror here: http://mirror.pacific.net.au/linux/redhat/fedora/test/3.92/x86_64/os/Fedora/ RPMS/mysqlclient10-3.23.58-6.x86_64.rpm Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_real_escape_string question
bruce [EMAIL PROTECTED] wrote on 09/22/2005 09:15:56 PM: hi... from the docs... it appears the the mysql_real_escape_string function (from php) is used to backslash a set of chars in the string for insertion into the mysql db... however, given that the '/' is mysql, vs ansi, i was wondering if there's a way to force it to use the ansi standard ' instead of the /. this is in a php app, and i'd like to be as standard as possible in the event i need to change dbs.. thanks -bruce [EMAIL PROTECTED] The function mysql_real_escape() is a MySQL-specific function. It will not properly escape an input string for any other RDMBS. You will need to replace it (and several other PHP functions that all start with mysql_) if you migrate your code to use any other database system. I don't see how, unless you create or use a database communications abstraction layer, you can write generic database code in PHP. ODBC is just one example of a database abstraction layer. If you wrote your code to use nothing but ODBC functions (no RDBMS-specific SQL code), then pointing your code to use any other database server could be as simple as telling ODBC to use the driver for the other system. Shifting from MySQL-specific code to ODBC (or any other abstraction layer)-specific code will be a major undertaking and should be performed as early in the design and development phases as possible. The later you wait, the harder the shift will be. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
SQL help: Updating Strange Chrs.
Hi, I have an odd situation where I was handed just bad data, and while I have cleaned it up to the best of my ability one hurdle remains. I have a situation where I have the following example in a field: Canna ÒBengalÒ. Note the odd chrs Ò in the field ? I need to get rid of those, my question is HOW. And I have thousands of rows like this and its just ONE field that's the stumbling block. Any suggestions ? M i l e s. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
connectin from ORACLE to MYSQL
Hi Friends, I have set up all the drivers and configured the Hetrogenous service in oracle and set all the relevent parameter. Now when i am connecting to MYSQL from ORACLE i am getting the below error select counter from ebay_stats@test where rownum 2 * ERROR at line 1: ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Generic Connectivity Using ODBC][H006] The init parameter HS_FDS_SHAREABLE_NAME is not set. Please set it in initorasid.ora file. ORA-02063: preceding 2 lines from TEST I have set the HS_FDS_SHAREABLE_NAME also in the initmyodbc3.ora file located in $ORACLE_HOME/hs/admin path HS_FDS_CONNECT_INFO = myodbc3 HS_FDS_TRACE_LEVEL = off HS_FDS_TRACE_FILE_NAME = log file name HS_FDS_SHAREABLE_NAME = /usr/local/lib/libmyodbc3.so Please help me. regards anandkl
Re: SQL help: Updating Strange Chrs.
m i l e s wrote: Hi, I have an odd situation where I was handed just bad data, and while I have cleaned it up to the best of my ability one hurdle remains. I have a situation where I have the following example in a field: Canna ÒBengalÒ. Note the odd chrs Ò in the field ? I need to get rid of those, my question is HOW. And I have thousands of rows like this and its just ONE field that's the stumbling block. Any suggestions ? M i l e s. replace() eg: update some_table set some_field = replace( my_field, Ò, O ); -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au
