Re: InnoDB error 5
What is the best way to backup your database. Which are the files that I need to store on a usb disk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: InnoDB error 5
OOoopppsss! I do mean for recovery/continual backup. I will do it manually, but basically get all the data on a USB disk and be able to recover/move it (the data) on another machine, the same machine etc.. I hope I did not just open up a can of worms. We just went live and this post gave me a rude awakening. What is an effective easy to follow protocol for backup and recovery in mysql! Nick from Toronto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!
On Wed, Aug 21, 2013 at 10:39 PM, h...@tbbs.net wrote: 2013/08/21 18:03 -0400, Nick Khamis We have the following mysql timetampe field startdate | timestamp | NO | | -00-00 00:00:00 When trying to insert a long value in there: Calendar c = Calendar.getInstance(TimeZone.getTimeZone(UTC)); c.getTimeInMillis(); We are presented with the following error: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1377119243640' for column 'stopdate' at row 1 Ugh, where is the SQL? In any case, although it looks as if that is MySQL s internal TIMESTAMP representation, one does not directly use Unix timestamps; instead, one converts them with the MySQL function FROM_UNIXTIME. The same effect may be gotten with any timestamp-formatting function that yields a string in the form '2013/08/21 18:03:00' (it is all one whether the separator is hyphen, slant, colon, ...). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql What I am trying to accomplish is pass down a valid long value (representative of UTC time) that mysql timestamp field accepts, unix time epoch whatever That way, I do not have to fight with java.sql.timestamp or java.sql.Date/Calander (for the love!@E@#!@) for reads and writes. I can't use Joda until it has been included... Deep breaths What I tried is the following: Straight Date: update test set stopdate='2013-08-22T17:49:45'; - Works Fine Formatted Date (long): SimpleDateFormat sdf = new SimpleDateFormat(-MM-dd'T'HH:mm:ss, new Locale(en, US)); long qu = sdf.parse(sdf.format(c.getTime())).getTime(); update test set stopdate='1377194323000'; - Zeros Out Formatted Date (long with milliseconds): c.getTimeInMillis() update test set stopdate='1377195098956'; - Zeros Out Formatted Date (long with milliseconds/1000): c.getTimeInMillis() / 1000 update test set stopdate='1377195098.956'; Can't change the table field to bigint either, it's an already existing project. Someone please help before I fire myself :). Kind Regards, Nick.
Re: Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!
Sorry, as was mentioned earlier: select FROM_UNIXTIME(1377196112065/1000); +---+ | FROM_UNIXTIME(1377196112065/1000) | +---+ | 2013-08-22 18:28:32 | +---+ Have a good day everyone :) Nick.
Java UTC Calendar and Mysql TimeStamp - Gets me every time!!!!!
Hello Everyone, We have the following mysql timetampe field startdate | timestamp | NO | | -00-00 00:00:00 When trying to insert a long value in there: Calendar c = Calendar.getInstance(TimeZone.getTimeZone(UTC)); c.getTimeInMillis(); We are presented with the following error: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1377119243640' for column 'stopdate' at row 1 Our environments is: JDBC Driver = 5.1.26 Mysql = 5.5 show variables like 'time_zone%'; +---++ | Variable_name | Value | +---++ | time_zone | +00:00 | +---++ SELECT @@global.sql_mode; +---+ | @@global.sql_mode | +---+ | | +---+ Not sure why I am getting this error. Thanks in Advance, Nick.
MariaDB
Question, is building the source exactly the same as mysql? Meaning, if I build it with set CMAKE options building mysql, can I use those exact same options building mariab? eg: cmake -DCMAKE_INSTALL_PREFIX=/usr -DINSTALL_INCLUDEDIR=include/mysql -DINSTALL_LIBDIR=lib/mysql -DMYSQL_USER=mysql -DMYSQL_UNIX_ADDR=/var/run/mysql.sock -DMYSQL_DATADIR=/var/lib/sql/data -DWITH_DEBUG=0 -DWITH_SSL=system -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 Thanks Nikki PS, I wish you guys would use a real list server and not that launchpad trash :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: NOW() is stuck...
Just out of curiosity, is the hardware stationed, or traveling close to the speed of light (i.e., 18,000 miles per second)? Sorry I could not help it N. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL 5.6.11 Missing MySQL Client
Hello Everyone, We installed mysql 5.6.11 from source without server, and was wondering what happened to the mysql command? /usr/local/mysql/bin/ innochecksummysql_waitpid replace resolveip my_print_defaults perror resolve_stack_dump Kind Regards, Nick. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: severe build bug 5.5 viossl
yes I'm aware, it is why I made the comment about change
However still subject to corporate policy which means sticking with
mysql until they screw up on regular basis, so if they fail to fix
this by next version, I can take it to CIO and present my case, two
deliberate broken versions when fix made available to them months ago,
should, get me a win to move away from mysql. (perhaps this is what
oracle wants anyway, since mysql earns them no money)
On 6/9/13, Claudio Nanni claudio.na...@gmail.com wrote:
Hi Nick,
It seems it is fixed already in MariaDB:
http://bugs.mysql.com/bug.php?id=68999
[9 Jun 9:34] Michael Widenius
This was fixed in MariaDB 5.5 in May 2013 as part of our merge of
MySQL 5.5 to MariaDB 5.5.
Cheers
Claudio
2013/6/9 Nick Edwards nick.z.edwa...@gmail.com
This was reported in 5.5.31, a patch, VERY SIMPLE was submitted.
The problem goes ignored by oracle
5.5.32 releases, same error, apply the same simple patch and builds
/tmp/mysql-5.5.32/vio/viossl.c: In function 'ssl_do':
/tmp/mysql-5.5.32/vio/viossl.c:175: error: 'SSL_OP_NO_COMPRESSION'
undeclared (first use in this
function)
/tmp/mysql-5.5.32/vio/viossl.c:175: error: (Each undeclared identifier
is reported only once
/tmp/mysql-5.5.32/vio/viossl.c:175: error: for each function it appears
in.)
make[2]: *** [vio/CMakeFiles/vio.dir/viossl.c.o] Error 1
make[1]: *** [vio/CMakeFiles/vio.dir/all] Error 2
Question, does anyone at oracle even bother with bug tracking now days?
How can something that causes a fail of building with versions of
openssl less then 1.0.0
go un fixed for so long.
Is this more proof that oracle DGAF about mysql? should I move to
mariadb?
because if we have to re patch a failed build on 5.5.33, we will I
think, since it shows oracle dont give a stuff
For list archive, patch is:
--- mysql-5.5.32/vio/viossl.c 2013-05-17 01:47:14.0 +1000
+++ mysql-5.5.32a/vio/viossl.c 2013-06-09 15:38:06.0 +1000
@@ -172,8 +172,10 @@
SSL_SESSION_set_timeout(SSL_get_session(ssl), timeout);
SSL_set_fd(ssl, vio-sd);
#ifndef HAVE_YASSL
+#ifdef SSL_OP_NO_COMPRESSION
SSL_set_options(ssl, SSL_OP_NO_COMPRESSION);
#endif
+#endif
if ((r= connect_accept_func(ssl)) 1)
{
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
Claudio
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
severe build bug 5.5 viossl
This was reported in 5.5.31, a patch, VERY SIMPLE was submitted.
The problem goes ignored by oracle
5.5.32 releases, same error, apply the same simple patch and builds
/tmp/mysql-5.5.32/vio/viossl.c: In function 'ssl_do':
/tmp/mysql-5.5.32/vio/viossl.c:175: error: 'SSL_OP_NO_COMPRESSION'
undeclared (first use in this
function)
/tmp/mysql-5.5.32/vio/viossl.c:175: error: (Each undeclared identifier
is reported only once
/tmp/mysql-5.5.32/vio/viossl.c:175: error: for each function it appears in.)
make[2]: *** [vio/CMakeFiles/vio.dir/viossl.c.o] Error 1
make[1]: *** [vio/CMakeFiles/vio.dir/all] Error 2
Question, does anyone at oracle even bother with bug tracking now days?
How can something that causes a fail of building with versions of
openssl less then 1.0.0
go un fixed for so long.
Is this more proof that oracle DGAF about mysql? should I move to mariadb?
because if we have to re patch a failed build on 5.5.33, we will I
think, since it shows oracle dont give a stuff
For list archive, patch is:
--- mysql-5.5.32/vio/viossl.c 2013-05-17 01:47:14.0 +1000
+++ mysql-5.5.32a/vio/viossl.c 2013-06-09 15:38:06.0 +1000
@@ -172,8 +172,10 @@
SSL_SESSION_set_timeout(SSL_get_session(ssl), timeout);
SSL_set_fd(ssl, vio-sd);
#ifndef HAVE_YASSL
+#ifdef SSL_OP_NO_COMPRESSION
SSL_set_options(ssl, SSL_OP_NO_COMPRESSION);
#endif
+#endif
if ((r= connect_accept_func(ssl)) 1)
{
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: remove me from the mailing
This is what happens when a large conglomerate writes large checks. Everyone goes on the aggressive... I'm KIDDING!@!!@#!@!! N. On 4/6/13, Noel Butler noel.but...@ausics.net wrote: On Sat, 2013-04-06 at 16:18 +, attee...@gmail.com wrote: You don't have to be a jackass to him/her. He's always a jackass, but, I must say, it is a rare occasion that I agree, and, hte *idiot* who posted all those lines was certainly more of a jackass. The CAN SPAM act requires that a single link, with no further action on behalf of the recipient, must be provided to unsubscribe. CAN SPAM Act? Sorry, no such named Act exists in my country., and I'm certain it would not include mailing lists due to their nature and design, and only an *idiot* lawmaker would say you need that one line click, since, for nearly 20 years that has been, and still is a very popular method for spammers to know who has read their junk and that they have reached a valid mailbox I see no such thing in the footer. The website is terrible to navigate on mobile devices... I don't see how I can unsubscribe myself either and I feel OP's pain. snip please MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql ^ WTF is that? an invitation to dinner? it is however well known for a very very long time that oracle have wrecked this list completely, it is non compliant in so many ways its a joke (think all those OoO messages you get when you post), and no one at oracle knows anyone who is in a position to fix it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Reusing ibdata1 space
Hello Reindi, I don't mean to revisit an old post however, we are also using innodb and experiencing a rough start. Should we set innodb_file_per_table to 1? Also we expereince this scenario a lot: mysql show tables; +---+ | Tables_in_symax | +---+ | acc | ERROR 1146 (42S02): Table 'symax.acc' doesn't exist I should mention that we have deleted the ib_* files in the past. Is it ok if I post our config for a quick review? Thanks in Advance, Nick. On Tue, Nov 1, 2011 at 10:09 AM, Reindl Harald h.rei...@thelounge.net wrote: Am 01.11.2011 15:02, schrieb Rozeboom, Kay [DAS]: We are running MySQL 5.0.77, and using INNODB in production for the first time. The production database has a lot of inserts and deletes, and the shared ibdata1 file is continually growing. I understand that to return the unused space to the operating system, we must delete and recreate ibdata1 and its associated .frm files. I am wondering if we could do the following instead: 1) Let ibdata1 grow for a while. 2) Rebuild the tables periodically using this syntax: ALTER TABLE t1 ENGINE = InnoDB; this will not help as long you are not using innodb_file_per_table and if you would using it ibdata1 would not grow in my opinion innodb_file_per_table=0 is a dumb default and requires that people with too few expierience with mysql/innodb would much more carefully read documentations as they usually do -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Reusing ibdata1 space
Hello Reindl, I just noticed that I misspelled your name. Sorry about that! Cheers, Nick. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Error when building libmsyqlclient 5.5
Hello Everyone, We needed to install the mysql header files on the client machines (i.e., libmysqlclient) therefore, the entire server is not required. We are using MySQL 5.5 with the following cmake: cmake ../ -DWITHOUT_SERVER=1 -DCURSES_LIBRARY=/usr/lib/libncurses.a -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_MAINTAINER_MODE=OFF -DWITH_DEBUG=OFF The configuration was ok but we recieved an error when issuing make: [ 85%] Generating ../include/mysqld_error.h, ../sql/share/english/errmsg.sys Can't create output directory for /usr/local/src/mysql-5.5.15/bld/sql/share/ukrainian Failed to create sys files make[2]: *** [include/mysqld_error.h] Error 1 make[1]: *** [extra/CMakeFiles/GenError.dir/all] Error 2 make: *** [all] Error 2 Thanks in Advance, Nick. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
libmysqlclient from source
Hello Everyone, I am looking to build just the mysql client, and not the entire server from source. Which cmake flags do I need to install just the mysql client/devel header files. The actualy mysql server is on a different machine. Thanks in Advance, Nick. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: libmysqlclient from source
I should have mentioned that this is using mysql 5.5.15 Thanks Again, Nick. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: credit where due
Thanks Shawn! Ninus from Montreal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL-devel.tar.gz Availability
Hello Everyone, I was wondering if the MySQL devel files tar was available for download? I was only able to find rpm on the mysql site. Thanks in Advance, Nick.
Re: MySQL-devel.tar.gz Availability
Hello Everyone, Thank you so much for your response. I don't need the entire MySQL server just the development libraries. I found MySQL-devel-5.5.16-1.linux2. 6.i386.rpm, but there is no MySQL-devel-5.5.16-1.linux2.6.i386.tar.gz. Thanks Again,
Re: MySQL-devel.tar.gz Availability
Hello Everyone, Thank you so much for your response. I don't need the entire MySQL server just the development libraries. I found MySQL-devel-5.5.16-1.linux2. 6.i386.rpm, but there is no MySQL-devel-5.5.16-1.linux2.6.i386.tar.gz. Thanks Again, Nick.
Re: MySQL-devel.tar.gz Availability
That being said, does cmake have a --help friend? I just don't want to install the complete MySQL server, just MySQL-devel, as mentioned earlier. Nick
Re: MySQL-devel.tar.gz Availability
I will post the cmake that get's just the devel header files shortly. Nick.
Subquery performance slow to non-existent...
Hey all...
I am having a problem with sub-queries that I cannot trouble shoot.
I run query a:
select symbol from names where market like 'NYMEX' and name like 'natural
gas {%';
and get 168 names that I manually insert into query b:
select * from endOfDayData where endOfDayData.market like 'NYMEX' and
endOfDayData.symbol IN
(NGF09,NGF10,NGF11,NGF12,NGF13,NGF14,NGF15,NGF16,NGF17,NGF18,NGF19,NGF20,
NGF21,NGF22,NGG09,NGG10,NGG11,NGG12,NGG13,NGG14,NGG15,NGG16,NGG17,NGG18,NGG19,NGG20,NGG21,NGG22,NGH09,NGH10,NGH11,NGH12,NGH13,NGH14,NGH15,NGH16,
NGH17,NGH18,NGH19,NGH20,NGH21,NGH22,NGJ09,NGJ10,NGJ11,NGJ12,NGJ13,NGJ14,NGJ15,NGJ16,NGJ17,NGJ18,NGJ19,NGJ20,NGJ21,NGJ22,NGK09,NGK10,NGK11,NGK12,
NGK13,NGK14,NGK15,NGK16,NGK17,NGK18,NGK19,NGK20,NGK21,NGK22,NGM09,NGM10,NGM11,NGM12,NGM13,NGM14,NGM15,NGM16,NGM17,NGM18,NGM19,NGM20,NGM21,NGM22,
NGN09,NGN10,NGN11,NGN12,NGN13,NGN14,NGN15,NGN16,NGN17,NGN18,NGN19,NGN20,NGN21,NGN22,NGQ09,NGQ10,NGQ11,NGQ12,NGQ13,NGQ14,NGQ15,NGQ16,NGQ17,NGQ18,
NGQ19,NGQ20,NGQ21,NGQ22,NGU09,NGU10,NGU11,NGU12,NGU13,NGU14,NGU15,NGU16,NGU17,NGU18,NGU19,NGU20,NGU21,NGU22,NGV09,NGV10,NGV11,NGV12,NGV13,NGV14,
NGV15,NGV16,NGV17,NGV18,NGV19,NGV20,NGV21,NGV22,NGX09,NGX10,NGX11,NGX12,NGX13,NGX14,NGX15,NGX16,NGX17,NGX18,NGX19,NGX20,NGX21,NGX22,NGZ09,NGZ10,
NGZ11,NGZ12,NGZ13,NGZ14,NGZ15,NGZ16,NGZ17,NGZ18,NGZ19,NGZ20,NGZ21,NGZ22);
Running query b gives me a result set as follows:
| 2010-01-15 | NYMEX | NGZ22 | 8.9620 | 8.9680 | 8.9620 | 8.9680
| 0 |
++++-+-+-+-++
86765 rows in set (4.46 sec)
I then because I want to generalize query b I continue by creating query c
as follows:
mysql select * from endOfDayData where endOfDayData.market like 'NYMEX'
and endOfDayData.symbol IN (select names.symbol from names where
names.market like 'NYMEX' and names.name like 'natural gas {%');
Query c seems to have good syntax as neither the command line mysql
interface nor the gui spit it back but it literally takes forever to run;
I've waited at least twenty minutes and not got anything back. I'm running
Ubuntu 9.10 on an intel core i7 with 4GB RAM and 12GB swap... the process
monitor doesn't even flinch so I'm not thinking hardware here... why is the
sub-query running so slow?
Thanks for you help!!
Nick
Re: Very slow inserts into InnoDB tables
hdparm -Tt /dev/sdX ? Ian Simpson wrote: That's pretty much what I've been doing to get that the drive is running at 100% bandwidth. What I'd like is something that just gives the bandwidth of the device in terms of Mb/s: you can probably work it out using that iostat command, seeing how much it wrote and what percentage of the bandwidth it's using, and then doing a calculation with those numbers to get the 100% value, but I don't know if that's valid, since there are generally a number of other operations going on at the same time. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to install MYSQL from source on windows?
I want to install MYSQL from source on Windows. Now, i have compiler success with Microsoft Visual Studio 2005 and Cmake on Windows XP. But nothing happened!! I have read the INSTALL-WIN-SOURCE that is in source, but no use. So, who can help me what should I do the next step to install MYSQL from source on Windows. -- View this message in context: http://www.nabble.com/how-to-install-MYSQL-from-source-on-windows--tp15530583p15530583.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Making a prefixed install / hard-wired global configuration files?
Is there an easy way to build MySQL client library, server and start-up script so that /etc/my.cnf is ignored? I need to build client and server so that it in no way interacts with the global system install, and build programs against that client library which also don't interact with the global system install. My attempts so far show that I need to make hacks to get it to work, where I expected something like ./configure --prefix=/my/prefix --global_config=blah would do the trick but doesn't seem to be the case. I notice Linux distros often have their configs in places other than /etc/my.cnf. Have I overlooked something? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Making a prefixed install / hard-wired global configuration files?
Hello Jeremy Thank you for the pointer. After make clean, re-configured, re-compiled then tried, but /path/to/bin/mysql --help still shows /etc/my.cnf as the default file. Jeremy Cole wrote: Hi Nick, This should do it: --sysconfdir=DIR read-only single-machine data [PREFIX/etc] Regards, Jeremy Nick Hill wrote: Is there an easy way to build MySQL client library, server and start-up script so that /etc/my.cnf is ignored? I need to build client and server so that it in no way interacts with the global system install, and build programs against that client library which also don't interact with the global system install. My attempts so far show that I need to make hacks to get it to work, where I expected something like ./configure --prefix=/my/prefix --global_config=blah would do the trick but doesn't seem to be the case. I notice Linux distros often have their configs in places other than /etc/my.cnf. Have I overlooked something? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
only update if values different
What is the best way to UPDATE a row only if values are different? We have a mainframe extract that literally has 100,000 rows and am worried about the performance of just running INSERTs each night. Is there a simple comparison command or would you have to nest a SELECT statement? Thank you, Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Struggling with the logic
Hello John There are two approaches to this. 1) Best, save date into table as a number. Select date like WHERE date=$date1 AND date=$date2 2)Otherwise, if storing dates a strings: WHERE date=$date1 OR date=$date2 OR date=$date3 OR date=$date4 OR date=$date5. In general, if you want to select date ranges and perform other date calculations, save the date as a unix date number then convert to human readable form as necessary. John Berman wrote: Hi I'm struggling with some logic I have a table called: submissions and each record has an approvedate field which stores the date mm/dd/ I want to display all records for 7 days only from their approved date so I guess something like Select * from submissions were approvedate - this is were im getting stuck Pointers appreciated, im sure its simple ? Regards John B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DROP TEMPORARY TABLE waiting for table???
I'm seeing something that I don't think should be happening, but I'm not sure if it's a MySQL bug. To allow some of my stored procedures to operate concurrently, I make a temporary memory copy of some shared tables that are accessed by the procedures. The temporary heap table has the same name as the table I'm copying. The scenario here is a data warehouse with a bunch of data marts and some shared dimensions that are in a separate database. I'm copying the necessary rows of the commonly used shared dimension tables, to overcome the problem of stored procedures locking all the tables they're going to use, which was preventing concurrency. The problem is that despite this, I'm seeing processes that are stuck with status Waiting for table when they are trying to drop the temporary table if it exists (DROP TEMPORARY TABLE IF EXISTS shared_dimensions.page_dim). I always drop and recreate it at the start of a series of analyses, so that they have the most recent copy. I create the temporary heap table in an independent procedure, so it can't be locking the table. There are other procedures using their own temporary heap table copies of the table I'm copying, but they're not using the real table, only the copy. So... my question is, why is there any problem dropping a table that should only be visible to the connection that's trying to drop it? What's even more bizarre is that I get this problem even when the temporary table doesn't exist, on a brand-new connection. I've had this code running for a couple of weeks and just noticed the problem, so I'm not sure if it cropped up right away or not. Haven't had a chance to bounce the server yet. Any insight appreciated. Nick -- Nick Arnett [EMAIL PROTECTED] Messages: 408-904-7198 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimising for many rows and returned records (de-coupling query time to record set size for range queries)
Hello Adam Adam Wolff wrote: Actually runs through the table four times instead of twice, and maybe can't even use the index for the whole query. Assuming my results are not typical of MySQL query times, this would explain the sqrt() relationship of returned rows to query time. I have tried your suggestions of using a sub-query and have had trouble getting the syntax valid. But on using explain, it seems that 4 bytes of the index (either lat or lon) are being used and a brute force search on the index for the other constraint. If the query is returning 25600 points from a 100m dataset, it is brute seaching through 1.6m records in the second part of the index. If it were an option of creating 2 1.6M lists then looking for commonalities, it may be faster to instead use 1 1.6m item list then brute force constraint search. I have received suggestions to use spatial indexes, which I am looking into. Alternatively, I could optimise queries by creating multiple slices of the data set accross one axis then use a key on the other axis. MySQL 5.1 partitioning scheme may help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimising for many rows and returned records (de-coupling query time to record set size for range queries)
Hello
I have been looking at planning the database strategy for openstreetmap
(http://www.openstreetmap.org).
There are several data types stored in tables with longitude and
latitude columns. Select statements work by selecting
where lat$lat1 and lat$lat2 and lon$lon1 and lon$lon2
I have made many empirical tests and have concluded:
1) I can improve performance by a factor of 2-2.5 by changing the double
lat/lon to an integer then selecting on an integer.
2) I have concluded that for each 10 fold increase in the number of
records, select queries take twice as long. For each doubling of the
number of returned records, there is a sqrt(2) increase in select query
time.
All this is assuming all relevant database information is in memory.
As the database grows, it would likely improve database performance by
splitting an individual table into several thousand tables using the
file system directory btree algorithm to effectively pre-select the data
before the query is handled to the MySQL engine. This is not a neat
solution. A much better way would be to improve the mysql index
performance on very large numbers of records.
Given that there is such a strong relationship between the number of
records returned, and query time, I conclude that the whole index tree
is matched for every given number of root x records returned. If all
records we are matching are under a single node or under a small number
of nodes in the index tree, perhaps there is some way of telling the
database engine to ignore the rest of the index tree.
Could this work, or am I misunderstanding how the index tree works? Are
there existing optimisations which can de-couple the relationship
between number of records and query time where the records I am
selecting are within a small range?
Background information:
We can boil all this down to a mathematical relationship where
query1 selects s number of records from r records dataset
and
query2 selects b number of records from c records dataset
Tquery1 is time to execue query 1 and Tquery2 is time to execute query2.
Tquery2=Tquery1 * sqrt(b/s) * (2^log(r/c)) + (b-s*CONST/15000)+CONST
Where for my processor, CONST is 0.03
This can be simplified (loosing some accuracy) to:
Tquery2=Tquery1 * sqrt(b/s) * (2^log(r/c)
Raw data for selects:
Creating a plan with 10 points and averaging over 25 queries
Points_per_tile Query_Time
25600 0.118
25600 0.119
25600 0.119
25600 0.119
12800 0.069
64000.042
32000.026
16000.017
800 0.011
400 0.008
200 0.005
100 0.004
50 0.003
Creating a plan with 100 points and averaging over 25 queries
Points_per_tile Query_Time
25600 0.224
25600 0.223
25600 0.222
25600 0.223
12800 0.145
64000.093
32000.062
16000.043
800 0.029
400 0.020
200 0.015
100 0.011
50 0.008
Creating a plan with 1000 points and averaging over 25 queries
Points_per_tile Query_Time
25600 0.558
25600 0.548
25600 0.551
25600 0.551
12800 0.376
64000.257
32000.181
16000.125
800 0.087
400 0.062
200 0.044
100 0.031
Creating a plan with 1 points and averaging over 25 queries
Points_per_tile Query_Time
25600 2.422
25600 2.332
25600 2.493
25600 2.446
12800 1.769
64001.295
32000.866
16000.657
800 0.456
400 0.328
200 0.233
100 0.159
50 0.118
Source code for the above test:
#!/usr/bin/perl -w
#Program creates random point fields eqyuivalent to bitfieldtest.pl
except the data is stored
#as regular signed integers. To represent the globe as closely as
possible, extents between
#-180 and +179.99 will be used. Therefore, adding 180 normalises for
international date line 0.
#Prime Meridian 180. 11**0.01
use DBI;
use Time::HiRes qw( usleep ualarm gettimeofday tv_interval );
$DBHOST = localhost;
$DBNAME = nickh;
$DBUSER = nickh;
$DBPASS = xx;
#initialise database
$driver = mysql;
$dsn = DBI:$driver:database=$DBNAME;host=$DBHOST;
$dbh = DBI-connect($dsn, $DBUSER, $DBPASS);
[EMAIL PROTECTED](1);
@plane_densities=(10,100,1000,1);
@tile_points=(25600,25600,25600,25600,12800,6400,3200,1600,800,400,200,100,50);
$query_iterations=25;
$debug=0;
sub create_bitfield;
sub run_tests;
foreach $density(@plane_densities){
print Creating a plan with $density points and averaging over
$query_iterations queries\nPoints_per_tile Query_Time\n;
create_bitfield($density);
foreach $tilepoints(@tile_points){
my $testtime=run_tests($density,$tilepoints);
Re: Optimising for many rows and returned records (de-coupling query time to record set size for range queries)
Alexey Polyakov wrote: On 4/23/06, Nick Hill [EMAIL PROTECTED] wrote: I've noticed a couple things. 1) Right now you're emulating spatial index. 2) In future, you're going to emulate partitioning. Why do you think that doing this stuff manually is better than using builtin capabilities? 1) I am ignorant about any performance advantages of spatial indexes. 2) I am ignorant about built-in partitioning capabilities. Selects against a table use b-trees too. Splitting data into lot of tables won't help with selects at all (well, it may help on scans with concurrent large data sets if data will be spread across different physical drives, but not with regular range lookups that you're doing). It will only help with inserts. Assuming even distribution, selecting a table amongst 1000 will only take a few ms while 2^log10(1000) gives an 8 fold improvement in select performance. But then, I may be running inefficient queries as postulated by Adam Wolff. Given that there is such a strong relationship between the number of records returned, and query time, I conclude that the whole index tree is matched for every given number of root x records returned. If all records we are matching are under a single node or under a small number of nodes in the index tree, perhaps there is some way of telling the database engine to ignore the rest of the index tree. What is a 'root record'? Are you speaking about internal representation of b-tree? Yes. I am suggesting that a lower node in the B-tree may have below it all records the select query is looking for, thereby providing a short-cut. Could this work, or am I misunderstanding how the index tree works? Are there existing optimisations which can de-couple the relationship between number of records and query time where the records I am selecting are within a small range? For studying select query performance issues it's better think about index as simply about a sorted array with random-access, where each random access costs O(lgN) and accesses to adjanced data cost O(1). If your points are spread uniformly in space, cost of select query you've shown is O(N*lgN) I am unfamiliar with this representation. I am not sure I understand. -Nick Hill. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Password expire?
Hi, experts How can I make mysql database users password with expire date? Does mysql can do it? -- NiCK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ANN: Database Workbench 2.8.5 released!
On 3/27/06, Martijn Tonies [EMAIL PROTECTED] wrote: Ladies, gentlemen, Upscene Productions is proud to announce the next version of the popular database development tool: Database Workbench 2.8.5 has been released today! I'd like to hear more about the stored procedure debugger -- does all the functionality in the documentation work with MySQL? Anybody used this with MySQL, who could describe their experience with it? I would really love a good SP debugger! Nick -- Nick Arnett [EMAIL PROTECTED] Messages: 408-904-7198
Can't materialize a view -- bug?
I'm doing the equivalent of a materialized view in MySQL 5.0.15 and I've hit a strange error. Here's the scenario. CREATE OR REPLACE VIEW x_view AS SELECT [select statement, blah, blah, blah]; DROP TABLE IF EXISTS x_mview; CREATE TABLE x_mview SELECT * FROM x_view; That was all working fine until I created a function that I use inside of the view. Now the last statement, the one that would materialize the view, returns a table locking error! ERROR 1100 (HY000): Table 'x_mview' was not locked with LOCK TABLES It's difficult to lock a table that doesn't exist yet... SELECT * FROM x_view works fine. The function is a simple bit of logic (it tests a count, moving average and standard deviation to see if the count is more or less than two standard deviations from the mean): CREATE FUNCTION get_signal (cnt MEDIUMINT(8), ma MEDIUMINT(8), stdv MEDIUMINT(8)) RETURNS TINYINT(1) DETERMINISTIC RETURN IF (ma 9 AND stdv 0 AND (cnt = ma + (2 * stdv)) OR cnt = ma - (2 * stdv), IF (cnt = ma + (2 * stdv), 1, -1), 0); If this is a bug, I'll be happy to file a report... but I'd really like a solution that will let me use the function. I hit a problem using functions in stored procedures, too, and I'm wondering if these are related. Nick -- Nick Arnett [EMAIL PROTECTED] Messages: 408-904-7198
FW: So when are we switching over to the...
Somebody give me some ammo for this. I flagged MySQL v5 to our CTO and got shot down. We currently develop in MSSQL and I don't know enough about either to make a constructive argument. I'm not even sure I understand what he's on about with 'table functions' and 'nested selects'. I couldn't find them specifically with a search through the docs. All advocate rantings welcome. -- np -Original Message- From: TT Sent: Monday, October 31, 2005 3:10 PM To: NP; JC Subject: RE: So when are we switching over to the... Answer: never Last time I looked, mysql had no stored procedures. It has now, but I doubt it can do table functions nor nested selects. -Original Message- From: NP Sent: 31 October 2005 15:08 To: TT, JC Subject: So when are we switching over to the... ...vastly superior MySQL backend then? ;) http://dev.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign Keys
Is there a way to have foreign keys and force referential integrity? I have a main table and a lookup table, i want to be able to require that the main table is using values from the lookup table. I am just not sure how in MySQL. Thanks
Re: Populate values in an Excel sheet from MySQL [SOLVED]
Thanks to all for your help. I solved my problem the
quick and dirty way. Here's what I did:
1. Created my Excel spreadsheet to look the way I
wanted it, saved it as an XML spreadsheet.
2. Used PHP to pull the desired data from the MySQL
server on our iSeries machine.
3. PHP writes the XML data to a new text file using
the values from the MySQL DB and gives it the .xls
extension.
4. Browser is redirected to the new spreadsheet.
5. Spreadsheet opens like normal in IE for
saving/downloading. Or in Firefox it lets you download
it and it opens like a normal .xls spreadsheet.
Thanks to everyone here for their insight. I coudln't
have achieved it without all of you!
-Nick
--- inferno [EMAIL PROTECTED] wrote:
Hi,
I had to do the same thing on a project and the
problem was that if
you use CSV you will not be able to make a formated
excel document.
I am using now *Spreadsheet_Excel_Writer
/package/Spreadsheet_Excel_Writer (
*http://pear.php.net/package/Spreadsheet_Excel_Writer
) and it does
everything I need, including formating the page for
printing, color,
bold and boarder on cells and the best part is that
it's no really hard
to use.
If you change your mind and want to generate
that from perl you also
have some PEAR packages for that, but I've sticked
to PHP and with this
the problem was solved and I generate my data on
access, custom build
depending on the select.
Best regards,
Cristi Stoica
Arjan Hulshoff wrote:
Hello Nick,
This you can do with the MySQL ODBC Driver
installed
(http://dev.mysql.com/downloads/connector/odbc/3.51.html).
Further more
you need to activate Microsoft ActiveX Data Objects
in the references.
You can use the following code:
--Begin Code--
Dim cn As ADODB.Connection
Dim rs As ADODB.RecordSet
Set cn = New ADODB.Connection
Set rs = New ADODB.RecordSet
cn.ConnectionString = DRIVER={MySQL ODBC 3.51
Driver};SERVER=data.domain.com;PORT=3306;DATABASE=myDatabase;USER=myUser
name;PASSWORD=myPassword;OPTION=3;
cn.Open
sSQL = SELECT * FROM database
rs.Open sSQL, cn
If Not rs.BOF Then rs.MoveFirst
Do While Not rs.EOF
Cells(1, 1) = rs.Fields(index)' This line you
can
adjust with your own code
rs.MoveNext
Loop
On Error Resume Next ' This is my solution to make
sure that the
recordset is always closed, _
without the errorhandling there
occurs an error when you use a query _
that doesn't return results
('INSERT' e.g.). If there is a better way _
to close the connection, then
let me know.
If rs.State = adStateOpen Then rs.Close
On Error Goto 0
cn.Close
Set rs = Nothing
Set cn = nothing
--End Code--
HTH,
Arjan.
-Original Message-
From: Nick Jones [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 01, 2005 08:23 PM
To: mysql@lists.mysql.com
Subject: Populate values in an Excel sheet from
MySQL
Does anyone know if it is possible to populate
values into an Excel
spreadsheet from a MySQL database? Can I do this
directly in Excel or do
I need to create an external program to do the work
(i.e. in VB).
Thanks
-Nick
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam
protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Populate values in an Excel sheet from MySQL
Does anyone know if it is possible to populate values into an Excel spreadsheet from a MySQL database? Can I do this directly in Excel or do I need to create an external program to do the work (i.e. in VB). Thanks -Nick __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Populate values in an Excel sheet from MySQL
--- J.R. Bullington [EMAIL PROTECTED] wrote: Automatically populate?? Not that _I_ know of, but of course there are those on this list that know much more than I. I do know that you can export the values into tab delimited format and then import it into Excel. I think that you may want to do this via ASP or PHP. It would make life a lot easier. J.R. PHP would definitely be the way to go on this. I'm working on a web application running on our AS/400 here running Apache, PHP, and MySQL to automate our schedule forms that we fill out daily around here. Everything I've got so far is running through PHP/MySQL so if I can find a way to do this in PHP I'm definitely going to try. I'll google around for awhile and see what I come up with. MS Knowledge Base has proven to be less than useful so far in my endeavor. Also, thanks to James for his suggestion on using the ODBC query directly from Excel. This will get us started for the time being, and give me some leeway so I can work on doing this in PHP. Thanks to you both! -Nick Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Populate values in an Excel sheet from MySQL
--- Partha Dutta [EMAIL PROTECTED] wrote: Nick Jones wrote: --- J.R. Bullington [EMAIL PROTECTED] wrote: Automatically populate?? Not that _I_ know of, but of course there are those on this list that know much more than I. I do know that you can export the values into tab delimited format and then import it into Excel. I think that you may want to do this via ASP or PHP. It would make life a lot easier. J.R. PHP would definitely be the way to go on this. I'm working on a web application running on our AS/400 here running Apache, PHP, and MySQL to automate our schedule forms that we fill out daily around here. Everything I've got so far is running through PHP/MySQL so if I can find a way to do this in PHP I'm definitely going to try. I'll google around for awhile and see what I come up with. MS Knowledge Base has proven to be less than useful so far in my endeavor. Also, thanks to James for his suggestion on using the ODBC query directly from Excel. This will get us started for the time being, and give me some leeway so I can work on doing this in PHP. Thanks to you both! -Nick Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs How about this for a novel aproach... Use the CSV storage engine that MySQL provides in the source distribution? It is very easy to use, and there is no headaches. -- Partha Dutta Ok, here's what we're doing in a nutshell: Every day we fill out forms with backup times and tapes for various computer systems. Three forms a day with 70-100 fields each with all the same data. One Excel sheet and two word documents that end up as PDFs later, all of this is very time consuming. I'm creating a web app in PHP that lets you fill out one form, then click a button and it creates the PDFs for you. I've gotten that far, and now I'm trying to get it to create the Excel sheet too with just one click (fill in your desired date, click submit, and up pops your PDFs and spreadsheet. Thanks -Nick __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
tmestamp resolution problem (rounding off to a day)
Hi All,
I seem to be having a problem with the resolution using the timestamp
function. I am accessing the database snort on an ACID/SNORT/MySQL
installation utilizing a collection of shell scripts that are run as
cron jobs and function as a bot adding and removing firewall rules
(iptables). The problem is with the script that removes entries, thus
creating a decaying blacklist.
* The problem seems to be that, even though this script is run many
times a day, regardless of comparing timestamps and a range of
times/dates using this function in a shell script:
[.] WHERE date_format(timestamp, '%Y-%m-%d %T')
=DATE_SUB(CURDATE(),INTERVAL $BLACKLIST_DECAY HOUR)
* ...It only resolves to the DAY and not an hourly resolution. I have
included a script below that I use for debugging, the MySQL
functionality is taken directly from one of the larger scripts in my
package. This is apparent, as the only time any entries are removed, is
once a day, as the number of hours is rounded off to a day, and I can
see this relected in the logs. Also, FYI, the $BLACKLIST_DECAY
variable from the sourced config file is in hours.
I have also included the full ip_decy (script that removes MySQL
entries) below.
Also, as an aside. Is there a way to run multiple queries within a shell
script without having to make a connection to MySQL evey time?
Any help is welcomed. TIA
---
Here is the output of the debugging script (included below):
[EMAIL PROTECTED] root]# ip_decay_report_mysql
Current decay variable is: 469 Hours OR approx 19 Days
*nothing*
Here is the output of the debugging script providing the number of hours.
*** I get this same result ALL DAY!
[EMAIL PROTECTED] root]# ip_decay_report_mysql 466
Using supplied decay variable: 466 Hours OR approx 19 Days
+---+---+---+
| inet_ntoa(ip_src) | date_format(timestamp, '%T %d-%m-%Y') |
sig_name |
+---+---+---+
| 203.15.17.32 | 13:16:27 09-07-2005 | ICMP
Destination Unreachable Host Unreachable |
| 203.15.17.32 | 13:16:03 09-07-2005 | ICMP
Destination Unreachable Host Unreachable |
| 203.15.17.32 | 13:15:51 09-07-2005 | ICMP
Destination Unreachable Host Unreachable |
| 203.15.17.32 | 13:15:45 09-07-2005 | ICMP
Destination Unreachable Host Unreachable |
| 203.15.17.32 | 13:15:43 09-07-2005 | ICMP
Destination Unreachable Host Unreachable |
| 203.72.197.172| 13:09:00 09-07-2005 |
(http_inspect) OVERSIZE REQUEST-URI DIRECTORY |
| 62.105.37.129 | 12:19:42 09-07-2005 | ICMP
Destination Unreachable Host Unreachable |
+---+---+---+
FYI: SNORT TABLE EXAMPLE
Here is a brief abstract of 3 rows from the main acid_event table using
a modified format timestamp:
mysql SELECT
acid_event.sid,acid_event.cid,inet_ntoa(ip_src),date_format(timestamp,
'%Y-%m-%d %T') FROM acid_event LIMIT 3;
+-+--+---+---+
| sid | cid | inet_ntoa(ip_src) | date_format(timestamp, '%Y-%m-%d %T') |
+-+--+---+---+
| 2 | 5692 | 203.15.17.32 | 2005-07-25 05:27:11 |
| 2 | 5691 | 203.15.17.32 | 2005-07-25 05:27:11 |
| 2 | 5690 | 194.24.131.163| 2005-07-25 05:22:21 |
+-+--+---+---+
3 rows in set (0.01 sec)
Here is a brief abstract using the regular timestamp:
mysql SELECT acid_event.sid,acid_event.cid,inet_ntoa(ip_src),timestamp
FROM acid_event WHERE date_format(timestamp, '%Y-%m-%d %T')
=DATE_SUB(CURDATE(),INTERVAL 466 HOUR);
+-+--+---+-+
| sid | cid | inet_ntoa(ip_src) | timestamp |
+-+--+---+-+
| 2 | 3278 | 203.15.17.32 | 2005-07-09 13:16:27 |
| 2 | 3277 | 203.15.17.32 | 2005-07-09 13:16:03 |
| 2 | 3276 | 203.15.17.32 | 2005-07-09 13:15:51 |
| 2 | 3275 | 203.15.17.32 | 2005-07-09 13:15:45 |
| 2 | 3274 | 203.15.17.32 | 2005-07-09 13:15:43 |
| 2 | 3273 | 203.72.197.172| 2005-07-09 13:09:00 |
| 2 | 3272 | 62.105.37.129 | 2005-07-09 12:19:42 |
+-+--+---+-+
7 rows in set (0.14 sec)
#!/bin/sh
shopt -s -o nounset
umask 0027
declare -rx SCRIPT=${0##*/}
source /usr/local/etc/ip_bot/ip_bot.conf
declare
timestamp resolution problem (rounding off to a day)
Hi All,
I seem to be having a problem with the resolution using the timestamp
function. I am accessing the database snort on an ACID/SNORT/MySQL
installation utilizing a collection of shell scripts that are run as
cron jobs and function as a bot adding and removing firewall rules
(iptables). The problem is with the script that removes entries, thus
creating a decaying blacklist.
* The problem seems to be that, even though this script is run many
times a day, regardless of comparing timestamps and a range of
times/dates using this function in a shell script:
[.] WHERE date_format(timestamp, '%Y-%m-%d %T')
=DATE_SUB(CURDATE(),INTERVAL $BLACKLIST_DECAY HOUR)
* ...It only resolves to the DAY and not an hourly resolution. I have
included a script below that I use for debugging, the MySQL
functionality is taken directly from one of the larger scripts in my
package. This is apparent, as the only time any entries are removed, is
once a day, as the number of hours is rounded off to a day, and I can
see this relected in the logs. Also, FYI, the $BLACKLIST_DECAY
variable from the sourced config file is in hours.
I have also included the full ip_decy (script that removes MySQL
entries) below.
Also, as an aside. Is there a way to run multiple queries within a shell
script without having to make a connection to MySQL evey time?
Any help is welcomed. TIA
---
Here is the output of the debugging script (included below):
[EMAIL PROTECTED] root]# ip_decay_report_mysql
Current decay variable is: 469 Hours OR approx 19 Days
*nothing*
Here is the output of the debugging script providing the number of hours.
*** I get this same result ALL DAY!
[EMAIL PROTECTED] root]# ip_decay_report_mysql 466
Using supplied decay variable: 466 Hours OR approx 19 Days
+---+---+---+
| inet_ntoa(ip_src) | date_format(timestamp, '%T %d-%m-%Y') |
sig_name |
+---+---+---+
| 203.15.17.32 | 13:16:27 09-07-2005 | ICMP
Destination Unreachable Host Unreachable |
| 203.15.17.32 | 13:16:03 09-07-2005 | ICMP
Destination Unreachable Host Unreachable |
| 203.15.17.32 | 13:15:51 09-07-2005 | ICMP
Destination Unreachable Host Unreachable |
| 203.15.17.32 | 13:15:45 09-07-2005 | ICMP
Destination Unreachable Host Unreachable |
| 203.15.17.32 | 13:15:43 09-07-2005 | ICMP
Destination Unreachable Host Unreachable |
| 203.72.197.172| 13:09:00 09-07-2005 |
(http_inspect) OVERSIZE REQUEST-URI DIRECTORY |
| 62.105.37.129 | 12:19:42 09-07-2005 | ICMP
Destination Unreachable Host Unreachable |
+---+---+---+
FYI: SNORT TABLE EXAMPLE
Here is a brief abstract of 3 rows from the main acid_event table using
a modified format timestamp:
mysql SELECT
acid_event.sid,acid_event.cid,inet_ntoa(ip_src),date_format(timestamp,
'%Y-%m-%d %T') FROM acid_event LIMIT 3;
+-+--+---+---+
| sid | cid | inet_ntoa(ip_src) | date_format(timestamp, '%Y-%m-%d %T') |
+-+--+---+---+
| 2 | 5692 | 203.15.17.32 | 2005-07-25 05:27:11 |
| 2 | 5691 | 203.15.17.32 | 2005-07-25 05:27:11 |
| 2 | 5690 | 194.24.131.163| 2005-07-25 05:22:21 |
+-+--+---+---+
3 rows in set (0.01 sec)
Here is a brief abstract using the regular timestamp:
mysql SELECT acid_event.sid,acid_event.cid,inet_ntoa(ip_src),timestamp
FROM acid_event WHERE date_format(timestamp, '%Y-%m-%d %T')
=DATE_SUB(CURDATE(),INTERVAL 466 HOUR);
+-+--+---+-+
| sid | cid | inet_ntoa(ip_src) | timestamp |
+-+--+---+-+
| 2 | 3278 | 203.15.17.32 | 2005-07-09 13:16:27 |
| 2 | 3277 | 203.15.17.32 | 2005-07-09 13:16:03 |
| 2 | 3276 | 203.15.17.32 | 2005-07-09 13:15:51 |
| 2 | 3275 | 203.15.17.32 | 2005-07-09 13:15:45 |
| 2 | 3274 | 203.15.17.32 | 2005-07-09 13:15:43 |
| 2 | 3273 | 203.72.197.172| 2005-07-09 13:09:00 |
| 2 | 3272 | 62.105.37.129 | 2005-07-09 12:19:42 |
+-+--+---+-+
7 rows in set (0.14 sec)
#!/bin/sh
shopt -s -o nounset
umask 0027
declare -rx SCRIPT=${0##*/}
source /usr/local/etc/ip_bot/ip_bot.conf
declare
Re: tmestamp resolution problem (rounding off to a day)
Jigal van Hemert wrote: Nick Sinclair wrote: [.] WHERE date_format(timestamp, '%Y-%m-%d %T') =DATE_SUB(CURDATE(),INTERVAL $BLACKLIST_DECAY HOUR) * ...It only resolves to the DAY and not an hourly resolution. I have included a script below that I use for debugging, the MySQL functionality is taken directly from one of the larger scripts in my package. This is apparent, as the only time any entries are removed, is once a day, as the number of hours is rounded off to a day, and I can see this relected in the logs. Also, FYI, the $BLACKLIST_DECAY variable from the sourced config file is in hours. I don't know the type of the field timestamp, but I suspect that the following will work better and faster: WHERE `timestamp` = NOW() - INTERVAL $BLACKLIST_DECAY HOUR; CURDATE() gives you a 'timestamp' of the beginning of today (only date part with time part as 00:00:00) NOW() gives you a 'timestamp' of this moment (including time part) The MySQL optimizer will see that the expression after the = is a constant and will produce a fast query (instead of calculating the DATE_FORMAT() for each row in de table) which can use an index. Regards, Jigal. Thanks Jigal, all works great - You are *so* right.
Re: joining six tables by mutual column
Try this If something is numeric SELECT * FROM table1, table2, table3, table4, table5, table6 WHERE table1.something = table2.something AND table1.something = table3.something AND table1.something = table4.something AND table1.something = table5.something AND table1.something = table6.something ; If something isn't numeric. SELECT * FROM table1, table2, table3, table4, table5, table6 WHERE table1.something LIKE table2.something AND table1.something LIKE table3.something AND table1.something LIKE table4.something AND table1.something LIKE table5.something AND table1.something LIKE table6.something ; ( Nick Pasich ) On Mon, Apr 25, 2005 at 12:51:53AM +0200, Schalk Neethling wrote: Greetings everyone. Hope someone can give me some pointers here. I have six tables in the database and I need to JOIN them on a row that appears in all of the tables. How do I do this? I have so far done the normal 'cross-join' saying SELECT * FROM table1, table2, table3, table4, table5, table6 WHERE something = something; I have also added STRAIGHT_JOIN to force the order but, how do I JOIN six tables to/by one column? I have done some google searches as well as looked at MySQL 2nd edition by Paul DuBois, sorry if I missed something here Paul, and so far I have not found an answer. Any help or pointers will be appreciated. Thank you. -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers emotionalize.conceptualize.visualize.realize Landlines Tel: +27125468436 Fax: +27125468436 Web email:[EMAIL PROTECTED] Global: www.volume4.com Messenger Yahoo!: v_olume4 AOL: v0lume4 MSN: [EMAIL PROTECTED] We support OpenSource Get Firefox!- The browser reloaded - http://www.mozilla.org/products/firefox/ This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug? Date insert comes out zero on Solaris using Python MySQLdb?
I have Python code that I run on Windows and Solaris. It works properly on Windows. However, on Solaris, when it inserts records, datetime columns end up with a value of zero. I suspect that this began happening after we upgraded the MySQL server to 4.1.10 from a 4.0.x version (I think we were on 4.0.18). I see some changes to datetime handling in the 4.1.x releases, but nothing that seems immediately obvious. I hope to fix this is by re-building MySQLdb based on the 4.1.10 libraries. Any other suggestions? I'm using MySQLdb.times to format the datetimes before inserting. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multi-Table Query Problem...
I'm trying to do a multitable query and am having problems. I have three tables: vendors, products, and vendorproducts. The vendorproducts table creates a many to many relationship between the vendors and the products. There is nothing more than the vendor and product ids in the vendorproducts table. I want to be able to create a query that will find vendors who have certain products. However, I'm trying to make a keyword search (PHP/MySQL) so that using form data I can search multiple columns for the same keyword. Here's how I am currently doing the query: $query = SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid ; $query .= FROM vendorproducts AS vp ; $query .= INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid ; $query .= INNER JOIN products AS p ON vp.vpvendorid = p.productid ; $query .= WHERE (p.productname LIKE '%.$_GET['keyword'].%') ; $query .= OR (p.productfamily LIKE '%.$_GET['keyword'].%') ; $query .= OR (v.vcategory LIKE '%.$_GET['keyword'].%') ; $query .= GROUP BY v.vbusiness ; As an example, it might look like this: SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid FROM vendorproducts AS vp INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid INNER JOIN products AS p ON vp.vpvendorid = p.productid WHERE (p.productname LIKE '%Apples%') OR (p.productfamily LIKE '%Apples%') OR (v.vcategory LIKE '%Apples%') GROUP BY v.vbusiness Where am I going wrong? The results aren't random, but I can't see how they're coming up with what they're coming up with. TIA, Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi-Table Query Problem...
On Mon, 14 Mar 2005 15:12:18 -0800, Scott Klarenbach wrote Because, with the '%keyword%' operator, you're going to match any of those columns that contain the keyword inside of it. This can be a little confusing as 'ef' will return true on 'abcdefghijk'? Instead, you might try 'keyword%' so that 'apple' returns true for 'apples', 'apple juice', 'apple cider', BUT returns false for 'ple'. But, without seeing the results and what you'd hoped them to be, it's tough to narrow down the problem. sk Yes, I understand, but that's not what I'm getting. I'm getting rows returned where apples is not present in any of the columns in any way. I think it's a syntax mistake in my query, but I can't see it. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi-Table Query Problem...SOLVED
Thanks. That was it. I'm glad it was something so simple. Just needed a careful set of eyes. I was worried my understanding of SQL/JOINs was screwy. Thanks again. Nick PS I'll read up on the FULLTEXT matching. I don't know it well. $query = SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid ; $query .= FROM vendorproducts AS vp ; $query .= INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid ; $query .= INNER JOIN products AS p ON vp.vpvendorid = p.productid ; ^^ I'd guess the problem is right there. Shouldn't that be something like INNER JOIN products AS p ON vp.vpproductid = p.productid instead? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't get table lock (4.0.23 and InnoDB)
We have something I can't figure out happening on one of our servers. It's running 4.0.23 on OSX. One of the InnoDB tables is locked even though we can't see any process that is even active that could have locked it. SHOW OPEN TABLES doesn't show it as in use or locked. We've tried all sorts of things to get access to it, short of bouncing the server, but it keeps saying it's locked. When we try to acquire a lock on that table, the process list says SYSTEM LOCK and we get the good old Lock wait timeout exceeded; Try restarting transaction message after 30 seconds. I've tried this with AUTOCOMMIT on and off, with LOCK TABLES and with START TRANSACTION... nothing seems to help. Strangely, FLUSH TABLES WITH READ LOCK seems to succeed, but after unlocking them, we still can't get a lock for this table. I've never been able to see how we can find out which connection has the lock on a given table. If that's possible, I'd like to know that, too, so I could at least know how it got locked in the first place. Could a misbehaving client do this in a way that would cause the lock to persist even after the client has disconnected? If so, ugh. Any help appreciated. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT and UPDATE together, with LIMIT? (incremental external indexing)
[EMAIL PROTECTED] wrote: Your solution is as good as any I can think of. If your source tables are InnoDB you could wrap your SELECT/UPDATE processing in a transaction to help make it even more robust. It might speed things up if you omit the offset to your LIMIT clause and just do: Yes, they are InnoDB and I routinely now do such things in transactions. And I did remove the LIMIT offset, which was from the first tests. that way you always find the first 5000 un-indexed records, regardless of physical position. Record order is not guaranteed unless you use an ORDER BY statement and that would just slow you down, wouldn't it? Indeed, which was one reason I went with the HEAP table... which is also a TEMPORARY table so that two clients can work concurrently. Thanks. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Networking querys
listsql listsql wrote: Since I read about Foaf [ http://www.foaf-project.org/ ], I become interested with Social Networking, What you're doing is often called link analysis -- searches on that term may yield more for you to chew on. There are software tools and visualization tools for answering the kind of questions this data covers. For the latter, Pajek (http://vlado.fmf.uni-lj.si/pub/networks/pajek/) is one of the most interesting. More generally, you're storing a graph in a relational database. Searching on that subject will show you several approaches. The way I deal with this is to create tables pretty much as you have, but instead of self-joins in MySQL, I load it all into a program that stores the graph in hashes, then uses recursion to get the kind of answers you want. This is much, much, much faster than doing the same in the database, though it might become memory constrained if you have a big network. In that case, my next step is to do all of the recursions and store the results in the database as pairs and their distances from one another. Then it's a simple lookup. If you do find a way to do this efficiently in MySQL, I'll be interested! Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Networking querys
Peter Brawley wrote: is, there is some method to iterate in this relation to avoid joining the table in itself each time ? Exactly the problem with trying to model an anything-goes network, a world of ends, in a relational model of a directed graph. I think you need an XML layer in there, eg see http://iswc2004.semanticweb.org/demos/03/. XML isn't necessary and probably just makes it more complicated. As someone wrote, if you have a problem and decide to solve it with XML, now you have two problems. On the other hand, since the semantic web relies on graphs, approaches that work for it do apply... but unless interoperability matters, it's hard for me to imagine a good reason to bother with XML. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table scan in join on primary keys??
I'm updating one table with data from another and finding that the server is doing a table scan on the second table, even though it's a simple join on the primary keys. This doesn't seem right. The query looks liket this: UPDATE a SET a.y TO b.a WHERE a.primary_key = b.primary_key and if I do the equivalent SELECT: EXPLAIN SELECT * FROM a, b WHERE a.primary_key = b.primary_key it shows that it's doing a table scan on table b. Is this normal? Is it because primary keys are unique? I had imagined that this would be a very fast operation, but it's not, it's surprisingly slow. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixed with Fields
Dathan Pattishall wrote: Use char And use fixed-length types for *all* columns... one variable-length column makes all records variable-length. Nick -Original Message- From: Marc Michalowski [mailto:[EMAIL PROTECTED] Sent: Friday, January 28, 2005 10:28 AM To: mysql@lists.mysql.com Subject: Fixed with Fields I was wondering if there is a way to create fixed width fields. Example: The field is set to 18 but data contained is 11. I need the length to remain 18. Is there anyway to do this? Thanks for your help. -Marc -- 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: FULLTEXT index on InnoDB tables (looking for a workaround)
symbulos partners wrote: Is there any other workaround? The reason because we are using InnoDB is because there s full support - for foreign keys, - for joint queries - for rollback on commit Does anybody know any other way of indexing the table in a way, which would allow full text search? Sure -- use an external search engine that has database hooks (or create your own connector). Depending on the sophistication (or existence) of a database connector for the search engine, you'll have to write more or less code to tell it how them to talk to each other -- how to know when there's new data to index, how to retrieve the text data into the search engine for indexing or display. The most sophisticated ones use database triggers to make it all fairly easy. Otherwise, you'll need to write code that hands the text and a pointer (typically the primary key) to the full-text engine when a record is added or modified, and the pointer for deletes. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.1.9 is released
Heikki Tuuri wrote: * Do not acquire an internal InnoDB table lock in LOCK TABLES if AUTOCOMMIT=1. This helps in porting old MyISAM applications to InnoDB. InnoDB table locks in that case caused very easily deadlocks. Could you explain a bit more about how this relates to MyISAM? Is it just that using LOCK TABLES with InnoDB was causing a lot of deadlocks? If so, that would explain what I've been seeing in MySQL 4.0.21 (lots of deadlocks on a very small table that I use for managing parallel processes). Until we upgrade (which will be soon, I think), is it best to turn AUTOCOMMIT off and COMMIT when appropriate? Thanks! Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.1.9 is released
Heikki Tuuri wrote: http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html The correct way to use LOCK TABLES with transactional tables, like InnoDB, is to set AUTOCOMMIT = 0 and not to call UNLOCK TABLES until you commit the transaction explicitly. When you call LOCK TABLES, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its table lock at the next commit, but for MySQL to release its table lock, you have to call UNLOCK TABLES. You should not have AUTOCOMMIT = 1, because then InnoDB releases its table lock immediately after the call of LOCK TABLES, and deadlocks will very easily happen. Starting from 4.1.9, we do not acquire the InnoDB table lock at all if AUTOCOMMIT=1. That helps old applications to avoid unnecessary deadlocks. LOCK TABLES when done on an InnoDB table first acquires an InnoDB table lock, and then the MySQL table lock. But when AUTOCOMMIT=1, the InnoDB lock is released immediately. This caused lots of deadlocks with LOCK TABLES. The fix is that in the AUTOCOMMIT=1 mode we do not acquire the InnoDB lock at all. It does not make sense to get a lock and then release it immediately. That's what I was just reading! So... is this the equivalent of using BEGIN and COMMIT, for which I have methods in the Python MySQLdb module? Or is there an advantage to the latter? Thanks again, Nick Arnett Director of Business Intelligence Services Liveworld Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT or SHOW full table name?
I don't see a way to ask MySQL what the full name of a table is... the equivalent of this: SELECT CONCAT(DATABASE(), ., table_name) which would return something like this (assuming the current database is called my_database: my_database.table_name The reason I want this is to ensure that a scripted operation isn't treating the same table as if it were two different tables. I have a method that moves records among tables by copying and then deleting. If the two tables are really the same table, it'll lose the records, since it will have copied them to the same table, then delete them. I can do it the way above, but I'm thinking there might be a better way... Thanks! Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
getting mysql working
Hi, I am trying to get MsSQL working. I can't get the server to start. I tried typing in mysql_install_db and got the following response: linux:/home/nick # mysql_install_db Installing all prepared tables 040602 5:54:02 /usr/sbin/mysqld: Shutdown Complete PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h linux password 'new-password' See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should run the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the /usr/bin/mysqlbug script! But this did not start it. I tried typing in rcmysql start but that failed. I can't get the service started in the first place so I can't add any passwards to any accounts. I installed the mysql with the suse 9.1 pro cd. I am thinking that I should reinstall my mysql. But I don't know how to do that. Can someone please tell me how to fix this or how to reinstall it? Nick _ Check out the coupons and bargains on MSN Offers! http://youroffers.msn.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 100% CPU Spikes
Hi, I recently installed MySQL on a Windows 2003 Server last night to pull some stats from a online game I run, however everytime a query is sent to the database it uses 100% cpu causing a 100% cpu spike for a breif second - I'm using .asp pages if that helps. Is there anyway to avoid this at all as it effects the performance of the server / game and I'm also worried someone could use such a thing to cause a Denial of Service attack. Regards, --Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 100% CPU Spikes
Jeremy, Any information on how I can go about doing this, or maybe some documentation on improving the speed. The problem is two of the databases I think, both are around 1.1MB each and: Database1: 13736 data records Database2: 77312 data records. Is this excessive ? When my .asp pages request from the database on my 2.7GHz with 2GB of RAM it will spike to 100% for around 1 second then go back to normal. --Nick - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Nick A. Sugiero [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, May 05, 2004 6:07 PM Subject: Re: MySQL 100% CPU Spikes On Wed, May 05, 2004 at 03:25:14PM +0100, Nick A. Sugiero wrote: Hi, I recently installed MySQL on a Windows 2003 Server last night to pull some stats from a online game I run, however everytime a query is sent to the database it uses 100% cpu causing a 100% cpu spike for a breif second - I'm using .asp pages if that helps. Is there anyway to avoid this at all as it effects the performance of the server / game and I'm also worried someone could use such a thing to cause a Denial of Service attack. Make sure your queries are well optimized. If they're not, they'll generally result in excessive CPU utilization. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FreeBSD stacksize problem, possibly?
I am running mysql-standard-4.0.18-unknown-freebsd4.7-i386-linuxthreads and I am unable to achieve more than 440 connections. I receive the common Can't create a new thread (errno 35). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug. Now, I have 1.5GB of free memory, so that's not the problem. I've also thought about the limits issue, I've checked, they are as follows: Resource limits (current): cputime infinity secs filesize infinity kb datasize 2096128 kb stacksize 524288 kb coredumpsize infinity kb memoryuseinfinity kb memorylocked infinity kb maxprocesses 5547 openfiles 11095 sbsize infinity bytes vmemoryuse infinity kb My thread_stack is about 192kb in MySQL, so 192*440 is only 84MB, which should be under 512MB. I'm not running out of processes or file handles, or sockets from sysctl -a. Does anyone have any other suggestions or things to double check? Nick
RE: FreeBSD 4.9 + MySQL 4.0 + LinuxThreads
Interesting idea, but I should be ok. I don't plan to have large amounts of MySQL threads. I just had FreeBSD setup to allow 2GB of memory per process because of memcached which I also plan to run on this server. I just compiled it to a little under 2GB and MySQL ran fine, it was just hard to track down at first because the manually compiled version of MySQL didn't give me near the details the binary version did. Thanks! nickg -Original Message- From: Jeremy Zawodny [mailto:] Sent: Wednesday, February 11, 2004 12:55 AM To: Nick Cc: [EMAIL PROTECTED] Subject: Re: FreeBSD 4.9 + MySQL 4.0 + LinuxThreads On Mon, Feb 02, 2004 at 02:00:46AM -0500, Nick wrote: Alas, when I tried the binary version, it gave me an error that google helped link me to the following: Fatal error 'Can't create gc thread' at line ? in file /usr/src/lib/libc_r/uthread/uthread_create.c (errno = ?) mysqld got signal 6; http://lists.freebsd.org/pipermail/freebsd-stable/2003-May/000979.html Apparently you have to keep the MAXDSIZ below 2GB for FreeBSD to create threads. Maybe I'll submit something for the docs people to update to include this, since a recompile and reboot now prove all installs work properly. Yes, it's a byproduct of the memory map that FreeBSD sets up. With that out of the way, you should find it to be quite solid. Another trick we've used is to recompile LinuxThreads with a smaller thred stack. It's only necessary if you expect to have LOTS of concurrent connections. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 150 days, processed 1,628,781,557 queries (125/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FreeBSD 4.9 + MySQL 4.0 + LinuxThreads
Alas, when I tried the binary version, it gave me an error that google helped link me to the following: Fatal error 'Can't create gc thread' at line ? in file /usr/src/lib/libc_r/uthread/uthread_create.c (errno = ?) mysqld got signal 6; http://lists.freebsd.org/pipermail/freebsd-stable/2003-May/000979.html Apparently you have to keep the MAXDSIZ below 2GB for FreeBSD to create threads. Maybe I'll submit something for the docs people to update to include this, since a recompile and reboot now prove all installs work properly. Sorry to waste your time all, nickg - Original Message - From: Nick [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, February 02, 2004 1:30 AM Subject: FreeBSD 4.9 + MySQL 4.0 + LinuxThreads I previouslly compiled my own version of MySQLd 4.0.14 on FreeBSD 4.8 with LinuxThreads using Jeremy Zawodny's config/compile options (http://jeremy.zawodny.com/blog/archives/000458.html) and have had no problems. However, I've got 4.9 install with updated ports, etc. and I can't seem to get 4.0.17 to run successfully (or 4.0.14 for that matter) after compiling. I've used the same config options and receive the following errors: InnoDB: Error: pthread_create returned 35 So I tried to compile --without-innodb and got.. 040201 23:59:53 Can't create interrupt-thread (error 35, errno: 4) ktrace shows the following: 18510 mysqld CALL kill(0x484d,0x1f) 18509 mysqld PSIG SIGUSR2 caught handler=0x881ea104 mask=0x40027007 code=0x0 18509 mysqld RET sigsuspend -1 errno 4 Interrupted system call 18509 mysqld CALL sigreturn(0xbfbff410) Here's the info on my /usr/ports/devel/linuxthreads install: linuxthreads-2.2.3_13 I looked at my old 4.8 box, which has been upgraded to 4.9, but I haven't done anything with MySQL on since it was 4.8, and it appears to have the following linux threads version: linuxthreads-2.2.3_11 Has anyone had success compiling MySQL 4.0 + LinuxThreads-2.2.3_13 using Jeremy Zawodny's configuration options on FreeBSD 4.9? Does anyone have any suggestions on where to go from here? Thanks, nickg -- 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]
FreeBSD 4.9 + MySQL 4.0 + LinuxThreads
I previouslly compiled my own version of MySQLd 4.0.14 on FreeBSD 4.8 with LinuxThreads using Jeremy Zawodny's config/compile options (http://jeremy.zawodny.com/blog/archives/000458.html) and have had no problems. However, I've got 4.9 install with updated ports, etc. and I can't seem to get 4.0.17 to run successfully (or 4.0.14 for that matter) after compiling. I've used the same config options and receive the following errors: InnoDB: Error: pthread_create returned 35 So I tried to compile --without-innodb and got.. 040201 23:59:53 Can't create interrupt-thread (error 35, errno: 4) ktrace shows the following: 18510 mysqld CALL kill(0x484d,0x1f) 18509 mysqld PSIG SIGUSR2 caught handler=0x881ea104 mask=0x40027007 code=0x0 18509 mysqld RET sigsuspend -1 errno 4 Interrupted system call 18509 mysqld CALL sigreturn(0xbfbff410) Here's the info on my /usr/ports/devel/linuxthreads install: linuxthreads-2.2.3_13 I looked at my old 4.8 box, which has been upgraded to 4.9, but I haven't done anything with MySQL on since it was 4.8, and it appears to have the following linux threads version: linuxthreads-2.2.3_11 Has anyone had success compiling MySQL 4.0 + LinuxThreads-2.2.3_13 using Jeremy Zawodny's configuration options on FreeBSD 4.9? Does anyone have any suggestions on where to go from here? Thanks, nickg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: C API: undefined reference
http://www.mysql.com/doc/en/mysql_connect.html
This function is deprecated. It is preferable to use
mysql_real_connect() instead.
#includestdio.h
#includemysql.h
int main(void) {
MYSQL mysql;
if(mysql_init(mysql) == NULL) {
fprintf(stderr,Unable to initlize MySQL structure.\n);
return(1);
}
if(mysql_real_connect(mysql,127.0.0.1,user,password, db, 0,
NULL, 0) == NULL) {
fprintf(stderr,Unable to connect to MySQL: %s\n,
mysql_error(mysql));
return(1);
}
return(0);
}
mybox:/home/nickgsu gcc seeLog.c -o seeLog -I/usr/local/mysql/include
-L/usr/local/mysql/lib -lmysqlclient -lz
mybox:/home/nickgsu ./seeLog
Unable to connect to MySQL: Access denied for user: '[EMAIL PROTECTED]'
(Using password: YES)
Good luck,
nickg
-Original Message-
From: hAj [mailto:[EMAIL PROTECTED]
Sent: Monday, November 10, 2003 4:03 PM
To: [EMAIL PROTECTED]
Subject: C API: undefined reference
Hello MySQL pros worldwide,
~/www -cat seeLog.c
#define USE_OLD_FUNCTIONS
#include stdio.h
#include mysql/mysql.h
int main() {
MYSQL mysql;
MYSQL *mysqldb = NULL;
mysqldb = mysql_connect(mysql, geneofcube.net, USERID,
PASSWORD);
return 0;
}
~/www -gcc seeLog.c -o seeLog -I/usr/include -L/usr/lib -lmysqlclient
/tmp/ccEj3tmv.o: In function `main':
/tmp/ccEj3tmv.o(.text+0x2a): undefined reference to `mysql_connect'
collect2: ld returned 1 exit status
~/www -
As shown above, I'm having a problem getting rid of a compilation error
(undefined reference) coming out with a very simple c code (seeLog.c)
which
I wrote for a testing purpose.
Got no I idea what I'm doing wrong or missing here.
I'd appreciate any of your suggestions.
Best,
hAj
--
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]
4.1 final
Any idea when 4.1 will be released as a final? nick marsh [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question
Dear ALL I am Nick, I come from Taiwan company - EC-SERVER~~I have two WEB SERVER to make load balance. Both of the WEB SERVER was installed mysql~~ I used the mount NFS instruction the storage spaces in linux.At the same time,WEB's Data and mysql's data both in NFS paration~modify mysql.server == datadir=/usr/local/mysql/var to datadir=/apache/mysql/varTwo Mysql access the same database at the same time~~but when I proceed the OLTP ~~ database display error message~~Can you tell me??? Why two Mysql can't access the same database ???Do you have any solution about above situation?Thanks very much ~~ Nick System EngineerNick EC-Server.com.,Inc. Tel: 886+2+27006611#607 Fax: 886+2+27006612mail:[EMAIL PROTECTED] 9F-1 , No.380 , Sec.1 , Fu-Shin S. Rd.,1.6, Taipei ,Taiwan.
FW: FreeBSD 4.8 runaway MySQL 4.0.14
I am running the binary installation of MySQL 4.0.14 on FreeBSD 4.8, all MyISAM tables, and after alittle while mysqld will jump to 100% of my CPU and go crazy. I've investigated SHOW INNODB STATUS, and as you can see below, there is nothing happening with Innodb. Infact my Innodb files have not even changed since I last restarted. But for some reason MySQLd uses 100% of my CPU with no queries happening. I tried to run ktrace and this was all I saw: 30488 mysqld CALL gettimeofday(0x2846e288,0) 30488 mysqld RET gettimeofday 0 30488 mysqld CALL poll(0x83b6000,0xc,0) 30488 mysqld RET poll 0 30488 mysqld CALL poll(0x83b6000,0xd,0x126) 30488 mysqld RET poll 1 30488 mysqld CALL gettimeofday(0x2846e288,0) 30488 mysqld RET gettimeofday 0 30488 mysqld CALL poll(0x83b6000,0xd,0x126) 30488 mysqld RET poll 1 With an occasional 30488 mysqld CALL poll(0x83b6000,0xd,0x126) 30488 mysqld PSIG SIGPROF caught handler=0x2842562c mask=0x0 code=0x0 30488 mysqld RET poll 1 30488 mysqld CALL gettimeofday(0x2846e288,0) 30488 mysqld RET gettimeofday 0 30488 mysqld CALL sigreturn(0x83a9db8) 30488 mysqld RET sigreturn JUSTRETURN Has anyone experienced a runway mysqld on FreeBSD before? Is there anything I can do to fix this or solve this problem? Below is show innodb status\g = 030828 14:36:37 INNODB MONITOR OUTPUT = Per second averages calculated from the last 16 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 4, signal count 4 Mutex spin waits 0, rounds 0, OS waits 0 RW-shared spins 6, OS waits 3; RW-excl spins 1, OS waits 1 TRANSACTIONS Trx id counter 0 1280 Purge done for trx's n:o 0 0 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 299134976 MySQL thread id 176926, query id 516226 localhost root show innodb status FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 40 OS file reads, 4 OS file writes, 4 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, used cells 0, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 43892 Log flushed up to 0 43892 Last checkpoint at 0 43892 0 pending log writes, 0 pending chkp writes 9 log i/o's done, 0.00 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 16338600; in additional pool allocated 641280 Buffer pool size 512 Free buffers 493 Database pages 19 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 19, created 0, written 0 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue Main thread id 158370816, state: waiting for server activity Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s END OF INNODB MONITOR OUTPUT Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: FreeBSD 4.8 runaway MySQL 4.0.14
Ken, Thanks for the response. I really wish MySQL AB would compile FreeBSD binaries with LinuxThreads. It's really a pain when you find a bug, the first thing they ask is have you compiled this yourself when in this case you have no choice but to compile it yourself. Maybe Jeremy could compile unofficial but recommended binaries that MySQL AB would support more like official binaries? :) If he's got nothing better to do of course, he already puts in so much time to the MySQL community as is. Nick -Original Message- From: Ken Menzel [mailto:[EMAIL PROTECTED] Sent: Thursday, August 28, 2003 4:14 PM To: Nick Gaugler; [EMAIL PROTECTED] Subject: Re: FreeBSD 4.8 runaway MySQL 4.0.14 Hi Nick, This is due (so I am told) to a problem in FreeBSD threads. I have this happen once in a while also. There have been some threads changes for 4.9 but I am not sure there is any change in this. The common suggestion is to compile with LINUXTHREADS option using the ports tree. This will solve that and give you better performance on a multi CPU system. (cd /usr/ports/databases/mysql41-server; more Makefile ) or see http://jeremy.zawodny.com/blog/archives/000458.html Another option would be to update to FreeBSD 5.1 and use the new threading (not so easy). I personaly hope 4.9 will settle this down some as I don't see the need to go to LINUX Threads for a single proc machine! Best of Luck Ken - Original Message - From: Nick Gaugler [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, August 28, 2003 3:53 PM Subject: FW: FreeBSD 4.8 runaway MySQL 4.0.14 I am running the binary installation of MySQL 4.0.14 on FreeBSD 4.8, all MyISAM tables, and after alittle while mysqld will jump to 100% of my CPU and go crazy. I've investigated SHOW INNODB STATUS, and as you can see below, there is nothing happening with Innodb. Infact my Innodb files have not even changed since I last restarted. But for some reason MySQLd uses 100% of my CPU with no queries happening. I tried to run ktrace and this was all I saw: 30488 mysqld CALL gettimeofday(0x2846e288,0) 30488 mysqld RET gettimeofday 0 30488 mysqld CALL poll(0x83b6000,0xc,0) 30488 mysqld RET poll 0 30488 mysqld CALL poll(0x83b6000,0xd,0x126) 30488 mysqld RET poll 1 30488 mysqld CALL gettimeofday(0x2846e288,0) 30488 mysqld RET gettimeofday 0 30488 mysqld CALL poll(0x83b6000,0xd,0x126) 30488 mysqld RET poll 1 With an occasional 30488 mysqld CALL poll(0x83b6000,0xd,0x126) 30488 mysqld PSIG SIGPROF caught handler=0x2842562c mask=0x0 code=0x0 30488 mysqld RET poll 1 30488 mysqld CALL gettimeofday(0x2846e288,0) 30488 mysqld RET gettimeofday 0 30488 mysqld CALL sigreturn(0x83a9db8) 30488 mysqld RET sigreturn JUSTRETURN Has anyone experienced a runway mysqld on FreeBSD before? Is there anything I can do to fix this or solve this problem? Below is show innodb status\g = 030828 14:36:37 INNODB MONITOR OUTPUT = Per second averages calculated from the last 16 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 4, signal count 4 Mutex spin waits 0, rounds 0, OS waits 0 RW-shared spins 6, OS waits 3; RW-excl spins 1, OS waits 1 TRANSACTIONS Trx id counter 0 1280 Purge done for trx's n:o 0 0 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 299134976 MySQL thread id 176926, query id 516226 localhost root show innodb status FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 40 OS file reads, 4 OS file writes, 4 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, used cells 0, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 43892 Log flushed up to 0 43892 Last checkpoint at 0 43892 0 pending log writes, 0 pending chkp writes 9 log i/o's done, 0.00 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 16338600; in additional pool allocated 641280 Buffer pool size 512 Free buffers 493 Database pages 19 Modified db pages 0 Pending reads 0 Pending writes
SQL Q: Concatenate multiple rows on same column.
I have a concatenation problem and I was wondering if somebody might be able to offer some help :-) I have the following table structure holding product long descriptions: Part No (pn)Sequence (seq) Long Description (long_desc) --- --- HL1450 10 This is part of a long description and HL1450 20 it overlaps onto several lines. I'm HL1450 30 having difficulty writing the SQL that HL1450 40 is needed to make this work... I need to write SQL that will give me the following output: Part No Long Description --- HL1450 This is part of a long description and it overlaps onto several lines. I'm having difficulty writing the SQLthat is needed to make this work... (Essentially it a GROUP BY on the part number with an ORDER by on the sequence field). However, I know how to concatenate multiple columns on the same row (using the CONCAT or CONCAT_WS functions) but I'm at a loss over how to concatenate the same column over multiple _rows_ I am aware of the GROUP_CONCAT function, but we are currently running MySQL 4.0.13 (this functionality is only available on a vers 4.1) If any one has a suggestion as how I should go about this one I would really appreciate it. Kind regards, Nick -- Nick Heppleston 07989 581766 | [EMAIL PROTECTED] The Funky PC - http://www.thefunkypc.biz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT with 1,000,000 ROWS
Hello, I have aproximately 1,000,000 rows and I would like to do some query. The first one is to get the number of row so I do : mysql SELECT COUNT(*) FROM `Log`; +--+ | COUNT(*) | +--+ | 969129 | +--+ 1 row in set (0.00 sec) MyISAM tables keep a specific count of the number of rows in the table, that is why this query is extremely fast. mysql SELECT COUNT(*) AS `Nb` FROM `Log` WHERE `ID` = 49; ++ | Nb | ++ | 969129 | ++ 1 row in set (1 min 20.99 sec) This query is slow, presumably, because MySQL must read all 969129 rows off of the disk and count them, which will take some time depending on the size of the rows and the speed of the system. But like you can see it, it take a long with the WHERE clause. I use Pentium III at 650 Mhz with 48 Mb of ram. I think that the probleme come from the computer but I'm not shure (I need more RAM ?). Yes, your ram will make a difference because the file system will do caching. You may want to read up on Indexes in MySQL and see how they are used to optimize queries and how they can also be cached in the key_buffer. http://www.mysql.com/doc/en/MySQL_indexes.html Thanks in advance. -- Arno nickg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
3.23.56 Replication Bug
I know the replication method is different in MySQL 4.0 then MySQL
3.23.x, but I have a bug that causes problems. The following query will
cause MySQL's logic to not properly read any of the following my.cnf
commands on slave servers:
replicate-wild-do-table
replicate-wild-ignore-table
replicate-ignore-table
I am sure it would make any of the match commands fail as well. This
query caused replication to stop on 2 of my slaves because it was in a
database that was not made to be replicated on the slave.
Below is the query:
UPDATE phpbb_search_results
SET search_id = 1929162862, search_array =
'a:7:{s:14:search_results;s:28:521, 577, 592, 593, 597,
598;s:17:total_
match_count;i:6;s:12:split_search;N;s:7:sort_by;i:0;s:8:sort_dir;
s:4:DESC;s:12:show_results;s:6:topics;s:12:return_chars;i:200;}
'
WHERE session_id =
'b85542aa0e83fdc6ee306e82ce1a6ad5''
Below is the error log entry:
030803 16:24:33 Slave: connected to master
'[EMAIL PROTECTED]:3306', replication started in log
'blue-bin.013' at position 72475077
030803 16:24:33 Slave: did not get the expected error running query
from master - expected: 'Duplicate entry '%-.64s' for key %d' (1062),
got
'no error' (0)
030803 16:24:33 Slave: error running query 'UPDATE
phpbb_search_results
SET search_id = 1929162862, search_array =
'a:7:{s:14:search_results;s:28:521, 577, 592, 593, 597,
598;s:17:total_
match_count;i:6;s:12:split_search;N;s:7:sort_by;i:0;s:8:sort_dir;
s:4:DESC;s:12:show_results;s:6:topics;s:12:return_chars;i:200;}
'
WHERE session_id =
'b85542aa0e83fdc6ee306e82ce1a6ad5''
030803 16:24:33 Error running query, slave aborted. Fix the problem,
and re-start the slave thread with mysqladmin start-slave. We stopped
a
t log 'blue-bin.013' position 72475077
030803 16:24:33 Slave thread exiting, replication stopped in log
'blue-bin.013' at position 72475077
Below is the binlog entry, keep in mind, it will ignore all of the
queries after this one, it will just not ignore a query with those
special characters in it:
# at 72475077
#030803 0:35:26 server id 1 Query thread_id=191959
exec_time=0 error_code=1062
use bluecustforum;
SET TIMESTAMP=1059888926;
UPDATE phpbb_search_results
SET search_id = 1929162862, search_array =
'a:7:{s:14:search_results;s:28:521, 577, 592, 593, 597,
598;s:17:total_
match_count;i:6;s:12:split_search;N;s:7:sort_by;i:0;s:8:sort_dir;
s:4:DESC;s:12:show_results;s:6:topics;s:12:return_chars;i:200;}
'
WHERE session_id =
'b85542aa0e83fdc6ee306e82ce1a6ad5';
# at 72475467
#030803 0:35:29 server id 1 Query thread_id=191960
exec_time=0 error_code=0
SET TIMESTAMP=1059888929;
UPDATE phpbb_topics
SET topic_views = topic_views + 1
WHERE topic_id = 18;
# at 72475580
#030803 0:35:34 server id 1 Query thread_id=191961
exec_time=0 error_code=0
SET TIMESTAMP=1059888934;
UPDATE phpbb_sessions
SET session_time =
1059888934, session_page = 9
WHERE session_id =
'b85542aa0e83fdc6ee306e82ce1a6ad5';
# at 72475755
#030803 0:35:34 server id 1 Query thread_id=191961
exec_time=0 error_code=0
SET TIMESTAMP=1059888934;
UPDATE phpbb_users
SET
user_session_time = 1059888934, user_session_page = 9
WHERE user_id =
8;
Below is the version info from mysqlbug
VERSION=3.23.56
COMPILATION_COMMENT=Official MySQL RPM
BUGmysql=[EMAIL PROTECTED]
# This is set by configure
COMP_ENV_INFO=CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium'
CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -feli
de-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS=''
CONFIGURE_LINE=./configure '--disable-shared'
'--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static'
'--without-berkeley-db'
'--without-innodb' '--enable-assembler' '--enable-local-infile'
'--with-mysqld-user=mysql'
'--with-unix-socket-path=/var/lib/mysql/mysql.sock'
'--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr'
'--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share'
'--locals
tatedir=/var/lib/mysql' '--infodir=/usr/share/info'
'--includedir=/usr/include' '--mandir=/usr/share/man'
'--with-comment=Official MySQL RPM' '
CC=gcc' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6
-fno-omit-frame-pointer -felide-constructors
-fno-exceptions
-fno-rtti -mpentium' 'CXX=gcc'
nickg
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running mysql (complete newbie)
Hi guys/gals, the problem is directory perms but not in the tmp. And yes
you should have my.cnf in your /etc (can change this location but cant
remember where), and there are sample cnf files in the support-files
directory of mysql.
As far as the directory issue goes simply run:
chown -R mysql data
chgrp -R mysql data
in the mysql directory. Obviously you need to have a mysql user, and
this lets that user, and only that user write to the data directory, or
even enter it for that matter (besides root of course).
HTH!
-Nick
On Wed, 2003-07-30 at 01:19, Peter Bradley wrote:
Hi Sanya
Don't appear to have a file called my.cnf anywhere on my system. Should
I have? If so how should it be formatted, and what should be in it?
Peter
On Wed, 2003-07-30 at 18:42, Sanya Shaik wrote:
You need to set the sock in usr/local/mysql/data/my.cnf to /tmp/mysql.sock
Peter Bradley [EMAIL PROTECTED] wrote:
Hi guys and gals,
I'm a complete newbie to mysql, so please bear with me.
Today I downloaded mysql4.0 binary distribution and installed it on my
SuSE Linux 8.1 box. I've unzipped it and put it in:
/usr/local/mysql-standard-4.0.14-pc-linux-i686
and I've created a symbolic link to give me /usr/local/mysql
I then ran scripts/mysql_install_db (as root). The output was:
=
|
|linux:/usr/local/mysql # ./scripts/mysql_install_db
|Installing all prepared tables
|030730 4:24:13 ./bin/mysqld: Shutdown Complete
|
|
|To start mysqld at boot time you have to copy
|support-files/mysql.server
|to the right place for your system
|
|PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
|This is done with:
|./bin/mysqladmin -u root password 'new-password'
|./bin/mysqladmin -u root -h linux password 'new-password'
|See the manual for more instructions.
|
|NOTE: If you are upgrading from a MySQL = 3.22.10 you should run
|the ./bin/mysql_fix_privilege_tables. Otherwise you will not be
|able to use the new GRANT command!
|
|You can start the MySQL daemon with:
|cd . ; ./bin/mysqld_safe
|
|You can test the MySQL daemon with the benchmarks in the 'sql-bench'
|directory:
|cd sql-bench ; perl run-all-tests
|
|Please report any problems with the ./bin/mysqlbug script!
|
|The latest information about MySQL is available on the web at
|http://www.mysql.com
|Support MySQL by buying support/licenses at https://order.mysql.com
|
|linux:/usr/local/mysql #
|
===
I then tried to start mysql as a normal user and got:
|
|[EMAIL PROTECTED]:/usr/local/mysql bin/safe_mysqld --log
|[1] 2693
|[EMAIL PROTECTED]:/usr/local/mysql Starting mysqld daemon with databases
|from /usr/local/mysql/data
|bin/safe_mysqld: line 296: /usr/local/mysql/data/linux.err: Permission
|denied
|rm: cannot remove `/tmp/mysql.sock': Operation not permitted
|bin/safe_mysqld: line 1: /usr/local/mysql/data/linux.err: Permission
|denied
|tee: /usr/local/mysql/data/linux.err: Permission denied
|030730 04:27:40 mysqld ended
|tee: /usr/local/mysql/data/linux.err: Permission denied
|
|==
So I tried as root and got:
|
|linux:/usr/local/mysql # ./bin/safe_mysqld --log
|[1] 2740
|linux:/usr/local/mysql # Starting mysqld daemon with databases from
|/usr/local/mysql/data
|030730 04:30:05 mysqld ended
|
I did check to see if mysqld was running, but of course it wasn't. I
also tried the perl script run-all-tests, but it just reported mysql
wasn't running ('Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (2)' when connecting to
DBI:mysql:database=test;host=test;host=localhost with user: '' password:
'')
Can anyone help?
Thanks
Peter
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Really slow shutdown with Innodb, db not accessible?
For the last four hours or so, I've been waiting for MySQL (4.0.12 on W2K) to complete a shutdown. The fast shutdown flag is not set (innodb_fast_shutdown=0), so I assume it is doing a purge and merge... but in the meantime, I don't have any access to the server -- clients simply can't connect. This is a real problem, since it renders the database useless for a long period of time. My Innodb table is about 15 GB and probably has about 10 million records in various tables. When the darn thing finally shuts down, I'll restart with fast shutdown on, but I'm wondering how foolish it would be to kill the process, given that Innodb should then do a crash repair. Would the crash repair take longer than what it's doing now? Would the server be inaccessible as it is now? Besides enabling fast shutdown, what else will help avoid this kind of thing in the future? Thanks for any info... -- Nick Arnett Phone/fax: (408) 904-7198 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Basic Database Design Question
This is, as you guess, a very basic issue for relational databases. You never want to put multiple entries in one column, as you describe it. That's the scenario in which you create another table and use a key to join the two. For your project, you should have a column in the nms_apps table that uniquely identifies it -- the primary key, usually. You'll want to create a table, perhaps called 'user,' containing the 'used_by' information. That table would have, at a minimum, a column for the nms_apps key and a column that contains the user information. Then to find out who uses a given application, the query would be along these lines (this uses the column 'app_id' as the key: SELECT used_by FROM nms_apps, user WHERE nms_apps.app_id = user.app_id AND app_name = Application Foo Hope that helps. If you grasp this, you'll have the basic idea of relational data. Nick -- Nick Arnett Phone/fax: (408) 904-7198 [EMAIL PROTECTED] -Original Message- From: James Walters [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 29, 2003 10:42 AM To: [EMAIL PROTECTED] Subject: Basic Database Design Question Hello, DB novice checking in here with a basic design question. I have a table called 'nms_apps' which stores information about all of our applications which we have developed/maintained for our client. One column which I would like to use is called 'used_by', which would store information about which business sections (Financial Management Branch, Human Resources Branch, etc.) use a particular application. Often times more than one section use a particular application. My question is this: How do you handle multiple entries in one column? Is it acceptable to more than one value in a column for one row? If not, what is the best way to design that in the table? Have multiple 'used_by' columns? 'used_by_1', 'used_by_2', 'used_by_3', etc.? Thanks in advance, Testudo __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Really slow shutdown with Innodb, db not accessible?
-Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 29, 2003 12:58 PM crash recovery is usually much faster than purge and merge. Killing the mysqld process is a legal (and the fastest :)) way of shutting down InnoDB. That's good to hear. W2K tells me I don't have permission to kill the process, despite having all admin privileges, so I'll look into that now. Soon, this database will move to Linux or BSD, I hope. Why did you set fast_shutdown=0? I'm asking myself the same question... ;-) I really don't remember. The last time I changed the config was when I started using Innodb, four or five months ago. Don't know what the heck I was thinking. By the way, I am not sure the setting really affects the variable value at all, since in versions 4.0.15 there was a bug that it was specified as a NO_ARG parameter. I noticed some of your other messages about that. It's probably time for me to update. Thanks very much. I really appreciate the speed with which you respond (not just to my messages, I read the list regularly). Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Help...
After some searching around different books/manuals/google I still can't seem to figure out how do to this. What I have is a table with 4 cols (task, resource, department, priority) and what I want to do is be able to select distinct resources and list what their highest priority is. In other words, if a resource is in a high priority task and a low priority task, I only want to show the high priority task. Thanks for the help! -Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Help...
I looked at the group by option already and I dont think it will do what I need it to do. I say this because it will only group things in the priority/task/whatever but that still leaves options for duplicate resources. Yes, it would get rid of the dup. resources per priority, but not for the entire table. I really want to group by the resource, but still have all the info available for what ever the highest priority task it is in. -Nick Btw, all the fields are varchars with the priorities being Hi, Medium, Low, but I could really care less on what particular priority it is atm. ---Original Message- --From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] --Sent: Thursday, July 24, 2003 12:53 PM --To: [EMAIL PROTECTED] --Subject: SQL Help... -- --After some searching around different books/manuals/google I still can't --seem to figure out how do to this. What I have is a table with 4 cols --(task, resource, department, priority) and what I want to do is be able --to --select distinct resources and list what their highest priority is. --In other words, if a resource is in a high priority task and a low --priority task, I only want to show the high priority task. Take a look at GROUP BY HAVING and Count(*) at mysql.com Something like SELECT resource, task, priority GROUP BY priority, task having priority what you define as task I have no idea what your column types are and what data is contained so please excuse this guess. -- --Thanks for the help! ---Nick -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Help...
That works great. =D Knew it shouldn't be that difficult, thanks a bunch. And it actually works with the Priorities being in text form to (low, med, hi). -Nick At 02:52 PM 7/24/2003, you wrote: After some searching around different books/manuals/google I still can't seem to figure out how do to this. What I have is a table with 4 cols (task, resource, department, priority) and what I want to do is be able to select distinct resources and list what their highest priority is. In other words, if a resource is in a high priority task and a low priority task, I only want to show the high priority task. Thanks for the help! -Nick Nick, You mean something like this: select resource, Max(Concat(priority, '=', Resource)) ResourcePriority group by Resource This will work with priority 1 through 9. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL Help...
Yep, saw that and did just as you stated :) Nick, As you stated, your priority field datatype is varchar, with possible values Hi, Medium and Low, as opposed to being integers. The use of max function, as suggested by some colleagues without knowing exactly the datatype would work correctly only on columns of datatype integer. In your case, for textual columns, lexicographic (dictionary) ordering will be used in computing function max, and Medium would win the competition, instead of Hi -- which actually has the lowest ranking in the lexicographic ordering. Perhaps you could use the CASE WHEN ... constructs to map your textual priority into numeric (integer) values (e.g., L - 1, M - 2, H - 3), and then apply the max function to the integer values to get correct results. Best regards, Lin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, July 24, 2003 4:41 PM To: Dathan Vance Pattishall Cc: [EMAIL PROTECTED] Subject: RE: SQL Help... I looked at the group by option already and I dont think it will do what I need it to do. I say this because it will only group things in the priority/task/whatever but that still leaves options for duplicate resources. Yes, it would get rid of the dup. resources per priority, but not for the entire table. I really want to group by the resource, but still have all the info available for what ever the highest priority task it is in. -Nick Btw, all the fields are varchars with the priorities being Hi, Medium, Low, but I could really care less on what particular priority it is atm. ---Original Message- --From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] --Sent: Thursday, July 24, 2003 12:53 PM --To: [EMAIL PROTECTED] --Subject: SQL Help... -- --After some searching around different books/manuals/google I still can't --seem to figure out how do to this. What I have is a table with 4 cols --(task, resource, department, priority) and what I want to do is be able --to --select distinct resources and list what their highest priority is. --In other words, if a resource is in a high priority task and a low --priority task, I only want to show the high priority task. Take a look at GROUP BY HAVING and Count(*) at mysql.com Something like SELECT resource, task, priority GROUP BY priority, task having priority what you define as task I have no idea what your column types are and what data is contained so please excuse this guess. -- --Thanks for the help! ---Nick -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Inserting HTML page to TEXT fails... syntax error?! (Python MySQLdb)
My hair... I am ready to tear it out. I've been working with Python and the MySQLdb module for a long time, thought I couldn't get snagged by anything, but today I just can't seem to persuade the blasted thing to let me stick an HTML document (a string) into a TEXT column. I'm getting SQL syntax errors, as though the document isn't properly escaped, even though I'm using substitution so that MySQLdb should be taking care of that. I'm wondering if I'm missing something terribly obvious, because this is dead-simple code. Here's the relevant bit and a couple of surrounding lines. for url in urls: doc = urllib.urlopen(url[0]).read() dbh.execute(INSERT INTO rss_article (body,url) VALUES (%s, %s),(doc, url)) dbh.execute(UPDATE rss_item SET cached = '1' WHERE url = %s,(url,)) It's retrieving the document just fine, resulting in a big ol' string (it really is a string, I checked), but that string just won't go into the database. rss_article is a TEXT column in a MyISAM table. I get an SQL syntax error and MySQL tells me to check the syntax with a snippet from right near the beginning of the HTML, where it has lots of (annoying, I suppose) backslashes, quotes and other stuff that is a pain to encode properly by hand. Any help will be most gratefully accepted. My hair will thank you, too. Nick -- Nick Arnett Phone/fax: (408) 904-7198 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Inserting HTML page to TEXT fails... syntax error?! (Python MySQLdb)
-Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Thursday, July 17, 2003 9:38 PM To: Nick Arnett; [EMAIL PROTECTED] Well, Paul, just knowing you were on the job inspired me and I finally realized the dumb thing I'd done. The list I'm iterating, urls, comes from a single-column MySQL results list, so it's a list of tuples (url,), not strings. That's why I open url[0], rather than just url in the first line inside the loop. I was smart enough to do that, but not smart enough to remember to use url[0], rather than url, in the INSERT statement. So I was trying to insert a tuple, not a string, and thus MySQL barfed. for url in urls: doc = urllib.urlopen(url[0]).read() dbh.execute(INSERT INTO rss_article (body,url) VALUES (%s, %s),(doc, url)) dbh.execute(UPDATE rss_item SET cached = '1' WHERE url = %s,(url,)) - Have you tried this with *short* HTML documents? What happens? This would help you determine whether it's a length-of-data issue. - Let's see an actual error message (at least the first part), and the corresponding text of the document. - What shows up in the server's query log? By the way, what helped me figure this out was switching the column names and values, which changed the string in the error message to )), rather than the start of the HTML doc. That's when I realized the problem was something about url, not the HTML. This one has bitten me before, but it's been a long time... Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
post installation problem
I am a MySQL newbie with a post installation problem. I have Linux Red Hat 9 with MySQL 4.0. After installing MySQL, I can not for the life of me get the mysql daemon to run. My installation directory is /usr/local/mysql but it wants to open a database from /var/lib/mysql. Obviously something is not set right. Any help would be greatly appreciated. Nick Oh, and I can not find ANY log files anywhere (/var/lib or /usr/local/mysql/data). Here are some messages: [EMAIL PROTECTED] mysql]# pwd /usr/local/mysql [EMAIL PROTECTED] mysql]# bin/safe_mysqld --console Starting mysqld daemon with databases from /var/lib/mysql 030710 20:52:14 mysqld ended [EMAIL PROTECTED] mysql]# support-files/mysql.server start Can't execute ./bin/mysqld_safe from dir /var/lib [EMAIL PROTECTED] mysql]# env HOSTNAME=localhost.localdomain TERM=xterm SHELL=/bin/bash HISTSIZE=1000 SSH_CLIENT=216.221.96.219 38781 22 OLDPWD=/usr/local/mysql/bin SSH_TTY=/dev/pts/2 USER=root LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;0 1:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.b tm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31: *.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:* .bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;3 5:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35: LD_LIBRARY_PATH=/usr/local/BerkeleyDB.4.1/lib USERNAME=root MAIL=/var/spool/mail/root PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin :/root/bin INPUTRC=/etc/inputrc PWD=/usr/local/mysql LANG=en_US.UTF-8 SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass SHLVL=1 HOME=/root BASH_ENV=/root/.bashrc LOGNAME=root SSH_CONNECTION=216.221.96.219 38781 192.168.0.2 22 LESSOPEN=|/usr/bin/lesspipe.sh %s G_BROKEN_FILENAMES=1 _=/bin/env
InnoDB: Operating system error number 13
Trying to start mysqld for the first time after a reinstall on Mac OS X gives me this error: 030709 12:53:26 mysqld started 030709 12:53:27 InnoDB: Operating system error number 13 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: Cannot continue operation. 030709 12:53:27 mysqld ended Anyone know what could be causing this? I'm baffled. Thanks, Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB: Operating system error number 13
Yep, that was it, along with what Mark said. On Wednesday, Jul 9, 2003, at 15:10 US/Central, gerald_clark wrote: Nick Boudreau wrote: Trying to start mysqld for the first time after a reinstall on Mac OS X gives me this error: 030709 12:53:26 mysqld started 030709 12:53:27 InnoDB: Operating system error number 13 in a file operation. mysql doesn't have permissions for this file. It is probably owned by root, or its directory is. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: Cannot continue operation. 030709 12:53:27 mysqld ended Anyone know what could be causing this? I'm baffled. Thanks, Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Problems Due to Stupid Newbieness
I've done a lot of stuff to try to get mySQL to work. I'm running Mac OS 10.2.6 and Apache webserver. First, I installed mySQL. No problems there. However, not knowing how everything worked, I used PHPmyAdmin to delete the root user. Yes, I know, stupid. Then, realizing my error with the help of a friend, I repeatedly tried to reinstall mySQL to get rid of those users. Didn't work. I tried uninstalling using an uninstall script from some site (entropy.ch or something) and it still didn't work when I reinstalled. I did not dump the database when I did it. I kept getting some socket error and mysqld would not start up. Finally, I deleted my mySQL user on my computer with hopes of replacing it... but I don't know how. If anyone can help me, I would GREATLY appreciate it. Here's the things I need in the order I need them: 1. How do I create the mySQL user on Mac OS X? 2. How can I delete all that stuff about root users and start anew? 3. What can I do about the socket error? #3 may not be an issue once I fix the others. Please consider helping a poor guy out. Thanks, Nick Boudreau -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replacing text on query..
Hello all. I was wandering if it was possible to do a general replacement of text on a query. What I want to do is for any fields that equal 'false' to be replaced with 0 and any fields that equal 'true' be replaced with 1. Now I know you can do if statements, but I have a whole bunch of fields and don't really want to go down the route on all of the fields. But if I have to I will. Thanks for the advice! -Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replacing text on query..
Thanks for pointing that one out. Unfortunately relplace(table.*, ...) does not work (syntax error) so I'll stil have to go through each field individually. This will be two statements per-field which kinda stinks, but oh well. Thanks again! -Nick On Mon, 2003-06-23 at 11:10, Mike Hillyer wrote: Take a look at the REPLACE() function: http://www.mysql.com/doc/en/String_functions.html#IDX1202 Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Nick Stuart [mailto:[EMAIL PROTECTED] Sent: Monday, June 23, 2003 8:58 AM To: MySQL List Subject: Replacing text on query.. Hello all. I was wandering if it was possible to do a general replacement of text on a query. What I want to do is for any fields that equal 'false' to be replaced with 0 and any fields that equal 'true' be replaced with 1. Now I know you can do if statements, but I have a whole bunch of fields and don't really want to go down the route on all of the fields. But if I have to I will. Thanks for the advice! -Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Insert query
-Original Message- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] Sent: Saturday, May 31, 2003 12:05 PM To: 'Ulterior'; [EMAIL PROTECTED] Subject: RE: Insert query Hi, I would use mediumint rather than int for the ID column (int has support for up to 2.1 Billion records wheras mediumint is up to 8.3 million - more efficient for your data type). If he only has a few million records, why would this be more efficient? Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MYSQL DB PROBLEM
-Original Message- From: Adam Murphy [mailto:[EMAIL PROTECTED] Sent: Saturday, May 31, 2003 10:48 PM To: [EMAIL PROTECTED] Subject: MYSQL DB PROBLEM I am trying to insert a table ibf_posts into a localhost database using the MYSQL control center every time i try to insert that one table i get an error [forum] ERROR 2013: Lost connection to MySQL server during query Are you trying to insert a lot of data at once? If so, that will produce this error if it exceeds a certain size. The solutions are to insert fewer records at once or increase MAX_ALLOWED_PACKET, if I recall the correct variable. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Insert query
-Original Message- From: Ulterior [mailto:[EMAIL PROTECTED] Sent: Saturday, May 31, 2003 7:35 AM To: [EMAIL PROTECTED] Subject: Re: Insert query sow what would you suggest, Jerry? ( I need a very FAST search on this table's filename field) Ulterior Don't use varchar unless you absolutely have to, that should help. Jerry - Original Message - From: Ulterior [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, May 31, 2003 3:13 PM Subject: Insert query Hi, I have a database table: CREATE TABLE FTPFILE ( ID int(11) NOT NULL auto_increment, FTPSITEID int(11) DEFAULT '0' NOT NULL, FILENAME varchar(254) DEFAULT 'Not defined' NOT NULL, FILEPATH varchar(254) DEFAULT 'Not defined' NOT NULL, FILEEXTN varchar(3) DEFAULT '---' NOT NULL, FILESIZE int(11) NOT NULL, FILEDATE datetime DEFAULT '-00-00 00:00:00' NOT NULL, PRIMARY KEY (ID) ); when this table reaches 1 records, insert queries are very slow, aproximately 2 records a second. And worst of all mysql-ntd.exe reaches 99% of my processor timing. I am using native mysql C API mysql_query() func for inserting data. Is there any way to speedup insert querys to this table? ( I am planning to have at least 2-5 million entries in this table ) Your VARCHAR columns can become CHAR columns, which should help a lot. But CHAR columns can't be longer than 255, so you're about at the limit. Are you locking the table before inserting, then using the multiple insert syntax? Those should help if you're not doing them, but I don't know what it is in the C API. How many indexes do you have? If you reduce the number of indexes, insertions will go faster, but that might slow down some of your queries, of course. If you're inserting quite a few at a time, you might disable indexing, do the inserts, then allow the indexing to happen all at once. For a large number of inserts, you might also try writing them to a file, then loading it. That's much faster for really large numbers of inserts, and you can also suspend indexing as I mentioned. I use the latter for building externally generated text indexes (frequency tables and such), generating a couple of million records at a shot. It made an enormous difference. But I'm working in Python and can't help you with the C API, as I said. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why doesn't this query work?
-Original Message- From: Mikey [mailto:[EMAIL PROTECTED] Sent: Friday, May 30, 2003 11:53 AM To: [EMAIL PROTECTED] Subject: Why doesn't this query work? OK, first of all thanks for the pointers, however, the query I now have doesn't seem to work. If I run the query up until pricelevel IN (1, 2, 3, 4, 5) it returns a large result set (the clauses are fairly inclusive), however, when I add in the rest of the query no results are returned. Any ideas? This is just a guess, but perhaps you are comparing INTs to STRINGs with that last bit, in which case you'd want to change the column type for pricelevel or put quotes around the numbers in the query? I hit that problem all the time using 1 and 0 as Booleans in an ENUM column. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem starting mysql server
-Original Message- From: Rehaz Golamnobee [mailto:[EMAIL PROTECTED] Sent: Thursday, May 29, 2003 3:41 AM ... I have just upgraded my MySQL from version 3.23 to 4.0.13. However I cannot start the server. When I type mysqld_safe I get the following : [1] 1730 Linux:/# starting mysqld-max daemon with databases from /var/lib/mysql 030529 11:10:51 mysqld ended [1]+ Done mysqld_safe Sounds like it's never starting up at all, which explains why you can't connect to it. First thing I'd do is check your config file. Are you using the same config file as you did with 3.23? If so, you almost surely need to update it to suit 4.x, working from the examples that came with it. I don't recall details, but I'm quite sure that there are variable settings that will cause this behavior. You might first try one of the generic sample config files that came with your new version, just to see if it'll start up properly. Then try modifying one variable at a time, restarting after each. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Pre parsing
You could pre-pend EXPLAIN and see if it generates an error. -- Nick Arnett Phone/fax: (408) 904-7198 [EMAIL PROTECTED] -Original Message- From: Jerry [mailto:[EMAIL PROTECTED] Sent: Friday, April 04, 2003 5:46 AM To: [EMAIL PROTECTED] Subject: Pre parsing Hi, Anyone know of a way of pre parsing a *.sql file to make sure that it is syntactically correct , opposed to having to up load a file and when it breaks loosing everything after the line with an error on it. Either that or a way of telling it to carry on after finding an error. i.e. using mysql -u user -ppassword my_dB some_file.sql With the Just do it flag ? Cheers Jerry -- 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]
Stopword file format?
I've searched and searched, but I can't find anything that describes the format of a custom stopword file for fulltext indexing in MySQL. Anybody have a pointer or a description of the format? Nick -- Nick Arnett Phone/fax: (408) 904-7198 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext search -- no wildcards in phrases?
It appears to me that fulltext phrase searches cannot include wildcards. For example, I would expect app* serv* to match application server, application services, etc. But it returns no results, so I'm having to run each variation separately. Can anyone confirm that wildcards, indeed, can't be used in phrase searches. I'm doing these in Boolean mode because I need exact counts of occurrences. This is on MySQL-4.0.12-nt. Nick -- Nick Arnett Phone/fax: (408) 904-7198 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Speed of SELECT ... LIMIT #,#?
-Original Message- From: Joseph Bueno [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 1:36 AM .. If you want to speed it up, you have to make it use an index. You need to add a WHERE or an ORDER BY clause. Have you tried : SELECT Message_ID, Body FROM Body_etc ORDER BY Message_ID LIMIT N,M Of course, I assume that Message_ID is indexed ;) Message_ID is the primary key. But your suggestion doesn't help. If anything, it is slower. However, I think I've figured out the right way to do this -- use a server-side cursor. I can completely get rid of the need for a LIMIT in the SELECT statement. I've never used server-side cursors before, so I am a bit surprised to see that even when I do a SELECT for all 1.5 million records, MySQL's memory usage doesn't increase a bit above where it was when I was doing the same queries using a normal cursor. All I have to do is figure out how many records I can safely insert at one shot, which is not a problem. Just noticed something odd, though, with the MySQLdb SSCursor. When close() is called, it does a fetchall(), getting any records that you hadn't retrieved, trying to load all of them into memory. It's actually calling nextset(), even though MySQL doesn't support multiple result sets. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
