Here's the table:
CREATE TABLE `lsd` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`latitude` decimal(10,8) NOT NULL,
`longitude` decimal(12,8) NOT NULL,
`coordinates` point NOT NULL,
PRIMARY KEY (`id`),
SPATIAL KEY `latlng` (`coordinates`(32))
) ENGINE=MyISAM DEFAULT CHARSE
Hi ,
Try this query...
SELECT COUNT(*), column1, column2 FROM tablename
GROUP BY column1, column2
HAVING COUNT(*)>1;
Thanks,
ViSolve DB Team
- Original Message -
From: "Tomás Abad Fernández" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, February 06, 2007 9:57 PM
Subject: How find duplicat
In the last episode (Feb 06), Tim Johnson said:
> I'm currently using OS linux slackware 10.0 with MySQL version 4.0.20
> I currently use python and rebol APIs to MySQL, not PHP.
>
> I've downloaded mysql-standard-5.0.27-linux-i686.tar.gz
> Questions:
> Should I first upgrade to to an intermedia
Hello:
I'm currently using OS linux slackware 10.0 with MySQL version 4.0.20
I currently use python and rebol APIs to MySQL, not PHP.
I've downloaded mysql-standard-5.0.27-linux-i686.tar.gz
Questions:
Should I first upgrade to to an intermediate version?
IF so, which?
URLs to relevant doc
On 2/6/07, Yong Lee <[EMAIL PROTECTED]> wrote:
Hi all,
Just wondering how people are dealing with tables that are used for
logging, ie: insert only tables supporting occasional queries used for audit
or event logs.
These tables will keep growing and there is no need to keep them that
large
> Hi all,
>
>
>
> Just wondering how people are dealing with tables that are used for
> logging, ie: insert only tables supporting occasional queries used for
> audit or event logs.
>
> These tables will keep growing and there is no need to keep them that
> large so what is the best strategy in man
Hi all,
Just wondering how people are dealing with tables that are used for logging,
ie: insert only tables supporting occasional queries used for audit or event
logs.
These tables will keep growing and there is no need to keep them that large
so what is the best strategy in managing the data
Hi
You can 'attack' the problem from another perspective :-P
I found out a tool (Linux) to convert all characters in a latin1 file to
utf8.
The command name is 'iconv' and in Debian it is shipped with the libc6
package.
So basically dump you DB and convert the file then import it.
See the articl
There is a more robust way if you running MySQL 5
Export this query using mysql client to an SQL script like this
mysql -h<...> -u<...> -p<...> --skip-column-names -A -e"SELECT CONCAT('OPTIMIZE
TABLE ',table_schema,'.',table_name,';') FROM information_schema.tables WHERE
ENGINE='InnoDB'"
Then
This is of course MySQL 5
SELECT engine FROM information_schema.tables
WHERE table_schema='...' AND table_name='...';
I haven't used MySQL 4
Maybe this might help
SHOW CREATE TABLE ;
You should see TYPE=MyISAM or TYPE=BDB or some other engine in the string
- Original Message -
From: "Ma
Marten Lehmann wrote:
How can I check which tables are using innodb with sql? How can walk through
the tables with "show databases" and "show tables". Thanks.
This somewhat depends on how the tables were declared. If you used
"ENGINE=InnoDb;" in the CREATE TABLE sequence, you'd be able to lo
Marten
In more recent version you can do a simple...
SELECT ENGINE FROM TABLES WHERE TABLE_SCHEMA = "{database name}" AND
TABLE_NAME = "{table name}";
...dunno how you'd do it on older versions exactly, you can do...
SHOW TABLE STATUS WHERE Name = "BID_UNIQUE_IDS";
...but you can't sel
Marten
In more recent version you can do a simple...
SELECT ENGINE FROM TABLES WHERE TABLE_SCHEMA = "{database name}" AND
TABLE_NAME = "{table name}";
...dunno how you'd do it on older versions exactly, you can do...
SHOW TABLE STATUS WHERE Name = "BID_UNIQUE_IDS";
...but you can't sel
>how can I get the type of a table (MyISAM, InnoDB, Berkeley DB)
>by a given table name with sql?
select engine
from information_schema.tables
where table_schema='dbname' and table_name='tblname';
PB
Marten Lehmann wrote:
Hello,
how can I get the type of a table (MyISAM, InnoDB, Berkeley DB)
Hello,
how can I get the type of a table (MyISAM, InnoDB, Berkeley DB) by a
given table name with sql?
Regards
Marten
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hello,
In a table [say t100], having 100 columns,
I want to allow the select on all columns but 1.
I tried to do this by granting all columns in the table t100, of the base,
then revoke SELECT on the column "hide_this",
but this doesn't work.
mysql> GRANT SELECT ON the_base.t100 to 'a_user'@'lo
That actually should be
HAVING ( NumOfFoos > 1 )
- Original Message -
From: "Lars Schwarz" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, February 06, 2007 11:26 AM
Subject: Re: How find duplicate entries
SELECT foobar,
COUNT(foobar) AS NumOfFoos
FROM bar
GROUP BY foobar
HAVING ( COUNT(foob
SELECT foobar,
COUNT(foobar) AS NumOfFoos
FROM bar
GROUP BY foobar
HAVING ( COUNT(foobar) > 1 )
On 2/6/07, Tomás Abad Fernández <[EMAIL PROTECTED]> wrote:
Any can tell me a slq to find duplicate entries in a table?
Thanks,
Tomás
--
Lars Schwarz
Gottorpstrasse 20
26122 Oldenburg
T 0441 2
I have run into that bug with mysql 5.0.24a: bug #16979 auto-inc
My application was running OK on 4.1.7. But after migration to 5, the
problem started.
My questions are:
1-Can I run the patch on 5.0.24a even if it is for 5.0.21.
2-Which patch finally is the good one : the one from [13 May 20
ViSolve DB Team wrote:
> Hi,
>
>> From MySQL 4.1, there is a support for mysqldump --compatible option.
> There is a safe/cool dump for your table:
> Try lik:
> shell > mysqldump -u dev -p visolvetestdb credits
> --compatible=postgresql > /home/test/ps.sql
>
> And also,
> By default tables are d
Have you tried using the WITH ROLLUP option after the group by clause? It seems to me that might give you something close to what
you are looking for.
- Original Message -
From: "C.R.Vegelin" <[EMAIL PROTECTED]>
To: "Lars Schwarz" <[EMAIL PROTECTED]>;
Sent: Tuesday, February 06, 2007
Thanks Lars, Dusan,
I found out that the problem is caused by an ORDER BY clause,
left out in my example because I had no idea this would be the problem.
It works fine with LEFT JOIN and GROUP BY.
However, I need the ORDER BY ...
Any more suggestions to work around ?
Thanks, Cor
- Origina
oops, sorry, forget the last post i made. it's not related to the
group by. it should work like that from my point of view. a stripped
down version of this works for me (tried without the left join)
On 2/6/07, Lars Schwarz <[EMAIL PROTECTED]> wrote:
i suppose this to be working when you leave th
try to put parenthesis around @runsum := @runsum +
SUM(db.Jan+db.Feb+db.Mar)
SELECT @row := @row+1 AS `Row`, mycountries.Name AS `Country`
, ROUND(SUM(db.Jan+db.Feb+db.Mar)) AS `Q1`
, (@runsum := @runsum + SUM(db.Jan+db.Feb+db.Mar)) AS `RunSum`
FROM data2006 AS db
LEFT JOIN mycountries ON db.Cou
I'm trying out the MySql database connection example from "C++ Gui
programming with Qt".
I'm using the binary build of Qt open-source edition from the books cd,
and a binary build of MySql 5.0.27 downloaded from the official MySql
website.
The Qt libraries includes a MySql driver, which howe
i suppose this to be working when you leave the group by?
On 2/6/07, C.R.Vegelin <[EMAIL PROTECTED]> wrote:
I want to calc a running sum with @variables.
Using the command line client, I enter:
SET @row := 0, @runsum := 0;
followed by:
SELECT @row := @row+1 AS `Row`, mycountries.Name AS `Country
I want to calc a running sum with @variables.
Using the command line client, I enter:
SET @row := 0, @runsum := 0;
followed by:
SELECT @row := @row+1 AS `Row`, mycountries.Name AS `Country`
, ROUND(SUM(db.Jan+db.Feb+db.Mar)) AS `Q1`
, @runsum := @runsum + SUM(db.Jan+db.Feb+db.Mar) AS `RunSum`
FRO
Nils Meyer wrote:
Hi Faygal,
Fagyal Csongor wrote:
for (1..5) {
$dbh = DBI->connect($dsn, $user, $password, {'RaiseError' => 1} );
my $sth = $dbh->prepare('SELECT * FROM users');
}
I think you are simply running out of available outgoing ports with
that. Here is some more insigh
Hi Faygal,
Fagyal Csongor wrote:
for (1..5) {
$dbh = DBI->connect($dsn, $user, $password, {'RaiseError' => 1} );
my $sth = $dbh->prepare('SELECT * FROM users');
}
I think you are simply running out of available outgoing ports with
that. Here is some more insight on that topic:
ht
Hi,
I have an interesting (a.k.a. frustrating) problem on MySQL 4.1.11.
I try to connect to the database via DBD::mysql. Everything works - except in a
few cases (once in every 1 occasions, approximately) I get:
DBI connect('database=test:host=192.168.0.200','test',...) failed: Can't
conn
30 matches
Mail list logo
