Re: right join troubles
On Sat, 19 Sep 2009, b wrote: I'm trying to select all members who have not registered for an event. I have tables 'members', 'events', and 'events_members', the latter a join table with event_id and member_id columns. The closest I've gotten is with this query: SELECT m.id, m.first_name, m.last_name FROM members AS m RIGHT JOIN events_members AS em ON (em.event_id = 10 AND m.id != em.member_id) ORDER BY m.last_name ASC; This returns an empty set IF there are no records at all in events_members with event_id = 10. But, in that case, I want to receive ALL members. However, if I add a single record with event_id = 10, I then get the expected list of all OTHER members. How can I modify this query so that, when there are 0 registered members for a particular event, I get back all members? Obviously, I could always first check for the existence of the event_id in the join table and, if not found, run the select on the members table. But I doubt that that's the best option. I think that you need two steps: CREATE TABLE events_members_tmp SELECT * FROM events_members WHERE event_id = 10; SELECT m.id, m.first_name, m.last_name FROM members AS m LEFT JOIN events_members_tmp AS em ON m.id = em.member_id WHERE em.member_id IS NULL ORDER BY m.last_name ASC; Having written this it appears that it could work in just one step as well: SELECT m.id, m.first_name, m.last_name FROM members AS m LEFT JOIN events_members AS em ON em.event_id = 10 AND m.id = em.member_id WHERE em.member_id IS NULL ORDER BY m.last_name ASC; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: myisamchk buffer_size warnings
On Tue, 16 Jun 2009, Thomas Spahni wrote:
Hi
I have MySQL 5.0.64 compiled from source. When I run myisamchk on any
table I get the following warnings:
Warning: option 'key_buffer_size': unsigned value 18446744073709551615
adjusted to 4294963200
Warning: option 'read_buffer_size': unsigned value 18446744073709551615
adjusted to 4294967295
Warning: option 'write_buffer_size': unsigned value 18446744073709551615
adjusted to 4294967295
Warning: option 'sort_buffer_size': unsigned value 18446744073709551615
adjusted to 4294967295
/etc/my.cnf contains the following:
[myisamchk]
key_buffer_size=20971520
sort_buffer_size=20971520
read_buffer_size=2097152
write_buffer_size=2097152
What's wrong here?
Thomas Spahni
Hi all,
Replying to myself as the problem is fixed in the meantime (at least for
me). It turned out to be bug #33785 described here:
<http://bugs.mysql.com/bug.php?id=33785>
The solution is to fix the source code of myisamchk.c and recompile. The
diff is:
--- myisamchk.c.original2009-06-18 13:01:55.0 +0200
+++ myisamchk.c 2009-06-18 13:17:34.0 +0200
@@ -295,7 +295,7 @@
{ "key_buffer_size", OPT_KEY_BUFFER_SIZE, "",
(gptr*) &check_param.use_buffers, (gptr*) &check_param.use_buffers, 0,
GET_ULONG, REQUIRED_ARG, (long) USE_BUFFER_INIT, (long) MALLOC_OVERHEAD,
-(long) ~0L, (long) MALLOC_OVERHEAD, (long) IO_SIZE, 0},
+(ulong) ~0, (long) MALLOC_OVERHEAD, (long) IO_SIZE, 0},
{ "key_cache_block_size", OPT_KEY_CACHE_BLOCK_SIZE, "",
(gptr*) &opt_key_cache_block_size,
(gptr*) &opt_key_cache_block_size, 0,
@@ -309,17 +309,17 @@
(gptr*) &check_param.read_buffer_length,
(gptr*) &check_param.read_buffer_length, 0, GET_ULONG, REQUIRED_ARG,
(long) READ_BUFFER_INIT, (long) MALLOC_OVERHEAD,
-(long) ~0L, (long) MALLOC_OVERHEAD, (long) 1L, 0},
+(ulong) ~0, (long) MALLOC_OVERHEAD, (long) 1L, 0},
{ "write_buffer_size", OPT_WRITE_BUFFER_SIZE, "",
(gptr*) &check_param.write_buffer_length,
(gptr*) &check_param.write_buffer_length, 0, GET_ULONG, REQUIRED_ARG,
(long) READ_BUFFER_INIT, (long) MALLOC_OVERHEAD,
-(long) ~0L, (long) MALLOC_OVERHEAD, (long) 1L, 0},
+(ulong) ~0, (long) MALLOC_OVERHEAD, (long) 1L, 0},
{ "sort_buffer_size", OPT_SORT_BUFFER_SIZE, "",
(gptr*) &check_param.sort_buffer_length,
(gptr*) &check_param.sort_buffer_length, 0, GET_ULONG, REQUIRED_ARG,
(long) SORT_BUFFER_INIT, (long) (MIN_SORT_BUFFER + MALLOC_OVERHEAD),
-(long) ~0L, (long) MALLOC_OVERHEAD, (long) 1L, 0},
+(ulong) ~0, (long) MALLOC_OVERHEAD, (long) 1L, 0},
{ "sort_key_blocks", OPT_SORT_KEY_BLOCKS, "",
(gptr*) &check_param.sort_key_blocks,
(gptr*) &check_param.sort_key_blocks, 0, GET_ULONG, REQUIRED_ARG,
Unfortunately MySQL 5.0.64 is packed with SuSE-11.1 and thus a lot of
installations will be broken. Some evil things may happen when you have
less physical memory than the maximum default values as applied by the
broken code.
Thomas
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: myisamchk buffer_size warnings
On Wed, 17 Jun 2009, Johan De Meersman wrote: Aren't those options defined in megabytes ? On Tue, Jun 16, 2009 at 4:59 PM, Thomas Spahni wrote: Hi I have MySQL 5.0.64 compiled from source. When I run myisamchk on any table I get the following warnings: Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200 Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 /etc/my.cnf contains the following: [myisamchk] key_buffer_size=20971520 sort_buffer_size=20971520 read_buffer_size=2097152 write_buffer_size=2097152 What's wrong here? Thomas Spahni Hi again According to the very fine Manual <http://dev.mysql.com/doc/refman/5.0/en/using-system-variables.html> the values can be given as Bytes or with a suffix of K|M|G. This didn't change anything in my case. It's strange that I see a similar error from mysqld when I run make test. It says: CURRENT_TEST: alias 090617 12:44:21 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295 Something must be very wrong here. Regards, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
myisamchk buffer_size warnings
Hi I have MySQL 5.0.64 compiled from source. When I run myisamchk on any table I get the following warnings: Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200 Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 /etc/my.cnf contains the following: [myisamchk] key_buffer_size=20971520 sort_buffer_size=20971520 read_buffer_size=2097152 write_buffer_size=2097152 What's wrong here? Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MAC address as primary key - BIGINT or CHAR(12)
On Thu, 14 May 2009, Ilia KATZ wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia Hi It depends. You may convert the MAC address to a decimal integer and store it as a BIGINT. Use UNSIGNED as well; there are no negative numbers involved. This may gain some speed and saves storage space. The drawback I can see is that these numbers are not human readable, but you may convert back to HEX when retrieving data. And it may break when they start using larger MAC addresses eventually. Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication config
Hi Scott
You may use the script below to reload replication if you can ensure that
the master db doesn't change during the dump operation. Otherwise you may
set a lock on the master manually.
Regards, Thomas
#!/bin/bash
#
# replicate-reload
#
# This is free software. There is no warranty at all.
# The program may melt your computer and kill your cat.
# Use at your own risk.
#
# restart new replication of DBASE on localhost; dump from MASTER
#
# Note: No changes to DBASE may take place on the master during
# the dump operation. See comments below.
#
# Set your values here:
DBASE=adbtoreplicate
MASTER=host.domain.tld
MYUSER=useronlocalhost
MYPWD=thisisagoodpassword
# Set replication user and password
REPLUSER=replicationuser
REPLPWD=replicationuserpassword
# End of user configuration
SPACE=' '
TAB=$(echo -ne "\t")
MASTER_ALIAS=$(echo $MASTER | sed -e "s/\\..*//")
MASTER_POS=$(echo "FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;" | mysql -u $MYUSER -h $MASTER -p$MYPWD $DBASE \
| sed -e "/^${MASTER_ALIAS}-bin/ !d")
#
# Beware: From this point on no changes on the master may be made
# until the dump has finished. If this can't be enforced you
# have to place a lock manually on the master and release it
# once the dump is complete.
#
MASTER_FILE=$(echo "$MASTER_POS" | cut -s -d "$TAB" -f 1)
MASTER_LOGPOS=$(echo "$MASTER_POS" | cut -s -d "$TAB" -f 2)
#echo MASTER_POS="$MASTER_POS"
echo MASTER_FILE=$MASTER_FILE
echo MASTER_LOGPOS=$MASTER_LOGPOS
# Get the dump
echo "Dumping '$DBASE' from $MASTER"
#
# User: set your own dump options here as needed
mysqldump -u $MYUSER -h $MASTER -p$MYPWD \
--skip-opt \
--add-drop-table \
--max_allowed_packet=1M \
--character-sets-dir=/usr/share/mysql/charsets \
--skip-set-charset \
--extended-insert --lock-all-tables --quick \
--quote-names --master-data=2 $DBASE \
| sed -e "/^SET / d" > ${DBASE}.sql
#
# Note: Changes on the master are allowed from here on
#
echo -e "\nCHANGE MASTER TO MASTER_HOST='$MASTER', \
MASTER_USER='$REPLUSER', MASTER_PASSWORD='$REPLPWD', \
MASTER_LOG_FILE='$MASTER_FILE', MASTER_LOG_POS=${MASTER_LOGPOS};" \
> ${DBASE}.sync.sql
echo "STOP SLAVE;" | mysql -u $MYUSER -h localhost -p$MYPWD $DBASE
# reload dumped database
echo "Reloading '${DBASE}' on localhost"
cat ${DBASE}.sql ${DBASE}.sync.sql | mysql \
-u $MYUSER -h localhost -p$MYPWD $DBASE
echo "Starting slave $(hostname)"
echo "START SLAVE;" | mysql \
-u $MYUSER -h localhost -p$MYPWD -E $DBASE
sleep 2
echo "SHOW SLAVE STATUS;" | mysql \
-u $MYUSER -h localhost -p$MYPWD -E $DBASE
rm -f ${DBASE}.sql ${DBASE}.sync.sql
exit 0
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Extending stopwords list
Hi I have configured MySQL to ignore stopwords from file /etc/my.stopwords. While playing around with myisam_ftdump I found that my fulltext index contains about a dozen words which are so common that they have a negative weight. Would it be a good idea to include these words in the stopwords file? Will this improve results for users? Any insight is welcome. Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: success return from mysql_query() when error return was expected
On Wed, 8 Apr 2009, Pete Wilson wrote: Hi everyone -- I'm a MySQL noob. I have MySQL queries in my C code and I was surprised to find I'm getting a success return from: mysql_query(pmysql, "select * from usrs where(usr=\"illegal name\""); In this table called "usrs," "usr" is the primary key and the engine is myisam. I expected an error return from this query on "illegal name," which is not in the table. An error is not returned until I call: prow = mysql_fetch_row(pmysqlres)); which returns NULL, which is great. If I run that same select from the command line, I see: mysql> select * from usrs where (usr="illegal name"); Empty set (0.00 sec) My question, finally: Is this response to my program call expected and normal for MySQL? I suppose it is, but I just want to make sure that the behavior is OK, that indeed the program call to mysql_query(select ...) must always in these circumstances return success. Thanks! -- Pete Wilson Hi Pete I suspect that "illegal name" means that this value is not existing in the table. Right? But this query is perfectly correct and should not throw an error. It's like select * from table where 1 = 0; which returns nothing, the correct answer. Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Unprintable characters in regexp
Hi How can I specify 'unprintable' characters is a MySQL regexp ? Query is (example only): SELECT something FROM table WHERE column REGEXP 'Ã\\xA0'; I'm looking for an equivalent of the search part of a sed expression like this: s/Ã\xA0/à/g which means I want to include a character with code hex A0 in the regexp. According to the manual this sytax is not supported for regexp. Any other way to do this? Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: avoiding use of Nulls (was: The <=> operator)
On Fri, 13 Mar 2009, mich...@j3ksolutions.com wrote: Explanation(5): The more you understand how the database is to be used, and the more complexity and thought you put into your database design, the less complex it will be to retrieve reliable information out of it. Furthermore, (and this is probably what makes me crazy when Nulls are evolved) after a ten year stretch of software development, where I and a team designed our own databases, I did a nine year stretch of statistical programming, using databases designed by other people, and Nulls in the data made the results unpredictable, and yeah, made me crazy! I had to write nightly processes to resolve inconsistencies in the data, if at least report inconsistencies. You know the old saying "Garbage in = Garbage out", to me Nulls are garbage, and if there is a good reason for nulls to be a part of good clean data then someone please help me understand that. Hi I'm in a argumentative mood today too. :-) I have a database logging weather data. When a station does not report a temperature, it is set to NULL. It would be a very bad idea to set it to 0 as this would ruin the whole statistics. NULL is a perfectly valid information in many cases. Cheers Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: generic remote command/script for monitoring MySQL instance health
On Mon, 9 Mar 2009, Sven wrote: Hi folks I am searching for a generic command to monitor that MySQL instance is up and running. I don't have any know-how about the schema of the DB. kind regards Sven Aluoor Hi What about 'mysqladmin ping' ? Regards, Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SQL_NO_CACHE
On Wed, 4 Mar 2009, Morten wrote: Hi, I was hoping that using SQL_NO_CACHE would help me bypass the query cache, but judging from the below it doesn't. What can I do to avoid the query cache? Thanks. Morten mysql> select count(*) from users where email = 'hello'; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (7.22 sec) mysql> select count(*) from users where email = 'hello'; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.45 sec) mysql> select count(*) from users where email = 'hello'; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.45 sec) mysql> select SQL_NO_CACHE count(*) from users where email = 'hello'; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.43 sec) Hi SQL_NO_CACHE means that the query result is not cached. It does not mean that the cache is not used to answer the query. You may use RESET QUERY CACHE to remove all queries from the cache and then your next query should be slow again. Same effect if you change the table, because this makes all cached queries invalid. But why do you want to do this? Regards, Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Finding replicated database
Hi I'm stuck with the following problem: SLAVE has a bunch of databases of which one or two are replicated from MASTER. I'm writing a shell script to be run by an ordinary user on SLAVE. This script should know which of the databases on SLAVE are replicated. Configuration: MASTER has --binlog-do-db rules. These determine what goes to binlog and will be replicated by SLAVE. SLAVE has no --replicate-do-db rules. On SLAVE I can find who the master is (SHOW SLAVE STATUS; will tell) but I can see no way to find what databases are logged by MASTER. User on SLAVE has no access to the replication user password on SLAVE and has no access to MASTER (otherwise mysql -h MASTER -e "SHOW MASTER STATUS," would do the trick). Any other way to make the SLAVE tell me what is's replicating? TIA Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Error: "Got error 139 from storage engine"
On Tue, 4 Nov 2008, Jesse wrote: > > prompt> perror 139 > > MySQL error code 139: Too big row > > > > Seems you are exceeding some limit. > > Where did you run the "perror" command from? I tried to run that in the > MySQL command line utility and got an error? > > At any rate, the field in question is a Text field. My understanding is > that the limit of a Text field is about 64K, right? I guess it's > possible that limit was exceeded, but not very likely. I'll have to do > some more checking. > > Thanks, > Jesse Hi Jesse I was running the 'perror' command from the bash command line of a Linux system. A column of type text will allow a maximum of 65'535 characters being stored, but this could be less when a multibyte character set is used. What storage engine type are you using? I found some issues with InnoDB regarding this error. Please check the .err log of mysqld as well. Regards, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: "Got error 139 from storage engine"
On Mon, 3 Nov 2008, Jesse wrote: > I have an ASP.net web application running on a WS03 server using MySQL > 5.0.67-community-nt-log. > > I have a form that allows the customer to use a visual HTML editor to input > text that will appear on a web page. So, the text contains HTML tags. When > it tries to save the text to the table in the database, I get the error, > "#HY000Got error 139 from storage engine". Does anyone know what this is and > how to fix it? prompt> perror 139 MySQL error code 139: Too big row Seems you are exceeding some limit. Regards, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replicate_Do_DB double entries
Hi, I have a slave (version 5.0.26) replicating from master (version 4.1.13). The slave's /etc/my.cnf contains the following (just once!): # what we should replicate replicate-do-db = bge replicate-do-db = blog replicate-do-db = lawlist replicate-do-db = library replicate-do-db = mandate replicate-do-db = mypal replicate-do-db = polyreg replicate-do-db = ssl replicate-do-db = timon replicate-do-db = vakw replicate-do-db = wikipolyreg Replication is working properly but when I do "mysql> show slave status;" on the slave I see: Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: bge,blog,lawlist,library,mandate,mypal, polyreg,ssl,timon,vakw,wikipolyreg,bge,blog,lawlist,library,mandate, mypal,polyreg,ssl,timon,vakw,wikipolyreg Replicate_Ignore_DB: All replicated databases appear twice. This seems odd to me. What is wrong? Thank you for any help. Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple-table UPDATE unexpected result
Dear listmembers On mysql version 4.1.13 I execute a query of this type: UPDATE a LEFT JOIN b ON a.col = b.col SET a.x = a.x + b.y WHERE b.col IS NOT NULL; I expect that column a.x is updated for every match in the join but this is not the case. Table a is updated for the first match only as in this example: mysql> use test; Database changed mysql> create table atable ( a int, b int); Query OK, 0 rows affected (0.00 sec) mysql> insert into atable values(1,10),(2,10),(3,10),(4,10); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from atable; +--+--+ | a| b| +--+--+ |1 | 10 | |2 | 10 | |3 | 10 | |4 | 10 | +--+--+ 4 rows in set (0.00 sec) mysql> create table btable (a int, b int); Query OK, 0 rows affected (0.01 sec) mysql> insert into btable values(2,5),(3,6),(3,7); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from btable; +--+--+ | a| b| +--+--+ |2 |5 | |3 |6 | |3 |7 | +--+--+ 3 rows in set (0.00 sec) mysql> update atable left join btable on atable.a = btable.a set atable.b = atable.b + btable.b where btable.a is not null; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from atable; +--+--+ | a| b| +--+--+ |1 | 10 | |2 | 15 | |3 | 16 | |4 | 10 | +--+--+ 4 rows in set (0.00 sec) However, the result I would like to achieve is (manually edited for the purpose of explanation): mysql> select * from atable; +--+--+ | a| b| +--+--+ |1 | 10 | |2 | 15 | |3 | 23 | |4 | 10 | +--+--+ 4 rows in set (0.00 sec) i.e. row 3 of atable should be updated 2 times, adding 6 and 7, as there are 2 rows in btable where column a is = 3. How can I do this? Any help is apreciated. Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Like inside in clause
On Thu, 14 Sep 2006, Ravi Kumar. wrote:
> Is there a way to specify wildcard inside values of an in clause. I want to
> be able to do this:
>
> select id, name from tableName where name in ('a%', 'b%', 'c%');
>
> instead of doing
>
> select id, name from tableName where name like 'a%' or name like 'b%' or
> name like 'c%';
no. You have to use LIKE.
Thomas Spahni
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlimport csv file import problem
On Sun, 27 Aug 2006, Jim Seymour wrote:
> Hi,
>
> I download a csv file from Yahoo in this format:
>
> ABIAX
>20.63
>2006-08-3
> ACEIX
> 8.78
>2006-08-3
> CIGAX
>10.08
>2006-08-3
> FSCTX
>22.25
>2006-08-3
> GGOAX
>20.55
>2006-08-3
> HWLAX
> 23.3
>2006-08-3
> HWMAX
>28.74
>2006-08-3
> MLEIX
>96.37
>2006-08-3
> NBPBX
>18.98
>2006-08-3
> PSVIX
>32.43
>2006-08-3
> PTRAX
> 10.3
>2006-08-3
> RGACX
>30.89
>2006-08-3
> ^DJI
> 11242.6
>2006-08-3
> ^IXIC
> 2092.34
>2006-08-3
> ^GSPC
> 1280.27
>2006-08-3
>
> My table for this data is in this format
>
> | 2006-08-02 | 20.72 | 8.81 | 10.08 | 22.19 | 20.48 | 23.19 | 28.52 |
> 96.21 | 18.87 | 32.14 | 10.31 | 30.95 | 11199.93 | 2078.81 | 1278.55 |
>
> Is there a way to get mysqlimport to pull the data from specific
> column/row to insert into a specified field? Trying to find an easier
> way than typing all of the data into a text file for import.
>
> Thanks,
>
Hi Jim,
that needs some preprocessing, but 'sed' is your friend. You could use
some shell script doing the work for you:
#!/bin/sh
BLANK=' '
TAB=' '
WHITESPACE="${BLANK}${TAB}"
DATE=$(cat mydatafile.csv | sed \
-e "3 !d" \
-e "s/^[$WHITESPACE]*//" \
-e "s/.*/'&'/")
DATA=$(cat mydatafile.csv | sed \
-e "/[A-Z]/ d" \
-e "/-/ d" \
-e "s/^[$WHITESPACE]*//" \
-e "s/.*/'&'/" | tr '\012' ',' | sed \
-e "s/,*$//")
echo "INSERT INTO mytable VALUES(${DATE},$DATA);"
exit 0
# end of shell script
The resulting queries can be piped into the mysql client.
HTH,
Thomas Spahni
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: joining 3 tables
On Tue, 14 Feb 2006, Amy Thornton wrote: > I am trying to join 3 tables together. > > Table A has 3 fields: sub_id, subject, id > Table B has 3 fields: ref_cat_id, sub_id, ref_cat > Table C has 7 fields: uid, ref_cat_id, etc. > > I am trying to join Table A and B over sub_id and join B and C over ref_cat_id > while pulling in the value of sub_id and ref_cat_id from a php program > > I tried doing it this way: > > select reference.uid from reference, subject_name, ref_cat where > subject_name.sub_id = '45' and ref_cat.ref_cat_id = '3' and > ref_cat.ref_cat_id = reference.ref_cat_id and subject_name.sub_id = > ref_cat.sub_id > > with the '45' and '3' being the values coming from my variables in > PHP. We only have MySql 4.0 so I can't use a subquery. Hi Amy, I suggest you try the following: SELECT reference.uid FROM reference INNER JOIN ref_cat ON reference.ref_cat_id = ref_cat.ref_cat_id INNER JOIN subject_name ON ref_cat.sub_id = subject_name.sub_id WHERE subject_name.sub_id = 45 AND ref_cat.ref_cat_id = 3; HTH Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
proper index definition for a self join
Dear listmembers I am using MySQL 4.0.25. I am trying to figure out how indexes should be defined to make the following query as fast as possible (i.e. use indexes): (shell script, for copy/paste) echo "DROP TABLE IF EXISTS lrows;" | mysql test echo " CREATE TABLE IF NOT EXISTS lrows ( lock_idINT NOT NULL AUTO_INCREMENT, lock_date DATETIME NOT NULL, lock_usr VARCHAR(255) NOT NULL, lock_dbVARCHAR(255) NOT NULL, lock_table VARCHAR(255) NOT NULL, lock_row INT NOT NULL DEFAULT 0, lock_pid INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (lock_id), INDEX lockone (lock_pid, lock_row), INDEX locktwo (lock_row, lock_pid, lock_db(16), lock_table(16)) ) TYPE=MYISAM;" | mysql test # some sample data echo " INSERT INTO lrows VALUES (NULL,NOW(),'tsp','preg','mbrs',1,1000); INSERT INTO lrows VALUES (NULL,NOW(),'tsp','preg','mbrs',2,1000); INSERT INTO lrows VALUES (NULL,NOW(),'tsp','preg','mbrs',3,1000); INSERT INTO lrows VALUES (NULL,NOW(),'mfu','preg','mbrs',2,9000); INSERT INTO lrows VALUES (NULL,NOW(),'mfu','preg','mbrs',5,9000); INSERT INTO lrows VALUES (NULL,NOW(),'sfa','dbto','othr',2,8000); " | mysql test # my query: echo "EXPLAIN SELECT t2.lock_id FROM lrows AS t1 INNER JOIN lrows AS t2 ON t1.lock_row = t2.lock_row WHERE t1.lock_pid = 1000 AND t2.lock_pid != 1000 AND t2.lock_db= 'preg' AND t2.lock_table = 'mbrs' LIMIT 1;" | mysql -E test # I try to find the 4th row which duplicates lock_row (value 2) for # a certain value of lock_db and lock_table but a different lock_pid EXPLAIN gives: *** 1. row *** table: t1 type: ref possible_keys: lockone,locktwo key: lockone key_len: 4 ref: const rows: 2 Extra: Using where; Using index *** 2. row *** table: t2 type: ALL possible_keys: locktwo key: NULL key_len: NULL ref: NULL rows: 5 Extra: Using where This looks quite good for t1 but does not use any index for t2. How should I set up my index to improve this situation? Any help is appreciated. Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting utf-8 data problems
Dave, what is the result of prompt> set | grep LANG ? I suspect your problem is not within MySQL. Did you look at your testfile using a editor? Thomas Spahni On Wed, 4 Jan 2006, Dave M G wrote: > MySQL List, > > I have recently switched over from Windows to Ubuntu Linux, in order to > emulate as much as possible the environment I have on my web hosting > service. The goal is to be able to develop and test my web sites more > completely at home before uploading them. > > I have successfully installed Apache, PHP, and MySQL (Most of which > came by default when installing Ubuntu). I'm comfortable writing PHP and > MySQL code in a web page, but I am very much a beginner in MySQL set up > and maintenance. > > I have all the PHP and HTML files downloaded, and now my next step is > to copy the databases from my web hosting service to my home machine. > > A lot of my database data is bilingual, English and Japanese. I try at > every turn to store and retrieve all data in UTF-8 format. > > Using phpMyAdmin on my virtual hosting service, I exported my database > information to a text file, which I then opened on my local machine, > again through the phpMyAdmin interface. > > It mostly worked. All the tables and their contents were inserted into > the home version of the database. > > However, when viewing the web pages where content is dynamically called > from the database, all the Japanese text appears on my home machine as a > series of question marks. > > So far as I know, I selected to use utf-8 encoding at every available > opportunity. I'm wondering if the problems came when saving to a plain > text file. > > Can anyone recommend the best way to preserve text encoding methods > when copying a database from one machine to another? > > Any advice is much appreciated. > > Thank you. > > -- > Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search
On Sun, 18 Dec 2005, Octavian Rasnita wrote: > Hi, > > Please tell me how can I configure MySQL 5 in order to be able to search > (using fulltext indexes) for combined words like "s-au". > > This is a single word and not 2 words but I think MySQL thinks that there > are 2 words, one of them having a single character, and the second 2 chars, > so it is not found because I have configured MySQL to index only the words > that have at least 3 chars. > > I don't think it would be a good idea to configure it to index one-char > words, so I hope there is another method. > > Thank you. > > Teddy Hi Teddy, you may try the following hack to make '-' a normal text character (these code fragments are from mysql-4.0.25 but it may work with 5.0.x as well): Change the source in myisam/ftdefs.h #define true_word_char(X) (isalnum(X) || (X)=='_') to #define true_word_char(X) (isalnum(X) || (X)=='_' || (X)=='-') and substitute another char for '-' in myisam/ft_static.c: const char *ft_boolean_syntax="+ -><()~*:\"\"&|"; which could become something like const char *ft_boolean_syntax="+ =><()~*:\"\"&|"; Then recompile and try your luck. You have to rebuild your indexes. Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding stop words table in german language
Hi Merlin, you can create your own stopword file (one word per line) and activate it in my.cnf like this: # The MySQL server [mysqld] set-variable= ft_stopword_file=/etc/my.stopwords HTH, Thomas On Fri, 28 Oct 2005, Merlin wrote: > Hi there, > > as mysql docs describe, there is a stop words table by default: > http://dev.mysql.com/doc/refman/5.0/en/fulltext-stopwords.html > > Does anybody know how to add a german table ( I guess there is an > equivalent to the engl. one)? > Does this also work with MySQL 4.0.18? > > Thank you for any help, > > Merlin > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT ... INTO OUTFILE ... and LOAD DATA INFILE ... with a key
Hi suomi, it can be done with a temporary table. See the following example. Regards, Thomas Spahni USE test; CREATE TABLE duprows ( id INT NULL AUTO_INCREMENT PRIMARY KEY, content VARCHAR(255) ) TYPE=MyISAM; INSERT INTO duprows VALUES(NULL, 'some text'),(NULL, 'some other text'); SELECT * FROM duprows; CREATE TEMPORARY TABLE duptemp SELECT * FROM duprows WHERE id = 2; ALTER TABLE duptemp CHANGE id id INT NULL; UPDATE duptemp SET id = NULL; SELECT * FROM duptemp; INSERT INTO duprows SELECT * FROM duptemp; SELECT * FROM duprows; yields: id content 1 some text 2 some other text id content NULLsome other text id content 1 some text 2 some other text 3 some other text On Fri, 19 Aug 2005, suomi wrote: > Hi listers > I once asked if there is an SQL syntax permitting to copy a row in the > same table. I got no answer, so there is no such syntax. > > now i meant to have found a work-around using (see subject). > > problem is, that when i do a SELECT * ... INTO OUTFILE .. i will also > catch the PRIMARY KEY column if there is one and the LOAD DATA INFILE > ... of this file will fail because of duplicate keys. i tried to use the > FOREIGN_KEY_CHECKS=0 but obiousely this works on foreign keys not on the > primary key. > > certainly, i can very very clumsily construct a SELECT at1, ... atn INTO > OUTFILE statement which selects all columns except the primary key. > > the REPLACE and IGNORE constructs are not what i want either, because i > want to add a row in any case, not replace an existing one nore ignore > the action. > > is there a more elegant way then the clumsy making of an attr list, > which includes alle columns except the primary key column? > > thanks very much for your interest and understanding. > > suomi > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A more general REPLACE(STR,FROM_STR,TO_STR) function
Hi all, sometimes life would be easier with a more general 'REPLACE' function available. That's when I find mysqlf dumping a database, editing with sed and reloading. My feature request: a string editing function similar to sed's s/regexp/replacement/ command to work on the contents of CHAR, VARCHAR and TEXT columns. In case of parse errors it should return the unchanged string as a default. What do others think? Any comments are welcome. Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where is my data?
Hi, check the script /etc/rc.d/mysql which is the SuSE equivalent to mysqld_safe. That's where they set datadir= and you have to change this to reflect your new path. Change the paths to the socket and pid file as well. There should be no problem to move all data to a new place and start the server giving the new datadir on the command line. Then you do host:/etc/rc.d # ./mysql start If you are going to use mysqld_safe then its something like: prompt> mysqld_safe --datadir=/data/mysql/mysqldata --user=mysql \ --pid-file=/data/mysql/mysqldata/mysqld.pid \ socket=/data/mysql/mysqldata/mysql.sock Regards, Thomas Spahni On Sat, 2 Apr 2005, kaustubh shinde wrote: > Hi, > I have suse 9.2 and MySQL 4.21 > My basedir is /var/lib/mysql > datadir /data/mysql/mysqldata > Both the directories and subdirectories and files are owned by user mysq,l > group mysql with rights 755. So I guess I have got the permissions part > right or so I hope. > Everything was working fine till I decided to move the data directory from > its previous location to the above one. > I had millions of problems after moving the data directory and the database > won't start at all. > Just to make things work I made the base and data dirs 777. Finally, I can > now start it using `mysql.server start` > But the good part ends here. I can only see mysql and test databases and i > have to log on as root to mysql. My earlier users and databases won't show. > Although I have the datafiles at the specified location. This might be coz > I ran mysql_install_db again. If I change the rights of base and data dirs > to 755, it won't work. > > I still can't start using mysqld_safe. > > My /etc/my.cnf has following: > > [mysqld] > port= 3306 > socket = /var/lib/mysql/mysql.sock > user=mysql > datadir=/data/mysql/mysqldata > bdb_home=/data/mysql/mysqldata > > [mysql_server] > basedir = /var/lib/mysql > > [mysql.server] > basedir = /var/lib/mysql > > [mysqld_safe] > err-log=/var/lib/mysql/mysqld.log > > > innodb_data_home_dir=/data/mysql/mysqldata > innodb_data_file_path=ibdata1:10M:autoextend > innodb_log_group_home_dir=/data/mysql/ > innodb_log_arch_dir=/data/mysql/ > > and so on.. > anyway, so this my.cnf doesn't seem to make any difference. when i try > > mysqld_safe & it gives > > Starting mysqld-max daemon with databases from /var/lib/mysql > /usr/bin/mysqld_safe: line 307: /var/lib/mysql/www.eh3.uc.edu.err: > Permission denied > /usr/bin/mysqld_safe: line 313: /var/lib/mysql/www.eh3.uc.edu.err: > Permission denied > STOPPING server from pid file /var/lib/mysql/www.eh3.uc.edu.pid > tee: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied > 050402 07:39:03 mysqld ended > tee: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied > > > So I try mysqld_safe --user=mysql --err-log=/var/lib/mysql/mysqld.log & > and I get > > 050402 07:40:29 mysqld started > 050402 7:40:29 InnoDB: Started > 050402 7:40:29 Fatal error: Can't open privilege tables: Table 'mysql.host' > doesn't exist > 050402 7:40:29 Aborting > > 050402 7:40:29 InnoDB: Starting shutdown... > 050402 7:40:31 InnoDB: Shutdown completed > 050402 7:40:31 /usr/sbin/mysqld-max: Shutdown Complete > > 050402 07:40:31 mysqld ended > > > and when i try to specify basedir with above command like > mysqld_safe --user=mysql --err-log=/var/lib/mysql/mysqld.log > --basedir=/var/lib/mysql & > > i get > 050402 07:38:28 mysqld started > 050402 7:38:28 Can't find messagefile > '/var/lib/mysql/share/mysql/english/errmsg.sys' > 050402 7:38:28 Aborting > > 050402 07:38:28 mysqld ended > > wonderful > > >From what I understand, mysqld_safe is supposed to read these options from > my.cnf. but its not and i have to specify them on command line. > > I have spent 4 days and sacrificed an enticing surfing trip on this and feel > like i m the dumbest guy on face of earth. every problem seem to spawn off a > new one as soon as its solved.. > > I will really appreciate if someone could just point out the exact problem > to me and give a direction. > > Thanks in advance > Kaustubh > > _ > Screensavers unlimited! http://www.msn.co.in/Download/screensaver/ Download > now! > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: QUOTE() function, what happens here?
Hi everyone,
I'm following up on my own question. There appears to be a bug in the way
how QUOTE() interacts with the mysql client.
Using --raw mode can apparently solve the problem reported in my original
post:
shell> mysql --raw -N -B -e "SELECT QUOTE(sometext) FROM foo;" test
'Pitt\'s Place'
That seems about right, but it doesn't solve the whole problem. Consider
this:
shell> mysql -e "INSERT INTO foo VALUES('Joe\'s place\tthat\'s nice');" test
(note: there is a between 'place' and 'that')
Let's retrieve this from the db, with and without --raw:
shell> mysql --raw -N -B -e "SELECT QUOTE(sometext) FROM foo;" test
'Joe\'s place that\'s nice'
shell> mysql -N -B -e "SELECT QUOTE(sometext) FROM foo;" test
'Joe\\'s place\tthat\\'s nice'
None of those results can be re-inserted as is. The only possibility to
fix this would probably be to add a new option (how about --medium-raw ?)
to the mysql client. All else would break existing scripts.
Thomas Spahni
On Tue, 15 Mar 2005, Thomas Spahni wrote:
> Dear list,
>
> I don't understand what happens here:
>
> shell> mysql -N -e "SHOW VARIABLES LIKE 'version';" test
> +-++
> | version | 4.0.14-log |
> +-++
>
> shell> mysql -e "CREATE TABLE foo (sometext VARCHAR(255));" test
> shell> mysql -e "INSERT INTO foo VALUES('Pitt\\'s Place');" test
> shell> mysql -N -e "SELECT QUOTE(sometext) FROM foo;" test
> +-+
> | 'Pitt\'s Place' |
> +-+
>
> So far so good; exactly what I would expect. The string is nicely escaped
> with ONE backslash. But now, look at this:
>
> shell> mysql -N -B -e "SELECT QUOTE(sometext) FROM foo;" test
> 'Pitt\\'s Place'
>
> Double backslash in batch mode. Same result if I pipe the query into
> mysql. Why? This can't be fed into any INSERT query. Bug or feature?
>
> Any comments from the list are very welcome.
>
> Thomas Spahni
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
QUOTE() function, what happens here?
Dear list,
I don't understand what happens here:
shell> mysql -N -e "SHOW VARIABLES LIKE 'version';" test
+-++
| version | 4.0.14-log |
+-++
shell> mysql -e "CREATE TABLE foo (sometext VARCHAR(255));" test
shell> mysql -e "INSERT INTO foo VALUES('Pitt\\'s Place');" test
shell> mysql -N -e "SELECT QUOTE(sometext) FROM foo;" test
+-+
| 'Pitt\'s Place' |
+-+
So far so good; exactly what I would expect. The string is nicely escaped
with ONE backslash. But now, look at this:
shell> mysql -N -B -e "SELECT QUOTE(sometext) FROM foo;" test
'Pitt\\'s Place'
Double backslash in batch mode. Same result if I pipe the query into
mysql. Why? This can't be fed into any INSERT query. Bug or feature?
Any comments from the list are very welcome.
Thomas Spahni
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Useful Relevance with FullText Boolean Mode - help
Hi Sergei, I have used FT search on a collection of ~ 38'000 documents totalling 550 MB of data since 4.0.2. To my observation people have a strong tendence to search for a few words they expect to be found close together in the text. As far as this is technically possible it would be helpful to give some extra relevance to those hits where the 'distance' between words is small. Best regards, Thomas Spahni On Tue, 1 Mar 2005, Sergei Golubchik wrote: > Hi! > > On Feb 26, leegold wrote: > > Is there any way to make relevance when using boolean mode more useful? > > If not, are there plans in the future Fulltext development "todo" for > > making it useful? > > Current relevance formula is described in internals.texi (see mysqldoc > repository on mysql.bkbits.net). > > Plans - yes, if the current one is bad. > But I don't know what to put instead. > Feel free to suggest a better weighting scheme :) > > Regards, > Sergei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL as Email database [sort of OT]
Brent,
see below:
On Thu, 10 Feb 2005, Brent Baisley wrote:
> I am looking to store all incoming emails into a MySQL database. I've
> looked into dbmail, but can't get it to compile under Mac OSX (I posted
> a message on that list). I was wondering if anyone could point me in
> another direction to use MySQL as an email message store. I don't need a
> webmail interface, just a way of getting messages from a mail server to
> a MySQL database. Preferably as a direct transfer, but it could be a
> script that runs periodically. Currently it seems the best path is using
> Perl, but I would think this has been done before, just can't find it on
> google.
I use procmail to forward a copy of certain mail messages to the following
shell script which you can use as a starting point.
Cheers,
Thomas Spahni
#!/bin/sh
# This shell script is free software; all possible disclaimers apply
#
# get Mail from stdin and store into MySQL database.
#
#
DBASE=mydbase
SENDER=sendertable
ARCHIV=messagearchive
MYSQL="mysql -N"
DECODEMIME="/home/user/bin/decmime.pl"
if test "$1" == "initialize" ; then
echo "CREATE TABLE IF NOT EXISTS $SENDER (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
whencrtdDATETIME,
lastaccsTIMESTAMP,
mailaddrVARCHAR(255) NOT NULL,
actcountINT DEFAULT 0,
remarks VARCHAR(255) NOT NULL,
UNIQUE INDEX (mailaddr)) TYPE=MyISAM;" | $MYSQL $DBASE
#
echo "CREATE TABLE IF NOT EXISTS $ARCHIV (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
whencrtdDATETIME,
mailfromVARCHAR(255) NOT NULL,
mailsubjVARCHAR(255) NOT NULL,
textbeitrag TEXT,
INDEX (mailfrom),
FULLTEXT INDEX (mailsubj,textbeitrag)) TYPE=MyISAM;" \
| $MYSQL $DBASE
#
exit 0
fi
# functions:
# code for escaping "$1" argument
# escaped string goes to stdout
TAB=' '
myesc() {
echo "$1" | sed -e 's/\\//g' -e "s/'/'/g" -e "s/$TAB/t/g" \
| tr -d '\012\015'
}
mysqltextescape() {
echo "$1" | sed -e 's/\\//g' -e "s/'/'/g" -e "s/$TAB/t/g" \
-e 's/$/\\n/g' -e '$ s/\\n$//' | tr -d '\012\015'
}
mysql_like_escape() {
echo "$1" | sed \
-e 's/\\//g' \
-e "s/'/'/g" \
-e 's/%/\\%/g' \
-e 's/_/\\_/g'
}
# mail comes from stdin
TEXT="$(cat -)"
FROMADDR="$(echo "$TEXT" | formail -cx From: \
| sed -e "s/^ *//" | $DECODEMIME)"
MYFROMADDR="$(mysql_like_escape "$FROMADDR")"
FROMEXISTS="$(echo "SELECT mailaddr FROM $SENDER \
WHERE mailaddr LIKE '$MYFROMADDR';" | $MYSQL $DBASE)"
if test -z "$FROMEXISTS" ; then
# insert new entry
echo "INSERT INTO $SENDER \
VALUES(NULL, NOW(), NULL, '$(myesc "$FROMADDR")', 1, '');" \
| $MYSQL $DBASE
else
# update existing entry
echo "UPDATE $SENDER \
SET lastaccs = NULL, actcount = actcount+1 \
WHERE mailaddr LIKE '$MYFROMADDR';" | $MYSQL $DBASE
fi
# put into archiv
# Global variables for metamail
export KEYHEADS=''
export MM_NOASK=1
export MM_NOTTTY=1
export MM_QUIET=1
export MAILCAPS="/home/tsp/bin/mailcap"
# White Space, one SPACE and one TAB:
WS=''
BODY="$(echo "$TEXT" | metamail -B -q -x 2>/dev/null | formail -I "" \
| sed -e "s/^[$WS]*$//" \
| sed -n -e "1,$ H" -e "$ g" \
-e "s/^\\n*//" -e "s/\\n*$//" \
-e "$ p")"
# beautify the subject line:
SUBJECT="$(echo "$TEXT" | formail -x Subject: | sed \
-e "s/AW: /Re: /g" \
-e "s/Aw: /Re: /g" \
-e "s/RE: /Re: /g" \
-e "s/R: /Re: /g" \
-e "s/^ *//" \
-e "s/ */ /g" \
-e "s/Re: Re: Re: Re: /Re: /" \
-e "s/Re: Re: Re: /Re: /" \
-e "s/Re: Re: /Re: /")"
MYBODY="$(mysqltextescape "$BODY")"
echo "INSERT INTO $ARCHIV \
VALUES(NULL, NOW(), '$(myesc "$FROMADDR")', \
'$(myesc "$SUBJECT")','$MYBODY');" \
| $MYSQL $DBASE
exit 0
###
And this is decmime.pl
###
#!/usr/bin/perl -w
use strict;
use MIME::Words qw(:all);
#my $mimewordarg;
my $decoded;
my $mimestdin;
#$mimewordarg = $ARGV[0];
$mimestdin = ;
chomp $mimestdin;
$decoded = decode_mimewords(
$mimestdin,
);
print $decoded, "\n";
exit(0);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Filtering non-ascii characters from mysql data, null, tab etc
What about the following?
mysql> create table test (txt varchar(255)) Type=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values('Some Text\0and some more');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
*** 1. row ***
txt: Some Text
1 row in set (0.00 sec)
mysql> update test set txt = replace(txt,'\0','NUL');
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test;
*** 1. row ***
txt: Some TextNULand some more
1 row in set (0.00 sec)
Cheers,
Thomas Spahni
On Thu, 10 Feb 2005, zzapper wrote:
> On Thu, 10 Feb 2005 10:17:00 +, wrote:
>
> >Hi,
> >I've successfully used the following update-replace statement to replace
> >strings in mysql data
> >
> >update tbl_county_lookup set countyname=replace(countyname,'&','and') ;
> >
> >However I've had problems trying to replace a null character 0x00h ,
> >
> >I'd be interested to know the syntax to filter null characters.
> >
> >Secondly I'd be interested in a general filter for non-ascii.
> >
> >cheers
> Can't believe I'm the only one who's ever had this problem, I've googled and
> just found a few fellow
> searchers!!
>
> (I will probably have to dump the db and use a perl script!)
>
> zzapper (vim, cygwin, wiki & zsh)
> --
>
> vim -c ":%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg?"
>
> http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Regarding replication
On Wed, 19 Jan 2005 [EMAIL PROTECTED] wrote: > Hi, > > Is replication of database is possible in MySQL 4.0.21?. Yes. > If not from which version it is available?. Could any one of you please > provide some helpful information about how to do the replication?. <http://dev.mysql.com/doc/mysql/en/Replication_HOWTO.html> Regrads, Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Search - Limits?
On Wed, 15 Dec 2004, EP wrote:
> Thomas Spahni <[EMAIL PROTECTED]> wrote:
>
> > the column type will limit the number of characters per row. A column
> > of type TEXT will hold up to 65,535 characters but with LONGTEXT you
> > can put up to 4,294,967,295 charcters into one row. I have an
> > application with Texts of up to 200 pages in one column. Full-Text
> > Search is handling this very well.
>
> Thanks...
>
> Really?! If I can follow-up with another question, does experience
> suggest Full-Text Search handles a large number of such documents
> efficiently? For example, I am expecting to have (up to) one million
> documents in my database. I was considering breaking each document into
> paragraphs for search efficiency, but if Full-Text Search can search
> return results quickly on a large number of "long" (e.g. 10,000+
> character) documents, my database has just become much simpler.
>
> Eric
My average document is 16700 bytes long and I have 21'649 of them (number
growing).
I can give you an example how slow (fast) it is:
mysql> select count(*) from unpublished where match (bgetxt) against
('Garten Waldbaum Gutachten');
+--+
| count(*) |
+--+
| 2841 |
+--+
1 row in set (1.97 sec)
mysql> select count(*) from unpublished where match (bgetxt) against
('Willensvollstrecker');
+--+
| count(*) |
+--+
| 34 |
+--+
1 row in set (0.03 sec)
This is on modest hardware (single P4, 1GB Ram, SCSI drive). My index
still fits into RAM but was not buffered for the first query above. I
would definitely try to keep your texts in a single piece each.
Thomas Spahni
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Search - Limits?
Eric, the column type will limit the number of characters per row. A column of type TEXT will hold up to 65,535 characters but with LONGTEXT you can put up to 4,294,967,295 charcters into one row. I have an application with Texts of up to 200 pages in one column. Full-Text Search is handling this very well. Thomas Spahni On Tue, 14 Dec 2004, EP wrote: > I've looked in the documentation but didn't see any indication of the > limits of Full-Text Search in terms of how many characters/words it can > process per row. > > For example, if I have a column with 4,000 character strings in it, can > I use it effectively in Full-Text Searching? > > What if the column holds gigabytes of text in each row? > > My mind is probably stuck in an "indexing" paradigm, but I'd like to > know where the limits (of Full Text search) are, if any. > > Can anyone advise? > Eric Pederson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Truncating trailing blanks of a constant
Hi everyone, recently I encountered the following problem: SELECT COUNT(id) FROM sometable WHERE somevarchar LIKE 'thistext '; returned 0 (of course!) because trailing blanks can't exist in a column of type VARCHAR. But: Shouldn't the constant be truncated automatically in this context before the comparison is made? I can certainly do it in my application but I think that it would be a consistent behaviour if MySQL would do it. Any opinions from the list? Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fwd: MySQL and partitions
On Wed, 3 Nov 2004, Yves Arsenault wrote: > Thanks for the reply, > > Is there any information on the mysql.com website on how to store data > on 2 separate partitions? There is something in the manual. The title is 'Symbolic links to databases'. Using Symbolic Links You can move tables and databases from the database directory to other locations and replace them with symbolic links to the new locations. You might want to do this, for example, to move a database to a file system with more free space or increase the speed of your system by spreading your tables to different disk. The recommended way to do this is to just symlink databases to a different disk. Symlink tables only as a last resort. Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying fulltext search
Randy,
could it be that the word 'bird' appears in more than half of all rows in
your dataset? I strongly recommend to spend a few minutes reading the
manual about Fulltext Search.
Regards,
Thomas
On Mon, 20 Sep 2004, Randy Paries wrote:
> Hello
>
> I have a table
> CREATE TABLE community_files (
> id int(3) unsigned NOT NULL auto_increment,
> type int(10) unsigned NOT NULL default '0',
> category_id int(10) unsigned NOT NULL default '0',
> filename varchar(50) NOT NULL default '',
> path varchar(255) NOT NULL default '',
> description varchar(255) NOT NULL default '',
> PRIMARY KEY (id),
> UNIQUE KEY id (id),
> KEY id_2 (id),
> KEY type (type),
> KEY catid (category_id),
> FULLTEXT KEY description (description)
> ) TYPE=MyISAM;
>
> Where I run a query like
> select * from community_files where description like '%bird%';
>
> I get records back, but if I try to do
> SELECT * FROM community_files WHERE MATCH (description) AGAINST ('bird' );
> I get nothing back
>
> I am running mysqld Ver 3.23.58 on rh9
>
> Thanks for any help
>
> Randy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help regarding securing data files
On Tue, 21 Sep 2004, VijayKumar Dogra wrote: > is there any way by which I can secure my data files such that even if > the data files are copied to other mysql server it cannot be accessed. > Some form of password protection or similar ? The short answer is: 'no'. You have to protect your server. Regards, Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving data from sql file
Uma, the answer is 42 (or - probably - "mysqldump --help | less"). But what is your problem? Are you dumping in --extended-insert mode? Can you give us an example? Regards, Thomas On Tue, 14 Sep 2004, T UmaShankari wrote: > > Hello , > >I am having some 10 rows of data in my database table. If i dump the > database the value retrieving is appending with some other row value also. > > For example > > If my one record contains the value as Hello..while dumping as a sql file > it is storing as a "Hello" + from some other row contents. > > Can anyone pls suggest why this problem coming ? > > Regards, > Uma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ft_min_word_len=2... YAY or NAY?
John, give it a try. I had to enable ft_min_word_len=2 on a collection of legal texts because people are searching for abbreviations consisting of 2 letters. It works fine for me. Thomas Spahni On Mon, 13 Sep 2004 [EMAIL PROTECTED] wrote: > I'm wondering whether or not I should implement fulltext searching for 2 > letter words within a product database (HP, TV, G5, LG, etc)... My > searchable text is currently about 600Mb and a 3-letter fulltext index > is consuming about 420Mb. > > Is the trade-off for user convience vs. performance worth it? In > general, how much will performance decrease? Or should I look for > alternatives such as a 2-letter keyword table that I build on my own? > Or do most of you just let 2-letter words slide? > > YAY or NAY? > - John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext usage for specific keywords
Lee, why not? That is what ft-search is meant for. Regards, Thomas Spahni On Tue, 7 Sep 2004, leegold wrote: > I asked a ques, in a previous post but maybe I should simplify the > question. Am I totallly crazy to use FullText for specific keyword > searchs? Let's say I have a text data type field and I load it with > keywords (text after all), the couldn't I just use a Fulltext index on > that field then search for specific keywords? I'd have the boolean mode > with a minimum of coding. > > If not, well, I haven't found tutorials that lay it out how to code for > "and, or, phrase, not" searches without Fulltext. I assume it'd be some > more SQL to do that. I'm not too handy with set theory at the moment - > it seems the fulltext pust me on the road to having a nice keyword > search w/bells and whistles w/min. of hassel even though I'm not using > it for it's heralded intended purpose. > > Thanks, > Lee G. > Wash DC > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bulk addition of users and databases
Rob,
use your imagination: this is a case for a quick and dirty shell script.
You don't have blanks in your usernames, do you? Try something like
this: (no guarantee that it works)
#!/bin/sh
# fetch users from a table and create databases
# creates a new table with preliminary passwords for those users
HOST="localhost" # where all your users reside
DBASE=test # set yours
USERTABLE=yourtablewithusernames
OPTS="" # options to access your database
MYSQL="mysql -N $OPTS $DBASE" # add any further options here
echo "CREATE TABLE IF NOT EXISTS usrpwd(\
user VARCHAR(255), pwd VARCHAR(13));" | $MYSQL
USERS="$(echo "SELECT user_name FROM $USERTABLE;" | $MYSQL)"
for USER in $USERS ; do
echo -n "User $USER"
if ! mysqladmin $OPTS create $USER ; then
echo -e "\nfailed to create database for '$USER'"
exit 1
fi
PW=$(echo "SELECT ENCRYPT('$USER');" | $MYSQL)
if ! echo "GRANT ALL ON $USER.* TO '$USER'@'$HOST' \
IDENTIFIED BY '$PW' WITH GRANT OPTION;" \
| $MYSQL ; then
echo -e "\nfailed to GRANT rights for '$USER'"
exit 1
else
echo " has Password '$PW'"
echo "INSERT INTO usrpwd VALUES('$USER','$PW');" \
| $MYSQL
fi
done
echo "SELECT * FROM usrpwd ORDER BY user;" | $MYSQL
# this is the end of the shell script
Have fun
Thomas Spahni
On Tue, 7 Sep 2004, Rob Keeling wrote:
> Having googled extensively, I can`t seem to find a way to do the following.
>
> I have a mysql table, with around 1200 usernames in it.
>
> What I want to do is programmatically add each user, and create a database
> of the same name
> that that user has access to.
>
> (This is for a school web server, we want to allow students to try out mysql
> querys with phpmyadm etc)
>
> Any suggestions?
>
> Thanks
>
> Rob Keeling
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Stopwords
Ari, what's the result of
mysql -N -e "SHOW VARIABLES;" | grep 'ft_min_word_len'
? Default is 4 but you need to reduce this to 3 (or even to 2, if you
want to match first_name against('Al')).
Regards, Thomas Spahni
On Sun, 5 Sep 2004, Ari Denison wrote:
> Hello list -
> I'm trying to do a full text search for the the string "May" using
> the following query:
>
> SELECT * FROM students WHERE MATCH(first_name) AGAINST("May");
>
> There are a number of students in that table wit the first_name of May.
> And, yes, I've removed may from the stopwords list and have reindexed
> the field but still return no records.
>
> Any idea how to get my May students to show in full text search results?
>
> Thanks,
> Ari Denison
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication config without stopping master server
Naveen, You don't need to shut down the master but you have to stop writing to the database for taking the snapshot. Follow the description in the manual: 3. If you are using MyISAM tables, flush all the tables and block write queries by executing `FLUSH TABLES WITH READ LOCK' command. mysql> FLUSH TABLES WITH READ LOCK; and then take a snapshot of the data on your master server. While the read lock placed by `FLUSH TABLES WITH READ LOCK' is in effect, read the value of the current binary log name and offset on the master: mysql > SHOW MASTER STATUS; +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | mysql-bin.003 | 73 | test,bar | foo,manual,mysql | +---+--+--+--+ 1 row in set (0.06 sec) The `File' column shows the name of the log, while `Position' shows the offset. In the above example, the binary log value is `mysql-bin.003' and the offset is 73. Record the values. You will need to use them later when you are setting up the slave. Once you have taken the snapshot and recorded the log name and offset, you can re-enable write activity on the master: mysql> UNLOCK TABLES; Regards, Thomas Spahni On Tue, 31 Aug 2004, Naveen C Joshi wrote: > Hi All : > > I am still waiting for the response. > > Thanks in advance. > > Regards > Naveen > > - Original Message - > From: "Naveen C Joshi" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Friday, August 27, 2004 4:17 PM > Subject: Re: replication config without stopping master server > > > > The replication slave server has the Physical memory 3.6 GB and the my.cnf > > file is as below : > > > > [client] > > port= 3306 > > socket = /var/lib/mysql/mysql.sock > > [mysqld] > > datadir = /data1/mysql > > basedir = / > > port= 3306 > > socket = /var/lib/mysql/mysql.sock > > skip-locking > > set-variable= max_allowed_packet=1M > > set-variable= table_cache=512 > > set-variable= sort_buffer=2M > > set-variable= record_buffer=2M > > set-variable= thread_cache=8 > > set-variable= thread_concurrency=8 > > set-variable= myisam_sort_buffer_size=64M > > set-variable= thread_stack=256k > > log-bin > > server-id = 1 > > master-host=xxx.xxx.xxx.xxx > > master-user= yyy > > master-password= zzz > > master-port= 3306 > > > > > > innodb_data_home_dir = /data1/mysql/ > > innodb_data_file_path = ibdata1:800M:autoextend > > innodb_log_group_home_dir = /data1/mysql/ > > innodb_log_arch_dir = /data1/mysql/ > > set-variable = innodb_buffer_pool_size=2000M > > set-variable = innodb_additional_mem_pool_size=20M > > set-variable = innodb_log_file_size=300M > > set-variable = innodb_log_buffer_size=150M > > innodb_flush_log_at_trx_commit=0 > > set-variable = innodb_lock_wait_timeout=50 > > > > set-variable= innodb_file_io_threads=4 > > transaction-isolation = READ-COMMITTED > > innodb_thread_concurrency = 4 > > > > > > [mysqldump] > > quick > > set-variable= max_allowed_packet=16M > > > > [mysql] > > no-auto-rehash > > # Remove the next comment character if you are not familiar with SQL > > #safe-updates > > > > [isamchk] > > set-variable= key_buffer=256M > > set-variable= sort_buffer=256M > > set-variable= read_buffer=2M > > set-variable= write_buffer=2M > > > > [myisamchk] > > set-variable= key_buffer=256M > > set-variable= sort_buffer=256M > > set-variable= read_buffer=2M > > set-variable= write_buffer=2M > > [mysqlhotcopy] > > interactive-timeout > > > > Regards > > > > Naveen > > > > > > - Original Message - > > From: "Naveen C Joshi" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Friday, August 27, 2004 2:04 PM > > Subject: replication config without stopping master server > > > > > > Hi, > > > > I have MySQL-4.0.5a-beta installed on my RH9.0 Linux machine. This > machine > > is as a replication master server. I have configured the other machine as >
Re: Testing for the existence of an index
Jesse, mysql> SHOW INDEX FROM mytable; gives you all indexes for `mytable`; you can process the results with perl. Thomas Spahni On Mon, 23 Aug 2004, Jesse Sheidlower wrote: > I have a database where, most of the time, I'm bulk-loading > data into new tables from an external source, several million > rows at a time. For this purpose, I create the tables without > indexes, and then add all my indexes after the load is done, > for efficiency purposes. > > I'd now like to add the possibility of adding some data to the > existing database. In this case, the indexes exist, and then > the new data will be indexed as it goes in (which is OK given > the relatively small amount of data to be processed this way). > > I'd like to be able to test for the existence of an index, so > that after the bulk-load, I can see if there are indexes, > there won't be any, and I can create them; but after an > addition to an existing database, there will be indexes, and I > won't create them. > > How do I do this? It wasn't clear from the manual, and I'm > away from my books now so can't look there for advice. I'm > using Perl to process the data, if there's a Perlish way of > doing things that would be easier than SQL. > > Jesse Sheidlower > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext performance problem.
Hi Fredrik,
a LEFT JOIN could be faster (I'm not sure, try it). And you don't have a
fulltext index on msg_header.list.
What about this?
SELECTmsg_header.bodyid, msg_header.id, msg_header.subject,
msg_header.mfrom, msg_header.date, msg_header.list
FROM msg_header
LEFT JOIN msg_body ON msg_header.bodyid = msg_body.id
WHERE msg_header.list LIKE 'LISTNAME%'
AND MATCH(msg_body.body) AGAINST('WORD');
Regards, Thomas Spahni
On Fri, 20 Aug 2004, Fredrik Carlsson wrote:
> Hi all,
>
> I'm running a small mail archive and have a little problem with the
> fulltext search performance.
> I really appreciate any tips/design suggestions (even if it dont have to
> do with the search problem ;) ).
>
> Database schema:
>
> mysql> describe msg_header;
> +-+--+--+-+-++
> | Field | Type | Null | Key | Default | Extra |
> +-+--+--+-+-++
> | id | int(11) | | PRI | NULL| auto_increment |
> | parent | int(11) | YES | MUL | NULL||
> | bodyid | int(11) | YES | | NULL||
> | list| varchar(80) | YES | MUL | NULL||
> | mfrom | varchar(80) | YES | | NULL||
> | mto | varchar(80) | YES | | NULL||
> | subject | varchar(200) | YES | MUL | NULL||
> | mcc | varchar(80) | YES | | NULL||
> | sdate | varchar(45) | YES | | NULL||
> | batch | varchar(80) | YES | MUL | NULL||
> | msgid | varchar(90) | YES | | NULL||
> | date| datetime | YES | MUL | NULL||
> +-+--+--+-+-++
> 12 rows in set (0.00 sec)
>
>
> mysql> describe msg_body;
> +---+-+--+-+-++
> | Field | Type| Null | Key | Default | Extra |
> +---+-+--+-+-++
> | id| int(11) | | PRI | NULL| auto_increment |
> | body | text| YES | MUL | NULL||
> +---+-+--+-+-++
> 2 rows in set (0.00 sec)
>
> index from msg_body;
>
> *** 1. row ***
> Table: msg_body
> Non_unique: 0
> Key_name: PRIMARY
> Seq_in_index: 1
> Column_name: id
> Collation: A
> Cardinality: 295996
> Sub_part: NULL
> Packed: NULL
> Null:
> Index_type: BTREE
> Comment:
> *** 2. row ***
> Table: msg_body
> Non_unique: 1
> Key_name: id
> Seq_in_index: 1
> Column_name: id
> Collation: A
> Cardinality: 295996
> Sub_part: NULL
> Packed: NULL
> Null:
> Index_type: BTREE
> Comment:
> *** 3. row ***
> Table: msg_body
> Non_unique: 1
> Key_name: body
> Seq_in_index: 1
> Column_name: body
> Collation: A
> Cardinality: 295996
> Sub_part: NULL
> Packed: NULL
> Null: YES
> Index_type: FULLTEXT
> Comment:
> 3 rows in set (0.00 sec)
>
>
> The search querys using fulltext indexes takes around > 1minute and no
> one want to use a search that slow :/
>
> The Query is the following:
>
> SELECT msg_header.bodyid,msg_header.id,
> msg_header.subject,msg_header.mfrom, msg_header.date, msg_header.list FROM
> msg_header,msg_body WHERE msg_header.bodyid=msg_body.id AND
> match(msg_header.list) against('LISTNAME')
> AND match(msg_body.body) AGAINST('WORD');
>
> For a couple of month ago the msg-body and msg-headers parts where in
> the same table and the fulltext search was really fast < 1 sec, but
> everything else just became slower so i splitted it upp in two tables.
> But now i need to match msg_header.bodyid against msg_body.id to be able
> to now witch body that belongs to with header and i think thats where
> things get slow..
>
> I ran an explain select query and the following result turned upp
>
> ++--+-+-+-+---+--+-+
> | table | type | possible_keys | key | key_len |
> ref | rows | Extra |
> ++--+-+-+-+---+--+-+
> | msg_header | fulltext | list_4 | list_4 | 0
> | |1 | Using where |
> | msg_body | eq_ref | PRIMARY,id,body | PRIMARY | 4 |
> msg_header.bodyid |1
Re: Mysql-Prozesse lassen sich nicht mehr killen :-(
Hi Daniel, try this: prompt> mysqladmin kill [id],[id] (for usage see mysqladmin --help | less) Cheers, Thomas Spahni On Mon, 16 Aug 2004, Beuter Daniel wrote: > Hallo miteinander dies ist mein erster Thread. > Weil ich nimmer weiter weiß wende ich mich an Euch. > Hab nen Traffich collector der in ne Datenbank auf nem anderen Server > schreibt. > Nachdem der collector gerade am laden in die Datebank war wurde per cronjob > das rotieren des in-use tables angestoßen. > Natürlich hat sich das laden aufgehängt. Mein problem ist nun das sich nun > über die Nacht hinweg so 15 Prozesse angehäuft haben. > 1.Problem:Hab nun : > Mysql> kill z.B 23465; > Gemacht nun steht da killed in der prozesslist aber die Verbindungen gehen > nicht weg. > Gibt's da ne möglichkeit die ganz verschwinden zu lassen um wieder was in > die Datenbaqnk zu laden? > > 2.Problem: > Wenn ich killall -9 mysqld mach und mysqld restart dann stoppt der server > aber die Prozesse sind noch da in ps -edf > Und ich kann die nicht mit kill -9 2344 usw. abschießen > Nur wenn die Prozesse noch da sind is es nicht möglich den mysqld neu zu > starten > > Was kann ich tun um die Zombies ohne reboot zu beenden. > Benutze fedora core1 und > mysql Ver 12.22 Distrib 4.0.20, for pc-linux (i686) > > mit freundlichem Gruß / kind regards > Daniel Beuter > > SIEMENS > I&S IT PS 221 OP4 > Network Solutions > Werner von Siemens Str. 60 > 91052 Erlangen > > Fon: +49 (9131) -6303-211 > Fax: +49 (9131) 7-42234 > > mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1045: Access denied for user: 'mysqladmin@localhost' (U sing password: YES)
On Mon, 19 Jul 2004, Laurent wrote: > On Monday 19 July 2004 14:59, Victor Pendleton wrote: > > mysqladmin is an executable. Did you set up an user named mysqladmin that > > can connect from the localhost? > > Exactly, it should be able to connect, but it doesn't. Should I change the > name from mysqladmin to mysql or something similar? I'll try. > > Laurent Laurent, with a fresh install, use 'root' as username and no password. Be sure to set a password later. Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Implementing full text searching - need recommendations
Hi, you can try to add your headings to the stopword list, but remember: - A "word" is any sequence of characters consisting of letters, digits, `'', and `_'. Any "word" that is present in the stopword list or is just too short is ignored. - The stopword list is global and will affect all tables. As for me, in your situation I would prefer another aproach distributing your text to several TEXT columns. Then your headers become column names. Cheers, Thomas Spahni On Mon, 12 Jul 2004, leegold wrote: > Please let me show you one (TEXT data-type) item example. You will see > "Title_[ ... Author[ ...Resp_Org__[..." Obviously, I don't want > those strings searchable, they are headings, but I do want the content > to the right of them indexed and searchable. So looking at it - what > should I put in my stop words list...eg. Author[ ? > > This would be a typical text field indexed by fulltext. I have never > used it before - any recomendations appreciated - it's formatted below > for readability. I have the actual field item below this formatted item > complete with '\r\n'...it's text afterall. Thanks for the help. > > > Title_[ Balloon Capabilities and Futures] > > Author[ Thomas W. Kelly > > Resp_Org__[ Air Force Cambridge Research Labs. > > FundingOrg[ > > Date__[ Dec 1963 > > Report_No_[ Found in: AFCRL-TR-63-919, AFSG No. 154 > > > Reposit_No[ Found in: AD-614 065 ContractNo[ Descript__[ Conference > Paper, 25 p > > Notes_[ This symposium was held in Boston, MA on 25 to 27 September > 1963. Twenty papers were presented, of which 16 were published. This > paper is found on pp 3-27.] > > Subj_Terms[ Balloon technology, tethered balloons, payload orientation, > hot air balloons, balloon design, manned balloons, instrumentation] > > Content___[ The papers covered present load and altitude capabilities of > many types of plastic balloons. Objectives of current research in > balloon technology are described, including extension of present > capabilities, increased reliability, and longer duration.] > > ElecAccess[ http://library/Databases/Balloon/Data/BT1034.02.pdf > > Avail_From[ WFF/BTL > > > > > Title_[ Balloon Capabilities and Futures]\r\nAuthor[ Thomas W. > Kelly\r\nResp_Org__[ Air Force Cambridge Research > Labs.\r\nFundingOrg[\r\nDate__[ Dec 1963\r\nReport_No_[ Found in: > AFCRL-TR-63-919, AFSG No. 154\r\nReposit_No[ Found in: AD-614 > 065\r\nContractNo[\r\nDescript__[ Conference Paper, 25 p\r\nNotes_[ > This symposium was held in Boston, MA on 25 to 27 September 1963. Twenty > papers were presented, of which 16 were published. This paper is found > on pp 3-27.]\r\nSubj_Terms[ Balloon technology, tethered balloons, > payload orientation, hot air balloons, balloon design, manned balloons, > instrumentation]\r\nContent___[ The papers covered present load and > altitude capabilities of many types of plastic balloons. Objectives of > current research in balloon technology are described, including > extension of present capabilities, increased reliability, and longer > duration.]\r\nElecAccess[ > http://library/Databases/Balloon/Data/BT1034.02.pdf\r\nAvail_From[ > WFF/BTL > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: making lower case then first char to upper case?
On Wed, 30 Jun 2004, Aaron Wolski wrote: > Hi Guys, > > I'm trying to figure out of this is possible. I know I could do it in > PHP but I am dealing with a ton of records and would rather put the > processing on the DB than PHP/client side. > > Question is. can I do a SELECT query on a column that changes all the > results to lower case and THEN changes the first character of each > result to an upper case? > > Example: > > Currently in DB: AARON > to Lowercase: aaron > to Uppercase: Aaron > > > Any idea on if I can do this and how I might approach it? what about: SELECT CONCAT( UPPER( LEFT(field,1) ), LOWER( SUBSTRING(field,2) ) ) AS Something FROM ... Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lots of databases...how wrong is it?
On Sat, 29 May 2004, Yannick Warnier wrote: > > > - how wrong is creating 20 databases (total 400 tables) when you know > > > you could create just one (total around 200 tables)? > > > > For linux ext2, I'd rather have 20db 20table than 1db 200 table. > > Neither approach practical limits tho. > > Well... hopefully it doesn't. > > > > - what does MySQL handle better? Databases or tables? > > > > Depends on filesystem/table type. For myisam, databases. Each > > database creates 1 directory entry, each table creates 3 file entries. > > Ext2 / MyIsam, so I guess that makes 2800 directories with 60 files each > or something similar I had a related problem: ext2 was very slow to list a drectory with 14'000 files. When I switched to ReiserFS it was fine. Recently someone on this list compared the performance of different file systems using MySQL. You may want to check the archives. Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Scenario
Hi, sharing a data directory between different servers (probably using nfs) is not a good idea and it would create a new bottleneck. A quote from the very fine manual: "Make it easy for yourself: Forget about sharing a data directory among servers over NFS. A better solution is to have one computer that contains several CPUs and use an operating system that handles threads efficiently." Alternatively you can spend on storage and replicate all data to 40 slave servers. Regards, Thomas Spahni On Thu, 27 May 2004, tachu wrote: > WOuld the following scenario be possible > I currently have about 1.2 Tb of data that i need to transform into > mysql and be able to server a very high amount of pages from a > discussion board. would i be able to place the mysql/data directory in a > main server with huge storage and then have several mysql server > instances running on different machines accessing the same /mysql/data > directory? would there be any limitations. i need to be able to server > about 200/sec on each server and have about 40 servers. basically my > ideas is to have several mysql servers running but one main data > repository. any help is appreciated > > Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Textfile to table
Hi Paul,
assuming that the first three occurances of blanks can be regarded as
field separators you can do:
prompt> cat file.txt | sed \
-e "s/'/'/g" \
-e "s/ */','/" \
-e "s/ */','/" \
-e "s/ */','/" \
-e "s/^/INSERT INTO categories VALUES('/" \
-e "s/$/');/" | mysql database
That's what sed is good for.
Regards,
Thomas Spahni
On Thu, 27 May 2004, Haplo wrote:
> Hi,
> I am trying to add this info into a table and I know there is a way to
> do it without using the insert command on every line of data.
>
> 201 200 NJ Jersey City
>
> INSERT INTO categories VALUES ('201', '200', 'NJ', 'Jersey City');
>
> there are way too many lines to do by manual inserts.
> Any comments would help. Thanks
> Paul
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT HELP
Andre, have a look at JOIN. This can solve your problem. Thomas Spahni On Fri, 30 Apr 2004, Andre MATOS wrote: > Is it possible to create a Select performing a math formula? For example: > > First I need to add two values come from the same table but from different > records. The result will be divided from one number got from another > table. Now, the new result will be added with another value got from > another table creating the final result. Like this: > > ((value_from_record_1_from_table_A + value_from_record_15_from_table_A) / > value_from_table_B ) + value_from_table_C > > Is this possible? Is there anyone who can help me to create this SELETC? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: genome sequence
Hi Liz, a column of type BLOB takes a maximum of 65535 bytes; try MEDIUMBLOB or even LONGBLOB. Regards, Thomas Spahni On Tue, 20 Apr 2004 [EMAIL PROTECTED] wrote: > hi, > I am trying to enter genome sequences of length 170 and more into > mysql database. > > I have created a table sequence like: > > create table sequence(seq blob); > > i am using python scripts to put the sequence into this field. > the python GUI gives me this error: > > OperationalError: (2006, 'MySQL server has gone away') > > I am not able to enter the sequence. > > what is wrong??? > > Liz > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with 4.0.18 and mysqldump
Jochen, what's the result of prompt> mysql -uroot -pXXX -hxxx.xxx.xxx.xxx when you do it on the client host? (I still suspect that permissions are not properly granted). Regards, Thomas Spahni On Wed, 17 Mar 2004, Jochen Kaechelin wrote: > I use the following Script to backup a remote MySQL-Server. > > > DATUM=`date +"%Y_%m_%d__%H_%M"` > BACKUPDIR="/home/jochen/SICHERUNG/MySQL_Dumps/debby/$DATUM" > > echo "" > echo "Erzeuge Sicherungvereichnis $BACKUPDIR..." > echo "" > > mkdir -p $BACKUPDIR > > > for DB in db1 db2 > do > echo "" > echo "Erzeuge lokele Sicherung der Datenbank $DB..." > echo "" > > /usr/bin/mysqldump -uroot -pXXX -hxxx.xxx.xxx.xxx --opt $DB > > $BACKUPDIR/$DB.sql > > echo "" > echo "Entferne moegliche Sicherungsduplikate..." > echo "" > > rm -rf $BACKUPDIR/$DB.sql.gz > > echo "" > echo "Komprimiere aktuelle Sicherungsdateien..." > echo "" > gzip $BACKUPDIR/$DB.sql > done > > and I always get the following error: > > /usr/bin/mysqldump: Got error: 2003: Can't connect to MySQL server > on '212.87.142.236' (111) when trying to connect > > > Permissions are ok and I can modify the complete db with my php > scripts. > > where's the error? > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum Database size
Stefan, it depends ... For MyISAM tables max size is limited by the maximum filesize of your OS/Filesystem. Regards, Thomas On Fri, 12 Mar 2004, Baum, Stefan wrote: > Hi, > > I'm planning to use MySQL for logging from an SMTP-Relay. > What is the maximum size of the database, that can be reached? > > Stefan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error
did you 1. read the manual and 2. run mysql_install_db ? Regards, Thomas Spahni On Wed, 3 Mar 2004, Liew Toh Seng wrote: > > How to fix this problem > > > > > > 040303 17:40:51 mysqld started > > InnoDB: The first specified data file ./ibdata1 did not exist: > > InnoDB: a new database to be created! > > 040303 17:40:51 InnoDB: Setting file ./ibdata1 size to 10 MB > > InnoDB: Database physically writes the file full: wait... > > 040303 17:40:52 InnoDB: Log file ./ib_logfile0 did not exist: new to > > be created > > InnoDB: Setting log file ./ib_logfile0 size to 5 MB > > InnoDB: Database physically writes the file full: wait... > > 040303 17:40:52 InnoDB: Log file ./ib_logfile1 did not exist: new to > > be created > > InnoDB: Setting log file ./ib_logfile1 size to 5 MB > > InnoDB: Database physically writes the file full: wait... > > InnoDB: Doublewrite buffer not found: creating new > > InnoDB: Doublewrite buffer created > > InnoDB: Creating foreign key constraint system tables > > InnoDB: Foreign key constraint system tables created > > 040303 17:40:53 InnoDB: Started; log sequence number 0 0 > > 040303 17:40:54 Fatal error: Can't open privilege tables: Table > > 'mysql.host' doesn't exist > > 040303 17:40:54 mysqld ended > > > > --- > Best Regards > Liew Toh Seng > Icq No: >> 36835809 << > MSN: >> [EMAIL PROTECTED] << > * .--. > * |o_o | > * |:_/ | > * // > * (| | ) > * /'\_ _/` The Internet Solution Company > * \___)=(___ My Directory Sdn Bhd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I import a SQL file with over 3000 lines in it ?
Adam, use the mysql client tool. yourbox> mysql -h host -u user -pPasswd yourdatabase < sqlfile.sql Cheers, Thomas Spahni On Thu, 12 Feb 2004, Adam Staunton wrote: > Hi all, > > I have an sql file that I exported from a database of mine that has over > 3000 lines (records) in it. How do I go about importing that information > into the database ? > > Previously I was using the same sql to test it, but it was only a few > hundred lines or so and I simply cut-and-pasted the sql into the "Run > SQL query/queries on database ..." text box and hit "Go". It created the > tables and inserted the data. > > Any help would be greatly appreciated. > > cheers, > Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: There has to be a way to do this
Mike,
you are close: you want the mysql client to give back just the data, no
column description. Change this line to read:
MYSQL="/usr/local/mysql/bin/mysql --user=$username --password=$password
--host=$server --skip-column-names cetechnology"
On Tue, 10 Feb 2004, Mike Tuller wrote:
> Ok. I think I am close to getting this. Here is what I have.
>
>
> MYSQL="/usr/local/mysql/bin/mysql --user=$username --password=$password
> --host=$server cetechnology"
>
> RESULT=$(echo "select count(*) from hardware_assets where
> ethernet_address='$ethernet_address' " | $MYSQL)
> if [ "$RESULT" = "0" ] ; then
> echo "INSERT INTO hardware_assets (ethernet_address) VALUES
> ('$ethernet_address');" | $MYSQL
> else
> echo "UPDATE hardware_assets SET operating_system='10.3.3';" | $MYSQL
> fi
>
> echo $RESULT
>
> When I run this, it always does an update, and updates all records, not just
> the ones that are matching $ethernet_address. I added the 'echo $RESULT' to
> see what it was returning. It comes back with 'count(*) 1'. I change the if
> statement to read 'if["$RESULT" = "count(*) 0" and the value for
> $ethernet_address to a number that I do not have in the database. It does
> not add the new ethernet address, and updates all of the records.
>
> So that tells me that there is something wrong the value of $RESULT and the
> comparison in the if statement. Further, if I change the value of $RESULT to
> '12345' and change the if line to
> if [ "$RESULT" = "12345" ] ; then
> It adds a record to the database. So there is something wrong with what is
> returned. Neither "0" or "count(*) 0" seem to work. So, does anyone have an
> idea as to what I need to put in for the comparison?
>
>
> Mike
>
> > From: gerald_clark <[EMAIL PROTECTED]>
> > Date: Mon, 09 Feb 2004 14:28:27 -0600
> > To: Mike Tuller <[EMAIL PROTECTED]>
> > Cc: MySql List <[EMAIL PROTECTED]>
> > Subject: Re: There has to be a way to do this
> >
> > This is NOT a script that can run under mysql.
> > It is a bash script that calls mysql.
> >
> > MYSQL="/usr/local/mysql/bin/mysql --user=$username --password=$password
> > --host=$server cetechnology"
> >
> > RESULT=`echo "select count(*) from hardware_assets where
> > ethernet_address='$ethernet_address'" | $MYSQL
> > if [ "$RESULT" = "0" ] ; then
> > echo "INSERT INTO hardware_assets (ethernet_address) VALUES
> > ($ethernet_address);" |$MYSQL
> > else
> > echo "UPDATE hardware_assets SET operating_system='10.3.3';" | $MYSQL
> > fi
> >
> >
> > Mike Tuller wrote:
> >
> >> I changed my script to this:
> >>
> >> /usr/local/mysql/bin/mysql --user=$username --password=$password
> >> --host=$server
> >>
> >> RESULT=`echo "select count(*) from hardware_assets where
> >> ethernet_address='$ethernet_address'" | cetechnology'
> >> if [ "$RESULT" = "0" ] ; then
> >>echo "INSERT INTO hardware_assets (ethernet_address) VALUES
> >> ($ethernet_address);"
> >> else
> >>echo "UPDATE hardware_assets SET operating_system='10.3.3';"
> >> fi
> >>
> >> Where cetechnology is the database. All the variables are set.
> >>
> >> When I run this, it starts the mysql client application, with the mysql>
> >> prompt. Nothing is inserted or updated in the database though.
> >>
> >> This is the same problem I had when I tried to do it this way, but I am not
> >> knowledgeable in shell scripting yet to know what I am doing wrong.
> >>
> >>
> >>
> >>
> >>
> >>
> >>> From: gerald_clark <[EMAIL PROTECTED]>
> >>> Date: Mon, 09 Feb 2004 11:11:24 -0600
> >>> To: Mike Tuller <[EMAIL PROTECTED]>
> >>> Cc: MySql List <[EMAIL PROTECTED]>
> >>> Subject: Re: There has to be a way to do this
> >>>
> >>> IF works on the selections not on the query.
> >>> Select IF(lastname='clark','Correct',''Incorrect'), firstname from
> >>> namefile;
> >>>
> >>> You need to do the checking in your script.
> >>> For example in bash:
> >>> RESULT=`echo "select count(*) from manefile where lastname='clark'" |
> >>> mysql database`
> >>> if [ "$RESULT" = "0" ] ; then
> >>> echo "insert into namefile ...
> >>> else
> >>> echo "update namefile ..
> >>> fi
> >>>
> >>>
> >>> Mike Tuller wrote:
> >>>
> >>>
> >>>
> I have posted this question a few times, and have not seen the answer that
> I
> need.
>
> I have a shell script, that gathers information from systems, and I want
> that info to be entered into a database. I want it to check first to see if
> the data is already entered, and if not, add it. If it has already been
> entered, then update the record.
>
> I would think that some type of if/else statement would work, but I can't
> get the IF statement http://www.mysql.com/doc/en/IF_Statement.html to work
> correctly in MySql.
>
> Here is what I have:
>
> "IF SELECT * FROM hardware_assets WHERE
> ethernet_address='$ethernet_address'
> IS NULL\
> THEN INSERT into hardware_assets (ethernet_address) VALUES
> ($ethernet_address)\
> ELSE\
Re: FULLTEXT Search and Hyphens
Michael,
have a look at the sources, especially myisam/ft_parser.c near line 108
and at myisam/ftdefs.h. It should not be difficult to hack the sources to
make the hyphen a real character. This will solve your problem (but could
create some new ones on others types of text input).
Thomas
On Wed, 31 Dec 2003, michael elston wrote:
> I am having some trouble with fulltext search when searching a Table
> for parts numbers which contain HYPHENS ( - ) and i am 90% sure that is
> where the problem is.
>
> My query is:
> SELECT * FROM ms_items
> where MATCH (it_mnfgID, it_title, it_descrip) AGAINST ('*kk-4835*' IN
> BOOLEAN MODE )
>
> What i want to be able to accomplish is say your are searching for a
> "KK-4835"
>
> I want to be able to type in "KK-4835", "KK4835", or even "KK 4835) and
> all return the same part. as it stands right now, searching for even
> just "kk-" returns nothing but searching for "4835" will return items
> with 4835.
>
> is this a known problem with MySQL or is there a way around it? I am
> half tempted to create another column for keywords.
>
> thanks for any help
>
> -me
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.0 has been released
Hi, is there any change in data format for MySQL 5.0.0? Is it safe (for a user of stable 4.0.17) to test 5.0.0 on existing data and then go back to 4.0.17 for production? Thanks, Thomas On Wed, 24 Dec 2003, Michael Widenius wrote: > Hi, > > MySQL 5.0.0, a new version of the popular Open Source/Free Software > Database Management System, has been released. It is now available in > source and binary form for a number of platforms from our download pages > at http://www.mysql.com/downloads/ and mirror sites. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sporadic myisam table corruption.
Hi, either this should be properly debugged (if the problem persists with 4.0.17) or it is a hardware glitch. MyIsam tables have proven quite stable anch changing to InnoDB might be looking for trouble (if hardware is the culprit). But it's true that InnoDB could be much more efficient in your environment. Thomas Spahni On Fri, 12 Dec 2003, Victor Medina wrote: > Change myisam format to something else, like InoDB or BDB > > Best Regards! > > On Fri, 2003-12-12 at 15:05, James E Hicks III wrote: > > I'm having sporadic myisam table corruption. This table is constantly being > > added to, updated, and deleted from. PHPMyAdmin reports that "The table is in > > use" when I try to access this table after corruption. After I perform this > > step in the correct directory everything goes back to normal. > > > > myisamchk --recover troubled_table > > > > After reading the mysql manual page I changed all the VARCHAR fields to CHAR > > fields, hoping my problem would dissapear. This change has had no effect on > > my problem. The only things in the error log are start ups and shutdowns > > performed nightly. > > > > Here's the table definition. > > > > CREATE TABLE troubled_table ( > > office smallint(4) unsigned NOT NULL default '0', > > ticket_id char(30) NOT NULL default '', > > item_quantity mediumint(8) unsigned NOT NULL default '0', > > from_face char(30) NOT NULL default '', > > from_down decimal(6,1) unsigned NOT NULL default '0.0', > > from_up decimal(6,1) unsigned NOT NULL default '0.0', > > from_depth decimal(6,1) unsigned NOT NULL default '0.0', > > to_face char(30) NOT NULL default '', > > to_down decimal(6,1) unsigned NOT NULL default '0.0', > > to_up decimal(6,1) unsigned NOT NULL default '0.0', > > to_depth decimal(6,1) unsigned NOT NULL default '0.0', > > associated_document char(30) NOT NULL default '', > > PRIMARY KEY (office,ticket_id), > > KEY move_id (ticket_id) > > ) TYPE=MyISAM COMMENT='Material Move Orders'; > > > > mysql --version says: > > > > mysql Ver 12.20 Distrib 4.0.13, for pc-linux-gnu (i686) > > > > What can I try now? > > > > James Hicks > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL installation with SuSE Linux 9.0 and YaST
Franz, it could not be easier. 1. install MySQL using Yast's software menu. 2. use the Yast run level editor to have MySQL up at run levels 3 and 5 and to start the application. 3. be root in a xterm window and type: mybox:~ # mysql ... and you will see: mysql> here you are accessing a running system from the client. Use GRANT to set a password for root and to add new users. Where is the problem? Liebe Grüsse Thomas Spahni On Sun, 7 Dec 2003, Franz Edler wrote: > I am not very experienced with Linux, but SuSE Linux 9.0 and YaST makes it > very comfortable for me to install various SW-packages. Therefore I try to > install also MySQL with YaST. > > There are MySQL packages (Version 4.0.15) included in SuSE Linux 9.0 > distribution and I try to install the server and the client with YaST. > > But after installation of these packages with YaST - which is the easy part > - one has to configure various parameters for MySQL to work properly e.g. > create the database files, define group and user, grant access ... > Unfortunately the MySQL-manual (which is a big document) does not give any > guidelines for installing with YaST. > > After several times re- and de-installing mysql and also SuSE-Linux itself I > still have troubles to get the server running. > > Has anyone already done a MySQL-installation with SuSE Linux 9.0 and YaST? > Is there any "quick installation guide" for this task? > > I would be very happy to get some help. > > Franz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql to LaTeX tables
On Mon, 1 Dec 2003, Xavier Fernández i Marín wrote:
> Is there any application to export MySQL results of queries to LaTeX
> tables? (preferably under GPL)
Xavier,
I have a hack for one of my applications. You can use this as a starting
point, but you will have to adjust directories. It converts to TeX (not
LaTeX) but prints very fancy tables (within my scope). Note: it has to fit
on one page with (using landscape mode). There is a limit on the numer of
columns which can be printed, depending on what data they contain.
Beware: this script can be dead slow. Someone should recode this in perl.
Thomas Spahni
--
filter: MySQL, Query
Shell script:
#!/bin/bash
# printtable
#
# usage: printtable []
#
# use a select query to generate desired
# example:
# echo "SELECT * FROM mytable;" | mysql --batch mydbase > filename
#
DB_VERSION='0.9.20 of 2002-01-07'
PROG_VERSION='1.0.0'
#
# print nice listings from tab delimited table data
#
## --
## This program is free software; you can redistribute it and/or
## modify it under the terms of the GNU General Public License
## as published by the Free Software Foundation; either version 2
## of the License, or (at your option) any later version.
##
## This program is distributed in the hope that it will be useful,
## but WITHOUT ANY WARRANTY; without even the implied warranty of
## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
## GNU General Public License for more details.
##
## You should have received a copy of the GNU General Public License
## along with this program; if not, write to the Free Software
## Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
## --
#
# In a first run everything is deleted except delimiting TABs
# and these TABs are converted to "X"s;
# Then sort rows and eliminate duplicates;
# One single line should remain, otherwise the
# dataset is inconsistent.
# For the time we do not handle escaped TAB's within data
# or TAB's within quoted datafields ;-((
# They appear as \t and will be treated as a space.
# Section 1
# some user definable values
# how to print
# get a default; use global variable if set
# adjust argument to PRINTOPT as desired
if test -n "$PRINTER" ; then
PRINTOPT="-P$PRINTER"
else
PRINTOPT="-Plp"
fi
# Temporary space; must be writeable for all users!
TMPDIR=/tmp
# A place to keep user's output and a personal copy
# of listing.hyph
PRINT_DIR=~/.myprinttable
# Where the TeX templates can be found
# This is possibly changed by make during installation
TEMPLATES=/var/tmp/db
# printtable will load macros for some multilanguage characters
# by default and a package named german.sty.
# Make sure that the required hyphenation tables are loaded.
TEXPROG=tex
# Style file to process national language
TEXSTYLE=german.sty
# Space distribution mode;
# If it doesn't fit otherwise this may be set to "tight"
#SPACEMODE="normal"
SPACEMODE="narrow"
#SPACEMODE="tight"
# Turning this on will print some internal data on the screen
# Values: on / off
DEBUG=on
# - END of user configuration
if test "$1" = -h -o "$1" = --help -o "$1" = "?" -o "$1" = "-?" \
-o "$1" = -V -o "$1" = -v -o "$1" = --version ; then
echo "printtable Version $PROG_VERSION"
echo "Print pretty formatted tables from TAB delimited data"
echo ""
echo "usage: printtable -h | --help | ? | -? | -V | -v | --version"
echo " printtable [\"Title for the table\"]"
exit 0
fi
# Section 2 Check for required files/directories
# test for temporary space
if ! test -w "$TMPDIR" ; then
echo "[$0] ERROR: can not write to tmp dir ${TMPDIR}; giving up"
exit 1
fi
# If this user has no private print directory it is created
# with permissions restricted to the user. Then writability
# is tested for.
if ! test -d "$PRINT_DIR" ; then
mkdir -p -m 0700 "$PRINT_DIR"
fi
if ! test -w "$PRINT_DIR" ; then
echo "[$0] ERROR: can not write to personal dir ${$PRINT_DIR}; giving up"
exit 1
fi
# Having a running TeX installation is a must
# let's check for it
if ! $TEXPROG --version >/dev/null 2>&1 ; then
echo "[$0] ERROR: no executable $TEXPROG found; giving up"
exit 1
fi
# See if there is the TeX style file at the right place
if ! test -r "${PRINT_DIR}/$TEXSTYLE" ; then
if test -r "${TEMPLATES}/$TEXSTYLE" ; then
cp "${TEMPLATES}/$TEXSTYLE" "${PRINT_DIR}/$TEX
Re: Escaping single quotes
Matthew,
I really don't understand the question. Apostrophes must be properly
escaped when text is inserted into the MySQL db, but any perl script will
easily do this for you. You may convert to HTML at the same time.
If the database gives nothing but a path to a *.txt source then your HTML
code should invoke a cgi script which in turn pulls a pathname from the
database, gets the data from the file, converts it to HTML and returns it
to the httpd.
Sorry, but I can't be more specific
Thomas Spahni
On Mon, 1 Dec 2003, Matthew Stuart wrote:
> I am going to take over an existing website and in its present format
> it is a site powered by an Oracle DB. I will be migrating to MySQL.
>
> The site is a news based site and has the use of the single quote or
> apostrophe (') through most of it's articles. I think that each article
> at present is an external .txt file that is pulled in to Oracle. If I
> carried on this method of having an external .txt file would that over
> come the necessity to escape (\') every single quote in each article?
>
> If so, how might I be able to pull that data through so that it loads
> into the web browser. I asume it is some sort of http:// based link as
> it would be for an image. Is there any special kind of formatting I
> have to do to the text file for it to show as html?
>
> TIA
> Mat
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: About maxdb
On Fri, 28 Nov 2003, Lemasson Sylvain wrote: > May be this not the good mailing list. I have recently installed maxdb. > I have create succefully a database instance but when I try to launch it > (admin or onlyne mode) I have a runtime environment error saying I must > have a look to the application event log. Where could I found this > apllication event log. Sylvain, there should be an error log in your datadir. Use 'mysqladmin variables' to find out where MySQL keeps its data. Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: "Status" shows wrong version number for server after upgrade
Mark, what's the output of shell> mysqladmin version It appears that you still have a version of 4.0.4-beta running. Try shell> find /usr /sbin /bin -name mysqld to see whether there is more than one copy of mysqld hanging around. Regards, Thomas Spahni On Wed, 19 Nov 2003, Mark Marshall wrote: > I just compiled 4.0.16 and installed it over top of 4.0.4 beta. > > After stopping and restarting mysqld_safe, I went into mysql and issued > a "status" command. I got back the following: > > mysql> status > -- > mysql Ver 12.22 Distrib 4.0.16, for pc-linux (i686) > > Connection id: 35 > Current database: > Current user: [EMAIL PROTECTED] > SSL:Not in use > Current pager: stdout > Using outfile: '' > Server version: 4.0.4-beta-log > Protocol version: 10 > Connection: Localhost via UNIX socket > Client characterset:latin1 > Server characterset:latin1 > UNIX socket:/tmp/mysql.sock > Uptime: 40 min 17 sec > > Threads: 11 Questions: 1923 Slow queries: 0 Opens: 22 Flush tables: > 1 Open > tables: 16 Queries per second avg: 0.796 > -- > > What gives? Is there something else I need to do to update the > server's version? Everything I see looks like I'm running the > executable that I just compiled this afternoon. > > Thanks, > Mark > > > As of November 1st, 2003, Brandywine Senior Care's Corporate Office new contact > information is: > > Brandywine Senior Care, Inc. > 525 Fellowship Road > Suite 360 > Mt. Laurel, NJ 08054 > (856) 813-2000 Phone > (856) 813-2020 Fax > > ** > This e-mail and any of its attachments may contain Brandywine Senior Care, Inc. > proprietary information, which is privileged, confidential, or subject to copyright > belonging to Brandywine Senior Care, Inc. > This e-mail is intended solely for the use of the individual or entity to which it > is addressed. If you are not the intended recipient of this e-mail, you are hereby > notified that any dissemination, distribution, copying, or action taken in relation > to the contents of and attachments to this e-mail is strictly prohibited and may be > unlawful. If you have received this e-mail in error, please notify the sender > immediately and permanently delete the original and any copy of this e-mail and any > printout. Thank You. > ** > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Help Upgrading From 4.x to 4.x
On Wed, 19 Nov 2003, Mark Marshall wrote: > Hi, everyone. > > I have a 4.0.4 beta install of Mysql on Red Hat 7.3. I want to upgrade > it to 4.0.16, and keep all the data intact. Do I just dump the > databases (just in case), stop the server, then "./configure, make, make > install" over top of the old server and start it up again and see what > happens? exactly. The dump is a good idea. Make sure that you compile with the same options to configure as your 4.0.4 build (everything should go to the same directory as it was before). This used to be a problem with SuSE distributions when installing over an old prm installation, because they used to have a different directory layout. I can't tell you how RedHat did this. Thomas Spahni > Thanks, > Mark > > > As of November 1st, 2003, Brandywine Senior Care's Corporate Office new contact > information is: > > Brandywine Senior Care, Inc. > 525 Fellowship Road > Suite 360 > Mt. Laurel, NJ 08054 > (856) 813-2000 Phone > (856) 813-2020 Fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WANTED: lots of data
Mark, I have not that many records but I can offer you ~29 million rows of wordwide meteorological data. They can be bzip2'd to 300 MB and are very tricky to summarize in a meaningful way. If you're interested pls mail me off-line. Regards, Thomas Spahni On Sat, 8 Nov 2003, Mark Horton wrote: > I'm working on some software that will generate all sorts of statistics > on large volumes of data. I'm looking for data to experiment on. I'd > like 100 million - 1 billion records. These records can be anything > from log files to database records, or whatever. I just need tons of data. > > If you have lots of data and have been interested in getting it > summarized, rolled-up, aggregated so that you can have nice summary > reports on it then let me know. > > BTW, I'm not trying to sell anything. This software is just an idea and > I'm looking for lots of real world data to test with. > > Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select query syntax help
Dan, SELECT ResourceTable.* FROM ResourceTable LEFT JOIN ResourceLinkTable ON ResourceTable.ResourceID = ResourceLinkTable.ResourceID WHERE ResourceLinkTable.ResourceID IS NULL; Regards, Thomas On Thu, 30 Oct 2003, Dan Lamb wrote: > Hello All, > > I have two table the look like this (greatly simplified): > > ResourceTable > - > int ResourceID > var ResourceName > > ResourceLinkTable > - > int ResourceLinkID > int ResourceID > var Text > > I need to find all rows in ResourceTable for which there is NO entry in > ResourceLinkTable. I know I could do this with sub-selects like this: > > Select * from ResourceTable where ResourceID not in (select distinct > ResourceID from ResourceLinkTable) > > How can I do this in MySQL 4.0 without using sub-selects? > > Thanks, > Dan Lamb > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: I need the opposite of BINARY
Simon you missed this from the manual: If you want to convert a number to a string explicitly, pass it as the argument to `CONCAT()'. If a string function is given a binary string as an argument, the resulting string is also a binary string. A number converted to a string is treated as a binary string. This only affects comparisons. Normally, if any expression in a string comparison is case-sensitive, the comparison is performed in case-sensitive fashion. It is well documented. My question was about how I could change this, because I want to perform a comparison in a NON-case-sensitive fashion. Thomas Spahni On Mon, 20 Oct 2003, Simon Green wrote: > CONCAT turns every this in to a string then puts them together? > LIKE is not case sensitive with string? > When is this turned in to BINARY? > > What have I missed please > Simon > > -Original Message- > From: Thomas Spahni [mailto:[EMAIL PROTECTED] > Sent: 20 October 2003 15:38 > To: [EMAIL PROTECTED] > Subject: I need the opposite of BINARY > > > Hi, > > I do the following: > > ... WHERE CONCAT(anumber, aname) LIKE '12SomeString' > > As explained in the manual this is treated as a BINARY comparison i.e. > case of the letters matter. I need a case independent comparison here. Is > there a way to get the usual behaviour of LIKE in this case? (besides > translating all characters to LOWER which is IMHO no elegant solution). > > TIA > Thomas Spahni > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I need the opposite of BINARY
Hi, I do the following: ... WHERE CONCAT(anumber, aname) LIKE '12SomeString' As explained in the manual this is treated as a BINARY comparison i.e. case of the letters matter. I need a case independent comparison here. Is there a way to get the usual behaviour of LIKE in this case? (besides translating all characters to LOWER which is IMHO no elegant solution). TIA Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb and fulltext
I remember that some weeks ago Heikki announced it is on his todo list but in the far future unless someone is funding the project. Thomas Spahni On Thu, 4 Sep 2003, electroteque wrote: > Hi i was wondering if there was ever going to be a time when Innodb can also > be fulltext indexable ? Being that i just started to work with Innodb and > cant believe how proper it feels of a relationional database over Myisam. > Like with Myisiam you cant set relationships up or is that going to change ? > As there has been a few projects which needs fulltext but would be good > setting up innodb aswell. > > Also when setting up relationships if i setup on delete to use cascade it > will delete all records from the other table joined to that row, this is a > wicked feature although, how can i still stop it from being deleted > accidently then ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max number of databases
--> might be equal to the max number of directories you file system supports. Thomas Spahni On Tue, 2 Sep 2003, Keith Schuster wrote: > Is there a max number of databases that will run under mysql > --- > Keith Schuster > Schuster & Company LLC > ph:704-799-2438 > fx:704-799-0779 > iChat/AIM:FSHSales > > WWW.FlagShipHosting.com > WWW.Schusterandcompany.com > WWW.Vsheet.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lots of FULLTEXT stuff (suggestions)
Matt,
I fully agree that indexing short words and numbers is a necessity
sometimes. I'm processing legal text where abbreviations are widely used
and people want to search for chunks like:
Art. 234 Abs. 3 OR
and the search should also find occurrances of
Art. 234 OR
These are so common that I risk to run into the 50% cutoff unless using
BOOLEAN MODE. Indexing numbers is top of my wish list.
I observed user's queries for some time now and found that the ranking of
the results is optimal (i.e. match the user's expectations) when the words
he typed occur close together in the text (but not necessarily close to
the top).
Regards,
Thomas Spahni
On Sun, 24 Aug 2003, Matt W wrote:
> Hi all,
>
> I'm planning to use MySQL's full-text search for my forum system
> (possibly 5+ million posts). I've been playing with it a lot lately to
> see the performance and functionality and have some
> suggestions/questions.
>
> First, since a few of you may be wanting to know, here is a thread where
> I was doing some speed/optimization tests and stuff with 3 million
> posts: http://www.sitepointforums.com/showthread.php?threadid=69555
> (From post #12)
>
> Especially discovered that IN BOOLEAN MODE is really slow if you want to
> sort by relevance (with a lot of matching rows anyway). :-( For
> non-BOOLEAN searches, though, I can get 1000 relevance-sorted results in
> about 8-10 secs. for searches that match a LOT of rows and everything
> has to be read from disk. The full-text processing seems to be very fast
> (max 1-2 seconds of "FULLTEXT initialization" in PROCESSLIST). It's the
> disk seeks to read random rows from the data file ("Sending data") that
> take the most time (7200 RPM/~8ms seek IDE drive). Searches are *MUCH*
> faster when the needed parts of the data file are cached by the OS!
>
> Anyway, my suggestions:
>
> --
> *) Min/Max Word Length -- This should really be able to be set on at
> least a per table basis (others may want per index). Right now, people
> that don't have control of the server are at the mercy of the admin to
> change the min/max word length.
>
> I would also suggest that ft_min_word_len be 3 and ft_max_word_len be 32
> by default. I think these would be better defaults for everyone than the
> current 4/254.
>
> Or if we could use
>
> SET ft_min_word_len=n;
>
> etc. for the current connection it would be nice.
>
>
> *) Parser: Indexing of Any and All Numbers -- I think it would be a good
> idea to index any sequence of digits less than ft_min_word_len long.
> Anything numeric could be very relevant for searching -- software
> versions, ages, dates, etc. -- and shouldn't be excluded.
>
> Even anything *containing* a number (among letters) is probably relevant
> for searching, again, even if it's shorter than ft_min_word_len. e.g.
> RC1, B2, 8oz, F5, etc.
>
>
> *) Parser: Other Things -- I've seen people trying to search
> catalog/item/part numbers with "pieces" of the "number" separated by -
> or / for example (making some "pieces" too short). How about indexing
> words that are on either side of a "-" or "/" (with no space) no matter
> their length? I don't mean including the - or / in the index -- just the
> usual word characters on either side (I think) as *separate* words, not
> a *single* word with the - or / removed. This would help with things
> like CD-ROM, TCP/IP, etc.
>
> Single quotes being counted as a word character is another issue I have.
> (I discovered that they're not counted as part of the word when on the
> end(s): 'quote' (thank God! :-))) Example: if someone searches for
> MySQL, it won't find rows with MySQL's. Since possessive's (sic) are the
> biggest problem, how about stripping any 's from the end of the word in
> the index? So MySQL's would be indexed as MySQL.
>
>
> *) "Always Index" Words -- Like it says in the full-text TODO section of
> the manual. This should be able to be set on at least a per table basis
> (again, others may want per index).
>
>
> *) Stopword File -- I would also like to be able to define this per
> table somehow.
>
>
> *) Miscellaneous -- Mostly functionality related, from the TODO:
> STEMMING! (controlled more finely than server level I hope), multi-byte
> character set support, proximity operators. Anything to get it closer to
> Verity's full-text functionality. ;-)
>
> Any speed/optimization improvements are welcome for gigs of data,
> especially with IN BOOLEAN MODE (e.g. automagically sorted by relevance
> like a natu
Re: Please HELP Romanian charset Collate in MySQL
Iulian, this is in the manual. Look at Database Administration Localisation Character arrays Regards, Thomas Spahni On Wed, 30 Jul 2003, Primaria Falticeni wrote: > I can copy the latin2.conf into new one, but I need to know what each code > from the conf file represents and how can I obtain the relation from these > codes and the chars.So that's two problems: > 1) Can I use asc function or what function I need to know to obtain the > codes from the chars? > 2) How is the way to arrange the codes in the conf file? Assuming that I > know the codes for each characters (found at the above point) how can I put > these codes in the three tables within the latin2.conf file? > > - Original Message - > From: "Primaria Falticeni" <[EMAIL PROTECTED]> > To: "MySQL LIST" <[EMAIL PROTECTED]> > Sent: Monday, July 28, 2003 10:00 PM > Subject: Please HELP Romanian charset Collate in MySQL > > > > Hello, > > > > Simply I made a table with romanian characters "aAîÎâÂsStT". I tried to > > sort > > it, in fact to order it in a query. > > > > I settled the default_charset to some charsets: > > latin1(default)(latin2 win1250(central european)) > > ÎteIbur > > Ibur Îte > > ItoIto > > > > The correct result must be: Ibur, Ito, Îte in ascending order. > > > > How can I do this? Please give me an example at how can I change the > > latin2.conf in a romanian one to fairly sort the chars for me. I must do > > change the behaviour and I don't know how. > > > > Thanks Anticipated, > > > > Iulian Teodosiu > > Economist/Analyst Programmer > > Primaria Falticeni > > Falticeni (town), jud. Suceava > > Romania, Europe > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How To Perfrom Query on Two Tables
Hi, join two tables in your select. Might look like insert into mm_tagrules select ID, 'NIL', 'D' from mm_Tag left join mm_tagrules on mm_Tag.ID = mm_tagrules.Tagid where mm_tagrules.Tagid IS NULL; Regards, Thomas Spahni On Tue, 29 Jul 2003, jsmurthy wrote: > Hello All, > > I need to insert into mm_tagrules table from mm_tag table, in the > following way. I am using mysql 4.0.12 version. But Mysql is not supporting > subqueries. Can any one help mysql query for the following query. > > insert into mm_tagrules > select ID, 'NIL', 'D' from mm_Tag where mm_Tag.ID not in (select Tagid from > mm_tagrules) > > Thanks in Advance > Regards > J.S.Murthy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please URGENTLY Romanian charset Collate in MySQL
Iulian, take the source code, go to 'sql/share/charsets' and create your own character set 'romanian.conf' departing from 'latin2.conf' (or whatever is closest to what you need). Regards, Thomas Spahni On Mon, 28 Jul 2003, Primaria Falticeni wrote: > Hello, > > Simply I made a table with romanian characters "aAîÎâÂsStT". I tried to sort > it, in fact to order it in a query. > > I settled the default_charset to some charsets: > latin1(default)(latin2(iso-8859-2 I think) the same like > win1250(central european)) > SteSbur > Sbur Ste > StoSto > > The correct result must be: Sbur, Sto, Ste in ascending order. > > How can I do this? Please give me an example at how can I change the > latin2.conf in a romanian one to fairly sort the chars for me. I must do > change the behaviour and I don't know how. > > Thanks Anticipated, > > Iulian Teodosiu > Economis/Analyst Programmer > Primaria Falticeni > Falticeni (town), jud. Suceava > Romania, Europe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0.14: Manual
Hi,
in some strange way the version number did not propagate into the
manual for version 4.0.14. It starts like this:
This is the Reference Manual for the `MySQL Database System'. This
version refers to the {No value for `mysqlversion'} version of `MySQL
Server' but it is also applicable for ...
Regards,
Thomas Spahni
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Image files
Hi, you have to escape your binary data; check mysql_real_escape_string() function in the manual and the archives. This is a frequently asked question. Thomas Spahni On 14 Jul 2003, Sreesekhar Palaparthy wrote: > Hi, > How do i insert binary data into a BLOB field??? Like , if i > have a table with say 3 fields of which 1 is blob type, now how do > i store some binary file in that particular field??? Please help > me out as i have to look put for some corresponding function in > C++ API after that.Thank You > ___ > Click below to experience Sooraj R Barjatya's latest offering > 'Main Prem Ki Diwani Hoon' starring Hrithik, Abhishek > & Kareena http://www.mpkdh.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query output get wrapped
Asif, try this: prompt> echo "select * from Tickets limit 1;" | mysql mydb > result.file You get everything in ONE line. Cheers, Thomas On Tue, 3 Jun 2003, Asif Iqbal wrote: > > mysql> select * from Tickets limit 1; > ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ > | id | EffectiveId | Queue | Type | IssueStatement | Resolution | Owner > | Subject | InitialPriority | FinalPriority | Priority | Status | > TimeWorked | TimeLeft | Told| Starts | Started | Due > | Resolved | LastUpdatedBy | LastUpdated | Creator | Created > | Disabled | > ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ > | 5 | 5 | 4 | ticket | NULL | NULL |16 > | RE: phonebook | 10 |80 | 10 | resolved | > 0 | NULL | 1970-01-01 00:00:00 | NULL | NULL| 1970-01-01 > 00:00:00 | NULL | 1 | 2001-04-17 18:38:02 | 1 | > 2001-04-17 18:26:46 |0 | > ++-+---++++---+---+-+---+--+--++--+-++-+-+--+---+-+-+-+--+ > 1 row in set (0.00 sec) > > Is there a way I can get the output unwrapped > > something like this > > mysql> select * from Tickets limit 1 \G > *** 1. row *** > id: 5 > EffectiveId: 5 > Queue: 4 >Type: ticket > IssueStatement: NULL > Resolution: NULL > Owner: 16 > Subject: RE: phonebook > InitialPriority: 10 > FinalPriority: 80 >Priority: 10 > Status: resolved > TimeWorked: 0 >TimeLeft: NULL >Told: 1970-01-01 00:00:00 > Starts: NULL > Started: NULL > Due: 1970-01-01 00:00:00 >Resolved: NULL > LastUpdatedBy: 1 > LastUpdated: 2001-04-17 18:38:02 > Creator: 1 > Created: 2001-04-17 18:26:46 >Disabled: 0 > 1 row in set (0.00 sec) > > Except I want it Horizontally > > Thanks > > Asif > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blobbing data on MySQL
Delz, what's the reason to put *.doc into a database? Format of *doc is a PITA and you can't search it. Store the path to the file instead along with a copy of it's content in plain ASCII and run a fulltext index on that. Have a look at wvWare to do the conversion. Thomas Spahni On Fri, 4 Apr 2003, delz wrote: > Hi All, > > Good day !!! > > I'm trying to setup up a mysql server that will store scanned documents > (.doc, gif, jpeg, etc..) and will try to achieve this by blobbing data > into the mysql server. I find this to be very useful for archiving old piles > of paper and scanning them before they worn out. I also need to create a > database for it of course, that will store the scanned documents into the > mysql server. This of course needs a lot of disk space on the hard drive of > the server specially if you have tons of documents to scan. Blobbing data > into the mysql server will obviously consume lots of disk space and if it > turns to be that way, I need another hard disk to store the blob data. Using > the rpms to install mysql the path where the database are installed is at > /var/lib/mysql, if I install it via tarball I would install it at > /usr/local/mysql, my question is how do I make a path for my blob data into > another hard disk in case the primary hard disk gets full so that the data > will be saved on another hard drive. I would want to achieve this kind of > setup for me to have more options rather than setting up another server to > store the data. I would appreciate if someone could help me on this. > > Regards, > > Delz > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: retry: Searching for values such as "10.2.4" via fulltext (more info)
Adam,
this will not work with fulltext. Non-alphanumeric characters will be
ignored and treated as spaces. There remain some chunks like 10 2 4 and
these are too short (less than 3 characters) and will never be indexed.
Sorry, no way. Except for an ugly hack. You may try to replace dots
between numbers in your data and in your query with something else. But
your client application has to take care of this.
Let's have a look at an example abusing character with ASCII 254 decimal:
> create table ft ( r1 varchar(255), fulltext ftindex (r1)) type=MyISAM;
> insert into ft values('some data 10þ4þ6'),('today 2003þ3þ4'),('on mysql
version 4þ0þ12');
> select * from ft where match(r1) against('10þ4þ6');
yields:
*** 1. row ***
r1: some data 10þ4þ6
1 row in set (0.01 sec)
and:
> select * from ft where match(r1) against('4þ0þ*' in boolean mode);
yields:
*** 1. row ***
r1: on mysql version 4þ0þ12
1 row in set (0.00 sec)
and its up to your client application to convert those 'þ' char(FE) back
to '.'
happy hacking,
Thomas Spahni
On Thu, 3 Apr 2003, Adam Randall wrote:
> Since no one responded to this message at all, I'm sending it again.
>
> I have a support system set up where people can search for various
> things using a fulltext index. The problem that I am having, though,
> is that I cannot seem to figure out how to get search results for
> values like "10.2.4" or "6.0.4". This is with MySQL 4.0.12, with the
> minimum word length of 3 (so we can search for PHP, IIS, etc).
>
> Here are some examples of how the search is being performed:
>
> select id,topic from articles where match(topic,summary,description)
> against ('10.2.4');
> (no results)
>
> select id,topic from articles where match(topic,summary,description)
> against ('"10.2.4"' in boolean mode);
> (no results)
>
> select id,topic from articles where topic like '%10.2.4%' or summary
> like '%10.2.4%' or description like '%10.2.4%';
> (3 rows found)
>
> I don't really want to do the last search because it's the slowest,
> and searching the most data un-indexed.
>
> Anyway, if anyone has any advice, I would appreciate it.
>
> Adam.
>
> ---
> Adam Randall http://www.xaren.net/
> [EMAIL PROTECTED] http://nt.xaren.net/
> [EMAIL PROTECTED]AIM/iChat: blitz574
>
> "Macintosh users are a special case. They care passionately about the
> Mac OS and would rewire their own bodies to run on Mac OS X if such a
> thing were possible." -- Peter H. Lewis
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext search -- no wildcards in phrases?
On Wed, 2 Apr 2003, Shamit Verma wrote: > The replay on the webpage says that: > "Nope. That would be a really slow search since mysql cant use any > indexes and a table scan would be the only way to find it." > > Then even "LIKE" operator should suffer from the same performance drawback, > how does LIKE operator work with indexes? exactly. LIKE '%something' will not use indexes but LIKE 'some%' will. Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext search
Christian, While fulltext had some problems with latin1 charset and German language it worked quite well for me with latin1_de and a customized German stopword list. BTW I create my stopword list from the texts to be indexed. All words are sorted by frequency and then common words from the top of this list (example: doch weil aber dennoch) are added to the stopword list. ft_min_word_len is set to 3. What exactly are the problems you are seeing? Have a nice day Thomas Spahni On Thu, 27 Mar 2003, Christian Jaeger wrote: > At 22:26 Uhr -0600 25.03.2003, mos wrote: > >How many people out there are willing to pay $$$ to see it done?? > >Please reply to this thread to see if there is a general interest > >and how much it is worth to you. > > IIRC, last time I looked, fulltext was not very good for i.e. the > german language. If there would be some hooks (on C level, like > UDF's) for adjusting the tokenizer(?), I could probably improve that > quite easily myself. (Alternatively, documentation of the relevant > code parts would help as well, so I don't spend much time trying to > understand it). > > So if it either is going to be useful for german or provide > hooks/documentation for adaptation, I'll pay as well (either myself > or by a customer). > > Christian. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.12 rpm's
Paul, ??? I see Linux x86 RPM downloads under http://www.mysql.com/downloads/mysql-4.0.html Regards, Thomas On Wed, 26 Mar 2003, Paul wrote: > This may indicate incredible ignorance but all I can find there are > tar.gz files which untar into the complete distribution but no rpm's are > present. > > Thomas Spahni wrote: > > On Wed, 26 Mar 2003, Paul wrote: > > > > > >>Can anyone tell me where I can get 4.0.12 server and client rpm's? > > > > > > www.mysql.com > > > > Regards, > > Thomas > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.12 rpm's
On Wed, 26 Mar 2003, Paul wrote: > Can anyone tell me where I can get 4.0.12 server and client rpm's? www.mysql.com Regards, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Creating fulltext index never completes...?
Nick, i am processing text in German. Uppper characters from the iso-8859-1 character set are used and never caused a problem. You should have a look at your variable settings; make myisam_sort_buffer_size as large as you can afford and set myisam_max_extra_sort_file_size and myisam_max_sort_file_size to a size appropriate for the size of your table. Double check that you have enough disk space. In my case I could reduce the time needed to create a fulltext index from 18 hrs to a few minutes. Thomas Spahni On Sun, 23 Mar 2003, Nick Arnett wrote: > > -Original Message- > > From: Nick Arnett [mailto:[EMAIL PROTECTED] > > ... > > > It dawned on me that perhaps the problem had to do with double-byte > > characters, > > Actually, what I think I meant to say was "unprintable" characters with > ASCII > 122. Does this break fulltext indexing? > > Nick > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.12 startup problem InnoDB related
Benjamin, you are right: make distclean did the trick Best regards, Thomas On Thu, 20 Mar 2003, Benjamin Pflugmann wrote: > On Thu 2003-03-20 at 15:59:29 +0100, [EMAIL PROTECTED] wrote: > > Hi, > > > > I was upgrading from 4.0.10 to 4.0.12 when a strange thing happened. I > > compiled from source on SuSE-8.0 (gcc version 2.95.3, kernel 2.4.18-4GB) > > and installed and everything was fine. However I noticed that I had > > debugging compiled in. > > > > I went back, took out --with-debug from my configure options and > > recompiled. The resulting mysqld did no longer start up but quit with the > > following message in error.log: > > > > 030320 09:02:00 mysqld started > > InnoDB: Error: trx_t size is 416 in ha_innodb.cc but 456 in srv0start.c > > InnoDB: Check that pthread_mutex_t is defined in the same way in these > > InnoDB: compilation modules. Cannot continue. > > 030320 9:02:00 Can't init databases > > 030320 9:02:00 Aborting > > > > How strange; I deleted all InnoDB files in the data directory. Same > > result. Then I recompiled again adding the option --with-debug and it > > started ok creating all necessary files. > > > > What is wrong here? > > Such a config change requires to do a full recompile (make distclean > or whatever), which you apparently did not. > > HTH, > > Benjamin. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
4.0.12 startup problem InnoDB related
Hi,
I was upgrading from 4.0.10 to 4.0.12 when a strange thing happened. I
compiled from source on SuSE-8.0 (gcc version 2.95.3, kernel 2.4.18-4GB)
and installed and everything was fine. However I noticed that I had
debugging compiled in.
I went back, took out --with-debug from my configure options and
recompiled. The resulting mysqld did no longer start up but quit with the
following message in error.log:
030320 09:02:00 mysqld started
InnoDB: Error: trx_t size is 416 in ha_innodb.cc but 456 in srv0start.c
InnoDB: Check that pthread_mutex_t is defined in the same way in these
InnoDB: compilation modules. Cannot continue.
030320 9:02:00 Can't init databases
030320 9:02:00 Aborting
How strange; I deleted all InnoDB files in the data directory. Same
result. Then I recompiled again adding the option --with-debug and it
started ok creating all necessary files.
What is wrong here?
TIA
Thomas Spahni
--
sql,query
-
int a=1,b,c=2800,d,e,f[2801],g;main(){for(;b-c;)f[b++]=a/5;
for(;d=0,g=c*2;c-=14,printf("%.4d",e+d/a),e=d%a)for(b=c;d+=f[b]*a,
f[b]=d%--g,d/=g--,--b;d*=b);}
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: latin1_de with index
On Thu, 6 Mar 2003, Fabian Schmidt wrote:
> Am 05.03.03 schrieb Thomas Spahni:
>
> > Some words like "Tetraeder" have the combination 'ae' and should be found
> > looking for LIKE '%ae%'; surprisingly this works:
>
> Queries with "LIKE '%...'" can't use the index and work correct.
> Unfortunately this is no option for big tables.
you are right, but LIKE 'ae%' does use the index and works.
mysql> explain select * from test where word like 'ae%';
*** 1. row ***
table: test
type: range
possible_keys: word
key: word
key_len: 255
ref: NULL
rows: 2
Extra: Using where; Using index
Ciao
Thomas Spahni
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: latin1_de with index
On Tue, 4 Mar 2003, Fabian Schmidt wrote:
> >Description:
> A select returns uncomplete results, when querying an indexed
> varchar row with latin1_de character set and strings containing umlauts
> or 'ß'.
>
> >How-To-Repeat:
>
> start MySQL 4.0 with character set latin1_de.
>
> create table test (word varchar(255) not null default '', index(word));
> insert into test values ('ss'),('ß'),('ä'),('ae');
> select * from test where word='ss';
> +--+
> | ss |
> | ß|
> +--+
> -> looks fine.
>
> select * from test where word='ß';
> +--+
> | ss |
> +--+
I would expect that it returns 'ß' as a result as well.
> select * from test where word='ä';
> +--+
> | ae |
> +--+
> -> is what you least expect.
I can reproduce this on 4.0.10-gamma-debug-log
> To get even more confused, the result is changing:
>
> select * from test where word like 'ae';
> -- ae
> select * from test where word='ä';
> -- ae, ä
> select * from test where word='ae';
> -- ae, ä
> select * from test where word='ä';
> -- ae
I could not reproduce this behaviour on 4.0.10; got both results;
however, when ä and ae are equivalent, is this really a problem? Some
texts do substitute ae for ä and I want to have a hit searching for 'ä'.
but look at this:
mysql> select * from test where word like 'ae';
Empty set (0.00 sec)
Some words like "Tetraeder" have the combination 'ae' and should be found
looking for LIKE '%ae%'; surprisingly this works:
mysql> insert into test values ('Tetraeder');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test where word like '%ae%';
*** 1. row ***
word: ae
*** 2. row ***
word: Tetraeder
2 rows in set (0.00 sec)
Whenever there is a joker it works:
mysql> select * from test where word like '%ae';
*** 1. row ***
word: ae
1 row in set (0.00 sec)
mysql> select * from test where word like 'ae%';
*** 1. row ***
word: ae
1 row in set (0.00 sec)
but this does not:
mysql> select * from test where word like 'ae';
Empty set (0.00 sec)
Regards,
Thomas Spahni
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: speeding up mysql client
On Fri, 28 Feb 2003, Sherwin T. Ang wrote: > Well I guess the best way is to make an insert statement that goes like, > > INSERT INTO table values (3,'S',1,6,'2002-07-15','The Palm Computing > Device',1),(2,'S',6,6,'2002-07-18','Programming for the > Palm',1),(4,'S',5,6,'2002-07-1 > 6','Medical Applications for the Palm Device',1); > > The above query will connect once and insert all 3 records, The method is > called extended insert, and is a much faster method of insert based on the > mysqldump man pages when restoring data back to the database. I > use --extended-insert in my sql dumps. unfortunately this is not an option in my case. Raw data contains certain errors and this will trigger an error upon insertion, which can be dealt with by the script when I do it 'one at a time'. My question was "what is the fastest way to start the mysql client". Regards, Thomas Spahni -- sql, query > am not sure if LOAD DATA INFILE is much faster, well on raw data i guess it > is. > > Respectfully yours, > > Sherwin T. Ang > Systems Administrator > Tridel Technologies Incorporated > http://www.tridel.net > > > - Original Message - > From: "Thomas Spahni" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Friday, February 28, 2003 8:37 PM > Subject: speeding up mysql client > > > > Hi everyone, > > > > I'm calling the mysql client from a bash shell script in a loop. It's > > doing one INSERT query each time. > > > > I'm aware that there is some overhead in this procedure, because the > > client has to connect to the server each time. > > > > Question: what are the recommended options to make this as fast as > > possible? I'm already using --disable-auto-rehash, but what other > > options could help? > > > > Thomas Spahni - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
speeding up mysql client
Hi everyone, I'm calling the mysql client from a bash shell script in a loop. It's doing one INSERT query each time. I'm aware that there is some overhead in this procedure, because the client has to connect to the server each time. Question: what are the recommended options to make this as fast as possible? I'm already using --disable-auto-rehash, but what other options could help? Thomas Spahni - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with registers import
Paulino, do you have any auto_increment index of type SMALLINT ??? .. and what's in the error.log ? Thomas Spahni (sql, query) On Thu, 27 Feb 2003, Paulino Michelazzo wrote: > People > > I have a txt file with 250.000 lines but, I'm import only 32767 lines. > The database don't say anything (errors) > > I'm using MySQL 3.23.53 in the Linux Slackware 8 system > > Anyone can help me to solve this? > > Regards > > > Paulino Michelazzo > [EMAIL PROTECTED] > ICQ: 2911392 > > NASA = Need Another Seven Astronauts > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Slow FULLTEXT searches
Jesse
But then something else must be terribly wrong. As long as you are pulling
ten thousands of hits from the server, it may be slow. But when you reduce
the number of results with (let's say) 'LIMIT 100' I expect typical serch
times of 0.02 sec. That's what I see on a comparable machine holding 200
MB of text plus index.
Can you check for the response time on a not so common single word?
Thomas Spahni
(sql, query)
On Thu, 27 Feb 2003, Jesse Sheidlower wrote:
> On Thu, Feb 27, 2003 at 04:45:37PM +0100, Thomas Spahni wrote:
> > Jesse,
> >
> > this is slow because you are SEARCH'ing for 'COUNT(*)' on a word occurring
> > many many times.
> >
> > SELECT * FROM q WHERE MATCH(qt) AGAINST ('computer') LIMIT 100;
> >
> > should be fast. Make sure to use a key_buffer_size as big as you can
> > afford, possibly keeping the whole index in memory.
>
> This may be the answer for why it's _that_ slow for that one
> query, but in general I'm afraid that's not it. I executed
> your above query on my development server (to ensure the cache
> was cleared), which is a somewhat slower machine, and it took
> 2.61 seconds--better than 16, but still problematic.
>
> And when I changed this:
>
> > > mysql> SELECT COUNT(*) FROM q WHERE MATCH(qt)
> > > -> AGAINST ('+"free love" -hippies' IN BOOLEAN MODE);
> > > +--+
> > > | COUNT(*) |
> > > +--+
> > > | 44 |
> > > +--+
> > > 1 row in set (1.71 sec)
>
> to this:
>
> mysql> SELECT * FROM q WHERE MATCH(qt)
> -> AGAINST ('+"free love" -hippies' IN BOOLEAN MODE);
>
> , it took 4.76 seconds--again, on a slower server, but this is
> returning only 44 results.
>
> It's certainly possible, and perhaps likely, that users will
> need to do fulltext searches on extremely common words--more
> common than "computer" in the above example--though limited by
> requirements in other tables not shown here, and it would be
> rather problematic if these searches are going to take over a
> second each.
>
> Jesse Sheidlower
>
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Slow FULLTEXT searches
Jesse,
this is slow because you are SEARCH'ing for 'COUNT(*)' on a word occurring
many many times.
SELECT * FROM q WHERE MATCH(qt) AGAINST ('computer') LIMIT 100;
should be fast. Make sure to use a key_buffer_size as big as you can
afford, possibly keeping the whole index in memory.
Thomas Spahni
(sql, query)
On Thu, 27 Feb 2003, Jesse Sheidlower wrote:
> I'm having a problem with FULLTEXT searches going much more slowly
> than I expect, and need. It seems that this is perfectly straightforward
> so I can't see why it's taking so long; other people on this list have
> been reporting almost instantaneous results from FULLTEXT searches.
>
> I'm using MySQL 4.0.10 on FreeBSD 4.7, on a 1.4 GHz PIII with 1G RAM.
> It's a lightly loaded server most of the time.
>
> The table in question is:
>
> mysql> show create table q\G
> *** 1. row ***
>Table: q
> Create Table: CREATE TABLE `q` (
> `id` int(10) unsigned NOT NULL default '0',
> `cit_id` int(10) unsigned NOT NULL default '0',
> `qt` text,
> `note` text,
> PRIMARY KEY (`id`),
> KEY `cit_id` (`cit_id`),
> FULLTEXT KEY `qt` (`qt`)
> ) TYPE=MyISAM
> 1 row in set (0.00 sec)
>
> There are about 2.3M rows in this table, and it takes up about 400M.
> I did shorten the ft_min_word_length to 2, since I need to search on
> short words.
>
> Here's a sample:
>
> mysql> SELECT COUNT(*) FROM q WHERE MATCH(qt) AGAINST ('computer');
> +--+
> | COUNT(*) |
> +--+
> |11892 |
> +--+
> 1 row in set (16.43 sec)
>
> Boolean searches are also slow:
>
> mysql> SELECT COUNT(*) FROM q WHERE MATCH(qt)
> -> AGAINST ('+"free love" -hippies' IN BOOLEAN MODE);
> +--+
> | COUNT(*) |
> +--+
> | 44 |
> +--+
> 1 row in set (1.71 sec)
>
> I don't get anything useful from EXPLAINs for searches like these:
>
> mysql> EXPLAIN SELECT COUNT(*) FROM q WHERE MATCH(qt)
> -> AGAINST ('+"free love" -hippies' IN BOOLEAN MODE)\G
> *** 1. row ***
> table: q
> type: fulltext
> possible_keys: qt
> key: qt
> key_len: 0
> ref:
> rows: 1
> Extra: Using where
> 1 row in set (0.00 sec)
>
> While a 1.7-second search may not be the end of the world, a 16-second
> search is getting closer to it, and this is just the simplest case. In
> practice, this would be an element of a larger search that's joining in
> a number of other tables, and with a number of concurrent users. Is there
> anything I can do to speed things up, or any explanation of why this is
> so slow?
>
> Thanks very much.
>
> Jesse Sheidlower
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Full Text Index Performance
On Tue, 25 Feb 2003 [EMAIL PROTECTED] wrote:
> I wanted to created a searh engine for our site with indexed content of
> the pages being stored in MYSQL full text index. I wanted to know how
> fulltext index scales if I have about 200,000 pages indexed. Is it wise
> to use MYSQL for this or go with other options like SWISH-e etc.
we have about 2x 200 MB of indexed text data (*.MYD files only)
representing 30'000 documents and our testruns peak at 50 fulltext queries
per second. This is on a Linux box with a single Pentium-4 and 1 GB RAM.
As long you are generous enough with RAM your database may deliver much
more data than your bandwith can transmit unless you have a very good
feed.
Perhaps you provide us with some more details about your setup; otherwise
you can't expect more specific answers.
Thomas Spahni
--
sql, query (filter-food)
int a=1,b,c=2800,d,e,f[2801],g;main(){for(;b-c;)f[b++]=a/5;
for(;d=0,g=c*2;c-=14,printf("%.4d",e+d/a),e=d%a)for(b=c;d+=f[b]*a,
f[b]=d%--g,d/=g--,--b;d*=b);}
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
