OT: Strategy for large resultsets
Hi! Does anybody have any thoughts regarding large result sets that should be sent to a client using http or similar stateless protocol. I have a situation where I need to be able to return a large result set and where I know that either the client or possible the server may fail due to resource limitations, out of memory etc. The result set will be fetched from a database, Java objects will be created in a collection, the collection will be returned through a network to a client. This collection may be to large so either the server or the client will fail. I can limit the number of values returned, but how should the problem be handled when all values must be returned? My thought is something along the lines of this: 1 If the result set is to large, chunk it into smaller parts. Return a object to the client with the collection and a info part containing the total number of values, the chunk size and next row number. 2 The client request next chunk of data from next row number. 3 Repeat step 2 until the client stops requesting data, i.e. has received all data wanted. I assume that somebody has solved this issue and in hoping the he or she even want to share the conceptual idea with the rest of us. Since this is of topic, please consider replying only to me. /Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Single vs Multiple primary keys
On Sunday 15 May 2005 20:31, Dan Bolser wrote: You must mean a multipart primary key with three parts :) or multiple-column indexes That is what I would do (use a multiple-column index (primary key) - its kinda based on opinion, but I think you should let the real data be the primary key where appropriate, and avoid artificial 'auto_increment' unless they are specifically useful or necessary in your situation. I.e build the database around the data, not the other way round :) That is just my design preference though. Not sure about performance problems, but you get two 'indexes' for free with one multipart primary key with three parts (so the order of the parts is significant (depending on your application)). I would advocate quite the opposite. The data is the data: primary/foreign keys are data about the database. You should always separate the two. For instance, the foreign key values used in a junction table, used to manage many-to-many relationships, are _simply_ foreign keys; the need for their presence in that particular table has more to do with normalisation and good database design than anything about the actual data in the real world. By all means, build the database AROUND the data, but don't actually USE the data to build the database. You can never really guarantee the uniqueness (or availability) of the data that you select for your primary key when you use _real_ data. A classic example, is where someone is using National Insurance numbers for employees an Employee database as the primary key for each employee - what happens if you suddenly start hiring foreign contractors, where no such data exists? Do you start inventing false data, just to satisfy your need for a foreign key? If you'd used auto-increment fields, the problem wouldn't arise. Furthermore, auto_increments are just integers: there is very little overhead involved in handling them. Real data is usually either more complex, or is apt to become so at some point in the future. Daniel Walker -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Php/Mysql problem - undiefined method mysql_connect()
Hello everyone, I've installed mysql server on my fedora box and it works great when i use the console =) But, i want my php sources to work with these bases but it doesn't work =( In fact it seems that the function mysql_connect() cannot be called, here is the error on the web page : Fatal error: Call to undefined function: mysql_connect() in /var/www/html/TechUnix/include/connexion.php on line 10 So i checked google and found out that a special rpm was needed .. so i've looked for it and when i try to install it, it tells me that : [EMAIL PROTECTED] dl]# rpm -ivh php-mysql-4.3.12-alt0.cvs20050406.i586.rpm warning: php-mysql-4.3.12-alt0.cvs20050406.i586.rpm: V3 DSA signature: NOKEY, key ID 68e31e54 error: Failed dependencies: libMySQL is needed by php-mysql-4.3.12-alt0.cvs20050406.i586 php-libs = 1:4.3.12-alt0.cvs20050406 is needed by php-mysql-4.3.12-alt0.cvs20050406.i586 libmysqlclient.so.12 is needed by php-mysql-4.3.12-alt0.cvs20050406.i586 So i tried : [EMAIL PROTECTED] dl]# rpm -ivh libMySQL-3.23.55-alt1.i586.rpm warning: libMySQL-3.23.55-alt1.i586.rpm: V3 DSA signature: NOKEY, key ID eac91ca0 error: Failed dependencies: /sbin/postun_ldconfig is needed by libMySQL-3.23.55-alt1.i586 :( the only thing i know is [EMAIL PROTECTED] dl]# rpm -qa |grep mysql mysql-3.23.58-13 libdbi-dbd-mysql-0.6.5-9 mysql-devel-3.23.58-13 mysql-server-3.23.58-16.FC3.1 [EMAIL PROTECTED] dl]# I'm quite a newby and do not really understand how i can install this pakage if someone could help ...^^ (I run a fedora Core 3)
Need help in running MySql in PHP script
Hi, Iam using mysql 5.04 , php 5.0 with IIS 5.0 webserver and written some scripts and executed on IE webbrowser but getting an fatal error undefined function mysql_connect() in the code kindly tell me where iam going wrong and also tell the mysql username how to find it out becoz when Iam opening the Mysql from start button it only asks the password and doesnot prompt for an username tell the porcedure to create a new user an tell me how to identify the current user in Mysql. Is there any special code to be written in .ini file of php to link Mysql with php to run the Mysql code in php scripts,if so please tell the code required in detail. Any sort of help is welcome. Thanks Sreedhar __ 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: Need help in running MySql in PHP script
It seems like you got the same problem' as me : Php (? ) do not have the librairies include, and you need to run a kink of daemon in order to make t works. Under linux, it is just a question of the good rpm to install (for me it was php-mysql-4.3.9-3.i386.rpm). Under windows, you'll need to install some kind of bridge whose name begins with php-mysql*. Check weather the php.ini has got the extension ligne uncommented ... Hope it helps -Message d'origine- De : madderla sreedhar [mailto:[EMAIL PROTECTED] Envoyé : lundi 16 mai 2005 14:52 À : sql firm Objet : Need help in running MySql in PHP script Hi, Iam using mysql 5.04 , php 5.0 with IIS 5.0 webserver and written some scripts and executed on IE webbrowser but getting an fatal error undefined function mysql_connect() in the code kindly tell me where iam going wrong and also tell the mysql username how to find it out becoz when Iam opening the Mysql from start button it only asks the password and doesnot prompt for an username tell the porcedure to create a new user an tell me how to identify the current user in Mysql. Is there any special code to be written in .ini file of php to link Mysql with php to run the Mysql code in php scripts,if so please tell the code required in detail. Any sort of help is welcome. Thanks Sreedhar __ 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem connecting to 4.1 server
Yes, the mysql.exe is the same version - I installed version 4.1.10 of
the server (and binaries) from the Windows executable
myself, about two months ago.
I've tried setting MYSQL_SECURE_AUTH to both true and false, to see if
it solves the problem, but it doesn't.
Not sure what else I can try. Any other ideas?
Gleb Paharenko wrote:
Hello.
Are you sure that mysql.exe is the same version as the server?
Check if you're able to connect to 3.23.xx server using API. What value
does your MYSQL_SECURE_AUTH option have? See:
http://dev.mysql.com/doc/mysql/en/mysql-options.html
Nicholas Watmough [EMAIL PROTECTED] wrote:
I am have trouble connecting to the MySQL server 4.1 using the API,
though I have no trouble connecting using the same parameters from the
command line mysql.exe.
I'm using server version 4.1.10 on Windows Server 2003, which was
started with --old-password (to ensure back compatibility with legacy
PHP 4 applications). As a result my mysql server uses the older style,
16-bit password hashing.
I'm using mysql_real_connect, and linking with the libmysql.lib that
ships with ver 4.1.10.
When I connect using mysql_real_connect() to a database with a password,
it fails, and returns a bad handshake error.
However, when I connect using mysql_real_connect() to a database with no
password, it connects without a problem.
This makes me suspect that the client is incorrectly trying to hash with
the newer style 41-bit hashing, against a server using the 16-bit
hashing. Is there any way to tell the client to use older-style hashing?
One of the client flags seems to relate to this,
ie:
#define CLIENT_LONG_PASSWORD 1 /* New more secure passwords */
(mysql_com.h, line 107)
but this should mean that a value of 0 uses short passwords, and I'm
calling mysql_real_connect with a value of 0 for the client flag
argument (though I've also tried with 1 to see if that works).
I have no trouble connecting to the database using the same connection
parameters using the command line mysql.exe.
Do anyone have any idea what I should do?
*** code extract ***
MYSQL my;
mysql_init(my);
if (!mysql_real_connect(my, host, user, password, db, port, NULL, 0))
{
cerr ...
exit(EXIT_FAILURE);
}
else
{
cerr ...
exit(EXIT_SUCCESS);
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: difficulty with UCASE and UPPER
[snip] I want to convert the whole thing to upper case so I can do a case insensitive compare against it. [/snip] If you want to do a case insensitive comparison against the data then there is no need to convert to upper case. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problems starting MySQL with InnoDB tables
In the process I noticed somehow someone had managed to install a slightly different version of mysql vs. mysql-max, so I uninstalled everything msyql related and started over, came right up with InnoDB support! - Kevin Korngut Mr. Magoo JANIMATION INC. www.janimation.com http://www.janimation.com/ Gleb Paharenko said the following on 5/13/2005 4:34 PM: Hello. You have a rather old version and it is built manually. I suggest you to upgrade to the latest release (4.1.11 or if it is impossible, then to 4.0.24). And check if problem is solved after that. kevin korngut [EMAIL PROTECTED] wrote: [-- text/plain, encoding 7bit, charset: ISO-8859-1, 74 lines --] I'm attempting to configure mysql with InnoDB tables and I'm running into problems. And am using the following version of mysqld-max, Ver 4.0.18-Max for suse-linux on i686 (Source distribution) First I uncommented the following lines in /etc/my.cnf: # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 16M innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Then I attempted to start mysqld-max as the user mysql (mysqld-max -u mysql) and got the following: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=131072 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 80383 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x8434638 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Bogus stack limit or frame pointer, fp=0xbffbcea4, stack_bottom=0x7ca35f80, thread_stack=196608, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x7bcf0ff0 is invalid pointer thd-thread_id=0 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. I then tried mysqld-max -u root which created the InnoDB file; however, when I then attempted to start mysql I got the above error (again, running it as the user mysql and not as root) Anyone seen this or happen to know what's going on?
Index Question in MyISAM
I have the following table:
CREATE TABLE `Article_Search` (
`ArticleID` int(11) NOT NULL default '0',
`Content` text NOT NULL,
PRIMARY KEY (`ArticleID`),
FULLTEXT KEY `Content` (`Content`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
This table has several million rows, but I only want to search a subset of
the table. IE:
SELECT * FROM Article_Search WHERE MATCH(Content) AGAINST('rubber
duckies' IN BOOLEAN MODE) AND ArticleID IN (100, 23, 223, 98, 4018, 1452,
91)
The reason I'm specifying a set of ArticleIDs is that I know any hits are
going to be within those articles. So the presence of the IN() clause is
purely there for performance. However, an explain on this Statement shows
that it is using the Full-Text index. Is mysql text-searching the entire
table under the hood, or does it use the PK to reduce the dataset before the
text-search.
Thanks again!
-Dan
Re: Index Question in MyISAM
Dan Salzer [EMAIL PROTECTED] wrote on 16/05/2005 14:36:41:
I have the following table:
CREATE TABLE `Article_Search` (
`ArticleID` int(11) NOT NULL default '0',
`Content` text NOT NULL,
PRIMARY KEY (`ArticleID`),
FULLTEXT KEY `Content` (`Content`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
This table has several million rows, but I only want to search a subset
of
the table. IE:
SELECT * FROM Article_Search WHERE MATCH(Content) AGAINST('rubber
duckies' IN BOOLEAN MODE) AND ArticleID IN (100, 23, 223, 98, 4018,
1452,
91)
The reason I'm specifying a set of ArticleIDs is that I know any hits
are
going to be within those articles. So the presence of the IN() clause is
purely there for performance. However, an explain on this Statement
shows
that it is using the Full-Text index. Is mysql text-searching the entire
table under the hood, or does it use the PK to reduce the dataset before
the
text-search.
MySQL can only use one index at a time. So if it used the ArticleID index
and your IN clkause as the primary index, it would be reduced to doing the
MATCH() the hard way, line by line, in the articles returned by the IN
clause.
On the other hand, you know that the only articles which contain the words
that you specify, it will be doiing a relatively fast lookup in the
FULLTEXT index to get the same set of IDs that you are feeding it, or an
even smaller one (because some even of those will not contained in the
hits). the only case where the simply doing the FUULTEXT search would not
be as fast as you quote would be when one of the separate words rubber
or duckies has a very large number of hits but the phrase does not.
In sum, I wouldn't bother with this optimisation unless your search truens
out in practice to be slow.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: QUERY (TOP)
Any suggestion pl? Seena Blace [EMAIL PROTECTED] wrote:hi, here is table description report1 +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(10) unsigned | | PRI | NULL | auto_increment | | host_id | int(10) unsigned | | MUL | 0 | | | report_rcpt_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_sender_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_ipaddress_id | int(10) unsigned | YES | MUL | NULL | | | time | datetime | | MUL | -00-00 00:00:00 | | | detected_spam | int(10) unsigned | | | 0 | | | detected_virus | int(10) unsigned | | | 0 | | | processed | int(10) unsigned | | | 0 | | | allowed | int(10) unsigned | | | 0 | | | suspected | int(10) unsigned | | | 0 | | | blocked | int(10) unsigned | | | 0 | | | spam | int(10) unsigned | | | 0 | | | virus | int(10) unsigned | | | 0 | | I WANT REPORT LIKE FOLLOWINGS date sender processed spam suspected I want top 10 spam sender each day. QUery i'm using select date_format(time,'%Y-%d-%m'),report_sender_domain_id,processed ,spam from report1 order by spam desc ,report_sender_domain_id,date_format(time,'%Y-%d-%m') limit 10; Please suggest. thanks - Discover Yahoo! Find restaurants, movies, travel more fun for the weekend. Check it out! - Do you Yahoo!? Yahoo! Small Business - Try our new resources site!
output in text file /migration
Hi, I want to migrate 1 table from MYSQL to oracle ? how to do that ? How to get output of table into text file? thanks . - Yahoo! Mail Mobile Take Yahoo! Mail with you! Check email on your mobile phone.
Re: QUERY (TOP)
Seena Blace [EMAIL PROTECTED] wrote on 05/16/2005 10:08:15 AM: Any suggestion pl? Seena Blace [EMAIL PROTECTED] wrote:hi, here is table description report1 +-+--+--+- +-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+- +-++ | id | int(10) unsigned | | PRI | NULL | auto_increment | | host_id | int(10) unsigned | | MUL | 0 | | | report_rcpt_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_sender_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_ipaddress_id | int(10) unsigned | YES | MUL | NULL | | | time | datetime | | MUL | -00-00 00:00:00 | | | detected_spam | int(10) unsigned | | | 0 | | | detected_virus | int(10) unsigned | | | 0 | | | processed | int(10) unsigned | | | 0 | | | allowed | int(10) unsigned | | | 0 | | | suspected | int(10) unsigned | | | 0 | | | blocked | int(10) unsigned | | | 0 | | | spam | int(10) unsigned | | | 0 | | | virus | int(10) unsigned | | | 0 | | I WANT REPORT LIKE FOLLOWINGS date sender processed spam suspected I want top 10 spam sender each day. QUery i'm using select date_format(time,'%Y-%d-%m'),report_sender_domain_id, processed ,spam from report1 order by spam desc ,report_sender_domain_id,date_format(time,'%Y-% d-%m') limit 10; Please suggest. thanks It was the weekend. Not everyone lurks on their days off. Try this select date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,processed ,spam ,suspected from report1 order by spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') limit 10; or if there is more than one entry per spammer per day SELECT date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,sum(processed) as processed ,sum(spam) as spam ,sum(suspected) as suspected FROM report1 GROUP BY date_format(time,'%Y-%d-%m') ,report_sender_domain_id ORDER BY spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') LIMIT 10; That will give you their total stats for each day. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: output in text file /migration
As for your second question, SELECT INTO OUTFILE (making sure mysql user has write privileges in the directory/file you want to write to). MySQL give the example: SELECT a,b,a+b INTO OUTFILE '/tmp/result.text' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n' FROM test_table; ...as producing a CSV file of lines of field-output 'a', 'b' and 'a+b'. On Monday 16 May 2005 15:15, Seena Blace wrote: Hi, I want to migrate 1 table from MYSQL to oracle ? how to do that ? How to get output of table into text file? thanks . - Yahoo! Mail Mobile Take Yahoo! Mail with you! Check email on your mobile phone. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OT: Strategy for large resultsets
I think usually large data sets like yours are returned via ftp or http file download. You would create the file on the server and do something like a redirect to initiate the download. If it's really big and will take a while to create the file, you could email a download link to the client. Doing it either way will allow the client to restart the download if it gets interrupted. And depending on what client they are using, the client may even restart where it left off. The O'Reilly Safari Bookshelf works this way. If I want to download a chapter from a book I have on my bookshelf, I order it and I'll get an email when the pdf file of the chapter is ready. Doing it this way will give you the added advantage of controlling load on your server, like only allowing 5 outstanding report requests at a time. On May 16, 2005, at 4:48 AM, Thomas Sundberg wrote: Hi! Does anybody have any thoughts regarding large result sets that should be sent to a client using http or similar stateless protocol. I have a situation where I need to be able to return a large result set and where I know that either the client or possible the server may fail due to resource limitations, out of memory etc. The result set will be fetched from a database, Java objects will be created in a collection, the collection will be returned through a network to a client. This collection may be to large so either the server or the client will fail. I can limit the number of values returned, but how should the problem be handled when all values must be returned? My thought is something along the lines of this: 1 If the result set is to large, chunk it into smaller parts. Return a object to the client with the collection and a info part containing the total number of values, the chunk size and next row number. 2 The client request next chunk of data from next row number. 3 Repeat step 2 until the client stops requesting data, i.e. has received all data wanted. I assume that somebody has solved this issue and in hoping the he or she even want to share the conceptual idea with the rest of us. Since this is of topic, please consider replying only to me. /Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: QUERY (TOP)
shawn I think 2nd query will return only 10 rows. I want out like followings date domain spam 05/05/05 hotmail.com120 05/05/05 yahoo.com 110 05/05/05 abc.com 99 05/05/05 def.com 80 05/05/05 mnpo.net 79 . like that upto 10 -- 05/06/05 yahoo.com 300 05/06/05 def.com 250 05/06/05 zer.com 200 ..like that upto 10 Each day there are multiple entry from diffrent domains or same domain. I want each day whatever top 10 spam sender domain. thanks [EMAIL PROTECTED] wrote: Seena Blace wrote on 05/16/2005 10:08:15 AM: Any suggestion pl? Seena Blace wrote:hi, here is table description report1 +-+--+--+- +-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+- +-++ | id | int(10) unsigned | | PRI | NULL | auto_increment | | host_id | int(10) unsigned | | MUL | 0 | | | report_rcpt_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_sender_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_ipaddress_id | int(10) unsigned | YES | MUL | NULL | | | time | datetime | | MUL | -00-00 00:00:00 | | | detected_spam | int(10) unsigned | | | 0 | | | detected_virus | int(10) unsigned | | | 0 | | | processed | int(10) unsigned | | | 0 | | | allowed | int(10) unsigned | | | 0 | | | suspected | int(10) unsigned | | | 0 | | | blocked | int(10) unsigned | | | 0 | | | spam | int(10) unsigned | | | 0 | | | virus | int(10) unsigned | | | 0 | | I WANT REPORT LIKE FOLLOWINGS date sender processed spam suspected I want top 10 spam sender each day. QUery i'm using select date_format(time,'%Y-%d-%m'),report_sender_domain_id, processed ,spam from report1 order by spam desc ,report_sender_domain_id,date_format(time,'%Y-% d-%m') limit 10; Please suggest. thanks It was the weekend. Not everyone lurks on their days off. Try this select date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,processed ,spam ,suspected from report1 order by spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') limit 10; or if there is more than one entry per spammer per day SELECT date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,sum(processed) as processed ,sum(spam) as spam ,sum(suspected) as suspected FROM report1 GROUP BY date_format(time,'%Y-%d-%m') ,report_sender_domain_id ORDER BY spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') LIMIT 10; That will give you their total stats for each day. Shawn Green Database Administrator Unimin Corporation - Spruce Pine - Yahoo! Mail Stay connected, organized, and protected. Take the tour
Default or existing value if JOIN ON clause fail?
I am trying to get default value for every ID of JOIN if ON clause were unsucessfull. In my example, I am trying to get english rows, but if they are not available, I would like to get default - estonian. I have two tables ie. table1: id 1 2 3 table2: id - lang - desc 1 - english - EN_A 1 - estonian - ES_A 2 - english - EN_B 2 - estonian - ES_B 3 - estonian - ES_C now if I join these two tables ie.: SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id AND table2.lang ='english' GROUP BY table1.id; I am getting: -- id - id - lang - desc 1 - 1 - english - EN_A 2 - 2 - english - EN_B -- But I would to have default row if english is not available: -- id - id - lang - desc 1 - 1 - english - EN_A 2 - 2 - english - EN_B 3 - 3 - estonian - ES_C -- I have tried with UNION of two JOINTS (one for english, one for estonian) but then I am getting duplicate rows with ID=(1,2) Thanx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Default or existing value if JOIN ON clause fail?
anagorn wrote: I am trying to get default value for every ID of JOIN if ON clause were unsucessfull. In my example, I am trying to get english rows, but if they are not available, I would like to get default - estonian. I have two tables ie. table1: id 1 2 3 table2: id - lang - desc 1 - english - EN_A 1 - estonian - ES_A 2 - english - EN_B 2 - estonian - ES_B 3 - estonian - ES_C now if I join these two tables ie.: SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id AND table2.lang ='english' GROUP BY table1.id; There is no need for GRUP BY in this query. I am getting: -- id - id - lang - desc 1 - 1 - english - EN_A 2 - 2 - english - EN_B -- But I would to have default row if english is not available: -- id - id - lang - desc 1 - 1 - english - EN_A 2 - 2 - english - EN_B 3 - 3 - estonian - ES_C -- I have tried with UNION of two JOINS (one for english, one for estonian) but then I am getting duplicate rows with ID=(1,2) Thanx I think you need to JOIN to table2 twice, once for each language. Try something like: SELECT t1.id, COALESCE(t2a.lang, t2b.lang) AS lang, COALESCE(t2a.desc, t2b.desc) AS desc FROM table1 t1 LEFT JOIN table2 t2a ON t1.id=t2a.id AND t2a.lang='english' LEFT JOIN table2 t2b ON t1.id=t2b.id AND t2b.lang='estonian'; COALESCE returns the first non-NULL value in the list, so you'll get estonian when english is missing. See the manual for more http://dev.mysql.com/doc/mysql/en/comparison-operators.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance of UNION vs alternative
I have four tables. These four tables save information from 4 different user activities...so they save 4 very different data sets. (for example, one activity could be choosing different tracks to make a song, and another activity could be choosing different clipart to make a collage) However, they have columns in common...such as 'title_of_composition', 'date_saved', 'description', and 'approved'. I need to first do a query to list these saved records and display their 'title' and the 'date_saved'. Then when the user clicks on one of these records, I would do another query to display all of the saved data, including activity specific data. I was wondering which method of querying these saved records would yield the best performance. METHOD A: I can stay with these 4 tables and use a UNION : SELECT record_id, title_of_composition, date_saved, 'song' as data_table FROM song WHERE description like '%cat%' UNION SELECT record_id, title_of_composition, date_saved , 'collage' as data_table FROM collage WHERE description like '%cat%' UNION ...etc. Now I will get a list of all records from the 4 tables that mentions 'cat' in the description. I also have enough information (data_table, and record_id) so that I can grab all the details from the right table later. METHOD B: I create another table 'all_compositions' that will hold: 'title_of_composition', 'date_saved', 'description'(...also,'data_table', and 'data_table_record_id') For the other 4 tables, I remove these columns, so they only hold the specific details of the activity. Now when I do the first query, all I have to do is: SELECT record_id, title_of_composition, date_saved, data_table, data_table_record_id WHERE description like '%cat%; Now I will have the necessary information to access the details of each activity when one of these records is selected. PERFORMANCE-wise, which is better? METHOD A using the UNION, or METHOD B, with a slight database modification. DATABASE DESIGN-wise which is better? Thanks! -- -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: QUERY (TOP)
Seena Blace [EMAIL PROTECTED] wrote on 05/16/2005 11:13:48 AM: shawn I think 2nd query will return only 10 rows. I want out like followings date domain spam 05/05/05 hotmail.com120 05/05/05 yahoo.com 110 05/05/05 abc.com 99 05/05/05 def.com 80 05/05/05 mnpo.net 79 . like that upto 10 -- 05/06/05 yahoo.com 300 05/06/05 def.com 250 05/06/05 zer.com 200 ..like that upto 10 Each day there are multiple entry from diffrent domains or same domain. I want each day whatever top 10 spam sender domain. thanks [EMAIL PROTECTED] wrote: Seena Blace wrote on 05/16/2005 10:08:15 AM: Any suggestion pl? Seena Blace wrote:hi, here is table description report1 +-+--+--+- +-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+- +-++ | id | int(10) unsigned | | PRI | NULL | auto_increment | | host_id | int(10) unsigned | | MUL | 0 | | | report_rcpt_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_sender_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_ipaddress_id | int(10) unsigned | YES | MUL | NULL | | | time | datetime | | MUL | -00-00 00:00:00 | | | detected_spam | int(10) unsigned | | | 0 | | | detected_virus | int(10) unsigned | | | 0 | | | processed | int(10) unsigned | | | 0 | | | allowed | int(10) unsigned | | | 0 | | | suspected | int(10) unsigned | | | 0 | | | blocked | int(10) unsigned | | | 0 | | | spam | int(10) unsigned | | | 0 | | | virus | int(10) unsigned | | | 0 | | I WANT REPORT LIKE FOLLOWINGS date sender processed spam suspected I want top 10 spam sender each day. QUery i'm using select date_format(time,'%Y-%d-%m'),report_sender_domain_id, processed ,spam from report1 order by spam desc ,report_sender_domain_id,date_format(time,'%Y-% d-%m') limit 10; Please suggest. thanks It was the weekend. Not everyone lurks on their days off. Try this select date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,processed ,spam ,suspected from report1 order by spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') limit 10; or if there is more than one entry per spammer per day SELECT date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,sum(processed) as processed ,sum(spam) as spam ,sum(suspected) as suspected FROM report1 GROUP BY date_format(time,'%Y-%d-%m') ,report_sender_domain_id ORDER BY spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') LIMIT 10; That will give you their total stats for each day. Shawn Green Database Administrator Unimin Corporation - Spruce Pine - OK, then what you want to do will take two steps and another table (unless you want to write application code) to do with MySQL; First step is to create a table where the intermediate results can be stored. What this table does is number each row for each day. Since it appears that you may want this information frequently (but only need to update it at the end of every day) I will make this a permanent table (not a temporary table). CREATE TABLE spam_stats ( report_date date not null, report_sender_domain_id int unsigned not null, rank int unsigned auto_increment, processed int unsigned, spam int unsigned, suspected int unsigned, PRIMARY KEY (report_date, rank), UNIQUE KEY (report_sender_domain_id, report_date), KEY (rank) ); The UNIQUE key ensures that the same spammer cannot have more than one entry per day while the PRIMARY KEY allows for a groupwize autonumber (each entry per day gets it's own number, rank, starting at 1). The last key is optional but will seriously speed up the returns for the report you wanted. Next we need to populate our new table with the spam report data (filled in from most spam to least spam per domain per day). INSERT spam_stats (report_date, report_sender_domain_id, processed, spam, suspected) SELECT date(`time`) ,report_sender_domain_id ,sum(processed) as processed ,sum(spam) as spam ,sum(suspected) as suspected FROM report1 GROUP BY date(`time`) ,report_sender_domain_id ORDER BY spam desc ,report_sender_domain_id ,date(`time`); What will happen is that the rank column will be automatically filled in for each day's spam statistics. Then to get your top ten list you just run: SELECT * FROM spam_stats WHERE
Re: Need help in running MySql in PHP script
The folks at http://us4.php.net/mailing-lists.php can help you. In PHP 5, MySQL is no longer enabled by default, nor is the MySQL library bundled with PHP. http://us4.php.net/manual/en/faq.databases.php#faq.databases.mysql.php5 kgt CIKALA Frédéric ROSI/SIPROD wrote: It seems like you got the same problem' as me : Php (? ) do not have the librairies include, and you need to run a kink of daemon in order to make t works. Under linux, it is just a question of the good rpm to install (for me it was php-mysql-4.3.9-3.i386.rpm). Under windows, you'll need to install some kind of bridge whose name begins with php-mysql*. Check weather the php.ini has got the extension ligne uncommented ... Hope it helps -Message d'origine- De : madderla sreedhar [mailto:[EMAIL PROTECTED] Envoyé : lundi 16 mai 2005 14:52 À : sql firm Objet : Need help in running MySql in PHP script Hi, Iam using mysql 5.04 , php 5.0 with IIS 5.0 webserver and written some scripts and executed on IE webbrowser but getting an fatal error undefined function mysql_connect() in the code kindly tell me where iam going wrong and also tell the mysql username how to find it out becoz when Iam opening the Mysql from start button it only asks the password and doesnot prompt for an username tell the porcedure to create a new user an tell me how to identify the current user in Mysql. Is there any special code to be written in .ini file of php to link Mysql with php to run the Mysql code in php scripts,if so please tell the code required in detail. Any sort of help is welcome. Thanks Sreedhar __ 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: select count(*) table
select count(*) table; gives me a syntax error in 4.0 and 4.1 so I'm not sure how that worked. Something similar would be select count(*) t; which uses the shortcut alias syntax. It's the same as doing select count(*) as t; Simon Garner wrote: [EMAIL PROTECTED] wrote: I have a curious situation I was hoping someone could shed some light on. mysql select count(*) table; +---+ | table | +---+ | 0 | +---+ 1 row in set (0.00 sec) mysql select count(*) from table; +--+ | count(*) | +--+ |25965 | +--+ 1 row in set (0.00 sec) I found it curious that the first query didn't return an error with there being no 'from', and even more curious that it returned a 0. Is the first query actually legit, and if so, what does the 0 mean? Yes, you can do a select without a table. This allows you to get the values of expressions or functions. E.g. SELECT 1+1 will return 2, and SELECT NOW() will return the current date and time. Your query is selecting COUNT(*) AS table rather than COUNT(*) FROM table. Naturally, without a table, COUNT(*) will return 0. -Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select count(*) table
just give a it try select count(columnname) from table; Eric Bergen [EMAIL PROTECTED] wrote:select count(*) table; gives me a syntax error in 4.0 and 4.1 so I'm not sure how that worked. Something similar would be select count(*) t; which uses the shortcut alias syntax. It's the same as doing select count(*) as t; Simon Garner wrote: [EMAIL PROTECTED] wrote: I have a curious situation I was hoping someone could shed some light on. mysql select count(*) table; +---+ | table | +---+ | 0 | +---+ 1 row in set (0.00 sec) mysql select count(*) from table; +--+ | count(*) | +--+ | 25965 | +--+ 1 row in set (0.00 sec) I found it curious that the first query didn't return an error with there being no 'from', and even more curious that it returned a 0. Is the first query actually legit, and if so, what does the 0 mean? Yes, you can do a select without a table. This allows you to get the values of expressions or functions. E.g. SELECT 1+1 will return 2, and SELECT NOW() will return the current date and time. Your query is selecting COUNT(*) AS table rather than COUNT(*) FROM table. Naturally, without a table, COUNT(*) will return 0. -Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses.
Re: Efficiently finding a random record
Michael Stassen wrote: For example, if the selected random id is missing, we take the next id we find, like this: SELECT @rand_id:= CAST( 1 + MAX(id)*RAND() AS UNSIGNED) FROM history; SELECT * FROM history WHERE id = @rand_id LIMIT 1; That will have a possibly undesired effect. Records that have gaps in the IDs before them will be twice, three times, etc. (depending on the size of the gap), as likely to be selected as records with no preceding gaps. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficiently finding a random record
Michael Stassen wrote: For example, if the selected random id is missing, we take the next id we find, like this: SELECT @rand_id:= CAST( 1 + MAX(id)*RAND() AS UNSIGNED) FROM history; SELECT * FROM history WHERE id = @rand_id LIMIT 1; That will have a possibly undesired effect. Records that have gaps in the IDs before them will be twice, three times, etc. (depending on the size of the gap), as likely to be selected as records with no preceding gaps. Replace MAX with COUNT and the WHERE clause with an OFFSET and the gap problem should go away... -philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlxml
Hello! The patch which can be found at http://mysql.r18.ru/~bar/myxml/ adds XPATH support into MySQL. It is not an UDF. It is a patch for main MySQL sources. In order to build MySQL with XPATH please do the following: 1. Unpack MySQL source tar ball, say mysql-5.0.4.tar.gz: tar -zxf mysql-5.0.4.tag.gz mysql-5.0.4 directory will appear, containing MySQL sources. 2. cd mysql-5.0.4 3. Unpack mysql-xml.tar.gz: tar -zxf mysql-xml.tar.gz After this step item_xmlfunc.cc and item_xmlfunc.h should appear in sql directory. Make sure they have appeared: ls sql/item_xmlfunc.* 4. Apply xml.diff: patch -p1 xml.diff 5. Run: aclocal autoheader automake autoconf 6. Build MySQL in usuall way, for example: ./confugure --prefix=/usr/local/mysql make make install If everything went wrong, you should have two new functions ExtractValue() and UpdateXML(). Enjoy. mel list_php wrote: Ok, I've been trying to install that function this morning and no luck I downloaded 5.0.4beta , compiled it . everything working fine. Trying the udf_example function (make udf_example.so) no problem. Trying to gcc -shared -o item_xmlfunc.so item_xmlfunc.cc it has been complaining about missing files (my_time.h .) and I managed to solve that by downloading the binary and copying the missing file. All is now in /usr/local/include. I'm trying to complie from /usr/local/mysql-5.0.4-beta/sql, but I don't think there's any path problem as it finds for example my_time.h. But know it finds an error in item_func.h about string2mydecimal which is not defined. Questions: - did anybody succeed to install that function (item_xmlfunc.cc) - any special tip about that? - is there an official repository of headers file wheer I could download the whole *.h files stable? - any solution to the error? thanks, Melanie Here is the whole error output: [EMAIL PROTECTED] sql]# gcc -shared -o item_xmlfunc.so item_xmlfunc.cc In file included from item.h:1381, from mysql_priv.h:457, from item_xmlfunc.cc:22: item_func.h: In member function `virtual my_decimal* Item_func_udf_str::val_decimal(my_decimal*)': item_func.h:968: error: `string2my_decimal' undeclared (first use this function) item_func.h:968: error: (Each undeclared identifier is reported only once for each function it appears in.) item_xmlfunc.cc: At global scope: item_xmlfunc.cc:56: error: use of enum `my_xml_node_type' without previous declaration item_xmlfunc.cc:56: error: ISO C++ forbids declaration of `type' with no type item_xmlfunc.cc: In member function `void Item_nodeset_func::prepare(String*)': item_xmlfunc.cc:167: error: 'class Item' has no member named 'val_nodeset' item_xmlfunc.cc: In member function `virtual Item::Type Item_nodeset_func::type() const': item_xmlfunc.cc:172: error: `XPATH_NODESET' undeclared (first use this function) item_xmlfunc.cc: In member function `virtual String* Item_nodeset_func::val_str(String*)': item_xmlfunc.cc:176: error: `val_nodeset' undeclared (first use this function) item_xmlfunc.cc:188: error: `MY_XML_NODE_TEXT' undeclared (first use this function) item_xmlfunc.cc: In member function `virtual longlong Item_xpath_cast_bool::val_int()': item_xmlfunc.cc:362: error: `XPATH_NODESET' undeclared (first use this function) item_xmlfunc.cc:364: error: 'class Item' has no member named 'val_nodeset' item_xmlfunc.cc: In member function `virtual longlong Item_func_xpath_position::val_int()': item_xmlfunc.cc:408: error: 'class Item' has no member named 'val_nodeset' item_xmlfunc.cc: In member function `virtual longlong Item_func_xpath_count::val_int()': item_xmlfunc.cc:426: error: 'class Item' has no member named 'val_nodeset' item_xmlfunc.cc: In member function `virtual double Item_func_xpath_sum::val_real()': item_xmlfunc.cc:443: error: 'class Item' has no member named 'val_nodeset' item_xmlfunc.cc:458: error: `MY_XML_NODE_TEXT' undeclared (first use this function) item_xmlfunc.cc: In member function `virtual Item::Type Item_nodeset_to_const_comparator::type() const': item_xmlfunc.cc:481: error: `XPATH_NODESET_CMP' undeclared (first use this function) item_xmlfunc.cc: In member function `virtual longlong Item_nodeset_to_const_comparator::val_int()': item_xmlfunc.cc:488: error: 'class Item' has no member named 'val_nodeset' item_xmlfunc.cc:503: error: `MY_XML_NODE_TEXT' undeclared (first use this function) item_xmlfunc.cc: In member function `String* Item_nodeset_func_union::val_nodeset(String*)': item_xmlfunc.cc:528: error: 'class Item' has no member named 'val_nodeset' item_xmlfunc.cc:529: error: 'class Item' has no member named 'val_nodeset' item_xmlfunc.cc: In member function `String* Item_nodeset_func_childbyname::val_nodeset(String*)': item_xmlfunc.cc:569: error: `MY_XML_NODE_TAG' undeclared (first use this function) item_xmlfunc.cc: In member function `String* Item_nodeset_func_descendantbyname::val_nodeset(String*)': item_xmlfunc.cc:592: error: `MY_XML_NODE_TAG' undeclared (first use this
repair table/close table ?????
Hi, Here is what I see. mysql check table report_1; +---+---+--++ | Table | Op| Msg_type | Msg_text | +---+---+--++ | mailtest.report_1 | check | error| record delete-link-chain corrupted | | mailtest.report_1 | check | error| Corrupt| +---+---+--++ 2 rows in set (0.02 sec) Does repair table report_1 command fix? or anything we need to do extra? Apart from this brightmail.din_top_spam warning : 1 clients is using or hasn't closed the table properly status : OK How to close such kind of table? thanks - Yahoo! Mail Mobile Take Yahoo! Mail with you! Check email on your mobile phone.
Re: MySQL 4.1.12 has been released
On Sun, 15 May 2005, Matt Wagner wrote: Hi, A new version of MySQL Community Edition 4.1.12 Open Source database management system has been released. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. How long is it before the Dec OSF downloads are updated usually? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.12 has been released
2005/5/16, Matt Wagner [EMAIL PROTECTED]: Hi, A new version of MySQL Community Edition 4.1.12 Open Source database management system has been released. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. The link to the Mac OS X 10.3 version is out of date. It links to 4.1.11. 4.1.12 is downloadable if this: http://dev.mysql.com/get/Downloads/MySQL-4.1/mysql-standard-4.1.11-apple-darwin7.8.0-powerpc.tar.gz/from/pick is manually changed to: http://dev.mysql.com/get/Downloads/MySQL-4.1/mysql-standard-4.1.12-apple-darwin7.9.0-powerpc.tar.gz/from/pick JP JP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb buffer pool size question
Hi all, I set the innodb_buffer_pool_size=1024M in my.cnf show variables returns . innodb_buffer_pool_size | 1073741824 show innodb status returns -- BUFFER POOL AND MEMORY -- Total memory allocated 1169691576; in additional pool allocated 1041024 Buffer pool size 65536 Free buffers 0 Database pages 65170 Modified db pages 979 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 170494, created 940061, written 2512012 41.96 reads/s, 2.00 creates/s, 0.00 writes/s Buffer pool hit rate 843 / 1000 why is the buffer pool size different?
mysql - Client does not support authentication protocol requested by server
Dear all, I'm mysql newbie. When I try to connect to mysql using php script, it answers: Warning: mysql_connect(): Client does not support authentication protocol requested by server. Consider upgrading MySQL client in /usr/local/www/data-dist/grad-web/email.php on line 18 It talks about line 18 of my email.php script, which is MYSQL_CONNECT($hostname,$username,$password); So how do I set the proper authentication protocol requested by server? MySQL version is 4.1.0-alpha OS is FreeBSD 5.2 Best regards, -- Yuriy Markiv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: innodb buffer pool size question
The buffer pool size value is in 16K pages (from SHOW INNODB STATUS output) -- Partha Dutta, Senior Consultant MySQL Inc, NY, USA, www.mysql.com Are you MySQL certified? www.mysql.com/certification -Original Message- From: Sergei Skarupo [mailto:[EMAIL PROTECTED] Sent: Monday, May 16, 2005 2:18 PM To: Mysql List (E-mail) Subject: innodb buffer pool size question Hi all, I set the innodb_buffer_pool_size=1024M in my.cnf show variables returns . innodb_buffer_pool_size | 1073741824 show innodb status returns -- BUFFER POOL AND MEMORY -- Total memory allocated 1169691576; in additional pool allocated 1041024 Buffer pool size 65536 Free buffers 0 Database pages 65170 Modified db pages 979 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 170494, created 940061, written 2512012 41.96 reads/s, 2.00 creates/s, 0.00 writes/s Buffer pool hit rate 843 / 1000 why is the buffer pool size different? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.12 has been released
On 5/16/05 12:57 PM, Dan Bolser [EMAIL PROTECTED] wrote: A new version of MySQL Community Edition 4.1.12 Open Source database management system has been released. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. How long is it before the Dec OSF downloads are updated usually? Dan, We had to skip Dec OSF for this release due to some unresolved build problems. Sorry. Matt -- Matt Wagner, Production Engineer MySQL AB, www.mysql.com Northfield, MN, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.12 has been released
On 5/16/05 1:04 PM, Jan Pieter Kunst [EMAIL PROTECTED] wrote: A new version of MySQL Community Edition 4.1.12 Open Source database management system has been released. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. The link to the Mac OS X 10.3 version is out of date. It links to 4.1.11. 4.1.12 is downloadable if this: http://dev.mysql.com/get/Downloads/MySQL-4.1/mysql-standard-4.1.11-apple-darw in7.8.0-powerpc.tar.gz/from/pick is manually changed to: http://dev.mysql.com/get/Downloads/MySQL-4.1/mysql-standard-4.1.12-apple-darw in7.9.0-powerpc.tar.gz/from/pick Jan, Yes, sorry about that. The latest update for OS X 10.3 changed the kernel version number again. A request is already in to our web team to update the download page, should be coming up shortly. Matt -- Matt Wagner, Production Engineer MySQL AB, www.mysql.com Northfield, MN, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: innodb buffer pool size question
thanks -Original Message- From: Partha Dutta [mailto:[EMAIL PROTECTED] Sent: Monday, May 16, 2005 11:30 AM To: Sergei Skarupo; 'Mysql List (E-mail)' Subject: RE: innodb buffer pool size question The buffer pool size value is in 16K pages (from SHOW INNODB STATUS output) -- Partha Dutta, Senior Consultant MySQL Inc, NY, USA, www.mysql.com Are you MySQL certified? www.mysql.com/certification -Original Message- From: Sergei Skarupo [mailto:[EMAIL PROTECTED] Sent: Monday, May 16, 2005 2:18 PM To: Mysql List (E-mail) Subject: innodb buffer pool size question Hi all, I set the innodb_buffer_pool_size=1024M in my.cnf show variables returns . innodb_buffer_pool_size | 1073741824 show innodb status returns -- BUFFER POOL AND MEMORY -- Total memory allocated 1169691576; in additional pool allocated 1041024 Buffer pool size 65536 Free buffers 0 Database pages 65170 Modified db pages 979 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 170494, created 940061, written 2512012 41.96 reads/s, 2.00 creates/s, 0.00 writes/s Buffer pool hit rate 843 / 1000 why is the buffer pool size different? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with query
Hello, I have a table where is saved all site´s access: access id year month day weekday hour minute ip Any column has multiple lines, and I have the follow query that returns the amount of access per day of month: SELECT year, month, day, COUNT(*) AS access FROM access WHERE year = 2005 AND month = 5 GROUP BY year, month, day ORDER BY year, month, day Now, I need to do the same query, but for unique access, in other words, with DISTINCT year, month, day, ip. I tryed to use the query: SELECT year, month, day, ip, COUNT(*) AS access FROM access WHERE year = 2005 AND month = 5 GROUP BY year, month, day, ip ORDER BY year, month, day but it returns me several lines of the same day and the amount of access per IP, and I need the amount of access from different IPs. Could anybody help me? Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with query
Hi, look at group by ... with rollup at : http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html Mathias Selon Ronan Lucio [EMAIL PROTECTED]: Hello, I have a table where is saved all site´s access: access id year month day weekday hour minute ip Any column has multiple lines, and I have the follow query that returns the amount of access per day of month: SELECT year, month, day, COUNT(*) AS access FROM access WHERE year = 2005 AND month = 5 GROUP BY year, month, day ORDER BY year, month, day Now, I need to do the same query, but for unique access, in other words, with DISTINCT year, month, day, ip. I tryed to use the query: SELECT year, month, day, ip, COUNT(*) AS access FROM access WHERE year = 2005 AND month = 5 GROUP BY year, month, day, ip ORDER BY year, month, day but it returns me several lines of the same day and the amount of access per IP, and I need the amount of access from different IPs. Could anybody help me? Ronan -- 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: Help with query
Mathias, Hi, look at group by ... with rollup at : http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html Thank you very much for your help. My needs aren´t this, exactly. GROUP BY WITH ROLLUP, returns me several lines of the same day (one per IP), plus the total. I need that every year-month-day-ip be counted as 1. And I need this total per day. Thank you, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with query
Ronan Lucio [EMAIL PROTECTED] wrote on 05/16/2005 04:21:17 PM: Mathias, Hi, look at group by ... with rollup at : http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html Thank you very much for your help. My needs aren´t this, exactly. GROUP BY WITH ROLLUP, returns me several lines of the same day (one per IP), plus the total. I need that every year-month-day-ip be counted as 1. And I need this total per day. Thank you, Ronan This should give you how many unique IP addresses were used and the total number of accesses for each day for the 5th month of 2005: SELECT year, month, day,COUNT(DISTINCT IP), COUNT(*) AS access FROM access WHERE year = 2005 AND month = 5 GROUP BY year, month, day ORDER BY year, month, day; The DISTINCT keyword eliminates all duplicates so that you only count how many different values appear in that column. Is this what you are looking for? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Help with query
Hi Ronan, I don't know if i understand your need, but your query gives something like that : mysql SELECT year, month, day, ip, COUNT(*) AS access - FROM access - WHERE year = 2005 - AND month = 5 - GROUP BY year, month, day, ip - ORDER BY year, month, day; +--+---+--+-++ | year | month | day | ip | access | +--+---+--+-++ | 2005 | 5 | 13 | 192.168.0.1 | 2 | | 2005 | 5 | 13 | 192.168.0.2 | 1 | | 2005 | 5 | 14 | 192.168.0.2 | 1 | | 2005 | 5 | 15 | 192.168.0.3 | 1 | +--+---+--+-++ 4 rows in set (0.00 sec) Your last email lets me understand that your want this data + IP. I tought to group_concat : mysql SELECT year, month, day, group_concat(ip),count(*) AS access - FROM access - WHERE year = 2005 - AND month = 5 - GROUP BY year, month,day - ORDER BY year, month, day; +--+---+--+-++ | year | month | day | group_concat(ip)| access | +--+---+--+-++ | 2005 | 5 | 13 | 192.168.0.1,192.168.0.1,192.168.0.2 | 3 | | 2005 | 5 | 14 | 192.168.0.2 | 1 | | 2005 | 5 | 15 | 192.168.0.3 | 1 | +--+---+--+-++ 3 rows in set (0.00 sec) To drop multiple IP, you can use distinct : mysql SELECT year, month, day, group_concat(distinct ip),count(*) AS access - FROM access - WHERE year = 2005 - AND month = 5 - GROUP BY year, month,day - ORDER BY year, month, day; +--+---+--+---++ | year | month | day | group_concat(distinct ip) | access | +--+---+--+---++ | 2005 | 5 | 13 | 192.168.0.1,192.168.0.2 | 3 | | 2005 | 5 | 14 | 192.168.0.2 | 1 | | 2005 | 5 | 15 | 192.168.0.3 | 1 | +--+---+--+---++ 3 rows in set (0.00 sec) But when you group by year-month-day-ip you have distinct year-month-day-ip as you said. The problem is that the count(*) is for those distinct values. I hope that this is near what you need. Mathias Selon Ronan Lucio [EMAIL PROTECTED]: Mathias, Hi, look at group by ... with rollup at : http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html Thank you very much for your help. My needs aren´t this, exactly. GROUP BY WITH ROLLUP, returns me several lines of the same day (one per IP), plus the total. I need that every year-month-day-ip be counted as 1. And I need this total per day. Thank you, Ronan -- 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: QUERY (TOP)
Shawn, query SELECT * FROM spam_stats WHERE rank = 10; will return all rows which I don't want. I need datewise top 10 spam domain. means condition would be serach those rows which are having top 10 spam (means highest) on each day and show the output like which I send earlier. thanks [EMAIL PROTECTED] wrote: Seena Blace wrote on 05/16/2005 11:13:48 AM: shawn I think 2nd query will return only 10 rows. I want out like followings date domain spam 05/05/05 hotmail.com 120 05/05/05 yahoo.com 110 05/05/05 abc.com 99 05/05/05 def.com 80 05/05/05 mnpo.net 79 . like that upto 10 -- 05/06/05 yahoo.com 300 05/06/05 def.com 250 05/06/05 zer.com 200 ..like that upto 10 Each day there are multiple entry from diffrent domains or same domain. I want each day whatever top 10 spam sender domain. thanks [EMAIL PROTECTED] wrote: Seena Blace wrote on 05/16/2005 10:08:15 AM: Any suggestion pl? Seena Blace wrote:hi, here is table description report1 +-+--+--+- +-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+- +-++ | id | int(10) unsigned | | PRI | NULL | auto_increment | | host_id | int(10) unsigned | | MUL | 0 | | | report_rcpt_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_sender_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_ipaddress_id | int(10) unsigned | YES | MUL | NULL | | | time | datetime | | MUL | -00-00 00:00:00 | | | detected_spam | int(10) unsigned | | | 0 | | | detected_virus | int(10) unsigned | | | 0 | | | processed | int(10) unsigned | | | 0 | | | allowed | int(10) unsigned | | | 0 | | | suspected | int(10) unsigned | | | 0 | | | blocked | int(10) unsigned | | | 0 | | | spam | int(10) unsigned | | | 0 | | | virus | int(10) unsigned | | | 0 | | I WANT REPORT LIKE FOLLOWINGS date sender processed spam suspected I want top 10 spam sender each day. QUery i'm using select date_format(time,'%Y-%d-%m'),report_sender_domain_id, processed ,spam from report1 order by spam desc ,report_sender_domain_id,date_format(time,'%Y-% d-%m') limit 10; Please suggest. thanks It was the weekend. Not everyone lurks on their days off. Try this select date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,processed ,spam ,suspected from report1 order by spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') limit 10; or if there is more than one entry per spammer per day SELECT date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,sum(processed) as processed ,sum(spam) as spam ,sum(suspected) as suspected FROM report1 GROUP BY date_format(time,'%Y-%d-%m') ,report_sender_domain_id ORDER BY spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') LIMIT 10; That will give you their total stats for each day. Shawn Green Database Administrator Unimin Corporation - Spruce Pine - OK, then what you want to do will take two steps and another table (unless you want to write application code) to do with MySQL; First step is to create a table where the intermediate results can be stored. What this table does is number each row for each day. Since it appears that you may want this information frequently (but only need to update it at the end of every day) I will make this a permanent table (not a temporary table). CREATE TABLE spam_stats ( report_date date not null, report_sender_domain_id int unsigned not null, rank int unsigned auto_increment, processed int unsigned, spam int unsigned, suspected int unsigned, PRIMARY KEY (report_date, rank), UNIQUE KEY (report_sender_domain_id, report_date), KEY (rank) ); The UNIQUE key ensures that the same spammer cannot have more than one entry per day while the PRIMARY KEY allows for a groupwize autonumber (each entry per day gets it's own number, rank, starting at 1). The last key is optional but will seriously speed up the returns for the report you wanted. Next we need to populate our new table with the spam report data (filled in from most spam to least spam per domain per day). INSERT spam_stats (report_date, report_sender_domain_id, processed, spam, suspected) SELECT date(`time`) ,report_sender_domain_id ,sum(processed) as processed ,sum(spam) as spam ,sum(suspected) as suspected FROM report1 GROUP BY date(`time`) ,report_sender_domain_id ORDER BY spam desc ,report_sender_domain_id ,date(`time`); What will happen is that the rank column will be automatically filled in for each day's spam statistics. Then to get your top ten list you just run: SELECT * FROM spam_stats WHERE rank = 10; Update spam_stats at the end
Re: Help with query
Mathias, To drop multiple IP, you can use distinct : mysql SELECT year, month, day, group_concat(distinct ip),count(*) AS access - FROM access - WHERE year = 2005 - AND month = 5 - GROUP BY year, month,day - ORDER BY year, month, day; +--+---+--+---++ | year | month | day | group_concat(distinct ip) | access | +--+---+--+---++ | 2005 | 5 | 13 | 192.168.0.1,192.168.0.2 | 3 | | 2005 | 5 | 14 | 192.168.0.2 | 1 | | 2005 | 5 | 15 | 192.168.0.3 | 1 | +--+---+--+---++ 3 rows in set (0.00 sec) Thank you very much for your attention. It also answer my question, but I think the Shawn´s tip is more optimized. Any way, I appreciate your help. Thank you, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto_increment trouble (not the usual check || alter table solution, though)
Hi everyone, First time on the list, although I've been (quietly) a (not-so)long-time quiet observer. This isn't a FAQ and I can assure you I've tried every procedure out there (both on and off-list) to solve it. A couple weeks ago, I started having problems with a MyISAM table which holds records from user profiles. It's a very simple table, with one auto_increment field and a bunch of other field - nothing out of the ordinary. To those of you familiar with slashcode, it's an old version of the users table: CREATE TABLE users ( uid int(11) NOT NULL auto_increment, nickname varchar(20) NOT NULL default '', realemail varchar(50) NOT NULL default '', fakeemail varchar(50) default NULL, homepage varchar(100) default NULL, passwd varchar(12) NOT NULL default '', sig varchar(160) default NULL, seclev int(11) NOT NULL default '0', matchname varchar(20) default NULL, banned int(11) NOT NULL default '0', permmod int(11) NOT NULL default '0', PRIMARY KEY (uid), KEY login (uid,passwd,nickname), KEY chk4user (nickname,realemail), KEY chk4email (realemail) ) TYPE=MyISAM PACK_KEYS=1; We have about 10k lines (more precisely, 10885 lines). Our code inserts data using a insert into users values (NULL, .) statement which hasn't changed over the last few years. Recently, we started seeing the last record with a _way_ high uid value - more precisely, 2147483647 (which keeps new inserts from happening). Obviously this is a problem with the auto_increment mechanism, and we have followed all the (documented) and traditional approaches, which follow: * search the row with the wrong uid and correct it: - upon inserting a new record, its uid will be 2147483647; * correct the uid and modify the auto_increment value from the table: mysql alter table users AUTO_INCREMENT=10900; Query OK, 10885 rows affected (0.33 sec) Records: 10885 Duplicates: 0 Warnings: 0 mysql show table status like 'users'; +---+++---++-+-+--+---++-+-+-++-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +---+++---++-+-+--+---++-+-+-++-+ | users | MyISAM | Dynamic| 10885 | 68 | 748604 | 4294967295 | 1051648 | 0 | --2147483647-- | 2005-05-16 20:23:49 | 2005-05-16 20:23:49 | 2005-05-16 20:23:49 | pack_keys=1| | +---+++---++-+-+--+---++-+-+-++-+ * dump the table, make sure it doesn't have any uid above =~10k, delete the table and restore the table (and data) from disk: - data is okay, select * from users where uid11k shows ZERO records - insert into users values (NULL,.) results in a new row with uid=2147483647 * several combinations of CHECK TABLE, myisamchk (-r|-o|), dump remore_wrong_record restore, drop the entire database: - data is OKAY, select results in no records above 11k, next insert will have uid=2147483647 The table fits in about 1MB on disk, and its indexes fit in about 700kB. Does anyone have a clue? Any help is highly appreciated. Best of regards, Ricardo Oliveira PS: Sorry for such a long message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: QUERY (TOP)
Please try my solution before you tell me it's broken, OK? I know you want to see the top 10 spammers for EACH day. That's what I wrote for you. Please try my solution with your data and get back to me with the results and explain to me what's wrong so I can fix it. Thank you for your patience, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Seena Blace [EMAIL PROTECTED] wrote on 05/16/2005 04:41:19 PM: Shawn, query SELECT * FROM spam_stats WHERE rank = 10; will return all rows which I don't want. I need datewise top 10 spam domain. means condition would be serach those rows which are having top 10 spam (means highest) on each day and show the output like which I send earlier. thanks [EMAIL PROTECTED] wrote: Seena Blace wrote on 05/16/2005 11:13:48 AM: shawn I think 2nd query will return only 10 rows. I want out like followings date domain spam 05/05/05 hotmail.com 120 05/05/05 yahoo.com 110 05/05/05 abc.com 99 05/05/05 def.com 80 05/05/05 mnpo.net 79 . like that upto 10 -- 05/06/05 yahoo.com 300 05/06/05 def.com 250 05/06/05 zer.com 200 ..like that upto 10 Each day there are multiple entry from diffrent domains or same domain. I want each day whatever top 10 spam sender domain. thanks [EMAIL PROTECTED] wrote: Seena Blace wrote on 05/16/2005 10:08:15 AM: Any suggestion pl? Seena Blace wrote:hi, here is table description report1 +-+--+--+- +-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+- +-++ | id | int(10) unsigned | | PRI | NULL | auto_increment | | host_id | int(10) unsigned | | MUL | 0 | | | report_rcpt_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_sender_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_ipaddress_id | int(10) unsigned | YES | MUL | NULL | | | time | datetime | | MUL | -00-00 00:00:00 | | | detected_spam | int(10) unsigned | | | 0 | | | detected_virus | int(10) unsigned | | | 0 | | | processed | int(10) unsigned | | | 0 | | | allowed | int(10) unsigned | | | 0 | | | suspected | int(10) unsigned | | | 0 | | | blocked | int(10) unsigned | | | 0 | | | spam | int(10) unsigned | | | 0 | | | virus | int(10) unsigned | | | 0 | | I WANT REPORT LIKE FOLLOWINGS date sender processed spam suspected I want top 10 spam sender each day. QUery i'm using select date_format(time,'%Y-%d-%m'),report_sender_domain_id, processed ,spam from report1 order by spam desc ,report_sender_domain_id,date_format(time,'%Y-% d-%m') limit 10; Please suggest. thanks It was the weekend. Not everyone lurks on their days off. Try this select date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,processed ,spam ,suspected from report1 order by spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') limit 10; or if there is more than one entry per spammer per day SELECT date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,sum(processed) as processed ,sum(spam) as spam ,sum(suspected) as suspected FROM report1 GROUP BY date_format(time,'%Y-%d-%m') ,report_sender_domain_id ORDER BY spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') LIMIT 10; That will give you their total stats for each day. Shawn Green Database Administrator Unimin Corporation - Spruce Pine - OK, then what you want to do will take two steps and another table (unless you want to write application code) to do with MySQL; First step is to create a table where the intermediate results can be stored. What this table does is number each row for each day. Since it appears that you may want this information frequently (but only need to update it at the end of every day) I will make this a permanent table (not a temporary table). CREATE TABLE spam_stats ( report_date date not null, report_sender_domain_id int unsigned not null, rank int unsigned auto_increment, processed int unsigned, spam int unsigned, suspected int unsigned, PRIMARY KEY (report_date, rank), UNIQUE KEY (report_sender_domain_id, report_date), KEY (rank) ); The UNIQUE key ensures that the same spammer cannot have more than one entry per day while the PRIMARY KEY allows for a groupwize autonumber (each entry per day gets it's own number, rank, starting at 1). The last key is optional but will seriously speed up the returns for the report you wanted. Next we need to populate our new table with the spam report data (filled in from most spam
RE: auto_increment trouble (not the usual check || alter table solution, though)
This may not be a very elegant solution, but you can do this just to get the inserts going again: ALTER TABLE users MODIFY COLUMN uid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT; If you don't have any negative uids, then you will be able to store 2 billion more uids. Just out of curiosity, have you DROPPED re-created the table? -- Partha Dutta, Senior Consultant MySQL Inc, NY, USA, www.mysql.com Are you MySQL certified? www.mysql.com/certification -Original Message- From: Ricardo Oliveira [mailto:[EMAIL PROTECTED] Sent: Monday, May 16, 2005 5:00 PM To: mysql@lists.mysql.com Subject: auto_increment trouble (not the usual check || alter table solution, though) Hi everyone, First time on the list, although I've been (quietly) a (not-so)long-time quiet observer. This isn't a FAQ and I can assure you I've tried every procedure out there (both on and off-list) to solve it. A couple weeks ago, I started having problems with a MyISAM table which holds records from user profiles. It's a very simple table, with one auto_increment field and a bunch of other field - nothing out of the ordinary. To those of you familiar with slashcode, it's an old version of the users table: CREATE TABLE users ( uid int(11) NOT NULL auto_increment, nickname varchar(20) NOT NULL default '', realemail varchar(50) NOT NULL default '', fakeemail varchar(50) default NULL, homepage varchar(100) default NULL, passwd varchar(12) NOT NULL default '', sig varchar(160) default NULL, seclev int(11) NOT NULL default '0', matchname varchar(20) default NULL, banned int(11) NOT NULL default '0', permmod int(11) NOT NULL default '0', PRIMARY KEY (uid), KEY login (uid,passwd,nickname), KEY chk4user (nickname,realemail), KEY chk4email (realemail) ) TYPE=MyISAM PACK_KEYS=1; We have about 10k lines (more precisely, 10885 lines). Our code inserts data using a insert into users values (NULL, .) statement which hasn't changed over the last few years. Recently, we started seeing the last record with a _way_ high uid value - more precisely, 2147483647 (which keeps new inserts from happening). Obviously this is a problem with the auto_increment mechanism, and we have followed all the (documented) and traditional approaches, which follow: * search the row with the wrong uid and correct it: - upon inserting a new record, its uid will be 2147483647; * correct the uid and modify the auto_increment value from the table: mysql alter table users AUTO_INCREMENT=10900; Query OK, 10885 rows affected (0.33 sec) Records: 10885 Duplicates: 0 Warnings: 0 mysql show table status like 'users'; +---+++---++-+ -+--+---++ -+-+-++--- --+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +---+++---++-+ -+--+---++ -+-+-++--- --+ | users | MyISAM | Dynamic| 10885 | 68 | 748604 | 4294967295 | 1051648 | 0 | --2147483647-- | 2005-05-16 20:23:49 | 2005-05-16 20:23:49 | 2005-05-16 20:23:49 | pack_keys=1| | +---+++---++-+ -+--+---++ -+-+-++--- --+ * dump the table, make sure it doesn't have any uid above =~10k, delete the table and restore the table (and data) from disk: - data is okay, select * from users where uid11k shows ZERO records - insert into users values (NULL,.) results in a new row with uid=2147483647 * several combinations of CHECK TABLE, myisamchk (-r|-o|), dump remore_wrong_record restore, drop the entire database: - data is OKAY, select results in no records above 11k, next insert will have uid=2147483647 The table fits in about 1MB on disk, and its indexes fit in about 700kB. Does anyone have a clue? Any help is highly appreciated. Best of regards, Ricardo Oliveira PS: Sorry for such a long message. -- 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: auto_increment trouble (not the usual check || alter table solution, though)
Partha, On 5/16/05, Partha Dutta [EMAIL PROTECTED] wrote: This may not be a very elegant solution, but you can do this just to get the inserts going again: ALTER TABLE users MODIFY COLUMN uid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT; If you don't have any negative uids, then you will be able to store 2 billion more uids. Although I haven't tried this, I guess it'd possibly work - but I'm trying to have a linear uid column, as it has relations with some other tables (where the linearity of this field is crucial). Nevertheless, I'll try it ASAP and I'll get back to you. Thank you, Ricardo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment trouble (not the usual check || alter table solution, though)
Hi, with a similar structure, you can have : mysql select * from users where uid =262140; ++--+ | uid| nickname | ++--+ | 262140 | text | | 262141 | text | | 262142 | text | | 262143 | text | | 262144 | text | ++--+ 5 rows in set (0.00 sec) it's surprising that you can insert NULL in a primary key auto_increment. second, with only 1 values, if the auto_increment reached 2147483647 this means that you have an intensive delete, or the auto_increment had been altered. you can create table toto like users, and insert data again into toto. This will reincremente between 1 and 10xxx. Then rename toto to users. Mathias Selon Ricardo Oliveira [EMAIL PROTECTED]: Hi everyone, First time on the list, although I've been (quietly) a (not-so)long-time quiet observer. This isn't a FAQ and I can assure you I've tried every procedure out there (both on and off-list) to solve it. A couple weeks ago, I started having problems with a MyISAM table which holds records from user profiles. It's a very simple table, with one auto_increment field and a bunch of other field - nothing out of the ordinary. To those of you familiar with slashcode, it's an old version of the users table: CREATE TABLE users ( uid int(11) NOT NULL auto_increment, nickname varchar(20) NOT NULL default '', realemail varchar(50) NOT NULL default '', fakeemail varchar(50) default NULL, homepage varchar(100) default NULL, passwd varchar(12) NOT NULL default '', sig varchar(160) default NULL, seclev int(11) NOT NULL default '0', matchname varchar(20) default NULL, banned int(11) NOT NULL default '0', permmod int(11) NOT NULL default '0', PRIMARY KEY (uid), KEY login (uid,passwd,nickname), KEY chk4user (nickname,realemail), KEY chk4email (realemail) ) TYPE=MyISAM PACK_KEYS=1; We have about 10k lines (more precisely, 10885 lines). Our code inserts data using a insert into users values (NULL, .) statement which hasn't changed over the last few years. Recently, we started seeing the last record with a _way_ high uid value - more precisely, 2147483647 (which keeps new inserts from happening). Obviously this is a problem with the auto_increment mechanism, and we have followed all the (documented) and traditional approaches, which follow: * search the row with the wrong uid and correct it: - upon inserting a new record, its uid will be 2147483647; * correct the uid and modify the auto_increment value from the table: mysql alter table users AUTO_INCREMENT=10900; Query OK, 10885 rows affected (0.33 sec) Records: 10885 Duplicates: 0 Warnings: 0 mysql show table status like 'users'; +---+++---++-+-+--+---++-+-+-++-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +---+++---++-+-+--+---++-+-+-++-+ | users | MyISAM | Dynamic| 10885 | 68 | 748604 | 4294967295 | 1051648 | 0 | --2147483647-- | 2005-05-16 20:23:49 | 2005-05-16 20:23:49 | 2005-05-16 20:23:49 | pack_keys=1| | +---+++---++-+-+--+---++-+-+-++-+ * dump the table, make sure it doesn't have any uid above =~10k, delete the table and restore the table (and data) from disk: - data is okay, select * from users where uid11k shows ZERO records - insert into users values (NULL,.) results in a new row with uid=2147483647 * several combinations of CHECK TABLE, myisamchk (-r|-o|), dump remore_wrong_record restore, drop the entire database: - data is OKAY, select results in no records above 11k, next insert will have uid=2147483647 The table fits in about 1MB on disk, and its indexes fit in about 700kB. Does anyone have a clue? Any help is highly appreciated. Best of regards, Ricardo Oliveira PS: Sorry for such a long message. -- 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: auto_increment trouble (not the usual check || alter table solution, though)
Partha, Partha Dutta wrote: This may not be a very elegant solution, but you can do this just to get the inserts going again: ALTER TABLE users MODIFY COLUMN uid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT; If you don't have any negative uids, then you will be able to store 2 billion more uids. Just out of curiosity, have you DROPPED re-created the table? Sorry about the other message (from my lurker, always-silent identity). I have tried DROPping both the table and the whole database to no avail. As I said in the other reply, I'll try your suggestion, but I really need a solution to this problem other than having a huge leap in the uid values. Thanks, Ricardo Oliveira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Improving the performance of joins
I have a question about joins. My situation is as follows: I have 5 tables identical in structure. Each table represents essentially the same data, however, the data in each table represents a different version. The processing involved in generating the values stored in each table may have changed slightly, and therefore the values calculated may vary as well. On top of this structure is a need to identify a particular version of a record as the preferred version. The version tables look something like this: Tables Version_(1-5) -- ID |PRODUCT_ID |DATA1 |DATA2 |DATA3 |.|DATA_N | -- IDis a unique value across all 5 version tables PRODUCT_ID is unique to a given version table and identifies the source product, but is not unique in all 5 tables, ie, given a PRODUCT_ID=27 all 5 version tables will have a single record with PRODUCT_ID=27. DATA1...DATA_N These are columns with calculated values. Now in order to identify which version of a given PRODUCT_ID is preferred, we have created a map table that looks similar to this: Table Preferred_Map - MAP_ID |ID |PRODUCT_ID | - MAP_ID is an auto_increment primary key ID is the unique ID from the Version tables (unique value) PRODUCT_ID is the PRODUCT_ID from the tables (unique value) So to find information about records that are preferred, we have queries that look like this: SELECT Preferred_Map.PRODUCT_ID, DATA1, DATA2, DATA3 FROM Preferred_Map INNER JOIN Version_1 USING(ID,PRODUCT_ID) WHERE DATA1='x' AND DATA2='y' AND DATA3='z'; We run this same query for each of the 5 version tables and union them together. However the performance is terrible compared with what we get by running the query on a single version table without the join. These queries run anywhere from 6 to 10 times slower with the join than without it, which is pretty much unacceptable for our application. Running explain on the above query yields this: *** 1. row *** id: 1 select_type: SIMPLE table: Preferred_Map type: ALL possible_keys: ID,PRODUCT_ID key: NULL key_len: NULL ref: NULL rows: 45191 Extra: *** 2. row *** id: 1 select_type: SIMPLE table: Version_1 type: eq_ref possible_keys: PRIMARY,PRODUCT_ID key: PRIMARY key_len: 4 ref: HiRISE_Test.Preferred_Map.ID rows: 1 Extra: Using where The bottle neck appears to be the full table scan on the Preferred_Map table. Is there a way to optimize this somehow, to prevent the full table scan? Or are we better off creating a Preferred Products table with copies of all the records that are preferred? At the moment, it appears that the latter option is better than trying to do this with a bunch of unions and joins. Thanks for your help! -Rod -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql decrypt
Hi, I´m developing a database with the aes_encrypt and aes_decrypt
functions, with integrity constraints and varbinary types.
My problem is that when I insert a row into a table, I encrypt with
aes_encrypt, but when I desencrypt with aes_decrypt, it returns null with
some values. The only way I've found is using a blob type instead of
varbinary, but this type doesn't support integrity constraints.
For example,
create table t1( asunto varbinary(16), asun varbinary(16) );
insert into t1 values(aes_encrypt('aNuevo1','asunto'), 'aNuevo1');
select aes_decrypt(asunto,'asunto') as decrypted, asun from t1;
The result is decrypted=NULL and asun='aNuevo1'
Why ??? The length of the fields is correct, I don´t know why. But if I
change varbinary by blob, it runs. But with blob I can´t do any integrity
constraint.
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installing MySQL 4.1.12 on Mandrake 10.1
Hello List, I have a new test Linux server with Mandrake 10.1 installed that I was trying to install MySQL version 4.1.12 on today. Once I had installed the software, whenever I tried to start MySQL on this server I was getting the error message [ERROR] Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist when I tried to run the 'mysql_setpermissions' script and was getting the same result. After this failed I decided to try the MySQL installation files that came with the operating system which were Version 4.0.20 and this version worked fine. When I was installing the 4.1.12 version I was simply going with a minimal install of just the server package and the clients. When I ran the installation package from Mandrake it installed other files that from my reading of the MySQL manual I did not think that I needed. My questions are: Is there other files besides just the server and the client that I would need to install to get MySQL to work on Mandrake v10.1? Are there other issues with 4.1.12 that I have not fully explored that might prevent this from being installed? Finally if I upgraded this system to 4.1.12 now that 4.0.20 is installed would I wind up having the same problems? Thank you Eric H. Lommatsch Programmer MICRONix, Inc. 2087 South Grant Street Denver, CO 80210 Tel 303-777-8939 Fax 303-778-0378 [EMAIL PROTECTED]
Re: mysql decrypt
Hi,
You may have invalid data or incorrect padding when null is retuned :
http://dev.mysql.com/doc/mysql/en/encryption-functions.html
Mathias
Selon Alejandro Alekhine [EMAIL PROTECTED]:
Hi, I´m developing a database with the aes_encrypt and aes_decrypt
functions, with integrity constraints and varbinary types.
My problem is that when I insert a row into a table, I encrypt with
aes_encrypt, but when I desencrypt with aes_decrypt, it returns null with
some values. The only way I've found is using a blob type instead of
varbinary, but this type doesn't support integrity constraints.
For example,
create table t1( asunto varbinary(16), asun varbinary(16) );
insert into t1 values(aes_encrypt('aNuevo1','asunto'), 'aNuevo1');
select aes_decrypt(asunto,'asunto') as decrypted, asun from t1;
The result is decrypted=NULL and asun='aNuevo1'
Why ??? The length of the fields is correct, I don´t know why. But if I
change varbinary by blob, it runs. But with blob I can´t do any integrity
constraint.
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with join in query
I have the following query. select sum(a.attendhours), s.attendhours from attendance a, attsum s where a.siteid = s.siteid and a.assignmentid = s.assignmentid and a.attenddate = s.attenddate and a.siteid = 'XXX' and a.attenddate = '-XX-XX' group by a.assignmentid, a.attenddate; I want it to return those values where sum(a.attendhours) does not equal s.attendhours. What more do I need to add to my query to accomplish this? Thanks in Advance Scott Pippin [EMAIL PROTECTED]
Re: Help with join in query
hi, you can add ... ... group by a.assignmentid, a.attenddate; having sum(a.attendhours) != s.attendhours Mathias Selon Scott Pippin [EMAIL PROTECTED]: I have the following query. select sum(a.attendhours), s.attendhours from attendance a, attsum s where a.siteid = s.siteid and a.assignmentid = s.assignmentid and a.attenddate = s.attenddate and a.siteid = 'XXX' and a.attenddate = '-XX-XX' group by a.assignmentid, a.attenddate; I want it to return those values where sum(a.attendhours) does not equal s.attendhours. What more do I need to add to my query to accomplish this? Thanks in Advance Scott Pippin [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with join in query
with no comma (sorry): select sum(a.attendhours), s.attendhours from attendance a, attsum s where a.siteid = s.siteid and a.assignmentid = s.assignmentid and a.attenddate = s.attenddate and a.siteid = 'XXX' and a.attenddate = '-XX-XX' group by a.assignmentid, a.attenddate having sum(a.attendhours) != s.attendhours; -- here is the end of the query Mathias Selon [EMAIL PROTECTED]: hi, you can add ... ... group by a.assignmentid, a.attenddate; having sum(a.attendhours) != s.attendhours Mathias Selon Scott Pippin [EMAIL PROTECTED]: I have the following query. select sum(a.attendhours), s.attendhours from attendance a, attsum s where a.siteid = s.siteid and a.assignmentid = s.assignmentid and a.attenddate = s.attenddate and a.siteid = 'XXX' and a.attenddate = '-XX-XX' group by a.assignmentid, a.attenddate; I want it to return those values where sum(a.attendhours) does not equal s.attendhours. What more do I need to add to my query to accomplish this? Thanks in Advance Scott Pippin [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 database problems
Greetings all, Im new to mysql and have inherited several mysql databases and everything has been going well until lately. Most of my webpages come from mysql databases with php scripts. Now I am getting the message Unable to load database indicated by configuration file or something similiar when trying to connect to any database running on the server when the mysql user is running @localhost. I can however login at terminal as the mysql user and look at the database with no problems. My mysql version is 3.23.58 (upgraded from yum), php version 4.3.10, server is Fedora Core 2 kernel 2.6.5-1.358smp. Im pretty new to mysql so be gentle and easy in any help. Everything was working fine prior to Friday of last week. Mysqld.log show nothing other than start and restarts that I initiated trying to get things working. thanks, ddh -- Dwayne Hottinger Network Administrator Harrisonburg City Public Schools -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
