Re: How to insert CURDATE() as default
Marc, In MySql (I am using 4.1.9 and 4.1.15; so i am not sure about 5) it is not possible to use functions as default values; you could create: create table tester (f_date date default curdate()); But this doesn;t work. You have to struggle through your knoda how to present the current date. Be sure you have your field datatype set to DATE. Create your form and set the datasource to the table having the date-field. Create your textbox and assign the field to it. Put the %NOWDATE% in the 'default value' field of your textbox. The currentdate will be shown after you run the form. Hope this little info helps you :-) Danny Marc wrote: I forgot to mention - I'm running MySQL 4.0.14 on Linux. And I'm not very knowledgeable on databases. I just use MySQL with Knoda to get the job done. == I've got a MySQL table that I'd like to have the current date, CURDATE(), as the default in a column. I'm using knoda to worj with this table. How do I use knoda to get this done? I can enter CURDATE() in the default using the GridColumns button, but all that does is insert the phrase CURDATE(). I've got the column with Date for the ColummnType. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
install mysql on linux AMD64 processor
SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: root To: mysql@lists.mysql.com Subject: [50 character or so descriptive subject here (for reference)] Description: i try to install mysql-standard-5.0.17_linux-x86_64-glibc23, but it did not work ! when i run ./mysql_safe --user=mysql - message error is cannot execute binary file ! mysqld did not work too ! How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:root Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-5.0.17-standard (MySQL Community Edition - Standard (GPL)) C compiler:gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52) C++ compiler: gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52) Environment: machine, os, target, libraries (multiple lines) System: Linux localhost.localdomain 2.6.9-10.2.aur.2 #1 Thu Feb 10 04:34:27 EST 2005 i686 athlon i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Lecture des spécification à partir de /usr/lib/gcc/i386-redhat- linux/3.4.2/specs Configuré avec: ../configure --prefix=/usr --mandir=/usr/share/man -- infodir=/usr/share/info --enable-shared --enable-threads=posix -- disable-checking --with-system-zlib --enable-__cxa_atexit --disable- libunwind-exceptions --enable-java-awt=gtk --host=i386-redhat-linux Modèle de thread: posix version gcc 3.4.2 20041017 (Red Hat 3.4.2-6) Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 sep 8 10:40 /lib/libc.so.6 - libc-2.3.3.so -rwxr-xr-x 1 root root 1504728 oct 28 2004 /lib/libc-2.3.3.so -rw-r--r-- 1 root root 2404716 oct 28 2004 /usr/lib/libc.a -rw-r--r-- 1 root root 204 oct 28 2004 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '-- localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=MySQL Community Edition - Standard (GPL)' '--with-extra- charsets=complex' '--with-server-suffix=-standard' '--enable-thread- safe-client' '--enable-local-infile' '--enable-assembler' '--disable- shared' '--with-zlib-dir=bundled' '--with-big-tables' '--with-readline' '--with-archive-storage-engine' '--with-innodb' 'CC=gcc' 'CXX=gcc' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
install mysql-5.0.17 on linux AMD 64 processor
SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: root To: mysql@lists.mysql.com Subject: install mysql-SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: root To: mysql@lists.mysql.com Subject: install mysql-5.0.17 on linux AMD 64 processor Description: i try to install mysql-standard-5.0.17_linux-x86_64-glibc23, but it did not work ! when i run ./mysql_safe --user=mysql - message error is cannot execute binary file ! mysqld did not work too ! How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:root Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-5.0.17-standard (MySQL Community Edition - Standard (GPL)) C compiler:gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52) C++ compiler: gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52) Environment: machine, os, target, libraries (multiple lines) System: Linux localhost.localdomain 2.6.9-10.2.aur.2 #1 Thu Feb 10 04:34:27 EST 2005 i686 athlon i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Lecture des spécification à partir de /usr/lib/gcc/i386-redhat- linux/3.4.2/specs Configuré avec: ../configure --prefix=/usr --mandir=/usr/share/man -- infodir=/usr/share/info --enable-shared --enable-threads=posix -- disable-checking --with-system-zlib --enable-__cxa_atexit --disable- libunwind-exceptions --enable-java-awt=gtk --host=i386-redhat-linux Modèle de thread: posix version gcc 3.4.2 20041017 (Red Hat 3.4.2-6) Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 sep 8 10:40 /lib/libc.so.6 - libc-2.3.3.so -rwxr-xr-x 1 root root 1504728 oct 28 2004 /lib/libc-2.3.3.so -rw-r--r-- 1 root root 2404716 oct 28 2004 /usr/lib/libc.a -rw-r--r-- 1 root root 204 oct 28 2004 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '-- localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=MySQL Community Edition - Standard (GPL)' '--with-extra- charsets=complex' '--with-server-suffix=-standard' '--enable-thread- safe-client' '--enable-local-infile' '--enable-assembler' '--disable- shared' '--with-zlib-dir=bundled' '--with-big-tables' '--with-readline' '--with-archive-storage-engine' '--with-innodb' 'CC=gcc' 'CXX=gcc' Description: i try to install mysql-standard-5.0.17_linux-x86_64-glibc23, but it did not work ! when i run ./mysql_safe --user=mysql - message error is cannot execute binary file ! mysqld did not work too ! How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:root Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-5.0.17-standard (MySQL Community Edition - Standard (GPL)) C compiler:gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52) C++ compiler: gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52) Environment: machine, os, target, libraries (multiple lines) System: Linux localhost.localdomain 2.6.9-10.2.aur.2 #1 Thu Feb 10 04:34:27 EST 2005 i686 athlon i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Lecture des spécification à partir de /usr/lib/gcc/i386-redhat- linux/3.4.2/specs Configuré avec: ../configure --prefix=/usr --mandir=/usr/share/man -- infodir=/usr/share/info --enable-shared --enable-threads=posix -- disable-checking --with-system-zlib --enable-__cxa_atexit --disable- libunwind-exceptions --enable-java-awt=gtk --host=i386-redhat-linux Modèle de thread: posix version gcc 3.4.2 20041017 (Red Hat 3.4.2-6) Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 sep 8 10:40 /lib/libc.so.6 - libc-2.3.3.so -rwxr-xr-x 1 root
Re: install mysql-5.0.17 on linux AMD 64 processor
On Friday 30 December 2005 10:35, jean-marc Jegou wrote: i try to install mysql-standard-5.0.17_linux-x86_64-glibc23, but it did not work ! machine, os, target, libraries (multiple lines) System: Linux localhost.localdomain 2.6.9-10.2.aur.2 #1 Thu Feb 10 04:34:27 EST 2005 i686 athlon i386 GNU/Linux Architecture: i686 x86_64 binaries will not function on an i686 installation. You've installed a 32 bit OS on a 64 bit CPU, OR you're using an Athlon XP not an Athlon 64/Opteron. Scanned by mailCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Capturing snapshots of content
Hi everyone One of the projects I am working on requires me to take snapshots of the Content Management System from certain points in time (usually before a change is made). These snapshots contain everything (not just the page contents), from navigation ordering to user permissions to actual content. This is so the client can go back in time and see what the CMS looked like then. I have 10 tables that I need to capture data from. The way I'm thinking of doing this is to do a dump of those 10 tables. When a snapshot is requested, it writes the dump file to a snapshot table, whose columns is every single column from those 10 tables. This is so I can do a select from that one table to get a snapshot of the CMS. But I will need to parse the dump output to append every single CREATE and INSERT statement to be the new table name. Does mysql cater for this or will I have to write a parse script to do this for me? Another option would be to have a snapshot_id column in every table, but is slightly more messy from my point of view. Please let me know your thoughts, thanks. -- Jonathan Chong http://www.arsenal-now.com/ http://www.arsenal-mania.com/ http://www.ashburrn.com/ http://www.jonathan-chong.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading to 5.0.15
Gary Richardson wrote: We moved directly from 4.0.20 to 5.0.16. Worked like a charm. I had a script that went through and optimized all tables with keys on text/varchar and char fields. We're also slowly ALTERing innodb tables to get them into the new compact format. i tryed to update from 4.1.15 to 5.0.17. it was a disaster... many varchar columns showed corrupted data after the update. can i do anything in mysql 4.1.15 to make the update work or is my only solution a complete dump and insert? best regards chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Converting table to InnoDB
I have a table on my forum that's frequently accessing with reads and writes. On a busy day, the table is locked when people are posting messages and the page hangs when you try to read a topic. Is it worth me converting the table to InnoDB, as I've heard that InnoDB doesn't lock tables during I/O access. I'm running an AMD64 3000+ with 1GB of memory. -- Jonathan Chong http://www.arsenal-now.com/ http://www.arsenal-mania.com/ http://www.ashburrn.com/ http://www.jonathan-chong.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to insert CURDATE() as default
Thanks, Danny. This little info was all I needed. It works perfectly now. Have a Happy New Year! --Marc On Fri, 2005-12-30 at 10:28 +0100, Danny Stolle wrote: Marc, In MySql (I am using 4.1.9 and 4.1.15; so i am not sure about 5) it is not possible to use functions as default values; you could create: create table tester (f_date date default curdate()); But this doesn;t work. You have to struggle through your knoda how to present the current date. Be sure you have your field datatype set to DATE. Create your form and set the datasource to the table having the date-field. Create your textbox and assign the field to it. Put the %NOWDATE% in the 'default value' field of your textbox. The currentdate will be shown after you run the form. Hope this little info helps you :-) Danny -- Marc [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compiler for Embedded SQl Macros
Hi, Is there any compiler to Compile Embedded SQL Macros into source codes for MySql? In otherwords, what is the equivalent of PRO*C (in Oracle) in MySql? Thanks, Ezhilarasan
Re: install mysql-5.0.17 on linux AMD 64 processor
Re: install mysql-5.0.17 on linux AMD 64 processor http://lists.mysql.com/mysql/193352 dir Duncan Hill , thank you to answer to my question ! my processeur is AMD 64 2800+ ( 64 Bits processeurs!) and linux is seeing an Athlon 32 bits processor (see previous message). My OS IS Linux Aurox ver 10.2 (OS 32 Bits ),therefore the trouble is probably resolved ! What do you think ? best regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: install mysql-5.0.17 on linux AMD 64 processor
On Friday 30 December 2005 14:05, jean-marc Jegou wrote: Re: install mysql-5.0.17 on linux AMD 64 processor http://lists.mysql.com/mysql/193352 dir Duncan Hill , thank you to answer to my question ! my processeur is AMD 64 2800+ ( 64 Bits processeurs!) and linux is seeing an Athlon 32 bits processor (see previous message). My OS IS Linux Aurox ver 10.2 (OS 32 Bits ),therefore the trouble is probably resolved ! Download the 64 bit version of Aurox if they provide one. Alternately, download the 32-bit binary of MySQL. The first method involves reinstalling the OS. The second is just a binary program to be installed. Scanned by mailCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can I go a boolean search and get the row count in 1 SQL query?
Hi,
I have a web app where I am doing a boolean search. I only want to return
10 results per page, but I'd also like to know how many total rows match the
search query. I'm currently performing this with 2 query statements:
1. (To get the actual rows via the search)
SELECT $product_column[title],
MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
AS score FROM $product_table
WHERE MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT $lower_limit,10
2. (To get the total number of results)
SELECT COUNT(*) as num_results
MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
AS score FROM $product_table
WHERE MATCH (title) AGAINST ('$q' IN BOOLEAN MODE)
ORDER BY score DESC
The queries might be a bit off as I don't have the exact code in front of me
right now.
Is there a way I can combine this into 1 query?
Thanks,
Grant
-
Yahoo! Photos
Ring in the New Year with Photo Calendars. Add photos, events, holidays,
whatever.
some myisam tables turned read only (MySQL Max 4.1.12)
Very odd problem. I do have a larger database, with many myisam tables.
A couple days ago, some of them turned 'read only'.
Symptom:
delete from day_48392 where id=28398776259;
ERROR 1036 (HY000): Table 'day_48392' is read only
I checked:
- table file permissions are right ('660 mysql:daemon').
They are the same for all tables
- directory permissions are 700 mysql:daemon
- tables are not packed.
To track this down, I tried:
- copy data from old table to a new table:
create table x like day_48392;
insert into x select * day_48392;
table 'x' is now writable and works fine
drop table day_48392;
alter table x rename day_48392;
and 'day_49392' is once again read only :-(
- compress/uncompress the table: no luck
- repair the table from within mysql fails (read only)
- repair table from shell with myisamchk works, but
doesn't fix the problem
- I flushed tables (several times)
- I restarted mysql
- I rebooted the system
So what else ???
Hint:
before this started, I backed up a number of older tables.
In order to back them up, I copied them to a different
partition, marked them '444'. Next, I dropped the tables, and added a
symlink to the backups.
Looks like tables are affected if they existed at the time of the
backup. Tables created after that are fine. The tables that have the
problem where not touched during the backup :-(
The tables are part of a merge table. but dropping the merge table
doesn't help.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
backslash and Update
I have 290 records in the database with backslashes. I want to remove the backslashes. Why the query below doesn't remove backslashes? update name set first_name = REPLACE(first_name,'','') where first_name like '%%';
character_set_client in v4.1
Hello list I've just install v4.1. Every thing works fine exept SQLyong shows all non-latin characters as question marks and the old Mysql-Front can't connect with tables after mysql_fix_privilege_tables. How can I change character_set_client=greek than to latin? I thing this will solve my problem Is that right? Thank you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
R: how use sql_slave_skip_counter to restore slave replication
So it should be possible to automate a skip of errors setting by code , at run-time, sql_slave_skip_counter = 1, each time I check the slave status and I found an error, and then I restart the slave Thanks, I should try this way. One think it is astonished: I can agree that there should bu some conflict, but why i have remove the same row, with tha same key value several times about 5-6 times ). Do you have some knowledge of problem like that ? -Messaggio originale- Da: Gleb Paharenko [mailto:[EMAIL PROTECTED] Inviato: giovedì 29 dicembre 2005 17.00 A: mysql@lists.mysql.com Oggetto: Re: how use sql_slave_skip_counter to restore slave replication Hello. but the problem is the same and the command 'show slave status' report a skip_counter filed equals 0. Where I am wrong , some can help me ? This variable is the number of events from the master that a slave server should skip. sql_slave_skip_counter decrements each time the slave skips one event. Your slave has already skipped 5 events, so the value of events that it should skip is 0. AESYS S.p.A. [Enzo Arlati] wrote: I'm trying to use teh parameter sql_slave_skip_counter at run-time to restore slave replication. When a slave replication broke due some errors in code, my be a duplicate key, the only working way to restore the replica where to delete the existing record which conflicts whith the ones inserted by the replication process. So if I have a duplicate key 30020 ,I have to remove the record with the id 30020 and the replication can reinsert it's copy of record with id = 30020. This should be difficult to automate so I try another way using the global variable sql_slave_skip_counter.I try to skip 5 records using a statemente like this: set global sql_slave_skip_counter = 5; and then restart the slavre start slave; but the problem is the same and the command 'show slave status' report a skip_counter filed equals 0. Where I am wrong , some can help me ? Regards, Enzo -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.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 5.0.18 has been released
Hi, MySQL 5.0.18, 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 version for the current production release family. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. We welcome and appreciate your feedback! News from the ChangeLog: Functionality added or changed: * It is now possible to build the server such that MyISAM tables can support up to 128 keys rather than the standard 64. This can be done by configuring the build using the option --with-max-indexes=N, where N=128 is the maximum number of indexes to permit per table. (Bug #10932 (http://bugs.mysql.com/10932)) * The server treats stored routine parameters and local variables (and stored function return values) according to standard SQL. Previously, parameters, variables, and return values were treated as items in expressions and were subject to automatic (silent) conversion and truncation. Now the data type is observed. Data type conversion and overflow problems that occur in assignments result in warnings, or errors in strict mode. The CHARACTER SET clause for character data type declarations is used. Parameters, variables, and return values must be scalars; it is no longer possible to assign a row value. Also, stored functions execute using the sql_mode value in force at function creation time rather than ignoring it. For more information, see Section 17.2.1, CREATE PROCEDURE and CREATE FUNCTION. (Bug #8702 (http://bugs.mysql.com/8702), Bug #8768 (http://bugs.mysql.com/8768), Bug #8769 (http://bugs.mysql.com/8769), Bug #9078 (http://bugs.mysql.com/9078), Bug #9572 (http://bugs.mysql.com/9572), Bug #12903 (http://bugs.mysql.com/12903), Bug #13705 (http://bugs.mysql.com/13705), Bug #13808 (http://bugs.mysql.com/13808), Bug #13909 (http://bugs.mysql.com/13909), Bug #14161 (http://bugs.mysql.com/14161), Bug #15148 (http://bugs.mysql.com/15148)) Bugs fixed: * SHOW [FULL] COLUMNS and SHOW INDEX FROM did not function with temporary tables. (Bug #14271 (http://bugs.mysql.com/14271), Bug #14387 (http://bugs.mysql.com/14387), Bug #15224 (http://bugs.mysql.com/15224)) * The INFORMATION_SCHEMA.COLUMNS table did not report the size of BINARY or VARBINARY columns. (Bug #14271 (http://bugs.mysql.com/14271)) * The server would not compile under Cygwin. (Bug #13640 (http://bugs.mysql.com/13640)) * DESCRIBE did not function with temporary tables. (Bug #12770 (http://bugs.mysql.com/12770)) * Reversing the order of operands in a WHERE clause testing a simple equality (such as WHERE t1.col1 = t2.col2) would produce different output from EXPLAIN. (Bug #15106 (http://bugs.mysql.com/15106)) * Column aliases were displayed incorrectly in a SELECT from a view following an update to a base table of the view. (Bug #14861 (http://bugs.mysql.com/14861)) * Prior versions did not allow the use of set functions which were aggregated in an outer [sub]query, this did not comply with the standard. Now this limitation is lifted. Moreover, the use of nested set functions is now supported. For more information, see a detailed comment in the bug database entry. (Bug #12762 (http://bugs.mysql.com/12762)) * When a connection using yaSSL was aborted, the server would continue to try to read the closed socket, and the thread continued to appear in the output of SHOW PROCESSLIST. Note that this issue did not affect secure connection attempts using OpenSSL. (Bug #15772 (http://bugs.mysql.com/15772)) * InnoDB: Having two tables in a parent-child relationship enforced by a foreign key where one table used ROW_FORMAT=COMPACT and the other used ROW_FORMAT=REDUNDANT could result in a MySQL server crash. Note that this problem did not exist prior to MySQL 5.0.3, when the compact row format for InnoDB was introduced. (Bug #15550 (http://bugs.mysql.com/15550)) * BDB: A DELETE, INSERT, or UPDATE of a BDB table could cause the server to crash where the query contained a subquery using an index read. (Bug #15536 (http://bugs.mysql.com/15536)) * A left join on a column that having a NULL value could cause the server to crash. (Bug #15268 (http://bugs.mysql.com/15268)) * A replication slave server could sometimes crash on a BEFORE UPDATE trigger if the UPDATE query was not executed in
Re: Fw: Re: create function with space
Hello. Sorry. It seems that I've understood your question now. In my opinion, IGNORE_SPACE not related to CREATE FUNCTION statement, because at: http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html for some cases we have to use a space between the name and the following parenthesis when defining the routine, or a syntax error occurs And we have a space between 'sp_name' token and parenthesis. So it seems that spaces are allowed for CREATE FUNCTION. Yes,I already read this section. But, maybe there are haven't relation to my question. Are the effect of setting IGNORE_SPACE in sql mode allowing add space between function name and (? If my attitude is right,then if i haven't setting IGNORE_SPACE in sql mode,I shouldn't add space between function name and (.But fact not like this. Please note example in my reference. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem With FulltText Index and VarChar
Hello.
I guess my question is, how do I make sure the full text search is
being done against the CommentsIDX and not just against the individual
fields...
If I've got your question right, you shouldn't worry about
it. As far as I know, the search is done against the union of all
columns in the FULLTEXT index, not against individual columns. Even
more, when performing a language natural search (as you do), the row
will be in the result set if at least one field has one word from the
list, however it will have a small relevance. Examples below should
prove my opinion:
mysql select * from ab;
++-+
| a | b |
++-+
| sf sfs | sf s sdfss |
| sss sss| 3435|
| search | words |
| sdf sdf 34345 | 35 35435 34535 iwur |
| cool search| NULL|
++-+
mysql select a, b, match(a,b) against('search words') from ab where
match(a,b) against('search words');
+-+---++
| a | b | match(a,b) against('search words') |
+-+---++
| search | words |1.7514755725861 |
| cool search | NULL | 0.39634910225868 |
+-+---++
This shows that the search is done against concatenation of the fields:
mysql select a, b, match(a,b) against('search words') from ab where
match(a,b) against('+search +words' in boolean mode);
++---++
| a | b | match(a,b) against('search words') |
++---++
| search | words |1.7514755725861 |
++---++
Michael Stearne wrote:
On 12/28/05, Gleb Paharenko [EMAIL PROTECTED] wrote:
Hello.
#1054 - Unknown column 'CommentsIDX' in 'where clause'
My table structure contains:FULLTEXT KEY `CommentsIDX` (`Comments`=
)
You should use column names not index names in your queries. Please,
provide CREATE statement for your tables and problematic queries. With
this information it'll be easier to help you.
Thanks this is the a snippet of the table structure:
CREATE TABLE `properties` (
`id` int(11) unsigned NOT NULL auto_increment,
`UserID` int(11) unsigned NOT NULL default '0',
`Type` enum('Commercial','Residential') NOT NULL default 'Residential',
`Subtype` varchar(64) NOT NULL default '0',
`Zip` varchar(10) NOT NULL default '',
`Heading` varchar(84) NOT NULL default '',
`Address1` varchar(32) NOT NULL default '',
`Address2` varchar(32) default NULL,
.
KEY `TypeSubType` (`Type`,`Subtype`),
KEY `CityHood` (`City`,`Neighborhood`),
FULLTEXT KEY `CommentsIDX`
(`Comments`,`Subtype`,`Heading`,`Zip`,`Address1`,`Neighborhood`,`City`,`Cou=
ntry`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1 AUTO_INCREMENT=3D101131 ;
I guess my question is, how do I make sure the full text search is
being done against the CommentsIDX and not just against the individual
fields...
SELECT * FROM properties WHERE MATCH
(Comments,Subtype,Heading,Zip,Address1,Neighborhood,City,Country)
AGAINST (
search words WITH QUERY EXPANSION)
Thanks,
Michael
Michael Stearne wrote:
I am trying to do a fulltext search with a multi-field index using MySQ=
L 4.=3D
1.15.
When I create a full text index of my Comments field which is of type
TEXT. I can do a fulltext search fine.
But when I add another field (like a varchar or even Text) to that
index or change the name of the index I get and error like:
SELECT * FROM properties WHERE MATCH (CommentsIDX)
AGAINST (
item1 OR item2 AND (item3 AND item4 NOT (item5 OR item6))
WITH QUERY EXPANSION
)
MySQL said: Documentation
#1054 - Unknown column 'CommentsIDX' in 'where clause'
My table structure contains:FULLTEXT KEY `CommentsIDX` (`Comments`)
Any ideas?
Thanks
--
For technical support contracts, goto https://order.mysql.com/?ref=3Densi=
ta
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
.com
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/
Re: R: how use sql_slave_skip_counter to restore slave replication
Hello. One think it is astonished: I can agree that there should bu some conflict, but why i have remove the same row, with the same key value several times about 5-6 times ).Do you have some knowledge of problem like that ? It is difficult to say something, because the whole picture of your application isn't clear for me. Provide short description of application's logic, include CREATE statements for your tables and main queries. Make it in a compact form as much as possible. I have a question: queries which causes 'duplicate key' errors should fail on the master, and therefore they can't be replicated. Are you directly update your slave? Or probably you have an old version which is buggy? AESYS S.p.A. [Enzo Arlati] wrote: So it should be possible to automate a skip of errors setting by code , at run-time, sql_slave_skip_counter = 1, each time I check the slave status and I found an error, and then I restart the slave Thanks, I should try this way. One think it is astonished: I can agree that there should bu some conflict, but why i have remove the same row, with tha same key value several times about 5-6 times ). Do you have some knowledge of problem like that ? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange behavior with integer unsigned type...
Hello. That seems like a bug: http://bugs.mysql.com/bug.php?id=14543 Marko Domanovic wrote: mysql 5.0.15-standard UPDATE table SET fieldname = fieldname-1 when the fieldname is 0 gives me 4294967295 fieldname is integer(10) unsigned... maybe it would be more logical the expression to evaluate as 0, insted 2^32 .. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SOLUTION] some myisam tables turned read only (MySQL Max 4.1.12)
Well, I found the reason for these table becoming 'read only': These tables where part of a merge table, which included some read only tables (read only set in file system). Now even after the merge table was dropped, all member tables stayed 'read only' regardless of their individual file system permissions. very odd.. a bug? to recover, you will have to: - drop the merge table. - create new tables - insert the data from the old tables into the new tables - drop the old tables - rename the new tables to match the names of the old tables. - change file system permissions on ALL the tables of the merge tables - recreate the merge tables... signature.asc Description: OpenPGP digital signature
Re: backslash and Update
Hi, What you perhaps could use is the REGEXP usage in your where clause. Try this: update name set first_name=replace(first_name, '\\', '') where first_name regexp ''; The fun thing is that when you put '\\' instead of the '' after the regexp function it doesn't work. But this sure does. Before you use the update, create a selection first with the regexp function; if that works, your update will do fine. Hope this little info helps you :-) Danny Jerry Swanson wrote: I have 290 records in the database with backslashes. I want to remove the backslashes. Why the query below doesn't remove backslashes? update name set first_name = REPLACE(first_name,'','') where first_name like '%%'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Force max query time, or max records returned
Is there a way to tell mysqld to stop running a query if it hasn't completed in over 60 seconds? Or how about stop running if the result is greater 100,000 rows or something? I have a bone head user who keeps querying two tables and NOT joining them, and then dragging the DB server down while it crunches this useless query. How can I prevent this? Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Force max query time, or max records returned
On Friday 30 December 2005 3:31 pm, Scott Baker wrote: How can I prevent this? Scott You know when I deal with users like this, I like to think about what Dennis Leary said about spanking children; I don't need to spank them, I find that waving the gun around works just as well. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Force max query time, or max records returned
At 04:31 PM 12/30/2005, Scott Baker wrote: Is there a way to tell mysqld to stop running a query if it hasn't completed in over 60 seconds? Or how about stop running if the result is greater 100,000 rows or something? I have a bone head user who keeps querying two tables and NOT joining them, and then dragging the DB server down while it crunches this useless query. How can I prevent this? Scott Scott, Have you tried electo-shock? Tasers are really good for this. Sneak up on him and zap him in the back of the neck. By the time he wakes up, it's quitting time. (Just kidding, don't try this at home kiddiesg) Unfortunately MySQL doesn't have anything related to restricting CPU usage. http://dev.mysql.com/doc/refman/5.0/en/user-resources.html You could write a small program that would execute a show process list every 30 seconds and if that user has a Select query running for more than 30 seconds, just have the program execute a Kill command. I know it's not as much fun as using a Taser, but you won't have to get up out of your chair to zap him.g Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backslash and Update
Danny Stolle wrote:
Hi,
What you perhaps could use is the REGEXP usage in your where clause.
Try this:
update name set first_name=replace(first_name, '\\', '') where
first_name regexp '';
The fun thing is that when you put '\\' instead of the '' after the
regexp function it doesn't work. But this sure does.
Before you use the update, create a selection first with the regexp
function; if that works, your update will do fine.
Hope this little info helps you :-)
Danny
Jerry Swanson wrote:
I have 290 records in the database with backslashes. I want to remove the
backslashes. Why the query below doesn't remove backslashes?
update name set first_name = REPLACE(first_name,'','') where
first_name
like '%%';
You should be able to do this with either LIKE or REGEXP. The problem, I think,
was too many backslashes in the REPLACE part of the UPDATE. That is,
REPLACE(first_name,'','')
tells mysql to replace double backslashes (\\) with nothing, but won't have any
effect on single backslashes (\). I believe this is because the LIKE or REGEXP
pattern is parsed twice, while the REPLACE pattern is parsed once, as the
following example shows:
CREATE TABLE bst (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
txt CHAR(32));
INSERT INTO bst (txt)
VALUES ('Here\'s some text.'), ('Here is some more.'),
('This has a \\ in it.'), ('This one has 2 in the middle.');
SELECT * FROM bst;
++--+
| id | txt |
++--+
| 1 | Here's some text.|
| 2 | Here is some more. |
| 3 | This has a \ in it. |
| 4 | This one has 2 \\ in the middle. |
++--+
4 rows in set (0.00 sec)
SELECT * FROM bst WHERE txt LIKE '%%';
++--+
| id | txt |
++--+
| 3 | This has a \ in it. |
| 4 | This one has 2 \\ in the middle. |
++--+
2 rows in set (0.00 sec)
mysql SELECT * FROM bst WHERE txt REGEXP '';
++--+
| id | txt |
++--+
| 3 | This has a \ in it. |
| 4 | This one has 2 \\ in the middle. |
++--+
2 rows in set (0.01 sec)
UPDATE bst SET txt = REPLACE(txt, '', '');
Query OK, 1 row affected (0.00 sec)
Rows matched: 4 Changed: 1 Warnings: 0
SELECT * FROM bst;
+++
| id | txt|
+++
| 1 | Here's some text. |
| 2 | Here is some more. |
| 3 | This has a \ in it.|
| 4 | This one has 2 in the middle. |
+++
4 rows in set (0.00 sec)
UPDATE bst SET txt = REPLACE(txt, '\\', '');
Query OK, 1 row affected (0.00 sec)
Rows matched: 4 Changed: 1 Warnings: 0
SELECT * FROM bst;
+++
| id | txt|
+++
| 1 | Here's some text. |
| 2 | Here is some more. |
| 3 | This has a in it. |
| 4 | This one has 2 in the middle. |
+++
4 rows in set (0.00 sec)
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Force max query time, or max records returned
Scott Baker wrote: Is there a way to tell mysqld to stop running a query if it hasn't completed in over 60 seconds? Or how about stop running if the result is greater 100,000 rows or something? I have a bone head user who keeps querying two tables and NOT joining them, and then dragging the DB server down while it crunches this useless query. How can I prevent this? Scott First some questions: How is the user connecting: Using a mysql client, a web interface or a custom program? Who controls the machine they are accessing it from? Do they have a dedicated unique database user? Does the user issue thousands scripted of querys per day or just a dozen manually? Do their queries contain inserts or updates? Who controls the database structure? Does anyone fully understand the database structure? Do you have a servers in a replication chain or could you set up a replication chain? Can you influence their behavior without a technological fix? Does their incompetance merit other forms of action? Just how much cash, hassle and time is a solution worth to you? The answers of course influence what is possible. I'd say that if the user is an experienced programmer or DBA you've simply hired the wrong guy. Assuming not and that you need a technical solution it's likely to be one of these (in order of increasing effort): Give the user a quick person-to-person refresher course in the use of entity models, explain and indexes If they're connecting via the mysql client form a box you control look at the --I-am-a-dummy, --select_limit= --join_limit= options and consider allowing the person only to be able to invoke the mysql client in that form. In *nix this is easy to achieve. Perhaps you should force them to switch to working this way until they can be trusted if they currently access using a different client. If their queries are infrequent, read only, can stand a small delay and you've spare hardware consider setting up a replicant reporting server. Deny the user access to the master server and let them slow the reporting server to their hearts content. If it's a web interface you control and your programming skills allow think about modying the code to apply a similar set of restrictions to the users queries. If the feature doesn't already exist in the web interface software and the appication changes needed are beyond your ken a jobbing programmer could be employed to add it inexpensively. As an expansion of the web interface idea if you understand the data realtionships it's possible for an interface to cross-check and enforce the correct links helping novice users without restricting experienced ones. I've implemented this myself on an MIS project and it works well. If the user is accessing remotely with a client they control, they're changing data state, you cannot influence their behavior and your pockets are deep. The last resort is to force the users queries through a spoof 'mysql server' which checks each query with explain before applying it. They have no access to the actual server only through the proxy. Whilst I've never done this but as old the MySQL manual document the protocol it's not impossible to make a validating proxy, just very, very expensive. Hope part of this this helps Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
installed modules
Like to know how to query MySQL for loaded or installed modules. Is this done through using the code: ? phpinfo(); ? I'm looking on my system for loaded modules such as DBD-MySQL, zlib-devel and perl-DBI. Thanks !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META http-equiv=Content-Type content=text/html; charset=us-ascii META content=MSHTML 6.00.2900.2802 name=GENERATOR/HEAD BODY style=MARGIN-TOP: 2px; FONT: 10pt Arial; MARGIN-LEFT: 2px DIVLike to know how to query MySQL for loaded or installed modules.nbsp; Is this done through using the code:/DIV DIVlt;? phpinfo(); ?gt;/DIV DIVnbsp;/DIV DIVI'm looking on my system for loaded modules such as DBD-MySQL, zlib-devel and perl-DBI./DIV DIVnbsp;/DIV DIVThanks/DIV/BODY/HTML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table Lock issue on insert
I am having trouble with table lock. The query is as follows: INSERT INTO USERS_PER_HOUR SELECT count( DISTINCT ( CUID ) ),`TV_LOG_DATE`,`TV_LOG_TIME`, INTERFACE_ID FROM `TV_LOG_ALL` group by 2,3; I keep getting this error: #1206 - The total number of locks exceeds the lock table size How do I fix this?
creating triggers?
I would like to create a trigger that either updates or inserts a row in my USERS_PER_HOUR table when an entry is made in the LOG table. The log table has LOG_DATE, LOG_TIME and a CUID columns that I would like to either create a new row if the LOG_DATE and LOG_TIME do not exist or update the number of CUIDS for that date and time if it does. I have never done anything with triggers and wonder if this is possible? John Brittingham Marketing and Customer Systems Desk: 425-288-8071 Cell: 425-770-0458
Re: Strange behavior with integer unsigned type...
Maybe it is because I am a programmer, but (unsigned) 0 - 1 = 4294967295. What's the big deal? Gleb Paharenko wrote: Hello. That seems like a bug: http://bugs.mysql.com/bug.php?id=14543 Marko Domanovic wrote: mysql 5.0.15-standard UPDATE table SET fieldname = fieldname-1 when the fieldname is 0 gives me 4294967295 fieldname is integer(10) unsigned... maybe it would be more logical the expression to evaluate as 0, insted 2^32 .. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem With FulltText Index and VarChar
On 12/30/05, Gleb Paharenko [EMAIL PROTECTED] wrote:
Hello.
I guess my question is, how do I make sure the full text search is
being done against the CommentsIDX and not just against the individual
fields...
This shows that the search is done against concatenation of the fields:
mysql select a, b, match(a,b) against('search words') from ab where
match(a,b) against('+search +words' in boolean mode);
++---++
| a | b | match(a,b) against('search words') |
++---++
| search | words |1.7514755725861 |
++---++
Thanks so much for your help. Everything makes sense. One this I did
come across though is that if you are using a multi-column index you
have to include all the columns in the index in your select statement.
e.g. MATCH (Comment, heading, ...)
Thanks,
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
rentering a sql script
I have a sql script that needs to be re entered using the following command mysql create.sql but the database already exists. IS there a way to overwrite or update the database using the script, or do I have to delete the existing database? !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META http-equiv=Content-Type content=text/html; charset=us-ascii META content=MSHTML 6.00.2900.2802 name=GENERATOR/HEAD BODY style=MARGIN-TOP: 2px; FONT: 10pt Arial; MARGIN-LEFT: 2px DIVI have a sql script that needs to be re entered using the following command mysql lt; create.sql but the database already exists.nbsp; IS there a way to overwrite or update the database using the script, or do I have to delete the existing database?/DIV DIVnbsp;/DIV DIVnbsp;/DIV/BODY/HTML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
