Re: Syntax Error
Hi Mark. On Fri, 7 Apr 2006, Mark Sargent wrote: > To: mysql@lists.mysql.com > From: Mark Sargent <[EMAIL PROTECTED]> > Subject: Syntax Error > > Hi All, > > am trying to get up to speed on cli syntax again, > > mysql> show open tables from osc > -> > > what is wrong with the command above and the one below, > > mysql> show tables from osc > -> Although it is not mentioned in the syntax diagram in the manual, you need to terminate a mysql command with ';', like this: mysql> show tables from osc; The reason for this is that mysql allows you to spread a command over many lines, which can be helpfull, eg: mysql> show create table bible_quiz_question \G *** 1. row *** Table: bible_quiz_question Create Table: CREATE TABLE `bible_quiz_question` ( `ID` mediumint(8) unsigned NOT NULL auto_increment, `question_text` text NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> select ID, question_text -> from bible_quiz_question -> where ID = 1 -> ; ++---+ | ID | question_text | ++---+ | 1 | How old was the first man Adam, when he died? | ++---+ 1 row in set (0.00 sec) So mysql will not execute the select query above, untill it sees the ';' that terminates the command. This is why you were getting: > mysql> show tables from osc > -> because mysql was waiting for you to type something else in, or terminate the command with ';'. If you have problems displaying output because it is to large to fit into the table output format, you can terminate the mysql command with: mysql> show tables from osc \G instead of: mysql> show tables from osc; HTH Regards Keith > Why do I not get any output? I was following here, > > http://dev.mysql.com/doc/refman/5.1/en/show-open-tables.html > > I'm a Linux user, and wish to do everything via cli as opposed to > phpmyadmin. That's a good way to learn how to use mysql properly. phpmyadmin is a usefull tool for people that allready know how to use mysql via the mysql monitor program (CLI program). > Cheers. > > Mark Sargent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stunningly slow query
I have just noticed this from section 13.4.5. LOCK TABLES and UNLOCK TABLES Syntax of the 5.0.18 ref manual, and wondered if it will help improve the speed of your query: Normally, you do not need to lock tables, because all single UPDATE statements are atomic; no other thread can interfere with any other currently executing SQL statement. However, there are a few cases when locking tables may provide an advantage: If you are going to run many operations on a set of MyISAM tables, it is much faster to lock the tables you are going to use. Locking MyISAM tables speeds up inserting, updating, or deleting on them. The downside is that no thread can update a READ-locked table (including the one holding the lock) and no thread can access a WRITE-locked table other than the one holding the lock. The reason some MyISAM operations are faster under LOCK TABLES is that MySQL does not flush the key cache for the locked tables until UNLOCK TABLES is called. Normally, the key cache is flushed after each SQL statement. Regards Keith Roberts In theory, theory and practice are the same; in practice they are not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Undelete rows with .MYD-File?
Thankyou for your reply Dilipkumar. Much appreciated. Regards Keith On Tue, 4 Apr 2006, Dilipkumar wrote: > To: [EMAIL PROTECTED] > From: Dilipkumar <[EMAIL PROTECTED]> > Subject: Re: Undelete rows with .MYD-File? > > Hi, > > If it is a busry server you cannot copy MYD & FRM file * MYI files to > another data ditrectory. > Try to take a dump or try using snapshot your datadirectory. > If it is a less MB database you can user as: > In mysql prompt. > flush tables with read locks > dont exit you mysql terminal > Open an another terminal tar -cvzf you mysql old datadirectory and then > after doing so > give in 1st prompt flush tables. > Then Untar your tar files to your new data-direcotry. > > This might help you out. > > [EMAIL PROTECTED] wrote: > > > As my server does not get alot of traffic I tend to shutdown mysql, > > and do an OS copy of the complete /var/lib/mysql directory to another > > partition on another drive. Then restart mysql again. > > > > This may not be a feasable option on a busy server. > > > > Obviously each person has their way of doing backups. I do need to > > study the manual myself on all available backup options. > > > > Regards > > > > Keith > > > > > > On Mon, 3 Apr 2006, Nico Schefer wrote: > > > > > > > > > To: [EMAIL PROTECTED] > > > From: Nico Schefer <[EMAIL PROTECTED]> > > > Subject: Re: Undelete rows with .MYD-File? > > > > > > Hi Keith > > > > > > > > > > > > > It looks like someone has packed this table with myisampack, > > > > which means it's read only. Did you use myisampack on the > > > > table before it got dropped Nico? > > > > > > > > > > > Thanks a lot for looking at the problem. As far as i know the > > > table has > > > not been compressed, and i've not used myisampack. I'v tried to > > > run > > > myisamchk -e -r as well, but is has trunctated my MYD-File as > > > well. > > > I think i have to live with it, i begun to recunstruct the data by > > > hand > > > wich took me the whole day now and i'm not finished by far.. but > > > well, > > > i'll certainly do some backups now ;-) > > > > > > Thanks and greetings, Nico > > > > > > > > > > > > > > > -- > Thanks & Regards, > Dilipkumar > DBA Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Undelete rows with .MYD-File?
As my server does not get alot of traffic I tend to shutdown mysql, and do an OS copy of the complete /var/lib/mysql directory to another partition on another drive. Then restart mysql again. This may not be a feasable option on a busy server. Obviously each person has their way of doing backups. I do need to study the manual myself on all available backup options. Regards Keith On Mon, 3 Apr 2006, Nico Schefer wrote: > To: [EMAIL PROTECTED] > From: Nico Schefer <[EMAIL PROTECTED]> > Subject: Re: Undelete rows with .MYD-File? > > Hi Keith > > > It looks like someone has packed this table with myisampack, > > which means it's read only. Did you use myisampack on the > > table before it got dropped Nico? > > Thanks a lot for looking at the problem. As far as i know the table has > not been compressed, and i've not used myisampack. I'v tried to run > myisamchk -e -r as well, but is has trunctated my MYD-File as well. > I think i have to live with it, i begun to recunstruct the data by hand > wich took me the whole day now and i'm not finished by far.. but well, > i'll certainly do some backups now ;-) > > Thanks and greetings, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Undelete rows with .MYD-File?
karsites:/var/swissmade # ./myisamchk -dvv shop_item.MYI MyISAM file: shop_item.MYI Record format: Packed Character set: latin1_swedish_ci (8) File-version:1 Creation time: 2006-03-31 13:59:48 Status: open,changed Auto increment key: 1 Last value: 673 Data records: 0 Deleted blocks: 675 Datafile parts: 675 Deleted data: 33760 Datafile pointer (bytes): 4 Keyfile pointer (bytes): 4 Datafile length: 33760 Keyfile length: 8192 Max datafile length: 4294967294 Max keyfile length: 4398046510079 Recordlength: 295 It looks like someone has packed this table with myisampack, which means it's read only. Did you use myisampack on the table before it got dropped Nico? Also, did mysql die when the table was in use? I have tries to run myisamchk -r shop_item.MYI but this sets the *.MYD file to zero. I don't know if it is possible to recover data from a packed table. AFAIK the packing process is one way, and you may need the original non-compressed table to get your data. Regards Keith On Mon, 3 Apr 2006, Nico Schefer wrote: > To: [EMAIL PROTECTED] > From: Nico Schefer <[EMAIL PROTECTED]> > Subject: Re: Undelete rows with .MYD-File? > > Hi > > Keith, thanks for your proposal with myisamchk. > > If i'm using the myisamchk, it finds the deleted rows, but i have not > found a way to restore them and i can't find nothing in the manual.. > > Checking MyISAM file: shop_item.MYI > Data records: 0 Deleted blocks: 675 > myisamchk.exe: warning: 1 client is using or hasn't closed the table > properly > - check file-size > - check record delete-chain > - check key delete-chain > - check index reference > - check data record references index: 1 > - check record links > MyISAM-table 'shop_item.MYI' is usable but should be fixed > > Does anybody know how to restore the data this way? > > Thanks a lot, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Undelete rows with .MYD-File?
IIRC there may be a hidden field in each table row that mysql uses to mark that row as deleted. I'm not sure if you can use some mysql utility program such as myisamchk to undelete the rows. This may be possible. Regards Keith In theory, theory and practice are the same; in practice they are not. On Mon, 3 Apr 2006 [EMAIL PROTECTED] wrote: > To: mysql@lists.mysql.com > From: [EMAIL PROTECTED] > Subject: Re: Undelete rows with .MYD-File? > > > I have downloaded the table files and this is what I get: > > mysql> use swissmade; > Database changed > mysql> show tables; > +-+ > | Tables_in_swissmade | > +-+ > | shop_item | > +-----+ > 1 row in set (0.00 sec) > > mysql> select * from shop_item \G > Empty set (0.00 sec) > > Do you have any copies of the table files that you have not > run the recovery program on? > > Keith > > In theory, theory and practice are the same; > in practice they are not. > > > On Mon, 3 Apr 2006, Nico Schefer wrote: > > > To: mysql@lists.mysql.com > > From: Nico Schefer <[EMAIL PROTECTED]> > > Subject: Undelete rows with .MYD-File? > > > > Hi! > > > > Today i've dropped a MySQL-table and realized seconds later that i've > > dropped the wrong one.. I've saved the .MYD-File and tried to recover it > > with a tool (MySQLRecovery 1.5), but it just recovered the structure and > > not the data (about 620 rows). > > I've searched now all the day and not found anything. Maybe someone knows > > how to bring the data back or has got any hint for me? > > > > Binary log is not enabled unfortunately... > > > > http: //www.swissmade.com/mysql/shop_item.MYD > > http: //www.swissmade.com/mysql/shop_item.MYI > > http: //www.swissmade.com/mysql/shop_item.frm > > > > Thanks a lot! > > > > Nico > > > > -- > > Nico Schefer > > [EMAIL PROTECTED] > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Undelete rows with .MYD-File?
I have downloaded the table files and this is what I get: mysql> use swissmade; Database changed mysql> show tables; +-+ | Tables_in_swissmade | +-+ | shop_item | +-+ 1 row in set (0.00 sec) mysql> select * from shop_item \G Empty set (0.00 sec) Do you have any copies of the table files that you have not run the recovery program on? Keith In theory, theory and practice are the same; in practice they are not. On Mon, 3 Apr 2006, Nico Schefer wrote: > To: mysql@lists.mysql.com > From: Nico Schefer <[EMAIL PROTECTED]> > Subject: Undelete rows with .MYD-File? > > Hi! > > Today i've dropped a MySQL-table and realized seconds later that i've > dropped the wrong one.. I've saved the .MYD-File and tried to recover it > with a tool (MySQLRecovery 1.5), but it just recovered the structure and > not the data (about 620 rows). > I've searched now all the day and not found anything. Maybe someone knows > how to bring the data back or has got any hint for me? > > Binary log is not enabled unfortunately... > > http: //www.swissmade.com/mysql/shop_item.MYD > http: //www.swissmade.com/mysql/shop_item.MYI > http: //www.swissmade.com/mysql/shop_item.frm > > Thanks a lot! > > Nico > > -- > Nico Schefer > [EMAIL PROTECTED] > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Undelete rows with .MYD-File?
If those three files were backed up some where before you dropped the table all you need to do is to copy them back into the data dir, and things should be ok again. Keith In theory, theory and practice are the same; in practice they are not. On Mon, 3 Apr 2006, Nico Schefer wrote: > To: mysql@lists.mysql.com > From: Nico Schefer <[EMAIL PROTECTED]> > Subject: Undelete rows with .MYD-File? > > Hi! > > Today i've dropped a MySQL-table and realized seconds later that i've > dropped the wrong one.. I've saved the .MYD-File and tried to recover it > with a tool (MySQLRecovery 1.5), but it just recovered the structure and > not the data (about 620 rows). > I've searched now all the day and not found anything. Maybe someone knows > how to bring the data back or has got any hint for me? > > Binary log is not enabled unfortunately... > > http: //www.swissmade.com/mysql/shop_item.MYD > http: //www.swissmade.com/mysql/shop_item.MYI > http: //www.swissmade.com/mysql/shop_item.frm > > Thanks a lot! > > Nico > > -- > Nico Schefer > [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stunningly slow query
On Mon, 3 Apr 2006 [EMAIL PROTECTED] wrote: > To: [EMAIL PROTECTED] > From: [EMAIL PROTECTED] > Subject: Re: stunningly slow query > > [EMAIL PROTECTED] wrote on 04/02/2006 05:35:59 PM: > > > > > > Can you post your show create table tbl_name statement for > > > > these tables that involve slow queries? > > > > > > | old_crumb |CREATE TABLE `old_crumb` ( > > > `link_ID` bigint(20) default NULL, > > > `dir_Travel` char(1) default NULL, > > > `customer_ID` int(11) NOT NULL default '0', > > > `source_ID` int(11) NOT NULL default '0', > > > `vehicle_ID` int(11) NOT NULL default '0', > > > `actual_Time` datetime NOT NULL default '-00-00 00:00:00', > > > > `last_Modified` datetime default NULL, > > > PRIMARY KEY (`customer_ID`,`source_ID`,`vehicle_ID`,`actual_Time`), > > > KEY `old_crumb_ix_reported_Time` (`reported_Time`), > > > KEY `old_crumb_ix_link_ID` (`link_ID`,`dir_Travel`) > > > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=10 > > COMMENT='List of breadcrumbs already rolled up.' INDEX > > DIRECTORY='/var/mysql_idx/trimble/' | > > > > I'm no DB expert Chris but do you really need to create a > > primary key index over 4 columns? > > > > What about something simple and possibly faster like adding > > a seperate ID primary key column to the table like: > > > > | old_crumb |CREATE TABLE `old_crumb` ( > >`ID` int unsigned not null auto_increment > >`link_ID` bigint(20) default NULL, > >`dir_Travel` char(1) default NULL, > >`customer_ID` int(11) NOT NULL default '0', > >`source_ID` int(11) NOT NULL default '0', > >`vehicle_ID` int(11) NOT NULL default '0', > > snip > >PRIMARY KEY (`ID`), > > snip > > > > An unsigned int will take an extra 4 bytes of storage space > > per row, and will give you an index range of 0 - 4294967295. > > > > If that is not enough range, an unsigned bigint will take an > > extra 8 bytes of storage space, and will give you an index > > range of 0 - 18446744073709551615. > > > > Although this will increase the amount of storage space > > required in the .MYD file, it may also decrease the amount > > of space required in the .MYI index file, as you would not > > be needing to store multi-column indexes. > > > > Keith, > Your method won't guarantee that there are no rows where the combination > of the values in those four columns fails to repeat in any other row. To > do that would require an EXTRA four-column unique index of type UNIQUE. > Your proposal would actually make the situation worse as now there would > be two indexes to maintain to achieve the same effect as the previous > single PK. Thankyou for your expert reply Shawn. Is it not possible to mark each of those those column values as UNIQUE without them becoming a part of the index as well? Or is this a contradiction in terms? Regards Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stunningly slow query
On Sun, 2 Apr 2006, Chris Kantarjiev wrote: > To: mysql@lists.mysql.com > From: Chris Kantarjiev <[EMAIL PROTECTED]> > Subject: Re: stunningly slow query > > > The problem with Load Data is the larger the table, the > > slower it gets because it has to keep updating the index > > during the loading process. > > Um, thanks. I'm not sure how Load Data got involved here, because > that's not what's going on. > > > > > > It's a MyISAM table. Are there separate logs files? If so, where? > > > I thought it was just .frm, .MYI and .MYD. We have MYI and MYD on > > > separate drives. > > > > Log files usually default to the mysql data directory, eg. > > /var/lib/mysql/ > > As I said, I don't think there are any log files for a MyISAM table. > InnoDB has separate logs. > > > > > Putting the database files on seperate drives may slow > > things down alot too - unless others know better. > > > > .frm is the database definition file. .MYI is the index > > file, and .MYD is the data file. There is one each of these > > files for each myisam table in the database. > > > > I may be wrong, but I would have thought it better if these > > are all together on the same disk and partition for each > > table in the database? > > This is counter-intuitive. Separating .MYI and .MYD means that > I can overlap the i/o. This is a standard strategy for other > databases (Oracle, in particular). I would be really surprised > if this was causing my problem. OK - something new I've just learnt Chris. > > This feature can be activated explicitly. ALTER TABLE ... > > DISABLE KEYS tells MySQL to stop updating non-unique indexes > > for a MyISAM table. ALTER TABLE ... ENABLE KEYS then should > > be used to re-create missing indexes. > > > Can you post your show create table tbl_name statement for > > these tables that involve slow queries? > > | old_crumb |CREATE TABLE `old_crumb` ( > `link_ID` bigint(20) default NULL, > `dir_Travel` char(1) default NULL, > `customer_ID` int(11) NOT NULL default '0', > `source_ID` int(11) NOT NULL default '0', > `vehicle_ID` int(11) NOT NULL default '0', > `actual_Time` datetime NOT NULL default '-00-00 00:00:00', > `actual_TZ` varchar(30) default NULL, > `reported_Time` datetime default NULL, > `reported_TZ` varchar(30) default NULL, > `speed_Format` int(11) default NULL, > `speed` float default NULL, > `direction` char(2) default NULL, > `compass` int(11) default NULL, > `speed_NS` float default NULL, > `speed_EW` float default NULL, > `distance` decimal(10,0) default NULL, > `duration` decimal(10,0) default NULL, > `latitude` decimal(10,5) default NULL, > `longitude` decimal(10,5) default NULL, > `report_Landmark` varchar(255) default NULL, > `report_Address` varchar(255) default NULL, > `report_Cross` varchar(255) default NULL, > `report_City` varchar(255) default NULL, > `report_State` char(2) default NULL, > `report_Zip` varchar(10) default NULL, > `report_County` varchar(255) default NULL, > `category` int(11) default NULL, > `speed_Limit` int(11) default NULL, > `street` varchar(255) default NULL, > `city` varchar(255) default NULL, > `state` char(2) default NULL, > `zip` varchar(10) default NULL, > `county` varchar(255) default NULL, > `match_Name` tinyint(1) default NULL, > `name_Matched` tinyint(1) default NULL, > `last_Modified` datetime default NULL, > PRIMARY KEY (`customer_ID`,`source_ID`,`vehicle_ID`,`actual_Time`), > KEY `old_crumb_ix_reported_Time` (`reported_Time`), > KEY `old_crumb_ix_link_ID` (`link_ID`,`dir_Travel`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=10 COMMENT='List of > breadcrumbs already rolled up.' INDEX DIRECTORY='/var/mysql_idx/trimble/' | I'm no DB expert Chris but do you really need to create a primary key index over 4 columns? What about something simple and possibly faster like adding a seperate ID primary key column to the table like: | old_crumb |CREATE TABLE `old_crumb` ( `ID` int unsigned not null auto_increment `link_ID` bigint(20) default NULL, `dir_Travel` char(1) default NULL, `customer_ID` int(11) NOT NULL default '0', `source_ID` int(11) NOT NULL default '0', `vehicle_ID` int(11) NOT NULL default '0', snip PRIMARY KEY (`ID`), snip An unsigned int will take an extra 4 bytes of storage space per row, and will give you an index range of 0 - 4294967295. If that is not enough range, an unsigned bigint will take an extra 8 bytes
Re: MySQL 4.0.18 on Mac OS X 10.2.8 won't start
Here are some screen snapshots of qps showing mysql server running on my machine. I tries to post these to the list, but they went over the file size limit for the mailing list. Showing mysql running in memory without using mysqld_safe script: http://www.karsites.net/KAR/websites/pub/computing/mysql-running/mysqld1.jpg First part of command-line parameters passed to mysqld: http://www.karsites.net/KAR/websites/pub/computing/mysql-running/mysqld2.jpg Second part of command-line parameters passed to mysqld: http://www.karsites.net/KAR/websites/pub/computing/mysql-running/mysqld3.jpg Regards Keith In theory, theory and practice are the same; in practice they are not. On Sun, 2 Apr 2006, Sachin Petkar wrote: > To: mysql@lists.mysql.com > From: Sachin Petkar <[EMAIL PROTECTED]> > Subject: MySQL 4.0.18 on Mac OS X 10.2.8 won't start > > For some reason, MySQL 4.0.18 has suddenly stopped running and will not > start anymore. > > It has been running for several weeks until about 5 days > ago. When I tried to reach it, I discovered that it is no > longer running. However, attempting to start it via the > mysqld_safe script simply returns with: > > Starting mysqld daemon with databases from /usr/local/mysql/data > 060402 18:49:55 mysqld ended > > [1] Done ./mysqld_safe --user mysql > > > To confirm, the /tmp/mysql.sock file does not exist at this point. > > Any ideas on how to get this running again? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.18 on Mac OS X 10.2.8 won't start
Do you have some sort of visual process manager for Mac OS X that can tell you at a glance if mysqld_safe and mysql server are actually running in memory? Under linux I use a program called qps. http://www.student.nada.kth.se/~f91-men/qps/ You may already have a similar utility to view running processes under Mac OS X. Or there may be something similar you can download for free off the net. You really need some way of verifying that mysqld is actually running in memory, before attempting to connect to it. This is handy for showing running multiple servers, ie when upgrading to a newer version. You can see the port and socket each mysqld is listening to, plus other server directives such as the data directory and PID. I start mysqld directly with a bash shell script: #! /bin/sh # # start the MySQL database server /usr/local/mysql-5.0.18/bin/mysqld \ --defaults-file=/usr/local/mysql-5.0.18/my.cnf \ --port=7000 \ --socket=/var/lib/mysql/mysql.sock \ --pid=/var/lib/mysql/laptop.pid \ --user=mysql \ --datadir=/var/lib/mysql & and stop it with: #! /bin/sh # # stop the MySQL database server /usr/local/mysql-5.0.18/bin/mysqladmin shutdown \ -uXX -pXX --socket=/var/lib/mysql/mysql.sock If I don't use the script to pass parameters to mysqld but add them to my.cnf, they will not appear in qps process manager. I have noticed that sometimes mysqld_safe script would start, and be in memory, but the mysqld server was not being loaded into memory for some reason, which obviuosly meant I could not connect to the mysql server. For that reason I no longer use mysqld_safe to start mysqld. HTH Keith In theory, theory and practice are the same; in practice they are not. On Sun, 2 Apr 2006, Sachin Petkar wrote: > To: mysql@lists.mysql.com > From: Sachin Petkar <[EMAIL PROTECTED]> > Subject: MySQL 4.0.18 on Mac OS X 10.2.8 won't start > > For some reason, MySQL 4.0.18 has suddenly stopped running and will not > start anymore. > > It has been running for several weeks until about 5 days > ago. When I tried to reach it, I discovered that it is no > longer running. However, attempting to start it via the > mysqld_safe script simply returns with: > > Starting mysqld daemon with databases from /usr/local/mysql/data > 060402 18:49:55 mysqld ended > > [1]Done ./mysqld_safe --user mysql > > > To confirm, the /tmp/mysql.sock file does not exist at this point. > > Any ideas on how to get this running again? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with >10,000 databases
Gary - is there any way to check how many concurrent connections you have per second out of those 14,000 users? IIRC, each table requires at least one file handle to open the table's data file (.MYD), plus 1 more for the index file (.MYI) if the index file is updated. So, I'm wondering if you are running out of file handles, which would make mysql wait untill there are enough file handles free, for mysql to do it's job. open_files_limit The number of files that the operating system allows mysqld to open. This is the real value allowed by the system and might be different from the value you gave mysqld as a startup option. The value is 0 on systems where MySQL can't change the number of open files. mysql> show variables like "open%"; +--+---+ | Variable_name| Value | +--+---+ | open_files_limit | 1024 | +--+---+ 1 row in set (0.00 sec) mysql> show status like "open%"; +---+---+ | Variable_name | Value | +---+---+ | Open_files| 44| | Open_streams | 0 | | Open_tables | 20| | Opened_tables | 0 | +---+---+ 4 rows in set (0.00 sec) What does this return on your system? If this is the case, then I can't see how running multiple mysql servers will help. Keith On Sun, 2 Apr 2006, Alexey Polyakov wrote: > To: [EMAIL PROTECTED] > From: Alexey Polyakov <[EMAIL PROTECTED]> > Subject: Re: Tuning a Server with >10,000 databases > > On 4/1/06, Greg Whalin <[EMAIL PROTECTED]> wrote: > > > Not necessarily sure this is the problem. But if it is, it could be > > solved by switching to a different filesystem. Or, if you are using > > ext3fs, you could try enabling dir_index on the filesystem (tune2fs -O > > dir_index ... man tune2fs), which could give you a boost in performance > > in a large dir (this could take a long time to complete). You may also > > want to up your table cache so that mysql can keep more of your commonly > > used tables open? > > FWIW, I've experimented heavily with FS options, and found out that > dir_index on ext3 doesn't help at all, it actually harms performance. > 'noatime' and 'nodiratime' options do help a little. > Also, 14000 subdirectories is not something that will cause 15 seconds > delay - those 14000 subdirectories will always live in OS dentry cache > anyway. > > > -- > Alexey Polyakov > > -- > 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: Tuning a Server with >10,000 databases
I'm not saying it is an apache issue Gary. I was just suggesting a way you might be able to implement several different mysql server instances, and still have one apache instance for your users to connect to via port 80. Each mysql server instance could be bound to an apache server instance via it's own php module. So each apache instance can talk to a different mysql server, via php, on a different unix socket. It may be possible for one apache listening on port 80 to forward requests to virtual servers, running on different apache servers on your machine, listening on different ports. That way your users will not have to define a different port to connect to. I've not tested this out yet, but it may be an option that would work for you. Hope this poor diagram make sense! - | apache main server port 80 | - | | | | | | | | | avs2 avs3 avs3 | | | --- | apache virtual server 1 port 81 | --- | v | php module 1 | | v -- | mysqld server 1 | -- ditto --- | apache virtual server x port xx | --- | v | php module x | | v -- | mysqld server x | -- Regards Keith On Sat, 1 Apr 2006, Gary Huntress wrote: > To: [EMAIL PROTECTED] > From: Gary Huntress <[EMAIL PROTECTED]> > Subject: Re: Tuning a Server with >10,000 databases > > I really don't think this is an apache issue. I get the same poor > connection speeds using the mysql client. > > Gary > > [EMAIL PROTECTED] wrote: > > I'm just wondering if it would be possible to use several apache > > servers on different ports and using virtual servers, that would each > > talk to a different instance of mysql, each running on different unix > > sockets? > > > > The main apache server listening on port 80 could then redirect > > requests to the other apache virtual server instances. > > > > Everything after that should be plain sailing I think. > > > > Best place to ask would probably be the apache user mailing list on > > this one. > > > > Regards > > > > Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting number days between 2 dates
Use SELECT DATEDIFF('new_date', 'old_date');
mysql> SELECT DATEDIFF('2006-04-01','2006-04-01');
+-+
| DATEDIFF('2006-04-01','2006-04-01') |
+-+
| 0 |
+-----+
1 row in set (0.00 sec)
mysql> SELECT DATEDIFF('2006-04-01','2007-04-01');
+-+
| DATEDIFF('2006-04-01','2007-04-01') |
+-+
| -365 |
+-+
1 row in set (0.00 sec)
mysql> SELECT DATEDIFF('2006-04-01','2005-04-01');
+-+
| DATEDIFF('2006-04-01','2005-04-01') |
+-+
| 365 |
+-+
1 row in set (0.00 sec)
DATEDIFF(expr,expr2)
DATEDIFF() returns the number of days between the start date
expr and the end date expr2. expr and expr2 are date or
date-and-time expressions. Only the date parts of the values
are used in the calculation.
mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
-> 1
mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');
-> -31
Regards
Keith
In theory, theory and practice are the same;
in practice they are not.
On Sat, 1 Apr 2006, Rhino wrote:
> To: Mike Blezien <[EMAIL PROTECTED]>,
> Jorrit Kronjee <[EMAIL PROTECTED]>, mysql@lists.mysql.com
> From: Rhino <[EMAIL PROTECTED]>
> Subject: Re: Getting number days between 2 dates
>
>
> - Original Message - From: "Mike Blezien"
> <[EMAIL PROTECTED]>
> To: "Jorrit Kronjee" <[EMAIL PROTECTED]>;
> Sent: Saturday, April 01, 2006 9:00 AM
> Subject: Re: Getting number days between 2 dates
>
>
> > Jorrit,
> >
> > - Original Message - From: "Jorrit Kronjee"
> > <[EMAIL PROTECTED]>
> > To:
> > Sent: Saturday, April 01, 2006 7:46 AM
> > Subject: Re: Getting number days between 2 dates
> >
> >
> > > Mike Blezien wrote:
> > > > Hello,
> > > >
> > > > I'm sure this is a simple query but haven't come up with a
> > > > good approach. Need to get the number of days between two
> > > > dates. IE: today's date: (2006-04-01 - 2006-03-05)
> > > > need to calculate the number of days between these dates..
> > > > what is the best query statement to accomplish this?
> > > >
> > > > TIA,
> >
> > > Mike,
> > > You probably want to use something like this:
> > >
> > > SELECT TO_DAYS('2006-03-15') - TO_DAYS('2006-03-01');
> >
> > Thanks, that works, also using the DAYOFYEAR produces the same
> > results as I just found :)
> >
> > appreciate the help
> >
> I'd be careful with DAYOFYEAR() if I were you.
>
> DAYOFYEAR() only tells you which day it is within a given year. If you try
> to use DAYOFYEAR to tell the difference in days between dates that are in
> different years, you are certainly going to get the wrong answer. For
> instance, DAYOFYEAR(2006-04-01) - DAYOFYEAR(2005-04-01) gives an answer of
> 0 days when the correct answer is 365.
>
> A better choice for getting the difference between two dates in days is
> probably DATEDIFF() or TO_DAYS().
>
> --
> Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with >10,000 databases
I'm just wondering if it would be possible to use several apache servers on different ports and using virtual servers, that would each talk to a different instance of mysql, each running on different unix sockets? The main apache server listening on port 80 could then redirect requests to the other apache virtual server instances. Everything after that should be plain sailing I think. Best place to ask would probably be the apache user mailing list on this one. Regards Keith In theory, theory and practice are the same; in practice they are not. On Sat, 1 Apr 2006, Gary Huntress wrote: > To: [EMAIL PROTECTED] > From: Gary Huntress <[EMAIL PROTECTED]> > Subject: Re: Tuning a Server with >10,000 databases > > > > David Logan wrote: > > mos wrote: > > > > > At 09:27 PM 3/31/2006, you wrote: > > > > > > > I have been offering free database hosting for over 4 years > > > > and I've been doing it on a shoestring.My last MySQL > > > > server was a generic 1GHz system with 256MB RAM running > > > > Redhat 9. The performance was surprisingly good because the > > > > query loads were not typically high. One persistent problem > > > > was the initial connection times. On that old system if I > > > > had less than approx 10,000 separate databases then the > > > > connection times were "fast", and on the order of 1 second or > > > > so. If I had more than 10,000 databases this dramatically > > > > changed the connection times to well over 15 seconds or more. > > > > > > > > I always attributed this connection lag to a problem with the > > > > filesystem and the large number of directories. The old > > > > server had RH9 and ext3 with no htree support which I was > > > > told could help with this problem. > > > > > > > > I recently bought a new 2.4 GHz system with 1GB of RAM and > > > > installed Fedora 4 with ext3 and htree support. All new > > > > hardware, faster drives, more RAM and updated software. I > > > > thought I was golden!Well, I have 14,000 databases on > > > > this new system and it is as slow as the old 1GHz system. > > > > The tuning articles I've read, and the sample my-*.cnf files > > > > that ship with the tarball appear to apply to the more > > > > typical installation of a single huge database rather than > > > > thousands of individual dbs. Can anyone offer any > > > > suggestions? > > > > > > > > Thanks, > > > > > > > > Gary Huntress > > > > > > > > > > > > Gary, > > >Just a guess, but could the problem be the 14,000 > > > directories you have to store the 14,000 databases? The problem > > > could be the OS directory structure. Putting the data into fewer > > > databases will likely solve the problem or perhaps move half of > > > the directories to another drive. > > > > > > Mike > > > > > > > > > > > > > -- > > > > MySQL General Mailing List > > > > For list archives: http://lists.mysql.com/mysql > > > > To unsubscribe: > > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > Hi Gary, > > > > I think that Mike may have hit the nail on the head. I've a few unix > > directories with multiple thousand files and they do become a bit of > > a problem to manage speedwise. Perhaps, as Mike has suggested, place > > half of them on another drive. > > > > The other option could be to run multiple instances of MySQL, each > > having a different port number (this could be based on username or > > something similar) eg. A-D port 3306, E-H 3307, etc. and reducing the > > number of dbs per instance (server) that way. > > > > Regards > > > I agree with the diagnosis. I'm unsure how to move 1/2 the databases to a > new drive though. That would be the simplest solution. As I understand > it, MySQL will only use 1 data directory, so the best case would be > symlinks. I'm not sure about this but 15,000 symlinks to multiple drives > may be just as slow as 15,000 directory entries. Were either of you > thinking of another way to split up the directories?Unfortunately, > since I assign one database per user, I can't limit the number created. > > Multiiple servers may be my best option. > > Thanks, > > Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: searching for words with special chars
There at least two ways to accomplish this. The easy way is to use the LIKE operator: See http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html Regards Keith In theory, theory and practice are the same; in practice they are not. On Sat, 1 Apr 2006, Octavian Rasnita wrote: > To: mysql > From: Octavian Rasnita <[EMAIL PROTECTED]> > Subject: searching for words with special chars > > Hi, > > Is it possible to create a query that searches for records which contains > words with special chars and with their english correspondents? > > For example, if a user searches for "mata", I want to return all the records > that contain the words: > > mata > m?ta > mâ?a > mâ?? > > (just like Google does). > > Is it possible with MySQL, or I need to create all the possible combinations > in the client program, then search for all those words? > > Thank you. > > Teddy > > > -- > 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: stunningly slow query
If you use ALTER TABLE on a MyISAM table, all non-unique indexes are created in a separate batch (as for REPAIR TABLE). This should make ALTER TABLE much faster when you have many indexes. This feature can be activated explicitly. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique indexes for a MyISAM table. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier. Can you post your show create table tbl_name statement for these tables that involve slow queries? Do you have alot of indexes on these slow queries? If so, would using the above help? (may have been mentioned already) ALTER TABLE tbl_name DISABLE KEYS; your slow insert or update query here; ALTER TABLE tbl_name ENABLE KEYS; HTH Keith more -> On Fri, 31 Mar 2006, Chris Kantarjiev wrote: > To: [EMAIL PROTECTED], mysql@lists.mysql.com > From: Chris Kantarjiev <[EMAIL PROTECTED]> > Subject: Re: stunningly slow query > > > Are your logs and data on the same partition? That's a bad idea for > > recovering from a blown part of the disk, but we also saw that one of > > our databases would crash when there were lots of > > inserts/updates/replaces -- other databases, which had the same > > version of MySQL and operating system, had the logs and data on a > > separate partition, and they did not crash. > > It's a MyISAM table. Are there separate logs files? If so, where? > I thought it was just .frm, .MYI and .MYD. We have MYI and MYD on > separate drives. Log files usually default to the mysql data directory, eg. /var/lib/mysql/ Putting the database files on seperate drives may slow things down alot too - unless others know better. .frm is the database definition file. .MYI is the index file, and .MYD is the data file. There is one each of these files for each myisam table in the database. I may be wrong, but I would have thought it better if these are all together on the same disk and partition for each table in the database? > We're investigating a possible MERGE organization. I'll report > back if we learn anything new. > > Thanks, > chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Type For PK/FK
>From the 5.0.18 manual: The FOREIGN KEY and REFERENCES clauses are supported by the InnoDB storage engine, which implements ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES ... (...). See Section 14.2.6.4, FOREIGN KEY Constraints. For other storage engines, the clauses are parsed but ignored. The CHECK clause is parsed but ignored by all storage engines. See Section 13.1.5, CREATE TABLE Syntax. The reason for accepting but ignoring syntax clauses is for compatibility, to make it easier to port code from other SQL servers, and to run applications that create tables with references. See Section 1.9.5, MySQL Differences from Standard SQL. You cannot add a foreign key and drop a foreign key in separate clauses of a single ALTER TABLE statement. You must use separate statements. InnoDB supports the use of ALTER TABLE to drop foreign keys: ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol; You cannot add a foreign key and drop a foreign key in separate clauses of a single ALTER TABLE statement. You must use separate statements. For more information, see Section 14.2.6.4, FOREIGN KEY Constraints. Regards Keith ;-) In theory, theory and practice are the same; in practice they are not. On Fri, 31 Mar 2006, sheeri kritzer wrote: > To: Martijn Tonies <[EMAIL PROTECTED]> > From: sheeri kritzer <[EMAIL PROTECTED]> > Subject: Re: Table Type For PK/FK > > I didn't write the codebase for MySQL, so it's pointless to tell me > that "it's useless to be able to create a foreign key on a MyISAM > table". I agree that it's useless, however, it's possible, which is > why I put it in there -- as a caveat. > > The use is that apparently in future versions MyISAM will support > foreign key constraints. It's a comment because it still shows up in > SHOW CREATE TABLE and such. > > Like I said, I didn't design MySQL -- I just use it and was warning > that it's possible to create a table. I've seen the dreaded Error > number 150 way too many times, and sometimes it's because I forgot the > "engine=innodb" part of the CREATE TABLE statement. > > -Sheeri > > On 3/31/06, Martijn Tonies <[EMAIL PROTECTED]> wrote: > > Hello Sheeri, > > > > > Indeed, only the BDB and InnoDB storage engines support referential > > > integrity. If you accidentally create the table as MyISAM, there is > > > no error, though -- the constraints serve as a comment. > > > > No error? A comment? What use is that? > > > > If you want FKs, having the FKs as "a comment" is useless. > > > > Instead, re-create the table as of the InnoDB type. > > > > Martijn Tonies > > Database Workbench - development tool for MySQL, and more! > > Upscene Productions > > http://www.upscene.com > > My thoughts: > > http://blog.upscene.com/martijn/ > > Database development questions? Check the forum! > > http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AlterTable Structure Across Multiple DBs
ALTER TABLE requires a table name for the current database.
You can specify:
ALTER TABLE db_name.tbl_name MODIFY col_name ...
or
mysql> alter table test1.t1 modify test1.t1.set1 varchar(30);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
but you cannot modify a table in a different database to the
one you first specify after TABLE db_name:
mysql> alter table test1.t1 modify test2.t1.set1 varchar(30);
ERROR 1102 (42000): Incorrect database name 'test2'
.
.
.
mysql> show create table t1 \G
*** 1. row
Table: t1
Create Table: CREATE TABLE `t1` (
`ID` int(11) NOT NULL auto_increment,
`set1` set('this','is','today') default NULL,
`col2` char(20) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
mysql> alter table t1 modify set1 varchar(30);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t1 \G
*** 1. row
Table: t1
Create Table: CREATE TABLE `t1` (
`ID` int(11) NOT NULL auto_increment,
`set1` varchar(30) default NULL,
`col2` char(20) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
It should not take you too long to use the command history
to recall the alter table ... statement, edit it, and work your
way through the databases like that.
Another way is to write a script that will parse the
database names in the data directory, and then generate the
SQL code to perfom the multiple ALTER TABLE statements.
The script could then replace the db_name to be changed on
each iteration.
To be safe you could make copies of your databases, and
perform the ALTER TABLE statements away from your live data
directory.
When you are happy with the modifications, then copy the
altered databases back to your live data directory.
Personally I'd feel alot safer altering one database table
at a time - just in case errors start appearing.
You need to be carefull that you do not loose any multiple
values in your set, as varchar will only hold one value at
a time.
HTH
Keith
In theory, theory and practice are the same;
in practice they are not.
On Thu, 30 Mar 2006, Jason Dimberg wrote:
> To: mysql@lists.mysql.com
> From: Jason Dimberg <[EMAIL PROTECTED]>
> Subject: AlterTable Structure Across Multiple DBs
>
> I have about 25 databases with the same structure and
> occasionally need to update the table structure. For
> example, I recently found a mistake in a field that was of
> type SET and needed to be VARCHAR. I will now need to
> edit each table. Is there an easy method to alter table
> structure across multiple dbs as opposed to editing each
> one individually?
>
> In retrospect I should have combined them into one db and
> may consider doing that.
>
> All dbs start with 'pm_' and have identically named tables
>
> MySQL 5.0.18
> Windows 2003
>
> Thank you,
> --
>
> *Jason Dimberg*
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment and the value 0
Are you saying just change the row with the 0 value as the PK, and change the FK's in the related tables to point to the new value instaed of 0? If so, would this move the row logically to the end of the table, if the 0 PK was replaced with the next auto_increment value? I suppose that would be alot easier than trying to bump the PK and related FK values of the whole table by 1, just to give the first row in the table the auto_increment value of 1? What about before migrating the database, just adding a new row to the end of the table, that would duplicate the data in the first row, then deleting the first row from the table? Would that work? Keith In theory, theory and practice are the same; in practice they are not. On Thu, 30 Mar 2006, Daniel Kasak wrote: > To: "Stanton, Brian" <[EMAIL PROTECTED]>, > "'mysql@lists.mysql.com'" > From: Daniel Kasak <[EMAIL PROTECTED]> > Subject: Re: auto_increment and the value 0 > > Stanton, Brian wrote: > > I'm migrating a database from 4.0.12 on Solaris to 4.0.18-0 on Red > > Hat > > Linux. A few of the tables have a 0 (zero) in the auto_increment > > primary > > key column. However, when importing, the 0 in the insert is > > translated to > > the next available auto_increment value thus causing a duplicate key > > situation on the next value in the import. I've tried removing the 0 > > row > > from the export and adding it in manually afterwards, but that also > > translates the 0 to the next available auto_increment value. I've > > also > > tried creating the table with the table option AUTO_INCREMENT=0 and > > inserting the 0 row first. That also translated it to a value of 1 > > and > > caused duplicate keys. > > > You can either: > - create the table without the auto_increment field, load the data, and > add the auto_increment field, or > - change all your zero values *now* ( and related fields in other tables > ), back things up, and then move the data > > I would take the 2nd option. > > -- > Daniel Kasak > IT Developer > NUS Consulting Group > Level 5, 77 Pacific Highway > North Sydney, NSW, Australia 2060 > T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 > email: [EMAIL PROTECTED] > website: http://www.nusconsulting.com.au k -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.19-2 and repair problems...
Have you tried the following myisamchk option: --extend-check, -e Check the table very thoroughly. This is quite slow if the table has many indexes. This option should only be used in extreme cases. Normally, myisamchk or myisamchk --medium-check should be able to determine whether there are any errors in the table. Keith In theory, theory and practice are the same; in practice they are not. On Wed, 29 Mar 2006, Sander Smeenk wrote: > To: mysql@lists.mysql.com > From: Sander Smeenk <[EMAIL PROTECTED]> > Subject: Re: MySQL 5.0.19-2 and repair problems... > > Quoting Kishore Jalleda ([EMAIL PROTECTED]): > > > That usually means the table is corrupt beyond repair and nothing is really > > fixing it or there is something one cannot easily comprehend ( this usually > > happens with inconsistency among deleted records and some kind of mismatch > > that occurs) -anyway what I would really advice in this case is to > > rebuild the table from a working/clean backup and start-over, if this is a > > slave then thats very easy to do, if not it depends on your latest clean > > backup available .. > > Amazing. Amazing that even the tools can't tell me it's unfixable. > I mean, i tried everything. :) > > I'll restore the databases from the master (this isn't a slave, but it's > a machine i want to switch to when it finally becomes stable...) and see > where we get from that... > > The machine didn't crash. Nor did mysql. Any clue what might cause this > to happen? Disk looks fine too, no read or write errors whatsoever... > > Thanks, > Sander. > > -- > | Just remember -- if the world didn't suck, we would all fall off. > | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D > > -- > 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: Random 'select permission denied' since upgrade to 5.0.18
On Tue, 28 Mar 2006, Jorrit Kronjee wrote: > To: mysql@lists.mysql.com > From: Jorrit Kronjee <[EMAIL PROTECTED]> > Subject: Re: Random 'select permission denied' since upgrade to 5.0.18 > > [EMAIL PROTECTED] wrote: > > On Tue, 28 Mar 2006, Jorrit Kronjee wrote: > > > > > To: mysql@lists.mysql.com > > > From: Jorrit Kronjee <[EMAIL PROTECTED]> > > > Subject: Re: Random 'select permission denied' since upgrade to > > > 5.0.18 > > snip > > > Could this message appear when, for instance, a maximum amount of > > > threads > > > has been spawned or MySQL has reached its connection limit? > > > > Possible - what are your settings for the relevant mysql server > > variables? > > > > You could try something like: > > > > show variables like "max%" \G > > > > I'm not really sure what all the server variables do, but they may be > > relevant to your problem. > > > > Keith > > > > Keith, > > Here's the output: > > mysql> show variables like "max%" \G > *** 1. row *** > Variable_name: max_allowed_packet > Value: 1047552 > *** 2. row *** > Variable_name: max_binlog_cache_size > Value: 4294967295 > *** 3. row *** > Variable_name: max_binlog_size > Value: 1073741824 > *** 4. row *** > Variable_name: max_connect_errors > Value: 10 > *** 5. row *** > Variable_name: max_connections > Value: 300 > *** 6. row *** > Variable_name: max_delayed_threads > Value: 20 is this relevant ? > *** 7. row *** > Variable_name: max_error_count > Value: 64 > *** 8. row *** > Variable_name: max_heap_table_size > Value: 16777216 > *** 9. row *** > Variable_name: max_insert_delayed_threads > Value: 20 ditto > *** 10. row *** > Variable_name: max_join_size > Value: 4294967295 > *** 11. row *** > Variable_name: max_length_for_sort_data > Value: 1024 > *** 12. row *** > Variable_name: max_relay_log_size > Value: 0 > *** 13. row *** > Variable_name: max_seeks_for_key > Value: 4294967295 > *** 14. row *** > Variable_name: max_sort_length > Value: 1024 > *** 15. row *** > Variable_name: max_sp_recursion_depth > Value: 0 > *** 16. row *** > Variable_name: max_tmp_tables > Value: 32 > *** 17. row *** > Variable_name: max_user_connections > Value: 0 > *** 18. row *** > Variable_name: max_write_lock_count > Value: 4294967295 > 18 rows in set (0.00 sec) > > > -- > System Developer > > Infopact Network Solutions > Hoogvlietsekerkweg 170 > 3194 AM Rotterdam Hoogvliet > tel. +31 (0)88 - 4636700 > fax. +31 (0)88 - 4636799 > mob. +31 (0)6 - 14105968 > [EMAIL PROTECTED] > http://www.infopact.nl/ > > -- > 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: Random 'select permission denied' since upgrade to 5.0.18
On Tue, 28 Mar 2006, Jorrit Kronjee wrote: > To: mysql@lists.mysql.com > From: Jorrit Kronjee <[EMAIL PROTECTED]> > Subject: Re: Random 'select permission denied' since upgrade to 5.0.18 snip > Could this message appear when, for instance, a maximum amount of threads > has been spawned or MySQL has reached its connection limit? Possible - what are your settings for the relevant mysql server variables? You could try something like: show variables like "max%" \G I'm not really sure what all the server variables do, but they may be relevant to your problem. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Customer Recommendation Query
On Tue, 28 Mar 2006, Brian Erickson wrote: > To: mysql@lists.mysql.com > From: Brian Erickson <[EMAIL PROTECTED]> > Subject: Customer Recommendation Query snip > We are using MySQL version 3.23. There are approximately > 500 unique rows in the 'actions' table and 2,000,000 rows > in the member_actions table, with 3,000+ actions being > recorded at any given time. snip > So, our question is whether or not this is feasible with a > one/few query approach, or if this is something that > should be accomplished with something similar to the > approach above? Can anyone provide a good start for us? I think a good start would be to consider the possiblity of upgrading from 3.23 to 5.0.18/19. I'm sure there is alot more functionality available for you to utilise then - not just in SELECT statements either. Regards Keith In theory, theory and practice are the same; in practice they are not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock'
On Tue, 28 Mar 2006, Áquila Chaves wrote:
> To: mysql@lists.mysql.com
> From: Áquila Chaves <[EMAIL PROTECTED]>
> Subject: error: 'Can't connect to local MySQL server through socket
> '/tmp/mysql.sock'
>
> - When I execute the command "mysqld_safe":
> [EMAIL PROTECTED] mysql]# bin/mysqld_safe --user=mysql &
>[1] 7298 [EMAIL PROTECTED] mysql]# Starting mysqld daemon
>with databases from /var/lib/mysql /* {processing...
>the cursor is blinking but I don't have any answer...
>So, I press } */ [EMAIL PROTECTED] mysql]#
>
> - The log message is:
> 060323 16:51:11 mysqld started
> 060323 16:51:11 InnoDB: Started; log sequence number 0 43655
> 060323 16:51:11 [Note] /usr/local/mysql/bin/mysqld: ready for
> connections.
> Version: '5.0.19-standard' socket: '/var/lib/mysql/mysql.sock' port:
> 3306 MySQL Community Edition - Standard (GPL)
So you have got mysqld running OK, and waiting for you to
connect to it on the socket /var/lib/mysql/mysql.sock
> - Aparently it's OK. But when I execute the command below
>occurs the following error: [EMAIL PROTECTED] mysql]#
>bin/mysqladmin version bin/mysqladmin: connect to
>server at 'localhost' failed error: 'Can't connect to
>local MySQL server through socket '/tmp/mysql.sock'
>(2)' Check that mysqld is running and that the socket:
>'/tmp/mysql.sock' exists!
You are trying to connect to the mysql server on a different
socket than the one mysqld is listening on for connections.
You need to tell mysqladmin to connect to the socket that
mysqld is listening to. In this case it is
/var/lib/mysql/mysql.sock.
You could do this with:
/bin/mysqladmin --socket=/var/lib/mysql/mysql.sock
That should work. If you have set a password you will need
to use that as well.
You could also set the --socket value in /etc/my.cnf by
adding a few lines to it like this:
Note that directives in the my.cnf file are the same as on
the command-line, but without the preceeding -- double-dash.
# /etc/my.cnf
#
# The following options will be passed to all MySQL clients
[client]
socket = /var/lib/mysql/mysql.sock
port = 3306
HTH
Keith
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about autoincrement ID
On Thu, 23 Mar 2006 [EMAIL PROTECTED] wrote: > > To: saf <[EMAIL PROTECTED]> > From: [EMAIL PROTECTED] > Subject: Re: Question about autoincrement ID > > One important thing to remember: You should not let UI > design requirements dictate your DB design. Most > developers who design the database just to support the > front end up regretting the decision. Those designs are > either impossible to extend or impossible to manage or > both. You should always design for an efficient database > and adjust your retrieval methods to present the data in > the manner requested, not the other way around. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine IMHO I think the database is the central core of a DB driven website. Therefore it should be the first thing designed in a DB driven website. Everything else in a DB driven site should then be built around the expected functionality of the database. So, if one starts out by designing a database (and it's server(s)) with optimum performance and upgradability as design goals, you won't go to far wrong. Just my 2c. Keith Roberts In theory, theory and practice are the same; in practice they are not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot select the database
>From the 5.0.x manual
How to create user accounts:
The next examples create three accounts and give them access
to specific databases. Each of them has a username of custom
and password of obscure.
To create the accounts with GRANT, use the following
statements:
shell> mysql --user=root mysql
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON bankaccount.*
-> TO 'custom'@'localhost'
-> IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON expenses.*
-> TO 'custom'@'whitehouse.gov'
-> IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON customer.*
-> TO 'custom'@'server.domain'
-> IDENTIFIED BY 'obscure';
The three accounts can be used as follows:
The first account can access the bankaccount database, but
only from the local host.
The second account can access the expenses database, but
only from the host whitehouse.gov.
The third account can access the customer database, but only
from the host server.domain.
So you need 1 accout to access your database from localhost,
and another account (with the same password to avoid
confusion) to access your database from any other domain
apart from localhost.
*
You also need to read this Charles, then you will know how
mysql checks who is authorised to connect to the server.
http://dev.mysql.com/doc/refman/4.1/en/connection-access.html
*
HTH
Keith
In theory, theory and practice are the same;
in practice they are not.
On Tue, 21 Mar 2006, Charles Gambrell wrote:
> To: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
> From: Charles Gambrell <[EMAIL PROTECTED]>
> Subject: Re: Cannot select the database
>
> Thanks for the links.
>
> The link on privilge had a link to a discussion of "access denied."
> http://dev.mysql.com/doc/refman/4.1/en/access-denied.html
>
> Where I read -
>
> "If you have access problems with a Perl, PHP, Python, or ODBC
> program, try to connect to the server with mysql -u user_name db_name
> or mysql -u user_name -pyour_pass db_name. If you are able to
> connect using the mysql client, the problem lies with your program,
> not with the access privileges. (There is no space between -p and the
> password; you can also use the --password=your_pass syntax to specify
> the password. If you use the -p --passwordoption with no password
> value, MySQL prompts you for the password.)"
>
> Well, that describes my situation and when I attempt at the command
> line of the host running MySQL to connect by "mysql -u mysql mydb" I
> get the error message "error 1044 (42000) Access denied for user "@"
> localhost to database "mydb."
>
> So I guess I do have an access problem the user "mysql" weather it is
> on the localhost or throught PHP.
>
> Now to figure out how to give the right permission(s) to the user. I
> know that the mysql user can connect to the "test" database from the
> command line or through a browser and PHP. At least it looks like I
> am narrowing the problem down.
>
> host | user | db
>
> %| | mynewdb
> %| mysql | mynewdb
> %| | test
> %| | test\_%
>
>
> This looks like it means that from any host the mysql user can access
> the mynewdb database. But it must not mean that. What am I missing?
> And I don't understand "test\_%" but I know I can connect to and open
> the test database.
>
> Charles
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error with mysqld_safe
On Tue, 21 Mar 2006, Áquila Chaves wrote: > [ERROR] > /usr/local/mysql/bin/mysqld: Can't create/write to file > '/var/run/mysqld/mysqld.pid' (Errcode: 13) 060321 12:12:22 > [ERROR] Can't start server: can't create PID file: > Permission denied 060321 12:12:22 mysqld ended Check the directory access permissions. Does mysql have permission to write the PID file to /var/run/mysqld/ ? Regards Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot select the database
Take a look at these links Charles. http://dev.mysql.com/doc/refman/4.1/en/privilege-system.html http://dev.mysql.com/doc/refman/4.1/en/user-account-management.html You don't need to fully understand the mysql privilege access system to set up users, but it would help in the long term. Regards Keith In theory, theory and practice are the same; in practice they are not. On Tue, 21 Mar 2006, Charles Gambrell wrote: > To: mysql@lists.mysql.com > From: Charles Gambrell <[EMAIL PROTECTED]> > Subject: Cannot select the database > > I know this must be a simple issue and maybe I am asking it in the > wrong place, so if the latter is the case, please direct me to the > correct place. > > I am getting my feet wet with MySQL. I have installed running on > WhiteBox linux and have created a datebase with one table and put some > date in it. All seems to work fine form the the command line. > > I am now trying to connect throw a browser on a different workstation > using PHP. I seem to be able to connect ok. I can select the "test" > database that ships with MySQL but when I try to select the database > I created the select fails. > > I am guessing this is some kind of premissions issue, that I am not > understanding yet. > > I have looked some at the db table i the mysql database and I see this - > > host | user | db > > %| | mynewdb > %| mysql | mynewdb > %| | test > %| | test\_% > > Where do I need to be looking to see the problem and better yet, > understanding the problem. > > Thanks for the help. > > Charles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Discussion: the efficiency in using foreign keys
I'd also like to add that if you have a choice between doing something in the application logic vs. MySQL's SQL statements, then it is probably more efficient to use SQL statements, constructs and related functions, to get the job done, rather than in the application logic if possible. Keith In theory, theory and practice are the same; in practice they are not. On Mon, 20 Mar 2006, Martijn Tonies wrote: > To: mysql@lists.mysql.com > From: Martijn Tonies <[EMAIL PROTECTED]> > Subject: Re: Discussion: the efficiency in using foreign keys > > Hi, > > > This is a fundamental concept in RDBMS: the use of foreign keys in > > database design. > > > > I'd just like to poll the community here, on whether it is a best > > practice, or practically essential to 'link' related tables by use of > > foreign keys. > > > > For myself, I usually do all the validity checking when adding a new > > record that references a record id from another table. I understand that > > this may not be efficient because it becomes 2 database calls (and db > > calls are expensive in high-load environments). > > > > What are the advantages/ disadvantages in using foreign keys? In MySQL, > > this means one cannot use MyISAM. Do you place a lot of triggers as well? > > When it comes to referential constraints, the answer is simple: > ALWAYS put them on the database. > > Anyway who answers differently either never had to recover > a database that was trashed by the lack of integrity constraints > or has no ide what he's talking about. > > Most probably, this statement will get me tons of e-mail again ;-) > > Martijn Tonies > Database Workbench - development tool for MySQL, and more! > Upscene Productions > http://www.upscene.com > My thoughts: > http://blog.upscene.com/martijn/ > Database development questions? Check the forum! > http://www.databasedevelopmentforum.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Discussion: the efficiency in using foreign keys
IMHO I think you will find that there is a balance between
the speed of opening and reading/writing several related
smaller tables connected by FK's, rather than one mega-sized
gigantic table.
How do you normalise a table without using FK's.
Your right, MySQL does not currently do any checking for
FK's, but this does not mean that you cannot still use them
in MyISAM tables.
Eg.
/* table to store quiz questions */
CREATE TABLE `quiz_question` (
`ID` mediumint UNSIGNED NOT NULL auto_increment,
`question_text` text NOT NULL,
PRIMARY KEY `ID` (`ID`)
) TYPE=MyISAM AUTO_INCREMENT=1;
/* table to store quiz answers */
CREATE TABLE `quiz_answer` (
`ID` mediumint UNSIGNED NOT NULL auto_increment,
`answer_text` text NOT NULL,
`status` enum('wrong', 'right') NOT NULL,
`questionID` mediumint UNSIGNED NOT NULL default '0',
PRIMARY KEY `ID` (`ID`),
KEY `questionID` (`questionID`)
) TYPE=MyISAM AUTO_INCREMENT=1;
/* table to track quiz questions with user answers */
CREATE TABLE `quiz_result` (
`ID` mediumint UNSIGNED NOT NULL auto_increment,
`user` char(32) NOT NULL default '',
`questionID` mediumint UNSIGNED NOT NULL default '0',
`answerID` mediumint UNSIGNED NOT NULL default '0',
PRIMARY KEY `ID` (`ID`),
KEY `questionID` (`questionID`),
KEY `answerID` (`answerID`)
) TYPE=MyISAM AUTO_INCREMENT=1;
So in the quiz_result table above questionID is a
column holding the primary key of each question_text column
in the quiz_question table. It is a foreign key.
answerID is a foreign key that points to the primary
key of the answer submitted by the user doing the quiz.
When the user has finished doing the quiz, the quiz_result
table is scanned for the user session ID, 'user', and then
the question and the user's chosen answer are picked from
the quiz_question and quiz_answer tables, using the foreign
keys in the result table.
I find it helps me to think of foreign keys as unique
pointers to rows in other related tables.
HTH
Keith
In theory, theory and practice are the same;
in practice they are not.
On Mon, 20 Mar 2006, Foo Ji-Haw wrote:
> To: mysql@lists.mysql.com
> From: Foo Ji-Haw <[EMAIL PROTECTED]>
> Subject: Discussion: the efficiency in using foreign keys
>
> Hi all,
>
> This is a fundamental concept in RDBMS: the use of foreign keys in
> database design.
>
> I'd just like to poll the community here, on whether it is a best
> practice, or practically essential to 'link' related tables by use of
> foreign keys.
>
> For myself, I usually do all the validity checking when adding a new
> record that references a record id from another table. I understand that
> this may not be efficient because it becomes 2 database calls (and db
> calls are expensive in high-load environments).
>
> What are the advantages/ disadvantages in using foreign keys? In MySQL,
> this means one cannot use MyISAM. Do you place a lot of triggers as well?
>
> Thanks.
>
> --
> 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: getting table metadata
If you upgrade to mysql 5.0.x there are also the The INFORMATION_SCHEMA tables that return meta information about tables. AFAIK they may not be available in pre 5.0 versions. Keith In theory, theory and practice are the same; in practice they are not. To unsubscribe from this list, please see detailed instructions already posted at: http://marc.theaimsgroup.com/?l=php-install&m=114138567814319&w=2 On Fri, 17 Mar 2006, Yves Glodt wrote: > To: mysql@lists.mysql.com > From: Yves Glodt <[EMAIL PROTECTED]> > Subject: getting table metadata > > Hi, > > is it possible to get information about tables by doing queries on some > system > tables? I am using mysql version 4.1.11 on debian sarge. > > In my case I need to know which columns (names and types) a table has, and > how > the primary key is defined. > > How can I get this information out of mysql by only using sql ? > > Best regards, > Yves > > -- > 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: E/R Tool
So did I Jim, on SuSE 9.2 pro. So I downloaded the windows versions to my laptop instead. Keith In theory, theory and practice are the same; In practice they are not. On Tue, 14 Mar 2006, Jim Douglas wrote: > To: [EMAIL PROTECTED] > From: Jim Douglas <[EMAIL PROTECTED]> > Subject: Re: E/R Tool > > From: [EMAIL PROTECTED] > > To: mysql@lists.mysql.com > > Subject: Re: E/R Tool > > Date: Mon, 13 Mar 2006 22:40:34 + (GMT) > > > > > > OK TY Peter. > > > > I have downloaded both DBDesigner and MySQL Workbench. > > > > Looking forward to workbench reaching GA status. > > > > Regards > > > > Keith > > > > In theory, theory and practice are the same; > > In practice they are not. > > > > On Mon, 13 Mar 2006, Peter Brawley wrote: > > > > > To: [EMAIL PROTECTED] > > > From: Peter Brawley <[EMAIL PROTECTED]> > > > Subject: Re: E/R Tool > > > > > > [EMAIL PROTECTED] wrote: > > > > Excellent DB design tool Peter. Thanks for posting the > > > > information. > > > > > > > > http://www.fabforce.net/dbdesigner4/ > > > > > > > It has become MySQL Workbench > > > (http://forums.mysql.com/read.php?10,73820,73820#msg-73820), but > > > it > > isn't > > > production-ready yet. > > > > > > PB > > > > > > > > > -- > > > No virus found in this outgoing message. > > > Checked by AVG Free Edition. > > > Version: 7.1.375 / Virus Database: 268.2.1/279 - Release Date: > > > 3/10/2006 > > > > > > > > > -- > > > 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] > > > > > Great looking tool I tried to install and got a lot of failed > dependencies on Fedora Core 4. > > Will a version for FC 4/5 be available anytime soon? > > Jim > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql5 options file location
I get the same results as you do using this: karsites:/usr/local/mysql-5.0.18/bin # ./my_print_defaults mysqld karsites:/usr/local/mysql-5.0.18/bin # ./my_print_defaults client --socket=/var/lib/mysql/mysql.sock --port= karsites:/usr/local/mysql-5.0.18/bin # It seems like the last parameter passed to my_print_defaults tells my_print_defaults to get that particular section from the/etc/my.cnf file and print it out. However, you can pass a parameter to my_print_defaults, to tell it which my.cnf file to check. So doing this: karsites:/usr/local/mysql-5.0.18/bin # ./my_print_defaults --defaults-file=/usr/local/mysql-5.0.18/my.cnf mysqld --basedir=/usr/local/mysql-5.0.18 --server-id=1 --skip-name-resolve --skip-locking --set-variable=key_buffer=16M --set-variable=max_allowed_packet=1M --set-variable=table_cache=64 --set-variable=sort_buffer=512K --set-variable=net_buffer_length=8K --set-variable=myisam_sort_buffer_size=8M --log=5-0-18.log --log-bin=laptop-bin --log-error=5-0-18.error-log --log-queries-not-using-indexes --log-slow-admin-statements --log-slow-queries=5-0-18.slow-log --log-warnings karsites:/usr/local/mysql-5.0.18/bin # Does actually return the correct my.cnf file details. You can also use the -c /usr/local... shorthand option to tell my_print_defaults which my.cnf to look at. 'my_print_defaults --help' will return all the available options you can use. This is actually a Linux ELF file, and not a shell script Alex. That's something I have also just learned, so I'm pleased you mentioned it, because I was having the same problem, until I checked the options available, using --help. Are there any other problems you have encountered using this type of my.cnf setup? Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Alex Moore wrote: > To: mysql@lists.mysql.com > From: Alex Moore <[EMAIL PROTECTED]> > Subject: Re: mysql5 options file location > > On Mon, 13 Mar 2006 23:05:30 + (GMT) > [EMAIL PROTECTED] wrote: > > > Exactly what are the problems you are having with the server > > specific my.cnf file? > > Sorry, I thought that I had described the problem. A quick example was > 'my_print_defaults mysqld' returning only the options defined in the > global file. None of the options from the server-specific my.cnf are > returned. This is not the way 4.1, and probably earlier, works. > > Thanks, > > Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql5 options file location
In your global /etc/my.cnf fle, you should be able to split that into sections for each client program that you want to set specific options for. Eg. # /etc/my.cnf # MySQL client program configuration file # last updated 2006-03-05 # mysqld server configuration file lives in # /usr/local/mysql-/my.cnf #--- # mysql client program configuration options #--- [mysql] no-auto-rehash # needed for security - to stops multiple deletes/updates # without a where clause safe-updates # # The following options will be passed to all MySQL clients [client] socket = /var/lib/mysql/mysql.sock port = 7000 # [myisamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M # [mysqldump] quick set-variable = max_allowed_packet=16M # # available programs/scripts are: (from 4.0.21 version) #my_print_defaults mysqladmin #myisamchk mysqlbinlog #myisamlog mysqlbug - n/a #myisampack mysqlcheck #mysql mysqld #mysql_convert_table_format - .plmysqld_multi #mysql_find_rows mysqldump #mysql_fix_privilege_tables n/a mysqlhotcopy - .pl #mysql_install_dbmysqlimport #mysql_setpermission - .pl mysqlshow #mysql_zap mysqltest #mysqlaccess - .pl safe_mysqld # # end of mysql client program configurations # /etc/my.cnf Take a look at the man pages for the client programs, as well as mysqld_safe and mysqld, in /opt/csw/mysql5/man/man1/ Most of the options for client programs are listed there. Exactly what are the problems you are having with the server specific my.cnf file? AFAIK it should be in basedir, and not normally in datadir. The log files are OK to have in datadir - that's the default place SuSE Linux puts them. HTH Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Alex Moore wrote: > To: mysql@lists.mysql.com > From: Alex Moore <[EMAIL PROTECTED]> > Subject: Re: mysql5 options file location > > On Mon, 13 Mar 2006 16:52:03 + (GMT) > [EMAIL PROTECTED] wrote: > > > > > Hi Alex. It seems that mysqld and all the client > > programs insist on reading /etc/my.cnf first. > > > > To overide this behaviour for a particular instance of > > mysqld you need to pass the --defaults-file option as the > > FIRST parameter to mysqld_safe or mysqld if you are not using > > mysqld_safe. > > Yes, I understand everything that you said Keith and have used the > options file logic to my advantage as well. Thanks for the full > description. > > What is not working for me is that the server-specific file does not > appear to be built into all of the objects like it was in 4.1. > > For example, my.cnf is in basedir. my.cnf has a [mysqld] group that > defines many options like various logging selections and tuning. I do > not want those options in a global file since I am not supposed to > write to /etc/. I am using a directory structure similar > to /usr/local/mysql5, but it is actually /opt/csw/mysql5. > > The server-specific options are read on mysqld_safe at startup, as > evidenced by `mysqladmin variables`. > > Without a /etc/my.cnf file, 'my_print_defaults mysqld' prints nothing. > With a /etc/my.cnf file, 'my_print_defaults mysqld' prints only the > options in /etc/my.cnf. This is very different from 4.1 and causing me > a lot of headaches and is probably my biggest issue with 5.0. > > I could go on about the utilities that do not work without a global > options file. > > Another clue about this change from 4.1 is the `mysqld --verbose > --help` output not listing a server-specific options file on 5.0 > > I just wondered if I did something wrong or if I can just not use some > utilities in 5.0 > > The situation with 5.0 and the options file is really much bigger than > what I have described. I have big problems with the 5.0 location of > the server-specific options file moving from datadir to basedir. But > that is another discussion. > > Thanks, > > Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: E/R Tool
OK TY Peter. I have downloaded both DBDesigner and MySQL Workbench. Looking forward to workbench reaching GA status. Regards Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Peter Brawley wrote: > To: [EMAIL PROTECTED] > From: Peter Brawley <[EMAIL PROTECTED]> > Subject: Re: E/R Tool > > [EMAIL PROTECTED] wrote: > > Excellent DB design tool Peter. Thanks for posting the information. > > > > http://www.fabforce.net/dbdesigner4/ > > > It has become MySQL Workbench > (http://forums.mysql.com/read.php?10,73820,73820#msg-73820), but it isn't > production-ready yet. > > PB > > > -- > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.1.375 / Virus Database: 268.2.1/279 - Release Date: 3/10/2006 > > > -- > 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: E/R Tool
Excellent DB design tool Peter. Thanks for posting the information. http://www.fabforce.net/dbdesigner4/ I have bookmarked that, and will be checking that out soon! Regards Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Peter M. Groen wrote: > To: mysql@lists.mysql.com > From: Peter M. Groen <[EMAIL PROTECTED]> > Subject: Re: E/R Tool > > On Monday 13 March 2006 17:37, Vinay wrote: > > Hello , > > Is there a mysql or any other tool that generates a > > E/R diagram using an existing mysql database. > > > > > > Thank You > > Vinay > > Try fabforce for DbDesigner4MySQL. Very good tool. > > -- > Peter M. Groen > Open Systems Development > Klipperwerf 12 > 2317 DZ Leiden > T : +31-(0)71-5216317 > M : +31-(0)6-29563390 > E : [EMAIL PROTECTED] > Skype : peter_m_groen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: E/R Tool
http://www.thekompany.com/products/dataarchitect/ There are free evaluation copies to download, and it's not that dear to buy a copy, very good value actually. I'm not sure if there is a free Linux version that theKompany have released as well. Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Vinay wrote: > To: mysql@lists.mysql.com > From: Vinay <[EMAIL PROTECTED]> > Subject: E/R Tool > > > > > Hello , > Is there a mysql or any other tool that generates a E/R diagram using an > existing mysql database. > > > Thank You > Vinay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql5 options file location
Hi Alex. It seems that mysqld and all the client programs insist on reading /etc/my.cnf first. To overide this behaviour for a particular instance of mysqld you need to pass the --defaults-file option as the FIRST parameter to mysqld_safe or mysqld if you are not using mysqld_safe. --defaults-extra-file=path The name of an option file to be read in addition to the usual option files. If given, this option must be first. --defaults-file=path The name of an option file to be read instead of the usual option files. If given, this option must be first. --no-defaults Do not read any option files. If given, this option must be first. More options are found in /usr/local/mysql-5.0.18/man/man1/mysqld_safe.1 I found this behaviour very annoying at first. But I now use this to my advantage. I have split my my.cnf file like this: /etc/my.cnf only has parameters used by mysql client programs. the my.cnf that lives in the mysqld installation directory only contains directives pertinent to that particular version of mysqld, and nothing there for the client programs. That way you have total control over all the parameters passed to all client programs in one central place, /etc/my.cnf. You do not have to worry about mysqld reading the wrong parameters for its invocation, because you have a seperate my.cnf just for that version of mysqld. I have written the following script to start a particular version of mysqld. This lives in /usr/local/mysql-/bin/ #! /bin/sh # # start the MySQL database server /usr/local/mysql-5.0.18/bin/mysqld \ --defaults-file=/usr/local/mysql-5.0.18/my.cnf \ --port= \ --socket=/var/lib/mysql/mysql.sock \ --pid=/var/lib/mysql/laptop.pid \ --user=mysql \ --datadir=/var/lib/mysql & And this script gets called from /etc/init.d/boot.local when the machine boots up. /etc/init.d/halt.local calls the following script to shutdown the mysqld server gracefully. #! /bin/sh # # stop the MySQL database server /usr/local/mysql-5.0.18/bin/mysqladmin shutdown \ -usqlsuperuser -p \ --socket=/var/lib/mysql/mysql.sock The other advantage of passing parameters on the command-line to mysqld is that you can actually see, using a visual process manager like http://www.student.nada.kth.se/~f91-men/qps/ * if mysqld is running OK * what parameters you passed to mysqld, eg the port, socket, datadir etc, each mysqld is using * how many different versions of mysqld you have running I find this very helpfull when running two versions of mysqld at a time, eg testing a newer version against an already installed version, before removing the older version. HTH Keith In theory, theory and practice are the same; In practice they are not. On Mon, 13 Mar 2006, Alex Moore wrote: > To: mysql@lists.mysql.com > From: Alex Moore <[EMAIL PROTECTED]> > Subject: mysql5 options file location > > I am building mysql5 latest from source on Solaris. > > The location of the options file is very confusing and does not work > according to the online documentation. For example, I have --basedir > of /opt/csw/mysql5 and --datadir of /opt/csw/mysql5/var. If I put > my.cnf in datadir or in basedir, the file is not used. I am using > mysqld_safe to start mysqld. my.cnf options will only work if I put > the file in /etc/ > > './libexec/mysqld --verbose --help' returns: > Default options are read from the following files in the given order: > /etc/my.cnf ~/.my.cnf > > This is very different from my mysql4 builds from source, which include > the documented server-specific file listed after /etc/my.cnf > > How can I get mysql5 to use a server-specfic options file? Am I > missing a configure option or defines for mysql5? > > Thanks, > > Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checking for good update
Thankyou for that explanation Michael.
I shall look into using that construct in my own code now!
Apologies to fbsd_user for my previous comments on his
coding style.
Regards
Keith Roberts
In theory, theory and practice are the same;
In practice they are not.
On Mon, 13 Mar 2006, Michael Stassen wrote:
> To: [EMAIL PROTECTED]
> From: Michael Stassen <[EMAIL PROTECTED]>
> Subject: Re: Checking for good update
>
> [EMAIL PROTECTED] wrote:
> > On Sun, 12 Mar 2006, Michael Stassen wrote:
> > >
> > > [EMAIL PROTECTED] wrote:
> > >
> > > > looks a bit strange to me.
> > > >
> > > > > $result = mysql_query($query) or die('Query couldn\'t
> > > > > executed:'.mysql_error());
> > > >
> > > > please try something like this:
> > >
> > > Why? There's nothing wrong with the above statement.
> >
> > I've never seen logic like that before. It looks to me like fbsd_user
> > is trying to use the OR operator outside an if statement.
> >
> > Is the mentioned in the php manual somewhere Michael?
> >
> > > > I've not tested this - but it looks like you are mixing
> > > > sending the
> > > > mysql query and testing for the result of the query at the
> > > > same time,
> > > > which AFAIK is not possible.
> > >
> > > You should try it. It works just fine, and isn't the problem.
> > > The
> > > problem is that you cannot treat the result of an UPDATE as if it
> > > were a
> > > SELECT.
> >
> > Regards
> > Keith Roberts
>
> Yes, this is documented. It's also standard practice (in perl and C as
> well).
>
> OR is not part of an if statement, it is a logical operator.
> <http://www.php.net/manual/en/language.operators.logical.php> "A or B"
> has a value, true or false, depending on the values of A and of B. In
> fact, if A is true, then "A or B" is certainly true, so there's no need to
> look at B at all. This short-circuit evaluation, combined with the fact
> that every assignment returns the assigned value
> <http://www.php.net/manual/en/language.expressions.php>, makes a statement
> like this possible.
>
> $result = mysql_query($query) or die('Query error:'.mysql_error());
>
> First, the function mysql_query() is called. Its return value is assigned
> to $result, *and* returned as the return value of the assignment operator
> (=). Now we know A. If mysql_query succeeded, its return value (A)
> evaluates as true, so the or operation must be true, so no need to look at
> B. If, on the other hand, A is false (mysql_query failed), we must
> evaluate B to determine the value of the "or" expression. Of course, to
> determine the value of B, we have to call the referenced function, die().
>
> Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checking for good update
On Sun, 12 Mar 2006, Michael Stassen wrote:
> To: [EMAIL PROTECTED]
> From: Michael Stassen <[EMAIL PROTECTED]>
> Subject: Re: Checking for good update
>
> [EMAIL PROTECTED] wrote:
> > looks a bit strange to me.
> >
> > > $result = mysql_query($query) or die('Query couldn\'t
> > > executed:'.mysql_error());
> >
> > please try something like this:
>
> Why? There's nothing wrong with the above statement.
I've never seen logic like that before. It looks to me like
fbsd_user is trying to use the OR operator outside an if
statement.
Is the mentioned in the php manual somewhere Michael?
> > I've not tested this - but it looks like you are mixing sending the
> > mysql query and testing for the result of the query at the same time,
> > which AFAIK is not possible.
>
> You should try it. It works just fine, and isn't the problem. The
> problem is that you cannot treat the result of an UPDATE as if it were a
> SELECT.
Regards
Keith Roberts
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Debuggers
Is this a free hosting package David B? That's a bit naughty I think - just removing your database without warning you about it first. At least you would have had time to make a backup copy. Keith In theory, theory and practice are the same; In practice they are not. On Sun, 12 Mar 2006, David Blomstrom wrote: > To: mysql@lists.mysql.com > From: David Blomstrom <[EMAIL PROTECTED]> > Subject: MySQL Debuggers > > I recently discovered that all my websites were wrecked. I spent several > hours trying to troubleshoot it, until I finally discovered that my database > was missing. I asked my webhost about it, and they told me they deactivated > it because it was "overwhelming the server." > > Someone told me about a software program called Mytop that can be used to > debug MySQL. However, it looks way too complex for me. I just wondered if > anyone on this list is aware of other, more user-friendly MySQL debuggers. > > Thanks. > > > > - > Yahoo! Mail > Use Photomail to share photos without annoying attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checking for good update
Maybe I need to read the copy of php pocket reference I have
to David - LOL.
Keith
On Sun, 12 Mar 2006 [EMAIL PROTECTED] wrote:
> To: mysql@lists.mysql.com
> From: [EMAIL PROTECTED]
> Subject: Re: Checking for good update
>
>
> looks a bit strange to me.
>
> > $result = mysql_query($query) or die('Query couldn\'t
> > executed:'.mysql_error());
>
> please try something like this:
>
> // build the query - (that's OK)
> $query = "UPDATE members SET email_verified='X' WHERE
> logon_id='" .$logonid. "'";
>
> // send the query to the server - save the result resource
> $res = mysql_query($query);
>
> // test for the result of the above query
> if(!$res)
> {
> // stop the script if the result is not valid
> die('Query couldn\'t be executed:'.mysql_error());
> }
>
> // process a valid result
> $row = mysql_fetch_array($res)
>
> if (mysql_num_rows($res) == 1)
> {
> // the user id and password match,
> print("User id on db");
> }
> else
> {
> //$errorMessage = 'Sorry, wrong user id / password';
> print("Sorry, wrong user id / password");
> }
>
> I've not tested this - but it looks like you are mixing sending the
> mysql query and testing for the result of the query at the same time,
> which AFAIK is not possible.
>
> Maybe you need to get a simple introductory book on php, such as O'reillys
> php pocket reference, ISBN 0596-00402-8.
>
> Regards
>
> Keith
>
> In theory, theory and practice are the same;
> In practice they are not.
>
> On Sun, 12 Mar 2006, fbsd_user wrote:
>
> > To: Mysql
> > From: fbsd_user <[EMAIL PROTECTED]>
> > Subject: Checking for good update
> >
> > Using this code I get this error message.
> >
> > Warning: mysql_num_rows(): supplied argument is not a valid MySQL
> > result resource in /usr/local/www/data/mls_verifyemail.php on line
>
> Probably because you are not sending a valid query to the server,
> you will not be getting a valid result resource back from the server.
>
> > What code should I use to check if the update worked or not?
> >
> >
> > $query = "UPDATE members SET email_verified='X' WHERE
> > logon_id='".$logonid."'";
> >
> > $result = mysql_query($query) or die('Query couldn\'t
> > executed:'.mysql_error());
> >
> > if (mysql_num_rows($result) == 1)
> > {
> > // the user id and password match,
> > print("User id on db");
> > }
> > else
> > {
> > //$errorMessage = 'Sorry, wrong user id / password';
> > print("Sorry, wrong user id / password");
> > }
>
> --
> 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: Checking for good update
looks a bit strange to me.
> $result = mysql_query($query) or die('Query couldn\'t
> executed:'.mysql_error());
please try something like this:
// build the query - (that's OK)
$query = "UPDATE members SET email_verified='X' WHERE
logon_id='" .$logonid. "'";
// send the query to the server - save the result resource
$res = mysql_query($query);
// test for the result of the above query
if(!$res)
{
// stop the script if the result is not valid
die('Query couldn\'t be executed:'.mysql_error());
}
// process a valid result
$row = mysql_fetch_array($res)
if (mysql_num_rows($res) == 1)
{
// the user id and password match,
print("User id on db");
}
else
{
//$errorMessage = 'Sorry, wrong user id / password';
print("Sorry, wrong user id / password");
}
I've not tested this - but it looks like you are mixing sending the
mysql query and testing for the result of the query at the same time,
which AFAIK is not possible.
Maybe you need to get a simple introductory book on php, such as O'reillys
php pocket reference, ISBN 0596-00402-8.
Regards
Keith
In theory, theory and practice are the same;
In practice they are not.
On Sun, 12 Mar 2006, fbsd_user wrote:
> To: Mysql
> From: fbsd_user <[EMAIL PROTECTED]>
> Subject: Checking for good update
>
> Using this code I get this error message.
>
> Warning: mysql_num_rows(): supplied argument is not a valid MySQL
> result resource in /usr/local/www/data/mls_verifyemail.php on line
Probably because you are not sending a valid query to the server,
you will not be getting a valid result resource back from the server.
> What code should I use to check if the update worked or not?
>
>
> $query = "UPDATE members SET email_verified='X' WHERE
> logon_id='".$logonid."'";
>
> $result = mysql_query($query) or die('Query couldn\'t
> executed:'.mysql_error());
>
> if (mysql_num_rows($result) == 1)
> {
>// the user id and password match,
>print("User id on db");
> }
> else
> {
>//$errorMessage = 'Sorry, wrong user id / password';
>print("Sorry, wrong user id / password");
> }
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql workbench and download?
It's not on the mysql site yet Greg. >From [EMAIL PROTECTED] Sat Mar 4 23:00:04 2006 To: [EMAIL PROTECTED] From: Alfredo Kengi Kojima <[EMAIL PROTECTED]> Subject: ANNC: MySQL Workbench 1.0.5 beta released MySQL Workbench 1.0.5 beta has been released. MySQL Workbench is a database design tool for MySQL. MySQL objects such as tables, routines and views can be created and edited and their visual representation on the canvas enables one to quickly understand and work efficiently with complex and simple database schemas. Feature highlights: - Reverse engineering of existing MySQL databases - Import DBDesigner4 models - Synchronize edited model with MySQL database - Generate SQL create script file - Printing (Windows) - Powrefull scripting and plugin interface. Plugins can be written in several languages, such as Lua, PHP, Java and Python. - Fast, OpenGL based graphical canvas - Quickly accessible "Overview" mode, which zooms out the whole canvas to reveal a more general view. An OpenGL capable video card with at least 32MB of memory is needed to use MySQL Workbench. Windows binaries are available at: ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-1.0.5-beta-win32.msi ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-1.0.5-beta-win32.msi.md5 ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-1.0.5-beta-win32-noinstall.zip ftp://ftp.mysql.com/pub/mysql/download/mysql-workbench-1.0.5-beta-win32-noinstall.zip.md5 Linux/Gtk+ binaries are available at: mysql-workbench-1.0.5beta-1.suse93.i586.rpm mysql-workbench-1.0.5beta-1.suse93.i586.rpm.md5 mysql-workbench-1.0.5beta-1.suse93.src.rpm mysql-workbench-1.0.5beta-1.suse93.src.rpm.md5 mysql-workbench-1.0.5beta-linux-i386.tar.gz mysql-workbench-1.0.5beta-linux-i386.tar.gz.md5 The SuSE RPM or the tar.gz should work on most recent distributions. Binaries for more distributions and architectures will follow. Mac OS X binaries will be available soon briefly. Sources: mysql-workbench-1.0.5beta.tar.gz mysql-workbench-1.0.5beta.tar.gz.md5 Changes since the last alpha version: All platforms - Significant speed improvements in the Generic Canvas - Auto arrange elements - Markers - Switchable grid display - Complete database <-> synchronization - Reverse engineering is complete - New feature: plug-ins, which are scripts to be used for certain tasks in WB- DBDesigner4 import Windows - Significantly faster model load - Export model as image (png) - Printing with preview - Copy Region as Image - Full GRT integration (including tabbed shell), supported for scripting, are now: Lua, Python - Model properties page - Table options editing - BUG when deleting objects is still present Linux - Added several commands for layouting (send back/front etc) - Object properties tab Enjoy! -- Alfredo Kojima, GUI Developer MySQL AB, www.mysql.com Buenos Aires, Argentina Are you MySQL certified? www.mysql.com/certification On Fri, 10 Mar 2006, Gregory Machin wrote: > To: "mysql@lists.mysql.com" > From: Gregory Machin <[EMAIL PROTECTED]> > Subject: mysql workbench and download? > > Hi where can I download mysql workbench ? The links on the mysql forum dont work ... and i cant seem to find another download ... probably not see the wood for the treats .. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: php and mysql
Mary, you need to know 100% that mysql is actually running and what port or socket it is listening on for connections, before you even try and connect to it. If you call mysqld directly with a script something like: #! /bin/sh # start-mysql-5.0.18 # # start the MySQL database server /usr/local/mysql-5.0.18/bin/mysqld \ --defaults-file=/usr/local/mysql-5.0.18/my.cnf \ --port=7000 \ --socket=/var/lib/mysql/mysql.sock \ --pid=/var/lib/mysql/laptop.pid \ --user=mysql \ --datadir=/var/lib/mysql & Then use a processes monitoring tool such as: http://www.student.nada.kth.se/~f91-men/qps/ This will then show you if mysqld is running OK. It will also show you the parameters in the above script that you passed to mysqld to get it started. You then need to try and connect to mysqld with the mysql monitor program as already mentioned. If you just set the client options in /etc/my.cnf config file, these will be applied globally to all mysql client programs. EG. # /etc/my.cnf # MySQL client program configuration file # last updated 2006-03-05 # mysqld server configuration file lives in # /usr/local/mysql-/my.cnf #--- # mysql client program configuration options #--- [mysql] auto-rehash # needed for security - to stops multiple deletes/updates # without a where clause safe-updates #--- # The following options will be passed to all MySQL clients [client] socket = /var/lib/mysql/mysql.sock port = 7000 #snip #--- # end of mysql client program configurations # /etc/my.cnf More options for your mysqld server can be set in the --defaults-file=/usr/local/mysql-5.0.18/my.cnf These options are additional to the options you pass in the above script. EG #--- # mysqld server configuration options #--- [mysqld] basedir=/usr/local/mysql-5.0.18 ## use for testing multiple instances of mysqld ## these parameters are normally passed to mysqld ## from the start-mysql-5.0.18 script ## ##basedir=/usr/local/mysql-5.0.18 ##port=7005 ##socket=/usr/local/mysql-5.0.18/data/mysql.sock ##pid-file=/usr/local/mysql-5.0.18/data/laptop.pid ##datadir=/usr/local/mysql-5.0.18/data ##user=mysql server-id=1 #skip-networking skip-name-resolve skip-locking set-variable = key_buffer=16M set-variable = max_allowed_packet=1M set-variable = table_cache=64 set-variable = sort_buffer=512K set-variable = net_buffer_length=8K set-variable = myisam_sort_buffer_size=8M # logging options log=5-0-18.log log-bin=laptop-bin log-error=5-0-18.error-log log-queries-not-using-indexes log-slow-admin-statements log-slow-queries=5-0-18.slow-log log-warnings #--- # end of mysqld server configuration file # /usr/local/mysql-/my.cnf Next you will need to check php is using the same socket to connect to mysqld server. Use the phpinfo() function in a php script, to verify this. Obviously, you will need the mysql or mysqli extension loaded for this. HTH Keith Roberts In theory, theory and practice are the same; In practice they are not. On Fri, 10 Mar 2006, Logan, David (SST - Adelaide) wrote: > To: Mary Adel <[EMAIL PROTECTED]> > From: "Logan, David (SST - Adelaide)" <[EMAIL PROTECTED]> > Subject: RE: php and mysql > > > If you still have issues after that, then read > http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html > > Regards > > > --- > ** _/ ** David Logan > *** _/ *** ITO Delivery Specialist - Database > *_/* Hewlett-Packard Australia Ltd > _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] > _/ _/ _/ _/ Desk: +618 8408 4273 > _/ _/ _/_/_/ Mobile: 0417 268 665 > *_/ ** > ** _/ Postal: 148 Frome Street, > _/ ** Adelaide SA 5001 > Australia > invent > --- > > -Original Message- > From: JC [mailto:[EMAIL PROTECTED] > Sent: Friday, 10 March 2006 6:42 AM > To: Mary Adel > Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com > Subject: RE: php and mysql > > On Thu, 9 Mar 2006, Mary Adel wrote: > > > Thanks for al your help and i di that and now i have another error > >
Re: Need help with a Basic Query.
13.1.5.1. Silent Column Specification Changes CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement] ^^^ Regards Keith In theory, theory and practice are the same; In practice they are not. On Wed, 8 Mar 2006, clint lenard wrote: > To: mysql@lists.mysql.com > From: clint lenard <[EMAIL PROTECTED]> > Subject: Need help with a Basic Query. > > Hey Guys, > > I was wondering if I could get some assistance with building a > Simple Import Script using PHP and MySQL. Basically I'm trying to pull > info out of one Table and Insert it into the other Table. > > Can anyone show me a simple example of this? I can figure out how to > do the rest if I had a simple example. > > Thanks! > > - Clint > > -- > 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: Table with multiple primary keys - How
It is not possible to have more than one PRIMARY key per table. Maybe you need to use one PRIMARY key as the main index into the table, then use UNIQUE or KEY which is a synonym for INDEX on the other two columns. This book will help you ALOT with designing tables. It will also teach you how to normalise (refactor) your tables into a more efficient form. http://www.apress.com/book/bookDisplay.html?bID=338 It is also more efficient IMHO to index on integer values if you can, rather than character text. Regards Keith > create table members ( > logon_idMEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, > email_addr varchar(30), ??? > last_name varchar(30), ??? > member_type char(1), > email_verified char(1), > logon_pwvarchar(15), > date_added date, > last_login timestamp, > first_name varchar(30), > addr1 varchar(30), > addr2 varchar(30), > cityvarchar(20), > state varchar(20), > zip varchar(15), > phone_home varchar(15), > phone_officevarchar(15), > phone_cell varchar(15), > mothers_maiden_name varchar(30), > ip_of_useratsignup varchar(16), > primary key(login_id, email_addr, last_name) > ); primary key login_id (login_id), key email_addr (email_addr), key last_name (last_name) ); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I observe mysqld?
Check this GUI out for monitoring running processes. http://www.student.nada.kth.se/~f91-men/qps/ It will show you the connection parameters passed to the mysqld daemon. I've had a terrible time trying to work out whether mysqld was actually running or not.The error messages are not too helpfull when trying to connect to the server. I also found it very usefull to know if mysqld was actually running. Sometimes, I would start mysql via mysqld_safe. But I noticed using qps that mysqld_safe was running, but it failed to start the mysqld! I don't bother using mysqld_safe anymore - apache doesn't need a wrapper script like mysqld_safe. If Apache stops running, then it doesn't matter if mysqld is still running or not - you won't connect to mysqld via a web application. Regards keith On Tue, 7 Mar 2006, Karl Schock wrote: > To: mysql@lists.mysql.com > From: Karl Schock <[EMAIL PROTECTED]> > Subject: Re: How can I observe mysqld? > > Hello Keith, > > > The web applications below will allow you to connect to and > > monitor and administer MySQL databases. > > > > http://www.phpmyadmin.net/home_page/index.php > > it helps a lot when MySQL is running. Thank you. > But when "mysqladmin --user=x --password=y ping" says that > the "connect failed" even phpmyadmin doesn't work anymore. > > Do you know programs like tcpdump to monitor a network interface > or top to watch the processes on a linux host? Is there a similar > program to observe mysqld? I would like to use it. Or can I do > that with phpmyadmin but I don't know it? > > Bye > Karl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I observe mysqld?
The web applications below will allow you to connect to and monitor and administer MySQL databases. http://www.phpmyadmin.net/home_page/index.php Check out the DEMO link in the top menu bar, RHS. Or there is the MySQL Administrator at: http://www.mysql.com/products/tools/administrator/index.html Check out the Tutorial Movie at the top of the RH column. Regards Keith Roberts In theory, theory and practice are the same; In practice they are not. On Tue, 7 Mar 2006, Karl Schock wrote: > To: mysql@lists.mysql.com > From: Karl Schock <[EMAIL PROTECTED]> > Subject: How can I observe mysqld? > > Hello, > > my problem: I run a phpbb-Forum with a mysql database on a linux > server. > Every 5 - 7 days the mysql database runs in a vicious circle: > > "mysqladmin --user=x --password=y ping" says that the "connect failed". > The socket /var/lib/mysql/mysql.sock is there. > "ps aux | grep mysqld" shows me some mysqld-processes. But one of them > is marked as "". > > To find the reason causing the problem I started mysqld with the > "--log"-option and the "--log-error"-option as recommended by others > to me at forums.mysql.com. > Now I have a log-file, but as far as I can judge it there > are no error-messages in the log-file but only innocent queries. > The mysqld.err-log-file is allways empty (0 Bytes). > > What else can I do to observe myslqd? > > If this mailing list is not the right place to ask such > questions please let me know. > > Bye > Karl > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error 1016 : cant open ibd file even though it exists
Still a mystery about your missing InnoDB database files Rithish. I seem to remember there is an option that will allow you to store InnoDB files in their own seperate directory safely, and not in the /var/lib/mysql default directory. One of the many things I like about MyISAM tables is that each database is kept in its own subdirectory, whereas the default for InnoDB databases seems to be mixed in with the mysql log files. This gives me a uneasy feeling, especially as I delete the log files when backing up mysql databases. The moral of this post must certainly be something like: "As the data stored in a company's databases is vital to the operation of the company, when it comes to the integrity and safety of your company's databases, you cannot be too paranoid. Make regular checks and backups of your databases, implementing a staged backup policy, so that whatever happens, you as the DBA, are fully covered for all possible circumstances." Which is obviously, thank God, what you have done Rithish. Kind Regards Keith In theory, theory and practice are the same; In practice they are not. On Tue, 28 Feb 2006, Rithish Saralaya wrote: > To: mysql@lists.mysql.com > From: Rithish Saralaya <[EMAIL PROTECTED]> > Subject: RE: error 1016 : cant open ibd file even though it exists > > Hello Keith. > > The power outage was known before-hand, and the server was > shutdown before the outage happened. The server was > brought up once the power returned. So no UPs intervention > happened here. > > Regards, > Rithish. > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Saturday, February 25, 2006 8:13 PM > To: mysql@lists.mysql.com > Subject: RE: error 1016 : cant open ibd file even though it exists > > > > I do not use Innodb tables at the moment, so all this is > pure speculation. > > Is/was the server connected to a UPS when the power failure > happened? > > If so, did the UPS function properly and do you have any UPS > logs to match against the mysql error log? > > Things to check for would be the time the UPS took over from > the mains supply? > > If there was no UPS in operation, were your InnoDB tables > corrupted when the power failure occured. > > Is it possible for the InnoDB tables to have become so > corrupted that mysql had no alternative but to re-construct > the tables? > > What are your config settings for mysql at boot up time? > Do you have any directives to force checking of InnoDB > tables at boot-time, and if so what are they please? > > As soon as you mentioned 'power failure' the words 'table > corruption' sprang to my mind. > > Also, are there any mysql transaction logs (*.log) you can > check, to see what was happening when the power outage > occured? > > Keith > > In theory, theory and practice are the same; > In practice they are not. > > On Sat, 25 Feb 2006, Rithish Saralaya wrote: > > > To: [EMAIL PROTECTED] > > From: Rithish Saralaya <[EMAIL PROTECTED]> > > Subject: RE: error 1016 : cant open ibd file even though it exists > > > > Hello David. > > > > There was supposed to be a power outage in our office that day. So the > > server was shut down. Finally when the power was back, the machine was > > plugged on. That's all. No file system change. Nothing. > > > > Regards, > > Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error 1016 : cant open ibd file even though it exists
I do not use Innodb tables at the moment, so all this is pure speculation. Is/was the server connected to a UPS when the power failure happened? If so, did the UPS function properly and do you have any UPS logs to match against the mysql error log? Things to check for would be the time the UPS took over from the mains supply? If there was no UPS in operation, were your InnoDB tables corrupted when the power failure occured. Is it possible for the InnoDB tables to have become so corrupted that mysql had no alternative but to re-construct the tables? What are your config settings for mysql at boot up time? Do you have any directives to force checking of InnoDB tables at boot-time, and if so what are they please? As soon as you mentioned 'power failure' the words 'table corruption' sprang to my mind. Also, are there any mysql transaction logs (*.log) you can check, to see what was happening when the power outage occured? Keith In theory, theory and practice are the same; In practice they are not. On Sat, 25 Feb 2006, Rithish Saralaya wrote: > To: [EMAIL PROTECTED] > From: Rithish Saralaya <[EMAIL PROTECTED]> > Subject: RE: error 1016 : cant open ibd file even though it exists > > Hello David. > > There was supposed to be a power outage in our office that day. So the > server was shut down. Finally when the power was back, the machine was > plugged on. That's all. No file system change. Nothing. > > Regards, > Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error 1016 : cant open ibd file even though it exists
Sounds like you have any empty database, from the messages below. Try adding a new dummy database, and some test data. See if you can do some selects on that test data. Keith In theory, theory and practice are the same; In practice they are not. On Fri, 24 Feb 2006, Rithish Saralaya wrote: > To: MySQL general mailing list > From: Rithish Saralaya <[EMAIL PROTECTED]> > Subject: RE: error 1016 : cant open ibd file even though it exists > > Hello. > > The tables were working perfectly fine a week back. The database was created > from a sql file generated through the mysqldump utility. So there was > nothing wrong with the database. This irregularity happened this week > onwards. > > Our system admins tell us that the server was restarted last weekend. When I > dug up the mysql error logs, this was what I found saw. > > == > 060219 5:20:25 InnoDB: Starting shutdown... > 060219 5:20:30 InnoDB: Shutdown completed; log sequence number 19 > 1867461149 > 060219 5:20:30 [Note] /usr/sbin/mysqld: Shutdown complete > > > Memory status: > Non-mmapped space allocated from system: 16589028 > Number of free chunks: 10 > Number of fastbin blocks: 0 > Number of mmapped regions: 19 > Space in mmapped regions: 1472028672 > Maximum total allocated space: 0 > Space available in freed fastbin blocks: 0 > Total allocated space: 16479548 > Total free space: 109480 > Top-most, releasable space:102224 > Estimated memory (with thread stack):1488744676 > > 060219 05:20:30 mysqld ended > > 060219 16:57:48 mysqld started > 060219 16:57:49 [Warning] Asked for 196608 thread stack, but got 126976 > InnoDB: The first specified data file /var/lib/mysql/ibdata1 did not > exist: > InnoDB: a new database to be created! > 060219 16:57:49 InnoDB: Setting file /var/lib/mysql/ibdata1 size to 10 > MB > InnoDB: Database physically writes the file full: wait... > 060219 16:57:50 InnoDB: Log file /var/lib/mysql/ib_logfile0 did not > exist: > new to be created > InnoDB: Setting log file /var/lib/mysql/ib_logfile0 size to 256 MB > InnoDB: Database physically writes the file full: wait... > InnoDB: Progress in MB: 100 200 > 060219 16:58:02 InnoDB: Log file /var/lib/mysql/ib_logfile1 did not > exist: > new to be created > InnoDB: Setting log file /var/lib/mysql/ib_logfile1 size to 256 MB > InnoDB: Database physically writes the file full: wait... > InnoDB: Progress in MB: 100 200 > 060219 16:58:14 InnoDB: Log file /var/lib/mysql/ib_logfile2 did not > exist: > new to be created > InnoDB: Setting log file /var/lib/mysql/ib_logfile2 size to 256 MB > InnoDB: Database physically writes the file full: wait... > InnoDB: Progress in MB: 100 200 > InnoDB: Doublewrite buffer not found: creating new > InnoDB: Doublewrite buffer created > InnoDB: Creating foreign key constraint system tables > InnoDB: Foreign key constraint system tables created > 060219 16:58:28 InnoDB: Started; log sequence number 0 0 > /usr/sbin/mysqld: ready for connections. > Version: '4.1.11-standard-log' socket: '/var/lib/mysql/mysql.sock' > port: > 3306 MySQL Community Edition - Standard (GPL) > = > > So... It shows that the ibdata1 file was recreated... But how can that be > possible? when it was a regular server shutdown and startup? > > Regards, > Rithish. > > > -Original Message- > From: Heikki Tuuri [mailto:[EMAIL PROTECTED] > Sent: Thursday, February 23, 2006 7:52 PM > To: mysql@lists.mysql.com > Subject: Re: error 1016 : cant open ibd file even though it exists > > > Rithish, > > the table definition does not exist in the ibdata file. You have the > .frm file and the .ibd file, but that does not help if the table > definition is not stored in the ibdata file. > > How did you end up in this situation? Did you move .frm and .ibd files > around? Did you recreate the ibdata1 file? > > Best regards, > > Heikki > Oracle Corp./Innobase Oy > InnoDB - transactions, row level locking, and foreign keys for MySQL > InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up > MyISAM tables > http://www.innodb.com/order.php > > . > List: mysql > Subject:error 1016 : cant open ibd file even though it exists > From: "Rithish Saralaya" > Date: 2006-02-22 11:27:44 > Message-ID: ANEAKJJGBMNHIAEBLIAIMEPCECAA.rithish.saralaya () > tallysolutions ! com > [Download message RAW] > > > Hello. > > I get the following error when I
Re: APACHE 2.0 can't connect to MYSQL 5 when using PHP 5.1.2
First you need to make sure that you have a running mysql server for the client mysql program to connect to. If the server is not runing, you will obviously not be able to connect to it. I find the following utility very helpfull - been messsing around with it today. http://www.student.nada.kth.se/~f91-men/qps/ It give a graphical UI, into the running processes. I noticed using qps, that sometimes the mysqld_safe script loads into memory, but does not actually load a mysqld process. Also, version 4.0.21 only spawns one child process, where 5.0.18 starts several processes in memory. I had the RPM 5.0.18 version running, and two other seperate binary distro's of 5.0.18, all on different sockets and ports. I've given up with using mysqld_safe to run the mysqld daemon. It is to unpredictable. qps also shows all the command line arguments passed to mysqld, which shows at a glance which ports and sockets the daemons are listening on. It is included with SuSE 9.2 pro, as an extra package. I think it lives on the DVD's. You can download qps from the link above. Keith In theory, theory and practice are the same; In practice they are not. On Thu, 16 Feb 2006, persant mpote wrote: > To: mysql@lists.mysql.com > From: persant mpote <[EMAIL PROTECTED]> > Subject: APACHE 2.0 can't connect to MYSQL 5 when using PHP 5.1.2 > > hi, > since 3 days, i'm trying to connect to MYSQL 5.0.18 from > php scripts using Apache et Macromedia 2004 Dreamweaver. > Could someone help me doing this? Best regard. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql oddity
You will not have access to the extra functionality in MySQL 5.0 if you are connecting to it via a php4 client API, as the php4 API does not know about the extra functionality in MSQL 5.0. You could try downloading and compiling Apache 2.0.55, or 2.2.0, MySQL standard linux statically linked binary .tar.gz, and php 5.1.2. You will need to compile apache first. Then install MySQL in /usr/local/mysql, or make a symlink from /usr/local/mysql, to the place you unpacked the MySQL binary. Then compile php with something like: #! /bin/sh # # Created by configure './configure' \ '--with-apxs2=/usr/local/apache-2.0.55/bin/apxs' \ '--prefix=/usr/local/php-5.1.2' \ '--with-mysql=shared,/downloads/mysql/bin/mysql-standard-5.0.18-linux-i686' \ '--with-mysql-sock=/var/lib/mysql/mysql.sock' \ '--with-zlib-dir=/usr/include' \ '--with-mysqli=shared,/downloads/mysql/bin/mysql-standard-5.0.18-linux-i686/bin/mysql_config' \ "$@" It is possible to compile php5 with both the old mysql extension, and the newer mysqli extension, provided they are both compiled to use the same MySQL client libraries. HTH Keith In theory, theory and practice are the same; In practice they are not. On Tue, 14 Feb 2006, Dan Stromberg wrote: > To: mysql@lists.mysql.com > From: Dan Stromberg <[EMAIL PROTECTED]> > Subject: mysql oddity > > > Hi folks. > > I've been compiling my own apps for over a decade, but it seems like > it's time to get -with- the times, and start using precompiled binaries > more. > > So I got a set of matching set of precomiled apache2/mysql5/php5 off of > ibiblio for a Solaris 9/Sparc box. > > However, even though I have apache2 running with php5, and mysql5 is > running (mysql4 is not), phpinfo is telling me that it was built against > mysql4... snip > So it kind of seems like maybe I have php5 using mysql5 via the mysql4 > client API. Does this sound about right? > > And if so, what would I lose by leaving it this way? That is, are there > useful things in later versions of the mysql client API? -Is- there a > version 5.* of the mysql client API? > > Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installation Issue
If you have not installed MySQL before, you must create the MySQL grant tables: shell> scripts/mysql_install_db --user=mysql Keith In theory, theory and practice are the same; In practice they are not. On Tue, 14 Feb 2006, Ravi Kumar wrote: > To: Imran Chaudhry <[EMAIL PROTECTED]> > From: Ravi Kumar <[EMAIL PROTECTED]> > Subject: Re: Installation Issue > > Imran, > I noticed couple of permissions were not correct.I changed mysql.mysql. > Still Ihave been getting following errors. > 060214 15:53:05 mysqld started > 060214 15:53:05 InnoDB: Started; log sequence number 0 43655 > 060214 15:53:06 [ERROR] Fatal error: Can't open and lock privilege tables: > Table 'mysql.host' doesn't exist > 060214 15:53:06 mysqld ended > > Please advice. > thanks > > Imran Chaudhry <[EMAIL PROTECTED]> wrote: > On 2/14/06, Ravi Kumar wrote: > > Starting mysql with root.I tried withn mysql user account also but still > > same error. > > thanks > > Ravi, > Assuming you are starting MySQL with mysqld_safe, then it will invoke > the MySQL server as the mysql user. > > I suspect the cause is that /var/lib/mysql is not owned by mysql > > If so, as superuser: chown -R mysql:mysql /var/lib/mysql > > Regards, > Imran Chaudhry > -- > http://www.ImranChaudhry.info > MySQL Database Management & Design Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird MySQL Connection Issues
If you are running MySQL on Windows, then I'm wondering whether you are having a problem with running out of available ports, for clients to connect to MySQL on. This may be your problem: >From the manual, 2.3.16. MySQL on Windows Compared to MySQL on Unix *** MySQL for Windows has proven itself to be very stable. The Windows version of MySQL has the same features as the corresponding Unix version, with the following exceptions: Limited number of ports Windows systems have about 4,000 ports available for client connections, and after a connection on a port closes, it takes two to four minutes before the port can be reused. In situations where clients connect to and disconnect from the server at a high rate, it is possible for all available ports to be used up before closed ports become available again. If this happens, the MySQL server appears to be unresponsive even though it is running. Note that ports may be used by other applications running on the machine as well, in which case the number of ports available to MySQL is lower. For more information, see http://support.microsoft.com/default.aspx?scid=kb;en-us;196271. *** Keith In theory, theory and practice are the same; In practice they are not. On Sun, 12 Feb 2006, Aaron Axelsen wrote: > To: mysql@lists.mysql.com > From: Aaron Axelsen <[EMAIL PROTECTED]> > Subject: Weird MySQL Connection Issues > > I have been experiencing some weird MySQL connection issues lately. > Twice now in that last couple weeks, there have been times where some > mysql applications are working, and others are not working. Both times > the mysql connection limit was rather high. A simple mysql restart has > fixed the problem both times. > > There are a few forums using phpbb running on this server which get > heavy access, which is most likely the cause of the problem. > > The version of mysql running is: 4.1.14 > > Is there a known issue like this with alot of mysql connections? > > Does anyone have any related ideas or suggestions? Thanks! > > -- > Aaron Axelsen > [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1045 access denied after granting all privs
I get the following: ERROR 1045 (28000): Access denied for user 'dba'@'XP0406OEM' (using password: YES) Did you do FLUSH PRIVILEGES as root after you performed the GRANT? I don't think that's the problem. You only need to FLUSH PRIVILEGES when you update the privilege tables directly. When you use GRANT and REVOKE, this isn't necessary. --df -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Connection Problem
do # my_print_defaults mysqld --port=3306 --socket=/var/lib/mysql/mysql.sock --skip-locking --key_buffer=16M --max_allowed_packet=1M --table_cache=64 --sort_buffer_size=512K --net_buffer_length=8K --myisam_sort_buffer_size=8M --server-id=1 To give you the defaults for your mysqld server. It may be listening on the wrong socket for connections. If your mysqld is listening on the same port as DW MX is trying to connect to, then maybe mysql is not running after all. Keith In theory, theory and practice are the same; In practice they are not. On Thu, 2 Feb 2006, Rhodes, Casey wrote: > To: mysql@lists.mysql.com > From: "Rhodes, Casey" <[EMAIL PROTECTED]> > Subject: MySQL Connection Problem > > When testing my connection via Dreamweave MX, I get the > following error message: > > > > 2002 Can't connect to local MySQL server through socket > '/var/mysql/mysql.sock' (2) > > > > It was suggested to me that the server may not be running, > though when I go to my System Preferences Panel (MacOSX), > I have a green light and it says that it is currently > running. > > --Casey Rhodes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: data entry GUI
I have just caught the end of this topic, so hope I'm not repeating something already mentioned. What I do is enter my data into a plain text file, like this; The questions are a bit dumb, just for testing purposes of course! /* file: general-quizdata.sql */ /* data to populate general knowledge quiz tables */ use web_app_tester; insert into question set question_text = 'What is the Capital of England?'; select @questionID := last_insert_id(); insert into answer set answer_text = 'London', status = 'right', questionID = @questionID; insert into answer set answer_text = 'Paris', questionID = @questionID; insert into answer set answer_text = 'Edinburgh', questionID = @questionID; insert into question set question_text = 'How many yards are there in a mile?'; select @questionID := last_insert_id(); insert into answer set answer_text = '5000', questionID = @questionID; insert into answer set answer_text = '1760', status = 'right', questionID = @questionID; insert into answer set answer_text = '2500', questionID = @questionID; insert into question set question_text = 'What are the 3 primary colors?'; select @questionID := last_insert_id(); insert into answer set answer_text = 'Red, Grey, Black', questionID = @questionID; insert into answer set answer_text = 'Yellow, White, Blue', questionID = @questionID; insert into answer set answer_text = 'Green, Blue, Red', status = 'right', questionID = @questionID; insert into question set question_text = 'RAM is an acronym for?'; select @questionID := last_insert_id(); insert into answer set answer_text = 'Random Access Memory', status = 'right', questionID = @questionID; insert into answer set answer_text = 'Read Access Memory', questionID = @questionID; insert into answer set answer_text = 'Read And Memorise', questionID = @questionID; /* data truncated here for brevity */ /* end of data */ and then load it into mysql from the mysql command prompt with: mysql> \. general-quizdata.sql This may seem like the long-winded version of LOAD DATA, but it does make the syntax easier to understand, plus you can put any other mysql commands in the file. Also you have the data and commands available in a file, in case you have to reload the table from scratch again. HTH Keith In theory, theory and practice are the same; In practice they are not. On Wed, 1 Feb 2006, Ryan Stille wrote: > To: mysql@lists.mysql.com > From: Ryan Stille <[EMAIL PROTECTED]> > Subject: RE: data entry GUI > > You can also install MyODBC and then hook an Excel spreadsheet into your > database. Editing the spreadsheet will update data in your database. > This isn't a good solution if you are going to be creating new tables > often. But for manipulating data in a known set of tables it's great. > > -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Kinda OT: Book database question
I think you will find a book ISBN is a reference to a particular publisher's version of a particular book. So in answer to your question, if several different publishers, or even the same publisher have published several different books all entitled 'Huckleberry Finn' then to avoid ambiguity in identifying one particular book from that group of books, each book should have a unique identifier, which AFAIK is what the ISBN is all about. The usual way to reference a book is by: Title: Author: Publisher: ISBN: Date Published: Edition: Price: plus any other attributes, such as hardback or paperback. HTH Keith In theory, theory and practice are the same; In practice they are not. On Wed, 1 Feb 2006, John Meyer wrote: > To: mysql@lists.mysql.com > From: John Meyer <[EMAIL PROTECTED]> > Subject: Kinda OT: Book database question > > Hi, I'm trying to develop my own book database, and I have a question > about ISBN: Is that number linked to a book or to a title? That is, can > one title (say, Huckleberry Finn) have several ISBNs associated with it > through several book releases? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dictionary
Well I have just done a google.co.uk search for english dictionary downloadable and got the following results: Web Results 1 - 10 of about 1,290,000 for english dictionary downloadable. They may not be in the correct format to import directly. But I'm sure it is possible to write a script to parse the downloaded dictionary, and build the query to insert the contents into a database yourself, or to convert the dictionary into another suitable format that would be compatible for loading directly into a database table. HTH Keith In theory, theory and practice are the same; In practice they are not. On Wed, 1 Feb 2006 [EMAIL PROTECTED] wrote: > To: Peter of Pedsters Planet <[EMAIL PROTECTED]> > From: [EMAIL PROTECTED] > Subject: Re: Dictionary > > Peter of Pedsters Planet <[EMAIL PROTECTED]> wrote on 02/01/2006 > 01:27:45 PM: > > > I'd like to know too if posible :) > > > > On 01/02/06, Scott Hamm <[EMAIL PROTECTED]> wrote: > > > I've been trying to google to no avail for English dictionary (with > > > definitions) in any format that I can download and import into MySQL. > > > Do anyone know where I can find it? > > > > There is a lot of effort that goes into creating and > maintaining all of the definitions for hundreds of > thousands of words. I seriously doubt that any dictionary > publisher is just going to give it all away for free, > regardless of the language. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to login to MYSQL as "root" without knowing the passward
Well under Linux I re-installed apache which overwrote the directory and deleted the pid of a currently running apache! My workaround was to do: $ netstat -l -t -p as root user from the command line. This gave me the running daemon processes that were listening to tcp ports, and their associated pid numbers. I was then able to stop the running apache with: $ kill 12345, where 12345 was the process number of the apache. There may be a similar command under windows that will enable you to get the PID of the mysql you want to kill. HTH Keith In theory, theory and practice are the same; In practice they are not. On Mon, 30 Jan 2006, Dan Trainor wrote: > To: sol beach <[EMAIL PROTECTED]>, mysql@lists.mysql.com > From: Dan Trainor <[EMAIL PROTECTED]> > Subject: Re: How to login to MYSQL as "root" without knowing the passward > > sol beach wrote: > > Sounds reasonable, but how do I shutdown down MYSQL so I can restart > > with > > --skip-grant-tables > > option? > > > > On 1/30/06, *Dan Trainor* <[EMAIL PROTECTED] > > <mailto:[EMAIL PROTECTED]>> wrote: > > > > sol beach wrote: > > > I've been asked to assist the folks who own the data in the > > database, but > > > folks who used to maintain it are no longer with the > > > company. > > > This installation resides on a Windows 2003 server to which > > > I > > have local > > > admin rights. > > > What is the most painless way to get "root" access to this > > > database? > > > I am more than willing to RTFM, if anyone will point me at > > > which > > FM to read. > > > > > > TIA > > > > > > > Hi - > > > > I've always used the startup option of '--skip-grant-tables'. > > This may > > or may not be the "correct" way to do so, but we'll see what kind > > of > > feedback we get. > > > > I do this, then update the Password field in the mysql.user > > table. > > > > HTH > > -dant > > > > > > Hi - > > Stop the service? Kill the process? That's up to you. > > hth > -dant > > -- > 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: display a hierarchic tree
Hi Jochen. An alternative approach could be to pull all the values out of the database using select * from ..., and then build the tree-structure in your application logic. Insert the relevant values returned from mysql in the appropriate places of the tree-structure in the app code. Keith In theory, theory and practice are the same; In practice they are not. On Sun, 29 Jan 2006, Jochen Kaechelin wrote: > To: mysql@lists.mysql.com > From: Jochen Kaechelin <[EMAIL PROTECTED]> > Subject: display a hierarchic tree > > I have the following table: > > mysql> select * from link_categories; > ++---+-+---+---+-+ > | id | level | category_id | category | parent_id | deleted | > ++---+-+---+---+-+ > | 1 | 1 |1000 | Software | 0 | 0 | > | 2 | 1 |2000 | Harware | 0 | 0 | > | 3 | 2 |1001 | Virenscanner | 1000 | 0 | > | 4 | 2 |1003 | Packprogramme | 1000 | 0 | > | 5 | 3 |1004 | Linux | 1001 | 0 | > | 6 | 3 |1005 | Windows | 1001 | 0 | > | 7 | 4 |1006 | Windows XP| 1005 | 0 | > | 8 | 2 |1007 | Sniffer | 1000 | 0 | > | 9 | 4 |1008 | Debian Woody | 1004 | 0 | > | 10 | 1 | 10 | Vermischtes | 0 | 0 | > ++---+-+---+---+-+ > 10 rows in set (0.24 sec) > > and I want to display a tree like: > > Software > Virenscanner >Linux >Debian Woody > Windows >Windowsd XP > Packprogramm >Sniffer > Hardware > Vermischtes > > > Can someone give me hint how to build a query? > > I run MySQL 4.1.x and 5.0.x and I use PHP. > > Thanx. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Support between MySQL and PHP
Hi Philip. I'm wondering if you have mysql server version 3.23.49 still running on your machine? If so, version 5 would not start up, as it would not be allowed access to port 3306. Try doing my_print_defaults mysqld and my_print_defaults client mysql from a root shell and see what values you get returned. HTH Keith In theory, theory and practice are the same; In practice they are not. On Sun, 29 Jan 2006, Philip R. Thompson wrote: > To: mysql@lists.mysql.com, php-general@lists.php.net > From: Philip R. Thompson <[EMAIL PROTECTED]> > Subject: Support between MySQL and PHP > > Hi all. > > I figured this question was suitable for both the MySQL list and the > PHP-General list. Here's what I'm running into. > > I just installed MySQL5 and currently have PHP 4.3.11 installed. I > am wanting to connect to the mysql database on localhost, but I > get the following results: > > -- > > "Client does not support authentication protocol requested by > server; consider upgrading MySQL client" > -- > > Well, I have the lastest stable version of MySQL, so I did some more > research on what the problem might be. When I checked my > information for PHP using phpinfo(), it gave me the "Client API > version" for MySQL was 3.23.49. So, I'm thinking my version of PHP > cannot connect to my version of MySQL. I then considered if I > installed the MySQLi extension for PHP (supports versions of MySQL > > 4.1), would that help me? Or, if I just upgraded PHP to version 5, > would that help me? > > Does anyone have any suggestions on the direction I should go? > > Thanks in advance, > ~Philip k -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: count(*) send a wrong value
>From the MySQL 4.1 manual 12.10.1. GROUP BY (Aggregate) Functions COUNT(expr) Returns a count of the number of non-NULL values in the rows retrieved by a SELECT statement. COUNT() returns 0 if there were no matching rows. mysql> SELECT student.student_name,COUNT(*) ->FROM student,course ->WHERE student.student_id=course.student_id ->GROUP BY student_name; COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values. COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. For example: mysql> SELECT COUNT(*) FROM student; This optimization applies only to MyISAM and ISAM tables only, because an exact record count is stored for these table types and can be accessed very quickly. For transactional storage engines (InnoDB, BDB), storing an exact row count is more problematic because multiple transactions may be occurring, each of which may affect the count. COUNT(DISTINCT expr,[expr...]) Returns a count of the number of different non-NULL values. COUNT(DISTINCT) returns 0 if there were no matching rows. mysql> SELECT COUNT(DISTINCT results) FROM student; In MySQL, you can get the number of distinct expression combinations that do not contain NULL by giving a list of expressions. In standard SQL, you would have to do a concatenation of all expressions inside COUNT(DISTINCT ...). COUNT(DISTINCT ...) was added in MySQL 3.23.2. Keith In theory, theory and practice are the same; In practice they are not. On Mon, 23 Jan 2006, fabsk wrote: > To: mysql@lists.mysql.com > From: fabsk <[EMAIL PROTECTED]> > Subject: count(*) send a wrong value > > Hi, > > I'm facing a strange problem. I am using a database at my Internet > provider (Free, France). The type of table is MyISAM (no choice), MySQL > 4.1.15. I can do my tests with my PHP code or phpMyAdmin. > > The definition of my table is: > - uid, int > - cid, int > - response, text > - points, int (can be null) > > keys: > - uid, cid > - cid, response(4) > - cid > > When I do "select * from my_table where cid=123", I get my 10 records. > But when I do "select count(*) from my_table where cid=123" I get "2". I > also happens with many other values of "cid" and the bad result is > always "2". > > I can't understand what's happen. It seems to simple, but there should > be something. Do you have an idea? > > Thank you for your attention > Fabien > > > -- > 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: Postcode Search
It might be more productive to add a foreign key to the Offices and Properties tables that points to the Primary key ID of the respective PostCode in the Postcodes table. Combined with the other suggestions, this would give you a key from the Offices and Properties tables directly into the Postcodes table, and the associated coordinates you want to match on. HTH Keith In theory, theory and practice are the same; In practice they are not. On Mon, 23 Jan 2006, Shaun wrote: > To: mysql@lists.mysql.com > From: Shaun <[EMAIL PROTECTED]> > Subject: Postcode Search > > Hi, > > We have a dataset of uk postcodes and their relevant > X-Coordinates and Y-Coordinates, a table of properties > (houses), a table of users and a table of offices - users > work in an office - table structures below. > > Is it possible to run a search of all properties in the > properties table that come within a certain distance of > the users postcode, currently we do this by downloading > all properties into an array and stripping out the ones > that don't come within the radius with php. > > Any advice would be greatly appreciated. > > > # -- MySQL dump -- > # > # Table structure for table 'Offices' > # > CREATE TABLE Offices ( > Office_ID int(11) DEFAULT '' NOT NULL auto_increment, > Subscriber_ID int(11), > Type varchar(10), > Address_Line_1 varchar(50), > Address_Line_2 varchar(50), > City varchar(50), > County varchar(50), > Postcode varchar(10), > Telephone varchar(12), > Fax varchar(12), > Radius tinyint(4), > PRIMARY KEY (Office_ID) > ); > > # > # Table structure for table 'Postcodes' > # > CREATE TABLE Postcodes ( > PCDSECT varchar(6) DEFAULT '' NOT NULL , > SORTSECT varchar(6), > PCDDIST varchar(4), > SORTDIST varchar(4), > PCDAREA char(2), > X_COORD double(7,1) unsigned , > Y_COORD double(7,1) unsigned , > PRIMARY KEY (PCDSECT) > ); > > # > # Table structure for table 'Properties' > # > CREATE TABLE Properties ( > CHK varchar(20), > Property_ID int(11) DEFAULT '' NOT NULL auto_increment, > Insertion_Date date, > Status varchar(20), > Property_Name_Or_Number varchar(50), > Address_Line_1 varchar(50), > Address_Line_2 varchar(50), > City varchar(50), > County varchar(50), > Postcode varchar(12), > PRIMARY KEY (Property_ID) > ); > > # > # Table structure for table 'Users' > # > CREATE TABLE Users ( > User_ID int(11) DEFAULT '' NOT NULL auto_increment, > Office_ID int(11), > Type varchar(20), > Title varchar(4), > Firstname varchar(20), > Lastname varchar(20), > Password varchar(20) DEFAULT '' NOT NULL , > Email varchar(50), > PRIMARY KEY (User_ID) > ); > > # --- Dump ends --- > > > > -- > 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: Slow sorting
> -Original Message- > From: "Marcus Bointon" <[EMAIL PROTECTED]> [SNIPPED] > That takes 11 seconds to run. Without the order by it takes 0.13 sec. > I have simple indexes on both first name and last name (they are > sometimes searched separately). It strikes me that this is really > very slow - it really doesn't have much to sort. I tied doing an > explain, and though I could see that it was using the indexes, it was > also saying use where, use temporary, use filesort. Why is it falling > back to these methods? How can I make this faster? Think you will find, should you study the query carefully, that in fact poor MySQL needs to query and actually sort all 400,000 records (was it not for the account id), before it can give you your batch of 30. If MySQL does not have enough ram allocated, may this even entail disk swapping for a NxMb table. This is of course the case because MySQL can not possibly give you the top 30, without first having to use the pertinent index to sort all of them. Indexes on the name and surname may also be a deathshot rather than a blessing, as these indexes would be almost as big as the original table. So instead of simply loading and sorting through one file, MySQL now has to do it with two equally big files. Dare I suggest the following: 1. Remove your name and surname indexes. 2. Ensure you have an index on the account column. 3. Insert a new composite column into the table which is of fixed width (CHAR) and at most 4/5 characters wide. Now populate this column with the first 2/3 characters of the surname and first 2 of the name, index this column and rather sort by it. (You can obviously change the containing data's permutation as you like, ex. first 4 of the surname, etc. 4. Also ensure MySQL has enough ram allocated (see show variables) such that it can load the complete table index in RAM (if possible, even the table's data), so that no disk/virtual mem swapping takes place. I think you will find MySQL much snappier with this source data, as it will first filter the data quickly by account and ordering of the subset should be very quick using only a 4 char column index. Chances are also good that if the first 4 chars of a surname match, the surnames are most likely identical (granted, the odd one will be sorted below rather than above it's actual position), but in the end is it a balance between speed and the odd mis-ordered record. Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql 4.1.14 memory leak.
I'm using mysql 4.1.14 rpm's on Fedora Core 4. I've setup max_heap_table_size to allow for 500M heap tables. Currently I use roughly 435M in heap by about 16 different tables. Some use btree, other's use default hash. The problem is, over time mysql looks like this in top: PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2898 mysql 15 0 1127m 626m 2076 S 18.3 62.5 2974:17 mysqld-max mysql is forcing the machine to swap. I've tried dropping all heap tables that are in use and this does not free the ram. I have to restart mysql and and using init-file reload the heap tables and everything is fine for a couple of days. My problem is strikingly similar to this: http://groups.google.com/group/mailing.database.myodbc/browse_thread/thread/485647dae02b59a1/32f0009e9cb135b7?lnk=st&q=virtual+heap+mysql&rnum=1&utoken=rT79JjoAAABobq0US6-f3p1tupn-bp7-GyqMAsXdt4_lvPhOluyGzfrEz8xuJ8FzZhQCB5gw1_s38laLLlcPg_ShAKo-q_vP I'm using the mysql rpm's from mysql.com <http://mysql.com>. Other than restarting mysql nightly, is there anything I could do to further debug this? Thanks, Mysql Rocks.
Re: Loading Decimal Values with load data infile
Hallo Thorsten, Op 14 Sep 05 schreef Thorsten Moeller aan MySQL Mailinglist: TM> i am trying to load a csv file with "LOAD DATA INFILE". This File TM> contains columns with decimal values with the german comma instead of TM> the dot as separator (e.g. 3.20 is 3,20). Is there an option to handle TM> this during or immediately before LOAD with MySQL Commands/Tools. Now we TM> only see values where the values on the right of the comma have been cut TM> off. I asked this same question on June 22, and got the following answer: What about reading the data into the table and storing the 'amount' in varchar for now. Then you can run an update query in which you replace the decimal comma with a decimal point and store that in the float field (emptying the varchar in the process). UPDATE `table` SET `amount`= REPLACE( `v_amount`, ',' , '.'), `v_amount` = NULL WHERE `v_amount` IS NOT NULL; Groetjes, Hans. jdh punt beekhuizen bij duinheks punt xs4all punt nl --- GoldED+/LNX 1.1.5/050823 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mark a single row?
Hallo Michael, Op 29 Aug 05 schreef Michael Stassen aan [EMAIL PROTECTED]: >> >> I would like a single row in a table, and not more than one, to >> >> be used as a preferred value in another application. >> JBG> add a column 'preferred', tinyint(1) NOT NULL. MS> It's hard to say without knowing just what you mean by "used as a MS> preferred value in another application", but I suspect that adding a MS> whole column for this may not the best way to go. You'd be storing a MS> lot of 0s just to keep one 1. The simplest solution may be to code the MS> preferred row's id in your app. Your instinct to keep this value in the MS> db is probably a better idea, however, especially if the preferred id MS> could ever change. It won't change very often, but there certainly is a chance. That's why I decided to keep it in the database somehow, rather than hard-coding it. Also, this way the users can change it without having to mess with the code. The amount of 0s would not be more than about 40, so not a big disaster. MS> An alternative to adding a column would be to add a table. MS> Something like: MS>CREATE TABLE pref_value (pref_id INT); MS>INSERT INTO pref_value (pref_id) VALUES (id_of_preferred_row); But this method is much more elegant. I wonder why I couldn't think of it myself... Thank you very much! Groetjes, Hans. --- GoldED+/LNX 1.1.5/050823 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mark a single row?
Hallo Jasper, Op 29 Aug 05 schreef Jasper Bryant-Greene aan mysql: >> I would like a single row in a table, and not more than one, to be >> used as a preferred value in another application. JBG> add a column 'preferred', tinyint(1) NOT NULL. Thank you! Groetjes, Hans. --- GoldED+/LNX 1.1.5/050823 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mark a single row?
Hallo, I would like a single row in a table, and not more than one, to be used as a preferred value in another application. Is this possible, and, if yes, how? Groetjes, Hans. --- GoldED+/LNX 1.1.5/050823 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DB admin with limited access
Salutations! I am relatively new to the MySQL flavor of database server and now have run into a situation on my hosting that I don't seem to be able to resolve. I installed a new query using TEMPORARY tables via the PHP 4 interface on a production database and suddenly began receiving PHP timeouts. From this point on, access to the database has been severely restricted. I have run a few diagnostics and ascertained the following: - there are a growing number of processes - queries - displayed by mysql_list_processes as accessing my database - phpMyAdmin displays a process that can't be killed. It appears to be the process to execute the SQL using TEMPORARY tables. - only one table appears to be blocked. SELECT COUNT(*) on all other tables works fine. This is a central table, however it was not involved in the TEMPORARY table statement. When I attempt SELECT COUNT(*) on the problem table, the statement blocks for a period (PHP timeout?) and then dies with no apparent error status or message. - The support people at my hosting claim that I still have 1.4 GB space (my complete hosting allocation) for TEMPORARY tables. I have tried to get my hosting - hostm.com - to assist, but besides suggesting RTFM and the repair function, they have not been particularly helpful. I'm sure all it needs is for the server to be restarted, but I do understand that it is not always possible to do such in a shared environment. So 1) How does a user with no shell access kill this process? 2) Is a "repair" really the right thing to do in this situation? 3) With the constraint of PHP 4.3.11 and MySQL 4.0.25-standard, how do I use TEMPORARY tables and not get myself into this predicament? Thanks much for any tips. James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile
Hallo, Op 30 Jun 05 schreef blackwater dev aan mysql@lists.mysql.com: bd> "1","23","345","45"; bd> "34","4","444","1er"; bd> Load data local infile '/httpd/htdocs/sql/loader.sql' into table bd> vehicles fields terminated by ',' enclosed by "'" lines terminated by bd> ';' I think it should be: enclosed by '"' Groetjes, Hans. jdh punt beekhuizen bij duinheks punt xs4all punt nl --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Preventing duplicates with "load data"
Hello Mike, On 27 Jun 05, mos wrote to mySQL list: >> How can I prevent duplicate entries when I fill the data base with >> "load data"? I tried ignore, but that has no effect. m> Ignore/Replace will only work on Unique keys and I bet your key is m> not unique. If you make it unique, then Ignore will keep the m> existing value, or Replace will replace the existing row with the m> new row. You're good at betting :) The next question would of course be: how do I create a unique key, but somebody else already asked that and got a clear answer. Thank you very much. Regards, Hans. jdh dot beekhuizen at duinheks dot xs4all dot nl --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Preventing duplicates with "load data"
Hello, How can I prevent duplicate entries when I fill the data base with "load data"? I tried ignore, but that has no effect. Probably I'm something very elementary, but I'm still learning... Regards, Hans. jdh dot beekhuizen at duinheks dot xs4all dot nl --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Decimal comma in input
Hallo Jigal, Op 22 Jun 05 schreef Jigal van Hemert aan <[EMAIL PROTECTED]: JvH> What about reading the data into the table and storing the JvH> 'amount' in varchar for now. Then you can run an update query in JvH> UPDATE `table` SET `amount`= REPLACE( `v_amount`, ',' , '.'), JvH> `v_amount` = NULL WHERE `v_amount` IS NOT NULL; Perfect! Thank you very much. Groetjes, Hans. jdh punt beekhuizen bij duinheks punt xs4all punt nl --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Decimal comma in input
Hallo Anoop, Op 22 Jun 05 schreef Anoop kumar V aan Jigal van Hemert <[EMAIL PROTECTED]>,: AkV> Alternatively, you can parse the text files using application logic AkV> (java, c++, etc) and then after extracting (and cleaning) your data AkV> insert them into mysql. I had been thinking about that, but that would be a cowardly way out :) As I'm very new to [My]SQL, I prefer to use its own methods as much as possible. Groetjes, Hans. jdh punt beekhuizen bij duinheks punt xs4all punt nl --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Decimal comma in input
Hello Anoop, On 22 Jun 05, Anoop kumar V wrote to All: AkV> Are u saying that u have data in a text file and you need to parse AkV> this and insert them into mysql? That's what I Ntried to say, yes :) The main problem is the decimal comma in the amounts. Regards, Hans. jdh dot beekhuizen at duinheks dot xs4all dot nl --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Decimal comma in input
Hallo,
I get data with a format that I can't choose. The fields are enclosed
in double quotes ("), separated by commas (,). The main problem is that
some fields contain amounts with a decimal comma. I have not found a
way yet to load these data properly. Could somebody help me please?>
Groetjes,
Hans.
--- GoldED+/LNX 1.1.5/040412
* Origin: The Wizard is using MBSE/Linux (2:280/1018)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Filling database with "load data"
Hallo Peter, Op 30 May 05 schreef Peter aan [EMAIL PROTECTED]: P> http://dev.mysql.com/doc/mysql/en/load-data-local.html P> # P> If LOAD DATA LOCAL INFILE is disabled, either in the server or the P> client, a client that attempts to issue such a statement receives the P> following error message: P> ERROR 1148: The used command is not allowed with this MySQL version P> I hope this help. I sure does, thank you very much. And that information was even in the tutorial part of the manual. I'm ashamed of myself :( Groetjes, Hans. --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Filling database with "load data"
Hallo, I'm using MySQL 4.0.23a as found in the SlackWare 10.1 distribution. As I'm totally new to MySQL I tried tom follow the tutorial from the manual. When I tried to fill the data base using a text file with the command load data local infile '~/temp' into table huisdier; I got ERROR 1148: The used command is not allowed with this MySQL version What's going wrong? The insert command works without problem. Groetjes, Hans. --- GoldED+/LNX 1.1.5/040412 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with Duplicate Key (32-bit Solaris MySQL 4.1)
Folks, I've had a database running for months now and suddenly, when inserting into table 'HEARTBEAT_COUNTS' (desc below), I get an error: Error - Duplicate key '444642', -1 (The syntax of the error message is not exact) My insert statement does not include a value for 'id' which is an auto_increment field. The table has about 390,000 rows. Please help -- I'll answer any question ASAP as I really want to understand why this happened. I have temporarily dropped the table and created a new one and the problem disappeared -- but I want to learn why it happened and how I can prevent it. Thank you very much. -- CREATE TABLE `heartbeat_counts` ( `id` int(10) unsigned NOT NULL auto_increment, `received_responses` smallint(5) unsigned NOT NULL default '0', `device_response` bigint(20) unsigned NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=MyISAM ; example insert: INSERT INTO heartbeat_counts set received_responses=1, device_response=12951 ; __ 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]
Grant Tables problem (I think)
Hi there, I have a problem connecting to the mysql server. I installed a new server with mysql 4.1.9, apache2, php4 on freebsd5.3 and have some websites running on it using the mysql server. Last friday I had a crash of one off my other servers and I copied the websites and db's to this new server. (old server was mysql 3). After that the mysql server didn't startup anymore. I then chown'ed the data dir to mysql:mysql. After that I was able to start the server with "--skip-grant-tables &". But it didn't start with out the --skip-grant-tables option". So I found out to use "mysql_install_db" to create new grant tables. After that the server starts with and without "--skip-grant-tables". But only with the "--skip-grant-tables" option, websites can connect to the databases. I can't find any real errors in the logs and I can't find anything on the web or forum. I run mysql_fix_privilege_tables and installed a root passwd. Hopefully somebody can help me on this. Roger
Re: Undo function?
On Thu, Jan 20, 2005 at 08:58:18AM -0600, Jay Blanchard wrote:
> [snip]
> I get very nervous when I log onto my database via SSH and type in
> queries
> manually. Take the following query for example:
>
> Delete FROM Users WHERE User_ID = 5;
>
> If I hit return before I start typing WHERE then things would be
> disastorous! Is there any type of undo function with MySQL?
> [/snip]
>
> You'll want to read this
>
> http://dev.mysql.com/doc/mysql/en/COMMIT.html
And try:
$ mysql --i-am-a-dummy
that makes it refuse deletes without 'where'
Good luck,
Harmen
--
The Moon is Waxing Gibbous (81% of Full)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqldump error 1017: errno: 24 - help?
Yep, that seems to have done it, at least when I'm testing it. I'm pretty sure the problem is fixed, but the next couple of days of automated backups will tell. Thanks! On Tue, 2005-01-11 at 00:43, Gleb Paharenko wrote: > Hello. > > Try '--open-files-limit=8192' at least. Check the real value of > open_file_limits with such statement: > show variables like '%open_f%'; > > You can find some recommendations for SuSe Linux at: > http://dev.mysql.com/doc/mysql/en/Linux-post-install.html > > Mysql user <[EMAIL PROTECTED]> wrote: > > Hi.. > > > > I've got an ISP, and all of our customers have databases in our mysql > > system. > > > > My backup command is: > > > > mysqldump --force --opt -A -p' | gzip -c > > > /var/sqlbackup/mysqldump-`date +\%A`.sql.gz > > > > This has worked fine for some time. > > > > Now, however, I get an error message: > > > > mysqldump: Got error: 1017: Can't find file: > > './usr_web22_1/invoices_va.frm' (errno: 24) when using LOCK TABLES > > > > On different runs, it reports different files, even right after > > restarting mysqld. > > > > I've looked at the mysql documentation for this error; it's at > > http://dev.mysql.com/doc/mysql/en/Not_enough_file_handles.html > > > > I've looked at the startup script, put in '--open-files-limit=1024', > > restarted mysql, and the error is the same. > > > > I've looked at the table_cache and max_connections system variables, > > which are 64 and 100, respectively. > > > > My question is: what should I do now? > > > > Since table_cache and max_connections are far less than 1024, do I > > reduce them even further? > > Is there something else I should be looking at? > > > > This is mysql-3.23.52-106 on SuSE Linux 8.1, running on a dual PIII > > 866Mhz system, with 512M of ram and 1G of swap, on a RAID-1 pair of 17G > > hard drives. > > > > 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 user <[EMAIL PROTECTED]> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqldump error 1017: errno: 24 - help?
Hi.. I've got an ISP, and all of our customers have databases in our mysql system. My backup command is: mysqldump --force --opt -A -p' | gzip -c > /var/sqlbackup/mysqldump-`date +\%A`.sql.gz This has worked fine for some time. Now, however, I get an error message: mysqldump: Got error: 1017: Can't find file: './usr_web22_1/invoices_va.frm' (errno: 24) when using LOCK TABLES On different runs, it reports different files, even right after restarting mysqld. I've looked at the mysql documentation for this error; it's at http://dev.mysql.com/doc/mysql/en/Not_enough_file_handles.html I've looked at the startup script, put in '--open-files-limit=1024', restarted mysql, and the error is the same. I've looked at the table_cache and max_connections system variables, which are 64 and 100, respectively. My question is: what should I do now? Since table_cache and max_connections are far less than 1024, do I reduce them even further? Is there something else I should be looking at? This is mysql-3.23.52-106 on SuSE Linux 8.1, running on a dual PIII 866Mhz system, with 512M of ram and 1G of swap, on a RAID-1 pair of 17G hard drives. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: are autoincrement values not always increasing in innodb?
Frank, "my experience MySQL returns the the rows in the order that you inserted them" This is true, if, and only if you have never deleted a record from the table. Like most SQL servers, MySQL leaves deleted records' space in the physical table "unoccupied", but still available. When you insert a record, it first checks if the new record can not be inserted into an already allocated space (previously occupied by a valid record). If so, it will insert it there, else it will be appended to the table. This will explain the order in which your records are listed. Record 5 either got inserted into an open space, OR it was inserted while the other client thread/transaction inserted the other 4 records. If you optimize your table, then only is it truly purged from space previously occupied by deleted records. To answer your question though, the previous situation has nothing to do with your autoinc values, which will always be incremented - guaranteed. This behaviour can be changed though if you actually specify a value for an AUTOINC column during the insert, and thereby not allow MySQL to do or follow it's normal course in incrementing the autoinc. Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents http://www.scibit.com/products/mysqlx http://www.scibit.com/products/mascon > > -Original Message- > From: "Frank Sonntag" <[EMAIL PROTECTED]> > To: "mysql@lists.mysql.com" > CC: > Subject: are autoincrement values not always increasing in innodb? > Sent: Mon, 27 Dec 2004 00:45:37 GMT > Received: Mon, 27 Dec 2004 00:49:46 GMT > Read: Tue, 28 Dec 2004 10:38:11 GMT > Hi, > > does InnoDB guarantee that the values of an autoincrement column do always > increase? > What happened to me is that a select * from my_table returns something like > > id | ... > > 10 > 11 > 5 > 12 > 13 > > where id is defined as int(10) unsigned NOT NULL auto_increment > and is the primary key of the table. > The inserts corresponding to ids (10, 11, 12, 13) are done inside one > transaction, the insert that generates id = 5, in another (concurrent) one. > > Cheers > Frank > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Re: error
Hi Jim, Your advice is indeed correct for the access denied problem. For your own problem, you might consider taking a look at max_allowed_packet variable of MySQL, as this error is common when you are sending a large blob update and the variable is too small for the update SQL, i.e. max_allowed is 1Mb and you are sending a 2Mb SQL statement. Also the client write and read timeout also causes this error, i.e. when you are doing a query which takes longer than say 60 seconds and your read timeout is set for a default 30 seconds. Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents http://www.scibit.com/products/mysqlx http://www.scibit.com/products/mascon > > -Original Message- > From: "Jim Zipper" <[EMAIL PROTECTED]> > To: "Emmanuel d" <[EMAIL PROTECTED]> > CC: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> > Subject: Re: error > Sent: Thu, 23 Dec 2004 03:58:55 GMT > Received: Thu, 23 Dec 2004 04:01:35 GMT > Read: Thu, 23 Dec 2004 09:05:20 GMT > I am no expert by any means but over the last week I have been trying to > solve why I can't connect from W XP as well. I keep getting the error > message 2013 "lost connection during SQL query." But what I have learned I > think is that the error message you have received indicates that you have > not set up the proper MySQL user access privileges. As I understand it you > need to define access privileges for the client host, user and password. > There are wildcard settings and defaults when these fields are left blank. I > learned allot from these sections of the MySQL manual > http://dev.mysql.com/doc/mysql/en/Privilege_system.html & > http://dev.mysql.com/doc/mysql/en/User_Account_Management.html but > unfortuantely I have still not solved my problem. > > I don't know if this helps or not but I thought I would try to help. If > there is anything you can suggest to solve my problem please respond as well > > TTFN > > - Original Message - > From: "Emmanuel d'Ange" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, December 22, 2004 3:26 PM > Subject: error > > > Hi, > I've install odbc 3.51driver on win XP. I've already configure the > connector with the correct parameter but when I try to test the connection, > I've got this message : [MySQL][ODBC 3.51 Driver]Access denied for user: > '[EMAIL PROTECTED]' (using password: yes) > I don't know what to do. > thanks > Best regard > > e.d'Ange > > > > -- > MySQL ODBC Mailing List > For list archives: http://lists.mysql.com/myodbc > 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: Weird problem upgrading from MySQL 4.0.21 to MySQL 4.1.7/InnoDB mysql db (D'oh)
No InnoDB here, just some old ISAM tables. Sorry about my stupidity on this one, I have only myself to blame... I have a bunch of old-school ISAM tables that need to be converted to MyISAM, is there any way to do this en-masse? (I.E. not having to go through each DB in the DBMS and ALTER TABLE tablename TYPE=MYISAM; one by one) I see that ISAM support is in the codebase but not built by default, I guess I would rather migrate up to a DB type that isn't considered to be 'legacy' . -- Mark P. Hennessy [EMAIL PROTECTED] I have a MySQL DBMS running with a mysql DB that apparently is an InnoDB DB, all other DBs in this DBMS are MyISAM. When I try to upgrade, I get the following output: 041215 16:41:53 mysqld started InnoDB: Resetting space id's in the doublewrite buffer 041215 16:41:57 InnoDB: Started; log sequence number 0 1404262 InnoDB: You are upgrading to an InnoDB version which allows multiple InnoDB: tablespaces. Wait that purge and insert buffer merge run to InnoDB: completion... InnoDB: Full purge and insert buffer merge completed. InnoDB: You have now successfully upgraded to the multiple tablespaces InnoDB: format. You should NOT DOWNGRADE to an earlier version of InnoDB: InnoDB! But if you absolutely need to downgrade, see InnoDB: http://dev.mysql.com/doc/mysql/en/Multiple_tablespaces.html InnoDB: for instructions. 041215 16:42:00 [ERROR] /usr/local/mysql-4.1.7/libexec/mysqld: Can't find file: 'host.MYI' (errno: 2) 041215 16:42:00 [ERROR] Fatal error: Can't open privilege tables: Can't find file: 'host.MYI' (errno: 2) 041215 16:42:00 mysqld ended Of course, there is no host.MYI because they are InnoDB and not MyISAM. What could be causing this? When I downgrade back to 4.0.21, I get a notice that the mysql DB using InnoDB is successfully downgraded to <4.1.1-style without any problem. Please advise. -- Mark P. Hennessy [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Weird problem upgrading from MySQL 4.0.21 to MySQL 4.1.7/InnoDB mysql db
I have a MySQL DBMS running with a mysql DB that apparently is an InnoDB DB, all other DBs in this DBMS are MyISAM. When I try to upgrade, I get the following output: 041215 16:41:53 mysqld started InnoDB: Resetting space id's in the doublewrite buffer 041215 16:41:57 InnoDB: Started; log sequence number 0 1404262 InnoDB: You are upgrading to an InnoDB version which allows multiple InnoDB: tablespaces. Wait that purge and insert buffer merge run to InnoDB: completion... InnoDB: Full purge and insert buffer merge completed. InnoDB: You have now successfully upgraded to the multiple tablespaces InnoDB: format. You should NOT DOWNGRADE to an earlier version of InnoDB: InnoDB! But if you absolutely need to downgrade, see InnoDB: http://dev.mysql.com/doc/mysql/en/Multiple_tablespaces.html InnoDB: for instructions. 041215 16:42:00 [ERROR] /usr/local/mysql-4.1.7/libexec/mysqld: Can't find file: 'host.MYI' (errno: 2) 041215 16:42:00 [ERROR] Fatal error: Can't open privilege tables: Can't find file: 'host.MYI' (errno: 2) 041215 16:42:00 mysqld ended Of course, there is no host.MYI because they are InnoDB and not MyISAM. What could be causing this? When I downgrade back to 4.0.21, I get a notice that the mysql DB using InnoDB is successfully downgraded to <4.1.1-style without any problem. Please advise. -- Mark P. Hennessy [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Definition of password hashing algorithm in 4.1.7
Mike, The newest MySQL uses SHA1 in combo with random generated 20 byte session hash values. The procedure is irreversible and therefore why it is not possible to obtain the original password. MySQL is thus very secure and only vulnerable to a bruteforce attack. You can partially secure yourself against this by limiting users to specific hosts. The day will surely come when MySQL will built in a "timeout" after a failed login attempt (i.e. when the username and host is ok, but the password failed). This will render even the bruteforce attack useless, as the attacker will have to wait years to test even a billion passwords (depending on the timeout value of course). As a typical bruteforce attack (depending on the number of valid characters and password length) can easily run into 10+ billion password permutations, this attack will be in vain as it will take decades to test all the passwords. Currently though, has MySQL no such feature. This allows you to test passwords against it upward of 10,000+ per second (if it is localhost), i.e. you can therefore test a billion passwords in approx. 30 mins. All this is obviously just estimates, as it depends on factors such as the MySQL hardware, your hardware, where the MySQL is running relative to you and how fast a connection can be established, etc etc. Typically (using a remote MySQL server) even just the connection setup time takes 1 second, i.e. 1 password/sec, thus 1 billion passwords will take 31 years :) Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents http://www.scibit.com/products/mysqlx http://www.scibit.com/products/mascon > > -Original Message- > From: "Mike Moran" <[EMAIL PROTECTED]> > To: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>;"[EMAIL PROTECTED]" > <[EMAIL PROTECTED]> > CC: > Subject: Definition of password hashing algorithm in 4.1.7 > Sent: Wed, 15 Dec 2004 12:44:10 GMT > Received: Wed, 15 Dec 2004 12:48:19 GMT > Read: Wed, 15 Dec 2004 13:46:54 GMT > X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on mail3.infinology.net > X-Spam-Status: No, hits=0.0 required=7.0 tests=none autolearn=no version=2.63 > > I've been looking into what algorithm MySQL 4.1.7 uses for password > hashing/encryption, with a view to ascertaining how secure it is. Does > it conform to any combinations of published Specs e.g. MD5/SHA-1/etc? > > I had a look at com.mysql.jdbc.Util#newHash() and #newCrypt() in > Connector/J 3.0, but the code is somewhat opaque. Is this algorithm > native to MySQL or is it just an implementation of a published > algorithm? Is it worth my time trying to track down the intriguing > 'Monty' code mentioned in Util.java? > > Ta, > > -- > Mike > > > > -- > 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: find all records with more than one occurrence
> Hi, > > How can I check all duplicated rows out from a large table? > > The values are not keys so they may have more than one occurrence. > > > Thanks for your help. > > > Regards, CHAN Chan, what about using DISTINCT in the select? .. or am I missing something? Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents http://www.scibit.com/products/mysqlx http://www.scibit.com/products/mascon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: MyCon 2005.2.6 Released
SciBit is proud to announce the release of the newest version of MyCon, v2.6 This version includes many new and improved features as well as all reported bugfixes. Amongst others: 1. Built-in support for the new 4.1 authentication, i.e. without need for an external libmysql.dll 2. Improved Copy&Paste and Drag&Drop functionality for copying/backing up and restoring databases, tables, queries, scripts and report MySQL objects. Now includes Outlook-style "Move to"/"Copy to" dialogs. 3. Simplied folder view for all the Mascon fans. 4. Full range of data editors for every MySQL column type, from Blob, Memo, Picture editors to date/time, string editors. Now includes a full date AND time editor for datetime/timestamp columns 5. Skin/Style support For more information see: http://forum.scibit.com/viewtopic.php?t=224 http://forum.scibit.com/viewtopic.php?t=215 http://forum.scibit.com/viewtopic.php?t=164 For free downloads and/or free versions, see: http://www.scibit.com/products/mycon Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents http://www.scibit.com/products/mysqlx http://www.scibit.com/products/mascon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where is my my.cnf files??
Hi, In my system my.cnf is in /etc/ directory. Bye. On Fri, 10 Dec 2004 Hiu Yen Onn wrote : >hi, > >i compiled mysql-4.1.7 from source. actually, i want to configure a mysql >cluster. from the documentation, i need to add some flag into a file called >"my.cnf". i searched through the files. it consisted of my-small.cnf, >my-medium.cnf, my-huge.cnf. but, i cant see the file my.cnf file. where does >it located pls enlighten me..thanks > > > >-- MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
Unable to login in MySQL database after installation
Hi, After I installed rpm packages on my system, I am unable to connect to the databse. I have installed in the following order 1)Server 2)Client 3)Devel 4)Shared Compat After starting the databse with: /etc/rc.d/init.d/mysql start I am unable to login as: /usr/bin/mysqladmin -u root password 'new-password' The following error occurs: /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' Please Help Me.
RE: Serious error in update Mysql 4.1.7
Hi Luciano, Not that this reply will solve your problem, but let it serve as a notice. It is NEVER a good idea to use a FLOAT/BLOB column in your where clause as MySQL can not uniquely identify the record. Especially not with floats because of the inherent floating point error made between machines after a specific number of decimals (which depends on the hardware on which the MySQL is running). To clarify: select MyFloat from MyTable; Machine A might result in: 0.123456789012345[987345765] Machine B, using exactly the same MySQL version with exactly the same table and data: 0.123456789012345[765365423] Because of precision floating point errors (in the sample, after the 15th decimal) the values in the square brackets will differ and effectively be random numbers. You can thus see the problem in asking MySQL to match floating point data using a WHERE clause. In fact you can do the same query twice on the same machine and MySQL won't be able to locate the record as the ultimate float value will differ twice in a row. Always remember computers are binary machines which loves integers. After filling the internal 8 bytes with a floating value, the rest of any floating value precision becomes a toss up. Another sample (MySQL 4.1.7): mysql> select pi(); +--+ | PI() | +--+ | 3.141593 | +--+ 1 row in set (0.00 sec) mysql> select pi()=3.141593; +---+ | pi()=3.141593 | +---+ | 0 | +---+ 1 row in set (0.00 sec) If the sample you gave was auto-generated by the MyODBC driver it most likely compiled the WHERE clause because you don't have an unique primary key in your table. Best advise is to always add a primary key AUTOINC column to all tables. This will not only result in all your queries always being able to find the exact record, but will also reduce the traffic your current queries cause. The addition of an AUTOINC column is mainly due to MySQL's lack of server side cursors. This will be corrected it seems in MySQL 5, after which everyone will always be able to find their records independent of the data contained in the table. Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents http://www.scibit.com/products/mysqlx http://www.scibit.com/products/mascon > -Original Message- > From: "Luciano Pulvirenti" <[EMAIL PROTECTED]> > To: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> > CC: > Subject: Serious error in update Mysql 4.1.7 > Sent: Fri, 03 Dec 2004 08:18:05 GMT > Received: Fri, 03 Dec 2004 08:22:55 GMT > Read: Fri, 03 Dec 2004 09:24:15 GMT > I am trying Mysql 4.1.7 before putting it in production in 4.0.16 > substitution on Windows NT. > I have found an anomaly for me serious. > I use Visual Basic 6 with ADO last version and the driver MYODBC 3.51.10. > The program produces the following query: > > UPDATE `paghe`.`anagpaghe` > SET `giorni_congedo_mp`=1.25000e+001, > `giorni_congedo_anno_prec_mp`=0.0e+000, > `giorni_permessi_retrib_mp`=2.0e+000, > `giorni_congedo`=1.15000e+001, > `giorni_congedo_anno_prec`=0.0e+000, > `giorni_permessi_retribuiti`=2.0e+000, > `swnuovo`=0 > WHERE `matricola`=43258 > AND `giorni_congedo_mp`=1.25000e+001 > AND `giorni_congedo_anno_prec_mp`=0.0e+000 > AND `giorni_permessi_retrib_mp`=2.0e+000 > AND `giorni_congedo`=1.15000e+001 > AND `giorni_congedo_anno_prec`=0.0e+000 > AND `giorni_permessi_retribuiti`=2.0e+000 > AND `swnuovo`=1 > > > Mysql doesn't succeed to update the record because no succeeds in finding > the record corresponding to the syntax WHERE. > I have made some tests have discover that the cause is > > AND `giorni_congedo`=1.15000e+001 > > In the version 4.0.16 work correctly. > The fields "giorni..." have declared in the structure double(5,1). > Thank you > > > > -- > Internal Virus Database is out-of-date. > Checked by AVG Anti-Virus. > Version: 7.0.290 / Virus Database: 265.4.3 - Release Date: 26/11/2004 > > > > -- > MySQL Windows Mailing List > For list archives: http://lists.mysql.com/win32 > 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: Different password() function ?
Hi Ady,
See your OLD_PASSWORD MySQL option in your MySQL Docs. Or the following
articles:
http://dev.mysql.com/doc/mysql/en/MySQL_4.1_Nutshell.html
http://forum.scibit.com/viewtopic.php?t=195
Kind Regards
SciBit MySQL Team
http://www.scibit.com
MySQL Products:
http://www.scibit.com/products/mycon
http://www.scibit.com/products/mysqlcomponents
http://www.scibit.com/products/mysqlx
http://www.scibit.com/products/mascon
>
> -Original Message-
> From: "Ady Wicaksono" <[EMAIL PROTECTED]>
> To: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
> CC:
> Subject: Different password() function ?
> Sent: Fri, 03 Dec 2004 10:01:06 GMT
> Received: Fri, 03 Dec 2004 09:51:54 GMT
> Read: Fri, 03 Dec 2004 09:57:47 GMT
> I just upgrade my MySQL from 4.0.20 to 4.1.7, however
>
> i found new things here, password() function
>
> in 4.0.20 -> password("xxx") result in 5336eb751494bdb1
> in 4.1.7 -> password("xxx") result in *3E5287812B7D1F947439AC45E739353
>
> how to get backward compatibility for this function ? since i use
> password() to encrypt users password
>
> Thanks
>
> --
> 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]
