Re: Maximum number of user variables
Not that I'm aware of. What type of conversions are you doing that you need 30,000 use vars? An easy solution would be to try it and find out :) I need to move multiple linked entries (in around 12 tables) from one running server to another. I'm using auto_increment's all over the place and I have the following problem: I need to move (no replication possible) the information in those 12 tables to an identical functioning system (same software, same db, but other server) and I need to preserve the relations between the tables. The problem that I have is that the systems evolve independently and I can have the same id for 2 different entries on the system (e.g. on the first system I can have the id 10 for User1, but on second system id 10 would be for another user). I want to make a script that dumps the info into an sql file that I can run on the other server, something like this: insert into customers () values (); select @current_customer_id:=max(id) from customers; insert into customer_categ (customer_id, name...) values (@current_customer_id, categ...); And I have around 20-30 000 records that are linked together (I cannot use a single variable because the tables are linked 3 levels deep and I need to keep the intermediate id's in variables). -- mack / -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum number of user variables
- Original Message - From: Neculai Macarie [EMAIL PROTECTED] To: Mysql mysql@lists.mysql.com Sent: Thursday, May 12, 2005 1:20 PM Subject: Re: Maximum number of user variables Not that I'm aware of. What type of conversions are you doing that you need 30,000 use vars? An easy solution would be to try it and find out :) I need to move multiple linked entries (in around 12 tables) from one running server to another. I'm using auto_increment's all over the place and I have the following problem: I need to move (no replication possible) the information in those 12 tables to an identical functioning system (same software, same db, but other server) and I need to preserve the relations between the tables. The problem that I have is that the systems evolve independently and I can have the same id for 2 different entries on the system (e.g. on the first system I can have the id 10 for User1, but on second system id 10 would be for another user). Perhaps after you dump the structure and data into sql files, you could remove temporarily the extra attribut auto increment to those columns. Then start inserting. After that add the auto increment attribut again. OR use bulkcopy(?) if im not mistaken, or any other methods to copy as it is to new dbservers (compress it first). Donny. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum number of user variables
Not that I'm aware of. What type of conversions are you doing that you need 30,000 use vars? An easy solution would be to try it and find out :) I need to move multiple linked entries (in around 12 tables) from one running server to another. I'm using auto_increment's all over the place and I have the following problem: I need to move (no replication possible) the information in those 12 tables to an identical functioning system (same software, same db, but other server) and I need to preserve the relations between the tables. The problem that I have is that the systems evolve independently and I can have the same id for 2 different entries on the system (e.g. on the first system I can have the id 10 for User1, but on second system id 10 would be for another user). Perhaps after you dump the structure and data into sql files, you could remove temporarily the extra attribut auto increment to those columns. Then start inserting. After that add the auto increment attribut again. OR use bulkcopy(?) if im not mistaken, or any other methods to copy as it is to new dbservers (compress it first). Donny, Thanks for the answer. Dumping the structure and data alone (mysqldump style) is not helping me because the tables are linked and I must update the linked tables with the new id's. -- mack / -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum number of user variables
It sounds like you should be doing the link preservation and number update part in php or perl. Neculai Macarie wrote: Not that I'm aware of. What type of conversions are you doing that you need 30,000 use vars? An easy solution would be to try it and find out :) I need to move multiple linked entries (in around 12 tables) from one running server to another. I'm using auto_increment's all over the place and I have the following problem: I need to move (no replication possible) the information in those 12 tables to an identical functioning system (same software, same db, but other server) and I need to preserve the relations between the tables. The problem that I have is that the systems evolve independently and I can have the same id for 2 different entries on the system (e.g. on the first system I can have the id 10 for User1, but on second system id 10 would be for another user). Perhaps after you dump the structure and data into sql files, you could remove temporarily the extra attribut auto increment to those columns. Then start inserting. After that add the auto increment attribut again. OR use bulkcopy(?) if im not mistaken, or any other methods to copy as it is to new dbservers (compress it first). Donny, Thanks for the answer. Dumping the structure and data alone (mysqldump style) is not helping me because the tables are linked and I must update the linked tables with the new id's. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum number of user variables
Neculai Macarie [EMAIL PROTECTED] wrote on 05/12/2005 03:26:33 AM:
Not that I'm aware of. What type of conversions are you doing that
you
need 30,000 use vars? An easy solution would be to try it and find
out
:)
I need to move multiple linked entries (in around 12 tables) from
one
running server to another. I'm using auto_increment's all over the
place
and
I have the following problem: I need to move (no replication
possible)
the
information in those 12 tables to an identical functioning system
(same
software, same db, but other server) and I need to preserve the
relations
between the tables. The problem that I have is that the systems
evolve
independently and I can have the same id for 2 different entries on
the
system (e.g. on the first system I can have the id 10 for User1, but
on
second system id 10 would be for another user).
Perhaps after you dump the structure and data into sql files,
you could remove temporarily the extra attribut auto increment to
those
columns.
Then start inserting.
After that add the auto increment attribut again.
OR
use bulkcopy(?) if im not mistaken, or any other methods to copy as it
is
to
new dbservers
(compress it first).
Donny,
Thanks for the answer. Dumping the structure and data alone (mysqldump
style) is not helping me because the tables are linked and I must update
the
linked tables with the new id's.
--
mack /
Here is a summary of how I have merged hierarchical data structures in the
past. I start by adding a column or two to my destination data tables for
each table in the tree I need to reconstruct. The first new column (I
usually call it something like old_ID) holds the original PK of the
source record. The second (if necessary) will hold the original PARENT's
PK value.
As an example, let's imagine that I need to merge a table that looks like
{ID, PARENT_ID, DATA columns} into a new table with the same structure.
In this table PARENT_ID points to some other record in the same table. I
would add my two columns like this
ALTER TABLE new_table add old_ID int, add old_parentid int;
Then, I would need to map the INSERT like this
INSERT new_table (old_ID, old_parentid, data columns)
SELECT old_table id, parent_id, data columns;
Then I go back and update the PARENT_ID of the records in new_table with
the new ID value of their OLD parent records.
UPDATE new_table nt1
INNER JOIN new_table nt2
ON nt2.old_ID = nt1.old_parentid
SET nt1.PARENT_ID = nt2.ID
WHERE nt1.old_parent_ID is not null;
This re-creates the parent-child relationship that used to exist in
old_table by filling in the new values for the PARENT_ID that were
auto-generated when the old records were merged. Repeat this for each
table in your hierarchy. In this example both parent and child records
were from the same table but they didn't need to be. nt1 is the alias for
the child table while nt2 represents the parent table.
You will need to disable any FK constraints (InnoDB) while you rebuild
your parent-child relationships but once you have filled in the child's
parent_id field, you should be able to re-enable those keys and move on to
the next level. If you get an error, check your data. Working from the
top down, you should not create too many issues, especially if your data
was well-organized to start with. Once you have regenerated your
parent-child links to use the new auto_increment values, you can start
dropping the old_* columns to recover that space and optimize your tables
at the same time (thanks to the behavior of the ALTER TABLE).
ALSO, before you start, make a backup of your data (mysqldump works well
for most people). That way if you hose it up in some major way, you can at
least get back to where you started without too much pain.
If you have any questions, I will be lurking ;-)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Maximum number of user variables
Here is a summary of how I have merged hierarchical data structures in the
past. I start by adding a column or two to my destination data tables for
each table in the tree I need to reconstruct. The first new column (I
usually call it something like old_ID) holds the original PK of the
source record. The second (if necessary) will hold the original PARENT's
PK value.
As an example, let's imagine that I need to merge a table that looks like
{ID, PARENT_ID, DATA columns} into a new table with the same structure.
In this table PARENT_ID points to some other record in the same table. I
would add my two columns like this
ALTER TABLE new_table add old_ID int, add old_parentid int;
Then, I would need to map the INSERT like this
INSERT new_table (old_ID, old_parentid, data columns)
SELECT old_table id, parent_id, data columns;
Then I go back and update the PARENT_ID of the records in new_table with
the new ID value of their OLD parent records.
UPDATE new_table nt1
INNER JOIN new_table nt2
ON nt2.old_ID = nt1.old_parentid
SET nt1.PARENT_ID = nt2.ID
WHERE nt1.old_parent_ID is not null;
This re-creates the parent-child relationship that used to exist in
old_table by filling in the new values for the PARENT_ID that were
auto-generated when the old records were merged. Repeat this for each
table in your hierarchy. In this example both parent and child records
were from the same table but they didn't need to be. nt1 is the alias for
the child table while nt2 represents the parent table.
You will need to disable any FK constraints (InnoDB) while you rebuild
your parent-child relationships but once you have filled in the child's
parent_id field, you should be able to re-enable those keys and move on to
the next level. If you get an error, check your data. Working from the
top down, you should not create too many issues, especially if your data
was well-organized to start with. Once you have regenerated your
parent-child links to use the new auto_increment values, you can start
dropping the old_* columns to recover that space and optimize your tables
at the same time (thanks to the behavior of the ALTER TABLE).
ALSO, before you start, make a backup of your data (mysqldump works well
for most people). That way if you hose it up in some major way, you can at
least get back to where you started without too much pain.
If you have any questions, I will be lurking ;-)
Thanks for this solution. Indeed it's much simpler then needing 30 000 user
variables :).
--
mack /
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Maximum number of user variables
Hi! What is the maximum number of user variables that I can have in MySQL ? (I need to transfer a application from one server to another and I need to rebuild the links between tables with new id's on the new server (I'm using auto_increment extensively), and I have a solution that involves many user variables, around 30 000 in a single connection). -- mack / -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum number of user variables
Not that I'm aware of. What type of conversions are you doing that you need 30,000 use vars? An easy solution would be to try it and find out :) -Eric Neculai Macarie wrote: Hi! What is the maximum number of user variables that I can have in MySQL ? (I need to transfer a application from one server to another and I need to rebuild the links between tables with new id's on the new server (I'm using auto_increment extensively), and I have a solution that involves many user variables, around 30 000 in a single connection). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
