SEARCH YOUR SOLUTION HERE  

Auto_increment problem while export-import

,

I have a problem that I googled for but couldn't get any resolution.

I have 4 databases with 2 columns and a single central database with the same 2 columns. I want to put all the data from all the 4 databases into the central database. In my tables I have auto_increment field (id). When I export the data the id field too gets exported with its values. When I try to import it in the central DB then it gives problems. My central DB's auto_increment id field clashes with the id field of my other database tables.

So, how can I perform this operation I thought maybe there is a way to avoid auto_increment field while exporting but there is none.

Please help me since this is very urgent.

Posted On: Monday 29th of October 2012 06:34:02 AM Total Views:  429
View Complete with Replies




Related Messages:

Insert, but also return an autoincremented value from the insert?   (172 Views)
hey! I want to be able to retrieve the autoincremented id from my newly inserted 'inboundcall' row. because it autoincrements, unless i do a special search for the highest one and add one to it, i can't find it, and that method is messy. for example: Code: UPDATE InboundCalls SET lead_id=0, username='', timezone='America/Los_Angeles', parentname='parenttt', studentname='studenttt', status=0, customer_type=0, academic_director='admin', educational_need=0, additional_notes='asdfsafasfasf\r\nsa\r\nf', closed=0 WHERE id=1 SO: i want it to execute that (above), and THEN retrieve the id it got. how can i do that thanks for any help btw. i know it's hard to be nice to others all the time (i'm a programmer and customer support ^^)
Delete from table with autoincrement field ?   (165 Views)
I have a table with a 2 column index and one of the columns is a autoincrement and primark key field. When I delete a record from this table then do another insert, the id count is now off. For instance if I delete the record with id=3, then do another insert, the id is set to 4 etc. What do I need to look at
Alphanumeric autoincrement no   (144 Views)
Hi all , I started my travel site db design , client asking auto increment alphanumeric no example ( ab100,ac100) somthing like this manner ) Is it possible in mysql , Then Please tell me the create table for this , 2. client having Around 40 location , so they book any where , some time through online , some time direct center for book tickets , What db engine i use for this , because the db must support concurrency transaction ,
Renumber autoincrement field   (198 Views)
I have a table with an autoincrement field and a couple other fields. The table has about 100 rows of data. After deleting a few rows, the autonumber field is not sequential anymore (i.e. it has holes from where I deleted the data). Any way to renumber the autoincrement field (i know this can break dependencies on other tables, but this table is isolated). Related question: when I add new fields to a table which has these "holes", is there a way to have the holes filled instead of appending to the table
Turn off autoincrement (temporarily) when insert...select?   (166 Views)
I have a massive table with an autoincrement column and I have a few hundred rows that used to be in that table but were inadvertently deleted. I have backup and have recreated the table the way it was before the deletion so I have a source from which to fetch the deleted rows. But in the meantime additional rows have been added to the real table so I can't just swap the tables. Is there anything wrong with my altering the real table to make the column no longer autoincrement, then doing an insert select statement to pull in the deleted rows from the backup, and then doing another alter to turn autoincrement back on Any tricks I need to be aware of in doing this I assume for example that I need to make a note of the autoincrement value and then when I turn autoincrement back on I need to make sure to set that value back to where it was, correct (I use navicat so that will make life easy on checking and resetting the autonicrement value.) Here's the query I plan to use after I've turned off autoincrement: Code: INSERT INTO phpbb_privmsg SELECT pb.* FROM privmsg_backup pb WHERE pb.msg_id IN ([comma separated list of rows I want recreated]);
Getting autoincremented value of inserted row   (169 Views)
I have a bunch of scripts that involve inserting new rows into a table with an auto-incrementing primary key, and then creating associated entries in other tables that are linked via that primary key. Is there any way to execute an INSERT statement so it returns the value of a field in the inserted row
Mysql installation problem   (163 Views)
Hi , When I am installing mysql database using scripts/mysql_install_db --user=mysql I am getting below error. 060526 [ERROR] ./bin/mysqld: can't find file: './mysql/help_relation.frm' (error no: 13) when I start server using bin/mysqld_safe --user=mysql & I am getting below error. starting mysqld daemon with datbases from /var/lib/mysql stopping server from pid file /var/run/mysqld/mysqld.pid mysqld ended. Please help me to solve this problem.
accommodation booking, start and end dates (was "Query problem")   (127 Views)
hi i need to do a query for accommodation booking the accomm wud have already have been chosen by the user so then i need to check if the start and end dates they entered are available for that particular accommID. im implementing it in php but i need a mysql query that will be able to check this for me. i thought that if i checked that the booking start and end dates were before any of the dates already stored for that accommID, or the start and end dates were both after the dates stored, this would work. but i cant seem to get the query rite i wud appreciate any suggestions thanks , ishnid, you also have to allow for an existing booking to span the deed start/end dates check out the timeline diagram here -- http://forums.devshed.com/ms-sql-de...ion-277324.html
Is mysql causing firewall problems (FW-1 at fw01: Access denied)?   (235 Views)
This seems unlikely to me, but I figured I'd ask anyway. For the last several years I have been running a phpbb forum with winxp, apache, php and mysql. None of my users has ever had trouble accessing it. Over the weekend, I tinkered with mysql to tighten up security. Specifically, I - created a mysql user just for the forum - granted that user database specific privs but not global privs - added skip-networking to my.ini to disallow all mysql tcp/ip connections and enabled pipes as described here: http://forums.devshed.com/mysql-help-4/properly-secuiring-msyql-333574.html ) I did not change my apache settings or any settings other than mysql settins. Now, I hear from a user that she can't get into the forum at her work and she is experiencing this error: Quote: FW-1 at smtlfw01: Access denied. I'm guessing her problem is unrelated to my mysql tinkering but I figured I'd ask here just to be sure. Any chance they are related
Efficiency of a "max of a group" problem   (125 Views)
, I'm revisiting some very old code to optimize it for better performance, and one of my key mistakes as a more junior programmer was using a correlated subquery to find the max row per group (where var=(subquery)). Now I'm going through and removing them... The structure I like to use now I discovered as a example in the MySQL manual when a LEFT JOIN is used: Code: SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL; Normally it's quick and works great. My current problem is that I'm using it on a CPU usage log table for a network of servers like this: Code: select c1.id, c1.server_name, c1.load_averages from cpu_log as c1 left join cpu_log as c2 on c1.server_name = c2.server_name AMD c1.log_time < c2.log_time where c2.id is null; id is a auto-inc server_name is a varchar(128) log_time is a int(11) with a unix_timestamp index on server_name 700,000 rows, 127 server_names and the query takes almost 2 mins Code: 127 rows in set (1 min 48.89 sec) Any ideas on why this is so slow and what I can do to improve this query that is usually quick Explain output: Code: +----+-------------+-------+------+---------------+-------+---------+--------------------------------+-------+-------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-------+---------+--------------------------------+-------+-------------------------+ | 1 | SIMPLE | p1 | ALL | NULL | NULL | NULL | NULL | 67829 | | | 1 | SIMPLE | p2 | ref | sname | sname | 34 | prod_server_log.c1.server_name | 534 | Using where; Not exists | +----+-------------+-------+------+---------------+-------+---------+--------------------------------+-------+-------------------------+ Cheers, whiteatom
Simple mysql query problem..   (148 Views)
I have a very simple query, but its not yielding the results im wanting. Im not sure why.. Code: select customers.id, customers.customer, customers.active, customers.prov from customers where customers.prov = '0' and customers.active = '0' and customers.type != '10' and customers.type != '11' and customers.type != '9' and customers.type != '26' and customers.type != '12' and customers.salesrep = '1' or customers.salesrep = '3' or customers.salesrep = '4' order by customers.id Outputs it shouldnt display anything with active = 1 or prov = 1 495 customer-1 1 1 523 customer-2 1 1 525 customer-3 0 0 530 customer-4 0 0 531 customer-5 1 1 532 customer-6 1 1 535 customer-7 0 0
MySQL select problem   (149 Views)
I'm fairly new and if theres a website to point me to that shows how to handle this issue I'd love to see it, I read the ones listed in mysql sources thread. The first issue is what if I have a infinate amount of colors to list that relate to a single color, I'm not sure I handled that correctly in my table. My table is set up like this int code being the code for the interoiur color, intdesc being the name and ext1code being the colors that match the int color: Code: IntCode pkey IntDesc style_id Ext1Code 0 1 Slate 13 1,2,3,4 1 2 Black 22 1,3,4 Since i know the ext1code what I want to do is find which intcodes that match it. consider red = ext1code 3 i've tried Code: SELECT * FROM `newcolint` WHERE `newcolint`.`Ext1Code` = '3' Code: SELECT * FROM `newcolint` WHERE `newcolint`.`Ext1Code` LIKE '3' Code: SELECT * FROM `newcolint` WHERE `newcolint`.`Ext1Code` IN ('3') None seem to work for me. Any suggestions or places I might see how others handled this.
Transfering database problems   (139 Views)
I have a word press site on my server, that I am transferring to another server. I have dumped and saved my database. There is one database named "Information schema." When I try put this database into the new server, I get the following error message: Code: #1163 - The used table type doesn't support BLOB/TEXT columns I have removed one table was causing this error. Now another table is giving me the same error. I understand what this error means, but I don't see what is causing it. Here's what the table looks like: Code: -- -- Table structure for table `COLUMN_PRIVILEGES` -- -- CREATE TEMPORARY TABLE `COLUMN_PRIVILEGES` ( -- `GRANTEE` varchar(81) NOT NULL default '', -- `TABLE_CATALOG` varchar(512) default NULL, -- `TABLE_SCHEMA` varchar(64) NOT NULL default '', -- `TABLE_NAME` varchar(64) NOT NULL default '', -- `COLUMN_NAME` varchar(64) NOT NULL default '', -- `PRIVILEGE_TYPE` varchar(64) NOT NULL default '', -- `IS_GRANTABLE` varchar(3) NOT NULL default '' -- ) ENGINE=MEMORY DEFAULT CHARSET=utf8; -- -- Dumping data for table `COLUMN_PRIVILEGES` -- what should I do to resolve this dilemma
extract WEEK FROM Date problem   (151 Views)
This is totally insane. This is simple PURE SQL. I can extract day,seconds, years, and monts but not week ( Query: SELECT extract( WEEK FROM '2005-05-11' ) MySQL said: #1064 - You have an error in your SQL syntax near ''WEEK' from '2005-05-11')' at line 1 I tried this also SQL query: SELECT EXTRACT( WEEK FROM CURRENT_TIMESTAMP( ) ) MySQL said: #1064 - You have an error in your SQL syntax near 'WEEK FROM CURRENT_TIMESTAMP())' at line 1 I think I am having a bad day
.sql compatibility problem with restore   (204 Views)
, I have read many posts on trying to restore a .sql dump and none of the suggestions work. I contacted my current provider and they said that the MySQL it was dumped from is different than the one that I am using now. Unfortunately, I am not able to go back to the original MySQL location. However, in the dump it says the following: -- phpMyAdmin SQL Dump -- version 2.6.0-pl3 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Oct 27, 2005 at 04:43 PM -- Server version: 3.23.58 -- PHP Version: 4.3.10 SET FOREIGN_KEY_CHECKS=0; SET AUTOCOMMIT=0; START TRANSACTION; -- -- Database: `active` -- Here is a look at the actual file: (2, 'lcwoodie@peoplepc.com', 1, 4, '2004-04-29', '14:30:45'), (3, 'lcwoodie@peoplepc.com', 1, 4, '2004-04-29', '14:30:45'), (4, 'lcwoodie@peoplepc.com', 1, 4, '2004-04-29', '14:30:45'), (5, 'lcwoodie@peoplepc.com', 1, 4, '2004-04-29', '14:30:45'), Note: I have changed the addresses so these are not real email addresses. If you want me to upload the file I can. Currently, I am using MySQL 4.1.9 and phpMyAdmin is 2.6.0 Please help me to get the file converted to a form that 4.1.9 can restore. If there is some utility that does this please let me know. Or, if there is a Perl program that would be helpful too.
Simple join problem   (196 Views)
I'm trying to get started on an assignment for school. I've created the tables as specified and filled them with rows. It's complaining, I believe, about the join clause; here's my syntax: SELECT herberth_projects.projno FROM herberth_clinicians JOIN herberth_projects ON herberth_clinicians.Clinicianno = herberth_projects.Clinicianno; Do you see anything apparently wrong
SQL UPDATE problem   (137 Views)
I am a newbie on Mysql under linux. I am attempting to execute an UPDATE statement: update unpostedjournals, ledger set ledger.amount = ledger.amount + unpostedjournals.amount where unpostedjournals.accountcode = ledger.accountcode; The problem is that when this executes (without warnings), the ledger.amount value appears to indicate that only the first value from the view is used. Not sure if this is helpful but here are the contents of the tables: mysql> select accountcode, amount from unpostedjournals; +-------------+------------+ | accountcode | amount | +-------------+------------+ | 2030 | 599000.00 | | 2030 | -598000.00 | | 2030 | -1000.00 | | 2030 | 0.01 | +-------------+------------+ 4 rows in set (0.00 sec) mysql> select * from ledger; +-------------+-----------+ | accountcode | amount | +-------------+-----------+ | 2030 | 599000.00 | +-------------+-----------+ 1 row in set (0.00 sec) The above shows the table after running the UPDATE. The value of amount before the update was zero.
Having problem with using LIMIT on LARGE table   (135 Views)
Ok.... I am running through a large MySQL table to update some values..... so I need to run through the whole thing in parts.... everything seemed to go ok using a query such as the follows.. Code: SELECT id,dob FROM users WHERE dob != '' ORDER BY id ASC LIMIT 1000000,500000; ..until we got to about the 9000000 mark. The page then wouldn't load at all.... even when left overnight. I just tested various things & found out it's because it resorts to using a filesort...... it doesn't use a filesort if I do something like this.. Code: SELECT id,dob FROM users WHERE dob != '' ORDER BY id ASC LIMIT 8000000,500000; ..but as soon as it reaches over 9000000 it uses a filesort.... however I can prevent this by doing less results per query, such as.. Code: SELECT id,dob FROM users WHERE dob != '' ORDER BY id ASC LIMIT 9000000,10000; ...however it seems I can't much higher than 10k per query to avoid the filesort. Here are my MySQL config options if they help.. Code: # The MySQL server [mysqld] skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_max_sort_file_size = 128849018880 myisam_sort_buffer_size = 268435456 ft_min_word_len=2 ft_max_word_len=15 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M ft_min_word_len=2 ft_max_word_len=15 [mysqlhotcopy] interactive-timeout
Select highest value mysql problem   (181 Views)
i want to get the highest vote of a member, so far i have this: PHP Code: $resultaat=mysql_query(" SELECT* FROM`members` WHEREvote=( SELECTMAX(vote) FROM`members`)") ordie(mysql_error()); while($rowing=mysql_fetch_array($resultaat)){ $winner=$rowing['uid'];} this kinda works but the problem here is when there are 2 members with the ame number of votes and that is the highest, the server seems to get confused a bit is there a way i could make it so that if there are a number of the same votes , the server picks the person that has had the last vote
Joining problems....I think.....   (296 Views)
Hi folks. If I use this query: Code: SELECT p.developmentId development , p.apartmentNum apartment , p.paymentYear , ao.totalDue, ao.otherAmount, ao.otherDescription , SUM(CASE p.paymentMonth WHEN 1 THEN p.payment ELSE 0 END) 'Jan' , SUM(CASE p.paymentMonth WHEN 2 THEN p.payment ELSE 0 END) 'Feb' , SUM(CASE p.paymentMonth WHEN 3 THEN p.payment ELSE 0 END) 'Mar' , SUM(CASE p.paymentMonth WHEN 4 THEN p.payment ELSE 0 END) 'Apr' , SUM(CASE p.paymentMonth WHEN 5 THEN p.payment ELSE 0 END) 'May' , SUM(CASE p.paymentMonth WHEN 6 THEN p.payment ELSE 0 END) 'Jun' , SUM(CASE p.paymentMonth WHEN 7 THEN p.payment ELSE 0 END) 'Jul' , SUM(CASE p.paymentMonth WHEN 8 THEN p.payment ELSE 0 END) 'Aug' , SUM(CASE p.paymentMonth WHEN 9 THEN p.payment ELSE 0 END) 'Sep' , SUM(CASE p.paymentMonth WHEN 10 THEN p.payment ELSE 0 END) 'Oct' , SUM(CASE p.paymentMonth WHEN 11 THEN p.payment ELSE 0 END) 'Nov' , SUM(CASE p.paymentMonth WHEN 12 THEN p.payment ELSE 0 END) 'Dec' , p.reminder FROM payments p, amountowed ao WHERE p.paymentYear = YEAR(CURDATE()) AND p.developmentId = AND p.developmentId = ao.developmentId AND p.apartmentNum = ao.apartmentNum Group BY development,apartment; ....my query works great, and returns expected results. But then, I need to retrieve a column value from another table called 'Owners'. When I update my query to the following, my monthly results are all over the show! It must be something to do with the join, but I just can't fix it! Please help! Code: SELECT p.developmentId development , p.apartmentNum apartment , p.paymentYear, o.Name , ao.totalDue, ao.otherAmount, ao.otherDescription , SUM(CASE p.paymentMonth WHEN 1 THEN p.payment ELSE 0 END) 'Jan' , SUM(CASE p.paymentMonth WHEN 2 THEN p.payment ELSE 0 END) 'Feb' , SUM(CASE p.paymentMonth WHEN 3 THEN p.payment ELSE 0 END) 'Mar' , SUM(CASE p.paymentMonth WHEN 4 THEN p.payment ELSE 0 END) 'Apr' , SUM(CASE p.paymentMonth WHEN 5 THEN p.payment ELSE 0 END) 'May' , SUM(CASE p.paymentMonth WHEN 6 THEN p.payment ELSE 0 END) 'Jun' , SUM(CASE p.paymentMonth WHEN 7 THEN p.payment ELSE 0 END) 'Jul' , SUM(CASE p.paymentMonth WHEN 8 THEN p.payment ELSE 0 END) 'Aug' , SUM(CASE p.paymentMonth WHEN 9 THEN p.payment ELSE 0 END) 'Sep' , SUM(CASE p.paymentMonth WHEN 10 THEN p.payment ELSE 0 END) 'Oct' , SUM(CASE p.paymentMonth WHEN 11 THEN p.payment ELSE 0 END) 'Nov' , SUM(CASE p.paymentMonth WHEN 12 THEN p.payment ELSE 0 END) 'Dec' , p.reminder FROM payments p, amountowed ao, owners o WHERE p.paymentYear = YEAR(CURDATE()) AND p.developmentId = AND p.developmentId = ao.developmentId AND p.apartmentNum = ao.apartmentNum AND p.apartmentNum = o.apartmentNum Group BY development,apartment;