Mysql+apache Optimization.
Hello All, Redhat 9 (shrike) Mysql 4.0.15 Apache 1.3.29 PHP 4.3.3 Server Conf [4 CPUs] Dual Xeon 2.4 1GB RAM 73GB SCSI, 80GB IDE HD No of Sites : only 2 (which are very Busy and mysql intensive ) Http.conf = Timeout 300 KeepAlive On MaxKeepAliveRequests 120 KeepAliveTimeout 10 MinSpareServers 10 MaxSpareServers 20 StartServers 5 MaxClients 512 MaxRequestsPerChild 1000 my.cnf == [mysqld] set-variable = max_connections=700 safe-show-database key_buffer = 384M myisam_sort_buffer_size = 64M join_buffer_size = 1M read_buffer_size = 1M sort_buffer_size = 1M table_cache = 512 thread_cache_size = 1500 wait_timeout = 10 interactive_timeout=10 connect_timeout = 10 max_allowed_packet = 16M max_connect_errors = 10 thread_concurrency= 8 query_cache_limit = 2M query_cache_size = 32M query_cache_type = 1 skip-innodb log-slow-queries = /var/log/mysql_slow_query_log long_query_time = 5 [mysqld_safe] err-log=/var/log/mysqld.log open_files_limit = 8192 [mysqldump] quick max_allowed_packet = 16M [isamchk] key_buffer = 64M sort_buffer_size = 64M read_buffer = 16M write_buffer = 16M [myisamchk] key_buffer = 64M sort_buffer_size = 64M read_buffer = 16M write_buffer = 16M Turck-MMCache Enabled (Zend Extension) top 05:46:25 up 12 days, 17:35, 3 users, load average: 7.46, 9.02, 10.76 509 processes: 501 sleeping, 6 running, 1 zombie, 1 stopped CPU0 states: 64.2% user 19.1% system0.1% nice 0.0% iowait 16.0% idle CPU1 states: 64.0% user 19.1% system0.0% nice 0.0% iowait 16.3% idle CPU2 states: 60.3% user 23.1% system0.0% nice 0.0% iowait 16.0% idle CPU3 states: 60.2% user 20.5% system0.0% nice 0.0% iowait 18.3% idle Mem: 1030244k av, 912116k used, 118128k free, 0k shrd, 187872k buff 534216k actv, 49792k in_d, 18180k in_c Swap: 2096440k av, 132220k used, 1964220k free 441464k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 12228 mysql 15 0 23588 20M 1444 S99.9 2.0 56:42 3 mysqld 3419 root 15 0 2676 1684 1616 S 7.0 0.1 13:57 1 httpd Can Anyone please tell me why I am not able to get the Load Down and the site Load Faster.The server load is always above 5.00 Thanks in Advance. Vishal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Convert query from v4 syntax to v3
Hello, I have a query that executes well when run on MySQL-4.x, but not 3.23.x: SELECT popbox.local_part, popbox.password_hash, popbox.domain_name, CONCAT(domain.path,'/',popbox.mbox_name) AS path FROM popbox JOIN domain USING (domain_name) I would like to make this query run on a 3.23.58 server. I have tried SELECT popbox.local_part, popbox.password_hash, popbox.domain_name, CONCAT(domain.path,'/',popbox.mbox_name) AS path FROM popbox JOIN domain ON popbox.domain_name=domain.domain_name ..but I still end up with error. Of course I am lost about the syntax now ;) Basically, the problem begings with the USING... -Wash -- +==+ |\ _,,,---,,_ | Odhiambo Washington[EMAIL PROTECTED] Zzz /,`.-'`'-. ;-;;,_ | Wananchi Online Ltd. www.wananchi.com |,4- ) )-,_. ,\ ( `'-'| Tel: +254 20 313985-9 +254 20 313922 '---''(_/--' `-'\_) | GSM: +254 722 743223 +254 733 744121 +==+ Whatever the missing mass of the universe is, I hope it's not cockroaches! -- Mom smime.p7s Description: S/MIME cryptographic signature
MySQL+Apache Optimization
Can Anyone please tell me why I am not able to get the Load Down and the site Load Faster.The server load is always above 5.00 Thanks in Advance. Vishal. Try the following changes: Apache: Turn KeepAlives off + increase your MaxRequestsPerChild. Apache is probably spending too much time recreating httpd processes. (I presume Apache MySQL are running on the same box). Also are your using mysql_connect() or pconnects? MySQL: Try increasing your table cache. Does your thread cache need to be that high? Maybe you should increase the thread concurrency first. According to the my.cnf, you can increase this based on the amount of CPUs, you start off by setting it to 24-32? Your max_connections settings is high and that will take up a fair amount of file descriptors. I suspect your table cache isn't big enough (try 2048 as a starting point, see http://www.mysql.com/doc/en/Table_cache.html for more info) (Not intending to hijack this thread), but I'm going through a similar issue with my Dual Xeon, 6GB RAM and RAID5 SCSI, but I'm running out of file-descriptors and max_connections=520 I hope my suggestions are useful and it'll be interesting to see whether they help... httpd.conf === KeepAlive Off MaxRequestsPerChild 9000 my.cnf == [mysqld] table_cache = 2048 thread_cache_size = 256 thread_concurrency= 32 _ 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: MySQL+Apache Optimization
Hello, Thank you for the Reply, I'll follow you suggestions and will post the results here, Also I use connect only and not aothers. Thank you again. ;) Vishal. - Original Message - From: my5ql _ [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 2:06 PM Subject: MySQL+Apache Optimization Can Anyone please tell me why I am not able to get the Load Down and the site Load Faster.The server load is always above 5.00 Thanks in Advance. Vishal. Try the following changes: Apache: Turn KeepAlives off + increase your MaxRequestsPerChild. Apache is probably spending too much time recreating httpd processes. (I presume Apache MySQL are running on the same box). Also are your using mysql_connect() or pconnects? MySQL: Try increasing your table cache. Does your thread cache need to be that high? Maybe you should increase the thread concurrency first. According to the my.cnf, you can increase this based on the amount of CPUs, you start off by setting it to 24-32? Your max_connections settings is high and that will take up a fair amount of file descriptors. I suspect your table cache isn't big enough (try 2048 as a starting point, see http://www.mysql.com/doc/en/Table_cache.html for more info) (Not intending to hijack this thread), but I'm going through a similar issue with my Dual Xeon, 6GB RAM and RAID5 SCSI, but I'm running out of file-descriptors and max_connections=520 I hope my suggestions are useful and it'll be interesting to see whether they help... httpd.conf === KeepAlive Off MaxRequestsPerChild 9000 my.cnf == [mysqld] table_cache = 2048 thread_cache_size = 256 thread_concurrency= 32 _ 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+Apache Optimization
Hello , What are Questions in Mysql , I mean is there something know as Questions in Mysql, I got a Script in PHP called as testload.php and it shows me this output : total processes are 55 Mysql Status is Uptime: 1839 Threads: 55 Questions: 175421 == What does this Stands For ??? Slow queries: 0 Opens: 142 Flush tables: 1 Open tables: 136 Queries per second avg: 95.389 Any comments will appreciated. Thank you, Vishal. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 2:35 PM Subject: Re: MySQL+Apache Optimization Hello, Thank you for the Reply, I'll follow you suggestions and will post the results here, Also I use connect only and not aothers. Thank you again. ;) Vishal. - Original Message - From: my5ql _ [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 2:06 PM Subject: MySQL+Apache Optimization Can Anyone please tell me why I am not able to get the Load Down and the site Load Faster.The server load is always above 5.00 Thanks in Advance. Vishal. Try the following changes: Apache: Turn KeepAlives off + increase your MaxRequestsPerChild. Apache is probably spending too much time recreating httpd processes. (I presume Apache MySQL are running on the same box). Also are your using mysql_connect() or pconnects? MySQL: Try increasing your table cache. Does your thread cache need to be that high? Maybe you should increase the thread concurrency first. According to the my.cnf, you can increase this based on the amount of CPUs, you start off by setting it to 24-32? Your max_connections settings is high and that will take up a fair amount of file descriptors. I suspect your table cache isn't big enough (try 2048 as a starting point, see http://www.mysql.com/doc/en/Table_cache.html for more info) (Not intending to hijack this thread), but I'm going through a similar issue with my Dual Xeon, 6GB RAM and RAID5 SCSI, but I'm running out of file-descriptors and max_connections=520 I hope my suggestions are useful and it'll be interesting to see whether they help... httpd.conf === KeepAlive Off MaxRequestsPerChild 9000 my.cnf == [mysqld] table_cache = 2048 thread_cache_size = 256 thread_concurrency= 32 _ 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL+Apache Optimization
Questions - Number of queries sent to the server. See http://www.mysql.com/doc/en/SHOW_STATUS.html for more info MySQL user From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: MySQL+Apache Optimization Date: Wed, 7 Jan 2004 15:12:19 +0530 Hello , What are Questions in Mysql , I mean is there something know as Questions in Mysql, I got a Script in PHP called as testload.php and it shows me this output : total processes are 55 Mysql Status is Uptime: 1839 Threads: 55 Questions: 175421 == What does this Stands For ??? Slow queries: 0 Opens: 142 Flush tables: 1 Open tables: 136 Queries per second avg: 95.389 Any comments will appreciated. Thank you, Vishal. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 2:35 PM Subject: Re: MySQL+Apache Optimization Hello, Thank you for the Reply, I'll follow you suggestions and will post the results here, Also I use connect only and not aothers. Thank you again. ;) Vishal. - Original Message - From: my5ql _ [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 2:06 PM Subject: MySQL+Apache Optimization Can Anyone please tell me why I am not able to get the Load Down and the site Load Faster.The server load is always above 5.00 Thanks in Advance. Vishal. Try the following changes: Apache: Turn KeepAlives off + increase your MaxRequestsPerChild. Apache is probably spending too much time recreating httpd processes. (I presume Apache MySQL are running on the same box). Also are your using mysql_connect() or pconnects? MySQL: Try increasing your table cache. Does your thread cache need to be that high? Maybe you should increase the thread concurrency first. According to the my.cnf, you can increase this based on the amount of CPUs, you start off by setting it to 24-32? Your max_connections settings is high and that will take up a fair amount of file descriptors. I suspect your table cache isn't big enough (try 2048 as a starting point, see http://www.mysql.com/doc/en/Table_cache.html for more info) (Not intending to hijack this thread), but I'm going through a similar issue with my Dual Xeon, 6GB RAM and RAID5 SCSI, but I'm running out of file-descriptors and max_connections=520 I hope my suggestions are useful and it'll be interesting to see whether they help... httpd.conf === KeepAlive Off MaxRequestsPerChild 9000 my.cnf == [mysqld] table_cache = 2048 thread_cache_size = 256 thread_concurrency= 32 _ 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ 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]
max length of primary key
Hi, I have a problem when creating a table: CREATE TABLE test ( test_key varchar(255) NOT NULL default '', test_value text NOT NULL, PRIMARY KEY (test_key) ) TYPE=MYISAM; It then report error: mySQL Error: Specified key was too long. Max key length is 500 Why the varchar exceeded 500? I have a set-variable= default-character-set=utf8 in my.cnf, maybe this is the cause of error? And is there way to increase this 500 value? Or I must use InnoDB then? Mysql 4.1.1, RedHat 9 Thanks. Francis Mak
How to find if the value returned is numeric
Hi all, as the subject of the mail says, is there any function to apply in a query, that finds out if the value returned from the database is numeric? thanx for help in advance Nitin
Re: max length of primary key
Hi, I have a problem when creating a table: CREATE TABLE test ( test_key varchar(255) NOT NULL default '', test_value text NOT NULL, PRIMARY KEY (test_key) ) TYPE=MYISAM; It then report error: mySQL Error: Specified key was too long. Max key length is 500 Why the varchar exceeded 500? I have a set-variable= default-character-set=utf8 in my.cnf, maybe this is the cause of error? And is there way to increase this 500 value? Or I must use InnoDB then? Apparently, the 500 number is bytes. Assuming UTF8 takes at least two bytes/character, then yes, your VARCHAR(255) takes up more than 500 bytes. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL+Apache Optimization
Hello all, Guys Where will i Find out If Mysql database is Optimized or Not, I have read the Documentation given on Mysql.com and also checked the database which tells that It has been Optimized well, Actually When I See the Mysql processlists it put all the Update Statement and Select Count(col1) from table name statements, Only one table is Heavily used. I also thought of Killing Mysql connections after a Specific time, where will i configure that ??? Thanks for any comments. Vishal. - Original Message - From: MySQL User Bob [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 3:25 PM Subject: Re: MySQL+Apache Optimization Questions - Number of queries sent to the server. See http://www.mysql.com/doc/en/SHOW_STATUS.html for more info MySQL user From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: MySQL+Apache Optimization Date: Wed, 7 Jan 2004 15:12:19 +0530 Hello , What are Questions in Mysql , I mean is there something know as Questions in Mysql, I got a Script in PHP called as testload.php and it shows me this output : total processes are 55 Mysql Status is Uptime: 1839 Threads: 55 Questions: 175421 == What does this Stands For ??? Slow queries: 0 Opens: 142 Flush tables: 1 Open tables: 136 Queries per second avg: 95.389 Any comments will appreciated. Thank you, Vishal. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 2:35 PM Subject: Re: MySQL+Apache Optimization Hello, Thank you for the Reply, I'll follow you suggestions and will post the results here, Also I use connect only and not aothers. Thank you again. ;) Vishal. - Original Message - From: my5ql _ [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 2:06 PM Subject: MySQL+Apache Optimization Can Anyone please tell me why I am not able to get the Load Down and the site Load Faster.The server load is always above 5.00 Thanks in Advance. Vishal. Try the following changes: Apache: Turn KeepAlives off + increase your MaxRequestsPerChild. Apache is probably spending too much time recreating httpd processes. (I presume Apache MySQL are running on the same box). Also are your using mysql_connect() or pconnects? MySQL: Try increasing your table cache. Does your thread cache need to be that high? Maybe you should increase the thread concurrency first. According to the my.cnf, you can increase this based on the amount of CPUs, you start off by setting it to 24-32? Your max_connections settings is high and that will take up a fair amount of file descriptors. I suspect your table cache isn't big enough (try 2048 as a starting point, see http://www.mysql.com/doc/en/Table_cache.html for more info) (Not intending to hijack this thread), but I'm going through a similar issue with my Dual Xeon, 6GB RAM and RAID5 SCSI, but I'm running out of file-descriptors and max_connections=520 I hope my suggestions are useful and it'll be interesting to see whether they help... httpd.conf === KeepAlive Off MaxRequestsPerChild 9000 my.cnf == [mysqld] table_cache = 2048 thread_cache_size = 256 thread_concurrency= 32 _ 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ 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 5 Stored Procedure
Hello I've installed Mysql 5 and have created a database. When I try and create a stored procedure (using the example in the help) it fails. The online help states that there should be a table called 'procs' in the database for stored procedures to work. I can't see this table. The help says this should have been created as part of the installation. Any ideas as to why this is and what I can do? Thanks - Email provided by http://www.ntlhome.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql slave trails behind the master
Many times during higher load periods one of our slaves' replication is slower than real-time and the slave can get behind several minutes. 94211 system user NULLConnect 519 Has read all relay log; waiting for the I/O slave thread to update it In this case it's 519 seconds behind. When I issue a SLAVE STOP followed by a SLAVE START the slave catches up its lag really fast. Can anyone hint me on why this is happening? Regards, Mikael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: store procedure in MySQL 5
Ulises, I downloaded the 5.0 binary for windows and installed it, but I can't create store procedure using MySQL Control Center, does the 5.0 alpha-binary have store procedure feature?. 5.0 has stored procedures, and it's documented here: http://www.mysql.com/doc/en/Stored_Procedures.html In MySQL Control Center, you could use the Query window to create a stored procedure (STRG+Q, or click on the button labeled SQL). Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Timestamp Problems
Hello, I am having problems with columns timestamp, what it happens is the following: I created the following table using timestamp and inserted data in this table: create table con(cod integer not null primary key auto_increment, cod_access integer, hr_con timestamp(14), hr_descon timestamp(14), status char); insert into con values(null,1,'2004010712','20040107120030','D'); when executing select in the table, the data are correct select * from con; ++---+-+--+--+ | cod | cod_access | hr_con | hr_descon | status | +-+--+-+--+--+ | 1 | 1 | 2004010712 | 20040107120030 | D | +-+--+--+--+-+ 1 row in set (0.01 sec) then I make one update in the column hr_descon update con set hr_descon='20040107120100'; there it is the problem, when bringing up to date the column hr_descon for the value informed in update, the column hr_con is brought up to date automatically for current date/time select * from con; select * from con; ++---+-+--+--+ | cod | cod_access | hr_con | hr_descon | status | +-+--+-+--+--+ | 1 | 1 | 20040107101056 | 20040107120100 | D| +-+--+--+--+-+ 1 row in set (0.01 sec) I am using Operational System Solaris 9 on Sparc Platform and I tested in versions 3.23.47, 3.23.58 and 4.0.14 of mysql. If somebody to know what happens please helps I. Thanks!!!
Re: Timestamp Problems
Hi Leandro, It is absolutly correct. Please read in doc about timestamp data type http://www.mysql.com/doc/en/DATETIME.html The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. Automatic updating of the first TIMESTAMP column occurs under any of the following conditions: a.. The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement. b.. The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.) c.. You explicitly set the TIMESTAMP column to NULL. TIMESTAMP columns other than the first may also be set to the current date and time. Just set the column to NULL or to NOW(). You can set any TIMESTAMP column to a value different from the current date and time by setting it explicitly to the desired value. This is true even for the first TIMESTAMP column. You can use this property if, for example, you want a TIMESTAMP to be set to the current date and time when you create a row, but not to be changed whenever the row is updated later: a.. Let MySQL set the column when the row is created. This will initialize it to the current date and time. b.. When you perform subsequent updates to other columns in the row, set the TIMESTAMP column explicitly to its current value. On the other hand, you may find it just as easy to use a DATETIME column that you initialize to NOW() when the row is created and leave alone for subsequent updates. Best regards, Mikhail. - Original Message - From: Leandro Saggin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 2:12 PM Subject: Timestamp Problems Hello, I am having problems with columns timestamp, what it happens is the following: I created the following table using timestamp and inserted data in this table: create table con(cod integer not null primary key auto_increment, cod_access integer, hr_con timestamp(14), hr_descon timestamp(14), status char); insert into con values(null,1,'2004010712','20040107120030','D'); when executing select in the table, the data are correct select * from con; ++---+-+ --+--+ | cod | cod_access | hr_con | hr_descon | status | +-+--+-+ --+--+ | 1 | 1 | 2004010712 | 20040107120030 | D | +-+--+-- +--+-+ 1 row in set (0.01 sec) then I make one update in the column hr_descon update con set hr_descon='20040107120100'; there it is the problem, when bringing up to date the column hr_descon for the value informed in update, the column hr_con is brought up to date automatically for current date/time select * from con; select * from con; ++---+-+ --+--+ | cod | cod_access | hr_con | hr_descon | status | +-+--+-+ --+--+ | 1 | 1 | 20040107101056 | 20040107120100 | D| +-+--+-- +--+-+ 1 row in set (0.01 sec) I am using Operational System Solaris 9 on Sparc Platform and I tested in versions 3.23.47, 3.23.58 and 4.0.14 of mysql. If somebody to know what happens please helps I. Thanks!!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamp Problems
2 (of many) solutions here: Create the column as a datetime instead and set it with NOW() when you first insert the data. In your update query, set the hr_con column to the current value. (If you set the value explicitly it will not be updated with the automatic timestamp value.) read this for a proper description: http://www.mysql.com/doc/en/DATETIME.html Mike On Wednesday 07 January 2004 14.12, Leandro Saggin wrote: Hello, I am having problems with columns timestamp, what it happens is the following: I created the following table using timestamp and inserted data in this table: create table con(cod integer not null primary key auto_increment, cod_access integer, hr_con timestamp(14), hr_descon timestamp(14), status char); insert into con values(null,1,'2004010712','20040107120030','D'); when executing select in the table, the data are correct select * from con; ++---+- +--+--+ | cod | cod_access | hr_con | | hr_descon | status | +-+--+- +--+--+ | 1 | 1 | 2004010712 | | 20040107120030 | D | +-+--+- -+--+-+ 1 row in set (0.01 sec) then I make one update in the column hr_descon update con set hr_descon='20040107120100'; there it is the problem, when bringing up to date the column hr_descon for the value informed in update, the column hr_con is brought up to date automatically for current date/time select * from con; select * from con; ++---+- +--+--+ | cod | cod_access | hr_con | | hr_descon | status | +-+--+- +--+--+ | 1 | 1 | 20040107101056 | | 20040107120100 | D| +-+--+- -+--+-+ 1 row in set (0.01 sec) I am using Operational System Solaris 9 on Sparc Platform and I tested in versions 3.23.47, 3.23.58 and 4.0.14 of mysql. If somebody to know what happens please helps I. Thanks!!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bet the Business
Quoting Mike [EMAIL PROTECTED]: One thing to remember is that every stored procedure you execute steals resources that would otherwise go to handling queries, so you do not necesscarily see a performance boost by using stored procedures for everything, in fact a MySQL server handling a lot of stored procedures could very well show poorer performance than a proper n-tier application. People have said this a few times, and I'm showing my ignorance level here - but a lot of the n-tier documentation I've seen (allbeit MS and probably dubious) has the data tier as an SQL Server full of stored procedures which is called from a business logic tier. Now, if I don't have stored procedures I'm basically making the same SQL calls (or different ones, but still SQL calls) from queries formed in my business layer - so I'm unsure how removing stored procedures helps as your still hitting the database. I'm new so I am probably missing something, I'm just interested in the details of how removing stored procedures might cause performance increase? Is it because you've moved logic, rather than data access, away from the stored procedure? I must admit, in my limited capacity, most of my simple apps so far have involve basic selects, inserts, updates, etc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL 5 Stored Procedure
Do a search on this list ... this has been covered within the last two weeks several times. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 5:40 AM Subject: MYSQL 5 Stored Procedure Hello I've installed Mysql 5 and have created a database. When I try and create a stored procedure (using the example in the help) it fails. The online help states that there should be a table called 'procs' in the database for stored procedures to work. I can't see this table. The help says this should have been created as part of the installation. Any ideas as to why this is and what I can do? Thanks - Email provided by http://www.ntlhome.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find if the value returned is numeric
That depends on what language you are using to return the query results. Visual Basic is an IsNumeric function. I'm sure other languages have something similiar. What are you using? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: compiling mysql with ssl
This is a known bug. You'll have to create a link in /usr/local/include/openssl. Also, you'll want to use this configure: $ ./configure --with-openssl --with-openssl-includes=/usr/local/ssl/include --with-openssl-libs=/usr/local/ssl/lib --with-vio Doing --with-openssl=/path/to/openssl seems to confuse configure. You also don't want to specify the particular .a file, just the directory. -Greg G Cion Chen wrote: Hi! I have some trouble tring to compile Mysql 4.0.17 with de options: --with-vio --with-openssl, I try with some recomedation about it but none successful. Compiling with: ./configure --with-vio --with-openssl=/path/to/openssl --with-openssl-includ e=/path/to/include/ssl.h --with-openssl-lib=/path/to/lib/libssl.a Everything is fine during de compilation but when i install the new database, it's show my variable have_openssl=no and the showing Status SSL=not in use *When i compile with: ./configure --with-vio --with-openssl --with-openssl-include=/path/to/includ e/ssl.h --with-openssl-lib=/path/to/lib/libssl.a during the compilation i get the following error: ../lib/mysql/.libs/libmysqlclient.so: undefined reference to openssl_add_all_algorithms collect 2: ld returned 1 exit status Can someone help me?, I don't know that to do or what i should do for now?? Thanks in advance, Cion
[mysql] Dump table _files_ to a text file?
I have a MySQL installation that went terribly wrong so I changed to a different server, reset everything up and am up and running again. The problem is, I'm so new to this I didn't know how to move my databases, and since there wasn't much information in them (about two weeks worth of posts to a phpBB), I just started from scratch with a new database. Is there a way I can access the .FRM, .MYD. and .MYI to dump the text to a text file of some sort? Is there any easy way to use these files and import them into Access 2003? Keep in mind that these files are not part of a working MySQL install, so I need a way to work with them that is not dependent upon the MySQL installation working. Thanks! Ryan Sinnwell Regional IT Engineer The Weitz Company 515-698-4281 515-229-5517 (Cell) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple sums in one query
I have a table 'table1' with fields, 'key' and 'number' There is another table 'table2' with fields 'key1' and 'key2' 'table2.key1' corresponds to 'table1.key'. Now, I need to find, in one query, the sum of all 'table1.number' fields for a given 'table2.key2' AND the highest sum of 'table1.number' with same key, within the 'table2.key2' So I need something like SELECT sum(table1.number) as highest FROM table1, table2 WHERE table1.key=table2.key1 AND table2.key2=? GROUP BY table1.key ORDER BY highest DESC LIMIT 1 and SELECT sum(table1.number) as total FROM table1, table2 WHERE table1.key=table2.key2 AND table2.key2=? GROUP BY table2.key2 in one query. Is this even possible? I would greatly appreciate any help, thanks Aleksander Wang-Hansen _ MSN Messenger http://www.msn.no/messenger - Den korteste veien mellom deg og dine venner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
Loosing one table [one data file] is less affecting than 10. My view is that MySQL is good because it uses different data files. If one data file gets corrupt, you will loose less information since it is only one table [meaning one data file] that is affected. I am not an expert though!! Just an Idea. Thanks Emery - Original Message - From: Martijn Tonies [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 16, 2003 10:07 Subject: Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ? Looking at the facts, the number of files used to store your data is of very little consequence at the end of the day. Looking at the following: * MS SQL Server stores database data in a single file and logs in another. * Oracle and FoxPro both have a pretty large number of files, for very different reasons * MySQL stores MyISAM each table in a few files: FRM for the schema, MYD for data and MYI for the index * FileMaker Pro stores each table along with a bunch of interface stuff in a single file * SQLBase (popular in the contract / closed market segment) has a single file unless you tell it to partition the database. This single file stores tables, stored procedures, views, triggers, indexes and all transaction logs * MySQL requires 3 types of file for InnoDB storage in 3.23.x and 4.x - ibdata*, ib_arch_log_* and iblogfile*, in 4.1.1 and higher you can go to 4 files with individual table spaces The last point is particularly worthy of note. The new option in 4.1.1 and higher has implications for performance - you can have your InnoDB data dictionary (ibdata*), logs and individual table/index spaces on physically seperate devices. Backing things up becomes more complicated though. To be honest, the vast majority of database installations experience problems in performance caused by poor query and schema design, bad application logic or grossly underspecified hardware. A change in the number of files used to store the data is extremely unlikely to resolve these problems given all other variables in the environment remain fixed. What does everyone else think? I fully agree with the part about performance and the number of files. :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
Loosing one table [one data file] is less affecting than 10. My view is that MySQL is good because it uses different data files. If one data file gets corrupt, you will loose less information since it is only one table [meaning one data file] that is affected. I am not an expert though!! Just an Idea. If you're loosing files, you have a different problem :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem while installing MySQL
Title: Problem while installing MySQL Hi I am trying to install MySQL for practice on my PC (Windows XP). I have all the administrative rights I downloaded the zip file mysql-4.1.1a-alpha.zip from the downloads. I extracted the zip file in a temporary folder. The manual says run setup.exe for installing on Windows but I couldn't find this file in the temporary folder. Can you help me on this regard Thanks and Regards Saurabh Sharma Fidelity Brokerage Technology -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: web hosting quesiong (slightly off topic)
snip i really love 'em. how often do you hear that about an ISP? /snip Usually everytime someone is looking for an ISP and an owner/person-with-something-to-gain happens to sniff out the opportunity. -J -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 8:33 AM To: [EMAIL PROTECTED] Subject: RE: web hosting quesiong (slightly off topic) www.soniccommerce.com They're awesome, all linux, and expect you to grow over time so they make upgrading your plan simple. i really love 'em. how often do you hear that about an ISP? -dan -Original Message- From: Chris W [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 8:10 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: web hosting quesiong (slightly off topic) I was wondering if anyone could recommend a good web hosting company for an Apache - php - MySQL project. I don't need much bandwidth or disk space to start out, but may need more if the site gets big. I would also like to have ssh access to the server, preferably a linux server. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: web hosting question (slightly off topic)
is it just me, or do hosts underestimate how much data we might want to put in a MySQL database? For instance, 250MB, the largest plan from that host would not come close to holding all my data. I guess what I'm really curious about: do others construct large (GB) databases in MySQL? Is there some reason not to I should know? Eric [yes, I'm trying to pare the memory size of my tables down, but a byte is a byte] snip i really love 'em. how often do you hear that about an ISP? /snip Usually everytime someone is looking for an ISP and an owner/person-with-something-to-gain happens to sniff out the opportunity. -J -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 8:33 AM To: [EMAIL PROTECTED] Subject: RE: web hosting quesiong (slightly off topic) www.soniccommerce.com They're awesome, all linux, and expect you to grow over time so they make upgrading your plan simple. i really love 'em. how often do you hear that about an ISP? -dan -Original Message- From: Chris W [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 8:10 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: web hosting quesiong (slightly off topic) I was wondering if anyone could recommend a good web hosting company for an Apache - php - MySQL project. I don't need much bandwidth or disk space to start out, but may need more if the site gets big. I would also like to have ssh access to the server, preferably a linux server. Chris W -- 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]
Installation of 4.1.1 on XP
I have downloaded the correct windows zip file, but unlike previous Windows downloads, this does not include a setup.exe file, but hundreds of other (source?) files. Yes, I have RTFM, which merely tells me to extract to a temporary directory the run the setup.exe file, which is non-existent. How can I upgrade from 4.0.17, please? Or even start again from scratch with 4.1.1? Regards Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: web hosting quesiong (slightly off topic)
What?!?! That wasn't called for. I am in no way affiliated with them and do not stand to gain anything if anyone would choose to host with them. I've worked with over a dozen ISPs and SonicCommerce is my favorite. That's it, plain and simple. -Original Message- From: Jeremy Weaver [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 9:29 AM To: [EMAIL PROTECTED] Subject: RE: web hosting quesiong (slightly off topic) snip i really love 'em. how often do you hear that about an ISP? /snip Usually everytime someone is looking for an ISP and an owner/person-with-something-to-gain happens to sniff out the opportunity. -J -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 8:33 AM To: [EMAIL PROTECTED] Subject: RE: web hosting quesiong (slightly off topic) www.soniccommerce.com They're awesome, all linux, and expect you to grow over time so they make upgrading your plan simple. i really love 'em. how often do you hear that about an ISP? -dan -Original Message- From: Chris W [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 8:10 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: web hosting quesiong (slightly off topic) I was wondering if anyone could recommend a good web hosting company for an Apache - php - MySQL project. I don't need much bandwidth or disk space to start out, but may need more if the site gets big. I would also like to have ssh access to the server, preferably a linux server. Chris W -- 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]
Query to Find Max Sum of 60 Consecutive Fields from 60 Consecutive Records
Howdy MySQL Subscribers. I'm hoping some MySQL users more experienced than myself can shed light on a work-related question that has recently arisen. We have a database with 24 meter tables similar to meter1 below. mysql explain meter1; ++--+---+---+---++ | Field | Type | Null | Key | Default| Extra | ++--+---+---+---++ | date_time | datetime | | | -00-00 00:00:00 || | count_per_minute | int(11) | || 0|| | dose_equiv_per_minute | double unsigned | YES | | NULL| | | counts| varchar(255) | YES | | NULL| | ++--++--++---+ The tables are populated with data from neutron monitoring stations; data are collected from the monitors and inserted into their respective tables once per minute. Currently there are some 45,000+ records in each table, though once in production mode we expect significantly higher record counts ( the schedule of data backups and table truncations has yet to be determined ). The data looks like this: mysql select date_time, count_per_minute, dose_equiv_per_minute from meter1 order by date_time desc limit 4, 10; +-+---++ | date_time| count_per_minute | dose_equiv_per_minute | +-+---++ | 2003-11-25 19:51:01 | 2310 | 0.483598 | | 2003-11-25 19:50:01 | 2316 | 0.484855 | | 2003-11-25 19:48:01 | 772| 0.161618 | | 2003-11-25 19:47:01 | 3846 | 0.80516 | | 2003-11-25 19:46:01 |8 | 0.001675| | 2003-11-25 19:44:01 | 2313 | 0.484227 | | 2003-11-25 19:43:00 | 2313 | 0.484227 | | 2003-11-25 19:41:00 | 1032 | 0.216049 | | 2003-11-25 19:40:00 | 3587 | 0.750938 | | 2003-11-25 19:39:00 |7 | 0.001465| +-+---++ We have a web GUI written in PHP that calls a C program which queries the database, analyzes the data, and creates a PDF report. One of many analytical tasks of this program is to find the maximum sum of the field, `dose_equiv_per_minute`, from any 60 consecutive records. In other words, we need to sum the values from `dose_equiv_per_minute` for records 1 through 60, 2 through 61, 3 through 62, etc... then determine which is the max sum. Currently this is being handled on the client-side through the C program, which selects all of the (many thousands of ) records, then does the math and loops through them, group by group. As you can imagine, if one were creating reports from all 24 monitors, the number-crunching can be quite time consuming (upwards of 5 minutes at present for just over 6 weeks worth of data). So the question is, can we save some time by asking the MySQL server to crunch the numbers, and if so, what would the query be to accomplish this task? Any and all suggestions greatly appreciated. TIA , dave
Re: Problem while installing MySQL, etc.
Sharma and Terry, I believe MySQL only supplies setup.exe files with the versions that have production status. However, if you download the appropriate .zip file from the website, then that will contain all the files that you need. Simply extract it to the default location (C:\mysql in Windows or \usr\local, I believe, in Linux). If you are upgrading, then make sure you back up your old installation as to not override your data. HTH, Matt At 09:25 AM 1/7/2004, Sharma, Saurabh wrote: Hi I am trying to install MySQL for practice on my PC (Windows XP). I have all the administrative rights I downloaded the zip file mysql-4.1.1a-alpha.zip from the downloads. I extracted the zip file in a temporary folder. The manual says run setup.exe for installing on Windows but I couldn't find this file in the temporary folder. Can you help me on this regard Thanks and Regards Saurabh Sharma Fidelity Brokerage Technology -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] At 09:43 AM 1/7/2004, Terry Riley wrote: I have downloaded the correct windows zip file, but unlike previous Windows downloads, this does not include a setup.exe file, but hundreds of other (source?) files. Yes, I have RTFM, which merely tells me to extract to a temporary directory the run the setup.exe file, which is non-existent. How can I upgrade from 4.0.17, please? Or even start again from scratch with 4.1.1? Regards Terry Riley -- 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 5 Stored Procedure
Documentation: http://www.mysql.com/doc/en/Stored_Procedures.html Marc. -Message d'origine- De : Peter Brawley [mailto:[EMAIL PROTECTED] Envoyé : mercredi 7 janvier 2004 16:32 À : [EMAIL PROTECTED]; [EMAIL PROTECTED] Objet : Re: MYSQL 5 Stored Procedure Matthew, You need to run the script mysql_fix_privilege_tables.sql. It's not included in some binaries, but it is in 5.0 source archives. Once you run this script, you can execute Create Procedure ... as a SQL cmd. How you execute them, though, is not mentioned yet in the docs :-). PB - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 5:40 AM Subject: MYSQL 5 Stored Procedure Hello I've installed Mysql 5 and have created a database. When I try and create a stored procedure (using the example in the help) it fails. The online help states that there should be a table called 'procs' in the database for stored procedures to work. I can't see this table. The help says this should have been created as part of the installation. Any ideas as to why this is and what I can do? Thanks - Email provided by http://www.ntlhome.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld without LinuxThreads
Hi all, does anyone know if it's possible to compile MySQL under Linux so that mysqld doesn't rely upon LinuxThreads, but makes direct call to fork() or clone() system calls instead? I'm looking for a way to avoid the exploitation of pthreads under Linux. Is this feature provided by MySQL? Many Thanks Fabio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld without LinuxThreads
On Wed, 2004-01-07 at 10:38, [EMAIL PROTECTED] wrote: Hi all, does anyone know if it's possible to compile MySQL under Linux so that mysqld doesn't rely upon LinuxThreads, but makes direct call to fork() or clone() system calls instead? As far as I know and can tell from reading docs, Linux's pthread implementation DOES use clone to create threads. In most cases, pthread is a wrapper around whatever threading services the host system provides. I'm looking for a way to avoid the exploitation of pthreads under Linux. Why wouldn't you want to use an industry standard threading model? -- Andy Bakun [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [mysql] Dump table _files_ to a text file?
Just copy the database folder to your new server. If they are not corrupt then they should be accessible from your new server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple sums in one query
Are you wanting to do this in one query? You could put an order by on your second query. The first row returned by it should be what you are after. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error 1044
[snip] /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' Please help. Thanks in advance. [/snip] Please RTFM at http://www.mysql.com/doc/en/GRANT.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1044
Jay Blanchard wrote: [snip] /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' Please help. Thanks in advance. [/snip] Please RTFM at http://www.mysql.com/doc/en/GRANT.html That's an excellent page to read, but it won't do him much good until he can get logged in as [EMAIL PROTECTED] Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error 1044
[snip] Please RTFM at http://www.mysql.com/doc/en/GRANT.html [/snip] Sorry, I pulled the send trigger too fast. My bad. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bet the Business
The fundamental reason for using stored procedures is performance. Stored procedures are compiled code. This means the database has reviewed the SQL, came up with the most efficient plan of action (often sorting through thousands of permutations when multiple table joins are concerned), and (given the right directives from the DBA) has this plan of action cached in memory, waiting to be called by name (optionally with parameters). When you issue your SQL directly from the application or business tier rather than use a stored procedure - you are issuing what's called dynamic SQL. The process of coming up with a plan of action for this SQL repeats itself every time the SQL is issued and is very expensive. In sophisticated transaction system it's common to see a 1000-fold improvement in the number of transactions per second processed - when switching from dynamic SQL to stored procedures. This is a big deal. On a small application with simple SQL and not much action - it's less of an issue. On an airline reservation or amazon type situation - stored procedures are an absolute must, and yes, they tie you to the specific database at hand because every database's stored procedure language is different (that's one way for the vendors to make it hard to switch). No pain no gain. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
1142: Issue in 4.1.0-alpha
Hi ya, Is it dissolved in the new versions ? Only three posting so far on that. I just keep forgetting 1142 and always endup digging the source!! root as user solves the problem. -- Aftab Jahan Subedar CEO/Software Engineer Subedar Technologies Subedar Baag Bibir Bagicha #1 North Jatrabari Dhaka 1204 Bangladesh http://www.DhakaStockExchangeGame.com/ - [EMAIL PROTECTED] http://www.CEOBangladesh.com/ - [EMAIL PROTECTED] http://www.geocities.com/jahan.geo/ - [EMAIL PROTECTED] sms://+447765341890 tel://+88027519050 EMail://[EMAIL PROTECTED] - Directly to my notebook -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug in Boolean mode fulltext searching.
Description:
Boolean mode fulltext searching returns zero hits for valid queries.
How-To-Repeat:
Create a database with a Text column. Add a fulltext index on it.
Try to search for multiple words with AND or phrase syntax.
Here are examples:
mysql select Notice_ID from Notices where match (Text) against ('+pollution +control'
in boolean mode);
Empty set (0.00 sec)
mysql select Notice_ID from Notices where match (Text) against ('pollution control'
in boolean mode);
Empty set (0.02 sec)
mysql select Notice_ID from Notices where Text like '%pollution control%';
+---+
| Notice_ID |
+---+
|192090 |
+---+
1 row in set (5.00 sec)
mysql select Notice_ID from Notices where match (Text) against ('+pollution +air' in
boolean mode);
Empty set (0.03 sec)
mysql select Notice_ID from Notices where match (Text) against ('air pollution' in
boolean mode);
Empty set (0.00 sec)
mysql select Notice_ID from Notices where Text like '%air pollution%';
+---+
| Notice_ID |
+---+
|196349 |
|196569 |
|188183 |
|192090 |
|192686 |
|199283 |
+---+
6 rows in set (0.17 sec)
(NOTE on the search for air -- my.cnf has ft_min_word_len=3)
All OR searches work perfectly fine, as per:
mysql select count(*) from Notices where match (Text) against ('air pollution');
+--+
| count(*) |
+--+
| 100 |
+--+
1 row in set (0.03 sec)
These are not overly common words:
mysql select count(*) from Notices;
+--+
| count(*) |
+--+
|11990 |
+--+
1 row in set (0.00 sec)
Fix:
Use a WHERE text-column LIKE %phrase% for phrase searching.
No known workaround for AND searches.
Submitter-Id: submitter ID
Originator:Joe Rhett
Organization:
Isite Services, Inc.
MySQL support: none
Synopsis: Boolean mode fulltext searching fails.
Severity: serious
Priority: high
Category: mysql
Class: sw-bug
Release: mysql-4.0.16 (Source distribution)
C compiler:2.95.3
C++ compiler: 2.95.3
Environment:
System: SunOS web031 5.8 Generic_108529-23 i86pc i386 i86pc
Architecture: i86pc
Some paths: /usr/bin/perl
Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 11 Sep 15 18:17 /lib/libc.so - ./libc.so.1
-rwxr-xr-x 1 root bin 956112 Jul 29 20:10 /lib/libc.so.1
lrwxrwxrwx 1 root root 11 Sep 15 18:17 /usr/lib/libc.so - ./libc.so.1
-rwxr-xr-x 1 root bin 956112 Jul 29 20:10 /usr/lib/libc.so.1
Configure command: ./configure '--prefix=/opt/mysql' '--localstatedir=/var/mysql'
'--without-debug'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL as document storage?
Hi, (disclaimer - this thread could easily go off topic; I'm interested only in the MySQL aspects of what follows...) At work we are currently investigating ways of filing all our electronic documents. There is commercial software that will do this I know, but I was wondering whether MySQL would be suitable for this type of thing. The 'documents' could be literally any binary file. My idea would be to create a table with a blob column for the document itself, and document title, reference number, keywords, other meta-data. And a web-based front-end to search and serve documents. Although the documents could be any file, the majority would be textual documents (Word documents, PDF, etc). How would one go about indexing such data, since full text searches operate on textual columns? How to cope with columns exceeding the max packet length? Why is there a max_packet_length setting; surely this is low-level stuff that shouldn't affect query and result sizes? Is storing the actual documents in the database such a good idea anyway? Perhaps store the file in a file system somwhere and just store the filename? If anyone has experience in doing (or been dissuaded from doing) this kind of application your thoughts and comments would be appreciated. (If only to tell me don't be so stupid, it'll never work :) Thanks. -- Regards, Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL as document storage?
Original Message - From: Steve Folly [EMAIL PROTECTED] At work we are currently investigating ways of filing all our electronic documents. I don't know the answer, but it's an interesting question. We are currently looking at using more and more SQL (we use MySQL now in places), and we face looking the issue of storing PDF files and such in a back-end. We also have Lotus Domino - which is very good at this sort of thing - but I'd still be interested in the discussion. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL as document storage?
May be there is an option on the Commercial Market, BasisPlus from OpenText. I worked quite a lot with it but, sincerely, I prefer a MySQL table with references to files on the File System. A lot faster, and cheaper Now there is true that I use to work with sgml files and I still prefer them. I this case, you can even use TEXT columns and - if you use MyISAM tables - you can index them to use full text retrieval. On the other hand, BasisPlus has the ability to import all sort of data and index them, but you have to pay a high price for that... In conclusion, I still think that MySQL is your best choice here. Leo. AFIP-AR. - Original Message - From: Ian O'Rourke [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 6:06 PM Subject: Re: MySQL as document storage? Original Message - From: Steve Folly [EMAIL PROTECTED] At work we are currently investigating ways of filing all our electronic documents. I don't know the answer, but it's an interesting question. We are currently looking at using more and more SQL (we use MySQL now in places), and we face looking the issue of storing PDF files and such in a back-end. We also have Lotus Domino - which is very good at this sort of thing - but I'd still be interested in the discussion. -- 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 upgrade on RedHat 9.0
I've MySQL-3.23.54a-11 installed and running on RH 9.0 via the RedHat rpm's. I'm trying to upgrade to MySQL-4.0.17-0 via rpm's from MySQL.com. I found references from web searches and through the archives from this list that suggest that it's as easy as: shell rpm -Uvh --nodeps MySQL* but this results in numerous error messages saying that various files from 4.0.17 conflict with files from 3.23.54a-11. So then I tried uninstalling 3.23.54 first, then running: shell rpm -i MySQL* but this also did not work. Although when I ran: shell rpm -qa | grep MySQL* at this point, I got the correct list of rpm's that I was trying to install. However, no mysqld was found. I'd much appreciate some guidance. dave
Re: MySQL as document storage?
This article discusses it briefly: http://php.dreamwerx.net/forums/viewtopic.php?t=6 I am using this type of design/technology for quite a few clients. Some storing gigs and gigs of data (images, documents, pdf, anything) over multiple servers. The scalability and performance of a well designed system I think are very close to standard filesystem storage. I have currently written http and ftp gateways to access data stored in mysql dataservers(databases). Quite easy and fast... I have yet to do much with searching and indexing of files (not required) but I would imagine you could have very fast searching features. On Wed, 7 Jan 2004, Steve Folly wrote: Hi, (disclaimer - this thread could easily go off topic; I'm interested only in the MySQL aspects of what follows...) At work we are currently investigating ways of filing all our electronic documents. There is commercial software that will do this I know, but I was wondering whether MySQL would be suitable for this type of thing. The 'documents' could be literally any binary file. My idea would be to create a table with a blob column for the document itself, and document title, reference number, keywords, other meta-data. And a web-based front-end to search and serve documents. Although the documents could be any file, the majority would be textual documents (Word documents, PDF, etc). How would one go about indexing such data, since full text searches operate on textual columns? How to cope with columns exceeding the max packet length? Why is there a max_packet_length setting; surely this is low-level stuff that shouldn't affect query and result sizes? Is storing the actual documents in the database such a good idea anyway? Perhaps store the file in a file system somwhere and just store the filename? If anyone has experience in doing (or been dissuaded from doing) this kind of application your thoughts and comments would be appreciated. (If only to tell me don't be so stupid, it'll never work :) Thanks. -- Regards, Steve. -- 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 as document storage?
I made an online file manager using PHP and MySQL some years ago, and am now embedding something similar into my office's database front-end. I decided to store our files in the file system rather than the database in order to keep the DB size low. A benefit of this is it takes less time to restore a backup of the database than it would if I were dealing with the extra gigabytes of embedded files (which I can restore on an individual basis). As for indexing, a lot of the document retrieval solutions out there just go by metadata when you do a file search. Business class scanning systems offer you the option of embedding user-supplied metadata in your scanned files so adding your own keywords is an option. In my experience you are better off going by just some supplied keywords and metadata rather than the full text of a document because you end up with more relevant results. The exception to this is when you are just dying to know how many documents contain the word pie. If you find that this is the case then you obviously have the free time needed to build some extra indexes... ;) Thanks, Rob Brahier Web Architect Email: [EMAIL PROTECTED] Special Notice: This email transmission may contain material, which is confidential under Florida statutes and is intended to be delivered only to the named addressee. This information belongs to our facility and is legally privileged. Unauthorized dissemination of this information may be a violation of criminal statutes. The recipient of this information is prohibited from disclosing, copying, distributing or using this information except as permitted by current government law governing privacy information issues. Such information must be destroyed after its stated need has been fulfilled, unless otherwise prohibited by law. If this information is received by anyone other than the named addressee, the recipient should immediately notify us at the address or telephone number shown and obtain instructions as to the disposal thereof. Under no circumstances should this material be read, retained, or copied by anyone other than the named addressee. -Original Message- From: Steve Folly [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 3:56 PM To: MySQL MySQL Subject: MySQL as document storage? Hi, (disclaimer - this thread could easily go off topic; I'm interested only in the MySQL aspects of what follows...) At work we are currently investigating ways of filing all our electronic documents. There is commercial software that will do this I know, but I was wondering whether MySQL would be suitable for this type of thing. The 'documents' could be literally any binary file. My idea would be to create a table with a blob column for the document itself, and document title, reference number, keywords, other meta-data. And a web-based front-end to search and serve documents. Although the documents could be any file, the majority would be textual documents (Word documents, PDF, etc). How would one go about indexing such data, since full text searches operate on textual columns? How to cope with columns exceeding the max packet length? Why is there a max_packet_length setting; surely this is low-level stuff that shouldn't affect query and result sizes? Is storing the actual documents in the database such a good idea anyway? Perhaps store the file in a file system somwhere and just store the filename? If anyone has experience in doing (or been dissuaded from doing) this kind of application your thoughts and comments would be appreciated. (If only to tell me don't be so stupid, it'll never work :) Thanks. -- Regards, Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with 4.1.1 on Mac OS X
Hi, I just installed version 4.1.1 on Mac OS X. If I enter 'show tables;' after starting mysqld_safe it just says ERROR: No query specified The same happens if I try 'CREATE DATABASE menagerie;' Why? Thanks, Martin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL as document storage?
On 7 Jan 2004, at 21:51, [EMAIL PROTECTED] wrote: This article discusses it briefly: http://php.dreamwerx.net/forums/viewtopic.php?t=6 That's an interesting article. Thanks. A similar table design to what I had in mind (hmmm... how different can these things be! :) I like the idea of splitting up binary data into segments so as reduce the load on the server. I assume this works because MySQL doesn't send all rows over the connection when the query completes, only just the ones you ask for? (In this case, a segment at a time?) Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL as document storage?
On 7 Jan 2004, at 22:24, Rob Brahier wrote: I made an online file manager using PHP and MySQL some years ago, and am now embedding something similar into my office's database front-end. I decided to store our files in the file system rather than the database in order to keep the DB size low. A benefit of this is it takes less time to restore a backup of the database than it would if I were dealing with the extra gigabytes of embedded files (which I can restore on an individual basis). Incremental backups would certainly be smaller. The backup would spot individual files being changed, but being blobs in a table, the one (large) file in the database would have to be backed up for each change? As for indexing, a lot of the document retrieval solutions out there just go by metadata when you do a file search. Business class scanning systems offer you the option of embedding user-supplied metadata in your scanned files so adding your own keywords is an option. In my experience you are better off going by just some supplied keywords and metadata rather than the full text of a document because you end up with more relevant results. The exception to this is when you are just dying to know how many documents contain the word pie. If you find that this is the case then you obviously have the free time needed to build some extra indexes... ;) I think you're right. Thanks for the input. -- Regards, Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple sums in one query
Sorry, I didn't look closely enough at your two queries. I don't believe that you will be able to get your desired results from one query. Besides it is usually better to execute 2 queries that are simplier than one that is very complex. Robert Rowe --- Aleksander Wang-Hansen [EMAIL PROTECTED] wrote: Yes, I want to do it in one query. And the second query I made should only return one row anyway. But I want both the value I select in my first query, and the value I select in my second query, from one query. Getting it from two isn't a problem, but I can't seem to figure out how to get it from only one query. Thanks Original Message Follows From: robert_rowe [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Multiple sums in one query Date: Wed, 7 Jan 2004 13:46:02 -0500 Are you wanting to do this in one query? You could put an order by on your second query. The first row returned by it should be what you are after. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Last ned nye MSN Messenger 6.1 gratis http://www.msn.no/computing/messenger - Den korteste veien mellom deg og dine venner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.0 Installation Problem
Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno 13) ... Well, since I failed to use RPM to install MySQL 4.0 over 3.23 -- I am not sure if I have to unstall the latter before I install the former, the manual does not mention that, I went ahead to do a fresh binary installation of it on my Linux 7, under usr/local/ directory. Followed the manual step by step until I started the server: /bin/mysqld_safe (also tried it with parameter user=mysql), but I got mysql ended ... [1]+ Done./bin/mysqld_safe msg, and I could not run any further commands, failing with msgs like Can't connect to local MySQL server though socket '/tmp/mysql.sock', Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! then I found in the sql log file: Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno 13) ... when starting the InnoDB tables. Both files mysql.sock and host.frm are not mentioned in the manual, and not found in my file system. Any one can point out the problem? Thanks. -Original Message- From: Zhao, Charles Sent: Tuesday, January 06, 2004 4:53 PM To: '[EMAIL PROTECTED]' Subject: MySQL 4.0 Installation Problem I have 3.23 that comes with my Linux distribution, now when I tried to do a rpm installation of 4.0, I got messages like: warning: MySQL-server-4.0.17 ... .rpm: V3 DSA signature: NOKEY, key ID ..., file /usr/bin/mysql from install of MySQL-client-4.0.17.0 conflicts with file from package mysql-3.23.52-3 ... file /usr/shar/man/man1/ ... from install of ... conflicts with file from package mysql-3.23... ... and the conflict list goes on. The installation manual does not seem to discuss about such issues. Do I need to remove my 3.23 before I run the installation? -- but that's not what the manual says, though. The first warning msg, is that a problem, too? Thanks.
Recommended Course
This might be a bit of a repeat, but I didn't get too many responses. Can someone recommend a course in the Northeast of the US that teaches the details of the use of mysql? Sincerely, Marc DVer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
apostrophe error
msg.pgp Description: PGP message
Recommended Course
Hello marc, Look at the URL: www.mysql.com/training/courses/using_and_managing_mysql.html There are courses coming soon at Boston and another one in Washington DC which I think is fairly in the Northeast of the US. Regards, Bernard Marc Dver [EMAIL PROTECTED] wrote .. This might be a bit of a repeat, but I didn't get too many responses. Can someone recommend a course in the Northeast of the US that teaches the details of the use of mysql? Sincerely, Marc DVer -- 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: apostrophe error
At 20:39 -0500 1/7/04, Asif Iqbal wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi All
I have been using mysql database to collect my syslog data. It was
working fine. However recently I had few logs that had apotrophe and I
failed to insert those lines into my database. It said syntax error. Is
there any patch anything I can put and recompile mysql so it won't fail
while inserting log lines that has apotrophe (') in it ?
MySQL is acting properly. If you don't escape apostophes in your data values
properly, the query is not syntactically legal.
However, we cannot advise you how to deal with this without more information.
Can you tell us how you're attempting to insert the log data into MySQL?
Are you using some script that you have written, for example? What language
is it in?
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing mysql dates as an integer
At 9:57 +0900 1/8/04, Dave G wrote: MySQL Gurus, Because of time zone differences between my web site's intended audience (Japan) and my server's location (Nevada), I've found that it's most efficient, or at least most simple, to do all my time calculations in PHP. Up to now, I've still been storing all my dates in native MySQL time formats - like DATETIME. However, this requires a bit of format conversion between PHP and MySQL. What I'm thinking now is that it might just be easier for me to store the date as a UNIX timestamp format in MySQL as a simple ten digit long integer (Unix timestamps are ten digits, aren't they?). That way I No, they're fourteen digits: CCYYMMDDhhmmss can pass them to and from PHP and do all the work on the PHP side without having to do any format conversions in my SELECT and INSERT queries. Is this a horribly bad idea for any reason? Is the INTEGER format the best suited for this purpose? Any tips would be greatly appreciated. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupt MYD table - can not repair - Get the free tool..
Hi ,
Kindly get the tool (Navicat version 5.1.1 - trial version) in
http://www.mysqlstudio.com. - I'm Using now..
-Aman.
Mike Brickman wrote:
Where can I get navicat-pro? I have downloaded a copy of navicat from
the www.navicat.com but this is a gui and does not seem to have the
functionality you describe. Am I missing something.
Mike Brickman
Radical Solutions
-Original Message-
From: Amanullah [mailto:[EMAIL PROTECTED]
Sent: Wednesday 07 January 2004 02:53
To: miguel solorzano
Cc: Mike Brickman; [EMAIL PROTECTED]
Subject: Re: Corrupt MYD table - can not repair
You can use navicat-pro tool (free ware) to repair
the corrupted MYD files, I was repaired with the same
TOOL.
-Aman.
miguel solorzano wrote:
At 19:04 6/1/2004 +, Mike Brickman wrote:
If you are able for to build myisamchk from the 4.1 bk tree
it will permit you to set a max row length for to skip them
and continue the recovery.
Hi,
I have a client who has managed to corrupt an MYD table so that it
can
not be repaired. I have tried:
Myisamchk -re {table}
And this crashes out with:
myisamchk: error: Not enough memory for blob at 3960 (need
1090519040)
and then quits.
There is no complete backup of this file so I would like to recover
as
much data as possible.
1) Are there any tools which will allow me to salvage some
data?
2) Why does myisamchk not fix the problem?
3) Are the internal structures of MYD files documented anywhere
(url please)?
4) What exactly does the error mean?
Any help or clues will be appreciated.
Mike Brickman
Radical Solutions
---
-
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.556 / Virus Database: 348 - Release Date: 26/12/2003
RE: Storing mysql dates as an integer
Paul, (Unix timestamps are ten digits, aren't they?). No, they're fourteen digits: CCYYMMDDhhmmss I think we might be talking about different things. A UNIX time stamp is ten digits long: http://www.unixtimestamp.com/ How MySQL stores it's own TIMESTAMP column is the format you describe. I want to know if there's any problem storing a UNIX timestamp purely as an integer in my MySQL database. -- Yoroshiku! Dave G [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: apostrophe error
msg.pgp Description: PGP message
RE: Storing mysql dates as an integer
At 11:25 +0900 1/8/04, Dave G wrote: Paul, (Unix timestamps are ten digits, aren't they?). No, they're fourteen digits: CCYYMMDDhhmmss I think we might be talking about different things. A UNIX time stamp is ten digits long: http://www.unixtimestamp.com/ How MySQL stores it's own TIMESTAMP column is the format you describe. I want to know if there's any problem storing a UNIX timestamp purely as an integer in my MySQL database. I think not, as long as you don't treat it like a TIMESTAMP column. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: apostrophe error
At 21:35 -0500 1/7/04, Asif Iqbal wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Paul DuBois wrote:
At 20:39 -0500 1/7/04, Asif Iqbal wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi All
I have been using mysql database to collect my syslog data. It was
working fine. However recently I had few logs that had apotrophe and I
failed to insert those lines into my database. It said syntax error. Is
there any patch anything I can put and recompile mysql so it won't fail
while inserting log lines that has apotrophe (') in it ?
MySQL is acting properly. If you don't escape apostophes in your data
values
properly, the query is not syntactically legal.
However, we cannot advise you how to deal with this without more
information.
Can you tell us how you're attempting to insert the log data into MySQL?
Are you using some script that you have written, for example? What language
is it in?
I am using a fifo /tmp/mysql.pipe to push my syslog data directly into
my database
Here is the relevant syslog-ng config file
source net { udp (ip(208.47.0.114) port(514)); };
destination d_mysql {
pipe(/tmp/mysql.pipe
template(INSERT INTO logs (host, facility, priority, level, tag, date,
time, program, msg) VALUES ( '$HOST', '$FACILITY', '$PRIORITY',
'$LEVEL', '$TAG',
'$YEAR-$MONTH-$DAY', '$HOUR:$MIN:$SEC', '$PROGRAM', '$MSG' );\n)
template-escape(yes));
};
log { source(net); destination(d_mysql); };
Here is my syslog database
CREATE DATABASE syslog;
USE syslog;
CREATE TABLE logs (
host varchar(32) default NULL,
facility varchar(10) default NULL,
priority varchar(10) default NULL,
level varchar(10) default NULL,
tag varchar(10) default NULL,
date date default NULL,
time time default NULL,
program varchar(15) default NULL,
msg text,
seq int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (seq),
KEY host (host),
KEY seq (seq),
KEY program (program),
KEY time (time),
KEY date (date),
KEY priority (priority),
KEY facility (facility)
) TYPE=MyISAM;
This is how I am piping the log into the database
mysql -u root --password=passwd syslog /tmp/mysql.pipe
It was working fine until it saw a apostrophe in the log and then failed
to insert
I am using mysql Ver 12.22 Distrib 4.0.17, for sun-solaris2.7 (sparc)
Basically syslog-ng does exactly what it suppose to do. collect the logs
from the source and send it to the destination which is happened to be a
pipe to the mysql database. So there is no language in the middle
I don't think that's going to work. You need to have some mechanism
that can properly escape data values in case they contain stuff like
apostrophes.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: link in your book (was Re: apostrophe error)
msg.pgp Description: PGP message
consistency checking InnoDB tables
Hi folks, I'm familar with using myisamchk to check/repair myISAM databases; how do I do consistency checks and repair operations on an InnoDB database? Thanks, Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- In theory there is no difference between theory and practice. In practice there is. - Yogi Berra ---
Re: link in your book (was Re: apostrophe error)
At 22:30 -0500 1/7/04, Asif Iqbal wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Paul DuBois wrote: At 21:35 -0500 1/7/04, Asif Iqbal wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Paul DuBois wrote: At 20:39 -0500 1/7/04, Asif Iqbal wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi All I don't think that's going to work. You need to have some mechanism that can properly escape data values in case they contain stuff like apostrophes. Hi Paul I am going to use perl to parse the log and then send it to the database as another recommeded ( and probably better for my case ). However since I need users to be able to query against the database for specific logs I like to see use PHP for the web part. Therefore I am actually going through your book, MySQL second edition, Chap 8 , PHP API. Now in here you have the urls http://www.snake.net/ushl/index.php and http://www.snake.net/gp/index.php (on page 488). But I can't access those links. I get The requested URL /ushl/index.php was not found on this server message. I can go to the main website though www.snake.net is a real site, but for purposes of the book, it's intended that you install the ushl and gp scripts on your own site and substitute the appropriate URLs for those in the book. For example, if your site is www.example.com and you install the scripts in directories named asif/ushl and asif/gp, you would access them as: http://www.example.com/asif/ushl/index.php http://www.example.com/asif/gp/index.php By the way, you may want to visit http://www.kitebird.com/mysql-cookbook and grab a copy of the recipes distribution. In its apache/httpdlog directory, you'll find a Perl script for reading Apache log entries and inserting them into a MySQL table. That's not quite the same as what you're doing, but the same principles of reading log entries, splitting them up into column values, and making sure the values are escaped properly still apply. So the httpdlog.pl script in that directory may be instructive. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
When does using multiple databases make sense?
Greetings, I am wandering under what circumstances it is sensible/beneficial to use multiple databases for a single project, and why. The reason I ask is because I am re-developing an existing database with MySQL that someone else created with another engine (DBISAM) and chose to have 5 separate databases used by one application. And yes, I do mean databases not tables LOL. Thanks for any advice! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist
MySQL 5.0.0-alpha-max-nt under win2k a small problem: If I have a foreign key with reference to the same table, I get an error, ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist what can I do ? Yours Sincerely Morten Gulbrandsen USE test; DROP TABLE IF EXISTS A; CREATE TABLE A ( SSN CHAR(9) NOT NULL, SUPERSSN CHAR(9), DNOINT NOT NULL DEFAULT 1, PRIMARY KEY (SSN), INDEX (SUPERSSN), INDEX (DNO) )TYPE = INNODB; DESCRIBE A; ALTER TABLE A # line 42 here is the buggy code ADD FOREIGN KEY (SUPERSSN) REFERENCES A(SSN) ON DELETE SET NULL ON UPDATE CASCADE; #ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist mysql select version(); ++ | version() | ++ | 5.0.0-alpha-max-nt | ++ 1 row in set (0.00 sec) Microsoft Windows 2000 [Version 5.00.2195] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist
At 05:26 8/1/2004 +0100, Morten Gulbrandsen wrote: Hi, I wasn't able to repeat: Microsoft Windows XP [versão 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. c:\cd\mysql\bin C:\mysql\binmysql -uroot test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.0-alpha-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql CREATE TABLE A ( - SSN CHAR(9) NOT NULL, - SUPERSSN CHAR(9), - DNO INT NOT NULL DEFAULT 1, - PRIMARY KEY (SSN), - INDEX (SUPERSSN), - INDEX (DNO) - )TYPE = INNODB; Query OK, 0 rows affected, 1 warning (0.11 sec) mysql DESCRIBE A; +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | SSN | char(9) | | PRI | | | | SUPERSSN | char(9) | YES | MUL | NULL| | | DNO | int(11) | | MUL | 1 | | +--+-+--+-+-+---+ 3 rows in set (0.00 sec) mysql ALTER TABLE A - ADD FOREIGN KEY (SUPERSSN) REFERENCES A(SSN) - ON DELETE SET NULL - ON UPDATE CASCADE; Query OK, 0 rows affected (0.27 sec) Records: 0 Duplicates: 0 Warnings: 0 Please verify if your MySQL stuff has all the below files: C:\mysql\bindir c:\mysql\data\mysql O volume na unidade C é Disk 1 Partition 1 O número de série do volume é - Pasta de c:\mysql\data\mysql 07/01/2004 11:39DIR . 07/01/2004 11:39DIR .. 22/12/2003 21:03 8.820 columns_priv.frm 22/12/2003 21:03 0 columns_priv.MYD 22/12/2003 21:03 1.024 columns_priv.MYI 22/12/2003 21:03 9.178 db.frm 22/12/2003 21:03 306 db.MYD 22/12/2003 21:03 3.072 db.MYI 22/12/2003 21:03 8.665 func.frm 22/12/2003 21:03 0 func.MYD 22/12/2003 21:03 1.024 func.MYI 22/12/2003 21:03 8.700 help_category.frm 22/12/2003 21:0320 help_category.MYD 22/12/2003 21:03 3.072 help_category.MYI 22/12/2003 21:03 8.612 help_keyword.frm 22/12/2003 21:03 0 help_keyword.MYD 22/12/2003 21:03 1.024 help_keyword.MYI 22/12/2003 21:03 8.630 help_relation.frm 22/12/2003 21:03 0 help_relation.MYD 22/12/2003 21:03 1.024 help_relation.MYI 22/12/2003 21:03 8.770 help_topic.frm 22/12/2003 21:03 0 help_topic.MYD 22/12/2003 21:03 1.024 help_topic.MYI 22/12/2003 21:03 9.148 host.frm 22/12/2003 21:03 0 host.MYD 22/12/2003 21:03 1.024 host.MYI 22/12/2003 21:03 9.470 proc.frm 07/01/2004 13:33 156 proc.MYD 07/01/2004 13:33 2.048 proc.MYI 22/12/2003 21:03 8.925 tables_priv.frm 22/12/2003 21:03 0 tables_priv.MYD 22/12/2003 21:03 1.024 tables_priv.MYI 22/12/2003 21:03 9.992 user.frm 22/12/2003 21:03 160 user.MYD 22/12/2003 21:03 2.048 user.MYI 33 arquivo(s)116.960 bytes MySQL 5.0.0-alpha-max-nt under win2k a small problem: If I have a foreign key with reference to the same table, I get an error, ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist what can I do ? Yours Sincerely Morten Gulbrandsen USE test; DROP TABLE IF EXISTS A; CREATE TABLE A ( SSN CHAR(9) NOT NULL, SUPERSSN CHAR(9), DNOINT NOT NULL DEFAULT 1, PRIMARY KEY (SSN), INDEX (SUPERSSN), INDEX (DNO) )TYPE = INNODB; DESCRIBE A; ALTER TABLE A # line 42 here is the buggy code ADD FOREIGN KEY (SUPERSSN) REFERENCES A(SSN) ON DELETE SET NULL ON UPDATE CASCADE; #ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist mysql select version(); ++ | version() | ++ | 5.0.0-alpha-max-nt | ++ 1 row in set (0.00 sec) Microsoft Windows 2000 [Version 5.00.2195] -- Regards, For technical support contracts, visit https://order.mysql.com/ Are you MySQL certified?, http://www.mysql.com/certification/ Miguel Angel Solórzano [EMAIL PROTECTED] São Paulo - Brazil --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.556 / Virus Database: 348 - Release Date: 26/12/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Python API ...
Paul DuBois wrote: By the way, you may want to visit http://www.kitebird.com/mysql-cookbook and grab a copy of the recipes distribution. In net style led me to: http://www.onlamp.com/pub/a/onlamp/excerpt/mysqlckbk/index2.html?page=1 Much appreciated strategy advice. Is the Python API mentioned: seq1=cursor1.insert_id() MySQLdb? Or another specific Python-MySQL API? [Should it be applicable to any Python Database API Specification v2.0 API ?] That approach will really help, thanks. Eric Pederson P.S. In absence of this kind of technique I was struggling with the idea of multiple SELECT FROM's within a single INSERT (is that even possible?) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Python API ...
. In absence of this kind of technique I was struggling with the idea of multiple SELECT FROM's within a single INSERT (is that even possible?) I'm not sure what you mean here. Will something like this work (with a correction of syntax which is unknown to me)? INSERT INTO webpages(author_key, subject_key, title, description) SELECT MAX(author_key) from authors, SELECT MAX(subject_key), title, description, from subjects; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
web hosting quesiong (slightly off topic)
I was wondering if anyone could recommend a good web hosting company for an Apache - php - MySQL project. I don't need much bandwidth or disk space to start out, but may need more if the site gets big. I would also like to have ssh access to the server, preferably a linux server. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: web hosting quesiong (slightly off topic)
On Wed, 2004-01-07 at 07:09, Chris W wrote: I was wondering if anyone could recommend a good web hosting company for an Apache - php - MySQL project. I don't need much bandwidth or disk space to start out, but may need more if the site gets big. I would also like to have ssh access to the server, preferably a linux server. Chris W Check out colossus.net (http://www.colossus.net). I've used them for years. They have options available from sharing a server with several other people up to having your own dedicated server. The last time I checked, they had two T-3, one T-1, and some kind of Ethernet connection. They also have good tech support. They are running Linux (not sure which distro), Apache, PHP, MySQL, ssh, etc. I highly recommend them. Don Henson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: web hosting quesiong (slightly off topic)
On Wed, Jan 07, 2004 at 08:09:50AM -0600, Chris W wrote: I was wondering if anyone could recommend a good web hosting company for an Apache - php - MySQL project. I don't need much bandwidth or disk space to start out, but may need more if the site gets big. I would also like to have ssh access to the server, preferably a linux server. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] I run a BSD server with the requirements you are looking for. http://www.nk.ca for more information . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: web hosting quesiong (slightly off topic)
We do not allow SSH access to our virtual hosts but we have dedicated servers starting at $49 per month. JR -- RHCE #808003122507415 MySQL #206067847 Ask Me About Top Notch Web Hosting Programming! -- Computers are like air conditioners: They stop working properly if you open windows. -Original Message- From: Chris W [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 8:10 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: web hosting quesiong (slightly off topic) I was wondering if anyone could recommend a good web hosting company for an Apache - php - MySQL project. I don't need much bandwidth or disk space to start out, but may need more if the site gets big. I would also like to have ssh access to the server, preferably a linux server. Chris W -- 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]
