Re: mysql.host: 3.23 to 4.0.2
Tim Johnson wrote: I am current working with mysql 3.23 on Red Hat 9.0 in the process of moving to slackware 10.0 on same machine, different partition. The version on the Slackwae partition is 4.0.2*. Question: Will I be able to simply mysqldump my mysql database on the RH 9.0 partion and then load the mysql DB on the Slackware partition safely? I feel it is no problem for normal dbs except mysql db. First, output your RH 9 dbs into ascii files using mysqldump and then load them into your Slackware DBMS. What issues are there, if any? Pointers to docs, URLs etc are appreciated. Thanks tim -- Chenzhou Cui (China-VO Project) National Astronomical Observatory | Tel: (8610)64841695 Chinese Academy of Sciences | FAX: (8610)64878240 Datun Road 20A, Chaoyang District | Email: [EMAIL PROTECTED] Beijing 100012, China | WWW: www.lamost.org/~cb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compiling for 64 Bit on OS X 10.4
Hi! Can anyone help me with a 64 Bit OS X 10.4 binary? I've tried to compile it myself but get errors in the make process that I have no idea what to do with. Apple ships a MySQL 4.1.10a binary with Tiger, but it's not 64 bit. MySQL doesn't have a 64 Bit OS X 10.4 binary yet. Maybe my question should be... When can we have a MySQL binary compiled for OS X 10.4 with 64 bit? Anyone with ideas on how I can compile one myself, would greatly appreciate it... Right now I run this (which will surely be munged by email clients adding line breaks - assume it's all one line): CC=gcc CFLAGS=-O3 -fno-omit-frame-pointer CXX=gcc CXXFLAGS=-O3 -fast -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti ./configure --prefix=/usr/local/mysql --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --disable-shared --without-isam --without-docs --without-debug --with-raid --without-bench --with-mysql=/usr/local/mysql Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determining if a table exists
On Mon, 02 May 2005 19:56:49 +0200, Eric Bergen [EMAIL PROTECTED] wrote: The column name is Tables_in_mysql. show syntax doesn't support order by. Information_schema tables in 5.0 do because they use the normal select syntax. -Eric Jim McAtee wrote: Hey, thanks. show tables like 'jst%_foo' looks like it would work just fine. Two questions: What is the column name returned and can I do an ORDER BY? If so, then I can just check the first and last rows in the results to determine the numeric range. My bad - server version is 3.23.x. Dictated by this (old as alabama) application. They refuse to support newer versions of MySQL. - Original Message - From: Eric Bergen [EMAIL PROTECTED] To: Jim McAtee [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, May 02, 2005 11:31 AM Subject: Re: Determining if a table exists I don't remember what commands are available in 3.21 but try these show tables like 'table_name'; then check mysql_num_rows on the result. describe table; check mysql_num_rows show tables; then pick out the table name; 3.21 is old as alabama (forrest gump) it's time for an upgrade :) Jim McAtee wrote: We're running an application that creates table names in a numeric sequence. For example: jst998_foo jst998_bar jst999_foo jst999_bar jst0001000_foo jst0001000_bar jst0001001_foo jst0001001_bar I need to write a maintenance app that first needs to determine the numeric range of existing tables. In the above example it would be 998 to 1001. What would be a half-way efficient way of doing this? MySQL version is 3.21.x, with MyISAM tables. Hello, What about: SELECT * FROM `information_schema`.`TABLES` where TABLE_SCHEMA = 'databaseName' AND TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'jst%' -- Dusan Kolesar Helsinska 19 040 13 Kosice Slovakia e-mail : [EMAIL PROTECTED] ICQ# : 160507424 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C API: Storing is easy; How do you retrieve?
Matthew Boehm wrote: Hey guys, (Why is there no C API specific list?) I want to write a C application that can take some audio file, store it in MySQL, then at a later date/time (upon request) pull from db and write to temporary file to be streamed. I've got the storing portion of the code down. The problem I have is, how do I SELECT out the audio and store it to a file on the local disk? 2 solutions for it. you can retrieve the data : mysql_query(mysql_conx, SELECT audio FROM ...); myres = mysql_store_result(mysql_conx); myrow = mysql_fetch_row(myres); audiodata = stripslashes(myrow[0]); (providing you escape characters); then the usual C function should do the trick (fopen, fwrite...) or SELECT INTO OUTFILE (check the manual, I don't know the details ;) to store the file on the server. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to design effective MySQL DB for multi-user web tracker system?
jkj kjhkjhk wrote: Hi, I develop multi-user web tracker system (php+mysql). In these days I have cca 150 users and each of the users has a set of the tables which are all included in central DB. In DB is on the whole cca 900 tables. Each user has also one (log) table in which are logged accesses to the web page. The table has 16 fields. It's OK, but if it's observed some web with huge traffic, the table has millions of records and performance (speed) is falling down. And the number of records in the table is still increasing. So I consider to automatically create each month new (log) table for each user. The main disadvantage of this solution is fact that by 150 users will be cca 2500 tables in database after 1 year. But on the other hand, the tables have less records so the speed of analysing is higher. I don't know whether it's OK to have 2500 tables in one DB... It's also difficult to manage so big DB with phpMyAdmin. Can you help me? Thanks... Don't create a table per user, but instead add a field in one table to reference this user, then you will have 150 times less tables to manage, therefore logging and truncation on a monthly basis wil be easier. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication 4.1.11 to 5.0.4beta
if i start to replicate the following table, i get this error: 050503 10:08:35 [ERROR] Key 1 - Found wrong stored record at 0 050503 10:08:35 [Note] Retrying repair of: './plugin_data/renderer' with keycache 050503 10:08:35 [ERROR] Key 1 - Found wrong stored record at 0 what's the problem??? -- CREATE TABLE `operatingsystem` ( `osid` int(10) unsigned NOT NULL default '0', `os` varchar(255) NOT NULL default '', PRIMARY KEY (`osid`), FULLTEXT KEY `os` (`os`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `operatingsystem` VALUES (1, 'OS: Microsoft Windows XP Service Pack 2'); INSERT INTO `operatingsystem` VALUES (2, 'OS: Microsoft Windows 98 SE A'); INSERT INTO `operatingsystem` VALUES (3, 'OS: Microsoft Windows XP'); INSERT INTO `operatingsystem` VALUES (4, 'OS: Microsoft Windows XP Service Pack 1'); INSERT INTO `operatingsystem` VALUES (5, 'OS: Microsoft Windows 2000 Service Pack 4'); INSERT INTO `operatingsystem` VALUES (6, 'OS: Microsoft Windows Millennium Edition\n'); INSERT INTO `operatingsystem` VALUES (7, 'OS: Microsoft Windows 2000 Service Pack 2'); INSERT INTO `operatingsystem` VALUES (8, 'OS: Microsoft Windows XP Dodatek Service Pack 2'); INSERT INTO `operatingsystem` VALUES (9, 'OS: Microsoft Windows XP Dodatek Service Pack. 1'); INSERT INTO `operatingsystem` VALUES (10, 'OS: Microsoft Windows XP Service Pack 2, v.2082'); INSERT INTO `operatingsystem` VALUES (11, 'OS: Microsoft Windows Server; 2003 family'); INSERT INTO `operatingsystem` VALUES (12, 'OS: Microsoft Windows XP Service Pack 2, v.2096'); INSERT INTO `operatingsystem` VALUES (13, 'OS: Microsoft Windows 95'); INSERT INTO `operatingsystem` VALUES (14, 'OS: Microsoft Windows 2000'); INSERT INTO `operatingsystem` VALUES (15, 'OS: Microsoft Windows XP Szervizcsomag 1'); INSERT INTO `operatingsystem` VALUES (16, 'OS: Microsoft Windows 98'); INSERT INTO `operatingsystem` VALUES (17, 'OS: Microsoft Windows 95 OSR2 B'); INSERT INTO `operatingsystem` VALUES (18, 'OS: Microsoft Windows 2000 Service Pack 3'); INSERT INTO `operatingsystem` VALUES (19, 'OS: Microsoft Windows XP Service Pack 2, v.2055'); INSERT INTO `operatingsystem` VALUES (20, 'OS: Microsoft Windows XP Service Pack 2, v.2135'); INSERT INTO `operatingsystem` VALUES (21, 'OS: Microsoft Windows 95 OSR2 C'); INSERT INTO `operatingsystem` VALUES (22, 'OS: Microsoft Windows XP Szervizcsomag 2'); INSERT INTO `operatingsystem` VALUES (23, 'OS: Microsoft Windows 98 B'); INSERT INTO `operatingsystem` VALUES (24, 'OS: Microsoft Windows 98 A'); INSERT INTO `operatingsystem` VALUES (25, 'OS: Microsoft Windows 2000 Service Pack 1'); INSERT INTO `operatingsystem` VALUES (26, 'OS: Microsoft Windows XP Service Pack 2, v.2149'); INSERT INTO `operatingsystem` VALUES (27, 'OS: Microsoft Windows Millennium Edition\n A'); INSERT INTO `operatingsystem` VALUES (28, 'OS: Microsoft Windows 2000 Service Pack 3, RC 3.51'); INSERT INTO `operatingsystem` VALUES (29, 'OS: Microsoft Windows XP Service Pack 2, v.2162'); INSERT INTO `operatingsystem` VALUES (30, 'OS: Microsoft Windows 2000 Dodatek Service Pack. 2'); INSERT INTO `operatingsystem` VALUES (31, 'OS: Microsoft Windows XP Service Pack 1, v.1081'); INSERT INTO `operatingsystem` VALUES (32, 'OS: Microsoft Windows XP Service Pack 2, v.2126'); INSERT INTO `operatingsystem` VALUES (33, 'OS: Microsoft Windows XP Service Pack 1, v.1050'); INSERT INTO `operatingsystem` VALUES (34, 'OS: Microsoft Windows Millennium Edition'); INSERT INTO `operatingsystem` VALUES (35, 'OS: Microsoft Windows 95 b'); INSERT INTO `operatingsystem` VALUES (36, 'OS: Microsoft Windows 95 a'); INSERT INTO `operatingsystem` VALUES (37, 'OS: Microsoft Windows 2000 Dodatek Service Pack. 1'); INSERT INTO `operatingsystem` VALUES (38, 'OS: Microsoft Windows Server; 2003 family Service Pack 1, v.1433'); INSERT INTO `operatingsystem` VALUES (39, 'OS: Microsoft Windows Server; 2003 family Service Pack 1, v.1039'); INSERT INTO `operatingsystem` VALUES (40, 'OS: Microsoft Windows XP Service Pack 2, v.2138'); INSERT INTO `operatingsystem` VALUES (41, 'OS: Microsoft Windows 2000 Service Pack 4, RC 3.154'); INSERT INTO `operatingsystem` VALUES (42, 'OS: Microsoft Windows XP Service Pack 1, v.1105'); INSERT INTO `operatingsystem` VALUES (43, 'OS: Microsoft Windows Server; 2003 family Service Pack 1, v.1289'); INSERT INTO `operatingsystem` VALUES (44, 'OS: Microsoft Windows NT Service Pack 2'); INSERT INTO `operatingsystem` VALUES (45, 'OS: Microsoft Windows XP Service Pack 2, v.2120'); INSERT INTO `operatingsystem` VALUES (46, 'OS: Microsoft Windows NT Service Pack 5'); INSERT INTO `operatingsystem` VALUES (47, 'OS: Microsoft Windows XP Service Pack 2, v.1204'); INSERT INTO `operatingsystem` VALUES (48, 'OS: Microsoft Windows 2000 Service Pack 4, RC 4.68'); INSERT INTO `operatingsystem` VALUES (49, 'OS: Microsoft Windows XP Service Pack 2, v.2142'); INSERT INTO `operatingsystem`
RE: Mysql 4.1.1 on Linux table size limit of 4GB and grant priv for Load
As it looks like no one else has replied. The default table size is 4GB - effectively if you do not modify the table create statement this is the size you get. To alter the table size you need to change the value of the MAX_ROWS parameter. The table size is governed by MAX_ROWS multiplied by average row length. SHOW TABLE STATUS will get you the average row length or 'properties' in mysqlcc. My advice is to rename you existing table and then do a create table definition MAX_ROWS=n as select columns) from renamed table. Alternatively use mysqldump to dump the table, drop the version on the database, edit the .txt to add the MAX_ROWS parameter to the create statement and then re-import. Changing the table using the ALTER TABLE command will take forever. Full details of the MAX_ROWS parameter can be found in the manual under the CREATE TABLE command. Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] -Original Message- From: V. Agarwal [mailto:[EMAIL PROTECTED] Sent: 30 April 2005 14:46 To: mysql@lists.mysql.com Subject: Mysql 4.1.1 on Linux table size limit of 4GB and grant priv for Load Hi, I am using Mysql built-in defaults for starting the server. It chokes when the table size grows to 4GB in data directory. What parameter needs to be tweaked for allowing larger size tables ? === -rw-rw1 vagarwal dev 4294967284 Apr 29 22:33 crec.MYD -rw-rw1 vagarwal dev 950261760 Apr 29 23:22 crec.MYI I created a user and granted all priviledges on a given database but it still does not allow 'load data infile ...' command to that user. Any clues ? = __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Character Set Problem
Hello, this is probably quite simple but I've got a text file that has non-english characters, when I view it I see : 'Dcouvrez un rseau europen d'htels et de restaurants beignant dans une atmosphre conviviale et familliale' I've imported this straight into my myisam DB which is set up with default charsets and collations - and these characters are just the same. I've looked into this but can't really grasp charsets and collations. I'd be happy to translate these codes back into the default charset (english characters). I'm using PHP to query the database, but would like to change the data in the DB if possible. I know this is a bit vague but I was wondering if anyone had any insight into this. Cheers, Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Functions
Hello.
See:
http://search.cpan.org/~spidb/Net-ext-1.011/lib/Net/Inet.pm
Mike Blezien [EMAIL PROTECTED] wrote:
Hello,
Sorry for the slightly OT question :)
Hoping we have some expert MySQL Function to perl function people on the list.
Is there an equivelant function in perl for converting IP's, the same as
MySQL
functions: INET_ATON('IP_ADDRESS_STR') and INET_NTOA('STR')
TIA,
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 4.1.1 on linux
Hello. Have you been at: http://dev.mysql.com/doc/mysql/en/full-table.html V. Agarwal [EMAIL PROTECTED] wrote: Hi, I am using Mysql built-in defaults for starting the server. It chokes when the table size grows to 4GB. === -rw-rw1 vagarwal dev 4294967284 Apr 29 22:33 crec.MYD -rw-rw1 vagarwal dev 950261760 Apr 29 23:22 crec.MYI I created a user and granted all priviledges on a given database but it still does not allow 'load data infile ...' command to that user. Any clues ? = __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Client requested master to start replication from impossible position
Hello. If the value of Read_Master_Log_Pos has a normal value, and slave asked weird position, this could be some internal bug. Now I see only a single way, but it could be difficult: if you have --log-slave-updates, use slave binary logs to find the last executed statement and it's position in master logs, and CHANGE MASTER to that position. Fagyal Csongor [EMAIL PROTECTED] wrote: Hi list, This night my slave stopped replicating the master (I am using 4.1.11 to replicate 4.1.9). Whenever I say start slave, The error log of the slave says: 050430 10:27:12 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'www-bin.03' at position 429178917 050430 10:27:12 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position (server_errno=1236) 050430 10:27:12 [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log 050430 10:27:12 [ERROR] Slave I/O thread exiting, read up to log 'www-bin.03', position 429178917 The interesting thing is that for the master: -rw-rw 1 mysql mysql 1073741913 Apr 26 21:52 www-bin.01 -rw-rw 1 mysql mysql 1073742497 Apr 29 01:37 www-bin.02 -rw-rw 1 mysql mysql 429153165 Apr 29 20:20 www-bin.03 -rw-rw 1 mysql mysql 169507064 Apr 30 11:01 www-bin.04 Looks like the master really has no position 429178917 as the length of www-bin.03 is smaller, 429153165 (if I understand this correctly). Is there a way to restart replication other than copying everything from the slave to the master again?? Thanks, - Csongor -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: mysqldump generates invalid code
Hello. With 'set foreign_key_checks=0' creation of the table works on my 4.1.11. Execute: set foreign_key_checks=0; Then create `shift` and all corresponding tables and set foreign_key_checks=1; [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Thank you for your help. I really appreciate it. I created the dump with --quote-names option, but this does not solve the problem unfortunately. I still get error: C:\xampp\xampp\mysql\binmysql -uroot db2 test5.txt ERROR 1005 at line 54: Can't create table '.\db2\shift.frm' (errno: 150) C:\xampp\xampp\mysql\bin Line 54 is the CREATE TABLE... line of the following bit, and the original database works fine. I want to upgarde, but unfortuantely my employer refuses to allow upgrade or to buy a fully supported database server. Is there perhaps a known defect recorded for this, which I could use as leverage. Andrew H CREATE TABLE `shift` ( `staff` char(20) NOT NULL default '', `shift` char(1) NOT NULL default '', `client` char(20) default NULL, `role` char(1) default NULL, `on` date NOT NULL default '-00-00', PRIMARY KEY (`staff`,`shift`,`on`), KEY `staff` (`staff`,`role`), KEY `shift` (`shift`), KEY `client` (`client`), KEY `role` (`role`), CONSTRAINT `shift_ibfk_1` FOREIGN KEY (`staff`, `role`) REFERENCES `staffrole` (`uniquename`, `role`), CONSTRAINT `shift_ibfk_2` FOREIGN KEY (`shift`) REFERENCES `shifttype` (`shift`), CONSTRAINT `shift_ibfk_3` FOREIGN KEY (`client`) REFERENCES `client` (`uniquename`), CONSTRAINT `shift_ibfk_4` FOREIGN KEY (`staff`) REFERENCES `staff` (`uniquename`), CONSTRAINT `shift_ibfk_5` FOREIGN KEY (`role`) REFERENCES `roletype` (`role`) ) TYPE=InnoDB; Original Message From: [EMAIL PROTECTED] Date: 30/04/2005 13:52 To: mysql@lists.mysql.com Subj: Re: mysqldump generates invalid code Hello. Make the dump using --quote-names option for mysqldump. Does it solve the problem? You have an old MySQL version. I strongly recommend you to upgrade. I backup a db using mysqldump, but when I come to execute the sql file on an empty db I get this C:\xampp\xampp\mysql\binmysql -uroot db2 test3.txtERROR 1005 at line 54: Can't create table '.\db2\shift.frm' (errno: 150) C:\xampp\xampp\mysql\bin I have tried to fix the foreign key constraint (error 150), but to no avail, I cannot see what is wrong with it nd anyway if I created this in the same mysql, then why doesn't it generate code that is valid? Here's the create that it fails on: CREATE TABLE shift ( staff char(20) NOT NULL default '', shift char(1) NOT NULL default '', client char(20) default NULL, role char(1) default NULL, on date NOT NULL default '-00-00', PRIMARY KEY (staff,shift,on), KEY staff (staff,role), KEY shift (shift), KEY client (client), KEY role (role), CONSTRAINT `shift_ibfk_1` FOREIGN KEY (`staff`, `role`) REFERENCES `staffrole` (`uniquename`, `role`), CONSTRAINT `shift_ibfk_2` FOREIGN KEY (`shift`) REFERENCES `shifttype` (`shift`), CONSTRAINT `shift_ibfk_3` FOREIGN KEY (`client`) REFERENCES `client` (`uniquename`), CONSTRAINT `shift_ibfk_4` FOREIGN KEY (`staff`) REFERENCES `staff` (`uniquename`), CONSTRAINT `shift_ibfk_5` FOREIGN KEY (`role`) REFERENCES `roletype` (`role`)) TYPE=InnoDB; I've tried back ticks on all the `on` column references, but still get same error. I have MySQL 4.0.18 and the mysqldump is :mysqldump Ver 9.10 Distrib 4.0.17, for Win95/Win98 Is this the problem? If so where do I get the right version of mysqldump? Later ones may not be bkwds compat. Can any-one help? Andrew H [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can't start mysqld
Hello. Are you using an official binary? Was there any error messages before crashing? Resolve the stack trace. Follow recomendations from: http://www.mysql.com/doc/en/Crashing.html Jack [EMAIL PROTECTED] wrote: hi,all. i can't start mysqld anymore , it's log reports below: 050503 00:09:58 mysqld started mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16384 read_buffer_size=131072 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 19215 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x40035158, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x814aecd 0x40035450 (nil) New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Reso lved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 050503 00:09:58 mysqld ended and i try to run mysql_install_db the backtrace is still 0x814aecd .. and i haven't found any one else has met this problem . so any suggestion ? i believe that thereis something wrong with my.cnf or i havn'e set some variable of it, because i have succeed starting the mysqld before my system has been cracked . and don't think that the system has some problem , the system i backup from the system that the mysqld has been started before. see my my.cnf: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-locking set-variable = key_buffer_size=16K set-variable = max_allowed_packet=1M set-variable = thread_stack=64K set-variable = table_cache=4 set-variable = sort_buffer=64K set-variable = net_buffer_length=2K # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 skip-innodb skip-bdb [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid jack -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Collation latin1_general_cs
Hello. | latin1_general_cs | latin1 | 49 | | | 0 | You have this collation installed. The output from SHOW COLLATION includes all available character sets. Andre Matos [EMAIL PROTECTED] wrote: Hi List, I was checking the list of collations for a character set latin that I have in my MySQL version 4.1.10-standard running on Mac OS X 10.4. The list that I have, bellow, does not include latin1_general_cs. What will happen if I force to use it when I create a database, tables and fields? Will it work or not? mysql SHOW COLLATION LIKE 'latin1%'; +---+-++-+--+-+ | Collation | Charset | Id | Default | Compiled | Sortlen | +---+-++-+--+-+ | latin1_german1_ci | latin1 | 5 | | | 0 | | latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 | | latin1_danish_ci | latin1 | 15 | | | 0 | | latin1_german2_ci | latin1 | 31 | | Yes | 2 | | latin1_bin| latin1 | 47 | | Yes | 1 | | latin1_general_ci | latin1 | 48 | | | 0 | | latin1_general_cs | latin1 | 49 | | | 0 | | latin1_spanish_ci | latin1 | 94 | | | 0 | +---+-++-+--+-+ Thanks for any help. Andre -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Data files
Hello. See: http://dev.mysql.com/doc/mysql/en/myisam-storage-engine.html http://dev.mysql.com/doc/mysql/en/using-innodb-tables.html V. Agarwal [EMAIL PROTECTED] wrote: Hi, On PC, I see *.frm files for each table while I see 3 files on Unix/Linux (*.MYD,*,MYI,*.frm). Need to know what these files are. I created a table on PC Mysql with type set to InnoDB which also created *.frm file. Any ideas ? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Synchronizing InnoDB tables
Hello. As for me, SELECT ... FOR UPDATE could help in your situation. See: http://dev.mysql.com/doc/mysql/en/innodb-locking-reads.html Locking the whole InnoDB table is usually not a very good solution, because it does row-level locking. Marcin Lewandowski [EMAIL PROTECTED] wrote: Hi, I want to use InnoDB tables in my project, because I can use transactions with them. I would write a hotel room's reservation system and sometimes I would need to get from particular SELECT query exact state - whether room is reserved or not. Because, when I'm using innodb I work only on copy of database, is it possible to check if any other connection is not writing to particular table? Would locking tables help? Or maybe I should do something else then locking table when writing reservation? Thanks, -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character Set Problem
Hello, Is the MySQL server that you are using MySQL4.1.x or MySQL5.0.x? As for most binarys of PHP and MySQL, the default charset of the MySQL connection client is set as 'latin1'. Therefore, if charset with the server is not 'latin1', it is necessary to set the MySQL connection client properly. It is whether to set to use the charset that you use by default, to compile the binary or to set the MySQL connection first by 'SET NAMES' syntax. http://dev.mysql.com/doc/mysql/en/set-option.html Regards, -- Sumito_Oda mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
order by confusion
Greetings! This might be a stupid question but here goes: I have a table that contains a column entitled current_pos. I want to search this table and then order the results by current_pos. Now I am running the following SQL query on the table: SELECT * FROM ab_leader_board WHERE sex = 'F' and cup = 'Kids' ORDER BY current_pos DESC; After running this the results are returned but as 2, 1, 0 , 0 etc. If I use: SELECT * FROM ab_leader_board WHERE sex = 'F' and cup = 'Kids' ORDER BY current_pos ASC; It returns 0, 1, 2 How do I go about getting this to return the results as 1,2,3,4 etc.? Any help would be appreciated. Thank you! -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers emotionalize.conceptualize.visualize.realize Landlines Tel: +27125468436 Fax: +27125468436 Web email:[EMAIL PROTECTED] Global: www.volume4.com Messenger Yahoo!: v_olume4 AOL: v0lume4 MSN: [EMAIL PROTECTED] We support OpenSource Get Firefox!- The browser reloaded - http://www.mozilla.org/products/firefox/ This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: order by confusion
[snip] This might be a stupid question but here goes: I have a table that contains a column entitled current_pos. I want to search this table and then order the results by current_pos. Now I am running the following SQL query on the table: SELECT * FROM ab_leader_board WHERE sex = 'F' and cup = 'Kids' ORDER BY current_pos DESC; After running this the results are returned but as 2, 1, 0 , 0 etc. If I use: SELECT * FROM ab_leader_board WHERE sex = 'F' and cup = 'Kids' ORDER BY current_pos ASC; It returns 0, 1, 2 How do I go about getting this to return the results as 1,2,3,4 etc.? Any help would be appreciated. Thank you! [/snip] So, is 1 == 0? Or do you need to start with any non-zero position? If I use: SELECT * FROM ab_leader_board WHERE sex = 'F' AND cup = 'Kids' AND current_pos '0' ORDER BY current_pos ASC; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: order by confusion
Schalk Neethling wrote: Greetings! This might be a stupid question but here goes: I have a table that contains a column entitled current_pos. I want to search this table and then order the results by current_pos. Now I am running the following SQL query on the table: SELECT * FROM ab_leader_board WHERE sex = 'F' and cup = 'Kids' ORDER BY current_pos DESC; After running this the results are returned but as 2, 1, 0 , 0 etc. If I use: SELECT * FROM ab_leader_board WHERE sex = 'F' and cup = 'Kids' ORDER BY current_pos ASC; It returns 0, 1, 2 How do I go about getting this to return the results as 1,2,3,4 etc.? Any help would be appreciated. Thank you! Well, since the results are 0, 1, 2 you are not going to get 1,2,3,4. You are getting what you are asking for, and it appears to be what you want, so what is the problem? A bit more detail and a real example might help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign Key Restriction
Hi I updated mySQL 4.0.8 to 4.1.1 and I have now problems with foreign key retrictions. Is it right, that mySQL 4.1.x has something changed due to the foreign key restriction? Is there an option to turn off the foreign key restriction in mySQL 4.1.1? Thanks. -- Oliver Hirschi http://www.FamilyHirschi.ch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Collation problems or messed joins?
Hi everyone! I'm havng the weirdest problem with mysql. I had v1.1.07 and everything worked like a charm. But it turns out that we had the need to move everything to UTF8 (until then, only the columns in russian where set to utf8, and the rest was latin1). Once we moved everything to utf8, this (the query is at the end of the email) didn't work anymore, apparently because of a collation problem with the literals. After doing some research i found that this wasn't a problem anymore in 4.1.11, so i decided to upgrade... After upgrading the query doesn't throw an error, but it doesn't return all the data it should... The funny thing is that i thought that the problem was in the 'case's that i use in the query, so i remove them from the query and the query runs fine. But later, i tried something else, and insetead of removing the cases y switched the inners for left joins, and the query worked fine, just like before, and it turns out it doesn't even matter wether I switch both joins or just one of them (either one or the other). If anybody can test this, and needs more data, or needs a script with the tables and data, I'll be glad to send it. Thanks!! Andrés Villanueva This is the problematic query: SELECT paper_id, Number, Revision, b.type, c.type_en as type2, a.name_en as name , case name_en when '' then 'N' else 'Y' end as name_en , case name_fr when '' then 'N' else 'Y' end as name_fr , case name_ru when '' then 'N' else 'Y' end as name_ru , case name_sp when '' then 'N' else 'Y' end as name_sp FROM `tpatcm28_paper` a inner join `tpgen_fmt_type` b using (fmt_type_id) inner join `tpatcm_pap_type` c on (a.pap_type_id = c.pap_type_id) order by Number, Revision -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Collation problems or messed joins?
Have you check that the collation for the text columns match the collation for the table and that you've set UTF8 for the query (set char set utf8). Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] -Original Message- From: Andrés Villanueva [mailto:[EMAIL PROTECTED] Sent: 03 May 2005 15:33 To: mysql@lists.mysql.com Subject: Collation problems or messed joins? Hi everyone! I'm havng the weirdest problem with mysql. I had v1.1.07 and everything worked like a charm. But it turns out that we had the need to move everything to UTF8 (until then, only the columns in russian where set to utf8, and the rest was latin1). Once we moved everything to utf8, this (the query is at the end of the email) didn't work anymore, apparently because of a collation problem with the literals. After doing some research i found that this wasn't a problem anymore in 4.1.11, so i decided to upgrade... After upgrading the query doesn't throw an error, but it doesn't return all the data it should... The funny thing is that i thought that the problem was in the 'case's that i use in the query, so i remove them from the query and the query runs fine. But later, i tried something else, and insetead of removing the cases y switched the inners for left joins, and the query worked fine, just like before, and it turns out it doesn't even matter wether I switch both joins or just one of them (either one or the other). If anybody can test this, and needs more data, or needs a script with the tables and data, I'll be glad to send it. Thanks!! Andrés Villanueva This is the problematic query: SELECT paper_id, Number, Revision, b.type, c.type_en as type2, a.name_en as name , case name_en when '' then 'N' else 'Y' end as name_en , case name_fr when '' then 'N' else 'Y' end as name_fr , case name_ru when '' then 'N' else 'Y' end as name_ru , case name_sp when '' then 'N' else 'Y' end as name_sp FROM `tpatcm28_paper` a inner join `tpgen_fmt_type` b using (fmt_type_id) inner join `tpatcm_pap_type` c on (a.pap_type_id = c.pap_type_id) order by Number, Revision -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Collation problems or messed joins?
The tables are now entirely in utf8, and that is also the instance's default. I'm using the .net dll to connect and the connection string has charset = utf8. I'm testing everything with the query browser, and i get the exact same behaviour. Again, if anyone wants a script to recreate the tables with some data, I can send it to you instantly. Thanks Andrés Villanueva Kevin Cowley wrote: Have you check that the collation for the text columns match the collation for the table and that you've set UTF8 for the query (set char set utf8). Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] -Original Message- From: Andrés Villanueva [mailto:[EMAIL PROTECTED] Sent: 03 May 2005 15:33 To: mysql@lists.mysql.com Subject: Collation problems or messed joins? Hi everyone! I'm havng the weirdest problem with mysql. I had v1.1.07 and everything worked like a charm. But it turns out that we had the need to move everything to UTF8 (until then, only the columns in russian where set to utf8, and the rest was latin1). Once we moved everything to utf8, this (the query is at the end of the email) didn't work anymore, apparently because of a collation problem with the literals. After doing some research i found that this wasn't a problem anymore in 4.1.11, so i decided to upgrade... After upgrading the query doesn't throw an error, but it doesn't return all the data it should... The funny thing is that i thought that the problem was in the 'case's that i use in the query, so i remove them from the query and the query runs fine. But later, i tried something else, and insetead of removing the cases y switched the inners for left joins, and the query worked fine, just like before, and it turns out it doesn't even matter wether I switch both joins or just one of them (either one or the other). If anybody can test this, and needs more data, or needs a script with the tables and data, I'll be glad to send it. Thanks!! Andrés Villanueva This is the problematic query: SELECT paper_id, Number, Revision, b.type, c.type_en as type2, a.name_en as name , case name_en when '' then 'N' else 'Y' end as name_en , case name_fr when '' then 'N' else 'Y' end as name_fr , case name_ru when '' then 'N' else 'Y' end as name_ru , case name_sp when '' then 'N' else 'Y' end as name_sp FROM `tpatcm28_paper` a inner join `tpgen_fmt_type` b using (fmt_type_id) inner join `tpatcm_pap_type` c on (a.pap_type_id = c.pap_type_id) order by Number, Revision -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql-mysqld connection
Just to echo the thoughts from Jeremiah, we have several freely available open source connectors that work *VERY HARD* tracking the protocol so it's hard to understand a situation where you would need to write your own implementation. -Reggie -Original Message- From: Nikola Skoric [mailto:[EMAIL PROTECTED] Sent: Monday, May 02, 2005 10:30 AM To: Jeremiah Gowdy; mysql@lists.mysql.com Subject: Re: mysql-mysqld connection Dana Sun, 1 May 2005 11:11:40 -0700, Jeremiah Gowdy rece: Where could I find speciffication of how MySQL client and server communicate? /... Look at the source code of libmysql? So, document regarding this protocol doesn't exist? Hm, and, where could I find the source code of libmysql? I googled, but failed to locate it. I'd be thrilled if you could help... -- Znam, kaljavo ordenje na mom kaputu je... -- 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 mem usage
Hi, I have a P4 system with 1GB Ram and 512MB Swap (a little low I know). I'm running a multi threaded MySQL installation on it. With a bit of shock, I realised today that I ran completely out of swap space!!! Each MySQL thread is consuming about 160MB of ram, and I had close to 40 threads running. All my databases currently are approximately 1.5GB as far as file size goes, so it's not *that* much really. The biggest table is MyISAM (for delayed inserts), holding approximately 2million records currently. Is there a way to limit the amount of threads that MySQL can start, and to manage the pool of threads? I.E. min servers, max servers, idle servers, etc And, can someone point me to a good side regarding getting optimum values for my.cnf? I'm running MySQL 4.1.11 my.cnf: [mysqld] log-queries-not-using-indexes skip-name-resolve skip-show-database skip-external-locking port = 3306 socket = /tmp/mysql.sock key_buffer_size= 8M max_allowed_packet = 4M bind-address = x.x.x.x.122 server-id = 100 set-variable = back_log=10 set-variable = connect_timeout=10 set-variable = interactive_timeout=28800 set-variable = interactive_timeout=20 set-variable = join_buffer=256000 set-variable = key_buffer_size=64M set-variable = max_allowed_packet=1M set-variable = max_connect_errors=512 set-variable = max_connections=64 set-variable = max_user_connections=64 set-variable = myisam_sort_buffer_size=16M set-variable = net_buffer_length=32K set-variable = net_read_timeout=30 set-variable = net_retry_count=1 set-variable = net_write_timeout=60 set-variable = query_cache_size=16M set-variable = query_cache_limit=1M set-variable = query_cache_type=1 set-variable = read_buffer_size=2M set-variable = sort_buffer=2M set-variable = table_cache=64 set-variable = thread_stack=64k set-variable = thread_cache=16 set-variable = thread_concurrency=2 set-variable = tmp_table_size=20 set-variable = wait_timeout=28800 set-variable = delayed_insert_limit=2000 set-variable = delayed_insert_timeout=1800 set-variable = delayed_queue_size=8000 set-variable = max_delayed_threads=32 -- Chris. I love deadlines. I especially love the whooshing sound they make as they fly by... - Douglas Adams, 'Hitchhiker's Guide to the Galaxy' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql mem usage
In the last episode (May 03), Chris Knipe said: I have a P4 system with 1GB Ram and 512MB Swap (a little low I know). I'm running a multi threaded MySQL installation on it. With a bit of shock, I realised today that I ran completely out of swap space!!! Each MySQL thread is consuming about 160MB of ram, and I had close to 40 threads running. All my databases currently are approximately 1.5GB as far as file size goes, so it's not *that* much really. The biggest table is MyISAM (for delayed inserts), holding approximately 2million records currently. Threads don't have their own memory. The process as a whole does. I can only assume you're running a Linux 2.4 kernel that displays threads with separate pids in top, which causes all sorts of confusion. They all share the same address space though so it's only using 160MB. If you're swapping, chances are it's something else at fault. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql-mysqld connection
Dana Tue, 3 May 2005 11:34:39 -0500, Reggie Burnett rece: Just to echo the thoughts from Jeremiah, we have several freely available open source connectors that work *VERY HARD* tracking the protocol so it's hard to understand a situation where you would need to write your own implementation. My aim is not to write my own implementation but to understand how that comunication works :-) And I did. Source code is very well documented, makes me think about my own code documenting (which is nonexistent :-) )... thanks for help, anyway... -- Znam, kaljavo ordenje na mom kaputu je... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Running a query on multiple databases
Hi, I would like to run a query on two tables that reside in two distinct databases: select * from db1.table1 join db2.table2; This lists both tables adjacent to one another rather than in a sequential fashion as one would get if both tables came from the same database. Is there a way to accomplish this task. Thank you. -- -Mahmoud Badreddine
Re: Running a query on multiple databases
Mahmoud Badreddine [EMAIL PROTECTED] wrote on 05/03/2005 01:43:55 PM: Hi, I would like to run a query on two tables that reside in two distinct databases: select * from db1.table1 join db2.table2; This lists both tables adjacent to one another rather than in a sequential fashion as one would get if both tables came from the same database. Is there a way to accomplish this task. Thank you. -- -Mahmoud Badreddine As long as the databases are on the same server and the account you logged into mysql with has the appropriate permissions, your statement should work. What happens when you try it? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Running a query on multiple databases
[snip] I would like to run a query on two tables that reside in two distinct databases: select * from db1.table1 join db2.table2; This lists both tables adjacent to one another rather than in a sequential fashion as one would get if both tables came from the same database. Is there a way to accomplish this task. [/snip] You don't say what version of MySQL, but this would be the expected behavior. UNION would be a good start... http://dev.mysql.com/doc/mysql/en/union.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
perl/mysql issue...
hi...
i have the following issue.. i've researched it from google.. but i still
can't quite figure it out...
i'm using a test app with DBI-connect() and i'm getting the following
response... (print/debug statements...)
--
'bd dsn =
DBI:mysql:livejournal;host=192.168.1.55;port=3306;mysql_connect_timeout=2
'bd user = lj
'bd passwd = ljpass
'bd fdsn1 = DBI:mysql:livejournal;host=192.168.1.55;port=3306|lj|ljpass
'bd loops dbh = 0
'bd loops user = lj
'bd loops pass = ljpass
'bd loops dbh111 = DBI::db=HASH(0x9bf20c8)
'DBD::mysql::db selectrow_hashref failed: Access denied for user:
'[EMAIL PROTECTED]' (Using password: NO) at /var/www/html/cgi-bin/DBI/Role.pm
line 347.
$dbh = DBI-connect($dsn, $user, $pass, {
PrintError = 1,
AutoCommit = 1,});
-
i did the following to configure the mysql database/table
--
$ mysql -uroot -p
mysql CREATE DATABASE livejournal;
mysql GRANT ALL PRIVILEGES ON livejournal.* TO
lj@'localhost' IDENTIFIED BY 'ljpass';
---
i can access the mysql db/livejournal from the mysql client/command line.
mysql -ulj -host192.168.1.55 -p
i'm pretty sure i've somehow made an error/left something out when
configuring the mysql db for access from perl apps. i'm running the perl app
from the same machine...
can anyone give me pointers/shed light on what i might need to change. my
gut tells me that there's some mysql table/attribute that needs to be
changed, but i'm not that familiar with mysql...
thanks
bruce
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running a query on multiple databases
That's exactly what a join does... join two or more tables as one, but you usually define a common field to join the tables by... If both tables have the same definition and you want to know how to display the data of one of them after the other you need to do a: select * from db1.table1 union select * from db2.table2; Andrés Villanueva Mahmoud Badreddine wrote: Hi, I would like to run a query on two tables that reside in two distinct databases: select * from db1.table1 join db2.table2; This lists both tables adjacent to one another rather than in a sequential fashion as one would get if both tables came from the same database. Is there a way to accomplish this task. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql mem usage
top... last pid: 56803; load averages: 0.29, 0.31, 0.14 up 5+11:10:10 20:09:05 174 processes: 1 running, 169 sleeping, 4 zombie CPU states: 0.0% user, 2.3% nice, 1.2% system, 0.0% interrupt, 96.5% idle Mem: 422M Active, 237M Inact, 217M Wired, 43M Cache, 111M Buf, 73M Free Swap: 512M Total, 297M Used, 215M Free, 58% Inuse, 16K In PID USERNAME PRI NICE SIZERES STATETIME WCPUCPU COMMAND 55651 mysql 8 12 138M 33524K nanslp 0:21 0.00% 0.00% mysqld 55649 mysql 20 14 138M 33524K pause0:21 0.00% 0.00% mysqld 55866 mysql 4 14 138M 33524K sbwait 0:12 0.00% 0.00% mysqld 55912 mysql 8 12 138M 33524K nanslp 0:04 0.00% 0.00% mysqld 55697 mysql 4 14 138M 33524K sbwait 0:03 0.00% 0.00% mysqld 55612 mysql110 14 138M 33524K select 0:01 0.00% 0.00% mysqld 55613 mysql110 14 138M 33524K select 0:00 0.00% 0.00% mysqld 55652 mysql 8 12 138M 33524K nanslp 0:00 0.00% 0.00% mysqld 55606 mysql107 11 138M 33524K select 0:00 0.00% 0.00% mysqld 55607 mysql107 11 138M 33524K select 0:00 0.00% 0.00% mysqld 56390 mysql 20 14 138M 33524K pause0:00 0.00% 0.00% mysqld 55653 mysql 4 14 138M 33524K sbwait 0:00 0.00% 0.00% mysqld 55614 mysql 20 14 138M 33524K pause0:00 0.00% 0.00% mysqld 55610 mysql 20 14 138M 33524K pause0:00 0.00% 0.00% mysqld 55615 mysql 20 11 138M 33524K pause0:00 0.00% 0.00% mysqld 55609 mysql 20 14 138M 33524K pause0:00 0.00% 0.00% mysqld 55611 mysql 20 14 138M 33524K pause0:00 0.00% 0.00% mysqld 55608 mysql 20 14 138M 33524K pause0:00 0.00% 0.00% mysqld 55616 mysql 20 12 138M 33524K pause0:00 0.00% 0.00% mysqld 76746 squid 960 90756K 38016K select 4:16 0.00% 0.00% squid 56725 pmx4 960 36524K 34908K select 0:01 0.00% 0.00% perl 56724 pmx4 960 36172K 34560K select 0:00 0.00% 0.00% perl FreeBSD 5.4-STABLE, linuxthreads -- Chris. I love deadlines. I especially love the whooshing sound they make as they fly by... - Douglas Adams, 'Hitchhiker's Guide to the Galaxy' - Original Message - From: Dan Nelson [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 03, 2005 7:34 PM Subject: Re: mysql mem usage In the last episode (May 03), Chris Knipe said: I have a P4 system with 1GB Ram and 512MB Swap (a little low I know). I'm running a multi threaded MySQL installation on it. With a bit of shock, I realised today that I ran completely out of swap space!!! Each MySQL thread is consuming about 160MB of ram, and I had close to 40 threads running. All my databases currently are approximately 1.5GB as far as file size goes, so it's not *that* much really. The biggest table is MyISAM (for delayed inserts), holding approximately 2million records currently. Threads don't have their own memory. The process as a whole does. I can only assume you're running a Linux 2.4 kernel that displays threads with separate pids in top, which causes all sorts of confusion. They all share the same address space though so it's only using 160MB. If you're swapping, chances are it's something else at fault. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql mem usage
In the last episode (May 03), Chris Knipe said: top... FreeBSD 5.4-STABLE, linuxthreads last pid: 56803; load averages: 0.29, 0.31, 0.14 up 5+11:10:10 20:09:05 174 processes: 1 running, 169 sleeping, 4 zombie CPU states: 0.0% user, 2.3% nice, 1.2% system, 0.0% interrupt, 96.5% idle Mem: 422M Active, 237M Inact, 217M Wired, 43M Cache, 111M Buf, 73M Free Swap: 512M Total, 297M Used, 215M Free, 58% Inuse, 16K In PID USERNAME PRI NICE SIZERES STATETIME WCPUCPU COMMAND 55651 mysql 8 12 138M 33524K nanslp 0:21 0.00% 0.00% mysqld 55649 mysql 20 14 138M 33524K pause0:21 0.00% 0.00% mysqld 55866 mysql 4 14 138M 33524K sbwait 0:12 0.00% 0.00% mysqld Ya, since you're using linuxthreads, these are all really one process with one single 138MB address space; note that SIZE and RES are identical all the way down. 76746 squid 960 90756K 38016K select 4:16 0.00% 0.00% squid 56725 pmx4 960 36524K 34908K select 0:01 0.00% 0.00% perl 56724 pmx4 960 36172K 34560K select 0:00 0.00% 0.00% perl Try running ps axlm, which will show all the processes sorted by memory usage. If there are more of those perl scripts running, they may be a contributing factor. Apache with script modules (perl/php etc) can also suck up lots of memory if you get lots of hits at once. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql mem usage
PID USERNAME PRI NICE SIZERES STATETIME WCPUCPU COMMAND 55651 mysql 8 12 138M 33524K nanslp 0:21 0.00% 0.00% mysqld 55649 mysql 20 14 138M 33524K pause0:21 0.00% 0.00% mysqld 55866 mysql 4 14 138M 33524K sbwait 0:12 0.00% 0.00% mysqld Ya, since you're using linuxthreads, these are all really one process with one single 138MB address space; note that SIZE and RES are identical all the way down. 76746 squid 960 90756K 38016K select 4:16 0.00% 0.00% squid 56725 pmx4 960 36524K 34908K select 0:01 0.00% 0.00% perl 56724 pmx4 960 36172K 34560K select 0:00 0.00% 0.00% perl Try running ps axlm, which will show all the processes sorted by memory usage. If there are more of those perl scripts running, they may be a contributing factor. Apache with script modules (perl/php etc) can also suck up lots of memory if you get lots of hits at once. You learn something new every day. It seems the culprit actually was our anti-spam engines + apache/mod_fastcgi. We're down to 100MB Swap used and 100MB free MEM - big difference. We'll need to get a upgrade though, we must be able to carry the load... Thanks for the help -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql-mysqld connection
If you really really want it it's in the internals doc from the mysqldoc bk tree. -Eric Reggie Burnett wrote: Just to echo the thoughts from Jeremiah, we have several freely available open source connectors that work *VERY HARD* tracking the protocol so it's hard to understand a situation where you would need to write your own implementation. -Reggie -Original Message- From: Nikola Skoric [mailto:[EMAIL PROTECTED] Sent: Monday, May 02, 2005 10:30 AM To: Jeremiah Gowdy; mysql@lists.mysql.com Subject: Re: mysql-mysqld connection Dana Sun, 1 May 2005 11:11:40 -0700, Jeremiah Gowdy rece: Where could I find speciffication of how MySQL client and server communicate? /... Look at the source code of libmysql? So, document regarding this protocol doesn't exist? Hm, and, where could I find the source code of libmysql? I googled, but failed to locate it. I'd be thrilled if you could help... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: Re: Collation problems or messed joins?]
Anyone?? Original Message The tables are now entirely in utf8, and that is also the instance's default. I'm using the .net dll to connect and the connection string has charset = utf8. I'm testing everything with the query browser, and i get the exact same behaviour. Again, if anyone wants a script to recreate the tables with some data, I can send it to you instantly. Thanks Andrés Villanueva Kevin Cowley wrote: Have you check that the collation for the text columns match the collation for the table and that you've set UTF8 for the query (set char set utf8). Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] -Original Message- From: Andrés Villanueva [mailto:[EMAIL PROTECTED] Sent: 03 May 2005 15:33 To: mysql@lists.mysql.com Subject: Collation problems or messed joins? Hi everyone! I'm havng the weirdest problem with mysql. I had v1.1.07 and everything worked like a charm. But it turns out that we had the need to move everything to UTF8 (until then, only the columns in russian where set to utf8, and the rest was latin1). Once we moved everything to utf8, this (the query is at the end of the email) didn't work anymore, apparently because of a collation problem with the literals. After doing some research i found that this wasn't a problem anymore in 4.1.11, so i decided to upgrade... After upgrading the query doesn't throw an error, but it doesn't return all the data it should... The funny thing is that i thought that the problem was in the 'case's that i use in the query, so i remove them from the query and the query runs fine. But later, i tried something else, and insetead of removing the cases y switched the inners for left joins, and the query worked fine, just like before, and it turns out it doesn't even matter wether I switch both joins or just one of them (either one or the other). If anybody can test this, and needs more data, or needs a script with the tables and data, I'll be glad to send it. Thanks!! Andrés Villanueva This is the problematic query: SELECT paper_id, Number, Revision, b.type, c.type_en as type2, a.name_en as name , case name_en when '' then 'N' else 'Y' end as name_en , case name_fr when '' then 'N' else 'Y' end as name_fr , case name_ru when '' then 'N' else 'Y' end as name_ru , case name_sp when '' then 'N' else 'Y' end as name_sp FROM `tpatcm28_paper` a inner join `tpgen_fmt_type` b using (fmt_type_id) inner join `tpatcm_pap_type` c on (a.pap_type_id = c.pap_type_id) order by Number, Revision -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: perl/mysql issue...
At 10:52 AM 5/3/05 -0700, [EMAIL PROTECTED] wrote: 'DBD::mysql::db selectrow_hashref failed: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) at /var/www/html/cgi-bin/DBI/Role.pm $ mysql -uroot -p mysql CREATE DATABASE livejournal; mysql GRANT ALL PRIVILEGES ON livejournal.* TO lj@'localhost' IDENTIFIED BY 'ljpass'; Yeah, just add [EMAIL PROTECTED] to ur Grants and it should work. MySQL see's that ip and not localhost when Perl connects to it. -- REMEMBER THE WORLD TRADE CENTER ---= WTC 911 =-- ...ne cede males 0100 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: perl/mysql issue...
bruce wrote:
The problem is in your code, not your db.
hi...
i have the following issue.. i've researched it from google.. but i still
can't quite figure it out...
i'm using a test app with DBI-connect() and i'm getting the following
response... (print/debug statements...)
--
'bd dsn =
DBI:mysql:livejournal;host=192.168.1.55;port=3306;mysql_connect_timeout=2
'bd user = lj
'bd passwd = ljpass
'bd fdsn1 = DBI:mysql:livejournal;host=192.168.1.55;port=3306|lj|ljpass
'bd loops dbh = 0
'bd loops user = lj
'bd loops pass = ljpass
'bd loops dbh111 = DBI::db=HASH(0x9bf20c8)
'DBD::mysql::db selectrow_hashref failed: Access denied for user:
'[EMAIL PROTECTED]' (Using password: NO) at /var/www/html/cgi-bin/DBI/Role.pm
line 347.
MySQL just told you that you connected as [EMAIL PROTECTED] with *no
password*. Presumably, this means $pass has no value on the following line.
$dbh = DBI-connect($dsn, $user, $pass, {
PrintError = 1,
AutoCommit = 1,});
-
From your debug output, perhaps you set $passwd but used $pass?
i did the following to configure the mysql database/table
--
$ mysql -uroot -p
mysql CREATE DATABASE livejournal;
mysql GRANT ALL PRIVILEGES ON livejournal.* TO
lj@'localhost' IDENTIFIED BY 'ljpass';
---
[EMAIL PROTECTED] can connect and use db livejournal.
i can access the mysql db/livejournal from the mysql client/command line.
mysql -ulj -host192.168.1.55 -p
but here you connect as [EMAIL PROTECTED] localhost is special to mysql.
That is, localhost and 192.168.1.55 are not the same thing. See the
manual for details
http://dev.mysql.com/doc/mysql/en/connection-access.html.
i'm pretty sure i've somehow made an error/left something out when
configuring the mysql db for access from perl apps. i'm running the perl app
from the same machine...
Then use localhost, not 192.168.1.55, in your connection string.
can anyone give me pointers/shed light on what i might need to change. my
gut tells me that there's some mysql table/attribute that needs to be
changed, but i'm not that familiar with mysql...
thanks
bruce
[EMAIL PROTECTED]
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Massive selects, improvements sought.
Since I'm learning about Left joins and am trying to figure out a way to quickly gather info from Database all at once, here is the SQL statement. Please give me feedback how I should improve it for better performance. There are around 10 users that will hit database like this along with other left joins ever minute. Thanks in advance: SELECT QA.ID, Brands.Brand, QA.KeyDate, A2.LastName + ', ' + A2.FirstName as Reviewer, A1.LastName + ', ' + A1.FirstName as Operator, Batch.[order], Errortypes.[Description], ErrorTypes.points, Batch.Comments, (SELECT sum(Errortypes.points) FROM ErrorTypes,QAErrors,Batch,QA WHERE Errortypes.Id=QAErrors.ErrorTypeID AND QAErrors.ID=Batch.QEID AND Batch.QAID=QA.ID AND QA.Batch='464005807') as points, (SELECT count(*)*100 FROM Batch,QA WHERE Batch.QAID=QA.ID AND QA.Batch='464005807') as total FROM Brands,Associates A1, Associates A2,QA Left Join Batch ON Batch.QAID=QA.ID Left Join QAErrors ON QAErrors.ID=Batch.QEID Left Join Errortypes On ErrorTypes.id=QAErrors.ErrorTypeID WHERE QA.Batch='464005807' AND A1.ID=QA.OperatorID AND A2.ID=QA.QAID AND Brands.ID=QA.BrandID; RESULT (delimited by ,): 100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis, Teresa,244770323,Error in Name,8.0,last name mispelled,24.0,600 100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis, Teresa,244770323,Error in Address,8.0,blvd mispelled,24.0,600 100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis, Teresa,244770324,Error in Address,8.0,BLVD MISSPELLED,24.0,600 100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis, Teresa,244770325,NULL,NULL,NULL,24.0,600 100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis, Teresa,244770322,NULL,NULL,NULL,24.0,600 100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis, Teresa,244770321,NULL,NULL,NULL,24.0,600 -- Power to people, Linux is here. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Massive selects, improvements sought.
My comments imbedded Scott Hamm [EMAIL PROTECTED] wrote on 05/03/2005 03:16:31 PM: Since I'm learning about Left joins and am trying to figure out a way to quickly gather info from Database all at once, here is the SQL statement. Please give me feedback how I should improve it for better performance. There are around 10 users that will hit database like this along with other left joins ever minute. Thanks in advance: First, THANK YOU for formatting your query. SELECT QA.ID, Brands.Brand, QA.KeyDate, A2.LastName + ', ' + A2.FirstName as Reviewer, A1.LastName + ', ' + A1.FirstName as Operator, Batch.[order], Errortypes.[Description], ErrorTypes.points, Batch.Comments, These next two statements have the potential to be evaluated once per row. However, you are not running a subquery that uses any values from the outer query so you are in essence computing constants for these next two columns. (SELECT sum(Errortypes.points) FROM ErrorTypes,QAErrors,Batch,QA WHERE Errortypes.Id=QAErrors.ErrorTypeID AND QAErrors.ID=Batch.QEID AND Batch.QAID=QA.ID AND QA.Batch='464005807') as points, (SELECT count(*)*100 FROM Batch,QA WHERE Batch.QAID=QA.ID AND QA.Batch='464005807') as total You say you are learning about LEFT joins (which, btw, look just like INNER joins) but you aren't using INNER JOINS? You are using crappy Oracle-style comma-separated table lists (ugggh). (It's not wrong to do this, it's just my personal pet peeve) FROM Brands,Associates A1, Associates A2,QA Left Join Batch ON Batch.QAID=QA.ID Left Join QAErrors ON QAErrors.ID=Batch.QEID Left Join Errortypes On ErrorTypes.id=QAErrors.ErrorTypeID WHERE QA.Batch='464005807' AND A1.ID=QA.OperatorID AND A2.ID=QA.QAID AND Brands.ID=QA.BrandID; RESULT (delimited by ,): 100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis, Teresa,244770323,Error in Name,8.0,last name mispelled,24.0,600 100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis, Teresa,244770323,Error in Address,8.0,blvd mispelled,24.0,600 100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis, Teresa,244770324,Error in Address,8.0,BLVD MISSPELLED,24.0,600 100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis, Teresa,244770325,NULL,NULL,NULL,24.0,600 100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis, Teresa,244770322,NULL,NULL,NULL,24.0,600 100528,Imaged G-5,2005-05-03 00:00:00,Cahall, Margaret,Custis, Teresa,244770321,NULL,NULL,NULL,24.0,600 snip See how the last two columns are always the same values. If that is really what you wanted to compute for those columns, there is an easier way to do it. Use variables to hold the results of computin those values then just add them to the SELECT clause of your final query. That way you are certain that the engine only computes them values once. I would modify your query to look like this (assuming that the Batch field on the QA table is really a numeric value and not a string): SELECT @points := sum(Errortypes.points) FROM ErrorTypes INNER JOIN QAErrors ON Errortypes.Id=QAErrors.ErrorTypeID INNER JOIN Batch ON QAErrors.ID=Batch.QEID INNER JOIN QA ON Batch.QAID=QA.ID WHERE QA.Batch=464005807; SELECT @total := count(1)*100 FROM Batch INNER JOIN QA ON Batch.QAID=QA.ID WHERE QA.Batch=464005807; SELECT QA.`ID`, Brands.`Brand`, QA.`KeyDate`, CONCAT(A2.`LastName`,', ',A2.`FirstName`) as Reviewer, CONCAT(A1.`LastName`,', ',A1.`FirstName`) as Operator, Batch.`order`, Errortypes.`Description`, ErrorTypes.`points`, Batch.`Comments`, @points as points, @total as total FROM Brands INNER JOIN QA ON Brands.ID=QA.BrandID INNER JOIN Associates A1 ON A1.ID=QA.OperatorID INNER JOIN Associates A2 ON A2.ID=QA.QAID Left Join Batch ON Batch.QAID=QA.ID Left Join QAErrors ON QAErrors.ID=Batch.QEID Left Join Errortypes ON ErrorTypes.id=QAErrors.ErrorTypeID WHERE QA.Batch=464005807; (BTW - I think the original query came from M$ SQL Server. Did it?) Without knowing more about what it is you are trying to get with this query, it's hard to make a better guess on how to make it work. The main issues I saw were: a) abusing subqueries when variables were more appropriate. b) using [] instead of `` to delimit reserved field names (not supported by MySQL). c) using + as a string concatenator (not supported by MySQL). d) using the comma-separated form of implicit inner joins and not fully declared, explicit INNER JOIN clauses. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: perl/mysql issue...' (Using password: NO)'
ummm
i made a mistake in what i posted... a typing error... it should have been
i did the following to configure the mysql database/table
--
$ mysql -uroot -p
mysql CREATE DATABASE livejournal;
mysql GRANT ALL PRIVILEGES ON livejournal.* TO
lj@'192.168.1.55' IDENTIFIED BY 'ljpass';
---
which should/does allow me to access the mysql/db from the mysql command
line client...
my issue that i've tracked down to mysql, is that once i'm inside/running
mysql, i can do a 'use livejournal' which selects the livejournal database.
when i do the following 'mysql show slave status' i get the error '(Using
password: NO)'. i can access the 'mysql' databases/tables ('use mysql') and
then run 'show slave status' with no problem... (due to my not having setup
any root passwd as of yet...
so, how/what do i need to do to resolve this issue!!
i would imagine that this is a function of access/privs but i'm not sure
what has to be set.
thoughts/comments/ideas/etc...
thanks
bruce
[EMAIL PROTECTED]
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of
bruce
Sent: Tuesday, May 03, 2005 10:53 AM
To: perl-win32-users@listserv.ActiveState.com; mysql@lists.mysql.com;
'Nikolas Coukouma'
Subject: perl/mysql issue...
hi...
i have the following issue.. i've researched it from google.. but i still
can't quite figure it out...
i'm using a test app with DBI-connect() and i'm getting the following
response... (print/debug statements...)
--
'bd dsn =
DBI:mysql:livejournal;host=192.168.1.55;port=3306;mysql_connect_timeout=2
'bd user = lj
'bd passwd = ljpass
'bd fdsn1 = DBI:mysql:livejournal;host=192.168.1.55;port=3306|lj|ljpass
'bd loops dbh = 0
'bd loops user = lj
'bd loops pass = ljpass
'bd loops dbh111 = DBI::db=HASH(0x9bf20c8)
'DBD::mysql::db selectrow_hashref failed: Access denied for user:
'[EMAIL PROTECTED]' (Using password: NO) at /var/www/html/cgi-bin/DBI/Role.pm
line 347.
$dbh = DBI-connect($dsn, $user, $pass, {
PrintError = 1,
AutoCommit = 1,});
-
i did the following to configure the mysql database/table
--
$ mysql -uroot -p
mysql CREATE DATABASE livejournal;
mysql GRANT ALL PRIVILEGES ON livejournal.* TO
lj@'localhost' IDENTIFIED BY 'ljpass';
---
i can access the mysql db/livejournal from the mysql client/command line.
mysql -ulj -host192.168.1.55 -p
i'm pretty sure i've somehow made an error/left something out when
configuring the mysql db for access from perl apps. i'm running the perl app
from the same machine...
can anyone give me pointers/shed light on what i might need to change. my
gut tells me that there's some mysql table/attribute that needs to be
changed, but i'm not that familiar with mysql...
thanks
bruce
[EMAIL PROTECTED]
___
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to check if keys disabled?
Hi, How can I find out if keys are enabled/disabled for a given table? Suppose I do: create table t1 (x int primary key); create table t2 (x int primary key); alter table t1 disable keys; How can I now find out that t1 has keys disabled, and t2 enabled? thanks, Jacek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to check if keys disabled?
[snip] How can I find out if keys are enabled/disabled for a given table? Suppose I do: create table t1 (x int primary key); create table t2 (x int primary key); alter table t1 disable keys; How can I now find out that t1 has keys disabled, and t2 enabled? [/snip] DESCRIBE t1 or DESCRIBE t2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to check if keys disabled?
Jay Are you sure? DESCRIBE tells me the table has an index, but not whether the index is enabled or not: mysql create table t1 (x int primary key); Query OK, 0 rows affected (0.01 sec) mysql create table t2 (x int primary key); Query OK, 0 rows affected (0.01 sec) mysql alter table t1 disable keys; Query OK, 0 rows affected (0.00 sec) mysql describe t1; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | x | int(11) | NO | PRI | | | +---+-+--+-+-+---+ 1 row in set (0.00 sec) mysql describe t2; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | x | int(11) | NO | PRI | | | +---+-+--+-+-+---+ 1 row in set (0.00 sec) Am I missing something? thanks, Jacek Jay Blanchard wrote: [snip] How can I find out if keys are enabled/disabled for a given table? Suppose I do: create table t1 (x int primary key); create table t2 (x int primary key); alter table t1 disable keys; How can I now find out that t1 has keys disabled, and t2 enabled? [/snip] DESCRIBE t1 or DESCRIBE t2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to check if keys disabled?
Try show index from t1; Show index from t2; -Original Message- From: Jacek Becla [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 03, 2005 4:00 PM To: Jay Blanchard Cc: mysql@lists.mysql.com Subject: Re: how to check if keys disabled? Jay Are you sure? DESCRIBE tells me the table has an index, but not whether the index is enabled or not: mysql create table t1 (x int primary key); Query OK, 0 rows affected (0.01 sec) mysql create table t2 (x int primary key); Query OK, 0 rows affected (0.01 sec) mysql alter table t1 disable keys; Query OK, 0 rows affected (0.00 sec) mysql describe t1; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | x | int(11) | NO | PRI | | | +---+-+--+-+-+---+ 1 row in set (0.00 sec) mysql describe t2; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | x | int(11) | NO | PRI | | | +---+-+--+-+-+---+ 1 row in set (0.00 sec) Am I missing something? thanks, Jacek Jay Blanchard wrote: [snip] How can I find out if keys are enabled/disabled for a given table? Suppose I do: create table t1 (x int primary key); create table t2 (x int primary key); alter table t1 disable keys; How can I now find out that t1 has keys disabled, and t2 enabled? [/snip] DESCRIBE t1 or DESCRIBE t2 -- 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: how to check if keys disabled?
Victor Pendleton wrote: Try show index from t1; Show index from t2; Hi Victor, Nope, this does not help. t1 has key disabled, t1 enabled, and the result is the same: mysql show index from t1\G *** 1. row *** Table: t1 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: x Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 1 row in set (0.00 sec) mysql show index from t2\G *** 1. row *** Table: t2 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: x Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 1 row in set (0.00 sec) cheers, Jacek -Original Message- From: Jacek Becla [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 03, 2005 4:00 PM To: Jay Blanchard Cc: mysql@lists.mysql.com Subject: Re: how to check if keys disabled? Jay Are you sure? DESCRIBE tells me the table has an index, but not whether the index is enabled or not: mysql create table t1 (x int primary key); Query OK, 0 rows affected (0.01 sec) mysql create table t2 (x int primary key); Query OK, 0 rows affected (0.01 sec) mysql alter table t1 disable keys; Query OK, 0 rows affected (0.00 sec) mysql describe t1; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | x | int(11) | NO | PRI | | | +---+-+--+-+-+---+ 1 row in set (0.00 sec) mysql describe t2; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | x | int(11) | NO | PRI | | | +---+-+--+-+-+---+ 1 row in set (0.00 sec) Am I missing something? thanks, Jacek Jay Blanchard wrote: [snip] How can I find out if keys are enabled/disabled for a given table? Suppose I do: create table t1 (x int primary key); create table t2 (x int primary key); alter table t1 disable keys; How can I now find out that t1 has keys disabled, and t2 enabled? [/snip] DESCRIBE t1 or DESCRIBE t2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
synopsis of the problem (one line)
Description: How-To-Repeat: Fix: Submitter-Id: submitter ID Originator:martin.mpsoft.dk Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: Severity: Priority: Category: mysql Class: Release: mysql-4.1.11-Debian_2 (Source distribution) C compiler:gcc (GCC) 3.3.5 (Debian 1:3.3.5-12) C++ compiler: g++ (GCC) 3.3.5 (Debian 1:3.3.5-12) Environment: System: Linux mail2.mpsoft.dk 2.4.18-bf2.4 #1 Son Apr 14 09:53:28 CEST 2002 i686 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-linux/3.3.5/specs Configured with: ../src/configure -v --enable-languages=c,c++,java,f77,pascal,objc,ada,treelang --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-gxx-include-dir=/usr/include/c++/3.3 --enable-shared --enable-__cxa_atexit --with-system-zlib --enable-nls --without-included-gettext --enable-clocale=gnu --enable-debug --enable-java-gc=boehm --enable-java-awt=xlib --enable-objc-gc i486-linux Thread model: posix gcc version 3.3.5 (Debian 1:3.3.5-12) Compilation info: CC='gcc' CFLAGS='-DBIG_JOINS=1 -O3' CXX='g++' CXXFLAGS='-DBIG_JOINS=1 -felide-constructors -fno-rtti -O3' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Apr 25 00:53 /lib/libc.so.6 - libc-2.3.2.so -rw-r--r-- 1 root root 1244688 Apr 16 14:10 /lib/libc-2.3.2.so -rw-r--r-- 1 root root 2641390 Apr 16 14:10 /usr/lib/libc.a -rw-r--r-- 1 root root 204 Apr 16 13:45 /usr/lib/libc.so Configure command: ./configure '--build=i386-linux' '--host=i386-linux' '--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--sysconfdir=/etc/mysql' '--localstatedir=/var/lib/mysql' '--includedir=/usr/include' '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--with-server-suffix=-Debian_2' '--enable-shared' '--enable-static' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' '--with-raid' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql' '--with-libwrap' '--with-client-ldflags=-lstdc++' '--with-vio' '--without-openssl' '--without-docs' '--without-bench' '--without-readline' '--with-extra-charsets=all' '--with-berkeley-db' '--with-innodb' '--with-isam' '--with-archive-storage-engine' '--with-csv-storage-engine' '--without-embedded-server' '--with-ndbcluster' '--with-ndb-shm' '--without-ndb-sci' '--without-ndb-test' '--with-embedded-server' '--with-embedded-privilege-control' '--with! -ndb-docs' 'CC=gcc' 'CFLAGS=-DBIG_JOINS=1 -O3' 'CXXFLAGS=-DBIG_JOINS=1 -felide-constructors -fno-rtti -O3' 'CXX=g++' 'build_alias=i386-linux' 'host_alias=i386-linux' This version is very slow, to all queries like: SHOW STATUS, SHOW * from tbl_name etc. What is wrong? Take Care. Martin P. Larsen mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie: how to sort a database without extracting the data
Hi I am really new to mysql. I need my database to be sorted according to one field. But since the database with 1200 records is huge, I don't want to do it using SELECT. What I need is just the stored database being sorted on hard-disk. Is there any way doing this like creating a new database and importing the old one but being sorted? many thanks for your kind help cheers christoph (p.s. I need this for later chunk-wise data-fetch with one chunk being homogenous in regard to one (the sorted) field) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: newbie: how to sort a database without extracting the data
Hi Christoph, I would suggest you read up about indexes and how they would assist you in your requirements. Without knowing the exact structure of your tables, it is difficult to judge but it sounds like an index (or indexes), correctly structured, would provide what you need. A good read would be http://dev.mysql.com/doc/mysql/en/mysql-optimization.html Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Christoph Lehmann [mailto:[EMAIL PROTECTED] Sent: Wednesday, 4 May 2005 8:57 AM To: mysql@lists.mysql.com Subject: newbie: how to sort a database without extracting the data Hi I am really new to mysql. I need my database to be sorted according to one field. But since the database with 1200 records is huge, I don't want to do it using SELECT. What I need is just the stored database being sorted on hard-disk. Is there any way doing this like creating a new database and importing the old one but being sorted? many thanks for your kind help cheers christoph (p.s. I need this for later chunk-wise data-fetch with one chunk being homogenous in regard to one (the sorted) field) -- 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: newbie: how to sort a database without extracting the data
thanks Damian but I don't understand this: My field according to which I want the database to be sorted IS an unique number. eg I have 1 ab 33 1 cd 21 1 ac 32 2 aa 22 2 cd 25 3 kw 03 3 ie 02 2 ei 05 2 wk 00 I need it in the form: 1 ab 33 1 cd 21 1 ac 32 2 aa 22 2 cd 25 2 ei 05 2 wk 00 3 kw 03 3 ie 02 what do you mean by adding an index thanks for your help cheers christoph Damian McMenamin wrote: add an index on the field. would be quickerthan any exporting importing. --- Christoph Lehmann [EMAIL PROTECTED] wrote: Hi I am really new to mysql. I need my database to be sorted according to one field. But since the database with 1200 records is huge, I don't want to do it using SELECT. What I need is just the stored database being sorted on hard-disk. Is there any way doing this like creating a new database and importing the old one but being sorted? many thanks for your kind help cheers christoph (p.s. I need this for later chunk-wise data-fetch with one chunk being homogenous in regard to one (the sorted) field) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Yours Sincerely, Damian McMenamin Analyst Programmer Melbourne Australia Cell: (61)040-0064107 Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tables lost in new location of database
I use mysql 4.0.23-nt on win XP. In order to shift some of my databases I followed the following steps as per a recent post: 1. I moved the folders containing the database files that I wanted to move to a new folder (from data to data2) after stopping mysql of course. 2. In the data folder I created a file named with the database (same as the folder names that I moved) prefixed with .sym. 3. Inside each of these .sym files I enterred (typed) in the exact path of the new folders and nothing else. 4. Restarted mysql Now when I give show databases; at the mysql prompt, all my database names are displayed including the ones I moved. Here is the issue: When I use one of the databases that I had moved and give: show tables I am getting an empty set. I know the data is there because the size of the .myd files is 50 MB - well none of the .myi or .frm file is missing or empty! Also I noticed that the .sym files have numbers auto-enterred before the file path I had specified - I guess it is in running mode. So how do I see my tables on the moved databases and query on them?? Thanks in advance, Anoop -- Thanks and best regards, Anoop
Slow queries, why?
So here's my situation: we have a database that has a table of about 5 million rows. To put a new row into the table, I do an INSERT ... SELECT, pulling data from one row in the table to seed the data for the new row. When there are no active connections to the DB other than the one making the INSERT, it runs like a charm. But during normal daytime operation, when we run around 50 connections (most sleeping at any one time), it takes up to two minutes to do, and ends up locking any other inserts or updates against that table for the entire time. I'll get into more specifics if they're required, but I wanted to ask in general if MySQL has tools to diagnose this, or if anyone has had general situations like this. In SQL Server (which is where I have most of my experience) I could use the trace tool and the Query Analyzer to tell what the execution plan for the query was and thus what's stalling it (an index gone bad, a weird locking situation, etc). We're running MySQL 4.11 on a machine with 2GB memory, the table is InnoDB with a compound primary key, and additional indexes on all rows with searchable options in the API. Any generic advice or admin tools would be great. -- Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow queries, why?
on 5/3/05 7:25 PM, Joseph Cochran at [EMAIL PROTECTED] wrote: So here's my situation: we have a database that has a table of about 5 million rows. To put a new row into the table, I do an INSERT ... SELECT, pulling data from one row in the table to seed the data for the new row. When there are no active connections to the DB other than the one making the INSERT, it runs like a charm. But during normal daytime operation, when we run around 50 connections (most sleeping at any one time), it takes up to two minutes to do, and ends up locking any other inserts or updates against that table for the entire time. Since you are pulling data from only one row, you may be a prime candidate for http://dev.mysql.com/doc/mysql/en/select-into-statement.html which is as far as I can tell, more efficient as it stores the results as variables. I am really pretty new to all this, so just trying to help where I can, but you may find that selecting and inserting in the application logic level will perform better for you as well. I am not sure what goes on behind the scenes in a insert select, from what I have read, inserting a large amount of rows is going to get you faster results that selecting them by hand, but for one or few rows, it is not as optimum. I'll get into more specifics if they're required, but I wanted to ask in general if MySQL has tools to diagnose this, or if anyone has had general situations like this. In SQL Server (which is where I have most of my experience) I could use the trace tool and the Query Analyzer to tell what the execution plan for the query was and thus what's stalling it (an index gone bad, a weird locking situation, etc). Take a look at: http://dev.mysql.com/doc/mysql/en/slow-query-log.html Though I have not had to use it yet, it may get you where you need to be. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
