why different users get different count(*) from same table?

[jinava]

Hi experts,

something strange here, use root and mysql (root and mysql are user
accounts) to select count(*) from same table, but the result is different.

login as root:

mysql -uroot -p information_schema

mysql> select * from tables where table_name='tb_staff'\G
*** 1. row ***
 TABLE_CATALOG: NULL
  TABLE_SCHEMA: dbsg
TABLE_NAME: tb_staff
TABLE_TYPE: BASE TABLE
ENGINE: MyISAM
   VERSION: 10
ROW_FORMAT: Dynamic
*TABLE_ROWS: 0*
AVG_ROW_LENGTH: 0
   DATA_LENGTH: 0
MAX_DATA_LENGTH: 281474976710655
  INDEX_LENGTH: 1024
 DATA_FREE: 0
AUTO_INCREMENT: 1
   CREATE_TIME: 2009-05-31 16:13:07
   UPDATE_TIME: 2009-05-31 16:13:07
CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
  CHECKSUM: NULL
CREATE_OPTIONS:
 TABLE_COMMENT:
1 row in set (0.00 sec)

then,


login as mysql:

mysql -umysql -p information_schema

mysql> select * from tables where table_name='tb_staff'\G
*** 1. row ***
 TABLE_CATALOG: NULL
  TABLE_SCHEMA: dbsg
TABLE_NAME: tb_staff
TABLE_TYPE: BASE TABLE
ENGINE: MyISAM
   VERSION: 10
ROW_FORMAT: Dynamic
*TABLE_ROWS: 7519*
AVG_ROW_LENGTH: 223
   DATA_LENGTH: 1680120
MAX_DATA_LENGTH: 281474976710655
  INDEX_LENGTH: 79872
 DATA_FREE: 0
AUTO_INCREMENT: 99022
   CREATE_TIME: 2009-06-29 16:57:50
   UPDATE_TIME: 2009-06-30 13:41:07
CHECK_TIME: 2009-06-30 11:15:48
TABLE_COLLATION: latin1_swedish_ci
  CHECKSUM: NULL
CREATE_OPTIONS:
 TABLE_COMMENT:
1 row in set (0.00 sec)

mysql>


Cheers
jinava

RE: Update email address domain

[John Furlong]

Nathan,

That was exactly what I was looking for, thanks for your help.

John

-Original Message-
From: Nathan Sullivan [mailto:nsulli...@cappex.com] 
Sent: Monday, June 29, 2009 2:55 PM
To: John Furlong; mysql@lists.mysql.com
Subject: RE: Update email address domain

John,

I think this should work:

UPDATE members SET email=REPLACE(email, SUBSTRING(email,INSTR(email,'@')+1), 
'Thanks_in_advance.com.com')


Regards,
Nathan

-Original Message-
From: John Furlong [mailto:john.furl...@rakutenusa.com] 
Sent: Monday, June 29, 2009 12:54 PM
To: mysql@lists.mysql.com
Subject: Update email address domain

I'm trying to mask the email addresses for a development database. I need to 
make all of the domains exactly the same. What is the best way to do this? We 
have about 67000 distinct domains.

I was able to use substring to get the list of domains, but am not sure how to 
turn that into an update statement

SELECT SUBSTRING(email,INSTR(email,'@')+1) AS domain FROM members limit 5;

+--+
| domain   |
+--+
| aol.com  |
| verizon.net  |
| netzero.com  |
| yahoo.com|
| comcast.net  |
+--+
5 rows in set (0.00 sec)

So the full email address will end up as
b...@thanks_in_advance.com
j...@thanks_in_advance.com
jack@ Thanks_in_advance.com
a...@thanks_in_advance.com
j...@thanks_in_advance.com




John F


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Update email address domain

[Nathan Sullivan]

John,

I think this should work:

UPDATE members SET email=REPLACE(email, SUBSTRING(email,INSTR(email,'@')+1), 
'Thanks_in_advance.com.com')


Regards,
Nathan

-Original Message-
From: John Furlong [mailto:john.furl...@rakutenusa.com] 
Sent: Monday, June 29, 2009 12:54 PM
To: mysql@lists.mysql.com
Subject: Update email address domain

I'm trying to mask the email addresses for a development database. I need to 
make all of the domains exactly the same. What is the best way to do this? We 
have about 67000 distinct domains.

I was able to use substring to get the list of domains, but am not sure how to 
turn that into an update statement

SELECT SUBSTRING(email,INSTR(email,'@')+1) AS domain FROM members limit 5;

+--+
| domain   |
+--+
| aol.com  |
| verizon.net  |
| netzero.com  |
| yahoo.com|
| comcast.net  |
+--+
5 rows in set (0.00 sec)

So the full email address will end up as
b...@thanks_in_advance.com
j...@thanks_in_advance.com
jack@ Thanks_in_advance.com
a...@thanks_in_advance.com
j...@thanks_in_advance.com




John F

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Update email address domain

[John Furlong]

I'm trying to mask the email addresses for a development database. I need to 
make all of the domains exactly the same. What is the best way to do this? We 
have about 67000 distinct domains.

I was able to use substring to get the list of domains, but am not sure how to 
turn that into an update statement

SELECT SUBSTRING(email,INSTR(email,'@')+1) AS domain FROM members limit 5;

+--+
| domain   |
+--+
| aol.com  |
| verizon.net  |
| netzero.com  |
| yahoo.com|
| comcast.net  |
+--+
5 rows in set (0.00 sec)

So the full email address will end up as
b...@thanks_in_advance.com
j...@thanks_in_advance.com
jack@ Thanks_in_advance.com
a...@thanks_in_advance.com
j...@thanks_in_advance.com




John F

Try to create a tunnel SSH to remote DB server, but with error Number 2013

[Lin Chun]

hi

As the DB server is behind bastion host, each time I have to connect to this
host and then connect the DB server

In order to connect the server from my pc windows, I use PuTTY to  create a
tunnel SSH,
mapping 'remotserver:port' to 'localhost:6001' thought the bastion host
After the configuration , the localhost is suceed  listening port 6001
but when I try to connect the server in my pc , it shows

"MySQL error Number 2013"
lost connection to Mysql server during query'


Should I have to change some configuration in server to support this kind of
connenction?


Regards,

-- 
-
Lin Chun

Re: LOAD DATA INFILE Syntax error

[Ralph Kutschera]

Johnny Withers schrieb:

Group is a keyword in mysql:

You need to put backticks around it in your statement:

| LOAD DATA INFILE 'test.csv' INTO TABLE table
|   FIELDS TERMINATED BY ','
|   LINES STARTING BY '' TERMINATED BY '\n'
|   (Page, Device, `GROUP` , ItemID, Item, Value);


Ooookay. Thank you very much!


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: LOAD DATA INFILE Syntax error

[Johnny Withers]

Group is a keyword in mysql:

You need to put backticks around it in your statement:

| LOAD DATA INFILE 'test.csv' INTO TABLE table
|   FIELDS TERMINATED BY ','
|   LINES STARTING BY '' TERMINATED BY '\n'
|   (Page, Device, `GROUP` , ItemID, Item, Value);


On Mon, Jun 29, 2009 at 7:07 AM, Ralph Kutschera <
news2...@ecuapac.dyndns.org> wrote:

> Hallo List!
>
>  I have a CVS file which i would like to import to MySQL.
>
> The file header and an example:
> | Page,Device,Group,ItemID,Item,Value
> | Overview,General,Computer,513,OS,Linux
>
>
> The table has:
> | Create Table: CREATE TABLE `table` (
> |   `ID` int(11) NOT NULL auto_increment,
> |   `Page` varchar(128) default NULL,
> |   `Device` varchar(128) default NULL,
> |   `Group` varchar(128) default NULL,
> |   `ItemID` varchar(128) default NULL,
> |   `Item` varchar(128) default NULL,
> |   `Value` varchar(128) default NULL,
> |   PRIMARY KEY  (`ID`)
> | ) ENGINE=MyISAM DEFAULT CHARSET=latin1
>
>
> So I would like to import the first file field to the second table field,
> the second file field to the third table,... Just to have an index.
>
> I'm using:
> | LOAD DATA INFILE 'test.csv' INTO TABLE table
> |   FIELDS TERMINATED BY ','
> |   LINES STARTING BY '' TERMINATED BY '\n'
> |   (Page, Device, GROUP , ItemID, Item, Value);
>
>
> which gives me:
> | #1064 - 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 'Group, ItemID, Item, Value)' at line 2
>
> I cannot find the error. Please help me!
> MySQL version = 5.0.32-Debian_7etch8-log
>
> TIA,
>  Ralph
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
>
>


-- 
-
Johnny Withers
601.209.4985
joh...@pixelated.net

LOAD DATA INFILE Syntax error

[Ralph Kutschera]

Hallo List!

  I have a CVS file which i would like to import to MySQL.

The file header and an example:
| Page,Device,Group,ItemID,Item,Value
| Overview,General,Computer,513,OS,Linux


The table has:
| Create Table: CREATE TABLE `table` (
|   `ID` int(11) NOT NULL auto_increment,
|   `Page` varchar(128) default NULL,
|   `Device` varchar(128) default NULL,
|   `Group` varchar(128) default NULL,
|   `ItemID` varchar(128) default NULL,
|   `Item` varchar(128) default NULL,
|   `Value` varchar(128) default NULL,
|   PRIMARY KEY  (`ID`)
| ) ENGINE=MyISAM DEFAULT CHARSET=latin1


So I would like to import the first file field to the second table 
field, the second file field to the third table,... Just to have an index.


I'm using:
| LOAD DATA INFILE 'test.csv' INTO TABLE table
|   FIELDS TERMINATED BY ','
|   LINES STARTING BY '' TERMINATED BY '\n'
|   (Page, Device, GROUP , ItemID, Item, Value);


which gives me:
| #1064 - 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 'Group, ItemID, Item, Value)' at line 2

I cannot find the error. Please help me!
MySQL version = 5.0.32-Debian_7etch8-log

TIA,
  Ralph


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Growing database & Performance

[Andrew Braithwaite]

>> Would it be beneficial to divide this database tables 
>> across different databases where each database holds some tables?

If you are planning to scale to large amounts of database activity in the 
future then yes, this will help very much.  If you split your tables into 
several logical databases and ensure there are no cross-database joins; in the 
future you will be able to scale by moving some of the logical databases onto 
separate physical hosts relatively easily.

Of course tuning your SQL/mysql configuration/operating system/indexes properly 
will afford you the best scalability rather than throwing hardware at it.

Cheers,

Andrew

-Original Message-
From: fa so [mailto:fak...@yahoo.com] 
Sent: 26 June 2009 16:26
To: mysql@lists.mysql.com
Subject: Growing database & Performance

I have a website where my database is continuously growing. And I started being 
worried about performance.
I have a couple of questions, and I would appreciate it very much if you can 
elaborate on them.

- I have about 70 tables in the same database.  some of them are becoming very 
large (over 1 million record), and I guess in a couple of months some of them 
will be double in size, like the mailbox table. Would it be beneficial to 
divide this database tables across different databases where each database 
holds some tables? 

- I was looking at MySQL table partition, and I would like to try it. I am not 
sure though what is the best way to do it, for example in my mailbox table, I 
have "senderId" and "receiverId" as keys and I query inbox and outbox using 
these keys.. If I want to paritition the table, what is the partion by method I 
should use?  

- do you think dividing my mailbox table into separate tables for inbox and 
outbox like: mailbox_inbox, mailbox_outbox, and mailbox_messages would be 
beneficial?


- I am also guessing that my mailbox table will be holding 10s of
millions of records in a year or two period.. and I am not sure about
the best way to habdle such a table when it grows very much.. any ideas
on how to plan for such a senario? 
I can imagine the best way would be to create many tables each holding a 
portion of the mailbox table while using MySQL partition on each of them... I 
am wondering though about the best way to map "senderId" and "receiverId" to 
the correct table

thank you



  

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

ANN: MicroOLAP DAC for MySQL 2.7.2 released

[Mikhail Oleynik]

Greetings!

Version 2.7.2 (June 16, 2009)

This release introduces several new properties and
design-time features. There are also several errors fixed.

Don't forget to uninstall old version before installing the new one.

If you install DAC for MySQL from sources you should build MySQLDAC*.dpk
package first, and then install design-time dcl_MySQLDAC*.dpk package.

Full changelog:

[+] TMySQLDatabase.DesignOptions property added
Details at 
http://microolap.com/products/connectivity/mysqldac/help/TMySQLDatabase/Properties/DesignOptions.htm

[+] Ability to treat TINYINT(1) fields as Boolean with "1"/"0" values
as "TRUE"/"FALSE"
Details at 
http://microolap.com/products/connectivity/mysqldac/help/Other/DataTypesMap.htm

[+] TmySQLTable.ReopenOnIndexChange property added. Resultset is
sorted locally using client-side sorting if it set to False
Details at 
http://microolap.com/products/connectivity/mysqldac/help/TMySQLTable/Properties/ReopenOnIndexChange.htm

[-] "Locate() method failed for TWideStringField columns after
RADStudio 2009 Update3/4" bug fixed

[-] "Field 'field name' is not indexed by current index and cannot be
modified" exception was raised when using SetRangeStart() method

[-] Sometimes BLOB data size was determined improperly and caused
errors with BLOB-fields storing binary data.

[-] "TmySQLTable.FindKey() throws an EDatabaseError exception 'Record
not found' when dataset is empty" bug fixed

[-] "TmySQLTable.GoToNearest() doesn't work when subset of fields is
given on a multi-field index" bug fixed

[-] "TmySQLTable.Delete() does not delete row when dataset status is in
dsEditModes" bug fixed

[-] "Unicode data are replaced with ??? sometimes when TmySQLQuery is
used with TmySQLUpdateSQL under Delphi 2009" bug fixed

[-] "mySQLTypes.NextSQLToken.GetSQLToken() will never return the token
for "FOR UPDATE" bug fixed

[-] Bug in TmySSHDatabase component fixed

You're welcome to download the DAC for MySQL v2.7.2 right now at:
http://microolap.com/products/connectivity/mysqldac/download/ ,
or login to your private area on our site at http://microolap.com/my/downloads/

Please don't hesitate to ask any questions or report bugs with our
Support Ticketing system available at
http://www.microolap.com/support/

-- 
MicroOLAP Technologies Team


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Problem with Dynamic table names.

[Joerg Bruehe]

Marco, all,


Marco Bartz wrote:
> I accidentally sent it before finishing...
> 
> I am looking for a way to do the following with a single query:
>> SELECT `ID`, `Name`, `Interface`,
>> (SELECT count(*) FROM CONCAT('listings_', `ID`) WHERE `Status`='Active') as
>> `activeListings`
>> FROM `sites`

There is no such thing as "dynamic identifiers" in SQL:
You cannot construct any identifier (like database, table, or column
name) inside an SQL statement.

> 
> I am querying the sites table and I want to have a field showing active
> listings.
> 
> Each row in the sites table has a corresponding 'listings' table. so a site
> where `ID`=1 will have a listings_1 table.

If you keep your current database schema (not change it, as proposed by
Nigel Wood), your only chance is "dynamic SQL":
construct the whole SQL statement at run time, within your application.
The MySQL command line client supports this in "prepared statements",
and of course any programming language (like Perl, PHP, ...) that
provides string operations (and allows using strings for SQL statements)
also supports this.

> 
> If I want to return data from 100 sites is there a way to get the active
> listings count without making 100 extra querys?

If the data of these 100 sites is stored in 100 different tables, you
need SQL statements accessing them.
If you like, you can try with a single big UNION of 100 SELECTs, but I
would not advise you to do so.


HTH,
Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg.bru...@sun.com
Sun Microsystems GmbH,   Komturstraße 18a,   D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Problem with Dynamic table names.

[nigel wood]

Marco Bartz wrote:

I accidentally sent it before finishing...

I am looking for a way to do the following with a single query:
  

SELECT `ID`, `Name`, `Interface`,
(SELECT count(*) FROM CONCAT('listings_', `ID`) WHERE `Status`='Active') as
`activeListings`
FROM `sites`




I am querying the sites table and I want to have a field showing active
listings.

Each row in the sites table has a corresponding 'listings' table. so a site
where `ID`=1 will have a listings_1 table.

If I want to return data from 100 sites is there a way to get the active
listings count without making 100 extra querys?

  
At first glance your table design looks to be sub optimal, a table per 
site certainly isn't normalised particularly if the structure of each 
listings table is the same. Do you have this structure for performance 
or archiving reasons?


You certainly could do this in two queries if you used the results of 
the first to build a large write a left join or UNION query for the 
second but it maybe just be using clever SQL to make up for poor table 
design and not scalable in the long term. The queries would be:


select ID from sites;

SELECT `ID`, `Name`, `Interface`, count(*) as active
FROM `sites`
 begin one left join per ID 
left join on  where site.id = '[ID]' and listings_[ID].status = 
'Active'
 end one left join per ID 
group by sites.id;


Assuming you've a good reason for the table design perhaps a merge table 
is more what you need?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Problem with Dynamic table names.

[Marco Bartz]

I accidentally sent it before finishing...

I am looking for a way to do the following with a single query:
> SELECT `ID`, `Name`, `Interface`,
> (SELECT count(*) FROM CONCAT('listings_', `ID`) WHERE `Status`='Active') as
> `activeListings`
> FROM `sites`
>

I am querying the sites table and I want to have a field showing active
listings.

Each row in the sites table has a corresponding 'listings' table. so a site
where `ID`=1 will have a listings_1 table.

If I want to return data from 100 sites is there a way to get the active
listings count without making 100 extra querys?

Problem with Dynamic table names.

[Marco Bartz]

I am looking for a way to do the following with a single query:
SELECT `ID`, `Name`, `Interface`,
(select count(*) FROM CONCAT('listings_', `ID`) WHERE `Status`='Active') as
`activeListings`
FROM `sites`