Re: UNION
Lorderon [EMAIL PROTECTED] wrote: the only why i know how to do this is to write the ind sums to a new table and then sum that table.. How you do it with a new table? CREATE TEMPORARY TABLE table3 (SELECT SUM(price) as column1 FROM table1) UNION ALL (SELECT SUM(price) as column1 FROM table2); Then: SELECT SUM(column1) FROM table3; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - SELECT Query Help
D F [EMAIL PROTECTED] wrote: I want to select a column twice but get the results using two different conditions. SELECT tbl.colA, tbl.colA from tbl where tbl.info = B (the first colA) and tbl.info = C (for the second colA) I hope this makes sense. Could you describe more detailed what exactly do you want to get? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error
How to fix this problem 040303 17:40:51 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 040303 17:40:51 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 040303 17:40:52 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 040303 17:40:52 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 040303 17:40:53 InnoDB: Started; log sequence number 0 0 040303 17:40:54 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 040303 17:40:54 mysqld ended --- Best Regards Liew Toh Seng Icq No: 36835809 MSN: [EMAIL PROTECTED] * .--. * |o_o | * |:_/ | * // * (| | ) * /'\_ _/` The Internet Solution Company * \___)=(___ My Directory Sdn Bhd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dream MySQL Server?
Mark Maggelet [EMAIL PROTECTED] wrote on 02/03/2004 19:08:02: Scanning a PC manufacturer's website, it seems easy to get 4x2.5GHz Xeon, 1Mb L3, 8Gb ram, dual 15000 rpm Scsi with Raid 1 (for performance as well as reliability). Does this sound balanced for a MySQL engine? Or what would other people advise? I think you're better off with 4hd's and 2cpu's then the other way around since that's where your bottleneck will probably be, double the hd's should cut read times in half (but maybe add to write times) I had assumed that, since the ram was of the same order as the database size, most of the database would be cached. Number of threads is of concern - the system will have many lightweight queries, which I would like answered fast, and a few heavy joins. As I understand it, if the data is in memory and no lock collisions occur, a heavyweight query will hog a CPU until completed. Many CPUs (or virtual CPUs, with hyperthreading) allow many opportunities for simple queries to overlap complex ones. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dream MySQL Server?
My advice is that in the end, hardware does not matter that much. If it solves a problem, it solves it until your data outgrows it again, and eventually you hit your budget limitations and cannot afford an upgrade. Of course, there are certain common sense rules that need to be followed, eg. if you have a 20 GB database, you need at least a 20 GB disk, but otherwise, if your application is good, it runs well on modest hardware, and if not, doubling the datais likely to kill it no matter how powerful hardware you use on it. A good case in point was an earlier post from an 8-CPU Irix user. I would suggest you focus on making the application good. It would be wise to invest a portion of the hardware budget into purchasing a MySQL support contract or consulting services. Done that - and very pleased with the result. The application is already, for reasons given below, as well-tuned as I can make it. Regarding Windows 2000 - I am curious why MySQL is an option, but Linux is not. They kind of go together, almost the same as MS-SQL and Windows, or Oracle and Solaris. Is this a dedicated MySQL machine? If yes, I cannot think of one technical reason to run Windows on it, and I've tried hard in the past. If you were using Oracle or especially MS-SQL, it would make sense. But if you've decided that MySQL is it for your database, I would really have a hard time coming up with any reasonalbe justification for Windows even if Microsoft or somebody else was going to pay me big money for it. This is not a unique system - this is a large scale example of a general system. It is the central database for a number of surrounding specialised hardware units, all of which are controlled by Windows PCs. Normally there are three or four such systems, and our current MySQL/Windows solution is very good. The order I am trying to meet now has about 24 surrounding systems instead of 4. But it is otherwise identical. We have, for example, 24/7 support staff who will have to support end user staff if anything goes wrong. It is hard enough getting them up to speed on the Windows platform - adding the Linux platform for them to learn would be an extra burden. And I would find it difficult to be truly expert on several databases. And the testing burden of the same software against several database back-ends would be considerable. And to the suggestion of using MSSQL: this giant project would support the cost of MSSQL but the smaller systems wouldn't. Aside from the fact that I have already coded some MySQL specific code (which is, of course, reversible), smaller systems would not absorb the cost of MSSQL with replication. (I didn't mention that we actually have a replication pair of the machines described, and MSSQL with replication is much more expensive than without). We are currently retreating from an unsatisfactory Linux development with an outside contractor. Nobody's fault, but our knowledge of Linux and his understanding of our requirements didn't overlap enough. Our management are understandably reluctant to introduce an OS of which we have little knowledge when we are working hard to increase our knowledge base of our primary platform, which is (for better or worse) Windows. Thanks to all who have commented. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimise SELECT ... LIMIT
Hi list, I'm trying to optimise a SELECT ... LIMIT query, perhaps i miss something important and so, i'm asking your help :) I'm using MySQL 4.0.15 under Linux. Here's a test query : mysql explain SELECT * FROM F4000 WHERE ReplyTo=8711465 ORDER BY Numero LIMIT 234599,20; +---+--+++-+---+ +-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +---+--+++-+---+ +-+ | F4000 | ref | ReplyTo_Numero | ReplyTo_Numero | 4 | const | 188063 | Using where | +---+--+++-+---+ +-+ And here's index description : mysql show index from F4000; | F4000 | 0 | PRIMARY| 1 | Numero | A | 2535091 | NULL | NULL | | BTREE | F4000 | 1 | ReplyTo_Numero | 1 | ReplyTo | A |NULL | NULL | NULL | | BTREE | F4000 | 1 | ReplyTo_Numero | 2 | Numero | A |NULL | NULL | NULL | | BTREE ReplyTo and Numero are both integer. This query took around 1 sec, could i made something to have better performance ? PS : I know that Richard Davey have post a question on limit, 2 two days ago, but i didn't find anything that can help me. I also look at http://www.mysql.com/doc/en/LIMIT_optimisation.html without any success. Thanks. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error
did you 1. read the manual and 2. run mysql_install_db ? Regards, Thomas Spahni On Wed, 3 Mar 2004, Liew Toh Seng wrote: How to fix this problem 040303 17:40:51 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 040303 17:40:51 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 040303 17:40:52 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 040303 17:40:52 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 040303 17:40:53 InnoDB: Started; log sequence number 0 0 040303 17:40:54 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 040303 17:40:54 mysqld ended --- Best Regards Liew Toh Seng Icq No: 36835809 MSN: [EMAIL PROTECTED] * .--. * |o_o | * |:_/ | * // * (| | ) * /'\_ _/` The Internet Solution Company * \___)=(___ My Directory Sdn Bhd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql 4.1.1 crashes
Hi Here is the stack trace : 0x8106af3 handle_segfault + 423 0x40047a65 _end + 933903229 0x832b873 lock_clust_rec_cons_read_sees + 111 0x82774fc row_search_for_mysql + 9884 0x81783cb general_fetch__11ha_innobasePcUiUi + 75 0x8178496 index_next_same__11ha_innobasePcPCcUi + 34 0x813eecc join_read_next_same__FP14st_read_record + 52 0x813e57e sub_select__FP4JOINP13st_join_tableb + 330 0x813e226 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 434 0x8134b36 exec__4JOIN + 4234 0x8135068 mysql_select__FP3THDPPP4ItemP13st_table_listUiRt4List1Z4ItemP4ItemUiP8st _orderT7T5T7UlP13select_resultP18st_select_lex_unitP13s + 832 0x8131fde handle_select__FP3THDP6st_lexP13select_result + 174 0x81142b7 mysql_execute_command__FP3THD + 1427 0x8118d49 mysql_parse__FP3THDPcUi + 177 0x8112f3f dispatch_command__F19enum_server_commandP3THDPcUi + 1635 0x81128d1 do_command__FP3THD + 161 0x8112047 handle_one_connection + 563 0x40044c3f _end + 933891415 0x401b5b2a _end + 935402562 Thanks Lior -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 02, 2004 10:08 PM To: Nisim, Lior Cc: [EMAIL PROTECTED] Subject: Re: Mysql 4.1.1 crashes Did you run a stack trace? Original Message On 3/2/04, 9:02:15 AM, Nisim, Lior [EMAIL PROTECTED] wrote regarding Mysql 4.1.1 crashes: hi My server crashes on sub qurey , can any one help ? --- 040302 16:32:20 mysqld started 040302 16:32:20 InnoDB: Started; log sequence number 0 60169 /tmp/mysql/mysql_4.1.1/bin/mysqld: ready for connections. Version: '4.1.1-alpha-max' socket: '/tmp/mysql.sock' port: 3306 mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=0 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x867e9a0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x424a6838, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8106af3 0x40047a65 0x832b873 0x82774fc 0x81783cb 0x8178496 0x813eecc 0x813e57e 0x813e226 0x8134b36 0x8135068 0x8131fde 0x81142b7 0x8118d49 0x8112f3f 0x81128d1 0x8112047 0x40044c3f 0x401b5b2a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x8685398 = SELECT milestone,date,user,notes FROM milestones_info WHERE block='agadderc' group by milestone having date=(SELECT max(date) FROM milestones_info as tmp WHERE block='agadderc' AND milestone=tmp.milestone) thd-thread_id=1 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 040302 16:32:31 mysqld restarted 040302 16:32:31 InnoDB: Started; log sequence number 0 60169 /tmp/mysql/mysql_4.1.1/bin/mysqld: ready for connections. Version: '4.1.1-alpha-max' socket: '/tmp/mysql.sock' port: 3306 --- Description: mysql crashes on sub query How-To-Repeat: check the below error log Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: Originator: Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-4.1.1-alpha-max (Official MySQL-max binary) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: machine, os, target, libraries (multiple lines) System: Linux iapp003 2.4.9-45lxset11 #1 Mon Jan 5 17:10:26 MST 2004 i686 unknown Architecture: i686 Some paths: /usr/bin/perl
Re: explain tree like structure? Code available? Examples?
Hi Chris, Hmm The question is, does MySQL's optimiser do enough planing to result in a tree of any non-trivial interest? I don't know - apparently, you think it doesn't? I love MySQL as much as the next geek with a significant other that loves dolphins, but I'm not sure that MySQL 4.0 would provide a lot of data for funky tree-drawing (MS SQL tool style). That was the idea ;-) Anyone have any comments regarding the optimiser improvements in MySQL 5? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems Installing
Hi Marie! OK, first of all, you could download a rpm package and have redhat rpm system manage all the installation details for you, this one has a basic advantage, using rpm system that comes with redhat you'll be eable to centrally manage all your packages automatically, wich is very nice thing to have when being new. s Most Modern Linux distro do not use /usr/local at all, most of modern Linux distros today are based on a new, better suited for today, standard called LSB. Basically LSB says that all userland programs should be placed under /usr, that is /usr/bin for normal userland programs, commands and utilities. /usr/lib for libraries. /usr/include for development files(such as .h files). /usr/libexec is used for many misc things, this is where mysql server will store the mysql server binary file (mysqld) since it doen't belong to userland cause it is a server, a background service. /usr/local is considered deprecated, old and is only there to keep compatibility with older stuff. If you download and install a rpm package, the system will copy and place your files automatically for you in the places where they should be. So if you have downloaded a rpm package (my recomendation) you can easilly install it by issuing the following command: rpm -i mysqlpackage-that-youdownloaded.rpm One thing to note is that mysql has several packages, usually one has the server binaries, that is, a package wich ONLY contains the server, other is the client wich are needed to connect and manage the db. The last package is de devel wich are ONLY needed if you intend to develop software against the db server(for example develop a c app), otherwise it is not needed. Command line or gui? It's up to you. I personally like the mysql command line client, i find it quite competent tool and having it to manage my server in every platform is a plus. But if you want a nice GUI you can equally try with any of the free tools available: sqlyog: www.sqlyog.com dbtools: www.dbtools.com.br mysqlcc: www.mysql.com Those 3 are only a short example, i am pretty sure that if you ask for a nice gui tool in the list you will get some others choices. After installing the db you will need to configure it, but that's for another mail, if you have problem with it just let us know =) Best regards! On Tue, 2004-03-02 at 20:24, Marie Salas wrote: Hello, I'm very new to this Linux OS, and just downloaded MYSQL and I'm having a problem. I downloaded your Mysql version Linux (x86, libc6) and once downloaded I was unpacking the files from File-Roller that was part of the Linux Redhat installation. When I went to extract the files and save in usr/local/ it gave me a message saying I don't have permission. I am a super user of this computer. I don't understand what the problem is. Also I'm confused about whether I should use the terminal or if I can do this with GUI??? The titorials I've read show the commands on how to install everything through the terminal. If you can offer any suggestion, I'd appreciate. Thanks, M. Marie Salas - Do you Yahoo!? Yahoo! Search - Find what youre looking for faster. -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...|
plz help
hi , i have some mysql tables in my mysql database . Table name is ServiceStatus and It is included with "status" field . In the status field ,it is maintainig "up" , "down" status. SO i want to add colors for this status. up = green down = red how can i do that ? can some body help me ..plz thanx in advance curlys -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: plz help
Hello CurlyBraces, Wednesday, March 3, 2004, 1:22:51 PM, you wrote: CTPL SO i want to add colors for this status. CTPL up = green CTPL down = red CTPL how can i do that ? can some body help me ..plz You asked this question a few days ago, didn't you read the reply you got last time? -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: read only table 'user' at install
A few things to try: 1.- did you create the data dir as root? chown mysql.mysql -R /var/dir-where-you-install 2.- who owns the mysql data dir and it's parent dirs? ls -lka /var/dir-where-you-installed 3.- try using mysql client Best Regards On Tue, 2004-03-02 at 21:55, Jonathan Villa wrote: Ok, I have installed MySQL many times and have never come across this... when try to run ./bin/mysqladmin -u root password 'new-password' I get the following ./bin/mysqladmin: unable to change password; error: 'Table 'user' is read only' I've never had this happen, and unfortunately do not know enough about read/write perms on tables to try to debug it. I promise that I'll study up on it, but right now I'm kinda of in rush... I'm using : mysql-standard-4.0.18-pc-linux-i686.tar.gz thanks! -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...|
install mysql++
Hi! I am trying to install mysql++ from an rpm but I get dependency failure [EMAIL PROTECTED] mayn]# rpm -iv mysql++-1.7.9-4.rh80.i386.rpm error: failed dependencies: libmysqlclient.so.10 is needed by mysql++-1.7.9-4.rh80 I have libmysqlclient.so.12 so I tried to create soft links grom libmysql.so.10 but It still does not work lrwxrwxrwx1 root root 24 Mar 3 11:03 /usr/lib/libmysqlclient.so - libmysqlclient.so.12.0.0 lrwxrwxrwx1 root root 26 Mar 3 11:58 /usr/lib/libmysqlclient.so.10 - /usr/lib/libmysqlclient.so lrwxrwxrwx1 root root 29 Mar 3 12:06 /usr/lib/libmysqlclient.so.12 - /usr/lib/libmysqlclient.so.10 -rwxr-xr-x1 root root 249972 Feb 12 17:46 /usr/lib/libmysqlclient.so.12.0.0 What do I have to do? Best Regards /Maria
Changing the primary key
Hi I´'m new with mysql and have following question: I have a table, that has a primary key with two columns and want to add a third column to this primary key. Is this possible and when yes: Do I have to delete all tables, that reference to this table? Best regards Michael R.
Hitting max_connections - safe to raise this?
Hi, Our main database server is a 2 x PIII 1.2Ghz with 1.5GB RAM and a 73GB SCSI RAID-5 made up of 3 36GB disks. It does between 300 and 1200 queries per second. The read to write ratio is about 4:1. My problem is that we're hitting our max_connections more and more frequently. Is it safe to raise this to (say) 200, or will this cause performance problems? Already the machine is using up a lot of swap; would you recommend that I bump the RAM up to 2GB, or should I bring down the key_buffer_size in order to fit everything into physical RAM? Should I be concerned about the load average of the machine - it goes up to 6 at some points in the day. Are there any other performance tips that anyone can give based on this configuration? If you need more information, please let me know. Here is the output from top (at a fairly busy time of day): 1:39pm up 224 days, 8:09, 8 users, load average: 2.20, 3.37, 3.44 101 processes: 96 sleeping, 5 running, 0 zombie, 0 stopped CPU0 states: 16.0% user, 60.1% system, 0.0% nice, 22.1% idle CPU1 states: 18.0% user, 72.1% system, 0.0% nice, 8.1% idle Mem: 1545040K av, 1531936K used, 13104K free, 0K shrd, 17048K buff Swap: 2061428K av, 421876K used, 1639552K free 1016380K cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND 17727 root 18 0 1064 1060 824 R31.5 0.0 0:01 top 17733 mysql 10 0 426M 385M 356M S 4.3 25.5 0:00 mysqld 17634 mysql 9 0 426M 385M 356M S 2.1 25.5 0:00 mysqld 17720 mysql 9 0 427M 386M 356M S 2.1 25.5 0:00 mysqld 17746 mysql 10 0 426M 385M 356M S 2.1 25.5 0:00 mysqld 15257 mysql 9 0 426M 385M 356M R 1.6 25.5 360:19 mysqld 17725 mysql 9 0 427M 386M 356M S 1.6 25.5 0:00 mysqld 17730 mysql 9 0 426M 385M 356M S 1.6 25.5 0:00 mysqld 17741 mysql 9 0 426M 385M 356M S 1.6 25.5 0:00 mysqld 17750 mysql 9 0 426M 385M 356M S 1.0 25.5 0:00 mysqld 4 root 19 19 00 0 RWN 0.5 0.0 7:07 ksoftirqd_CPU1 15260 mysql 9 0 426M 385M 356M S 0.5 25.5 88:46 mysqld 29177 root 6 0 740 696 560 S 0.5 0.0 0:17 watch 17654 mysql 9 0 427M 386M 356M S 0.5 25.5 0:00 mysqld 17717 mysql 9 0 426M 385M 356M S 0.5 25.5 0:00 mysqld 17718 mysql 9 0 427M 386M 356M S 0.5 25.5 0:00 mysqld 17734 mysql 9 0 426M 385M 356M S 0.5 25.5 0:00 mysqld 17745 mysql 9 0 426M 385M 356M S 0.5 25.5 0:00 mysqld 17749 mysql 9 0 426M 385M 356M S 0.5 25.5 0:00 mysqld 1 root 9 0 488 440 424 S 0.0 0.0 3:24 init 2 root 9 0 00 0 SW0.0 0.0 0:05 keventd 3 root 19 19 00 0 RWN 0.0 0.0 7:20 ksoftirqd_CPU0 5 root 9 0 00 0 SW0.0 0.0 93:38 kswapd 6 root 9 0 00 0 SW0.0 0.0 0:00 bdflush 7 root 9 0 00 0 SW0.0 0.0 14:45 kupdated 8 root 9 0 00 0 SW0.0 0.0 0:00 scsi_eh_0 9 root 9 0 00 0 SW0.0 0.0 0:00 scsi_eh_1 10 root -1 -20 00 0 SW 0.0 0.0 0:00 mdrecoveryd 11 root 9 0 00 0 SW0.0 0.0 161:25 kjournald 509 root 9 0 560 504 464 S 0.0 0.0 2:27 syslogd 514 root 9 0 456 392 392 S 0.0 0.0 0:00 klogd 713 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty 714 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty 715 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty 716 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty 717 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty 718 root 9 0 392 336 336 S 0.0 0.0 0:00 mingetty Here is the output of SHOW VARIBLES (minus character_sets): mysql show variables; +-+---+ | Variable_name | Value | +-+---+ | back_log| 200 | | basedir | /usr/local/mysql-max-3.23.55-pc-linux-i686/ | | bdb_cache_size | 8388600 | | bdb_log_buffer_size | 262144 | | bdb_home| /usr/local/mysql/data/ | | bdb_max_lock| 1
Re: Changing the primary key
Hello Michael, Wednesday, March 3, 2004, 1:40:00 PM, you wrote: RM I have a table, that has a primary key with two columns and want to add a RM third column to this primary key. RM Is this possible and when yes: Do I have to delete all tables, that RM reference to this table? No, you don't have to delete all tables that reference this table. You can drop the key: ALTER TABLE tablename DROP PRIMARY KEY and then re-create it: ALTER TABLE tablename ADD PRIMARY KEY (a,b,c) Please note that if you have a field with a property such as auto-increment then dropping the primary key will fail because it will leave an invalid table definition. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: Changing the primary key
Hi Richard At first, thank you for your answer. I've tried your solution, but when I try to drop the PRIMARY KEY I get following error: [localhost] ERROR 1025: Error on rename of '.\austro\#sql-280_110' to '.\austro\fluege' (errno: 150) Best regards Michael R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Changing the primary key
Michael: One thing to keep in mind with Richard's answer is that when you recreate the key with the third column, you are no longer guaranteeing uniqueness on the original two column key. Any tables referencing the old key could become corrupt (in the data sense) if a second identical entry becomes available. EG If you have in the table in question a key of (a,b) now you add the new column... you could have a key of (a,b,c) and (a,b,d). Any existing data will not know which item to reference if you need it to reference only one. So, give a need for uniqueness in the referencing tables you will need to accommodate the new key in each of them as well. If it is OK to get more than one row back from the FK reference then you can ignore the concern. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: WOW! What a ride! -Original Message- From: Richard Davey [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 7:53 AM To: [EMAIL PROTECTED] Subject: Re: Changing the primary key Hello Michael, Wednesday, March 3, 2004, 1:40:00 PM, you wrote: RM I have a table, that has a primary key with two columns and want to add a RM third column to this primary key. RM Is this possible and when yes: Do I have to delete all tables, that RM reference to this table? No, you don't have to delete all tables that reference this table. You can drop the key: ALTER TABLE tablename DROP PRIMARY KEY and then re-create it: ALTER TABLE tablename ADD PRIMARY KEY (a,b,c) Please note that if you have a field with a property such as auto-increment then dropping the primary key will fail because it will leave an invalid table definition. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.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: mysql vs. MySQL
The real name of the rpm package should be inside the .spec file contained within the rpm file. Best Regards! On Tue, 2004-03-02 at 17:53, David Quenzler wrote: My machines have several mysql RPMs installed as part of a SuSE UL 1.0 base configuration. RPMs are all lower-case of the form 'mysql, mysql-client, mysql-devel, mysql-shared', etc. Upgrade RPMs are available as MySQL, mixed case, no longer all lower-case. Is this a cosmetic issue only, am I able to rename lower-case and rpm -F without a problem? - Dave -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...|
found rows in union
How can I find the number of rows a query returns when I'm using UNION ? for example, how can I know how much rows the next query returns: (SELECT price FROM table1 WHERE id100) UNION (SELECT price FROM table2 WHERE id150) thanks in advance, -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PgSQL vs MySQL
What advantages, besides ease of setup, does MySQL hold over PostgreSQL? It would seem, to me, that the two are close competitors (both in quality, and performance). Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: Changing the primary key
Hi! At first thanks for your help. Now I can change the PRIMARY KEY of the table, but now I have a new question: How can I change the FOREIGN KEYS? Best regards Michael R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Listing vacant auto_increment keys
Hi there. I've a table with an auto_increment primary key. I want to reuse keys (index numbers) that becomes vacant when I delete entries. How do I list the vacant keys from a table? Example table: 1 2 5 6 9 10 = max value, next is 11. List of vacant keys: 3 4 7 8 Thanks... __ Acabe com aquelas janelinhas que pulam na sua tela. AntiPop-up UOL - É grátis! http://antipopup.uol.com.br/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Who can help the Newbie???
Hi! First of all, in order for us to help you, we will need more concise descriptions of your problems installing mysql, i guess for what you are saying that you are installing on windows, so we will need at least to know, in what you are stuck, version of the OS you are using, what is going wrong, the command or actions you took or executed and the output so we can help you. Regarding the things of having to access mysql ONLY trough a DOS windows, that is not at all right, sure you can do whatever you want with mysql using only the console, but you can also use a nice gui, a few examples: 1.- dbtools: www.dbtools.com.br 2.- sqlyog: www.sqlyog.com Best Regards On Tue, 2004-03-02 at 15:26, Randal wrote: I am very new to this database thing. I dont want to clog up the board with all the basic questions that I need answered. I have been cataloging contact info and other things in MS Excel for years. I am familiar with the base concepts of a database; I think, but I have no idea what goes on in a DOS window. It took me a few days to figure out that I was going to have to use it to access the software. (I said I was new to this) I was hoping that the vast network of knowledge could point me in the direction of a source of what it is that I need to know about DOS to get moving on this darn 21 day tutorial I am stuck on day two of. Thanks in advance, Rand -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...|
Re: Changing the primary key
Ranetbauer, Michael [EMAIL PROTECTED] wrote: Hi Richard At first, thank you for your answer. I've tried your solution, but when I try to drop the PRIMARY KEY I get following error: [localhost] ERROR 1025: Error on rename of '.\austro\#sql-280_110' to '.\austro\fluege' (errno: 150) $ perror 150 Error code 150: Unknown error 150 150 = Foreign key constraint is incorrectly formed You should create index on that column and then drop primary key. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing the primary key
Ranetbauer, Michael [EMAIL PROTECTED] wrote on 03/03/2004 13:40:00: I have a table, that has a primary key with two columns and want to add a third column to this primary key. Is this possible and when yes: Do I have to delete all tables, that It is possible, and you do not have to delete any tables. You have to drop the current Primary Key and then add another one. See the ALTER TABLE command: ALTER TABLE table DROP PRIMARY KEY ; ALTER TABLE table ADD PRIMARY KEY (col1, col2, col3) ; The primary key has to be rebuilt, which may take some time. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing the primary key
Ranetbauer, Michael [EMAIL PROTECTED] wrote: Hi! At first thanks for your help. Now I can change the PRIMARY KEY of the table, but now I have a new question: How can I change the FOREIGN KEYS? You can drop old FOREIGN KEY with ALTER TABLE .. DROP FOREIGN KEY statement: http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html Then create a new one. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table selection
Hello all, I have a little problem to solve. I have a List table that lists the tables contained in the same DB. This table has a Name filed that contains such names. My problem is that the list.name filed doesn't contain the complete name of the tables but it misses a prestring. Say: a table is named FX but the list.name field contains only. I have to create a query like: SELECT * FROM [every table in list.name] WHERE [condition] but I cannot use SELECT Name FROM List to gain the names of the tables because they all miss the FX string. How can I create this kind of query? Something like: SELECT * FROM (FX + SELECT Name FROM List) WHERE [condition] I hope I was clear 'cause my English is not so good. Is there anyone who can help me? Thank you! Marco Bresciani -- (o Utente [EMAIL PROTECTED] da 3,632 anni (2200 unità) //\ Tempo CPU: 2,238 anni (8h 54min 44s medio) V_/_ Posizione: 111701/4900443 (97,72%) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to import a file to the database
how to import a file to the database - I have a file called dat.db.001 in local system . It is mysql database file . I have installed mysql in my system and now would like to open the file dat.db.001 .I am an newbee to databases.Can somebody mail me how to open this file. regards sairam - Do you Yahoo!? Yahoo! Search - Find what youre looking for faster.
Re: found rows in union
I'm running MySQL 4.0 and it doesn't support sub-queries... I've checked and found that the next query returns rows of counting each union part seperatedly and could make sum on its rows: (SELECT COUNT(*) FROM table WHERE id100) UNION ALL (SELECT COUNT(*) FROM table2 WHERE id150) returns: ++ | COUNT(num) | ++ |124 | |912 | ++ When running the query without union, you could use SQL_CALC_FOUND_ROWS even when having LIMIT, but this option not works when using union.. :( Is there anything like SQL_CALC_FOUND_ROWS in union? since I don't want to run the query twice Also... if I run the same query twice.. first run and selecting columns.. then I run the same query but selecting COUNT(*).. does the second time will run using MySQL's cache? Lorderon [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] How can I find the number of rows a query returns when I'm using UNION ? for example, how can I know how much rows the next query returns: (SELECT price FROM table1 WHERE id100) UNION (SELECT price FROM table2 WHERE id150) thanks in advance, -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
What advantages, besides ease of setup, does MySQL hold over PostgreSQL? It would seem, to me, that the two are close competitors (both in quality, and performance). Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html You can check out postgresql's on the same site but they are significally less critical. After reviewing this and talking to some other people i'm switching over to postgresql. I'm sure there is a place with mysql but I don't think i'd trust it for anything critical unless you very confident your developers know what they are doing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SUM() not working as expected
Hi all, Just trying to figure out why the SUM() in this query is not returning the sum, but is returning the same value that signed_amt contains. Anyone have a clue? Thanks for any help :) Here are the results I am getting: id | signed_amt | balance_amt | entry_dtm ---++-+--- 7 |-20 | -20 | 1078117200 7 | 3 | 3 | 1078263566 Here is the query: SELECT u.id, CASE WHEN tt.trans_cd = 'D' THEN trans_amt * - 1 WHEN tt.trans_cd = 'C' THEN trans_amt END AS signed_amt, SUM ( CASE WHEN tt.trans_cd = 'D' THEN trans_amt * - 1 WHEN tt.trans_cd = 'C' THEN trans_amt END ) AS balance_amt, t.entry_dtm FROM clients u, tbl_transactions t, tbl_transaction_types tt WHERE t.client_id = u.id AND t.trans_id = tt.trans_type_id GROUP BY u.id, t.entry_dtm, signed_amt Here is some data to play with: CREATE TABLE `clients` ( `id` int(11) NOT NULL auto_increment, `company` varchar(50) NOT NULL default '', PRIMARY KEY (`id`) ) TYPE=MyISAM AUTO_INCREMENT=8 ; INSERT INTO `clients` VALUES (1, 'client_1'); INSERT INTO `clients` VALUES (7, 'test client'); CREATE TABLE `tbl_transaction_types` ( `trans_type_id` int(11) NOT NULL auto_increment, `trans_name` varchar(10) NOT NULL default '', `trans_cd` char(1) NOT NULL default '', PRIMARY KEY (`trans_type_id`) ) TYPE=MyISAM AUTO_INCREMENT=3 ; INSERT INTO `tbl_transaction_types` VALUES (1, 'Debit', 'D'); INSERT INTO `tbl_transaction_types` VALUES (2, 'Credit', 'C'); CREATE TABLE `tbl_transactions` ( `trans_id` int(11) NOT NULL auto_increment, `client_id` int(11) NOT NULL default '0', `trans_type_id` double NOT NULL default '0', `trans_amt` double NOT NULL default '0', `entry_dtm` int(11) NOT NULL default '0', PRIMARY KEY (`trans_id`) ) TYPE=MyISAM AUTO_INCREMENT=3 ; INSERT INTO `tbl_transactions` VALUES (1, 7, '2', '20', 1078117200); INSERT INTO `tbl_transactions` VALUES (2, 7, '1', '3', 1078263566); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table selection
Marco Bresciani [EMAIL PROTECTED] wrote: Hello all, I have a little problem to solve. I have a List table that lists the tables contained in the same DB. This table has a Name filed that contains such names. My problem is that the list.name filed doesn't contain the complete name of the tables but it misses a prestring. Say: a table is named FX but the list.name field contains only. I have to create a query like: SELECT * FROM [every table in list.name] WHERE [condition] but I cannot use SELECT Name FROM List to gain the names of the tables because they all miss the FX string. How can I create this kind of query? Something like: SELECT * FROM (FX + SELECT Name FROM List) WHERE [condition] You can't do it only with MySQL. Retrieve table names from List table: SELECT CONCAT(FX, Name) FROM List; and then use programming language to construct a query. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to import a file to the database
Manda Sairam [EMAIL PROTECTED] wrote: how to import a file to the database - I have a file called dat.db.001 in local system . It is mysql database file . I have installed mysql in my system and now would like to open the file dat.db.001 .I am an newbee to databases.Can somebody mail me how to open this file. If this file contains SQL statements you can do: shell mysql -uuser_name -p database_name /path/to/the/file/dat.db.001 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table selection
Egor Egorov [EMAIL PROTECTED] ha scritto: You can't do it only with MySQL. Retrieve table names from List table: SELECT CONCAT(FX, Name) FROM List; and then use programming language to construct a query. Thank you... I've supposed it... What about using UNION? Something like: SELECT * FROM FX5686 UNION SELECT * FROM FX5698 UNION ... WHERE [condition] I obviously need a programming language to compose this query... but it seems clearer to me... if it works! Marco Bresciani -- (o Utente [EMAIL PROTECTED] da 3,632 anni (2200 unità) //\ Tempo CPU: 2,238 anni (8h 54min 44s medio) V_/_ Posizione: 111701/4900443 (97,72%) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication Can Master initiate replication to Slave?
The documentation I find describes the slave contacting the master to initiate the replication service. I need the master to be able to contact the slave. Is this possible. Thanks lee -- Lee Chalupa Something Else Enterprises, Inc. [EMAIL PROTECTED] 770 381 2377 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
David wrote: Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html Those have nothing to do with the quality of MySQL. I don't think i'd trust it for anything critical unless you very confident your developers know what they are doing. Would you trust anything to developers who do not know what they are doing? As a _novice_ developer I learned the noted aspects of MySQL: it's part of knowing how the database works and how to use it, and not terribly sophisticated. I think if a database developer would not get into that level of detail, you are going to have problems no matter what database they use. That said, postgresql may be a fine choice. Enjoy. Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql monitor
Hi, Due to poor database design and heavy load, database tables get corrupted a lot. I want to monitor that process and once a table to get corrupted to repair it on the fly and send email to the support team that table is corrupted and reapired automatically including the hostaname, and mysql version info. I think about a perl script in cron job that do this. Is that the correct way ?. Any ideas which is better to the perl system call to myisamchk or 'REPAIR TABLE'...any ideas are appreciated ... Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html Those have nothing to do with the quality of MySQL. No? Why not? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: PgSQL vs MySQL
Would you trust anything to developers who do not know what they are doing? As a _novice_ developer I learned the noted aspects of MySQL: it's part of knowing how the database works and how to use it, and not terribly sophisticated. Then you may be a good developer but experience tells me (7 years as an Oracle DBA) that developers don't take the time to learn the intricacies of a particular database. So if you plan on using mysql, make all your developers read that gotcha's page. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
The thing which bothers me most about MySQL is the lack of a proper boolean. I don't like having to abstract a tinyint(1) into true or false. As much of my work involves building applications with Yes or No questions, I think I am switching to PostgreSQL. [EMAIL PROTECTED] wrote: What advantages, besides ease of setup, does MySQL hold over PostgreSQL? It would seem, to me, that the two are close competitors (both in quality, and performance). Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html You can check out postgresql's on the same site but they are significally less critical. After reviewing this and talking to some other people i'm switching over to postgresql. I'm sure there is a place with mysql but I don't think i'd trust it for anything critical unless you very confident your developers know what they are doing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LIKE % not including NULL values?
Hello list SELECT * FROM testdb WHERE col1 LIKE '%' The above SELECT statement doesnt return columns with NULL values. Should it? ;). Should I approce the problem in another direction? Regards /Jonas
Re: Table selection
Marco Bresciani [EMAIL PROTECTED] wrote: Egor Egorov [EMAIL PROTECTED] ha scritto: You can't do it only with MySQL. Retrieve table names from List table: SELECT CONCAT(FX, Name) FROM List; and then use programming language to construct a query. Thank you... I've supposed it... What about using UNION? Something like: SELECT * FROM FX5686 UNION SELECT * FROM FX5698 UNION ... WHERE [condition] I obviously need a programming language to compose this query... but it seems clearer to me... if it works! You can use UNION, but WHERE condition should be written for each SELECT in the UNION: (SELECT * FROM FX5686 WHERE ..) UNION (SELECT * FROM FX5698 WHERE .. ) .. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIKE % not including NULL values?
seems like gotcha :)) use: SELECT * FROM testdb WHERE col1 LIKE '%' OR col1 IS NULL; On Wednesday 03 of March 2004 16:41, Jonas Lindén wrote: Hello list SELECT * FROM testdb WHERE col1 LIKE '%' The above SELECT statement doesnt return columns with NULL values. Should it? ;). Should I approce the problem in another direction? Regards /Jonas -- Z lepimi pozdravi, Bostjan Skufca sistemski administrator Domenca d.o.o. Tel: +386 4 5835444 Fax: +386 4 5831999 http://www.domenca.si -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
In the last episode (Mar 03), [EMAIL PROTECTED] said: Would you trust anything to developers who do not know what they are doing? As a _novice_ developer I learned the noted aspects of MySQL: it's part of knowing how the database works and how to use it, and not terribly sophisticated. Then you may be a good developer but experience tells me (7 years as an Oracle DBA) that developers don't take the time to learn the intricacies of a particular database. So if you plan on using mysql, make all your developers read that gotcha's page. You may need better developers then :) If you don't learn about the database you're coding for, you are always going to get average or below-average performance/quality. All databases have their gotchas. -- 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: how to import a file to the database
Does the file contain valid MySQL syntax? If so, you can import the file using \. file_name from the MySQL monitor Original Message On 3/3/04, 8:40:45 AM, Manda Sairam [EMAIL PROTECTED] wrote regarding how to import a file to the database : how to import a file to the database - I have a file called dat.db.001 in local system . It is mysql database file . I have installed mysql in my system and now would like to open the file dat.db.001 .I am an newbee to databases.Can somebody mail me how to open this file. regards sairam - Do you Yahoo!? Yahoo! Search - Find what you're looking for faster. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIKE % not including NULL values?
Jonas Lind?n [EMAIL PROTECTED] wrote: SELECT * FROM testdb WHERE col1 LIKE '%' The above SELECT statement doesnt return columns with NULL values. Should it? ;). Yes. % matches any number of characters, NULL is not character string. Use IS NULL or IS NOT NULL operators if you want to test for NULL. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Client connection error
Hi Sasha, after I sent this email for the list, I did some tests and I realized that the problem could be my firewall, so I just turned off for some tests and it worked. Now I need to reconfigure the firewall to permit access for the MySQL. Thanks a lot. Andre On Tue, 2 Mar 2004, Sasha Pachev wrote: Andre MATOS wrote: Hi, I am trying to connect from one computer that has MySQL installed (client) in another one that also has MySQL (server). Using this command: C:\mysql\binmysql.exe -u root -h server.com -p Enter password: * ERROR 2003: Can't connect to MySQL server on 'server.com' (10060) However I am also using Apache+PHP and it is working withou problem. Does anyone know what is the problem? The error means connection timed out. Possibly a restrictive firewall? -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql monitor
If you plan on constructing this your self may I suggest that you create a matrix of all the possible scenarios/issues you would like to trap and define how you want to those `automatically` handled. For example, `replication stopped`, `duplicate index`, `long running query`, `corrupt table`, etc, and what action to take and who should be notified. A cron job that runs every fifteen or thirty or whatever the desired interval could start the monitoring process and each run could be logged. ... This is only a suggestion of course. Original Message On 3/3/04, 9:24:20 AM, Peter [EMAIL PROTECTED] wrote regarding Mysql monitor: Hi, Due to poor database design and heavy load, database tables get corrupted a lot. I want to monitor that process and once a table to get corrupted to repair it on the fly and send email to the support team that table is corrupted and reapired automatically including the hostaname, and mysql version info. I think about a perl script in cron job that do this. Is that the correct way ?. Any ideas which is better to the perl system call to myisamchk or 'REPAIR TABLE'...any ideas are appreciated ... Peter -- 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: LIKE % not including NULL values?
If you want all values why not just do SELECT * FROM testdb ... The `%` will not match NULL values. Original Message On 3/3/04, 9:41:22 AM, Jonas Lindén [EMAIL PROTECTED] wrote regarding LIKE % not including NULL values?: Hello list SELECT * FROM testdb WHERE col1 LIKE '%' The above SELECT statement doesnt return columns with NULL values. Should it? ;). Should I approce the problem in another direction? Regards /Jonas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to import a file to the database
Do these files contain the .frm, .myd and .myi file extensions? How were these files originally created? Original Message dated 3/3/04, 10:11:10 AM Author: Manda Sairam [EMAIL PROTECTED] Re: Re: how to import a file to the database : The file is a binary file these are mysql db files I need to open or read the file. please mail me a solution . [EMAIL PROTECTED] wrote: Does the file contain valid MySQL syntax? If so, you can import the file using \. file_name from the MySQL monitor Original Message On 3/3/04, 8:40:45 AM, Manda Sairam wrote regarding how to import a file to the database : how to import a file to the database - I have a file called dat.db.001 in local system . It is mysql database file . I have installed mysql in my system and now would like to open the file dat.db.001 .I am an newbee to databases.Can somebody mail me how to open this file. regards sairam - Do you Yahoo!? Yahoo! Search - Find what you're looking for faster. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Do you Yahoo!? Yahoo! Search - Find what youre looking for faster. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SUM() not working as expected
charles kline [EMAIL PROTECTED] wrote: Hi all, Just trying to figure out why the SUM() in this query is not returning the sum, but is returning the same value that signed_amt contains. Anyone have a clue? Thanks for any help :) Here are the results I am getting: id | signed_amt | balance_amt | entry_dtm ---++-+--- 7 |-20 | -20 | 1078117200 7 | 3 | 3 | 1078263566 SUM() function works fine in your query and it returns sum per group (u.id, t.entry_dtm, and signed_amt). -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql in a jail
I have a system running FreeBSD 4.9 four jails. MySQL is running in two of the jails and I am trying to add it to a 3rd jail. Starting the server gets the message: 040302 19:34:15 mysql started 040302 19:34:15 Can't start server : Bind on unix socket: Permission denied 040302 19:34:15 Do you already have another mysqld server running on socket: /tmp/mysql.sock ? 040302 19:34:15 Aborting 040302 19:34:15 /usr/local/libexec/mysqld: Shutdown Complete 040302 19:34:15 mysqld ended The new jail was created by copying the file tree from a jail that had the desired configuration and then doing clean-up as required. In response to the error I tried configuring mysqld to use a different port and socket file. That did not fix the problem. I had forgotten the other two mysql's are using port 3306 and /tmp/mysql.sock. I next removed mysql and its dependencies and reinstalled mysql-server-4.0.16 using pkg_add. I am still getting the same error. At this point I think it is a jail problem with something I missed in clean-up but I do not know where to look next. Having done all this, I recalled in installing the 2nd MySQL jail, it was necessary to remove and reinstall mysql. In that instance the re-installation solved the problem. Thanks for any ideas. _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: found rows in union
Lorderon [EMAIL PROTECTED] wrote: I'm running MySQL 4.0 and it doesn't support sub-queries... I've checked and found that the next query returns rows of counting each union part seperatedly and could make sum on its rows: (SELECT COUNT(*) FROM table WHERE id100) UNION ALL (SELECT COUNT(*) FROM table2 WHERE id150) returns: ++ | COUNT(num) | ++ |124 | |912 | ++ When running the query without union, you could use SQL_CALC_FOUND_ROWS even when having LIMIT, but this option not works when using union.. :( Is there anything like SQL_CALC_FOUND_ROWS in union? since I don't want to run the query twice SQL_CALC_FOUND_ROWS returns those number of rows for UNION that it should be returned without _global_ LIMIT clause. Also... if I run the same query twice.. first run and selecting columns.. then I run the same query but selecting COUNT(*).. does the second time will run using MySQL's cache? No. Queries should be the same, byte for byte, otherwise MySQL will treat them as different queries. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Show Processlist command
All: I recently discovered the Show Processlist statement which is great. My question is this... One the process completes it falls off this list. Is there another command that will show me the run time of the processes that have completed? Is this info in a log? If so, which one? Thanks for your time. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: WOW! What a ride! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: found rows in union
Victoria: In response you wrote: Also... if I run the same query twice.. first run and selecting columns.. then I run the same query but selecting COUNT(*).. does the second time will run using MySQL's cache? No. Queries should be the same, byte for byte, otherwise MySQL will treat them as different queries. Does this mean that MySQL does _not_ have the concept of a bind variable and thus have to reparse any query that has parameter changes? Thanks for your time. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: WOW! What a ride! -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 10:47 AM To: [EMAIL PROTECTED] Subject: Re: found rows in union Lorderon [EMAIL PROTECTED] wrote: I'm running MySQL 4.0 and it doesn't support sub-queries... I've checked and found that the next query returns rows of counting each union part seperatedly and could make sum on its rows: (SELECT COUNT(*) FROM table WHERE id100) UNION ALL (SELECT COUNT(*) FROM table2 WHERE id150) returns: ++ | COUNT(num) | ++ |124 | |912 | ++ When running the query without union, you could use SQL_CALC_FOUND_ROWS even when having LIMIT, but this option not works when using union.. :( Is there anything like SQL_CALC_FOUND_ROWS in union? since I don't want to run the query twice SQL_CALC_FOUND_ROWS returns those number of rows for UNION that it should be returned without _global_ LIMIT clause. Also... if I run the same query twice.. first run and selecting columns.. then I run the same query but selecting COUNT(*).. does the second time will run using MySQL's cache? No. Queries should be the same, byte for byte, otherwise MySQL will treat them as different queries. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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 import problem
Hello Sir, I have a file with the name doc.db.001 It is a binary file and it is a mysql file.I tried opening the file using load data file command but failed with errors .Actually the problem is i donot know about the columns or the details about the db file.How can i import such a file. regards sairam - Do you Yahoo!? Yahoo! Search - Find what youre looking for faster.
import ing files into mysql
I would like to import zip file into mysql database. Is this possible? regards sairam - Do you Yahoo!? Yahoo! Search - Find what youre looking for faster.
Re: mysqlbinlog: unknown command errors
Since I didn't get an answer to this, I'll try asking it another way: has anybody gotten a binlog with binary data (images) to load from one server to another? And if so, what version are you using? Thanks, - Mark On Mon, 1 Mar 2004 21:09:22 -0800, Mark Maggelet wrote: Hi, I'm having problems running queries in my binlog that contain binary data. Apparently the / character is being interpreted as a mysql command and I get errors. The queries must have gone through on the original machine because they're there in the binlog. Specs are: original server: mysqld Ver 4.0.14-standard for pc-linux on i686 update server: mysqld Ver 4.0.18-standard for pc-linux on i686 (Official MySQL-standard binary) This is a problem I've had for a while and I've never really gotten the binlog to work the way it's supposed to. Any help greatly appreciated. Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
:-)
someflag enum('TRUE','FALSE');
Not quite boolean, but it works.
Curtis
On Wed, 3 Mar 2004, Mark Warner wrote:
The thing which bothers me most about MySQL is the lack of a proper
boolean. I don't like having to abstract a tinyint(1) into true or
false. As much of my work involves building applications with Yes or No
questions, I think I am switching to PostgreSQL.
[EMAIL PROTECTED] wrote:
What advantages, besides ease of setup, does MySQL hold over PostgreSQL?
It would seem, to me, that the two are close competitors (both in
quality, and performance).
Are you sure about quality? Check out:
http://sql-info.de/mysql/gotchas.html
You can check out postgresql's on the same site but they are significally less
critical. After reviewing this and talking to some other people i'm switching
over to postgresql. I'm sure there is a place with mysql but I don't think i'd
trust it for anything critical unless you very confident your developers know what
they are doing.
--
--
Curtis Maurand
mailto:[EMAIL PROTECTED]
http://www.maurand.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SUM() not working as expected
Oh I see... so how would I get balance_amt to have a running balance? Thanks, Charles On Mar 3, 2004, at 11:20 AM, Victoria Reznichenko wrote: charles kline [EMAIL PROTECTED] wrote: Hi all, Just trying to figure out why the SUM() in this query is not returning the sum, but is returning the same value that signed_amt contains. Anyone have a clue? Thanks for any help :) Here are the results I am getting: id | signed_amt | balance_amt | entry_dtm ---++-+--- 7 |-20 | -20 | 1078117200 7 | 3 | 3 | 1078263566 SUM() function works fine in your query and it returns sum per group (u.id, t.entry_dtm, and signed_amt). -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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: import ing files into mysql
do you mean inserting the whole .zip file in a table? if the above is right, short answer is: YES On Wed, 2004-03-03 at 13:32, Manda Sairam wrote: I would like to import zip file into mysql database. Is this possible? regards sairam - Do you Yahoo!? Yahoo! Search - Find what youre looking for faster. -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...|
Re: Install Mysql
That's not the way of removing the service! Change it to the older file. Then run: nameofold-mysql.exe --remove To reinstall the service: nameofthenew-mysql.exe --install On Wed, 2004-03-03 at 13:31, Teddy Ruxpin wrote: (sorry my english) I had in my computer EasyPHP. Always worked. I tried update EasyPHP and mysql stopped work. I saw the Mysql service was pointing a file that doesn't exist. On regedit I made point to correct file mysqld.exe. But to the try execute the service, show a error message. Even executing the mysqld on dos, don't work. help me! --- Acabe com aquelas janelinhas que pulam na sua tela. AntiPop-up UOL - É grátis! http://antipopup.uol.com.br -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...|
Re: mysql in a jail
Maybe you could explain what a jail is. In 20+ years doing systems work I've never heard that term mean anything but a place where criminals are locked up. Rhino - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 11:28 AM Subject: mysql in a jail I have a system running FreeBSD 4.9 four jails. MySQL is running in two of the jails and I am trying to add it to a 3rd jail. Starting the server gets the message: 040302 19:34:15 mysql started 040302 19:34:15 Can't start server : Bind on unix socket: Permission denied 040302 19:34:15 Do you already have another mysqld server running on socket: /tmp/mysql.sock ? 040302 19:34:15 Aborting 040302 19:34:15 /usr/local/libexec/mysqld: Shutdown Complete 040302 19:34:15 mysqld ended The new jail was created by copying the file tree from a jail that had the desired configuration and then doing clean-up as required. In response to the error I tried configuring mysqld to use a different port and socket file. That did not fix the problem. I had forgotten the other two mysql's are using port 3306 and /tmp/mysql.sock. I next removed mysql and its dependencies and reinstalled mysql-server-4.0.16 using pkg_add. I am still getting the same error. At this point I think it is a jail problem with something I missed in clean-up but I do not know where to look next. Having done all this, I recalled in installing the 2nd MySQL jail, it was necessary to remove and reinstall mysql. In that instance the re-installation solved the problem. Thanks for any ideas. _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- 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]
Install Mysql
(sorry my english) I had in my computer EasyPHP. Always worked. I tried update EasyPHP and mysql stopped work. I saw the Mysql service was pointing a file that doesn't exist. On regedit I made point to correct file mysqld.exe. But to the try execute the service, show a error message. Even executing the mysqld on dos, don't work. help me! --- Acabe com aquelas janelinhas que pulam na sua tela. AntiPop-up UOL - É grátis! http://antipopup.uol.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
xml DTD and MySql
Hi everyone, I have XML data with a corresponding DTD, and I want create the necessary tables and load the data. Does anyone has experience with this? Are there any tools to help me? Thanks for your advice. Bernd
slave hotbackup question
Hi folks,
I have a question regarding backups taken from a running slave. I have a
slave
replicating to a master server, and do a hot backup using the following
script:
#!/usr/bin/perl
use strict;
use DBI;
use File::Copy;
use POSIX :sys_wait_h;
my $dbh =
DBI-connect(DBI:mysql:dbname=mysql;mysql_socket=/tmp/mysql.sock,user
,password) || die DBI-errstr();
## Create the backup folder
my ($d,$m,$y) = (localtime)[3..5];
my $date = sprintf(%d-%02d-%02d,$y+1900,$m+1,$d);
my $ibbackup_pid;
print Creating backup folder : /storage/backups/backup-$date\n;
mkdir(/storage/backups/backup-$date) || die couldn't create backup
dir: $!;
mkdir(/storage/backups/backup-$date/sql) || die couldn't create
backup dir: $!;
## Parse the /etc/my.cnf
print Parsing my.cnf\n;
open(F,/etc/my.cnf) || die;
my $found = 0;
my %cfg;
while(F) {
chomp;
## Junk until we have start of [mysqld] section
if (!$found) {
next if (!/\[mysqld\]/i);
$found = 1;
next;
}
## We aren't interested in anything not relating to the [mysqld]
section
## stop reading when it's finished.
last if ($found /^\s*\[/);
## Skip comments and empty lines
next if (/^(?:(?:#\|;).*|\s*)$/);
## Get rid of trailing space or ;
s/(?:|\s*)$//;
## Handle the set-variable=var=something
if (/^set-variable=(.*)$/) {
my ($var,$val) = split(/\s*=\s*/,$1);
$cfg{'set-variable'}{$var} = $val;
next;
}
## Split name/value pairs
my ($var,$val) = split(/\s*=\s*/);
$cfg{$var} = $val;
}
## At this point %cfg contains the relevant [mysqld] configuration
information.
## Create the backup config file
print Creating /storage/backups/backup-$date/my-backup.cnf\n;
open(FILE, /storage/backups/backup-$date/my-backup.cnf) || die
Failed to open file /storage/backups/backup-$date/my-backup.cnf: $!;
print FILE (EOF);
# This MySQL options file was generated by Mark's backup script.
[mysqld]
datadir=/storage/backups/backup-$date/sql
innodb_data_home_dir=/storage/backups/backup-$date/sql
innodb_data_file_path=$cfg{'innodb_data_file_path'}
innodb_log_group_home_dir=/storage/backups/backup-$date/sql
innodb_log_files_in_group=$cfg{'set-variable'}{'innodb_log_files_in_grou
p'}
innodb_log_file_size=$cfg{'innodb_log_file_size'}
(EOF)
close(FILE);
## Start the backup...
my $pid = undef;
if (defined($pid = fork)) {
if ($pid) {
# parent process
$ibbackup_pid = $pid;
} else {
# child process
print Child process started\n;
print Command: ibbackup --suspend-at-end --compress
/etc/my.cnf /storage/backups/backup-$date/my-backup.cnf\n;
exec(ibbackup --suspend-at-end --compress /etc/my.cnf
/storage/backups/backup-$date/my-backup.cnf) || die Failed to exec
ibbackup: $!;
}
} else {
die failed to fork ibbackup child process: $!;
}
print Waiting for child to create
/storage/backups/backup-$date/sql/ibbackup_suspended\n;
while(1) {
sleep 5;
$dbh-do(SELECT UNIX_TIMESTAMP());
last if -e
/storage/backups/backup-$date/sql/ibbackup_suspended;
}
print Starting backup of MyISAM tables\n;
## ibbackup is waiting for us to get rid of the suspended file.
## start backing up the files.
print Locking tables\n;
$dbh-do(DROP TABLE IF EXISTS ibbackup_binlog_marker);
$dbh-do(CREATE TABLE ibbackup_binlog_marker(a INT) TYPE=INNODB);
$dbh-{AutoCommit} = 0;
$dbh-do(INSERT INTO ibbackup_binlog_marker VALUES (1));
$dbh-do(FLUSH TABLES WITH READ LOCK);
$dbh-commit();
opendir(DIR,$cfg{'datadir'}) || die can't open directory: $!;
while (my $f = readdir(DIR)) {
next if (!-d $cfg{'datadir'}/$f || $f =~ /^\.{1,2}$/);
mkdir(/storage/backups/backup-$date/sql/$f) || die couldn't
create folder: /storage/backups/backup-$date/$f : $!;
my @list = glob($cfg{'datadir'}/$f/*.{frm,MYD});
my @listidx = glob($cfg{'datadir'}/$f/*.MYI);
for (@list) {
$dbh-do(SELECT UNIX_TIMESTAMP());
print Copying $_\n;
copy($_,/storage/backups/backup-$date$_) || die
couldn't copy file $_: $!;
}
for (@listidx) {
print Copying INDEX $_\n;
$dbh-do(SELECT UNIX_TIMESTAMP());
open(FIDX,$_) || die couldn't open index... ack!;
my $buff;
my $length = read(FIDX,$buff,2048);
#die Can't read index header from $_\n if ($length
1024);
close(FIDX);
open(FIDX,/storage/backups/backup-$date$_) || die
couldn't open file for copy: $_: $!;
if (syswrite(FIDX,$buff) != length($buff)) {
die Error when writing data to
/storage/backups/backup-$date$_: $!\n;
}
close FIDX || die Error on close of
/storage/backups/backup-$date$_: $!\n;
Re: mysql in a jail
chrooted ? Rhino wrote: Maybe you could explain what a jail is. In 20+ years doing systems work I've never heard that term mean anything but a place where criminals are locked up. Rhino - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 11:28 AM Subject: mysql in a jail I have a system running FreeBSD 4.9 four jails. MySQL is running in two of the jails and I am trying to add it to a 3rd jail. Starting the server gets the message: 040302 19:34:15 mysql started 040302 19:34:15 Can't start server : Bind on unix socket: Permission denied 040302 19:34:15 Do you already have another mysqld server running on socket: /tmp/mysql.sock ? 040302 19:34:15 Aborting 040302 19:34:15 /usr/local/libexec/mysqld: Shutdown Complete 040302 19:34:15 mysqld ended The new jail was created by copying the file tree from a jail that had the desired configuration and then doing clean-up as required. In response to the error I tried configuring mysqld to use a different port and socket file. That did not fix the problem. I had forgotten the other two mysql's are using port 3306 and /tmp/mysql.sock. I next removed mysql and its dependencies and reinstalled mysql-server-4.0.16 using pkg_add. I am still getting the same error. At this point I think it is a jail problem with something I missed in clean-up but I do not know where to look next. Having done all this, I recalled in installing the 2nd MySQL jail, it was necessary to remove and reinstall mysql. In that instance the re-installation solved the problem. Thanks for any ideas. _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- 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: mysql in a jail
Jail is a technique available on FreeBSD to have isolated environments running on a common operating system (something similar to virtual machines, but shares memory as main difference). More detailed about this is available at: http://www.freebsd.org/doc/en_US.ISO8859-1/books/arch-handbook/jail.html []s... Conrado - Original Message - From: Rhino [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 2:22 PM Subject: Re: mysql in a jail Maybe you could explain what a jail is. In 20+ years doing systems work I've never heard that term mean anything but a place where criminals are locked up. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question on root access
Here is the user, host and password fields from one of my MySQL servers named TestServer mysql select user, host, password from user; +++--+ | user | host | password | +++--+ | root | localhost | hekw8838hdd8938d | | root | TestServer | | || localhost | | || TestServer | | | root | 192.% | hekw8838hdd8938d | +++--+ 6 rows in set (0.03 sec) The first and last entries were created by me and are fine. The 3rd and fourth are default entries which block everyone access (all the privileges are 'N' for those But what about the second entry? I did not create it is it a security risk since it has no password? I tested it to be sure and cannot even log in on the local machine using the host parameter 'TestServer' TestServer:~ admin$ mysql -u root -h TestServer -p Enter password: ERROR 2005: Unknown MySQL Server Host 'TestServer' (1) TestServer:~ admin$ Does the mysql daemon use this particular one for access?? ___ Kieran Kelleher Director of Product Development SmartleadsUSA,LLC 2656 West Lake Rd Palm Harbor, FL 34684 [EMAIL PROTECTED] 727-785-0766 x33
Re: PgSQL vs MySQL
As a DBA and someone who has worked both with PostgresQL and MySQL, I think I can answer this knowingly. First, MySQL is significantly faster than PostgresQL and Oracle. Second, MySQL is also a simpler database to set up and configure. Third, the documentation is better, and there are far more third party books out there. Fourth, MySQL has a more impressive list of customers. Yes, there are some large PostgresQL customers (the .org domain system?), but none like Yahoo and Slashdot. MySQL does not have triggers, stored procedures or views yet. Sub-selects should be out in six months. After fighting with PostgresQL to try to get it to use indexes, rewriting tonnes of queries, and still getting poor performance, I gave up on it. I prefer MySQL with InnoDB. Some of the gotcha's are valid, and others can be found in any database. Forewarned is forearmed. David. - Original Message - From: [EMAIL PROTECTED] To: Mark Warner [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 6:51 AM Subject: Re: PgSQL vs MySQL What advantages, besides ease of setup, does MySQL hold over PostgreSQL? It would seem, to me, that the two are close competitors (both in quality, and performance). Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html You can check out postgresql's on the same site but they are significally less critical. After reviewing this and talking to some other people i'm switching over to postgresql. I'm sure there is a place with mysql but I don't think i'd trust it for anything critical unless you very confident your developers know what they are doing. -- 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: import ing files into mysql
Ok! =) YES was the short answer! The long answer is: it will deeply in the choice of language you choose (php, java, perl) and the choice of access you use to communicate with the server (web, client-server, web service) On Wed, 2004-03-03 at 13:32, Manda Sairam wrote: I would like to import zip file into mysql database. Is this possible? regards sairam - Do you Yahoo!? Yahoo! Search - Find what youre looking for faster. -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...|
Re: PgSQL vs MySQL
from the manual it appears that char(0) null default null can be
used as a boolean, will the values of either null or . haven't
tried it myself, but its documented.
http://www.mysql.com/documentation/mysql/bychapter/manual_Column_types.html#Column_types
[NATIONAL] CHAR(M) [BINARY | ASCII | UNICODE]
This is also quite nice when you need a column that only can
take 2 values: A CHAR(0), that is not defined as NOT NULL, will only
occupy one bit and can only take 2 values: NULL or . See section
6.2.3.1 The CHAR and VARCHAR Types.
On Wednesday 03 March 2004 11:43, Curtis Maurand wrote:
:-)
someflag enum('TRUE','FALSE');
Not quite boolean, but it works.
Curtis
On Wed, 3 Mar 2004, Mark Warner wrote:
The thing which bothers me most about MySQL is the lack of a
proper boolean. I don't like having to abstract a tinyint(1) into
true or false. As much of my work involves building applications
with Yes or No questions, I think I am switching to PostgreSQL.
[EMAIL PROTECTED] wrote:
What advantages, besides ease of setup, does MySQL hold over
PostgreSQL? It would seem, to me, that the two are close
competitors (both in quality, and performance).
Are you sure about quality? Check out:
http://sql-info.de/mysql/gotchas.html
You can check out postgresql's on the same site but they are
significally less critical. After reviewing this and talking
to some other people i'm switching over to postgresql. I'm
sure there is a place with mysql but I don't think i'd trust it
for anything critical unless you very confident your developers
know what they are doing.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: imposible cargar datos en tablas
At 15:33 3/3/2004, Marcelo Rodriguez Salinas wrote: Me podrian dcir el por que sucede esto dependiendo de la versión de MySQL es un bug con el wait_timeout al lado del cliente. Usa las últimas versiones. Error Code:2013 Lost connection to Mysql server during query Atentamente Regards, For technical support contracts, visit https://order.mysql.com/ Are you MySQL certified?, http://www.mysql.com/certification/ Miguel Angel Solórzano [EMAIL PROTECTED] São Paulo - Brazil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
One other point that I forgot I mentioned - PostgresQL does not have a decent replication solution. There are a few solutions, including one from PostgreSQL.com. The PostgresQL.com version is not the latest - you need to pay for support to get that. The other ones were (last I looked) incomplete. Davi. - Original Message - From: David Griffiths [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Mark Warner [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 10:27 AM Subject: Re: PgSQL vs MySQL As a DBA and someone who has worked both with PostgresQL and MySQL, I think I can answer this knowingly. First, MySQL is significantly faster than PostgresQL and Oracle. Second, MySQL is also a simpler database to set up and configure. Third, the documentation is better, and there are far more third party books out there. Fourth, MySQL has a more impressive list of customers. Yes, there are some large PostgresQL customers (the .org domain system?), but none like Yahoo and Slashdot. MySQL does not have triggers, stored procedures or views yet. Sub-selects should be out in six months. After fighting with PostgresQL to try to get it to use indexes, rewriting tonnes of queries, and still getting poor performance, I gave up on it. I prefer MySQL with InnoDB. Some of the gotcha's are valid, and others can be found in any database. Forewarned is forearmed. David. - Original Message - From: [EMAIL PROTECTED] To: Mark Warner [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 6:51 AM Subject: Re: PgSQL vs MySQL What advantages, besides ease of setup, does MySQL hold over PostgreSQL? It would seem, to me, that the two are close competitors (both in quality, and performance). Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html You can check out postgresql's on the same site but they are significally less critical. After reviewing this and talking to some other people i'm switching over to postgresql. I'm sure there is a place with mysql but I don't think i'd trust it for anything critical unless you very confident your developers know what they are doing. -- 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: PgSQL vs MySQL
As a DBA I have a few questions about what you said here. You have worked with both PostgreSQL and MySQL, and yet you say that MySQL is 'signifigantly' faster than Oracle? Can you PROVE that? And how is it faster to chase down data problems when MySQL has no native constraints in it data design? Wouldn't you think that since MySQL is 'simpler' to set up and configure that it just lends itself to poor design principles and that you will constantly be fighting with it after a point? Or migrating to something else? Can't argue with the third point at all. In all of this, isn't it really InnoDB that you like, and not MySQL specifically? -James -Original Message- From: David Griffiths [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 10:28 AM To: [EMAIL PROTECTED]; Mark Warner; [EMAIL PROTECTED] Subject: Re: PgSQL vs MySQL As a DBA and someone who has worked both with PostgresQL and MySQL, I think I can answer this knowingly. First, MySQL is significantly faster than PostgresQL and Oracle. Second, MySQL is also a simpler database to set up and configure. Third, the documentation is better, and there are far more third party books out there. Fourth, MySQL has a more impressive list of customers. Yes, there are some large PostgresQL customers (the .org domain system?), but none like Yahoo and Slashdot. MySQL does not have triggers, stored procedures or views yet. Sub-selects should be out in six months. After fighting with PostgresQL to try to get it to use indexes, rewriting tonnes of queries, and still getting poor performance, I gave up on it. I prefer MySQL with InnoDB. Some of the gotcha's are valid, and others can be found in any database. Forewarned is forearmed. David. - Original Message - From: [EMAIL PROTECTED] To: Mark Warner [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 6:51 AM Subject: Re: PgSQL vs MySQL What advantages, besides ease of setup, does MySQL hold over PostgreSQL? It would seem, to me, that the two are close competitors (both in quality, and performance). Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html You can check out postgresql's on the same site but they are significally less critical. After reviewing this and talking to some other people i'm switching over to postgresql. I'm sure there is a place with mysql but I don't think i'd trust it for anything critical unless you very confident your developers know what they are doing. -- 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: read only table 'user' at install
nevermind that webmaster stuff...wrong 'send as' setting... On Wed, 2004-03-03 at 12:59, HPGM Webmaster wrote: 1.- did you create the data dir as root? chown mysql.mysql -R /var/dir-where-you-install Well, the data dir was created when I untarred mysql... 2.- who owns the mysql data dir and it's parent dirs? drwxrwxr-x4 mysqlmysql4096 Mar 3 21:07 data and the parent dir is owned by root, should I try making the /usr/local/mysql dir owned by mysql? ls -lka /var/dir-where-you-installed 3.- try using mysql client Never used this before, I will try... here is an ls -la from /usr/local/mysql [EMAIL PROTECTED] mysql]# ls -la /usr/local/mysql total 92 drwxrwx--- 14 root mysql4096 Mar 3 02:35 . drwxr-xr-x 15 root root 4096 Mar 3 02:27 .. drwxrwx---2 root mysql4096 Feb 10 12:59 bin -rwxrwx---1 root mysql 773 Feb 10 12:59 configure -rwxrwx---1 root mysql 19106 Feb 10 12:50 COPYING drwxrwxr-x4 mysqlmysql4096 Mar 3 21:07 data drwxrwx---2 root mysql4096 Feb 10 12:59 docs drwxrwx---2 root mysql4096 Feb 10 12:59 include -rwxrwx---1 root mysql7633 Feb 10 12:50 INSTALL-BINARY drwxrwx---2 root mysql4096 Feb 10 12:59 lib drwxrwx---3 root mysql4096 Feb 10 12:59 man drwxrwx---6 root mysql4096 Feb 10 12:59 mysql-test -rwxrwx---1 root mysql1937 Feb 10 12:15 README drwxrwx---2 root mysql4096 Feb 10 12:59 scripts drwxrwx---3 root mysql4096 Feb 10 12:59 share drwxrwx---5 root mysql4096 Feb 10 12:59 sql-bench drwxrwx---2 root mysql4096 Feb 10 12:59 support-files drwxrwx---2 root mysql4096 Feb 10 12:59 tests On Wed, 2004-03-03 at 07:27, Victor Medina wrote: A few things to try: 1.- did you create the data dir as root? chown mysql.mysql -R /var/dir-where-you-install 2.- who owns the mysql data dir and it's parent dirs? ls -lka /var/dir-where-you-installed 3.- try using mysql client Best Regards On Tue, 2004-03-02 at 21:55, Jonathan Villa wrote: Ok, I have installed MySQL many times and have never come across this... when try to run ./bin/mysqladmin -u root password 'new-password' I get the following ./bin/mysqladmin: unable to change password; error: 'Table 'user' is read only' I've never had this happen, and unfortunately do not know enough about read/write perms on tables to try to debug it. I promise that I'll study up on it, but right now I'm kinda of in rush... I'm using : mysql-standard-4.0.18-pc-linux-i686.tar.gz thanks! -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: PgSQL vs MySQL
I do not approve or disapprove of your choice of Postgres over MySQL. I don't profess to be a postgresql expert, i'm still learning about it.. but a quick glance at the documentation tells me that the only option would be to use pg_dump utility which creates a sql file. I haven't looked at one of these files but I guess to do a point in time recovery you would delete all of the sql after that PIT. You could also possibly write a script to backup the data in individual tables by locking the table and then 'selecting' out the data like in mysql. Like I said, i'm not an expert in either mysql or postgresql and each dbms has its place.. Its just I would probably use postgresql for a more critical database. Actually, if the database is critical i'd be using oracle. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
Hm. I am not a professional DBA, but I have used both MySQL and PostreSQL for the same application, and I can say that I am more satisfied with PgSQL. It is faster and more reliable, on the platform that I have built (Dual Opteron 244 w/ 3GB of PC3200). While I had few technical problems with MySQL, I just never felt comfortable using it, so I have switched to PgSQL for everything. Serials (aka auto-increment in MySQL) work fantastically. The ability to SET the value of the serial is useful. I tried for weeks to replicate that functionaility with MySQL, and failed. The core superiority of the PgSQL SERIAL, as opposed to the MySQL AUTO-INCREMENT lies in the fact that the serials' values are stored in a table, and can be set, read, and predicted accurately, and easily. My database is somewhat small (6-8GB), and PgSQL offered an almost 15% performance increase over MySQL, for the same data. Additionally, my support code was simplified drastically by some of the core functions of PgSQL. For this reason, I have abandoned MySQL, and switched to PgSQL. Also, in the past week, there have been about 10 times as many technical support problems in this mailing list, as in the relative PgSQL list. This supports my belief that PgSQL is a better DB platform. Thank you all for your information. Good Day, Mark Warner. David Griffiths wrote: One other point that I forgot I mentioned - PostgresQL does not have a decent replication solution. There are a few solutions, including one from PostgreSQL.com. The PostgresQL.com version is not the latest - you need to pay for support to get that. The other ones were (last I looked) incomplete. Davi. - Original Message - From: David Griffiths [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Mark Warner [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 10:27 AM Subject: Re: PgSQL vs MySQL As a DBA and someone who has worked both with PostgresQL and MySQL, I think I can answer this knowingly. First, MySQL is significantly faster than PostgresQL and Oracle. Second, MySQL is also a simpler database to set up and configure. Third, the documentation is better, and there are far more third party books out there. Fourth, MySQL has a more impressive list of customers. Yes, there are some large PostgresQL customers (the .org domain system?), but none like Yahoo and Slashdot. MySQL does not have triggers, stored procedures or views yet. Sub-selects should be out in six months. After fighting with PostgresQL to try to get it to use indexes, rewriting tonnes of queries, and still getting poor performance, I gave up on it. I prefer MySQL with InnoDB. Some of the gotcha's are valid, and others can be found in any database. Forewarned is forearmed. David. - Original Message - From: [EMAIL PROTECTED] To: Mark Warner [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 6:51 AM Subject: Re: PgSQL vs MySQL What advantages, besides ease of setup, does MySQL hold over PostgreSQL? It would seem, to me, that the two are close competitors (both in quality, and performance). Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html You can check out postgresql's on the same site but they are significally less critical. After reviewing this and talking to some other people i'm switching over to postgresql. I'm sure there is a place with mysql but I don't think i'd trust it for anything critical unless you very confident your developers know what they are doing. -- 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]
CREATE TEMPORARY TABLE
I am trying to create a temporary table through code in my DB. I can do
it when I use MySQL Control Center or something like that, but when I
try to do it through my VB program it doesn't work.
Here is the sql string:
CREATE TEMPORARY TABLE IndenturedList (Level_1 TEXT, Level_2 TEXT,
AssemblyNumber TEXT, NSIPartNumber TEXT, RecordID INT NOT NULL
AUTO_INCREMENT, PRIMARY KEY (RecordID));
If I paste this into MySQL Control Center I have no problem creating
it. Here is the code from my VB program:
Dim cn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim fld As ADODB.Field
Dim DataArray() As String
Dim i As Long
Dim lngRecordAffected As Long
Set cn = New ADODB.Connection
strSQL = CREATE TEMPORARY TABLE IndenturedList (Level_1 TEXT,
Level_2 TEXT, AssemblyNumber TEXT, NSIPartNumber TEXT, RecordID INT NOT
NULL AUTO_INCREMENT, PRIMARY KEY (RecordID));
cn.Open Driver={MySQL ODBC 3.51 Driver}; Server= gStrSQLServer
;database=NSITbls; uid= gStrCurrentUser ; Password=
gStrCurrentUser ;
cn.Execute strSQL, lngRecordAffected
Nothing happens when I run this code. Is the SQL string only for a DAO
connection? The reason I ask is because when I need to write data to
the db I just change the SQL string and it works.
Does anyone know what is wrong?
Thank you,
Jacque
Re: Fw: imposible cargar datos en tablas
Que es la version de MySQL. De la computadora? Original Message On 3/3/04, 12:42:25 PM, Miguel Angel Solorzano [EMAIL PROTECTED] wrote regarding Re: Fw: imposible cargar datos en tablas: At 15:33 3/3/2004, Marcelo Rodriguez Salinas wrote: Me podrian dcir el por que sucede esto dependiendo de la versión de MySQL es un bug con el wait_timeout al lado del cliente. Usa las últimas versiones. Error Code:2013 Lost connection to Mysql server during query Atentamente Regards, For technical support contracts, visit https://order.mysql.com/ Are you MySQL certified?, http://www.mysql.com/certification/ Miguel Angel Solórzano [EMAIL PROTECTED] São Paulo - Brazil -- 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: CREATE TEMPORARY TABLE
In your VB code, are your connections pooled, or are you using the same
connection for each database call?
Original Message
On 3/3/04, 1:12:07 PM, Jacque Scott [EMAIL PROTECTED] wrote regarding
CREATE TEMPORARY TABLE:
I am trying to create a temporary table through code in my DB. I can do
it when I use MySQL Control Center or something like that, but when I
try to do it through my VB program it doesn't work.
Here is the sql string:
CREATE TEMPORARY TABLE IndenturedList (Level_1 TEXT, Level_2 TEXT,
AssemblyNumber TEXT, NSIPartNumber TEXT, RecordID INT NOT NULL
AUTO_INCREMENT, PRIMARY KEY (RecordID));
If I paste this into MySQL Control Center I have no problem creating
it. Here is the code from my VB program:
Dim cn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim fld As ADODB.Field
Dim DataArray() As String
Dim i As Long
Dim lngRecordAffected As Long
Set cn = New ADODB.Connection
strSQL = CREATE TEMPORARY TABLE IndenturedList (Level_1 TEXT,
Level_2 TEXT, AssemblyNumber TEXT, NSIPartNumber TEXT, RecordID INT NOT
NULL AUTO_INCREMENT, PRIMARY KEY (RecordID));
cn.Open Driver={MySQL ODBC 3.51 Driver}; Server= gStrSQLServer
;database=NSITbls; uid= gStrCurrentUser ; Password=
gStrCurrentUser ;
cn.Execute strSQL, lngRecordAffected
Nothing happens when I run this code. Is the SQL string only for a DAO
connection? The reason I ask is because when I need to write data to
the db I just change the SQL string and it works.
Does anyone know what is wrong?
Thank you,
Jacque
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: imposible cargar datos en tablas
At 16:20 3/3/2004, [EMAIL PROTECTED] wrote: Que es la version de MySQL. De la computadora? 4.0.XX, más especificamente me refiero a la versión 4.0.15. No es la computadora. Regards, For technical support contracts, visit https://order.mysql.com/ Are you MySQL certified?, http://www.mysql.com/certification/ Miguel Angel Solórzano [EMAIL PROTECTED] São Paulo - Brazil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
We did benchmarking, with identical schemas on identical hardware. Second, we use InnoDB, which does have constraints, transactions and row locking. Not sure where this significnatly out-dated idea that MySQL has no data integrity comes from, but it's false if you use BDB or InnoDB. It will soon be false for MyISAM as well. I found MySQL (both MyISAM and InnoDB) simpler in that it's setup more closely corresponded to other software I was familiar with. MySQL is as simple as you need to be. You have the option of tweaking advanced variables if you need to. I spent alot of time reading up on PostgresQL performance tuning, and was surprised at how little was known. For example, this page, http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html is linked to frequently in the PostgresQL mailing lists as a good resource for performance tuning. Some of the parameters discussed include random_page_cost, cpu_tuple_cost, cpu_index_tuple_cost, and cpu_operator_cost. The explanation? The default costs are based entirely on anecdotal experience, and are probably not ideal for your system. In fact, many of the parameters have a comment attached stating that you have to run alot of queries to figure out the right balance, and that finding the ... the right balance spot can be hard. (that quote is from the sort_mem parameter comment). Our database evolves fairly quickly (a few changes a month). We don't have time to re-test any query that might be affected by adding columns to a table, altering columns in a table, etc. The optimizer in PostgresQL is it's achilles heel. Do a search on the PostgresQL site for optimizer index and see how often the optimizer makes the wrong choice. I have yet to see a case where MySQL could have used an index but did not. InnoDB is MyQL. MySQL offeres multiple persistence engines - InnoDB, MyISAM (ISAM), heap, and BDB. It is shipped by default with MySQL. David - Original Message - From: James Kelty [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 10:51 AM Subject: RE: PgSQL vs MySQL As a DBA I have a few questions about what you said here. You have worked with both PostgreSQL and MySQL, and yet you say that MySQL is 'signifigantly' faster than Oracle? Can you PROVE that? And how is it faster to chase down data problems when MySQL has no native constraints in it data design? Wouldn't you think that since MySQL is 'simpler' to set up and configure that it just lends itself to poor design principles and that you will constantly be fighting with it after a point? Or migrating to something else? Can't argue with the third point at all. In all of this, isn't it really InnoDB that you like, and not MySQL specifically? -James -Original Message- From: David Griffiths [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 10:28 AM To: [EMAIL PROTECTED]; Mark Warner; [EMAIL PROTECTED] Subject: Re: PgSQL vs MySQL As a DBA and someone who has worked both with PostgresQL and MySQL, I think I can answer this knowingly. First, MySQL is significantly faster than PostgresQL and Oracle. Second, MySQL is also a simpler database to set up and configure. Third, the documentation is better, and there are far more third party books out there. Fourth, MySQL has a more impressive list of customers. Yes, there are some large PostgresQL customers (the .org domain system?), but none like Yahoo and Slashdot. MySQL does not have triggers, stored procedures or views yet. Sub-selects should be out in six months. After fighting with PostgresQL to try to get it to use indexes, rewriting tonnes of queries, and still getting poor performance, I gave up on it. I prefer MySQL with InnoDB. Some of the gotcha's are valid, and others can be found in any database. Forewarned is forearmed. David. - Original Message - From: [EMAIL PROTECTED] To: Mark Warner [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 6:51 AM Subject: Re: PgSQL vs MySQL What advantages, besides ease of setup, does MySQL hold over PostgreSQL? It would seem, to me, that the two are close competitors (both in quality, and performance). Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html You can check out postgresql's on the same site but they are significally less critical. After reviewing this and talking to some other people i'm switching over to postgresql. I'm sure there is a place with mysql but I don't think i'd trust it for anything critical unless you very confident your developers know what they are doing. -- 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:
Re: PgSQL vs MySQL
Curtis Maurand wrote:
:-)
someflag enum('TRUE','FALSE');
Not quite boolean, but it works.
Curtis
On Wed, 3 Mar 2004, Mark Warner wrote:
The thing which bothers me most about MySQL is the lack of a proper
boolean. I don't like having to abstract a tinyint(1) into true or
false. As much of my work involves building applications with Yes or No
questions, I think I am switching to PostgreSQL.
SNIP
To my mind, a boolean is something that can be used in a boolean
context. That is, if flag is a boolean, the following should work as
expected:
SELECT * FROM mytable WHERE flag; # rows with flag = TRUE
SELECT * FROM mytable WHERE NOT flag; # rows with flag = FALSE
If you have to compare the column's value to something, it isn't really
a boolean value.
MySQL, like most programming languages, treats 0 as FALSE and any other
number as TRUE. That means that boolean expressions are evaluated
numerically and compared to 0.
In a numeric context, ENUM columns return the value's position in the
list of allowed values, starting with 1. This means that with the
definition
someflag enum('TRUE','FALSE')
someflag evaluates as 1 when it is 'TRUE' and 2 when it is 'FALSE', both
of which are TRUE in boolean context. Hence, you cannot use someflag in
boolean context and get the expected results. In other words, someflag
looks like a boolean if you view the data, but doesn't behave as a
boolean in queries.
I don't quite know what Mark Warner means by abstract a tinyint(1) into
true or false, but I personally find using tinyint for boolean to be a
simple solution. I define someflag TINYINT, then set it to 0 for
FALSE and 1 (or any other number) for true. Of course, if you're not a
programmer it won't look like a boolean when viewing the data (is that
what you don't like, Mark?), but it will behave as one.
With tinyint you get some added flexibility, which may be an advantage,
depending on your application. For example, if I store the number of
children a person has in the tinyint kids, I can use kids in boolean
context:
SELECT * FROM persontable WHERE kids; #people who have children
SELECT * FROM persontable WHERE NOT kids; #people who don't
One more advantage of tinyints is that, with common programming
languages, they continue to function as booleans in your application.
Another option is to take advantage of the special error value in every
ENUM. If you insert a wrong value, mysql replaces it with '', which is
always 0 in numeric context. So, you could define
someflag ENUM ('TRUE', 'T');
If you insert 'FALSE' or 'F' (or 'false' or 'f') into someflag, it will
get the error value, 0 or ''. When you view the data, you will see
'TRUE' or 'T' for TRUE values and blanks for FALSE, and someflag will
behave as boolean in queries.
If your values are 'Y' and 'N', then, strictly speaking, you don't have
a boolean unless you've defined which is TRUE and which is FALSE.
Assuming you want to treat 'Y' as TRUE, you could define
yesflag ENUM ('YES', 'Y');
Inserting 'YES' or 'Y' (or 'yes' or 'y') will get you the corresponding
entries, which are TRUE in boolean context. Inserting 'NO' or 'N' (or
'no' or 'n') will get you blanks, which are FALSE in boolean context.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
Michael,
I couldn't agree more! I didn't even realize there was a boolean issue
until now. I don't really understand the issue of using 0 as false, and
1 as true. The way I view it is that true and false are merely
abstract names for 0 and 1. When the database is accessed using Java, I
don't even see the 0 and 1. For example, using the JDBC API,
pStmt.setBoolean(1, true) command sets the field to 1, and
rs.getBoolean('booleanField') returns true if 1, and false if 0.
So again, I reiterate, what's the issue?
Kevin
On Wed, 2004-03-03 at 11:31, Michael Stassen wrote:
Curtis Maurand wrote:
:-)
someflag enum('TRUE','FALSE');
Not quite boolean, but it works.
Curtis
On Wed, 3 Mar 2004, Mark Warner wrote:
The thing which bothers me most about MySQL is the lack of a proper
boolean. I don't like having to abstract a tinyint(1) into true or
false. As much of my work involves building applications with Yes or No
questions, I think I am switching to PostgreSQL.
SNIP
To my mind, a boolean is something that can be used in a boolean
context. That is, if flag is a boolean, the following should work as
expected:
SELECT * FROM mytable WHERE flag; # rows with flag = TRUE
SELECT * FROM mytable WHERE NOT flag; # rows with flag = FALSE
If you have to compare the column's value to something, it isn't really
a boolean value.
MySQL, like most programming languages, treats 0 as FALSE and any other
number as TRUE. That means that boolean expressions are evaluated
numerically and compared to 0.
In a numeric context, ENUM columns return the value's position in the
list of allowed values, starting with 1. This means that with the
definition
someflag enum('TRUE','FALSE')
someflag evaluates as 1 when it is 'TRUE' and 2 when it is 'FALSE', both
of which are TRUE in boolean context. Hence, you cannot use someflag in
boolean context and get the expected results. In other words, someflag
looks like a boolean if you view the data, but doesn't behave as a
boolean in queries.
I don't quite know what Mark Warner means by abstract a tinyint(1) into
true or false, but I personally find using tinyint for boolean to be a
simple solution. I define someflag TINYINT, then set it to 0 for
FALSE and 1 (or any other number) for true. Of course, if you're not a
programmer it won't look like a boolean when viewing the data (is that
what you don't like, Mark?), but it will behave as one.
With tinyint you get some added flexibility, which may be an advantage,
depending on your application. For example, if I store the number of
children a person has in the tinyint kids, I can use kids in boolean
context:
SELECT * FROM persontable WHERE kids; #people who have children
SELECT * FROM persontable WHERE NOT kids; #people who don't
One more advantage of tinyints is that, with common programming
languages, they continue to function as booleans in your application.
Another option is to take advantage of the special error value in every
ENUM. If you insert a wrong value, mysql replaces it with '', which is
always 0 in numeric context. So, you could define
someflag ENUM ('TRUE', 'T');
If you insert 'FALSE' or 'F' (or 'false' or 'f') into someflag, it will
get the error value, 0 or ''. When you view the data, you will see
'TRUE' or 'T' for TRUE values and blanks for FALSE, and someflag will
behave as boolean in queries.
If your values are 'Y' and 'N', then, strictly speaking, you don't have
a boolean unless you've defined which is TRUE and which is FALSE.
Assuming you want to treat 'Y' as TRUE, you could define
yesflag ENUM ('YES', 'Y');
Inserting 'YES' or 'Y' (or 'yes' or 'y') will get you the corresponding
entries, which are TRUE in boolean context. Inserting 'NO' or 'N' (or
'no' or 'n') will get you blanks, which are FALSE in boolean context.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PgSQL vs MySQL
On Wed, Mar 03, 2004 at 10:51:40AM -0800, James Kelty wrote: As a DBA I have a few questions about what you said here. You have worked with both PostgreSQL and MySQL, and yet you say that MySQL is 'signifigantly' faster than Oracle? Can you PROVE that? For a given set of data and workload, of course you can. And how is it faster to chase down data problems when MySQL has no native constraints in it data design? What about MySQL's constraints are not native? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 171 days, processed 2,369,026,392 queries (159/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql 4.1.1 crashes
Lior, this is probably the subquery bug that was fixed in the 4.1 source tree about 2 months ago. The seg fault happens because MySQL-4.1.1 releases table 'intention' locks too early, and InnoDB closes the consistent 'read view' of the transaction. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html List:MySQL General Discussion« Previous MessageNext Message » From: Nisim, Lior Date: March 3 2004 1:02 pm Subject: RE: Mysql 4.1.1 crashes Hi Here is the stack trace : 0x8106af3 handle_segfault + 423 0x40047a65 _end + 933903229 0x832b873 lock_clust_rec_cons_read_sees + 111 0x82774fc row_search_for_mysql + 9884 0x81783cb general_fetch__11ha_innobasePcUiUi + 75 0x8178496 index_next_same__11ha_innobasePcPCcUi + 34 0x813eecc join_read_next_same__FP14st_read_record + 52 0x813e57e sub_select__FP4JOINP13st_join_tableb + 330 0x813e226 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 434 0x8134b36 exec__4JOIN + 4234 0x8135068 mysql_select__FP3THDPPP4ItemP13st_table_listUiRt4List1Z4ItemP4ItemUiP8st _orderT7T5T7UlP13select_resultP18st_select_lex_unitP13s + 832 0x8131fde handle_select__FP3THDP6st_lexP13select_result + 174 0x81142b7 mysql_execute_command__FP3THD + 1427 0x8118d49 mysql_parse__FP3THDPcUi + 177 0x8112f3f dispatch_command__F19enum_server_commandP3THDPcUi + 1635 0x81128d1 do_command__FP3THD + 161 0x8112047 handle_one_connection + 563 0x40044c3f _end + 933891415 0x401b5b2a _end + 935402562 Thanks Lior -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 02, 2004 10:08 PM To: Nisim, Lior Cc: [EMAIL PROTECTED] Subject: Re: Mysql 4.1.1 crashes Did you run a stack trace? Original Message On 3/2/04, 9:02:15 AM, Nisim, Lior [EMAIL PROTECTED] wrote regarding Mysql 4.1.1 crashes: hi My server crashes on sub qurey , can any one help ? --- 040302 16:32:20 mysqld started 040302 16:32:20 InnoDB: Started; log sequence number 0 60169 /tmp/mysql/mysql_4.1.1/bin/mysqld: ready for connections. Version: '4.1.1-alpha-max' socket: '/tmp/mysql.sock' port: 3306 mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=0 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x867e9a0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x424a6838, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8106af3 0x40047a65 0x832b873 0x82774fc 0x81783cb 0x8178496 0x813eecc 0x813e57e 0x813e226 0x8134b36 0x8135068 0x8131fde 0x81142b7 0x8118d49 0x8112f3f 0x81128d1 0x8112047 0x40044c3f 0x401b5b2a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x8685398 = SELECT milestone,date,user,notes FROM milestones_info WHERE block='agadderc' group by milestone having date=(SELECT max(date) FROM milestones_info as tmp WHERE block='agadderc' AND milestone=tmp.milestone) thd-thread_id=1 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 040302 16:32:31 mysqld restarted 040302 16:32:31 InnoDB: Started; log sequence number 0 60169 /tmp/mysql/mysql_4.1.1/bin/mysqld: ready for connections. Version: '4.1.1-alpha-max' socket: '/tmp/mysql.sock' port: 3306 --- Description: mysql crashes on sub query How-To-Repeat: check the below error log Fix: how to correct or work around the problem, if known (multiple lines)
RE: PgSQL vs MySQL
I have an extrememly query heavy site that I tried to switch from MySQL to PgSQL. And after spending literally a week reconfiguring thousands of queries and rewriting code, I finally had the pgSQL version of the site live, but when I had even a trickle of users on the site it was HORRENDOUSLY slow. Now, if I had optimized my code and my queries for another 3 weeks, I may have had something that was usable. But I will take the speed of MySQL and live without some of the niceties of PgSQL. Jeremy -Original Message- From: David Griffiths [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 1:28 PM To: [EMAIL PROTECTED]; Mark Warner; [EMAIL PROTECTED] Subject: Re: PgSQL vs MySQL As a DBA and someone who has worked both with PostgresQL and MySQL, I think I can answer this knowingly. First, MySQL is significantly faster than PostgresQL and Oracle. Second, MySQL is also a simpler database to set up and configure. Third, the documentation is better, and there are far more third party books out there. Fourth, MySQL has a more impressive list of customers. Yes, there are some large PostgresQL customers (the .org domain system?), but none like Yahoo and Slashdot. MySQL does not have triggers, stored procedures or views yet. Sub-selects should be out in six months. After fighting with PostgresQL to try to get it to use indexes, rewriting tonnes of queries, and still getting poor performance, I gave up on it. I prefer MySQL with InnoDB. Some of the gotcha's are valid, and others can be found in any database. Forewarned is forearmed. David. - Original Message - From: [EMAIL PROTECTED] To: Mark Warner [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 6:51 AM Subject: Re: PgSQL vs MySQL What advantages, besides ease of setup, does MySQL hold over PostgreSQL? It would seem, to me, that the two are close competitors (both in quality, and performance). Are you sure about quality? Check out: http://sql-info.de/mysql/gotchas.html You can check out postgresql's on the same site but they are significally less critical. After reviewing this and talking to some other people i'm switching over to postgresql. I'm sure there is a place with mysql but I don't think i'd trust it for anything critical unless you very confident your developers know what they are doing. -- 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: Question on root access
Kieran Kelleher wrote: Here is the user, host and password fields from one of my MySQL servers named TestServer mysql select user, host, password from user; +++--+ | user | host | password | +++--+ | root | localhost | hekw8838hdd8938d | | root | TestServer | | || localhost | | || TestServer | | | root | 192.% | hekw8838hdd8938d | +++--+ 6 rows in set (0.03 sec) The first and last entries were created by me and are fine. The 3rd and fourth are default entries which block everyone access (all the privileges are 'N' for those But what about the second entry? I did not create it is it a security risk since it has no password? I tested it to be sure and cannot even log in on the local machine using the host parameter 'TestServer' The first four entries were created by default (mysql_install_db), and you followed the directions to set a password for the first one. The second entry says root can connect via tcp *from* the machine with hostname TestServer with no password. Presumably, TestServer is (or was) the hostname of your machine. TestServer:~ admin$ mysql -u root -h TestServer -p Here you try to connect via tcp *to* the mysql server on the machine with hostname TestServer. Enter password: ERROR 2005: Unknown MySQL Server Host 'TestServer' (1) TestServer:~ admin$ Apparently, TestServer is not running mysqld, or couldn't be found. I'd guess a DNS lookup on TestServer failed to retrieve an IP. Does the mysql daemon use this particular one for access?? Since root can connect from localhost (line 1) and from any machine on the local net (192.%, line 5), you can safely remove the [EMAIL PROTECTED] user. It is my impression that most people also remove the two anonymous users (lines 3 and 4). Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb table space getting filled up without any increase in actual rows!!
Sp. Raja, please check with SHOW INNODB STATUS\G if purge is still running and removing delete-marked rows. Also check that you do not have old, dangling transactions, which can prevent purge from running, as those old transactions could still see the delete-marked rows. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html List:MySQL General Discussion« Previous MessageNext Message » From:Sp.RajaDate:March 3 2004 8:48am Subject:Fw: Innodb table space getting filled up without any increase in actual rows!! I missed attaching the trace file! Sorry !! Regards, Sp.Raja Original Message From: Sp.Raja [EMAIL PROTECTED] To: '[EMAIL PROTECTED]' [EMAIL PROTECTED] Date: Wed, Mar-3-2004 1:13 PM Subject: Innodb table space getting filled up without any increase in actual rows!! Hi List, My tablespace is getting filled up so quick when no. of transactions increase without number of rows increasing considerably. I have a test client which adds and deletes row dynamically maintaining number of rows at any instant between 20 and 60. When I run this test client, after some time the client emits the following error: [MySQL][ODBC 3.51 Driver][mysqld-4.0.15a-debug]The table 'axactivealarmtbl' is full. I was confused on seeing this. So ran the test again but this time monitoring table status. I noticed that Rows, Data_length and Index_length column increased monotonically and InnoDB free decreased. I was not able to reason why ? But when I used select count(*) from tablename it consistently gave me numbers between 20 and 60 I have attached output of show table status(trace.txt) as I observed taken at increasing time. Any Pointers/Ideas on this to help me resolve this issue?? Thanks, Sp.Raja -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=1com +--+++--++-- ---+-+--+---++-- ---+-+++--+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +--+++--++-- ---+-+--+---++-- ---+-+++--+ | axactivealarmtbl | InnoDB | Dynamic| 467 |210 | 98304 |NULL |98304 | 0 | NULL | NULL | NULL| NULL || InnoDB free: 9216 kB | +--+++--++-- ---+-+--+---++-- ---+-+++--+ +--+++--++-- ---+-+--+---++-- ---+-+++--+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +--+++--++-- ---+-+--+---++-- ---+-+++--+ | axactivealarmtbl | InnoDB | Dynamic| 4946 |321 | 1589248 |NULL | 622592 | 0 | NULL | NULL | NULL| NULL || InnoDB free: 6144 kB | +--+++--++-- ---+-+--+---++-- ---+-+++--+ +--+++--++-- ---+-+--+---++-- ---+-+++--+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +--+++--++-- ---+-+--+---++--
Re: mysql in a jail
Thank you for the replies. Jail is a FreeBSD construction that provides a chroot'ed environment that allows you to create another instance of the entire OS. So in my case the physical server running the jails appears to the world like 5 hosts. Each jail has its own IP and is running an independent version of FreeBSD. The jail API layer keeps one instance of a jail from breaking into jail. Only the /proc file system is shared. The kernel belongs to the base system. I was hoping for a FreeBSD/mysql person who has traveled this road. Failing that, is there some residual file that MySQL uses that could trigger this error? I can tell for sure that: 1) there is no mysql socket active 2) there is no file /tmp/mysql.sock (in this jail). 3) it is possible to do. The only reason I have to suspect something in mysql is that I had to re-install to get my second jail running mysql. The re-install did not work here but I did not build the failing jail from scratch. Hence my thought that I left some cruft behind. The reinstall should have cleared /usr/local of mysql files and I cleared /var/db/mysql and /tmp by hand. I think the MySQL question here is: Can a configuration error cause mysqld to think a socket is open when in fact it is not? On Wed, 3 Mar 2004, Rhino wrote: Maybe you could explain what a jail is. In 20+ years doing systems work I've never heard that term mean anything but a place where criminals are locked up. Rhino - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 11:28 AM Subject: mysql in a jail I have a system running FreeBSD 4.9 four jails. MySQL is running in two of the jails and I am trying to add it to a 3rd jail. Starting the server gets the message: 040302 19:34:15 mysql started 040302 19:34:15 Can't start server : Bind on unix socket: Permission denied 040302 19:34:15 Do you already have another mysqld server running on socket: /tmp/mysql.sock ? 040302 19:34:15 Aborting 040302 19:34:15 /usr/local/libexec/mysqld: Shutdown Complete 040302 19:34:15 mysqld ended The new jail was created by copying the file tree from a jail that had the desired configuration and then doing clean-up as required. In response to the error I tried configuring mysqld to use a different port and socket file. That did not fix the problem. I had forgotten the other two mysql's are using port 3306 and /tmp/mysql.sock. I next removed mysql and its dependencies and reinstalled mysql-server-4.0.16 using pkg_add. I am still getting the same error. At this point I think it is a jail problem with something I missed in clean-up but I do not know where to look next. Having done all this, I recalled in installing the 2nd MySQL jail, it was necessary to remove and reinstall mysql. In that instance the re-installation solved the problem. Thanks for any ideas. _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql in a jail
This error usually occurs for one of two reasons: 1. There is already a /tmp/mysql.sock. You've ruled this out. 2. The user mysqld runs as, typically mysql, does not have permission to write to /tmp. Normally, you would want to `chmod 1777 /tmp`. If that's not appropriate for a jail, just make sure that mysqld can write there. As far as I know, installing (or reinstalling) mysql should have no effect on /tmp permissions, so I would guess that the problem you had previously with the second jail was different. If it turns out that /tmp permissions are not the problem, take a look at the hostname.err (where hostname is the name of the machine from this jail's point of view) file in the data directory. If the contents don't make sense to you, post them in a follow-up message. Michael [EMAIL PROTECTED] wrote: Thank you for the replies. Jail is a FreeBSD construction that provides a chroot'ed environment that allows you to create another instance of the entire OS. So in my case the physical server running the jails appears to the world like 5 hosts. Each jail has its own IP and is running an independent version of FreeBSD. The jail API layer keeps one instance of a jail from breaking into jail. Only the /proc file system is shared. The kernel belongs to the base system. I was hoping for a FreeBSD/mysql person who has traveled this road. Failing that, is there some residual file that MySQL uses that could trigger this error? I can tell for sure that: 1) there is no mysql socket active 2) there is no file /tmp/mysql.sock (in this jail). 3) it is possible to do. The only reason I have to suspect something in mysql is that I had to re-install to get my second jail running mysql. The re-install did not work here but I did not build the failing jail from scratch. Hence my thought that I left some cruft behind. The reinstall should have cleared /usr/local of mysql files and I cleared /var/db/mysql and /tmp by hand. I think the MySQL question here is: Can a configuration error cause mysqld to think a socket is open when in fact it is not? On Wed, 3 Mar 2004, Rhino wrote: Maybe you could explain what a jail is. In 20+ years doing systems work I've never heard that term mean anything but a place where criminals are locked up. Rhino - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 11:28 AM Subject: mysql in a jail I have a system running FreeBSD 4.9 four jails. MySQL is running in two of the jails and I am trying to add it to a 3rd jail. Starting the server gets the message: 040302 19:34:15 mysql started 040302 19:34:15 Can't start server : Bind on unix socket: Permission denied 040302 19:34:15 Do you already have another mysqld server running on socket: /tmp/mysql.sock ? 040302 19:34:15 Aborting 040302 19:34:15 /usr/local/libexec/mysqld: Shutdown Complete 040302 19:34:15 mysqld ended The new jail was created by copying the file tree from a jail that had the desired configuration and then doing clean-up as required. In response to the error I tried configuring mysqld to use a different port and socket file. That did not fix the problem. I had forgotten the other two mysql's are using port 3306 and /tmp/mysql.sock. I next removed mysql and its dependencies and reinstalled mysql-server-4.0.16 using pkg_add. I am still getting the same error. At this point I think it is a jail problem with something I missed in clean-up but I do not know where to look next. Having done all this, I recalled in installing the 2nd MySQL jail, it was necessary to remove and reinstall mysql. In that instance the re-installation solved the problem. Thanks for any ideas. _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Douglas Denault [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PHP and Mysql
Hi,
Does any one help me in formating the output result in PHP with mysql?
The code is here.
form action=hostrep.php method=get
/form
?
$username=root;
$password=;
$database=databasename;
$host=localhost;
$today = date(F j, Y, g:i a);
mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( Unable to select database);
#echo Connected successfully;
#$softwareid=$_GET['softwareid'];
#$softwareid1=$_GET['softwareid1'];
$query=SELECT hostid,hostname,cabinetnumber FROM host;
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
echo bcenterHost Details $today/center/bbrbr;
$i=0;
while ($i $num) {
$hostid=mysql_result($result,$i,hostid);
$hostname=mysql_result($result,$i,hostname);
$cabinetnumber=mysql_result($result,$i,cabinetnumber);
echo b$hostid $hostname
$cabinetnumber brbr;
//echo b$softwareid $manufacturer/bbrname: $namebrVersion:
$versionbrhrbr;
Thanks
-
Do you Yahoo!?
Yahoo! Search - Find what youre looking for faster.
Re: read only table 'user' at install
The parent dir, /usr/local/mysql, owned by root is fine. We can see that the data dir is owned by mysql, but what about its contents? Try chown -R mysql:mysql /usr/local/mysql/data (as root or using sudo), then try mysqladmin again. Michael Jonathan Villa wrote: nevermind that webmaster stuff...wrong 'send as' setting... On Wed, 2004-03-03 at 12:59, HPGM Webmaster wrote: 1.- did you create the data dir as root? chown mysql.mysql -R /var/dir-where-you-install Well, the data dir was created when I untarred mysql... 2.- who owns the mysql data dir and it's parent dirs? drwxrwxr-x4 mysqlmysql4096 Mar 3 21:07 data and the parent dir is owned by root, should I try making the /usr/local/mysql dir owned by mysql? ls -lka /var/dir-where-you-installed 3.- try using mysql client Never used this before, I will try... here is an ls -la from /usr/local/mysql [EMAIL PROTECTED] mysql]# ls -la /usr/local/mysql total 92 drwxrwx--- 14 root mysql4096 Mar 3 02:35 . drwxr-xr-x 15 root root 4096 Mar 3 02:27 .. drwxrwx---2 root mysql4096 Feb 10 12:59 bin -rwxrwx---1 root mysql 773 Feb 10 12:59 configure -rwxrwx---1 root mysql 19106 Feb 10 12:50 COPYING drwxrwxr-x4 mysqlmysql4096 Mar 3 21:07 data drwxrwx---2 root mysql4096 Feb 10 12:59 docs drwxrwx---2 root mysql4096 Feb 10 12:59 include -rwxrwx---1 root mysql7633 Feb 10 12:50 INSTALL-BINARY drwxrwx---2 root mysql4096 Feb 10 12:59 lib drwxrwx---3 root mysql4096 Feb 10 12:59 man drwxrwx---6 root mysql4096 Feb 10 12:59 mysql-test -rwxrwx---1 root mysql1937 Feb 10 12:15 README drwxrwx---2 root mysql4096 Feb 10 12:59 scripts drwxrwx---3 root mysql4096 Feb 10 12:59 share drwxrwx---5 root mysql4096 Feb 10 12:59 sql-bench drwxrwx---2 root mysql4096 Feb 10 12:59 support-files drwxrwx---2 root mysql4096 Feb 10 12:59 tests On Wed, 2004-03-03 at 07:27, Victor Medina wrote: A few things to try: 1.- did you create the data dir as root? chown mysql.mysql -R /var/dir-where-you-install 2.- who owns the mysql data dir and it's parent dirs? ls -lka /var/dir-where-you-installed 3.- try using mysql client Best Regards On Tue, 2004-03-02 at 21:55, Jonathan Villa wrote: Ok, I have installed MySQL many times and have never come across this... when try to run ./bin/mysqladmin -u root password 'new-password' I get the following ./bin/mysqladmin: unable to change password; error: 'Table 'user' is read only' I've never had this happen, and unfortunately do not know enough about read/write perms on tables to try to debug it. I promise that I'll study up on it, but right now I'm kinda of in rush... I'm using : mysql-standard-4.0.18-pc-linux-i686.tar.gz thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Microseconds in the 4.0.x series...
In the manual that came with MySQL 4.0.17 (at least in the Mandrake packages of such) it says I can use %f to get the microseconds in a DATE_FORMAT call. This does not work, however, and searching the archives reveals that %f was only added in 4.1.1. So..which one is correct? The release announcement or the 4.0.17 documentation? Is there a way to get sub-second resolution in 4.0.17, or must I rely on my application? Thanks! j- k- -- Joshua J. Kugler Fairbanks, Alaska Computer Consultant--Systems Designer .--- --- ... ..- .--.- ..- --. .-.. . .-. [EMAIL PROTECTED] ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TEMPORARY TABLE
In my original post I didn't add that I close the connection just before I exit the function. If lngRecordAffected 0 Then ' if the execute was successful then let's commit the updates cn.CommitTrans WriteData = lngRecordAffected Else ' otherwise let's rollback to before the execute command. cn.RollbackTrans WriteData = lngRecordAffected End If If cn Is Nothing Then Else cn.Close End If Exit Function I do not really understand your question about pooled connections. Does the above code answer your question? In your VB code, are your connections pooled, or are you using the same connection for each database call?
MySQL SELECT problem
Hi, I have a problem about writing a proper SELECT query for the following goal: (I only have basic knowledge of SQL) Table name: peoplelist column 1: id (not NULL, auto_incremental) column 2: name column 3: country now, there are about 7,000 rows in this table. I want to select out: first (in terms of id) 10 or less people of each country. There are more than 100 countries. One solution is to run one query for each country, then combine the results. But how can I do this in a better way, e.g. by one SELECT sql query ? ps. I am using MySQL 4.0 that doesn't support subselect. Thanks. Han = Email: [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Search - Find what youre looking for faster http://search.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL SELECT problem
Try something like this: SELECT ID, Name, Country FROM peoplelist GROUP BY Country HAVING count(Country)10; That might work. Also you can have subselects in 4.0.
