Mysql hypothetical performance

I build a few login and password systems in MySql, my general layout is as follows: Users table and account table. The users table has the bare minimum columns in it, id, username, password, session, updated, added. Account table has stuff like first name, last name, email address, street addres

error on restting password - with some addition

in addition : when issue : "mysqld-nt --skip-grant-tables" mysql stop automatically, and in process tab on task manager there is no any process name mysqld*. thats why i just added a line "skip-grant-tables" on my.ini. my next question is can we address this problem by installing 4.1.9. and/or d

error on restting password

hi all, i need to reset the root password. i have done 2 things. but not worked. i read http://dev.mysql.com/doc/mysql/en/resetting-permissions.html and do .. a). i changed my.ini, add a line like "skip-grant-tables" and started mysql. then issue command .. mysqladmin -u root flush-privile

Re: MySQL as Email database [sort of OT]

Hi! I just finished a project just like this. We just put in a brand new mail server running PostFix on Mac OS X Panther. We added all the extra bells and whistles for spam and virus filtering, as well as squirrel mail There is an single-line option in Postfix's config file to have all email go

Error 1271 (HY000) - Illegal mix of collations

Hi, I'm running a query using UNION, where all parts of the union are queries from the same syntax and from tables with same definition, and the select is same too. Each of the union parts is a query with JOINs. I got this error: ERROR 1271 (HY000): Illegal mix of collations for operation 'UNION

ANN: Gtk2::Ex::DBI-0.7

I'm pleased to annouce the 7th release of Gtk2::Ex::DBI ... http://entropy.homelinux.org/Gtk2-Ex-DBI/ Gtk2::Ex::DBI is an open-source helper object that makes your Gtk2-Perl apps data aware. It handles querying, 'painting' records on your Glade-generated form, passing updates back to the database

Re: Any means to get the optimizer out of the way?

Hi, On Feb 10, 2005, at 7:00 PM, Brad Eacker wrote: Folks, I have a 677M row table with index desc pts_awarded_snap; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | memb

table handler error

[MySQL][ODBC 3.51 Driver][mysqld-4.0.20]Got error 5 from table handler Can anyone translate this for me and/or point me to a mapping of table handler error codes to their meanings? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://l

Sub query again

I have worked out, to send that field to the where query, i have to select the outer table again !! i dont think this is very efficient ? select * from complaints, (SELECT IF (count(*) > 1,"Yes","No") AS count FROM complainant ccINNER JOIN complaints c ON cc.complainantID=c.complainantID WHERE c.

Any means to get the optimizer out of the way?

Folks, I have a 677M row table with index desc pts_awarded_snap; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | member_id | int(11) | | MUL | 0 | |

Sub Query question

Hi there, I was wondering if its possible to be able to send a field from the outer table to be used as the where statement for the sub query ? something like this select somefield from table 1, (select count(*) from table2 inner join table1 using somekey where table1.key=somefield) as alias w

RE: 1 to many query

> -Original Message- > From: Tom Crimmins > Sent: Thursday, February 10, 2005 17:08 > To: livejavabean > Cc: mysql@lists.mysql.com > Subject: RE: 1 to many query > > > > -Original Message- > > From: livejavabean > > Sent: Thursday, February 10, 2005 16:47 > > To: mysql@lists.mys

Re: "show table status" extremely slow

My experience with innodb is that show table status is slow. It's better to do show table status like 'my_table' -Eric On Thu, 10 Feb 2005 16:47:41 -0500, Zhe Wang <[EMAIL PROTECTED]> wrote: > Hi, there, > > We have a huge database (84 tables of about 360 G of data in MyISAM > tables). Rece

RE: 1 to many query

> -Original Message- > From: livejavabean > Sent: Thursday, February 10, 2005 16:47 > To: mysql@lists.mysql.com > Subject: 1 to many query > > Hi there.. > > > > Hope you can give me some thoughts on this. let say we have 3 tables > > > > table 1 (pk=project_id) > === > - proj

1 to many query

Hi there.. Hope you can give me some thoughts on this. let say we have 3 tables table 1 (pk=project_id) === - project_id - project_name table 2 (pk=project_id, project_state_flag) === - project_id - project_state_flag (fk to state_flag) table 3 (pk=state_flag) === - state_fla

RE: Add an ENUM Column

[snip] Is it possible to add an ENUM column to a table after it has been created? [/snip] Yes... http://dev.mysql.com/doc/mysql/en/alter-table.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

"show table status" extremely slow

Hi, there, We have a huge database (84 tables of about 360 G of data in MyISAM tables). Recently, we converted the entire database to InnoDB (in one table space) and set up replication. Then we experienced some slower performance. For example, "show table status" on the master took more

Add an ENUM Column

Hi, Is it possible to add an ENUM column to a table after it has been created? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: php conection problems

Argh,...this is what happens when you leave your sysadmin job to become a bureaucrat! Lost my edge I guess,...I just looked again at what I downloaded earlier today to install,...and I had the version for Mac OS X v 10.2 and not 10.3,... Thanks all for your help! Jeff At 8:29 AM +1100 2/11/05,

Re: global object-id

Hi! On Feb 10, Konrad Kieling wrote: > hi, > is there a simple way (ie without creating and deleting datasets in an > extra table) to use an auto_increment id field in several tables. no > table has to have all ids, so i cannot use the field of a master table > in childs. it seems one can use a

Makefile & my entries.

Dears,I want to add mysql's INCLUDEs & LIBs path to my Makefile.in . Of course, i have wizarded a project with KDEvelop.It has created 3 Makefile with .cvs .am .in extensions. Please guide me that i can add mysql_config to them. Yours,Mohsen -- MySQL General Mailing List For list archives: http

RE: php conection problems

As I said, I don't know the process on a mac (unless this is os X and then I can guess) so can't advise on that. The manual at http://www.php.net/manual/en/install.macosx.php probably has the info there. Another reason would be the different release schedules with php and mysql. They are dropping

RE: php conection problems

I'm running Mac OS X,...so if the the disable and rebuild isn't too hard, can you outline that process? Jeff Probably because they don't have the time to get the latest and greatest client library every time. I'm not sure how to rebuild it on Windows but you can disable the built in on unix and r

RE: php conection problems

Probably because they don't have the time to get the latest and greatest client library every time. I'm not sure how to rebuild it on Windows but you can disable the built in on unix and rebuild it linking in the latest library. Regards David Logan Database Administrator HP Managed Services 148 F

RE: php conection problems

That did the trick,...thanks so,...why is my Apache installation using an old client? After reading your suggestion, it makes more sense when I look at the output of phpinfo() which says that mysql is "builtin" and the Client API version is 3.23.49,...why?! I guess it was a bad assumption on

RE: php conection problems

I'm not sure if it can be a password problem as you can connect through command line,but when upgrading to 4.1 I had the classical "access deny" error with phpmyadmin whereas everything else was ok. You can still have a look there: http://dev.mysql.com/doc/mysql/en/old-client.html but I think in

RE: php conection problems

I've tried using a variety of very wide open permissions just to get something to connect including: using the root user/password in the php scripts declaring a user in mysql,... grant all on mydatabase.* to myuser identified by 'mypassword'; grant all on mydatabase.* to myuser@'%' identified by

RE: php conection problems

phpinfo() should tell you whether it is using the MySQL module or not, that will help you find out if that part is working. Michael -Original Message- From: Jeff Mao [mailto:[EMAIL PROTECTED] Sent: Thursday, 10 February, 2005 15:02 To: mysql@lists.mysql.com Subject: php conection proble

RE: Daily Incremental Backups on Mysql

The option in my.cnf is log-bin = 'logfilename here' Michael -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Thursday, 10 February, 2005 14:58 To: Jerry Swanson; mysql@lists.mysql.com Subject: Re: Daily Incremental Backups on Mysql Jerry Swanson wrote: >Does bina

RE: php conection problems

[snip] In the past, I've always simply installed these two pieces as I mentioned above and everything was so easy,but it's been awhile since I last did this,did I miss something? [/snip] Permissions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubs

php conection problems

Hi All, I'm not sure this a MySQL problem or a PHP problem,... I just picked up a new laptop, and like I had always done in the past, installed MySQL and PHP so I could test and play with code locally. The laptop is a Mac Powerbook running 10.3.8. The MySQL installation is the Standard 4.1.19 i

Re: Daily Incremental Backups on Mysql

Jerry Swanson wrote: Does binary logs are avaialbe in Mysql 4 or only in Mysql 5? Thanks On Thu, 10 Feb 2005 10:28:56 +1100, Daniel Kasak <[EMAIL PROTECTED]> wrote: Jerry Swanson wrote: Is it possible to do "daily incremental backups" on mysql? Thanks MySQL 4.x supports binary log

Re: MySQL as Email database [sort of OT]

Sorry, should have been a little clearer on that point. I will be using Postfix as the mail server. So I will be retrieving email from the SMTP server via POP3 or IMAP. I'm not really looking for help on the database structure, I've looked over the dbmail structure. I would want to retrieve all

Re: MySQL as Email database [sort of OT]

One of my friends is using "James" for this purpose - it uses MySQL as a backend - and he is very happy with it. You can find out more at http://james.apache.org/. Rhino - Original Message - From: "Brent Baisley" <[EMAIL PROTECTED]> To: Sent: Thursday, February 10, 2005 12:21 PM Subjec

Re: Aes Encryption

Has any body implemented Aes encryption while storing critical data in mysql? I want to know the logic you are implementing to store your passwords to encrypt/decrypt data. Love Kumar Love Kumar wrote: Hi, Aes Encryption requires a password (key) to access data, now where do we store this key?

RE: Error 2002

I will try to explain what I have done: The previous version of MySQL had some different directory structure. The .pid and .sock files were placed in /var/run/mysqld/, so instead of changing this after the upgrade I attempted to change my /etc/my.cnf file to reflect this. There was a [cl

Re: MySQL as Email database [sort of OT]

Brent Baisley <[EMAIL PROTECTED]> wrote on 02/10/2005 12:21:38 PM: > I am looking to store all incoming emails into a MySQL database. I've > looked into dbmail, but can't get it to compile under Mac OSX (I posted > a message on that list). I was wondering if anyone could point me in > another d

MySQL as Email database [sort of OT]

I am looking to store all incoming emails into a MySQL database. I've looked into dbmail, but can't get it to compile under Mac OSX (I posted a message on that list). I was wondering if anyone could point me in another direction to use MySQL as an email message store. I don't need a webmail int

Error 2002

I have upgraded my Debian box from MySQL version 3.23 to version 4.19. Everything works perfectly fine except on the local box. Therefore I'm sure it has something to do with sockets. When I am logged in as root I can connect to the server fine, when I am logged in as a user I get this error mess

RE: Importing Tables on Top of Tables

> -Original Message- > From: David Blomstrom > Sent: Thursday, February 10, 2005 10:06 > To: mysql@lists.mysql.com > Subject: Importing Tables on Top of Tables > > If I revise a MySQL table and try to publish it > online, I often get the error message, "Table my_table > already exists."

Re: slow query, how can i imporve it?

See below Shailendra Soni <[EMAIL PROTECTED]> wrote on 02/10/2005 01:43:18 AM: > Thank , > But i can't create multipal index it will not useful for my tabels. > > I tryed to set GLOBAL keycache1.key_buffer_size = 128*1024 > > but it gives error that "unknown system varible ' keycache1' ".

Importing Tables on Top of Tables

If I revise a MySQL table and try to publish it online, I often get the error message, "Table my_table already exists." So I have to delete the online version before I can import the revised version. Is there a way to just publish one table over another, as long as both have the same name? Thanks

RE: Need a New Password & Username

--- Tom Crimmins <[EMAIL PROTECTED]> wrote: > It's all there in the manual. I like your explanation a lot better; it works, too! :) Thanks. __ Do you Yahoo!? All your favorites on one personal page – Try My Yahoo! http://my.yahoo.com -- M

Re: global object-id

> is there a simple way (ie without creating and deleting datasets in an > extra table) to use an auto_increment id field in several tables. no > table has to have all ids, so i cannot use the field of a master table > in childs. it seems one can use a sequence in postgresql in different > tables.

global object-id

hi, is there a simple way (ie without creating and deleting datasets in an extra table) to use an auto_increment id field in several tables. no table has to have all ids, so i cannot use the field of a master table in childs. it seems one can use a sequence in postgresql in different tables. is

RE: Need a New Password & Username

> -Original Message- > From: David Blomstrom > Sent: Thursday, February 10, 2005 09:21 > To: mysql@lists.mysql.com > Subject: RE: Need a New Password & Username > > Wow, I don't remember dealing with all that code the > first time around. Can someone show me EXACTLY what I > would write i

RE: Need a New Password & Username

Wow, I don't remember dealing with all that code the first time around. Can someone show me EXACTLY what I would write if my database is named my_database, and I want to add the username private_host and the password superstar? And if I don't have mysqladmin, can I type it into the SQL Query window

Re: disable multiple statements -- how??

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Dan wrote: > I've searched the documentation and Google but can't find the answer. > > How do I disable the multiple statements processing in v4.1.x? > > I don't want the user to be able to send more than one statement (like it > was in 4.0.x). > >

RE: Need a New Password & Username

> -Original Message- > From: David Blomstrom > Sent: Thursday, February 10, 2005 08:52 > To: mysql@lists.mysql.com > Subject: Need a New Password & Username > > I just recreated a database after my computer crashed. > All my MyISAM tables survived, but my InnoDB tables > vanished. The ot

Re: No mysqld pid file found !!! Please help...

Read this: http://www.whoopis.com/howtos/php5-mysql4-FC3-rpm.html Quoting Helena Carlsson <[EMAIL PROTECTED]>: > Hi all, > > On a Fedora Core 3 machine which releases with MySQL > 3.23 I downloaded all necessary 4.1 RPMs and > installed, however the MySQL 3.23 existed, but it > didn't work. Als

RE: Daily Incremental Backups on Mysql

> -Original Message- > From: Jerry Swanson > Sent: Thursday, February 10, 2005 08:33 > To: Daniel Kasak > Cc: mysql@lists.mysql.com > Subject: Re: Daily Incremental Backups on Mysql > > Does binary logs are avaialbe in Mysql 4 or only in Mysql 5? > Thanks > Binary logs are available in

No mysqld pid file found !!! Please help...

Hi all, On a Fedora Core 3 machine which releases with MySQL 3.23 I downloaded all necessary 4.1 RPMs and installed, however the MySQL 3.23 existed, but it didn't work. Also, all 4.1 RPMs had uninstalled automatically MySQL 3.23 and had re-installed itself. Then I removed all new installed 4.1 RPM

Need a New Password & Username

I just recreated a database after my computer crashed. All my MyISAM tables survived, but my InnoDB tables vanished. The other problem is that I need to reassign a username and password, and I can't remember how I did that originally. I now have the latest version of phpMyAdmin, but I can't find a

Re: Daily Incremental Backups on Mysql

Does binary logs are avaialbe in Mysql 4 or only in Mysql 5? Thanks On Thu, 10 Feb 2005 10:28:56 +1100, Daniel Kasak <[EMAIL PROTECTED]> wrote: > Jerry Swanson wrote: > > >Is it possible to do "daily incremental backups" on mysql? > > > > > >Thanks > > > > > > > Yes. > mysqldump will give you

RE: aborting slipped keys

> -Original Message- > From: Scott Haneda > Sent: Thursday, February 10, 2005 04:40 > To: MySql > Subject: aborting slipped keys > > In the myslq monitor, in my shell, sometimes I mess up and > slip a few keys, > and mysql is just stuck, waiting on more input, and nothing I > tell it, o

disable multiple statements -- how??

I've searched the documentation and Google but can't find the answer. How do I disable the multiple statements processing in v4.1.x? I don't want the user to be able to send more than one statement (like it was in 4.0.x). ie: SELECT * FROM table; DROP TABLE table; would generate a SQL error. I w

Re: Locked myself out of the mysql database!

hi, 1. i have same problem. SPEC : winxp sp2 + recent security bug patch. mysql 4.1.9. the docs : http://dev.mysql.com/doc/mysql/en/Resetting_permissions.html just doesnt't worked. At the DOS command prompt, execute this command: C:\> C:\mysql\bin\mysqld-nt --skip-grant-tables mysql\bin is

Re: Experience with MySQL 5

> Does anyone has experience with MySQL 5.x? > > How reliable is it? On Windows: currently, not at all. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For

Re: Filtering non-ascii characters from mysql data, null, tab etc

On Thu, 10 Feb 2005 10:17:00 +, wrote: >Hi, >I've successfully used the following update-replace statement to replace >strings in mysql data > >update tbl_county_lookup set countyname=replace(countyname,'&','and') ; > >However I've had problems trying to replace a null character 0x00h , > >

Re: AW: Slow Replication

"Hannes Rohde" <[EMAIL PROTECTED]> wrote on 10/02/2005 11:44:13: >I don't think we are dealing with an IO bottleneck here because the > slave server should quite faster with writings to the disc at least since we > are using Raid 0 here. Or is there any way which could explain an IO > bottlen

RE: Upgrading from 3.23.57 to 4.1.9 using mysqldump

Modify the insert statement so that you name the column you're inserting to. INSERT INTO `db` (put the column names in here) VALUES('localhost','dbxxx','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y'),. .. Kevin Cowley R&D Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alc

Upgrading from 3.23.57 to 4.1.9 using mysqldump

Hi, I just tried to upgrade from mysql 3.23.57 (SPARC/Solaris) to 4.1.9 (i386/Linux) using mysqldump but when importing the dump on the new machine mysql gives the error: ERROR 1136 (21S01) at line NNN: Column count doesn't match value count at row 1 and in line NNN you find INSERT INTO `db`

Full-text search performance issues

Since MySQL stores RowIDs with the indexed words instead of the table's primary key IDs, and since it uses only one index per table in any query, performing a full-text search on a large table (several million rows) and joining it with another large table proves to be extremely slow! The bottlenec

AW: Slow Replication

I don't think we are dealing with an IO bottleneck here because the slave server should quite faster with writings to the disc at least since we are using Raid 0 here. Or is there any way which could explain an IO bottleneck even though the slave is not running as many selects as the master

Re: How to specify autoincrement primary key value

> > > One simple question... AFAIK I can specify value for an autoincrement > > > primary key (int) when inserting a record like this: > > > > > > INSERT INTO `tablename` (`id`, `name`) VALUES (1, 'test') > > > > > > But it doesn't work for id = 0. Why? > > > > > > I would like to use some primary

RE: How to specify autoincrement primary key value

> > > > > Hello, > > > > One simple question... AFAIK I can specify value for an autoincrement > > primary key (int) when inserting a record like this: > > > > INSERT INTO `tablename` (`id`, `name`) VALUES (1, 'test') > > > > But it doesn't work for id = 0. Why? > > > > I would like to use some p

Re: Copying Database from One Hard Drive to Another

Well if you copy my.cnf settings along with ibdata files and log files then it is possible to copy innodb exactly on the different machine and i have done this before worked fine for me. Love .. [EMAIL PROTECTED] wrote: InnoDB files can not be copied from one machine to another like the MyISAM f

Re: How to specify autoincrement primary key value

"Denis Gerasimov" <[EMAIL PROTECTED]> wrote on 10/02/2005 10:59:11: > > Hello, > > One simple question... AFAIK I can specify value for an autoincrement > primary key (int) when inserting a record like this: > > INSERT INTO `tablename` (`id`, `name`) VALUES (1, 'test') > > But it doesn't work

RE: Slow Replication

I think he is talking about the "innodb_flush_log_at_trx_commit" parameter. Try to put it at a value of 0. innodb_flush_log_at_trx_commit = 0 If you have an IO bottleneck, this may help. Marc. -Message d'origine- De : Hannes Rohde [mailto:[EMAIL PROTECTED] Envoyé : jeudi 10 février 20

Re: How to log some queries and not others

Dear Paul, Paul DuBois wrote: At 19:52 +0300 2/9/05, George Chelidze wrote: Hello, I have the following problem: I have a table with a blob field and turned on query logging. I'd like to log every query except inserts into table with a blob field because my log files grow very fast and I don't l

How to specify autoincrement primary key value

Hello, One simple question... AFAIK I can specify value for an autoincrement primary key (int) when inserting a record like this: INSERT INTO `tablename` (`id`, `name`) VALUES (1, 'test') But it doesn't work for id = 0. Why? I would like to use some primary key values for special purpose, e.g.

AW: Slow Replication

I don't quite get what you mean with the second paragraph. Do you mean increasing the thread concurrency to 6 or something like that? I have already put it on 4 because we do have HT active on the cpu. On the other it is just a single processor P IV system. On other hand I think it wouldn't

aborting slipped keys

In the myslq monitor, in my shell, sometimes I mess up and slip a few keys, and mysql is just stuck, waiting on more input, and nothing I tell it, other than a control-C gets me out, at which point I have to login again, which is a time killer. How do you get out of a stuck mysql? Sometimes, and

Re: Slow Replication

On Wed, 9 Feb 2005 22:07:19 +0100, Hannes Rohde <[EMAIL PROTECTED]> wrote: > Hi all, > > We use MySQL as a database backend on a portal site. We have a two > database server setup (one master, one slave). The master is a PIV 3,2 GHz., > 2 GB Ram and a 80GB Raid-1 system. The slave is a PIV

Re: Memory limit?

On Thu, 10 Feb 2005 10:19:32 +0900, Batara Kesuma <[EMAIL PROTECTED]> wrote: > Hi Tobias, > > On Wed, 9 Feb 2005 14:48:16 +0100 (CET) > Tobias Asplund <[EMAIL PROTECTED]> wrote: > > > > I try to install MySQL 4.1.9 (official RPM from mysql.com). My machine > > > is running linux 2.6.9, and it has

Filtering non-ascii characters from mysql data, null, tab etc

Hi, I've successfully used the following update-replace statement to replace strings in mysql data update tbl_county_lookup set countyname=replace(countyname,'&','and') ; However I've had problems trying to replace a null character 0x00h , I'd be interested to know the syntax to filter null ch