Re: finding count of spaces in a string
[EMAIL PROTECTED] ha scritto: We have numerous identical tables with a varchar column that holds data like this: 0 0 0 1 0 1 0 25 7 0 139 0 9. Essentially there are a bunch of integers with a single space as a separator. There _should_ be no more than 30 entries ( and 29 spaces ), but sometimes the system misfires and there are more or less. Is there a MySQL solution to getting a count of the spaces present in the field, figuring that spaces + 1 will equal entries? It's fairly straight forward using a PHP application, but I'd like to get the DB server to accomplish this task. Not having much luck finding a solution in the manual. SELECT 1 + CHAR_LENGTH(0 0 0 1 0 1 0 25 7 0 139 0 9) - CHAR_LENGTH(REPLACE(0 0 0 1 0 1 0 25 7 0 139 0 9, , )) AS ret HopeItHelp, Francesco -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Not Sorting Correctly
It's possible to repeat with a varbinary field also in 5.1
CREATE TABLE t1 (
RoomNo varbinary(10) DEFAULT NULL,
LastName varchar(25) NOT NULL,
FirstName varchar(25) NOT NULL,
ChapterID int(11) NOT NULL DEFAULT '358'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `t1` (`RoomNo`, `LastName`, `FirstName`, `ChapterID`) VALUES
('1', 'Anderson', 'Kayla', 358),
('2', 'Barton', 'Greg', 358),
('3', 'Beaty', 'Brooke', 358),
('1', 'Brown', 'Paige', 358),
('2', 'Bynum', 'Wesley', 358),
('8', 'Clark', 'Andrew', 358),
('8', 'Clark', 'Ramsey', 358);
The solution are I've found are 2, the first convert the field to utf8,
the second to use lpad function instead of concat, this inherently work
(but don't ask why).
-- explicitly convert it to utf8
SELECT CONVERT( (RIGHT(CONCAT('000',RoomNo),3)) USING utf8) AS
PaddedRoomNo, LastName, FirstName
FROM t1
WHERE ChapterID=358 AND RoomNo IS NOT NULL
ORDER BY PaddedRoomNo, LastName, FirstName
-- pad server side (implicitly convert to utf8 ?)
SELECT LPAD(RoomNo,3,'0')AS PaddedRoomNo, LastName, FirstName
FROM t1
WHERE ChapterID=358 AND RoomNo IS NOT NULL
ORDER BY PaddedRoomNo, LastName, FirstName
Jesse ha scritto:
Strange. I'm running the same exact version, and it's not the same.
What field types are you using? Mine are as follows:
RoomNo VarChar(10)
LastName VarChar(25)
FirstName VarChar(25)
the values that I put into Room No are 1,2,3, etc. I'm not
storing 001,002,003, etc in there.
Jesse
- Original Message - From: Zhaowei [EMAIL PROTECTED]
To: Jesse [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Friday, March 23, 2007 5:03 AM
Subject: Re: Not Sorting Correctly
Hi, Jesse,
I did a small test and found it was in order. My version is
+-+
| version() |
+-+
| 5.0.22-community-nt-log |
+-+
select right(concat('000',text_id),3) AS text,name from
an order by right(concat('000',text_id),3),name;
+--+-+
| text | name|
+--+-+
| 001 | cat |
| 001 | dog |
| 001 | monkey |
| 001 | rat |
| 001 | wolf|
| 002 | cat |
| 002 | whale |
| 003 | lax |
| 003 | penguin |
| 006 | ostrich |
+--+-+
10 rows in set (0.00 sec)
On 3/23/07, Jesse [EMAIL PROTECTED] wrote:
When I run the following query:
SELECT RIGHT(CONCAT('000',RoomNo),3),LastName,FirstName
FROM ConfHotelDet
WHERE ChapterID=358 AND RoomNo IS NOT NULL
ORDER BY RIGHT(CONCAT('000',RoomNo),3), LastName, FirstName
I get the following result:
001AndersonKayla
002BartonGreg
003BeatyBrooke
001BrownPaige
002BynumWesley
008ClarkAndrew
008ClarkRamsey
Etc...
As you can see, it's out of order.
Jesse
- Original Message -
From: Ales Zoulek [EMAIL PROTECTED]
To: Jesse [EMAIL PROTECTED]
Cc: MySQL List mysql@lists.mysql.com
Sent: Monday, March 19, 2007 9:06 PM
Subject: Re: Not Sorting Correctly
pls, post result of:
SELECT RIGHT(CONCAT('000,RoomNo),3),LastName,FirstName FROM
Ales
On 3/19/07, Jesse [EMAIL PROTECTED] wrote:
I have an app that I've converted to MySQL from MS SQL. I used
to use
the
following to force a Alpha field to sort as if it were numeric (I
know,
perhaps it is better if I made the field numeric to begin with,
but it's
not, and I don't remember why, but that's not the question here):
ORDER BY RIGHT('000' + RoomNo,3),LastName,FirstName
I converted this to the following in MySQL:
ORDER BY RIGHT(CONCAT('000,RoomNo),3),LastName,FirstName
In MS SQL, it would sort correctly:
1 Kayla Andre
1 Paige Brackon
1 Kasie Guesswho
1 Katelyn Hurst
2 Craig Bartson
2 Wesley Bytell
2 Kevin Peterson
2 Bryan Wilton
etc...
Now, the Above (RIGHT(CONCAT...)-MySQL Version), seems to ignore the
first
sort field, and simply sorts alphabatically:
1 Kayla Andre
2 Craig Bartson
1 Paige Brackon
2 Wesley Bytell
1 Kasie Guesswho
1 Katelyn Hurst
2 Kevin Peterson
2 Bryan Wilton
I finally ended up with:
ORDER BY CAST(RoomNo AS UNSIGNED), LastName, FirstName
Which works perfectly, but I'm just wondering why the first attempt
(right(concat...)) didn't work?? Any ideas?
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
--
Ales Zoulek
NetCentrum s.r.o.
+420 739 542 789
+420 604 332 515
ICQ: 82647256
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Best Regards,
Yours Zhaowei
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding a record in a result set
Maybe this one could do (untested) ? SELECT COUNT(*), ( SELECT count(*) FROM employees AS t2 WHERE t2.name = 'Joe' AND t2.state = 'ME' AND t2.hiredate = datevalue) AS joesexist FROM employees WHERE name = 'Joe' AND state = 'ME' AND hiredate = datevalue; Jerry Schwartz ha scritto: I don't think that will work. If there are 1,000 records that qualify but none for Joe, then it will return 1,001. If Joe is in record 1 of the retrieved record set, and there are 999 other people who match the WHERE clause, then it will retrieve 1,000. Am I missing something? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Thursday, March 22, 2007 12:33 PM To: James Tu Cc: MySQL List Subject: Re: Finding a record in a result set I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) If 'Joe' is a unique name LOL... SELECT 1 + COUNT(*) FROM employees WHERE name 'Joe' AND state = 'MA' AND hiredate datevalue; PB James Tu wrote: Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.17/730 - Release Date: 3/22/2007 7:44 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Functions and NULL with standard install
select rpad(|IFNULL(|null, ''),5,'1');
|/||/|
[EMAIL PROTECTED] ha scritto:
I am running a basic install of MySQL 5.0 with strict mode turned on I
would like to use RPAD however at times a NULL var will be sent to the
function. My goal is to have a function that will convert the NULL to a
blank string to get the result of EXAMPLE3 if a NULL is returned.
EXAMPLE 1
mysql select rpad(null,5,'1');
+--+
| rpad(null,5,'1') |
+--+
| NULL |
+--+
1 row in set (0.00 sec)
EXAMPLE 2
mysql select rpad('0',5,'1');
+-+
| rpad('0',5,'1') |
+-+
| 0 |
+-+
1 row in set (0.00 sec)
mysql
EXAMPLE 3
mysql select rpad('',5,'1');
++
| rpad('',5,'1') |
++
| 1 |
++
1 row in set (0.02 sec)
Is there a function I can use to convert the null string to a blank string
before it is sent to RPAD in a basic installation of MySQL or will I need
to create a function?
Wishing you the best you know you deserve,
__
Lucas Heuman
CM Web Developer
SRA International, Inc.
FAA, WJHTC/Bldg 300, 2nd Fl., H33
Atlantic City Int'l Airport, NJ 08405
Phone 609.485.5401
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max number of 64 indices per table?
Horst Jäger ha scritto: Hi everyone, the number of incices per table seems to be restricted to 64. Any way to change that? recompile with configure --with-max-indexes=128 I'm using MySQL 5.0.27 . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: www.innodb.com
Heikki Tuuri ha scritto: Bill, we are moving the DNS of innodb.com from Capnova to Oracle Corp. I can now view http://www.innodb.com through my ISP, Elisa. Does anyone still have problems accessing http://www.innodb.com? If you cannot see some web page, you can resort to Google's cache to view it. I hope that we will not get more disruption of service this weekend. Best regards, This is the answer from ibm nameservers i.e. none: ##- dig www.innodb.com @ns.almaden.ibm.com. ; DiG 9.3.2 www.innodb.com @ns.almaden.ibm.com. ; (1 server found) ;; global options: printcmd ;; Got answer: ;; -HEADER- opcode: QUERY, status: NOERROR, id: 33840 ;; flags: qr rd; QUERY: 1, ANSWER: 0, AUTHORITY: 13, ADDITIONAL: 0 ;; QUESTION SECTION: ;www.innodb.com.IN A ;; AUTHORITY SECTION: com.172552 IN NS i.gtld-servers.net. com.172552 IN NS j.gtld-servers.net. com.172552 IN NS k.gtld-servers.net. com.172552 IN NS l.gtld-servers.net. com.172552 IN NS m.gtld-servers.net. com.172552 IN NS a.gtld-servers.net. com.172552 IN NS b.gtld-servers.net. com.172552 IN NS c.gtld-servers.net. com.172552 IN NS d.gtld-servers.net. com.172552 IN NS e.gtld-servers.net. com.172552 IN NS f.gtld-servers.net. com.172552 IN NS g.gtld-servers.net. com.172552 IN NS h.gtld-servers.net. ;; Query time: 188 msec ;; SERVER: 198.4.83.35#53(198.4.83.35) ;; WHEN: Fri Nov 10 13:04:51 2006 ;; MSG SIZE rcvd: 256 ##- This is a query to the internet: ##- [EMAIL PROTECTED] dbdesigner 1 $ dig www.innodb.com ; DiG 9.3.2 www.innodb.com ;; global options: printcmd ;; Got answer: ;; -HEADER- opcode: QUERY, status: NOERROR, id: 3995 ;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 0 ;; QUESTION SECTION: ;www.innodb.com.IN A ;; ANSWER SECTION: www.innodb.com. 80775 IN A 216.40.33.31 ;; Query time: 24 msec ;; SERVER: 192.168.4.1#53(192.168.4.1) ;; WHEN: Fri Nov 10 13:03:11 2006 ;; MSG SIZE rcvd: 48 [EMAIL PROTECTED] dbdesigner 0 $ dig -x 216.40.33.31 ; DiG 9.3.2 -x 216.40.33.31 ;; global options: printcmd ;; Got answer: ;; -HEADER- opcode: QUERY, status: NOERROR, id: 33913 ;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 3, ADDITIONAL: 1 ;; QUESTION SECTION: ;31.33.40.216.in-addr.arpa. IN PTR ;; ANSWER SECTION: 31.33.40.216.in-addr.arpa. 1200 IN PTR www.renewyourname.net. ;; AUTHORITY SECTION: 33.40.216.in-addr.arpa. 1200IN NS dns1.tucows.com. 33.40.216.in-addr.arpa. 1200IN NS dns2.tucows.com. 33.40.216.in-addr.arpa. 1200IN NS dns3.tucows.com. ;; ADDITIONAL SECTION: dns3.tucows.com.172051 IN A 204.50.180.59 ;; Query time: 263 msec ;; SERVER: 192.168.4.1#53(192.168.4.1) ;; WHEN: Fri Nov 10 13:03:19 2006 ;; MSG SIZE rcvd: 161 [EMAIL PROTECTED] dbdesigner 0 $ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SPAM] - Re: Converting decimal to binary - Bayesian Filter detected spam
And another one is (in inverse order for laziness):
select
(8 1) AS `0`
, (8 2 1) AS `1`
, (8 4 1) AS `2`
, (8 8 1) AS `3`
, (8 16 1) AS `4`
, (8 32 1) AS `5`
, (8 64 1) AS `6`
, (8 128 1) AS `7`
;
Ed Reed wrote:
Well I solved the problem by using LPAD but it would be nice if there was a
more elegant way of handling this problem.
Thanks for the help.
Ed Reed [EMAIL PROTECTED] 1/10/06 2:52 PM
Thanks Gordon and Bill but this has one big problem
If my decimal number is 8 the result ends up,
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 0 | 0 | 0 | | | | |
+---+---+---+---+---+---+---+---+
when what i really need is,
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
+---+---+---+---+---+---+---+---+
Any thoughts?
Gordon Bruce [EMAIL PROTECTED] 1/10/06 1:44 PM
Actually CONV converts from any base to any base so if it is base 10
then just replace the 16's with 10's.
Too much time looking at dump's.
-Original Message-
From: Bill Dodson [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 3:09 PM
To: Gordon Bruce
Cc: Ed Reed; mysql@lists.mysql.com
Subject: [SPAM] - Re: Converting decimal to binary - Bayesian Filter
detected spam
If you really do mean decimal (base 10) you could use Gordon's solution
like this:
SELECT
MID(CONV(HEX(245),16,2),1,1) AS `7`,
MID(CONV(HEX(245),16,2),2,1) AS `6`,
MID(CONV(HEX(245),16,2),3,1) AS `5`,
MID(CONV(HEX(245),16,2),4,1) AS `4`,
MID(CONV(HEX(245),16,2),5,1) AS `3`,
MID(CONV(HEX(245),16,2),6,1) AS `2`,
MID(CONV(HEX(245),16,2),7,1) AS `1`,
MID(CONV(HEX(245),16,2),8,1) AS `0` ;
Results:
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
Hope this helps.
Gordon Bruce wrote:
If by Decimal you mesn HEXIDECIMAL you can use CONV
where the 1st arg is the HEX value,
2nd arg is From Base
and 3rd arg is To Base.
You will have to suround the aliases with `'s if you really want
the names to be numeric.
mysql select MID(CONV('A5',16,2),1,1) AS `7`,
- MID(CONV('A5',16,2),2,1) AS `6`,
- MID(CONV('A5',16,2),3,1) AS `5`,
- MID(CONV('A5',16,2),4,1) AS `4`,
- MID(CONV('A5',16,2),5,1) AS `3`,
- MID(CONV('A5',16,2),6,1) AS `2`,
- MID(CONV('A5',16,2),7,1) AS `1`,
- MID(CONV('A5',16,2),8,1) AS `0` ;
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 0 | 1 | 0 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
1 row in set (0.00 sec)
-Original Message-
From: Ed Reed [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 10, 2006 12:16 PM
To: mysql@lists.mysql.com
Subject: Converting decimal to binary
Can anyone tell me if it's possible, in 4.1.11, to convert a decimal
number to binary and have the result be returned as a separate field
for
each bit? For example, what I'd like to do is,
Select ConvertToBin(245);
And have a result that looked like this
+---+---+---+---+---+---+---+---+
| 7 | 6 | 5 | 4 | 3 | 2 | 1 | 0 |
+---+---+---+---+---+---+---+---+
| 1 | 1 | 1 | 1 | 0 | 1 | 0 | 1 |
+---+---+---+---+---+---+---+---+
- Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SPAM] - Re: Converting decimal to binary - Bayesian Filter detected spam
Francesco Riosa wrote: And another one is (in inverse order for laziness): select (8 1) AS `0` , (8 2 1) AS `1` , (8 4 1) AS `2` , (8 8 1) AS `3` , (8 16 1) AS `4` , (8 32 1) AS `5` , (8 64 1) AS `6` , (8 128 1) AS `7` ; but this one looks better: select (8 1) AS `0` , (8 1 1) AS `1` , (8 2 1) AS `2` , (8 3 1) AS `3` , (8 4 1) AS `4` , (8 5 1) AS `5` , (8 6 1) AS `6` , (8 7 1) AS `7` ; http://dev.mysql.com/doc/refman/4.1/en/bit-functions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem installing MySQL 5.0
Hello everybody, This is the first time I try to install MySQL 5.0, I think I followed the steps showed by the documentation in the right way, but when I run the command: rpm -i MySQL-server-Version-.i386.rpm I get back the error: error: Failed dependencies: perl(DBI) is needed by MySQL-server-standard-5.0.18-0.rhel3 I am trying to install MySQL on this platform: - CPU:AMD Athlon 800 Mhz - Operating System: Linux Fedora core 2, release 2.6.5-1.358 The packages I have downloaded are: - MySQL-server-standard-5.0.18-0.rhel3.i386.rpm - MySQL-client-standard-5.0.18-0.rhel3.i386.rpm After the error, I found a few information on internet about perl(DBI), so I downloaded it and installed it on my system. The test step (make test) ended with error 355 (??), but I run anyway make install which ended without problems, I think. At this point I do not know what to do, so I need your help to solve this problem. Greetings Francesco Vincenti * *
Re: Problem installing MySQL 5.0
Hello Chander, thank you very much. I tried to do in your way ad the installation works! I downloaded the perl(DBI) .rpm and I installed it the first time using the command: rpm -Uvh perl-DBI-1.50-1.i386.rpm but I got back an error about one glibc library not founded, so I use this format: rpm -Uvh --nodeps perl-DBI-1.50-1.i386.rpm and the installation finished without problems. Then, I installed both MySQL-server-standard-5.0.18-0 .rhel3.i386.rpm, with which I have had the problem, and MySQL-client-standard-5.0.18-0.rhel3.i386.rpm and all went in the right way. Greetings Francesco Vincenti
Re: Importing dBase II +
Alle 08:21, giovedì 13 ottobre 2005, Huub ha scritto: Hi, Can someone tell me if it is anyhow possible to import a dBaseIII+ database into MySQL and if so: how? On linux there is dbf2mysql http://packages.debian.org/stable/misc/dbf2mysql On windows you could import the tables in m$access and reexport them via odbc to mysql, then alter the tables to respect the original use. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication slave's warnings
Master: 4.0.20-max Slave: 4.1.14-max Replication seems goes well. This morning i see the follow messages on error log: 050831 5:30:04 [Warning] Slave: load data infile on table 'Stage' at log position 30438086 in log 'tana-bin.001' produced 5003 warning(s). Default database: 'TSM' 050831 6:00:10 [Warning] Slave: load data infile on table 'Day_Tape' at log position 31499001 in log 'tana-bin.001' produced 10 warning(s). Default database: 'TSM' 050831 6:00:16 [Warning] Slave: load data infile on table 'Day_Reclamation' at log position 31854414 in log 'tana-bin.001' produced 4 warning(s). Default database: 'TSM' 050831 6:00:16 [Warning] Slave: load data infile on table 'DB_Day' at log position 32025985 in log 'tana-bin.001' produced 7 warning(s). Default database: 'TSM' 050831 6:31:58 [Warning] Slave: load data infile on table 'Day_Occupancy' at log position 32080517 in log 'tana-bin.001' produced 6 warning(s). Default database: 'TSM' What's happened? How i can see the produced warning on the slave? Thanks in advance, Francesco Dalla Ca'. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Incorrect column name
I have a database on a mysql server (version 4.0.20-max) with this table: mysql show create table Dsmaccnt\G *** 1. row *** Table: Dsmaccnt Create Table: CREATE TABLE `Dsmaccnt` ( ... ... `Termination ` tinyint(3) unsigned NOT NULL default '0', ... ... ) TYPE=MyISAM 1 row in set (0.01 sec) mysql The field `Termination ` cause me some trouble: If i try to recreate from dump (mysqldump from 4.0) the table 'Dsmaccnt', on another server (version 4.1), i've got this error: ERROR 1166: Incorrect column name 'Termination ' On the same server (4.0.20-max) if i try to create a dummy table with same blank-added field i've got same error: mysql create table prova (`Termination ` int); ERROR 1166: Incorrect column name 'Termination ' mysql How is possible that i have this table with this field? (The database was created by others admin, not by me). There are some particular SQL mode that permit this? Thank in advance. Francesco. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how i can log client connections?
I need log the connections (succesfull and failed) on a production environment for safety reason and monitoring. Best regards. Date: Fri, 08 Jul 2005 12:46:30 +0300 To: mysql@lists.mysql.com From: Gleb Paharenko [EMAIL PROTECTED] Subject: Re: how i can log client connections? Message-ID: [EMAIL PROTECTED] Hello. This issue has been raised in the list already before, however I don't remember that good solution was found. And without queries which was executed these log records don't give any useful information in most production environments. Francesco Dalla Ca' [EMAIL PROTECTED] wrote: How i can log (somewhere) the client connections on the mysql server, without enabling the general-query log? Please give me some help or pointers... Best regards. -- === CINECA Via Magnanelli 6/3 40033 Casalecchio di Reno (Bologna) Settore Gestione Sistemi Francesco Dalla Ca' Email [EMAIL PROTECTED] === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how i can log client connections?
How i can log (somewhere) the client connections on the mysql server, without enabling the general-query log? Please give me some help or pointers... Best regards. -- === CINECA Via Magnanelli 6/3 40033 Casalecchio di Reno (Bologna) Settore Gestione Sistemi Francesco Dalla Ca' Email [EMAIL PROTECTED] === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld threads
B. Heller wrote: Hello List, I compiled mySQLd 4.1.10a from Source on Linux 2.6. Although the server does it's work well, I wondered why there's only one mysqld thread running. Is that new/normal for mySQL 4.1? While using mySQL 3.23 I always had several threads running on my system. your glibc probably are compiled with NPTL. The thread are still there but to see them you must use a different sintax of ps , like: #ps -eLf -- No problem is so formidable that you can't walk away from it. ~ Charles M. Schulz But sometimes run fast is better ~ Francesco R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
is mysql activities consuming RAM or is a linux kernel caching matter?
Dear list, I've been searching the archives before posting this request since it is about something you've already discussed. But I didn't find the solution, so here it comes my question. It is about RAM consuming. I'm running a PHP/mysql application on a redhat 8.1 - kernel 2.4-18 - 512 MB RAM system. I'm using mysql 3.23.54a mysql-server and PHP 4.2.2. I understand that mysqld won't steal too much RAM as long as it is well tuned. I'm not sure I succeeded in tuning the mysqld so here it follows the relavant part of my.cnf, the values are calculated by logging the mysqld status: set-variable= key_buffer=16M set-variable= max_allowed_packet=1M set-variable= table_cache=100 set-variable= sort_buffer=512k set-variable= net_buffer_length=8K set-variable= myisam_sort_buffer_size=8M set-variable= max_connections=200 set-variable= max_user_connections=200 set-variable= wait_timeout=300 skip-innodb skip-bdb While there are 23 threads running and 100 opened tables, I see from the top tool that a great amount of RAM is allocated as cached. Typically from 200 to 350 MB, leaving the rest to the processes. This leads very soon to a degradation of the system performance (the swap memory increases dramatically). In some other posts of this list I found out that this couldn't be neccessarily related to mysqld activities but to some other linux kernel matters (I didn't understand which one precisely). Well, I have another server, configured exactly as the previous one. This is a sort of backup server ready to be used in case the main one crashes. No one but me is accessing this second server. I notice that the amount of RAM allocated as cached is pretty low (around 80MB). But at the time I start browsing some web pages (involving mysql queries) the RAM used as cached increases quickly. Am I wrong in thinking that there must be some relationship between mysql queries and kernel cache memory? (maybe filesystem caching related to index and tables files??) I cannot find out how to prevent the kernel from caching so much memory. The only thing I've been able to do for now is just rebooting the system. Of course I cannot keep doing that. What is wrong? Is something that I have to fix on the mysql side or on the linux side? This is driving me crazy so any help would be precious and appreciated. Thank you. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
problem with drop table
Hello, I tried to drop a table with Mysql 4.0.1 alpha nt and I have encountered the following problem: ERROR : Error on delete of .'\camcomm.tmpge.MYI' The problem persists. I don't know the reason of this error. I ask if a new version of Mysql (the version 4.0.7 i s released) is better. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
problem with PHP-Mysql.
Hello,
I tried to execute the following form:
html
head
title/title
/head
body
FORM method=POST action=jokehandle.php
INPUT TYPE=submit name=addjoke value=InsertBR/
INPUT TYPE=submit name=deletejoke value=Delete
/FORM
/body
/html
and the following script PHP:
HTML
HEADTITLEOperazioni su joke/TITLE/HEAD
BODY
?php
// If the user wants to add a joke
if (isset($_POST['addjoke'])==true):
?
FORM ACTION=jokehandle.php METHOD=POST
PType your joke here:BR
TEXTAREA NAME=joketext ROWS=10 COLS=40 WRAP
/TEXTAREABR
INPUT TYPE=SUBMIT NAME=submitjoke VALUE=SUBMIT
/FORM
?php
else:
// Connect to the database server
$dbcnx = @mysql_pconnect(localhost,
root, );
if (!$dbcnx) {
echo( PUnable to connect to the .
database server at this time./P );
exit();
}
// Select the jokes database
if (! @mysql_select_db(jokes) ) {
echo( PUnable to locate the joke .
database at this time./P );
exit();
}
// If a joke has been submitted,
// add it to the database.
if (SUBMIT == $_POST['submitjoke']) {
$joketext=$_POST['joketext'];
$sql = INSERT INTO Jokes SET .
JokeText='$joketext', .
JokeDate=CURDATE();
if (mysql_query($sql)) {
echo(PYour joke has been added./P);
} else {
echo(PError adding submitted joke: .
mysql_error() . /P);
}
}
if (isset($_POST['deletejoke'])==true) {$sql = DELETE FROM Jokes .
WHERE ID=$deletejoke;
if (mysql_query($sql)) {
echo PThe joke has been
deleted./P;
}
} else {
echo(PError deleting joke: .
mysql_error() . /P);
}
echo(P Here are all the jokes .
in our database: /P);
// Request the text of all the jokes
$result = mysql_query(
SELECT ID,JokeText FROM Jokes);
if (!$result) {
echo(PError performing query: .
mysql_error() . /P);
exit();
}
// Display the text of each joke in a paragraph
while ( $row = mysql_fetch_array($result) ) {
$jokeid=$row[ID];
$joketext=$row[JokeText];
$PHP_SELF=jokehandle.php;
echo P$joketext .
A HREF='$PHP_SELF?deletejoke=$jokeid' .
Delete this Joke/A/P;
}
// When clicked, this link will load this page
// with the joke submission form displayed.
echo(PA HREF='$PHP_SELF?addjoke=1' .
Add a Joke!/A/P);
endif;
?
/BODY
/HTML
I have this problem:
If I try to execute the delete no information is deleted from the database
and I call jokehandle passing addjoke no joke is inserted in the database.
The browser show this message:
Notice: Undefined index: submitjoke in C:\FoxServ\www\db\jokehandle.php on
line 43
Error deleting joke:
Here are all the jokes in our database:
Why did the chicken cross the road? To get to the other side! Delete this Joke
Prova Delete this Joke
Crisi Fiat forse risolta Delete this Joke
Sport. Vittoria della Juventus a Reggio Calabria per 2-0. Delete this Joke
La crisi Fiat c terminata male. Delete this Joke
Francesco Delete this Joke
Francesco Delete this Joke
Add a Joke!
Only the insert from a form does run. I don' t understand the problem.
I also ask if the FoxServer Version has bounded perfromance because the
form in a script PHP that call itself run only if the action of the form is
the name of the script or the S_SERVER['PHP_SELF'];. If the action is the
langage variable SPHP_SELF the scipt doesn't run. If I use S_POST or S_GET
with index 'PHP_SELF the script give the notice S_POST[''] undefined.
Bye from Francesco.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
other problem
Hello, I tried the modify two fields in a table and MyCC and MySqlFront return the following error: Error: 7 - Error on rename of '.\aziendeseat\aziendege.MYI' to '.\aziendeseat\#sql2-610-1a.MYI' (Errcode: 13) The Table is named aziendege and contains the fields: ID NOT NULL AUTO_INCREMENT, Nome VARCHAR(50) NOT NULL, Indirizzo VARCHAR(50) NOT NULL, Telefono VARCHAR(15) NOT NULL, URL VARCHAR(50) NOT NULL, EMAIL VARCHAR(50) NOT NULL, PRIMARY KEY(ID) I tried to change properties for URL and EMAIL removing NOT NULL and the result show the error above. I have the Mysql 4.0.1 alpha version and I want to know if the new version MYsql 4.0.x not yet officially released are more stable. The problem can be depend from alpha-version? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql problem
Hello, I tried to execute the following query: SELECT studenti.* FROM studenti,valutazioni LEFT JOIN valutazioni ON studenti.sid=valutazioni.sid WHERE valutazioni.sid IS NULL; and give me the following error message: ERROR 1066: Not unique table/alias: 'valutazioni' I have the Mysql 4.0.1 alpha version. I want to know what is the problem. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multiple instances running over the same DB data with GFS/OpenGFS
I have a GFS/OpenGFS-based cluster I intend to use for an HA mysql setup. Just for fun, I tried running two instances of MySQL over the same table files, and you can imagine my surprise when it mostly worked I only got an error in table handler when when inserting row #8k of a concurrent 5k+5k insertion from both nodes. The tables do NOT seem to be corrupt. The point is, how much did this work by chance? Or can it somehow work, e.g. provided that the concurrent insertions are not too frequent? Thanks for any insight. -- /kinkie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
[beginner] How to update mysql with Linux ?
Hi all, I'd like to update my version of Mysql on linux. How can I do it so that I don't have two copies of mysql on my machine ? Do I have to clean up the folder mysql and then install the new version under it? And what should I download -the source code or the binaries- in order to update the starting scripts under /etc/rc.d/init.d ? Thanks a lot Francesco [EMAIL PROTECTED]
High availability anyone?
Hi. I'd like to build an HA MySQL server using shared storage over Fibre Channel. Having a look at the mailing list archives, the issue was addressed earlier on (I could find traces of it as early as 1999) but AFAICS no definitive solution has been reached. I know there is a commercial solution from SteelEye tecnologies, but it's very expensive and I'd like to see if there is some other way (i.e. GFS). Has anybody tried this? Any success stories? Is there any reason why this COULDN'T work? (i.e. two+ mysql processes accessing concurrently the same storage space would stomp on each other's feet, have problems of cache incoherency or who-knows-what-else) Thanks in advance for any replies. -- /kinkie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
