Auto SQL
Dear MySQL Experts, I am using MySQL 4.1, I have a database with more than 100 tables inside, is there any 3rd party tools that can help me to create a SQL statement of CREATE TABLE blah blah blah for each table in my DB . please advise Regards Winanjaya -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UDF Request AGGLOM()
On Mon, 11 Apr 2005, Arjen Lentz wrote: Hi Dan, On Sat, 2005-04-09 at 02:59, Dan Bolser wrote: Who can I prod about setting up a UDF repo at MySQL. I think 'they' should do this ;) Yep it's an existing idea, a very good one, and it's on the todo. Putting such an infrastructure into place will take some time though. I can imagine it isn't trivial to set up. Would a special forum for this purpose perhaps be a good intermediate solution? Routines could be posted there, it's searchable... Forum = list? I think it would be a good start, if only to discuss things like this :) All the best, Dan. Regards, Arjen. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Charset and LOAD DATA INFILE problem
Hello, I'm trying to convert tables from Paradox DBs to MySQL and I have a big problem with setting correct character set. I created small application which generates csv files which are in cp1250 (I can't change it, it's charset ofmy Paradox DBs). My databases and tables in MySQL are in latin2. I tried in MySQL client command SET NAMES cp1250 and then LOAD DATA INFILE but that didn't help. Some chars were screwed up. If I create SQL script from part of csv file and load it by command SOURCE 'script.sql' then everything is OK. So, does anybody know the way how to import files from CSV directly by LOAD DATA INFILE without difficult manual conversions to sql scripts? Thanks in advance for any help or ideas. Dusan Pavlica
Re: Auto SQL
From: Winanjaya I am using MySQL 4.1, I have a database with more than 100 tables inside, is there any 3rd party tools that can help me to create a SQL statement of CREATE TABLE blah blah blah for each table in my DB . please advise I think many MySQL management tools can help you do this. phpMyAdmin (http://www.phpmyadmin.net/), our 'beloved' :-) web based tool is one of them. If you select a database you'll see an overview of all the tables in the database with some statistics. Select the tab 'Export' and you can set all the details of the export you want to make. E.g. Select all tables, SQL export, Structure, no data, download resulting statements as file, etc. and you'll end up with a nice list of CREATE TABLE statements for all the selected tables. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Auto SQL
What tool you're talking now? .. PHPMyAdmin? .. I am using MySQL Administrator .. and I didnot find such feature .. any suggestion? thanks a lot in advance Regards Winanjaya -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: Monday, April 11, 2005 4:36 PM To: mysql@lists.mysql.com Subject: Re: Auto SQL From: Winanjaya I am using MySQL 4.1, I have a database with more than 100 tables inside, is there any 3rd party tools that can help me to create a SQL statement of CREATE TABLE blah blah blah for each table in my DB . please advise I think many MySQL management tools can help you do this. phpMyAdmin (http://www.phpmyadmin.net/), our 'beloved' :-) web based tool is one of them. If you select a database you'll see an overview of all the tables in the database with some statistics. Select the tab 'Export' and you can set all the details of the export you want to make. E.g. Select all tables, SQL export, Structure, no data, download resulting statements as file, etc. and you'll end up with a nice list of CREATE TABLE statements for all the selected tables. Regards, Jigal. -- 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]
Display 1st row of every group?
I read with great interest this http://www.artfulsoftware.com/queries.php#4 Display 1st row of every group SELECT id FROM tbl GROUP BY id HAVING count(*) = 1; I want to use this syntax with an 'order by' like this... SELECT * FROM tbl GROUP BY id HAVING count(*) = 1 ORDER BY bleah; Will this syntax return the row within the id group with the smallest value of the bleah column? (is it guaranteed to do so?) Cheers, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Display 1st row of every group?
I guess that entry is either wrong or misleading. I can get what I want like this SELECT * FROM tbl INNER JOIN ( SELECT id, min(bleah) as bleah FROM tbl GROUP BY id ) USING (id,bleah); Which will work so long as bleah has a unique minimum value per id group. On Mon, 11 Apr 2005, Dan Bolser wrote: I read with great interest this http://www.artfulsoftware.com/queries.php#4 Display 1st row of every group SELECT id FROM tbl GROUP BY id HAVING count(*) = 1; I want to use this syntax with an 'order by' like this... SELECT * FROM tbl GROUP BY id HAVING count(*) = 1 ORDER BY bleah; Will this syntax return the row within the id group with the smallest value of the bleah column? (is it guaranteed to do so?) Cheers, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Auto SQL
Hi, I am using MySQL 4.1, I have a database with more than 100 tables inside, is there any 3rd party tools that can help me to create a SQL statement of CREATE TABLE blah blah blah for each table in my DB . please advise If you're running Windows, you might want to check out our tool Database Workbench : www.upscene.com With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
importing, exporting data in IIF format (for example quickbooks)
Dear friends, one of our customer is asking to integrate a mysql application with an application called Quickbooks (accounting package, www.quickbooks.co.uk). Apparrently the application can only import export files in the format IIF. Has anybody any expereince in importing, exporting to this format with Mysql? Any help would be much appreciated. Best regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump, --single-transaction and --flush-logs
If I use the options --single-transaction and --flush-logs with mysqldump and InnoDB tables, does the new log file start at the same time as the transaction? If not, is it known for sure if the log file is switched before the transaction starts or if it is switched after the transaction starts? Is there some way to determine the binary log position as of the single transaction for the dump? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Auto SQL
Hi Winanjaya
Am Montag, 11. April 2005 09.50 schrieb Winanjaya:
I am using MySQL 4.1, I have a database with more than 100 tables inside,
is there any 3rd party tools that can help me to create a SQL statement of
CREATE TABLE blah blah blah for each table in my DB . please advise
Here's a quick and dirty 3rd party tool doing what you want.
- minimal error checking
- pay attention to the fact that the password is in the script
- not the best/elegant/fastest coding
After configuring db access and making the script executable, you call it e.g.
like
$ script file_with_create_table_statements
greetings joe
=== begin script ===
#!/usr/bin/perl
# This quick'n'dirty (!!) script outputs the CREATE TABLE
# statements of all tables in a mysql database
use strict; use warnings;
use DBI;
# ADJUST $db to $password to your needs
#
my $dbh=DBI-connect(
DBI:mysql:$db:$host:$port,
$username, $password,
{RaiseError=1}
)
or die $0: $DBI::errstr; }
# get the list of tables into arrayref $tables
#
my $out=$dbh-prepare(show tables) or die;
$out-execute or die;
my $tables=$out-fetchall_arrayref;
# output CREATE TABLE statement for every table
#
foreach my $t (@$tables) {
my $out2=$dbh-prepare(show create table @{[ $t-[0] ]}) or die;
$out2-execute or die;
print $out2-fetchrow_array, ;\n;
}
=== end script ===
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Auto SQL
Winanjaya wrote: Dear MySQL Experts, I am using MySQL 4.1, I have a database with more than 100 tables inside, is there any 3rd party tools that can help me to create a SQL statement of CREATE TABLE blah blah blah for each table in my DB . please advise Regards Winanjaya mysqldump -d --add-drop-table=0 -n -u USERNAME -p DATABASE Replace USERNAME DATABASE with your actual values. For more options see: mysqldump --help HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
results in random order.
I am trying to get this query to randomly pick a record from a table (trans) but it's not working: SELECT ID FROM trans ORDER BY RAND() LIMIT 1; Can anyone let me know what I'm doing wrong and/or how to fix it. Thanks, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where is ndb_mgm and ndb_mgmd in the RPM max package on LINUX
Hi David, all! Am Sa, den 09.04.2005 schrieb Marois, David um 23:22: We installed these rpm packages on linux 2.4.30 with redhat es 3.0 MySQL-server-4.1.10a-0 MySQL-Max-4.1.10a-0 perl-DBD-MySQL-2.1021-3 MySQL-shared-compat-4.1.10a-0 MySQL-client-4.1.10a-0 After that, I did not find the ndb_mgm and ndb_mgmd for the configuration of the management server who suppose to be in the MySQL-Max-4.1.10a-0 (for the cluster)? For NDB, there are separate RPMs at the bottom of the page. These should provide what you are looking for. You need them in addition to max. [[...]] Can someone help me ? I hope the above does help. Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UDF request?
Dan Bolser [EMAIL PROTECTED] wrote on 04/08/2005 06:45:42 PM: On Fri, 8 Apr 2005 [EMAIL PROTECTED] wrote: Dan Bolser [EMAIL PROTECTED] wrote on 04/08/2005 12:41:35 PM: On Thu, 7 Apr 2005, Sean Nolan wrote: I think you'll find you can do what you want with a cross join. A cross join will join every row from the first table with every row from the second table. It will not randomly do this, so you'd have to be creative in randomly selecting rows in the WHERE clause. Here's how you could do this with your data, pardon the highly original and very scientific randomization, but it is just an example :-) Perhaps I don't understand your randomization (because I really don't understand it ;), but I don't think a CROSS join does the trick, because I want to randomly pick the same rows from either side of the join that I would have gotten with an INNER JOIN using the 1:1 primary key between the two tables. This is analagous to sampling without replacement. If I do a cross join and then just restrict the number of rows returned (hey! I just worked out what you are doing below) I don't necessarily get the same 'marginals' (or to randomly pick the same rows from either side of the join) . This is analagous to sampling with replacement. Do you see what I mean? SELECT Dept, Gender, AVG(Spending) FROM table_one a CROSS JOIN table_two b WHERE (a.Person * b.Person) % 4 = 3 GROUP BY Dept, Gender; Sean - Original Message -- Hi, I have a request for a UDF that I would find really useful. I am duplicating this question on the community list as well as the MySQL list, because I am not sure where best to make this kind of request (see the previous post http://lists.mysql.com/community/97). I think the following syntax would be really cool... SELECT a.*, b.* FROM table_one a RANDOM JOIN -- -- :) table_two b USING (ID) ; Lets say table_one and table_two have a primary key called ID in common (although of course that isn't necessary). The idea is that the RANDOM JOIN would first calculate the INNER JOIN, then scramble the 'links' (or rows) between the two tables. The result would be the same number of rows overall, the same number of distinct a.ID's and b.ID's, but those a.ID's and b.ID's would be randomly associated with the marginals given by the correct inner join. Hopefully that makes sense. I think this function would be really useful for statistical analysis of scientific data within MySQL (using randomized versions of the associations within the tables). Not sure if the above syntax is optimal, because I would like to be able to do things like this... table_one Dept Person Gender A 1 M A 2 F A 3 M B 4 F B 5 M B 6 F table_two DeptPerson Spending A 1 10 A 2 20 A 3 30 B 4 40 B 5 50 B 6 60 SELECT Dept, Gender, AVG(Spending) FROM table_one a INNER JOIN table_two b USING (Dept,Person) GROUP BY Dept, Gender ; With the above query (which I hope is sensible) I would like to keep the departments fixed, and randomize the genders of the people in the departments (keeping the number of each sex in each department the same). So we could INNER JOIN using Dept and then RANDOM JOIN (as described above) using Person - all in one 'JOIN'. All else being the same this should randomize the Gender, but keep the marginals. I guess this is overly complex given the problem, and it actually raises more questions instantly (about statistical analysis), but the function is basic (I hope), and like all SQL it is the simplicity that allows you to build complex and robust statements. Does the random join make any sense? Is this UDF material? Any feedback is welcome, Dan. Thread UDF request? - Dan Bolser, April 1 2005 12:10am _ Don?t just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ I think I get it. It sounds to me like you want to take a random sampling (possibly to include the entire set) and analyze each sample. You can return the results of a query in random order if you use the RAND() function: http://dev.mysql.com/doc/mysql/en/mathematical-functions.html quote from site As of MySQL 3.23, you can retrieve rows in random order like this: mysql SELECT * FROM tbl_name ORDER BY RAND(); ORDER BY RAND() combined with LIMIT is useful for selecting a random sample of a set of rows: mysql SELECT * FROM table1, table2 WHERE a=b AND cd - ORDER BY RAND() LIMIT 1000; end quote So if
Re: Display 1st row of every group?
- Original Message - From: Dan Bolser [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, April 11, 2005 5:58 AM Subject: Display 1st row of every group? I read with great interest this http://www.artfulsoftware.com/queries.php#4 Display 1st row of every group SELECT id FROM tbl GROUP BY id HAVING count(*) = 1; Despite what the heading says for that query, it is NOT going to return the first row of every group. In fact, it is going to return only groups that contain one row. For example, given this data: ID -- 5 5 5 6 7 7 8 8 8 9 That query will return return this: id -- 6 9 In other words, it is returning only groups where there is a single ID with that value. Note also that it is returning ONLY an ID, not the ID plus the rest of the row. Their description of what the query does is just plain wrong. I want to use this syntax with an 'order by' like this... SELECT * FROM tbl GROUP BY id HAVING count(*) = 1 ORDER BY bleah; Will this syntax return the row within the id group with the smallest value of the bleah column? (is it guaranteed to do so?) No, absolutely not. Try it yourself with a small table of sample data and you will see for yourself. Your biggest problem is that you are trying to select all the columns of the table but only grouping on one column. The whole intent of GROUP BY is to summarize data, not give lots of details, but you are asking for details. There is probably a way to get what you want but it would take me a fair while to properly explain GROUP BY to you and I can't spare that much time. As a temporary expedient, may I suggest that you: - change the SELECT * clause so that it names only the column(s) in the GROUP BY, plus any summarizations (SUM(), COUNT(*), AVG(), etc.) you need - drop the HAVING altogether - use ORDER BY, if necessary, to make sure that your result set rows come out in the desired order Also, be sure to identify which version of MySQL you are using when you post an SQL question. In many, many cases the answer to an SQL question is It depends on which version of MySQL you are using. The later versions, 4.1 and 5.0, have many more options that could be useful for this problem than the older versions. Rhino Cheers, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.5 - Release Date: 07/04/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.6 - Release Date: 11/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign key design stratgies
- Original Message -
From: Steve Lefevre [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Monday, April 11, 2005 12:26 AM
Subject: foreign key design stratgies
Hey folks -
I'm having a little problem understanding ON DELETE foreign key
constraints. Here's my options from the manual:
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
When do I use each one? I can find anything in the online manual that
suggests how to construct a database using this.
I'm not surprised; the manual is pretty weak on design issues for the most
part.
I am rushed for time so I am going to be fairly brief and superficial in my
answer but it should be a reasonable start for you. By the way, I am gong to
talk about foreign keys more from the perspective of DB2 than MySQL because
I've used DB2 a lot longer; however, I'm reasonably sure that they work
exactly the same way in MySQL as DB2 (or Oracle, or Microsoft SQL Server, or
...) since they all tend to write towards the same SQL standards.
The delete rule for a foreign key always describes what should happen to the
child/dependent rows if the parent row is deleted. Picture a table named
DEPT containing one row for each department in a company with the primary
key being DEPTNO (department number). Picture another table named EMP
containing one row for each employee in the company with the primary key
being EMPNO (employee number). Each row in the EMP table also contains a
department number (WORKDEPT) that represents the department in which the
employee works. WORKDEPT is then a foreign key pointing to the DEPTNO column
of the DEPT table.
If your delete rule is RESTRICT, you are saying that you cannot delete
department D21 from the DEPT table if anyone in the EMP table has a WORKDEPT
of D21. If you try, the database will give you an error message about an
invalid delete of a foreign key and the delete will fail. Therefore, if you
want to delete department D21, you first have to get rid of all rows with a
WORKDEPT of D21 in the EMP table. You could do that by deleting those
employees first or by assigning them to different departments via UPDATE
statements.
If your delete rule is CASCADE, you are saying that you can delete
department D21 from the DEPT table regardless of how many people in the EMP
table have a WORKDEPT of D21; the delete of the DEPT row for D21 will also
cause all rows of employees with a WORKDEPT of D21 to be deleted as well.
Furthermore, if those employee rows are linked to rows in other tables via
foreign keys, those rows will be deleted as well. This is not limited in
depth so it could cause a *lot* of deletes. Therefore, if the D21 employees
were also present in tables that listed employee health claims and the
company baseball team, they would also be deleted from those tables and any
dependents of THOSE tables as well, ad infinitum.
If your delete rule is SET NULL, you are saying that you can delete
department D21 from the DEPT table regardless of how many people in the EMP
table have a WORKDEPT of D21; in this case, the employees in the EMP table
who have a WORKDEPT of D21 will have their WORKDEPT value changed to null
but the rows will still be in the table. This option does not cause ripples
in the other dependent tables.
The NO ACTION rule is very very similar to the RESTRICT rule in effect.
There is one small difference but I don't recall what it is for DB2. Even
the DB2 manual doesn't make the distinction very clear.
DB2 doesn't have a SET DEFAULT rule. If I had to guess, I'd speculate that
it deletes the desired row from the DEPT table and then changes the values
of the D21 rows in the EMP table to a default department number, which was
presumably defined in the original CREATE TABLE statement. But that is
strictly a guess.
I hope this helps,
Rhino
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.6 - Release Date: 11/04/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Display 1st row of every group?
On Mon, 11 Apr 2005, Rhino wrote: - Original Message - From: Dan Bolser [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, April 11, 2005 5:58 AM Subject: Display 1st row of every group? I read with great interest this http://www.artfulsoftware.com/queries.php#4 Display 1st row of every group SELECT id FROM tbl GROUP BY id HAVING count(*) = 1; Despite what the heading says for that query, it is NOT going to return the first row of every group. In fact, it is going to return only groups that contain one row. For example, given this data: ID -- 5 5 5 6 7 7 8 8 8 9 That query will return return this: id -- 6 9 In other words, it is returning only groups where there is a single ID with that value. Note also that it is returning ONLY an ID, not the ID plus the rest of the row. Their description of what the query does is just plain wrong. Delicious second opinion! Thanks for the reply - if you see my second post you see I fixed my problem. Cheers, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Display 1st row of every group?
I guess that entry is either wrong or misleading. Ordering by another column which isn't mutually dependent with the grouping column will have unpredictable results. Is that what you mean by the example being "wrong or misleading"? PB - Dan Bolser wrote: I guess that entry is either wrong or misleading. I can get what I want like this SELECT * FROM tbl INNER JOIN ( SELECT id, min(bleah) as bleah FROM tbl GROUP BY id ) USING (id,bleah); Which will work so long as bleah has a unique minimum value per id group. On Mon, 11 Apr 2005, Dan Bolser wrote: I read with great interest this http://www.artfulsoftware.com/queries.php#4 Display 1st row of every group SELECT id FROM tbl GROUP BY id HAVING count(*) = 1; I want to use this syntax with an 'order by' like this... SELECT * FROM tbl GROUP BY id HAVING count(*) = 1 ORDER BY bleah; Will this syntax return the row within the "id group" with the smallest value of the bleah column? (is it guaranteed to do so?) Cheers, Dan. No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.5 - Release Date: 4/7/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Charset and LOAD DATA INFILE problem - solved
Finally I found the solution. Just short note for everybody who has problem with LOAD DATA INFILE and correct charset of the file. Loaded file must have SAME charset as DATABASE which contains target table of LOAD DATA command. Target table charset doesn't matter. I didn't find any note about it in manual but I think it is useful to know it. Dusan - Original Message - From: Duan Pavlica [EMAIL PROTECTED] To: list mysql mysql@lists.mysql.com Sent: Monday, April 11, 2005 10:52 AM Subject: Charset and LOAD DATA INFILE problem Hello, I'm trying to convert tables from Paradox DBs to MySQL and I have a big problem with setting correct character set. I created small application which generates csv files which are in cp1250 (I can't change it, it's charset ofmy Paradox DBs). My databases and tables in MySQL are in latin2. I tried in MySQL client command SET NAMES cp1250 and then LOAD DATA INFILE but that didn't help. Some chars were screwed up. If I create SQL script from part of csv file and load it by command SOURCE 'script.sql' then everything is OK. So, does anybody know the way how to import files from CSV directly by LOAD DATA INFILE without difficult manual conversions to sql scripts? Thanks in advance for any help or ideas. Dusan Pavlica -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Display 1st row of every group?
On Mon, 11 Apr 2005, Peter Brawley wrote: /I guess that entry is either wrong or misleading./ Ordering by another column which isn't mutually dependent with the grouping column will have unpredictable results. Is that what you mean by the example being wrong or misleading? No, I mean the example here http://www.artfulsoftware.com/queries.php#4 Its misleading in so far as it mislead me. Strange how we can take on faith the behaviour of something that is so clearly not doing what is expected simply given the context in which it is presented! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recreating primary index on MyIsam table
If the column was full of 0s when you recreated it, that means you forgot to make it AUTO_INCREMENT. Since it didn't complain about duplicate key entries, you also didn't make it a PRIMARY KEY. That is, you must have done something like ALTER TABLE c1 ADD c_serial INT NOT NULL; Peter's solution, then, is incomplete. You've got the values you want in the current rows, but c_serial still isn't the primary key, and the next row added will have the default value, 0. What you should have done was ALTER TABLE c1 ADD c_serial INT NOT NULL AUTO_INCREMENT PRIMARY KEY; Since you're half way there, you should be able to fix what you have with ALTER TABLE c1 CHANGE c_serial c_serial INT NOT NULL AUTO_INCREMENT PRIMARY KEY; See the manual http://dev.mysql.com/doc/mysql/en/alter-table.html for more on ALTER TABLE. Michael P.S. Reordering the ids is usually a bad idea, and rarely, if ever, necessary. If unsequenced ids bother the admin, he/she needs to get over it. Trying to keep ids in order complicates things unnecessarily. Nils Valentin wrote: Hi Peter, thanks a bunch, I new that it must have been something simple like this. I am just no programmer. ;-) Thanks a bunch !! Best regards Nils Valentin Tokyo / Japan On Sunday 10 April 2005 23:41, Peter Brawley wrote: Nils, So the task is to recreate the current primary key (c_serial),so that the current index would start with 1,2,3,4, SET @i=0; UPDATE c1 SET c_serial=(@i:[EMAIL PROTECTED]); PB - Nils Valentin wrote: Hi MySQL fans ;-), I was just asked recently with the task to recreate a tables index gracefully on a MyIsam table. This is the table layout: CREATE TABLE `cl` ( `c_serial` int(11) NOT NULL auto_increment, `cname` tinytext NOT NULL, `cl_vals` text NOT NULL, `utime` int(11) NOT NULL default '0', PRIMARY KEY (`c_serial`) ) TYPE=MyISAM So the task is to recreate the current primary key (c_serial),so that the current index would start with 1,2,3,4, Currently the numbers are all over the place. To make this simple (for now) there are no dependencies to other tables so the order of the index doesnt really matter, its more for the admins peace of mind ;-) I tried the obvious first, removing the column completely and adding the primary key, which left me with a lot of 0s - which is not what I had in mind ;-). I know this shouldnt be too difficult, but perhaps I am just too long in front of the screen. ;-) Best regards Nils Valentin Tokyo / Japan www.be-known-online.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: results in random order.
Quoting Richard Reina [EMAIL PROTECTED]: I am trying to get this query to randomly pick a record from a table (trans) but it's not working: SELECT ID FROM trans ORDER BY RAND() LIMIT 1; Can anyone let me know what I'm doing wrong and/or how to fix it. Thanks, Richard What error(s) is it giving you? Does it always return the same record? Is there more than one record in trans? I change the name of the table and tested it against mine and it works fine R. Whitney Transcend Development Producing the next phase of your internet presence http://xend.net Premium Quality Web Hosting http://xendhosting.com rw AT xend.net Net Binder http://netbinder.net 310-943-6498 602-288-5340 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Display 1st row of every group?
Thanks to Rhino Dan for the corrections, HAVING in that query makes it return only id values for which there is one row, and the header is wrong since "first row of every group" doesn't mean anything without explicit ordering. PB - Dan Bolser wrote: On Mon, 11 Apr 2005, Rhino wrote: - Original Message - From: "Dan Bolser" [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, April 11, 2005 5:58 AM Subject: Display 1st row of every group? I read with great interest this http://www.artfulsoftware.com/queries.php#4 Display 1st row of every group SELECT id FROM tbl GROUP BY id HAVING count(*) = 1; Despite what the heading says for that query, it is NOT going to return the first row of every group. In fact, it is going to return only groups that contain one row. For example, given this data: ID -- 5 5 5 6 7 7 8 8 8 9 That query will return return this: id -- 6 9 In other words, it is returning only groups where there is a single ID with that value. Note also that it is returning ONLY an ID, not the ID plus the rest of the row. Their description of what the query does is just plain wrong. Delicious second opinion! Thanks for the reply - if you see my second post you see I fixed my problem. Cheers, Dan. No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.6 - Release Date: 4/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Find valid numeric values in a string field?
You were on the right track. Casting a string to a number results in a 0 if MySQL can't make a proper conversion, which is slightly counter-intuitive. This might suit your needs: mysql select * from mixed_num; ++ | my_col | ++ | a | | 0 | | 1 | | abc123 | | 123abc | | 1.2| | -1 | ++ 7 rows in set (0.12 sec) mysql select my_col, my_col + 0 from mixed_num where my_col = '0' OR my_col + 0 != 0; +++ | my_col | my_col + 0 | +++ | 0 | 0 | | 1 | 1 | | 123abc |123 | | 1.2|1.2 | | -1 | -1 | +++ 5 rows in set (0.00 sec) Note that '123abc' is changed to '123', which may make a big difference to you. A less magical way to get at these numbers is using REGEXP: Unsigned decimals: mysql select my_col from mixed_num where my_col REGEXP '^[0-9.]+$'; ++ | my_col | ++ | 0 | | 1 | | 1.2| ++ 3 rows in set (0.00 sec) Unsigned integers: mysql select my_col from mixed_num where my_col REGEXP '^[0-9]+$'; ++ | my_col | ++ | 0 | | 1 | ++ 2 rows in set (0.00 sec) Signed integers: mysql select my_col from mixed_num where my_col REGEXP '^-?[0-9]+$'; ++ | my_col | ++ | 0 | | 1 | | -1 | ++ 3 rows in set (2.78 sec) ...et cetera. REGEXP is explained here: http://dev.mysql.com/doc/mysql/en/regexp.html Eamon Daly - Original Message - From: Dan Bolser [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, April 10, 2005 10:10 AM Subject: Find valid numeric values in a string field? I have a column like this my_col varchar(20) null. The values in the column can be text or numbers. How can I select only those rows where the value in this column is a valid number? I need something like IS_DECIMAL(), but I can't find that function. The following SQL fails to do the job (probably because of optimization)... SELECT MIXED_COLUMN, MIXED_COLUMN + 0 FROM TABLE WHERE MIXED_COLUMN = MIXED_COLUMN + 0 ; Hey, lets make an IS_DECIMAL UDF! ;) p.s. why aren't the addresses of these mailing lists anywhere to be found on the MySQL Lists pages? Dan. -- 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]
SCRAMBLE(A,B) (was UDF:Request).
Requirement: Given two columns of a table (Column1 and Column2) of length x, return two columns (Column1_Scram and Column2_Scram) such that the distributions of values in Column1 and Column2 are preserved in Column1_Scram and Column2_Scram, but the pairs of values are randomized. Solution suggested by Shawn Green: Create a table with two columns, and populate this table with random pairs of primary keys picked from the original table. Additionally, allow no duplicate primary keys within either column. Select x rows from this table, and link both primary keys (the primary key pair) back to the original table to get the appropriate number of randomized pairs of Column1 and Column2. He suggests doing the above like this (more or less): OriginalTable PK A B 1 a c 2 a d 3 b e ... CREATE TEMPORARY TABLE IntermediateTable ( PK1 INT NOT NULL, ACHAR(1) NOT NULL, PK2 INT NOT NULL, BCHAR(1) NOT NULL, # UNIQUE INDEX (PK1,A), UNIQUE INDEX (PK2,B) ); INSERT IGNORE INTO IntermediateTable SELECT x.PK, x.A, y.PK, y.B FROM OriginalTable x, OriginalTable y ORDER BY RAND(); SELECT x.A, y.B FROM IntermediateTable INNER JOIN OriginalTable x ON (PK1 = x.PK) INNER JOIN OriginalTable y ON (PK2 = y.PK) LIMIT the_length_of_OriginalTable; The problem with this solution: Its too slow on reasonable sized tables! I never get past the second step with my data after 10 minutes! I have 52699 rows in my 'OriginalTable' leading to ~2.7 billion checks when inserting into the IntermediateTable... or rather 5.4 billion, as I guess it has to check both rows for the UNIQUE key constraint on every attempted insert. Ideally I would like to be able to do several thousand randomizations over my data, and at 10 mins a pop that would take all week. (assuming the query was about to finish when I killed it after 10 mins.) Is their a faster way to do this randomization in SQL? Am I doing something really dumb that was never intended by Shawn? I can easily get the data I need with a quick step into perl, but it would be really neat if I could do all this in MySQL. I can imagine a general way to create 'random' joins (over scrambled data) would have some interesting applications. Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Display 1st row of every group?
On Mon, 11 Apr 2005, Peter Brawley wrote: Thanks to Rhino Dan for the corrections, HAVING in that query makes it return only id values for which there is one row, and the header is wrong since first row of every group doesn't mean anything without explicit ordering. What do you mean? PB - Dan Bolser wrote: On Mon, 11 Apr 2005, Rhino wrote: - Original Message - From: Dan Bolser [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, April 11, 2005 5:58 AM Subject: Display 1st row of every group? I read with great interest this http://www.artfulsoftware.com/queries.php#4 Display 1st row of every group SELECT id FROM tbl GROUP BY id HAVING count(*) = 1; Despite what the heading says for that query, it is NOT going to return the first row of every group. In fact, it is going to return only groups that contain one row. For example, given this data: ID -- 5 5 5 6 7 7 8 8 8 9 That query will return return this: id -- 6 9 In other words, it is returning only groups where there is a single ID with that value. Note also that it is returning ONLY an ID, not the ID plus the rest of the row. Their description of what the query does is just plain wrong. Delicious second opinion! Thanks for the reply - if you see my second post you see I fixed my problem. Cheers, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Find valid numeric values in a string field?
On Mon, 11 Apr 2005, Eamon Daly wrote: You were on the right track. Casting a string to a number results in a 0 if MySQL can't make a proper conversion, which is slightly counter-intuitive. This might suit your needs: I see. Cheers. Shame their is no 'IS_VALID_INT()' or 'IS_VALID_FLOAT()'. :) Dan. mysql select * from mixed_num; ++ | my_col | ++ | a | | 0 | | 1 | | abc123 | | 123abc | | 1.2| | -1 | ++ 7 rows in set (0.12 sec) mysql select my_col, my_col + 0 from mixed_num where my_col = '0' OR my_col + 0 != 0; +++ | my_col | my_col + 0 | +++ | 0 | 0 | | 1 | 1 | | 123abc |123 | | 1.2|1.2 | | -1 | -1 | +++ 5 rows in set (0.00 sec) Note that '123abc' is changed to '123', which may make a big difference to you. A less magical way to get at these numbers is using REGEXP: Unsigned decimals: mysql select my_col from mixed_num where my_col REGEXP '^[0-9.]+$'; ++ | my_col | ++ | 0 | | 1 | | 1.2| ++ 3 rows in set (0.00 sec) Unsigned integers: mysql select my_col from mixed_num where my_col REGEXP '^[0-9]+$'; ++ | my_col | ++ | 0 | | 1 | ++ 2 rows in set (0.00 sec) Signed integers: mysql select my_col from mixed_num where my_col REGEXP '^-?[0-9]+$'; ++ | my_col | ++ | 0 | | 1 | | -1 | ++ 3 rows in set (2.78 sec) ...et cetera. REGEXP is explained here: http://dev.mysql.com/doc/mysql/en/regexp.html Eamon Daly - Original Message - From: Dan Bolser [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, April 10, 2005 10:10 AM Subject: Find valid numeric values in a string field? I have a column like this my_col varchar(20) null. The values in the column can be text or numbers. How can I select only those rows where the value in this column is a valid number? I need something like IS_DECIMAL(), but I can't find that function. The following SQL fails to do the job (probably because of optimization)... SELECT MIXED_COLUMN, MIXED_COLUMN + 0 FROM TABLE WHERE MIXED_COLUMN = MIXED_COLUMN + 0 ; Hey, lets make an IS_DECIMAL UDF! ;) p.s. why aren't the addresses of these mailing lists anywhere to be found on the MySQL Lists pages? Dan. -- 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]
please help : linux version
Hello I need to download a version of mySql v4, but I don't know which one to download. The Server I'm going to load the built database on to is running: Linux 2.4.19C13_v As the server is mySql compatible they must already be running mySql on the system. So the catch is... My home terminal is either Windows XP based or Mac OSX. I need to download a version of mySql that can I can use on either of those platforms to build the database, that I can then upload, and it will run smoothly on the above Unix platform on the server. please help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems upgrading from 4.0 to 4.1
Hello Sergei On Thu, Apr 07, 2005 at 03:14:10PM +0200, Sergei Golubchik wrote: I've just run into deep troubles while upgrading from 4.0.24 to 4.1.10a using precomiled Debian packages on Debian Woody although I read the comments regarding upgrading on dev.mysql.com. After starting the new server and running mysqlcheck -r -v -A, I experienced the following problems on about 15% of my tables (seemed to be quite random, i.e. not only the biggest or most used ones): Christian, do you have any of that tables ? Can you upload one of them (preferably, the smallest one) to our ftp ? beta_hardware_verwaltung.hardware_log info : Found wrong packed record at 0 info : Found wrong packed record at 56 info : Found wrong packed record at 112 Did you found the files on the incoming/ dir and do you have any news on this case? If it's not something extremly stupid from my side, can you open a bug report to make the issue easier to track? bye, -christian- -- Christian Hammers WESTEND GmbH | Internet-Business-Provider Technik CISCO Systems Partner - Authorized Reseller Lütticher Straße 10 Tel 0241/701333-11 ch@westend.comD-52064 Aachen Fax 0241/911879 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SCRAMBLE(A,B) (was UDF:Request).
On Mon, 11 Apr 2005, Dan Bolser wrote: Requirement: Given two columns of a table (Column1 and Column2) of length x, return two columns (Column1_Scram and Column2_Scram) such that the distributions of values in Column1 and Column2 are preserved in Column1_Scram and Column2_Scram, but the pairs of values are randomized. Solution suggested by Shawn Green: Create a table with two columns, and populate this table with random pairs of primary keys picked from the original table. Additionally, allow no duplicate primary keys within either column. Select x rows from this table, and link both primary keys (the primary key pair) back to the original table to get the appropriate number of randomized pairs of Column1 and Column2. He suggests doing the above like this (more or less): OriginalTable PK A B 1 a c 2 a d 3 b e ... CREATE TEMPORARY TABLE IntermediateTable ( PK1 INT NOT NULL, ACHAR(1) NOT NULL, PK2 INT NOT NULL, BCHAR(1) NOT NULL, # UNIQUE INDEX (PK1,A), UNIQUE INDEX (PK2,B) ); INSERT IGNORE INTO IntermediateTable SELECT x.PK, x.A, y.PK, y.B FROM OriginalTable x, OriginalTable y ORDER BY RAND(); SELECT x.A, y.B FROM IntermediateTable INNER JOIN OriginalTable x ON (PK1 = x.PK) INNER JOIN OriginalTable y ON (PK2 = y.PK) LIMIT the_length_of_OriginalTable; The problem with this solution: Its too slow on reasonable sized tables! Their is also a problem with the way RAND() works... SELECT x.PK, x.A, y.PK, y.B FROM OriginalTable x, OriginalTable y ORDER BY RAND() LIMIT 1; This takes soo long to pick a random row. Some cleaver 'LIMIT' optimization could pick a results set almost instantly, instead of taking in excess of half an hour with ~50,000 rows. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Display 1st row of every group?
Dan, Thanks to Rhino Dan for the corrections, HAVING in that query makes it return only id values for which there is one row, and the header is wrong since "first row of every group" doesn't mean anything without explicit ordering. What do you mean? In SELECT foo ... GROUP BY foo ..., which row, of a group of rows with repeating foo values, is returned is "undetermined" (which means, roughly, it's determined by current physical ordering), unless there's an explicit within-group ordering as in your example. Here's a table of five rows with repeating values of id: SELECT * FROM chi; +--++--+ | child_id | id | type | +--++--+ | 1 | 1 | 0 | | 2 | 1 | 0 | | 3 | 1 | 1 | | 4 | 1 | 2 | | 5 | 2 | 2 | +--++--+ Make a copy of the table which physically reverses row order: CREATE TABLE chi2 SELECT * FROM chi ORDER BY child_id DESC; SELECT * FROM chi2; +--++--+ | child_id | id | type | +--++--+ | 5 | 2 | 2 | | 4 | 1 | 2 | | 3 | 1 | 1 | | 2 | 1 | 0 | | 1 | 1 | 0 | +--++--+ The two tables have identical data and opposite physical ordering, so SELECT ... GROUP BY id returns different rows: SELECT * FROM chi GROUP BY id; +--++--+ | child_id | id | type | +--++--+ | 1 | 1 | 0 | | 5 | 2 | 2 | +--++--+ SELECT * FROM chi2 GROUP BY id; +--++--+ | child_id | id | type | +--++--+ | 4 | 1 | 2 | | 5 | 2 | 2 | +--++--+ 2 rows in set (0.00 sec) Applying your query to chi1 and chi2, though, returns identical results. PB - Dan Bolser wrote: On Mon, 11 Apr 2005, Peter Brawley wrote: Thanks to Rhino Dan for the corrections, HAVING in that query makes it return only id values for which there is one row, and the header is wrong since "first row of every group" doesn't mean anything without explicit ordering. What do you mean? PB - Dan Bolser wrote: On Mon, 11 Apr 2005, Rhino wrote: - Original Message - From: "Dan Bolser" [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, April 11, 2005 5:58 AM Subject: Display 1st row of every group? I read with great interest this http://www.artfulsoftware.com/queries.php#4 Display 1st row of every group SELECT id FROM tbl GROUP BY id HAVING count(*) = 1; Despite what the heading says for that query, it is NOT going to return the first row of every group. In fact, it is going to return only groups that contain one row. For example, given this data: ID -- 5 5 5 6 7 7 8 8 8 9 That query will return return this: id -- 6 9 In other words, it is returning only groups where there is a single ID with that value. Note also that it is returning ONLY an ID, not the ID plus the rest of the row. Their description of what the query does is just plain wrong. Delicious second opinion! Thanks for the reply - if you see my second post you see I fixed my problem. Cheers, Dan. No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.6 - Release Date: 4/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Analyze Table
Howdy all, Quick question about Analyze table. I just deleted 3+ million records from a table that gets a lot of insert activity throughout the day. It seems like now would be a good time to run optimize table and/or analyze table. I saw in the docs that analyze table puts a read lock on the table. This will prevent inserts/updates/deletes while the table is being analyzed, correct? For a table with 5 million records (the table has 5 int fields, 1 decimal, 1 timestamp) I would assume that this might take a while. I don't want to tie this table up for a long period of time during the day/evening. Just wanted to see if, in anyone's experience, this is generally a fast or slow thing. Cheers, Tripp __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please help : linux version
Drew Reid wrote: Hello I need to download a version of mySql v4, but I don't know which one to download. As the server is mySql compatible they must already be running mySql on the system. Uh-oh -- not the perennial faceless they, again!? Shouldn't you ask them what version is on the server so you can get the corresponding package(s) for your own system(s)? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems upgrading from 4.0 to 4.1
Hi! On Apr 11, Christian Hammers wrote: Hello Sergei On Thu, Apr 07, 2005 at 03:14:10PM +0200, Sergei Golubchik wrote: I've just run into deep troubles while upgrading from 4.0.24 to 4.1.10a using precomiled Debian packages on Debian Woody although I read the comments regarding upgrading on dev.mysql.com. After starting the new server and running mysqlcheck -r -v -A, I experienced the following problems on about 15% of my tables (seemed to be quite random, i.e. not only the biggest or most used ones): Christian, do you have any of that tables ? Can you upload one of them (preferably, the smallest one) to our ftp ? beta_hardware_verwaltung.hardware_log info : Found wrong packed record at 0 info : Found wrong packed record at 56 info : Found wrong packed record at 112 Did you found the files on the incoming/ dir and do you have any news on this case? If it's not something extremly stupid from my side, can you open a bug report to make the issue easier to track? Yes, I found the file and repeated this problem, thanks. I opened a bugreport #9824 for it. about 15% of my tables (seemed be quite random - it happens on the tables that have live checksum enabled (CHECKUM=1 in CREATE TABLE). workarounds - disable checksum (in 4.0, before upgrade) or copy the table with create ... select. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where is ndb_mgm and ndb_mgmd in the RPM max package on LINUX
Hello. See NDB RPM downloads section at: http://dev.mysql.com/downloads/mysql/4.1.html We installed these rpm packages on linux 2.4.30 with redhat es 3.0 MySQL-server-4.1.10a-0 MySQL-Max-4.1.10a-0 perl-DBD-MySQL-2.1021-3 MySQL-shared-compat-4.1.10a-0 MySQL-client-4.1.10a-0 After that, I did not find the ndb_mgm and ndb_mgmd for the configuration of the management server who suppose to be in the MySQL-Max-4.1.10a-0 (for the cluster)? I followed the documentation who said we must install MySQL-server-4.1.10a-0 and MySQL-client-4.1.10a-0 first. Can someone help me ? Marois, David [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: After upgrade to 5.03beta, mysqld won't start
Hello. What is in error log? See: http://dev.mysql.com/doc/mysql/en/starting-server.html John Swartzentruber [EMAIL PROTECTED] wrote: I was rash and decided to try to upgrade from 4.1 to 5.03 on my home server. This isn't a critical system, but I am working on a class project on it. Yup, I'm a newbie. I'm running Fedora Core3. I did an rpm -ivh for each of the appropriate RPMs (server, client, devel, bench, shared). For the shared I did a --force because there is no shared-compat for 5.03. Anyway, it looks like everything installed, but now, when I do /etc/init.d/mysqld start, it tries for awhile, then fails. I'm not sure where to look to figure out this problem. I don't see any log files, and there are no error messages. Can anyone please get me pointed in the right direction? If I can't figure this out soon, I'll need to try to get 4.1 working again. Thanks for your help. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large tables
Hello. See also these links: http://dev.mysql.com/doc/mysql/en/table-size.html http://dev.mysql.com/tech-resources/crash-me.php and maybe this one :) http://www.mysql.com/news-and-events/success-stories/ Daniel Kiss [EMAIL PROTECTED] wrote: Hi All, I would like to know how big is the biggest database that can be handled= effectively by MySQL/InnoDB. Like physical size, number of tables, number of rows per table, average row= lenght, number of indexes per table, etc. Practically, what if I have a master/detail table-pair, where the master= contains about 30 million rows, the detail in average contains 50 row for= each master row, so about 1.5 billion rows in total? I know that the performace heavily relies on the hardware, but let's assume= that the MySQL server runs on a high-end machine with about 2GB or RAM. Does anyone have experience with extremely large databases? Thank you, =09Daniel -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld threads
Hello. Use -m command line option for ps to see all threads. I compiled mySQLd 4.1.10a from Source on Linux 2.6. Although the server does it's work well, I wondered why there's only one mysqld thread running. Is that new/normal for mySQL 4.1? While using mySQL 3.23 I always had several threads running on my system.B. Heller [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump, --single-transaction and --flush-logs
Hello. I think you may use a General Query Log to find out a sequence of queries which mysqldump will perform. Is there some way to determine the binary log position as of the single transaction for the dump? --master-data might be helpful. Bill Easton [EMAIL PROTECTED] wrote: If I use the options --single-transaction and --flush-logs with mysqldump and InnoDB tables, does the new log file start at the same time as the transaction? If not, is it known for sure if the log file is switched before the transaction starts or if it is switched after the transaction starts? Is there some way to determine the binary log position as of the single transaction for the dump? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrade to 5.0.3-beta and stored procedures
Hello. The problem may in that as of MySQL 5.0.3, it is necessary to have the EXECUTE privilege to be able to execute the routine. I think you should grant an EXECUTE privilege to other users. See: http://dev.mysql.com/doc/mysql/en/create-procedure.html http://dev.mysql.com/doc/mysql/en/stored-procedure-privileges.html Greetings, I wanted to see if anyone else has found this problematic, or if I just missed something. I upgraded from 5.0.2 to 5.0.3-beta on my Red Hat AS3 platform. It appears everything was fine till I went to execute my stored procedures, which were owned by root at the console, since that is the only local db access, all remote access is controlled via strong users/passwords. Running as the local db root account I get results I expect. I am getting no results back from any other id attempting the SP execution from something like MySQL Query Browser. I did notice two new items, a procs_priv table and that you can set DEFINER/INVOKER setting on the SP. However, none of these have fixed my problem. Also, I had to add the line manually to the procs_priv table to allow my user to execute the SP. [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: changing the datadir on .rpm SuSE install?
Hello. Is there an error log in /export? Have you set the correct permissions? See: http://dev.mysql.com/doc/mysql/en/starting-server.html Jeff Drew [EMAIL PROTECTED] wrote: [-- text/plain, encoding quoted-printable, charset: iso-8859-1, 31 lines --] Can the the datadir be changed with a .rpm install on SuSE? Our SA gave us a large partition on /export. We installed the .rpm and were running fine with the datadir set to the default /var/lib path. So I need to move the datadir to /export. Since we're just moving to Production, we do not need the data currently residing on /var/lib. Going forward, we need to read/write to /export. I have not been able to get mysql running with the datadir set to /export. I read in the documentation that the datadir is set at build time for some distributions, but it also explains how to change the datadir in my.cnf, etc so I'm confused. To move datadir to /export, I: 1. Changed the datadir parameter to /export in the /etc/my.cnf file 2. Reran mysql_database_install script. 3. Verified that all the appropriate files and directories appear to have installed under /export. However, the mysqld_safe script says it's: - using /export for data - using a .pid file in /export - exits without starting mysql. There are no other error messages. Can datadir be set to /export successfully? If yes, is there some other configuration that needs to be changed? Thanks Jeff -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error in the binaries installation of mysql-max-4.1.10a-pc-linux-gnu-i686
Hello. You have links in MySQL message which you should follow. Resolve the stack trace. I suggest you to switch to 4.1.11. If you are able to make a repeatable test case you may report a bug. Does the problem remain with a debug package? Hi, We tried to install mysql-max-4.1.10a-pc-linux-gnu-i686 on linux 2.4.30 with redhat es 3.0 When I executed the mysql_install_db script , I have these errors: [EMAIL PROTECTED] mysql]# scripts/mysql_install_db --user=mysql Installing all prepared tables mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x40a4c77c, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8136a43 0x40054e58 (nil) New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Can someone help me ? David M. Marois, David [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slave out of sync
Hello. I think that it is possible using mysqlbinlog with options --stop-position and --start-position to get missed data. But if you had an updates and deletes after crashing the master your task became difficult. But the easiest way, as for me, is to clear the slave and setup replication again. You may use LOAD DATA FROM MASTER to make your task as simple as possible. In feature I suggest you to make a regular snapshots of your master data. MySQL has ability for incremental backups, which could save you a lot of space. Chris Scheller [EMAIL PROTECTED] wrote: i have replication going between 2 boxes. the master crashed a few days ago, and this morning i noticed that the slaves slave thread errored out. the binary log and offset had changed on the master and the slave couldn't sync up. i got the slave up and running again by changing the log file and offset in the slave. but now i am left with out of sync data. how do i get these back in sync, and how do i aviod this in the future? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SCRAMBLE(A,B) (was UDF:Request).
Dan Bolser [EMAIL PROTECTED] wrote on 04/11/2005 11:50:31 AM: On Mon, 11 Apr 2005, Dan Bolser wrote: Requirement: Given two columns of a table (Column1 and Column2) of length x, return two columns (Column1_Scram and Column2_Scram) such that the distributions of values in Column1 and Column2 are preserved in Column1_Scram and Column2_Scram, but the pairs of values are randomized. Solution suggested by Shawn Green: Create a table with two columns, and populate this table with random pairs of primary keys picked from the original table. Additionally, allow no duplicate primary keys within either column. Select x rows from this table, and link both primary keys (the primary key pair) back to the original table to get the appropriate number of randomized pairs of Column1 and Column2. He suggests doing the above like this (more or less): OriginalTable PK A B 1 a c 2 a d 3 b e ... CREATE TEMPORARY TABLE IntermediateTable ( PK1 INT NOT NULL, ACHAR(1) NOT NULL, PK2 INT NOT NULL, BCHAR(1) NOT NULL, # UNIQUE INDEX (PK1,A), UNIQUE INDEX (PK2,B) ); INSERT IGNORE INTO IntermediateTable SELECT x.PK, x.A, y.PK, y.B FROM OriginalTable x, OriginalTable y ORDER BY RAND(); SELECT x.A, y.B FROM IntermediateTable INNER JOIN OriginalTable x ON (PK1 = x.PK) INNER JOIN OriginalTable y ON (PK2 = y.PK) LIMIT the_length_of_OriginalTable; The problem with this solution: Its too slow on reasonable sized tables! Their is also a problem with the way RAND() works... SELECT x.PK, x.A, y.PK, y.B FROM OriginalTable x, OriginalTable y ORDER BY RAND() LIMIT 1; This takes soo long to pick a random row. Some cleaver 'LIMIT' optimization could pick a results set almost instantly, instead of taking in excess of half an hour with ~50,000 rows. Let's try this. I will assume, because you used the PK hack, you have duplicate values in at least one of your sets. Let's cure the Rand() speed issue by adding a column to Original Table to hold a random number and eliminate the lookup problem. Since integer math is much faster than floating point math, we will set up this field as an integer field and fill it appropriately ALTER TABLE OriginalTable ADD COLUMN RandomKey INT UNSIGNED; UPDATE OriginalTable SET RandomKey = RAND()*200; Let's also modify IntermediateTable like this: DROP TABLE IntermediateTable; CREATE TABLE FirstColumn id INT auto_increment , a char(1) , PRIMARY KEY (id) ); CREATE TABLE SecondColumn id INT auto_increment , b char(1) , PRIMARY KEY (id) ); And populate the new tables: INSERT FirstColumn (a) SELECT a FROM OriginalTable ORDER BY PK1; INSERT SecondColumn (b) SELECT b FROM OriginalTable ORDER BY RandomKey; Then get your randomized (A,B) pairs this way: SELECT x.A, y.B FROM FirstColumn x INNER JOIN SecondColumn y on x.id = y.id; This should be MUCH faster than 30 mins (I would guess on the order of 2 or 3 at most). FirstColumn gets filled with data in original order, SecondColumn gets filled with data in random order (thanks to the random value). By creating new tables to cache those values we create two new contiguous auto_increment runs (this way you can analyze subsets of your original data and not need to worry about mismatching on the final INNER JOIN). On the next pass, Re-run the UPDATE to assign new RAND() values to your data. Do not empty or refill FirstColum. Execute a TRUNCATE TABLE SecondColumn; then refill it (INSERT SecondColumn...) and repeat the final query. HTH!! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Help with Duplicate Key (32-bit Solaris MySQL 4.1)
Folks, I've had a database running for months now and suddenly, when inserting into table 'HEARTBEAT_COUNTS' (desc below), I get an error: Error - Duplicate key '444642', -1 (The syntax of the error message is not exact) My insert statement does not include a value for 'id' which is an auto_increment field. The table has about 390,000 rows. Please help -- I'll answer any question ASAP as I really want to understand why this happened. I have temporarily dropped the table and created a new one and the problem disappeared -- but I want to learn why it happened and how I can prevent it. Thank you very much. -- CREATE TABLE `heartbeat_counts` ( `id` int(10) unsigned NOT NULL auto_increment, `received_responses` smallint(5) unsigned NOT NULL default '0', `device_response` bigint(20) unsigned NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=MyISAM ; example insert: INSERT INTO heartbeat_counts set received_responses=1, device_response=12951 ; __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with Duplicate Key (32-bit Solaris MySQL 4.1)
On Monday 11 April 2005 16:00, mysql helppp wrote: Error - Duplicate key '444642', -1 (The syntax of the error message is not exact) probably index error, run myisamchk on the table, or repair table inside MySQL command line. Should fix without loose any data. --rengolin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problems with 4.1.11 on Solaris
On Apr 8, 2005, at 12:21 PM, Adam Arrowood wrote: I just installed the 32-bit SPARC Solaris package (mysql-standard-4.1.11-sun-solaris2.9-sparc.pkg.gz) in made a symbolic link to the install directory as /usr/local/mysql . I am having the following problems: 1) when I start mysql with the included mysql.server script, I get the following error: ./mysql.server start Starting MySQL ./mysql.server: log_success_msg: not found ... there is no such file /lib/lsb/init-functions, so it should get aliased, but don't know why it isn't working. Ok, so here's the deal... unlike linux, /bin/sh on Solaris does not have a built-in alias function, so in mysql.server line 64, alias log_success_msg=echo \ SUCCESS! will call /usr/bin/alias, which only works for csh and ksh... so an easy fix for this is to change the mysql.server script from sh to ksh, by changing the first line to: #!/usr/bin/ksh 2) (much more serious) a core file appears in the mysql root directory. 'file core' gives: core: ELF 32-bit MSB core file SPARC Version 1, from 'my_print_defaul' (note the lack of t)... yet the server starts correctly. When I run mysql_print_defaults manually, it's fine. Correction, on my_print_defaults will core dump if called with --defaults-file= or --defaults-extra-file= , but not when called with --config-file= ... My quick fix is to edit mysqld_safe, changing line 97 to: defaults=-c $MY_BASEDIR_VERSION/data/my.cnf defaults_exec=--defaults-file=$MY_BASEDIR_VERSION/data/my.cnf then lines 318-320 to: $NOHUP_NICENESS $ledir/$MYSQLD $defaults_exec --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file $err_log 21 else eval $NOHUP_NICENESS $ledir/$MYSQLD $defaults_exec --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file $args $err_log 21 3) (er, this is since 4.1.10) Given there are no pre or post scripts in the pkg file, can you start putting the version number in the PKG value or at least set the MAXINST to more than on in the pkginfo file? That way when there is a new version, we can install a new version without having to pkgrm the old one (or, like I've done, pkgtrans the new package and make these changes manually) ... I saw the reply from [EMAIL PROTECTED] about Solaris packaging... I hope the download page will be fixed ? -adam a -- Adam Arrowood :: http://www.gatech.edu/adam :: [EMAIL PROTECTED] Office of Information Technology/OE/CS :: (404) 894-0372 Georgia Institute of Technology, Atlanta, GA USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
xp server problem
Can someone tell me how to set the server up and running under xp. I keep getting error:0 when I try to configure mysql 4.1. I just want to run mysql on my local machine for practice. Any suggestions would be greatly appreciated. I can't seem to get the service running at all. I understand that on microsoft machines the service is automatically started when you boot the machine, and I had it running for a while and then it just went away and stayed away. thanks in advance
Re: After upgrade to 5.03beta, mysqld won't start
On 4/10/2005 10:57 AM Gleb Paharenko wrote: Hello. What is in error log? See: http://dev.mysql.com/doc/mysql/en/starting-server.html As I said, I couldn't find any error logs that seemed to relate (i.e, had information newer than the upgrade and start failures). I looked into the /etc/init.d/mysql script and tried running mysqlmanager directly. It would not run because of errors. From another message in this newsgroup, I saw that someone else was having similar problems and that removing the items from the [mysql] section of my.conf took care of them. That's what I did, and then the server started. From what I can tell, this appears to be a bug because the [mysql] section should have no affect on the server process and should not prevent mysqlmanager from running. John Swartzentruber [EMAIL PROTECTED] wrote: I was rash and decided to try to upgrade from 4.1 to 5.03 on my home server. This isn't a critical system, but I am working on a class project on it. Yup, I'm a newbie. I'm running Fedora Core3. I did an rpm -ivh for each of the appropriate RPMs (server, client, devel, bench, shared). For the shared I did a --force because there is no shared-compat for 5.03. Anyway, it looks like everything installed, but now, when I do /etc/init.d/mysqld start, it tries for awhile, then fails. I'm not sure where to look to figure out this problem. I don't see any log files, and there are no error messages. Can anyone please get me pointed in the right direction? If I can't figure this out soon, I'll need to try to get 4.1 working again. Thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How many innodb_log_file and how many groups
Hello I have Mysql 4.1.7 on Solaris / innodb tables I have 2 innodb_log_files of 100M each. A group of statements (19000 insert) does that both logfiles are stamped with the same time. It might be because one has just finished filled and the other start or it might be because they both rotated and my data might be lost in a crash. To prevent that, I could increase the files size but they are already big enough and the recovery might take too long as it is said in the documentation. What I want to do, is to create 4 files of 50M instead of 2 files of 100M. In the documentation it is said that the number of files recommended is 2. Is there a problem to have more? Just to confirm that the recommended applies to have more than one. Does more files decrease the speed? Second. Since there is a parameter innodb_log_files_in_group does it means that I can have more than one group? If so, is the group concept for duplicates of innodb_log_files that you would be kept on another diks to reinforce the safety of the data? And finally, if you are still there: The innodb_buffer_log_buffer_pool_size should be +~80% of the memory available. I have 4 gigs. Let's I fixed it at 3 gigs. Now the innodb_log_files_size: sensible values range from 1MB to 1/n of the size of the buffer pool (N = number of logs size) If I use 2 logfiles I get 1.5 gigs. if I use 4 logfiles I get 750 megs Is not that too big for the recovery. Thank you in advance Johanne Duhaime
How to call mysql_insert_id
A long time ago, I posted this query: If I use the auto-increment feature for a couple of normallized relations, how do I insert into them? Specifically, when I insert into a relation with the autoincrement feature on the primary key, how do I get the value of the index on the newly created row so I can use that the value of a foreign key in another relation? That's database specific, and you haven't specified a database. * In MySQL - mysql_insert_id() How do I call this function? I was hoping I could use SQL such as SELECT mysql_insert_id() FROM XYZ but I discovered that does not work. I'm using a mixture of java and perl. I see in my old documentation that PHP programmers can call such a function. Thanks, Siegfried
Re: How to call mysql_insert_id
On Apr 11, 2005 10:50 PM, Siegfried Heintze [EMAIL PROTECTED] wrote: * In MySQL - mysql_insert_id() How do I call this function? I was hoping I could use SQL such as SELECT mysql_insert_id() FROM XYZ but I discovered that does not work. I'm using a mixture of java and perl. I see in my old documentation that PHP programmers can call such a function. SELECT LAST_INSERT_ID(); HTH, JP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to call mysql_insert_id
Thanks, JP. And will this work for multi-threaded, multi-user applications? Siegfried -Original Message- From: Jan Pieter Kunst [mailto:[EMAIL PROTECTED] Sent: Monday, April 11, 2005 3:05 PM To: mysql@lists.mysql.com Subject: Re: How to call mysql_insert_id On Apr 11, 2005 10:50 PM, Siegfried Heintze [EMAIL PROTECTED] wrote: * In MySQL - mysql_insert_id() How do I call this function? I was hoping I could use SQL such as SELECT mysql_insert_id() FROM XYZ but I discovered that does not work. I'm using a mixture of java and perl. I see in my old documentation that PHP programmers can call such a function. SELECT LAST_INSERT_ID(); HTH, JP -- 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: slave out of sync
Gleb Paharenko wrote: But if you had an updates and deletes after crashing the master your task became difficult. But the easiest way, as for me, is to clear the slave and setup replication again. You may use think this is what i am going to have to do as i found more errors this morning with the slave thread dead. now that i know how to setup repilcation it won't be as painful this go around. my only remaining problem with this is i thought that this shouldn't have happened in the first place. am i going to face this problem any time the master goes offline or for that matter the slave goes offline? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to retain Key order when Deleting a Row
Hello experts, I just need someone to please point me in the right direction on retaining my key or id order in my table when I delete a row. So for example I have 10 rows Id 1-10 that are created automatically when the record is inserted. So if I delete record with id 5 my id's are not 1,2,3,4,6,7,8,9,10 but 1,2,3,4,5,6,7,8,9 ??? thanks _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Group By - Is there a way to set which rows values are used for the fields not in the Group By clause?
Is there a way to set which rows values are used by the GROUP BY clause for the fields that are not in the GROUP BY clause? In this following select statement the group by and order work but the value of the lastviewtime field is not the value of the most recent datetime row. Is there any way to modify the select statement so that the returned lastviewtime field will have the most recent datetime? select app, itemid, ownerid, visitorid, vusername, lastviewtime, sum(viewcount) AS totalcount, itemname from viewvisitor where ownerid = 2 GROUP BY concat( app, itemid ) ORDER BY totalcount create table viewvisitor ( app char(15), itemidINT UNSIGNED NOT NULL, ownerid INT UNSIGNED NOT NULL, ousername varchar(25), visitorid INT UNSIGNED NOT NULL, vusername varchar(25), vfullname varchar(70), lastviewtime DATETIME NOT NULL, viewcount INT, itemname VARCHAR(40), PRIMARY KEY master(app, visitorid, itemid), INDEX (ownerid), INDEX (lastviewtime), INDEX (viewcount) ); Thanks!!! Rich - Do you Yahoo!? Yahoo! Small Business - Try our new resources site!
RE: How to retain Key order when Deleting a Row
Hi, If I understand your question correctly, your remaining keys after the delete will be '1,2,3,4,6...'. The keys will not reorder, so five should be gone, and the next record inserted will be 11. //jjs -Original Message- From: B Wiley Snyder [mailto:[EMAIL PROTECTED] Sent: Mon 4/11/2005 7:38 PM To: mysql@lists.mysql.com Subject: How to retain Key order when Deleting a Row Hello experts, I just need someone to please point me in the right direction on retaining my key or id order in my table when I delete a row. So for example I have 10 rows Id 1-10 that are created automatically when the record is inserted. So if I delete record with id 5 my id's are not 1,2,3,4,6,7,8,9,10 but 1,2,3,4,5,6,7,8,9 ??? thanks _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to retain Key order when Deleting a Row
On Tue, Apr 12, 2005 at 02:38:12AM +, B Wiley Snyder wrote: I just need someone to please point me in the right direction on retaining my key or id order in my table when I delete a row. So for example I have 10 rows Id 1-10 that are created automatically when the record is inserted. Such a thing isn't possible as it would require MySQL to update every row 'past' the deleted row. Why do you need to do this? -Jason Martin -- Oxymoron: Steel wool. This message is PGP/MIME signed. pgpdul81Q104Q.pgp Description: PGP signature
Re: How to call mysql_insert_id
On Apr 12, 2005 1:37 AM, Siegfried Heintze [EMAIL PROTECTED] wrote: Thanks, JP. And will this work for multi-threaded, multi-user applications? Siegfried Yes: The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions. See http://dev.mysql.com/doc/mysql/en/information-functions.html and look for LAST_INSERT_ID(). JP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
VB.NET how to connect to MySQL 4.1 DB
Dear All, Any body has experience to connect MySQL 4.1 DB from VB.NET application? please advise .. how to deal with this? thanks a lot in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
data export
hi all... is there a simple way to export data from mysql tables? like the opposite of 'load data infile'? i was looking around in the manual but without success... thanks. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data export
tir, 12,.04.2005 kl. 01.04 -0400, skrev kalin mintchev: hi all... is there a simple way to export data from mysql tables? like the opposite of 'load data infile'? i was looking around in the manual but without success... SELECT * from table_name into outfile '/tmp/whatever'; Jarle -- Jarle Aase email: [EMAIL PROTECTED] Author of freeware. http://www.jgaa.com news:alt.comp.jgaa War FTP Daemon: http://www.warftp.org War FTP Daemon FAQ: http://www.warftp.org/faq/warfaq.htm Jgaa's PGP key: http://war.jgaa.com/pgp NB: If you reply to this message, please include all relevant information from the conversation in your reply. Thanks. no need to argue - just kill'em all! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
NewBie how to create store procedure
Dear All, I am very new to MySQL, I am using MySQL 4.1 Adminstrator, my question is very simple: how to create store procedure? ..I saw the Create New Procedure/Function menu but it disabled? .. please advise .. thanks Regards Winanjaya -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: VB.NET how to connect to MySQL 4.1 DB
Download the .NET connector for MySQL: http://dev.mysql.com/downloads/connector/net/1.0.html Reference it in your project and you're ready to go. It comes with plenty of code examples on how to use it. --- Winanjaya [EMAIL PROTECTED] wrote: Dear All, Any body has experience to connect MySQL 4.1 DB from VB.NET application? please advise .. how to deal with this? thanks a lot in advance -- 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: NewBie how to create store procedure
MySQL supports Stored procedures from version 5.0 onwards...for more information see http://dev.mysql.com/doc/mysql/en/stored-procedures.html Rgds Manoj - Original Message - From: Winanjaya [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 12, 2005 2:23 PM Subject: NewBie how to create store procedure Dear All, I am very new to MySQL, I am using MySQL 4.1 Adminstrator, my question is very simple: how to create store procedure? ..I saw the Create New Procedure/Function menu but it disabled? .. please advise .. thanks Regards Winanjaya -- 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]
