Re: rpm mysql5.0 for fedora4
Hi! unplug wrote: Thanks! If I have the glibc 2.3, I have to use Linux x86 generic RPM (dynamically linked). Otherwise, version lower than 2.3, we should use Linux x86 generic RPM (statically linked against glibc 2.2.5). Am I right? Yes. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Quoted DB name in USE statement.
Hi, List! First of all, what does mean 'log' suffix in version name of MySQL server? I have some problems with MySQL server 4.0.25-log (Don't say that I should update it. I don't have such privileges, so I have to deal with it). Server doesn't understand quoted DB names in USE statement. mysql USE `mysql`; ERROR 1049: Unknown database '`mysql`' I couldn't find something similar at bugs.mysql.com. Can anyone suggest some workaround? Removing -Q while making a dump isn't a way.. I am moving other users' tables where can be a lot of bad field and table names. Thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Logging (wrong) passwords by mysqld
On 3/5/06, Manuel Schmitt (manitu) [EMAIL PROTECTED] wrote: is there a reason that the unknown passwords can't simply be reset? yes, because not all clients are currently known and resetting them would possibly break the application(s) I am not pretty sure, but MySQL use to send encrypted passwords to the server, so, you'll never ever be able to really see the passwords. MySQL uses one-way ecryption, that means, if you've lost it, its gone. There's no way to find the characteres that generated that encrypted code. -- Manuel Schmitt - Geschäftsführer - manitu [EMAIL PROTECTED] Welvertstraße 2http://www.manitu.de/ 66606 St. Wendel Telefon: +49-(0)6851-99808-20 Telefax: +49-(0)6851-99808-99 PGP-Key-ID: 0x3E486E93 Unser Impressum finden Sie unter http://www.manitu.de/impressum/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.19?
Hi, Any info about when 5.0.19 will come out? We have some serious problems with some bugs is MySQL 5.0.18, and we have to choose: wait a bit, or try to downgrade to 4.1.x (and unfortunately no guarantee, that there is no bug there, too). Bye, Andras -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
To Print an output to the Screen
I was wondering about a simple solution. While writing a Script file for Batch execution, Suppose I just wanna print a comment on screen how should I do that.. ? ( I want and output Comment on Screen ) Can any one suggest rather than using a SELECT statement to print an out put on screen for the same purpose.. Thank you
RE: To Print an output to the Screen
[snip] While writing a Script file for Batch execution, Suppose I just wanna print a comment on screen how should I do that.. ? ( I want and output Comment on Screen ) Can any one suggest rather than using a SELECT statement to print an out put on screen for the same purpose.. [/snip] Depending on the batch or script language you can usually use an echo statement to send output to stdout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changing the binlog dir
Hello all, I had a problem last night where my Master server filled up the /var/ partition and stopped logging to the bin log. This caused all sorts of havok on my slaves and replication. My bad for not watching this but now what I'd like to do is move where MySQL writes the binlog to. Currently it's in the default /var/lib/mysql but I'd like to move it where I actually have the database files which is on a much larger partition. From what I've read I can put --log-bin=/data/hostname-bin into the my.cnf and restart the mysql server. Questions: 1) do I have to move the old binlogs to the /data/ prior to restarting mysql 2) should I move the binlog index as well? 3) will moving the binlog location throw the slaves off? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: viewing number of current connections
jonathan [EMAIL PROTECTED] wrote on 03/05/2006 03:19:41 PM: is there a way to view the current number of connections in mysql? I'm developing a PHP / MySQL app and would like to be able to debug a few performance problems. Ideally, I'd like to be able to query the number of current connections via the mysqli interface and make decisions based upon that. -jonathan I believe what you are after is: SHOW FULL PROCESSLIST; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Logging (wrong) passwords by mysqld
I believe Daniel is correct. The passwords are hashed before leaving the client. You may be able to capture invalid hashes but they are already encrypted before they get to the server. I do not know of any event or callback function you can use to tie into the server to trigger a logging event. Sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Daniel da Veiga [EMAIL PROTECTED] wrote on 03/06/2006 07:40:50 AM: On 3/5/06, Manuel Schmitt (manitu) [EMAIL PROTECTED] wrote: is there a reason that the unknown passwords can't simply be reset? yes, because not all clients are currently known and resetting them would possibly break the application(s) I am not pretty sure, but MySQL use to send encrypted passwords to the server, so, you'll never ever be able to really see the passwords. MySQL uses one-way ecryption, that means, if you've lost it, its gone. There's no way to find the characteres that generated that encrypted code. -- Manuel Schmitt - Geschäftsführer - manitu [EMAIL PROTECTED] Welvertstraße 2 http://www.manitu.de/ 66606 St. Wendel Telefon: +49-(0)6851-99808-20 Telefax: +49-(0)6851-99808-99 PGP-Key-ID: 0x3E486E93 Unser Impressum finden Sie unter http://www.manitu.de/impressum/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL InnoDB Row insert Calculation
With autocommit=1, anybody could give calculation on how many rows could be inserted in 1 seconds? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Changing the binlog dir
Well, I supposed that your slave is broken from now, because your bin-log is not the exact situation of your databases. So: 1) no. If your slave is stopped for a abrupt lost of connection to your master, the old binlogs cannot do anything about. You need to copy the databases from your master to your slave before start it, and, with this, have a real database replication. 2) no. When you started, mysqld will create a new for you. 3) yes. Stop the slaves to copy the databases and rebuild your replication. -Original Message- From: Jeff [mailto:[EMAIL PROTECTED] Sent: Monday, March 06, 2006 12:00 PM To: mysql@lists.mysql.com Subject: Changing the binlog dir Hello all, I had a problem last night where my Master server filled up the /var/ partition and stopped logging to the bin log. This caused all sorts of havok on my slaves and replication. My bad for not watching this but now what I'd like to do is move where MySQL writes the binlog to. Currently it's in the default /var/lib/mysql but I'd like to move it where I actually have the database files which is on a much larger partition. From what I've read I can put --log-bin=/data/hostname-bin into the my.cnf and restart the mysql server. Questions: 1) do I have to move the old binlogs to the /data/ prior to restarting mysql 2) should I move the binlog index as well? 3) will moving the binlog location throw the slaves off? Thanks, Jeff -- 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]
stucked, unkillable mysql query
hello i have a strange problem. freebsd 6.0 amd64, dual xeon mysql 5.0.18 huge conf, myisam and heap tables. around 1000 query/sec, without any problem in the last 2 week. the problem is: the stuff is running perfectly with a load of 0.8-1.0 it was correct, but 20 hours before something happened. one query arrived, done it's job, and after it the connection doesn't turn to closed. the query is still in the processlist, state sleep, running time is crazy, and the machine's load is jumped to 1.8-2.2 in the moment when this process was started in mysql. i tired to kill it, but (and this is the most strange) process's state turned to killed, but it's already in the process list and nothing happen. i tired to kill it in console and with mysqladmin, but nothing helps. i think a restart will help it but it's not the solution for me. i want to know why and how happened this, and how i can kill this damn process. and i'm not allowed to restart the mysql daemon. it must be another way. anybody know any solution? thanks a lot. sixday ps: sorry for my shit english -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: is this safe against sql-injection?
Jochen Kaechelin wrote:
Can somebody give me some general hints how to prevent
sql-injection?
I always go this way to build my queries:
function clean_mysql_string($string) {
$clean_string = stripslashes($string);
$clean_string = htmlentities(strip_tags(($clean_string)));
$clean_string = trim($clean_string);
$clean_string = rtrim($clean_string);
$clean_string = mysql_real_escape_string($clean_string);
return($clean_string);
}
$searchstring = clean_mysql_string($_POST[searchstring]);
$query = SELECT id,uname,nickname, MATCH(uname,nickname)
AGAINST('$searchstring' IN BOOLEAN MODE) AS mtch
FROM wlh_accounts
HAVING mtch 0.001
ORDER BY mtch DESC;
$results = mysql_query($query);
while ($row = mysql_fetch_array($results, MYSQL_ASSOC)) {
$values[] = array (
id = $row[id],
uname = $row[uname],
nickname= $row[nickname],
mtch= $row[mtch]
);
}
Is this safe??
AFAIK, all you really need to prevent SQL injection is to use
mysql_real_escape_string and enclose the variable in single-quotes when
you construct the query. Stripslashes is a good idea if magic quotes are
enabled in PHP. htmlentities, strip_tags, trim and rtrim are not
necessary for preventing SQL injection (and the rtrim is redundant).
Look at Example 3 on http://www.php.net/mysql_real_escape_string (but
pay attention to the user comments regarding the is_numeric check).
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.1.7-beta has been released
Hi,
MySQL 5.1.7-beta, a new version of the popular Open Source Database
Management System, has been released. The Community Edition is now
available in source and binary form for a number of platforms from our
download pages at
http://dev.mysql.com/downloads/ and mirror sites.
Note that not all mirror sites may be up to date at this point in time -
if you can't find this version on some mirror, please try again later or
choose another download site.
This is the first published Beta release in the 5.1 series.
All attention will continue to be focused on fixing bugs and stabilizing
5.1 for later production release.
NOTE: This Beta release, as any other pre-production release, should not
be installed on ``production'' level systems or systems with critical
data. It is good practice to back up your data before installing any new
version of software. Although MySQL has worked very hard to ensure a
high level of quality, protect your data by making a backup as you would
for any software beta release.
Please refer to our bug database at http://bugs.mysql.com/ for more
details about the individual bugs fixed in this version.
We welcome and appreciate your feedback!
News from the ChangeLog:
Functionality added or changed:
* Incompatible change: TYPE = engine_name is no longer accepted as a
synonym for the ENGINE = engine_name table option. (TYPE has been
deprecated for some time already.)
* Added the mysql_upgrade program that checks all tables for
incompatibilities with the current version of MySQL Server and
repairs them if necessary. This program should be run for each MySQL
upgrade (rather than mysql_fix_privilege_tables). See Section 5.5.2,
mysql_upgrade --- Check Tables for MySQL Upgrade.
* All subpartitions within a given partitioned table are now
guaranteed to have unique names.
(Bug #15408 (http://bugs.mysql.com/15408))
* Added the FOR UPGRADE option for the CHECK TABLE statement. This
option checks whether tables are incompatible with the current
version of MySQL Server. Also added the --check-upgrade option to
mysqlcheck that invokes CHECK TABLE with the FOR UPGRADE option.
Added the --fix-db-names and --fix-table-names options to
mysqlcheck.
* Added the RENAME DATABASE statement.
This is a first version with limited functionality, especially it
does not migrate any stored routines or events to the new schema
(stored routines and events must be redefined in the new database).
Its current main purpose is for migration of old data that uses
filenames/dirnames in some coding page to the new filename encoding.
Like a RENAME TABLE, it will also not change any account privileges
listed in the system tables (this needs to be done manually, if this
is required for the renamed table).
* The SQL mode in effect at the time an event is created or altered is
recorded and used during event execution.
(Bug #16407 (http://bugs.mysql.com/16407))
* Added the PROCESSLIST table to INFORMATION_SCHEMA.
* Attempting to read pre-5.1.6 partitioned tables with a MySQL 5.1.7
(or later) server now generates a suitable warning message.
(Bug #16695 (http://bugs.mysql.com/16695))
For additional information about this issue, see Section D.1.3,
Changes in release 5.1.6 (01 February 2006).
* NDB Cluster: Attempting to SELECT ... FROM INFORMATION_SCHEMA.FILES
now raises a warning in the event that the cluster has crashed.
(Bug #17087 (http://bugs.mysql.com/17087))
* Removed the have_isam and have_raid system variables.
* Status messages added to ndb_restore to allow users to know that
data files for Disk Data are being created.
(Bug #16873 (http://bugs.mysql.com/16873))
* Added the IN NATURAL LANGUAGE MODE and IN NATURAL LANGUAGE MODE WITH
QUERY EXPANSION modifiers for full-text searches. See Section 12.7,
Full-Text Search Functions.
* Creator privileges are now checked for all events before execution.
(Bug #17289 (http://bugs.mysql.com/17289))
* CREATE/DROP/ALTER EVENT statements are not allowed in triggers.
(Bug #16410 (http://bugs.mysql.com/16410))
Bugs fixed:
* The mysql_fix_privilege_tables.sql script did not properly
initialize the Event_priv column to 'Y' for those accounts that
should have the EVENT privilege.
(Bug #16400 (http://bugs.mysql.com/16400))
* NDB Cluster: Inserting the output of REPEAT('some_string', some_int)
into a BLOB column resulted in the error Invalid blob attributes or
invalid blob parts table. (Bug #17505 (http://bugs.mysql.com/17505))
* NDB Cluster: Row-based replication was not being set up correctly if
a backup was already in progress. For example, connecting a mysqld
instance to a cluster which was being backed up would result in the
message NDB: skipping setup table test.t1 being written to the error
log. (Bug #17459 (http://bugs.mysql.com/17459))
* NDB Cluster: CREATE TEMPORARY
locking federated table not possible?
Hi,
is it not possible to lock federated tables (creating a lock on the remote
machine to lock the table on the machine containing the data?)
I've a situation where I have some different databases on different servers but
they share a small amount of tables that are physically stored on only one
machine.
the other (remote) servers use these table as federated tables.
on the server where the data is stored physically in a myIsam-table I can lock
the table, insert some data and unlock it.
I've made some tests where I create a write lock, then call 1 queries to
insert some data (when starting these queries I created a new request that
tries to insert data) then the first request unlocks the table and the second
request runs when the unlock is done. great.
but when I try to run a lock on a federated table it has no effect, I try to
lock the table, insert 1 rows and unlock the table again. but when I'm
running a second request while the 1 rows are inserted the second request
doesnt wait for the table to be unlocked, the data is inserted while the first
request is made.
here is my request thats starting first: (some pseudo-style code)
---
lock tables tbl_lager write;
do from 1 to 1
INSERT INTO `tbl_lager` (`fld_pid`) VALUES ('1');
end do
unlock tables;
unlocked at #now()#
and heres the second that starts when the first is running:
---
lock tables tbl_lager write;
INSERT INTO `tbl_lager` (`fld_pid`) VALUES ('1');
unlock tables;
unlocked at #now()#
doing this local ob the server where the data is stored physically it works
fine.
but on the federated table it doesn't work.
Any comments would be great. thx.
--
Sebastian Mork [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
'Into outfile' doesn't include the column names. How can it be done?
When using select into outfile, I can only get the table data, but I can't find how to include the column names. I haven't been able to include the column names into the actual return of the query, and they don't get stored in the .csv Anyone know how to include the column names, table name, other info, as an actual answer from MySQL? I realize that when I use the command line I can see the column names, but this is not the case when using, for example, Delphi, and my real problem, when the outfile is made. I use SQLyog for exporting to csv and they do include the columns, my bet is its some SQL option. The manual doesn't specify, at least in the select syntax part. Ariel
Re: 'Into outfile' doesn't include the column names. How can it be done?
Ariel Sánchez Mora [EMAIL PROTECTED] wrote on 03/06/2006 03:12:20 PM: When using select into outfile, I can only get the table data, but I can't find how to include the column names. I haven't been able to include the column names into the actual return of the query, and they don't get stored in the .csv Anyone know how to include the column names, table name, other info, as an actual answer from MySQL? I realize that when I use the command line I can see the column names, but this is not the case when using, for example, Delphi, and my real problem, when the outfile is made. I use SQLyog for exporting to csv and they do include the columns, my bet is its some SQL option. The manual doesn't specify, at least in the select syntax part. Ariel As far as I am aware, INTO OUTFILE does not have a setting where you can tag the first line with the column names. You may need to use something else like the CSV storage engine or mysqldump to get the results you want. Or, as a last resort, you may have to create your own table export routine. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: 'Into outfile' doesn't include the column names. How can it be done?
At 14:12 -0600 3/6/06, Ariel Sánchez Mora wrote: When using select into outfile, I can only get the table data, but I can't find how to include the column names. I haven't been able to include the column names into the actual return of the query, and they don't get stored in the .csv Anyone know how to include the column names, table name, other info, as an actual answer from MySQL? I realize that when I use the command line I can see the column names, but this is not the case when using, for example, Delphi, and my real problem, when the outfile is made. I use SQLyog for exporting to csv and they do include the columns, my bet is its some SQL option. The manual doesn't specify, at least in the select syntax part. There is no option. INTO OUTFILE dumps only data, not metadata. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 'Into outfile' doesn't include the column names. How can it be done?
Maybe, if you make a file with the SELECT content and call them from the OS shell, like this: example of the content of the my_file SELECT table1.a, tabela1.b, table1.c FROM mydatabase.table; After, call this file from the OS shell: shellmysql -A mydatabase my_file This will result on the screen. You can direct the result to a file: shellmysql -A mydatabase my_file my_table.sql If you need the tabs, do with -t on the options: shellmysql -A -t mydatabase my_file my_table.sql -Original Message- From: Ariel Sánchez Mora [mailto:[EMAIL PROTECTED] Sent: Monday, March 06, 2006 5:12 PM To: mysql@lists.mysql.com Subject: 'Into outfile' doesn't include the column names. How can it be done? When using select into outfile, I can only get the table data, but I can't find how to include the column names. I haven't been able to include the column names into the actual return of the query, and they don't get stored in the .csv Anyone know how to include the column names, table name, other info, as an actual answer from MySQL? I realize that when I use the command line I can see the column names, but this is not the case when using, for example, Delphi, and my real problem, when the outfile is made. I use SQLyog for exporting to csv and they do include the columns, my bet is its some SQL option. The manual doesn't specify, at least in the select syntax part. Ariel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
trouble finding WinMySQLadmin, and opening MySQLadmin
Hi everybody, I have Windows XP computer and I've installed Apache, and MySQL 5.0.18. I downloaded MySQL from: http://www.softpedia.com/progDownload/MySQL-for-Windows-Download-2668.html but the following problem occurs. According to the instructions in php and mysql for dummies (I'm a newbee) I should find a program called WinMySQLadmin, located in mysql/bin. First, this program is nowhere to be found (used start/find but nothing) I did however find the program MySQLadmin. Since WinMySQLadmin isn't there I thought it might work if I use the MySQLadmin program instead. According to the instructions I should be able to double click it so it wil open a new window. Now the second problem: It opens, but for a tiny fraction of a second. I've tried several other links to download MySQL but it's the same with all versions. What am I doing wrong? Best regards, Bibi
Problem INNODB error 995
We have 4 windows 2003 server with mysql 4.0.12 install in each one. They have been working for about 1.5 years with no problem. Two weeks ago, in one server we lost the mysql service, and when we look the .err file it report a 995 error. We can star the service again and it works. In the two weeks the service has stop in the same server about 10 times, but none in the other server. We have the same applications working in each server. What can we do to stop the error? We even thought that we have a problem with windows, so we reinstall it, but the problem continue. Please help Osvaldo Sommer
Re: trouble finding WinMySQLadmin, and opening MySQLadmin
Bibi, WinMySqlAdmin has been disocntinued. Try mysql/bin/MySqlInstqanceConfig.exe. Getting PHP to work with MySQL under Windows is a bit tricky at first, but once set up it's solid. For setup have a look at http://forums.mysql.com/read.php?52,70381,70628#msg-70628. PB http://www.artfulsoftware.com - Bibi Snelderwaard wrote: Hi everybody, I have Windows XP computer and I've installed Apache, and MySQL 5.0.18. I downloaded MySQL from: http://www.softpedia.com/progDownload/MySQL-for-Windows-Download-2668.html but the following problem occurs. According to the instructions in php and mysql for dummies (I'm a newbee) I should find a program called WinMySQLadmin, located in mysql/bin. First, this program is nowhere to be found (used start/find but nothing) I did however find the program MySQLadmin. Since WinMySQLadmin isn't there I thought it might work if I use the MySQLadmin program instead. According to the instructions I should be able to double click it so it wil open a new window. Now the second problem: It opens, but for a tiny fraction of a second. I've tried several other links to download MySQL but it's the same with all versions. What am I doing wrong? Best regards, Bibi No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 3/3/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 3/3/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Message could not be delivered
The original message was included as attachment -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT DISTINCT Optimizations
SELECT DISTINCT can be kind of slow if there are many result values, specifically if those result values include large VARCHARs. Furthermore, some database engines cannot support a SELECT DISTINCT if any LOBs are included in the result values. I'm trying to find a general way to optimize SELECT DISTINCT since for more purposes I rarely need to decide DISTINCT on any more than a single column. For example, consider I have a tree structure where leafs or groups can have more than a single parent group (i.e. they are links). (A) SELECT DISTINCT G.oid, G.name, G.description, G.type [, ...] FROM Groupable G JOIN Link ON G.oid = Link.childId JOIN Path ON Link.parentId = Path.descendantId WHERE (G.type = LEAF AND Path.ancestorId = 12345) Something like this can be slow on a large result set with many large character columns and it does not even express clearly what I mean, because what I really mean to say is: (B) SELECT ( FOR DISTINCT ON G.oid ), G.name, G.description, G.type [, ...] FROM Groupable G JOIN Link ON G.oid = Link.childId JOIN Path ON Link.parentId = Path.descendantId WHERE (G.type = LEAF AND Path.ancestorId = 12345) Of course there is no valid SQL like this. So what I end up doing is the following: (C) SELECT G.oid, G.name, G.description, G.type [, ...] FROM Groupable G WHERE G.oid IN ( SELECT G.oid FROM Groupable G JOIN Link ON G.oid = Link.childId JOIN Path ON Link.parentId = Path.descendantId WHERE (G.type = LEAF AND Path.ancestorId = 12345)) For the majority of cases this will perform better than (A). However, sometimes the expense of the subquery will out weight the sort used for the distinct and it will not be faster. Is there a standard SQL way to avoid sorting on every field for a distinct on a unique key without having to perform a subquery? Maybe something with a group by? TIA for any ideas or thoughts... R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select IP from Text Type
Im trying to select an IP from a TEXT (details) type field, which works if I do
this:
select id from `table` where `details` like '%192.168.0.1%' :
But If I want any record with an IP in that TEXT type field it seems a little
tricky. I have tried:
select id from `table` where INET_ATON(details) BETWEEN INET_ATON('127.0.0.1')
and INET_ATON('192.168.0.2');
select id from `table` where INET_ATON(details) BETWEEN
INET_ATON('%127.0.0.1%') and INET_ATON('%192.168.0.2%');
select id from `table` where details BETWEEN INET_ATON('%127.0.0.1%') and
INET_ATON('%192.168.0.2%');
For instance I know this IP is in 14 different records, what I was hoping for
is a result like so:
+--+
| id |
+--+
| 66 |
| 148 |
etc..
14 rows in set (0.01 sec)
Any other ideas on how I could do this?
Ron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Boolean searches on InnoDB tables?
Osku is working on FULLTEXT for InnoDB. So, despite what the documentation says: http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html Which states: They can work even without a FULLTEXT index, although a search executed in this fashion would be quite slow. You're saying that InnoDB tables cannot use a BOOLEAN search at all, no way, no how? At the moment I only have a small db while I'm building the product, so slow is going to be relative at this point (ie. fast), and I would expect it to converge when you get FULLTEXT working in InnoDB. I would like to get the code in there though for now. Do you have an estimate timeframe till this is implemented? Weeks? Months? Version? You could look at Sphinx, for exapmple. What is Sphinx? I did a quick google search but found many references to pyramids and other projects (popular name). Do you have a URL for this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: is this safe against sql-injection?
Date: Sun, 5 Mar 2006 16:59:13 +0100 To: mysql@lists.mysql.com From: Jochen Kaechelin [EMAIL PROTECTED] Subject: is this safe against sql-injection? Message-Id: [EMAIL PROTECTED] Can somebody give me some general hints how to prevent sql-injection? You may also want to consider the use of the PECL/filter extension (http://pecl.php.net/package/filter) which 'automagically' sanitizes user input. Rasumus uses this himself, see his article here: http://toys.lerdorf.com/archives/38-The-no-framework-PHP-MVC-framework.html cheers, scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Speed of InnoDB Insert with autocommit=1
Speed of InnoDB Insert with autocommit=1; Personal Test ! Linux : Fedora Core 4, kernel 2.6.11 mysqlgt; \s -- mysql Ver 14.12 Distrib 5.0.18, for pc-linux-gnu (i686) using readline 5.0 Connection id: 2349 Current database: test Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Using delimiter:; Server version: 5.0.18-max Protocol version: 10 Connection: 127.0.0.1 via TCP/IP Server characterset:latin1 Db characterset:latin1 Client characterset:latin1 Conn. characterset:latin1 TCP port: 3306 Uptime: 41 days 13 hours 34 min 36 sec Threads: 1 Questions: 3112491 Slow queries: 0 Opens: 1 Flush tables: 1 Open tables: 1148 Queries per second avg: 0.867 -- CREATE TABLE `sbtest` ( `pad` int(11) default NULL, `id` int(11) NOT NULL, `k` int(11) default NULL, `c` int(11) default NULL, t timestamp not null default current_timestamp ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Use sysbencdh 0.4.4-sp Edit tests/oltp/sb_oltp.c, change snprintf(query, MAX_QUERY_LEN, INSERT INTO %s values(?,?,?,?), to snprintf(query, MAX_QUERY_LEN, INSERT INTO %s (pad,id,k,c) values(?,?,?,?), Do ./sysbench --test=oltp --oltp-table-size=10 --oltp-table-name=sbtest --oltp-test-mode=nontrx --oltp-nontrx-mode=insert --max-requests=0 --num-threads=10 --mysql-db=test run --mysql-password=sbtest --mysql-user=sbtest --mysql-host=127.0.0.1 10 Threads, could insert around 1200 rows/seconds (autocommit=1); mysqlgt; SELECT COUNT(*), t FROM sbtest GROUP by t; +--+-+ | count(*) | t | +--+-+ | 946 | 2006-03-07 12:08:16 | | 1199 | 2006-03-07 12:08:17 | | 1210 | 2006-03-07 12:08:18 | | 1220 | 2006-03-07 12:08:19 | | 1158 | 2006-03-07 12:08:20 | | 1234 | 2006-03-07 12:08:21 | | 1166 | 2006-03-07 12:08:22 | | 1169 | 2006-03-07 12:08:23 | | 1211 | 2006-03-07 12:08:24 | | 1173 | 2006-03-07 12:08:25 | | 1221 | 2006-03-07 12:08:26 | | 1222 | 2006-03-07 12:08:27 | | 1229 | 2006-03-07 12:08:28 | | 1221 | 2006-03-07 12:08:29 | | 1185 | 2006-03-07 12:08:30 | | 1192 | 2006-03-07 12:08:31 | | 1169 | 2006-03-07 12:08:32 | | 1223 | 2006-03-07 12:08:33 | | 1223 | 2006-03-07 12:08:34 | | 1216 | 2006-03-07 12:08:35 | | 1217 | 2006-03-07 12:08:36 | +--+-+ ulimit -s 192;./sysbench --test=oltp --oltp-table-size=10 --oltp-table-name=sbtest --oltp-test-mode=nontrx --oltp-nontrx-mode=insert --max-requests=0 --num-threads=100 --mysql-db=test run --mysql-password=sbtest --mysql-user=sbtest --mysql-host=127.0.0.1 100 Threads, could insert around 2500 rows/seconds (autocommit=1); mysqlgt; SELECT COUNT(*), t FROM sbtest GROUP by t; +--+-+ | COUNT(*) | t | +--+-+ | 100 | 2006-03-07 12:13:58 | | 319 | 2006-03-07 12:13:59 | | 2302 | 2006-03-07 12:14:00 | | 2256 | 2006-03-07 12:14:01 | | 2494 | 2006-03-07 12:14:02 | | 2804 | 2006-03-07 12:14:03 | | 2970 | 2006-03-07 12:14:04 | | 2686 | 2006-03-07 12:14:05 | | 2949 | 2006-03-07 12:14:06 | | 2954 | 2006-03-07 12:14:07 | | 2835 | 2006-03-07 12:14:08 | | 2829 | 2006-03-07 12:14:09 | | 2785 | 2006-03-07 12:14:10 | | 2946 | 2006-03-07 12:14:11 | | 2350 | 2006-03-07 12:14:12 | | 2555 | 2006-03-07 12:14:13 | | 2611 | 2006-03-07 12:14:14 | | 2753 | 2006-03-07 12:14:15 | | 2984 | 2006-03-07 12:14:16 | | 3098 | 2006-03-07 12:14:17 | | 2911 | 2006-03-07 12:14:18 | | 2788 | 2006-03-07 12:14:19 | | 2754 | 2006-03-07 12:14:20 | | 2712 | 2006-03-07 12:14:21 | | 2798 | 2006-03-07 12:14:22 | | 2974 | 2006-03-07 12:14:23 | | 2451 | 2006-03-07 12:14:24 | | 2901 | 2006-03-07 12:14:25 | | 3144 | 2006-03-07 12:14:26 | | 2447 | 2006-03-07 12:14:27 | | 2471 | 2006-03-07 12:14:28 | | 2106 | 2006-03-07 12:14:29 | | 2906 | 2006-03-07 12:14:30 | | 2929 | 2006-03-07 12:14:31 | | 2853 | 2006-03-07 12:14:32 | | 2675 | 2006-03-07 12:14:33 | +--+-+ ulimit -s 192; ./sysbench --test=oltp --oltp-table-size=10 --oltp-table-name=sbtest --oltp-test-mode=nontrx --oltp-nontrx-mode=insert --max-requests=0 --num-threads=200 --mysql-db=test run --mysql-password=sbtest --mysql-user=sbtest --mysql-host=127.0.0.1 mysqlgt; SELECT COUNT(*), t FROM sbtest GROUP by t; +--+-+ | COUNT(*) | t | +--+-+ | 564 | 2006-03-07 12:23:26 | | 294 | 2006-03-07 12:23:27 | | 949 | 2006-03-07 12:23:28 | | 815 | 2006-03-07 12:23:29 | | 1152 | 2006-03-07 12:23:30 | | 1120 | 2006-03-07 12:23:31 | | 1201 |
How to make a social network?
Anyone have some pointers at a HowTo on creating a social network? Basically I need to show people in your immediate network, and also friends of your friends, etc... Like the whole 'six degrees of separation' thing. Ala: myspace, friendster, etc. ad nauseum. I prefer mySQL and PHP, but I could port from most any code. I guess I'm mostly interested in the theory of this an how do I set up the tables properly and what is the magic incantation (JOIN) to get this chain of people. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem INNODB error 995
Osvaldo, - Original Message - From: Osvaldo Sommer [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, March 07, 2006 12:58 AM Subject: Problem INNODB error 995 --Boundary_(ID_PMYElD1sU13Il0ENO4J+aw) Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit We have 4 windows 2003 server with mysql 4.0.12 install in each one. They have been working for about 1.5 years with no problem. Two weeks ago, in one server we lost the mysql service, and when we look the .err file it report a 995 error. We can star the service again and it works. In the two weeks the service has stop in the same server about 10 times, but none in the other server. We have the same applications working in each server. What can we do to stop the error? We even thought that we have a problem with windows, so we reinstall it, but the problem continue. Please help http://msdn.microsoft.com/library/default.asp?url=/library/en-us/debug/base/system_error_codes__500-999_.asp ERROR_OPERATION_ABORTED 995 The I/O operation has been aborted because of either a thread exit or an application request a few InnoDB users have reported this error number in the past 2 years. I have suspected that it is some bug in Windows or its device drivers, since InnoDB does not request aborts of I/O requests, nor do InnoDB's threads exit in the middle of an I/O operation. Did you upgrade the OS in that server before you started getting this error? Is the server identical to the other servers where mysqld works ok? The error might actually be a hardware problem. I have noticed that a hardware fault can produce strange error numbers in Linux. The same might hold for Windows. Osvaldo Sommer Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Boolean searches on InnoDB tables?
Daevid, - Original Message - From: Daevid Vincent [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, March 07, 2006 5:54 AM Subject: RE: Boolean searches on InnoDB tables? Osku is working on FULLTEXT for InnoDB. So, despite what the documentation says: http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html Which states: They can work even without a FULLTEXT index, although a search executed in this fashion would be quite slow. You're saying that InnoDB tables cannot use a BOOLEAN search at all, no way, no how? hmm... I have not heard that a MATCH ... AGAINST query in MySQL could work without a FULLTEXT index. If you try the query on a MyISAM table that does NOT have a FULLTEXT index, does it work there? At the moment I only have a small db while I'm building the product, so slow is going to be relative at this point (ie. fast), and I would expect it to converge when you get FULLTEXT working in InnoDB. I would like to get the code in there though for now. Do you have an estimate timeframe till this is implemented? Weeks? Months? Version? Sorry, no. You could look at Sphinx, for exapmple. What is Sphinx? I did a quick google search but found many references to pyramids and other projects (popular name). Do you have a URL for this? http://www.shodan.ru/projects/sphinx/ Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL InnoDB Row insert Calculation
Ady, - Original Message - From: Ady Wicaksono [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, March 06, 2006 5:32 PM Subject: MySQL InnoDB Row insert Calculation With autocommit=1, anybody could give calculation on how many rows could be inserted in 1 seconds? I am assuming that you perform a COMMIT after each insert. If the computer does not have a battery-backed disk cache, then the commit speed is limited by the disk rotation speed, which is at most 250 rotations per second nowadays. If the computer does have a battery-backed disk cache (or you take the risk and use a non-battery-backed cache), then the speed is limited by the CPU usage, and for big tables by the disk seek time. If the insertion is CPU-bound, you normally can insert 3000 rows per second, or more. For a big table, several gigabytes or more, inserts to secondary indexes may require disk seeks, limiting the maximum insert speed to 100 rows per second, or less. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL InnoDB Row insert Calculation
Heikki Please see my testing result on MySQL Forum http://forums.mysql.com/read.php?22,74279,74279#msg-74279 I need explanation about this issue :) Heikki Tuuri wrote: Ady, - Original Message - From: Ady Wicaksono [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, March 06, 2006 5:32 PM Subject: MySQL InnoDB Row insert Calculation With autocommit=1, anybody could give calculation on how many rows could be inserted in 1 seconds? I am assuming that you perform a COMMIT after each insert. If the computer does not have a battery-backed disk cache, then the commit speed is limited by the disk rotation speed, which is at most 250 rotations per second nowadays. If the computer does have a battery-backed disk cache (or you take the risk and use a non-battery-backed cache), then the speed is limited by the CPU usage, and for big tables by the disk seek time. If the insertion is CPU-bound, you normally can insert 3000 rows per second, or more. For a big table, several gigabytes or more, inserts to secondary indexes may require disk seeks, limiting the maximum insert speed to 100 rows per second, or less. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to convert InnoDB to MyISAM in 5.0?
hi guys, it's a simple thing (I think), but I can't find the docu on this from the mysql site. Thanks in advance for the help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'Into outfile' doesn't include the column names. How can it be done?
Hi Ariel, Maybe this example helps you to create CSV output from MySQL. The first SELECT generates the headerline; the second the data. ( SELECT 'FieldA','FieldB','FieldC', ... ) UNION ( SELECT `FieldA`, `FieldB`, `FieldC`, ... INTO OUTFILE 'D:/MySQL Datafiles/myFile.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM ... ... GROUP BY `FieldA`, `FieldB`, `FieldC`, ... ); Don't forget the braces ( and ). HTH, Cor - Original Message - From: Ariel Sánchez Mora [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, March 06, 2006 8:12 PM Subject: 'Into outfile' doesn't include the column names. How can it be done? When using select into outfile, I can only get the table data, but I can't find how to include the column names. I haven't been able to include the column names into the actual return of the query, and they don't get stored in the .csv Anyone know how to include the column names, table name, other info, as an actual answer from MySQL? I realize that when I use the command line I can see the column names, but this is not the case when using, for example, Delphi, and my real problem, when the outfile is made. I use SQLyog for exporting to csv and they do include the columns, my bet is its some SQL option. The manual doesn't specify, at least in the select syntax part. Ariel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
