How does one transpose a group of columns into rows?
I have a table: Id |path1 | display| value 1 | ostype | os|windows 1 | ostype | ver |NT4 2 | ostype | os | linux 2 | ostype | ver | RHEL 5.4 That I would like to tranform into Id | os| ver 1 |windows | NT4 2| linux |RHEL 5.4 Thank you, Raymond smime.p7s Description: S/MIME cryptographic signature
Re: corrupted tables
From: "Steve Edberg" <[EMAIL PROTECTED]> Sometimes I see that some tables from my database get corrupted. Why does this happpen and how can I avoid it? It is not hard to go and use "repair table" but it seems that in this way some records could be deleted and this is not ok. If I want to have a very secure database, can I use MySQL? I hope the answer won't be that I need to make backups regularily. You'll have to give us some more information...at least: * What MySQL version, OS platform, and file system used for database? I am using MySQL 5, under Fedora Core 4, installed with its default options. * Does this happen at a regular time, or apparently randomly? It happends apparently randomly. Sometimes I just see that the programs are not working. Sometimes I can do some simple queries in the table with problems (like select count(*) from table_name), and the query works fine, but only when trying some more complex queries I can find that the table is corrupt and I need to fix it. Sometimes after fixing the table no records are deleted, but sometimes one or more records are deleted after fixing it. * Does this happen to the same tables all the time, or is that random as well? I found that it happends in more tables, but especially with one of them. That table has more than 2 million records and it is a MyISAM table. Should I use InnoDB instead? (Or another storage system?) That table is updated by a single program which runs continuously a few hours every day, and the program add (just addings and no updates) aproximately 1 records in those few hours... so they are not very very many. But other programs query that table very often. * Is this a precompiled binary from MySQL or did you build it yourself? It is a precompiled version from MySQL. I could see that if you compiled it yourself against some buggy libraries you could have problems; perhaps a cronjob is doing some copy/restore process on the underlying files without shutting mysql down or flushing logs; perhaps a lot of things...more information is needed. I have also seen (in most of the tables if not all) that after using "check table table_name" for the first time, I receive the message that the table was not closed by a few processes (from 2 to 6 processes). If I use that query a second time, I receive the message that the table is ok, and that message doesn't appear again. It has been my experience (on Windows NT, Solaris and Linux platforms) that MySQL has been one of the more reliable programs out there. Even after system crashes I haven't lost any data; a repair table and index rebuild fixed things. Yes in some cases it is the same for me, but after reparing a table, sometimes it tells me that some records were deleted because before that repair query the number of records reported is bigger. steve Thank you. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: corrupted tables
At 6:56 PM +0200 3/16/07, Octavian Rasnita wrote: Hi, Sometimes I see that some tables from my database get corrupted. Why does this happpen and how can I avoid it? It is not hard to go and use "repair table" but it seems that in this way some records could be deleted and this is not ok. If I want to have a very secure database, can I use MySQL? I hope the answer won't be that I need to make backups regularily. You'll have to give us some more information...at least: * What MySQL version, OS platform, and file system used for database? * Does this happen at a regular time, or apparently randomly? * Does this happen to the same tables all the time, or is that random as well? * Is this a precompiled binary from MySQL or did you build it yourself? I could see that if you compiled it yourself against some buggy libraries you could have problems; perhaps a cronjob is doing some copy/restore process on the underlying files without shutting mysql down or flushing logs; perhaps a lot of things...more information is needed. It has been my experience (on Windows NT, Solaris and Linux platforms) that MySQL has been one of the more reliable programs out there. Even after system crashes I haven't lost any data; a repair table and index rebuild fixed things. steve -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp://pgfsun.ucdavis.edu/ | | UC Davis Genome Center[EMAIL PROTECTED] | | Bioinformatics programming/database/sysadmin (530)754-9127 | + said t e lawrence, picking up his fork + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: find hanging keys
At 03:27 PM 3/16/2007, you wrote: How to find all the values of column a in table A that are not values of column b in table B? Thanks YL, You need to do a Left Join and it will use NULL's for rows in the second table if the row is missing. select A.* from TableA TA left join TableB TB on TA.id=TB.Id where TB.Id is null http://dev.mysql.com/doc/refman/5.1/en/join.html http://dev.mysql.com/doc/refman/5.1/en/left-join-optimization.html Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to get query result from two tables...
I have two tables: Table A: Code | Name 100 | Name A 200 | Name B 300 | Name C Table B: Code | Date | Qty | Value 100 | 2007-01-01 | 10 | 123 100 | 2007-02-01 | -2 | 300 200 | 2007-02-01 | 3 | 100 What I want is to get this result: Result Table on date example 2007-01-05: Code | Name | Qty | Value 100 | Name A | 10 | 123 Result Table on date example 2007-02-05: Code | Name | Qty | Value 100 | Name A | 8 | 300 200 | Name B | 3 | 100 And so on So, Table 1 is primary and Table B is some sort of history what happened with some "Code" on some dates What I want is to get result on certain date with right QTY on that date and also right Value on that date (or most nearest value). Tahnks... -- View this message in context: http://www.nabble.com/How-to-get-query-result-from-two-tables...-tf3416593.html#a9521748 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
performance of big tables - fundamental question on how to design a database
Hello list, I'm currently developing a newsletter tool allowing customers to send their newsletters to their clients and get all kinds of statistics. For each customer of ours, I need to save up to five different lists of newsletter recipients with their email addresses and some other stuffs our customer wants to store. Furthermore I'll be saving all sent newsletters (only the templates) over half a year. At the moment, we only got a few thousand customers but this design should be fit to serve a lot more. Let's assume we got 20,000 customers with 2,000 newsletter recipients each. That'd make some 40,000,000 recipients to be stored in our database and some 520,000 newsletters each half year providing that they send one each week. Well, I was thinking of putting them all in one giant table and associating them with the different lists. That way one person might actually be very redundantly saved in our database. And, I presume, it'd take a while to gather the information relevant for a customer. So, I've been thinking of maybe saving the lists as txt-dumps and importing them into a temporary table when needed. Or, well, I'm stuck here. Since I've never dealed with tables that big I'm not exactly what you would call a performance specialist. Maybe one of you has already designed a similar database or an idea of how it could work. Any ideas welcome! Thanks for reading Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDER BY issue
I have an app that I've converted to MySQL from MS SQL. I used to use the
following to force a Alpha field to sort as if it were numeric (I know,
perhaps it is better if I made the field numeric to begin with, but it's
not, and I don't remember why, but that's not the question here):
ORDER BY RIGHT('000' + RoomNo,3),LastName,FirstName
I converted this to the following in MySQL:
ORDER BY RIGHT(CONCAT('000,RoomNo),3),LastName,FirstName
In MS SQL, it would sort correctly:
1 Kayla Andre
1 Paige Brackon
1 Kasie Guesswho
1 Katelyn Hurst
2 Craig Bartson
2 Wesley Bytell
2 Kevin Peterson
2 Bryan Wilton
etc...
Now, the Above (RIGHT(CONCAT...)-MySQL Version), seems to ignore the first
sort "field", and simply sorts alphabatically:
1 Kayla Andre
2 Craig Bartson
1 Paige Brackon
2 Wesley Bytell
1 Kasie Guesswho
1 Katelyn Hurst
2 Kevin Peterson
2 Bryan Wilton
I finally ended up with:
ORDER BY CAST(RoomNo AS UNSIGNED), LastName, FirstName
Which works perfectly, but I'm just wondering why the first attempt
(right(concat...)) didn't work?? Any ideas?
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: corrupted tables
Octavian Rasnita wrote: Hi, Sometimes I see that some tables from my database get corrupted. Why does this happpen and how can I avoid it? It is not hard to go and use "repair table" but it seems that in this way some records could be deleted and this is not ok. If I want to have a very secure database, can I use MySQL? I hope the answer won't be that I need to make backups regularily. This speaks nothing to the security or reliability of MySQL (or any other crucial/sensitive information), but why wouldn't you want to make regular backups? You could have the most reliable piece of software out there, but that won't help you when a hard drive fails. -- John C. Nichel IV Programmer/System Admin Dot Com Holdings of Buffalo 716.856.9675 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PBXT 0.9.85 storage engine with support for Windows released
Hi All! The first version of the PrimeBase XT storage engine for MySQL with support for Windows NT/XP has just been released. Since MySQL for Windows does not yet support runtime pluggable engines, I have built the MySQL 5.1.16 server executable with the PBXT storage engine compiled in. The executable is included in the binary package now available for download: http://www.primebase.com/xt/download/pbxt-0.9.85- plugins.tar.gz Instructions on how to install are provided in the README file. The binary distribution also includes plug-ins for MySQL 5.1.16 running on Mac OS X and Linux 32/64-bit platforms. For more information on PBXT please refer to the PBXT home page: http://www.primebase.com/xt Source code and bug tracking is managed by SourceForge.net: http:// sourceforge.net/projects/pbxt See the release notes for further details of changes in this version: http://www.primebase.com/xt/download/pbxt-release.txt Just a reminder: I'll be speaking about PBXT and storage engine development at the MySQL Conference and Expo: http://www.mysqlconf.com/cs/mysqluc2007/view/e_spkr/3104 See you there! :) Best regards, Paul Paul McCullagh SNAP Innovation GmbH www.primebase.com/xt pbxt.blogspot.com sourceforge.net/projects/pbxt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
corrupted tables
Hi, Sometimes I see that some tables from my database get corrupted. Why does this happpen and how can I avoid it? It is not hard to go and use "repair table" but it seems that in this way some records could be deleted and this is not ok. If I want to have a very secure database, can I use MySQL? I hope the answer won't be that I need to make backups regularily. Thank you. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: High Avaliablity mysql db.
Hi! I would start at: http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication.html Bye, Ian Ananda Kumar said the following, On 16-Mar-07 11:04: > Hi All, > We are planing to develop and high available mysql db. > Can anybody please point me to any good documentation. Also how stable is > MySQL cluster and replication. > > regards > anandkl > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Synchronizing a remote database with a local database
On Fri, 2007-03-16 at 18:43 +0900, Dave M G wrote: > MySQL Users, > > I have a local website development environment where I have a "master" > MySQL database. > > I have several web sites which use the exact same database structure. > > The structure of the master database doesn't change very often, but it > does sometimes. When that happens, I want to be able to synchronize all > the web sites to match it. > > Currently, I'm creating a bash sell script so that I can update all my > web sites in one go. I've got it so that it will upload all the newest > PHP and other web files. > > For MySQL, so far I've figured out that I can create a .sql file with > the latest database structure with this command: > > mysqldump -u "root" -p"password" articlass_db --no-data > --result-file=backup_db.sql > > But I'm now stuck on how to use that .sql file to upload the new > structure to each web site's MySQL server. Can this be done? > > And can it be done in a non-destructive way. I mean, the web sites may > include data that I don't want to lose. So I don't want the new data > structure to wipe out any existing structure. I just want to compare the > master database structure with the one on the web site, and if there are > new tables or columns, then add them. > > Is this possible without third party commercial software? > > Thank you for any advice. > > -- > Dave M G > Ubuntu 6.10 Edgy Eft > Kernel 2.6.20-5-generic > Pentium D Dual Core Processor > PHP 5, MySQL 5, Apache 2 > The developers of http://dbdeploy.com/ tell me it supports MySQL despite that not being clear on the site. Cheers, -Janek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
High Avaliablity mysql db.
Hi All, We are planing to develop and high available mysql db. Can anybody please point me to any good documentation. Also how stable is MySQL cluster and replication. regards anandkl
Synchronizing a remote database with a local database
MySQL Users, I have a local website development environment where I have a "master" MySQL database. I have several web sites which use the exact same database structure. The structure of the master database doesn't change very often, but it does sometimes. When that happens, I want to be able to synchronize all the web sites to match it. Currently, I'm creating a bash sell script so that I can update all my web sites in one go. I've got it so that it will upload all the newest PHP and other web files. For MySQL, so far I've figured out that I can create a .sql file with the latest database structure with this command: mysqldump -u "root" -p"password" articlass_db --no-data --result-file=backup_db.sql But I'm now stuck on how to use that .sql file to upload the new structure to each web site's MySQL server. Can this be done? And can it be done in a non-destructive way. I mean, the web sites may include data that I don't want to lose. So I don't want the new data structure to wipe out any existing structure. I just want to compare the master database structure with the one on the web site, and if there are new tables or columns, then add them. Is this possible without third party commercial software? Thank you for any advice. -- Dave M G Ubuntu 6.10 Edgy Eft Kernel 2.6.20-5-generic Pentium D Dual Core Processor PHP 5, MySQL 5, Apache 2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
