Crystal Reports

[Vicente]

Hola,
 
Tengo una base de datos realizada con MySQL y desearía generar informes
mediante Crystal Reports XI. Al intentar conectar a MySQL via ODBC 3.51.10
me sale el siguiente error:
Logon Failed
Details: ADO error Code 0x8000fff
Source: Provider
Description: Error catastrófico
Native Error: -2147418113
 
Algiuen sabe que estoy haciendo mal?
 
Gracias, 
Vicente
 
 

Re: 1064 error

[Schalk]

Thanks Roger, I wondered whether it was the spaces that caused the problem.

Roger Baklund wrote:


Schalk Neethling wrote:


Greetings

What might be causing the 1064 error in the following query?

SELECT mem_number, first_name, last_name, area_represented,
joining_points + E-Model Challenge + SA Pro Model + Star Model 
Challenge + Eastern Cape Classic + SA Model Super Star + KZN Model GP 
+ Mpumalanga GP + Glam Slam Model Challenge + Model Man Woman + SA 
Look of the Year + SA Face of the Year + KZN Classic + Eastern Cape 
GP + Western Cape Classic + Free State Classic + North West GP + 
Northern Province Classic + SA Model Open + Cover Search + Champion 
of Champions + Northern Cape Classic + Goldfields Model GP + Limpopo 
Classic + SA Model Portfolio + Top Model Challenge + Gauteng Model 
Classic + Year of the Model AS total_points

FROM modelcup.ab_leader_board

All of the rows does exist in the table and all row names are correct. 



row names ?

You seem to be selecting data from a table called ab_leader_board in a 
database called modelcup. Some of the fields/columns in the table 
seems to be mem_number, first_name, last_name, area_represented and 
joining_points, but then it gets unclear... what does the next part 
mean:  joining_points + E-Model Challenge +


MySQL will interpret this as ...joining_points pluss E minus Model AS 
Challenge pluss... and give a syntax error. (The 'AS' alias keyword 
is optional.)


You can't use + for concatination, if that is what you are trying to do.

If you have columns named E-Model Challenge, SA Pro Model and so 
on, and you want to add the numeric value of all these columns into 
one column named total_points, you must use `backticks` because of 
the spaces in the names:


  joining_points + `E-Model Challenge` + `SA Pro Model` + ...

URL: http://dev.mysql.com/doc/mysql/en/legal-names.html 



--
Kind Regards
Schalk Neethling
Web Developer.Designer.Programmer.President
Volume4.Business.Solution.Developers



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: database link

[Ananda Kumar]

Hi Mark,
Thanks for the email. Yes i also read about this.
But the problem with this approach is since our oracle db is a production 
database which runs 24*78 and during night time it runs lot of other jobs i 
cannot make the reporting job run from this oracle database. 
 regards
anandkl

 On 9/16/05, Mark Leith [EMAIL PROTECTED] wrote: 
 
 You may want to think about doing this the opposite way around also, and
 look at pushing the data from Oracle in to MySQL. Oracle has something
 called heterogeneous services, which allows you to define ODBC 
 datasources
 as valid entries in the tnsnames.ora file. Then you could simply create a
 job in Oracle that executes a procedure to do the entire process (truncate 
 /
 load), no external scripting necessary..
 
 Here's a quick example of what to do:
 
 First set up an ODBC data source for your MySQL database, using MyODBC.
 
 Create a file in ORACLE_HOME/hs/admin called initMySQL.ora. In this file 
 put
 the following options:
 
 HS_FDS_CONNECT_INFO = MySQL5 #ODBC DSN /* Replace MySQL5 with your DSN */
 HS_FDS_TRACE_LEVEL = OFF
 
 Alter your listener.ora file (ORACLE_HOME/network/admin) to add the
 following:
 
 (SID_DESC =
 (PROGRAM = hsodbc)
 (ORACLE_HOME = oracle/product/92) /* Your ORACLE_HOME */
 (SID_NAME = MySQL5) /* Your DSN */
 )
 
 Add the following to your tnsnames.ora file:
 
 MYSQL5 =
 (DESCRIPTION=
 (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
 (CONNECT_DATA=(SID=MYSQL5))
 (HS=OK)
 )
 
 Reload your Oracle listener (lsnrctl reload), and then connect to the 
 Oracle
 database. To set the database link up:
 
 CREATE DATABASE LINK mysql5 CONNECT TO user identified by password 
 using
 'mysql5';
 
 User and password should be a valid user within MySQL, that can connect 
 from
 the Oracle host. You should be set to go from there. Here's a quick 
 example
 of this working, to a MySQL 5 database using the new sakila sample
 database that Mike Hillyer recently released
 (http://www.openwin.org/mike/download/sakila.zip):
 
 SQL select count(*) from [EMAIL PROTECTED];
 
 COUNT(*)
 --
 1000
 
 SQL desc [EMAIL PROTECTED];
 Name Null? Type
 - 
 
 film_id NUMBER(10)
 category_id NOT NULL NUMBER(10)
 title NOT NULL VARCHAR2(27)
 description LONG
 rental_duration NOT NULL NUMBER(3)
 length NUMBER(10)
 rating CHAR(5)
 
 SQL insert into [EMAIL PROTECTED] values (100,1,'test','test',1,1,'PG');
 
 1 row created.
 
 ---change prompts---
 
 mysql use sakila
 Database changed
 mysql select max(film_id) from film;
 +--+
 | max(film_id) |
 +--+
 | 100 |
 +--+
 1 row in set (0.01 sec)
 
 HTH
 
 Mark Leith
 
 --
 No virus found in this outgoing message.
 Checked by AVG Anti-Virus.
 Version: 7.0.344 / Virus Database: 267.11.0/103 - Release Date: 15/09/2005
 
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
 

Re: Yet another null question

[Martijn Tonies]

Hello Doug,


 My question question from MySQL 4.0.22: is it okay that a null interger
tests as
 equal to any value. I am not sure the table structure matters but in case
it is
 I included it.

 I did not expect that null was not not-equal to 1. Cursory testing seems
to
 indicate that a null integer field is not not-equal to any specific value.
If
 this is the known behavior, I would suggest that this behavior be added
to
 section A.5.3. Testing the field=1 works as I would expect, that is the
null
 value is not equal to 1.

That's because NULL is NOT a value (not the logical not ;) ... It's a
state.

Any column can have two states: either NULL or NOT NULL.

When a column is not null it can have values suitable for it's given
domain
(eg: all integer values).

If there's no value (NULL), how can you compare it?

Think of NULL as unknown. Is 2 equal to unknown? I don't know, so
the result is unknown, hence, NULL.


Hope this helps,

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle  MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: database link

[Ananda Kumar]

Hi Mark,
Thanks for the email. Yes i also read about this.
But the problem with this approach is since our oracle db is a production 
database which runs 24*78 and during night time it runs lot of other jobs i 
cannot make the reporting job run from this oracle database. 
 regards
anandkl

 On 9/16/05, Mark Leith [EMAIL PROTECTED] wrote: 
 
 You may want to think about doing this the opposite way around also, and
 look at pushing the data from Oracle in to MySQL. Oracle has something
 called heterogeneous services, which allows you to define ODBC 
 datasources
 as valid entries in the tnsnames.ora file. Then you could simply create a
 job in Oracle that executes a procedure to do the entire process (truncate 
 /
 load), no external scripting necessary..
 
 Here's a quick example of what to do:
 
 First set up an ODBC data source for your MySQL database, using MyODBC.
 
 Create a file in ORACLE_HOME/hs/admin called initMySQL.ora. In this file 
 put
 the following options:
 
 HS_FDS_CONNECT_INFO = MySQL5 #ODBC DSN /* Replace MySQL5 with your DSN */
 HS_FDS_TRACE_LEVEL = OFF
 
 Alter your listener.ora file (ORACLE_HOME/network/admin) to add the
 following:
 
 (SID_DESC =
 (PROGRAM = hsodbc)
 (ORACLE_HOME = oracle/product/92) /* Your ORACLE_HOME */
 (SID_NAME = MySQL5) /* Your DSN */
 )
 
 Add the following to your tnsnames.ora file:
 
 MYSQL5 =
 (DESCRIPTION=
 (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
 (CONNECT_DATA=(SID=MYSQL5))
 (HS=OK)
 )
 
 Reload your Oracle listener (lsnrctl reload), and then connect to the 
 Oracle
 database. To set the database link up:
 
 CREATE DATABASE LINK mysql5 CONNECT TO user identified by password 
 using
 'mysql5';
 
 User and password should be a valid user within MySQL, that can connect 
 from
 the Oracle host. You should be set to go from there. Here's a quick 
 example
 of this working, to a MySQL 5 database using the new sakila sample
 database that Mike Hillyer recently released
 (http://www.openwin.org/mike/download/sakila.zip):
 
 SQL select count(*) from [EMAIL PROTECTED];
 
 COUNT(*)
 --
 1000
 
 SQL desc [EMAIL PROTECTED];
 Name Null? Type
 - 
 
 film_id NUMBER(10)
 category_id NOT NULL NUMBER(10)
 title NOT NULL VARCHAR2(27)
 description LONG
 rental_duration NOT NULL NUMBER(3)
 length NUMBER(10)
 rating CHAR(5)
 
 SQL insert into [EMAIL PROTECTED] values (100,1,'test','test',1,1,'PG');
 
 1 row created.
 
 ---change prompts---
 
 mysql use sakila
 Database changed
 mysql select max(film_id) from film;
 +--+
 | max(film_id) |
 +--+
 | 100 |
 +--+
 1 row in set (0.01 sec)
 
 HTH
 
 Mark Leith
 
 --
 No virus found in this outgoing message.
 Checked by AVG Anti-Virus.
 Version: 7.0.344 / Virus Database: 267.11.0/103 - Release Date: 15/09/2005
 
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
 

Re: mysql_free_result() WITHOUT mysql_fetch_row()

[Pooly]

Hi,

always reply to the list please

2005/9/19, Miguel Cardenas [EMAIL PROTECTED]:
  I'm a bit confused. If you just need to know if there is data matching
  a criteria, a count(*) is enough and will do absolutely the same thing
  that you want, and spare you the mysql_store_result with a whole
  dataset.
  count(*) as nothing to do with knowing is there is data in the table or
  not... If I'm still wrong, could you provide a sample query ?
 
 Well maybe am making a storm in a glass trying that and possibly there is
 another solution...
 
 There is a table like this:
 
 --
 |   id INT   |   list CHAR(16)   |
 --
 
 I have (id,list) pairs, there may be duplicate id's or list's separately, but
 together may exist unique pairs.
 
 a,1 --- ok
 a,2 --- ok
 b,1 --- ok
 b,2 --- ok
 a,1 --- error, duplicated pair
 
 so, in pseudocode I do this
 
 lets supose I want to insert (x,something)
 
 while ( retrieve data from file ) {
1. select id,list from mytable where id=x and list=something
2. use_result()
3. fetch_row()!=NULL ? (data exists already)
   yes: don't do nothing
   no:  insert into mytable values(x,something)
...
 }
 
 the process is repeated thousands of times, so, retrieving all data in every
 loop would make a big difference, 'cos that I use use_result() instead of
 store_result() thas would retrive all data in every loop...


Have you try :
select count(*) from mytable where id=x and list=something
if count ==0, it's like fetchrow==NULL from your solution, but without
all the fuss about use_result();

 
 I am not very experienced in mysql, with a 'UNIQUE' option for a field allows
 only one unique field, but in this case fields may be duplicated, what can
 not be duplicated are pairs, 'cos that first I see if it already exists on
 the table before insertion.

You may run into problem if two process access your table at the same time.
First solution would be to : lock the table, check the existance,
insert if it's ok, unlock the table.
Second solution :
ALTER TABLE mytable ADD UNIQUE(id,list)
which make a unique index on two field. inserting a duplicate value
would give you back an error and let the table untouched.

 
 Maybe you have a suggestion to do the same in another way.
 
 Thanks
 


-- 
Pooly
Webzine Rock : http://www.w-fenec.org/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Drop Table Problem

[xtcsuk]

Running the command:
Drop table if exists table1
complaints of table1 does not exist (Error: 1051).
However, if table1 is swapped with another table, irrespective of its
existence it works just fine.

Running a Show Table command lists table1 as an existing table and
trying to create table1 complaints of this table already eixsts.

Please advise.

regards

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query matches twice, but not simultaneously...

[Hobbs, Richard]

Hello,

Perfect :-)

Thank you,
Hobbs.


Quoting Peter Brawley [EMAIL PROTECTED]:


Richard

I would like to display all messages which match both 5 and 7 in terms
of the parent_id, meaning messages 10 and 13 would be displayed.

SELECT f1.child
FROM foo AS f1
INNER JOIN foo AS f2 USING(child)
WHERE  f1.parent=5 AND f2.parent=7;

PB

-

Hobbs, Richard wrote:


Hello,

I have two tables - one containing messages, and another containing links
between messages and other messages in a tree structure (much like a 
threaded

mailing list archiving thing). A single message can have multiple parents
though, meaning the links table can have several entries for a 
single message.

For example:


childparent
105
107
115
127
135
137


I would like to display all messages which match both 5 and 7 in 
terms of the

parent_id, meaning messages 10 and 13 would be displayed.

I have used the following query:


SELECT DISTINCT message.username,message.content
FROM message,links WHERE links.child_id = message.id AND (
links.parent_id = 5 OR links.parent_id = 7
);


NOTE: Without the word DISTINCT, if this query finds a message that 
matches both
5 AND 7, it will display the message twice. I have obviously used 
DISTINCT as an

easy way to get around this problem.

However, this query displays the message if it matches 5 OR 7. I 
only want it to

be displayed if it matches 5 AND 7.

However, if i change the word OR to AND, it displays no message at all!

I presume this is because it finds two instances of each message, neither of
which match both 5 and 7 (i.e. the first instance matches 5, but not 
7, and the

second instance matches 7, but not 5).

Does anyone know how i can get around this problem?

Thanks in advance to anyone who can help! :-)

Richard.





--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.1/104 - Release Date: 9/16/2005


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]






--
Richard Hobbs
[EMAIL PROTECTED]

Visit my web sites: http://mysites.mongeese.co.uk

Would you like jokes in your email? http://jokes.fishsponge.co.uk
Would you like to discuss unix/linux?   http://ufq.unixforum.co.uk


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

possible MySQL bug - insert into 'double' column problem with mysql 4.1

[Andrew Braithwaite]

Hi All,

I have a strange error when trying to insert into a table with 2
'double' fields.  It inserts into the 1st field OK but fills the 2nd one
with nines.  See below for a complete recreate.

Is this a known problem?  Does anyone have a solution?

I'm running standard MySQL binaries on redhat linux 7.2 kernel
2.4.20-28.7smp.  Help!

Cheers,

Andrew


mysql desc table1;
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra  |
+---+--+--+-+-++
| id| mediumint(6) |  | PRI | NULL| auto_increment |
| GeoQuality| varchar(5)   | YES  | | NULL||
| lon   | double(7,6)  | YES  | MUL | NULL||
| lat   | double(7,6)  | YES  | | NULL||
| GeocodeDate   | date | YES  | | NULL||
| GeocodeSource | varchar(25)  | YES  | | NULL||
| state | varchar(70)  | YES  | | NULL||
| client_id | varchar(40)  | YES  | MUL | NULL||
+---+--+--+-+-++
15 rows in set (0.00 sec)

mysql INSERT INTO table1VALUES
(8002,'2a',-0.361080,39.468270,'2005-08-31','ES052',NULL,NULL);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql select * from table1 where id=8002\G
*** 1. row ***
   id: 8002
   GeoQuality: 2a
  lon: -0.361080
  lat: 9.99
  GeocodeDate: 2005-08-31
GeocodeSource: ES052
state: NULL
client_id: NULL
1 row in set (0.00 sec)

Mysql, query 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: possible MySQL bug - insert into 'double' column problem with mysql 4.1

[Martijn Tonies]

Hello Andrew,

 I have a strange error when trying to insert into a table with 2
 'double' fields.  It inserts into the 1st field OK but fills the 2nd one
 with nines.  See below for a complete recreate.

 Is this a known problem?  Does anyone have a solution?

 I'm running standard MySQL binaries on redhat linux 7.2 kernel
 2.4.20-28.7smp.  Help!

 Cheers,

 Andrew


 mysql desc table1;
 +---+--+--+-+-++
 | Field | Type | Null | Key | Default | Extra  |
 +---+--+--+-+-++
 | id| mediumint(6) |  | PRI | NULL| auto_increment |
 | GeoQuality| varchar(5)   | YES  | | NULL||
 | lon   | double(7,6)  | YES  | MUL | NULL||
 | lat   | double(7,6)  | YES  | | NULL||
 | GeocodeDate   | date | YES  | | NULL||
 | GeocodeSource | varchar(25)  | YES  | | NULL||
 | state | varchar(70)  | YES  | | NULL||
 | client_id | varchar(40)  | YES  | MUL | NULL||
 +---+--+--+-+-++
 15 rows in set (0.00 sec)

 mysql INSERT INTO table1VALUES
 (8002,'2a',-0.361080,39.468270,'2005-08-31','ES052',NULL,NULL);
 Query OK, 1 row affected, 1 warning (0.01 sec)

My guess is that the second value overflows the maximum value in the column
and that MySQL is SILENTLY inserting the max value for the column.

Have you tried a larger double column?

Obviously: screw silent data changes!!

 mysql select * from table1 where id=8002\G
 *** 1. row ***
id: 8002
GeoQuality: 2a
   lon: -0.361080
   lat: 9.99
   GeocodeDate: 2005-08-31
 GeocodeSource: ES052
 state: NULL
 client_id: NULL

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle  MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: possible MySQL bug - insert into 'double' column problem with mysql 4.1

[Roger Baklund]

Andrew Braithwaite wrote:

Hi All,

I have a strange error when trying to insert into a table with 2
'double' fields.  It inserts into the 1st field OK but fills the 2nd one
with nines.  See below for a complete recreate.

[...]

| lon   | double(7,6)  | YES  | MUL | NULL||
| lat   | double(7,6)  | YES  | | NULL||


From the manual:

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

M is the total number of decimal digits and D is the number of digits 
following the decimal point.


URL: http://dev.mysql.com/doc/mysql/en/numeric-type-overview.html 


mysql INSERT INTO table1VALUES
(8002,'2a',-0.361080,39.468270,'2005-08-31','ES052',NULL,NULL);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql select * from table1 where id=8002\G
*** 1. row ***
   id: 8002
   GeoQuality: 2a
  lon: -0.361080
  lat: 9.99


You have created your columns with a max width of 7, out of which 6 are 
decimals, but you are trying to insert a number with 8 digits.


--
Roger


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How to bind mysqld to 2 ip on 3

[andrea_brujo_websol]

Hi All,
I have a linux box with 2 ethenet interfaces let's say one public
10.10.10.10 and one private 192.168.1.1. How can I configure my.cfg to
listen on 192.168.1.1 and 127.0.0.1 but not 10.10.10.10? 

Thanks in advance.
Andrea Sodomaco
 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Circular Replication

[Sid Lane]

stupid ?:

what keeps them from getting caught in a write loop? turning off 
log_slave_updates?

I had never thought of this but is has intriging possibilities...

Crystal Reports

[Vicente]

Hello,
 
I have a MySQL database. Now I would like to create reports with Crystal
Reports XI. When I try to connect Crystal Reports to MySQL through ODBC
Connector 3.51.1 this error happens:
 
Details: ADO error Code 0x8000fff
Source: Provide
Description: Fatal Error
Native Error: -2147418113
 
Can anybody help me?
 
Thank you
Vicente

Re: Circular Replication

[Alec . Cawley]

Sid Lane [EMAIL PROTECTED] wrote on 19/09/2005 15:02:58:

 stupid ?:
 
 what keeps them from getting caught in a write loop? turning off 
 log_slave_updates?
 
 I had never thought of this but is has intriging possibilities...

Each update is marked with the unique server id of the server which 
originated it. When the update returns to its originating server, it is 
dropped instead of being executed. That is why every server must have a 
unique id.

Alec

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: `gmake test` probs Solaris9 for M. 4.1.4.

[Hugh Sasse]

On Fri, 9 Sep 2005, Michael Stassen wrote:


With mysql 4.1.12, `make -n test` reveals

 cd mysql-test; ./mysql-test-run  ./mysql-test-run --ps-protocol


For the record, here's what I got on Solaris 9 for 4.1.13

neelix hgs 18 % cd mysql-test  ./mysql-test-run --force
Installing Test Databases
Removing Stale Files
Installing Master Databases
running  ../sql/mysqld --no-defaults --bootstrap --skip-grant-tables 
--basedir=. --datadir=./var/master-data --skip-innodb --skip-ndbcluster 
--skip-bdb --language=../sql/share/english/ 
--character-sets-dir=../sql/share/charsets/
Installing Slave Databases
running  ../sql/mysqld --no-defaults --bootstrap --skip-grant-tables 
--basedir=. --datadir=./var/slave-data --skip-innodb --skip-ndbcluster 
--skip-bdb --language=../sql/share/english/ 
--character-sets-dir=../sql/share/charsets/
Manager disabled, skipping manager start.
Loading Standard Test Databases
Starting Tests

TESTRESULT
---
alias  [ pass ]
alter_table[ pass ]
analyse[ pass ]
analyze[ pass ]
ansi   [ pass ]
archive[ skipped ]
auto_increment [ pass ]
backup [ pass ]
bdb-alter-table-1  [ skipped ]
bdb-alter-table-2  [ skipped ]
bdb-crash  [ skipped ]
bdb-deadlock   [ skipped ]
bdb[ skipped ]
bdb_cache  [ skipped ]
bench_count_distinct   [ pass ]
bigint [ pass ]
binary [ pass ]
blackhole  [ skipped ]
bool   [ pass ]
bulk_replace   [ pass ]
case   [ pass ]
cast   [ pass ]
check  [ pass ]
comments   [ pass ]
compare[ pass ]
connect[ pass ]
consistent_snapshot[ pass ]
constraints[ pass ]
count_distinct [ pass ]
count_distinct2[ pass ]
count_distinct3[ pass ]
create [ pass ]
create_select_tmp  [ pass ]
csv[ skipped ]
ctype_big5 [ skipped ]
ctype_collate  [ pass ]
ctype_cp1250_ch[ skipped ]
ctype_cp1251   [ pass ]
ctype_cp932[ skipped ]
ctype_create   [ pass ]
ctype_latin1   [ pass ]
ctype_latin1_de[ pass ]
ctype_latin2   [ pass ]
ctype_many [ skipped ]
ctype_mb   [ pass ]
ctype_recoding [ pass ]
ctype_sjis [ skipped ]
ctype_tis620   [ skipped ]
ctype_uca  [ skipped ]
ctype_ucs  [ skipped ]
ctype_ucs_binlog   [ skipped ]
ctype_ujis [ skipped ]
ctype_utf8 [ pass ]
date_formats   [ pass ]
delayed[ pass ]
delete [ pass ]
derived[ pass ]
dirty_close[ pass ]
distinct   [ pass ]
drop   [ pass ]
drop_temp_table[ pass ]
empty_table[ pass ]
endspace   [ pass ]
errors [ pass ]
exampledb  [ skipped ]
explain[ pass ]
flush  [ pass ]
flush_block_commit [ pass ]
flush_table[ pass ]
foreign_key[ pass ]
fulltext   [ pass ]
fulltext2  [ pass ]
fulltext_cache [ pass ]
fulltext_distinct  [ pass ]
fulltext_left_join [ pass ]
fulltext_multi [ pass ]
fulltext_order_by  [ pass ]
fulltext_update[ pass ]
fulltext_var   [ pass ]
func_compress  [ pass ]
func_concat[ pass ]
func_crypt [ pass ]
func_date_add  [ pass ]
func_default   [ pass ]
func_des_encrypt   [ skipped ]
func_encrypt   [ skipped ]
func_encrypt_nossl [ pass ]
func_equal [ pass ]
func_gconcat   [ pass ]
func_group [ pass ]
func_if[ pass ]
func_in[ pass ]
func_isnull[ pass ]
func_like  [ pass ]
func_math  [ pass ]
func_misc  [ pass ]
func_op[ pass ]
func_regexp 

How to benchmark performans

[OKAN ARI]

How can I test the performans benchmark of my MYSQL?
For instance how can I learn query per second information? And any other 
informatioin?


Thanks


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Fw: Crystal Reports

[SGreen]

I know I sent this once Was my advice useful or not?

translated by http://babelfish.altavista.com/tr 
Sé que envié esto una vez ¿Era mi consejo útil o no? 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

- Forwarded by Shawn Green/Unimin on 09/19/2005 10:20 AM -

[EMAIL PROTECTED] 
09/16/2005 09:41 AM

To
Vicente [EMAIL PROTECTED]
cc
mysql@lists.mysql.com
Subject
Re: Crystal Reports






Vicente [EMAIL PROTECTED] wrote on 09/16/2005 07:45:22 AM:

 Hola,
 
 Tengo una base de datos realizada con MySQL y desearía generar informes
 mediante Crystal Reports XI. Al intentar conectar a MySQL via ODBC 
3.51.10
 me sale el siguiente error:
 Logon Failed
 Details: ADO error Code 0x8000fff
 Source: Provider
 Description: Error catastrófico
 Native Error: -2147418113
 
 Algiuen sabe que estoy haciendo mal?
 
 Gracias, 
 Vicente
 

When using the ODBC driver, you must specify a database name or it will 
fail to connect. An initial database is optional for many of the other 
MySQL client libraries but not the ODBC driver. Also verify that you can 
connect from the MySQL command line client using the same username and 
password that you are trying to connect with through the driver. 

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Startup Error

[Johnson, Richard (NY Int)]

Hi there

 

 

 

Total Newbie here.Any idea why I am getting these error messages 

 

 

 

[EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from
/usr/local/mysql/data 

 

 

 

STOPPING server from pid file /usr/local/mysql/data/
RJLINUX.rj.johnson.net.pid

 

050919 10:05:36  mysqld ended

 

 

 

Here are the steps I followed to loading mysql onto my linux box..

 

 

 

shell groupadd mysql

 

 shell useradd -g mysql mysql

 

 shell cd /usr/local

 

 shell gunzip  /PATH/TO/MYSQL-VERSION-OS.tar.gz | tar xvf -

 

 shell ln -s FULL-PATH-TO-MYSQL-VERSION-OS mysql

 

 shell cd mysql

 

 shell scripts/mysql_install_db --user=mysql

 

 shell chown -R root  .

 

 shell chown -R mysql data

 

 shell chgrp -R mysql .

 

 shell bin/mysqld_safe --user=mysql 

 

 

Richard Johnson

212-589-6503

[EMAIL PROTECTED]

 

RE: Startup Error

[J.R. Bullington]

Check your hostname.err logs located in the /data/ directory. This will
tell you why you can't start the server.

J.R.

-Original Message-
From: Johnson, Richard (NY Int) [mailto:[EMAIL PROTECTED] 
Sent: Monday, September 19, 2005 10:38 AM
To: 'mysql@lists.mysql.com'
Subject: Startup Error

Hi there

 

 

 

Total Newbie here.Any idea why I am getting these error messages 

 

 

 

[EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from
/usr/local/mysql/data 

 

 

 

STOPPING server from pid file /usr/local/mysql/data/
RJLINUX.rj.johnson.net.pid

 

050919 10:05:36  mysqld ended

 

 

 

Here are the steps I followed to loading mysql onto my linux box..

 

 

 

shell groupadd mysql

 

 shell useradd -g mysql mysql

 

 shell cd /usr/local

 

 shell gunzip  /PATH/TO/MYSQL-VERSION-OS.tar.gz | tar xvf -

 

 shell ln -s FULL-PATH-TO-MYSQL-VERSION-OS mysql

 

 shell cd mysql

 

 shell scripts/mysql_install_db --user=mysql

 

 shell chown -R root  .

 

 shell chown -R mysql data

 

 shell chgrp -R mysql .

 

 shell bin/mysqld_safe --user=mysql 

 

 

Richard Johnson

212-589-6503

[EMAIL PROTECTED]

 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Startup Error

[Michael Stassen]

Johnson, Richard (NY Int) wrote:

Hi there

Total Newbie here.Any idea why I am getting these error messages 


[EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from
/usr/local/mysql/data 


STOPPING server from pid file /usr/local/mysql/data/
RJLINUX.rj.johnson.net.pid
050919 10:05:36  mysqld ended

Here are the steps I followed to loading mysql onto my linux box..

 shell groupadd mysql
 shell useradd -g mysql mysql
 shell cd /usr/local
 shell gunzip  /PATH/TO/MYSQL-VERSION-OS.tar.gz | tar xvf -
 shell ln -s FULL-PATH-TO-MYSQL-VERSION-OS mysql
 shell cd mysql
 shell scripts/mysql_install_db --user=mysql
 shell chown -R root  .
 shell chown -R mysql data
 shell chgrp -R mysql .
 shell bin/mysqld_safe --user=mysql 

Richard Johnson 


There should be an error log (hostname.err) in your data directory 
(/usr/local/mysql/data/).  What does it say?


Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query matches twice, but not simultaneously...

[Michael Stassen]

Hobbs, Richard wrote:
 Hello,

 I have two tables - one containing messages, and another containing links
 between messages and other messages in a tree structure (much like a
 threaded mailing list archiving thing). A single message can have
 multiple parents though, meaning the links table can have several
 entries for a single message.
 For example:

 
 childparent
 105
 107
 115
 127
 135
 137
 

 I would like to display all messages which match both 5 and 7 in terms of
 the parent_id, meaning messages 10 and 13 would be displayed.

 I have used the following query:

 
 SELECT DISTINCT message.username,message.content
 FROM message,links WHERE links.child_id = message.id AND (
 links.parent_id = 5 OR links.parent_id = 7
 );
 

 NOTE: Without the word DISTINCT, if this query finds a message that
 matches both 5 AND 7, it will display the message twice. I have obviously
 used DISTINCT as an easy way to get around this problem.

 However, this query displays the message if it matches 5 OR 7. I only
 want it to be displayed if it matches 5 AND 7.

 However, if i change the word OR to AND, it displays no message at
 all!

 I presume this is because it finds two instances of each message, neither
 of which match both 5 and 7 (i.e. the first instance matches 5, but not
 7, and the second instance matches 7, but not 5).

 Does anyone know how i can get around this problem?

 Thanks in advance to anyone who can help! :-)

 Richard.


Peter Brawley wrote:
 Richard

 I would like to display all messages which match both 5 and 7 in terms
 of the parent_id, meaning messages 10 and 13 would be displayed.

 SELECT f1.child
 FROM foo AS f1
 INNER JOIN foo AS f2 USING(child)
 WHERE  f1.parent=5 AND f2.parent=7;

 PB

Hobbs, Richard wrote:
 Hello,

 Perfect :-)

 Thank you,
 Hobbs.

Peter's solution is a self-join.  Here it is translated to your tables:

  SELECT message.username, message.content
  FROM message
  JOIN links l1 ON l1.child_id = message.id
  JOIN links l2 ON l2.child_id = message.id
  WHERE l1.parent_id = 5
AND l2.parent_id = 7;

This works fine.  For completeness, I'll point out an alternate solution.

Your original query, before you added DISTINCT, produced two rows for each 
message you wanted, and 1 row for each message that had one, but not both, 
of the desired parents.  That is, number of rows per message equals number 
of matching criteria.  We can use this difference to select only the rows 
you want:


  SELECT message.username, message.content
  FROM message
  JOIN links ON links.child_id = message.id
  WHERE links.parent_id IN (5, 7)
  GROUP BY message.id
  HAVING COUNT(*) = 2;

This replaces a JOIN with a GROUP BY, which may be faster.  You might want 
to test both ways to see which works better for your data.


If you will ever need messages with more than 2 specified parents, I think 
you'll find the second method works better.  The self-join method requires 
an additional JOIN and an additional WHERE condition for each requirement. 
For example, here's the self join for 3 criteria:


  SELECT message.username, message.content
  FROM message
  JOIN links l1 ON l1.child_id = message.id
  JOIN links l2 ON l2.child_id = message.id
  JOIN links l3 ON l3.child_id = message.id
  WHERE l1.parent_id = 5
AND l2.parent_id = 7
AND l3.parent_id = 8;

In contrast, the GROUP BY solution changes very little:

  SELECT message.username, message.content
  FROM message
  JOIN links ON links.child_id = message.id
  WHERE links.parent_id IN (5, 7, 8)
  GROUP BY message.id
  HAVING COUNT(*) = 3;

The extra criteria are added to the IN list, and the HAVING clause is 
changed to look for COUNT(*) = number_of_criteria.  In this case, notice 
that we have replaced two JOINs with one GROUP BY.


(Note: For the GROUP BY versions, I am assuming there is a unique value of 
message.username and message.content for each message.id, and I'm using a 
mysql extension 
http://dev.mysql.com/doc/mysql/en/group-by-hidden-fields.html.  If the 
assumption is wrong, or you want portability, change the GROUP BY clause to 
GROUP BY message.username, message.content.)


Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Yet another null question

[doug]

Thanks Martijn - that is a clear explanation of the philosophy. I did not get it
from the examples and the manual.

Doug

On Mon, 19 Sep 2005, Martijn Tonies wrote:

 Hello Doug,


  My question question from MySQL 4.0.22: is it okay that a null interger 
  tests as
  equal to any value. I am not sure the table structure matters but in case
 it is
[cut]
 That's because NULL is NOT a value (not the logical not ;) ... It's a
 state.

 Any column can have two states: either NULL or NOT NULL.

 When a column is not null it can have values suitable for it's given
 domain
 (eg: all integer values).

 If there's no value (NULL), how can you compare it?

 Think of NULL as unknown. Is 2 equal to unknown? I don't know, so
 the result is unknown, hence, NULL.


 Hope this helps,

 With regards,

 Martijn Tonies
 Database Workbench - tool for InterBase, Firebird, MySQL, Oracle  MS SQL
 Server
 Upscene Productions
 http://www.upscene.com
 Database development questions? Check the forum!
 http://www.databasedevelopmentforum.com


_
Douglas Denault
http://www.safeport.com
[EMAIL PROTECTED]
Voice: 301-469-8766
  Fax: 301-469-0601

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Foreign keys across databases?

[Jake Krohn]

I would like to use separate databases to group certain related tables 
to keep things clean, but I would also like to use foreign keys to 
enforce referential integrity between tables in different databases. I 
don't think this is possible. Am I right? And if so, am I reduced to 
using carefully chosen table names to keep things organized? What have 
others done in this situation?


Thanks,

Jake Krohn

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Circular Replication

[Sid Lane]

NOW I see the violence inhierent in the system...

this has some profoundly cool possibilities...

BWAH-HA-HA-HA!!!

muchos!

Re: Result row count *without* buffering all results?

[Harrison Fisk]

Hi Ken,

On Sep 18, 2005, at 3:53 PM, Ken Gieselman wrote:


Scott Gifford wrote:

Ken Gieselman [EMAIL PROTECTED] writes:
[...]

So, here's my question: How can I find out how many rows are being
returned by a query, without using mysql_stmt_store_result() to
buffer all the rows back to the client?

One straightforward way is to replace the SELECT clause with just
SELECT COUNT(*) and execute that query first.
-ScottG.


Yeah, that's my fall-back option -- though the thought of executing 
the query twice is a bit daunting.  Some of the tables run into 
billions of rows per year (the merge tables anyhow, the data tables 
are broken down by month to keep them from becoming totally 
unmanageable), and a multi-year search can take a while to grind out.


Seems to me that by the time the first query execution is done, the 
server should *know* exactly how many rows are in the result set -- 
just need to find the magic trick that allows the client to query that 
number, rather than counting for itself as it buffers them.


The problem is that your assumption isn't always correct.  The server 
doesn't always know how many rows are in a result set before it starts 
returning rows back to the client.  Imagine if you did a simple SELECT 
* FROM tbl WHERE unindexed_col = 5; statement.  The server certainly 
isn't going to read in and count the number of rows before beginning to 
send the rows back to the client, for the same reason that you don't 
want to do a store_result, it will take up too much resources.  The 
only way for the server to know 100%, is to count the rows as they are 
being returned to the client  (It could in theory do it for some 
statements, such as where it has to do a filesort, however an API that 
only sometimes worked based on the execution plan wouldn't be very 
useful) .


Regards,

Harrison

--
Harrison C. Fisk, Trainer and Consultant
MySQL AB, www.mysql.com

Get a jumpstart on MySQL Cluster -- 
http://www.mysql.com/consulting/packaged/cluster.html



Regards,

Harrison

--
Harrison C. Fisk, Trainer and Consultant
MySQL AB, www.mysql.com
Mobile: +1 315 380-6048

Get a jumpstart on MySQL Cluster -- 
http://www.mysql.com/consulting/packaged/cluster.html



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Startup Error

[J.R. Bullington]

Well, as you can see in the first few lines, you have a Permission Denied
problem.
 
This means that your /data/ folder doesn't have the write permissions to it
via the mysql user.
 
Try running the permission scripts again, but this time use the full shell
names.
 
shell chown -R root /usr/local/mysql/.
shell chown -R mysql /usr/local/mysql/data/.
shell chgrp -R mysql /usr/local/mysql/.
 
Then check to make sure that user has write permission to the folders:
 
shell ls -l /usr/local/mysql/data
 
It should say something similar to:
 
shell ls -l /usr/local/mysql/data
total 63868
drwx--2mysqlmysqlsizedatedirname
-rw-rw1mysqlmysqlsizedatefilename-bin.000
.
.
-rw-rw1mysqlmysqlsizedatehostname.err
 
 
 
J.R.
 

  _  

From: Johnson, Richard (NY Int) [mailto:[EMAIL PROTECTED] 
Sent: Monday, September 19, 2005 11:35 AM
To: 'J.R. Bullington'
Subject: RE: Startup Error



Here are the contents of the file 

050919 09:33:52  mysqld started 
050919  9:33:52 [ERROR] bdb:  /usr/local/mysql/data: Permission denied 
050919  9:33:52 [ERROR] bdb:  /usr/local/mysql/data/log.01:
Permission denied 
050919  9:33:52 [ERROR] bdb:  PANIC: Permission denied 
050919  9:33:52 [ERROR] bdb:  PANIC: DB_RUNRECOVERY: Fatal error, run
database recovery 
050919  9:33:52 [ERROR] bdb:  fatal region error detected; run recovery 
050919  9:33:52 [ERROR] bdb:  /usr/local/mysql/data: Permission denied 
InnoDB: The first specified data file ./ibdata1 did not exist: 
InnoDB: a new database to be created! 
050919  9:33:52  InnoDB: Setting file ./ibdata1 size to 10 MB 
InnoDB: Database physically writes the file full: wait... 
050919  9:33:53  InnoDB: Log file ./ib_logfile0 did not exist: new to be
created 
InnoDB: Setting log file ./ib_logfile0 size to 5 MB 
InnoDB: Database physically writes the file full: wait... 
050919  9:33:53  InnoDB: Log file ./ib_logfile1 did not exist: new to be
created 
InnoDB: Setting log file ./ib_logfile1 size to 5 MB 
InnoDB: Database physically writes the file full: wait... 
InnoDB: Doublewrite buffer not found: creating new 
InnoDB: Doublewrite buffer created 
InnoDB: Creating foreign key constraint system tables 
InnoDB: Foreign key constraint system tables created 
050919  9:33:53  InnoDB: Started; log sequence number 0 0 
050919  9:33:53 [ERROR] Can't init databases 
050919  9:33:53 [ERROR] Aborting 

050919  9:33:53  InnoDB: Starting shutdown... 
050919  9:33:55  InnoDB: Shutdown completed; log sequence number 0 43634 
050919  9:33:55 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 

050919 09:33:55  mysqld ended 

050919 09:46:55  mysqld started 
050919  9:46:55 [ERROR] bdb:  /usr/local/mysql/data: Permission denied 
050919  9:46:56 [ERROR] bdb:  /usr/local/mysql/data/log.01:
Permission denied 
050919  9:46:56 [ERROR] bdb:  PANIC: Permission denied 
050919  9:46:56 [ERROR] bdb:  PANIC: DB_RUNRECOVERY: Fatal error, run
database recovery 
050919  9:46:56 [ERROR] bdb:  fatal region error detected; run recovery 
050919  9:46:56 [ERROR] bdb:  /usr/local/mysql/data: Permission denied 
050919  9:46:56  InnoDB: Started; log sequence number 0 43634 
050919  9:46:56 [ERROR] Can't init databases 
050919  9:46:56 [ERROR] Aborting 

050919  9:46:56  InnoDB: Starting shutdown... 
050919  9:46:58  InnoDB: Shutdown completed; log sequence number 0 43634 
050919  9:46:58 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 

050919 09:46:58  mysqld ended 

050919 09:56:10  mysqld started 
050919  9:56:10 [ERROR] bdb:  /usr/local/mysql/data: Permission denied 
050919  9:56:10 [ERROR] bdb:  /usr/local/mysql/data/log.01:
Permission denied 
050919  9:56:10 [ERROR] bdb:  PANIC: Permission denied 
050919  9:56:10 [ERROR] bdb:  PANIC: DB_RUNRECOVERY: Fatal error, run
database recovery 
050919  9:56:10 [ERROR] bdb:  fatal region error detected; run recovery 
050919  9:56:10 [ERROR] bdb:  /usr/local/mysql/data: Permission denied 
050919  9:56:10  InnoDB: Started; log sequence number 0 43634 
050919  9:56:10 [ERROR] Can't init databases 
050919  9:56:10 [ERROR] Aborting 

050919  9:56:10  InnoDB: Starting shutdown... 
050919  9:56:12  InnoDB: Shutdown completed; log sequence number 0 43634 
050919  9:56:12 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 

050919 09:56:12  mysqld ended 

050919 10:05:33  mysqld started 
050919 10:05:34 [ERROR] bdb:  /usr/local/mysql/data: Permission denied 
050919 10:05:34 [ERROR] bdb:  /usr/local/mysql/data/log.01:
Permission denied 
050919 10:05:34 [ERROR] bdb:  PANIC: Permission denied 
050919 10:05:34 [ERROR] bdb:  PANIC: DB_RUNRECOVERY: Fatal error, run
database recovery 
050919 10:05:34 [ERROR] bdb:  fatal region error detected; run recovery 
050919 10:05:34 [ERROR] bdb:  /usr/local/mysql/data: Permission denied 
050919 10:05:34  InnoDB: Started; log sequence number 0 43634 
050919 10:05:34 [ERROR] Can't init databases 
050919 10:05:34 [ERROR] Aborting 

050919 

Prepare v. Do functions: how to benefit from prepare

[Siegfried Heintze]

I'm using activestate perl on WinXP but I'm sure this applies to all
languages on all platforms. I cannot remember the JDBC terms presently.

There have been lots of discussion on the performance virtues of using the
prepare function instead of the do function.

Is it necessary to save the statement handle of the prepared statement to
benefit from using prepare or does the prepare statement cache earlier calls
to prepare and use those when available?

I'm finding it difficult to implement the logic to determine if I need to
call prepare and if not, where is that previous statement handle?

Assuming the answer is yes: are we better off using do instead of prepare if
we cannot save the statement handles?

Thanks,
Siegfried


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql_free_result() WITHOUT mysql_fetch_row()

[Miguel Cardenas]

Hello

 Have you try :
 select count(*) from mytable where id=x and list=something
 if count ==0, it's like fetchrow==NULL from your solution, but without
 all the fuss about use_result();

No, but looks fine, I didn't know this usage of count(*) combined with 
conditions, but now will use it :)

 Second solution :
 ALTER TABLE mytable ADD UNIQUE(id,list)
 which make a unique index on two field. inserting a duplicate value
 would give you back an error and let the table untouched.

Is it possible to do this during the creation of the table? my database is not 
large, it has just some records (by now is being tested only), so I can 
backup all data and build the table again.

Is there a MySQL manual I can download, apart of the one manual-split.tar.gz 
found in mysql.com? so I would be able to find a more generic or advanced 
usage?

Thanks for your patience :-)


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Result row count *without* buffering all results?

[Ken Gieselman]

Harrison Fisk wrote:

Hi Ken,

On Sep 18, 2005, at 3:53 PM, Ken Gieselman wrote:


Scott Gifford wrote:


Ken Gieselman [EMAIL PROTECTED] writes:
[...]


So, here's my question: How can I find out how many rows are being
returned by a query, without using mysql_stmt_store_result() to
buffer all the rows back to the client?


One straightforward way is to replace the SELECT clause with just
SELECT COUNT(*) and execute that query first.
-ScottG.



Yeah, that's my fall-back option -- though the thought of executing 
the query twice is a bit daunting.  Some of the tables run into 
billions of rows per year (the merge tables anyhow, the data tables 
are broken down by month to keep them from becoming totally 
unmanageable), and a multi-year search can take a while to grind out.


Seems to me that by the time the first query execution is done, the 
server should *know* exactly how many rows are in the result set -- 
just need to find the magic trick that allows the client to query that 
number, rather than counting for itself as it buffers them.



The problem is that your assumption isn't always correct.  The server 
doesn't always know how many rows are in a result set before it starts 
returning rows back to the client.  Imagine if you did a simple SELECT * 
FROM tbl WHERE unindexed_col = 5; statement.  The server certainly isn't 
going to read in and count the number of rows before beginning to send 
the rows back to the client, for the same reason that you don't want to 
do a store_result, it will take up too much resources.  The only way for 
the server to know 100%, is to count the rows as they are being returned 
to the client  (It could in theory do it for some statements, such as 
where it has to do a filesort, however an API that only sometimes worked 
based on the execution plan wouldn't be very useful) .


Regards,

Harrison



That makes a fair amount of sense :)  I guess I'd just envisioned it as having a 
list of pointers to matches or something similar internally.  OK, given the 
limitations, is there an effective method to *estimate* what's likely to come 
back?  I've played a bit with pulling the numbers out of an EXPLAIN SELECT... 
but it seems that they tend to come in low, if the query is using indexes, or 
high (all rows) if not.


Appreciate the input guys! Thanks a bunch.

ken
--
===
  Diplomacy is the weapon of the Civilized Warrior
- Hun, A.T.

Ken Gieselman   [EMAIL PROTECTED]
Endlessknot Communications   http://www.endlessknot.com
===

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql_free_result() WITHOUT mysql_fetch_row()

[SGreen]

Miguel Cardenas [EMAIL PROTECTED] wrote on 09/19/2005 12:13:47 PM:

 Hello
 
  Have you try :
  select count(*) from mytable where id=x and list=something
  if count ==0, it's like fetchrow==NULL from your solution, but without
  all the fuss about use_result();
 
 No, but looks fine, I didn't know this usage of count(*) combined with 
 conditions, but now will use it :)
 
  Second solution :
  ALTER TABLE mytable ADD UNIQUE(id,list)
  which make a unique index on two field. inserting a duplicate value
  would give you back an error and let the table untouched.
 
 Is it possible to do this during the creation of the table? my 
 database is not 
 large, it has just some records (by now is being tested only), so I can 
 backup all data and build the table again.
 
 Is there a MySQL manual I can download, apart of the one 
manual-split.tar.gz 
 found in mysql.com? so I would be able to find a more generic or 
advanced 
 usage?
 
 Thanks for your patience :-)
 
 
There are many versions of the manual. Check here for details:

http://dev.mysql.com/doc/

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

RE: possible MySQL bug - insert into 'double' column problem with mysql 4.1

[Andrew Braithwaite]

Thanks; you're absolutely right - doh!  It's just amazing that this ever
worked in MySQL 4.0 and below...

Andrew

-Original Message-
From: Roger Baklund [mailto:[EMAIL PROTECTED] 
Sent: Mon, 19 Sep 2005 12:27
To: mysql@lists.mysql.com
Cc: Andrew Braithwaite
Subject: Re: possible MySQL bug - insert into 'double' column problem
with mysql 4.1

Andrew Braithwaite wrote:
 Hi All,
 
 I have a strange error when trying to insert into a table with 2
 'double' fields.  It inserts into the 1st field OK but fills the 2nd
one
 with nines.  See below for a complete recreate.
[...]
 | lon   | double(7,6)  | YES  | MUL | NULL|
|
 | lat   | double(7,6)  | YES  | | NULL|
|

 From the manual:

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

M is the total number of decimal digits and D is the number of digits 
following the decimal point.

URL: http://dev.mysql.com/doc/mysql/en/numeric-type-overview.html 

 mysql INSERT INTO table1VALUES
 (8002,'2a',-0.361080,39.468270,'2005-08-31','ES052',NULL,NULL);
 Query OK, 1 row affected, 1 warning (0.01 sec)
 
 mysql select * from table1 where id=8002\G
 *** 1. row ***
id: 8002
GeoQuality: 2a
   lon: -0.361080
   lat: 9.99

You have created your columns with a max width of 7, out of which 6 are 
decimals, but you are trying to insert a number with 8 digits.

--
Roger




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: LVM-snapshot + mysqldump -- is this a reasonable backup

[James G. Sack (jim)]

On Mon, 2005-09-19 at 12:15 -0400, George Herson wrote:
 Dear Jim,
 
 Re: your post at http://lists.mysql.com/mysql/189058, why bother 
 creating the mysqldump if you already have the snapshot?  Why not just 
 backup the snapshot?
 
 (I'd have hit reply online but didn't see a Reply button and didn't want 
 to bother with joining the forum yet.)

George, I think it's ok to post to the list even if one is not not a
subscribed member -- perhaps someone will correct me if this is wrong.

Unless you have a personal message, or have been specifically asked to
communicate something offlist, the benefit of posting to the list is
that everybody gets to learn (and ask).

Now, to respond to your question..

The output of mysqldump *is* the backup. If the db goes away, it can be
restored with (something like) mysql dumpfile.2005-09-01.

The dump operation is run periodically, and some number of back versions
can be kept around (or offloaded) for archival value.

Anyway, that expresses my intention. It is admittedly and intentionally
a simple-minded backup strategy -- I am hopeful that more experienced
list contributors will critique the idea or suggest improvements.

..jim


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: LVM-snapshot + mysqldump -- is this a reasonable backup

[George Herson]

James G. Sack (jim) wrote:


On Mon, 2005-09-19 at 12:15 -0400, George Herson wrote:
 


Dear Jim,

Re: your post at http://lists.mysql.com/mysql/189058, why bother 
creating the mysqldump if you already have the snapshot?  Why not just 
backup the snapshot?
   


[...]

The output of mysqldump *is* the backup. If the db goes away, it can be
restored with (something like) mysql dumpfile.2005-09-01.

The dump operation is run periodically, and some number of back versions
can be kept around (or offloaded) for archival value.
 

Yes, but can't you also save your snapshot instead, then, when/if you 
want, restore it,


4. mount the snapshot

5. load a second database server daemon accessing the db within the
snapshot (with a suitable alternate my.cnf file)

6. perform mysqldump operation on the snapshot-db ??

thx,
George


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: LVM-snapshot + mysqldump -- is this a reasonable backup

[James G. Sack (jim)]

On Mon, 2005-09-19 at 13:14 -0400, George Herson wrote:
 James G. Sack (jim) wrote:
 
 On Mon, 2005-09-19 at 12:15 -0400, George Herson wrote:
   
 
 Dear Jim,
 
 Re: your post at http://lists.mysql.com/mysql/189058, why bother 
 creating the mysqldump if you already have the snapshot?  Why not just 
 backup the snapshot?
 
 
 [...]
 
 The output of mysqldump *is* the backup. If the db goes away, it can be
 restored with (something like) mysql dumpfile.2005-09-01.
 
 The dump operation is run periodically, and some number of back versions
 can be kept around (or offloaded) for archival value.
   
 
 Yes, but can't you also save your snapshot instead, then, when/if you 
 want, restore it,
 
 4. mount the snapshot
 
  5. load a second database server daemon accessing the db within the
 snapshot (with a suitable alternate my.cnf file)
 
  6. perform mysqldump operation on the snapshot-db ??
 
..

George, LVM snapshots are generally intended to be short-lived -- they
give you a virtual copy of the underlying (origin) filesystem (as it
existed at one point in time), which occupies only a fraction of the
original storage space because it only deals with changes that occur to
the origin fs over the lifetime of the snapshot. 

The snapshot point-in-time concept allows running some slow process
(typically, maybe, writing to a tape device) without needing to be
concerned that the data is changing while you are copying it. The
database does need to be made consistent (locked) at the point of taking
the snapshot, but creating the snapshot only takes seconds. So the
database may be write-unlocked after only a minor disruption, instead of
during the entire backup operation.

If the snapshot were kept around indefinitely, it would eventually need
perhaps as much storage as the origin. So typically one deletes a
snapshot after using it for a staging operation such as above. A further
consideration is that the snapshot adds overhead to every disk
operation, so that's another incentive to minimize the snapshot
lifetime.

..jim


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Startup Error

[Johnson, Richard (NY Int)]

Still getting the following errors after running the chown's below...

Here is what the ls -l result window shows...

mysql]# ls -l /usr/local/mysql/data
total 20536
-rw-rw1 mysqlmysql10485760 Sep 19
10:05 ibdata1
-rw-rw1 mysqlmysql 5242880 Sep 19
10:05 ib_logfile0
-rw-rw1 mysqlmysql 5242880 Sep 19
09:33 ib_logfile1
drwxr-x---2 mysqlmysql4096 Sep 19
09:30 mysql

The error log shows..

050919 14:44:36  mysqld started
050919 14:44:36 [ERROR] bdb:  /usr/local/mysql/data:
Permission denied
050919 14:44:36 [ERROR] bdb: 
/usr/local/mysql/data/log.01: Permission denied
050919 14:44:36 [ERROR] bdb:  PANIC: Permission denied
050919 14:44:36 [ERROR] bdb:  PANIC: DB_RUNRECOVERY:
Fatal error, run database recovery
050919 14:44:36 [ERROR] bdb:  fatal region error detected; run recovery
050919 14:44:36 [ERROR] bdb:  /usr/local/mysql/data:
Permission denied
050919 14:44:36  InnoDB: Started; log sequence number 0 43634
050919 14:44:36 [ERROR] Can't init databases
050919 14:44:36 [ERROR] Aborting

050919 14:44:36  InnoDB: Starting shutdown...
050919 14:44:38  InnoDB: Shutdown completed; log sequence number 0 43634
050919 14:44:38 [Note] /usr/local/mysql/bin/mysqld:
Shutdown complete

050919 14:44:38  mysqld ended

Richard Johnson
212-589-6503
[EMAIL PROTECTED]

-Original Message-
From: J.R. Bullington [mailto:[EMAIL PROTECTED] 
Sent: Monday, September 19, 2005 11:49 AM
To: mysql@lists.mysql.com
Subject: RE: Startup Error

Well, as you can see in the first few lines, you have a Permission Denied
problem.
 
This means that your /data/ folder doesn't have the write permissions to it
via the mysql user.
 
Try running the permission scripts again, but this time use the full shell
names.
 
shell chown -R root /usr/local/mysql/.
shell chown -R mysql /usr/local/mysql/data/.
shell chgrp -R mysql /usr/local/mysql/.
 
Then check to make sure that user has write permission to the folders:
 
shell ls -l /usr/local/mysql/data
 
It should say something similar to:
 
shell ls -l /usr/local/mysql/data
total 63868
drwx--2mysqlmysqlsizedatedirname
-rw-rw1mysqlmysqlsizedatefilename-bin.000
.
.
-rw-rw1mysqlmysqlsizedatehostname.err
 
 
 
J.R.
 

  _  

From: Johnson, Richard (NY Int) [mailto:[EMAIL PROTECTED] 
Sent: Monday, September 19, 2005 11:35 AM
To: 'J.R. Bullington'
Subject: RE: Startup Error



Here are the contents of the file 

050919 09:33:52  mysqld started 
050919  9:33:52 [ERROR] bdb:  /usr/local/mysql/data: Permission denied 
050919  9:33:52 [ERROR] bdb:  /usr/local/mysql/data/log.01:
Permission denied 
050919  9:33:52 [ERROR] bdb:  PANIC: Permission denied 
050919  9:33:52 [ERROR] bdb:  PANIC: DB_RUNRECOVERY: Fatal error, run
database recovery 
050919  9:33:52 [ERROR] bdb:  fatal region error detected; run recovery 
050919  9:33:52 [ERROR] bdb:  /usr/local/mysql/data: Permission denied 
InnoDB: The first specified data file ./ibdata1 did not exist: 
InnoDB: a new database to be created! 
050919  9:33:52  InnoDB: Setting file ./ibdata1 size to 10 MB 
InnoDB: Database physically writes the file full: wait... 
050919  9:33:53  InnoDB: Log file ./ib_logfile0 did not exist: new to be
created 
InnoDB: Setting log file ./ib_logfile0 size to 5 MB 
InnoDB: Database physically writes the file full: wait... 
050919  9:33:53  InnoDB: Log file ./ib_logfile1 did not exist: new to be
created 
InnoDB: Setting log file ./ib_logfile1 size to 5 MB 
InnoDB: Database physically writes the file full: wait... 
InnoDB: Doublewrite buffer not found: creating new 
InnoDB: Doublewrite buffer created 
InnoDB: Creating foreign key constraint system tables 
InnoDB: Foreign key constraint system tables created 
050919  9:33:53  InnoDB: Started; log sequence number 0 0 
050919  9:33:53 [ERROR] Can't init databases 
050919  9:33:53 [ERROR] Aborting 

050919  9:33:53  InnoDB: Starting shutdown... 
050919  9:33:55  InnoDB: Shutdown completed; log sequence number 0 43634 
050919  9:33:55 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 

050919 09:33:55  mysqld ended 

050919 09:46:55  mysqld started 
050919  9:46:55 [ERROR] bdb:  /usr/local/mysql/data: Permission denied 
050919  9:46:56 [ERROR] bdb:  /usr/local/mysql/data/log.01:
Permission denied 
050919  9:46:56 [ERROR] bdb:  PANIC: Permission denied 
050919  9:46:56 [ERROR] bdb:  PANIC: DB_RUNRECOVERY: Fatal error, run
database recovery 
050919  9:46:56 [ERROR] bdb:  fatal region error detected; run recovery 
050919  9:46:56 [ERROR] bdb:  /usr/local/mysql/data: Permission denied 
050919  9:46:56  InnoDB: Started; log sequence number 0 43634 
050919  9:46:56 [ERROR] Can't init databases 
050919  9:46:56 [ERROR] Aborting 

050919  9:46:56  InnoDB: Starting shutdown... 
050919  9:46:58  InnoDB: Shutdown completed; log sequence number 0 43634 
050919  9:46:58 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 

050919 

Re: Foreign keys across databases?

[Martijn Tonies]

Hello Jake,

 I would like to use separate databases to group certain related tables
 to keep things clean, but I would also like to use foreign keys to
 enforce referential integrity between tables in different databases. I
 don't think this is possible. Am I right? And if so, am I reduced to
 using carefully chosen table names to keep things organized? What have
 others done in this situation?

Ehm... According to me, all tables related to eachother or a certain
domain should go in 1 database. That is, each database should be
able to exist by itself.

So, in your case, dump everything into 1 database.

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle  MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 1064 error

[Rory McKinley]

Schalk Neethling wrote:
 Greetings
 
 What might be causing the 1064 error in the following query?
 
 SELECT mem_number, first_name, last_name, area_represented,
 joining_points + E-Model Challenge + SA Pro Model + Star Model Challenge
 + Eastern Cape Classic + SA Model Super Star + KZN Model GP + Mpumalanga
 GP + Glam Slam Model Challenge + Model Man Woman + SA Look of the Year +
 SA Face of the Year + KZN Classic + Eastern Cape GP + Western Cape
 Classic + Free State Classic + North West GP + Northern Province Classic
 + SA Model Open + Cover Search + Champion of Champions + Northern Cape
 Classic + Goldfields Model GP + Limpopo Classic + SA Model Portfolio +
 Top Model Challenge + Gauteng Model Classic + Year of the Model AS
 total_points
 FROM modelcup.ab_leader_board
 
 All of the rows does exist in the table and all row names are correct.
 TYIA!
 
Wow, a select statement like that is always going to cause you problems
- I know if I had to do it I would be sitting with typos from now until
Christmas. If I may suggest an alternative design, which will be a bot
more normalised and perhaps easier to work with ... making a suppostion
based on the above I think a table structure such as the following may
make lfe easier...


Table: models
___
|mem_number
|first_name
|last_name
|area_respresented
|
|

Table: events
___
|event_id
|event_name

Table: events_points
|event_id
|mem_number
|points

Would result in a query that looks like this:

SELECT a.mem_number, a.first_name, a.last_name, a.area_represented,
IFNULL(SUM(b.points), 0) AS total_points
FROM models LEFT JOIN events_points ON a.mem_number = b.mem_number
GROUP BY  a.mem_number, a.first_name, a.last_name, a.area_represented

Makes for a design that is easier to maintain (you could also treat your
joining points as another event). New events just get added as an entry
in the events table and are then referenced in the events_points table
rather than having to add a whole stack of columns ;).

Sorry, I know, answering an unasked question but I hope it helps

Rory

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

very large key_buffer on amd64?

[Chris Kantarjiev]

We're starting to use mysql (4.0.25) on an amd64 machine (running
NetBSD-3). One of the reasons for doing this is to use much more
RAM - we tend to thrash the key_buffer on i386 because one of our
indexes is 10GB in size (the table is 15GB).

It appears that mysqld won't start if the setting for key_buffer
is more than 2GB. 

053419 11:34:15  Starting mysqld daemon with databases from /var/mysql
053419 11:34:15  mysqld started
mysqld: Couldn't allocate stack for idle thread!: Cannot allocate memory
053419 11:34:15  STOPPING server from pid file /var/mysql/vern.landsonar.com.pid
053419 11:34:15  mysqld ended

I'd like it to be 10GB (we have 16GB of RAM).  I'm guessing that
the variable(s) that deal with the key_buffer are 32-bit ints ...
is there a straightforward way to find all the relevant places and
make them 64-bit ints?

Or is the problem that every thread is ending up with its
own key_buffer, so there isn't enough memory to do this?

Or something else?

Thanks.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Database replication between oracle-mysql (was RE: database link)

[Burke, Dan]

This is really cool!  Can something be done with this to use a
materialized view log on a table in Oracle and sync with a table in
mysql?


-Original Message-
From: Mark Leith [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 16, 2005 9:54 AM
To: mysql@lists.mysql.com
Subject: RE: database link

You may want to think about doing this the opposite way around also, and
look at pushing the data from Oracle in to MySQL. Oracle has something
called heterogeneous services, which allows you to define ODBC
datasources
as valid entries in the tnsnames.ora file. Then you could simply create
a
job in Oracle that executes a procedure to do the entire process
(truncate /
load), no external scripting necessary..

Here's a quick example of what to do:

First set up an ODBC data source for your MySQL database, using MyODBC.

Create a file in ORACLE_HOME/hs/admin called initMySQL.ora. In this file
put
the following options:

HS_FDS_CONNECT_INFO = MySQL5 #ODBC DSN /* Replace MySQL5 with your DSN
*/
HS_FDS_TRACE_LEVEL = OFF

Alter your listener.ora file (ORACLE_HOME/network/admin) to add the
following:

(SID_DESC =
  (PROGRAM  = hsodbc)
  (ORACLE_HOME  = oracle/product/92) /* Your ORACLE_HOME */
  (SID_NAME = MySQL5) /* Your DSN */
)

Add the following to your tnsnames.ora file:

MYSQL5 =
  (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=MYSQL5))
(HS=OK)
  )

Reload your Oracle listener (lsnrctl reload), and then connect to the
Oracle
database. To set the database link up:

CREATE DATABASE LINK mysql5 CONNECT TO user identified by password
using
'mysql5';

User and password should be a valid user within MySQL, that can connect
from
the Oracle host. You should be set to go from there. Here's a quick
example
of this working, to a MySQL 5 database using the new sakila sample
database that Mike Hillyer recently released
(http://www.openwin.org/mike/download/sakila.zip):

SQL select count(*) from [EMAIL PROTECTED];

  COUNT(*)
--
  1000

SQL desc [EMAIL PROTECTED];
 Name  Null?Type
 - 

 film_idNUMBER(10)
 category_id   NOT NULL NUMBER(10)
 title NOT NULL VARCHAR2(27)
 descriptionLONG
 rental_duration   NOT NULL NUMBER(3)
 length NUMBER(10)
 rating CHAR(5)

SQL insert into [EMAIL PROTECTED] values (100,1,'test','test',1,1,'PG');

1 row created.

---change prompts---

mysql use sakila
Database changed
mysql select max(film_id) from film;
+--+
| max(film_id) |
+--+
|  100 |
+--+
1 row in set (0.01 sec)

HTH

Mark Leith

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.0/103 - Release Date:
15/09/2005
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]



___
This message has been scanned for viruses 
by TechTeam's email gateway.



_
This e-mail transmission is strictly confidential 
and intended solely for the person or organization 
to whom it is addressed. It may contain privileged 
and confidential information and if you are not the 
intended recipient, you must not copy, distribute or 
take any action in reliance on it. If you have 
received this e-mail in error, please notify the 
sender as soon as possible and delete the e-mail 
message and any attachment(s).

This message has been scanned for viruses 
by TechTeam's email gateway.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: possible MySQL bug - insert into 'double' column problem with mysql 4.1

[Andrew Braithwaite]

Hmm - and further to this.  Strange differences of behaiour between the
last two production versions of MySQL

sh-2.05b# ln -s mysql-standard-4.0.24-apple-darwin7.7.0-powerpc mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.24-standard-log

mysql create table wibble3 (
- test1 double(3,6),
- test2 double(7,6),
- test3 double(9,6));
Query OK, 0 rows affected (0.11 sec)

mysql desc wibble3;
+---+-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-+---+
| test1 | double(7,6) | YES  | | NULL|   |
| test2 | double(7,6) | YES  | | NULL|   |
| test3 | double(9,6) | YES  | | NULL|   |
+---+-+--+-+-+---+
3 rows in set (0.17 sec)

mysql insert into wibble3 values(50.123456,50.123456,50.123456);
Query OK, 1 row affected (0.08 sec)

mysql select * from wibble3;
+---+---+---+
| test1 | test2 | test3 |
+---+---+---+
| 50.123456 | 50.123456 | 50.123456 |
+---+---+---+
1 row in set (0.08 sec)

sh-2.05b# ln -s mysql-standard-4.1.10a-apple-darwin7.7.0-powerpc mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.10a-standard-log

mysql insert into wibble3 values(50.123456,50.123456,50.123456);
Query OK, 1 row affected, 2 warnings (0.11 sec)

mysql select * from wibble3;
+---+---+---+
| test1 | test2 | test3 |
+---+---+---+
| 50.123456 | 50.123456 | 50.123456 |
|  9.99 |  9.99 | 50.123456 |
+---+---+---+
2 rows in set (0.06 sec)

Looks like while MySQL 4.1 was not changing what was stored in the data but
changing what is inserted into new records to match the proper data tye
definitions.


On 19/9/05 17:49, Andrew Braithwaite [EMAIL PROTECTED] wrote:

 Thanks; you're absolutely right - doh!  It's just amazing that this ever
 worked in MySQL 4.0 and below...
 
 Andrew
 
 -Original Message-
 From: Roger Baklund [mailto:[EMAIL PROTECTED]
 Sent: Mon, 19 Sep 2005 12:27
 To: mysql@lists.mysql.com
 Cc: Andrew Braithwaite
 Subject: Re: possible MySQL bug - insert into 'double' column problem
 with mysql 4.1
 
 Andrew Braithwaite wrote:
 Hi All,
 
 I have a strange error when trying to insert into a table with 2
 'double' fields.  It inserts into the 1st field OK but fills the 2nd
 one
 with nines.  See below for a complete recreate.
 [...]
 | lon   | double(7,6)  | YES  | MUL | NULL|
 |
 | lat   | double(7,6)  | YES  | | NULL|
 |
 
  From the manual:
 
 DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
 
 M is the total number of decimal digits and D is the number of digits
 following the decimal point.
 
 URL: http://dev.mysql.com/doc/mysql/en/numeric-type-overview.html 
 
 mysql INSERT INTO table1VALUES
 (8002,'2a',-0.361080,39.468270,'2005-08-31','ES052',NULL,NULL);
 Query OK, 1 row affected, 1 warning (0.01 sec)
 
 mysql select * from table1 where id=8002\G
 *** 1. row ***
id: 8002
GeoQuality: 2a
   lon: -0.361080
   lat: 9.99
 
 You have created your columns with a max width of 7, out of which 6 are
 decimals, but you are trying to insert a number with 8 digits.
 
 --
 Roger
 
 
 
 
 --
 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: very large key_buffer on amd64?

[Jigal van Hemert]

- Original Message - 
From: Chris Kantarjiev [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Monday, September 19, 2005 9:06 PM
Subject: very large key_buffer on amd64?


 We're starting to use mysql (4.0.25) on an amd64 machine (running
 NetBSD-3). One of the reasons for doing this is to use much more
 RAM - we tend to thrash the key_buffer on i386 because one of our
 indexes is 10GB in size (the table is 15GB).

 It appears that mysqld won't start if the setting for key_buffer
 is more than 2GB.

Maybe you've also hit the quirks of memory management and malloc, just as
we've posted a while ago in http://lists.mysql.com/mysql/186930 ?

I assume that you've installed an appropriate 64-bit version of MySQL...

Regards, Jigal.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: very large key_buffer on amd64?

[Chris Kantarjiev]

 
 Maybe you've also hit the quirks of memory management and malloc, just as
 we've posted a while ago in http://lists.mysql.com/mysql/186930 ?

An interesting thread, but I'm on NetBSD, not Linux. But it's some place
to start looking around, I guess.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

query goes to sleep mode!

[Mohammad shojatalab]

Hi All,

I have seen a couple of posting in this regard but didn't get the answer 
by reading those; so I appreciate your answers.


Everything was fine about a week ago, untill we realized that our 
website has got ver slow.
after spending some time I realized that it has nothing to do with 
perl-cgi side (which I originally thought is the reason)
after any click, the query goes to a 90 sec - 110 sec of sleep mode and 
then gets executed and I dont even see the execution of it even when I

issue this command:

mysqladmin -i1 processlist

I dont know why this going to sleep mode is happening.
I even shut the server down but it didn't help. the web server is a 
simple perl-cgi and not the mod_perl with persistent connection.


any thoughts?

Thanks
Mohammad


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql_free_result() 2 different connections

[Miguel Cardenas]

Hello

I was re-reading last mails, analized it carefull again and...

  while ( my1.fetch ) {
     my2.query + my2.store
     if ( my2.fetch ) {
        report = my1 + my2 results
     } else {
        report = my1
     }
     my2.FreeResult --- free for every my2.query inside loop
  }
  my1.FreeResult --- free for unique my1.query

 One My2.store == One My2.free.
 you're doing it more thant once, it can't work.
 from the fine manual :
 http://dev.mysql.com/doc/mysql/en/mysql-store-result.html
 You must call mysql_free_result() once you are done with the result set. 
 but in you're code you're still using the result set ! --- ***

I have 2 different result sets, since are two different connections to the 
server, simultaneous but independent (two handles, two connections, two 
different queries)

Today was probing one of my programs with just one single connection and 
mysql_free_result() still crashes even with just one set of results, no 
matter how I use it, it always crashes :-(

Any idea?


Regards

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]