Re: Named Pipe for General Query Log
Hi Jake, all, please note that the following remarks are about communicating via named pipes in general, not specific for MySQL. I do _not_ comment whether the idea is good or bad, will work, alternatives, ... Jake Peavy wrote: Hey, I sent this a while ago, but never received a response. This still seems to exist under 5.0.15-standard (at least under mysql-standard-5.0.15-linux-i686-glibc23) Can anyone from MySQL comment on this or should I open it as a bug? Thanks, JP On 6/11/05, Jake Peavy [EMAIL PROTECTED] wrote: Has anyone been able to use a named pipe for their general query log (or any of the other logfiles for that matter)? I tried the following as user mysql: rm /var/lib/mysql/myhost.log mkfifo -m 0660 /var/lib/mysql/myhost.log but the mysql server would not start. Sure: Works as designed. man 2 open will tell you that an open() call on a named pipe synchronizes: Any such call will block until there is a corresponding call at the other end of the pipe. So the general technique is: mkfifo the_pipe reader_command the_pipe writer_command the_pipe Note that the pipe has a limited buffer capacity, so the writer cannot produce more info than the reader has processed: If your reader is slow (say, more and a human watching), the writer has to wait. Also, writing to the pipe fails if there is no reader attached. So if your reader terminates (crash, q input to more, ...), your writer cannot write any more, this may be fatal (depends on error handling). I think it would be very useful to be able to use a FIFO for this so I can use the log for debugging/info without having to create a log rotation script. For any log of a MySQL server, this is IMHO useful _only_ in a test environment, because of the speed and stability restrictions described above. I am running 5.0.2-alpha-standard on linux on i386. These pipe semantics hold for any Unix since pipes were introduced: For anonymous pipes (open() implicit in pipe()), since the early 1970s; for named pipes (aka FIFOs), since ATT Unix System V in the late 1980s (AFAIR). HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where is the mistake in this SQL statement?
Hi all, Peter! Sujay Koduri wrote: You havent specified the datatype for the column 'id'. Right, this should be fatal. But I also take issue with other parts: [[...]] -Original Message- From: Peter Matulis [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 15, 2005 10:57 AM [[...]] Hello, I am using MySQL 4.0.24 on OpenBSD 3.8. I am having difficulty creating a SQL schema. My attempts contain lines like: CREATE TABLE users ( id PRIMARY KEY, priority integer NOT NULL DEFAULT '7', policy_id integer unsigned NOT NULL DEFAULT '1', Even though this may work, it is wrong IMNSHO: You set character strings as default values for numeric columns! Your strings consist of digits only, so they can (and will) be converted to numbers, but IMO you should not make use of that. [[...]] Regards, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Anyone use Snort and Acid?
I used snort and acid but that go me nowhere -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Monday, November 14, 2005 11:18 PM To: mysql@lists.mysql.com; Ed Kasky Subject: Re: Anyone use Snort and Acid? . . . . . . . . . . . . . . . . . . Randomly Generated Quote (125 of 1011): It must be a peace without victory Only a peace between equals can last. --Woodroe Wilson Whoever generated this quote needs to use a spellchecker; the correct spelling is WOODROW Wilson. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- 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: where is the mistake in this SQL statement?
In article [EMAIL PROTECTED], Joerg Bruehe [EMAIL PROTECTED] writes: CREATE TABLE users ( id PRIMARY KEY, priority integer NOT NULL DEFAULT '7', policy_id integer unsigned NOT NULL DEFAULT '1', Even though this may work, it is wrong IMNSHO: You set character strings as default values for numeric columns! Your strings consist of digits only, so they can (and will) be converted to numbers, but IMO you should not make use of that. You're right, but MySQL thinks otherwise: CREATE TABLE t1 ( val INT NOT NULL DEFAULT 42 ); SHOW CREATE TABLE t1; returns CREATE TABLE `t1` ( `val` int(11) NOT NULL default '42' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci on 5.0.15. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where is the mistake in this SQL statement?
Harald Fuchs wrote: In article [EMAIL PROTECTED], Joerg Bruehe [EMAIL PROTECTED] writes: CREATE TABLE users ( id PRIMARY KEY, priority integer NOT NULL DEFAULT '7', policy_id integer unsigned NOT NULL DEFAULT '1', Even though this may work, it is wrong IMNSHO: You set character strings as default values for numeric columns! Your strings consist of digits only, so they can (and will) be converted to numbers, but IMO you should not make use of that. You're right, but MySQL thinks otherwise: [snip] MySQL is very weakly typed. This behaviour is by design. Jasper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Duplicate Insert Crashes Client
Ok, I found this one too. It was my own client, and of course, the problem was on my side of things. I want to thank everyone who replied. What was happening was I was trying to free the mysql structure and it was already freed. On Nov 14, 2005, at 6:50 PM, Gleb Paharenko wrote: Hello. Ok new problem. If for some reason, my client tries to INSERT something Please could you answer, what kind of client do you mean. Is it your own client application or mysql command line client or something else? If it is yours and it is small, you can send the code to the list, include the 'CREATE' statement for you table and sample data. Provide information about operating system and MySQL version. Bruce Martin wrote: Hello again, Ok new problem. If for some reason, my client tries to INSERT something to the database that is identical to a record already there, my client crashes. Is there an error I can trap for this? The DB does insert the new record. Bruce Martin The Martin Solution PO Box 644 Delaware Water Gap, PA (570) 421-0670 [EMAIL PROTECTED] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Bruce Martin The Martin Solution PO Box 644 Delaware Water Gap, PA (570) 421-0670 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
differenced backup from sql dumps
We are making whole database sql dump every night. Now I have a bunch of sql dumps, that covers much space. Is there opensource tools with whom I could make one smaller differencial backup file with possibility to get dump from every signle day? Any ideas?:) Thankyou! Aktvists Advertisement: MEKLEJAM LACPLESI! Atrodi, nofotografe un publice! WWW.APOLLO.LV
Background tasks performed by MySQL?
Hi. We have a MySQLd with both MyISAM and InnoDB tables that at uneven intervals stops responding correctly to connections. At all times, about one connection per minut fails, regardless of which database and/or user and/or remote host is connecting. The same connection parameters (and same queries) work correctly 99.9% of the time, and it is entirely random which connections time out and when. We can live with that problem, which does not seem to have any explanation. But some times, MySQLd starts taking all the CPU it can get, and gets extremely sluggish for a few minutes. At these times, several connections every second are rejected because of timeouts. These rejections we can't live with. To attempt solving the problem, I've started thinking that there might be some form of periodical cleanup that MySQLd or InnoDB performs automatically, and that we could force it to perform at night when the expected load is lower. Is there any such background cleanup performed? It could be periodical, when a certain number of queries/updates/inserts have been run, or when some query cache or similar gets full? If these problems or descriptions somehow ring a bell, I would welcome any insight I could get from the list. Thanks in advance, /Viktor... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Pk vs index
hi all.. i have created a table (INNODB) with a primary key column. And all the queries i am running are of the following type only. select col1,col2... from table where primary_key_col = value My question here is will mysql automatically create an index on this column which is having the primary key constraint. (This is what i am asusming all the time, as oracle do this) But when i do a show table status on this table, it is giving the index length as 0. (does this mean there is no index on this column ?) And if i create an index now on this primary key column, is it going to increase my performance anyway. I am using mysql 4.1.14 on RHEL 3.0 Thank you sujay
Optimal configuration on slow/old machines
Hello! I run a picture-database (http://gissmoh.kicks-ass.net) on Ubuntu Breezy on an old machine: processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 7 model name : Pentium III (Katmai) stepping: 3 cpu MHz : 501.226 cache size : 512 KB fdiv_bug: no hlt_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 2 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 sep mtrr pge mca cmov pat pse36 mmx fxsr sse bogomips: 993.28 mysql Ver 12.22 Distrib 4.0.24, for pc-linux-gnu (i486) key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K query_cache_limit = 1048576 query_cache_size= 16777216 query_cache_type= 1 # log-bin = /var/log/mysql/mysql-bin.log # max_binlog_size = 104857600 # binlog-do-db = include_database_name # binlog-ignore-db = include_database_name skip-bdb [mysqldump] quick quote-names max_allowed_packet = 16M [isamchk] key_buffer = 16M Is there a way to optimize MySQL Performance? There a not many clients connected at the same time. Thanx -- Jochen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimal configuration on slow/old machines
Am Dienstag, 15. November 2005 13:29 schrieb Jochen Kächelin: Ups!! Wrong link! The right one: http://gissmoh.kicks-ass.net:81 -- Jochen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: differenced backup from sql dumps
2005/11/14, [EMAIL PROTECTED] [EMAIL PROTECTED]: We are making whole database sql dump every night. Now I have a bunch of sql dumps, that covers much space. Is there opensource tools with whom I could make one smaller differencial backup file with possibility to get dump from every signle day? Any ideas?:) diff ? -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pk vs index
Yes, MySQL will create an index on the field you specify as the primary key. Instead of using show table status, use desc tablename. That will show you which fields have indexes on them. The field with the primary key index will have a PRI in the key column. Indexes will always increase performance when use properly. On Nov 15, 2005, at 6:47 AM, Sujay Koduri wrote: hi all.. i have created a table (INNODB) with a primary key column. And all the queries i am running are of the following type only. select col1,col2... from table where primary_key_col = value My question here is will mysql automatically create an index on this column which is having the primary key constraint. (This is what i am asusming all the time, as oracle do this) But when i do a show table status on this table, it is giving the index length as 0. (does this mean there is no index on this column ?) And if i create an index now on this primary key column, is it going to increase my performance anyway. I am using mysql 4.1.14 on RHEL 3.0 Thank you sujay -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pk vs index
Sujay Koduri [EMAIL PROTECTED] wrote on 11/15/2005 06:47:43 AM: hi all.. i have created a table (INNODB) with a primary key column. And all the queries i am running are of the following type only. select col1,col2... from table where primary_key_col = value My question here is will mysql automatically create an index on this column which is having the primary key constraint. (This is what i am asusming all the time, as oracle do this) But when i do a show table status on this table, it is giving the index length as 0. (does this mean there is no index on this column ?) And if i create an index now on this primary key column, is it going to increase my performance anyway. I am using mysql 4.1.14 on RHEL 3.0 Thank you sujay For InnoDB, the primary key is also a clustering key (the other storage engines do not cluster their data). That means that the data is physically arranged on disk in the order of the PK. No extra space is required for storage so the size of this key is 0. Make sense? And, yes, with MySQL a PK *is* an index (for all storage engines). No need to create a separate key on the same column(s) in an attempt to improve search performance. Just for the sake of completeness: An INDEX is the same thing as a KEY. The two terms are interchangeable. Any KEY will provide a sorted list of the terms used to create the key so that finding instances of those terms in the data will be much faster. A table can contain multiple INDEXes(KEYs). A UNIQUE key is an index with a constraint built into it. It will cause the database engine to reject any record that will duplicate any item already indexed by it. A table can contain multiple UNIQUE indexes A PRIMARY KEY is also an index with a uniqueness constraint (like UNIQUE) and even more. There can only be one PK defined on any table and for the InnoDB engine the PK determines the physical sequencing of data as it is written to disk. The PRIMARY KEY also plays roles in secondary index creation and physical table organization in the other storage engines but not to the extent it does with InnoDB. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: differenced backup from sql dumps
On Mon, 14 Nov 2005, [EMAIL PROTECTED] wrote: We are making whole database sql dump every night. Now I have a bunch of sql dumps, that covers much space. Is there opensource tools with whom I could make one smaller differencial backup file with possibility to get dump from every signle day? SCCS, RCS, cvs, svn. All of these use differential systems for archiving versions of a file. I've links to information at http://www.eng.cse.dmu.ac.uk/~hgs/#RCS if that's any help. Any ideas?:) Thankyou! Aktvists Hugh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimal configuration on slow/old machines
Jochen Kächelin [EMAIL PROTECTED] wrote on 11/15/2005 07:46:13 AM: Am Dienstag, 15. November 2005 13:29 schrieb Jochen Kächelin: Ups!! Wrong link! The right one: http://gissmoh.kicks-ass.net:81 -- Jochen Hardware recommendations: Add as much RAM as possible and if you can, swap out your hard drives with something faster. SQL performance recommendations: http://dev.mysql.com/doc/refman/4.1/en/optimization.html (read the whole chapter) That should get you started. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Changing range of identity column for future inserts
I would like to change one of my tables so that future inserts use a higher range of numbers for the primary key values. I have a table 'event' with column: id int unsigned not null auto_increment primary key Currently new records are going into the table with id column values in the 3,000,000-range. I'd like to change that so that new records get id column values in the 13,000,000 range or so ... but without changing the values of existing records. This is so I can combine data into another, existing table with 12 million entries, without overlapping id numbers or assigning different ones. I can't find how to do this on the MySQL site. Any suggestions? Thanks, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Changing range of identity column for future inserts
I think you want that: When you add an AUTO_INCREMENT column, column values are filled in with sequence numbers for you automatically. For MyISAM tables, you can set the first sequence number by executing SET INSERT_ID=value before ALTER TABLE or by using the AUTO_INCREMENT=value table option. From MySQL 5.0.3, you can use the ALTER TABLE ... AUTO_INCREMENT=value table option for InnoDB tables to set the sequence number for new rows if the value is greater than the maximum value in the AUTO_INCREMENT column. (from http://dev.mysql.com/doc/refman/5.0/en/alter-table.html) hth, melanie From: Dan Buettner [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Changing range of identity column for future inserts Date: Tue, 15 Nov 2005 08:50:03 -0600 I would like to change one of my tables so that future inserts use a higher range of numbers for the primary key values. I have a table 'event' with column: id int unsigned not null auto_increment primary key Currently new records are going into the table with id column values in the 3,000,000-range. I'd like to change that so that new records get id column values in the 13,000,000 range or so ... but without changing the values of existing records. This is so I can combine data into another, existing table with 12 million entries, without overlapping id numbers or assigning different ones. I can't find how to do this on the MySQL site. Any suggestions? Thanks, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: With Rollup wrong results
Björn Persson wrote: Noel Stratton: As you can see the summary total in the 'Total' column is incorrect. The summary total should be 36 but is spitting out 40. I am not even sure where it is getting 40. The only way to get 40 is if it is multiplying 5X8. Isn't that what you told it to do? Well, that obviously wasn't his intent. ROUND(price*count(log.product), 2) AS 'Total' For the summary line that means Pick a price field at random (as there are several rows to choose from) and multiply it with the count of all the log.product fields. How do you know that? Or more appropriately, how was he supposed to know that? The manual gives the clear impression that WITH ROLLUP simply totals columns. Based on that, it really wasn't unreasonable to expect that the ROLLUP row for the price*COUNT(log.product) column would be the total of that column. You are certainly right that it doesn't work that way, but that is undocumented. Instead, it added up the COUNTs and then multiplied by a single price. That's probably correct behavior, but it is certainly counterintuitive. Gleb Paharenko wrote: Hello. In my opinion, you have something wrong with your query in general. product.price field is in SELECT part and not in GROUP BY, so the result is not-predictable. See: http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html I think you've missed the point. I cannot be certain, but it seems clear that there is a unique price for each product. In that case, the manual page you reference makes it quite clear that selecting price is perfectly allowable *in MySQL* with a GROUP BY on product, as there will be no ambiguity. That is, the value of price for each group is entirely predictable. The problem comes in the interaction between price and WITH ROLLLUP. It seems that while MySQL allows extra, unique-valued columns with a GROUP BY, they turn into nonsense in the ROLLUP results. (Although I note the result is still predictable, as MySQL seems to simply use the last value found.) Again, that's probably correct behavior, but it is utterly undocumented. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Background tasks performed by MySQL?
I would expect this to finally be something on the client end, rather than the server end... is there a search index that gets rebuilt periodically? Maybe some reports that get generated against the data? The last example that comes to my mind is if you use a client that caches data, does the cache get dumped or the client restarted at some point? These are the sort of things that we found led to the type of behavior you are talking about. Let me give clearer examples... Our databases typically do Discussion Boards... usually very large scale discussion boards (think eBay or HBO scales). The discussion board server (in this case the database client) keeps it's own search index, but need to update it on a regular basis to keep it current. If that period is too infrequent or the queries poorly optimized, they can generate a lot of load on the database, and you get the type of results you are seeing. Or if the discussion board tries to analyze the stats for the last day (or week or month etc) to provide information for reports... in our example a million page views a day means a million stats records a day, and any analysis can be quite the load generator. Same thing with our cache on our discussion board... if our discussion board has been up for some time it has all the messages most frequently used already in local cache, it doesn't do a query to recover each message in this situation... an instance of the discussion board going live into production with no data in the cache can mean a huge database hit for a few minutes while the caches in the discussion board get populated. These are just examples from our life, but I'm pretty sure when al is said and done that the cause will be some process that your client is generating to do something periodic, rather than the MySQL Server running some sort of process, which we've never seen. Take a look at the process list when it is in one of these cycles (from the mysql command line client type show processlist;). it should give you a pretty good idea of what's doing what at the time and will give you some idea on where to look. Best Regards, Bruce On Nov 15, 2005, at 2:20 AM, Viktor Fougstedt wrote: Hi. We have a MySQLd with both MyISAM and InnoDB tables that at uneven intervals stops responding correctly to connections. At all times, about one connection per minut fails, regardless of which database and/or user and/or remote host is connecting. The same connection parameters (and same queries) work correctly 99.9% of the time, and it is entirely random which connections time out and when. We can live with that problem, which does not seem to have any explanation. But some times, MySQLd starts taking all the CPU it can get, and gets extremely sluggish for a few minutes. At these times, several connections every second are rejected because of timeouts. These rejections we can't live with. To attempt solving the problem, I've started thinking that there might be some form of periodical cleanup that MySQLd or InnoDB performs automatically, and that we could force it to perform at night when the expected load is lower. Is there any such background cleanup performed? It could be periodical, when a certain number of queries/updates/inserts have been run, or when some query cache or similar gets full? If these problems or descriptions somehow ring a bell, I would welcome any insight I could get from the list. Thanks in advance, /Viktor... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where is the mistake in this SQL statement?
--- Joerg Bruehe [EMAIL PROTECTED] wrote: Sujay Koduri wrote: You havent specified the datatype for the column 'id'. Right, this should be fatal. But I also take issue with other parts: CREATE TABLE users ( id PRIMARY KEY, priority integer NOT NULL DEFAULT '7', policy_id integer unsigned NOT NULL DEFAULT '1', Even though this may work, it is wrong IMNSHO: You set character strings as default values for numeric columns! Your strings consist of digits only, so they can (and will) be converted to numbers, but IMO you should not make use of that. I don't understand. I have integer there. Where is character strings? __ Find your next car at http://autos.yahoo.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing range of identity column for future inserts
mel list_php wrote: From: Dan Buettner [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Changing range of identity column for future inserts Date: Tue, 15 Nov 2005 08:50:03 -0600 I would like to change one of my tables so that future inserts use a higher range of numbers for the primary key values. I have a table 'event' with column: id int unsigned not null auto_increment primary key Currently new records are going into the table with id column values in the 3,000,000-range. I'd like to change that so that new records get id column values in the 13,000,000 range or so ... but without changing the values of existing records. This is so I can combine data into another, existing table with 12 million entries, without overlapping id numbers or assigning different ones. I can't find how to do this on the MySQL site. Any suggestions? Thanks, Dan I think you want that: When you add an AUTO_INCREMENT column, column values are filled in with sequence numbers for you automatically. For MyISAM tables, you can set the first sequence number by executing SET INSERT_ID=value before ALTER TABLE or by using the AUTO_INCREMENT=value table option. From MySQL 5.0.3, you can use the ALTER TABLE ... AUTO_INCREMENT=value table option for InnoDB tables to set the sequence number for new rows if the value is greater than the maximum value in the AUTO_INCREMENT column. (from http://dev.mysql.com/doc/refman/5.0/en/alter-table.html) hth, melanie The ALTER TABLE solution works in 4.1, as well http://dev.mysql.com/doc/refman/4.1/en/alter-table.html: ALTER TABLE event AUTO_INCREMENT=1300; Note, however, that any ALTER TABLE operation makes a new copy of your table and replaces the old with the new when done. This can take a while with a large table, and writes are locked out until it's finished. You can also simply insert a dummy row with the id column explicitly named and set to 1300. The next regularly inserted row will get id = 1301. Subsequent rows will get higher numbers. You can verify that the next Auto_increment value has been changed by looking at the output of SHOW TABLE STATUS LIKE 'event'; You can delete the dummy row immediately with MyISAM tables. With InnoDB, you can delete it as soon as you have one real row with an id 1300. (See the manual for details http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-column.html). Michael Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do I?
Hello everyone, I have changed a value of max_allowed_packet in my.cnf. Do I need to restart MySQL itself for the change to take place or is there a way to refresh this variable (parameter) without restart of MySQL? I have looked through documentation and www.mysql.com and could not find the answer. Below is info on my environment: mysql status -- mysql Ver 14.7 Distrib 4.1.9, for sun-solaris2.8 (sparc) Connection id: 7230 Current database: Current user: xx SSL:Not in use Current pager: stdout Using outfile: '' Using delimiter:; Server version: 4.1.9-standard Protocol version: 10 Connection: Localhost via UNIX socket Server characterset:latin1 Db characterset:latin1 Client characterset:latin1 Conn. characterset:latin1 UNIX socket:/tmp/mysql.sock Uptime: 12 days 11 hours 48 min 22 sec Threads: 1 Questions: 47908924 Slow queries: 983 Opens: 4964 Flush tables: 1 Open tables: 275 Queries per second avg: 44.389 Thank you in advance, Mikhail Berman
RE: Changing range of identity column for future inserts
Yes, I saw that tried it, but turned out I was just doing it wrong. I had an errant SET in my statement. What works is: ALTER TABLE event AUTO_INCREMENT = 1300 Thanks Dan At 2:59 PM + 11/15/05, mel list_php wrote: I think you want that: When you add an AUTO_INCREMENT column, column values are filled in with sequence numbers for you automatically. For MyISAM tables, you can set the first sequence number by executing SET INSERT_ID=value before ALTER TABLE or by using the AUTO_INCREMENT=value table option. From MySQL 5.0.3, you can use the ALTER TABLE ... AUTO_INCREMENT=value table option for InnoDB tables to set the sequence number for new rows if the value is greater than the maximum value in the AUTO_INCREMENT column. (from http://dev.mysql.com/doc/refman/5.0/en/alter-table.html) hth, melanie From: Dan Buettner [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Changing range of identity column for future inserts Date: Tue, 15 Nov 2005 08:50:03 -0600 I would like to change one of my tables so that future inserts use a higher range of numbers for the primary key values. I have a table 'event' with column: id int unsigned not null auto_increment primary key Currently new records are going into the table with id column values in the 3,000,000-range. I'd like to change that so that new records get id column values in the 13,000,000 range or so ... but without changing the values of existing records. This is so I can combine data into another, existing table with 12 million entries, without overlapping id numbers or assigning different ones. I can't find how to do this on the MySQL site. Any suggestions? Thanks, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- 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: where is the mistake in this SQL statement?
Hi Peter, all! Peter Matulis wrote: --- Joerg Bruehe [EMAIL PROTECTED] wrote: [[...]] CREATE TABLE users ( id PRIMARY KEY, priority integer NOT NULL DEFAULT '7', policy_id integer unsigned NOT NULL DEFAULT '1', Even though this may work, it is wrong IMNSHO: You set character strings as default values for numeric columns! Your strings consist of digits only, so they can (and will) be converted to numbers, but IMO you should not make use of that. I don't understand. I have integer there. Where is character strings? The column type is integer, right. But the default values '7' and '1' are strings, by their quotes; only the unquoted numbers 7 and 1 would be integer. I know that MySQL silently converts, if possible. I was not aware of the quoting that happens in the SHOW CREATE TABLE output which Harald then showed - sorry. So it is quite possible that Peter gave plain integer default values but then used an output in his mail which had got the quotes added. Please accept my apologies for this. Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How do I?
Yes, you can change it at runtime by doing the following SET GLOBAL max_allowed_packet = 1024 (or whatever size); Not only this varaible, whatever variables are listed here http://dev.mysql.com/doc/refman/5.0/en/dynamic-system-variables.html ,you can change them at runtime without restarting the server. Have a look at this also http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html sujay -Original Message- From: Mikhail Berman [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 15, 2005 8:58 PM To: mysql@lists.mysql.com Subject: How do I? Hello everyone, I have changed a value of max_allowed_packet in my.cnf. Do I need to restart MySQL itself for the change to take place or is there a way to refresh this variable (parameter) without restart of MySQL? I have looked through documentation and www.mysql.com and could not find the answer. Below is info on my environment: mysql status -- mysql Ver 14.7 Distrib 4.1.9, for sun-solaris2.8 (sparc) Connection id: 7230 Current database: Current user: xx SSL:Not in use Current pager: stdout Using outfile: '' Using delimiter:; Server version: 4.1.9-standard Protocol version: 10 Connection: Localhost via UNIX socket Server characterset:latin1 Db characterset:latin1 Client characterset:latin1 Conn. characterset:latin1 UNIX socket:/tmp/mysql.sock Uptime: 12 days 11 hours 48 min 22 sec Threads: 1 Questions: 47908924 Slow queries: 983 Opens: 4964 Flush tables: 1 Open tables: 275 Queries per second avg: 44.389 Thank you in advance, Mikhail Berman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Remove all non-alpha characters?
Hello List-people , I am looking to remove all non-alphanumeric characters from a column: UPDATE mytable SET mycolumn = REMOVE_NON_ALPHAS(mycolumn) WHERE mycolumn REGEXP '[^[:alpha:]]' where REMOVE_NON_ALPHAS() is a fictional function. How can I achieve this result? MySQL 3.23, BTW. -- Wellington -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How do I?
Thank you Sujay and everyone else for your help Mikhail Berman -Original Message- From: Sujay Koduri [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 15, 2005 11:03 AM To: Mikhail Berman; mysql@lists.mysql.com Subject: RE: How do I? Yes, you can change it at runtime by doing the following SET GLOBAL max_allowed_packet = 1024 (or whatever size); Not only this varaible, whatever variables are listed here http://dev.mysql.com/doc/refman/5.0/en/dynamic-system-variables.html ,you can change them at runtime without restarting the server. Have a look at this also http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html sujay -Original Message- From: Mikhail Berman [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 15, 2005 8:58 PM To: mysql@lists.mysql.com Subject: How do I? Hello everyone, I have changed a value of max_allowed_packet in my.cnf. Do I need to restart MySQL itself for the change to take place or is there a way to refresh this variable (parameter) without restart of MySQL? I have looked through documentation and www.mysql.com and could not find the answer. Below is info on my environment: mysql status -- mysql Ver 14.7 Distrib 4.1.9, for sun-solaris2.8 (sparc) Connection id: 7230 Current database: Current user: xx SSL:Not in use Current pager: stdout Using outfile: '' Using delimiter:; Server version: 4.1.9-standard Protocol version: 10 Connection: Localhost via UNIX socket Server characterset:latin1 Db characterset:latin1 Client characterset:latin1 Conn. characterset:latin1 UNIX socket:/tmp/mysql.sock Uptime: 12 days 11 hours 48 min 22 sec Threads: 1 Questions: 47908924 Slow queries: 983 Opens: 4964 Flush tables: 1 Open tables: 275 Queries per second avg: 44.389 Thank you in advance, Mikhail Berman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Grant confusion
Simple question: I'd like to create a user that has all privileges for just one database on the server. When I try the following: grant all privileges on my_db.* to 'myuser'@'%.mydomain.com' identified by 'foobar'; the statement runs fine. If I think try to connect to the server through the mysql client like this: mysql -u myuser -pfoobar my_db I get access denied... So then if I do this: grant all privileges on *.* to 'myuser'@'%.mydomain.com' identified by 'foobar'; I can connect no problem. What am I doing wrong? I definitely don't want this user to have privileges on other databases but I also want the user to be functional. Thanks, Tripp __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question
question
Re: Question
Chris Guo wrote: question answer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to create database in different location
Hi, We are using mysql as backend database for one of the application in our company server, and there are too many data in the Mysql database. So I wonder how I create a database on different location. Any help would be highly appreciated. Chris
Re: Grant confusion
Tripp Bishop wrote: Simple question: I'd like to create a user that has all privileges for just one database on the server. When I try the following: grant all privileges on my_db.* to 'myuser'@'%.mydomain.com' identified by 'foobar'; the statement runs fine. You can verify it worked with SHOW GRANTS FOR 'myuser'@'%.mydomain.com'; If you need to follow up, show us that output. If I think try to connect to the server through the mysql client like this: mysql -u myuser -pfoobar my_db No -h, so this is [EMAIL PROTECTED] That's fine, but could be important. Mysql users are [EMAIL PROTECTED], with hoost being primary. When you try to connect, mysql searches the user table for the *best* match of [EMAIL PROTECTED] Best match means most specific, with host taking precedence over user. When trying to connect as [EMAIL PROTECTED], then, here are some possible users which would match: [EMAIL PROTECTED] ''@localhost [EMAIL PROTECTED] ''@thismachine.mydomain.com myuser@'%.mydomain.com' ''@'%.mydomain.com' myuser@'%' ''@'%' (A blank username, '', is the anonymous user.) I've arranged those in descending order of specificity, so the first one which exists will be the one used. One possibility, then, is that you have another [EMAIL PROTECTED] definition which is taking precedence over the one you defined. I get access denied... What is the exact error message? So then if I do this: grant all privileges on *.* to 'myuser'@'%.mydomain.com' identified by 'foobar'; I can connect no problem. What am I doing wrong? I definitely don't want this user to have privileges on other databases but I also want the user to be functional. Adding privileges to other dbs shouldn't fix the problem. Another possibility, then, is a typo in the first definition. Now that you can get in, try SELECT CURRENT_USER(); to verify your actual, rather than intended, identity. Thanks, Tripp See the manual for all the details http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database IDs
Hello. I suppose I should file a bug report then? Could you provide a repeatable test case for you problem? If you're using LAST_INSERT_ID() in a wrong way, that doesn't mean that there is something wrong with MySQL. Björn Persson wrote: Gleb Paharenko: So in you query both last_insert_id() should return the same value, which equals to the value that was set for an AUTO_INCREMENT column by the last INSERT or UPDATE query. Note that you have one query, which just inserts several rows, so during it is processed the returned value of last_insert_id() is constant, even if your bulk insert is changing an AUTO_INCREMENT field. That's what I thought, and on one server I have (4.0.21) it seems to work=20 reliably that way. On another server (4.1.10a) that kind of insertions fail= ed=20 on a foreign key constraint. (I use InnoDB.) On a third box (4.1.14) it see= ms=20 to work sometimes and fail sometimes. I think what happens when it fails is= =20 that the second last_insert_ID() gets the ID of the first row in the same=20 query. I suppose I should file a bug report then? Bj=F6rn Persson -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Turn of bin log for a session
Hey all, I need to do some repairs on a replication master/slave and can't remember the command to turn off bin logging on the master for a single session. Set session log_bin = off; Just returns an error: ERROR 1193: Unknown system variable 'LOG_BIN' MySQL ver 4.1.13 Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump
I have a very simple database I am trying to backup. I run myslqdump and it gives me the code below. Which does nothing but cause errors when I try to run it. Thank you in advance for your help -Aaron -- MySQL Administrator dump 1.4 -- -- -- -- Server version 4.1.11-nt /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; -- -- Create schema `inventory` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `inventory`; USE `inventory`; -- -- Table structure for table `inventory`.`bug_item` -- DROP TABLE IF EXISTS `bug_item`; CREATE TABLE `bug_item` ( `id` int(4) NOT NULL default '0', `title` text NOT NULL, `description` text, `pageLink` text, `status_id` int(4) NOT NULL default '0', `user_id` int(4) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `inventory`.`bug_item` -- /*!4 ALTER TABLE `bug_item` DISABLE KEYS */; /*!4 ALTER TABLE `bug_item` ENABLE KEYS */; -- -- Table structure for table `inventory`.`bug_item_log` -- DROP TABLE IF EXISTS `bug_item_log`; CREATE TABLE `bug_item_log` ( `log_id` int(4) NOT NULL default '0', `bug_id` int(4) NOT NULL default '0', `dt_timestamp` datetime NOT NULL default '-00-00 00:00:00', `title` text NOT NULL, `description` text, `pageLink` text, `status_id` int(4) NOT NULL default '0', `user_id` int(4) NOT NULL default '0', PRIMARY KEY (`log_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `inventory`.`bug_item_log` -- /*!4 ALTER TABLE `bug_item_log` DISABLE KEYS */; /*!4 ALTER TABLE `bug_item_log` ENABLE KEYS */; -- -- Table structure for table `inventory`.`bug_status` -- DROP TABLE IF EXISTS `bug_status`; CREATE TABLE `bug_status` ( `status_id` int(4) NOT NULL default '0', `title` text NOT NULL, PRIMARY KEY (`status_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `inventory`.`bug_status` -- /*!4 ALTER TABLE `bug_status` DISABLE KEYS */; /*!4 ALTER TABLE `bug_status` ENABLE KEYS */; -- -- Table structure for table `inventory`.`bug_user` -- DROP TABLE IF EXISTS `bug_user`; CREATE TABLE `bug_user` ( `user_id` int(4) NOT NULL default '0', `email` text NOT NULL, `password` varchar(50) NOT NULL default '', `user_name` varchar(200) default NULL, `phone` varchar(50) default NULL, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `inventory`.`bug_user` -- /*!4 ALTER TABLE `bug_user` DISABLE KEYS */; /*!4 ALTER TABLE `bug_user` ENABLE KEYS */; -- -- Table structure for table `inventory`.`inv_item` -- DROP TABLE IF EXISTS `inv_item`; CREATE TABLE `inv_item` ( `inv_id` int(10) unsigned NOT NULL auto_increment, `title` varchar(150) default NULL, `description` text, `inv_list` varchar(150) default NULL, `inv_stock_num` varchar(150) default NULL, `inv_serial` varchar(150) default NULL, `inv_year` varchar(150) default NULL, `inv_make` varchar(150) default NULL, `inv_model` varchar(150) default NULL, `inv_color` varchar(150) default NULL, PRIMARY KEY (`inv_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `inventory`.`inv_item` -- /*!4 ALTER TABLE `inv_item` DISABLE KEYS */; INSERT INTO `inv_item` (`inv_id`,`title`,`description`,`inv_list`,`inv_stock_num`,`inv_serial`,`inv _year`,`inv_make`,`inv_model`,`inv_color`) VALUES (2,'2000 Ford Truck','PSTRONGTHIS TRUCK IS THE CHIT/STRONG /P\r\nH2FONT face=\Arial, Helvetica, sans-serif\ size=4Seriouely I love this thing!/FONT/H2','$2','124','6587943156854','2005','4dr Truck','EXT','Black'), (3,'2005 Jaguar S-Type','H3 class=subheaderWhat\'s New for the 2005 Jaguar S-Type?/H3\r\nDIV class=photoIMG height=100 alt=\2005 Jaguar S-Type\ src=\http://us.autos1.yimg.com/img.autos.yahoo.com/i/buyersguide/small/s-ty pe11.jpg\ width=200 /DIV\r\nDIV class=contentJaguar\'s 2005 S-Type receives a muscular-looking redesign this year, as well as an updated interior that features Bronze Madrona wood accents, a new instrument panel, clearer displays, and available aluminum trim to replace the traditional wood appliques. A new VDP Edition is available on the S-Type 4.2 V8, which includes leather upholstery with contrasting piping, deep-pile foot well rugs, heated seats, burl walnut trim, walnut and leather trim for the steering wheel and shift knob, auto-leveling xenon headlamps, unique 17-inch multi-spoke wheels and an electronic rear sunshade. The high-performance 2005 Jaguar S-Type R
Missing column in select??
I thought I'd found a mistake... $sql = (select date(date) as date, time_format(time(date),'%H:%i') as time, units, # I forgot to include this round(sum(item1 * units),2), but all the numbers are correct. Does mysql know to multiply by the units column _of the current row_ without my selecting it? I thought I'd read about selecting a column before the point of needing to act on it. --Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump
Aaron Morris wrote: I have a very simple database I am trying to backup. I run myslqdump and it gives me the code below. That is not code. It is a file of sql statements. Which does nothing but cause errors when I try to run it. You can't run it. It is input for the 'mysql' client program. mysql thedumpfile. Thank you in advance for your help -Aaron -- MySQL Administrator dump 1.4 -- -- -- -- Server version 4.1.11-nt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump
Right, that is what I am doing, but it does not work. Have you used mysqldump successfully? - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Aaron Morris [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, November 15, 2005 1:40 PM Subject: Re: mysqldump Aaron Morris wrote: I have a very simple database I am trying to backup. I run myslqdump and it gives me the code below. That is not code. It is a file of sql statements. Which does nothing but cause errors when I try to run it. You can't run it. It is input for the 'mysql' client program. mysql thedumpfile. Thank you in advance for your help -Aaron -- MySQL Administrator dump 1.4 -- -- -- -- Server version 4.1.11-nt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
frustration building
I am still trying to create a schema. I do not get errors when I do so but in phpmyadmin I get these types of warning for five tables: table 1: UNIQUE and INDEX keys should not both be set for column `email` table 2: More than one INDEX key was created for column `mail_id` More than one INDEX key was created for column `rid` table 3: More than one INDEX key was created for column `sid` table 4: More than one INDEX key was created for column `preference` table 5: UNIQUE and INDEX keys should not both be set for column `email` Should I be worried about this? __ Find your next car at http://autos.yahoo.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: frustration building
Peter Matulis [EMAIL PROTECTED] wrote on 11/15/2005 02:47:56 PM: I am still trying to create a schema. I do not get errors when I do so but in phpmyadmin I get these types of warning for five tables: table 1: UNIQUE and INDEX keys should not both be set for column `email` table 2: More than one INDEX key was created for column `mail_id` More than one INDEX key was created for column `rid` table 3: More than one INDEX key was created for column `sid` table 4: More than one INDEX key was created for column `preference` table 5: UNIQUE and INDEX keys should not both be set for column `email` Should I be worried about this? Only if you care about the speed of your INSERTs and how much space your data+indexes take on your hard drives. If you don't care about either of those performance factors, then no, you don't need to worry about those warnings. UNIQUE is not simply a constraint in MySQL it is an index, too. So you don't need to say CREATE TABLE example ( ,a int ,b int ,UNIQUE(b) ,KEY(b) ); Because b is already indexed by the UNIQUE. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: mysqldump
Aaron Morris wrote: Right, that is what I am doing, but it does not work. Since you don't tell us what you did, what error messages you got, or what 'does not work' means, little can be done to assist you. Have you used mysqldump successfully? - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Aaron Morris [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, November 15, 2005 1:40 PM Subject: Re: mysqldump Aaron Morris wrote: I have a very simple database I am trying to backup. I run myslqdump and it gives me the code below. That is not code. It is a file of sql statements. Which does nothing but cause errors when I try to run it. You can't run it. It is input for the 'mysql' client program. mysql thedumpfile. Thank you in advance for your help -Aaron -- MySQL Administrator dump 1.4 -- -- -- -- Server version 4.1.11-nt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Missing column in select??
Stupid question. units is no different from item1 at this point. - Original Message - From: Jonathan Mangin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, November 15, 2005 2:36 PM Subject: Missing column in select?? I thought I'd found a mistake... $sql = (select date(date) as date, time_format(time(date),'%H:%i') as time, units, # I forgot to include this round(sum(item1 * units),2), but all the numbers are correct. Does mysql know to multiply by the units column _of the current row_ without my selecting it? I thought I'd read about selecting a column before the point of needing to act on it. --Jon -- 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: Turn of bin log for a session
At 14:07 -0500 11/15/05, Jeff wrote: Hey all, I need to do some repairs on a replication master/slave and can't remember the command to turn off bin logging on the master for a single session. Set session log_bin = off; Just returns an error: ERROR 1193: Unknown system variable 'LOG_BIN' MySQL ver 4.1.13 You want the SQL_LOG_BIN option. http://dev.mysql.com/doc/refman/5.0/en/set-option.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Remove all non-alpha characters?
Hello. You should think about using REPLACE function. See: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html BTW, 3.23 is a very old version. If you switch to 5.0 you'll be able to use all power of stored functions. Think about UDFs as well (but it could be a very heavy solution). See: http://dev.mysql.com/doc/refman/5.0/en/adding-functions.html Fan, Wellington wrote: Hello List-people , I am looking to remove all non-alphanumeric characters from a column: UPDATE mytable SET mycolumn = REMOVE_NON_ALPHAS(mycolumn) WHERE mycolumn REGEXP '[^[:alpha:]]' where REMOVE_NON_ALPHAS() is a fictional function. How can I achieve this result? MySQL 3.23, BTW. -- Wellington -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Turn of bin log for a session
Hello. SQL_LOG_BIN session variable is what you want. Have a look here: http://dev.mysql.com/doc/refman/5.0/en/set-option.html Jeff wrote: Hey all, I need to do some repairs on a replication master/slave and can't remember the command to turn off bin logging on the master for a single session. Set session log_bin = off; Just returns an error: ERROR 1193: Unknown system variable 'LOG_BIN' MySQL ver 4.1.13 Thanks, Jeff -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump
Hello. Most of MySQL users use successfully mysqldump. What error is mysql command line client reporting during the import? Aaron Morris wrote: Right, that is what I am doing, but it does not work. Have you used mysqldump successfully? - Original Message --- -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to reload my.cnf?
Hi Gleb, Thanks for your reply. I am using MySQL 3.23.33. The ones that I will need to add in my.cnf are the syncronisation parameters as follow: binlog-do-db=XYZ replicate-do-db=XYZ replicate-ignore-table=XYZ.table1 replicate-ignore-table=XYZ.table2 replicate-ignore-table=XYZ.table3 Database XYZ was in synch for the past 12 months, I have found database is not in synch anymore as the parameters I mentioned above are missing in my.cnf. I want to put back those parameters but I can't afford to restart the server as MySQL server is also used by other database which is currently in synch and runs happily. Cheers, TA Hello. You can change some variables without restarting the server. See: http://dev.mysql.com/doc/refman/5.0/en/dynamic-system-variables.html Tedy Aulia wrote: Hi All, I will need to change my.cnf in master machine, but I can't afford to restart MySQL server as the server has been used for heavy traffic databases. Can anyone tell me how to do it? Cheers, *Tedy Aulia* -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@/stripped/ /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com
Re: Database IDs
Gleb Paharenko:
Could you provide a repeatable test case for you problem?
I don't know how repeatable this is. As I said I have one computer where it
seems to work sometimes, as if there's a race condition. Right now it seems
repeatable on the computer I'm using at the moment:
mysql select version();
+---+
| version() |
+---+
| 4.1.14|
+---+
1 row in set (0.00 sec)
mysql create table parent (
- ID int unsigned not null auto_increment,
- value varchar(50),
- primary key (ID)
- );
Query OK, 0 rows affected (0.01 sec)
mysql create table child (
- ID int unsigned not null auto_increment,
- parent_ID int unsigned not null,
- value varchar(50),
- primary key (ID)
- );
Query OK, 0 rows affected (0.00 sec)
mysql insert into parent (value) values ('a');
Query OK, 1 row affected (0.00 sec)
mysql insert into child (parent_ID, value) values
- (last_insert_ID(), 'b1'), (last_insert_ID(), 'b2'),
- (last_insert_ID(), 'b3');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql insert into parent (value) values ('c');
Query OK, 1 row affected (0.00 sec)
mysql insert into child (parent_ID, value) values
- (last_insert_ID(), 'd1'), (last_insert_ID(), 'd2'),
- (last_insert_ID(), 'd3');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql select * from child;
++---+---+
| ID | parent_ID | value |
++---+---+
| 1 | 1 | b1|
| 2 | 1 | b2|
| 3 | 2 | b3|
| 4 | 2 | d1|
| 5 | 4 | d2|
| 6 | 5 | d3|
++---+---+
6 rows in set (0.00 sec)
Note how child rows 1 and 4 have the IDs of their respective parent row in the
parent_ID field, but child rows 3, 5 and 6 have the ID of the preceding child
row instead. (For row 2 we can't tell the difference.)
Here's the SQL code for easy copying:
use test;
create table parent (
ID int unsigned not null auto_increment,
value varchar(50),
primary key (ID)
);
create table child (
ID int unsigned not null auto_increment,
parent_ID int unsigned not null,
value varchar(50),
primary key (ID)
);
insert into parent (value) values ('a');
insert into child (parent_ID, value) values
(last_insert_ID(), 'b1'), (last_insert_ID(), 'b2'),
(last_insert_ID(), 'b3');
insert into parent (value) values ('c');
insert into child (parent_ID, value) values
(last_insert_ID(), 'd1'), (last_insert_ID(), 'd2'),
(last_insert_ID(), 'd3');
select * from child;
Björn Persson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Background tasks performed by MySQL?
Hi! Also look at SHOW INNODB STATUS\G during the slow phase. What does it say about the 'Main thread ... state'? What does it say about transactions? 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 - Original Message - From: Bruce Dembecki [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, November 15, 2005 5:08 PM Subject: Re: Background tasks performed by MySQL? I would expect this to finally be something on the client end, rather than the server end... is there a search index that gets rebuilt periodically? Maybe some reports that get generated against the data? The last example that comes to my mind is if you use a client that caches data, does the cache get dumped or the client restarted at some point? These are the sort of things that we found led to the type of behavior you are talking about. Let me give clearer examples... Our databases typically do Discussion Boards... usually very large scale discussion boards (think eBay or HBO scales). The discussion board server (in this case the database client) keeps it's own search index, but need to update it on a regular basis to keep it current. If that period is too infrequent or the queries poorly optimized, they can generate a lot of load on the database, and you get the type of results you are seeing. Or if the discussion board tries to analyze the stats for the last day (or week or month etc) to provide information for reports... in our example a million page views a day means a million stats records a day, and any analysis can be quite the load generator. Same thing with our cache on our discussion board... if our discussion board has been up for some time it has all the messages most frequently used already in local cache, it doesn't do a query to recover each message in this situation... an instance of the discussion board going live into production with no data in the cache can mean a huge database hit for a few minutes while the caches in the discussion board get populated. These are just examples from our life, but I'm pretty sure when al is said and done that the cause will be some process that your client is generating to do something periodic, rather than the MySQL Server running some sort of process, which we've never seen. Take a look at the process list when it is in one of these cycles (from the mysql command line client type show processlist;). it should give you a pretty good idea of what's doing what at the time and will give you some idea on where to look. Best Regards, Bruce On Nov 15, 2005, at 2:20 AM, Viktor Fougstedt wrote: Hi. We have a MySQLd with both MyISAM and InnoDB tables that at uneven intervals stops responding correctly to connections. At all times, about one connection per minut fails, regardless of which database and/or user and/or remote host is connecting. The same connection parameters (and same queries) work correctly 99.9% of the time, and it is entirely random which connections time out and when. We can live with that problem, which does not seem to have any explanation. But some times, MySQLd starts taking all the CPU it can get, and gets extremely sluggish for a few minutes. At these times, several connections every second are rejected because of timeouts. These rejections we can't live with. To attempt solving the problem, I've started thinking that there might be some form of periodical cleanup that MySQLd or InnoDB performs automatically, and that we could force it to perform at night when the expected load is lower. Is there any such background cleanup performed? It could be periodical, when a certain number of queries/updates/inserts have been run, or when some query cache or similar gets full? If these problems or descriptions somehow ring a bell, I would welcome any insight I could get from the list. Thanks in advance, /Viktor... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [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: Database IDs
Hi Bjorn,
It looks like the LAST_INSERT_ID() is returning the id of the last
auto_increment INSERT, this seems to be in line with the documentation.
quote
The ID that was generated is maintained in the server on a per-connection
basis. This means that the value which the function returns to a given client
is the first AUTO_INCREMENT value generated for most recent statement affecting
an AUTO_INCREMENT column by that client.
/quote
To get what I think you need, you could do
use test;
drop table parent;
drop table child;
create table parent (
ID int unsigned not null auto_increment,
value varchar(50),
primary key (ID)
);
create table child (
ID int unsigned not null auto_increment,
parent_ID int unsigned not null,
value varchar(50),
primary key (ID)
);
insert into parent (value) values ('a');
set @lid=last_insert_id(); -
insert into child (parent_ID, value) values
(@lid, 'b1'), (@lid, 'b2'),
(@lid, 'b3');
insert into parent (value) values ('c');
set @lid=last_insert_id(); -
insert into child (parent_ID, value) values
(@lid, 'd1'), (@lid, 'd2'),
(@lid, 'd3');
select * from child;
With the results being
ID parent_ID value
1 1 b1
2 1 b2
3 1 b3
4 2 d1
5 2 d2
6 2 d3
Regards
David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia
+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax
-Original Message-
From: Björn Persson [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 16 November 2005 10:06 AM
To: mysql@lists.mysql.com
Subject: Re: Database IDs
Gleb Paharenko:
Could you provide a repeatable test case for you problem?
I don't know how repeatable this is. As I said I have one computer where it
seems to work sometimes, as if there's a race condition. Right now it seems
repeatable on the computer I'm using at the moment:
mysql select version();
+---+
| version() |
+---+
| 4.1.14|
+---+
1 row in set (0.00 sec)
mysql create table parent (
- ID int unsigned not null auto_increment,
- value varchar(50),
- primary key (ID)
- );
Query OK, 0 rows affected (0.01 sec)
mysql create table child (
- ID int unsigned not null auto_increment,
- parent_ID int unsigned not null,
- value varchar(50),
- primary key (ID)
- );
Query OK, 0 rows affected (0.00 sec)
mysql insert into parent (value) values ('a');
Query OK, 1 row affected (0.00 sec)
mysql insert into child (parent_ID, value) values
- (last_insert_ID(), 'b1'), (last_insert_ID(), 'b2'),
- (last_insert_ID(), 'b3');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql insert into parent (value) values ('c');
Query OK, 1 row affected (0.00 sec)
mysql insert into child (parent_ID, value) values
- (last_insert_ID(), 'd1'), (last_insert_ID(), 'd2'),
- (last_insert_ID(), 'd3');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql select * from child;
++---+---+
| ID | parent_ID | value |
++---+---+
| 1 | 1 | b1|
| 2 | 1 | b2|
| 3 | 2 | b3|
| 4 | 2 | d1|
| 5 | 4 | d2|
| 6 | 5 | d3|
++---+---+
6 rows in set (0.00 sec)
Note how child rows 1 and 4 have the IDs of their respective parent row in the
parent_ID field, but child rows 3, 5 and 6 have the ID of the preceding child
row instead. (For row 2 we can't tell the difference.)
Here's the SQL code for easy copying:
use test;
create table parent (
ID int unsigned not null auto_increment,
value varchar(50),
primary key (ID)
);
create table child (
ID int unsigned not null auto_increment,
parent_ID int unsigned not null,
value varchar(50),
primary key (ID)
);
insert into parent (value) values ('a');
insert into child (parent_ID, value) values
(last_insert_ID(), 'b1'), (last_insert_ID(), 'b2'),
(last_insert_ID(), 'b3');
insert into parent (value) values ('c');
insert into child (parent_ID, value) values
(last_insert_ID(), 'd1'), (last_insert_ID(), 'd2'),
(last_insert_ID(), 'd3');
select * from child;
Björn Persson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to use sysmbol link to link to a database
Dear all, I am new to Mysql, and we are using Mysql as the backend database in out data server, and I wonder if how I create a symbol link under the Mysql data directory to link to a database under another directory so that we can save disk space. Any help will be highly appreciated. Chris
Re: Anyone use Snort and Acid?
At 08:22 PM Monday, 11/14/2005, Jason Martin wrote -= On Sun, Nov 13, 2005 at 08:56:12PM -0800, Ed Kasky wrote: Snort v2.4.3 ACID v0.9.6b23 I seem to recall hearing that ACID is deprecated; you might want to look around if there is a newer equivalent. SQUIL (http://sguil.sourceforge.net/) is supposed to be really good. -Jason Martin I finally found out the problem. It has to do with the way ACID was referencing the Schema table and info. One of the developers of BASE, a fork project of ACID, pointed this out. In one of the php scripts I changed: $sql = SELECT vseq FROM schema; to $sql = SELECT vseq FROM `schema`; and it works as advertised. Ed . . . . . . . . . . . . . . . . . . Randomly Generated Quote (229 of 1011): When you get to the end of your rope, tie a knot and hang on. --Franklin Delano Roosevelt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: frustration building
--- [EMAIL PROTECTED] wrote: Peter Matulis [EMAIL PROTECTED] wrote on 11/15/2005 02:47:56 PM: but in phpmyadmin I get these types of warning for five tables: table 1: UNIQUE and INDEX keys should not both be set for column `email` table 2: More than one INDEX key was created for column `mail_id` More than one INDEX key was created for column `rid` table 3: More than one INDEX key was created for column `sid` table 4: More than one INDEX key was created for column `preference` table 5: UNIQUE and INDEX keys should not both be set for column `email` Should I be worried about this? Only if you care about the speed of your INSERTs and how much space your data+indexes take on your hard drives. If you don't care about either of those performance factors, then no, you don't need to worry about those warnings. But what are the advantages? If none, why is such a schema proposed? __ Find your next car at http://autos.yahoo.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: frustration building
Peter Matulis wrote: --- [EMAIL PROTECTED] wrote: Peter Matulis [EMAIL PROTECTED] wrote on 11/15/2005 02:47:56 PM: but in phpmyadmin I get these types of warning for five tables: table 1: UNIQUE and INDEX keys should not both be set for column `email` table 2: More than one INDEX key was created for column `mail_id` More than one INDEX key was created for column `rid` table 3: More than one INDEX key was created for column `sid` table 4: More than one INDEX key was created for column `preference` table 5: UNIQUE and INDEX keys should not both be set for column `email` Should I be worried about this? Only if you care about the speed of your INSERTs and how much space your data+indexes take on your hard drives. If you don't care about either of those performance factors, then no, you don't need to worry about those warnings. But what are the advantages? If none, why is such a schema proposed? There are no advantages. It is duplication of indexes which do exactly the same thing. If phpMyAdmin creates that schema it is probably a bug in phpMyAdmin. Upgrade to the latest version (as I use the latest and have seen no such bug) or contact the developers of phpMyAdmin. Jasper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie Query: Error starting MySQL..changed Data Directory
Hi all First usage of MySQL. Newbie in Linux as well as MySQL. Using CentOS 4.2 with MySQL 4.1.12, rpm install. Changed the data directory. My /etc/my.conf [mysqld] datadir=/home.dbdata/mysql socket=/home.dbdata/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Ran these commands for permissions: chown -R mysql:mysql /home.dbdata/mysql chmod -R go-rwx /home.dbdata/mysql Running /etc/rc.d/init.d/mysqld start says that mysql start failed BUT ps -aux shows that mysql is running 0:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pidmysql 6184 0.2 3.4 125808 17636 pts/0 Sl 15:47 0:01 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usrroot 6336 0.0 0.1 2920 744 pts/0R+ 15:57 0:00 ps -aux Log shows 051114 14:51:07 mysqld started 051114 14:51:08 InnoDB: Started; log sequence number 0 43634 /usr/libexec/mysqld: ready for connections. Version: '4.1.12' socket: '/home.dbdata/mysql/mysql.sock' port: 3306 Source distribution 051114 15:47:06 [Note] /usr/libexec/mysqld: Normal shutdown 051114 15:47:06 InnoDB: Starting shutdown... 051114 15:47:09 InnoDB: Shutdown completed; log sequence number 0 43634 051114 15:47:09 [Note] /usr/libexec/mysqld: Shutdown complete 051114 15:47:09 mysqld ended 051114 15:47:33 mysqld started 051114 15:47:33 InnoDB: Started; log sequence number 0 43634 /usr/libexec/mysqld: ready for connections. Version: '4.1.12' socket: '/home.dbdata/mysql/mysql.sock' port: 3306 Source distribution Please advise what I have done wrong. I suspect permissions /or incorrect data directory migration. Please help. With regards. Sanjay. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie Query: Error starting MySQL..changed Data Directory
Running /etc/rc.d/init.d/mysqld start says that mysql start failed BUT This doesn't nesessarily mean that mysql hasn't started. This script waits only for certain time to check if mysql has started or not. If it is not started in that time, it simply says 'mysql start failed'. But mysql may take some more time to come up. So the best practice is to have a look at the logs (as you rightly did) to find out what exactly is going on. And your logs anyway are saying /usr/libexec/mysqld: ready for connections.. This means that mysql has started without any problems and waiting for connections. ps -aux shows that mysql is running And for the same reason I mentioned above, you are seeing this. Hope this helps. sujay -Original Message- From: Sanjay Arora [mailto:[EMAIL PROTECTED] Sent: Monday, November 14, 2005 4:46 PM To: MySql Mailing List Subject: Newbie Query: Error starting MySQL..changed Data Directory Hi all First usage of MySQL. Newbie in Linux as well as MySQL. Using CentOS 4.2 with MySQL 4.1.12, rpm install. Changed the data directory. My /etc/my.conf [mysqld] datadir=/home.dbdata/mysql socket=/home.dbdata/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Ran these commands for permissions: chown -R mysql:mysql /home.dbdata/mysql chmod -R go-rwx /home.dbdata/mysql Running /etc/rc.d/init.d/mysqld start says that mysql start failed BUT ps -aux shows that mysql is running 0:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pidmysql 6184 0.2 3.4 125808 17636 pts/0 Sl 15:47 0:01 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usrroot 6336 0.0 0.1 2920 744 pts/0R+ 15:57 0:00 ps -aux Log shows 051114 14:51:07 mysqld started 051114 14:51:08 InnoDB: Started; log sequence number 0 43634 /usr/libexec/mysqld: ready for connections. Version: '4.1.12' socket: '/home.dbdata/mysql/mysql.sock' port: 3306 Source distribution 051114 15:47:06 [Note] /usr/libexec/mysqld: Normal shutdown 051114 15:47:06 InnoDB: Starting shutdown... 051114 15:47:09 InnoDB: Shutdown completed; log sequence number 0 43634 051114 15:47:09 [Note] /usr/libexec/mysqld: Shutdown complete 051114 15:47:09 mysqld ended 051114 15:47:33 mysqld started 051114 15:47:33 InnoDB: Started; log sequence number 0 43634 /usr/libexec/mysqld: ready for connections. Version: '4.1.12' socket: '/home.dbdata/mysql/mysql.sock' port: 3306 Source distribution Please advise what I have done wrong. I suspect permissions /or incorrect data directory migration. Please help. With regards. Sanjay. -- 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]
