Re: Linux 2GB Memory Limit
On Tue, 13 Jul 2004 23:26:48 +0100, Marvin Wright [EMAIL PROTECTED] wrote: Hi, I'm now running redhat AS 3.0 with kernel version 2.4 and have 8GB of RAM. If I set my innodb_buffer_pool to 2048M, it just will not start, I get this error. 040713 22:10:24 mysqld started 040713 22:10:24 Warning: Asked for 196608 thread stack, but got 126976 InnoDB: Fatal error: cannot allocate 2147500032 bytes of InnoDB: memory with malloc! Total allocated memory Now I remember what I tracked down the limit to be ... 2147500032 is just above 2 gigabytes of memory. From what I have seen, glibc (not sure if this is fixed in recent versions) just refuses to allocate chunks of memory larger than 2 gigs in a single call. This seems a little odd given the library the malloc code is based on, but I haven't dug deeper. You can probably get around this if you do both of: 1. replace the call to malloc() in the innodb source with one that does a mmap() 2. run a kernel that has the 4G/4G patch, and possibly also moves where mmap()ed regions start to be a bit lower than 1 gig (not sure what the 4G/4G patch does with that). A pain in the ass. I strongly encourage people wanting larger innodb buffers to consider 64-bit Opterons or, less desirably, Intel's xeons w/64-bit support when they become generally available fairly soon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Copy the database
Hello, I wants to copy a full database with all its information to a database on another computer. Is it really that simple that I just create a new db on the new system and then copy the files from the old db directory into the newly created one? cheers Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why this query doesn't group the email addresses?
In article [EMAIL PROTECTED], Justin Swanhart [EMAIL PROTECTED] writes: Where is the implicit group? The 'order by' shouldn't effect how things are grouped. On MySQL 4.0.17: Wow. I wasn't aware. Is that expected behavior? Other databases (Oracle) generate an error when you include columns in the select list that aren't in a group by, Yes, because the SQL standard prohibits this. ... or they do an implicit group by (postgres) on the columns. Huh? PostgreSQL throws an error - as it should do. I [wrongly] assumed an implicit group by was going on because the query hadn't generated an error. Nope; that's a MySQL extension to the SQL standard which confused everyone and has few real use cases: MySQL groups by email and then fetches a random first and last name out of the group. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication stopping
Il lun, 2004-07-12 alle 13:45, Cemal Dalar ha scritto: To debug the problem. Make SHOW SLAVE STATUS in the slave and check for the error number.. Best Regards, Cemal Dalar a.k.a Jimmy System Administrator Web Developer http://www.gittigidiyor.com http://www.dalar.net - Original Message - From: Nico Sabbi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, July 12, 2004 6:08 PM Subject: Replication stopping Hi, it happened again just now, this is the status: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 55463 to server version: 4.0.18-Max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show slave status \G *** 1. row *** Master_Host: master Master_User: replica Master_Port: 3306 Connect_retry: 60 Master_Log_File: db-bin.3323 Read_Master_Log_Pos: 437183883 Relay_Log_File: server-relay-bin.005 Relay_Log_Pos: 228252575 Relay_Master_Log_File: db-bin.3323 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: Replicate_ignore_db: mysql Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 437183883 Relay_log_space: 228252575 1 row in set (0.00 sec) Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stored Procedure Limitation
Thanks Daniel. Finally, I am making use of the C API and the flag 'CLIENT_MULTI_RESULTS' as suggested. It's working !! :)) -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 14, 2004 5:07 AM To: Nawal Lodha; [EMAIL PROTECTED] Subject: Re: Stored Procedure Limitation Nawal Lodha wrote: Dear All, On executing a Stored Procedure (in MySQL 5.0.0-alpha) containing multiple Select statements, I get the error SELECT in a stored procedure must have INTO. I found that this is a MyODBC bug. http://bugs.mysql.com/bug.php?id=2273 and http://bugs.mysql.com/bug.php?id=2658 Mark Matthews has suggested to use the client flag 'CLIENT_MULTI_RESULTS' until we get the 'MyODBC 3.53' Release. Can someone help me in using it? I wish to Call the Stored Procedure from my C++ as well as VB code through ADO. Any input would be of immense help. Thanks, Nawal Lodha. Thanks for noticing my bug :) My interpretation of the response given to both bugs is that the current driver ( MyODBC-3.51.x ) will not work AT ALL with MySQL stored procedures that return a result set. The 'CLIENT_MULTI_RESULTS' is only available in client libraries that are compiled against MySQL-4.1 or newer, and MyODBC-3.51.x is not. According to the comments at the bottom of my bug: MyODBC 3.53 is near completion but no date has been set yet. ie you will be waiting for quite some time. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au
Re: Replication stopping
Ok. better check .err files in /usr/local/mysql/data/hostname.err for any errors.. It seems that there isn't any problems on slave but from my experience there can be problem on the slave like auth. For example when replica user can not login to master then 'show slave status' doesn't give any error messages but writes some lines to .err file.. Check those files both in master and in slave.. Best Regards, Cemal Dalar a.k.a Jimmy System Administrator Web Developer http://www.dalar.net - Original Message - From: Nico Sabbi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 14, 2004 2:15 PM Subject: Re: Replication stopping Il lun, 2004-07-12 alle 13:45, Cemal Dalar ha scritto: To debug the problem. Make SHOW SLAVE STATUS in the slave and check for the error number.. Best Regards, Cemal Dalar a.k.a Jimmy System Administrator Web Developer http://www.gittigidiyor.com http://www.dalar.net - Original Message - From: Nico Sabbi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, July 12, 2004 6:08 PM Subject: Replication stopping Hi, it happened again just now, this is the status: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 55463 to server version: 4.0.18-Max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show slave status \G *** 1. row *** Master_Host: master Master_User: replica Master_Port: 3306 Connect_retry: 60 Master_Log_File: db-bin.3323 Read_Master_Log_Pos: 437183883 Relay_Log_File: server-relay-bin.005 Relay_Log_Pos: 228252575 Relay_Master_Log_File: db-bin.3323 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: Replicate_ignore_db: mysql Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 437183883 Relay_log_space: 228252575 1 row in set (0.00 sec) Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mySQL on MAC
Astrum Et Securis Kieran, Thanks a bunch! Some very useful and handy tips... Really appreciate it. Dominor, RSJ -Original Message- From: Kieran Kelleher [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 14, 2004 12:03 AM To: MySQL Junkie Cc: [EMAIL PROTECTED] Subject: Re: mySQL on MAC I got some tips here for MySQL on Mac OS X. Hope it helps. http://homepage.mac.com/kelleherk/iblog/C711669388/index.html On Jul 13, 2004, at 7:21 PM, MySQL Junkie wrote: Astrum Et Securis We've just bought a new Power Mac G5 Macintosh computer for the office and I'll be needing to do some database engineering work using it. I've never worked with mySQL on MAC, so I really have no experience with what happens on a MAC.. Anyone here who works with mySQL on a MAC? Any helpful insights? Thanks. RSJ ___ Kieran Kelleher SmartleadsUSA,LLC 2656 West Lake Rd Palm Harbor, FL 34684 [EMAIL PROTECTED] 727-785-0766 x33 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copy the database
mysql wrote: Hello, I wants to copy a full database with all its information to a database on another computer. Is it really that simple that I just create a new db on the new system and then copy the files from the old db directory into the newly created one? cheers Alex A safer solution is to use mysqldump - this dumps the contents of your database and the data structure as SQL queries, and then you can just treat the dumped file as a batch file when recreating. -- Rory McKinley Nebula Solutions +27 21 555 3227 - office +27 21 551 0676 - fax +27 82 857 2391 - mobile www.nebula.co.za This e-mail is intended only for the person to whom it is addressed and may contain confidential information which may be legally privileged. Nebula Solutions accepts no liability for any loss, expense or damage arising from this e-mail and/or any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No Response from Server
Have you checked your slow query log and your error logs? Depending on your usage patterns, you may need to optimize some of your queries. Is your MySQL server sharing CPU time with any other applications? How many? Could they be the cause of your CPU usage spike? To the list: (This may or may not be related to his problem) Could several low-speed connections tie up CPU resources while transferring large outbound datasets? As a list member, would you think that installing a cache server to buffer the outbound data would help? (Something like a print spooler but for his outbound network data). Regards, Shawn Green Database Administrator Unimin Corporation - Spruce Pine s.ahmad [EMAIL PROTECTED]To: [EMAIL PROTECTED] il.com cc: Fax to: 07/13/2004 02:28 Subject: No Response from Server AM Hello, Dear All, i'm now a days having quite big problem, i would like to get help from you guyz, ... we have Railways Reservation System of whole country hosted on our servers which is purely in php MYSQL. problem is that when the country wide offices start working, our server CPU uUsage goes upto 99% and oftenly it chokes the server. We tried it on blank server with only 1 site hosted. the server specs were 1 GB RAM Xeon Dual Processor 100 GB HDD but same, a blank serevr was also choked by the usage. This started happening bcz. DB is growing day by day and is quite big in size. Can any body tell me what can i do. Should i use MYSQL Clusters or any other thing ... i'll be so gratefull regards, s.ahmad Lahore, Pakistan -- 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: No Response from Server
What is the amout of volume? Are you reaching max connections? What does the I/0 look like? -Original Message- From: [EMAIL PROTECTED] To: s.ahmad Cc: [EMAIL PROTECTED] Sent: 7/14/04 7:51 AM Subject: Re: No Response from Server Have you checked your slow query log and your error logs? Depending on your usage patterns, you may need to optimize some of your queries. Is your MySQL server sharing CPU time with any other applications? How many? Could they be the cause of your CPU usage spike? To the list: (This may or may not be related to his problem) Could several low-speed connections tie up CPU resources while transferring large outbound datasets? As a list member, would you think that installing a cache server to buffer the outbound data would help? (Something like a print spooler but for his outbound network data). Regards, Shawn Green Database Administrator Unimin Corporation - Spruce Pine s.ahmad [EMAIL PROTECTED]To: [EMAIL PROTECTED] il.com cc: Fax to: 07/13/2004 02:28 Subject: No Response from Server AM Hello, Dear All, i'm now a days having quite big problem, i would like to get help from you guyz, ... we have Railways Reservation System of whole country hosted on our servers which is purely in php MYSQL. problem is that when the country wide offices start working, our server CPU uUsage goes upto 99% and oftenly it chokes the server. We tried it on blank server with only 1 site hosted. the server specs were 1 GB RAM Xeon Dual Processor 100 GB HDD but same, a blank serevr was also choked by the usage. This started happening bcz. DB is growing day by day and is quite big in size. Can any body tell me what can i do. Should i use MYSQL Clusters or any other thing ... i'll be so gratefull regards, s.ahmad Lahore, Pakistan -- 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]
cannot update read/write table with a read-only table in the where clause
Hi all ! Sorry for the long subject line, but I couldn't find a short one... Here is the problem : My database (mysql 4.1.1) consists mostly for compressed MyISAM tables. I wrote a script, relying on a temporary memory-based table. This table is, of course, writable. If try to do an update or a delete on this table (and only on this table), but with a join on a compressed table in the where clause, I get an error : 'table [mycompressedtable] is read only'. The problem is that I am not trying to write to this table ! For example, let's say I have a memory based table 'tmpTable', and a compressed table 'storageTable'. All these queries give me errors : UPDATE tmpTable, storageTable SET tmpTable.Field1 = 'whatever' WHERE (tmpTAble.Field2 = storageTable.Field2) AND (storageTable.Field3 = 'something') or DELETE tmpTAble FROM tmpTable INNER JOIN storageTable ON (tmpTAble.Field2 = storageTable.Field2) WHERE (storageTable = 'something') or DELETE FROM tmpTAble USING tmpTable, storageTable WHERE (tmpTAble.Field2 = storageTable.Field2) AND (storageTable = 'something') So, it looks like the query analyzer does not even check if the table being updated or deleted is writable, it only checks if there is a read only table in the query, and rejects it in this case... I have seen no mention of this in the documentation, should I report this as a bug ? I can work around this using a subquery, but it is a lot less optimized than using joins, I think... Thanks for helping ! Arnaud Lesauvage -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 4.1 performance
I actually posted something yesterday, but it got bounced (HTML with long lines?). I'm starting to wonder whether the problem is my configuration. I just noticed yesterday that the 4.1 my sysadmin set up is running bin/safe_mysqld. Unfortunately it will take me some time to get back to where I was and rerun my tests not in safe mode. Here again is the post in plain text: Running ANALYZE appears to help, narrowing the gap between what I'm seeing with 3.23.58 (3.23.58 is still more than 20% faster than 4.1.3beta on my queries) Here's some information about the tables, and EXPLAIN for a typical slow query: mysql describe All_tid; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | term | varchar(100) | | PRI | | | | termID | int(11) | YES | | NULL| | ++--+--+-+-+---+ 2 rows in set (0.00 sec) mysql describe All_tidpos; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | termID | int(11) | | PRI | 0 | | | recID | int(11) | | PRI | 0 | | | pos| int(11) | | PRI | 0 | | | field | char(4) | | | | | ++-+--+-+-+---+ 4 rows in set (0.00 sec) mysql explain select count(distinct tp1.recID) from All_tidpos tp1, All_tid tid1, All_tidpos tp2, All_tid tid2, All_tidpos tp3, All_tid tid3, All_tidpos tp4, All_tid tid4, All_tidpos tp5, All_tid tid5, All_tidpos tp6, All_tid tid6, All_tidpos tp7, All_tid tid7 where tid1.term='new' and tid1.termID=tp1.termID and tid2.term='york' and tid2.termID=tp2.termID and tid3.term='state' and tid3.termID=tp3.termID and tid4.term='commission' and tid4.termID=tp4.termID and tid5.term='education' and tid5.termID=tp5.termID and tid6.term='reform' and tid6.termID=tp6.termID and tid7.term='sound' and tid7.termID=tp7.termID and tp1.recID=tp2.recID and tp1.recID=tp3.recID and tp1.recID=tp4.recID and tp1.recID=tp5.recID and tp1.recID=tp6.recID and tp1.recID=tp7.recID; ++-+---+---+---+-+-+ ---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+-+-+ ---+--+-+ | 1 | SIMPLE | tid1 | const | PRIMARY | PRIMARY | 100 | const |1 | | | 1 | SIMPLE | tid2 | const | PRIMARY | PRIMARY | 100 | const |1 | | | 1 | SIMPLE | tid3 | const | PRIMARY | PRIMARY | 100 | const |1 | | | 1 | SIMPLE | tid4 | const | PRIMARY | PRIMARY | 100 | const |1 | | | 1 | SIMPLE | tid5 | const | PRIMARY | PRIMARY | 100 | const |1 | | | 1 | SIMPLE | tid6 | const | PRIMARY | PRIMARY | 100 | const |1 | | | 1 | SIMPLE | tid7 | const | PRIMARY | PRIMARY | 100 | const |1 | | | 1 | SIMPLE | tp1 | ref | PRIMARY | PRIMARY | 4 | const | 23 | Using index | | 1 | SIMPLE | tp2 | ref | PRIMARY | PRIMARY | 8 | const,worldcat0.tp1.recID |1 | Using index | | 1 | SIMPLE | tp3 | ref | PRIMARY | PRIMARY | 8 | const,worldcat0.tp1.recID |1 | Using index | | 1 | SIMPLE | tp4 | ref | PRIMARY | PRIMARY | 8 | const,worldcat0.tp1.recID |1 | Using index | | 1 | SIMPLE | tp5 | ref | PRIMARY | PRIMARY | 8 | const,worldcat0.tp1.recID |1 | Using index | | 1 | SIMPLE | tp6 | ref | PRIMARY | PRIMARY | 8 | const,worldcat0.tp1.recID |1 | Using index | | 1 | SIMPLE | tp7 | ref | PRIMARY | PRIMARY | 8 | const,worldcat0.tp1.recID |1 | Using index | ++-+---+---+---+-+-+ ---+--+-+ 14 rows in set (0.11 sec) As possibly you can tell, this is full-text retrieval layered on top of straight SQL. The query is long but simple: 'new and york and state and commission and education and reform and sound', which should run fast since the result is empty, but each of the terms will result in many rows in the All_tidpos table. One thing that 4.1 is better at is speeding up repeated queries, so for testing we're forced to run thousands of queries through the system to avoid speed-ups across runs.
Re: Access hosts wildcard.
You can test if you can see the server from your other user's machine by trying to connect to it with telnet. On the other user's machine, open a telnet session to the server's address on port 3306 and press ENTER a few times. You should be able to pick the server's executable name and version number out of the response. If you cannot make this connection, you have issues other than MySQL authentication. (http://dev.mysql.com/doc/mysql/en/Can_not_connect_to_server.html) MySQL allows you to specify for which addresses a user account will be valid. If you grant permissions to [EMAIL PROTECTED] then someone can log in using that name only from that one address. To allow users to log in from multiple addresses MySQL, allows for wildcards in the host field of the user table. (http://dev.mysql.com/doc/mysql/en/Connection_access.html) Here is a defacto FAQ about debugging various access denied problems: http://dev.mysql.com/doc/mysql/en/Access_denied.html I have no experience with that particular product (cPanels interface) but it _should_ allow you to run the MySQL commands GRANT and REVOKE to let you manually work around any UI issue, if one exists.(http://dev.mysql.com/doc/mysql/en/GRANT.html) Sorry I couldn't be more directly helpful, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Matthew Stanfield [EMAIL PROTECTED]To: Frederic Wenzel [EMAIL PROTECTED] re.net cc: [EMAIL PROTECTED] Fax to: 07/13/2004 05:51 Subject: Re: Access hosts wildcard. PM Matthew Stanfield wrote: I'm setting up MySQL databases on 'shared server' space I've rented using cPanel's 'MySQL Database' interface. I can add 'access hosts' (it's not limited to localhost) and it says the % wildcard is allowed. Forgive me if this is a trivial or annoying question (I can't find the answer anywhere) but how do I just 'allow any host'? Presumably I use % in some way maybe '%.%.%.%' (an all encompassing IP address with wildcards instead of numbers) or maybe it's just '%'? Frederic Wenzel wrote: [...] how do I just 'allow any host'? [...] maybe it's just '%'? AFAIK, that's it. Thanks Frederic. Does anyone know any different as this doesn't seem to be working? --But maybe the problem lies elsewhere. :( ..matthew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1146
I don't see 'caldata.resource' anywhere in the statement you posted. Are you sure you are sending the correct string to MySQL for execution as you didn't post what MySQL tried to execute (based on its error message) for us to look at. This may be a variable name typo issue. One very simple technique you can use to debug many SQL issues like these is to add to your code commands to write your SQL statements into your output just before you execute them. (I have found *SO* many finger flubs, missing spaces, and data type mismatches that way!!) If you wrap your debugging echo commands with conditional statements you will be able to turn them on and off with great ease by just adjusting a single variable at the top of your code. This is just a good practice to get into while developing new applications. You could strategically strip them out again later when you take your code into production or you can (I think should) leave them in for the rare cases when you need that information again. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeff Blasius [EMAIL PROTECTED]To: '[EMAIL PROTECTED]' [EMAIL PROTECTED] rs.com cc: Fax to: 07/13/2004 05:45 Subject: Error 1146 PM I am trying to do an INSERT ... SELECT statement. When using MySQLcc everything is fine. However when I try to execute the command using PHP I get error 1146: Table 'caldata.resource' doesn't exist. Following is my query statement. I am trying to combine a bunch of tables together and I don't think the MERGE method will work very well for me. The table 'job' already exists with all of the necessary fields. There is a primary key setup in 'job' called 'id'. I appreciate any help. INSERT INTO job (CustomerName, ModelNumber, SerialNumber, CertificateNumber, AverageFluidViscosity, JobNumber, SystemID) SELECT CustomerName, ModelNumber, SerialNumber, CertificateNumber, AverageFluidViscosity, JobNumber, SystemID FROM 50041_job Jeff Blasius mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- 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
Search one table, then display another table where keys match ?
If you would entertain a MYSQL/PHP, hope not too off-topicIt's
probably not difficult to solve - but you would be helping me
with some SQL logic.
The only way I can think of to explain what I want to do
is to give you my working newbie MSQL/PHP code that I'm learning
MYSQL/PHP with, and at a certain point in the code below I'll state
exactly as I can what I want to try to do. It's probably
quite simple but I can't get it- Thanks:
...
pre
?php
$dblink = mysql_connect ( 'localhost', guest, password );
mysql_select_db( balloon, $dblink );
// Doing a FULLTEXT search
// Re the SELECT: I indexed both fields together, so seemed like
// I should put them both in the MATCH...OK, it works.
$query=SELECT * FROM balloon_txt WHERE MATCH(access_no, recs_txt)
AGAINST ('robin');
$result = MySQL_query($query);
/
OK, right here - next below I'm gonna display/loop $result from table
balloon_txt. But, what I really want to do is take the result set
access_no fields from the search above and (access_no is a Key in all
my tables) and use it to generate results (ie. matching records) from
another table called balloon_rec and dispaly/loop the results from
balloon_rec. So I'm searching balloon_txt, getting results, but I want
to display matching records from another table - balloom_rec. Is there
a way to do a join or something in the SELECT above? Or do I process
$result? Seems a join in the SELECT above or some SQL above is cleaner
- but not sure how(?) Thanks, Lee G.
///
while ( $row = mysql_fetch_row( $result ) ) {
for ( $i=0; $imysql_num_fields( $result ); $i++ )
{echo $row[$i] . ;}
echo\n\n\n;
}
// Close the db connection
mysql_close ( $dblink );
?
/pre
...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql function for timestamp
Hi, What SQL function do I need to convert timestamp 200406011403 into 2004-06-01 14:03 ? Thanks, JS. _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: 4.1 performance
I was able to rerun my tests using mysqld (as opposed to safe_mysqld). I'm happy to report that the times now almost exactly match MySQL 3.23.58. --Th -Original Message- From: Hickey,Thom Sent: Wednesday, July 14, 2004 9:12 AM To: 'Lachlan Mulcahy'; [EMAIL PROTECTED] Subject: RE: 4.1 performance I actually posted something yesterday, but it got bounced (HTML with long lines?). I'm starting to wonder whether the problem is my configuration. I just noticed yesterday that the 4.1 my sysadmin set up is running bin/safe_mysqld. Unfortunately it will take me some time to get back to where I was and rerun my tests not in safe mode. Here again is the post in plain text: Running ANALYZE appears to help, narrowing the gap between what I'm seeing with 3.23.58 (3.23.58 is still more than 20% faster than 4.1.3beta on my queries) Here's some information about the tables, and EXPLAIN for a typical slow query: mysql describe All_tid; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | term | varchar(100) | | PRI | | | | termID | int(11) | YES | | NULL| | ++--+--+-+-+---+ 2 rows in set (0.00 sec) mysql describe All_tidpos; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | termID | int(11) | | PRI | 0 | | | recID | int(11) | | PRI | 0 | | | pos| int(11) | | PRI | 0 | | | field | char(4) | | | | | ++-+--+-+-+---+ 4 rows in set (0.00 sec) mysql explain select count(distinct tp1.recID) from All_tidpos tp1, All_tid tid1, All_tidpos tp2, All_tid tid2, All_tidpos tp3, All_tid tid3, All_tidpos tp4, All_tid tid4, All_tidpos tp5, All_tid tid5, All_tidpos tp6, All_tid tid6, All_tidpos tp7, All_tid tid7 where tid1.term='new' and tid1.termID=tp1.termID and tid2.term='york' and tid2.termID=tp2.termID and tid3.term='state' and tid3.termID=tp3.termID and tid4.term='commission' and tid4.termID=tp4.termID and tid5.term='education' and tid5.termID=tp5.termID and tid6.term='reform' and tid6.termID=tp6.termID and tid7.term='sound' and tid7.termID=tp7.termID and tp1.recID=tp2.recID and tp1.recID=tp3.recID and tp1.recID=tp4.recID and tp1.recID=tp5.recID and tp1.recID=tp6.recID and tp1.recID=tp7.recID; ++-+---+---+---+-+-+ ---+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+-+-+ ---+--+-+ | 1 | SIMPLE | tid1 | const | PRIMARY | PRIMARY | 100 | const |1 | | | 1 | SIMPLE | tid2 | const | PRIMARY | PRIMARY | 100 | const |1 | | | 1 | SIMPLE | tid3 | const | PRIMARY | PRIMARY | 100 | const |1 | | | 1 | SIMPLE | tid4 | const | PRIMARY | PRIMARY | 100 | const |1 | | | 1 | SIMPLE | tid5 | const | PRIMARY | PRIMARY | 100 | const |1 | | | 1 | SIMPLE | tid6 | const | PRIMARY | PRIMARY | 100 | const |1 | | | 1 | SIMPLE | tid7 | const | PRIMARY | PRIMARY | 100 | const |1 | | | 1 | SIMPLE | tp1 | ref | PRIMARY | PRIMARY | 4 | const | 23 | Using index | | 1 | SIMPLE | tp2 | ref | PRIMARY | PRIMARY | 8 | const,worldcat0.tp1.recID |1 | Using index | | 1 | SIMPLE | tp3 | ref | PRIMARY | PRIMARY | 8 | const,worldcat0.tp1.recID |1 | Using index | | 1 | SIMPLE | tp4 | ref | PRIMARY | PRIMARY | 8 | const,worldcat0.tp1.recID |1 | Using index | | 1 | SIMPLE | tp5 | ref | PRIMARY | PRIMARY | 8 | const,worldcat0.tp1.recID |1 | Using index | | 1 | SIMPLE | tp6 | ref | PRIMARY | PRIMARY | 8 | const,worldcat0.tp1.recID |1 | Using index | | 1 | SIMPLE | tp7 | ref | PRIMARY | PRIMARY | 8 | const,worldcat0.tp1.recID |1 | Using index | ++-+---+---+---+-+-+ ---+--+-+ 14 rows in set (0.11 sec) As possibly you can tell, this is full-text retrieval layered on top of straight SQL. The query is long but simple: 'new and york and state and commission and education and reform
RE: How to create an Installation Package using VB.NET?
Reggie I was hoping to perform the mysql installation myself. I was also hoping that I don't have to install the full suite as available on the site, but a subset of that. What I am looking for is the list of necessary files that I need to install mysql, to package with my VB application. I will be using the package deployment feature in VB.NET to create the installer. In the meantime, I will check out the command line help to see if I can find to command to install mysql as a service, and probably grant user access as well. Thanks, ~roy. -Original Message- From: Reggie Burnett [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 13, 2004 8:15 PM To: 'Roy Brown'; [EMAIL PROTECTED] Subject: RE: How to create an Installation Package using VB.NET? Roy I don't think we provide any merge module for our ODBC installation yet, but the changes that the odbc installer makes are very trivial so you could make them yourself. Are you going to run the mysql installer or perform the install yourself? If you are going to perform the install yourself, you could install the service by simply running the mysql binary with the --install option. [check the command line help for syntax] Once you get the mysql service installed, you could start the service and then add users using some vb.net ado.net code you write. Or you could just create a text file in temp and feed that to the server. Are you using the Vs.net installer stuff or wix? -reggie -Original Message- From: Roy Brown [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 13, 2004 3:54 PM To: [EMAIL PROTECTED] Subject: How to create an Installation Package using VB.NET? Hi, I am very new to MySQL and using it as the backend of my VB.NET application. Now, I am trying to create an installation package from within VB and don't know what files from MySQL MyODBC 3.51 I need to add to my installation package. Can anyone provide some help in this regards? Also, I will need to know how to start mysql as a service, setup users password, all through the installation package. I would appreciate any help that anyone can provide. Sincerely, Roy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to create an Installation Package using VB.NET?
I'm sure there is some subset of files that you can include, based on what features you want to make available to your clients. -reggie -Original Message- From: Roy Brown [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 14, 2004 10:14 AM To: 'Reggie Burnett'; [EMAIL PROTECTED] Subject: RE: How to create an Installation Package using VB.NET? Reggie I was hoping to perform the mysql installation myself. I was also hoping that I don't have to install the full suite as available on the site, but a subset of that. What I am looking for is the list of necessary files that I need to install mysql, to package with my VB application. I will be using the package deployment feature in VB.NET to create the installer. In the meantime, I will check out the command line help to see if I can find to command to install mysql as a service, and probably grant user access as well. Thanks, ~roy. -Original Message- From: Reggie Burnett [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 13, 2004 8:15 PM To: 'Roy Brown'; [EMAIL PROTECTED] Subject: RE: How to create an Installation Package using VB.NET? Roy I don't think we provide any merge module for our ODBC installation yet, but the changes that the odbc installer makes are very trivial so you could make them yourself. Are you going to run the mysql installer or perform the install yourself? If you are going to perform the install yourself, you could install the service by simply running the mysql binary with the --install option. [check the command line help for syntax] Once you get the mysql service installed, you could start the service and then add users using some vb.net ado.net code you write. Or you could just create a text file in temp and feed that to the server. Are you using the Vs.net installer stuff or wix? -reggie -Original Message- From: Roy Brown [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 13, 2004 3:54 PM To: [EMAIL PROTECTED] Subject: How to create an Installation Package using VB.NET? Hi, I am very new to MySQL and using it as the backend of my VB.NET application. Now, I am trying to create an installation package from within VB and don't know what files from MySQL MyODBC 3.51 I need to add to my installation package. Can anyone provide some help in this regards? Also, I will need to know how to start mysql as a service, setup users password, all through the installation package. I would appreciate any help that anyone can provide. Sincerely, Roy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to create an Installation Package using VB.NET?
All I want is to just use mysql as the backend for my application. I don't want to implement any features from mysql. So basically, I just want the list of files necessary to use it as a data store. ~roy~ -Original Message- From: Reggie Burnett [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 14, 2004 8:29 AM To: 'Roy Brown'; [EMAIL PROTECTED] Subject: RE: How to create an Installation Package using VB.NET? I'm sure there is some subset of files that you can include, based on what features you want to make available to your clients. -reggie -Original Message- From: Roy Brown [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 14, 2004 10:14 AM To: 'Reggie Burnett'; [EMAIL PROTECTED] Subject: RE: How to create an Installation Package using VB.NET? Reggie I was hoping to perform the mysql installation myself. I was also hoping that I don't have to install the full suite as available on the site, but a subset of that. What I am looking for is the list of necessary files that I need to install mysql, to package with my VB application. I will be using the package deployment feature in VB.NET to create the installer. In the meantime, I will check out the command line help to see if I can find to command to install mysql as a service, and probably grant user access as well. Thanks, ~roy. -Original Message- From: Reggie Burnett [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 13, 2004 8:15 PM To: 'Roy Brown'; [EMAIL PROTECTED] Subject: RE: How to create an Installation Package using VB.NET? Roy I don't think we provide any merge module for our ODBC installation yet, but the changes that the odbc installer makes are very trivial so you could make them yourself. Are you going to run the mysql installer or perform the install yourself? If you are going to perform the install yourself, you could install the service by simply running the mysql binary with the --install option. [check the command line help for syntax] Once you get the mysql service installed, you could start the service and then add users using some vb.net ado.net code you write. Or you could just create a text file in temp and feed that to the server. Are you using the Vs.net installer stuff or wix? -reggie -Original Message- From: Roy Brown [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 13, 2004 3:54 PM To: [EMAIL PROTECTED] Subject: How to create an Installation Package using VB.NET? Hi, I am very new to MySQL and using it as the backend of my VB.NET application. Now, I am trying to create an installation package from within VB and don't know what files from MySQL MyODBC 3.51 I need to add to my installation package. Can anyone provide some help in this regards? Also, I will need to know how to start mysql as a service, setup users password, all through the installation package. I would appreciate any help that anyone can provide. Sincerely, Roy. -- 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_real_connect' with DSN
Hi All, Can 'mysql_real_connect' be called using DSN name rather than giving hostname, database name, port no., etc ? Thanks, Nawal.
RE: 4.1 performance
Hickey,Thom [EMAIL PROTECTED] wrote: One thing that 4.1 is better at is speeding up repeated queries, ...thanks to query cache, introduced in 4.0.1. so for testing we're forced to run thousands of queries through the system to avoid speed-ups across runs. SELECT SQL_NO_CACHE is pretty easier. ./ premax ./ [EMAIL PROTECTED] ./ koniec i bomba, a kto czytal ten traba. w.g. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sql function for timestamp
In MySQL you can do that with: DATE_FORMAT DATE_FORMAT (column_name,'%Y-%m-%d %H:%i') -Mensaje original- De: J S [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 14 de Julio de 2004 08:26 a.m. Para: [EMAIL PROTECTED] Asunto: sql function for timestamp Hi, What SQL function do I need to convert timestamp 200406011403 into 2004-06-01 14:03 ? Thanks, JS. _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- 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: sql function for timestamp
While in PHP you can try something like this... $date_added = $row['date_added']; $date = date(M d, Y, strtotime($date_added)); echo $date; H and i do work in PHP as well, I believe, if you want to add the hour and seconds. Check out the 'date' function in PHP for info. - Eve -Original Message- From: Chinchilla Zúñiga, Guillermo [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 14, 2004 11:54 AM To: J S; [EMAIL PROTECTED] Subject: RE: sql function for timestamp In MySQL you can do that with: DATE_FORMAT DATE_FORMAT (column_name,'%Y-%m-%d %H:%i') -Mensaje original- De: J S [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 14 de Julio de 2004 08:26 a.m. Para: [EMAIL PROTECTED] Asunto: sql function for timestamp Hi, What SQL function do I need to convert timestamp 200406011403 into 2004-06-01 14:03 ? Thanks, JS. _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- 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]
deleting millions of records did not change the file size
My question in brief is why wouldn't the physical mysql files change in size if a substantial percentage of the records are deleted from a given table??? I have a table which contained 13.5 million records. I then deleted 6.5 million records; however, the physical file size did not change. The file size before was: 12K -rw-rw1 mysqlmysql8.7K Nov 28 2003 position_log.frm 1006M -rw-rw1 mysqlmysql 1005M Jul 13 15:42 position_log.MYD 233M -rw-rw1 mysqlmysql232M Jul 13 15:43 position_log.MYI The file size after deleting 6.5 M records is: -rw-rw1 mysqlmysql8.7K Nov 28 2003 /data1/mysql/positionresearch/position_log.frm -rw-rw1 mysqlmysql 1005M Jul 14 07:32 /data1/mysql/positionresearch/position_log.MYD -rw-rw1 mysqlmysql232M Jul 14 07:32 /data1/mysql/positionresearch/position_log.MYI [EMAIL PROTECTED] Position Research, Inc. Search engine results by research tel: (760) 480-8291 fax: (760) 480-8271 www.PositionResearch.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql function for timestamp
If you have 4.1.1 or greater, you can use GET_FORMAT( timestamp, 'ISO' ). Otherwise, I think if you just use DATE_FORMAT( timestamp, '%Y-%m-%d %H:%i ) you will get weird values if your timestamp field doesn't include seconds. So, either alter your field to be CONCAT( timestamp, '00' ), or, as far as I can tell, you will probably be better off just using string functions. Wes On Jul 14, 2004, at 10:25 AM, J S wrote: Hi, What SQL function do I need to convert timestamp 200406011403 into 2004-06-01 14:03 ? Thanks, JS. _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- 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: Search one table, then display another table where keys match ?
Match() returns a graded relevance rating, as a floating point number
(when called as you call it) for matches. Words present in more than half
the rows are ignored, presence of a word in most rows lowers relevance,
presence of a word in just a few rows increases it, weights are then
combined to compute the return value. Match() doesn't sound quite like what
you are looking for, though what you're looking for isn't entirely clear
from your post.
The first problem is, extracting multiple keys from text, for matching
against keys in another table, is usually the sort of thing that is best
done before the data comes into the database. The reason is that the
assumptions underpinning SQL are not compatible with your use of the text
column as a key bank: your use violates one of Codd's rules, that a single
column should yield only one value. So you might want to consider breaking
out the key-containing text into rows of data that can be processsed by
ordinary SQL logic.
The second problem is, if you must find rows in tableB having key values
which have been found in a text column from certain rows in tableA, is a
match to be computed without regard to context, for example the text '123'
is to be accepted as a match no matter where it is found, and no matter
whether it occurs inside strings like '01234'? If so, have a look at the
Locate() function. OTOH, if there are context rules, you will probably need
to use regular expression logic to find the matches you want, so have a look
at str REGEXP pattern in the manual.
Once you reslve these two problems, probably simple JOIN syntax will get you
the SQL result you need.
HTH.
PB
- Original Message -
From: leegold
To: mySQL mailinglist
Sent: Wednesday, July 14, 2004 9:06 AM
Subject: Search one table, then display another table where keys match ?
If you would entertain a MYSQL/PHP, hope not too off-topicIt's
probably not difficult to solve - but you would be helping me
with some SQL logic.
The only way I can think of to explain what I want to do
is to give you my working newbie MSQL/PHP code that I'm learning
MYSQL/PHP with, and at a certain point in the code below I'll state
exactly as I can what I want to try to do. It's probably
quite simple but I can't get it- Thanks:
...
pre
?php
$dblink = mysql_connect ( 'localhost', guest, password );
mysql_select_db( balloon, $dblink );
// Doing a FULLTEXT search
// Re the SELECT: I indexed both fields together, so seemed like
// I should put them both in the MATCH...OK, it works.
$query=SELECT * FROM balloon_txt WHERE MATCH(access_no, recs_txt)
AGAINST ('robin');
$result = MySQL_query($query);
/
OK, right here - next below I'm gonna display/loop $result from table
balloon_txt. But, what I really want to do is take the result set
access_no fields from the search above and (access_no is a Key in all
my tables) and use it to generate results (ie. matching records) from
another table called balloon_rec and dispaly/loop the results from
balloon_rec. So I'm searching balloon_txt, getting results, but I want
to display matching records from another table - balloom_rec. Is there
a way to do a join or something in the SELECT above? Or do I process
$result? Seems a join in the SELECT above or some SQL above is cleaner
- but not sure how(?) Thanks, Lee G.
///
while ( $row = mysql_fetch_row( $result ) ) {
for ( $i=0; $imysql_num_fields( $result ); $i++ )
{echo $row[$i] . ;}
echo\n\n\n;
}
// Close the db connection
mysql_close ( $dblink );
?
/pre
...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
RE: sql function for timestamp
Thanks for your help. I would like to do it with the SQL. However I'm still having problems with the syntax below. Is this wrong? mysql SELECT DATE_FORMAT (20040601123456,'%Y-%m-%d'); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(20040601123456,'%Y-%m-%d')' at line 1 Also I tried: mysql SELECT DATE_FORMAT (time,'%Y-%m-%d %H:%i') - from internet_usage - where time2004060112; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(time,'%Y-%m-%d %H:%i') from internet_usage where time=20040601 While in PHP you can try something like this... $date_added = $row['date_added']; $date = date(M d, Y, strtotime($date_added)); echo $date; H and i do work in PHP as well, I believe, if you want to add the hour and seconds. Check out the 'date' function in PHP for info. - Eve In MySQL you can do that with: DATE_FORMAT DATE_FORMAT (column_name,'%Y-%m-%d %H:%i') -Mensaje original- De: J S [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 14 de Julio de 2004 08:26 a.m. Para: [EMAIL PROTECTED] Asunto: sql function for timestamp Hi, What SQL function do I need to convert timestamp 200406011403 into 2004-06-01 14:03 ? Thanks, JS. _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- 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] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: deleting millions of records did not change the file size
In the last episode (Jul 14), Gil Vidals said: My question in brief is why wouldn't the physical mysql files change in size if a substantial percentage of the records are deleted from a given table??? Automatic filesize reduction would only be possible if the deleted records were at the end of the file, and that's rare enough that it's not implemented. Try running OPTIMIZE TABLE mytable, which will copy all the records out of your table into a fresh new one, and will also rebuild and analyze your indexes. I have a table which contained 13.5 million records. I then deleted 6.5 million records; however, the physical file size did not change. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: deleting millions of records did not change the file size
Run optimize table position_log Marvin. -Original Message- From: Gil Vidals [mailto:[EMAIL PROTECTED] Sent: 14 July 2004 17:04 To: [EMAIL PROTECTED] Cc: robert gavina Subject: deleting millions of records did not change the file size My question in brief is why wouldn't the physical mysql files change in size if a substantial percentage of the records are deleted from a given table??? I have a table which contained 13.5 million records. I then deleted 6.5 million records; however, the physical file size did not change. The file size before was: 12K -rw-rw1 mysqlmysql8.7K Nov 28 2003 position_log.frm 1006M -rw-rw1 mysqlmysql 1005M Jul 13 15:42 position_log.MYD 233M -rw-rw1 mysqlmysql232M Jul 13 15:43 position_log.MYI The file size after deleting 6.5 M records is: -rw-rw1 mysqlmysql8.7K Nov 28 2003 /data1/mysql/positionresearch/position_log.frm -rw-rw1 mysqlmysql 1005M Jul 14 07:32 /data1/mysql/positionresearch/position_log.MYD -rw-rw1 mysqlmysql232M Jul 14 07:32 /data1/mysql/positionresearch/position_log.MYI [EMAIL PROTECTED] Position Research, Inc. Search engine results by research tel: (760) 480-8291 fax: (760) 480-8271 www.PositionResearch.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'mysql_real_connect' with DSN
In message [EMAIL PROTECTED] 'mysql_real_connect' with DSN Nawal Lodha [EMAIL PROTECTED] wrote: Can 'mysql_real_connect' be called using DSN name rather than giving hostname, database name, port no., etc ? Are you using ODBC? Data Source Name(DSN) is an alias of the database in ODBC. So you must use DSN with ODBC API and cannot with 'mysql_real_connect' that is MySQL API. --- OGINO Tomonori @Osaka, Japan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
do i need an index for this?
Hi, I want to find the earliest and latest times in the time column of my table internet_usage: +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | uid | int(10) unsigned | | MUL | 0 | | | time | timestamp(14)| YES | | NULL| | | ip | int(10) unsigned | | | 0 | | | urlid| int(10) unsigned | | | 0 | | | timetaken| smallint(5) unsigned | YES | | 0 | | | cs_size | int(10) unsigned | YES | | 0 | | | sc_size | int(10) unsigned | YES | | 0 | | | method_ID| tinyint(3) unsigned | | | 0 | | | action_ID| tinyint(3) unsigned | | | 0 | | | virus_ID | tinyint(3) unsigned | | | 0 | | | useragent_ID | smallint(5) unsigned | | | 0 | | +--+--+--+-+-+---+ So far there are 324936160 rows. If I do : SELECT MIN(time) as earliest, MAX(time) as latest from internet_usage; I can see the query is going to run for a long time. Do I have to create an index on time to speed this up or is there another way of doing it? Thanks for any help. JS. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql function for timestamp
JS: When I use DATE_FORMAT(), if I have a space character between DATE_FORMAT and the ( I get an error. Try writing it like: SELECT DATE_FORMAT( 20040601123456, '%Y-%m-%d' ); MySQL v4.1.2-alpha-standard Mac OS X Wes On Jul 14, 2004, at 12:20 PM, J S wrote: Thanks for your help. I would like to do it with the SQL. However I'm still having problems with the syntax below. Is this wrong? mysql SELECT DATE_FORMAT (20040601123456,'%Y-%m-%d'); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(20040601123456,'%Y-%m-%d')' at line 1 Also I tried: mysql SELECT DATE_FORMAT (time,'%Y-%m-%d %H:%i') - from internet_usage - where time2004060112; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(time,'%Y-%m-%d %H:%i') from internet_usage where time=20040601 While in PHP you can try something like this... $date_added = $row['date_added']; $date = date(M d, Y, strtotime($date_added)); echo $date; H and i do work in PHP as well, I believe, if you want to add the hour and seconds. Check out the 'date' function in PHP for info. - Eve In MySQL you can do that with: DATE_FORMAT DATE_FORMAT (column_name,'%Y-%m-%d %H:%i') -Mensaje original- De: J S [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 14 de Julio de 2004 08:26 a.m. Para: [EMAIL PROTECTED] Asunto: sql function for timestamp Hi, What SQL function do I need to convert timestamp 200406011403 into 2004-06-01 14:03 ? Thanks, JS. _ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql function for timestamp
That was it! Thanks so much. JS JS: When I use DATE_FORMAT(), if I have a space character between DATE_FORMAT and the ( I get an error. Try writing it like: SELECT DATE_FORMAT( 20040601123456, '%Y-%m-%d' ); MySQL v4.1.2-alpha-standard Mac OS X Wes On Jul 14, 2004, at 12:20 PM, J S wrote: Thanks for your help. I would like to do it with the SQL. However I'm still having problems with the syntax below. Is this wrong? mysql SELECT DATE_FORMAT (20040601123456,'%Y-%m-%d'); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(20040601123456,'%Y-%m-%d')' at line 1 Also I tried: mysql SELECT DATE_FORMAT (time,'%Y-%m-%d %H:%i') - from internet_usage - where time2004060112; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(time,'%Y-%m-%d %H:%i') from internet_usage where time=20040601 While in PHP you can try something like this... $date_added = $row['date_added']; $date = date(M d, Y, strtotime($date_added)); echo $date; H and i do work in PHP as well, I believe, if you want to add the hour and seconds. Check out the 'date' function in PHP for info. - Eve In MySQL you can do that with: DATE_FORMAT DATE_FORMAT (column_name,'%Y-%m-%d %H:%i') -Mensaje original- De: J S [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 14 de Julio de 2004 08:26 a.m. Para: [EMAIL PROTECTED] Asunto: sql function for timestamp Hi, What SQL function do I need to convert timestamp 200406011403 into 2004-06-01 14:03 ? Thanks, JS. _ _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'x' on blob field when retrieving records
Hi!
On Jul 13, [EMAIL PROTECTED] wrote:
I have a query like this
select hex(blob_field) as myField from table
When the blob field is empty ('') it returns 'x'.
i'm using ADO with Visual Basic and MyODBC 3.51.
MySQL Version is 4.0.18 and SO is Win XP Pro (also happens on Windows 2K)
If you can provide a repeatable test case, please submit a bugreport at
bugs.mysql.com
So far I was not able to repeat it:
mysql create table a (b blob);
Query OK, 0 rows affected (0.01 sec)
mysql insert a values (NULL), (''), (0), ('aaa');
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql select hex(b) from a;
++
| hex(b) |
++
| NULL |
||
| 30 |
| 616161 |
++
4 rows in set (0.01 sec)
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search one table, then display another table where keys match ?
Does access_no contain actual textual data, or is it
simply a key like a category or an integer? If you
don't need to do a fulltext search against access_no
then there is no reason to include it in your fulltext
index. You should most likely have a seperate index
for access_no in that case.
The following query assumes access_no is exactly the
same in both balloon_txt and balloon_rec for the rows
you are matching.
SELECT br.*
FROM balloon_txt bt,
balloon_rec br
/*find the matching rows from balloon_txt*/
WHERE MATCH(bt.access_no, bt.recs_txt)
AGAINST ('robin');
/*and join them to rows in balloon_rec using
the access_no column*/
AND bt.access_no = br.access_no
if access_no doesn't need to be full text indexed, you
could drop the fulltext key and add a new one just for
recs_txt and remove bt.access_no from the MATCH()
--- leegold [EMAIL PROTECTED] wrote:
If you would entertain a MYSQL/PHP, hope not too
off-topicIt's
probably not difficult to solve - but you would be
helping me
with some SQL logic.
The only way I can think of to explain what I want
to do
is to give you my working newbie MSQL/PHP code that
I'm learning
MYSQL/PHP with, and at a certain point in the code
below I'll state
exactly as I can what I want to try to do. It's
probably
quite simple but I can't get it- Thanks:
...
pre
?php
$dblink = mysql_connect ( 'localhost', guest,
password );
mysql_select_db( balloon, $dblink );
// Doing a FULLTEXT search
// Re the SELECT: I indexed both fields together, so
seemed like
// I should put them both in the MATCH...OK, it
works.
$query=SELECT * FROM balloon_txt WHERE
MATCH(access_no, recs_txt)
AGAINST ('robin');
$result = MySQL_query($query);
/
OK, right here - next below I'm gonna display/loop
$result from table
balloon_txt. But, what I really want to do is take
the result set
access_no fields from the search above and
(access_no is a Key in all
my tables) and use it to generate results (ie.
matching records) from
another table called balloon_rec and dispaly/loop
the results from
balloon_rec. So I'm searching balloon_txt, getting
results, but I want
to display matching records from another table -
balloom_rec. Is there
a way to do a join or something in the SELECT
above? Or do I process
$result? Seems a join in the SELECT above or some
SQL above is cleaner
- but not sure how(?) Thanks, Lee G.
///
while ( $row = mysql_fetch_row( $result ) ) {
for ( $i=0; $imysql_num_fields( $result ); $i++ )
{echo $row[$i] . ;}
echo\n\n\n;
}
// Close the db connection
mysql_close ( $dblink );
?
/pre
...
--
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: do i need an index for this?
In the last episode (Jul 14), J S said: I want to find the earliest and latest times in the time column of my table internet_usage: +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | time | timestamp(14)| YES | | NULL| | +--+--+--+-+-+---+ So far there are 324936160 rows. If I do : SELECT MIN(time) as earliest, MAX(time) as latest from internet_usage; I can see the query is going to run for a long time. Do I have to create an index on time to speed this up or is there another way of doing it? Most definitely. An index will make that query almost instantaneous. Mysql won't even have to hit the table at all, and just has to look at the first and last index blocks. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: 4.1 performance
Hi! On Jul 14, Hickey,Thom wrote: I was able to rerun my tests using mysqld (as opposed to safe_mysqld). I'm happy to report that the times now almost exactly match MySQL 3.23.58. It's VERY strange. It cannot have any affect on the speed. Are you sure you run the correct version of mysqld ? That is, I can suspect that either safe_mysqld used wrong .my.cnf or run wrong mysqld, or when you run mysqld directly you used wrong mysqld or it used wrong my.cnf. I could start all your tests with SELECT VERSION(); SHOW VARIABLES; - to be sure you are using correct mysqld and correct my.cnf. By the way, if you want to disable query cache you can turn it of with a command line switch :) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1 performance
Hi! On Jul 14, Lachlan Mulcahy wrote: Sergei, Thom.. I am interested in seeing this thread followed through. As developers at my work have experienced similar performance issues between 3.23.x and 4. Our database is also of similar size and a full optimize has been run. Could you provide a repeatable test case ? (if yes, you can submit it on bugs.mysql.com, instead of replying here - and be sure it will get a proper attention) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: do i need an index for this?
Creating a key will make that query execute very fast, but if that is the only reason for the key you are going to be trading quite a lot of space for the speed of one query. How often are you going to run this query? If you have 324 million rows, then that index is going to consume somewhere in the order of 2G or more of disk space. Is it worth using all that space to make one query faster? --- J S [EMAIL PROTECTED] wrote: Hi, I want to find the earliest and latest times in the time column of my table internet_usage: +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | uid | int(10) unsigned | | MUL | 0 | | | time | timestamp(14)| YES | | NULL| | | ip | int(10) unsigned | | | 0 | | | urlid| int(10) unsigned | | | 0 | | | timetaken| smallint(5) unsigned | YES | | 0 | | | cs_size | int(10) unsigned | YES | | 0 | | | sc_size | int(10) unsigned | YES | | 0 | | | method_ID| tinyint(3) unsigned | | | 0 | | | action_ID| tinyint(3) unsigned | | | 0 | | | virus_ID | tinyint(3) unsigned | | | 0 | | | useragent_ID | smallint(5) unsigned | | | 0 | | +--+--+--+-+-+---+ So far there are 324936160 rows. If I do : SELECT MIN(time) as earliest, MAX(time) as latest from internet_usage; I can see the query is going to run for a long time. Do I have to create an index on time to speed this up or is there another way of doing it? Thanks for any help. JS. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 'mysql_real_connect' with DSN
Thanks OGINO San. You are right. I am using MySQL API. So, I need to connect thro' hostname, databaseName, etc. only and not thro' the ODBC DSN. -Original Message- From: OGINO Tomonori [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 14, 2004 9:57 PM To: [EMAIL PROTECTED] Subject: Re: 'mysql_real_connect' with DSN In message [EMAIL PROTECTED] 'mysql_real_connect' with DSN Nawal Lodha [EMAIL PROTECTED] wrote: Can 'mysql_real_connect' be called using DSN name rather than giving hostname, database name, port no., etc ? Are you using ODBC? Data Source Name(DSN) is an alias of the database in ODBC. So you must use DSN with ODBC API and cannot with 'mysql_real_connect' that is MySQL API. --- OGINO Tomonori @Osaka, Japan -- 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: Weeding out duplicates
You _can_ do this in SQL, it just takes several steps. What you need to do is create a table that contains the ID values of the records you want to get rid of. If you want to keep the first record in a set of dupes try this: CREATE TABLE DeleteMe SELECT t.email, t.ID FROM yourtablenamehere t GROUP BY email HAVING count(1) 1; ALTER TABLE DeleteMe add Key(ID); This makes a full list of all IDs for all duplicate emails and puts an index on it (you may not need the index if your dupes list is small enough). But you said that you don't want to get rid of all dupes, you want to keep the first dupe in each set. Let's make another list: CREATE TABLE KeepMe SELECT email, MIN(ID) FROM DeleteMe GROUP BY email This is a list of the IDs you want to keep. Now to eliminate the keepers from the full dupes list. DELETE DeleteMe FROM DeleteMe d LEFT JOIN KeepMe k on k.id = d.id WHERE k.id is null You now have a list of all duplicate IDs except the first ones. We will use this list to clear the dupes from your original table: DELETE yourtablenamehere FROM yourtablenamehere t INNER JOIN Deleteme d ON d.id = t.id Now, all that's left to do is to clean up our working tables: DROP TABLE KeepMe, DeleteMe And you are done! Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Robert A. Rosenberg To: [EMAIL PROTECTED] [EMAIL PROTECTED]cc: Fax to: Subject: RE: Weeding out duplicates 07/10/2004 10:05 PM At 10:25 -0600 on 07/09/2004, Jonathan Duncan wrote about Re: Weeding out duplicates: Lachlan, I want to identify the entries in the table where the email addresses are the same as another entry. Whatever else is in the record does not matter to me. However, a second requirement for the query is that it show me the last duplicate instead of the first. This way I keep the first entries and remove subsequent ones. Thanks, Jonathan Duncan If you are willing to go with a PHP/MySQL solution as opposed to a pure MySQL one, try this: 1) Use that query to get a list of what Email Addresses are duplicated. 2) Now do a query with a WHERE Email=Duplicate-Email-Address and fetch the Primary Key. 3) Do a Delete by Primary Key for all rows fetched in 2 EXCEPT for those keys fetched in 1. The sequence is that you do step 1, loop though the results one at a time (step 2) doing step 3 in that loop. -- 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: do i need an index for this?
Thanks for your reply. Can I just check my syntax is right for creating the key? CREATE INDEX urlindex ON internet_usage (time); I ran this before but got an error 27 (eventhough the filesize ulimit was set to unlimited). So just wondered if I was using the wrong syntax? JS. In the last episode (Jul 14), J S said: I want to find the earliest and latest times in the time column of my table internet_usage: +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | time | timestamp(14)| YES | | NULL| | +--+--+--+-+-+---+ So far there are 324936160 rows. If I do : SELECT MIN(time) as earliest, MAX(time) as latest from internet_usage; I can see the query is going to run for a long time. Do I have to create an index on time to speed this up or is there another way of doing it? Most definitely. An index will make that query almost instantaneous. Mysql won't even have to hit the table at all, and just has to look at the first and last index blocks. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
identifying multi-byte characters / coverting
hi when ppl paste from MS Word to a text field in a form, characters like apostrophe before s (e.g. It's) in docs looks like (it looks like a comma but at the top). in a mysqldump, i can see that it's stored as #8217;. i tried editing the in a text box and when i reprint it, it's looks like it did. are there any situations where this may cause a problem? and is there any way to filter it or convert it to a single-byte char? i'm assuiming it's multibyte, coz i didn't see it in the ascii char list. thanks abs ___ALL-NEW Yahoo! Messenger - so many all-new ways to express yourself http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: do i need an index for this?
In the last episode (Jul 14), J S said: Thanks for your reply. Can I just check my syntax is right for creating the key? CREATE INDEX urlindex ON internet_usage (time); I ran this before but got an error 27 (eventhough the filesize ulimit was set to unlimited). So just wondered if I was using the wrong syntax? Getting errno 27 (as opposed to a table is full message or a signal 25/SIGXFSZ) usually means you're running Linux on a filesystem that doesn't support large file (ext2 for example), on a kernel too old to support large files, or a mysqld not compiled for large file support. Since your table is currently at least 10GB, I'm not sure why it just started complaing now :) The combination of your existing index plus this new one will bump the .MYI file over the 2GB point, but I don't think that should be a problem. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Search one table, then display another table where keys match ?
soapbox
If all you need from your query is one column, then ask for just that one
column. Don't overuse SELECT *. You wind up filling your data transfer
channels with unnecessary and unused data.
/soapbox (sorry about that, one of my pet peeves)
You can store the results of your fulltext search in a temporary table and
JOIN that to your other table. I think it will take 3 separate calls to
MySQL_query() but I am not sure (I work for a Win32 company and I don't get
to work with PHP very often).
call 1:
CREATE TEMPORARY TABLE tmpSearch
SELECT access_no FROM ballon_txt WHERE MATCH (access_no, recs_txt)
AGAINST('robin')
call 2 (this one will give you your matching records:
SELECT field list here
FROM balloon_rec r
INNER JOIN tmpSearch s
ON s.access_no = r.access_no
call 3 (I always try to clean up after myself):
DROP TABLE tmpSearch
Depending on what version of MySQL you have , you might be able to use the
first query as an unnamed view and avoid the need for a temp table:
SELECT field list here
FROM balloon_rec r
INNER JOIN (SELECT access_no FROM ballon_txt WHERE MATCH (access_no,
recs_txt) AGAINST('robin')) s
ON s.access_no = r.access_no
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
leegold
[EMAIL PROTECTED]To: mySQL mailinglist [EMAIL
PROTECTED]
.fm cc:
Fax to:
07/14/2004 10:06 Subject: Search one table, then
display another table where keys match
AM?
If you would entertain a MYSQL/PHP, hope not too off-topicIt's
probably not difficult to solve - but you would be helping me
with some SQL logic.
The only way I can think of to explain what I want to do
is to give you my working newbie MSQL/PHP code that I'm learning
MYSQL/PHP with, and at a certain point in the code below I'll state
exactly as I can what I want to try to do. It's probably
quite simple but I can't get it- Thanks:
...
pre
?php
$dblink = mysql_connect ( 'localhost', guest, password );
mysql_select_db( balloon, $dblink );
// Doing a FULLTEXT search
// Re the SELECT: I indexed both fields together, so seemed like
// I should put them both in the MATCH...OK, it works.
$query=SELECT * FROM balloon_txt WHERE MATCH(access_no, recs_txt)
AGAINST ('robin');
$result = MySQL_query($query);
/
OK, right here - next below I'm gonna display/loop $result from table
balloon_txt. But, what I really want to do is take the result set
access_no fields from the search above and (access_no is a Key in all
my tables) and use it to generate results (ie. matching records) from
another table called balloon_rec and dispaly/loop the results from
balloon_rec. So I'm searching balloon_txt, getting results, but I want
to display matching records from another table - balloom_rec. Is there
a way to do a join or something in the SELECT above? Or do I process
$result? Seems a join in the SELECT above or some SQL above is cleaner
- but not sure how(?) Thanks, Lee G.
///
while ( $row = mysql_fetch_row( $result ) ) {
for ( $i=0; $imysql_num_fields( $result ); $i++ )
{echo $row[$i] . ;}
echo\n\n\n;
}
// Close the db connection
mysql_close ( $dblink );
?
/pre
...
--
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: deleting millions of records did not change the file size
At 11:04 AM 7/14/2004, you wrote: My question in brief is why wouldn't the physical mysql files change in size if a substantial percentage of the records are deleted from a given table??? Why would it? If you delete 1 row from the middle of the table, it leaves a hole. This space is recovered when a new row is added. If MySQL changed the physical file size every time a row or a group of rows are deleted, it would slow down to a crawl. It would have to physically move all the rows up to fill the hole. If you want to get rid of the holes and shrink the file size, use Optimize Table. Manual: OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB, or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. http://dev.mysql.com/doc/mysql/en/OPTIMIZE_TABLE.html Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: do i need an index for this?
Hmmw your syntax should ork. Possibly try CREATE INDEX urlindex ON internet_usage (time(8)); W 8hich will only use the first charaters of your time field, it should make the index significantly smaller in size. Though I'm not completly sure shorting time field this way is legal, but you can always try. J S wrote: Thanks for your reply. Can I just check my syntax is right for creating the key? CREATE INDEX urlindex ON internet_usage (time); I ran this before but got an error 27 (eventhough the filesize ulimit was set to unlimited). So just wondered if I was using the wrong syntax? JS. In the last episode (Jul 14), J S said: I want to find the earliest and latest times in the time column of my table internet_usage: +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | time | timestamp(14)| YES | | NULL| | +--+--+--+-+-+---+ So far there are 324936160 rows. If I do : SELECT MIN(time) as earliest, MAX(time) as latest from internet_usage; I can see the query is going to run for a long time. Do I have to create an index on time to speed this up or is there another way of doing it? Most definitely. An index will make that query almost instantaneous. Mysql won't even have to hit the table at all, and just has to look at the first and last index blocks. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql growing pains, 4 days to create index on one table!
Lopez David E-r9374c wrote: matt 1) inserts using this format is much faster: INSERT INTO table (col1, col2) VALUES (val1,val2), (val3,val4) is much faster then single row insert. My experience is 2.5 hrs vs.. 36 hrs. 2) The PACK_KEYS=1 may be hurting you. I've never used it. 3) There may be a cache somewhere that's to small. You'll have to do some digging in this area. 4) dup key ignore - what does that mean exactly? 5) what is your OS rev, mysql rev. Please post any suggestions that you find valuable so we can all learn.. david If I understand it, pack_keys should help if your disk IO limited vs cpu limited, cpu is ususally near idle. I increased the cache sizes, helped a little, but not much delay_key_write=ALL key_buffer_size=1000M read_buffer_size=512M record_buffer=512M What would the syntax for that type of insert be? I have a table with 30,000 records, I need to insert them into the main table with millions of records, I thought that insert into table select * from 2nd table would be the fastest way. insert ignore will not insert a record, if it violates the primary key, I do this to keep duplicate records out of the system windows 2003 and 2000 servers, mysql-nt 4.0.16 I tried the newer versions, but found bugs on all of them, I submitted them to the bug system. I believe my bottleneck is reading the data to ensure the primary key is not violated, I see lots of read IO, but little write IO Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql growing pains, 4 days to create index on one table!
Egor Egorov wrote: Are you running this under Microsoft Windows? Yes, windows 2k and 2003, mysql-nt 4.0.16 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql growing pains, 4 days to create index on one table!
[EMAIL PROTECTED] wrote: You may want more indexes but you might be getting killed because you already have too many. To test - try loading into a table without indexes and see if it makes a difference. At the very least - check to see if the primary index which starts with 'dic' can make your special 'dic' index superfluous. If write speed is a bottleneck you might consider Raid-1 instead of Raid-5. Reading lots of rows via index is a killer. Depending on your hardware it may be cheaper to table scan 50 rows than to read 1 via index. However, this requires partitioning of the data based on some column which appears in every query and acts as an initial filter. If you are lucky enough to be in that situation - consider a MERGE table. These tables are merged, the total table size is huge, on this particular table, it's , 45,449,534 rows, however, all the merge tables combined are 258,840,305 records perhaps I should reorder the pimary key, putting the longest most unique record up front, and the least unique at the end, would that speed up the key check? I can tell that almost everything is read IO, very little write IO -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hold System
Hi, I am currently working on a hold system. The system will allow Coordinators to hold international students to place them with American Host Families. I am having a hard time coming up with a good MySQL table design for this process. I have a table now that looks something like: CREATE TABLE `hold_tracker` ( `id` int(11) NOT NULL auto_increment, `STUDENT_ID` int(11) NOT NULL default '0', `USER_ID` int(11) NOT NULL default '0', `valid` char(1) NOT NULL default '1', `date_held` bigint(20) NOT NULL default '0', `date_created` bigint(20) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=MyISAM AUTO_INCREMENT=12 ; This works okay, for just keep track of who is holding the student. There are some restrictions: 1. A Coordinator may only hold 3 students at a time. (Program logic, this is done already.) 2. A Coordinator can only hold a student while in the #1 position for a certain amount of time. (I don't have a problem with calculating the time.The problems are: A. What do I do with the record once there hold has expired? B. Also what do I do with the other records that are holding so they get adequate hold times in the number one position?) 3. There can only be 3 holds per student. (Program logic, this is done already) I can come up with some solutions for Restriction #2, but I feel that they are kind of sloppy with the current table schema. I also think that a better table schema would be in order here. sloppy_way After time has expired or the Coordinator has canceled the hold. I can turn the 'valid' field of the record to zero. And update the next Coordinators 'date_held' to the current date. Giving them adequate time to have there hold. /sloppy_way Has anyone else built a hold system before that might be able to help me out with some pointers? I hope this makes sense. If not please ask for clarification. As always I appreciate any assistance. Thank you, Justin Palmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: do i need an index for this?
In the last episode (Jul 14), John Larsen said: Hmmw your syntax should ork. Possibly try CREATE INDEX urlindex ON internet_usage (time(8)); W 8hich will only use the first charaters of your time field, it should make the index significantly smaller in size. A timestamp field is only 4 bytes as it is, and I don't think you can index part of it. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql growing pains, 4 days to create index on one table!
Indexes can generate vast amounts of random i/o. Because of rotational latency, random i/o can really slow you down, especially if you are using IDE or SATA disks because they can't do tagged queueing like SCSI disks can. If you have the budget for it, I would consider getting some solid state disks. Because they have extremely low latency you will be able to get full i/o bandwidth on your reads. If you can't afford those, consider adding more disks to your RAID array so that you can spread the reads over more spindles, which will help performance. --- matt ryan [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: You may want more indexes but you might be getting killed because you already have too many. To test - try loading into a table without indexes and see if it makes a difference. At the very least - check to see if the primary index which starts with 'dic' can make your special 'dic' index superfluous. If write speed is a bottleneck you might consider Raid-1 instead of Raid-5. Reading lots of rows via index is a killer. Depending on your hardware it may be cheaper to table scan 50 rows than to read 1 via index. However, this requires partitioning of the data based on some column which appears in every query and acts as an initial filter. If you are lucky enough to be in that situation - consider a MERGE table. These tables are merged, the total table size is huge, on this particular table, it's , 45,449,534 rows, however, all the merge tables combined are 258,840,305 records perhaps I should reorder the pimary key, putting the longest most unique record up front, and the least unique at the end, would that speed up the key check? I can tell that almost everything is read IO, very little write IO -- 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: Hold System
I don't see how hold_tracker coordinates a student with a coordinator (is the coordinator the user_Id ?) What is this #1 position you speak of? I don't see anything like a priority or ranking field in the table you posted. Maybe if you posted your entire data structure, things would make better sense. It sounds like your system equates to a reservation system for a resource limited to 3 concurrent users with each user being limited to consuming only 3 resources at a time. (Equating this to your model, your coordinators would be the users and the students would be the resources). You ask what should happen to the expired holds I guess that depends on if you need to keep that information (audit log, reservation history, ...) or not. I would expect that if a coordinator has two holds then he would have two records in the holds table (hold_tracker?). If a hold is expired you can either copy it to another table (to archive it) or delete it. I mentally model the system like this: CREATE TABLE Student( ID int not null auto_increment primary key, FirstName varchar(30) not null, LastName varchar(30) not null ... other student fields ... ) CREATE TABLE Coordinator( ID int not null auto_increment primary key, FirstName varchar(30) not null, LastName varchar(30) not null ... other coordinator fields ... ) CREATE TABLE Hold( ID int not null auto_increment, Student_ID int not null, Coordinator_ID int not null, DateCreated datetime, Primary Key(Student_ID, Coordinator_ID) Key(ID), Key(Coordinator_ID) ) I defined the primary key that way so that each Student could be assigned to any Coordinator only once. I had to add an index the ID field because auto_increment requires it. I added an index for the Coordinator_ID so that you could join Hold to either table rather quickly. To determine how many Holds a Student participates in: SELECT count(1) as numholds FROM Hold WHERE Student_ID = some number Or if you want to go by name: SELECT count(1) as numholds FROM Hold h INNER JOIN Student s ON s.ID=h.Student_ID AND s.FirstName = 'Joe' AND s.LastName = 'Brown' Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Justin Palmer [EMAIL PROTECTED]To: [EMAIL PROTECTED] g cc: Fax to: 07/14/2004 03:37 Subject: Hold System PM Please respond to justin Hi, I am currently working on a hold system. The system will allow Coordinators to hold international students to place them with American Host Families. I am having a hard time coming up with a good MySQL table design for this process. I have a table now that looks something like: CREATE TABLE `hold_tracker` ( `id` int(11) NOT NULL auto_increment, `STUDENT_ID` int(11) NOT NULL default '0', `USER_ID` int(11) NOT NULL default '0', `valid` char(1) NOT NULL default '1', `date_held` bigint(20) NOT NULL default '0', `date_created` bigint(20) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=MyISAM AUTO_INCREMENT=12 ; This works okay, for just keep track of who is holding the student. There are some restrictions: 1. A Coordinator may only hold 3 students at a time. (Program logic, this is done already.) 2. A Coordinator can only hold a student while in the #1 position for a certain amount of time. (I don't have a problem with calculating the time.The problems are: A. What do I do with the record once there hold has expired? B. Also what do I do with the other records that are holding so they get adequate hold times in the number one position?) 3. There can only be 3 holds per student. (Program logic, this is done already) I can come up with some solutions for Restriction #2, but I feel that they are kind of sloppy with the current table schema. I also think that a better table schema would be in order here. sloppy_way After time
mysql libs and multiple hostnames
Hi, I have a quick question about how programs linked against the mysql C libraries handle the following: -assume three mysql hosts, say 10.0.0.2, 10.0.0.3, and 10.0.0.4 -assume a dns name db.example.com that returns the following: [EMAIL PROTECTED] host db.example.com db.example.com has address 10.0.0.2 db.example.com has address 10.0.0.3 db.example.com has address 10.0.0.4 If my client program repeatedly connects to db.example.com and my nameserver round-robins through those IPs, will the client also keep cycling through those? What is the behaviour if one of those hosts does not respond? Will the client application then try the next one? Thanks, Charles ___ Charles Sprickman NetEng/SysAdmin Bway.net - New York's Best Internet - www.bway.net [EMAIL PROTECTED] - 212.655.9344 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: do i need an index for this?
In the last episode (Jul 14), J S said: Thanks for your reply. Can I just check my syntax is right for creating the key? CREATE INDEX urlindex ON internet_usage (time); I ran this before but got an error 27 (eventhough the filesize ulimit was set to unlimited). So just wondered if I was using the wrong syntax? Getting errno 27 (as opposed to a table is full message or a signal 25/SIGXFSZ) usually means you're running Linux on a filesystem that doesn't support large file (ext2 for example), on a kernel too old to support large files, or a mysqld not compiled for large file support. Since your table is currently at least 10GB, I'm not sure why it just started complaing now :) The combination of your existing index plus this new one will bump the .MYI file over the 2GB point, but I don't think that should be a problem. -- I am using AIX and the filesystem is large file enabled. I'm using the mysql max db binary downloaded off the mysql site as well. Do you know if that would have large file support compiled in? I don't know why it's complaining about the size because I'm running a backup of the database at the moment and that's already 20GB on the same filesystem. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrade from 3.23 to 4.0 - mysql.sock error
Greetings all, I recently upgraded from mysql 3.23 to 4.0 on a Fedora box. I moved the old installation to a directory called old_mysql in my home directory. I can start the new installation using mysqld_safe . Now, when I try to do something like: /new/install/mysqladmin -u root -p version it says it can't connect because there is no /tmp/mysql.sock. And that is the case. The mysql.sock file is in /var/lib/mysql/mysql.sock When I do something like: old_mysql/mysqladmin -u root -p version it can connect and, in fact, I can connect to the new mysql server by using old_mysql/mysql -u root -p Should I just go on using the old clients? Why is there no /tmp/mysql.sock? How can I make mysql create one? Help! and Thanks!!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Expensive InnoDB queries crash mysql daemon
Hello All, I hope someone can shed some light on this problem... This concerns large InnoDB tables (having on the order of millions of rows). When I run ALTER TABLE (for example, to change the default column value) or UPDATE or DELETE queries that affect many rows, mysqld-max crashes and apparently gets restarted by mysqld_safe. This also seems to hang the web server that runs on the same machine and maintains connections with the database. Of course, the query does not complete. I'm using mysql version 4.0.16 on Red Hat 9 Linux, kernel 2.4.20. Thanks in advance, Sergei
Any select with a large result set locks all other threads until it completes. Any suggestions?
Hi, I am testing mysql on a large database (10GB). When I post a large select request via myodbc all other process threads lock until that thread completes. I am not locking any tables. How do I stop a read only select statement with a large result set from locking all the other threads until it completes? I am using version mysql 3.23.56 with odbc 2.5 and mysql tables. All the best. John Nairn Readysell Pty Ltd
Comparisons Through VFP not working properly
I'm using SqlPassthrough In VFP 8.0 Ive got a Sql statement that looks like this. This following fails: Update User Set Active = True Where UserId = ?pnUserId pnUserId is 222 in this instance. The following works fine: Update User Set Active = True Where UserId = 222 The workaround is to cast the parameter ?pnUSerId to a signed integer but I'm trying to understand why this is happening. I created a table from the parameter and it stored it in a table as a double. It's probably storing it as 221.998 which is why the comparison is failing. Has anyone seen this behavior? TIA Dan
What format for text fields?
I recently started experimenting with the text field. It generally works OK, but I have a question about pasting in the text. It seems a little awkward in phpMyAdmin, and I generally wind up with everything mushed together like this: p clsas=firstspan class=dropcapV/spanermont is nicknamed the #8220;Green Mountain State.#8221;/ppIt is a beautiful place to live./p It would be easier to proofread and modify if it looked like this: p clsas=firstspan class=dropcapV/spanermont is nicknamed the #8220;Green Mountain State.#8221;/p pIt is a beautiful place to live./p I was just curious how most of you do it. Do you just dump in your text, or do you go to extra lengths to make sure each paragraph begins on a new line in your database? Whay code do you use for line breaks, so it looks neat when you preview your source code on a web page? Thanks. __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrade from 3.23 to 4.0 - mysql.sock error
The correct way to deal with this is to edit /etc/my.cnf and, in the mysqld section set the location of the socket. [mysqld] socket = /tmp/mysql.sock if there are other systems expecting it to be at the new location, a simple end run might be to create a link at /tmp/mysql.sock - /var/lib/mysql/mysql.sock. - michael dykman - [EMAIL PROTECTED] Jough P wrote: Greetings all, I recently upgraded from mysql 3.23 to 4.0 on a Fedora box. I moved the old installation to a directory called old_mysql in my home directory. I can start the new installation using mysqld_safe . Now, when I try to do something like: /new/install/mysqladmin -u root -p version it says it can't connect because there is no /tmp/mysql.sock. And that is the case. The mysql.sock file is in /var/lib/mysql/mysql.sock When I do something like: old_mysql/mysqladmin -u root -p version it can connect and, in fact, I can connect to the new mysql server by using old_mysql/mysql -u root -p Should I just go on using the old clients? Why is there no /tmp/mysql.sock? How can I make mysql create one? Help! and Thanks!!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Any select with a large result set locks all other threads un til it completes. Any suggestions?
What type of select is this? Is the application and the database on the same machine? Is this query build a large temp table? -Original Message- From: John Nairn To: [EMAIL PROTECTED] Sent: 7/14/04 7:11 PM Subject: Any select with a large result set locks all other threads until it completes. Any suggestions? Hi, I am testing mysql on a large database (10GB). When I post a large select request via myodbc all other process threads lock until that thread completes. I am not locking any tables. How do I stop a read only select statement with a large result set from locking all the other threads until it completes? I am using version mysql 3.23.56 with odbc 2.5 and mysql tables. All the best. John Nairn Readysell Pty Ltd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slight confusion about mysql_fetch_length() API - Internals
Hello, Probably this mail should go to the internals list :) I was looking into MySQL Client libaray and I could figure out the DATA are kept as linked list of MYSQL_ROW structure i.e. in MYSQL_ROWS being one node. Now MYSQL_ROW is typedefed as char** that is array of strings. Looking into the mysql_fetch_lengths() API code, the lengths are calculated from the offset between pointers. Forgive my C knowledge, but I was unable to understand how we are calculating thru offset. As far as I can understand this is what is happening: A MYSQL_ROW is allocated with field_count number of indexes. Each index is then again my_alloced() to store information about each each and so on. i.e. myrow = malloc ( sizeof (char*), field_count ); myrow[0] = malloc ( sizeof(char), field_data ); myrow[1] = malloc ( sizeof(char), field_data ); myrow[2] = malloc ( sizeof(char), field_data ); In this case how can we calculate lengths using offsets? Or I am missing something very stupid. A little clarity will do hell lot of good to me :) Any pointers... Regards Karam __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrade from 3.23 to 4.0 - mysql.sock error
Okay, so what I did to solve this was: ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock That seems like a bit of a hack though. The my.cnf file has this line: socket=/var/lib/mysql/mysql.sock Anyone know why it's looking in /etc ? On Jul 14, 2004, at 4:53 PM, Jough P wrote: Greetings all, I recently upgraded from mysql 3.23 to 4.0 on a Fedora box. I moved the old installation to a directory called old_mysql in my home directory. I can start the new installation using mysqld_safe . Now, when I try to do something like: /new/install/mysqladmin -u root -p version it says it can't connect because there is no /tmp/mysql.sock. And that is the case. The mysql.sock file is in /var/lib/mysql/mysql.sock When I do something like: old_mysql/mysqladmin -u root -p version it can connect and, in fact, I can connect to the new mysql server by using old_mysql/mysql -u root -p Should I just go on using the old clients? Why is there no /tmp/mysql.sock? How can I make mysql create one? Help! and 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]
#sql-... files
Greetings All, I see some files in the data dir with names like #sql-85d_4.MYI. Some of these are pretty old. Are these the temp tables that didn't get deleted properly? Is it OK to shut down the server and delete them? Thanks, Sergei
Re: Expensive InnoDB queries crash mysql daemon
Sergei, please run CHECK TABLE on the tables, and check if it prints something to the .err log. Also, resolve the stack trace printed by the crashing mysqld, as explained in the manual. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Sergei Skarupo [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, July 15, 2004 1:50 AM Subject: Expensive InnoDB queries crash mysql daemon --_=_NextPart_001_01C469F5.1B46DF30 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hello All, =20 I hope someone can shed some light on this problem... =20 This concerns large InnoDB tables (having on the order of millions of = rows). =20 When I run ALTER TABLE (for example, to change the default column value) = or UPDATE or DELETE queries that affect many rows, mysqld-max crashes = and apparently gets restarted by mysqld_safe. This also seems to hang = the web server that runs on the same machine and maintains connections = with the database. Of course, the query does not complete. =20 I'm using mysql version 4.0.16 on Red Hat 9 Linux, kernel 2.4.20. =20 Thanks in advance, =20 Sergei --_=_NextPart_001_01C469F5.1B46DF30-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
