[q] can I start MySQL in READONLY mode?
Hello, I have a situation where I'd like to do some debugging against a MySQL database that's setup so I couldn't modify it (select only). I did some google searching and searched mysql.com but didn't find much on readonly. Is this possible? I already know I can just backup/restore the datafiles as many times as I need to - I was just hoping for a flag or startup option or something. Thanks. -- Schedule your world with ScheduleWorld.com http://www.ScheduleWorld.com/ Java Web start (JNLP): http://www.ScheduleWorld.com/sw/ScheduleWorld.jnlp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [q] can I start MySQL in READONLY mode?
Hi Mark, How about just giving the user(s) only the SELECT privilege temporarily? Matt - Original Message - From: Mark Swanson Sent: Monday, September 01, 2003 7:33 PM Subject: [q] can I start MySQL in READONLY mode? Hello, I have a situation where I'd like to do some debugging against a MySQL database that's setup so I couldn't modify it (select only). I did some google searching and searched mysql.com but didn't find much on readonly. Is this possible? I already know I can just backup/restore the datafiles as many times as I need to - I was just hoping for a flag or startup option or something. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [q] can I start MySQL in READONLY mode?
Hi When you set up the mysql user use a GRANT statement that only gives SELECT access. That is effectivly read only access for that user Peter -Original Message- From: Mark Swanson [mailto:[EMAIL PROTECTED] Sent: 02 September 2003 01:34 To: [EMAIL PROTECTED] Subject: [q] can I start MySQL in READONLY mode? Hello, I have a situation where I'd like to do some debugging against a MySQL database that's setup so I couldn't modify it (select only). I did some google searching and searched mysql.com but didn't find much on readonly. Is this possible? I already know I can just backup/restore the datafiles as many times as I need to - I was just hoping for a flag or startup option or something. Thanks. -- Schedule your world with ScheduleWorld.com http://www.ScheduleWorld.com/ Java Web start (JNLP): http://www.ScheduleWorld.com/sw/ScheduleWorld.jnlp -- 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]
InnoDB slow?
I'm running a large database which is currently using MyISAM. There are approximately 300 million rows in about a dozen tables totaling 7GB of storage. The system is averaging 257 querries per second, probably peaking at around 500-600+ during busy times. We're running a single database with one programming doing insertions/updates and a web server doing only selects. The problem is that the insertions/updates tend to bog down a bit when the web side gets busy. I figured switching to InnoDB might help with the row-locking support, however, on my test system things ran very slow using InnoDB instead of MyISAM. This was just running the script handling the inserts/updates with no web access, but a test that took 2 minutes 34 seconds using MyISAM tables took 10 minutes 37 seconds using InnoDB. I setup enough disk space to hold the tables and had seemingly sufficient ammounts of mmeory configured for InnoDB, so I cannot understand the drastic slowdown. Any advise would be greatly appreciated.
Re: InnoDB slow?
Could you send us your my.cnf / my.ini ? We might be able to help you tune your InnoDB config for this setup. In my experience, InnoDB performance should approach MyISAM in most environments where the disk is the bottleneck (due to the fact, as said in the InnoDB table type intro in the docs, InnoDB is more CPU efficient than any other disk-based transaction engine). Additionally, I have a similar workload on a few boxes down at a client's office. InnoDB made a lot of sense in this case. Regards, Chris Paul Gallier wrote: I'm running a large database which is currently using MyISAM. There are approximately 300 million rows in about a dozen tables totaling 7GB of storage. The system is averaging 257 querries per second, probably peaking at around 500-600+ during busy times. We're running a single database with one programming doing insertions/updates and a web server doing only selects. The problem is that the insertions/updates tend to bog down a bit when the web side gets busy. I figured switching to InnoDB might help with the row-locking support, however, on my test system things ran very slow using InnoDB instead of MyISAM. This was just running the script handling the inserts/updates with no web access, but a test that took 2 minutes 34 seconds using MyISAM tables took 10 minutes 37 seconds using InnoDB. I setup enough disk space to hold the tables and had seemingly sufficient ammounts of mmeory configured for InnoDB, so I cannot understand the drastic slowdown. Any advise would be greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB slow?
Paul, if your MySQL server is runnign under Linux then try to play with innodb_flush_method variable. I've changed it to O_DSYNC and InnoDB became ~ 9 times faster (Suse 8.2 Linux 2.4.20-4GB i386). Also check that you didn't allocate too much memory (OS shouldn't swap). Best regards, Mikhail. - Original Message - From: Paul Gallier [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 2:57 AM Subject: InnoDB slow? I'm running a large database which is currently using MyISAM. There are approximately 300 million rows in about a dozen tables totaling 7GB of storage. The system is averaging 257 querries per second, probably peaking at around 500-600+ during busy times. We're running a single database with one programming doing insertions/updates and a web server doing only selects. The problem is that the insertions/updates tend to bog down a bit when the web side gets busy. I figured switching to InnoDB might help with the row-locking support, however, on my test system things ran very slow using InnoDB instead of MyISAM. This was just running the script handling the inserts/updates with no web access, but a test that took 2 minutes 34 seconds using MyISAM tables took 10 minutes 37 seconds using InnoDB. I setup enough disk space to hold the tables and had seemingly sufficient ammounts of mmeory configured for InnoDB, so I cannot understand the drastic slowdown. Any advise would be greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [q] can I start MySQL in READONLY mode?
Matt, Peter - thanks guys!! -- Schedule your world with ScheduleWorld.com http://www.ScheduleWorld.com/ Java Web start (JNLP): http://www.ScheduleWorld.com/sw/ScheduleWorld.jnlp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with oracledump (contributed program)
On Mon, Sep 01, 2003 at 11:09:02AM -0700, Martin Gainty wrote: Doug I copied this from an SAP integration with Orace site http://saphelp.cob.csuchico.edu/OraHelp/Darwin36/inst36fhp/html/5-odbc.htm 5.5.3 tsnames.ora File the file ORACLE_HOME/network/admin/tnsnames.ora must point to the listener (i.e., the TCP/IP port) on the database server. (ORACLE_HOME is the directory on Unix where Oracle is installed.) For example, ora_db0_net= (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=test-console.think.com) (PORT=1521) ) (CONNECT_DATA= (SID=ORCL) ) ) Note: Do not use tabs in the file tnsnames.ora. *OracleDump is performed by (SID=ORCL)* Keep us apprised to your progress... Hi Marty, Thanks for following up. I've created a tnsnames.ora file in /usr/local/oracle/9.0.1/network/admin with the settings appropriate to my host. When I run the following commands: % setenv ORACLE_HOME /usr/local/oracle/9.0.1 % setenv ORACLE_SID VAPDEV % oracledump -c -u myUser -p myPassword I get the error message... % Can't call method do on an undefined value at /home/doug/bin/oracledump line 95. When I run it with the -x switch I see... Configuration: (remove --explain/-x option to run with this configuration) Database SID: VAPDEV Database user: myUser Database password: myPassword Tables: All tables Options: --default-databaseUse default database (VAPDEV) --with-table-comments Include table comments --with-column-commentsInclude column comments --default-precision Set to 18 --default-scale Set to 0 --complete-insert Includes list of column names in insert statements I'm not a perl guy and I'm not sure what to make of it other than the variables $nls_date_format, $nls_time_format, $nls_timestamp_format have data at run-time. Thanks again for your help so far... -- Regards, Doug Marty Gainty - Original Message - From: Doug Poland [EMAIL PROTECTED] To: Jim Smith [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, September 01, 2003 6:18 AM Subject: Re: Need help with oracledump (contributed program) On Mon, Sep 01, 2003 at 01:03:48PM +0100, Jim Smith wrote: I'm trying to figure out how to use the contributed program oracledump in an environment where I don't have a login to the *nix host running Oracle. All my connectivity to the Oracle host is via port 1521 and JDBC. The oracle dump command seems to be looking for a SID in a file called tnsnames.ora. Those do not exist on the system I am using. Is it possible to still use oracledump in this case? I doubt it. It looks as if oracledump requires Oracle's network layer (SQL*Net) and unless you have that you can't do anything. The oracle thin JDBC driver implements SQL*Net for java only, but the other Oracle jdbc drivers require SQL*Net Thanks for the reply. Looks like I'll have to roll my own in Java. -- Regards, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB slow?
Thanks for the info. I'm running MySQL 4.0.14 under Redhat 8.0 / Linux 2.4.20. Here are the timings I ended up with from playing with innodb_flush_method: innodb_flush_method=fdatasync (default) 10 minutes 37 seconds innodb_flush_method=littlesync 10 minutes 22 seconds innodb_flush_method=O_DSYNC 5 minutes 18 seconds innodb_flush_method=nosync3 minutes 12 seconds MyISAM tables instead of InnoDB 2 minutes 34 seconds Now of course, the question is what potential harm am I looking at by using nosync or o_dsync? Mikhail Entaltsev wrote: Paul, if your MySQL server is runnign under Linux then try to play with innodb_flush_method variable. I've changed it to O_DSYNC and InnoDB became ~ 9 times faster (Suse 8.2 Linux 2.4.20-4GB i386). Also check that you didn't allocate too much memory (OS shouldn't swap). Best regards, Mikhail. - Original Message - From: Paul Gallier [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 2:57 AM Subject: InnoDB slow? I'm running a large database which is currently using MyISAM. There are approximately 300 million rows in about a dozen tables totaling 7GB of storage. The system is averaging 257 querries per second, probably peaking at around 500-600+ during busy times. We're running a single database with one programming doing insertions/updates and a web server doing only selects. The problem is that the insertions/updates tend to bog down a bit when the web side gets busy. I figured switching to InnoDB might help with the row-locking support, however, on my test system things ran very slow using InnoDB instead of MyISAM. This was just running the script handling the inserts/updates with no web access, but a test that took 2 minutes 34 seconds using MyISAM tables took 10 minutes 37 seconds using InnoDB. I setup enough disk space to hold the tables and had seemingly sufficient ammounts of mmeory configured for InnoDB, so I cannot understand the drastic slowdown. Any advise would be greatly appreciated.
re: bind-address
Hi, I have mysql 3.23.56 on RedHat 8.0 I can access mysql through my machine using PHP alright with the following server: ns1.mydomain.ca user: me Password: foo I can access Mysql using Putty with the same variables However I am using a program that uses ADO and MyOBDC driver and it is unable to connect. I know the program works because it connects to a commercial site providing Mysql I also use. The problem I believe is a parameter I am missing with my own Mysql set up. Thanks. Mike
Re: Using multiple character sets (Russian English)
Egor, Thank you for the response. You are the perfect person to answer my questions regarding Russian character sets. :) I two follow-up questions: 1. Will this work even though I am using two different character sets? I just want to be clear on what I am describing: one column of my table will contain English words (Latin1?) and the other column will contain the words written in Russian (Cyrillic fonts). It will not be Russian words written with Latin letters. 2. I have done some research since I first posted my question and I have read that I need MySQL v4.1 to support multiple character sets. I am using v3.23.57-Max. Have you used MySQL for multiple character sets in the same table for versins prior to 4.1? Again, thank you for your assistance, Egor, and for anyone else who may be able to assist. Best regards, Rachel. --- Egor Egorov [EMAIL PROTECTED] wrote: Rachel Rodriguez [EMAIL PROTECTED] wrote: I have experience using MySQL and SQL commands in general, but I am going through my first experience with working with multiple character sets. I am working on a Russian/English translation project and I'd like to create a table where column1 contains words in Russian and column2 contains the word translated into English. Russian character set should be cp1251, which I have confirmed is installed on the MySQL server (3.23.57-Max running on Linux). At this time, it is not possible for us to upgrade beyond this version of the MySQL server. How do I issue SQL commands in English: (insert into TableName values...) values that are of type varchar in both Russian and English: (null, cyrillic_russian_word, english_word); You can do it as you wrote above :) -- 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] __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RAID or not?
--On Friday, August 22, 2003 1:21 PM -0400 Lefevre, Steven [EMAIL PROTECTED] wrote: that is not true. mirroring gives you double the read speed and half the write speed. RAID5 gives you less than half the write speed. - OK, I see how it can give you double the read speed, but how can it give you have the write speed? Does it split the data between disks and then sync them later? No. You write 2x, remember. ;P Your write speed, best case and assuming no other bottlenecks (say an IDE a drive sharing the same controller with another IDE drive, esp. in the same mirror set) will be only as fast as the slowest drive in write mode. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID or not?
--On Friday, August 22, 2003 8:37 PM -0600 Jim McAtee [EMAIL PROTECTED] wrote: I don't quite understand the need to read data before any write. Why wouldn't it just calculate the parity of whatever is being written and just write it to disk? Wouldn't there be slack space, as with any disk system? Write a 1 byte file and it uses an N byte block on one disk, plus an N byte parity block on another. This wholly depends on the RAID subsystem, but better than 80% will need to either read the entire stripe, or hold off until they're writing the whole stripe at once. Remember the RAID is below the filesystem layer, and *separate* from it, esp. in the case of a hardware controller. Really big systems may (do) keep 'maps' of the space so they can cheat by not reading a strip when it knows it hasn't been written since (destructive) initialization and is thus all 0's. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange behavior -- user variables in 4.0.14b
I get the following strange behavior with a user variable. @T has the value 0 to start; after adding 1 to @T a few times, it ends up with a clearly incorrect value. I'd expect it to have a value of 280 after the second select. -- SELECT @T -- +--+ | @T | +--+ | 0| +--+ 1 row in set (0.00 sec) -- UPDATE contown_svr SET contown_id=(@T:[EMAIL PROTECTED]) -- Query OK, 280 rows affected (0.05 sec) Rows matched: 280 Changed: 280 Warnings: 280 -- SELECT @T -- +--+ | @T | +--+ | 1.8669204411349e+021 | +--+ 1 row in set (0.00 sec) --- More data: I'm using Windows 2000 with MySQL 4.0.14b, and connected through localhost. It appears to work correctly on MySQL 4.0.13 and MySQL 3.23.57. I also tried it on Linux with MySQL 4.0.13, and it worked correctly. It continues to fail with the 3.23.57 client and the 4.0.14b server. I get various values for @T, sometimes with a negative exponent. Sometimes it gives the correct value once, then twice the correct value on the second try, etc., despite @T being reset to zero. Sometimes, when I select the values in contown_svr, contown_id (which is an int) prints as something like 561.1. This happens when I have the mysql client read a file. When I cut and paste the content of the file to console, it appears to give the correct result. Any help would be appreciated. It sure sounds to me like a bug in thread synchronization within the server. Here's the smallest program I've gotten to fail. It still fails (gives wrong value to @T) even if the select returns 0 rows, but it doesn't fail if I remove the insert...select. I'll try to cut it down some more and post an example that's not missing the data--but it may take a while to get to it. - select @t:=0; drop table if exists contown_svr; create table contown_svr select * from contown where 0; insert into contown_svr select -999,pw.owner,pc.contact_id,0 from fundown pw inner join fundcont pc using (funding_id) left join contown cw on cw.contact_id = pc.contact_id left join grouptree on pw.owner=subject and cw.owner=target where subject is null and pc.contact_id 0; select @t; update contown_svr set contown_id=(@t:=(@t+1)); select @t; exit -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CPU Usage and MySQL...
On Sun, Aug 31, 2003 at 12:02:20AM -0400, K Old wrote: I ran across this tool the other day and it is awesome. Basically it is like the top utility for *nix, but it's for mysql. It basically gives you a live look into the database and what queries it's processing, etc in real time. Glad you like it! Maybe I should add some testimonials to the mytop web site... :-) Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 31 days, processed 1,353,656,389 queries (497/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CPU Usage and MySQL...
On Sun, Aug 31, 2003 at 07:14:12AM -0400, Albert wrote: Can this tool be used on Windows and if so what version do I need to DL and does it need anything else besides the software (e.g. Perl - which I see listed for the nix versions. When I last had a Windows box at work, mytop worked with ActivePerl (from ActiveState). Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 31 days, processed 1,353,657,492 queries (497/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using multiple character sets (Russian English)
Just an update (but a good one): I have this working now if I issue all of my SQL statements direct from the MySQL monitor. Egor was 100% correct that this can be done. As a native English speaker, I was confused on how MySQL would handle characters that were not of the Western European type since this is my first time trying this. I have an off-topic question for those on the list that use scripts with mixed character sets for the values: Our MySQL server is running on Linux, but I am doing my development on Windows 2000. I want to now create a .sql script to issue all of my commands to create my tables and populate the tables. I've done this many times (in English) with no problems, but now that some of my values contain words written in Cyrillic, the MySQL server will not display them correctly when I do a select query afterwards. I am using Notepad and saving as UNICODE format. Does anyone have a suggestion for a text editor under Windows, or an encoding that I should be using other than UNICODE to preserve my Cyrillic words? Or does the problem not exist in the text editor? I just can't handle the idea of sitting at the MySQL monitor and issuing each insert command for my Russian words and my English translations. Help! :) Thank you again. Rachel. --- Rachel Rodriguez [EMAIL PROTECTED] wrote: Egor, Thank you for the response. You are the perfect person to answer my questions regarding Russian character sets. :) I two follow-up questions: 1. Will this work even though I am using two different character sets? I just want to be clear on what I am describing: one column of my table will contain English words (Latin1?) and the other column will contain the words written in Russian (Cyrillic fonts). It will not be Russian words written with Latin letters. 2. I have done some research since I first posted my question and I have read that I need MySQL v4.1 to support multiple character sets. I am using v3.23.57-Max. Have you used MySQL for multiple character sets in the same table for versins prior to 4.1? Again, thank you for your assistance, Egor, and for anyone else who may be able to assist. Best regards, Rachel. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: re: bind-address
Mike Kinzie [EMAIL PROTECTED] wrote: I have mysql 3.23.56 on RedHat 8.0 I can access mysql through my machine using PHP alright with the following server: ns1.mydomain.ca user: me Password: foo I can access Mysql using Putty with the same variables However I am using a program that uses ADO and MyOBDC driver and it is unable to connect. What error did you receive? I know the program works because it connects to a commercial site providing Mysql I also use. The problem I believe is a parameter I am missing with my own Mysql set up. -- 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]
insert ... select .. order by, problem
Hallo everyone ! I already sent this, but I think some people think is not clear enough ;-) Im using Mysql 4.0.12 on RedHat 7.3 x86 I know it's not the last binary but I cannot upgrade now. (And i saw nothing about this in the changelog for 4.013 and 4.0.14) I found the following : I have two tables : Stock (InnoDB, primary key on d): a char (16) b char (20) c char (20) d int e decimal (9,2) h int i int PTemp (MyISAM, no keys): d int e decimal f int g char (1) And the statement I am using is : INSERT INTO PTemp SELECT d,e,32,'E' FROM Stock WHERE hi ORDER BY a,b,c; I am doing an insert/select with order by, in both cases I am using the same statemant. When I use the same statement in my application (built with C, and statically linked to libmysqlclient.a) I get the reversed order (the records that start with '0' are at the end). When I test the statement in the mysql cli and I get the results well sorted (the records that start with '0' are at the begining). I checked the log and both statements are equal, (but the two users used to access the DB are different, the mysql cli user is root, and the other just have enough permissions to select, update,delete and insert in the tables). I would like to know (if that is possible) what happens. Thanks in advance. Ale __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
crash after 15 minutes
hello list i migrated a server of ours (from 3.23) to mysql-4.0.14 (solaris 8/sparc/64bit). i took the precompiled binary of the website. so far so good. my problem now is the mysql-server crashed with signal 11 every 15 minutes. Ok, signal 11 normally sounds like memory or hardware problem but i do not believe this since this box runs stable except of mysql. What makes me more suspicious is the fact that it's literally 15 minutes varying by 1 second. did someone of you guys had this problem already? might it be better to go back to 32bit version? are some debug switches to enable get useful logs for the developers? any help is appreciated. -- Kind regards, Roman Hochuli CCNP, CQS Cable GPS-Technik AG Zürcherstrasse 139 8952 Schlieren Switzerland Tel +41-1-7329977 Fax +41-1-7311840 X-NCC-RegID: ch.gps While talking a little bit annoyed about taxes: ...at least it does not make addicted. -- 2002, Andre Wieler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Hi, well, I'm not totally sure about your question. Which order is reversed ? The order you get the entries with a select after the insert ? If it is this, then I think it's not a problem with the insert. The order is then given by the select, and if no order by is in the select, it is arbitrary. Since the physical order of entries in your temp table isn't of any relevance, I can't see any point in using order by in this statement. Stefan Am Tuesday 02 September 2003 10:06 schrieb Alejandro Paz: Hallo everyone ! I already sent this, but I think some people think is not clear enough ;-) Im using Mysql 4.0.12 on RedHat 7.3 x86 I know it's not the last binary but I cannot upgrade now. (And i saw nothing about this in the changelog for 4.013 and 4.0.14) I found the following : I have two tables : Stock (InnoDB, primary key on d): a char (16) b char (20) c char (20) d int e decimal (9,2) h int i int PTemp (MyISAM, no keys): d int e decimal f int g char (1) And the statement I am using is : INSERT INTO PTemp SELECT d,e,32,'E' FROM Stock WHERE hi ORDER BY a,b,c; I am doing an insert/select with order by, in both cases I am using the same statemant. When I use the same statement in my application (built with C, and statically linked to libmysqlclient.a) I get the reversed order (the records that start with '0' are at the end). When I test the statement in the mysql cli and I get the results well sorted (the records that start with '0' are at the begining). I checked the log and both statements are equal, (but the two users used to access the DB are different, the mysql cli user is root, and the other just have enough permissions to select, update,delete and insert in the tables). I would like to know (if that is possible) what happens. Thanks in advance. Ale __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Hi Stephan, Let's see the case : I use ORDER BY, because I want that order in PTemp table, so I do not have to order them later (because they are retrieved several times later). 1. Inserting with mysql c.l.i. : I get the records well sorted : first by a, secondly by b and finally by c (ascendig order). I'm using d to relink both tables in a join. 2. Inserting with the application : I get the records well sorted : first by a, secondly by b and finally by c, but in descending order. As all records has the same value in a, so the records that in case 1 start with '0' are at the beginning, the same records here are at the end. I'm using d to relink both tables in a join. I thought that was clear when I said reverse order, sorry. So you see what I mean ? thanks ! __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB slow?
Paul, Where did you find information about 'littlesync' and 'nosync'? In InnoDB manual I found only ** This is only relevant on Unix. The default value for this is fdatasync. Then InnoDB uses fsync() to flush both the data and log files. If O_DSYNC is specified, InnoDB uses O_SYNC to open and flush the log files, but uses fsync() to flush the data files. If O_DIRECT is specified (available on some Linux versions starting from MySQL-4.0.14), InnoDB uses O_DIRECT to open the data files, and uses fsync() to flush both the data and log files. Note that InnoDB does not use fdatasync() or O_DSYNC because there have been problems with them on many Unix flavors. ** Mikhail. - Original Message - From: Paul Gallier To: Mikhail Entaltsev ; [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 4:24 AM Subject: Re: InnoDB slow? Thanks for the info. I'm running MySQL 4.0.14 under Redhat 8.0 / Linux 2.4.20. Here are the timings I ended up with from playing with innodb_flush_method: innodb_flush_method=fdatasync (default) 10 minutes 37 seconds innodb_flush_method=littlesync 10 minutes 22 seconds innodb_flush_method=O_DSYNC 5 minutes 18 seconds innodb_flush_method=nosync3 minutes 12 seconds MyISAM tables instead of InnoDB 2 minutes 34 seconds Now of course, the question is what potential harm am I looking at by using nosync or o_dsync? Mikhail Entaltsev wrote: Paul, if your MySQL server is runnign under Linux then try to play with innodb_flush_method variable. I've changed it to O_DSYNC and InnoDB became ~ 9 times faster (Suse 8.2 Linux 2.4.20-4GB i386). Also check that you didn't allocate too much memory (OS shouldn't swap). Best regards, Mikhail. - Original Message - From: Paul Gallier [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 2:57 AM Subject: InnoDB slow? I'm running a large database which is currently using MyISAM. There are approximately 300 million rows in about a dozen tables totaling 7GB of storage. The system is averaging 257 querries per second, probably peaking at around 500-600+ during busy times. We're running a single database with one programming doing insertions/updates and a web server doing only selects. The problem is that the insertions/updates tend to bog down a bit when the web side gets busy. I figured switching to InnoDB might help with the row-locking support, however, on my test system things ran very slow using InnoDB instead of MyISAM. This was just running the script handling the inserts/updates with no web access, but a test that took 2 minutes 34 seconds using MyISAM tables took 10 minutes 37 seconds using InnoDB. I setup enough disk space to hold the tables and had seemingly sufficient ammounts of mmeory configured for InnoDB, so I cannot understand the drastic slowdown. Any advise would be greatly appreciated.
Re: insert ... select .. order by, problem
Hi Alejondro I use ORDER BY, because I want that order in PTemp table, so I do not have to order them later (because they are retrieved several times later). If I understand correct ,,, U can never trust the order ur records get return from DB (it is indepented of the order u insert records ) with other words , I u are interest in a specific order when selecting , u MUST use Order By hope this help regards Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Got a warning!
Hi I'm a mysql/php novice. I tried connect a mysql-db using php4 under linux. Then I get a warning by the parser from the function mysql_free_result($result); Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /srv/www/htdocs In all reference books I found this syntax. Has anyone an idea what wrong? Many thanx Mag -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Got a warning!
On Tue, 2 Sep 2003 12:12:42 +0200 Magnus D. Klein [EMAIL PROTECTED] wrote: I tried connect a mysql-db using php4 under linux. Then I get a warning by the parser from the function mysql_free_result($result); Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /srv/www/htdocs In all reference books I found this syntax. Has anyone an idea what wrong? you should use mysql_free_result(); only with resources you get from mysql_query(); --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Hi, I think you can't do this. There is no order in the table, so there is no point in using order by with insert. You always have to do this when retrieving the records (the order you get with select without order by is accidential). HTH Stefan Am Tuesday 02 September 2003 11:49 schrieb Alejandro Paz: Hi Stephan, Let's see the case : I use ORDER BY, because I want that order in PTemp table, so I do not have to order them later (because they are retrieved several times later). 1. Inserting with mysql c.l.i. : I get the records well sorted : first by a, secondly by b and finally by c (ascendig order). I'm using d to relink both tables in a join. 2. Inserting with the application : I get the records well sorted : first by a, secondly by b and finally by c, but in descending order. As all records has the same value in a, so the records that in case 1 start with '0' are at the beginning, the same records here are at the end. I'm using d to relink both tables in a join. I thought that was clear when I said reverse order, sorry. So you see what I mean ? thanks ! __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Virus Found in message Wicked screensaver
Symantec AntiVirus found a virus in an attachment you ([EMAIL PROTECTED] [EMAIL PROTECTED]) sent to Jay Blanchard. To ensure the recipient(s) are able to use the files you sent, perform a virus scan on your computer, clean any infected files, then resend this attachment. Attachment: details.pif Virus name: [EMAIL PROTECTED] Action taken: Clean failed : Quarantine succeeded : File status: Infected -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Stefan, Do you imply that tables cannot be sorted desc or asc based on one of the columns e.g. a last name? or am I misunderstanding you. Albert Atlanta (anyone else in Atlanta?) - Original Message - From: Stefan Kuhn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 6:57 AM Subject: Re: insert ... select .. order by, problem Hi, I think you can't do this. There is no order in the table, so there is no point in using order by with insert. You always have to do this when retrieving the records (the order you get with select without order by is accidential). HTH Stefan Am Tuesday 02 September 2003 11:49 schrieb Alejandro Paz: Hi Stephan, Let's see the case : I use ORDER BY, because I want that order in PTemp table, so I do not have to order them later (because they are retrieved several times later). 1. Inserting with mysql c.l.i. : I get the records well sorted : first by a, secondly by b and finally by c (ascendig order). I'm using d to relink both tables in a join. 2. Inserting with the application : I get the records well sorted : first by a, secondly by b and finally by c, but in descending order. As all records has the same value in a, so the records that in case 1 start with '0' are at the beginning, the same records here are at the end. I'm using d to relink both tables in a join. I thought that was clear when I said reverse order, sorry. So you see what I mean ? thanks ! __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- 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: Need help with oracledump (contributed program)
A TNSNAMES file isn't going to help unless you have the Oracle client software installed. If you had the software, you would already have a tnsnames file. -Original Message- From: Doug Poland [mailto:[EMAIL PROTECTED] Sent: 02 September 2003 02:58 To: Martin Gainty Cc: [EMAIL PROTECTED] Subject: Re: Need help with oracledump (contributed program) On Mon, Sep 01, 2003 at 11:09:02AM -0700, Martin Gainty wrote: Doug I copied this from an SAP integration with Orace site http://saphelp.cob.csuchico.edu/OraHelp/Darwin36/inst36fhp/htm l/5-odbc.htm 5.5.3 tsnames.ora File the file ORACLE_HOME/network/admin/tnsnames.ora must point to the listener (i.e., the TCP/IP port) on the database server. (ORACLE_HOME is the directory on Unix where Oracle is installed.) For example, ora_db0_net= (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=test-console.think.com) (PORT=1521) ) (CONNECT_DATA= (SID=ORCL) ) ) Note: Do not use tabs in the file tnsnames.ora. *OracleDump is performed by (SID=ORCL)* Keep us apprised to your progress... Hi Marty, Thanks for following up. I've created a tnsnames.ora file in /usr/local/oracle/9.0.1/network/admin with the settings appropriate to my host. When I run the following commands: % setenv ORACLE_HOME /usr/local/oracle/9.0.1 % setenv ORACLE_SID VAPDEV % oracledump -c -u myUser -p myPassword I get the error message... % Can't call method do on an undefined value at /home/doug/bin/oracledump line 95. When I run it with the -x switch I see... Configuration: (remove --explain/-x option to run with this configuration) Database SID: VAPDEV Database user: myUser Database password: myPassword Tables: All tables Options: --default-databaseUse default database (VAPDEV) --with-table-comments Include table comments --with-column-commentsInclude column comments --default-precision Set to 18 --default-scale Set to 0 --complete-insert Includes list of column names in insert statements I'm not a perl guy and I'm not sure what to make of it other than the variables $nls_date_format, $nls_time_format, $nls_timestamp_format have data at run-time. Thanks again for your help so far... -- Regards, Doug Marty Gainty - Original Message - From: Doug Poland [EMAIL PROTECTED] To: Jim Smith [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, September 01, 2003 6:18 AM Subject: Re: Need help with oracledump (contributed program) On Mon, Sep 01, 2003 at 01:03:48PM +0100, Jim Smith wrote: I'm trying to figure out how to use the contributed program oracledump in an environment where I don't have a login to the *nix host running Oracle. All my connectivity to the Oracle host is via port 1521 and JDBC. The oracle dump command seems to be looking for a SID in a file called tnsnames.ora. Those do not exist on the system I am using. Is it possible to still use oracledump in this case? I doubt it. It looks as if oracledump requires Oracle's network layer (SQL*Net) and unless you have that you can't do anything. The oracle thin JDBC driver implements SQL*Net for java only, but the other Oracle jdbc drivers require SQL*Net Thanks for the reply. Looks like I'll have to roll my own in Java. -- Regards, Doug -- 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]
Abt Mysqldump
Hello, I have dumped the database contents as one txt file by using this command mysqldump databasename -uusername -ppasswd textfilename. but while redumping back to mysql it is giving some errors in the textfile..is there any possibility to redump the contents without any error.. Regards, Uma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Abt Mysqldump
On Tue, 2 Sep 2003 17:02:37 +0530 (IST) Uma Shankari T. [EMAIL PROTECTED] wrote: but while redumping back to mysql it is giving some errors in the textfile..is there any possibility to redump the contents without any error.. what exactly does it say ? --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Abt Mysqldump
What are the errors? For example, is the error like unable to create the tables, because the tables still exist? See the dumpfile if tables are created (by default). If so, delete the tables first, and redump back the dumpfile. -Original Message- From: Uma Shankari T. [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 1:33 PM To: [EMAIL PROTECTED] Subject: Abt Mysqldump Hello, I have dumped the database contents as one txt file by using this command mysqldump databasename -uusername -ppasswd textfilename. but while redumping back to mysql it is giving some errors in the textfile..is there any possibility to redump the contents without any error.. Regards, Uma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Zie ook/see also: http://www.knmi.nl/maildisclaimer.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Hi Albert, you are not misunderstanding me :-) Tables can indeed not be sorted, it's output which gets sorted. The difference is not academic, but important: It's not the table which gets an order, but the output. Take a command like: insert into x ... select from y ... order by z. Here the output of select gets sorted and inserted into the table x. In this table, there is not order, so if you do then a select from x, the order is arbitrary again and you need to do select from x order by z. And this means you could have dropped the order by in insert totally. Hope it became clear. Stefan Am Tuesday 02 September 2003 13:20 schrieb Albert: Stefan, Do you imply that tables cannot be sorted desc or asc based on one of the columns e.g. a last name? or am I misunderstanding you. Albert Atlanta (anyone else in Atlanta?) - Original Message - From: Stefan Kuhn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 6:57 AM Subject: Re: insert ... select .. order by, problem Hi, I think you can't do this. There is no order in the table, so there is no point in using order by with insert. You always have to do this when retrieving the records (the order you get with select without order by is accidential). HTH Stefan Am Tuesday 02 September 2003 11:49 schrieb Alejandro Paz: Hi Stephan, Let's see the case : I use ORDER BY, because I want that order in PTemp table, so I do not have to order them later (because they are retrieved several times later). 1. Inserting with mysql c.l.i. : I get the records well sorted : first by a, secondly by b and finally by c (ascendig order). I'm using d to relink both tables in a join. 2. Inserting with the application : I get the records well sorted : first by a, secondly by b and finally by c, but in descending order. As all records has the same value in a, so the records that in case 1 start with '0' are at the beginning, the same records here are at the end. I'm using d to relink both tables in a join. I thought that was clear when I said reverse order, sorry. So you see what I mean ? thanks ! __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
* Albert Stefan, I'm Roger, but I reply anyway. :) Do you imply that tables cannot be sorted desc or asc based on one of the columns e.g. a last name? or am I misunderstanding you. In relational database theory the order of rows within the table is undefined, i.e. it is up to the server, and the server can re-organize a table at any time. If you want an ordered result, you have to use ORDER BY in your SELECT statement. However, the MySQL server has some features that can be used to achieve exactly what you ask. It is possible to sort a table physically, in order to do faster reads later. The order is however destroyed if you do additional inserts. See the ORDER BY option of the ALTER TABLE statement: ORDER BY allows you to create the new table with the rows in a specific order. Note that the table will not remain in this order after inserts and deletes. In some cases, it may make sorting easier for MySQL if the table is in order by the column that you wish to order it by later. This option is mainly useful when you know that you are mostly going to query the rows in a certain order; by using this option after big changes to the table, you may be able to get higher performance. URL: http://www.mysql.com/doc/en/ALTER_TABLE.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Abt Mysqldump
use mysql database-name backupfilename I think , the backup file name should have .sql extension , like backup.sql Hoeven, Maarten van der wrote: What are the errors? For example, is the error like unable to create the tables, because the tables still exist? See the dumpfile if tables are created (by default). If so, delete the tables first, and redump back the dumpfile. -Original Message- From: Uma Shankari T. [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 1:33 PM To: [EMAIL PROTECTED] Subject: Abt Mysqldump Hello, I have dumped the database contents as one txt file by using this command mysqldump databasename -uusername -ppasswd textfilename. but while redumping back to mysql it is giving some errors in the textfile..is there any possibility to redump the contents without any error.. Regards, Uma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Zie ook/see also: http://www.knmi.nl/maildisclaimer.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] . -- Vidhya CS .
Re: crash after 15 minutes
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 2 Sep 2003, Roman Hochuli wrote: i migrated a server of ours (from 3.23) to mysql-4.0.14 (solaris 8/sparc/64bit). i took the precompiled binary of the website. so far so good. my problem now is the mysql-server crashed with signal 11 every 15 minutes. Ok, signal 11 normally sounds like memory or hardware problem but i do not believe this since this box runs stable except of mysql. What makes me more suspicious is the fact that it's literally 15 minutes varying by 1 second. That sounds strange, indeed. did someone of you guys had this problem already? might it be better to go back to 32bit version? are some debug switches to enable get useful logs for the developers? We do offer a special debug binary that will give more verbose information when crashing. You might want to try it. Also, see http://www.mysql.com/doc/en/Crashing.html for more info. You could also try the 32bit binary, if your database is not that big. Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany For technical support contracts, visit https://order.mysql.com/?ref=mlgr -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD4DBQE/VIohSVDhKrJykfIRAhQoAJ9TpPC4w/+cB8O/3YFf5cPmyy8DbgCWL7kD Vh63crbmbIxJVDqpOja5uQ== =G5eb -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with oracledump (contributed program)
On Tue, Sep 02, 2003 at 12:25:35PM +0100, Jim Smith wrote: A TNSNAMES file isn't going to help unless you have the Oracle client software installed. If you had the software, you would already have a tnsnames file. Thanks all for your help. I've found a free java-based application (JOracleDump) and am modifying that to do what I need. -- Regards, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Abt Mysqldump
Hi The only problem I get with mysqldump is that if I have used a reserved word as a column name, then the create starement fails. (eg CREATE TABLE fred ( KEY int(10) not null default '0' ); will fail (but as produced by mysqldump) However, if you edit the file and put ` characters either side of the column name (ie KEY above) then it then works. You can sort this in the first instance if you use the '--quote-names' or '-Q' option to mysqldump (OK, so I've just read the man page for the first time as well) Regards Matthew -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 02 September 2003 18:39 To: [EMAIL PROTECTED] Subject: Re: Abt Mysqldump use mysql database-name backupfilename I think , the backup file name should have .sql extension , like backup.sql Hoeven, Maarten van der wrote: What are the errors? For example, is the error like unable to create the tables, because the tables still exist? See the dumpfile if tables are created (by default). If so, delete the tables first, and redump back the dumpfile. -Original Message- From: Uma Shankari T. [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 1:33 PM To: [EMAIL PROTECTED] Subject: Abt Mysqldump Hello, I have dumped the database contents as one txt file by using this command mysqldump databasename -uusername -ppasswd textfilename. but while redumping back to mysql it is giving some errors in the textfile..is there any possibility to redump the contents without any error.. Regards, Uma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Zie ook/see also: http://www.knmi.nl/maildisclaimer.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] . -- Vidhya CS . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert ... select .. order by, problem
Stefan, Indeed, and my mistake (semantics). I meant what you explained. It is clear to me that the order in the table remains in the manner the data were entered, and that cannot be changed, unless a record is deleted and then re-entered, which would place it elsewhere (at the end). This does not really have any benefits IMHO. And yes, I understand that it is the output that is sorted based on the query. Thanks for clarifying this, and as I read my question, I should have seen the difference myself. Mea culpa! : =) I am quite familiar with SQL Server 2000, but need to use MySQL for a project for the University I am at (Devry Alpharetta, Atlanta), to capture the input from a student survey of the classes and the Faculty members. There are several fields: semester (char), course(char), courseID (int), Faculty (varchar[30] - if that is acceptable in that format - and the answers to 18 questions, all alpha characters (char) or numeric char (int), and one Boolean (yes/no or 1,0). I need to figure out how to best structure this, e.g. create tables on the fly (if that is possible using ASP/ADO and SQL with ODBC connector), or create tables with many to many relationships and store the data for each course survey in a separate table. The tables with many to many relationships would hold all the courses, courseID's, and Faculty members, and the answers to the survey would create links between those and the results from the surveys. A typical class unique identification would look like this: sum03_FBaah_CIS_349 The cols would be 1 through 18 + a calculation col for the average of questions 1 to 18 and a col for the average of all answers to question 1, question 2, etc ... Mind you I may export the answers to an excel spreadsheet and do the calculations there rather than in the DB itself. Anyway this is a long answer to your response but I wanted those who read this to get an idea of what I am working with. Any suggestions are welcomed. Albert - Original Message - From: Stefan Kuhn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 8:10 AM Subject: Re: insert ... select .. order by, problem Hi Albert, you are not misunderstanding me :-) Tables can indeed not be sorted, it's output which gets sorted. The difference is not academic, but important: It's not the table which gets an order, but the output. Take a command like: insert into x ... select from y ... order by z. Here the output of select gets sorted and inserted into the table x. In this table, there is not order, so if you do then a select from x, the order is arbitrary again and you need to do select from x order by z. And this means you could have dropped the order by in insert totally. Hope it became clear. Stefan Am Tuesday 02 September 2003 13:20 schrieb Albert: Stefan, Do you imply that tables cannot be sorted desc or asc based on one of the columns e.g. a last name? or am I misunderstanding you. Albert Atlanta (anyone else in Atlanta?) - Original Message - From: Stefan Kuhn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 6:57 AM Subject: Re: insert ... select .. order by, problem Hi, I think you can't do this. There is no order in the table, so there is no point in using order by with insert. You always have to do this when retrieving the records (the order you get with select without order by is accidential). HTH Stefan Am Tuesday 02 September 2003 11:49 schrieb Alejandro Paz: Hi Stephan, Let's see the case : I use ORDER BY, because I want that order in PTemp table, so I do not have to order them later (because they are retrieved several times later). 1. Inserting with mysql c.l.i. : I get the records well sorted : first by a, secondly by b and finally by c (ascendig order). I'm using d to relink both tables in a join. 2. Inserting with the application : I get the records well sorted : first by a, secondly by b and finally by c, but in descending order. As all records has the same value in a, so the records that in case 1 start with '0' are at the beginning, the same records here are at the end. I'm using d to relink both tables in a join. I thought that was clear when I said reverse order, sorry. So you see what I mean ? thanks ! __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zülpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL
Re: insert ... select .. order by, problem
Roger, Thanks for the additional clarification Albert Atlanta - Original Message - From: Roger Baklund [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Albert [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 8:00 AM Subject: Re: insert ... select .. order by, problem * Albert Stefan, I'm Roger, but I reply anyway. :) Do you imply that tables cannot be sorted desc or asc based on one of the columns e.g. a last name? or am I misunderstanding you. In relational database theory the order of rows within the table is undefined, i.e. it is up to the server, and the server can re-organize a table at any time. If you want an ordered result, you have to use ORDER BY in your SELECT statement. However, the MySQL server has some features that can be used to achieve exactly what you ask. It is possible to sort a table physically, in order to do faster reads later. The order is however destroyed if you do additional inserts. See the ORDER BY option of the ALTER TABLE statement: ORDER BY allows you to create the new table with the rows in a specific order. Note that the table will not remain in this order after inserts and deletes. In some cases, it may make sorting easier for MySQL if the table is in order by the column that you wish to order it by later. This option is mainly useful when you know that you are mostly going to query the rows in a certain order; by using this option after big changes to the table, you may be able to get higher performance. URL: http://www.mysql.com/doc/en/ALTER_TABLE.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1115: Unknown character set: 'ucs2'
Dear programmers,
is this correct ?
mysql select version();
+---+
| version() |
+---+
| 4.1.0-alpha-max-debug |
+---+
1 row in set (0.00 sec)
mysql SET @s = CONVERT('ABC' USING ucs2);
ERROR 1115: Unknown character set: 'ucs2'
mysql
the manual has some statement about ucs2 :
Temporary restriction:
UCS-2 can't (yet) be used as a client character set. That means that
SET NAMES ucs2
will not work.
Can I please have an example how to utilize this ?
Yours sincerely
Morten Gulbrandsen
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Abt Mysqldump
try to export the database once again , using the command mysqldump database-name file.sql . then try importing the same using mysql database-name file.sql .(delete all the backups before trying this) by the way , are you trying to export/import between diff versions of mysql / or between diff systems of same version. Uma Shankari T. wrote: MS MS MSuse MSmysql database-name backupfilename MSI think , the backup file name should have .sql extension , like backup.sql MS MSHoeven, Maarten van der wrote: MS i have dumped the another 2 database contents with the text file extension..even before i have done this many times but now it is giving error..is there any other possibility to correct this ?? Regards, Uma -- Vidhya CS .
Re: Show database problem
When I enter : mysql\binSHOW GRANTS FOR uma; I get a response that show is not a command etc.. This is on Win32 version 4.x The same happens when I try it from mysql and also after I run mysqld Any ideas what is wrong here? Thanks Albert - Original Message - From: Joris Beckers [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 01, 2003 11:19 AM Subject: Show database problem I've got a user, admFrederic He got the grants shown below. Those are correct and I configured them that way. mysql SHOW GRANTS FOR admFrederic; +--- + | Grants for [EMAIL PROTECTED] | +--- + | GRANT USAGE ON *.* TO 'admFrederic'@'%' IDENTIFIED BY PASSWORD '6ddf5e1a1cc75e8a' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `cehdb`.* TO 'admFrederic'@'%' | +--- + But, when I log in using that username. That user can access the main database... mysql show databases; +--+ | Database | +--+ | cehdb| | mysql| +--+ Does anyone know how to fix this? Only cehdb should be accessable. Also, the user can run SQL commands on the mysql database. GRANT commands etc. are not possible. This is the version info. mysql status; -- mysql Ver 11.18 Distrib 3.23.53, for Win95/Win98 (i32) Connection id: 351 Current database: Current user: [EMAIL PROTECTED] Server version: 4.1.0-alpha-max-nt Protocol version: 10 Connection: nemesis via TCP/IP Client characterset:latin1 Server characterset:latin1 TCP port: Uptime: 1 hour 6 min 14 sec Thanks, Joris -- 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]
How much user LOAD can Mysql bear???
Hi All! How much user LOAD can Mysql bear before die. Regards, Tariq -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
upgrading
We are currently running production on ver 3.23. We have two db servers that are in need of hardware upgrade. DB1 replicates to DB2. I plan on taking DB2 offline, upgrading RAM and Processors, installing latest RH OS and MySQL 4.0. Then replace DB1 with the upgraded DB2 making it the new DB1 and then repeating the process with the old DB1 making it the new DB2. Here are my questions. Any known problmes with RH9.0 and MySQL 4.0? Our current 3.23 db uses MyISAM tables. I've read the how to on upgrading from 3.23 to 4.0 but I'm not really upgrading as much as moving the database to another server that just happens to be ver 4.0 instead of 3.23. If I do a On new server: # mysql create dbname On old server: # mysqldump dbname | mysql -h newserver dbname Will this take care of any table changes or will this cause problems? If I do things this way will I still need to run the mysql_fix_privilege_tables, mysql_convert_table_format etc? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backup procedure
All, I'm looking for opinions/suggestions on a backup procedure I plan on implementing. All databases (DBXX) will be MySQL ver 4.0 All our applications work with DB01. DB01 replicates to DB02. Once a day I will Stop the slave on DB02, lock the tables, flush the logs and perform a mysqldump of the database. I will then copy the existing bin.log files and config filesls to a backup directory and then backup the db dump and bin.logs to an external backup device. A) this seems like the most non-intrusive way to get a clean backup of the database, is it? B) I'm not sure how I'm going to automate the unlocking of the tables and the restart of the slave only AFTER the dump is done. Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Abt Mysqldump
Matthew (UK), I have two questions regarding this: 1. are you using the tick that is under the ~ sign on US keyboards, or the ' which is under the quotes on US keyboards? I tried both and get an error executing the following query in mysql client gui screen GRANT ALL mysql.* TO USER 'albert' mysql client tells me I have an SQL error I tried the other ` (the one under the ~) and that did not work either. 2. I have tried to enter passwords for users including root, yet mysql does not take them in, even though they are listed in my.ini (in clear which is odd) When I use the password in conjunction with the user I get an error that says access denied with password YES or sometimes with password NO. Any clues? Thanks for the help Albert Atlanta - Original Message - From: Matthew Smith [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 8:29 AM Subject: RE: Abt Mysqldump Hi The only problem I get with mysqldump is that if I have used a reserved word as a column name, then the create starement fails. (eg CREATE TABLE fred ( KEY int(10) not null default '0' ); will fail (but as produced by mysqldump) However, if you edit the file and put ` characters either side of the column name (ie KEY above) then it then works. You can sort this in the first instance if you use the '--quote-names' or '-Q' option to mysqldump (OK, so I've just read the man page for the first time as well) Regards Matthew -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 02 September 2003 18:39 To: [EMAIL PROTECTED] Subject: Re: Abt Mysqldump use mysql database-name backupfilename I think , the backup file name should have .sql extension , like backup.sql Hoeven, Maarten van der wrote: What are the errors? For example, is the error like unable to create the tables, because the tables still exist? See the dumpfile if tables are created (by default). If so, delete the tables first, and redump back the dumpfile. -Original Message- From: Uma Shankari T. [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 1:33 PM To: [EMAIL PROTECTED] Subject: Abt Mysqldump Hello, I have dumped the database contents as one txt file by using this command mysqldump databasename -uusername -ppasswd textfilename. but while redumping back to mysql it is giving some errors in the textfile..is there any possibility to redump the contents without any error.. Regards, Uma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Zie ook/see also: http://www.knmi.nl/maildisclaimer.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] . -- Vidhya CS . -- 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]
Sorting and use of tables
Stefan, Indeed, and my mistake (semantics). I meant what you explained. It is clear to me that the order in the table remains in the manner the data were entered, and that cannot be changed, unless a record is deleted and then re-entered, which would place it elsewhere (at the end). This does not really have any benefits IMHO. And yes, I understand that it is the output that is sorted based on the query. Thanks for clarifying this, and as I read my question, I should have seen the difference myself. Mea culpa! : =) I am quite familiar with SQL Server 2000, but need to use MySQL for a project for the University I am at (Devry Alpharetta, Atlanta), to capture the input from a student survey of the classes and the Faculty members. There are several fields: semester (char), course(char), courseID (int), Faculty (varchar[30] - if that is acceptable in that format - and the answers to 18 questions, all alpha characters (char) or numeric char (int), and one Boolean (yes/no or 1,0). I need to figure out how to best structure this, e.g. create tables on the fly (if that is possible using ASP/ADO and SQL with ODBC connector), or create tables with many to many relationships and store the data for each course survey in a separate table. The tables with many to many relationships would hold all the courses, courseID's, and Faculty members, and the answers to the survey would create links between those and the results from the surveys. A typical class unique identification would look like this: sum03_FBaah_CIS_349 The cols would be 1 through 18 + a calculation col for the average of questions 1 to 18 and a col for the average of all answers to question 1, question 2, etc ... Mind you I may export the answers to an excel spreadsheet and do the calculations there rather than in the DB itself. Anyway this is a long answer to your response but I wanted those who read this to get an idea of what I am working with. Any suggestions are welcomed. Albert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Show database problem
Albert, This may sound minuscule. You're certain that 'uma' is a user account and not a database or table? Normally, you use SHOW GRANTS FOR user_account. See example: mysql SHOW GRANTS FOR test_usr; +--+ | Grants for [EMAIL PROTECTED] +--+ | GRANT USAGE ON *.* TO 'test_usr'@'% | GRANT ALL PRIVILEGES ON 'tempdb'.* TO 'test_usr'@'%' | GRANT ALL PRIVILEGES ON 'test'.* TO 'test_usr'@'%' +--+ However, if you attempt to show grants on an object with SHOW GRANTS FOR object_name you get the following error. See example: mysql SHOW GRANTS FOR tempdb; ERROR 1141: There is no such grant defined for user 'tempdb' on host '%' Albert, one suggestion. Next time provide the exact error message. It makes it easier to assist. Regards, Adam -Original Message- From: Albert [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 8:46 AM To: [EMAIL PROTECTED] Subject: Re: Show database problem When I enter : mysql\binSHOW GRANTS FOR uma; I get a response that show is not a command etc.. This is on Win32 version 4.x The same happens when I try it from mysql and also after I run mysqld Any ideas what is wrong here? Thanks Albert - Original Message - From: Joris Beckers [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 01, 2003 11:19 AM Subject: Show database problem I've got a user, admFrederic He got the grants shown below. Those are correct and I configured them that way. mysql SHOW GRANTS FOR admFrederic; +--- + | Grants for [EMAIL PROTECTED] | +--- + | GRANT USAGE ON *.* TO 'admFrederic'@'%' IDENTIFIED BY PASSWORD '6ddf5e1a1cc75e8a' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `cehdb`.* TO 'admFrederic'@'%' | +--- + But, when I log in using that username. That user can access the main database... mysql show databases; +--+ | Database | +--+ | cehdb| | mysql| +--+ Does anyone know how to fix this? Only cehdb should be accessable. Also, the user can run SQL commands on the mysql database. GRANT commands etc. are not possible. This is the version info. mysql status; -- mysql Ver 11.18 Distrib 3.23.53, for Win95/Win98 (i32) Connection id: 351 Current database: Current user: [EMAIL PROTECTED] Server version: 4.1.0-alpha-max-nt Protocol version: 10 Connection: nemesis via TCP/IP Client characterset:latin1 Server characterset:latin1 TCP port: Uptime: 1 hour 6 min 14 sec Thanks, Joris -- 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: Auto Increment ID of Inserted Row
(newbie to MySQL) I've been banging my head against the wall on this one for a bit now, and I understand that last_insert_id() is per-connection based, but most webapps are connection pooled (simple) or clustered (harder). What are my options to get the id of the inserted row in a webapp? As a side note, I'm using JDBC to access the DB. my thoughts: 1- use an innoDB table, start a txn (lock the table), insert, select max(id_column), end txn (unlock the table) 2- make an id pool table (innodb), have app server grab pool of ids at startup, and when pool is empty in similar manner (lock, update, select, unlock) 3- look to other product (don't make me do this one ;) ) 4- continue to bang head against the wall please cc me on any replies, as although I sent a subscription request, I'm not on list yet... previous info- In the last episode (Sep 19), Steven Kreuzer said: What is the SQL to get the created AutoInc ID from a row that I have just inserted? SELECT MAX(id_field) FROM table Nope. If someone else inserted a record between the time you inserted yours and the time you run that select, your answer will be wrong. Use LAST_INSERT_ID(), or whatever construct your language provides for retrieving it without doing another query. -- Dan Nelson [EMAIL PROTECTED] -- Daniel Greene Manager, Software Development Chelsea Interactive [EMAIL PROTECTED] (571)203-4105 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show database problem
* Albert When I enter : mysql\binSHOW GRANTS FOR uma; I get a response that show is not a command etc.. This is on Win32 version 4.x The same happens when I try it from mysql and also after I run mysqld Any ideas what is wrong here? When you say you try it from mysql do you mean standing in the mysql directory in the command promt? In that case, that would be the explanation. When you see mysql on this list, it means the mysql SQL shell, AKA the mysql command line, AKA the mysql monitor, AKA the standard client. It is an executable in the mysql/bin directory, named mysql.exe on win32. Execute this program, and then enter your SQL commands. URL: http://www.mysql.com/doc/en/mysql.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show database problem
This is the error I get and yes uma is a user and listed in my.ini file actually C:\mysql\binSHOW GRANTS FOR uma; 'SHOW' is not recognized as an internal or external command, operable program or batch file. C:\mysql\bin Albert - Original Message - From: Fortuno, Adam [EMAIL PROTECTED] To: 'Albert' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 9:38 AM Subject: RE: Show database problem Albert, This may sound minuscule. You're certain that 'uma' is a user account and not a database or table? Normally, you use SHOW GRANTS FOR user_account. See example: mysql SHOW GRANTS FOR test_usr; +--+ | Grants for [EMAIL PROTECTED] +--+ | GRANT USAGE ON *.* TO 'test_usr'@'% | GRANT ALL PRIVILEGES ON 'tempdb'.* TO 'test_usr'@'%' | GRANT ALL PRIVILEGES ON 'test'.* TO 'test_usr'@'%' +--+ However, if you attempt to show grants on an object with SHOW GRANTS FOR object_name you get the following error. See example: mysql SHOW GRANTS FOR tempdb; ERROR 1141: There is no such grant defined for user 'tempdb' on host '%' Albert, one suggestion. Next time provide the exact error message. It makes it easier to assist. Regards, Adam -Original Message- From: Albert [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 8:46 AM To: [EMAIL PROTECTED] Subject: Re: Show database problem When I enter : mysql\binSHOW GRANTS FOR uma; I get a response that show is not a command etc.. This is on Win32 version 4.x The same happens when I try it from mysql and also after I run mysqld Any ideas what is wrong here? Thanks Albert - Original Message - From: Joris Beckers [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 01, 2003 11:19 AM Subject: Show database problem I've got a user, admFrederic He got the grants shown below. Those are correct and I configured them that way. mysql SHOW GRANTS FOR admFrederic; +--- + | Grants for [EMAIL PROTECTED] | +--- + | GRANT USAGE ON *.* TO 'admFrederic'@'%' IDENTIFIED BY PASSWORD '6ddf5e1a1cc75e8a' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `cehdb`.* TO 'admFrederic'@'%' | +--- + But, when I log in using that username. That user can access the main database... mysql show databases; +--+ | Database | +--+ | cehdb| | mysql| +--+ Does anyone know how to fix this? Only cehdb should be accessable. Also, the user can run SQL commands on the mysql database. GRANT commands etc. are not possible. This is the version info. mysql status; -- mysql Ver 11.18 Distrib 3.23.53, for Win95/Win98 (i32) Connection id: 351 Current database: Current user: [EMAIL PROTECTED] Server version: 4.1.0-alpha-max-nt Protocol version: 10 Connection: nemesis via TCP/IP Client characterset:latin1 Server characterset:latin1 TCP port: Uptime: 1 hour 6 min 14 sec Thanks, Joris -- 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: ERROR 1115: Unknown character set: 'ucs2'
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Tuesday 02 September 2003 1:36 pm, Morten Gulbrandsen wrote:
mysql SET @s = CONVERT('ABC' USING ucs2);
ERROR 1115: Unknown character set: 'ucs2'
mysql
It works for me. I think you need to be using mysql 4.1.1alpha from BK.
see: http://www.mysql.com/doc/en/Installing_source_tree.html
mysql SET @s = CONVERT('ABC' USING ucs2);
Query OK, 0 rows affected (0.09 sec)
...
[EMAIL PROTECTED] simon $ mysql_alpha --version
mysql_alpha Ver 14.1 Distrib 4.1.1-alpha, for pc-linux (i686)
the manual has some statement about ucs2 :
Temporary restriction:
UCS-2 can't (yet) be used as a client character set. That means that
SET NAMES ucs2
will not work.
Can I please have an example how to utilize this ?
I use latin-1 as my client character set and add all my UCS2 data in hex.
Example:
INSERT INTO foo (ucs2text,num) VALUES (_ucs2 x'006300610074', 123);
^^ Adds the text cat into ucs2text, and 123 into num...
There may be more graceful ways of doing this. Also, I am having trouble
making wildcards work using this method. For example... If I ran the above
SQL, then:
SELECT * FROM foo WHERE ucs2text LIKE cat ;
return a result, but:
SELECT * FROM foo WHERE ucs2text LIKE ca% ;
does not.
- - Simon
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.2 (GNU/Linux)
iD8DBQE/VJ0XPBt+tvwCnbYRAqLYAJ4gz3a5DISd28RETk+vaperus8xKwCfdsUN
nryG0WzFwcJ5QZiUwy4nPcE=
=NhU+
-END PGP SIGNATURE-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show database problem
Stefan, Indeed, and my mistake (semantics). I meant what you explained. It is clear to me that the order in the table remains in the manner the data were entered, and that cannot be changed, unless a record is deleted and then re-entered, which would place it elsewhere (at the end). This does not really have any benefits IMHO. And yes, I understand that it is the output that is sorted based on the query. Thanks for clarifying this, and as I read my question, I should have seen the difference myself. Mea culpa! : =) I am quite familiar with SQL Server 2000, but need to use MySQL for a project for the University I am at (Devry Alpharetta, Atlanta), to capture the input from a student survey of the classes and the Faculty members. There are several fields: semester (char), course(char), courseID (int), Faculty (varchar[30] - if that is acceptable in that format - and the answers to 18 questions, all alpha characters (char) or numeric char (int), and one Boolean (yes/no or 1,0). I need to figure out how to best structure this, e.g. create tables on the fly (if that is possible using ASP/ADO and SQL with ODBC connector), or create tables with many to many relationships and store the data for each course survey in a separate table. The tables with many to many relationships would hold all the courses, courseID's, and Faculty members, and the answers to the survey would create links between those and the results from the surveys. A typical class unique identification would look like this: sum03_FBaah_CIS_349 The cols would be 1 through 18 + a calculation col for the average of questions 1 to 18 and a col for the average of all answers to question 1, question 2, etc ... Mind you I may export the answers to an excel spreadsheet and do the calculations there rather than in the DB itself. Anyway this is a long answer to your response but I wanted those who read this to get an idea of what I am working with. Any suggestions are welcomed. Albert - Original Message - From: Fortuno, Adam [EMAIL PROTECTED] To: 'Albert' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 9:38 AM Subject: RE: Show database problem .com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show database problem
On Tue, 2 Sep 2003 08:45:42 -0400 Albert [EMAIL PROTECTED] wrote: When I enter : mysql\binSHOW GRANTS FOR uma; I get a response that show is not a command etc.. This is on Win32 version 4.x The same happens when I try it from mysql and also after I run mysqld Any ideas what is wrong here? Thanks you should enter this in mysql console, not in shell. run .../mysql/bin/mysql binary (--help option will show you all possible keys) and then enter MySQL commands. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re-establishing nuked log file
I recently restarted my MySQL server (4.0.10 in this case) with the general query log enabled, to help out with some debugging and optimization issues. After looking at a batch of these, I then deleted the log file directly, with rm foo.log, assuming that it would be re-generated as soon as the next query came in. It was not. Is there any way to get logging restarted without stopping and restarting the server itself, which is live and which I'd prefer not to interrupt? Thanks. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Crystal Decisions Report Application Server Problem
I'm writing to get some help on the following problem: We're running MySql (1.4.1 version) with MyODBC (version 3.51.06) and the Report Application Server 9 (RAS) from Crystal Decisions. We're having a problem getting the Report Application Server to connect to the MySql database via MyODBC. We have two databases going - an Oracle database and a MySQL database. We wrote a simple report for each and we're trying to run the report via the RAS sample web page (e-portfolio lite). The oracle report works fine - RAS manages to display it and access the database. The MySql report fails to connect to the database - it asks for a user name and password, but always fails to connect to the database (it prompts for another user name and password). However, Crystal Reports itself run the report fine. There seems to be a disconnect between how RAS connects to the database versus Crystal Reports. Anyone able to get RAS to work? Kristina -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Re-establishing nuked log file
I don't know the answer to your question, but as a side note, I've always found cat'ing /dev/null into a file to be safer if the file may be in use cat /dev/null foo.log -Original Message- From: Jesse Sheidlower [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 10:27 AM To: [EMAIL PROTECTED] Subject: Re-establishing nuked log file I recently restarted my MySQL server (4.0.10 in this case) with the general query log enabled, to help out with some debugging and optimization issues. After looking at a batch of these, I then deleted the log file directly, with rm foo.log, assuming that it would be re-generated as soon as the next query came in. It was not. Is there any way to get logging restarted without stopping and restarting the server itself, which is live and which I'd prefer not to interrupt? Thanks. Jesse Sheidlower -- 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: Re-establishing nuked log file
flush logs from the mysql command line works Jesse Sheidlower wrote: I recently restarted my MySQL server (4.0.10 in this case) with the general query log enabled, to help out with some debugging and optimization issues. After looking at a batch of these, I then deleted the log file directly, with rm foo.log, assuming that it would be re-generated as soon as the next query came in. It was not. Is there any way to get logging restarted without stopping and restarting the server itself, which is live and which I'd prefer not to interrupt? Thanks. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show database problem
OK I think I got it, in Win32 it has to be done from the mysql client screen and not from the command prompt. Either that or use another GUI client as the front end for MySQL I guess and I have had some suggestions on that one. I will try doing so. Thanks for the help Albert - Original Message - From: Roger Baklund [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Albert [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 9:36 AM Subject: Re: Show database problem * Albert When I enter : mysql\binSHOW GRANTS FOR uma; I get a response that show is not a command etc.. This is on Win32 version 4.x The same happens when I try it from mysql and also after I run mysqld Any ideas what is wrong here? When you say you try it from mysql do you mean standing in the mysql directory in the command promt? In that case, that would be the explanation. When you see mysql on this list, it means the mysql SQL shell, AKA the mysql command line, AKA the mysql monitor, AKA the standard client. It is an executable in the mysql/bin directory, named mysql.exe on win32. Execute this program, and then enter your SQL commands. URL: http://www.mysql.com/doc/en/mysql.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re-establishing nuked log file
On Tue, Sep 02, 2003 at 07:59:07AM -0700, Bruce Ferrell wrote: flush logs from the mysql command line works And so it does. Thank you. I misunderstood what the Manual said about this command, though I should have tried it first anyway. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Abt Mysqldump
For grant statemnets
grant all ON mysql.* to 'albert'
is the correct syntax
back ticks (`) are for around column names
single quotes (') are for around strings/varchars/chars...
M
-Original Message-
From: Albert [mailto:[EMAIL PROTECTED]
Sent: 02 September 2003 14:04
To: [EMAIL PROTECTED]
Subject: Re: Abt Mysqldump
Matthew (UK),
I have two questions regarding this:
1. are you using the tick that is under the ~ sign on US keyboards, or the '
which is under the quotes on US keyboards?
I tried both and get an error executing the following query in mysql client
gui screen
GRANT ALL mysql.* TO USER 'albert'
mysql client tells me I have an SQL error
I tried the other ` (the one under the ~) and that did not work either.
2. I have tried to enter passwords for users including root, yet mysql does
not take them in, even though they are listed in my.ini (in clear which is
odd)
When I use the password in conjunction with the user I get an error that
says access denied with password YES or sometimes with password NO.
Any clues?
Thanks for the help
Albert
Atlanta
- Original Message -
From: Matthew Smith [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Tuesday, September 02, 2003 8:29 AM
Subject: RE: Abt Mysqldump
Hi
The only problem I get with mysqldump is that if I have used a reserved
word
as a column name, then the create starement fails.
(eg
CREATE TABLE fred (
KEY int(10) not null default '0'
);
will fail (but as produced by mysqldump)
However, if you edit the file and put ` characters either side of the
column
name (ie KEY above) then it then works.
You can sort this in the first instance if you use the '--quote-names' or
'-Q' option to mysqldump
(OK, so I've just read the man page for the first time as well)
Regards
Matthew
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 02 September 2003 18:39
To: [EMAIL PROTECTED]
Subject: Re: Abt Mysqldump
use
mysql database-name backupfilename
I think , the backup file name should have .sql extension , like
backup.sql
Hoeven, Maarten van der wrote:
What are the errors?
For example, is the error like unable to create the tables, because the
tables still exist? See the dumpfile if tables are created (by default).
If so, delete the tables first, and redump back the dumpfile.
-Original Message-
From: Uma Shankari T. [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 02, 2003 1:33 PM
To: [EMAIL PROTECTED]
Subject: Abt Mysqldump
Hello,
I have dumped the database contents as one txt file by using this
command
mysqldump databasename -uusername -ppasswd textfilename.
but while redumping back to mysql it is giving some errors in the
textfile..is there any possibility to redump the contents without any
error..
Regards,
Uma
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Zie ook/see also: http://www.knmi.nl/maildisclaimer.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
.
--
Vidhya CS
.
--
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]
WARNING! Virus Win32/Sobig.F@mm detected
Your EMail with subject 'Thank you!', sent to the recipient(s) [EMAIL PROTECTED] contains a virus or other harmful content. The message has NOT been delivered to the recipients. Please contact the postmaster (mailto:[EMAIL PROTECTED]) to resolve this issue. /var/ex...006LT-00-tmp/document_9446.pif Infected: Win32/[EMAIL PROTECTED] -- Message generated by exiscan. NISC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlbug
shell groupadd mysql shell useradd -g mysql mysql shell cd /usr/local shell gunzip /path/to/mysql-VERSION-OS.tar.gz | tar xvf - shell ln -s full-path-to-mysql-VERSION-OS mysql shell cd mysql shell scripts/mysql_install_db shell chown -R root . shell chown -R mysql data I'm getting the error: data directory doesn't exist What is the most likely cause of this? - Jim Gronquist Computer Network and Programming Analyst Office of the Bursar Indiana University 812.856.3026 x6-3026 [EMAIL PROTECTED]
Re: Crystal Decisions Report Application Server Problem
Sounds like a problem authenticating the host the queries are coming from. Try using the grant command with like john@%, which allows any host to connect. (replace john with the username you want.) As a historical note, we tried using Crystal RAS with Lotus Notes and concluded it was too slow and too inflexible in generating reports. Because we wanted to try FileMaker, we switched to that with better results, but my team still thinks it's slow. So I'm getting ready to recommend a switch to MySQL and PHP, which I know will be blazing fast, and it will be simple to design whatever reports are desired. [EMAIL PROTECTED] ics.ca To: [EMAIL PROTECTED] cc: 09/02/2003 09:50 Subject: Crystal Decisions Report Application Server Problem AM I'm writing to get some help on the following problem: We're running MySql (1.4.1 version) with MyODBC (version 3.51.06) and the Report Application Server 9 (RAS) from Crystal Decisions. We're having a problem getting the Report Application Server to connect to the MySql database via MyODBC. We have two databases going - an Oracle database and a MySQL database. We wrote a simple report for each and we're trying to run the report via the RAS sample web page (e-portfolio lite). The oracle report works fine - RAS manages to display it and access the database. The MySql report fails to connect to the database - it asks for a user name and password, but always fails to connect to the database (it prompts for another user name and password). However, Crystal Reports itself run the report fine. There seems to be a disconnect between how RAS connects to the database versus Crystal Reports. Anyone able to get RAS to work? Kristina -- 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]
Fw: No valid command found
Does anyone know what prompt the message below to be sent by the list? I have now been getting a few of these and cannot figure out why. Thanks for any input Uma From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 11:39 AM Subject: No valid command found Your message does not contain a valid command for this mail server to process. No action has been taken. Message-Id: [EMAIL PROTECTED] From: [EMAIL PROTECTED] Time-Stamp: Tue, 2 Sep 2003 11:03:33 -0400 : Message contains [1] file attachments -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1115: Unknown character set: 'latin1_de'
mysql create table mytbl( c1 char(10) character set latin1_de); ERROR 1115: Unknown character set: 'latin1_de' mysql create table mytbl( c1 char(10) character set utf8); ERROR 1115: Unknown character set: 'utf8' mysql create table mytbl( c1 char(10) character set sjis); ERROR 1115: Unknown character set: 'sjis' mysql select version(); +---+ | version() | +---+ | 4.1.0-alpha-max-debug | +---+ 1 row in set (0.01 sec) mysql Hi again, is this solved in a next release, Please ? I need to insert statements in german language, öäüß is part of that. Yours Sincerely Morten Gulbrandsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing data into MySQL
I have data that is in a progress database. I need to get a copy of the data into my mysql database. What would be the best approach ? I can dump the data in any specific format, so. Never tried ODBC with Progress (8.2C12), so don't know if that can/will work. thanks, Darryl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Assembly file not found while trying to connect ASP.NET to MySQL
i copy the Bytefx.data i.72 i nto my project folder, but when i run the project, an error occur : File or assembly name System, or one of its dependencies, was not found. . As usual, i copy the file into my project folder, then i add a reference. After that i imports ByteFX.Data.MySQLCLIENT. I create a normal connection. why the problem above Assembly not found occur? Thanks Yahoo! Games - Who Wants to Be A Millionaire? Play now!
Large query techniques
Assuming that I had a database containing a single table used to record an audit trail of messages originated from customers and their applications like so: CREATE TABLE `LogMessage` ( `MessageID` int(11) NOT NULL auto_increment, `CustomerName` varchar(100) default '', `ApplicationName` varchar(100) default '', `MessageText` text, `Status` int(11) default '0', PRIMARY KEY (`MessageID`) ) TYPE=MyISAM; Each message could be in 4 different states (according to the value of the Status column) 0, 1, 2, 3 What would be the best way to query the table in order to generate counts of the number of messages in each state PER customer and application? I was thinking about: SELECT CustomerName, ApplicationName, '0' AS CountType, COUNT(*) AS Count FROM LogMessage WHERE Status = 0 GROUP BY CustomerName, ApplicationName UNION SELECT CustomerName, ApplicationName, '1' AS CountType, COUNT(*) AS Count FROM LogMessage WHERE Status = 1 GROUP BY CustomerName, ApplicationName UNION SELECT CustomerName, ApplicationName, '2' AS CountType, COUNT(*) AS Count FROM LogMessage WHERE Status = 2 GROUP BY CustomerName, ApplicationName UNION SELECT CustomerName, ApplicationName, '3' AS CountType, COUNT(*) AS Count FROM LogMessage WHERE Status = 3 GROUP BY CustomerName, ApplicationName What would be the correct way to index the table? KEY `comboindex1` (`CustomerName`,`Status`), KEY `comboindex2` (`CustomerName`,`ApplicationName`,`Status`), KEY `status` (`Status`), KEY `customername` (`CustomerName`), KEY `applicationname` (`ApplicationName`) I could have up to 30million rows in my table and am looking for some fundamental techniques in order to query such a large table in the manner described above. Any pointers or help would be much appreciated. Regards Stephen McMullan ANAM Wireless Internet Solutions http://www.anam.com +353 1 284 7555 Castle Yard, Saint Patrick's Road, Dalkey, County Dublin, Ireland -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1115: Unknown character set: 'latin1_de'
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tuesday 02 September 2003 5:40 pm, Morten Gulbrandsen wrote: mysql create table mytbl( c1 char(10) character set latin1_de); ERROR 1115: Unknown character set: 'latin1_de' IIRC latin1_de is not a character set in Mysql 4.1.. The character set latin1 contains all western european characters... So you'll be fine with those. You can specify a German-specific sort order by using the collation settings. There is a latin1_german1_ci collation. (Also a latin1_german2_ci, which works like the German phonebook instead of the dictionary.) So, you'd want: CREATE TABLE foo ( blah ... ) CHARACTER SET latin1 COLLATE latin1_german1_ci; - - Simon -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) iD8DBQE/VMnpPBt+tvwCnbYRAtxfAJ95zJyXpIcN4e0FqWMPuNwT9AqwUQCggc+9 2IES4NWNC97itxEvrVGdmHQ= =ejfA -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Large query techniques
I may be missing something, but why not just do: SELECT CustomerName, ApplicationName, Status, COUNT(1) AS Count FROM LogMessage GROUP BY Status, CustomerName, ApplicationName with rollup; which should return all the data you need in 1 query, which has got to run faster than 4 seperate queries... -Original Message- From: Stephen McMullan [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 12:59 PM To: [EMAIL PROTECTED] Subject: Large query techniques Assuming that I had a database containing a single table used to record an audit trail of messages originated from customers and their applications like so: CREATE TABLE `LogMessage` ( `MessageID` int(11) NOT NULL auto_increment, `CustomerName` varchar(100) default '', `ApplicationName` varchar(100) default '', `MessageText` text, `Status` int(11) default '0', PRIMARY KEY (`MessageID`) ) TYPE=MyISAM; Each message could be in 4 different states (according to the value of the Status column) 0, 1, 2, 3 What would be the best way to query the table in order to generate counts of the number of messages in each state PER customer and application? I was thinking about: SELECT CustomerName, ApplicationName, '0' AS CountType, COUNT(*) AS Count FROM LogMessage WHERE Status = 0 GROUP BY CustomerName, ApplicationName UNION SELECT CustomerName, ApplicationName, '1' AS CountType, COUNT(*) AS Count FROM LogMessage WHERE Status = 1 GROUP BY CustomerName, ApplicationName UNION SELECT CustomerName, ApplicationName, '2' AS CountType, COUNT(*) AS Count FROM LogMessage WHERE Status = 2 GROUP BY CustomerName, ApplicationName UNION SELECT CustomerName, ApplicationName, '3' AS CountType, COUNT(*) AS Count FROM LogMessage WHERE Status = 3 GROUP BY CustomerName, ApplicationName What would be the correct way to index the table? KEY `comboindex1` (`CustomerName`,`Status`), KEY `comboindex2` (`CustomerName`,`ApplicationName`,`Status`), KEY `status` (`Status`), KEY `customername` (`CustomerName`), KEY `applicationname` (`ApplicationName`) I could have up to 30million rows in my table and am looking for some fundamental techniques in order to query such a large table in the manner described above. Any pointers or help would be much appreciated. Regards Stephen McMullan ANAM Wireless Internet Solutions http://www.anam.com +353 1 284 7555 Castle Yard, Saint Patrick's Road, Dalkey, County Dublin, Ireland -- 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: mysqlbug
On Tuesday, September 02, 2003 5:01 PM CET, Gronquist, Jim M wrote: shell groupadd mysql shell useradd -g mysql mysql shell cd /usr/local shell gunzip /path/to/mysql-VERSION-OS.tar.gz | tar xvf - shell ln -s full-path-to-mysql-VERSION-OS mysql shell cd mysql shell scripts/mysql_install_db shell chown -R root . shell chown -R mysql data I'm getting the error: data directory doesn't exist What is the most likely cause of this? This looks like a MySQL installation... right? Well, you already said what the cause is ;) Then they might just have forgotten to create the directory in the tar.gz file. Try to create it on your own by mkdir data just before the chown -R mysql datd This should help. -- Yves Goergen [EMAIL PROTECTED] Please don't CC me (causes double mails) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Many Read and Writes...
So, we have a webmail application that uses a mysql server for holding it's session information. Obviously this causes many reads,updates,inserts, and deletes to happen. Here are my lock statistics. | Table_locks_immediate| 73099 | | Table_locks_waited | 32187 | This ratio seems REALLY bad to me. I'm wondering if there is anything I can do to tune the server a little. I have 4G of memory on my system, but MySQL only seems to be using about 64M. If giving it more would help with this a little, how do I do that? I have almost a 30 second avg on queries right now, and it's basically making my webmail stuff crap out. Should I just move the sessions away from the database? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many Read and Writes...
On Tue, Sep 02, 2003 at 10:37:34AM -0700, James Kelty wrote: So, we have a webmail application that uses a mysql server for holding it's session information. Obviously this causes many reads,updates,inserts, and deletes to happen. Here are my lock statistics. | Table_locks_immediate| 73099 | | Table_locks_waited | 32187| This ratio seems REALLY bad to me. It's not good. Especially if that's a small number of tables. I'm wondering if there is anything I can do to tune the server a little. I have 4G of memory on my system, but MySQL only seems to be using about 64M. If giving it more would help with this a little, how do I do that? What's your my.cnf file look like? How lagre are the data and index files? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 31 days, processed 1,373,610,821 queries (496/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many Read and Writes...
Well, there isn't a my.cnf file, so other that setting the max_connections with the -O option, it's whatever is default for 3.23.56. -James On Tue, 2003-09-02 at 10:50, Jeremy Zawodny wrote: On Tue, Sep 02, 2003 at 10:37:34AM -0700, James Kelty wrote: So, we have a webmail application that uses a mysql server for holding it's session information. Obviously this causes many reads,updates,inserts, and deletes to happen. Here are my lock statistics. | Table_locks_immediate| 73099 | | Table_locks_waited | 32187 | This ratio seems REALLY bad to me. It's not good. Especially if that's a small number of tables. I'm wondering if there is anything I can do to tune the server a little. I have 4G of memory on my system, but MySQL only seems to be using about 64M. If giving it more would help with this a little, how do I do that? What's your my.cnf file look like? How lagre are the data and index files? Jeremy -- James Kelty E-Commerce / Financial Systems Administrator Portland State University 503.725.9152 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlbug
On Tuesday, September 02, 2003 7:20 PM CET, Gronquist, Jim M wrote: Yves, Thanks so much! Yes, I was able to create the data directory and get farther along. Now, when I try and start cd ~ /etc/rc.d/rc3.d/S90mysql start starting mysql daemon with db from /usr/local/mysql/var 030902 11:02 mysql d ended Any ideas? Jim Ehm, sorry no. But I forwarded this question to the mysql list again... ;) PS: you can use the 'reply to all' function of your mailer next time. -- Yves Goergen [EMAIL PROTECTED] Please don't CC me (causes double mails) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many Read and Writes...
On Tue, Sep 02, 2003 at 10:58:03AM -0700, James Kelty wrote: Well, there isn't a my.cnf file, so other that setting the max_connections with the -O option, it's whatever is default for 3.23.56. Since you didn't answer the other questions, I'm going to do some guessing here... I'll guess that you have a lot of data and that MySQL hasn't allocated enough memory for its key buffer. That means it hitting the disk more often than it needs to and is slowing things down. I'd suggest setting up a my.cnf file with a larger key buffer to test performance. You might look at mytop, since it'll show your key buffer hit percentage: http://jeremy.zawodny.com/mysql/mytop/ See the sample my.cnf files that come with MySQL. One of them will likely be a good starting point for you. Jeremy On Tue, 2003-09-02 at 10:50, Jeremy Zawodny wrote: On Tue, Sep 02, 2003 at 10:37:34AM -0700, James Kelty wrote: So, we have a webmail application that uses a mysql server for holding it's session information. Obviously this causes many reads,updates,inserts, and deletes to happen. Here are my lock statistics. | Table_locks_immediate| 73099 | | Table_locks_waited | 32187| This ratio seems REALLY bad to me. It's not good. Especially if that's a small number of tables. I'm wondering if there is anything I can do to tune the server a little. I have 4G of memory on my system, but MySQL only seems to be using about 64M. If giving it more would help with this a little, how do I do that? What's your my.cnf file look like? How lagre are the data and index files? Jeremy -- James Kelty E-Commerce / Financial Systems Administrator Portland State University 503.725.9152 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 32 days, processed 1,373,779,139 queries (496/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many Read and Writes...
Whoa, ok. Sorry. I didn't read the questions about the data and index files. I'm, uh, not exactly sure how to tell that, can you give me a hint there as well? *look sheepishly around*... -James On Tue, 2003-09-02 at 11:06, Jeremy Zawodny wrote: On Tue, Sep 02, 2003 at 10:58:03AM -0700, James Kelty wrote: Well, there isn't a my.cnf file, so other that setting the max_connections with the -O option, it's whatever is default for 3.23.56. Since you didn't answer the other questions, I'm going to do some guessing here... I'll guess that you have a lot of data and that MySQL hasn't allocated enough memory for its key buffer. That means it hitting the disk more often than it needs to and is slowing things down. I'd suggest setting up a my.cnf file with a larger key buffer to test performance. You might look at mytop, since it'll show your key buffer hit percentage: http://jeremy.zawodny.com/mysql/mytop/ See the sample my.cnf files that come with MySQL. One of them will likely be a good starting point for you. Jeremy On Tue, 2003-09-02 at 10:50, Jeremy Zawodny wrote: On Tue, Sep 02, 2003 at 10:37:34AM -0700, James Kelty wrote: So, we have a webmail application that uses a mysql server for holding it's session information. Obviously this causes many reads,updates,inserts, and deletes to happen. Here are my lock statistics. | Table_locks_immediate| 73099 | | Table_locks_waited | 32187 | This ratio seems REALLY bad to me. It's not good. Especially if that's a small number of tables. I'm wondering if there is anything I can do to tune the server a little. I have 4G of memory on my system, but MySQL only seems to be using about 64M. If giving it more would help with this a little, how do I do that? What's your my.cnf file look like? How lagre are the data and index files? Jeremy -- James Kelty E-Commerce / Financial Systems Administrator Portland State University 503.725.9152 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 32 days, processed 1,373,779,139 queries (496/sec. avg) -- James Kelty E-Commerce / Financial Systems Administrator Portland State University 503.725.9152 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
keeping a fulltext index in memory
Hi, I'm having problems with a fulltext indexed table where it takes a long time return from a query where many rows match. I noticed that when I run a query like select count(*) from table where keywords like '%x%'; it takes a long time but after that all fulltext queries are much faster, I'm not talking about cached queries here, and I'm guessing that it's because the index has been loaded into memory. but then after a while it slows down again. what do I have to do to keep this from happening? my key_buffer is bigger than the total size of my MYI's, and my table_cache is higher than my # of tables. I'm using 4-0-14 standard on Linux. thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB slow?
I've not a clue - digging around somewhere on the Internet. I didn't see the info in the manual regarding fsync being used as default for Linux, but now I also notice that my manual says version 4.0.5 off to grab current manual Mikhail Entaltsev wrote: Paul, Where did you find information about 'littlesync' and 'nosync'? In InnoDB manual I found only ** This is only relevant on Unix. The default value for this is fdatasync. Then InnoDB uses fsync() to flush both the data and log files. If O_DSYNC is specified, InnoDB uses O_SYNC to open and flush the log files, but uses fsync() to flush the data files. If O_DIRECT is specified (available on some Linux versions starting from MySQL-4.0.14), InnoDB uses O_DIRECT to open the data files, and uses fsync() to flush both the data and log files. Note that InnoDB does not use fdatasync() or O_DSYNC because there have been problems with them on many Unix flavors. ** Mikhail. - Original Message - From: Paul Gallier To: Mikhail Entaltsev ; [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 4:24 AM Subject: Re: InnoDB slow? Thanks for the info. I'm running MySQL 4.0.14 under Redhat 8.0 / Linux 2.4.20. Here are the timings I ended up with from playing with innodb_flush_method: innodb_flush_method=fdatasync (default) 10 minutes 37 seconds innodb_flush_method=littlesync 10 minutes 22 seconds innodb_flush_method=O_DSYNC 5 minutes 18 seconds innodb_flush_method=nosync3 minutes 12 seconds MyISAM tables instead of InnoDB 2 minutes 34 seconds Now of course, the question is what potential harm am I looking at by using nosync or o_dsync? Mikhail Entaltsev wrote: Paul, if your MySQL server is runnign under Linux then try to play with innodb_flush_method variable. I've changed it to O_DSYNC and InnoDB became ~ 9 times faster (Suse 8.2 Linux 2.4.20-4GB i386). Also check that you didn't allocate too much memory (OS shouldn't swap). Best regards, Mikhail. - Original Message - From: Paul Gallier [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 2:57 AM Subject: InnoDB slow? I'm running a large database which is currently using MyISAM. There are approximately 300 million rows in about a dozen tables totaling 7GB of storage. The system is averaging 257 querries per second, probably peaking at around 500-600+ during busy times. We're running a single database with one programming doing insertions/updates and a web server doing only selects. The problem is that the insertions/updates tend to bog down a bit when the web side gets busy. I figured switching to InnoDB might help with the row-locking support, however, on my test system things ran very slow using InnoDB instead of MyISAM. This was just running the script handling the inserts/updates with no web access, but a test that took 2 minutes 34 seconds using MyISAM tables took 10 minutes 37 seconds using InnoDB. I setup enough disk space to hold the tables and had seemingly sufficient ammounts of mmeory configured for InnoDB, so I cannot understand the drastic slowdown. Any advise would be greatly appreciated.
different between index and key when create table
Can anyone tell me what is different between index and key when creating table? like this situation: create table info ( fname char(9), lname char (15), address char(30), index(lname)); or create table info ( fname char(9), lname char(15), address char(30), key(lname)); Thanks.
Fwd: different between index and key when create table
mysql: Can anyone tell me what is different between index and key when creating table? like this situation: create table info ( fname char(9), lname char (15), address char(30), index(lname)); or create table info ( fname char(9), lname char(15), address char(30), key(lname)); Thanks.
Re: Many Read and Writes...
On Tue, Sep 02, 2003 at 11:23:57AM -0700, James Kelty wrote: Whoa, ok. Sorry. I didn't read the questions about the data and index files. I'm, uh, not exactly sure how to tell that, can you give me a hint there as well? *look sheepishly around*... Sure. First you need to figure out where MySQL is storing your data files. You can find the value of datadir in the output of SHOW VARIABLES. In that directory, you'll see a sub-directory for each database. And each MyIAM table is composed of three files: table.MYI - indexes table.MYD - data table.frm - table definition Find out how large your various indexs are by doing something like: du -sk *.MYI Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 32 days, processed 1,375,019,175 queries (496/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many Read and Writes...
Ahhh! Ok, yeah the index file was 1.0k and the data file was 8.6M. -James On Tue, 2003-09-02 at 12:17, Jeremy Zawodny wrote: On Tue, Sep 02, 2003 at 11:23:57AM -0700, James Kelty wrote: Whoa, ok. Sorry. I didn't read the questions about the data and index files. I'm, uh, not exactly sure how to tell that, can you give me a hint there as well? *look sheepishly around*... Sure. First you need to figure out where MySQL is storing your data files. You can find the value of datadir in the output of SHOW VARIABLES. In that directory, you'll see a sub-directory for each database. And each MyIAM table is composed of three files: table.MYI - indexes table.MYD - data table.frm - table definition Find out how large your various indexs are by doing something like: du -sk *.MYI Jeremy -- James Kelty E-Commerce / Financial Systems Administrator Portland State University 503.725.9152 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fwd: different between index and key when create table
From: Vivian Wang [EMAIL PROTECTED] Date: 2003/09/02 Tue PM 02:16:26 CDT To: [EMAIL PROTECTED] Subject: Fwd: different between index and key when create table mysql: Can anyone tell me what is different between index and key when creating table? like this situation: create table info ( fname char(9), lname char (15), address char(30), index(lname)); or create table info ( fname char(9), lname char(15), address char(30), key(lname)); Thanks. From the online manual: KEY is normally a synonym for INDEX. From version 4.1, the key attribute PRIMARY KEY may also be specified as just KEY. This was implemented for compatibility with other databases. See: http://www.mysql.com/doc/en/CREATE_TABLE.html Note that in DB *theory*, however, keys and indexes are not the same. sjfromm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Auto Increment ID of Inserted Row
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Dan Greene wrote: (newbie to MySQL) I've been banging my head against the wall on this one for a bit now, and I understand that last_insert_id() is per-connection based, but most webapps are connection pooled (simple) or clustered (harder). What are my options to get the id of the inserted row in a webapp? As a side note, I'm using JDBC to access the DB. my thoughts: 1- use an innoDB table, start a txn (lock the table), insert, select max(id_column), end txn (unlock the table) 2- make an id pool table (innodb), have app server grab pool of ids at startup, and when pool is empty in similar manner (lock, update, select, unlock) 3- look to other product (don't make me do this one ;) ) 4- continue to bang head against the wall please cc me on any replies, as although I sent a subscription request, I'm not on list yet... Is there a reason you don't hold on to the same connection during the lifespan of one of your web 'transactions'? Also, to avoid a round trip to the server you should use Statement.getGeneratedKeys(). -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Are you MySQL Certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQE/VPjstvXNTca6JD8RAs3RAKC6T+RQnStioRLAJ1cwHHsBMwuPxgCeJU+N co0jO0c6pCDxIwxMAHaHkCk= =Nkgp -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql replication question(s)
Hi All, I'm having a few issues getting things up and running in regards to replication. I believe I have everything setup correctly the slave is roughly in the same spot as the master as far as the logs are concerned. However, when I make changes in the master db I don't see them show up in the slave db? show master status: 'mhqedi02-bin.012','622609','','' show slave status: 'mhqedi02','replicant','7003','60','mhqedi02-bin.012','630264','royedi01-relay-bin.001','630874','mhqedi02-bin.007','Yes','Yes','','','0','','0','629259','630874' show process list (slave): '3','system user','none','','Connect','20139','Reading master update','' '4','system user','none','','Connect','20139','Slave: waiting for binlog update','' my main concern is that the time on the process list is so long. Am I missing anything? I noticed there is a show hosts command to run on the master. When I run this I get an empty set back. Is this needed? Thanks for your help, Mario __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlimport command question
greetings, I have created a text file in windows (from a database) that has the format empnum name fname lname ext email listit bm bd hd bm, bd are integers and hd is date. I have tried to import using the follow command #mysqlimport -u root -pmypass -d --local iweb emp2.txt it imports the 97 records with 970 errors. When I look at the records created, the empnum field is properly populated, but all other fields are NULL. what am I screwing up here ? thanks, Darryl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql replication question(s)
Hi All, I'm having a few issues getting things up and running in regards to replication. I believe I have everything setup correctly the slave is roughly in the same spot as the master as far as the logs are concerned. However, when I make changes in the master db I don't see them show up in the slave db? show master status: 'mhqedi02-bin.012','622609','','' show slave status: 'mhqedi02','replicant','7003','60','mhqedi02-bin.012','630264','royedi01-relay-bin.001','630874','mhqedi02-bin.007','Yes','Yes','','','0','','0','629259','630874' show process list (slave): '3','system user','none','','Connect','20139','Reading master update','' '4','system user','none','','Connect','20139','Slave: waiting for binlog update','' my main concern is that the time on the process list is so long. Am I missing anything? I noticed there is a show hosts command to run on the master. When I run this I get an empty set back. Is this needed? Thanks for your help, Mario __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB slow?
Hi! - Original Message - From: Paul Gallier [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, September 02, 2003 9:55 PM Subject: Re: InnoDB slow? --060404050304080006000506 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit I've not a clue - digging around somewhere on the Internet. I didn't see the info in the manual regarding fsync being used as default for Linux, but now I also notice that my manual says version 4.0.5 off to grab current manual Mikhail Entaltsev wrote: Paul, Where did you find information about 'littlesync' and 'nosync'? In InnoDB manual I found only 'nosync' and 'littlesync' are undocumented features :). They were documented 2 years ago, but I removed the documentation because I did not want to maintain these features. ** This is only relevant on Unix. The default value for this is fdatasync. Then InnoDB uses fsync() to flush both the data and log files. If O_DSYNC is specified, InnoDB uses O_SYNC to open and flush the log files, but uses fsync() to flush the data files. If O_DIRECT is specified (available on some Linux versions starting from MySQL-4.0.14), InnoDB uses O_DIRECT to open the data files, and uses fsync() to flush both the data and log files. Note that InnoDB does not use fdatasync() or O_DSYNC because there have been problems with them on many Unix flavors. ** Mikhail. - Original Message - From: Paul Gallier To: Mikhail Entaltsev ; [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 4:24 AM Subject: Re: InnoDB slow? Thanks for the info. I'm running MySQL 4.0.14 under Redhat 8.0 / Linux 2.4.20. Here are the timings I ended up with from playing with innodb_flush_method: innodb_flush_method=fdatasync (default) 10 minutes 37 seconds innodb_flush_method=littlesync 10 minutes 22 seconds innodb_flush_method=O_DSYNC 5 minutes 18 seconds innodb_flush_method=nosync3 minutes 12 seconds MyISAM tables instead of InnoDB 2 minutes 34 seconds Now of course, the question is what potential harm am I looking at by using nosync or o_dsync? 'nosync' is dangerous. If there is a power outage, or the OS crashes, there is a great chance that your tablespace will be corrupted. MyISAM always runs in the 'nosync' mode, that is, it never calls fsync() to flush the files to disk. InnoDB's nosync is useful in testing if some OS/computer is extremely slow in fsync(). But it should not be used in a production system. O_DSYNC is safe, assuming there are no bugs in Linux/drivers/hardware. Since it is not very much used, the risk of bugs is bigger than for the default value fdatasync. I would rather tweak innodb_buffer_pool_size innodb_log_file_size innodb_flush_log_at_trx_commit to improve performance. Note that InnoDB really maps fdatasync() - fsync() O_DSYNC - O_SYNC This is because in 2001 there was some evidence that fdatasync() caused file corruption both in Linux and Solaris. Mikhail Entaltsev wrote: Paul, if your MySQL server is runnign under Linux then try to play with innodb_flush_method variable. I've changed it to O_DSYNC and InnoDB became ~ 9 times faster (Suse 8.2 Linux 2.4.20-4GB i386). Also check that you didn't allocate too much memory (OS shouldn't swap). Best regards, Mikhail. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqlimport command question
Darryl, Provide a copy of the table's details either with a describe table output or the table's definition and a sample of the input file's top 5-rows. Regards, Adam -Original Message- From: Darryl Hoar [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 4:31 PM To: [EMAIL PROTECTED] Subject: mysqlimport command question greetings, I have created a text file in windows (from a database) that has the format empnum name fname lname ext email listit bm bd hd bm, bd are integers and hd is date. I have tried to import using the follow command #mysqlimport -u root -pmypass -d --local iweb emp2.txt it imports the 97 records with 970 errors. When I look at the records created, the empnum field is properly populated, but all other fields are NULL. what am I screwing up here ? thanks, Darryl -- 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]
Select from one table where ID not in another table
All, Am wondering if it's possible to do a query that does something like this:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.id DOESN'T EXIST IN table2.id; Regards Marty --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select from one table where ID not in another table
All, Am wondering if it's possible to do a query that does something like this:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.id DOESN'T EXIST IN table2.id; Regards Marty --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select from one table where ID not in another table
Use LEFT JOIN SELECT t1.*, t2.id FROM table1 as t1 LEFT JOIN table2 as t2 ON t1.id=t2.id WHERE t2 IS NULL; -- something like that -- ---Original Message- --From: Martin Moss [mailto:[EMAIL PROTECTED] --Sent: Tuesday, September 02, 2003 1:50 PM --To: [EMAIL PROTECTED] --Subject: Select from one table where ID not in another table -- --All, -- --Am wondering if it's possible to do a query that does something like --this:- -- --SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 --WHERE table1.id DOESN'T EXIST IN table2.id; -- -- --Regards -- --Marty -- -- - --Outgoing mail is certified Virus Free. --Checked by AVG anti-virus system (http://www.grisoft.com). --Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- -- --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: Many Read and Writes...
Hi, If the index file is just 1k (the same size as an EMPTY table!), it sounds like you don't have any indexes. The 8.6MB table is probably at least a few thousand rows, right? Well, if all your queries are scanning the whole table, that would cause a few Table_locks_waited! :-) In order to help you add indexes, we will need the output of SHOW CREATE TABLE your_table; for your table(s). Also need examples of queries that you're running. Matt - Original Message - From: James Kelty Sent: Tuesday, September 02, 2003 2:23 PM Subject: Re: Many Read and Writes... Ahhh! Ok, yeah the index file was 1.0k and the data file was 8.6M. -James On Tue, 2003-09-02 at 12:17, Jeremy Zawodny wrote: On Tue, Sep 02, 2003 at 11:23:57AM -0700, James Kelty wrote: Whoa, ok. Sorry. I didn't read the questions about the data and index files. I'm, uh, not exactly sure how to tell that, can you give me a hint there as well? *look sheepishly around*... Sure. First you need to figure out where MySQL is storing your data files. You can find the value of datadir in the output of SHOW VARIABLES. In that directory, you'll see a sub-directory for each database. And each MyIAM table is composed of three files: table.MYI - indexes table.MYD - data table.frm - table definition Find out how large your various indexs are by doing something like: du -sk *.MYI Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select from one table where ID not in another table
Sorry I missed out the difficult bit, query sould read:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.otherkeyid = '7236523' AND table2.otherkeyid = '7236523' AND table1.id DOESN'T EXIST IN table2.id; If there are NO entries in table2 for otherkeyid I still want to get table1.* Regards Marty - Original Message - From: Martin Moss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 9:49 PM Subject: Select from one table where ID not in another table All, Am wondering if it's possible to do a query that does something like this:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.id DOESN'T EXIST IN table2.id; Regards Marty --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select from one table where ID not in another table
Sorry I missed out the difficult bit, query sould read:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.otherkeyid = '7236523' AND table2.otherkeyid = '7236523' AND table1.id DOESN'T EXIST IN table2.id; If there are NO entries in table2 for otherkeyid I still want to get table1.* Regards Marty - Original Message - From: Martin Moss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 9:49 PM Subject: Select from one table where ID not in another table All, Am wondering if it's possible to do a query that does something like this:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.id DOESN'T EXIST IN table2.id; Regards Marty --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select from one table where ID not in another table
select t1.* from table1 t1 LEFT JOIN table2 t2 on t1.id=t2.id WHERE t2.id IS NULL you can print out table2 values if you want, but they will all be NULL.. provided that table2.id and table1.id are the matches you are trying to find. Kelley Martin Moss wrote: All, Am wondering if it's possible to do a query that does something like this:- SELECT table1.*,table2.id from Table1 AS table1,Table2 AS table2 WHERE table1.id DOESN'T EXIST IN table2.id; Regards Marty --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 -- 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]
