Re: SQL challenge
1. The average TIME ELAPSED between consecutive REPEATED instances of the rowID (GROUP BY rowID, I assume) between one month ago and now. The easiest solution (if not the only) would be to handle this client side. If you sort the records by their timestamp you can easily keep track of the time between them and later on calculate the average interval. The problem here is that you do not want statistics on individual records, but you want statistics on data you calculate from two different records. You could do a self join to combine records of the same table, but to exclude the combination between row 1 and 3 is not trivial IMHO. The client script can easily handle these things, since it receives the records sequentially and so it can easily calculate differences between two neighbouring records. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to complie MySQL in MIPS platform?
hi, How to complie MySQL in MIPS platform? I have tried, but no success. jy
How to complie MySQL in MIPS platform?
hi, How to complie MySQL in MIPS platform? I have tried, but no success. jy
Re: C API -- huge result sets slowin me down
Hi,
The only thing that could slow you down is that the genAttrib array will take
more and more memory as the result set grows. I would recommend you to create
a function that uses the mysql row directly instead of creating this huge
array.
something like
while ((row = mysql_num_rows(result))){
usedata(row);
}
Of course it depends on what do you need the mysql data for - but if you can
make it to use one row at a time it should run a lot more faster.
--
Dobromir Velev
[EMAIL PROTECTED]
http://www.websitepulse.com/
On Tuesday 29 June 2004 08:50, Matt Eaton wrote:
Hi all,
I was hoping this was the right place for a question about the C API.
I've been grabbing result sets from tables in the C API for a few years
now, but I'm starting to work with result sets that are big enough to
bog me down. Of course, the result sets aren't insanely big, so I was
wondering why it was taking so long for me to suck them in to C,
especially when I can run the same query from the command line using the
binaries and they can cache it to a file on the hard disk pretty much
instantly. So, basically, I was just hoping that I've been doing
something wrong, or at least that there was something I could do better,
to make my database communication as fast as the mysql command line
tools. I've checked out their source and nothing obvious jumps out at
me. Here's a non-functional sample of my code:
int main(int argc, char *argv[] ) {
int uid;
int sid;
char sqlBuff[4000];
int err = 0;
int i;
// Setup the database communications space:
MYSQL dbase;
MYSQL_RES *result;
MYSQL_ROW row;
float **genAttrib;
//... snip ...
// Connect to the database:
if (mysql_init(dbase) == NULL) err = 1;
else {
if(mysql_real_connect(dbase,localhost,login,pass,test,0,NULL,CL
IENT_FOUND_ROWS) == NULL) {
err = 1;
fprintf(stderr, Failed to connect to database:
Error: %s\n,
mysql_error(dbase));
}
}
// If the connection couldn't be established:
if(err) {
printf(db connection failed!\n);
exit(1);
}
//... snip ...
// This query could have as many as a million rows returned, but
the query itself runs quite fast. It seems to just be
// sucking it into C that can take up to four seconds on our
dual Xeon server.
sprintf(sqlBuff,SELECT A.* FROM `attribs` as A, login AS L
WHERE A.guid=L.guid AND L.isActive=1 AND L.sid=%d AND
A.guid!=%d,sid,uid);
if (mysql_real_query(dbase,sqlBuff,strlen(sqlBuff))) {
printf(Pool Attributes Select Failed... dumbass\n);
fprintf(stderr, Error: %s\n,
mysql_error(dbase));
exit(1);
}
result = mysql_store_result(dbase);
numRows=mysql_num_rows(result);
for (i=0;inumRows;i++) {
row = mysql_fetch_row(result);
tempq=atoi(row[1]);
tempP=atoi(row[0]);
genAttrib[tempP][tempq]=atoi(row[2]);
}
return 0;
}
So, if someone sees something that I could change to speed things up, or
I should direct this question elsewhere... thanks for your help and
thanks for reading this far!
Thanks again,
Matt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to complie MySQL in MIPS platform?
At first, Thanks a lot! My platform is MIPS CPU. After I got the MySQL source code, I compiled it in a develop X86 PC. In the develop PC, I compile MySQL by a Cross-Compiler for MIPS. Its name is mipsel-linux-gcc. As you know, before I compile the project, I should execute ./configure to generate Makefile. But what argument should be add, so as to generate the Makefile we want? That is to say, how to compile the project by mipsel-linux-gcc? JY - Original Message - From: juhui [EMAIL PROTECTED] To: Jianyu Lin [EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 4:00 PM Subject: Re: How to complie MySQL in MIPS platform? what error you encounter? On Tue, 29 Jun 2004 15:38:06 +0800, Jianyu Lin [EMAIL PROTECTED] wrote: hi, How to complie MySQL in MIPS platform? I have tried, but no success. jy -- LB can FlY
[ANN] SQL Maestro 1.4 released
Hello, SQL Maestro Group is happy to introduce a new release of SQL Maestro - a powerful Win32 GUI solution for MySQL server administration and database development. A fully functional trial version of SQL Maestro is available at http://www.sqlmaestro.com/products/download.html. SQL Maestro supports all the latest versions of MySQL, including MySQL 4.1/5.0, and all of the most important MySQL features, including: - stored procedures and functions; - user-definable functions; - InnoDB foreign keys; - transaction-safe tables; - BLOB and TEXT field types; - MySQL 4.x user privilege extensions; - and many more. On top of everything, SQL Maestro provides you with powerful client- side features, such as multi-functional database explorer, query repository, visual query builder, data export and import, drag-and-drop operations, diagram viewer, etc. For more information, please visit SQL Maestro website at http://www.sqlmaestro.com. Best regards, SQL Maestro Group --- http://www.sqlmaestro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication, max_allowed_packet and LOAD DATA INFILE
Hi, Someone please clear things up for me a little! Here is a try :) Seems the data exceeded the max_allowed_packet size which is 1M on master and slaves so all slaves stopped replicating. Question is, why did this happen and how could this have been avoided? Should max_allowed_packet also limit the LOAD DATA INFILE size so that it would stop and warn on the master instead of on the slaves...? There is no constraint like the loaded file must be max_allowed_packet. You can load a GBs file. What happens is that this file is written in chunks into the master's binary log. If you do mysqlbinlog on the binlog you'll see something like: LOAD DATA INFILE # Append_block ... # Append_block ... # Append_block ... (one Append_block per chunk) # Append_block ... # Execute_load When the slave sees the LOAD DATA INFILE it knows it creates a temporary file. Then every time it sees an Append_block, it copies the file block it contains into the temp file. Finally when it sees the execute load, it loads the temp file into the table. Each chunk is rather small (1MB or slightly more). When you set SQL_SLAVE_SKIP_COUNTER, each chunk counts for 1. Only mysqlbinlog can show you how many chunks there are and so what the right value for SQL_SLAVE_SKIP_COUNTER is (it is at least 2; if file is small there is no Append_block; so then it's the LOAD line plus the Execute_load line = 2). Why this logging in chunks instead of one big file? Concurrency. If we were to write 500 MB to the binary log in one chunk, it would lock the binary log for the time to write 500 MB; during this, all other updates would be stalled (as they need to write to the binary log too). Because the data loaded wasn't important I did not care much to find a better solution... I just wanted to skip the errors and let mysql get on with it... After I got one slave running I did the following on the rest of the slaves: SLAVE STOP; SET GLOBAL max_allowed_packet=1000; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; SLAVE START; I could probably have changed position with CHANGE MASTER TO instead... (finding the proper good position by running mysqlbinlog on the master logs) *** Events description follows: 1. LOAD DATA INFILE statement replicated to slaves. The CSV file was larger than max_allowed_packet: Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master (server_errno=1236) Got fatal error 1236: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master' from master when reading data from binary log Slave I/O thread exiting, read up to log 'george-bin.3794', position 241147667 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306\', replication started in log 'george-bin.3794' at position 241147667 2. max_allowed_packet was increased on the MASTER with the following command: MASTER: SET GLOBAL max_allowed_packet=10475220 (10M) Now the complaint is because of the slave's max_allowed_packet instead: Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'george-bin.3794' at position 241147667 Error reading packet from server: Packet too large - increase max_allowed_packet on this server (server_errno=1153) Log entry on master is longer than max_allowed_packet (1047552) on slave. If the entry is correct, restart the server with a higher value of max_allowed_packet Slave I/O thread exiting, read up to log 'george-bin.3794', position 241147667 yes, both master and slave need the packet to fit, as they both use the packet in network communication. 3. So I increase the max_allowed_packet on the slave as well: SLAVE: SET GLOBAL max_allowed_packet=10475220 This time when I do the SLAVE START it messes up some more: Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'george-bin.3794' at position 241147667 Error in Log_event::read_log_event(): 'Event too big', data_len: 1069482, event_type: 8 --- Why does it say Event too big? max_allowed_packet should now have been changed to 10M, data_len above == 1.1M. --- Or did the replication SQL thread fail to understand the new max_allowed_packet I set? Yes. When you do a SET GLOBAL it affects only new threads. When you did the SET GLOBAL, slave I/O thread did not exist (it had terminated because of the Error reading packet from server: Packet too large. BUT slave SQL thread did exist (it had no reason to terminate; there was nothing in the relay log but hey that could be normal). Then after the SET you did START SLAVE. This started only what needed to be started, i.e. the slave I/O thread. So, the slave SQL thread was the one which already existed before the SET. So it did not take into account the SET. You would need to STOP SLAVE; START SLAVE;. 4. So the next step SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; followed by another SLAVE START: Slave: Error in Exec_load event:
Re: Migrating Access Tables -- Empty Columns, Date and Time
Bob My versions are Access 2000 and MySQL 4.0.20. Here's what I do. When exporting my Access table, I choose the file type Text Files. Then, on the next dialog box, make sure to click the Advanced button to get to the formatting information. Change the date order to YMD and the date delimiter to -. That will take care of the date. As for the NULL value. The table into which I'm importing has columns defined to allow NULLs, but when I import the Access table with empty columns, like you describe, it doesn't force a null -- it just leaves the value blank. It doesn't cause any problems, but if you want to force a NULL then I guess you could rework the exported file to state NULL for the empty fields you want to read as such. Wes On Jun 28, 2004, at 10:20 PM, Robert L Cochran wrote: 2) When consecutive commas (meaning at least 1 empty column, sometimes several) are seen, what does mysqlimport/LOAD DATA do to the corresponding column entrie(s)? Will it set them to NULL? Or to the default specified in the CREATE TABLE statement? Should I explicitly set these to NULL where permitted by the column type? Last of all, look at this date and time stamp exported by Access: ,2/12/1998 0:00:00, Will mysqlimport choke on this, since MySQL likes dates to be in ccyy-mm-dd format? Will I need to reformat the date with a sed script? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
defaults for net_read_timeout and net_write_timeout?
Hello, what are the defaults for the following options? net_read_timeout net_write_timeout Regards Marten Lehmann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Federated servers
Hello! We are currently running with one big ms sql-server. Is it possible to do federated servers on mysql? What I want to do is to purchase one more server and split the work load on the two servers. Any ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
authentication error
Hello, I run a toaster email server which is having trouble authenticating at times. Both methods of authentication, imap AND web for some reason just hang. I'm pretty new to MySql but I figure if both methods are trying to authenticate against the same table, the problem is there. I activated binary logging, what else can I do to find the issue? Thanks
Re: Memory to Memory INSERTS
Shawn, Very Interesting idea. I definitely want to look into this a bit more. I fear though that the bin-logs might be written first to disk before they are copied over to the replicas. Another member of my team mentioned there might be a way to issue direct MyISAM table INSERTS. She suggested that we could bypass the parsing and param checking of our large string-based INSERT queries. Since our sampling application can guarantee the inputs on the INSERT, this would offer us a potential performance boost as there is no reason for MySQL to verify our INSERT query inputs a second time. Does anyone know if the direct table/index insert idea is valid? At 09:43 AM 6/29/04, you wrote: Could Alejandro re-use the sections of the MySQL source code that handle replication and bin-logging to make his data capture application appear as a Master server and have his MySQL database act as its slave? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Startup
Hello I created the icon but nothing happens. What do I need to do? Andrew At 03:20 PM 6/19/2004, Andrew McHorney wrote: Hello I installed the software. I would like to start up the server but there is no icon. What is the name of the executable so I can make an icon? I am running under Windows. Thanks, Andrew -- 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: Federated servers
On Tue, 29 Jun 2004 [EMAIL PROTECTED] wrote: Hello! We are currently running with one big ms sql-server. Is it possible to do federated servers on mysql? What I want to do is to purchase one more server and split the work load on the two servers. Yes, you can use master/slave replication in MySQL to share the load across multiple serevrs. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: authentication error
What does the error log say? -Original Message- From: Chip Bell To: [EMAIL PROTECTED] Sent: 6/29/04 9:03 AM Subject: authentication error Hello, I run a toaster email server which is having trouble authenticating at times. Both methods of authentication, imap AND web for some reason just hang. I'm pretty new to MySql but I figure if both methods are trying to authenticate against the same table, the problem is there. I activated binary logging, what else can I do to find the issue? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Federated servers
[EMAIL PROTECTED] wrote: We are currently running with one big ms sql-server. Is it possible to do federated servers on mysql? What I want to do is to purchase one more server and split the work load on the two servers. Any ideas? On a quick note: What about MySQL replication? http://dev.mysql.com/doc/mysql/en/Replication.html A tutorial discussing what you can use replication for can be found here (chapter 7 of Jeremy Zawodny's High Performance MySQL): http://dev.mysql.com/books/hpmysql-excerpts/ch07.html Regards, Stefan Hinz -- Stefan Hinz [EMAIL PROTECTED] MySQL AB, Documentation Team Taunusstr. 27, 12161 Berlin, Germany Desk: +49 30 82702940 Fax: +49 30 82702941 Mobile: +49 177 7841069 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Startup
I'm assuming your a first time user of mySQL, so I'm going to give you the basics, if I have miss read your post and all of the following you have done I apologise: There is no icon... So you have already installed mySQL? You need to install it as a service, follow this guide: http://www.kitebird.com/articles/nt-serv.html Then get a copy of mySQL CC http://dev.mysql.com/downloads/mysqlcc.html This is the GUI to mySQL and if your a windows person (like me) you will find this easier to use. Hope that gets you started -- Dave - Original Message - From: Andrew McHorney [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 3:26 PM Subject: Re: Server Startup Hello I created the icon but nothing happens. What do I need to do? Andrew At 03:20 PM 6/19/2004, Andrew McHorney wrote: Hello I installed the software. I would like to start up the server but there is no icon. What is the name of the executable so I can make an icon? I am running under Windows. Thanks, Andrew -- 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: Migrating Access Tables -- Empty Columns, Date and Time
Why don't you use the export utility in ms access to load the data into mysql. The export will create the table in the database. All you need is a dsn connection and the mysql odbc. Osvaldo Sommer -Original Message- From: Robert L Cochran [mailto:[EMAIL PROTECTED] Sent: Monday, June 28, 2004 8:21 PM To: [EMAIL PROTECTED] Subject: Migrating Access Tables -- Empty Columns, Date and Time I'm migrating a Microsoft Access 2002 (Service Pack 3) table constructed by my wife to a corresponding table in MySQL 4.0.20. Some columns in most of the 3000+ rows are empty. Some of these are contiguous empty columns. I don't know if Access considers them NULL or not, but when you export an Access row containing empty columns to a comma separated values file, the empty column will be represented by a sequence of placeholder commas. Here is a part of the first table row exported by Access: WEEKEND,,8,1,,0,,,at,,,2/12/1998 0:00:00,11/27/1998 0:00:00,,MB Based on recent experience with loading a simpler Access table, these empty columns will be imported as is by both mysqlimport and LOAD DATA LOCAL INFILE, but with warnings. I have 2 questions associated with this: 1) How do I make mysqlimport or LOAD DATA LOCAL tell me the text of each warning? By default they print a summary count of warnings but don't issue actual warning messages. The default log files show nothing. mysqlimport -v does not do it. 2) When consecutive commas (meaning at least 1 empty column, sometimes several) are seen, what does mysqlimport/LOAD DATA do to the corresponding column entrie(s)? Will it set them to NULL? Or to the default specified in the CREATE TABLE statement? Should I explicitly set these to NULL where permitted by the column type? Last of all, look at this date and time stamp exported by Access: ,2/12/1998 0:00:00, Will mysqlimport choke on this, since MySQL likes dates to be in ccyy-mm-dd format? Will I need to reformat the date with a sed script? Thanks Bob Cochran Greenbelt, Maryland, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 6/27/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 6/27/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to turn rows into columns
Hi , Sorry to ask this question, but I'm in desperate need to acomplish this report, and don't even know it is possible to be done. The thing is I have to turn rows from one table into columns in other table. The first table looks like this T1 --- id cod --- 1 bb 1 ff 1 gg 1 kk 2 rr --- An it should be converted to the following format where every cod value pertaining to T1 should be located into T2 under the specified column T2 --- id c1 c2 c3 c4 --- 1 bb ff gg kk 2 rr --- I know how to perform this under other languages but don't know how to do it with mysql's sql. I think someone else must have had the same requirement before, at least I hope so. Thanks in advance, Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INNODB transaction log size
Keep in my mind that if something does go `wrong` you could possibly have to wait hours for all of your transactions to roll back. -Original Message- From: Michael Lee To: '[EMAIL PROTECTED] ' Sent: 6/28/04 9:35 PM Subject: RE: INNODB transaction log size Victor, Thanks for your reply. Actually, i cannot find such an option and want to see if i have missed something. Referring to Innodb transaction log, I do some more searching and would like to confirm what i found from the web (this information is not available in MySQL documentation) . Transaction Log file is the redo log and can be overwritten by the a single transaction . Undo Log is stored in the innodb tablespace and used for rollback of uncommitted transaction. According to the quota below (from ACID Transaction in MySQL with InnoDB by Arjen Lentz) Writing uncommitted data to the tablespace (with checkpoints) ensures that transaction size is not limited by memory or log file size, but simply by the size of the tablespace Therefore, I do not need to increate the log file size even though a single Load Data statement insert millions rows. For those innodb expert, please inform me whether my conculsion is right or wrong. Thanks Regards, Michael Victor Pendleton [EMAIL PROTECTED] wrote: I do not believe this is currently an option in the `load data infile` syntax. One option would be to read the file programmatically and issue the commits after `x` number of inserts. -Original Message- From: Michael Lee To: [EMAIL PROTECTED] Sent: 6/28/04 1:21 AM Subject: INNODB transaction log size Hi, I would like to migrate my DB from Sybase ASE to MySQL INNODB table. Data has been extracted and stored as a file. I want to use the command Load Data Infile to insert the data to MySQL. However, some table contains millions of rows. Can i control the batch size of the loading (e.g. commit the transaction after 5 rows inserted)? If no, should i define a very large transaction log to handle the huge transaction? (currently, it is 5M) Any suggestion is welcomed. TIA Michael ???... ?? http://mobile.yahoo.com.hk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ???... ?? http://mobile.yahoo.com.hk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Broken Pipe Error with mysqlaccess
Hello List I've recently compiled and installed the latest stable version of MySQL from source file mysql-4.0.20.tar.gz on Red Hat Linux Advanced Server 3 (without installing the version of MySQL contained within the Red Hat OS install), using the standard configure, make and make install. For security I gave the root user a password using the mysqladmin utility. Everything has been running perfectly apart from the mysqlaccess utility, which only appears to work once root has had its password set to '': Thus with password set to '': [EMAIL PROTECTED] bin]# mysqlaccess root mysql mysqlaccess Version 2.06, 20 Dec 2000 By RUG-AIV, by Yves Carlier ([EMAIL PROTECTED]) Changes by Steve Harvey ([EMAIL PROTECTED]) This software comes with ABSOLUTELY NO WARRANTY. Access-rights for USER 'root', from HOST 'localhost', to DB 'mysql' +-+---+ +-+---+ | Select_priv | Y | | Show_db_priv| Y | | Insert_priv | Y | | Super_priv | Y | | Update_priv | Y | | Create_tmp_table_priv | Y | | Delete_priv | Y | | Lock_tables_priv | Y | | Create_priv | Y | | Execute_priv| Y | | Drop_priv | Y | | Repl_slave_priv | Y | | Reload_priv | Y | | Repl_client_priv | Y | | Shutdown_priv | Y | | Ssl_type| ? | | Process_priv| Y | | Ssl_cipher | ? | | File_priv | Y | | X509_issuer | ? | | Grant_priv | Y | | X509_subject| ? | | References_priv | Y | | Max_questions | 0 | | Index_priv | Y | | Max_updates | 0 | | Alter_priv | Y | | Max_connections | 0 | +-+---+ +-+---+ BEWARE: Everybody can access your DB as user `root' from host `localhost' : WITHOUT supplying a password. : Be very careful about it!! The following rules are used: db: 'No matching rule' host : 'Not processed: host-field is not empty in db-table.' user : 'localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y ','Y','Y','Y','Y','Y','Y','Y','','','','','0','0','0' BUGs can be reported by email to [EMAIL PROTECTED] Change the password from '' to 'password' (thus no special characters in the password): [EMAIL PROTECTED] bin]# mysqladmin -u root -p password 'password' Enter password: [EMAIL PROTECTED] bin]# mysqladmin -u root -p status Enter password: Uptime: 15310 Threads: 1 Questions: 323 Slow queries: 0 Opens: 12 Flush tables: 1 Open tables: 4 Queries per second avg: 0.021 Now try to run mysqlaccess with the new password: [EMAIL PROTECTED] bin]# mysqlaccess -u root -p -d mysql mysqlaccess Version 2.06, 20 Dec 2000 By RUG-AIV, by Yves Carlier ([EMAIL PROTECTED]) Changes by Steve Harvey ([EMAIL PROTECTED]) This software comes with ABSOLUTELY NO WARRANTY. Password for MySQL user root: Broken pipe Checking everything else is still alright with the new password: [EMAIL PROTECTED] bin]# mysqladmin -u root --password='password' status Uptime: 15375 Threads: 1 Questions: 324 Slow queries: 0 Opens: 12 Flush tables: 1 Open tables: 4 Queries per second avg: 0.021 Reset password to '': [EMAIL PROTECTED] bin]# mysqladmin -u root -p password '' Enter password: [EMAIL PROTECTED] bin]# mysqlaccess -u root -p -d mysql mysqlaccess Version 2.06, 20 Dec 2000 By RUG-AIV, by Yves Carlier ([EMAIL PROTECTED]) Changes by Steve Harvey ([EMAIL PROTECTED]) This software comes with ABSOLUTELY NO WARRANTY. Password for MySQL user root: Access-rights for USER 'root', from HOST 'localhost', to DB 'mysql' +-+---+ +-+---+ | Select_priv | Y | | Show_db_priv| Y | | Insert_priv | Y | | Super_priv | Y | | Update_priv | Y | | Create_tmp_table_priv | Y | | Delete_priv | Y | | Lock_tables_priv | Y | | Create_priv | Y | | Execute_priv| Y | | Drop_priv | Y | | Repl_slave_priv | Y | | Reload_priv | Y | | Repl_client_priv | Y | | Shutdown_priv | Y | | Ssl_type| ? | | Process_priv| Y | | Ssl_cipher | ? | | File_priv | Y | | X509_issuer | ? | | Grant_priv | Y | | X509_subject| ? | | References_priv | Y | | Max_questions | 0 | | Index_priv | Y | | Max_updates | 0 | | Alter_priv | Y | | Max_connections | 0 | +-+---+ +-+---+ BEWARE: Everybody can access your DB as user `root' from host `localhost' : WITHOUT supplying a password. : Be very careful about it!! The following rules are used: db: 'No matching rule' host : 'Not processed: host-field is not empty in db-table.' user : 'localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y ','Y','Y','Y','Y','Y','Y','Y','','','','','0','0','0' BUGs can be reported by email to [EMAIL PROTECTED] Can
RE: authentication error
Nothing actually. I'm guessing I don't have enough logging turned on. The only thing my servername.err files shows is Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do do not.etc -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:04 PM To: Chip Bell; '[EMAIL PROTECTED] ' Subject: RE: authentication error What does the error log say? -Original Message- From: Chip Bell To: [EMAIL PROTECTED] Sent: 6/29/04 9:03 AM Subject: authentication error Hello, I run a toaster email server which is having trouble authenticating at times. Both methods of authentication, imap AND web for some reason just hang. I'm pretty new to MySql but I figure if both methods are trying to authenticate against the same table, the problem is there. I activated binary logging, what else can I do to find the issue? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Migrating Access Tables -- Empty Columns, Date and Time
Show warnings is not available until 4.1.x ... The empty field will be imported as that. Empty and not a null value. ... Have you tried importing the data yet to see how the date fields look in MySQL? -Original Message- From: Robert L Cochran To: [EMAIL PROTECTED] Sent: 6/28/04 9:20 PM Subject: Migrating Access Tables -- Empty Columns, Date and Time I'm migrating a Microsoft Access 2002 (Service Pack 3) table constructed by my wife to a corresponding table in MySQL 4.0.20. Some columns in most of the 3000+ rows are empty. Some of these are contiguous empty columns. I don't know if Access considers them NULL or not, but when you export an Access row containing empty columns to a comma separated values file, the empty column will be represented by a sequence of placeholder commas. Here is a part of the first table row exported by Access: WEEKEND,,8,1,,0,,,at,,,2/12/1998 0:00:00,11/27/1998 0:00:00,,MB Based on recent experience with loading a simpler Access table, these empty columns will be imported as is by both mysqlimport and LOAD DATA LOCAL INFILE, but with warnings. I have 2 questions associated with this: 1) How do I make mysqlimport or LOAD DATA LOCAL tell me the text of each warning? By default they print a summary count of warnings but don't issue actual warning messages. The default log files show nothing. mysqlimport -v does not do it. 2) When consecutive commas (meaning at least 1 empty column, sometimes several) are seen, what does mysqlimport/LOAD DATA do to the corresponding column entrie(s)? Will it set them to NULL? Or to the default specified in the CREATE TABLE statement? Should I explicitly set these to NULL where permitted by the column type? Last of all, look at this date and time stamp exported by Access: ,2/12/1998 0:00:00, Will mysqlimport choke on this, since MySQL likes dates to be in ccyy-mm-dd format? Will I need to reformat the date with a sed script? Thanks Bob Cochran Greenbelt, Maryland, USA -- 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: authentication error
Can you view or log the errors that imap and the web authentication are getting? Try issuing a flush-hosts to see if the max_connect_errors was reached. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 11:47 AM Subject: RE: authentication error Nothing actually. I'm guessing I don't have enough logging turned on. The only thing my servername.err files shows is Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do do not.etc -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:04 PM To: Chip Bell; '[EMAIL PROTECTED] ' Subject: RE: authentication error What does the error log say? -Original Message- From: Chip Bell To: [EMAIL PROTECTED] Sent: 6/29/04 9:03 AM Subject: authentication error Hello, I run a toaster email server which is having trouble authenticating at times. Both methods of authentication, imap AND web for some reason just hang. I'm pretty new to MySql but I figure if both methods are trying to authenticate against the same table, the problem is there. I activated binary logging, what else can I do to find the issue? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: authentication error
Ok, I tried to run flush-hosts from both # and inside mysql..nada. How can I do this exactly? Sorry about my lack of knowledge, I'm still green. I have my books though and am feverishly trying to find the answer! Thanks for your help!! -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:53 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Can you view or log the errors that imap and the web authentication are getting? Try issuing a flush-hosts to see if the max_connect_errors was reached. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 11:47 AM Subject: RE: authentication error Nothing actually. I'm guessing I don't have enough logging turned on. The only thing my servername.err files shows is Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do do not.etc -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:04 PM To: Chip Bell; '[EMAIL PROTECTED] ' Subject: RE: authentication error What does the error log say? -Original Message- From: Chip Bell To: [EMAIL PROTECTED] Sent: 6/29/04 9:03 AM Subject: authentication error Hello, I run a toaster email server which is having trouble authenticating at times. Both methods of authentication, imap AND web for some reason just hang. I'm pretty new to MySql but I figure if both methods are trying to authenticate against the same table, the problem is there. I activated binary logging, what else can I do to find the issue? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: authentication error
From the mysql monitor it is `FLUSH HOSTS;` . Does the email server not log failed connection attempts? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 11:55 AM Subject: RE: authentication error Ok, I tried to run flush-hosts from both # and inside mysql..nada. How can I do this exactly? Sorry about my lack of knowledge, I'm still green. I have my books though and am feverishly trying to find the answer! Thanks for your help!! -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:53 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Can you view or log the errors that imap and the web authentication are getting? Try issuing a flush-hosts to see if the max_connect_errors was reached. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 11:47 AM Subject: RE: authentication error Nothing actually. I'm guessing I don't have enough logging turned on. The only thing my servername.err files shows is Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do do not.etc -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:04 PM To: Chip Bell; '[EMAIL PROTECTED] ' Subject: RE: authentication error What does the error log say? -Original Message- From: Chip Bell To: [EMAIL PROTECTED] Sent: 6/29/04 9:03 AM Subject: authentication error Hello, I run a toaster email server which is having trouble authenticating at times. Both methods of authentication, imap AND web for some reason just hang. I'm pretty new to MySql but I figure if both methods are trying to authenticate against the same table, the problem is there. I activated binary logging, what else can I do to find the issue? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: authentication error
I ran the FLUSH HOSTS and it said 0 rows affected The authentication goes against the mysql table, which is where I'm guessing the errors would show. When I try to log in during the slow down of the server, it just hangs and hangs and finally will let me through. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:02 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error From the mysql monitor it is `FLUSH HOSTS;` . Does the email server not log failed connection attempts? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 11:55 AM Subject: RE: authentication error Ok, I tried to run flush-hosts from both # and inside mysql..nada. How can I do this exactly? Sorry about my lack of knowledge, I'm still green. I have my books though and am feverishly trying to find the answer! Thanks for your help!! -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:53 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Can you view or log the errors that imap and the web authentication are getting? Try issuing a flush-hosts to see if the max_connect_errors was reached. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 11:47 AM Subject: RE: authentication error Nothing actually. I'm guessing I don't have enough logging turned on. The only thing my servername.err files shows is Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do do not.etc -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:04 PM To: Chip Bell; '[EMAIL PROTECTED] ' Subject: RE: authentication error What does the error log say? -Original Message- From: Chip Bell To: [EMAIL PROTECTED] Sent: 6/29/04 9:03 AM Subject: authentication error Hello, I run a toaster email server which is having trouble authenticating at times. Both methods of authentication, imap AND web for some reason just hang. I'm pretty new to MySql but I figure if both methods are trying to authenticate against the same table, the problem is there. I activated binary logging, what else can I do to find the issue? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: authentication error
What is the connection like between the two servers? Once you are logged in can you do a show processlist and see if anything is bottelnecking the database? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:05 PM Subject: RE: authentication error I ran the FLUSH HOSTS and it said 0 rows affected The authentication goes against the mysql table, which is where I'm guessing the errors would show. When I try to log in during the slow down of the server, it just hangs and hangs and finally will let me through. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:02 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error From the mysql monitor it is `FLUSH HOSTS;` . Does the email server not log failed connection attempts? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 11:55 AM Subject: RE: authentication error Ok, I tried to run flush-hosts from both # and inside mysql..nada. How can I do this exactly? Sorry about my lack of knowledge, I'm still green. I have my books though and am feverishly trying to find the answer! Thanks for your help!! -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:53 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Can you view or log the errors that imap and the web authentication are getting? Try issuing a flush-hosts to see if the max_connect_errors was reached. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 11:47 AM Subject: RE: authentication error Nothing actually. I'm guessing I don't have enough logging turned on. The only thing my servername.err files shows is Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do do not.etc -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:04 PM To: Chip Bell; '[EMAIL PROTECTED] ' Subject: RE: authentication error What does the error log say? -Original Message- From: Chip Bell To: [EMAIL PROTECTED] Sent: 6/29/04 9:03 AM Subject: authentication error Hello, I run a toaster email server which is having trouble authenticating at times. Both methods of authentication, imap AND web for some reason just hang. I'm pretty new to MySql but I figure if both methods are trying to authenticate against the same table, the problem is there. I activated binary logging, what else can I do to find the issue? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: authentication error
It's all on the same box. I'm familiar with the show processlist but I don't know what to look for. See what I mean lol? I know most of the commands, but not quite what I'm looking for. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:11 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error What is the connection like between the two servers? Once you are logged in can you do a show processlist and see if anything is bottelnecking the database? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:05 PM Subject: RE: authentication error I ran the FLUSH HOSTS and it said 0 rows affected The authentication goes against the mysql table, which is where I'm guessing the errors would show. When I try to log in during the slow down of the server, it just hangs and hangs and finally will let me through. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:02 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error From the mysql monitor it is `FLUSH HOSTS;` . Does the email server not log failed connection attempts? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 11:55 AM Subject: RE: authentication error Ok, I tried to run flush-hosts from both # and inside mysql..nada. How can I do this exactly? Sorry about my lack of knowledge, I'm still green. I have my books though and am feverishly trying to find the answer! Thanks for your help!! -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:53 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Can you view or log the errors that imap and the web authentication are getting? Try issuing a flush-hosts to see if the max_connect_errors was reached. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 11:47 AM Subject: RE: authentication error Nothing actually. I'm guessing I don't have enough logging turned on. The only thing my servername.err files shows is Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do do not.etc -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:04 PM To: Chip Bell; '[EMAIL PROTECTED] ' Subject: RE: authentication error What does the error log say? -Original Message- From: Chip Bell To: [EMAIL PROTECTED] Sent: 6/29/04 9:03 AM Subject: authentication error Hello, I run a toaster email server which is having trouble authenticating at times. Both methods of authentication, imap AND web for some reason just hang. I'm pretty new to MySql but I figure if both methods are trying to authenticate against the same table, the problem is there. I activated binary logging, what else can I do to find the issue? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: authentication error
Do you have an `execessive` numer of processes running? Do you have any processes that have been running for an `abnormal` length of time? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:12 PM Subject: RE: authentication error It's all on the same box. I'm familiar with the show processlist but I don't know what to look for. See what I mean lol? I know most of the commands, but not quite what I'm looking for. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:11 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error What is the connection like between the two servers? Once you are logged in can you do a show processlist and see if anything is bottelnecking the database? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:05 PM Subject: RE: authentication error I ran the FLUSH HOSTS and it said 0 rows affected The authentication goes against the mysql table, which is where I'm guessing the errors would show. When I try to log in during the slow down of the server, it just hangs and hangs and finally will let me through. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:02 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error From the mysql monitor it is `FLUSH HOSTS;` . Does the email server not log failed connection attempts? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 11:55 AM Subject: RE: authentication error Ok, I tried to run flush-hosts from both # and inside mysql..nada. How can I do this exactly? Sorry about my lack of knowledge, I'm still green. I have my books though and am feverishly trying to find the answer! Thanks for your help!! -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:53 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Can you view or log the errors that imap and the web authentication are getting? Try issuing a flush-hosts to see if the max_connect_errors was reached. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 11:47 AM Subject: RE: authentication error Nothing actually. I'm guessing I don't have enough logging turned on. The only thing my servername.err files shows is Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do do not.etc -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:04 PM To: Chip Bell; '[EMAIL PROTECTED] ' Subject: RE: authentication error What does the error log say? -Original Message- From: Chip Bell To: [EMAIL PROTECTED] Sent: 6/29/04 9:03 AM Subject: authentication error Hello, I run a toaster email server which is having trouble authenticating at times. Both methods of authentication, imap AND web for some reason just hang. I'm pretty new to MySql but I figure if both methods are trying to authenticate against the same table, the problem is there. I activated binary logging, what else can I do to find the issue? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: authentication error
Ok, we might be on to something. Right now, the server is running fine. I did a show processlist and got back 138 rows. There are times ranging from 0-14556. Granted, it's an email server so people are staying logged in... -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:31 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do you have an `execessive` numer of processes running? Do you have any processes that have been running for an `abnormal` length of time? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:12 PM Subject: RE: authentication error It's all on the same box. I'm familiar with the show processlist but I don't know what to look for. See what I mean lol? I know most of the commands, but not quite what I'm looking for. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:11 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error What is the connection like between the two servers? Once you are logged in can you do a show processlist and see if anything is bottelnecking the database? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:05 PM Subject: RE: authentication error I ran the FLUSH HOSTS and it said 0 rows affected The authentication goes against the mysql table, which is where I'm guessing the errors would show. When I try to log in during the slow down of the server, it just hangs and hangs and finally will let me through. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:02 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error From the mysql monitor it is `FLUSH HOSTS;` . Does the email server not log failed connection attempts? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 11:55 AM Subject: RE: authentication error Ok, I tried to run flush-hosts from both # and inside mysql..nada. How can I do this exactly? Sorry about my lack of knowledge, I'm still green. I have my books though and am feverishly trying to find the answer! Thanks for your help!! -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:53 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Can you view or log the errors that imap and the web authentication are getting? Try issuing a flush-hosts to see if the max_connect_errors was reached. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 11:47 AM Subject: RE: authentication error Nothing actually. I'm guessing I don't have enough logging turned on. The only thing my servername.err files shows is Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do do not.etc -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:04 PM To: Chip Bell; '[EMAIL PROTECTED] ' Subject: RE: authentication error What does the error log say? -Original Message- From: Chip Bell To: [EMAIL PROTECTED] Sent: 6/29/04 9:03 AM Subject: authentication error Hello, I run a toaster email server which is having trouble authenticating at times. Both methods of authentication, imap AND web for some reason just hang. I'm pretty new to MySql but I figure if both methods are trying to authenticate against the same table, the problem is there. I activated binary logging, what else can I do to find the issue? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using REGEXP
Hi,
select * from ytbl_development as t1
where (t1.txtDevPostCode REGEXP ^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1} in
#QuotedValueList(qryRadius.shortpostcode)#)
The above Where clause doesn't work , it just seems you can't use REGEXP this way
qryRadius.shortpostcode contains a list of short postcodes OX14 1,OX14 2 etc
whereas t1.postcode contains full postcodes OX14 5RA
(If qryRadius.shortpostcode wasn't a list I'd be able to use LIKE)
How can I write a Where clause that gets round this
MySql 4.018
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using REGEXP
Hi,
select * from ytbl_development as t1
where (t1.txtDevPostCode REGEXP ^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1} in
#QuotedValueList(qryRadius.shortpostcode)#)
The above Where clause doesn't work , it just seems you can't use REGEXP this way
qryRadius.shortpostcode contains a list of short postcodes OX14 1,OX14 2 etc
whereas t1.postcode contains full postcodes OX14 5RA
(If qryRadius.shortpostcode wasn't a list I'd be able to use LIKE)
How can I write a Where clause that gets round this
MySql 4.018
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: authentication error
The values in the `State` and `Command` fields of the queries are what you should be looking at. For example if you have a select statement that is running over an acceptable threshold you should look into that. If you have a query that is taking a `long` time to create a temp table , you should look at that one. You just need to identify what is acceptable and normal behaviour and correct where possible. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:43 PM Subject: RE: authentication error Ok, we might be on to something. Right now, the server is running fine. I did a show processlist and got back 138 rows. There are times ranging from 0-14556. Granted, it's an email server so people are staying logged in... -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:31 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do you have an `execessive` numer of processes running? Do you have any processes that have been running for an `abnormal` length of time? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:12 PM Subject: RE: authentication error It's all on the same box. I'm familiar with the show processlist but I don't know what to look for. See what I mean lol? I know most of the commands, but not quite what I'm looking for. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:11 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error What is the connection like between the two servers? Once you are logged in can you do a show processlist and see if anything is bottelnecking the database? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:05 PM Subject: RE: authentication error I ran the FLUSH HOSTS and it said 0 rows affected The authentication goes against the mysql table, which is where I'm guessing the errors would show. When I try to log in during the slow down of the server, it just hangs and hangs and finally will let me through. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:02 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error From the mysql monitor it is `FLUSH HOSTS;` . Does the email server not log failed connection attempts? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 11:55 AM Subject: RE: authentication error Ok, I tried to run flush-hosts from both # and inside mysql..nada. How can I do this exactly? Sorry about my lack of knowledge, I'm still green. I have my books though and am feverishly trying to find the answer! Thanks for your help!! -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:53 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Can you view or log the errors that imap and the web authentication are getting? Try issuing a flush-hosts to see if the max_connect_errors was reached. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 11:47 AM Subject: RE: authentication error Nothing actually. I'm guessing I don't have enough logging turned on. The only thing my servername.err files shows is Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do do not.etc -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:04 PM To: Chip Bell; '[EMAIL PROTECTED] ' Subject: RE: authentication error What does the error log say? -Original Message- From: Chip Bell To: [EMAIL PROTECTED] Sent: 6/29/04 9:03 AM Subject: authentication error Hello, I run a toaster email server which is having trouble authenticating at times. Both methods of authentication, imap AND web for some reason just hang. I'm pretty new to MySql but I figure if both methods are trying to authenticate against the same table, the problem is there. I activated binary logging, what else can I do to find the issue? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: authentication error
Is there anything I should set in my startup options to accommodate this? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:54 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error The values in the `State` and `Command` fields of the queries are what you should be looking at. For example if you have a select statement that is running over an acceptable threshold you should look into that. If you have a query that is taking a `long` time to create a temp table , you should look at that one. You just need to identify what is acceptable and normal behaviour and correct where possible. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:43 PM Subject: RE: authentication error Ok, we might be on to something. Right now, the server is running fine. I did a show processlist and got back 138 rows. There are times ranging from 0-14556. Granted, it's an email server so people are staying logged in... -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:31 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do you have an `execessive` numer of processes running? Do you have any processes that have been running for an `abnormal` length of time? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:12 PM Subject: RE: authentication error It's all on the same box. I'm familiar with the show processlist but I don't know what to look for. See what I mean lol? I know most of the commands, but not quite what I'm looking for. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:11 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error What is the connection like between the two servers? Once you are logged in can you do a show processlist and see if anything is bottelnecking the database? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:05 PM Subject: RE: authentication error I ran the FLUSH HOSTS and it said 0 rows affected The authentication goes against the mysql table, which is where I'm guessing the errors would show. When I try to log in during the slow down of the server, it just hangs and hangs and finally will let me through. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:02 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error From the mysql monitor it is `FLUSH HOSTS;` . Does the email server not log failed connection attempts? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 11:55 AM Subject: RE: authentication error Ok, I tried to run flush-hosts from both # and inside mysql..nada. How can I do this exactly? Sorry about my lack of knowledge, I'm still green. I have my books though and am feverishly trying to find the answer! Thanks for your help!! -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:53 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Can you view or log the errors that imap and the web authentication are getting? Try issuing a flush-hosts to see if the max_connect_errors was reached. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 11:47 AM Subject: RE: authentication error Nothing actually. I'm guessing I don't have enough logging turned on. The only thing my servername.err files shows is Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do do not.etc -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:04 PM To: Chip Bell; '[EMAIL PROTECTED] ' Subject: RE: authentication error What does the error log say? -Original Message- From: Chip Bell To: [EMAIL PROTECTED] Sent: 6/29/04 9:03 AM Subject: authentication error Hello, I run a toaster email server which is having trouble authenticating at times. Both methods of authentication, imap AND web for some reason just hang. I'm pretty new to MySql but I figure if both methods are trying to authenticate against the same table, the problem is there. I activated binary logging, what else can I do to find the issue? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server tuning
Hello everybody, I am setting up a server to do POP/SMTP authentication using vpopmail. I took a look at the variables at global my.cnf and wondered what would be the best values for things like key_buffer, read_buffer_size and table_cache. I searched the manual, but it does not go deep in this subject, or I was not capable of finding the right place. What I want to know is how can I calculate how much memory I should give to key_buffer before I start to give away too much memory. That's because I got only 1GB of RAM and I have about 34k domains in my database, averaging from 8 to 15 accounts each. In a normal situation, there are ~400 simultaneous clients. I was wanting to know how can I estimate the memory usage for this scenario, if exists some kind of formula to answer this, like: clients * total size of key fields used in query or if this is some thing that is clear in the manual (if so, my apologies, but I could not find). TIA, -- João Paulo Vasconcellos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Didn't find any fields in table, why?
I have two mysql servers as shown below: MySQL server A: version 3.23.58, database: mydb, table: mytable MySQL server B: version 5.0.0-alpha-standard-log I want the server B to have the exact same database and table as those on the server A. So, on server B, I manually created a database called 'mydb'. Then I copied three files mytable.frm, mytable.MYD, mytable.MYI from server A's /var/lib/mysql/mydb/ directory to server B's /var/lib/mysql/mydb directory. Now on server B, when running the command 'mysql -h localhost -u root -p mydb', I got the following messages back: Didn't find any fields in table 'mytable' By the way, I did restart the mysql server on server B after copying files. Does that mean version 3.* can not read version 5.* tables? How should I set up the same database and tables on another server just by copying files because I have a lot tables that need to be moved over? Thanks in advance for any help. Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL challenge
If you require a SQL-only solution, there may be one (I can imagine a 4- or 5-pass process that might get you the numbers you want). However, I agree with the other respondents to your message, it will not be as fast, as efficient, or as easy to write as one written in client-side code. IMHO, the ability to step through the records and compute statistics on a row-by-row basis is just too good a fit to justify spending the time to develop a batch solution. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine John Mistler [EMAIL PROTECTED]To: Roger Baklund [EMAIL PROTECTED], [EMAIL PROTECTED] phia.netcc: Fax to: 06/28/2004 04:17 Subject: Re: SQL challenge PM Wow, that was it! I changed the WHERE to (because I wasn't clear): . . . WHERE theDate BETWEEN SUBDATE(CURDATE(), INTERVAL 1 MONTH) AND CURDATE() . . . Now, I have just one more that I still am stumped by, if anyone (Roger or other) has a second: Given theTable with 2 columns: rowID (MEDIUMINT-but *NOT* AUTO_INCREMENT) and theDate (DATETIME) I need to find: 1. The average TIME ELAPSED between consecutive REPEATED instances of the rowID (GROUP BY rowID, I assume) between one month ago and now. - So, if there are 3 rows with rowID = 1 and 2 rows with rowID = 2, it would return the average time (total seconds, or HH:MM:SS) of ((time elapsed between row1 and row2 where rowID = 1, row 2 and row 3 where rowID = 1) AND (time elapsed between row1 and row2 where rowID = 2)). *Note that it would not use the time elapsed between row 1 and row 3 where rowID = 1) for the average calculation. 2. The average time elapsed between REPEATED instances of the rowID PER WEEK between one month ago and now. (This one might be as easy as using the WEEK() function as before . . .) Thanks, - John on 6/28/04 2:37 AM, Roger Baklund at [EMAIL PROTECTED] wrote: * John Mistler I have a difficult query for you that has me stumped. The table has two columns: rowID (MEDIUMINT-but *NOT* AUTO_INCREMENT) and theDate (DATETIME). I need it to find: 1. The COUNT of REPEATED instances of the rowID in the last month. - so if there are 5 rows with the same rowID in the last month, it would return 4 (I can only seem to get it to return 10 WHERE t1.rowID = t2.rowID AND t1.theDate t1.theDate) I'm not sure if I understand, but have you tried something like this: select rowID,COUNT(*) AS cnt from theTable where month(theDate) = month(curdate()) group by rowID having cnt1; If you by last month meant the last in the dataset, you could find the month by issuing: select @m:=month(max(theDate)) from theTable; You say you want the answer 4 when the count is 5...? You can subtract one from the count in the query: select rowID,COUNT(*)-1 AS cnt from theTable where month(theDate) = @m group by rowID having cnt0; 2. The AVERAGE number of REPEATED instances of the rowID per week (Monday 00:00:00 through Monday 00:00:00 one week later) in the last month. You want to group by week, you can get the week using the week() function. For weeks starting on monday, the second parameter should be 1. You want the average of the counts... try using a temporary table, something like this: create temporary table tmp1 select week(theDate,1) AS week, rowID, count(*)-1 AS cnt, from theTable where month(theDate) = @m group by week,rowID having cnt0; select week,avg(cnt) from tmp1 group by week; -- Roger -- 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: Memory to Memory INSERTS
I am proposing this as a hypothetical situation and I would like the full feedback of the group: Could Alejandro re-use the sections of the MySQL source code that handle replication and bin-logging to make his data capture application appear as a Master server and have his MySQL database act as its slave? Would that be faster than periodic batch files with LOAD DATA INFILE? Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Alejandro Heyworth [EMAIL PROTECTED]To: [EMAIL PROTECTED] ciples.com cc: Fax to: 06/25/2004 03:48 PM Subject: Memory to Memory INSERTS Hi! I'm looking for a better way to insert large numbers of rows from a client application that is sampling physical data in real-time. In our case, we are using a C double hipvalues[100] cyclical array to buffer our sampled values. We're currently creating large query strings similar to: INSERT DELAYED INTO hipjoint VALUES (hipvalues[0]),(hipvalues[1]),(hipvalues[2]),(hipvalues[3]),etc... We would like to continue to insert our values directly from our client app without first having to dump the data to a temp file and LOAD DATA INFILEing it periodically. Any ideas? Config values of interest: key_buffer_size = 4G bulk_insert_buffer_size = 1024M We are using MySQL 4.1.2. 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: How to turn rows into columns
What you are trying to create is called a pivot table or a cross-tab report. I recently walked someone else through this same process. Read through this thread and let me know if it helps you or not. You can also Google the groups for MySQL pivot table and find other examples and methods. http://groups.google.com/groups?hl=enlr=ie=UTF-8safe=offthreadm=cautac%243u8%241%40FreeBSD.csie.NCTU.edu.twrnum=1prev=/groups%3Fq%3DMySQL%2Bpivoting%2Btable%2Bsgreen%26ie%3DUTF-8%26hl%3Den Best Wishes, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Mauricio Pellegrini To: MySql List [EMAIL PROTECTED] [EMAIL PROTECTED]cc: com.ar Fax to: Subject: How to turn rows into columns 06/29/2004 04:35 PM Please respond to hrrg-inf Hi , Sorry to ask this question, but I'm in desperate need to acomplish this report, and don't even know it is possible to be done. The thing is I have to turn rows from one table into columns in other table. The first table looks like this T1 --- id cod --- 1bb 1ff 1gg 1kk 2rr --- An it should be converted to the following format where every cod value pertaining to T1 should be located into T2 under the specified column T2 --- id c1 c2 c3 c4 --- 1bb ff gg kk 2rr --- I know how to perform this under other languages but don't know how to do it with mysql's sql. I think someone else must have had the same requirement before, at least I hope so. Thanks in advance, Mauricio -- 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 REGEXP
A quick review of the REGEXP portion of the manual helped me to understand
what went wrong:
http://dev.mysql.com/doc/mysql/en/String_comparison_functions.html
REGEXP is a comparitor, not a function. It works like = or and the
result is a boolean value.
Were you trying to validate t1.postcode (to make sure it fit a certain
style of postal codes, as defined in your regular expression) AND make
sure it was one of a list of codes given to you by the user?
If you were, you needed to create your WHERE clause this way:
WHERE t1.postcode REGEXP
^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1}
AND t1.postcode in (insert your comma delimited list here)
The rule is: if you need to compare the same value against two conditions,
you have to make two distinct comparisons.
Cheers!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
zzapper
[EMAIL PROTECTED]To: [EMAIL PROTECTED]
cc:
Sent by: newsFax to:
[EMAIL PROTECTED]Subject: Using REGEXP
rg
06/29/2004 09:25
AM
Hi,
select * from ytbl_development as t1
where (t1.txtDevPostCode REGEXP
^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1} in
#QuotedValueList(qryRadius.shortpostcode)#)
The above Where clause doesn't work , it just seems you can't use REGEXP
this way
qryRadius.shortpostcode contains a list of short postcodes OX14 1,OX14 2
etc
whereas t1.postcode contains full postcodes OX14 5RA
(If qryRadius.shortpostcode wasn't a list I'd be able to use LIKE)
How can I write a Where clause that gets round this
MySql 4.018
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
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: authentication error
You should just become familiar with your data and the queries that are sent to the database. You could turn on the slow query log and after a few days or hours or whatever see what queries are logged. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 1:03 PM Subject: RE: authentication error Is there anything I should set in my startup options to accommodate this? -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:54 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error The values in the `State` and `Command` fields of the queries are what you should be looking at. For example if you have a select statement that is running over an acceptable threshold you should look into that. If you have a query that is taking a `long` time to create a temp table , you should look at that one. You just need to identify what is acceptable and normal behaviour and correct where possible. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:43 PM Subject: RE: authentication error Ok, we might be on to something. Right now, the server is running fine. I did a show processlist and got back 138 rows. There are times ranging from 0-14556. Granted, it's an email server so people are staying logged in... -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:31 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Do you have an `execessive` numer of processes running? Do you have any processes that have been running for an `abnormal` length of time? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:12 PM Subject: RE: authentication error It's all on the same box. I'm familiar with the show processlist but I don't know what to look for. See what I mean lol? I know most of the commands, but not quite what I'm looking for. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:11 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error What is the connection like between the two servers? Once you are logged in can you do a show processlist and see if anything is bottelnecking the database? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 12:05 PM Subject: RE: authentication error I ran the FLUSH HOSTS and it said 0 rows affected The authentication goes against the mysql table, which is where I'm guessing the errors would show. When I try to log in during the slow down of the server, it just hangs and hangs and finally will let me through. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:02 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error From the mysql monitor it is `FLUSH HOSTS;` . Does the email server not log failed connection attempts? -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 11:55 AM Subject: RE: authentication error Ok, I tried to run flush-hosts from both # and inside mysql..nada. How can I do this exactly? Sorry about my lack of knowledge, I'm still green. I have my books though and am feverishly trying to find the answer! Thanks for your help!! -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:53 PM To: Chip Bell; Victor Pendleton; '[EMAIL PROTECTED] ' Subject: RE: authentication error Can you view or log the errors that imap and the web authentication are getting? Try issuing a flush-hosts to see if the max_connect_errors was reached. -Original Message- From: Chip Bell To: Victor Pendleton; [EMAIL PROTECTED] Sent: 6/29/04 11:47 AM Subject: RE: authentication error Nothing actually. I'm guessing I don't have enough logging turned on. The only thing my servername.err files shows is Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do do not.etc -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 12:04 PM To: Chip Bell; '[EMAIL PROTECTED] ' Subject: RE: authentication error What does the error log say? -Original Message- From: Chip Bell To: [EMAIL PROTECTED] Sent: 6/29/04 9:03 AM Subject: authentication error Hello, I run a toaster email server which is having trouble authenticating at times. Both methods of authentication, imap AND web for some reason just hang. I'm pretty new to MySql but I figure if both methods are trying to authenticate against the same table, the problem is there. I activated binary logging, what else can I do to find the issue? Thanks -- MySQL General Mailing List For list
Re: Using REGEXP
zzapper:
I could be reading it wrong, but it looks like you're looking for the
result of your REGEXP in a list. REGEXP returns only a 0 or 1, not the
expression resulting from performing a REGEXP.
Wes
On Jun 29, 2004, at 9:25 AM, zzapper wrote:
Hi,
select * from ytbl_development as t1
where (t1.txtDevPostCode REGEXP
^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1} in
#QuotedValueList(qryRadius.shortpostcode)#)
The above Where clause doesn't work , it just seems you can't use
REGEXP this way
qryRadius.shortpostcode contains a list of short postcodes OX14
1,OX14 2 etc
whereas t1.postcode contains full postcodes OX14 5RA
(If qryRadius.shortpostcode wasn't a list I'd be able to use LIKE)
How can I write a Where clause that gets round this
MySql 4.018
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
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: Didn't find any fields in table, why?
Bing Du wrote: I have two mysql servers as shown below: MySQL server A: version 3.23.58, database: mydb, table: mytable MySQL server B: version 5.0.0-alpha-standard-log I want the server B to have the exact same database and table as those on the server A. So, on server B, I manually created a database called 'mydb'. Then I copied three files mytable.frm, mytable.MYD, mytable.MYI from server A's /var/lib/mysql/mydb/ directory to server B's /var/lib/mysql/mydb directory. Make sure mysql owns mydb and all of its files. Now on server B, when running the command 'mysql -h localhost -u root -p mydb', I got the following messages back: Didn't find any fields in table 'mytable' By the way, I did restart the mysql server on server B after copying files. Does that mean version 3.* can not read version 5.* tables? How should I set up the same database and tables on another server just by copying files because I have a lot tables that need to be moved over? Thanks in advance for any help. Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to turn rows into columns
You might also want to read these articles: http://www.onlamp.com/lpt/a/4397 and http://dev.mysql.com/tech-resources/articles/wizard/index.html In addition, there's a Perl module - DBIx::SQLCrosstab - that may help. It's available from CPAN at http://search.cpan.org/~gmax/DBIx-SQLCrosstab-1.17. Hope this helps, Jack -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 2:34 PM To: [EMAIL PROTECTED] Cc: MySql List Subject: Re: How to turn rows into columns What you are trying to create is called a pivot table or a cross-tab report. I recently walked someone else through this same process. Read through this thread and let me know if it helps you or not. You can also Google the groups for MySQL pivot table and find other examples and methods. http://groups.google.com/groups?hl=enlr=ie=UTF-8safe=offthreadm=cautac%2 43u8%241%40FreeBSD.csie.NCTU.edu.twrnum=1prev=/groups%3Fq%3DMySQL%2Bpivoti ng%2Btable%2Bsgreen%26ie%3DUTF-8%26hl%3Den Best Wishes, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Mauricio Pellegrini To: MySql List [EMAIL PROTECTED] [EMAIL PROTECTED]cc: com.ar Fax to: Subject: How to turn rows into columns 06/29/2004 04:35 PM Please respond to hrrg-inf Hi , Sorry to ask this question, but I'm in desperate need to acomplish this report, and don't even know it is possible to be done. The thing is I have to turn rows from one table into columns in other table. The first table looks like this T1 --- id cod --- 1bb 1ff 1gg 1kk 2rr --- An it should be converted to the following format where every cod value pertaining to T1 should be located into T2 under the specified column T2 --- id c1 c2 c3 c4 --- 1bb ff gg kk 2rr --- I know how to perform this under other languages but don't know how to do it with mysql's sql. I think someone else must have had the same requirement before, at least I hope so. Thanks in advance, Mauricio -- 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: Didn't find any fields in table, why?
Yes, both user and group ownership of the mydb directory and all its sub directories/files are mysql. I also tried on server A 'mysqldump mydb /tmp/mydb.dump'. Then copy /tmp/mydb.dump to server B. On server B, I did 'mysql mydb /tmp/mydb.dump'. Then I got ERROR 1064 at line 11: You have an error in your SQL syntax near 'ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List the names and locations of fi' at line 12 Line 12 the above error refers to exists in the following CREATE TABLE statement: 1. CREATE TABLE mytable ( 2. attachment_id mediumint(5) NOT NULL auto_increment, 3. task_id mediumint(10) NOT NULL default '0', 4. orig_name varchar(100) NOT NULL default '', 5. file_name varchar(30) NOT NULL default '', 6. file_desc varchar(100) NOT NULL default '', 7. file_type varchar(50) NOT NULL default '', 8. file_size mediumint(20) NOT NULL default '0', 9. added_by mediumint(3) NOT NULL default '0', 10. date_added varchar(12) NOT NULL default '', 11. PRIMARY KEY (attachment_id) 12. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List the names and locations of files attached to tasks'; I did a test, seems after removing 'DEFAULT CHARSET=latin1' from line 12, the table could be imported fine. Is there some syntax of version 3.* that is not supported in version 5.*? Like I said, there are a lot tables that need to be transferred, manually editting the dump file is not acceptable. Any workaround? Bing Bing Du wrote: I have two mysql servers as shown below: MySQL server A: version 3.23.58, database: mydb, table: mytable MySQL server B: version 5.0.0-alpha-standard-log I want the server B to have the exact same database and table as those on the server A. So, on server B, I manually created a database called 'mydb'. Then I copied three files mytable.frm, mytable.MYD, mytable.MYI from server A's /var/lib/mysql/mydb/ directory to server B's /var/lib/mysql/mydb directory. Make sure mysql owns mydb and all of its files. Now on server B, when running the command 'mysql -h localhost -u root -p mydb', I got the following messages back: Didn't find any fields in table 'mytable' By the way, I did restart the mysql server on server B after copying files. Does that mean version 3.* can not read version 5.* tables? How should I set up the same database and tables on another server just by copying files because I have a lot tables that need to be moved over? Thanks in advance for any help. Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using REGEXP
On Tue, 29 Jun 2004 15:13:10 -0400, wrote:
zzapper:
I could be reading it wrong, but it looks like you're looking for the
result of your REGEXP in a list. REGEXP returns only a 0 or 1, not the
expression resulting from performing a REGEXP.
Wes
On Jun 29, 2004, at 9:25 AM, zzapper wrote:
Hi,
select * from ytbl_development as t1
where (t1.txtDevPostCode REGEXP
^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1} in
#QuotedValueList(qryRadius.shortpostcode)#)
The above Where clause doesn't work , it just seems you can't use
REGEXP this way
qryRadius.shortpostcode contains a list of short postcodes OX14
1,OX14 2 etc
whereas t1.postcode contains full postcodes OX14 5RA
(If qryRadius.shortpostcode wasn't a list I'd be able to use LIKE)
How can I write a Where clause that gets round this
Shawn,
Your solution doesn't work unfortunately because I need to operate on t1.postcode
before making the
comparison (IT'S USUALLY the OTHER WAY ROUND) , I think this must be a generic
problem, so I think
an eventual solution will be interesting
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using REGEXP
Then you need to tell us what operation needs to be performed on t1.postcode
before making the comparison. That is, describe what you want, rather than
what didn't work.
Michael
zzapper wrote:
On Tue, 29 Jun 2004 15:13:10 -0400, wrote:
zzapper:
I could be reading it wrong, but it looks like you're looking for the
result of your REGEXP in a list. REGEXP returns only a 0 or 1, not the
expression resulting from performing a REGEXP.
Wes
On Jun 29, 2004, at 9:25 AM, zzapper wrote:
Hi,
select * from ytbl_development as t1
where (t1.txtDevPostCode REGEXP
^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1} in
#QuotedValueList(qryRadius.shortpostcode)#)
The above Where clause doesn't work , it just seems you can't use
REGEXP this way
qryRadius.shortpostcode contains a list of short postcodes OX14
1,OX14 2 etc
whereas t1.postcode contains full postcodes OX14 5RA
(If qryRadius.shortpostcode wasn't a list I'd be able to use LIKE)
How can I write a Where clause that gets round this
Shawn,
Your solution doesn't work unfortunately because I need to operate on t1.postcode
before making the
comparison (IT'S USUALLY the OTHER WAY ROUND) , I think this must be a generic
problem, so I think
an eventual solution will be interesting
zzapper (vim, cygwin, wiki zsh)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Didn't find any fields in table, why?
ENGINE syntax is availiable in 4.1.x and later versions. -Original Message- From: Bing Du To: [EMAIL PROTECTED] Sent: 6/29/04 2:30 PM Subject: Re: Didn't find any fields in table, why? Yes, both user and group ownership of the mydb directory and all its sub directories/files are mysql. I also tried on server A 'mysqldump mydb /tmp/mydb.dump'. Then copy /tmp/mydb.dump to server B. On server B, I did 'mysql mydb /tmp/mydb.dump'. Then I got ERROR 1064 at line 11: You have an error in your SQL syntax near 'ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List the names and locations of fi' at line 12 Line 12 the above error refers to exists in the following CREATE TABLE statement: 1. CREATE TABLE mytable ( 2. attachment_id mediumint(5) NOT NULL auto_increment, 3. task_id mediumint(10) NOT NULL default '0', 4. orig_name varchar(100) NOT NULL default '', 5. file_name varchar(30) NOT NULL default '', 6. file_desc varchar(100) NOT NULL default '', 7. file_type varchar(50) NOT NULL default '', 8. file_size mediumint(20) NOT NULL default '0', 9. added_by mediumint(3) NOT NULL default '0', 10. date_added varchar(12) NOT NULL default '', 11. PRIMARY KEY (attachment_id) 12. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List the names and locations of files attached to tasks'; I did a test, seems after removing 'DEFAULT CHARSET=latin1' from line 12, the table could be imported fine. Is there some syntax of version 3.* that is not supported in version 5.*? Like I said, there are a lot tables that need to be transferred, manually editting the dump file is not acceptable. Any workaround? Bing Bing Du wrote: I have two mysql servers as shown below: MySQL server A: version 3.23.58, database: mydb, table: mytable MySQL server B: version 5.0.0-alpha-standard-log I want the server B to have the exact same database and table as those on the server A. So, on server B, I manually created a database called 'mydb'. Then I copied three files mytable.frm, mytable.MYD, mytable.MYI from server A's /var/lib/mysql/mydb/ directory to server B's /var/lib/mysql/mydb directory. Make sure mysql owns mydb and all of its files. Now on server B, when running the command 'mysql -h localhost -u root -p mydb', I got the following messages back: Didn't find any fields in table 'mytable' By the way, I did restart the mysql server on server B after copying files. Does that mean version 3.* can not read version 5.* tables? How should I set up the same database and tables on another server just by copying files because I have a lot tables that need to be moved over? Thanks in advance for any help. Bing -- 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: Server tuning
max_memory is roughly equivalent to == key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections -Original Message- From: João Paulo Vasconcellos To: Mysql-general Mailing List Sent: 6/29/04 1:09 PM Subject: Server tuning Hello everybody, I am setting up a server to do POP/SMTP authentication using vpopmail. I took a look at the variables at global my.cnf and wondered what would be the best values for things like key_buffer, read_buffer_size and table_cache. I searched the manual, but it does not go deep in this subject, or I was not capable of finding the right place. What I want to know is how can I calculate how much memory I should give to key_buffer before I start to give away too much memory. That's because I got only 1GB of RAM and I have about 34k domains in my database, averaging from 8 to 15 accounts each. In a normal situation, there are ~400 simultaneous clients. I was wanting to know how can I estimate the memory usage for this scenario, if exists some kind of formula to answer this, like: clients * total size of key fields used in query or if this is some thing that is clear in the manual (if so, my apologies, but I could not find). TIA, -- João Paulo Vasconcellos -- 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: Didn't find any fields in table, why?
Yup, that's it. I just checked out the mysql online documents. mysqldump should have an option --compatible=name. I tried --compatible=mysql323 with mysqldump on my version 5.0 server, but got mysqldump: ERROR: unknown variable 'compatible=mysql323'. 'mysqldump --help' does not show --compatible as a valid option. 'mysqldump -V' shows mysqldump Ver 9.10 Distrib 4.0.18, for pc-linux (i686). I also looked through mysqldump help but did not find any option that could be used to produce a backward compatible dump. Any ideas? Bing ENGINE syntax is availiable in 4.1.x and later versions. -Original Message- From: Bing Du To: [EMAIL PROTECTED] Sent: 6/29/04 2:30 PM Subject: Re: Didn't find any fields in table, why? Yes, both user and group ownership of the mydb directory and all its sub directories/files are mysql. I also tried on server A 'mysqldump mydb /tmp/mydb.dump'. Then copy /tmp/mydb.dump to server B. On server B, I did 'mysql mydb /tmp/mydb.dump'. Then I got ERROR 1064 at line 11: You have an error in your SQL syntax near 'ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List the names and locations of fi' at line 12 Line 12 the above error refers to exists in the following CREATE TABLE statement: 1. CREATE TABLE mytable ( 2. attachment_id mediumint(5) NOT NULL auto_increment, 3. task_id mediumint(10) NOT NULL default '0', 4. orig_name varchar(100) NOT NULL default '', 5. file_name varchar(30) NOT NULL default '', 6. file_desc varchar(100) NOT NULL default '', 7. file_type varchar(50) NOT NULL default '', 8. file_size mediumint(20) NOT NULL default '0', 9. added_by mediumint(3) NOT NULL default '0', 10. date_added varchar(12) NOT NULL default '', 11. PRIMARY KEY (attachment_id) 12. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List the names and locations of files attached to tasks'; I did a test, seems after removing 'DEFAULT CHARSET=latin1' from line 12, the table could be imported fine. Is there some syntax of version 3.* that is not supported in version 5.*? Like I said, there are a lot tables that need to be transferred, manually editting the dump file is not acceptable. Any workaround? Bing Bing Du wrote: I have two mysql servers as shown below: MySQL server A: version 3.23.58, database: mydb, table: mytable MySQL server B: version 5.0.0-alpha-standard-log I want the server B to have the exact same database and table as those on the server A. So, on server B, I manually created a database called 'mydb'. Then I copied three files mytable.frm, mytable.MYD, mytable.MYI from server A's /var/lib/mysql/mydb/ directory to server B's /var/lib/mysql/mydb directory. Make sure mysql owns mydb and all of its files. Now on server B, when running the command 'mysql -h localhost -u root -p mydb', I got the following messages back: Didn't find any fields in table 'mytable' By the way, I did restart the mysql server on server B after copying files. Does that mean version 3.* can not read version 5.* tables? How should I set up the same database and tables on another server just by copying files because I have a lot tables that need to be moved over? Thanks in advance for any help. Bing -- 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]
Using Access as client all fields are marked #Deleted
Hi all, My setup is windows 2000, Access 2000 and Mysql built from source on Debian. Some tables can be seen but all new entries are marked #Deleted. If I close the table in Access and re-open it, its looks fine. Others have every field marked #Deleted and nothing I do makes those tables readable. HOWEVER, queries that rely on the data work accurately but show the results as #Deleted. I've installed myodbc, the latest MDAC and Jet Service packs. Is there some test or diagnostic procedure to establish what isn't working? I think I've followed all the documented steps but the lack of diagnostics makes this very difficult. Thanks in advance, Daniel Eliav Sharvit _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Didn't find any fields in table, why?
Are you migrating from 5.0.x to 3.23.x? Do you have any innodb or dbd tables? -Original Message- From: Bing Du To: '[EMAIL PROTECTED] ' Sent: 6/29/04 3:59 PM Subject: RE: Didn't find any fields in table, why? Yup, that's it. I just checked out the mysql online documents. mysqldump should have an option --compatible=name. I tried --compatible=mysql323 with mysqldump on my version 5.0 server, but got mysqldump: ERROR: unknown variable 'compatible=mysql323'. 'mysqldump --help' does not show --compatible as a valid option. 'mysqldump -V' shows mysqldump Ver 9.10 Distrib 4.0.18, for pc-linux (i686). I also looked through mysqldump help but did not find any option that could be used to produce a backward compatible dump. Any ideas? Bing ENGINE syntax is availiable in 4.1.x and later versions. -Original Message- From: Bing Du To: [EMAIL PROTECTED] Sent: 6/29/04 2:30 PM Subject: Re: Didn't find any fields in table, why? Yes, both user and group ownership of the mydb directory and all its sub directories/files are mysql. I also tried on server A 'mysqldump mydb /tmp/mydb.dump'. Then copy /tmp/mydb.dump to server B. On server B, I did 'mysql mydb /tmp/mydb.dump'. Then I got ERROR 1064 at line 11: You have an error in your SQL syntax near 'ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List the names and locations of fi' at line 12 Line 12 the above error refers to exists in the following CREATE TABLE statement: 1. CREATE TABLE mytable ( 2. attachment_id mediumint(5) NOT NULL auto_increment, 3. task_id mediumint(10) NOT NULL default '0', 4. orig_name varchar(100) NOT NULL default '', 5. file_name varchar(30) NOT NULL default '', 6. file_desc varchar(100) NOT NULL default '', 7. file_type varchar(50) NOT NULL default '', 8. file_size mediumint(20) NOT NULL default '0', 9. added_by mediumint(3) NOT NULL default '0', 10. date_added varchar(12) NOT NULL default '', 11. PRIMARY KEY (attachment_id) 12. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List the names and locations of files attached to tasks'; I did a test, seems after removing 'DEFAULT CHARSET=latin1' from line 12, the table could be imported fine. Is there some syntax of version 3.* that is not supported in version 5.*? Like I said, there are a lot tables that need to be transferred, manually editting the dump file is not acceptable. Any workaround? Bing Bing Du wrote: I have two mysql servers as shown below: MySQL server A: version 3.23.58, database: mydb, table: mytable MySQL server B: version 5.0.0-alpha-standard-log I want the server B to have the exact same database and table as those on the server A. So, on server B, I manually created a database called 'mydb'. Then I copied three files mytable.frm, mytable.MYD, mytable.MYI from server A's /var/lib/mysql/mydb/ directory to server B's /var/lib/mysql/mydb directory. Make sure mysql owns mydb and all of its files. Now on server B, when running the command 'mysql -h localhost -u root -p mydb', I got the following messages back: Didn't find any fields in table 'mytable' By the way, I did restart the mysql server on server B after copying files. Does that mean version 3.* can not read version 5.* tables? How should I set up the same database and tables on another server just by copying files because I have a lot tables that need to be moved over? Thanks in advance for any help. Bing -- 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: Didn't find any fields in table, why?
Yes, migrate from 5.0.x to 3.23.x. No innodb and dbd tables. Bing Are you migrating from 5.0.x to 3.23.x? Do you have any innodb or dbd tables? -Original Message- From: Bing Du To: '[EMAIL PROTECTED] ' Sent: 6/29/04 3:59 PM Subject: RE: Didn't find any fields in table, why? Yup, that's it. I just checked out the mysql online documents. mysqldump should have an option --compatible=name. I tried --compatible=mysql323 with mysqldump on my version 5.0 server, but got mysqldump: ERROR: unknown variable 'compatible=mysql323'. 'mysqldump --help' does not show --compatible as a valid option. 'mysqldump -V' shows mysqldump Ver 9.10 Distrib 4.0.18, for pc-linux (i686). I also looked through mysqldump help but did not find any option that could be used to produce a backward compatible dump. Any ideas? Bing ENGINE syntax is availiable in 4.1.x and later versions. -Original Message- From: Bing Du To: [EMAIL PROTECTED] Sent: 6/29/04 2:30 PM Subject: Re: Didn't find any fields in table, why? Yes, both user and group ownership of the mydb directory and all its sub directories/files are mysql. I also tried on server A 'mysqldump mydb /tmp/mydb.dump'. Then copy /tmp/mydb.dump to server B. On server B, I did 'mysql mydb /tmp/mydb.dump'. Then I got ERROR 1064 at line 11: You have an error in your SQL syntax near 'ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List the names and locations of fi' at line 12 Line 12 the above error refers to exists in the following CREATE TABLE statement: 1. CREATE TABLE mytable ( 2. attachment_id mediumint(5) NOT NULL auto_increment, 3. task_id mediumint(10) NOT NULL default '0', 4. orig_name varchar(100) NOT NULL default '', 5. file_name varchar(30) NOT NULL default '', 6. file_desc varchar(100) NOT NULL default '', 7. file_type varchar(50) NOT NULL default '', 8. file_size mediumint(20) NOT NULL default '0', 9. added_by mediumint(3) NOT NULL default '0', 10. date_added varchar(12) NOT NULL default '', 11. PRIMARY KEY (attachment_id) 12. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List the names and locations of files attached to tasks'; I did a test, seems after removing 'DEFAULT CHARSET=latin1' from line 12, the table could be imported fine. Is there some syntax of version 3.* that is not supported in version 5.*? Like I said, there are a lot tables that need to be transferred, manually editting the dump file is not acceptable. Any workaround? Bing Bing Du wrote: I have two mysql servers as shown below: MySQL server A: version 3.23.58, database: mydb, table: mytable MySQL server B: version 5.0.0-alpha-standard-log I want the server B to have the exact same database and table as those on the server A. So, on server B, I manually created a database called 'mydb'. Then I copied three files mytable.frm, mytable.MYD, mytable.MYI from server A's /var/lib/mysql/mydb/ directory to server B's /var/lib/mysql/mydb directory. Make sure mysql owns mydb and all of its files. Now on server B, when running the command 'mysql -h localhost -u root -p mydb', I got the following messages back: Didn't find any fields in table 'mytable' By the way, I did restart the mysql server on server B after copying files. Does that mean version 3.* can not read version 5.* tables? How should I set up the same database and tables on another server just by copying files because I have a lot tables that need to be moved over? Thanks in advance for any help. Bing -- 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: Using Access as client all fields are marked #Deleted
There is a section in the MySQL manual titled `Programs Known to Work with MyODBC' and section called `Access`. http://dev.mysql.com/doc/mysql/en/MyODBC_clients.html Look at this section and let us know if you are still having issues. -Original Message- From: Daniel Eliav Sharvit To: [EMAIL PROTECTED] Sent: 6/29/04 4:03 PM Subject: Using Access as client all fields are marked #Deleted Hi all, My setup is windows 2000, Access 2000 and Mysql built from source on Debian. Some tables can be seen but all new entries are marked #Deleted. If I close the table in Access and re-open it, its looks fine. Others have every field marked #Deleted and nothing I do makes those tables readable. HOWEVER, queries that rely on the data work accurately but show the results as #Deleted. I've installed myodbc, the latest MDAC and Jet Service packs. Is there some test or diagnostic procedure to establish what isn't working? I think I've followed all the documented steps but the lack of diagnostics makes this very difficult. Thanks in advance, Daniel Eliav Sharvit _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- 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: Didn't find any fields in table, why?
I have not tried 5.0.x to 3.23.x but give this a try: Flush the tables with read lock or flush the tables and shutdown the database tar.gz the files Move the files to the the new server Untar/unzip the files Start up both databases -Original Message- From: Bing Du To: ''[EMAIL PROTECTED] ' ' Sent: 6/29/04 4:13 PM Subject: RE: Didn't find any fields in table, why? Yes, migrate from 5.0.x to 3.23.x. No innodb and dbd tables. Bing Are you migrating from 5.0.x to 3.23.x? Do you have any innodb or dbd tables? -Original Message- From: Bing Du To: '[EMAIL PROTECTED] ' Sent: 6/29/04 3:59 PM Subject: RE: Didn't find any fields in table, why? Yup, that's it. I just checked out the mysql online documents. mysqldump should have an option --compatible=name. I tried --compatible=mysql323 with mysqldump on my version 5.0 server, but got mysqldump: ERROR: unknown variable 'compatible=mysql323'. 'mysqldump --help' does not show --compatible as a valid option. 'mysqldump -V' shows mysqldump Ver 9.10 Distrib 4.0.18, for pc-linux (i686). I also looked through mysqldump help but did not find any option that could be used to produce a backward compatible dump. Any ideas? Bing ENGINE syntax is availiable in 4.1.x and later versions. -Original Message- From: Bing Du To: [EMAIL PROTECTED] Sent: 6/29/04 2:30 PM Subject: Re: Didn't find any fields in table, why? Yes, both user and group ownership of the mydb directory and all its sub directories/files are mysql. I also tried on server A 'mysqldump mydb /tmp/mydb.dump'. Then copy /tmp/mydb.dump to server B. On server B, I did 'mysql mydb /tmp/mydb.dump'. Then I got ERROR 1064 at line 11: You have an error in your SQL syntax near 'ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List the names and locations of fi' at line 12 Line 12 the above error refers to exists in the following CREATE TABLE statement: 1. CREATE TABLE mytable ( 2. attachment_id mediumint(5) NOT NULL auto_increment, 3. task_id mediumint(10) NOT NULL default '0', 4. orig_name varchar(100) NOT NULL default '', 5. file_name varchar(30) NOT NULL default '', 6. file_desc varchar(100) NOT NULL default '', 7. file_type varchar(50) NOT NULL default '', 8. file_size mediumint(20) NOT NULL default '0', 9. added_by mediumint(3) NOT NULL default '0', 10. date_added varchar(12) NOT NULL default '', 11. PRIMARY KEY (attachment_id) 12. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List the names and locations of files attached to tasks'; I did a test, seems after removing 'DEFAULT CHARSET=latin1' from line 12, the table could be imported fine. Is there some syntax of version 3.* that is not supported in version 5.*? Like I said, there are a lot tables that need to be transferred, manually editting the dump file is not acceptable. Any workaround? Bing Bing Du wrote: I have two mysql servers as shown below: MySQL server A: version 3.23.58, database: mydb, table: mytable MySQL server B: version 5.0.0-alpha-standard-log I want the server B to have the exact same database and table as those on the server A. So, on server B, I manually created a database called 'mydb'. Then I copied three files mytable.frm, mytable.MYD, mytable.MYI from server A's /var/lib/mysql/mydb/ directory to server B's /var/lib/mysql/mydb directory. Make sure mysql owns mydb and all of its files. Now on server B, when running the command 'mysql -h localhost -u root -p mydb', I got the following messages back: Didn't find any fields in table 'mytable' By the way, I did restart the mysql server on server B after copying files. Does that mean version 3.* can not read version 5.* tables? How should I set up the same database and tables on another server just by copying files because I have a lot tables that need to be moved over? Thanks in advance for any help. Bing -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Didn't find any fields in table, why?
Well, then I'll just go back to my original problem if you have read through this thread from the beginning. Because copying files did not work, I turned to try mysqldump. Mysqldump does not work for me either. So there really isn't a way to create a dump on 5.0.x that's compatible with 3.23.x? Bing I have not tried 5.0.x to 3.23.x but give this a try: Flush the tables with read lock or flush the tables and shutdown the database tar.gz the files Move the files to the the new server Untar/unzip the files Start up both databases -Original Message- From: Bing Du To: ''[EMAIL PROTECTED] ' ' Sent: 6/29/04 4:13 PM Subject: RE: Didn't find any fields in table, why? Yes, migrate from 5.0.x to 3.23.x. No innodb and dbd tables. Bing Are you migrating from 5.0.x to 3.23.x? Do you have any innodb or dbd tables? -Original Message- From: Bing Du To: '[EMAIL PROTECTED] ' Sent: 6/29/04 3:59 PM Subject: RE: Didn't find any fields in table, why? Yup, that's it. I just checked out the mysql online documents. mysqldump should have an option --compatible=name. I tried --compatible=mysql323 with mysqldump on my version 5.0 server, but got mysqldump: ERROR: unknown variable 'compatible=mysql323'. 'mysqldump --help' does not show --compatible as a valid option. 'mysqldump -V' shows mysqldump Ver 9.10 Distrib 4.0.18, for pc-linux (i686). I also looked through mysqldump help but did not find any option that could be used to produce a backward compatible dump. Any ideas? Bing ENGINE syntax is availiable in 4.1.x and later versions. -Original Message- From: Bing Du To: [EMAIL PROTECTED] Sent: 6/29/04 2:30 PM Subject: Re: Didn't find any fields in table, why? Yes, both user and group ownership of the mydb directory and all its sub directories/files are mysql. I also tried on server A 'mysqldump mydb /tmp/mydb.dump'. Then copy /tmp/mydb.dump to server B. On server B, I did 'mysql mydb /tmp/mydb.dump'. Then I got ERROR 1064 at line 11: You have an error in your SQL syntax near 'ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List the names and locations of fi' at line 12 Line 12 the above error refers to exists in the following CREATE TABLE statement: 1. CREATE TABLE mytable ( 2. attachment_id mediumint(5) NOT NULL auto_increment, 3. task_id mediumint(10) NOT NULL default '0', 4. orig_name varchar(100) NOT NULL default '', 5. file_name varchar(30) NOT NULL default '', 6. file_desc varchar(100) NOT NULL default '', 7. file_type varchar(50) NOT NULL default '', 8. file_size mediumint(20) NOT NULL default '0', 9. added_by mediumint(3) NOT NULL default '0', 10. date_added varchar(12) NOT NULL default '', 11. PRIMARY KEY (attachment_id) 12. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='List the names and locations of files attached to tasks'; I did a test, seems after removing 'DEFAULT CHARSET=latin1' from line 12, the table could be imported fine. Is there some syntax of version 3.* that is not supported in version 5.*? Like I said, there are a lot tables that need to be transferred, manually editting the dump file is not acceptable. Any workaround? Bing Bing Du wrote: I have two mysql servers as shown below: MySQL server A: version 3.23.58, database: mydb, table: mytable MySQL server B: version 5.0.0-alpha-standard-log I want the server B to have the exact same database and table as those on the server A. So, on server B, I manually created a database called 'mydb'. Then I copied three files mytable.frm, mytable.MYD, mytable.MYI from server A's /var/lib/mysql/mydb/ directory to server B's /var/lib/mysql/mydb directory. Make sure mysql owns mydb and all of its files. Now on server B, when running the command 'mysql -h localhost -u root -p mydb', I got the following messages back: Didn't find any fields in table 'mytable' By the way, I did restart the mysql server on server B after copying files. Does that mean version 3.* can not read version 5.* tables? How should I set up the same database and tables on another server just by copying files because I have a lot tables that need to be moved over? Thanks in advance for any help. Bing -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where is the utf8_general_ci collation in sources?
Much has changed with Unicode in MySQL 4.1.3. So you should be sure to look at the newest bitkeeper source. And be sure to look at strings/ctype-uca.c. This is the new implementation of the Unicode Collation Algorithm. best, Jeremy March -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Resources
Hi everyone. As a newbie Id just like to say that Im enjoying delving into MySQL and its a fresh change from ASP and other MS stuff Ive been using. You never know, I might even start running Linux. But hey one step at a time eh As I am currently learning I am digging into books but wondered if there were any good Walkthroughs out there. Im looking for some very basic input/output commands and rather than beg, borrow and steal the code I wanted to spend some time really getting to understand the syntax. Any suggestions? Michael Mason Business Support Services Arras People Tel: 01706 342310 Mobile: 07793 782287 Fax: 01706 642754 Member of the Recruitment Employment Confederation (00052055) The views expressed in this mail are entirely those of the sender, and do not necessarily represent the views or position of Arras Services Ltd. The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in relation to the contents of this information is strictly prohibited and may be unlawful. Neither the sender nor the represented institution is liable for the correct and complete transmission of the contents of this e-mail, or for its timely receipt.
Re: Server tuning
If it starts swapping you have key_buffer set to high. -Eric On Tue, 29 Jun 2004 15:41:20 -0500, Victor Pendleton [EMAIL PROTECTED] wrote: max_memory is roughly equivalent to == key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections -Original Message- From: João Paulo Vasconcellos To: Mysql-general Mailing List Sent: 6/29/04 1:09 PM Subject: Server tuning Hello everybody, I am setting up a server to do POP/SMTP authentication using vpopmail. I took a look at the variables at global my.cnf and wondered what would be the best values for things like key_buffer, read_buffer_size and table_cache. I searched the manual, but it does not go deep in this subject, or I was not capable of finding the right place. What I want to know is how can I calculate how much memory I should give to key_buffer before I start to give away too much memory. That's because I got only 1GB of RAM and I have about 34k domains in my database, averaging from 8 to 15 accounts each. In a normal situation, there are ~400 simultaneous clients. I was wanting to know how can I estimate the memory usage for this scenario, if exists some kind of formula to answer this, like: clients * total size of key fields used in query or if this is some thing that is clear in the manual (if so, my apologies, but I could not find). TIA, -- João Paulo Vasconcellos -- 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: Memory to Memory INSERTS
http://dev.mysql.com/doc/mysql/en/Insert_speed.html -Eric On Tue, 29 Jun 2004 09:43:04 -0400, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I am proposing this as a hypothetical situation and I would like the full feedback of the group: Could Alejandro re-use the sections of the MySQL source code that handle replication and bin-logging to make his data capture application appear as a Master server and have his MySQL database act as its slave? Would that be faster than periodic batch files with LOAD DATA INFILE? Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Alejandro Heyworth [EMAIL PROTECTED]To: [EMAIL PROTECTED] ciples.com cc: Fax to: 06/25/2004 03:48 PM Subject: Memory to Memory INSERTS Hi! I'm looking for a better way to insert large numbers of rows from a client application that is sampling physical data in real-time. In our case, we are using a C double hipvalues[100] cyclical array to buffer our sampled values. We're currently creating large query strings similar to: INSERT DELAYED INTO hipjoint VALUES (hipvalues[0]),(hipvalues[1]),(hipvalues[2]),(hipvalues[3]),etc... We would like to continue to insert our values directly from our client app without first having to dump the data to a temp file and LOAD DATA INFILEing it periodically. Any ideas? Config values of interest: key_buffer_size = 4G bulk_insert_buffer_size = 1024M We are using MySQL 4.1.2. 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory to Memory INSERTS
Eric, I'm looking for a way to eliminate the construction, transmission, and parsing of the long multi-row INSERT queries that we are issuing from our client app. Since we are inserting 200k rows a shot, we're looking for every boost that we can find. * Connecting: (3) [want to use a connection pool] * Sending query to server: (2) [want to eliminate] * Parsing query: (2) [want to eliminate] * Inserting record: (1 x size of record) [no way around this] * Inserting indexes: (1 x number of indexes) [no way around this] * Closing: (1) [want to use a connection pool] Since we have already tuned the server, I'm looking for other ideas. Radical ideas are welcome! At 07:34 PM 6/29/04, you wrote: http://dev.mysql.com/doc/mysql/en/Insert_speed.html
Re: Memory to Memory INSERTS
On Tue, Jun 29, 2004 at 08:46:35PM -0400, Alejandro Heyworth wrote: Eric, I'm looking for a way to eliminate the construction, transmission, and parsing of the long multi-row INSERT queries that we are issuing from our client app. Since we are inserting 200k rows a shot, we're looking for every boost that we can find. * Connecting: (3) [want to use a connection pool] * Sending query to server: (2) [want to eliminate] * Parsing query: (2) [want to eliminate] * Inserting record: (1 x size of record) [no way around this] * Inserting indexes: (1 x number of indexes) [no way around this] * Closing: (1) [want to use a connection pool] Since we have already tuned the server, I'm looking for other ideas. Radical ideas are welcome! I missed the earlier part of the thread, but have you considered simply building raw MyISAM data files (.MYD) from your application? You could use a merge table over top of them after using myisamchk or ALTER TABLE to add the appropriate index(es). Just a thought. The file format is documented and not terribly difficult for some applications. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL and Macs
I¹m new to the list and new to mySQL. I¹m a Mac user and would like to set up a DB on my Mac. When I look at the MySQL web site, I can¹t seem to find any info on what hardware can be used. Can mySQL be run on a Mac? If so, what do I need to know about how to set it up? Can anyone point me to a setup procedure? Jim -- OYF is... Highly resourceful people working together. http://www.OwnYourFuture-net.com Own Your Future Consulting Services Limited, 1959 Upper Water Street, Suite 407, Halifax, Nova Scotia. B3J 3N2 Info Line: 902-823-2477, Phone: 902-823-2339. Fax: 902-823-2139
Re: Production release of MySql 4.1
Jocelyn Fournier wrote: Hi, AFAIK 4.1.3 should be beta. It is a little frustrating, at Linux Conf Adelaide 2004 (January), the Mysql guy there said that 4.1 would be in beta, in the next few weeks ... Its now July and its still in Alpha. It says on the webpage MySQL 4.1 -- Alpha release (use this for new development) - and it has said that for 6months+ So we did our development on 4.1, and were expecting it to be beta by February 2004. We're ready to roll it out as soon as it hits beta, i told my boss it would be in beta by March 2004 at the latest. We now have hardware sitting for around with 4.1 alpha on it that cannot be deployed. Does anyone actually have a concrete date when 4.1 will go into beta? Cheers Jon -- Jonathan Soong Information Services Institute of Medical and Veterinary Science (IMVS) Email: [EMAIL PROTECTED] Web : http://www.imvs.sa.gov.au Tel : +61 8 82223095 Fax : +61 8 82223147 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Macs
At 22:14 -0300 6/29/04, Jim Carwardine wrote: I'm new to the list and new to mySQL. I'm a Mac user and would like to set up a DB on my Mac. When I look at the MySQL web site, I can't seem to find any info on what hardware can be used. Can mySQL be run on a Mac? If so, what do I need to know about how to set it up? Can anyone point me to a setup procedure? Jim You need Mac OS X, not Mac OS. http://dev.mysql.com/doc/mysql/en/Installing.html http://dev.mysql.com/doc/mysql/en/Mac_OS_X_installation.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Macs
iH any machine running OS X can run MySQL, this link http://dev.mysql.com/downloads/mysql/4.0.html has a Mac OS X version with a package installer - hcir On Jun 29, 2004, at 5:14 PM, Jim Carwardine wrote: Im new to the list and new to mySQL. Im a Mac user and would like to set up a DB on my Mac. When I look at the MySQL web site, I cant seem to find any info on what hardware can be used. Can mySQL be run on a Mac? If so, what do I need to know about how to set it up? Can anyone point me to a setup procedure? Jim -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Macs
Mac OS X is well supported by MySQL. MySQL is even preinstalled in Mac OS X Server, although we choose to ignore the OS X Server installation in favor of installing the binary so that development and servers all have the MySQL files in the same paths. Developers in our company run MySQL on our Powerbooks and we have a dedicated master XServe running MySQL with another XServe acting as a slave replicating to the master. It is easy to install. Here are my installation notes for Panther (OS X 10.3) http://homepage.mac.com/kelleherk/iblog/C711669388/E733468496/index.html The www.mysql.com site has information on Mac OS X too aswell as the read me file in the download. Once you are up and running, I recommend CocoaMySQL as a complementary GUI (note complementary, you still need to do stuff on the command line ... easy after a little while) if you are still learning MySQL command line. -Kieran On Jun 29, 2004, at 9:14 PM, Jim Carwardine wrote: Im new to the list and new to mySQL. Im a Mac user and would like to set up a DB on my Mac. When I look at the MySQL web site, I cant seem to find any info on what hardware can be used. Can mySQL be run on a Mac? If so, what do I need to know about how to set it up? Can anyone point me to a setup procedure? Jim -- OYF is... Highly resourceful people working together. http://www.OwnYourFuture-net.com Own Your Future Consulting Services Limited, 1959 Upper Water Street, Suite 407, Halifax, Nova Scotia. B3J 3N2 Info Line: 902-823-2477, Phone: 902-823-2339. Fax: 902-823-2139 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using REGEXP
I'm sorry for my overly terse reply.
Perhaps I'm being dense, but I just don't get it. Your REGEXP matches a
string which starts with 1 or 2 letters, followed by 1 or 2 digits, followed
by 0 or 1 letters, which tells me a short postcode would be 'OX14' or
'OX14A', but your example short postcodes are 'OX14 1' and 'OX14 2'. You
add that the shortpostcodes column contains a list, but don't explicitly
state what it looks like. I'd guess 'OX14 1,OX14 2', but then you throw me
off with like '%xx%' would work if it weren't for the list.
Perhaps this would all be obvious to me if I were familiar with UK
postcodes, but I'm not, so I decided to ask for clarification rather than
guess. If you would clarify how to divide the short part from a postcode,
and verify what the list looks like, I'd be happy to try to come up with a
solution.
Michael
David Rayner wrote:
Michael,
I do believe I have described what I need doing to t1.txtDevPostCode (see
below) I need to truncate the t1.txtDevPostCode according to the RegExp
(UK Postcodes do not unfortunately have fixed lengths) and then compare
it with a list of already truncated Postcodes ie OX14 1, OX14 2 etc
If I was just comparing with a single value rather than a list I could do
a like '%xx%'
From: Michael Stassen [EMAIL PROTECTED]
select * from ytbl_development as t1
where (t1.txtDevPostCode REGEXP
^[[:alpha:]]{1,2}[[:digit:]]{1,2}[[:alpha:]]{0,1} in
#QuotedValueList(qryRadius.shortpostcode)#)
The above Where clause doesn't work , it just seems you can't use
REGEXP this way
qryRadius.shortpostcode contains a list of short postcodes
OX14 1,OX14 2 etc whereas t1.postcode contains full postcodes OX14 5RA
(If qryRadius.shortpostcode wasn't a list I'd be able to use LIKE)
How can I write a Where clause that gets round this
Shawn,
Your solution doesn't work unfortunately because I need to operate on t1.postcode
before making the
comparison (IT'S USUALLY the OTHER WAY ROUND) , I think this must be a generic
problem, so I think
an eventual solution will be interesting
zzapper (vim, cygwin, wiki zsh)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
creating log files
I apologise for not lurking for longer before posting, but this is becoming increasingly important. This is a FreeBSD system, but it's standard MySQL (mysql Ver 12.22 Distrib 4.0.20, for portbld-freebsd5.0 (i386)). This morning, a user of my network told me that the Internet is down. Quick inspection of the machine revealed that the WinXP box had failed to receive an IP address from the DHCP server, the machine in question. I plugged a monitor into it (since I now couldn't boot another Unix machine that required NFS and couldn't talk to it from a windows box) to reveal a screen full of errors, most of them saying no room left on device or not enough inodes. A quick df -i revealed that indeed, /var had no inodes left, which is weird since it usually sits at about 6% inodes used (obviously dhcpd couldn't write out the new lease file because it had no inodes left, explaining the internet being down). du -d2|sort -n revealed that 95% of the inodes used were in /var/db/mysql, and a directory listing revealed several thousand files named innodb.status. where ? is a number from 0-9. (They look suspiciously like PIDs.) I've noticed that past few days that mysql, while sitting idle, has been taking up as much as 30% CPU, and I can't track down why it would be doing that. It does it in spurts, taking 6%, and then 30% right around the time it creates the files. It seemed to be created about one every twenty seconds, without even querying the database! Here's an example: [EMAIL PROTECTED]:/var/db/mysql# while true; do sleep 60; ls inno*|wc -l; done 5 8 12 16 19 23 26 30 34 37 ^C Obviously, this is a bad thing. A typical innodb.status. looks like this: [EMAIL PROTECTED]:/var/db/mysql# cat innodb.status.1959 = 040614 20:55:02 INNODB MONITOR OUTPUT = Per second averages calculated from the last 16 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 4, signal count 4 Mutex spin waits 0, rounds 0, OS waits 0 RW-shared spins 6, OS waits 3; RW-excl spins 1, OS waits 1 Does anyone know what could cause this? It looks to be, for whatever reason writing out status information. But why it would do that, and why it would take 30% CPU idling is beyong me. Any ideas? (I wrote that email a few days ago, here is an update:) It seems that now another set of files is being created in the same directory, with names like ib_arch_log_050412, those numbers change as more files are created. I can't time how many are being created per minute exactly, but here's an example like above: [EMAIL PROTECTED]:/var/db$ while true; do sudo ls mysql | grep 'inno\|ib_arch_log' | wc -l; sleep 60; done 0 8 14 22 28 36 42 49 56 64 70 76 84 90 ^C The ib_arch_log_050444 files are not printable, and are all 2560 bytes long, owned by mysql:mysql. My server houses one small database with two tables, and then the mysql internal databse and an empty test database, and to my knowledge nothing is strange about its setup. I am using the default my_small.cnf that installed with it (from FreeBSD ports), and if it has any modifications they are only things like hostname and so on. I did turn off IP ports for it, so it's only using /tmp/mysql.sock for connection. If any more information is needed I can provide it. My current fix is walking up to the machine every few hours and deleting the log files, which definately isn't acceptable. I apologise if I am in the wrong group for this, I would like if I could be directed to the correct one. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
php-mysql RPM
Hi, I'm installing the php-mysql-4.3.6-1.3.i386.rpm and it's complaining about a dependency libmysqlclient.so.10. My mysql server version is 4.0.20. Can anyone tell me where I can get libmysqlclient.so.10. Thanks, -Ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: php-mysql RPM
you can get that on the mysql-client package On Wed, 30 Jun 2004 00:35:13 -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, I'm installing the php-mysql-4.3.6-1.3.i386.rpm and it's complaining about a dependency libmysqlclient.so.10. My mysql server version is 4.0.20. Can anyone tell me where I can get libmysqlclient.so.10. Thanks, -Ray -- 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]
Mac OSX Tiger and 64 Bit
So I have a question for those who understand developer speak and MySQL builds and so on... Apple announced their new OS earlier this week, including this information on the improvements to 64 Bit version using the G5 processor: http://www.apple.com/macosx/tiger/64bit.html One of our biggest problems to date on our G5 servers is despite the bulk ram we have installed, the current Apple OS isn't really 64 Bit so we can't give the InnoDB caches more than 2Gb of ram, and thus there are always no empty pages. This statement from Apple stops short of saying the OS was fully 64 bit... But I think they are saying that apps such as mysqld will be able to call larger chunks of memory, which is what we want. Between MySQL's strong Apple ties and the build engineers working on MySQL binaries and the knowledgeable members of this list can anyone interpret this statement from Apple and tell us if we will be able to increase the InnoDB cache settings to take advantage of the memory in the systems? As our application uses many different databases and any application server only ever speaks to one database I am seriously considering running multiple instances of MySQL on a single machine with different databases - but it's aheadache to administer... I'd rather use my 65 bit hardware and MySQL's 64 bit builds and use the memory in the machine in a single instance... Comments welcome. Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
