Performance problem

Hallo, Ich benutze mysql seit 2 Jahren. Inzwischen haben sich 50MB Nutzdaten angesammeln und hab ein Problem mit der Performance während der Abfrage meiner "Haupttabelle". Die Tabelle hat ca 200.000 Datensätze und ich mache im verhältnis zu anderen Abfragen doch ein eher simplen select. Ich selekt

Re: Problems setting up on Mac OS X

on 9/28/05 9:49 PM, Jim C. at [EMAIL PROTECTED] wrote: > I'm not fond of the idea, but perhaps if I can't get it working, I'll > try compiling it from source. There is no reason to do this unless you really want to, I assure you, mysql can run on OS X. You may want to not use the startup pane thi

Re: Problems setting up on Mac OS X

Bruce Dembecki wrote: > On OSX server there is a copy of MySQL already installed and just > installing the MySQL binary doesn't necessarily bypass I'm afraid this isn't an OS X server nor is a server grade installation of MySQL what I am after, right now. I'm a developer and what I am after is

Re: referential integrity lock up

Tripp, thanks for your prompt response, however I would like to keep the FOREIGN_KEY_CHECKS enabled so I dont shoot myself on the foot. unless, as it looks right now, it turns impossible to delete stuff from the table. thanks, esv. --- Tripp Bishop <[EMAIL PROTECTED]> wrote: > Enrique, > > Tr

referential integrity lock up

hi, I've been assigned the task to come up with a script to clean up a database that has a LOT of foreign key constraints, where tables make reference to each other and back, some of the tables are empty or have a record or so and make the process a breze but I wonder what would happen the day the

Re: Problems setting up on Mac OS X

On OSX server there is a copy of MySQL already installed and just installing the MySQL binary doesn't necessarily bypass it. Try logging on as root with no password, you may still be booting from Apple's data directory rather than MySQL's... starting mysqld using "/ usr/local/mysql/bin/mysql

Re: MyISAM to InnoDB

On Sep 28, 2005, at 5:21 PM, Devananda wrote: Jeff wrote: Lots of stuff goes in here... So without going into specifics here... your sort_buffer and read_buffer become pretty much unimportant if you move everything to InnoDB... keeping in mind the earlier advice to leave the mysql databa

Calculations on user variables

Hi there I am experiencing a wierd bug in mysql 4.1 which is working in Mysql5 beta where i am trying to run calculations on user variables. I use these to pass values to sub queries. FORMAT((IF(c.bandwidth_limit=0, ((c.monthly_price/@count)*f.percentage_paid)[EMAIL PROTECTED], ((@[EMAIL PROTE

Re: MyISAM to InnoDB

Jeff wrote: Ugh... mysqladmin -uroot -ptelaurus processlist | grep -c "Sleep" And it returned 200 sleeping connections, all persistant connections from our app servers and 4 threads_running Also a show status gave me a max_used_connections of 236. If that's the case then I can probably

RAID stripe size recommendations

I am planning on running some tests on a SATA server with a 3ware 9000 series RAID card to see if there's a stripe size that performs better than the current setting (which I don't recall at the moment, probably whatever the max is). This will be RAID10 and our databases are either in the 500Mb or

Re: daemon crash when shutting down large databases

I forgot to include the output of show variables and show status in the last message :( mysql> show variables\G *** 1. row *** Variable_name: back_log Value: 50 *** 2. row *** Variable_name:

Re: daemon crash when shutting down large databases

Gleb Paharenko wrote: Hello. > = 77591546 K Really - something is wrong with your memory settings - MySQL is using about 77G of memory (or you have such a cool server :)! Please send the output of 'SHOW VARIABLES' statement, 'SHOW STATUS' statement and your configuration file. Include the a

Re: Problems setting up on Mac OS X

on 9/28/05 11:52 AM, Jim C. at [EMAIL PROTECTED] wrote: > I had the same problem with both of these: > > mysql-standard-4.1.14-apple-darwin7.9.0-powerpc.dmg > and > mysql-standard-4.0.26-apple-darwin7.9.0-powerpc.dmg > > They seem to install OK, but when I set the root password I can't get in >

are concurent queries serialized ?

hi, I run a web server on a linux box from which PHP connect to a mySQL 4 server running on another linux box. tis works well but when the number of concurent connection/queries go high, the DB server runs very slowly. while trying to find a fix to this problem, I've been told that whatever t

Re: Problems setting up on Mac OS X

Jim C. wrote: > I had the same problem with both of these: > > mysql-standard-4.1.14-apple-darwin7.9.0-powerpc.dmg > and > mysql-standard-4.0.26-apple-darwin7.9.0-powerpc.dmg > > They seem to install OK, but when I set the root password I can't get in > afterwards as root. > > Clues? > > Jim C.

Problems setting up on Mac OS X

I had the same problem with both of these: mysql-standard-4.1.14-apple-darwin7.9.0-powerpc.dmg and mysql-standard-4.0.26-apple-darwin7.9.0-powerpc.dmg They seem to install OK, but when I set the root password I can't get in afterwards as root. Clues? Jim C. -- MySQL General Mailing List For l

Re: Access, ODBC, Oracle, MySQL

Robert Citek <[EMAIL PROTECTED]> wrote on 09/28/2005 04:01:33 PM: > > On Sep 28, 2005, at 2:37 PM, Sujay Koduri wrote: > > I don't know how far this is going to help you. But this is what we > > are > > following to migrate from Oracle to MySQL. > > If we were migrating, that's probably the st

RE: Access, ODBC, Oracle, MySQL

Robert, I have not worked for a while with Crystal Reports and I do not know if CR allows to go across different database type, and are you willing to spend the money on it, but for plain querying Crystal Reports works well using ODBC connections. CR works where MS-Access fails, because MS-Access

Re: To multi thread or NOT to multi thread?

John McCaskey wrote: I wasnt quite sure about the safety of the error output that occured later on, but it is true for sure in the case of no errors occuring releasing sooner is safe. Good optimization. I have no problems here using it like that, or any errors or core dumps or connectivity pro

Re: Access, ODBC, Oracle, MySQL

On Sep 28, 2005, at 3:09 PM, Sujay Koduri wrote: Once you get some "Here's how you do it." about option 1, please post it in the lists also. Because we are also looking to do a similar one, but didn't find out any good info. I have been looking through here, but have not found anything c

RE: Access, ODBC, Oracle, MySQL

Once you get some "Here's how you do it." about option 1, please post it in the lists also. Because we are also looking to do a similar one, but didn't find out any good info. sujay -Original Message- From: Robert Citek [mailto:[EMAIL PROTECTED] Sent: Thursday, September 29, 2005 1:32

Re: Recover from plain text log

Hi, I was wondering if you have solved your problem. I am quite interested excually. Best regards, Danny Todd Ellison wrote: OK, I am ready to get flamed. I have done so much stuff wrong on this MySQL server that it finally came back to bite me. I've learned my lesson and hopefully someon

Re: Access, ODBC, Oracle, MySQL

On Sep 28, 2005, at 2:37 PM, Sujay Koduri wrote: I don't know how far this is going to help you. But this is what we are following to migrate from Oracle to MySQL. If we were migrating, that's probably the strategy that we would use. But we are not, at least not yet. For now we just want

Re: Table names with periods

Patrick, Very helpful. I will look into the coverting the addresses. Thanks. On 9/28/05, Patrick <[EMAIL PROTECTED]> wrote: > > Historically any form of punctuation, parameter delimiter, or filepath > delimiter in either a database name, field or column name, file or table > name would not be r

Re: Table names with periods

From: "Chance Ellis" <[EMAIL PROTECTED]> Point taken and yes the manual was reviewed. However, I thought in the past that I had seen someone post a method in which table names could be created with special characters. Can't you use a single table instead of more tables? Or each table has differ

RE: Access, ODBC, Oracle, MySQL

I don't know how far this is going to help you. But this is what we are following to migrate from Oracle to MySQL. Have a flag keeporacle as long as you want oracle to be in place. Open connection to mysql If (keeporacle) { open connection to oracle also. } if (keepOracle)

Re: Table names with periods

>Point taken and yes the manual was reviewed. However, I thought in the past >that I had seen someone post a method in which table names could be created >with special characters. Most special characters can be used by using "back ticks": `my_special name - for example with dashes and spaces` Th

Access, ODBC, Oracle, MySQL

Is there a way to query data where one table is in an Oracle database and another is in a MySQL database? We have two projects. One project is entirely in Oracle 9i on an MS Windows 2000 server and the other is in MySQL 4.1 on Linux. It isn't worth our time at this point to migrate enti

Re: Table names with periods

Point taken and yes the manual was reviewed. However, I thought in the past that I had seen someone post a method in which table names could be created with special characters. On 9/28/05, Martijn Tonies <[EMAIL PROTECTED]> wrote: > > > > > > > >Instead of criticizing people's designs without un

Re: Table names with periods

Historically any form of punctuation, parameter delimiter, or filepath delimiter in either a database name, field or column name, file or table name would not be recommended; even if the RDBMS or File Handler allows it. If you are able to stick to alphanumeric characters using underscores char

Re: InnoDB error "unable to create temporary file"

Hi, Thanks for your reply. TMPDIR is not set explicitly, and the first 'fallback' directory in that case is '/tmp', which is accessible and has enough diskspace. At least, when I checked this morning. The error occurred in the night, maybe there were other processes that filled the directory

RE: MyISAM to InnoDB

"Jeff" <[EMAIL PROTECTED]> wrote on 09/28/2005 02:24:48 PM: > > -Original Message- > > From: Devananda [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, September 28, 2005 13:06 > > To: Jeff > > Cc: mysql@lists.mysql.com > > Subject: Re: MyISAM to InnoDB > > > > > > Jeff wrote: > > >> Cut

Re: Where to store comments?

On Wednesday 28 September 2005 20:47, Siegfried Heintze wrote: > Other databases allow one to store comments describing each field in a > table definition. These are stored in the database. In addition, one can > store comments about the table. Examples include MS SQL Server and MS > Access. > > Do

Re: InnoDB error "unable to create temporary file"

Hello. Maybe some directory doesn't exist in the path where MySQL stores temporary files. See: http://dev.mysql.com/doc/mysql/en/temporary-files.html Martijn van den Burg wrote: > Hi list, > > After the most recent reboot (which we used to increase the > ulimit/open_files_limit

Where to store comments?

Other databases allow one to store comments describing each field in a table definition. These are stored in the database. In addition, one can store comments about the table. Examples include MS SQL Server and MS Access. Does MySQL have such a feature? Is there a GUI to support it? What perl or

Re: Table names with periods

> > >Instead of criticizing people's designs without understanding the >requirements why don't you just answer the question, or don't you know >either? PS: As Shawn (who is more helpful in giving fast answers ;-) pointed out: the answer can be found in the manual... Which begs for another questi

Re: Table names with periods

Hi there, >Instead of criticizing people's designs without understanding the >requirements why don't you just answer the question, or don't you know >either? That's why I asked "why". It's not uncommon that a faulty/suboptimal design leads to questions for a problem that shouldn't be existing in

RE: MyISAM to InnoDB

> -Original Message- > From: Devananda [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 28, 2005 13:06 > To: Jeff > Cc: mysql@lists.mysql.com > Subject: Re: MyISAM to InnoDB > > > Jeff wrote: > >> Cut orignal thread because it was too long > > > > > > The system is only used as a

Re: Drop Table Problem

Try running a flush tables in mysql to make sure that mysqld knows about all the tables on disk. xtcsuk wrote: Running the command: Drop table if exists table1 complaints of table1 does not exist (Error: 1051). However, if table1 is swapped with another table, irrespective of its existence it

RE: MyISAM to InnoDB

> -Original Message- > From: Devananda [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 28, 2005 13:06 > To: Jeff > Cc: mysql@lists.mysql.com > Subject: Re: MyISAM to InnoDB > > > Jeff wrote: > >> Cut orignal thread because it was too long > > > > > > The system is only used as a

FROMAT function - German Notation

How can I display the result of the FROMAT function in german notation like '#.###,##'? I found no better way then this: REPLACE(REPLACE(REPLACE( FORMAT(money,2),'.','x'),',','.'),'x',',') as ge_money does anybody know a solution? thank's for help Benedikt -- MySQL General Mailing List For

Re: MyISAM to InnoDB

Jeff wrote: Cut orignal thread because it was too long The system is only used as a database server, it's a dual processor system with 2gig of ram. As you can see, Table1's MyISAM data file is quite large at 2.1 gig. Taking this into account what size InnoDB data files should I configure in m

Re: Table names with periods

Instead of criticizing people's designs without understanding the requirements why don't you just answer the question, or don't you know either? On 9/28/05, Martijn Tonies <[EMAIL PROTECTED]> wrote: > > > > >I am sure this has been asked before but I can't seemed to find it. > > Canyou create ta

Re: To multi thread or NOT to multi thread?

Hi, On 9/28/05, Lefteris Tsintjelis <[EMAIL PROTECTED]> wrote: > > John McCaskey wrote: > > Hello again, > > > > I modified your threading code to use a thread pool. Here are my > results: > > Hello, > > I modified the thread pool a bit to get rid of that lock ASAP. It is safe > to get rid of that

Re: varchar vs char speed improvement

On Tuesday 27 September 2005 11:59, Pooly wrote: > 2005/9/27, Ow Mun Heng <[EMAIL PROTECTED]>: > > Is there any doc looking at benchmarks of a database which is populated > > entirely with fixed length char compared to variable character lengths? > > > > I know using char is preferred over varchar

Re: Can we run linux commands from inside the msql client

[EMAIL PROTECTED] wrote: > The system commands you execute through the MySQL client are not performed > at the server ... Ah, OK, got it. Of course. I plead temporary under-caffeination :-) Thanks for the clarification. -- Hassan Schroeder - [EMAIL PROTECTED] Webtui

How to update libmysqlclient

Hi. Working on Linux Fedora Core 2. I'm programming an application which requires version 14 of the MySQL Client libraries. Now I've got version 10: --- [EMAIL PROTECTED] mysql_config --libs -L'/usr/lib/mysql' -lmysqlclient -lz -lcrypt -lnsl -lm [EMAIL PROTECTED] locate mysqlclient /

Re: Table names with periods

> >I am sure this has been asked before but I can't seemed to find it. > Canyou create table names with periods in them? I need to create tables >based on the IP address of machines (x.x.x.x) Ehm. Why? Instead of going the "silly table names" route, why not try a different design? With regards,

Re: Table names with periods

Chance Ellis <[EMAIL PROTECTED]> wrote on 09/28/2005 11:56:53 AM: > I am sure this has been asked before but I can't seemed to find it. > Canyou create table names with periods in them? I need to create tables > based on the IP address of machines (x.x.x.x) Currently, Mysql is puking on > format

Re: Can we run linux commands from inside the msql client

Hassan Schroeder <[EMAIL PROTECTED]> wrote on 09/28/2005 11:58:35 AM: > Wolfram Kraus wrote: > > >> can we execute shell commands inside mysql client > > > Use \! > > Whoa. I didn't know that. Is that controlled in any way by GRANT? > Or by conf/startup options? > > It seems like something you

Table names with periods

I am sure this has been asked before but I can't seemed to find it. Canyou create table names with periods in them? I need to create tables based on the IP address of machines (x.x.x.x) Currently, Mysql is puking on format. Any ideas? Thanks

Re: Can we run linux commands from inside the msql client

Wolfram Kraus wrote: >> can we execute shell commands inside mysql client > Use \! Whoa. I didn't know that. Is that controlled in any way by GRANT? Or by conf/startup options? It seems like something you might (strongly!) want to disallow in a shared environment :-) -- Hassan Schroeder -

RE: MyISAM to InnoDB

One more thing, noneed to give so much space for each logfile. The combined size of all log files should be around 25-50% of innodb_buffer_pool size. So you can reduce the size of each log file to 100M. You can specify that for storing log files. sujay -Original Message- From: Jeff [ma

RE: MyISAM to InnoDB

> -Original Message- > From: Sujay Koduri [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 28, 2005 11:04 > To: Jeff; mysql@lists.mysql.com > Cc: [EMAIL PROTECTED] > Subject: RE: MyISAM to InnoDB > > > > If you think your storage requiremnets will increase in > future, try to est

RE: MyISAM to InnoDB

If you think your storage requiremnets will increase in future, try to estimate how much you will be needing in the future in the worst case and try allocating that much of disk space now itself (Any way you have good amount of disk space left). Try creating a different partition for storing the

RE: MyISAM to InnoDB

> Cut orignal thread because it was too long Ok so I'm about to convert two tables in my database from MyISAM to InnoDB. They are currently: 14K Sep 15 13:15 Table1.frm 2.1G Sep 28 14:15 Table1.MYD 198M Sep 28 14:15 Table1.MYI 11K Sep 20 08:45 Table2.frm 424K Sep 28 14:15 Table2.MYD 110K Sep

Re: Foreign key support in MyISAM

On Wed, 28 Sep 2005 04:24:08 -0700 Sujay Koduri <[EMAIL PROTECTED]> wrote: > does anyone have an idea when mysql guys are going to include foreign key > support in myisam tables. > i have read that it is slated for a future release, but it is not mentioned > when it is actually scheduled for. >

RE: Weird database files

> -Original Message- > From: Gleb Paharenko [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 28, 2005 06:30 > To: mysql@lists.mysql.com > Subject: Re: Weird database files > > > Hello. > > > On the master we're still running 4.0.16, the slaves are > up to 4.1.13. > > If you can

MySQL 5.0 Release Candidate sproc's

I got MySQL 5.0 up and running on new hard drive. Now I would like to transfer MSSQL's stored proc to MySQL. Is it possible if I can transfer it over even if it have errors and that I will try to get them working with MySQL in later time? Or what is the best way to do them? Currently MySQL Migrati

Re: MySQL 5.0.13-rc has been released

Lenz Grimmer wrote: Yes, the MD5 checksum and the GPG signature were updated. This is how it looks on our local staging server: [EMAIL PROTECTED]:/build/stage/Downloads/MySQL-5.0> ls -l mysql-5.0.13-rc.tar.gz* - -rw-r--r--2 mysqldev users19209618 Sep 23 20:09 mysql-5.0.13-rc.tar.gz - -r

Re: Can we run linux commands from inside the msql client

> hi.. > > I searched the documentation and googled for sometime, but didnt find > anything related to this > can we execute shell commands inside mysql client (like using ! in oracle), > and if possible please tell me how. > > sujay system(\!) Execute a system shell command. I think \! i

Re: Weird database files

Hello. > On the master we're still running 4.0.16, the slaves are up to 4.1.13. If you can - upgrade the master server. Jeff McKeon wrote: >>Jeff wrote: >> >>>Had problem with our database this weekend, apparently an >> >>app did an >> >>>insert query that was huge size wise and t

Foreign key support in MyISAM

does anyone have an idea when mysql guys are going to include foreign key support in myisam tables. i have read that it is slated for a future release, but it is not mentioned when it is actually scheduled for. reference -- http://dev.mysql.com/doc/mysql/en/ansi-diff-foreign-keys.html

Re: MySQL 5.0 Release Candidate

On 9/27/05, Fred Ballard <[EMAIL PROTECTED]> wrote: > > > I had the same problem. Disabling it isn't enough. I had > to use the sc command from the Windows command prompt to > delete the service. Just enter sc on the command line and > a description of the command will be displayed. > > Fred > > >

RE: Can we run linux commands from inside the msql client

Thanks a ton :) sujay -Original Message- From: David Ziggy Lubowa [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 28, 2005 4:37 PM To: mysql@lists.mysql.com Cc: Sujay Koduri Subject: Re: Can we run linux commands from inside the msql client Yes you can ... e.g mysql>\! vi fil

Re: Can we run linux commands from inside the msql client

Yes you can ... e.g mysql>\! vi filename ( you would be able to edit the file while within mysql. cheers On Wednesday 28 September 2005 13:41, Sujay Koduri wrote: > hi.. > > I searched the documentation and googled for sometime, but didnt find > anything related to this > can we execute

Re: Can we run linux commands from inside the msql client

Sujay Koduri wrote: hi.. I searched the documentation and googled for sometime, but didnt find anything related to this can we execute shell commands inside mysql client (like using ! in oracle), and if possible please tell me how. sujay Use \! E.g. \! ls More information :\? HTH, Wolfra

Can we run linux commands from inside the msql client

hi.. I searched the documentation and googled for sometime, but didnt find anything related to this can we execute shell commands inside mysql client (like using ! in oracle), and if possible please tell me how. sujay

Re: To multi thread or NOT to multi thread?

John McCaskey wrote: Hello again, I modified your threading code to use a thread pool. Here are my results: Hello, I modified the thread pool a bit to get rid of that lock ASAP. It is safe to get rid of that lock right after mysql_store_results. http://dev.mysql.com/doc/mysql/en/threaded-cli