DATEDIFF and TIMEDIFF
Best groupmember, I run version 3.23.58 and need to use something similar to DATEDIFF and TIMEDIFF to calculate difference between two a timestamp and current_timestamp(). Is there any other function that is working for version 3.23.58 that do the same job? Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATEDIFF and TIMEDIFF
Peter Lauri schrieb: Best groupmember, I run version 3.23.58 and need to use something similar to DATEDIFF and TIMEDIFF to calculate difference between two a timestamp and current_timestamp(). Is there any other function that is working for version 3.23.58 that do the same job? Best regards, Peter Lauri Cast it into seconds and calculate the difference. -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table so slow to read
Gabriel Mahiques schrieb: My name is Gabriel, Saludos Cordiales is the same than Best Regard in spanish. The server explanin is the same. The table structure is the same, the application is the same (redirect the data source only), the quantity of record is the same. All is the same, I copy the database from one server to other But the servers are not the same, right? This could be one problem. The other problem might be missing indezies on your other server. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5 Calculate Field query
Hi all, I found something strange with calculate field query in MySQL 5. My base table structure like this : CREATE TABLE `BrgIn2` ( `PO` varchar(17) NOT NULL default '', `BrgId` int(10) NOT NULL default '0', `Qty` decimal(10,3) NOT NULL default '0.000', `Price` decimal(19,4) NOT NULL default '0.', `Kurs` decimal(10,4) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Create Table testig Select PO,BrgId,Qty,Price,Kurs, Qty*Price*Kurs AS Amount from BrgIn2 Result in MySQL 4.1x CREATE TABLE `testig` ( `PO` varchar(17) NOT NULL default '', `BrgId` int(10) NOT NULL default '0', `Qty` decimal(10,3) NOT NULL default '0.000', `Price` decimal(19,4) NOT NULL default '0.', `Kurs` decimal(10,4) default NULL, `Amount` double(21,4) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Result in MySQL 5.0.20a CREATE TABLE `testig` ( `PO` varchar(17) NOT NULL default '', `BrgId` int(10) NOT NULL default '0', `Qty` decimal(10,3) NOT NULL default '0.000', `Price` decimal(15,4) NOT NULL default '0.', `Kurs` decimal(10,4) default NULL, `Amount` decimal(35,11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 The result field is different size. Is it normal behaviour for MySQL 5 or bug? Because i have trouble with this behaviour in my apps. I use Delphi 5/6 for my development tool. regards, Hendro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Add a new value in an ENUM by manipulate .frm
Hi,
I need to add another value in an enum so ALTER TABLE table1 MODIFY
Status ENUM('a','b','c') where 'c' is the new value. My problem is
that the tables are VERY big and it would take days to alter them all.
So, i figure the values in enum:s are in the .frm file so why not change
it and leave the rest. I created a new empty table and altered it and
then copied the new .frm file over the old one.
... and it does seem to work! Select, update works fine and I can use
the new value.
My question is if its OK? Has it been done before? Do I miss anything here?
Regards
Jorgen
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Add a new value in an ENUM by manipulate .frm
Jörgen Winqvist schrieb:
Hi,
I need to add another value in an enum so ALTER TABLE table1 MODIFY
Status ENUM('a','b','c') where 'c' is the new value. My problem is
that the tables are VERY big and it would take days to alter them all.
Tried it?
I also use big tables but adding a field takes a few seconds.
Well it does take long if there is a key on the field.
That would rise the querytime.
So, i figure the values in enum:s are in the .frm file so why not change
it and leave the rest. I created a new empty table and altered it and
then copied the new .frm file over the old one.
... and it does seem to work! Select, update works fine and I can use
the new value.
My question is if its OK? Has it been done before? Do I miss anything here?
Probably the indizes might not work properly anymore.
But i am not so much into MySQL that i could tell you how MySQL works
behind ALTER TABLE.
Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert Select problem
I get this error: Fehler in /home/virtual/site4/fst/var/www/html/adminheaven/artikel-vererben-save.php in Zeile 36 mit Error:br Query:INSERT INTO objektflyer_verknuepfung (av_o_id_haupt,av_o_id_link,av_text,av_op_id) SELECT 418,av_o_id_link,av_text,av_op_id FROM objektflyer_verknuepfung WHERE av_id IN (1) brNot unique table/alias: 'objektflyer_verknuepfung' I use a very old Mysql. 3.2 or similiar. Anyone knows that error and can give a helping hand? Thanks, Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Add a new value in an ENUM by manipulate .frm
Barry wrote:
Jörgen Winqvist schrieb:
Hi,
I need to add another value in an enum so ALTER TABLE table1 MODIFY
Status ENUM('a','b','c') where 'c' is the new value. My problem is
that the tables are VERY big and it would take days to alter them all.
Tried it?
I also use big tables but adding a field takes a few seconds.
Well it does take long if there is a key on the field.
That would rise the querytime.
Yes I have tried it and it takes hours on a table 4 Gb data and 4 Gb
index with apx 50 milj rows and i have 15 of them. The enum field is not
in any indexes.
So, i figure the values in enum:s are in the .frm file so why not
change it and leave the rest. I created a new empty table and altered
it and then copied the new .frm file over the old one.
... and it does seem to work! Select, update works fine and I can use
the new value.
My question is if its OK? Has it been done before? Do I miss anything
here?
Probably the indizes might not work properly anymore.
But i am not so much into MySQL that i could tell you how MySQL works
behind ALTER TABLE.
Barry
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert Select problem
Hi Barry, see: http://dev.mysql.com/doc/refman/5.0/en/insert-select.html you cannot insert into a table you're doing select on (same goes for update). Regards, /Johan Barry skrev: I get this error: Fehler in /home/virtual/site4/fst/var/www/html/adminheaven/artikel-vererben-save.php in Zeile 36 mit Error:br Query:INSERT INTO objektflyer_verknuepfung (av_o_id_haupt,av_o_id_link,av_text,av_op_id) SELECT 418,av_o_id_link,av_text,av_op_id FROM objektflyer_verknuepfung WHERE av_id IN (1) brNot unique table/alias: 'objektflyer_verknuepfung' I use a very old Mysql. 3.2 or similiar. Anyone knows that error and can give a helping hand? Thanks, Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert Select problem
Johan Höök schrieb: Hi Barry, see: http://dev.mysql.com/doc/refman/5.0/en/insert-select.html you cannot insert into a table you're doing select on (same goes for update). But i am doing it on a test server version 5.x and it works like a charm :) -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Memory Problem causing mysql to crash
Hi, I'm trying to resolve why InnoDB is crashing. It happened twice for the last month without obvoius reason Any help will be appreciated. Dobromir Velev My Server is Red Hat Enterprise Linux ES release 3 (Taroon Update 7) 2.4.21-32.0.1.ELsmp Dual 3.2 GHz Intel Xeon 8 GB RAM with 3 x 146GB SCA Ultra 320 10K RPM SCSI Drives my.cnf settings innodb_buffer_pool_size=2000M innodb_additional_mem_pool_size=20M innodb_log_file_size=150M innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 innodb_lock_wait_timeout=50 key_buffer_size=1000M read_buffer_size=500K read_rnd_buffer_size=1200K sort_buffer_size=1M thread_cache=256 thread_concurrency=8 thread_stack=126976 myisam_sort_buffer_size=64M max_connections=600 The error log shows the following message: InnoDB: Fatal error: cannot allocate 1048576 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 2263507272 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=1048576000 read_buffer_size=507904 max_used_connections=600 max_connections=600 threads_connected=473 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1935995 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. You seem to be running 32-bit Linux and have 473 concurrent connections. If you have not changed STACK_SIZE in LinuxThreads and built the binary yourself, LinuxThreads is quite likely to steal a part of the global heap for the thread stack. Please read http://www.mysql.com/doc/L/i/Linux.html thd=(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbff1f558, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8072d74 0x826d678 0x8213c74 0x8213d04 0x8218b84 0x81d5ba6 0x80fd659 0x826ae2c 0x82a0cda New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 060503 16:37:21 mysqld restarted 060503 16:37:21 Can't start server: Bind on TCP/IP port: Address already in use 060503 16:37:21 Do you already have another mysqld server running on port: 3306 ? 060503 16:37:21 Aborting and the resolved stack trace is 0x8072d74 handle_segfault + 420 0x826d678 pthread_sighandler + 184 0x8213c74 ut_malloc_low + 132 0x8213d04 ut_malloc + 20 0x8218b84 os_aio_simulated_handle + 916 0x81d5ba6 fil_aio_wait + 214 0x80fd659 io_handler_thread + 25 0x826ae2c pthread_start_thread + 220 0x82a0cda thread_start + 4 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Q2. Is there anything could be done to speed up this query
Thank you Dan, I will try that. Mikhail Berman -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 03, 2006 5:29 PM To: Mikhail Berman Cc: Chris White; mysql@lists.mysql.com Subject: Re: Q2. Is there anything could be done to speed up this query In the last episode (May 03), Mikhail Berman said: Thank you, Chris But the table is indexed on the field you are referring to and the other one the query, which is evident from this: KEY `prdadadx` (`price_data_date`), KEY `prdatidx` (`price_data_ticker`) These are two separate keys, though, and your query is doing a GROUP BY across both fields, so neither of those keys would be useful (mysql would have to do a random record lookup for each row to fetch the other field). Try an index on (price_data_ticker, price_data_date). Since your query only references those fields, mysql should be able to return your results just by scanning the index. -- Dan Nelson [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]
Help on VB and ODBC
Hi everybody, Please I need help on this. I am beginning a new application. I am doing it with VB 6.0 - MySQL 5.0 - MyODBC 3.51 on Windows Is there any parameter i need to establish in order to be able to work with RecordSets in the Client side , actually i can't because if i do, when i change any value i receive the following message: Error '-2147217900 (80040e14) en tiempo de ejecucion: [MySQL][ODBC 3.51 Driver][mysqld-5.0.15]You have an error in your SQL syntax; check the manual that corresponds to your MySQL Server version for the right syntax to use near'. ´catpartes´ SET ´condicion´ =2 WHERE ´CveCia´ =1 and ´Almacen´ ='01' and ´NoParte´ at line 1 If i change the CursorLocation to 3 - adUseClient i don't receive the above message and the information is updated correctly . But with the CursorLocation 2 - adUseServer the DataList, DataCombo and DataGrid dont work. Thanks in advance. - Do You Yahoo!? La mejor conexión a Internet y 2GB extra a tu correo por $100 al mes. http://net.yahoo.com.mx
blank user names in user table
Hi all, I've got a database I recently inherited where there are a number of records in the mysql.user table that have no user id. According to the mysql docs, this is supposed to allow guest access, and there is mention of how to turn it off. How does one actually connect in guest mode? My attempts with the mysql client have so far been in vain. How can I ask mysql to log connection attempts by users so I can see if any of these ID's are actually in use? Thank you. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5 Calculate Field query
In the last episode (May 04), Hendro Suryawan said: Hi all, I found something strange with calculate field query in MySQL 5. My base table structure like this : CREATE TABLE `BrgIn2` ( `Qty` decimal(10,3) NOT NULL default '0.000', `Price` decimal(19,4) NOT NULL default '0.', `Kurs` decimal(10,4) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Create Table testig Select PO,BrgId,Qty,Price,Kurs, Qty*Price*Kurs AS Amount from BrgIn2 Result in MySQL 4.1x CREATE TABLE `testig` ( `Qty` decimal(10,3) NOT NULL default '0.000', `Price` decimal(19,4) NOT NULL default '0.', `Kurs` decimal(10,4) default NULL, `Amount` double(21,4) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Result in MySQL 5.0.20a CREATE TABLE `testig` ( `Qty` decimal(10,3) NOT NULL default '0.000', `Price` decimal(15,4) NOT NULL default '0.', `Kurs` decimal(10,4) default NULL, `Amount` decimal(35,11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 The result field is different size. Is it normal behaviour for MySQL 5 or bug? Because i have trouble with this behaviour in my apps. I use Delphi 5/6 for my development tool. MySQL 5's result is correct. MySQL 4 converts decimal values to double when doing math, where MySQL 5 has a true fixed-point math package to process double values. http://dev.mysql.com/doc/refman/5.0/en/precision-math.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Coded fields
What is the best way to create a coded field? I want to do something
similar to enumeration but I don't want to have to define the values at
table creation time because sometimes the end-users need to add or remove
the codes.
I've been using char binary fields in my database to this point figuring
that takes only one byte per stored code. Then the values can be ASCII
chars and would be kind of meaningful if retrieved from the database. For
instance, I might have 'f' for freshman, 'S' for Sophomore, 'J' for Junior,
and 's' for Senior. Actually, there's like 20 different categories for
students. So then I have a lookup table for codes:
create table codes (
code_type varchar(10,
code_key char binary,
code_text varchar(80)
);
Then I can do left joins to retrieve a description of the code if
necessary. For example:
INSERT INTO codes VALUES ('class', 'F', 'Freshman');
INSERT INTO codes VALUES (class', 'S', 'Sophomore');
INSERT INTO codes VALUES ('class', 'J', 'Junior');
INSERT INTO codes ('class', 's', 'Senior');
SELECT C.code_text AS academic_class
FROM students S
LEFT JOIN codes C ON (C.code_type='class' AND S.class=C.code_key);
That particular example might work better with enum but it's a deliberately
trivialized example. Most of my coded fields have 5-20 possible values.
My problem is that I've had some codes imposed upon me that are 5 chars. I
don't know if I should just start over or what. Maybe other people deal
with coded fields in a totally different way that is way better than what
I've invented.
Suggestions?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Adding a second slave and Load Data from master questions
Hi. im trying to add a second slave using load data from master ,and
it seems to me that when i run this command on the
new slave that its simply picking up from where the first slave is
replicating from. Does this make sense? Also interesting is that
when both the IO and SQL threads are No on the new slave, it
downloads until 661 MB then stops. Another fact is that the first
slave is actually
out of sync.
So i have some questions on load data from master:
is it designed to work with a partway updated slave, or is it only
designed to work from a completely blank database?
Heres the error logs from the new slave im trying to set up.
May 4 09:29:41 localhost mysqld[29920]: 060504 9:29:41
[Note] Slave SQL thread initialized, starting replication in log
'FIRST' at position 0, relay log './djembe-relay-bin.01' position: 4
May 4 09:29:52 localhost mysqld[29920]: 060504 9:29:52
[Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]',
replication started in log 'FIRST' at position 4
May 4 09:29:52 localhost mysqld[29920]: 060504 9:29:52
[ERROR] Slave: Error 'Table 'tracking.session' doesn't exist' on
query. Default database: 'tracking'. Query: 'insert into `session`
(phpsessio
nid, useragent, remoteip, guid, userid, entryurl, referurl,
created) values('10e55f72ff0321de6199df3c650608d3', 'Python-urllib/
1.15', '10.2.1.11', NULL, NULL, '/', NULL, NULL)', Error_code: 1146
May 4 09:29:52 localhost mysqld[29920]: 060504 9:29:52
[ERROR] Error running query, slave SQL thread aborted. Fix the
problem, and restart the slave SQL thread with SLAVE START. We
stopped at log
'mysql-bin.000256' position 4
May 4 09:30:06 localhost mysqld[29920]: 060504 9:30:06
[ERROR] Slave I/O thread killed while reading event
May 4 09:30:06 localhost mysqld[29920]: 060504 9:30:06
[ERROR] Slave I/O thread exiting, read up to log 'mysql-bin.000256',
position 6268185
Hunter Peress
[EMAIL PROTECTED]
Web Programmer
The New Mexican, Inc.
www.freenewmexican.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.19 has been released
Hi, MySQL 4.1.19, a new version of the popular Open Source Database Management System, has been released. The Community Edition is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is a bugfix release for the recent production release family. This MySQL 4.1.19 release includes the patches for recently reported security vulnerabilites in the MySQL client-server protocol. We would like to thank Stefano Di Paola [EMAIL PROTECTED] for finding and reporting these to us. This section documents all changes and bug fixes that have been applied since the last official MySQL release. If you would like to receive more fine-grained and personalised update alerts about fixes that are relevant to the version and features you use, please consider subscribing to MySQL Network (a commercial MySQL offering). For more details please see http://www.mysql.com/network/advisors.html. We welcome and appreciate your feedback! Functionality added or changed: * Security enhancement: Added the global max_prepared_stmt_count system variable to limit the total number of prepared statements in the server. This limits the potential for denial-of-service attacks based on running the server out of memory by preparing huge numbers of statements. The current number of prepared statements is available through the prepared_stmt_count system variable. (Bug#16365: http://bugs.mysql.com/16365) * InnoDB now caches a list of unflushed files instead of scanning for unflushed files during a table flush operation. This improves performance when --innodb-file-per-table is set on a system with a large number of InnoDB tables. (Bug#15653: http://bugs.mysql.com/15653) * New charset command added to mysql command-line client. By typing charset name or \C name (such as \C UTF8), the client character set can be changed without reconnecting. (Bug#16217: http://bugs.mysql.com/16217) * Large file support was re-enabled for the MySQL server binary for the AIX 5.2 platform. (Bug#13571: http://bugs.mysql.com/13571) * When using the GROUP_CONCAT() function where the group_concat_max_len system variable was greater than 512, the type of the result was BLOB only if the query included an ORDER BY clause; otherwise the result was a VARCHAR. The result type of the GROUP_CONCAT() function is now VARCHAR only if the value of the group_concat_max_len system variable is less than or equal to 512. Otherwise, this function returns a BLOB. (Bug#14169: http://bugs.mysql.com/14169) Bugs fixed: * Security fix: A malicious client, using specially crafted invalid login or COM_TABLE_DUMP packets was able to read uninitialized memory, which potentially, though unlikely in MySQL, could have led to an information disclosure. Thanks to Stefano Di Paola [EMAIL PROTECTED] for finding and reporting this bug. * NDB Cluster: A simultaneous DROP TABLE and table update operation utilising a table scan could trigger a node failure. (Bug#18597: http://bugs.mysql.com/18597) * NDB Cluster: Backups could fail for large clusters with many tables, where the number of tables approached MaxNoOfTables. (Bug#17607: http://bugs.mysql.com/17607) * The IN-to-EXISTS transformation was making a reference to a parse tree fragment that was left out of the parse tree. This caused problems with prepared statements. (Bug#18492: http://bugs.mysql.com/18492) * Attempting to set the default value of an ENUM or SET column to NULL caused a server crash. (Bug#19145: http://bugs.mysql.com/19145) * Index corruption could occur in cases when key_cache_block_size was not a multiple of myisam_block_size (for example, with key_cache_block_size=1536 and myisam_block_size=1024). (Bug#19079: http://bugs.mysql.com/19079) * UNCOMPRESS(NULL) could cause subsequent UNCOMPRESS() calls to return NULL for legal non-NULL arguments. (Bug#18643: http://bugs.mysql.com/18643) * Conversion of a number to a CHAR UNICODE string returned an invalid result. (Bug#18691: http://bugs.mysql.com/18691) * A call to MIN() with a CASE expression as its argument could return a non-minimum value. (Bug#17896: http://bugs.mysql.com/17896) * A LOCK TABLES statement that failed could cause MyISAM not to update table statistics properly, causing a subsequent CHECK TABLE to report table corruption. (Bug#18544: http://bugs.mysql.com/18544) * Avoid trying to include asm/atomic.h when it doesn't work in C++ code. (Bug#13621: http://bugs.mysql.com/13621) * Executing SELECT on a large table that had been compressed within myisampack could cause
RE: Fixing Databases When Replication Is Enabled?
I assume you are referring to this thread: http://lists.mysql.com/mysql/197528 If so, a very important question still stands: What version of MySQL are you using? MySQL replication uses the binary log (binlog) to pass update queries (INSERT, UPDATE, etc) to the slave; in other words it does not operate based on what data actually changed on the master. Take this statement for example: UPDATE table SET var = 'foo' WHERE seqid = 123 To simplify this example, let's just say that the above query is all that is written to the binlog. Once the slave asks for a replication update, the master will send the above query as-is. It does not say Record ID 123 changed var = 'foo'. So, if you are running MySQL 4.0 or less; the commands that mysqlcheck send the master will NOT be replicated to the slave. If you are running MySQL 4.1 or up; the commands that mysqlcheck (by default) send the master will be replicated to the slave. I hope this made sense. :) Atle - Flying Crocodile Inc, Unix Systems Administrator On Wed, 3 May 2006, Robinson, Eric wrote: So, just to be clear, when I run: mysqlcheck -r -f database_name Any fixes are recorded to the binlog and replicated to the slave? I want to be sure about this because someone in this forum said the opposite a couple of weeks ago. Thanks! --Eric -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 02, 2006 8:23 AM To: Marciano Cc: Robinson, Eric; mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? My Sincere apologies, I intented to mean mysqlcheck but somehow came out as myisamchk :) Kishore Jalleda On 5/2/06, Marciano [EMAIL PROTECTED] wrote: How myisamchk can write to binlog if the server need to be down? - Mensagem Original De: Kishore Jalleda [EMAIL PROTECTED] Para: Robinson, Eric [EMAIL PROTECTED] Cópia: mysql@lists.mysql.com Assunto: Re: Fixing Databases When Replication Is Enabled? Data: 01/05/06 22:25 Yes On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck, not myisamchk or REPAIR. Is what you said still true? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 1:56 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... Kishore Jalleda http://kjalleda.googlepages.com/projects -- 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] Yes On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck, not myisamchk or REPAIR. Is what you said still true? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 1:56 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... Kishore Jalleda http://kjalleda.googlepages.com/projects -- 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] __ Webmail Intercol http://www.intercol.com.br -- MySQL General Mailing List For
Mysql on Production Servers
Hi, I have doubt on Mysql download and installation 1.Which mysql download is better to install on production server rpm installation or binary installation or source installation 2.What are the difference between them. Regards, Shivaji.
Re: New to TRIGGER and CALL. Example gives errors.
(catching up on old mail) What version of MySQL are using? It works in 5.0.19 and 5.1.9-beta. If you're trying on a version 5.0, it won't work. -Sheeri On 4/9/06, Daevid Vincent [EMAIL PROTECTED] wrote: I'm trying to follow the example in the manual to create a trigger: http://dev.mysql.com/doc/refman/5.0/en/using-triggers.html #DROP TRIGGER upd_check; delimiter // CREATE TRIGGER upd_check BEFORE UPDATE ON starkeys FOR EACH ROW BEGIN IF NEW.skey 1 THEN SET NEW.skey = 1; ELSEIF NEW.skey 9 THEN SET NEW.skey = 9; END IF; END;// delimiter ; All I'm trying to do is enforce that my starkeys.skey column is always in the range of 1 through 9. I was planning to start with this example and work my way up. Ideally it should check on UPDATE or INSERT. The manual recommended: It can be easier to define a stored procedure separately and then invoke it from the trigger using a simple CALL statement. This is also advantageous if you want to invoke the same routine from within several triggers. But I don't know how to do that yet. vmware public_html # mysql --version mysql Ver 14.12 Distrib 5.0.19, for pc-linux-gnu (i686) using readline 5.1 But I just get these errors: Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delimiter // CREATE TRIGGER upd_check BEFORE UPDATE ON starkeys FOR EACH ROW BEG' at line 2 (0 ms taken) Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ELSEIF NEW.skey 9 THEN SET NEW.skey = 9' at line 1 (0 ms taken) Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF' at line 1 (0 ms taken) Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1 (0 ms taken) Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '// delimiter' at line 1 (0 ms taken) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql on Production Servers
Well I would not bother with the source distro as it can take a while to compile, and possibly end up being slower than a pre-compiled version. Personally I would choose between the .tar.gz or the RPM versions. The RPM version is basically self-installing, and also installs the scripts to start the server at boot-up time. You could download and install the RPM binary to get you started, then download the .tar.gz binary to do a custom installation later on if you wanted to run different versions at the same time, eg for testing upgrades before making using them on the live databases. The statically-linked .tar.gz binaries would be the most flexible option as they do not eally on any external libraries at run-time. AFAIK you cannot run two different RPM binary versions at the same time, because you have no control over where the RPM packages are installed. Normally a RPM upgrade will overwrite the previous RPM version - which is OK if you are only doing minor upgrades, and are confident that there will be no problems with the upgrade process. The .tar.gz binaries give you the option to choose where to install the server files. This means you can have different versions of the .tar.gz distro's on one machine, and run each mysql server on the same machine for testing your upgrades, by using a different port, socket pid-file and database (even a snapshot of your live data for testing purposes that you can throw away later). Each mysql version could have it's own unique my.cnf file with just a [mysqld] section and nothing else for that particular version, in it's installation directory. Eg. # /usr/local/mysql-version/my.cnf # MySQL server configuration file # last updated 2006-03-08 # mysql client program configuration file lives in /etc/my.cnf #--- # mysqld server configuration options #--- [mysqld] basedir=/usr/local/mysql-5.0.18 ## use for testing multiple instances of mysqld ## these parameters are normally passed to mysqld ## from the start-mysql-5.0.18 script ## ##basedir=/usr/local/mysql-5.0.18 ##port=7005 ##socket=/usr/local/mysql-5.0.18/data/mysql.sock ##pid-file=/usr/local/mysql-5.0.18/data/laptop.pid ##datadir=/usr/local/mysql-5.0.18/data ##user=mysql server-id=1 #skip-networking skip-name-resolve skip-locking set-variable = key_buffer=16M set-variable = max_allowed_packet=1M set-variable = table_cache=64 set-variable = sort_buffer=512K set-variable = net_buffer_length=8K set-variable = myisam_sort_buffer_size=8M # logging options log=5-0-18.log log-bin=laptop-bin log-error=5-0-18.error-log log-queries-not-using-indexes log-slow-admin-statements log-slow-queries=5-0-18.slow-log log-warnings #--- # end of mysqld server configuration file # /usr/local/mysql-version/my.cnf As /etc/my.cnf is the first place all mysql client programs seem to look by default (as well as the server, which is why I do not put any configuration section for the server in /etc/my.cnf), your mysql clients could all share one common my.cnf, Eg. # /etc/my.cnf # MySQL client program configuration file # last updated 2006-03-05 # mysqld server configuration file lives in # /usr/local/mysql-version/my.cnf #--- # mysql client program configuration options #--- [mysql] no-auto-rehash # needed for security - to stops multiple deletes/updates # without a where clause safe-updates #--- # The following options will be passed to all MySQL clients [client] socket = /var/lib/mysql/mysql.sock port = #--- [myisamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M #--- [mysqldump] quick set-variable = max_allowed_packet=16M #--- # available programs/scripts are: #my_print_defaults mysqladmin #myisamchk mysqlbinlog #myisamlog mysqlbug - n/a #myisampack mysqlcheck #mysql mysqld #mysql_convert_table_format - .plmysqld_multi #mysql_find_rows mysqldump #mysql_fix_privilege_tables n/a mysqlhotcopy - .pl #mysql_install_dbmysqlimport #mysql_setpermission - .pl mysqlshow #mysql_zap mysqltest #mysqlaccess - .pl safe_mysqld
Re: Effective-dating
Coming to the table SOOO late. But this has special relevance as I'm
working on an application that stores event dates and therefore will
also need to solve this problem.
The biggest question I have is What will this be used for? My first
thought is to have at least 2 tables -- one table with the rows that
are expired and another with non-expired rows.
In an events database, for example probaby 75% of the queries will be
current and future events. Folks will be interested in past events,
but usually as a part of a separate logical flow. Users may want past
events for research, but probably won't need to compare previous
events to current/future ones. (they might want to compare events all
in the past, for instance how many people attended each meeting,
average rating, etc; or they might want to compare current/future
events for conflicts. etc. But rarely both in the same query).
If you have reporting to do that might include both tables, you could
replicate them to MyISAM tables and make a MERGE table for your
reporting purposes.
Sorry this is so late; usually I get to MySQL list mail about once a
week, but the Users Conference took up a lot of my time! I hope this
helps
-Sheeri
On 4/10/06, Douglas Sims [EMAIL PROTECTED] wrote:
Does anyone know of a thorough discussion of effective dating on the
web, particularly with respect to MySQL, or have any opinions you
could share?
I've worked with effective-dated tables in MS SQL Server and never
been particularly awe-struck by how well it works. I can think of
three ways of doing it:
1) Store a Begin date and an End date for each row and then
reference the row with 'WHERE {transaction date} BETWEEN {begin} AND
END. This inevitably winds up with overlapping rows that shouldn't
be or gaps where you don't want them, and also requires an extra date
column, but the select queries are simplest. Also, what about
indexing the dates?
2) Store an Expires date with each row, but then to find the actual
row you have to do a subselect or some messy joins and I'm not at all
confident this will be optimized reasonably.
3) Store an Effective as of date with each row but this has
essentially the same problem as 2.
None of the SQL books on my shelf even mentions this, including
Jeremy Zawodny's Hi-Performance MySQL and the MySQL Reference
Manual.
This page is interesting but doesn't explain the different options
nor try to analyze which is best and under what circumstances: http://
llamasery.com/forums/showthread.php?p=34945
Strangely enough, most of what I find by googling the topic
effective dating has to do with meeting girls efficiently - which
is also interesting, but outside the scope of this list and not
immediately relevant to the system I'm working on.
Douglas Sims
[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: Effective-dating
I don't think I noticed the original post for this discussion when it first
arrived - or maybe I did and mistook it for spam having to do with social
dating ;-) - but I just saw Sheeri's reply and want to jump in if I may.
Most professional databases in corporate environments that I have seen over
the years use the approach of having begin and end dates on each row. Those
dates indicate when the row is valid. For instance, if the row is recording
interest rates, the table would get a new row whenever the current interest
rate changed. Assuming no one knew when the interest rate would change
again, the new row would typically set the current date as the begin date
and the end date would be set to null. The row for the previous interest
rate would be updated at the same time and have its end date set to the
current date (or the day before in some cases).
Queries could easily determine the current interest rate by just finding the
only row in the table whose end date was null. Older interest rates could be
find by searching for the row whose begin date was on or before the search
date and whose end date was on or after the search date. Eventually, as
older interest rates were no longer desired for queries very often, you
might move them to archive tables with names like IntRates_2005 and remove
them from the main interest rates table altogether.
I don't know enough about performance in MySQL to venture an opinion of how
this would perform. You'll need to research that yourself.
--
Rhino
- Original Message -
From: sheeri kritzer [EMAIL PROTECTED]
To: Douglas Sims [EMAIL PROTECTED]
Cc: mysql List mysql@lists.mysql.com
Sent: Thursday, May 04, 2006 3:15 PM
Subject: Re: Effective-dating
Coming to the table SOOO late. But this has special relevance as I'm
working on an application that stores event dates and therefore will
also need to solve this problem.
The biggest question I have is What will this be used for? My first
thought is to have at least 2 tables -- one table with the rows that
are expired and another with non-expired rows.
In an events database, for example probaby 75% of the queries will be
current and future events. Folks will be interested in past events,
but usually as a part of a separate logical flow. Users may want past
events for research, but probably won't need to compare previous
events to current/future ones. (they might want to compare events all
in the past, for instance how many people attended each meeting,
average rating, etc; or they might want to compare current/future
events for conflicts. etc. But rarely both in the same query).
If you have reporting to do that might include both tables, you could
replicate them to MyISAM tables and make a MERGE table for your
reporting purposes.
Sorry this is so late; usually I get to MySQL list mail about once a
week, but the Users Conference took up a lot of my time! I hope this
helps
-Sheeri
On 4/10/06, Douglas Sims [EMAIL PROTECTED] wrote:
Does anyone know of a thorough discussion of effective dating on the
web, particularly with respect to MySQL, or have any opinions you
could share?
I've worked with effective-dated tables in MS SQL Server and never
been particularly awe-struck by how well it works. I can think of
three ways of doing it:
1) Store a Begin date and an End date for each row and then
reference the row with 'WHERE {transaction date} BETWEEN {begin} AND
END. This inevitably winds up with overlapping rows that shouldn't
be or gaps where you don't want them, and also requires an extra date
column, but the select queries are simplest. Also, what about
indexing the dates?
2) Store an Expires date with each row, but then to find the actual
row you have to do a subselect or some messy joins and I'm not at all
confident this will be optimized reasonably.
3) Store an Effective as of date with each row but this has
essentially the same problem as 2.
None of the SQL books on my shelf even mentions this, including
Jeremy Zawodny's Hi-Performance MySQL and the MySQL Reference
Manual.
This page is interesting but doesn't explain the different options
nor try to analyze which is best and under what circumstances: http://
llamasery.com/forums/showthread.php?p=34945
Strangely enough, most of what I find by googling the topic
effective dating has to do with meeting girls efficiently - which
is also interesting, but outside the scope of this list and not
immediately relevant to the system I'm working on.
Douglas Sims
[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]
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.3/331 - Release Date: 03/05/2006
--
No virus found
Re: Index merge optimization (with OR) and table joins
(again, apologies for the lateness...) MySQL has a cost-based optimizer. If it's deciding that a full-table scan is appropriate, there's a reason. If more than 30% (approx) of the table would be returned in a range query, the optimizer reasons that it's LESS expensive to just do a full table scan. Otherwise, if say you're returning 50% of the rows, you have to find the pointer to the row using the index, then go to the row. Doing a table scan eliminates needing that extra step of the index. Next time full SHOW CREATE TABLE On 4/11/06, Stuart Brooks [EMAIL PROTECTED] wrote: Hi, I have been having a hassle getting the index_merge to work as expected when I am joining 2 tables on MySQL 5.0.19. The following example should make it clear: Table A key1 (primary key) key2 some_data Table B key1 (indexed) key2 (indexed) more_data SELECT a.key1,a.key2,b.more_data FROM A as a JOIN B as b ON ((a.key1=b.key1) OR (a.key2=b.key2)) WHERE (a.key1=10); This works as expected. An EXPLAIN yields : a | const| PRIMARY b | index_merge | key1,key2 However if I make the WHERE clause a range (or remove it altogether): SELECT a.key1,a.key2,b.more_data FROM A as a JOIN B as b ON ((a.key1=b.key1) OR (a.key2=b.key2)) WHERE (a.key110) #optional I end up with... a | range | PRIMARY b | ALL | none which is a brute force attack on table b. Am I missing something here, I would have expected it to use an index merge on table b in both cases. Is there a way to force it to use the index merge? Regards Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index merge optimization (with OR) and table joins
On 5/4/06, sheeri kritzer [EMAIL PROTECTED] wrote: (again, apologies for the lateness...) MySQL has a cost-based optimizer. If it's deciding that a full-table scan is appropriate, there's a reason. If more than 30% (approx) of the table would be returned in a range query, the optimizer reasons that it's LESS expensive to just do a full table scan. Otherwise, if say you're returning 50% of the rows, you have to find the pointer to the row using the index, then go to the row. Doing a table scan eliminates needing that extra step of the index. Next time full SHOW CREATE TABLE statements would be useful. -Sheeri On 4/11/06, Stuart Brooks [EMAIL PROTECTED] wrote: Hi, I have been having a hassle getting the index_merge to work as expected when I am joining 2 tables on MySQL 5.0.19. The following example should make it clear: Table A key1 (primary key) key2 some_data Table B key1 (indexed) key2 (indexed) more_data SELECT a.key1,a.key2,b.more_data FROM A as a JOIN B as b ON ((a.key1=b.key1) OR (a.key2=b.key2)) WHERE (a.key1=10); This works as expected. An EXPLAIN yields : a | const| PRIMARY b | index_merge | key1,key2 However if I make the WHERE clause a range (or remove it altogether): SELECT a.key1,a.key2,b.more_data FROM A as a JOIN B as b ON ((a.key1=b.key1) OR (a.key2=b.key2)) WHERE (a.key110) #optional I end up with... a | range | PRIMARY b | ALL | none which is a brute force attack on table b. Am I missing something here, I would have expected it to use an index merge on table b in both cases. Is there a way to force it to use the index merge? Regards Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can't make a backup of the database?
Not sure if you're still having this problem. But if MySQL can't open the file orders_pay_methods.InnoDB I'd check the permissions in the mysql datadir. -Sheeri On 4/18/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, I'm trying to make an backup of a database and this is what I got: [EMAIL PROTECTED] mysql]# mysqldump -uroot -px OSCOMM test.sql mysqldump: Can't get CREATE TABLE for table `orders_pay_methods` (Can't open file: 'orders_pay_methods.InnoDB'. (errno: 1)) What's a problem? Note: OSCOM is database for osCommerce based store and orders_pay_methods is it's table. Thanks for any help -afan -- 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: ldd undefined symbol error
I'd check to make sure you installed mysql-devel, which includes the libraries and header files. -Sheeri On 4/18/06, tuxlinsecure [EMAIL PROTECTED] wrote: Hi, I uninstalled Mysql 3.23 (tar.gz )and installed Mysql 5.0 (rpm) on my server. When I check shared lib dependencies using the following command i get undefined symbol Any Clues? Thanks, ldd -r /usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15 libKCScriptData.so.0 = /usr/share/Fazzt/lib/libKCScriptData.so.0 (0x00b14000) libKCFC.so.0 = /usr/share/Fazzt/lib/libKCFC.so.0 (0x0052e000) libKOS.so.1 = /usr/share/Fazzt/lib/libKOS.so.1 (0x00111000) libmysqlclient_r.so.15 = not found libz.so.1 = /usr/lib/libz.so.1 (0x002f1000) libcrypt.so.1 = /lib/libcrypt.so.1 (0x0072c000) libnsl.so.1 = /lib/libnsl.so.1 (0x004fc000) libpthread.so.0 = /lib/tls/libpthread.so.0 (0x00d57000) libstdc++-libc6.2-2.so.3 = /usr/lib/libstdc++-libc6.2-2.so.3 (0x00ef1000) libm.so.6 = /lib/tls/libm.so.6 (0x0026d000) libc.so.6 = /lib/tls/libc.so.6 (0x00117000) /lib/ld-linux.so.2 = /lib/ld-linux.so.2 (0x00de2000) libgcc_s.so.1 = /lib/libgcc_s.so.1 (0x0088c000) undefined symbol: mysql_field_count (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_store_result (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_fetch_row (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_affected_rows (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_init(/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_fetch_fields (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_close (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_num_rows (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_error (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_num_fields (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_free_result (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_query (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_real_connect (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) - Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ countries) for 2¢/min or less. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlmanager logging?
Can the program write to /var/lib/mysql/mysqlmanager.log? check permissions. On 4/19/06, Duzenbury, Rich [EMAIL PROTECTED] wrote: MySQL 5.0.20 I've got two instances running with mysqlmanager. I'm not getting any logging of any sort. mysqlmanager --help shows: - - log /var/lib/mysql/mysqlmanager.log pid-file /tmp/manager.pid socket/tmp/manager.sock bind-address (No default value) port 2273 password-file /etc/mysqlmanager.passwd default-mysqld-path /usr/sbin/mysqld monitoring-interval 10 run-as-serviceFALSE user (No default value) wait-timeout 28800 I've also tried to set the --log option in the [manager] section of /etc/my.cnf and restarted the server, to no avail. Do I have to run-as-service to get logging? Thanks for any help you can offer. Regards, Rich -- 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: ORDER BY making recordset non-updatable
Maybe I'm thick You have a view, called vwMyView. You SELECT rows from it, and you're able to update the view? Yet when you SELECT with an ORDER BY clause, you're not allowed to update the view? I just do not understand how a read statement affects DML. I think you're going to have to post the query you're using, as it's more complex than a SELECT. Perhaps you're using a REPLACE INTO SELECT statement? Or UPDATE WHERE IN (SELECT)? -Sheeri On 4/19/06, Eland, Travis M. [EMAIL PROTECTED] wrote: Heya. I am in the process of modifying a program to access data from a MySQL database instead of a SQL Server database. I have a view that is referenced as follows (through use of a data environment command): Select * from vwMyView where id = ? If I run this command, I get the data that I would expect, and I am able to update the data that I would expect to update (there are a few joins in the view so there are a couple fields that I understand that I cannot update). My problem is, if I add an ORDER BY statement at the end of this command, the recordset still returns data, but it becomes non-updatable. I would include my SQL, but unfortunately it is on a classified machine. I have verified the SQL numerous times and it works fine in every way except when I use ORDER BY. The SQL structure (though slightly modified for mySQL) also worked fine in SQL Server. Is this a known issue? Is there something that I could possibly be missing? I apologize for the lack of actual code, but I appreciate any insight! Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql on Production Servers
Keith Roberts thanks for the input ,i have few doubts on your reply. 1.when i am installaing rpm based files, should my operating system contains the necessary files that are build on rpm packagei.e compilers,gccversions etc. 2.if i want to create more than one instance on my server with same mysql versions then how rpm package will help for me. 3.in case of binary installation is it recommended for production database compared to source files. Regards, Shivaji
query of a query?
The following are Cold Fusion code. It's interesting that previously defined queries can be used as 'tables' to pull data from. In MySQL, does the same functionality have to be implemented using join or subselect? === CFQUERY name=getprojects datasource=#dbname# SELECT db_entry_num, title FROM account_info /CFQUERY CFQUERY name=getprojectID datasource=#GSN# SELECT projectID FROM ResearchProjects WHERE IDNo = #url.IDNo# /cfquery cfif getprojectID.recordcount GT 0 !--- Query of a query --- cfquery dbtype=query name=getstudproj SELECT title, db_entry_num, projectID FROM getprojects, getprojectID WHEREdb_entry_num = projectID /cfquery /cfif = Thanks, Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql on Production Servers
On Thu, 4 May 2006, Shivaji S wrote: To: [EMAIL PROTECTED], mysql@lists.mysql.com From: Shivaji S [EMAIL PROTECTED] Subject: Re: Mysql on Production Servers Keith Roberts thanks for the input ,i have few doubts on your reply. 1.when i am installaing rpm based files, should my operating system contains the necessary files that are build on rpm packagei.e compilers,gccversions etc. You might like to try this distro first: Linux x86 generic RPM (statically linked against glibc 2.2.5) downloads Server 5.0.21-013.9M Pick a mirror MD5: b22c4bb165716062647f3ffb478cf04e Max 5.0.21-02.9MPick a mirror MD5: bb4c3e5764ba6c5f8439dfbc849c5ca3 Benchmark/test suites 5.0.21-06.1MPick a mirror MD5: eed868b8cc18cd45f18f33db4e738955 Client programs 5.0.21-07.0MPick a mirror MD5: 0190ce11547014465747707ea4062abf Libraries and header files 5.0.21-03.8M Pick a mirror MD5: 8c3ea8cf16346f5292cdfe6296ebc956 Shared client libraries 5.0.21-02.0MPick a mirror MD5: 28d626cda409290e8527d1d79af91e0c Shared compatibility libraries (3.23, 4.x, 5.x libs in same package) 5.0.21-0 3.7MPick a mirror MD5: 2f8aac33b2c659ef1b9bdc634b6f419e 2.if i want to create more than one instance on my server with same mysql versions then how rpm package will help for me. Please see: http://dev.mysql.com/doc/refman/5.0/en/ 5.4.3. mysqld_multi - Manage Multiple MySQL Servers 5.5. mysqlmanager - The MySQL Instance Manager 5.5.1. Starting the MySQL Server with MySQL Instance Manager 5.5.2. Connecting to the MySQL Instance Manager and Creating User Accounts 5.5.3. MySQL Instance Manager Command Options 5.5.4. MySQL Instance Manager Configuration Files 5.5.5. Commands Recognized by the MySQL Instance Manager 3.in case of binary installation is it recommended for production database compared to source files. The binaries are all built from the same source code for each version, so there should be no difference, apart from you not having to go to the trouble of compiling things yourself. Regards Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql on Production Servers
thanks keith... Regards, shivaji On Thu, 4 May 2006, Shivaji S wrote: To: [EMAIL PROTECTED], mysql@lists.mysql.com From: Shivaji S [EMAIL PROTECTED] Subject: Re: Mysql on Production Servers Keith Roberts thanks for the input ,i have few doubts on your reply. 1.when i am installaing rpm based files, should my operating system contains the necessary files that are build on rpm packagei.e compilers,gccversions etc. You might like to try this distro first: Linux x86 generic RPM (statically linked against glibc 2.2.5) downloads Server 5.0.21-013.9M Pick a mirror MD5: b22c4bb165716062647f3ffb478cf04e Max 5.0.21-02.9MPick a mirror MD5: bb4c3e5764ba6c5f8439dfbc849c5ca3 Benchmark/test suites 5.0.21-06.1MPick a mirror MD5: eed868b8cc18cd45f18f33db4e738955 Client programs 5.0.21-07.0MPick a mirror MD5: 0190ce11547014465747707ea4062abf Libraries and header files 5.0.21-03.8M Pick a mirror MD5: 8c3ea8cf16346f5292cdfe6296ebc956 Shared client libraries 5.0.21-02.0MPick a mirror MD5: 28d626cda409290e8527d1d79af91e0c Shared compatibility libraries (3.23, 4.x, 5.x libs in same package) 5.0.21-0 3.7MPick a mirror MD5: 2f8aac33b2c659ef1b9bdc634b6f419e 2.if i want to create more than one instance on my server with same mysql versions then how rpm package will help for me. Please see: http://dev.mysql.com/doc/refman/5.0/en/ 5.4.3. mysqld_multi - Manage Multiple MySQL Servers 5.5. mysqlmanager - The MySQL Instance Manager 5.5.1. Starting the MySQL Server with MySQL Instance Manager 5.5.2. Connecting to the MySQL Instance Manager and Creating User Accounts 5.5.3. MySQL Instance Manager Command Options 5.5.4. MySQL Instance Manager Configuration Files 5.5.5. Commands Recognized by the MySQL Instance Manager 3.in case of binary installation is it recommended for production database compared to source files. The binaries are all built from the same source code for each version, so there should be no difference, apart from you not having to go to the trouble of compiling things yourself. Regards Keith
question about utf and collation
I'm sorry if this is not the apropriate list, but I couldn't fined any other list where this question would fit in. If someone know where to post it, please suggest. I have a question about collation and utf in mysql. I'm using mysql on several of my websites, but the mysql database doesn't have croatian collation for utf. And most of my sites are in croatian. Is there any plan to add croatian collation for utf, and when? Thanks. -- Marko Žmak, dipl.ing.mat. Mob: +385 98 212 801 Email: [EMAIL PROTECTED] Web: http://www.studioartlan.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query of a query?
Bing Du wrote: The following are Cold Fusion code. It's interesting that previously defined queries can be used as 'tables' to pull data from. === CFQUERY name=getprojects datasource=#dbname# SELECT db_entry_num, title FROM account_info /CFQUERY CFQUERY name=getprojectID datasource=#GSN# SELECT projectID FROM ResearchProjects WHERE IDNo = #url.IDNo# /cfquery cfif getprojectID.recordcount GT 0 !--- Query of a query --- cfquery dbtype=query name=getstudproj SELECT title, db_entry_num, projectID FROM getprojects, getprojectID WHEREdb_entry_num = projectID /cfquery /cfif = It's interesting that previously defined queries can be used as 'tables' to pull data from. In MySQL, does the same functionality have to be implemented using join or subselect? Not necessarily. I would think the CREATE SELECT statement would be the closest equivalent. http://dev.mysql.com/doc/refman/4.1/en/create-table.html --J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to convert this DELETE command from MySQL 4.0.25 to 3.23?
This query running fine on 4.0.25 but when trying on 3.23 an error occurs. can one help me to find correct command for 3.23? Probably if you post the error message you get. DELETE FROM A USING A RIGHT JOIN B ON B.id = A.sectionid WHERE B.id is null error message on 3.23 is: You have an error in your SQL syntax near 'USING USING A RIGHT JOIN B ON B.id = A.sectionid' at line 1 SQL=DELETE FROM A USING A RIGHT JOIN B ON B.id = A.sectionid WHERE B.id is null BUT this query run ok in 4.0.25. i need to find error free syntaks for 3.23 version. any help? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow query using between
Hello,
I am running Ver 14.7 Distrib 4.1.12, for redhat-linux-gnu (i386)
using readline 4.3 on a Dual Xeon 2.4Ghz RHEL4 box with 4GB of RAM.
I have a query that takes anywhere from .25 - .85 seconds to run.
Following are the query and the related table structures I have
currently set up and the output from 'explain'. After twiddling with
this query for some time, i cannot seem to get it to run any faster
and was curious if i am over looking something, or am i simply stuck
with a slow query. The problem is that this query runs each time a
user comes to our website, so the slowness tends to add up a little.
Query:
select t1.city, t1.region, t1.latitude, t1.longitude from
hn_iplocation as t1,hn_iprange as t2 where t1.locid=t2.locid and
('2720518136' between t2.startIpNum and t2.endIpNum) limit 1
Explain:
mysql explain select t1.city, t1.region, t1.latitude, t1.longitude
from hn_iplocation as t1,hn_iprange as t2 where t1.locid=t2.locid and
('2720518136' between t2.startIpNum and t2.endIpNum) limit 1\G
*** 1. row ***
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 20029
Extra:
*** 2. row ***
id: 1
select_type: SIMPLE
table: t2
type: ref
possible_keys: locId
key: locId
key_len: 5
ref: helloneighbour_com_1.t1.locId
rows: 4
Extra: Using where
2 rows in set (0.00 sec)
Table structure T1:
***
mysql describe hn_iplocation;
++--+--+-+-
++
| Field | Type | Null | Key | Default |
Extra |
++--+--+-+-
++
| locId | int(16) unsigned | | PRI | NULL|
auto_increment |
| country| char(2) | YES | | NULL
||
| region | char(2) | YES | | NULL
||
| city | varchar(45) | YES | MUL | NULL
||
| postalCode | varchar(7) | YES | MUL | NULL
||
| latitude | float(9,5) | YES | | NULL
||
| longitude | float(9,5) | YES | | NULL
||
++--+--+-+-
++
7 rows in set (0.00 sec)
Table structure T2:
***
mysql describe hn_iprange;
++--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++--+--+-+-+---+
| startIpNum | int(10) unsigned | YES | MUL | NULL| |
| endIpNum | int(10) unsigned | YES | | NULL| |
| locId | int(16) unsigned | YES | MUL | NULL| |
++--+--+-+-+---+
3 rows in set (0.00 sec)
Index from T1:
**
mysql show index from hn_iplocation;
+---+++--
+-+---+-+--++--
++-+
| Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed | Null |
Index_type | Comment |
+---+++--
+-+---+-+--++--
++-+
| hn_iplocation | 0 | PRIMARY|1 |
locId | A | 20029 | NULL | NULL | |
BTREE | |
| hn_iplocation | 1 | postalcode |1 |
postalCode | A |1820 | NULL | NULL | YES |
BTREE | |
| hn_iplocation | 1 | city |1 |
city| A |1820 | NULL | NULL | YES |
BTREE | |
+---+++--
+-+---+-+--++--
++-+
3 rows in set (0.00 sec)
Index from T2:
**
mysql show index from hn_iprange;
+++--+--+-
+---+-+--++--+
+-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type |
Comment |
+++--+--+-
+---+-+--++--+
+-+
| hn_iprange | 1 | locId|1 | locId |
A | 20587 | NULL | NULL | YES | BTREE
| |
| hn_iprange | 1 |
Re: mysqlmanager logging?
As it was already advised, check the dir/log file permissions. Also, it might help if you post your my.cnf here. Petr On Wed, 2006-04-19 at 13:17 -0500, Duzenbury, Rich wrote: MySQL 5.0.20 I've got two instances running with mysqlmanager. I'm not getting any logging of any sort. mysqlmanager --help shows: - - log /var/lib/mysql/mysqlmanager.log pid-file /tmp/manager.pid socket/tmp/manager.sock bind-address (No default value) port 2273 password-file /etc/mysqlmanager.passwd default-mysqld-path /usr/sbin/mysqld monitoring-interval 10 run-as-serviceFALSE user (No default value) wait-timeout 28800 I've also tried to set the --log option in the [manager] section of /etc/my.cnf and restarted the server, to no avail. Do I have to run-as-service to get logging? Thanks for any help you can offer. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
importing a dumpfile from with the mysql client
Hi. I recently imported a dumpfile into mysql4.1.18 I did this using the 'source' syntax from inside the mysql client. this is syntax I used: mysqlcreate database dbname; mysqluse dbname; -unset the creation of bin-logs in my.cnf mysql SET SQL_LOG_BIN = 0 then some tips to fast import dump files. mysql SET FOREIGN_KEY_CHECKS = 0; mysql SET AUTOCOMMIT=0; mysql SOURCE foo.out-20060419-23 I started this process remotely then went to the site to finish it. But when the dump finished (several hours later) I was not able to execute the following commands from my original location. mysql SET FOREIGN_KEY_CHECKS = 1; mysql COMMIT; My question is: Since the import completed the database has grown in size and been backed up etc. Yet from the original session I have not executed those 2 commands. Is it safe to execute them? Or would executing them cause corruption or other unforseen stuff? Is it unnecessary to execute them? Is it safe to kill that original mysql session? Since the commands were executed from a single session, and the database has been extensively modified in the meantime, would executing those commands from the same original session now, cause problems? Thanks. Kind regards. Luke. -- Luke Vanderfluit. Analyst/Programmer. Internode Systems Pty. Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to rename a DB
Hi, Anybody know how can i safely and easily rename a database in MySQL 5.0.19. Have tried some tips from google results (most of them are for MySQL 4.x) but no luck till now. Any help would be greatly appreciated. Rgds/Hardi
Output to a file
Hey, been trying to output a select statment to a file, all the books I have only show how to input from a file, what is the correct way I thought I could do select * from my_toy `/tmp/my_toys` But I get an error. Payne -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Output to a file
Payne wrote: Hey, been trying to output a select statment to a file, all the books I have only show how to input from a file, what is the correct way I thought I could do select * from my_toy `/tmp/my_toys` But I get an error. Payne You can use the 'tee' command, eg: tee logfile.txt Or you can start mysql with the --tee switch, eg: mysql --tee logfile.txt -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help on VB and ODBC
Hi Manuel, I had a similar problem a few months ago and I found 'somewhere' in the documentation a post by Matthew van Os on May 27 2005, saying: The CursorLocation should be of the type adUseClient instead of adUseServer. The adUseServer returns a -1 as recordcount. The adUseClient returns the actual recordcount. Please also take a look at: http://dev.mysql.com/doc/refman/5.1/en/programs-known-to-work-with-myodbc.html HTH, Cor - Original Message - From: Manuel Betanzos [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, May 04, 2006 4:01 PM Subject: Help on VB and ODBC Hi everybody, Please I need help on this. I am beginning a new application. I am doing it with VB 6.0 - MySQL 5.0 - MyODBC 3.51 on Windows Is there any parameter i need to establish in order to be able to work with RecordSets in the Client side , actually i can't because if i do, when i change any value i receive the following message: Error '-2147217900 (80040e14) en tiempo de ejecucion: [MySQL][ODBC 3.51 Driver][mysqld-5.0.15]You have an error in your SQL syntax; check the manual that corresponds to your MySQL Server version for the right syntax to use near'. ´catpartes´ SET ´condicion´ =2 WHERE ´CveCia´ =1 and ´Almacen´ ='01' and ´NoParte´ at line 1 If i change the CursorLocation to 3 - adUseClient i don't receive the above message and the information is updated correctly . But with the CursorLocation 2 - adUseServer the DataList, DataCombo and DataGrid dont work. Thanks in advance. - Do You Yahoo!? La mejor conexión a Internet y 2GB extra a tu correo por $100 al mes. http://net.yahoo.com.mx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Output to a file
Hi Payne. Payne wrote: Hey, been trying to output a select statment to a file, all the books I have only show how to input from a file, what is the correct way I thought I could do select * from my_toy `/tmp/my_toys` You can do 'select * into outfile from table name;' But I get an error. Payne -- Luke Vanderfluit. Analyst/Programmer. Internode Systems Pty. Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
