RE: Slave stuck at registering
Can you see the slave thread on the master when you do a show processlist from the master? -Original Message- From: Keith Ivey [mailto:[EMAIL PROTECTED] Sent: Thursday, May 05, 2005 1:48 PM To: mysql@lists.mysql.com Subject: Slave stuck at registering I'm trying to set up replication over the Internet -- something I've done successfully many times before. But this time I'm having problems I haven't run into before, and I'm wondering if they're related to firewall settings or network problems or just something I'm overlooking. For the slave, I'm using the same settings that I used successfully for another slave of the same master at a different location. The relevant section of my.cnf looks like this: master-host = [the master hostname] master-user = [username] master-password = [password] server-id = 47 report-host = [the slave hostname] slave_compressed_protocol = 1 read-only When I start the slave I get the proper connected to master ... replication started in log 'FIRST' at position 4 message in the slave error log. In SHOW SLAVE STATUS I see Connecting to master briefly and then Registering slave on master. It stays in the Registering state for about 500 seconds, after which the I/O thread stops and I get Error on COM_REGISTER_SLAVE: 2013 'Lost connection to MySQL server during query' in the error log. In the error log on the master I get Aborted connection 114025 to db: 'unconnected' user: '[username]' host: `[slave IP]' (Got an error reading communication packets). If I use the mysql command-line client on the slave, I can connect fine to the master, and vice versa. Also, PHP and Perl programs on the slave use databases on the master with no problems. What would replication require that the normal client-server communication doesn't? The master is running 4.0.22 on FreeBSD and the slave is running 4.0.24 on Linux. Any suggestions? -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- 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: Slave stuck at registering
From your previous posts I know you are very competent so no disrespect intended. Was the slave data reloaded from the master, and the master.info reset? What does the master.info log say? -Original Message- From: Keith Ivey [mailto:[EMAIL PROTECTED] Sent: Thursday, May 05, 2005 3:16 PM To: mysql@lists.mysql.com Subject: Re: Slave stuck at registering Victor Pendleton wrote: Can you see the slave thread on the master when you do a show processlist from the master? Yes. It shows up with the command as Sleep, nothing in the State column, and NULL in the Info column. It hangs around for a while until Time reaches a little over 500, and then disappears. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to check if keys disabled?
Try show index from t1; Show index from t2; -Original Message- From: Jacek Becla [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 03, 2005 4:00 PM To: Jay Blanchard Cc: mysql@lists.mysql.com Subject: Re: how to check if keys disabled? Jay Are you sure? DESCRIBE tells me the table has an index, but not whether the index is enabled or not: mysql create table t1 (x int primary key); Query OK, 0 rows affected (0.01 sec) mysql create table t2 (x int primary key); Query OK, 0 rows affected (0.01 sec) mysql alter table t1 disable keys; Query OK, 0 rows affected (0.00 sec) mysql describe t1; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | x | int(11) | NO | PRI | | | +---+-+--+-+-+---+ 1 row in set (0.00 sec) mysql describe t2; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | x | int(11) | NO | PRI | | | +---+-+--+-+-+---+ 1 row in set (0.00 sec) Am I missing something? thanks, Jacek Jay Blanchard wrote: [snip] How can I find out if keys are enabled/disabled for a given table? Suppose I do: create table t1 (x int primary key); create table t2 (x int primary key); alter table t1 disable keys; How can I now find out that t1 has keys disabled, and t2 enabled? [/snip] DESCRIBE t1 or DESCRIBE t2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL server disconnects when executing simple Select stateme nts++
What device driver are you using to access the database? Can you run a sql trace or maybe turn on the logging on the server side? -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 23, 2005 11:05 AM To: MySQL list Subject: MySQL server disconnects when executing simple Select statements++ I have a compiled application that accesses a dedicated MySQL 4.1.1 server with MyISAM tables on Win XP. For some reason when the application is in a loop doing simple single table Select statements, the MySQL server after 48 seconds of processing will upchuck with the error Can't connect to MySQL server on 'localhost' (10048). This machine has the server and application on it and no one else is using it. MaxUsedConnections=3 and connections=3974 after it crashes. It is doing about 20 queries per second before it crashes. It usually crashes near the same record. Seconds after the crash if I have another application do a Show status I get an error Lost connection to MySQL server during query. If I wait a few seconds and re-run it, I get the status results. It is running on Win XP AMD 3500+ with 1g ram. There is plenty of memory because the queries are returning only a dozen rows, maybe less. Any idea how I can eliminate the crashing? 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: import problem.
Does SQLyog log any errors for you? Are your lines terminated by \n or
\r\n? Have you tried performing this import with the LOAD DATA INFILE
command? There you could at least see if warnings or errors were
encountered.
Scott Hamm wrote:
I got my csv delimited using { as follows:
87547{240414986{0{
87547{240414987{0{
87547{240414988{0{
87547{240414989{0{
87547{240414990{1{GALLEGOS---MISKEYED MONTH IN BIRTHDATE
I tried to import using SQLyog as following:
Lines Terminated By: \n
Fields set to Variable length, Fields Terminated by: {
Then issued MySQL command to select orderid=20414989
mysql select * from batch where orderid=240414989;
+---+---++--+
| QAID | OrderID | Errors | Comments |
+---+---++--+
| 240414989 | 0 |
+---+---++--+
1 row in set (0.51 sec)
Why is QAID number not there where not null is specified as following:
create table batch (
QAID int not null,
OrderID int not null,
Errors tinyint not null default 0,
Comments varchar(255)
);
I'm confused and any help would be appreciated.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: one hour is/is not 60 minutes, that's the question...
Is the DATE_ADD(time, INTERVAL) an option? SELECT Now(), DATE_ADD(Now(), interval 60 minute) schlubediwup wrote: Hi again mysql-listers mysql select version(); ++ | version() | ++ | 4.1.9-standard-log | ++ 1 row in set (0.00 sec) mysql [EMAIL PROTECTED]:~ uname -a Linux mydom 2.6.4-54.5-default #1 Fri May 7 21:43:10 UTC 2004 i686 i686 i386 GNU/Linux [EMAIL PROTECTED]:~ mysql select addtime(now(), '00:00:00'); ++ | addtime(now(), '00:00:00') | ++ | 2005-02-15 16:49:17| ++ 1 row in set (0.00 sec) mysql select addtime(now(), '00:60:00'); ++ | addtime(now(), '00:60:00') | ++ | NULL | ++ 1 row in set, 1 warning (0.00 sec) mysql select addtime(now(), '01:00:00'); ++ | addtime(now(), '01:00:00') | ++ | 2005-02-15 17:50:27| ++ 1 row in set (0.00 sec) in my opinion the result of the second and third example above must be the same. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Qcache and read data from master
I believe it may be because the tables in the Query cache have been modified causing the cached results to become invalid. Olivier Kaloudoff wrote: Hi, I noticed the following behaviour on mysql 4.1.8 and two servers; to setup replication, I did the required grant on the master, and choose to use LOAD DATA FROM MASTER on the slave. As we monitor the Qcache usage on the master, it happend that Qcache was reset to 0 Queries during this operation. I would like to know if this behaviour is expected, and would be interrested if someone can explain me why this is necessary ? Olivier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying Database from One Hard Drive to Another
InnoDB files can not be copied from one machine to another like the MyISAM files. If you did not dump the data or you do not have an archived backup you will more than likely have to recreate the table structures. David Blomstrom wrote: Yikes...I've been replying to myself! I forgot that when I reply to messages from the MySQL newsgroup on this e-mail account, it doesn't include mysql@lists.mysql.com, for some odd reason. At any rate, I wanted to say that I have just ONE file type left - FRM. My MYD and MYI files have both vanished. But I took another look and discovered that SOME tables are represented by all three files, and when I paste them into my new MySQL Data folder, they work just fine. I also discovered the following files in my backup folder: ibdata1...215,040 KB ib_logfile1...5,120 KB ib_logfile0...5,120 KB ib_arch_log_0...25 KB Can these somehow be used to reconstitute lost database tables? The situation isn't critical, as I can recreate most of these tables from my original data, which is on spreadsheets. But recreating all the fields and keys would obviously be a pain in the butt; I had over 100 tables. Thanks. __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't install Mysql on Windows XP
Did you install MySQL as a service and have you started the service? Sheni R. Meledath wrote: Hello: I am trying to install mysql on my laptop which is running Windows XP professional. But after installation when trying to connect to the MySQL server I am getting the following error message: Error 2003: Can't connect to MySQL server on 'localhost' (10061). Tried all the options with my knowledge. No luck yet. Can anybody help me out. MySQL installation file/version - mysql4.0.20d-win Regards Sheni R Meledath Assistant Manager - Web Development * Cyber Gear LLC P.O. Box 53735 Dubai, United Arab Emirates Tel: (971 4) 331-2627, Fax: (971 4) 331-8812 Email: [EMAIL PROTECTED], http://www.cyber-gear.com VISIT OUR ASSOCIATE SITES: http://www.Click2Advertising.com http://www.DubaiCityGuide.com http://www.MiddleEastDirectory.com http://www.MiddleEastEvents.com http://www.MiddleEastPostBox.com http://www.PressReleaseNetwork.com * This message has been checked for all known viruses by McAfee. No liability is accepted by virtue of checking this message and you are recommended to have in place your own anti-virus detection procedures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot complete database transfer
Did you install MySQL on the other box? It sounds like you took a snapshot of the data but have not installed the executables yet. James Sherwood wrote: I am trying to transfer a database from one linux box to another, both using redhat. On one I performed a mysqldump and it worked fine. On the other I created the database using a manager and tried the 'mysql username password database filename.sql' command The problem is, I cannot seem to get the box to see the mysql. I found it in init.d I have root access I keep getting 'command not found'. Any ideas would be greatly appreciated James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot connect to local server problem
Have you verified that the MySQL server is currently running? Was this installed as service to automatically start up? Leandro Melo wrote: Hi, i built an application which uses MySQL 4.0.17 using Windows XP Professional. Tomorrow, i need to present the application to my client, so i preparing my enviroment in a laptop, which runs Windows 2000. When i installed MySQL 4.0.17 and tried to run the client from command line, i got the following error message: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) I don't remember this problem when i first installed the db in windows XP (or maybe i just don't remeber solving it). What am i missing? How do i fix that? Thanks, ltcmelo ___ Yahoo! Mail - Agora com 250MB de espaço gratuito. Abra uma conta agora! http://br.info.mail.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot complete database transfer
What do you get when you do a `which mysql` from the command line? James Sherwood wrote: Yes, there are other databases on that mysql installation. The only thing is, I recently upgraded it to the latest release of mysql. James - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: James Sherwood [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, December 15, 2004 10:47 AM Subject: Re: Cannot complete database transfer Did you install MySQL on the other box? It sounds like you took a snapshot of the data but have not installed the executables yet. James Sherwood wrote: I am trying to transfer a database from one linux box to another, both using redhat. On one I performed a mysqldump and it worked fine. On the other I created the database using a manager and tried the 'mysql username password database filename.sql' command The problem is, I cannot seem to get the box to see the mysql. I found it in init.d I have root access I keep getting 'command not found'. Any ideas would be greatly appreciated James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: One of my tables seems to repeatedly hang for 30-180 seconds
What does the show processlist look like? Have you ran top/vmstat/iostat when the `hanging` occurs? Hjalmar Gislason wrote: Hi there, I've been having some trouble with an application that I'm running (www.spurl.net). I have a database that consists of about 20 tables. The largest tables are a little more than one million rows and the database in total around 1GB - so nothing serious there. The amount of queries is on average less than 10 per second and it's running on a pretty powerful machine, so all in all there shouldn't really be any performance issues - and normally there aren't. All of the tables are myISAM. Now, from time to time (even several times per hour) one of my tables seems to hang for 30 - 180 seconds, and as this is a key table in the application it basically brings everything to a halt. The interesting thing is that I can not see a pattern in the web server logs of anything special going on at these times. I have run a check table and analyze table and everything seems to be fine there. So my question is pretty general: What can cause a table to hang like that? Obviously write locks could, but as I said it seems that there is not necessarily any writes or updates going on at the time. And normally (when the server is not in this foul mood) I can run dozens or even hundreds of all my write queries per second. - Can VERY heavy SELECT statements have this effect? - Could it have something to do with indexes (there is only one partial text index on this table in addition to the primary index)? - Could my hosting provider be running some scheduled tasks on the server that cause this? It is a dedicated server and they run backup once a day, which might cause something like this - but that does not go hand in hand with the fact that this happens now and then during the entire day. Basically I'm looking for ideas for what to look into as I'm running out of ideas (have been rewriting queries, making caches and all sort of things). As I'm running this in a hosted environment I don't have full access to everything - but at least a place to start would be really heplful. Thanks in advance, -hjalmar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems Moving from 3.23 to 4 (Win2k)
What errors are being displayed when you attempt to log in? Is anything being written to the error log itself? A. Clausen wrote: I made a copy of my entire Mysql 3.23 data directory and moved it on to a box with a fresh install of MySQL 4. While the NT service starts up fine (I'm using 4.0.22-nt-max), I can't login. I know that there are supposed to be mods made to the mysql database tables, but I thought there was at least a minimum compatibility. The only way to get access to anything is to shut down MySQL, put in the default table that came with the install. What am I doing wrong here? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why is this simple query so slow?
Have you tried optimizing or run an analyze table command on this table? Aaron wrote: Hi all , I have a relatively simple query that is taking longer than I think it should. Can anyone possibly give me some idea why this might be or any potential bottleneck areas I might want to check out? thanks! Here is some information. The query below takes around 8 seconds, and returns 3253 rows. Mysql Version: 4.1.7-standard-log Operating System: Linux 2.4.20-8smp Query:SELECT ID FROM Offers_To_Buy FORCE INDEX(scdd) WHERE subcatID = 72 ; ... ... 3253 rows in set (8.00 sec) Explain says: mysql EXPLAIN SELECT ID FROM Offers_To_Buy FORCE INDEX(scdd) WHERE subcatID = 72 ; ++-+---+--+---+--+-- ---+---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-- ---+---+--+-+ | 1 | SIMPLE | Offers_To_Buy | ref | scdd | scdd | 4 | const | 2988 | Using where | ++-+---+--+---+--+-- ---+---+--+-+ 1 row in set (0.02 sec) mysql SHOW INDEXES FROM Offers_To_Buy ; +---++-+--+- +---+-+--++--++- + | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++-+--+- +---+-+--++--++- + | Offers_To_Buy | 1 | ID |1 | ID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | oldtitle|1 | oldtitle | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | user|1 | userID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | date|1 | deletedate | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | subcategory |1 | subcatID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | country |1 | country | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | source |1 | source | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | scdd|1 | subcatID | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | scdd|2 | deletedate | A |NULL | NULL | NULL | | BTREE | | | Offers_To_Buy | 1 | keywords|1 | keywords | NULL |NULL | NULL | NULL | YES | FULLTEXT | | | Offers_To_Buy | 1 | bid |1 | bid | NULL |NULL | NULL | NULL | | FULLTEXT | | +---++-+--+- +---+-+--++--++- + 11 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server hanging once a week (Solaris 8)
When the database is in a `hung` state, is the file system where the MYI, MYD and frm files accessible? Paul Smith wrote: %% Gleb Paharenko [EMAIL PROTECTED] writes: gp Very often similar symptoms causes various lock problems. Do you gp use InnoDB? Try log_slow_queries to find hung queries. I'm using MyISAM tables. When I wrote this application InnoDB wasn't even an option :-). I don't think it's hung queries, because even new queries that I know are very fast fail. Heck, I can't even attach to the DB except through the admin interface (mysqladmin). Unless you're saying that the hung queries are using up the processes/threads in the DB so that new queries can't get through? Thanks for the response... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server hanging once a week (Solaris 8)
What state are the queries in while they are hung? What command or SQL are they executing? Paul Smith wrote: %% Victor Pendleton [EMAIL PROTECTED] writes: vp When the database is in a `hung` state, is the file system where vp the MYI, MYD and frm files accessible? Definitely. I can log in, see the files, run the mysqladmin commands, etc. The databases live on the local disk, not a remote server (I do backups every night to a remote server). Other operations and services on that server continue to work. Thanks for the response! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server hanging once a week (Solaris 8)
Understandable. The state, command and info values from `show processlist` could shed some light on what is occurring. Paul Smith wrote: %% Victor Pendleton [EMAIL PROTECTED] writes: vp What state are the queries in while they are hung? What command or vp SQL are they executing? Unfortunately I lost the output of the processlist command. Whenever the server hangs it's always a crazy race to get it back up again. Next time it happens I'll be sure to stow the list somewhere before restarting the server. However, IIRC there were a mix of queries: some were Wait, some were Sleep. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow date query
A function on the left hand side will nullify the use of an index. Marc Dumontier wrote: Hi, I'd really appreciate any help in speeding up this type of query SELECT BindId,RecordType from BrowseData WHERE DATE_FORMAT(DateLastRevised,'%Y-%m-%d') = '2004-12-07'; On a MYISAM table of 122,000 rows, this query takes very long, in the neighbourhood of 20 minutes. i'm using mysqld 4.0.20. I have an index on DateLastRevised mysql show indexes from BrowseData; ++++--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | ++++--+-+---+-+--++--++-+ | BrowseData | 0 | PRIMARY|1 | BindId | A | 122850 | NULL | NULL | | BTREE | | | BrowseData | 1 | bbs_dlr|1 | DateLastRevised | A | 122850 | NULL | NULL | | BTREE | | | BrowseData | 1 | bbs_bid_recordtype |1 | BindId | A | 122850 | NULL | NULL | | BTREE | | | BrowseData | 1 | bbs_bid_recordtype |2 | RecordType | A | 122850 | NULL | NULL | | BTREE | | ++++--+-+---+-+--++--++-+ mysql explain SELECT BindId,RecordType from BrowseData WHERE DATE_FORMAT(DateLastRevised,'%Y-%m-%d') = '2004-12-07'; ++--+---+--+-+--++-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+--+-+--++-+ | BrowseData | ALL | NULL | NULL |NULL | NULL | 122850 | Using where | ++--+---+--+-+--++-+ 1 row in set (0.00 sec) thanks, Marc Dumontier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow date query
mysql explain select * from queryLog where date_format(queryTime,
'%Y-%m-%d') =
'2004-11-16';
+--+--+---+--+-+--+--+-+
| table| type | possible_keys | key | key_len | ref | rows |
Extra |
+--+--+---+--+-+--+--+-+
| queryLog | ALL | NULL | NULL |NULL | NULL | 245 | Using
where |
+--+--+---+--+-+--+--+-+
1 row in set (0.01 sec)
mysql explain select * from queryLog where queryTime =
date_format('2004-11-16'
, '%Y-%m-%d');
+--+--+---+---+-+---+---
---+-+
| table| type | possible_keys | key | key_len |
ref | ro
ws | Extra |
+--+--+---+---+-+---+---
---+-+
| queryLog | ref | idx_querytime_log | idx_querytime_log | 4 |
const |
1 | Using where |
+--+--+---+---+-+---+---
---+-+
1 row in set (0.00 sec)
Jeff Smelser wrote:
On Tuesday 07 December 2004 02:03 pm, Victor Pendleton wrote:
A function on the left hand side will nullify the use of an index.
Sides dont matter, a function on an index, no matter what side, will
kill an
index.
Jeff
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Locking Problem? Very Slow MyISAM DB - PLS HELP!
When the server begins to slow down, what does top reveal? Andrew Nelson wrote: The reason I ask is because eight select statements should not bog down a production server. On the MySQL side, is anything being written to the slow query log? On the application side is there any virus scanning or similar activity being performed? Does iostat show any heavy reading or writing activity? Is memory being swapped? What is the server load? Do you have a high wait time or is CPU usage the only symptom? Nothing is being written very often.. Maybe a few email accounts added/modified each day. iostat shows very small disk activity (around 0.4Mb/s average). No memory is being swapped.. We do have a high wait time - the email is queueing up and the website interface to update the database (in PHP) times out when it's busy and I figured it's the CPU - I don't get what is so computational, the Databases are quite small (it's just a userbase - about 200Kb). I appreciate your help, Thanks, Andrew. Andrew Nelson wrote: Hi Victor, How did you deduce that the database server is the bottleneck? Are all your processes running on the same machine? Because 'ps -aux' shows it running at 94% of the CPU and when I stop/start the mysql server, it seems to be ok again for another hour. Any ideas? Andrew Nelson wrote: Hi, I have a MySQL 3.23.55 server managing accounts on my exim mail server.. The table type on all tables MyISAM.. I have the MTA performing various queries for each incoming email - determining mail aliases, vacation messages and filtering rules etc but they're all pretty much SELECT statements.. I know this isn't ideal and i've started replacing runtime queries with processes that search text files instead (generated every few minutes etc) but it should still be able to cope I would have thought? At it's busiest, it's performing about 8 trivial queries per second. It's a Xeon 2.6Ghz machine with 1Gb of RAM (running on FreeBSD) but it's grinding to a halt. I have to keep stopping and restarting the MySQL server to regain speed. As there's many processes trying to access the same tables to do SELECTs I thought it might be a locking issue.. BDB didn't seem to help - can anyone suggest something that might help? Thanks, Andrew. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Windows 2003 read-only problem
Have you tried connecting with this user directly from the MySQL monitor? Hathaway, Scott L wrote: I have a server that is Windows 2003 server. It is running php from IIS 6. I access the latest 4.0x MySQL and am having some trouble. My database access is readonly. The user that I connect as has update privileges (all privileges, actually). We checked the file permissions to the Mysql directory, the data directory and the directory that houses the database. The ISUR and IWAM users have read/write to those folders. Does anyone know what else might be causing this problem? Thanks, Scott Hathaway -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Locking Problem? Very Slow MyISAM DB - PLS HELP!
How did you deduce that the database server is the bottleneck? Are all your processes running on the same machine? Andrew Nelson wrote: Hi, I have a MySQL 3.23.55 server managing accounts on my exim mail server.. The table type on all tables MyISAM.. I have the MTA performing various queries for each incoming email - determining mail aliases, vacation messages and filtering rules etc but they're all pretty much SELECT statements.. I know this isn't ideal and i've started replacing runtime queries with processes that search text files instead (generated every few minutes etc) but it should still be able to cope I would have thought? At it's busiest, it's performing about 8 trivial queries per second. It's a Xeon 2.6Ghz machine with 1Gb of RAM (running on FreeBSD) but it's grinding to a halt. I have to keep stopping and restarting the MySQL server to regain speed. As there's many processes trying to access the same tables to do SELECTs I thought it might be a locking issue.. BDB didn't seem to help - can anyone suggest something that might help? Thanks, Andrew. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Locking Problem? Very Slow MyISAM DB - PLS HELP!
The reason I ask is because eight select statements should not bog down a production server. On the MySQL side, is anything being written to the slow query log? On the application side is there any virus scanning or similar activity being performed? Does iostat show any heavy reading or writing activity? Is memory being swapped? What is the server load? Do you have a high wait time or is CPU usage the only symptom? Andrew Nelson wrote: Hi Victor, How did you deduce that the database server is the bottleneck? Are all your processes running on the same machine? Because 'ps -aux' shows it running at 94% of the CPU and when I stop/start the mysql server, it seems to be ok again for another hour. Any ideas? Andrew Nelson wrote: Hi, I have a MySQL 3.23.55 server managing accounts on my exim mail server.. The table type on all tables MyISAM.. I have the MTA performing various queries for each incoming email - determining mail aliases, vacation messages and filtering rules etc but they're all pretty much SELECT statements.. I know this isn't ideal and i've started replacing runtime queries with processes that search text files instead (generated every few minutes etc) but it should still be able to cope I would have thought? At it's busiest, it's performing about 8 trivial queries per second. It's a Xeon 2.6Ghz machine with 1Gb of RAM (running on FreeBSD) but it's grinding to a halt. I have to keep stopping and restarting the MySQL server to regain speed. As there's many processes trying to access the same tables to do SELECTs I thought it might be a locking issue.. BDB didn't seem to help - can anyone suggest something that might help? Thanks, Andrew. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql Hangs
Are you data files on a local filesystem? When this issue occurs can you access any regular files on the drive? Ajay Kalambur wrote: Hi We have been having problems with a MySql database that runs on Linux.It just occurred suddenly and was working fine before Details: MySql Version:# mysql Ver 14.7 Distrib 4.1.6-gamma The filesystems which are accessed by MySql just hang.We cannot access any of the tables.All clients just hang.When we Shutdown the server, it hangs again.The Mysql deamons wont get killed even if I use the kill -9 command.After a reboot Everything works fine for sometime and then goes bad again.The problem seems to be that mysql seems to have a lock on all the tables and hence the use database,select,update hangs.All of the Mysql configuration is with default options. Has anyone encountered a similar problem with the 4.1.6-gamma version?? Thanks Ajay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Securing mysql from remote access
If you plan on connecting only from the localhost host or via an ssh tunnel you block port 3306 at the firewall or router. What exactly are you looking to accomplish? Scott Haneda wrote: I can do a telnet host.com 3386 and get a answer back from mysql. Is the only way to turn off remote access like this with a router and limit the ports, or is there some setting in mysql that will not allow outside connections other than from a IP range I tell it to? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Securing mysql from remote access
Some of the vulnerabilities have been addressed in recent releases. I would check the bug list and change log to see if your versions have been patched. If you want to disallow all tcp/ip connections you can use the |--skip-networking parameter. Only Unix sockets or Windows named pipes connections will be allowed. |Scott Haneda wrote: on 11/30/04 8:53 PM, Victor Pendleton at [EMAIL PROTECTED] wrote: If you plan on connecting only from the localhost host or via an ssh tunnel you block port 3306 at the firewall or router. What exactly are you looking to accomplish? Well, some friend of a friend decided to scan me and found he could get myslq to report the version I am runing. He could not login or anything like that. He then pointed me to http://www.securityfocus.com/bid/11261/discussion/ http://www.securityfocus.com/bid/8590/discussion/ http://www.securityfocus.com/bid/10655/discussion/ http://www.securityfocus.com/bid/10654/discussion/ http://www.securityfocus.com/bid/975/discussion/ For which I just started wondering. I can easily block it out in the router, but it made me curious as to what other options there are. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding Foreign Key
Do you have an index on the client_id in the t_quiz table? Can you post the ddl for t_quiz? Ady Wicaksono wrote: mysql alter table t_quiz_trivia add foreign key (client_id) references t_client (client_id) on delete set default; ERROR 1005: Can't create table './smsserver/#sql-215d_11eff.frm' (errno: 150) May i know what makes error ? Here is the reference table CREATE TABLE `t_client` ( `client_id` int(11) NOT NULL default '0', `client_desc` varchar(255) NOT NULL default '', PRIMARY KEY (`client_id`) ) TYPE=InnoDB; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: enrror in foreign key reference
Is there an index on the column in the table referenced? F.Balicchia wrote: Hello i'd like to converte this DDL for mysql ALTER TABLE MYTABLE_ROLE ADD ( FOREIGN KEY (LOGIN) REFERENCES MYTABLE_USER ) ; i wrote ALTER TABLE MYTABLE_ROLE ADD FOREIGN KEY (LOGIN) REFERENCES MYTABLE_USER ; by it return me this error. #1005 - Can't create table '.\mydb\#sql-4b4_63.frm' (errno: 150). I don't understand how i can resolve it. thanks --Filippo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cannot Connect to local MYSQL server through socket
Is the MySQL server running and you can not connect? Or are you not able to start the MySQL server? Morris, Andrew wrote: ../var/lib/mysql/mysql.sock. I've been round the houses with this following the thread but I can't get past this error despite all the chmod and chown modifications, installed MySQL admin and it still won't start. Am I best to chuck it in the bin or uninstall the lot and start again? This is Fedora Core 3 with MySQL 3.23.58-13. Any help gladly appreciated but its not obvious from the web site how to go beyond the chmod and chown fixes. What does the socks file do anyway? Thanks Andrew G Morris ILT Advisor (Technical) JISC RSC West Midlands 01902 824434 07980 982738 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: On but off topic Putting a file in Ram
Is the actual data stored in the database or somewhere in the file system? If you do not have text or blob columns you may be able to use heap/memory tables. [EMAIL PROTECTED] wrote: Actually no. I have a file that is determined to be requested by mysql (Top 100 site) What I am wanting to do is put the images and or files into Ram to serve them from there instead of the harddrive and conserve hd resources for not known tasks. Thanks Donny Lairson President http://www.gunmuse.com 469 228 2183 -Original Message- From: Eamon Daly [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 9:17 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: On but off topic Putting a file in Ram The reason I ask this here. Is I have graphics that are loaded by Mysql and was wondering if I can do the same for them since some of these sites can call my server 10-20,000 times a day for that same graphic. I assume you mean that you have image data stored in a MySQL table somewhere and are using a SELECT to fetch and serve it. I think the general consensus would be something along the lines of Don't do that. Apache was /designed/ to serve files quickly, so let it do what it does best. Store just the filenames in MySQL and let Apache handle the rest. Once you've done that, you can do plenty of things to speed up or scale your system, such as mapping the files to memory with mod_file_cache, judicious use of a caching proxy, or the creation of a ramdisk. Eamon Daly - Original Message - From: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, November 22, 2004 8:20 PM Subject: On but off topic Putting a file in Ram I have a small file that calls a search function at Findwhat in case Mysql locally overloads. I just put on a new partner who looks like they may call my server 40 million times a month. I know there is some way to put a file into Ram for super fast response. Question is how do I do this? Will it still write to Mysql from the Ram Drive? What is the downside of doing this? The reason I ask this here. Is I have graphics that are loaded by Mysql and was wondering if I can do the same for them since some of these sites can call my server 10-20,000 times a day for that same graphic. -- 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: On but off topic Putting a file in Ram
Are you inquiring about database heap/memory tables or file system memory tables? This is the link for MySQL heap/memory tables, http://dev.mysql.com/doc/mysql/en/MEMORY_storage_engine.html. As for the file system you could look at using ramfs, http://www.linuxfocus.org/English/July2001/article210.shtml, or just google for it. [EMAIL PROTECTED] wrote: Heap/Memory tables that is the phrase I couldn't remember. The data is stored in the file system. I have one file that that is linked to via JavaScript to run a php file and send an output. That file accesses MySql OR if I am overloaded it bypasses my local system and goes directly to Findwhat.com to produce the search. By putting that file into memory I should be able to handle any load fairly easily. Any suggestions on where I should read to learn how to use heap/memory on Linux/enterprise? Thanks Donny Lairson President http://www.gunmuse.com 469 228 2183 -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 11:10 AM To: [EMAIL PROTECTED] Cc: Eamon Daly; [EMAIL PROTECTED] Subject: Re: On but off topic Putting a file in Ram Is the actual data stored in the database or somewhere in the file system? If you do not have text or blob columns you may be able to use heap/memory tables. [EMAIL PROTECTED] wrote: Actually no. I have a file that is determined to be requested by mysql (Top 100 site) What I am wanting to do is put the images and or files into Ram to serve them from there instead of the harddrive and conserve hd resources for not known tasks. Thanks Donny Lairson President http://www.gunmuse.com 469 228 2183 -Original Message- From: Eamon Daly [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 9:17 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: On but off topic Putting a file in Ram The reason I ask this here. Is I have graphics that are loaded by Mysql and was wondering if I can do the same for them since some of these sites can call my server 10-20,000 times a day for that same graphic. I assume you mean that you have image data stored in a MySQL table somewhere and are using a SELECT to fetch and serve it. I think the general consensus would be something along the lines of Don't do that. Apache was /designed/ to serve files quickly, so let it do what it does best. Store just the filenames in MySQL and let Apache handle the rest. Once you've done that, you can do plenty of things to speed up or scale your system, such as mapping the files to memory with mod_file_cache, judicious use of a caching proxy, or the creation of a ramdisk. Eamon Daly - Original Message - From: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, November 22, 2004 8:20 PM Subject: On but off topic Putting a file in Ram I have a small file that calls a search function at Findwhat in case Mysql locally overloads. I just put on a new partner who looks like they may call my server 40 million times a month. I know there is some way to put a file into Ram for super fast response. Question is how do I do this? Will it still write to Mysql from the Ram Drive? What is the downside of doing this? The reason I ask this here. Is I have graphics that are loaded by Mysql and was wondering if I can do the same for them since some of these sites can call my server 10-20,000 times a day for that same graphic. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to connect to MyDBPAL from MySQL
Are there any MySQL errors being logged? Did you confirm that the ODBC connection is working? Sanjeev Sagar wrote: I have seen mails talking about MydbPAL. I downloaded it and I have MySQL ODBC data source too but not able to connect to MydbPal at all. I have tested my odbc data source, it work just fine. My machine is having Linux and Windows through vmvare workstation. I installed dbpal on windows and try to connect to database on my local linux based partition. Indise MydbPAL, I click on Workshop-click on Object Browser-select the data source name I still see wait symbol in toolbar. Also when I click on db test connect, nothing happen. Am I missing anything. I checked the documentation but it's not that clear that how to connect by using ODBC. Any help will be highly appreciable. Regards, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to connect to MyDBPAL from MySQL
You could look at the processlist in the MySQL monitor to see if a connection has been made. You may also want to enable ODBC tracing. Sanjeev Sagar wrote: I guess I am getting close I drag the odbc connection line to database icon in db-test-connection. I created new user and drag it to user part of db-test-connection. Now the waiting tray has go icon. I drag the go icon to console tray, nothing happened. What is the next step to see if database connection is going through. -Original Message- From: Sanjeev Sagar [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 3:55 PM To: Victor Pendleton Cc: [EMAIL PROTECTED] Subject: RE: how to connect to MyDBPAL from MySQL YES ODBC work perfect. I am using same data source for MySQL administrator and Query Browser too. Anyway, if you are using can you go over the steps for db connection 1. Open dbPAL, Click on Workshop 2. Choose data source by clicking ODBC from the list under MySQL 3. What next after that?? 4. I clicked on new user and gave entry, it just gave me one a open lock icon on toolbar. How do I test my database connection. Db-test-connect is grade out, not clickable. No error reported in err file Regards, -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 3:32 PM To: Sanjeev Sagar Cc: [EMAIL PROTECTED] Subject: Re: how to connect to MyDBPAL from MySQL Are there any MySQL errors being logged? Did you confirm that the ODBC connection is working? Sanjeev Sagar wrote: I have seen mails talking about MydbPAL. I downloaded it and I have MySQL ODBC data source too but not able to connect to MydbPal at all. I have tested my odbc data source, it work just fine. My machine is having Linux and Windows through vmvare workstation. I installed dbpal on windows and try to connect to database on my local linux based partition. Indise MydbPAL, I click on Workshop-click on Object Browser-select the data source name I still see wait symbol in toolbar. Also when I click on db test connect, nothing happen. Am I missing anything. I checked the documentation but it's not that clear that how to connect by using ODBC. Any help will be highly appreciable. Regards, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE question...
You can load the file to an intermediate table and then complete your process using INSERT INTO targetTable SELECT col_1, col_5, col_28, col_71 FROM intermediateTABLE Ferhat BINGOL wrote: Hi, I have a 72 fields data txt file and I was inserting all data previously but now I need only some of them to dump into the table. I would like to select only 4 fields which are the 1st, 5th,28th and 71st fields. Is there a statement to do that. I have read http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html but could not find it... Thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries taking 60 seconds+
What does the explain plan look like? John Smith wrote: Afternoon All, I have the following table structure: CREATE TABLE properties ( id int(11) NOT NULL auto_increment, propid varchar(14) NOT NULL default '0', townid varchar(255) NOT NULL default '', countyid mediumint(5) NOT NULL default '0', address text NOT NULL, price int(14) NOT NULL default '0', image text NOT NULL, description text NOT NULL, link text NOT NULL, underoffer tinyint(1) NOT NULL default '0', sold tinyint(1) NOT NULL default '0', added int(14) NOT NULL default '0', `new` tinyint(1) NOT NULL default '1', old tinyint(1) NOT NULL default '0', PRIMARY KEY (id), KEY old (old), KEY `new` (`new`), KEY sold (sold), KEY underoffer (underoffer), KEY propid (propid), KEY price (price), KEY countyid (countyid), FULLTEXT KEY address (address) ) ENGINE=MyISAM Which I have ran the following commands on: myisamchk -rq --sort-index --analyze --sort-records=7 properties.MYI myisampack properties.MYI myisamchk -rq --sort-index --analyze properties.MYI It contains just over 400,000 rows and compressed is 163 Meg in size. I have just upgraded to 4.1 as well to see if I can squeeze any more performance out. This query: SELECT SQL_CACHE SQL_CALC_FOUND_ROWS address,price, image, description, link , underoffer, sold ,added ,new FROM properties WHERE countyid = 44 AND price = 1 AND old=0 ORDER BY price desc LIMIT 100, 10; Takes 123 seconds and examins 19068 rows according to the query log, without the limit it returns 9512 rows. I am all resourced out and have spent weeks googleing and reading the docs etc. I am pretty sure returing all properties containing the countyid should not take 2 minutes and I am confused at why its examining 19068 rows also. It could be down to my server I suppose which is a jailed (no idea what that is its just what my hosting company call it) freebsd virtial server. I have been playing with mysqld variables in my.cnf also...these are probably all set too high...i am not really sure of the specs of my server as I don't get that info.. set-variable = query_cache_type=2 set-variable= key_buffer=300M set-variable= query_cache_size=200M set-variable= query_cache_limit=50M set-variable= max_allowed_packet=10M set-variable= table_cache=50 set-variable= sort_buffer=5M set-variable= read_rnd_buffer=5M set-variable= record_buffer=5M set-variable= tmp_table_size=64M set-variable= thread_cache=9 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=2 set-variable= ft_min_word_len=3 set-variable = long_query_time=2 log-long-format log-slow-queries = /var/log/mysqld.slow.log The table is read only for most of the day and will get updated (once I get the queries down to an acceptable level) nightly...if that helps. Any suggestions or further tips would be very helpful as its taken me months to get my code to input the data in the way I need its now trying to get the data back out that is the problem. Thanks John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries taking 60 seconds+
If you build the composit indexes as suggested, does your performance improve? John Smith wrote: On Thu, 2004-11-11 at 13:58, Victor Pendleton wrote: What does the explain plan look like? id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE properties ref old,price,countyid countyid 3 const 9233 Using where; Using filesort The filesort I know is a problem but I thought I read sorting it using myisamchk by the price key helps this. Also if I remove ORDER BY and the price =1 AND old = 0 the query still takes 46 seconds which is too long for a dynamic site that I am trying to build. John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Script question
If you are on an *nix system you can try \! echo Hello test.txt Rhino wrote: Sorry, you still haven't got it;-) I want the echo command to be in the *script*, not to be supplied at the command line. Is there some way to put a mix of MySQL commands and OS commands into a script and have both executed successfully via: mysql -u myid -pmypass Load.sql Load.out Rhino - Original Message - From: Logan, David (SST - Adelaide) [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql [EMAIL PROTECTED] Sent: Monday, October 18, 2004 8:09 PM Subject: RE: Script question After re-reading your email in not so quite a rush 8-) Further to that, you can use the system command to run an OS command eg. system echo thing; mysql system echo thing; thing mysql 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 From: Rhino [mailto:[EMAIL PROTECTED] Sent: Tuesday, 19 October 2004 9:13 AM To: mysql Subject: Script question Can anyone tell me how to make a script containing MySQL commands also execute an OS command? For instance, given this script, called Load.sql: -- use SFL; #Load the data from the export file that was exported from QA as a standard #ASCII file. load data infile '/home/rhino/MySQL/SFL/FILMS.AS3' replace into table Dougs_Movies fields terminated by ';' optionally enclosed by '' escaped by '\\' lines starting by '' terminated by '\r\n'; I'd like to add some 'echo' statements to the script. However, when I include: echo Hello or !echo Hello I get a syntax error when I execute the script. I am executing the script from the Linux command line via this command: mysql -u myid -pmypass Load.sql Load.out It isn't strictly necessary to include 'echo' commands in the script as long as I can ensure that the text of my choice appears in the Load.out file in the place of my choosing, i.e. after the 'use' command has been executed but before the 'load' command has been executed. Can anyone help me this problem? I can't believe it is hard but I can't think what to search on in the archives. Rhino --- rhino1 AT sympatico DOT ca There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies. - C.A.R. Hoare -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL doesn't startup anymore
What is written to ther error log? Marc wrote: I'm using MySQL on Gentoo Linux and today it stopped starting up. It was working a few days ago, and I haven't played with anything in the system - no new installs, config changes, etc. It just decided to go on vacation. I run mysqld_safe and it quits right away with no error messages. I've got a MySQL book and it doesn't help much. I also tried mysqld_safe --debug, but no trace file is created. Thanks for any help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Batch Querying
What version of MySQL are you using? I believe multiple delimited statements are available in 4.1.x and later. Jeff Burgoon wrote: I'm writing my first MySQL app in VB.net using myODBC. However I think this question applies to all languages using MySQL. From what I understand, I am unable to issue a batch statement of commands separated by commas to mySQL. I receive an error whenever I try to do so from my app. For this reason, I am unable to make use of SQL variables and temporary tables. I must instead use persistant tables. Can anyone tell me if this is in fact the case and if so, any suggestions on how to get over this hurdle? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN columns with different types
A data type conversion will have to take place and depending on the size of the tables the performance could be quite miserable. Qunfeng wrote: Hi, MySQL seems to be able to JOIN columns with different types, e.g., one column type is int; the other type is varchar. My question is: how badly does that affect the join performance even though both columns are indexed. Thanks! Qunfeng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with Replication - Slave Option replicate-ignore-tabl e and replicate-wild-ignore-table
In the slave's my.cnf have you tried just expliciting statement what tables to ignore? replicate-ignore-table=db.table1 replicate-ignore-table=db.table2 ... replicate-ignore-table=db.tablen And remove the other statements? -Original Message- From: Mike Lohman To: [EMAIL PROTECTED] Sent: 9/10/04 10:53 AM Subject: Problem with Replication - Slave Option replicate-ignore-table and replicate-wild-ignore-table Hi, I've got a running Master-Slave environment with 8 Slaves and 1 Master. The problem is not the replication itself, but to exclude some tables out of it. Excluding databases runs without problems. All server are of the same version: mysql --version mysql Ver 12.22 Distrib 4.0.18, for pc-linux-gnu (i686) From several forums, discussion groups and the manual I found out, that I have no mistake in my configs. But I cannot get it to run. Worse. Perhaps someone has an idea. I found some statements in this list, generated earlier, but never be answered: http://lists.mysql.com/mysql/153722 I tried several times to delete the master.info on the slave and restart the slave-server. Replication allways got up to work again. But the replicate-wild and replicate-ignore-table entries are never used. Please help. Part of the slave- my.cnf, concerning replication: master-host=masterip master-user=repl master-password=password master-port=3306 server-id = 2 replicate-ignore-db = mysql replicate-ignore-db = test replicate-do-db=normal replicate-wild-ignore-table=normal.page% Part of the master- my.cnf, concerning replication: server-id = 1 log-bin = /var/log/mysql/mysql-bin.log #log-update = /var/log/mysql/mysql-update.log binlog-do-db= normal binlog-ignore-db= mysql SHOW MASTER STATUS: mysql SHOW MASTER STATUS; +---+--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | +---+--+--+--+ | mysql-bin.002 | 761239 | normal| mysql| +---+--+--+--+ 1 row in set (0.02 sec) SHOW SLAVE STATUS: mysql SHOW SLAVE STATUS\G *** 1. row *** Master_Host: master IP Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: mysql-bin.002 Read_Master_Log_Pos: 774689 Relay_Log_File: slave-relay-bin.006 Relay_Log_Pos: 323911 Relay_Master_Log_File: mysql-bin.002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: normal Replicate_ignore_db: mysql,test Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 774689 Relay_log_space: 323911 1 row in set (0.00 sec) Thanks in advance. 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: 4.1.4 still reports itself as 4.1.3b-beta
In the registry and/or the windows service utility see which executable is actually in the path. -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 9/2/04 9:19 AM Subject: Re: 4.1.4 still reports itself as 4.1.3b-beta Oh, yes, certainly, Egor. I stopped it before doing the upgrade, and restarted afterwards. And have restarted several times since, because I couldn't believe it! The majority of the files now in the c:\mysql directory have creation dates on or about 27 August, the documentation is for 4.1.4gamma, so it does look as though the upgrade went through, as far as copying files is concerned. Just restarted again with no change (just in case). Any other clues? Terry - Original Message - Terry Riley [EMAIL PROTECTED] wrote: I have tried several instals (Windows XP), using binaries from several of the mirrors, over a 4.1.3b-beta-log version. No errors reported during any install, but it still reports itself as the 4.1.3b-beta-log version - both in MySQL Administrator (v 1.0.12) and at the command line opening of mysql. Have I done something stupid, or are others seeing this, too? First thing to check: have you stopped mysql service and started it again? -- 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: 4.1.4 still reports itself as 4.1.3b-beta
You can test if this is correct by manually starting the service from a dos window and logging and seeing what version this executable actually is. -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 9/2/04 10:01 AM Subject: RE: 4.1.4 still reports itself as 4.1.3b-beta Victor C:\MYSQL\BIN\mysqld-opt --defaults-file=C:\WINDOWS\my.ini MySql is what the path-to-executable states. If I remember rightly, the new version should be mysqld, period. Is that what's wrong? Will I have to tweak the registry to change that? Cheers Terry - Original Message - In the registry and/or the windows service utility see which executable is actually in the path. -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 9/2/04 9:19 AM Subject: Re: 4.1.4 still reports itself as 4.1.3b-beta Oh, yes, certainly, Egor. I stopped it before doing the upgrade, and restarted afterwards. And have restarted several times since, because I couldn't believe it! The majority of the files now in the c:\mysql directory have creation dates on or about 27 August, the documentation is for 4.1.4gamma, so it does look as though the upgrade went through, as far as copying files is concerned. Just restarted again with no change (just in case). Any other clues? Terry - Original Message - Terry Riley [EMAIL PROTECTED] wrote: I have tried several instals (Windows XP), using binaries from several of the mirrors, over a 4.1.3b-beta-log version. No errors reported during any install, but it still reports itself as the 4.1.3b-beta-log version - both in MySQL Administrator (v 1.0.12) and at the command line opening of mysql. Have I done something stupid, or are others seeing this, too? First thing to check: have you stopped mysql service and started it again? -- 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: 4.1.4 still reports itself as 4.1.3b-beta
DuBois just trumped me. Just check the version from the command line. -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 9/2/04 10:13 AM Subject: RE: 4.1.4 still reports itself as 4.1.3b-beta Further to last message, I've found that string in three different places in the registry: HKEY_LOCAL_MACHINE\system\controlset001\services\MySQL\ImagePath\ HKEY_LOCAL_MACHINE\system\controlset003\services\MySQL\ImagePath\ HKEY_LOCAL_MACHINE\system\currentcontrolset\services\MySQL\ImagePath\ Never having had to fiddle with the registry much in the past, can I presume that if I ignore the first two and change the third one to read 'mysqld' instead of 'mysqld-opt', save and reboot, I should get the right one started? As you can tell, I'm a little wary of this. Cheers Terry - Original Message - In the registry and/or the windows service utility see which executable is actually in the path. -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 9/2/04 9:19 AM Subject: Re: 4.1.4 still reports itself as 4.1.3b-beta Oh, yes, certainly, Egor. I stopped it before doing the upgrade, and restarted afterwards. And have restarted several times since, because I couldn't believe it! The majority of the files now in the c:\mysql directory have creation dates on or about 27 August, the documentation is for 4.1.4gamma, so it does look as though the upgrade went through, as far as copying files is concerned. Just restarted again with no change (just in case). Any other clues? Terry - Original Message - Terry Riley [EMAIL PROTECTED] wrote: I have tried several instals (Windows XP), using binaries from several of the mirrors, over a 4.1.3b-beta-log version. No errors reported during any install, but it still reports itself as the 4.1.3b-beta-log version - both in MySQL Administrator (v 1.0.12) and at the command line opening of mysql. Have I done something stupid, or are others seeing this, too? First thing to check: have you stopped mysql service and started it again? -- 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: Duplicate entry breaks replication
Did you actually stop all writes to the master data while you were copying the data over to the slave database? Is it possible that the connection you issued the read lock with timed out before the transfer was completed? -Original Message- From: Alex Greg To: [EMAIL PROTECTED] Sent: 9/2/04 10:56 AM Subject: Duplicate entry breaks replication Hi, We recently moved to new servers, and also upgraded from MySQL 3.23 to 4.0.20. We have one master database server and a second identical machine configured to replicate all databases from the master. Previously this has worked without a hitch, but we seem to be having some problems. The way we did the transition was as follows: 1) On old database server, issued FLUSH TABLES WITH READ LOCK; command 2) Rsynced the data directory onto the new server (db1) 3) Locked the new database server 4) Rsynced the data directory onto the new server (db2) 5) Configured replication and started it up This works, but we keep getting errors like Error 'Duplicate entry '53751556' for key 1' on query. Default database: 'archive'. I know what this means, but how come we are getting them if the two servers started off with an identical snapshot of data. Also, should issuing an insert that fails in this nature break replication? Regards, -- Alex -- 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: bad too many connections error (os x)
Is it a too many connections or Host blocked because of many connection erros? If it is the later you have reached max_connect_errors and need to issue flush-hosts. -Original Message- From: Michael Winston To: [EMAIL PROTECTED] Sent: 9/1/04 11:02 AM Subject: bad too many connections error (os x) Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael -- 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 connectivity
What will the connection be used for? Replication? Query between datasources? There are some third party applications such as Golden Gate that have replication products. You could also write the connection yourself in a language such as Java or C. -Original Message- From: Seena Blace To: Seena Blace; [EMAIL PROTECTED] Sent: 9/1/04 11:30 AM Subject: Re: Database connectivity Hi, I wanted to setup connectivity between mysql database and oracle database.I have one database in mysql from which all tables data I want to insert real time data into oracle database.How to connect mysql database with oracle database? thanks - Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: bad too many connections error (os x)
Not if the host that is blocked is `localhost`. -Original Message- From: Michael Winston To: Victor Pendleton Cc: '[EMAIL PROTECTED] ' Sent: 9/1/04 11:24 AM Subject: Re: bad too many connections error (os x) This would make sense since they all the connections are coming from the same website. But if this is true, then why can't we connect using 'mysqladmin -uroot'? Shouldn't that work from any host? Thanks, Michael On Sep 1, 2004, at 9:08 AM, Victor Pendleton wrote: Is it a too many connections or Host blocked because of many connection erros? If it is the later you have reached max_connect_errors and need to issue flush-hosts. -Original Message- From: Michael Winston To: [EMAIL PROTECTED] Sent: 9/1/04 11:02 AM Subject: bad too many connections error (os x) Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to admin a server that currently has too many connections ?
Is there already a mysql privileged account logged in? The database will allow for an administrative account to login, if one is not already active, in the event of too many connections. It sounds as if you may be running one or more accounts with this privilege. -Original Message- From: Justin Swanhart To: [EMAIL PROTECTED] Sent: 8/31/04 11:43 AM Subject: How to admin a server that currently has too many connections? I am creating an index on a very large innodb table that is taking a very long time to complete which I understand is a limitation of innodb. The problem is that another application has been issuing queries against the table and those queries have never timed out. So now I can't kill the connections, because I can't make a database connection to mysql to even see their thread ids: [EMAIL PROTECTED] mysql]$ mysqladmin -u root -p processlist Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Too many connections' I don't want to kill the mysqld process, because that would stop my index creation. Is there anything you can do in this situation? I don't think connections from mysqladmin should ever be denied. Is this a feature request that I should make? -- 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: powerpoint and mysql
How did you plan on using the information pulled from the database? If you are currently using MS Query or a DSN you can modify the current connection information to point to your MySQL database. -Original Message- From: redhat To: mysql Sent: 8/27/04 2:21 PM Subject: powerpoint and mysql Greetings, I'm new to the list. I was wondering if it is possible to user mysql and powerpoint? I have a small database of simple text entries that I want to create whereby I can pull a title up and have the rest of the slide dynamically generated. The reason for this is that the presentation data will change weekly but over time the data will be used over and over - so I don't want to lose it or have to re-enter it. Any ideas? thanks, Doug -- 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: Mysql command line queries not working....
Can you run this from inside the monitor and see if any messages are sent to the consol? \. latest_distribution.sql -Original Message- From: Mike Morton To: [EMAIL PROTECTED] Sent: 8/26/04 12:05 PM Subject: Mysql command line queries not working I know the subject is not totally descriptive of the problem, but: mysql -h -u *** -p*** mikerocks latest_distribution.sql Is producing no results. I mean none, totally, no errors, nothing. The host, user and pass are all correct (of course removed here for security ;) ) and have the correct permissions. The latest_distribution.sql is simply a mysqldump of a database... And I have narrowed down the problem to the comments that the dump puts in there: # phpMyAdmin SQL Dump # version 2.5.6 # http://www.phpmyadmin.net Etc... Every comment in there aborts the rest of the file... Again, not with any errors, it just simply stops. Mysql on originating server: Ver 11.18 Distrib 3.23.54, for pc-linux (i686) Mysql on destination server: Ver 12.20 Distrib 4.0.13, for pc-linux (i686) Additional info: The mysqldump was created on the originating server. There is nothing strange in the mysqldump. The latest_distribution.sql works if imported as an sql query via PhpMyAdmin. I need to do this command line (part of an automated setup), so the questions are: 1. Is this a bug or some sort of setting that is messed up or something? 2. Can the mysqldump file be created without comments, as removing them every time there is a new dump will be tedious to say the least... 3. Am I a total idiot and have missed something dead simple? TIA! -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- 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: autoincrement question
You will have to call the last_insert_id() function in order to obtain the autoincrement value. -Original Message- From: dan orlic To: [EMAIL PROTECTED] Sent: 8/25/04 2:26 PM Subject: autoincrement question question: I have an insert statement that has a null for the value of the primary key, id, since that value is also a auto-increment field. The problem i am running into is I need that Id further down the road for map tables, but I have no idea what the id is, since it is in fact, autoincremented when inserted into the DB. My question is this... is there anything I can add to my insert statement that can have mysql return the id value? or am I just nuts? thank you for your help dan -- 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: runaway process eating all cpu-time?
Do you have anything in the show processlist? Are you using innodb table types and a rollback is occurring? -Original Message- From: Thomas - Lists To: [EMAIL PROTECTED] Sent: 8/24/04 6:49 AM Subject: runaway process eating all cpu-time? Hi there, after a system-crash I got the following problem. Some seconds after starting mysql (v4.0.20) a process will stay there taking all cpu-time it can get (99.9% ;)) ... and it can't be terminated, just killed completely. mysqlcheck ran through without any problems, the logs tell nothing. I just did a strace on the last process doing that: --- time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 sched_yield() = 0 time(NULL) = 1093348120 rt_sigprocmask(SIG_SETMASK, NULL, [HUP INT QUIT PIPE TERM TSTP 32], 8) = 0 rt_sigsuspend([HUP INT QUIT PIPE TERM TSTP] unfinished ... --- SIGRT_0 (Real-time signal 0) --- --- Any idea where to look at that problem further? The system is a linux - redhat-7.2 with dual athlon-mp, 2 GB ram, running kernel-2.4.26. Using the official mysql.com-binaries (rpm-install). TIA, Thomas -- 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: sql2000 to mysql
Are you wanting to move Foreign keys, Triggers, Stored procedures and the like as well or just the data? -Original Message- From: Tim Winters To: [EMAIL PROTECTED] Sent: 8/24/04 10:36 AM Subject: sql2000 to mysql Hello, Can someone advise me of the best/easiest way to move an entire DB (Tables and data) from sql2000 (my client) to mySQL (my System)? I need to advise someone on how I wish the data sent to me. Any help would be appreciated. Thanks, Tim -- 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: sql2000 to mysql
If you have DTS you can export the data to a CSV format. If you have MyODBC installed you could export directly to MySQL. -Original Message- From: Tim Winters To: Victor Pendleton; '[EMAIL PROTECTED] ' Sent: 8/24/04 11:34 AM Subject: RE: sql2000 to mysql Hi Victor, Just Tables and Data. Ideally scripted to create the tables and insert the data. will sql2000 product something similer to a .sql file which can simple be run as a script? Thx At 12:45 PM 24/08/2004, Victor Pendleton wrote: Are you wanting to move Foreign keys, Triggers, Stored procedures and the like as well or just the data? -Original Message- From: Tim Winters To: [EMAIL PROTECTED] Sent: 8/24/04 10:36 AM Subject: sql2000 to mysql Hello, Can someone advise me of the best/easiest way to move an entire DB (Tables and data) from sql2000 (my client) to mySQL (my System)? I need to advise someone on how I wish the data sent to me. Any help would be appreciated. Thanks, Tim -- 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: showing dbs in terminal window opposed to phpmyadmin
Are you loggin into the terminal and PHPmyadmin as the same user? -Original Message- From: Hull, Douglas D To: Note To MySQL List (E-mail) Sent: 8/23/04 8:30 AM Subject: showing dbs in terminal window opposed to phpmyadmin How come when I am using the terminal on my mac os x 10.3.x and give the command show databases I only see one database (the 'test' database that is included in the install') as compared to viewing databases with my browser and phpmyadmin I can see 6 databases (that same 'test' database, a 'mysql' database that came with the install, and 4 other databases I created myself while in phpmyadmin)? Shouldn't I have access to all of them from the terminal as well? Thanks again, Doug -- 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: into outfile
If you wanted html output you could do this mysql -uuser -p -H -equery_to_run webFile.html ... The -H switch produces HTML output. -Original Message- From: mos To: [EMAIL PROTECTED] Sent: 8/23/04 12:11 PM Subject: Re: into outfile Scott, At 11:58 AM 8/23/2004, you wrote: SELECT foo INTO OUTFILE '\\l030k12\qcda\2004-08-03.html' WHATEVER foo; Is that possible? I seem to be doing it wrong. select * into outfile 'p:\win2ktmp\mytest.txt' from mytable where order by Make sure the output directory exists and make sure the into Outfile filename appears before the from statement. BTW, you have .html as a file extension. MySQL only outputs in text (Into OutFile) or binary (Into DumpFile) files, not HTML. If you want HTML then you need to format the thml table it yourself in whatever language you prefer. 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: How to Reset a field
Try alter table usuarios auto_increment = 1; -Original Message- From: Yusdaniel Rodriguez Espinosa To: [EMAIL PROTECTED] Sent: 8/23/04 9:15 AM Subject: How to Reset a field Hello I have a db with table usuarios I erased all data in this table but Id is a autoinc and he have the value of the last value. How I can Reset the field ID thanks --- Hola Yo tengo una db con una tabla usuarios, yo borre todos los campos de la db pero el campo id que un autonumerico que se incrementa automaticamente se queda con el valor del ultimo campo, como puedo restablecer el valor de Id a 0 cada vez que limpie la db??? gracias Yusdaniel -- 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: Testing for the existence of an index
Why don't you disable the indexes before the load and enable the indexes after the data load? -Original Message- From: Jesse Sheidlower To: [EMAIL PROTECTED] Sent: 8/23/04 3:33 PM Subject: Testing for the existence of an index I have a database where, most of the time, I'm bulk-loading data into new tables from an external source, several million rows at a time. For this purpose, I create the tables without indexes, and then add all my indexes after the load is done, for efficiency purposes. I'd now like to add the possibility of adding some data to the existing database. In this case, the indexes exist, and then the new data will be indexed as it goes in (which is OK given the relatively small amount of data to be processed this way). I'd like to be able to test for the existence of an index, so that after the bulk-load, I can see if there are indexes, there won't be any, and I can create them; but after an addition to an existing database, there will be indexes, and I won't create them. How do I do this? It wasn't clear from the manual, and I'm away from my books now so can't look there for advice. I'm using Perl to process the data, if there's a Perlish way of doing things that would be easier than SQL. Jesse Sheidlower -- 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: load data infile
What is the current value for you id field? Approximately how many records are you inserting? -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 8/20/04 3:16 AM Subject: load data infile Dear freinds, I am still getting errors.Load infile script. Guidance , please. Asif Qureshi -- --- mysql describe chicago - ; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(11) | | PRI | NULL| auto_increment | | email | varchar(150) | | PRI | || +---+--+--+-+-++ 2 rows in set (0.02 sec) mysql LOAD DATA INFILE - 'CHICAGO.TXT' into table - B.CHICAGO; ERROR 1062: Duplicate entry '0-' for key 1 mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Remotely dumping data to a file
Can you use the -w/--where option in mysqldump to dump only the records you want? -Original Message- From: Tucker, Gabriel To: Mysql General (E-mail) Sent: 8/20/04 8:07 AM Subject: Remotely dumping data to a file Hi All I would like to dump certain data remotely from a table. I am unable to use mysqldump because I do not want to dump the entire table. The select into outfile will not work when the connection is remote. I saw in the manual that mysql -e would do what I want. However, that is not true. The output from this is formatted, I want it unformatted, like the mysqldump command. I could take the output from the mysql -e command and formatted the way I would like. Before doing this, I wanted to know if there is another option. Thanks! There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- 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: Login problem
Use the -p option so that the server can prompt you for the password you
just created.
mysql -uroot -p
-Original Message-
From: Francois A. Atallah
To: [EMAIL PROTECTED]
Sent: 8/20/04 12:59 PM
Subject: Login problem
After Installed mysql on Windows I have entered a root password using
the
command below
mysql UPDATE mysql.user SET Password = PASSWORD('test')
- WHERE User = 'root';
After saves had been made I get the erros below ? Any help
C:\c:\mysql\bin\mysql -u root
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
NO)
C:\c:\mysql\bin\mysqlshow mysql
c:\mysql\bin\mysqlshow: Access denied for user: '@localhost' to database
'mysql'
Thanks,
Francois A
--
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: Databases not showing in cmd and mysqladmin?
Has the ODBC user been removed? Have you logged into mysql, mysql -uuser -p and done show databases? -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 8/19/04 9:44 AM Subject: Databases not showing in cmd and mysqladmin? Just so you know I am pretty new to all this. I recently installed MySQL 4 on my PC. My friend gave me mysqlgui.exe to help configure stuff. So I made several databases using it. Now I know these databases are there, because I am running IPB on one of them, and they show up under the data directory. However, when I run and look at mysql via the command prompt, and enter in the mysqlshow command, all it does is show the original test database. This same thing occurs when I look at my databases in mysqladmin. All it shows is the original test database. What is going on? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Restoring dump file problem
Are you getting any error messages? -Original Message- From: Andre Mato To: [EMAIL PROTECTED] Sent: 8/19/04 9:59 AM Subject: Restoring dump file problem Hi List, Today I got a surprise when I tried to restore a dump file did yesterday. I did not work. I don't know if because is too big, I don't think so (just 3 lines). However, when I remove one table AuditTrail that tracks changes in the database, it worked. I am using MySQL 4.0.18-standard with InnoDB on Mac OS X 10.3.5. Is there any issue related a dumped file size? Thanks!!! Andre -- Andre Matos [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: query_cache_size will impact timeout variables ?
Currently your timeout values are set for four hours. I would first attempt to adjusty these to whatever suits your needs. Then connect and see if the connections are disconnected. -Original Message- From: Linda To: Victor Pendleton; [EMAIL PROTECTED] Sent: 8/17/04 9:13 PM Subject: Re: query_cache_size will impact timeout variables ? Hi Victor, My server's connections seem never time out. Please tell me how to fix it. The following is my.cnf: # The following options will be passed to all MySQL clients [client] #password = your_password port= 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock #skip-locking # max_allowed_packet = 8M myisam_sort_buffer_size = 64M # key_buffer = 1024M table_cache = 1024 record_buffer = 8M sort_buffer_size = 8M read_buffer_size = 8M # query_cache_size = 32M # max_connections = 1024 max_connect_errors = 100 # wait_timeout = 14400 interactive_timeout = 14400 # Try number of CPU's*2 for thread_concurrency thread_concurrency = 10 thread_cache = 10 My process status: Id User Host/IP DB TimeCmd Query or State -- --- -- --- -- 21110 admin 10.20.1.202 testdb 33786 Sleep 1220admin10.145.0.254 test1 33174 Sleep 23371 admin 10.20.1.156 testdb 30518 Sleep 3292admin10.145.0.254 test130510 Sleep 23427 admin 10.20.1.156 testdb 30487 Sleep Thanks Linda - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Linda ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 2:13 AM Subject: RE: query_cache_size will impact timeout variables ? These server settings are independent of one another. Query cache size should not effect your *_timeout values. Can you post the my.cnf section where these values are intialized? -Original Message- From: Linda To: [EMAIL PROTECTED] Sent: 8/17/04 10:10 AM Subject: query_cache_size will impact timeout variables ? Hi, I found out if I set query_cache_size = 32M to enable Query Cache, interactive_timeout and wait_timeout will not be effected. It makes the connections too many if the connections never timeout. How can I fix it? Linda -- 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: Can't connect to local MySQL server through socket
Either the socket has been removed or is in a different location than you think. Try connecting by host and port. I would also verify with a database restart that the socket is being created where you think it is. If you continue to have issues look over this page to see if it helps. http://dev.mysql.com/doc/mysql/en/Problems_with_mysql.sock.html -Original Message- From: Rajashik Kar To: [EMAIL PROTECTED] Sent: 8/18/04 2:06 AM Subject: Can't connect to local MySQL server through socket Hi, Iam getting the error Can't connect to local MySQL server through socket whenever Iam trying to start mysql prompt. Kindly Help. Following is the trace. Iam using an Unix Solaris8. svtlab7/opt/mysql/bin% mysql -u root -p Enter password: ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) svtlab7/opt/mysql/bin% ls /tmp/mysql.sock /usr/local/bin/ls: /tmp/mysql.sock: No such file or directory svtlab7/opt/mysql/bin% ps -aef | grep mysqld root 16448 16422 0 12:23:59 pts/50:00 grep mysqld root 9848 1 0 Aug 14 ?0:00 /bin/sh ./bin/safe_mysqld --defaults-extra-file=/opt/ALAams/ams4.4.7/config/mys root 9866 9848 0 Aug 14 ?0:01 /opt/mysql/bin/mysqld --defaults-extra-file=/opt/ALAams/ams4.4.7/config/mysql.c svtlab7/opt/mysql/bin% It seems the mysqld is running, but still Iam getting Can't connect to local MySQL server through socket error thanks, -raj Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- 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: Strange Text Field
What is the application you are using to insert and retrieve the blob data? Is the data actually being inserted? -Original Message- From: Stuart Felenstein To: [EMAIL PROTECTED] Sent: 8/18/04 2:49 PM Subject: Strange Text Field Hoping I can figure this out. Using MySQl 4.0.20-Standard. I have a table (tried this both myISAM and Innodb) with 12 fields, 3 are ints, 7 are varchars and 1 is set to text. Form is using a textarea for input into the text field. Basically a copy and paste of rich text. (I'm not concerned with formatting right now) Anyway it won't take and returns a vague SQL syntax error. If I try to input it directly into the database I get an error about the grid being 12 columns and the clipboard being 1 column. Yet I had all the other fields filled in. And all but the ID is set to allow nulls. Frustrated to figure out why this is occuring I created a table with 2 fields, one for the recordID, the other for the text dump. Works like a charm. So has anyone seen this before ? It seems odd , I've searched the manual and bug base and came up with nothing. Thank you , Stuart -- 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: I can delete, add, but not update
Can you log on as the web account and try to insert, update, select directly from the MySQL monitor? -Original Message- From: Chip Wiegand To: Victor Pendleton VPendleton Cc: '[EMAIL PROTECTED] ' Sent: 8/18/04 2:41 PM Subject: RE: I can delete, add, but not update Victor Pendleton [EMAIL PROTECTED] wrote on 08/17/2004 10:54:14 AM: Are there any errors being caught by the web form or being logged to the MySQL error log? I checked the log, there are no errors listed at all. It looks okay. No error message appears when running the script. I double checked my update script to make sure there were no spelling errors in the field names, and all looks good. It's just really strange. -- Chip -Original Message- From: Chip Wiegand To: [EMAIL PROTECTED] Sent: 8/17/04 11:29 AM Subject: I can delete, add, but not update I don't know for sure, but I think I may have something wrong with permissions in MySQL. I have some web forms from which I can add new data and delete data. But when I try to do an update of existing data the data is not actually changed. Is it possible that the permissions are not set up correctly in MySQL? Thanks, Chip Wiegand Computer Services Simrad, Inc 425-778-8821 425-771-7211 (FAX) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Strange Text Field
Are both of these fields defined as blob datatypes? Since one table works and the other does not work this leads me to question whether the application is performing consistently. Have you verified if data is entered into that does not display as expected? -Original Message- From: Stuart Felenstein To: Victor Pendleton; '[EMAIL PROTECTED] ' Sent: 8/18/04 3:14 PM Subject: RE: Strange Text Field In the table that fails, and the one that works, I'm using some products from a company called Interakt. I mentioned this in my last reply, however didn't mention one part. With this suite of tools , I can replace a text area with a mini html / cms tool. Basic formatting etc. Now in the 2nd table where the insertion works. The form works great, retains all the formatting of the original document. Stuart --- Victor Pendleton [EMAIL PROTECTED] wrote: What is the application you are using to insert and retrieve the blob data? Is the data actually being inserted? -Original Message- From: Stuart Felenstein To: [EMAIL PROTECTED] Sent: 8/18/04 2:49 PM Subject: Strange Text Field Hoping I can figure this out. Using MySQl 4.0.20-Standard. I have a table (tried this both myISAM and Innodb) with 12 fields, 3 are ints, 7 are varchars and 1 is set to text. Form is using a textarea for input into the text field. Basically a copy and paste of rich text. (I'm not concerned with formatting right now) Anyway it won't take and returns a vague SQL syntax error. If I try to input it directly into the database I get an error about the grid being 12 columns and the clipboard being 1 column. Yet I had all the other fields filled in. And all but the ID is set to allow nulls. Frustrated to figure out why this is occuring I created a table with 2 fields, one for the recordID, the other for the text dump. Works like a charm. So has anyone seen this before ? It seems odd , I've searched the manual and bug base and came up with nothing. Thank you , Stuart -- 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: contains?
There is not a function equivalent to Oracle's dump but you could throw together a substring loop that prints out the ascii value of each character. -Original Message- From: Martin Gainty To: [EMAIL PROTECTED] Sent: 8/18/04 3:24 PM Subject: contains? is there way to interrogate what is inside a column specifically a special character? thanks, Martin Gainty _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- 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: Full text for keyword(s) search?
If you are using MyISAM tables you can create a fulltext index. This index should give you better performance over a wildcarded like search provided that no more than fifty percent of your rows do not contain the word or words you will be searching for. -Original Message- From: Eve Atley To: [EMAIL PROTECTED] Sent: 8/16/04 7:18 PM Subject: Full text for keyword(s) search? If I want to search for a word (or multiple words) in a field on one table, searching through at least a thousand records (and growing daily), should I be using full text or something else to make this search faster? I'm using MySQL 3.23.58. Thanks, Eve -- 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: I can delete, add, but not update
Are there any errors being caught by the web form or being logged to the MySQL error log? -Original Message- From: Chip Wiegand To: [EMAIL PROTECTED] Sent: 8/17/04 11:29 AM Subject: I can delete, add, but not update I don't know for sure, but I think I may have something wrong with permissions in MySQL. I have some web forms from which I can add new data and delete data. But when I try to do an update of existing data the data is not actually changed. Is it possible that the permissions are not set up correctly in MySQL? Thanks, Chip Wiegand Computer Services Simrad, Inc 425-778-8821 425-771-7211 (FAX) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query_cache_size will impact timeout variables ?
These server settings are independent of one another. Query cache size should not effect your *_timeout values. Can you post the my.cnf section where these values are intialized? -Original Message- From: Linda To: [EMAIL PROTECTED] Sent: 8/17/04 10:10 AM Subject: query_cache_size will impact timeout variables ? Hi, I found out if I set query_cache_size = 32M to enable Query Cache, interactive_timeout and wait_timeout will not be effected. It makes the connections too many if the connections never timeout. How can I fix it? Linda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Assistance with SQL syntax: pulling duplicates back
You have a cartesian join because you do not have join criteria between the
resume and candidate tables.
-Original Message-
From: Eve Atley
To: [EMAIL PROTECTED]
Sent: 8/17/04 12:22 PM
Subject: Assistance with SQL syntax: pulling duplicates back
I think this is an easy question...I've set up a SQL statement like so:
SELECT resume.Section_Value, candidate.Location FROM resume, candidate
WHERE
resume.Section_ID = '1' AND MATCH (resume.Section_Value) AGAINST
('html')
AND candidate.Location LIKE '%CA%' OR 'California'
--
And where 'html' should come up in 1 entry, I get duplicates when
printing
out the field to the screen:
--
html unix network php Over 10 years of HTML experience. 2 years
networking
administration.
html unix network php Over 10 years of HTML experience. 2 years
networking
administration.
--
I can't decide if this is my code, or the SQL syntax. Would it be
possible,
based on this statement, to have pulled back duplicates from the same
record?
- Eve
--
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: Why VARCHAR TO CHAR automatically when the length less th an 4.
This is described here: http://dev.mysql.com/doc/mysql/en/Silent_column_changes.html. The table is created as a fixed table format. -Original Message- From: Emi Lu To: [EMAIL PROTECTED] Sent: 8/16/04 3:20 PM Subject: Why VARCHAR TO CHAR automatically when the length less than 4. Hello all, I have a questions about varchar columns change to CHAR columns automatically. When I designed a table t1 create table t1(id varchar(3) not null) in MySQL. After that, we use desc t1 to see the description of table t1. The result is the following: === +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | id| char(3) | YES | | NULL| | +---+-+--+-+-+---+ I did not understand why the varchar(3) is changed to char(3) automatically. While if we define a varchar(4) it will not change to char(4) automatically. Could somebody help and explain that please? Thanks a lot! Emi Lu -- 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: multiple server versions on 1 box startup error#
Have you set it up so that these instances run on different ports and different sockets? If you are not using mysqld multi you will have to specify which mysql/bin/safe_mysqld you want to start on the command line. -Original Message- From: sean c peters To: [EMAIL PROTECTED] Sent: 8/12/04 5:33 PM Subject: multiple server versions on 1 box startup error# I am trying to run two different MySQL server versions on the same solaris machine. I already have a 4.0.2 running, and i have successfully installed 4.1.3 beta. I ran mysql_install_db successfully (after setting LD_LIBRARY_PATH), but when i try to run mysqld_safe --user=mysql I get the error: A mysqld process already exists [1]+ Exit 1 ./mysqld_safe --user=mysql This is certainly true, because my 4.0.2 is running, but i dont understand why this is a problem. I am running the mysqld_safe for version 4.1.3, and when i built 4.1.3 i used the configure options: (among others) --prefix=/usr/loca/mysql-4.1.3 --with-tcp-port=3306 --with-unix-socket-path=/tmp/mysql-4.1.3.sock --datadir=/var/mysql-4.1.3 And these options are all different from the 4.0.2 configuration. So i assumed that things would run ok. The only thing i can think of is that the /vaar/mysql-4.1.3/my.cnf is not being read correctly. Perhaps i have an error in it, but i'd think that mysqld_safe would let me know. If its not being read, then some options from my default /etc/my.cnf are not being overridden, and that could be the problem. Here is the info on the /var/mysql-4.1.3/my.cnf file: -rw-r--r-- 1 root other 2042 Aug 12 18:08 my.cnf and the permissions ownership are identical to that for /etc/my.cnf I am at a loss thanks much. sean peters [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: Indexes
Yes. Those keys are redundant. -Original Message- From: Cemal Dalar To: Group MySQL List Sent: 8/13/04 7:12 AM Subject: Indexes mysql show index from urun; +---++--+--+-+-- -+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+-- -+-+--++--++-+ | urun | 0 | PRIMARY |1 | urun_id | A | 108725 | NULL | NULL | | BTREE | | | urun | 0 | UC_urun_id |1 | urun_id | A | 108725 | NULL | NULL | | BTREE | | | urun | 1 | IDX_urun_urun_id |1 | urun_id | A | 108725 | NULL | NULL | | BTREE | | | urun | 1 | ktgr |1 | ktgr| A | 512 | NULL | NULL | | BTREE | | +---++--+--+-+-- -+-+--++--++-+ 4 rows in set (0.00 sec) Also my show create table urun looks like this.. .. PRIMARY KEY (`urun_id`), UNIQUE KEY `UC_urun_id` (`urun_id`), KEY `IDX_urun_urun_id` (`urun_id`), KEY `ktgr` (`ktgr`) ) TYPE=MyISAM | Isn't this KEY `IDX_urun_urun_id` (`urun_id`), and UNIQUE KEY `UC_urun_id` (`urun_id`), indexes are unnecessary? Best Regards, Cemal Dalar a.k.a Jimmy System Administrator Web Developer http://www.dalar.net -- 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: datadir specification, etc
If possible, and for testing purposes try starting the 4.1.3 server with the options given on the command line. (datadir, pid, socket, port, etc...) -Original Message- From: sean c peters To: [EMAIL PROTECTED] Sent: 8/12/04 6:37 PM Subject: datadir specification, etc I am 100% convinced that mysql 4.1.3 beta is not properly reading the my.cnf configuration files. If i remove the /etc/my.cnf file and try to start mysql 4.1.3 with (im working from /usr/local/mysql-4.1.3/bin) ./mysqld_safe i get the following output: (mccoy is the name of the machine im on) touch: /usr/local/mysql-4.1.3/var/mccoy.err cannot create chown: /usr/local/mysql-4.1.3/var/mccoy.err: No such file or directory Starting mysqld daemon with databases from /usr/local/mysql-4.1.3/var ./mysqld_safe: /usr/local/mysql-4.1.3/var/mccoy.err: cannot create If i remove the /var/mysql-4.1.3/my.cnf file, i get the same output as above, so its not being read either way. And i did specify /var/mysql-4.1.3/ as my datadir with .configure when building 4.1.3 if i put the /etc/my.cnf file back, i get the following: A mysqld process already exists So clearly, the /etc/my.cnf is being read, but the /var/mysql-4.1.3/my.cnf is not. so i guess it doesnt matter what i specify in there at this point. One strange thing is that ./msqyd_safe tries to use the databases in /usr/local/mysql-4.1.3/var/ But i specified a different datadir with configure! my configure --prefix=/usr/local/mysql-4.1.3 but why should that matter? In fact, when i installed 4.1.3 (make install), the directory /usr/local/mysql-4.1.3/var/ was NOT created. I dont think most of the info ive given matters, because my run-time configuration doesnt appear to be the problem. I dont believe that my build configuration took effect properly. Does any of this make sense? Still completely lost. thanks sean peters [EMAIL PROTECTED] *** Here's some my.cnf data, if it really matters *** Here is part of the /var/mysql-4.1.3/my.cnf file: [client] port= 3307 socket = /tmp/mysql-4.1.3.sock pid-file= /usr/local/mysql-4.1.3/mysql-4.1.3.pid datadir = /var/mysql-4.1.3/ [mysqld] port= 3307 socket = /tmp/mysql-4.1.3.sock pid-file= /usr/local/mysql-4.1.3/mysql-4.1.3.pid datadir = /var/mysql-4.1.3/ And here is info from /etc/my.cnf file: [client] port= 3306 socket = /tmp/mysql.sock [mysqld] port= 3306 socket = /tmp/mysql.sock -- 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: Pinging
Can you describe your network setup? Are you expecting port 3306 to be open on your ISP? Are you ssh'ed in and you can not connect? -Original Message- From: EWAGW To: [EMAIL PROTECTED] Sent: 8/13/04 12:02 PM Subject: Pinging Hi list, I get this error from MySQL administrator, everything was fine before I converted the db and uploaded it to our host company. What is going on here? Thanks a lot for any help Could not connect to the specified host MySQL error Nr.2003 Cant connect to MySQL server on local host (10061) Click the ping button to see if there is a networking problem Pinging localhost Reply from 127.0.0.1: Time=0ms TTL=128 Reply from 127.0.0.1: Time=0ms TTL=128 I turn on the service as well and it turns off after about 5 seconds -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slave crashing
That depends on the setup and configuration and network and other factors. I have been running slaves for months and a year with little to no problems. Are you asking something in specific or just looking for feedback? -Original Message- From: Scott Hamm To: 'Mysql ' (E-mail) Sent: 8/13/04 11:51 AM Subject: Slave crashing I'm currently on research on SQL selection for our future Database project, and so far I've noticed that there are problem with slave, is it common for MySQL to have slave problem? I'm looking into Microsoft SQL Server, PostgreSQL and MySQL for our future project. Scott -- 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: Pinging
Do you know if the user you are using is allowed to connect from the localhost? Logically one would think that if the ISP granted you the ability to connect from outside the localhost, your host value would be wildcarded and thus you should be able to connect from the localhost as well. Do you have shell access to this MySQL server? -Original Message- From: EWAGW To: [EMAIL PROTECTED] Sent: 8/13/04 1:57 PM Subject: Re: Pinging Thanks for the post Victor, I can connect fine to my isp(through port 3306) , but not to localhost that is the problem - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'EWAGW ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, August 13, 2004 1:09 PM Subject: RE: Pinging Can you describe your network setup? Are you expecting port 3306 to be open on your ISP? Are you ssh'ed in and you can not connect? -Original Message- From: EWAGW To: [EMAIL PROTECTED] Sent: 8/13/04 12:02 PM Subject: Pinging Hi list, I get this error from MySQL administrator, everything was fine before I converted the db and uploaded it to our host company. What is going on here? Thanks a lot for any help Could not connect to the specified host MySQL error Nr.2003 Cant connect to MySQL server on local host (10061) Click the ping button to see if there is a networking problem Pinging localhost Reply from 127.0.0.1: Time=0ms TTL=128 Reply from 127.0.0.1: Time=0ms TTL=128 I turn on the service as well and it turns off after about 5 seconds -- 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: Tuning InnoDB situation
Do vmstat and top reveal anything about the server's performance? Can you post the show status output? -Original Message- From: Boyd E. Hemphill To: [EMAIL PROTECTED] Sent: 8/13/04 12:17 PM Subject: Tuning InnoDB situation All: I have been nosing about for some time now and think I need some help. The Problem: Mytop is telling me that I am running no more than 1000 queries per second, and the key efficiency is 100%. But, some select and replace statements are taking an unusually long time. These seem to revolve around a couple of tables that are written to and read from very often. The queries normally take no longer than 5 seconds in a test environment on a slower machine. In production (where the problem is) they can last from 90 to 1400 seconds. A few of these bring our site to a crawl. Suspicions: I have seen this machine run at 3000 to 6000 qps and still move data out fast. Some of the queries it is now performing slow are were part of this performance in the past. So my first idea is that the server, rather than the query, needs to be tuned. Here is the my.cnf stuff: [mysqld] port= 3306 socket = /var/run/mysqld/mysqld.sock skip-locking skip-bdb set-variable= key_buffer=16M set-variable= max_allowed_packet=10M set-variable= max_connections=1200 set-variable= table_cache=256 set-variable= sort_buffer=2M set-variable= net_buffer_length=64K set-variable= myisam_sort_buffer_size=32M log-bin server-id = 2 pid-file= /var/run/mysqld/mysqld.pid #log = /var/log/mysql/mysql.log log-slow-queries basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english default-table-type = innodb query-cache-type = 1 query-cache-size = 20M set-variable = net_read_timeout=600 set-variable= net_write_timeout=600 innodb_data_home_dir = /var/lib/mysql/innodb innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:15G:autoextend set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=1G set-variable = innodb_log_buffer_size=16M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=800M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 I am considering raising the table_cache to 1500 and the innodb_buffer_pool_size to 2.5GB. Comments on this would be appreciated as well. If this is not the issue then I suspect there is contention in some of the busy tables. Where do I look at the SHOW INNODB STATUS output to detect this situation? What am I looking for? Can I schedule InnoDB transactions? We are running Gentoo Linux 2.6.4 on a dual AMD Opteron machine with 3.5 GB of memory and a RAID 1+0 disc array. We are using the InnoDB storage engine. The DB contains about 160 tables. The DB is about 50GB in size. Thanks for your time. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: WOW! What a ride! -- 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: Fulltext Search takes 17 sec.
We Fulltext index multi million rows with very good response time. The
cardinality of `1` is okay. Did you fulltext index the rows as is or did you
create some sort of combined field?
-Original Message-
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 8/12/04 7:43 AM
Subject: RE: Fulltext Search takes 17 sec.
Hmmm... looks like it may be impossible to do fulltext searching on 3
million rows as few have chimed in on this one.
Just to add some background to my setup, I am currently running on MySQL
4.0.17 (plan to upgrade soon.. will this help??) and I created the index
that took 9 minutes to process using phpmyadmin. I did notice that when
I created the fulltext index that the fulltext has field size of 1
when looking at the keys in phpmyadmin. More specifically, it looks
like this:
KeynameTypeCardinalityAction__Field
PRIMARY...PRIMARY.3237981.product_id
search_text...FULLTEXT3237981.search_text..1
Also, would tuning mysql settings get the processing time down from 5-40
seconds to something more reasonable like 0.3 - 1.0 sec? My current
system variables are:
ft_min_word_len: 3
ft_max_word_len: 100
ft_max_word_len_for_sort: 20
max_heap_table_size: 16777216
max_join_size: 4294967295
max_seeks_for_key: 4294967295
max_sort_length: 1024
myisam_max_extra_sort_file_size: 268435456
myisam_max_sort_file_size: 2147483647
myisam_sort_buffer_size: 8388608
query_alloc_block_size: 8192
read_buffer_size: 131072
sort_buffer_size: 89128952
tmp_table_size: 33554432
I think I've heard of people getting good results will fulltext in under
a second with 3 million rows+ so I think its possible...
Help greatly appreciated,
- John
- Original Message
I am trying to do a fulltext search on my database of 3.3 million rows
(~ 1 Gb). I
attempted to make the searching faster by concatenating all columns I
wanted to search
and placed them in a table:
product_fulltext (product_id, search_text)
Then I added the fulltext index:
ALTER TABLE product_fulltext ADD FULLTEXT search_ft (search_text)
The index was created in about 9 minutes.
Next, I need the results grouped into categories. For instance, if I
search for
'Mountain' I would like to see the top 3 results for each category. For
instance
'mountain' might return:
Apparel
Mountainsmith Backpack
Mountain Hardware Sub Zero Jacket
Mountain Horse Stable Loafer
Food Beverage
Green Mountain Coffee
Mountain Grown Fodgers
Mountain Berry Tart
Video Games
No Fear Downhill Mountain Biking
Mountain Climber - Xbox
Cliff Hanger
Books
Mountain High, Mountain Rescue
Mountain Bike Magic
Go Tell It on the Mountain
.. etc ...
Obviously doing a fulltext search on each category individually wouldn't
be fast since
there are about 20 categories. I decided instead to make a TEMPORARY
TABLE, insert all
the matching product_ids into it, then group by category and limit for
the top 3.
Unfortunately, the temporary table insert combined with the fulltext
query takes much too
long:
CREATE TEMPORARY TABLE temp_table_326 SELECT product_id FROM
product_fulltext WHERE MATCH
( search_text ) AGAINST ('Mountain')
Query took: 17.46 seconds
So I tried a plain-jane fulltext select query to see if the temp table
was the issue:
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text )
AGAINST ('United
States')
Executed in: 13.52 seconds
At this point, I haven't even grouped by the top 3 results per category
(not sure how I
will do that yet) or joined the products on any tables for images etc.
I didn't set any
LIMIT parameters because if I said LIMIT 20, all 20 matches could be
Apparel products
and the other categories would not show up. The BEST performance I can
get is about 5
seconds on a single search term (as opposed to the 2 terms in 'United
States')
I'm at a loss here...
--
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: Problem after crash
Are you using InnoDB tables? If so, have you properly configured the directories? -Original Message- From: Jevos, Peter To: '[EMAIL PROTECTED]' Sent: 8/12/04 6:02 AM Subject: Problem after crash Hello Please help me. Im newbie in the database and I got one crash after outage. Now my mysql.server cant start. Im my log is nothing . And in err log in database directory is: ** 040811 20:26:59 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 040811 20:27:02 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... InnoDB: Error: all log files must be created at the same time. InnoDB: All log files must be created also in database creation. InnoDB: If you want bigger or smaller log files, shut down the InnoDB: database and make sure there were no errors in shutdown. InnoDB: Then delete the existing log files. Edit the .cnf file InnoDB: and start the database again. 040811 20:27:03 Can't init databases 040811 20:27:03 Aborting 040811 20:27:04 InnoDB: Warning: shutting down a not properly started InnoDB: or created database! 040811 20:27:04 /usr/local/mysql/libexec/mysqld: Shutdown Complete 040811 20:27:04 mysqld ended 040812 12:09:53 mysqld started 040812 12:09:53 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 50170 InnoDB: Doing recovery: scanned up to log sequence number 0 50170 InnoDB: Page directory corruption: supremum not pointed to 040812 12:09:53 InnoDB: Page dump in ascii and hex (16384 bytes): 040812 12:09:53 InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432 InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0 InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0 InnoDB: Error: trying to access a stray pointer c0cbbff8 InnoDB: buf pool start is at 404c8000, number of pages 512 040812 12:09:53 InnoDB: Assertion failure in thread 16384 in file ../../innobase/include/buf0buf.ic line 284 InnoDB: Failing assertion: 0 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x838c740 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Bogus stack limit or frame pointer, fp=0xbfffdcf8, stack_bottom=0x20, thread_stack=196608, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at (nil) is invalid pointer thd-thread_id=-488513313 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 040812 12:09:53 mysqld ended *** I look at this page but I cannot find an answer my problem What can I do ? Thanx a lot for any advice pet -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext Search takes 17 sec.
On your two word plus searches, `New York` for example, have you tried using
`IN BOOLEAN MODE` to reduce the number of false positives?
AGAINST(New York IN BOOLEAN MODE)
-Original Message-
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 8/12/04 8:33 AM
Subject: RE: Fulltext Search takes 17 sec.
Hi Victor,
The fulltext index was created on 1 column only that is of type
text.
The benchmarks I get are very inconsistant...
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text )
AGAINST ('China')
Query took 20.17 seconds
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text )
AGAINST ('Mongolia')
Query took 0.43 seconds
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text )
AGAINST ('russia')
Query took 6.18 seconds
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text )
AGAINST ('United States')
Query took 35.57 seconds
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text )
AGAINST ('Chicago')
Query took 11.81 seconds
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text )
AGAINST ('New York')
Query took 43.14 seconds
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text )
AGAINST ('heserfretzel')
Query took 0.04 seconds
The last word I made up. It seems to be directly proportional to the
number of results it pulls up.
:(
- John
--
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: Help, slave wont stay running!
Can you reset the slave to read the next event its relay log? If this is not possible, is refreshing the data from the master a viable option? -Original Message- From: matt ryan To: [EMAIL PROTECTED] Sent: 8/12/04 10:22 AM Subject: Help, slave wont stay running! I cant keep the slave up for more than 10 minutes constantly getting these errors 040812 10:32:25 Error reading packet from server: binlog truncated in the middle of event (server_errno=1236) 040812 10:32:25 Got fatal error 1236: 'binlog truncated in the middle of event' from master when reading data from binary log 040812 10:32:25 Slave I/O thread exiting, read up to log 'FINANCE-bin.185', position 284963878 both servers have plenty of free space Here is the master setup.. skip-locking set-variable= key_buffer_size=1000M set-variable=bulk_insert_buffer_size=256M set-variable=delay_key_write=ALL set-variable=join_buffer_size=256M set-variable= max_allowed_packet=256M set-variable= table_cache=512 set-variable= sort_buffer_size=256M set-variable=tmp_table_size=256M set-variable= record_buffer=256M set-variable= thread_cache=8 set-variable=myisam_sort_buffer_size=256M myisam-recover=BACKUP,FORCE set-variable=read_buffer_size=256M set-variable=interactive_timeout=7200 set-variable=wait_timeout=7200 here is the slave setup skip-locking set-variable= key_buffer_size=1500M set-variable=bulk_insert_buffer_size=512M set-variable=delay_key_write=ALL set-variable=join_buffer_size=512M set-variable= max_allowed_packet=384M set-variable= table_cache=512 set-variable= sort_buffer_size=256M set-variable=tmp_table_size=400M set-variable= record_buffer=384M set-variable= thread_cache=8 set-variable=myisam_sort_buffer_size=256M myisam-recover=BACKUP,FORCE set-variable=read_buffer_size=384M set-variable=interactive_timeout=7200 set-variable=wait_timeout=7200 I'm out of ideas, I've played with buffer sizes, packet sizes, but still get the same error my other master/slave has no problems at all, the slave is the same server (one box that's slave for two sites) -- 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: CSV Output
You can use mysqldump with the --fields-terminated by= option. -Original Message- From: David Perron To: [EMAIL PROTECTED] Sent: 8/12/04 1:07 PM Subject: CSV Output Im looking for a way to output a file into true CSV format (with quoted fields) using the Perl DBI. Does anyone know of a way to do this (in either native MySQL or Perl)? Thanks as always! -- 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: MySQL Tables
Unless you are specifying some other type in your table creation statements, your tables will be created as MyISAM. -Original Message- From: EWAGW To: [EMAIL PROTECTED] Sent: 8/11/04 11:30 AM Subject: MySQL Tables Hi list, If I am converting an access db to mysql what will the tables be MyISAM? 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: Indicating an INDEX during TEMPORARY table creation
You will need to know the name of the column(s) you will be indexing in order to do this. create temporary table some_table (index idx_name(col1) SELECT * from source_table -Original Message- From: David Perron To: [EMAIL PROTECTED] Sent: 8/11/04 11:36 AM Subject: Indicating an INDEX during TEMPORARY table creation When executing: CREATE TEMPORARY TABLE temp_1 AS SELECT * FROM table_name Is it possible to indicate an index within the syntax of this statement - as opposed to issuing an ALTER TABLE command after the table is created? Thanks in advance for any help. -- 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: Auto_increment and existing table
If you do an ALTER TABLE table_name MODIFY id INTEGER AUTO_INCREMENT, ADD PRIMARY KEY(id); ... The next record entered should be properly auto_incremented. -Original Message- From: Scott Hamm To: 'Mysql ' (E-mail) Sent: 8/11/04 1:32 PM Subject: Auto_increment and existing table I've imported Access DB into MySQL, and AutoID was lost. The number in ID column exists and I wonder if I update the column from int(10) to auto_increment, will it replace existing number with MySQL's auto_increment number? Or is there a proper way to update from int(10) to auto_increment without replacing existing data that are already there? -- 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: HELP!!! SEVERE: VendorError: 1064 during INSERT
Can you system.out.println the sql statement to verify what you have is correct? Also, are you using prepared statements? -Original Message- From: Shaffin Bhanji To: [EMAIL PROTECTED] Sent: 8/11/04 2:07 PM Subject: HELP!!! SEVERE: VendorError: 1064 during INSERT Hello All, I have the following environment: MySQL 4.1.3-beta-standard Connector/J 3.0.14 SUN Java JDK 1.4.2 SUSE Linux 9.1 I have written a java program to insert information into an emp table as follows: INSERT INTO dirxml.emp (fld_ind_id,fld_frname,fld_srname,fld_init,fld_sh_name1,fld_sh_name2,fld _posnacty_cd,fld_posnacty_name1,fld_posnacty_name2,fld_emp_job_desc1,fld _emp_job_desc2,fld_statrsn_cd,fld_statrsn_name1,fld_statrsn_name2,fld_rs n_cd,fld_aflgrp_cd,fld_aflgrp_name1,fld_aflgrp_name2,fld_city_t_name1,fl d_city_t_name2,fld_manager_indv_id,fld_comm_lang) VALUES(10087,SANIA,RANCOURT,,Miss,Mlle,TFR,T L VISION FRAN AISE R GIONALE,T L VISION FRAN AISE R GIONALE,MAKE-UP ARTIST,MAQUILLEUR (EUSE),R01,RETURN TO ACTIVE STATUS,REMISE EN ACTIVIT ,019,U2R,STARF,STARF,QUEBEC,QUEBEC,SYSM63,F); When I run this statement from MySQL Control Center, it work without any errors but when I execute it via java code, I get the following error: -- SEVERE: SQLException: Syntax error or access violation, message from server: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'QUEB' at line 1 -- SEVERE: SQLState: 42000 -- SEVERE: VendorError: 1064 Can someone please help what I may be doing wrong... Thanks, Shaffin. -- 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: mysqld_multi different server versions
You can use mysqld_multi to run different versions of MySQL on the same Server. You can run 4.0.x, 4.1.x and 5.0.x and manage these with mysqld_multi. -Original Message- From: sean c peters To: [EMAIL PROTECTED] Sent: 8/9/04 4:43 PM Subject: mysqld_multi different server versions In my ongoing quest to get upgraded to 4.1.3 beta (yes the version I'm upgrading to keeps changing), i have been reading about mysqld_multi to manage multiple server instances on the same machine. But, from what i've read, it appears that this is for running multiple instances of the same server version on one box. Same binary anyway. I say this because all the examples show as varying are the socket, port, pid-file, datadir, language, and user. The [mysqld_multi] directive for my.cnf points mysqld at a particular mysqld_safe, which would imply that all the instances would use the same server. So am i correct in thinking that i wont be able to use mysqld_multi for running two different server versions? This shouldn't be a problem, I dont think i'll need it. Just wanted to check if im missing something. thanks sean peters [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: using temp tables...
CREATE TEMPORARY TABLE table SELECT * FROM source_table ... Query temp table ... DROP TABLE temp_table -Original Message- From: bruce To: [EMAIL PROTECTED] Sent: 8/10/04 12:45 AM Subject: using temp tables... hi... in trying to get a better understanding of temp tables. is there a way to perform a select, to write the results to a temp table, and then use the temp table to perform another operation on the information within the temp table... ie... select * from foo write the results of the select to a temp table perform an operation on the information stored within the temp table i'm curious as to how it can be done within the mysql env. searching through google/mysql didn't spell this out to me... thanks -bruce -- 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: bug
Who did you log in as? -Original Message- From: Farnaz Akhavi To: [EMAIL PROTECTED] Sent: 8/10/04 9:57 AM Subject: bug I have downloaded and installed MYSQL Client/Server 4.0. 1. when I get a query on SHOW DATABASES, it only shows test and not mysql which has the users info 2. It doesn't give me access to create a new DATABASE and gives me an access denied error. Please advise. Thanks. p.s. I have worked with MSQL 2.0 and I never had these problems. Regards, Farnaz Akhavi Research Engineer Engineering Technology Industrial Distribution 303 Fermier Hall , 3367 TAMU Texas AM University College Station, TX - 77843-3367 Phone: 979-845-4939 Fax:979-845-4980 http://etidweb.tamu.edu/industrialdistribution/ -- 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: What would happen in these two cases?
The rename scenario is your best option. Just make sure you flush the tables to ensure that users are seeing current data. You normally do not want to `pull the rug` out from your users by deleting a table that they may be attempting to access. -Original Message- From: Haitao Jiang To: [EMAIL PROTECTED] Sent: 8/9/04 12:42 AM Subject: What would happen in these two cases? I hope this is not a so dumb question: Case 1: I have a table A under a running MySQL 4.1.3b server. If I replace A.MYD and A.MYI files without stop and start the server, will data gets corrupted? Case 2: I rename A to A1, and A2 to A, assume A, A2 have the same schema but different data/index. Is it bad? Will data gets corrupted? I tied this one, it seems ok. In both cases, there will be some queries on A, but no updates. Thanks Haitao -- 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: what is causing these XX.1.1.1.1 logs?
Are you in the process of stopping and restarting the MySQL Database server? -Original Message- From: Ciarochi, Anthony To: [EMAIL PROTECTED] Sent: 8/9/04 2:13 PM Subject: what is causing these XX.1.1.1.1 logs? For some reason, I am getting hundreds of zero-length binary log remnants in my log area. Any idea what could be causing this? Is there any danger in deleting the logs that don't show up in 'show master logs'? show master logs output: mysqld_bin.058 mysqld_bin.059 bash-2.05b# ls -altr | grep 059 -rw-rw1 psccats mysql 916402 Aug 9 11:43 mysqld_bin.059 bash-2.05b# ls -altr | grep 058 -rw-rw1 psccats mysql 2248499 Aug 8 23:30 mysqld_bin.058.1 -rw-rw1 psccats mysql 0 Aug 9 04:20 mysqld_bin.058 bash-2.05b# ls -altr | grep .057 -rw-rw1 psccats mysql 4275592 Aug 7 23:30 mysqld_bin.057.1.1 -rw-rw1 psccats mysql 0 Aug 9 04:20 mysqld_bin.057.1 bash-2.05b# ls -altr | grep .056 -rw-rw1 psccats mysql11127356 Aug 5 23:30 mysqld_bin.056.1.1.1 -rw-rw1 psccats mysql 0 Aug 7 04:19 mysqld_bin.056.1 -rw-rw1 psccats mysql 0 Aug 9 04:20 mysqld_bin.056.1.1 bash-2.05b# ls -altr | grep .055 -rw-rw1 psccats mysql17898151 Aug 3 23:30 mysqld_bin.055.1.1.1.1 -rw-rw1 psccats mysql 0 Aug 5 04:16 mysqld_bin.055.1 -rw-rw1 psccats mysql 0 Aug 7 04:19 mysqld_bin.055.1.1 -rw-rw1 psccats mysql 0 Aug 9 04:20 mysqld_bin.055.1.1.1 bash-2.05b# ls -altr | grep .054 -rw-rw1 psccats mysql 63008 Aug 1 23:30 mysqld_bin.054.1.1.1.1 -rw-rw1 psccats mysql 0 Aug 4 04:05 mysqld_bin.054.1 -rw-rw1 psccats mysql 0 Aug 6 04:28 mysqld_bin.054.1.1 -rw-rw1 psccats mysql 0 Aug 8 04:33 mysqld_bin.054.1.1.1 The pattern (in case it's not obvious): The logs are rotated every day or two, possibly by the backup script Every couple of days, each old log is renamed to mysqld.bin.0XX.1[.1...], and the old name appears as a zero-length file After several days, I have dozens and dozens of these zero length files in my log dir (the number grows exponentially). I am also getting dozens of zero-length mysql_bin_ind.index.1[.1...] files, all with the same time stamp (~4:00-4:30, today's date). Any idea what's up with that? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: AM software (access dump) and GUI
You can load the data by using the mysql command.
mysql -uuser -ppassword fileToLoad.sql
...
If you are on a shared database server I do not think you will be able to
issue the CREATE and DROP database commands.
-Original Message-
From: EWA Goodson Wickes
To: [EMAIL PROTECTED]
Sent: 8/9/04 3:47 PM
Subject: AM software (access dump) and GUI
Hi all,
I have just installed mysql on my pc and outside hosting with a
webhosting company.
I have an access db that I want to convert into mysql. I tried the
program
http://www.intranet2internet.com/public/default.asp?PAGE=softwareDETAIL
S=A2M
to convert a tiny access db as a test. This I did and now I have a .sql
file which I have pasted below. This file I uploaded via ftp to our
hosting with hostdepartment.com
What do I do with this file? Upload it via ftp? Thanks a lot for any
help
DROP DATABASE temp_db1;
CREATE DATABASE if not exists temp_db1;
USE temp_db1;
drop table Mailing_List;
CREATE TABLE if not exists Mailing_List (
MailingListID INT AUTO_INCREMENT NOT NULL,
FirstName CHAR(50),
MiddleName CHAR(30),
PRIMARY KEY ( MailingListID )
);
INSERT INTO Mailing_List VALUES ('1','ed','good');
INSERT INTO Mailing_List VALUES ('2','trev','wickes');
INSERT INTO Mailing_List VALUES ('3','ron','wickes');
This I uploaded to my website.
phpAdmin control panel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: cannot add mysql user on debian sarge
Are you attempting to add a user in the MySQL database or the mysql user on the system? -Original Message- From: Levi Campbell To: mysql Sent: 8/4/04 8:12 AM Subject: cannot add mysql user on debian sarge On debian sarge, I'm trying to add a user for MySQL but every time I try, I get the following error /usr/bin/perl: relocation error: /usr/perl5/locale/gettext/gettext.so: unrecognized symbol: Perl_gthr_key_ptr What do I need to do? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Default username and pw for MySQL Connector/J
The JDBC driver will not recognize the my.cnf file. -Original Message- From: Sergei Skarupo To: Mysql List (E-mail) Sent: 8/3/04 10:51 PM Subject: Default username and pw for MySQL Connector/J Hi everyone, Is there a standard way to specify a default user name and password for the java driver? For example, will it recognize .my.cnf like most mysql clinets do? Thanks, Sergei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
