Re: Unlimited client connections for MySQL
Dwight Tovey wrote: Brent Anderson wrote: Hello. I'm developing a client application for several platforms that will need to connect to a remote MySQL database. Unfortunately, MySQL refuses connections from external IP's that aren't allowed and since the clients using this will be on unknown IP addresses (their home computers), I'm in a bit of a situation. How does one setup a MySQL account with no IP restrictions? You probably have a line in your my.cnf that restricts the server to only listen on the localhost address. Look for bind-address = 127.0.0.1 Comment that line out, restart the server, and it should accept connections from all client machines (assuming that you don't have other firewall restrictions as well). Note however that this can be a big security hole. /dwight also check for: skip-networking -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SUM() of 1 and NULL is 1 ?
C.R.Vegelin wrote: Hi List, I need to SUM() on months from a table like: CREATE TABLE `data` ( `Jan` float default NULL, ... ) ENGINE=MyISAM; # V 5.0.15 Months may have NULL values, like: INSERT INTO data (Jan) VALUES (1), (NULL); However, when I use SELECT SUM(Jan) AS Jan, the returned value is 1 in stead of NULL. How to get a result NULL when in such a case ? TIA, Cor So you want NULL if there's 1 ore more NULLs in that column. This could be done either in code with separate query/queries or with single SQL statement like this: 1. if you don't have 0 i.e. just NULL or 1 in Jan then: SELECT IF( SUM( COALESCE(Jan,1) ) = SUM(Jan), SUM(Jan), NULL ) AS Jans FROM data [GROUP BY ...] 2. if you have 0 then it's more elaborate :-) SELECT IF( SUM( IF(Jan IS NULL, 1, 0) ) 0, NULL, SUM(Jan) ) AS Jans FROM data [GROUP BY ...] HTH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update query help
ViSolve DB Team wrote: Hi, Try this.. UPDATE table2 inner join table1 on table2.playedid=table1.playerid SET table2.totalscore=sum(table1.score) Just a guess... Thanks, ViSolve DB Team - Original Message - From: Ravi Kumar. [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, December 06, 2006 4:11 PM Subject: Update query help Dear Friends, I have two tables: T1, T2. T1 has 3 columns: playerid, gameid, score T2 has 2 columns: playerid, totalscore. I wish to update table T2 such that sum of T1.score of each player, gets updated in T2.totalscore. It may be something like this: update T2, T1 set T2.totalscore = sum(T1.score) where T1.playerid = T2.playerid OR update T2, T1 set T2.totalscore = sum(T1.score) where T1.playerid = T2.playerid group by playerid However none of the above is working. Where am I wrong? Please help. The version of MySQL I am using is 4.1.14-standard-log. Thanks, Ravi. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.15.9/573 - Release Date: 12/5/2006 ViSolve, I think yo've missed a GROUP BY needed for every Aggregated function (like SUM) ;-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update query help
Ravi Kumar. wrote: Dear Friends, I have two tables: T1, T2. T1 has 3 columns: playerid, gameid, score T2 has 2 columns: playerid, totalscore. I wish to update table T2 such that sum of T1.score of each player, gets updated in T2.totalscore. It may be something like this: update T2, T1 set T2.totalscore = sum(T1.score) where T1.playerid = T2.playerid OR update T2, T1 set T2.totalscore = sum(T1.score) where T1.playerid = T2.playerid group by playerid However none of the above is working. Where am I wrong? Please help. The version of MySQL I am using is 4.1.14-standard-log. Thanks, Ravi. First I assunme you've done INSERT INTO T2 SELECT DISTINCT(playerid), NULL FROM T1; Since you need an aggregate function like SUM() and it needs GROUP BY alas manual says For the multiple-table syntax, UPDATE ... In this case, ORDER BY and LIMIT cannot be used.: http://dev.mysql.com/doc/refman/4.1/en/update.html so I suppose you could try s.th. like this: 1. In code update each T2.totalscore in separate query using SELECT SUM(score) as totalscore FROM T1 GROUP BY playerid; Traverse resultset and in code (perl/php/whatever) update each T2.totalscore with result 2. OR you can try second approach UPDATE T2 SET totalscore=0 then you could try this UPDATE T2 JOIN T1 ON T2.playerid=T1.playerid SET T2.totalscore = T2.totalscore + COALESCE(T1.score, 0); 3. If your version supports subqueries... well then it is easy ;-) If you want to update a table based on an aggregate function applied to another table, you can use a correlated subquery, for example: UPDATE T2 SET totalscore = (SELECT SUM(T1.score) FROM T1 WHERE T2.playerid=T1.playerid) Additional information on MySQL correlated subqueries is at http://dev.mysql.com/doc/mysql/en/correlated-subqueries.html HTH :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calling Stored Procedures from PHP
Filipe Freitas wrote:
Hi,
This is not a mysql thing but maybe you can help me.
I want to call a stored procedure from PHP, so I tried it like normal
querys: mysql_query(CALL mySP();); with no success.
thx
I'm not PHP expert but I've managed to do this in perl using DBI (in
spite of all docs saying there are no such thing as OUT params). Perhaps
you could try s.th. alike:
# perl DBI with firebird: stored proc has 3 IN and 4 OUT params as you
can see...
my $s = SELECT field1, field2, field3, CAST(field4 AS VARCHAR(32)).
FROM S_GET_NAL_WEB('12-31-05', 4201, '2051600');
my $sth = $dbh-prepare($s) or die $dbh-errstr();
$sth-execute() or die $sth-errstr();
while (@data = $sth-fetchrow_array()) {
print join(BR, @data).BRBR;
}
...
HTH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem logging into mysql
Joey wrote: I wanted to confirm something as I am having a problem logging into mysql from the network. I have a server configured with a user showing host as % and the user with it's appropriate privalages. It allows me to login from phpmyadmin as well as via port 3306 to connect to the DB with MySQL gui tools. My new server has the same configuration for the user host showing % and all privalages, but it won't allow me to connect except for via phpmyadmin Any ideas? Thanks Please re-check your config/my.cnf|my.ini/commandline options looking for --skip-networking ...or your firewall -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Having Problem With Load Files and AutoNumber id fields
John Kopanas wrote:
Argh... I am autogenerating a load file after parsing and massaging a
text file I get from an AS/400 system. My first field is an id that
is on auto_number so I put a tab at the begging of the line for each
one of my records... it throws a warning for each line... if instead
of putting an autonumber I put a random number it works perfectly.
How can I get it to work?
Sample line in my load file that works:
1306600121001001467SMART ALARMS INC201
MILLWAY AVE UNIT #8\N\NVAUGHANONL4K 5K80
1999-9-1
00:00:001999-9-1 00:00:00
Sample line that does not work:
306600121001001467SMART ALARMS INC201 MILLWAY AVE UNIT
#8\N\NVAUGHANONL4K 5K801999-9-1 00:00:00
1999-9-1 00:00:00
My code that generates the sample line that does not work:
tempfile.puts
\t#{record[0,22].strip}\t#{record[22,32].strip}\t#{record[54,32].strip}\t#{record[86,32].strip}\t#{record[118,32].strip}\t#{record[150,30].strip}\t#{record[180,2].strip}\t#{record[182,7].strip}\t0\t2000-01-01
00:00:00\t2000-01-01 00:00:00
How can I get mysql to put the auto_numbers in for me? Why is it even
accepting my numbers?
Thanks :-)
Does anyone also know how I can see the warnings created by loadfile?
All I just see is the summary with skipped and warnings.
Thanks :-)
Try with 0 at autoinc position. If 0 or NULL doesn't do the trick...
well perhaps you should check your --sql-mode=
http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html
NO_AUTO_VALUE_ON_ZERO
NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns.
Normally, you generate the next sequence number for the column by
inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses
this behavior for 0 so that only NULL generates the next sequence number.
This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT
column. (Storing 0 is not a recommended practice, by the way.) For
example, if you dump the table with mysqldump and then reload it, MySQL
normally generates new sequence numbers when it encounters the 0 values,
resulting in a table with contents different from the one that was
dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file
solves this problem. mysqldump now automatically includes in its output
a statement that enables NO_AUTO_VALUE_ON_ZERO, to avoid this problem.
HTH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Restore Help
Jesse wrote: Strange it ran just fine here on my 5.0.22-nt with sample MyISAM table `alumni`... Is your table `alumni` MyISAM or Innodb? It is MyISAM. Here are few more pointers: 1. If it is possible *always* try latest version first when solving problems. In your case 5.0.22 I think... You were right. I updatd to 5.0.22, and the restore works just fine now. However, I've got one question. when I do a SELECT version(); now, it returns 5.0.22-community-nt. What is that? Does it make a difference? Did I download the wrong version? 3. For single line CREATE TRIGGER changing DELIMITER wasn't needed actually... but if mandatory I would personally write it like this: Unfortunately, I'm not writing it. I'm dealing with what MySQLDump gives me. Thanks for your help. I think the problem is resolved. Once the 5.0.22-community thing is resolved. Don't know if I should look into this or not. Thanks, Jesse I don't know why they renamed it again ?!? For better explanation and comparison take look here: http://www.mysql.com/network/compare.html http://www.mysql.com/products/database/ http://www.mysql.com/products -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Restore Help
Jesse wrote: Are you sure this is the right line - I mean the whole statement? You are right, I did not include the whole statement. Here's the entire section: /*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/; DELIMITER ;; /*!50003 SET SESSION SQL_MODE=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION */;; /*!50003 CREATE TRIGGER `AlumniAddDate` BEFORE INSERT ON `alumni` FOR EACH ROW SET NEW.AddDate=Now() */;; DELIMITER ; /*!50003 SET SESSION [EMAIL PROTECTED] */; SELECT VERSION(); on my server returns 5.0.15-nt. This appears to be sufficient to execute the statement. ...perhaps as some comments suggest try to remove single apostrophes /which sometimes puzzle parser/ and see if it works. What puzzles me personally is double ;; at the end?! As you can probably see from the rest of the statement that I've included above, ;; ends the current line becuase the delimiter was changed before hand. Any ideas why this won't execute? I could go through the entire backup file and remove the comments, but this would take quite a while, and I'd rather it execute properly to begin with, but not sure why it's not executing now. Thanks, Jesse Strange it ran just fine here on my 5.0.22-nt with sample MyISAM table `alumni`... Is your table `alumni` MyISAM or Innodb? Here are few more pointers: 1. If it is possible *always* try latest version first when solving problems. In your case 5.0.22 I think... 2. Yes 5.0.15-nt should run commented code just fine - I still think problem is in that multiline comment... can you try running it from console (or to edit file with text editor if its only at line 29765) and see what happens? - just try to remove backticks first. I read somewhere on the forums for problems with some spec. characters `'..etc. in comments in earlier versions. 3. For single line CREATE TRIGGER changing DELIMITER wasn't needed actually... but if mandatory I would personally write it like this: DELIMITER //; DROP TRIGGER `AlumniAddDate`// create trigger `AlumniAddDate` BEFORE INSERT on `alumni` for each row SET NEW.AddDate=Now(); // DELIMITER ;// -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQLHotCopy
Jesse wrote: I read in the manual that MySQLHotCopy would be better than MySQLDump for backing up MyISAM tables. However, I cannot find a .exe in the bin directory by that name. I found a script by that name in a 4.1 installation that I have, but I don't think it's the latest version. Is this a script? If so, does anyone know where can I get the latest copy? I've checked MySQL.com, but can't seem to find it there. Thanks, Jesse Sorry pal: It runs on Unix and NetWare. What version are you using? I assume Windows at least ( if still looking for an .exe :-) ) Then please read this: http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html 8.13. mysqlhotcopy — A Database Backup Program mysqlhotcopy is a Perl script that was originally written and contributed by Tim Bunce. It uses LOCK TABLES, FLUSH TABLES, and cp or scp to make a database backup quickly. It is the fastest way to make a backup of the database or single tables, but it can be run only on the same machine where the database directories are located. mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. It runs on Unix and NetWare. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Restore Help
Jesse wrote: I have a backup that was created by a MySQL 5 server using MySQLDump. When I try to restore the database using the following command: mysql -u root -p -D BPA c:\backup\mydata.sql I get the error: ERROR 1064 (42000) at line 29765: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/' at line 1 Here is what line 29765 says in the backup file: /*!50003 CREATE TRIGGER `AlumniAddDate` BEFORE INSERT ON `alumni` FOR EACH ROW SET NEW.AddDate=Now() */;; This is obviously one of the triggers that I've created. I don't know why it's commented them out in the backup, but I don't seem to be able to overcome this. I'd rather it ignore these lines anyway. How do I get passed this? Thanks, Are you sure this is the right line - I mean the whole statement? Also please read (with comments): http://dev.mysql.com/doc/refman/5.0/en/comments.html If you add a version number after the ‘!’ character, the syntax within the comment is executed only if the MySQL version is greater than or equal to the specified version number. The TEMPORARY keyword in the following comment is executed only by servers from MySQL 3.23.02 or higher: CREATE /*!32302 TEMPORARY */ TABLE t (a INT); ...perhaps as some comments suggest try to remove single apostrophes /which sometimes puzzle parser/ and see if it works. What puzzles me personally is double ;; at the end?! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Specified key was too long; max key length is 1000 bytes (UNIQUE KEY on multiple columns)
Lubomir Host 'rajo' wrote: Description: Migration problem from 4.0.22 to 5.0.x. I can't create following table on 5.0.x version of mysql. Problem does't apper on 4.0.x version: CREATE TABLE `PHONESlog_uniq` ( `user_agent` varchar(80) default NULL, `http_x_wap_profile` varchar(255) default NULL, `pid` smallint(5) unsigned NOT NULL default '0', UNIQUE KEY `uniq_phone_key` (`user_agent`,`http_x_wap_profile`,`pid`) ) TYPE=MyISAM; How-To-Repeat: server 1: mysql SELECT VERSION(); ++ | VERSION() | ++ | 4.0.22-log | ++ 1 row in set (0.00 sec) mysql CREATE TABLE `PHONESlog_uniq` ( `user_agent` varchar(80) default NULL, `http_x_wap_profile` varchar(255) default NULL, `pid` smallint(5) unsigned NOT NULL default '0', UNIQUE KEY `uniq_phone_key` (`user_agent`,`http_x_wap_profile`,`pid`) ) TYPE=MyISAM; Query OK, 0 rows affected (0.06 sec) server 2: mysql SELECT VERSION(); +---+ | VERSION() | +---+ | 5.0.18| +---+ 1 row in set (0.00 sec) mysql CREATE TABLE `PHONESlog_uniq` ( `user_agent` varchar(80) default NULL, `http_x_wap_profile` varchar(255) default NULL, `pid` smallint(5) unsigned NOT NULL default '0', UNIQUE KEY `uniq_phone_key` (`user_agent`,`http_x_wap_profile`,`pid`) ) TYPE=MyISAM; ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes Fix: Submitter-Id: submitter ID Originator: Lubomir Host Organization: Lubomir Host 'rajo' rajo AT platon.sk ICQ #: 257322664 ,''`. Platon Group http://platon.sk/ : :' : Homepage: http://rajo.platon.sk/ `. `' http://www.gnu.org/philosophy/no-word-attachments.html `- MySQL support: extended email support Synopsis: Migration problem from 4.0.22 to 5.0.x. I can't create following table on 5.0.x version of mysql. Problem does't apper on 4.0.x version Severity: serious Priority: high Category: mysql Class: sw-bug Release:mysql-5.0.22-Debian_3 (Debian Etch distribution) C compiler:gcc (GCC) 4.1.2 20060613 (prerelease) (Debian 4.1.1-4) C++ compiler: g++ (GCC) 4.1.2 20060613 (prerelease) (Debian 4.1.1-4) Environment: Debian GNU/Linux or FreeBSD, all versions of MySQL 5.0.x System: Linux Idea 2.6.15-1-686 #2 Mon Mar 6 15:27:08 UTC 2006 i686 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Using built-in specs. Target: i486-linux-gnu Configured with: ../src/configure -v --enable-languages=c,c++,java,f95,objc,ada,treelang --prefix=/usr --enable-shared --with-system-zlib --libexecdir=/usr/lib --without-included-gettext --enable-threads=posix --enable-nls --program-suffix=-4.0 --enable-__cxa_atexit --enable-clocale=gnu --enable-libstdcxx-debug --enable-java-awt=gtk-default --enable-gtk-cairo --with-java-home=/usr/lib/jvm/java-1.4.2-gcj-4.0-1.4.2.0/jre --enable-mpfr --disable-werror --with-tune=i686 --enable-checking=release i486-linux-gnu Thread model: posix gcc version 4.0.4 20060507 (prerelease) (Debian 4.0.3-3) Compilation info: CC='gcc' CFLAGS='-DBIG_JOINS=1 -O2' CXX='g++' CXXFLAGS='-DBIG_JOINS=1 -felide-constructors -fno-rtti -O2' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Jun 28 23:32 /lib/libc.so.6 - libc-2.3.6.so -rwxr-xr-x 1 root root 1177116 May 31 08:59 /lib/libc-2.3.6.so -rw-r--r-- 1 root root 2628734 Jun 8 09:25 /usr/lib/libc.a -rwxr-xr-x 1 root root 204 Jun 8 09:07 /usr/lib/libc.so lrwxrwxrwx 1 root root 19 Jun 28 22:04 /usr/lib/libc-client.a - /usr/lib/c-client.a lrwxrwxrwx 1 root root 28 Jun 28 22:05 /usr/lib/libc-client.so.2002edebian - libc-client.so.2002edebian.1 -rw-r--r-- 1 root root 772872 Jan 16 21:34 /usr/lib/libc-client.so.2002edebian.1 Configure command: ./configure '--build=i486-linux-gnu' '--host=i486-linux-gnu' '--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--datadir=/usr/share' '--sysconfdir=/etc/mysql' '--localstatedir=/var/lib/mysql' '--includedir=/usr/include' '--infodir=/usr/share/info' '--mandir=/usr/share/man' '--with-server-suffix=-Debian_3' '--with-comment=Debian Etch distribution' '--enable-shared' '--enable-static' '--enable-thread-safe-client' '--enable-assembler' '--enable-local-infile' '--with-big-tables' '--with-raid' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql' '--with-libwrap' '--with-vio' '--without-openssl' '--without-docs' '--without-bench' '--without-readline' '--with-extra-charsets=all' '--with-innodb' '--with-isam' '--with-archive-storage-engine' '--with-csv-storage-engine' '--with-federated-storage-engine' '--without-embedded-server' '--with-ndbcluster' '--with-ndb-shm' '--without-ndb-sci' '--without-ndb-test' '--with-embedded -server' '--with-embedded-privilege-control' '--with-ndb-docs' 'CC=gcc' 'CFLAGS=-DBIG_JOINS=1 -O2' 'CXXFLAGS=-DBIG_JOINS=1 -felide-constructors -fno-rtti -O2' 'CXX=g++'
Re: UPDATE Based on Relation
Jesse wrote: I need to be able to do an UPDATE based on a relation. The following code works in Microsoft SQL. What is the MySQL Equivalent? UPDATE Chapters SET MatSentDate='2006-07-04' FROM Invoices I JOIN Chapters C ON C.ID=I.ChapterID JOIN Schools S ON S.ID=C.SchoolID WHERE I.InvoiceDate = '2006-01-06' AND I.InvoiceDate = '2006-01-31' AND MatSentDate IS NULL Thanks, Jesse Hi, Jess What version of mysql you're using? Since v 4.0 (I think) it is possible to use query like this: update t1, t2 set t1.field = t2.value where t1.this = t2.that; or in your case s.th. like: UPDATE Invoices I JOIN Chapters C ON C.ID=I.ChapterID JOIN Schools S ON S.ID=C.SchoolID SET C.MatSentDate='2006-07-04' WHERE I.InvoiceDate = '2006-01-06' AND I.InvoiceDate = '2006-01-31' AND MatSentDate IS NULL - for more insight please read for multi-table updates here: http://dev.mysql.com/doc/refman/5.1/en/update.html ... You can also perform UPDATE operations covering multiple tables. However, you cannot use ORDER BY or LIMIT with a multiple-table UPDATE. The table_references clause lists the tables involved in the join. Its syntax is described in Section 13.2.7.1, “JOIN Syntax”. Here is an example: UPDATE items,month SET items.price=month.price WHERE items.id=month.id; HTH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Autoindexing
If you are using autoincrement filed you could try this: ALTER TABLE `my_database`.`my_table` AUTO_INCREMENT = 201; ...or else if it is some stored proc you should find and edit table where it sotres index/counter data.. Tom Ray [Lists] wrote: Hey, I have a really simple question (I hope)..I have a database that has a field in it that autoindexes the number. I screwed up and imported a bunch of wrong data. Up to row 200 it's right, beyond that it was wrong so I had delete it all. The problem now is the autoindexing is set to 1105, I want to change it so the next insert gets a number of 201 not 1105. I've tried to change it via phpMyAdmin but it keeps going back to 1105. Is there anyway I can do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: name 'Szczech' returns more rows then 'Szczec%'
Lukasz Budnik wrote:
Hi All,
I'm using 5.0.21 on Gentoo.
I've got very strange problem.
I have clients table with id_c and name columns.
I'm running simple selects:
mysql select id_c, name, concat('#',name, '@'), length(name) from clients
where name like 'Szczec%';
+---+--+---+--+
| id_c | name | concat('#',name, '@') | length(name) |
+---+--+---+--+
| 30181 | Szczech | #Szczech@ |7 |
+---+--+---+--+
1 row in set (0.00 sec)
mysql select id_c, name, concat('#',name, '@'), length(name) from clients
where name like 'Szczech';
+---+--+---+--+
| id_c | name | concat('#',name, '@') | length(name) |
+---+--+---+--+
| 30181 | Szczech | #Szczech@ |7 |
| 30693 | Szczech | #Szczech@ |7 |
+---+--+---+--+
2 rows in set (0.00 sec)
ups... what's going on?
% stands for (m)any character(s) but returned 1 row,
when % replaced with 'h' the same query returned 2 rows.
I'm using latin2 charsets but 'h' is standard latin1 character.
Any ideas?
thanks in advance for any help
best regards
Lukasz Budnik
just a thought...
Can you try OPTIMIZE TABLE... or REPAIR TABLE ... [EXTENDED] first
then try those queries again
HTH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: name 'Szczech' returns more rows then 'Szczec%'
Lukasz Budnik wrote: Hi Remo, just a thought... Can you try OPTIMIZE TABLE... or REPAIR TABLE ... [EXTENDED] first then try those queries again HTH I've just run those statements, results: optimize | status | OK repair | status | OK but like 'Szczec%' still returns 1 row (now I know that it should return at least 7 rows) best regards Lukasz Budnik Well then can you try ... WHERE BINARY name like 'Szczec%'; Could it happen that you upgraded from 4.0.x directly to 5.0.x mysql.com says that is a bad thing and not to skip versions i.e. 3.x 4.0.x must be upgraded first to 4.1.x and then to 5.0.x or... you're heading for trouble(s) esp. charset issues http://dev.mysql.com/doc/refman/5.1/en/upgrade.html http://dev.mysql.com/doc/refman/4.1/en/upgrade.html As a general rule, we recommend that when you upgrade from one release series to another, you should go to the next series rather than skipping a series. For example, if you currently are running MySQL 4.0 and wish to upgrade to a newer series, upgrade to MySQL 4.1 rather than to 5.0 or 5.1. ... You can always move the MySQL format files and data files between different versions on the same architecture as long as you stay within versions for the same release series of MySQL. If you change the character set when running MySQL, you must run myisamchk -r -q --set-collation=collation_name on all MyISAM tables. Otherwise, your indexes may not be ordered correctly, because changing the character set may also change the sort order. HTH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import from unknown format (.bdd .ind .mor .ped)
http://perso.easynet.fr/cibderf/tout.html HTH Pedro mpa wrote: Greetings. I'm building an application and I need to import data to mysql from a db format I don't know. Unfortunately the person in charge of the data won't be reachable for the next 2 weeks and I want to continue my work. I was wondering if anyone knows the format extensions like: .bdd .ind .mor .ped Thanks in advance. Apologies for my bad English. Pedro. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.18 restore dump file 'max_allowed_packet' error
Luke Vanderfluit wrote: Hi. I've got mysql 4.0.18 installed on a sun X4100 running solaris. This is just a 32 bit version of mysql. I've reverted back to this version after trying mysql 5, 4.0.26 and 4.0.18 64bit. Those versions were all unstable on 64bit, that is, the server would just go away for no apparent reason. This would ofter rear it's head when importing a dump file, the dump file I need to import is around 10 GIG, but also at other unpredictable times. I have this same database running on another machine running solaris, with no problems, except speed/performance. This other machine is the one that produces the dump file I'm trying to import. It does that with the following command: /usr/local/bin/mysqldump --opt --complete-insert --max_allowed_packet=32M rt3 | bzip2 -9 rt3.out-`date +\%Y\%m\%d-\%H`.bz2 I'm trying to restore the file on the new machine and I'm getting a 'max_allowed_packet' error: ERROR 1153 at line 162: Got a packet bigger than 'max_allowed_packet I've tried different settings for this in my.cnf, from 32 up to 1024M, and I still get the error. Is there anything I can do to remedy this? Kind regards. Yes in principle rules are: http://www.mysql.com/news-and-events/newsletter/2003-08/a000216.html but you *must* enforce them on server too (not just on mysqldump): http://dev.mysql.com/doc/refman/4.1/en/packet-too-large.html so either run second mysql with: mysqld --max_allowed_packet=32M or edit your my.cnf and restart mysqld: You can also use an option file to set max_allowed_packet. For example, to set the size for the server to 16MB, add the following lines in an option file: [mysqld] max_allowed_packet=32M Before MySQL 4.0, use this syntax instead: [mysqld] set-variable = max_allowed_packet=32M or (if you're trying from mysql console then run it with) mysql --max_allowed_packet=32M HTH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Delphi
[EMAIL PROTECTED] wrote: Hi, Anyone out there using MySQL and Delphi together? Would love to exchange experiences. Regards, John John Barrington [EMAIL PROTECTED] +27 11 6489876 You'd better try Borland's newsgroups like: borland.public.delphi.database.dbexpress etc. - that's where the problems usually are ;-) HTH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Byte Swapping
David Godsey wrote: This might be an unusual question because mysql always stores in little endian for all platforms (if I'm not mistaken). However, I am receiving data from several different platforms (custom boards with various processors) in raw form. I will have stored as a configuration option what endianess it is. So what I need to do is write a procdure, or use a mysql function (if there is one available) to byte swap the data (for integer types). I'm really just hoping someone has done this, or it is implemented as a mysql function and I just wasn't able to find it. Any help will be appreciated. Thanks. Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey Hi, David You could also try using Bit Functions: http://dev.mysql.com/doc/refman/5.0/en/bit-functions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: changing the UNIX user for mysql server startup
Perumal, Jai wrote: Hi, We install the mysql version 5.0.16 on Linux Red hat as the root operating system user. After the install we changed the ownership and group to a mysql UNIX user. We tested starting and stopping the mysql server using the mysql UNIX user account and it worked fine. But when we reboot the Linux server, the /etc/init.d/mysql script starts the mysql server processes as the root user not as the mysql UNIX user. What is the best way to start the mysql server with the mysql UNIX owing the mysql server processes instead of it starting with the root user owing the mysql processes, when using the /etc/init.d/mysql script and the server reboots? Thanks Regards Jai # /usr/sbin/mysqld --help | grep \-\-user -u, --user=name Run mysqld daemon as user You could also read : 4.3. Specifying Program Options http://dev.mysql.com/doc/refman/5.1/en/program-options.html HTH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: is this normal?
kalin mintchev wrote: system freebsd 4.10 mysql is binary: mysql-standard-5.0.15-freebsd4.7-i386 after mysql installation and root password set up i can type: /usr/local/mysql/bin/mysql -u no_matter_what_user_here and i get right in. even if i type -p. i do get the password prompt but doesn't matter what i type there - even nothing i get straight in... is this normal?? thanks... What is the error message? Is yor server running? Usually by default this should work: /usr/local/mysql/bin/mysql i.e. default user root without password connecting from localhost or /usr/local/mysql/bin/mysql -u root or (preferably disabled if possible for security reasons if localhost is just enough) /usr/local/mysql/bin/mysql -h host.domain.com -u root -p after mysql installation and root password set up i can... Well if you also changed some RIGHTS/GRANTs or changing some options in your my.cnf e.g. --skip-networking sometimes lead to this confusion ..or sometimes its just good old Caps Lock :) also look in the manual (depends on your vesrion): http://dev.mysql.com/doc/refman/5.1/en/can-not-connect-to-server.html http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html http://dev.mysql.com/doc/refman/4.1/en/can-not-connect-to-server.html HTH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: is this normal?
kalin mintchev wrote: system freebsd 4.10 mysql is binary: mysql-standard-5.0.15-freebsd4.7-i386 after mysql installation and root password set up i can type: /usr/local/mysql/bin/mysql -u no_matter_what_user_here and i get right in. even if i type -p. i do get the password prompt but doesn't matter what i type there - even nothing i get straight in... is this normal?? thanks... Sorry I first misread yor question (I hope I cancelled prev. post on time). I thought you have problems connecting to mysql while actual problem is: Why it is so easy for everyone (even from outside) to connect? Yes this is normal: default user is root *without password* if you wan't to secure your installation... well just read the manual :-) http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html also http://dev.mysql.com/doc/refman/5.0/en/security.html HTH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Assigment sign not work on Delphi
Which component, Spider?
with TSQLQuery try setting: ParamCheck property to false. Look in help
for your specific component...
To Leo's :
Delphi/dbExpress uses ':' character to specify SQL Parameters much alike
mysql uses '@' for variables :)
e.g.:
q.sql := 'select * from tbl where col1=:val1';
// then you can
// if ParamCheck := True of course - which it *is* by default to do:
q.Params[0] := 'somevalue'; //or even e.g.:
q.Params.ParamByName('val1').AsInteger := 999;
more thorough example is in help:
Query2.SQL.Clear;
Query2.SQL.Add('INSERT INTO COUNTRY (NAME, CAPITAL, POPULATION)');
Query2.SQL.Add('VALUES (:Name, :Capital, :Population)');
Query2.Params[0].AsString := 'Liechtenstein';
Query2.Params[1].AsString := 'Vaduz';
Query2.Params[2].AsInteger := 42;
Query2.ExecSQL;
The Nice Spider wrote:
Using Delphi to with this query: SELECT TOTAL :=
PRICE * QTY FROM
INV_PRODUCT
will caused error Parameter object is improperly
defined. Inconsistent or
incomplete information was provided. because Delphi
look it as Parameter (a
parameter of query in Delphi using : at the
beginning).
Is it better for MySQL using = rather than := ? Or
is there any setting
to set MySQL to accept the = sign?
__
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Column type problem
As seen in manual there's no exact data type for your kind of data: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html see also: http://dev.mysql.com/doc/refman/5.0/en/column-types.html so you can: 1. Use 1 timestamp column and 1 INT column just for milliseconds 2. Use BIGINT [or even DECIMAL with new high-precision arithmetic :-) ] column to store time elapsed since ... some epoch in milliseconds or centiseconds so the wrap-around period is ten times as long. ;-) Yet be carefull with MySQL TIME Type : The following alternative formats are also understood: SS, MMSS, HHMMSS, HHMMSS.fraction. Note that MySQL doesn't (yet) store the fraction. http://dev.mysql.com/doc/refman/5.0/en/time.html HTH Longstreth, Lance wrote: I am trying to create a table to import data on cpu usage based on certain programs. the following is a sample of the data. When I import into the tables their is no field type that matches up to this type of time data. The field type (time) is close to it but it drops of the :nn. Is their some way around this? or I'm just out of luck. Job time(format(hour:min:sec.nn)) - APPLRASI 01:18.9 APPLRCTL 22.9 APPLRCUS08:05.0 APPLRDLE26.99 APPLRDLS53.3 ARST831S 22.12 BAPP00014.36 BAPP00045.57 BAPP00058.01 BAPP00065.1 BAPP0007 23.02 BAPP0008 2.35 Lance Longstreth Bandag Inc GITS Infrastructure Security and Engineering zOS systems/Linux [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DELETE syntax
http://dev.mysql.com/doc/mysql/en/delete.html
...
Multiple-table syntax:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*] ...]
FROM table_references
[WHERE where_definition]
Or:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name[.*] [, tbl_name[.*] ...]
USING table_references
[WHERE where_definition]
...
From MySQL 4.0, you can specify multiple tables in the DELETE
statement to delete rows from one or more tables depending on a
particular condition in multiple tables. However, you cannot use ORDER
BY or LIMIT in a multiple-table DELETE.
The first multiple-table DELETE syntax is supported starting from MySQL
4.0.0. The second is supported starting from MySQL 4.0.2. The
table_references part lists the tables involved in the join. Its syntax
is described in Section 13.2.7.1, “JOIN Syntax”.
That's fo MyISAM and.. for InnoDB read Rhino's answer to Merlin's
thread (just above) deleting rows in 2 tables about Foreign key(s) and
on delete cascade
[EMAIL PROTECTED] wrote:
Hi,
I need to know the syntax I can use for a certain delete statement. This is
the idea:
- A table with users, which has at least these fields, a user id field and a
field containing its status (active, disabled, review).
- A table that links certain users to each other, containing at least these
fields: a link id, a user id 1 and a user id 2.
Now I want to delete all records from the second table when any of the
linked users is disabled, eg something like this:
DELETE FROM `matches` WHERE `userAid` IN ('SELECT `ID` FROM `users` WHERE
`status`='disabled')' OR `userBid` IN ('SELECT `ID` FROM `users` WHERE
`status`='disabled')'
The query must be run on a mysql server version 4.0.24
Thanks for any help with this one,
Mark
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 Bugs
1) that's common problem when doing custom install on PATH containing spaces like C:\Program Files. Solution - try custom location with short(er) path and without spaces like C:\mysql5013\. Probably that will solve problem 2. if not then.. 2. Edit my.ini looking for skip-innodb or related options. Read docs 1st. HTH Philippe HAMEAU wrote: Hi, I noticed 1 bug with 5.0.13rc (win32 msi) : when installing mysql in a custom location (eg c:\program files\easyphp\mysql), 1) the service will not get installed properly 2) mysqld will complain about InnoDB engine not present. Hope this helps. Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 Bugs
...and 3. Are you running another instance of mysql? Then perhaps both compete for same resource(s) like: port 3306 ;-) Remo Tex wrote: 1) that's common problem when doing custom install on PATH containing spaces like C:\Program Files. Solution - try custom location with short(er) path and without spaces like C:\mysql5013\. Probably that will solve problem 2. if not then.. 2. Edit my.ini looking for skip-innodb or related options. Read docs 1st. HTH Philippe HAMEAU wrote: Hi, I noticed 1 bug with 5.0.13rc (win32 msi) : when installing mysql in a custom location (eg c:\program files\easyphp\mysql), 1) the service will not get installed properly 2) mysqld will complain about InnoDB engine not present. Hope this helps. Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 Bugs
Philippe HAMEAU wrote: Hi, I noticed 1 bug with 5.0.13rc (win32 msi) : when installing mysql in a custom location (eg c:\program files\easyphp\mysql), 1) the service will not get installed properly 2) mysqld will complain about InnoDB engine not present. Hope this helps. Phil Have you read: http://dev.mysql.com/doc/mysql/en/windows-troubleshooting.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: converting access (.mdb) files...
Perhaps you could try MySQL Migration Toolkit http://dev.mysql.com/downloads/migration-toolkit/1.0.html still beta though... NB! ATTENTION: Before installing the MySQL Migration Toolkit please download and install the Java 5.0 or 1.4.2 Runtime Environment (JRE) from http://java.sun.com NOTICE: If you want to use the MySQL Migration Toolkit with Oracle please make sure to attach the Oracle JDBC driver (ojdbc14.jar) by selecting the main menu item [Tools] [Attach JDBC Driver]. A typical location of that file on Windows would be 'C:\Oracle\ora92\jdbc\lib'. Craig Hoffman wrote: Anyone know a way to convert MS Access DB files (.mdb) into a format MySQL can import? I'm on a Mac hence I don't have Access. Best, CH ___ Craig Hoffman www.eclimb.net [EMAIL PROTECTED] iChat / AIM: m0untaind0g ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.26 has been released
...and when MySQL-shared-compat-4.0.26-0.i386.rpm will be released? rpm (and some older tools) complains :( There is only: Dynamic client libraries (including 3.23.x libraries)4.0.25-0482.1K Joerg Bruehe wrote: Hi, MySQL 4.0.26, a new version of the popular Open Source/Free Software Database Management System, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is a bugfix release for the recent production version. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. News from the ChangeLog: Functionality added or changed: * Added the mysql_get_client_version C API function to the embedded server library. (It was present in the regular client library but inadvertently omitted from the embedded library.) (Bug #10266 (http://bugs.mysql.com/10266)) Bugs fixed: * An optimizer estimate of zero rows for a non-empty InnoDB table used in a left or right join could cause incomplete rollback for the table. (Bug #12779 (http://bugs.mysql.com/12779)) * Query cache is switched off if a thread (connection) has tables locked. This prevents invalid results where the locking thread inserts values between a second thread connecting and selecting from the table. (Bug #12385 (http://bugs.mysql.com/12385)) * For PKG installs on Mac OS X, the preinstallation and postinstallation scripts were being run only for new installations and not for upgrade installations, resulting in an incomplete installation process. (Bug #11380 (http://bugs.mysql.com/11380)) * On Windows, applications that used the embedded server made it not possible to remove certain files in the data directory, even after the embedded server had been shut down. This occurred because a file descriptor was being held open. (Bug #12177 (http://bugs.mysql.com/12177)) * Creation of the mysql group account failed during the RPM installation. (Bug #12348 (http://bugs.mysql.com/12348)) * Attempting to repair a table having a fulltext index on a column containing words whose length exceeded 21 characters and where myisam_repair_threads was greater than 1 would crash the server. (Bug #11684 (http://bugs.mysql.com/11684)) * When two threads compete for the same table, a deadlock could occur if one thread has also a lock on another table through LOCK TABLES and the thread is attempting to remove the table in some manner and the other thread want locks on both tables. (Bug #10600 (http://bugs.mysql.com/10600)) Bye, Joerg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Questions about last_insert_id() instead of @@IDENTITY
http://dev.mysql.com/doc/mysql/en/insert-select.html There are 2 forms of INSERT INTO statement 1. INSERT INTO tbl_name (...) VALUES(...); 2. INSERT INTO tbl_name (...) SELECT ...; there's no : INSERT INTO tbl_name (...) VALUES(...) SELECT ...; its either ... or in MySQL i.e. aither provide values or get then form SELECT ... but not both in same INSERT ... so indeed: 'INSERT INTO rps_names (nameid, name) VALUES (NULL, Ryan Smithland) SELECT LA' - is invalid query! Ryan Stille wrote: I am migrating from MS SQL Server to MySQL 4.1. I have code in my ColdFusion/SQL Server application that went something like this: cfquery name=insertquery SET NOCOUNT ON INSERT INTO TABLE () SELECT @@IDENTITY AS adid SET NOCOUNT OFF /cfquery That whole block was processed in coldfusion as a single query. I gave it a name of insertquery, and I can get the ID back as insertquery.adid. I am having trouble getting the same functionality with MySQL. cfquery name=insertquery INSERT INTO TABLE (...) SELECT LAST_INSERT_ID() as adid /cfquery I get this MySQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO rps_names (nameid, name) VALUES (NULL, Ryan Smithland) SELECT LA' at line 2 With MSSQL the trick was the NO COUNT. That told the database server not to return a count of the rows affected. Is there something similar I need to do in MySQL to get this to work? Thanks, -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: long text insert failure
Liz you didn't mention by I suppose you're using Windows client too i.e.
mysql.exe. A quick test revealed that in such a case char limit is =255
i.e. that's limitation of Windows Command Prompt: command.com or in case
of Win NT,2000,XP - cmd.exe
try writing your long SQL command in text file and import from there ;-)
try:
mysql (your host, login etc. options here ) import.sql
or from within mysql prompt source import.sql;
HTH
Elizabeth Bonifacio wrote:
Dear Guys,
Can please anyone advice me how to successfully insert long text data
into my innodb table rawlog with table stucture as follows:
mysql desc rawlog;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| log | longtext | YES | | NULL| |
+---+--+--+-+-+---+
1 row in set (0.03 sec)
here is one sample of the syslog data I'm trying to insert into the
table without success:
insert into rawlog values ('133date=2005-07-25 time=12:38:23
device_id=FGT1002105200379 log_id=0022010001 type=traffic
subtype=allowed pri=notice vd=root SN=1321 duration=180 policyid=1
proto=17 service=29716/udp status=accept src=192.168.2.63
srcname=192.168.2.63 dst=193.11.28.37 dstname=193.11.28.37
src_int=internal dst_int=external sent=46 rcvd=86 sent_pkt=1
rcvd_pkt=1 src_port=10055 dst_port=29716 vpn=n/a tran_ip=202.189.48.98
tran_port=43957 dir_disp=org tran_disp=noop');
I've already tried changing my column data type from varchar to text
types but still the insert statement cannot be typed all the way to
the end of statement.I can only type half of the syslog data. Does it
has something to do
with my system? I'm using mysql version 4.1.11 running in windows xp
32 bit. My max_allowed_packet is 1048576, do I need to increase this?
by how much?
Please advice me as a need to insert lots of long syslog data in this table.
All the best.
Elizabeth
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
About column privileges, COLUMN_INSERT...
I want to allow some users to SELECT, INSERT, UPDATE all columns in a table EXCEPT 1 (last one). Is it possile in MYSQL and how? So my question is : What COLUMN_INSERT privilege does? How can I INSERT_COLUMN without having to insert whole row? Does COLUMN_INSERT = TABLE_INSERT? I've found some answers in Manual but: In MySQL, if you have the INSERT privilege on only some of the columns in a table, you can execute INSERT statements on the table; the columns for which you don't have the INSERT privilege are set to their default values. Standard SQL requires you to have the INSERT privilege on all columns. 1. What about UPDATE privilege: 2. the columns for which you don't have the INSERT privilege are set to their default values. seems incorrect: When i try to INSERT or UPDATE last column for which I have only TABLE_SELECT privilege using MySQL Front 2.5 on Mysql 4.0.24 server it's OK ?!?! Value changed and visible from everywhere! NO error(s)... Mysql Front uses older client dll (3.23 I think) but shouldn't this rule be enforced from server (side) not client (side)? MySQL Front 3.0+ behaves corectly : SQL Error 1142 UPDATE COMMAND denied to user ... for table tbl_name. yet again if I try the same with latest MySQl Query Browser 1.1.6: ! UPDATE command denied to user: '[EMAIL PROTECTED]' for table 'tbl_name' Nothing changes P.S. How older client cheats newest server is still mistery to me... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
More on column privileges, COLUMN_UPDATE...
http://dev.mysql.com/doc/mysql/en/grant.html Sorry for previous post - my mistake: I added record as admin and continued using that user ;-) now logged as ordinary user another problem(s) arise: MySQL Server 4.0.24, trying varoius clients (dlls) 3.23, 4.0.x, 5.x? comes with MySQL Query Browser 1.1.6 to no avail Manual still states privileges are evaluated this way: global privileges OR (database privileges AND host privileges) OR table privileges OR column privileges i.e. my [EMAIL PROTECTED] has NO global priv, has DB_SELECT only, host = %, has TABLE_SELECT only and has NO column privileges on all columns except UPDATE, INSERT, SELECT on last_col so.. by manual it is possible to INSERT a COLUMN/ROW and/or UPDATE last_col [OR column privileges IN effect] SHOW GRANTS FOR user - shows privs only downto TABLE level only :( yet they really ARE in mysql.columns_priv? In MySQL, if you have the INSERT privilege on only some of the columns in a table, you can execute INSERT statements on the table; the columns for which you don't have the INSERT privilege are set to their default values. Standard SQL requires you to have the INSERT privilege on all columns. alas :-( UPDATE tbl set last_col=25; !UPDATE command denued to [EMAIL PROTECTED] for table tbl and for INSERT !INSERT command denied to [EMAIL PROTECTED] for table tbl Why SHOW GRANTS FOR user doesn't show Column_priv since they are in mysql.columns_priv? Is this server version issue mysql 4.0.24 accepts column_priv w/o error(s) justs doesn't enforce them? -- Remo Tex wrote: I want to allow some users to SELECT, INSERT, UPDATE all columns in a table EXCEPT 1 (last one). Is it possile in MYSQL and how? So my question is : What COLUMN_INSERT privilege does? How can I INSERT_COLUMN without having to insert whole row? Does COLUMN_INSERT = TABLE_INSERT? I've found some answers in Manual but: In MySQL, if you have the INSERT privilege on only some of the columns in a table, you can execute INSERT statements on the table; the columns for which you don't have the INSERT privilege are set to their default values. Standard SQL requires you to have the INSERT privilege on all columns. 1. What about UPDATE privilege: 2. the columns for which you don't have the INSERT privilege are set to their default values. seems incorrect: When i try to INSERT or UPDATE last column for which I have only TABLE_SELECT privilege using MySQL Front 2.5 on Mysql 4.0.24 server it's OK ?!?! Value changed and visible from everywhere! NO error(s)... Mysql Front uses older client dll (3.23 I think) but shouldn't this rule be enforced from server (side) not client (side)? MySQL Front 3.0+ behaves corectly : SQL Error 1142 UPDATE COMMAND denied to user ... for table tbl_name. yet again if I try the same with latest MySQl Query Browser 1.1.6: ! UPDATE command denied to user: '[EMAIL PROTECTED]' for table 'tbl_name' Nothing changes P.S. How older client cheats newest server is still mistery to me... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: efficient query for: it's your birthday today
NB! ...WHERE doycol=312 Valid only for dates = 28.Feb OR Dates 29.Feb but not in leap year(s) ;-) doycol=DAYOFYEAR(2004-11-07) or doycol=312 Eric McGrane wrote: How about adding another column that stores the day of year for the birthday. You could then index on this column and your query would be for 11/7 would be doycol=DAYOFYEAR(2004-11-07) or doycol=312 E Jigal van Hemert [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I have date of birth stored in a DATETIME column and need to find for which persons a certain date is their birthday. I've tried so far: DATE_FORMAT (col, '%m%d') = '1107'= 0.2001 sec col LIKE '%-11-07%'= 0.1643 sec col RLIKE '-11-07'= 0.1702 sec Are there faster alternatives for MySQL 4.0.21 ? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: union, intersct and except operation?
SELECT DISTINCT study FROM table WHERE keyword='chemistry';
- Of course if it's 'chemistry' it IS ''computers' or anything else
'chemistry' 'computers'
so last part ot your SQL statement is obsolete
L a n a wrote:
Hello,
I've had a problem trying to get working except operator in mysql:
statement SELECT study from table WHERE keyword = 'chemistry' NOT
keyword = 'computers' returns SQL error
I've got the following not working solutions to my problem:
SELECT [distinct] study FROM table WHERE keyword = 'chemistry' AND
keyword (or !=) 'computers'
SELECT study FROM table WHERE keyword = 'chemistry' AND NOT (or OR NOT)
keyword = 'computers'
To explain better what result I need to get, there is table
Id Study keyword
1 Achemistry
2 Acomputers
3 Bchemistry
4 Bcomputers
5 Cchemistry
I need to return study C, because it has only 'chemistry' keyword(not
'computers'). However, all the suggested solutions return me wrong results:
SELECT study FROM table WHERE keyword = 'chemistry' AND keyword
'computers'
returns A, B, C results
SELECT study FROM table WHERE keyword = 'computers' AND keyword
'chemistry'
returns A,B
SELECT study FROM table WHERE keyword = 'chemistry' AND NOT keyword =
'computers'
returns 0 results
I tried SELECT T.data_id from table T WHERE T.keyword = 'chemistry'
EXCEPT SELECT T2.data_id from table T2 WHERE T2.keyword =computers .
But it gives SQL error.
I, also, tried SELECT * from table as t1 left join table as t2 on
t1.study=t2.study and not t2.keyword ='computers' where t1.keyword =
'chemistry' - gives SQL error as well.
Is there a way to get exception (not) operator working in mysql at all,
does it mean that only Union (or) and Intersect (and) available?
I know that in fulltext search it's possible to do: sql = SELECT ...
MATCH...AGAINST ('+chemestry -omputers IN BOOLEAN MODE)
What about one field search?
Thank you,
Lana
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing a space
Have you tried insertig non-breaking space instead #160 or 0xA0 ...i think so - this shoud be it /taken from here 3,02 KB (3 098 bytes)-All NON-Breaking/ HTH ;-) John Mistler wrote: Is there a way to force an invisible space character to be stored at the END of a string in a column (either VARCHAR or CHAR), so that when it is retrieved the space at the end is not cut off? theString + space or even, theString + space + space + space, etc. Currently, I can only get the string back as theString Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
