Create Virtual Table During Query Or Load Table To Memory From Text File
I have access to my application database via a secure link, so I cannot link a local database to it, only run queries on the remote server and download the result.
The database is missing some useful information, and I cannot add a table at this stage. I would like to achieve the result by creating a virtual table or the like. ...
View Complete Forum Thread with Replies
Related Forum Messages:
Cant Load Text File Into Table From Remote Browser
I'm trying to run: $sql = "LOAD DATA INFILE 'E:AHLog.txt' REPLACE INTO TABLE `items` FIELDS TERMINATED BY '' LINES TERMINATED BY ''"; $result = mysql_query($sql); And I thought that "REPLACE" would take care of repeating rows, but it doesn't. How do I write a mysql command that only inserts UNIQUE rows, and not repeat rows?
View Replies !
Loading Text File Into Table Using Load Data Infile
I'm trying to a load a text file ( txt1.txt) file into a table (table1). My table has 2 columns... column 1 (integer), auto increment column 2 (text). I want to load each line in the text file as a new row in my table. And i want to load the contents of the the text file ONLY in column2. (column 1 handles itself.) It goes without saying I expect each row to be of a different length. When I run a simple: load data infile 'txt1.txt' into table table1(column2); I get an error 1262 "Row 2 was truncated;it contained more data than there were input columns
View Replies !
How To Create New Table From A Text File?
Is there a GUI utility which can create a new table in my mysql database from a 50 million-record text file? I want mysql to get the data into the database as whatever it can - varchar etc. I can change columns later to double.... unless mysql is intelligent enough to see that the field in the text file is numeric.
View Replies !
Chew So Much Virtual Memory On Windows?
Have a fairly busy MySQL server using MyISAM and InnoDB tables on Win 2003 with plenty of available RAM (total 4GB, with about 1.5GB unused) which is using a massive amount of virtual memory despite having tweaked MySQL to use as much memory as it needs. RAM usage never gets much above 400MB, however Windows is reporting that MySQL is using a massive 1.5GB of virtual memory. Most other processes use a similar amount of RAM and virtual memory, so why would MySQL be using 4~5 times more virtual memory than physical memory? Especially when there is so much free physical memory?
View Replies !
Load Table From File
Is there a method if I wanted to load from a file but place a static value into each row that is loaded into the table? Pseudo code: declare aValue char; set aValue = 'staticvalue'; LOAD DATA INFILE 'twoColumnFile.txt' INTO TABLE ThreeColumnTable (column1, column2, column3) SET column3 = aValue; Edited 1 time(s). Last edit at 01/09/2008 02:13PM by John Doe.
View Replies !
DB Log Vs Text File Log And 1 Table Vs N Tables
1. Storing log in DB/Text file which mayb keep increaing every second. Which one is more feasible? 2. I hv a situation which to record the impression, the record keeps growing and should be huge one day, i wonder, spliting the same type of data into several tables like impression_[month]_[day] which will create different tables to store same type of data. Is it a good idea to reduce the select/insert time for the system?
View Replies !
Loading Text File Into Table
i created a table with the key attribute having an autoincrement property... i loaded a text file without the key attribute column to populate my table...but there's an error... but when i loaded the file with the key attribute column it doesnt post an error... how can i insert or load a text file without the key attribute column since it is already autoincremented?
View Replies !
How To Create A Table And Import A CSV File
Server version: 5.0.41-community-nt phpMyAdmin - 2.10.2 MySQL client version: 5.0.37 Windows XP Professional on home PC Linux shared hosting I am a total newbie to MySQL and have never created a table. I'm using oscommerce and have the stock database that came with it and it is up and running on both my home computer and on the live server although the shop is not selling anything yet as it has no products other than the basic oscommerce product list. I'm hoping to do all this on my home computer before uploading to the live server which I do know how to do using import. My supplier has sent me a load of CSV files with the headers as follows: actionidcategory_pathnamedescriptionshort_descriptionimagespricetax_applicabledelivery_chargespecial_offerorder_locationnumber_in_stockproduct_weightproduct_codedoes_it_have_optionsname_of_optionstype_of_optionsoption_itemsprice_difference_of_option_items*EOL* Total of 21 fields. I found this information on how to upload CSV files but have no idea what it all means although I did try and failed. To import an Excel file into MySQL, first export it as a CSV file. Remove the CSV headers from the generated CSV file along with empty data that Excel may have put at the end of the CSV file. You can then import it into a MySQL table by running: load data local infile 'uniq.csv' into table tblUniq fields terminated by ',' enclosed by '"' lines terminated by ' ' (uniqName, uniqCity, uniqComments) The fields here are the actual tblUniq table fields that the data needs to sit in. The enclosed by and lines terminated by are optional and can help if you have columns enclosed with double-quotes such as Excel exports, etc. I created a table called mynewtable with 21 fields but haven't got a clue what I should put in all the fields so it will not let me save it.
View Replies !
Inserting Data Into A Table From A Text File
I have a table in MySQL with the columns: "ID, JokeText, JokeCategory, JokeDate". I want to take a text file that is a long list of jokes, and load each joke into a new row of the table in the JokeText field. I have tried a bunch of different approaches, and just can't get it to work right. Can anyone enlighten me? If it helps, here is a sample from the jokes text file (yes, they are stupid :P) Q: What goes up and down but does not move? A: Stairs %% Q: Where should a 500 pound alien go? A: On a diet %% Q: What did one toilet say to the other? A: You look a bit flushed. %% Q: Why did the picture go to jail? A: Because it was framed. %% Q: What did one wall say to the other wall? A: I'll meet you at the corner. %%
View Replies !
Loading Data Into A Table From A Text File
After creating an empty table I try to load data into it from a local .txt file on my machine. This is my command: load data local infile 'doc.txt' into table data; MySQL returns this error message: File 'doc.txt' not found. What is the default folder MySQL looks into? I have installed MySQL on a Windows XP SP3 machine. The doc.txt file is written with MS Notepad.
View Replies !
How Do I Create A Table For Holding File Permissions?
I am wanting to make file sharing website. So users can upload files and have control over who access the files. I am wanting to use MySQL for storing the users, passwords, home folders and access permissions for each file. I want to make a table that has the userid and a list of all the files he can access. But I don't know how to make the table that would hold a list of files.
View Replies !
Load Text File Into A Field
found examples about how to populate different fields from a text file (with MySQL, the LOAD DATA INFILE sentece) http://dev.mysql.com/doc/refman/5.0/en/load-data.html
View Replies !
How Can I Load This Text File Into MySQL?
the following are the content of the txt file. It is 2 rows. There are much more rows like these in the txt file. I want to load every 1st line into my database. I used " load data infile ' file path/file name' into tablename fields terminated by ' ' lines starting 'r '; " to load the file. But there is error. I think "V2Dir " of 2nd line make the error. It conflicts with lines starting 'r '. Could somebody give me any solution for loading this text into my database? Code:
View Replies !
Load Text File Error
All I want to do is load a simple txt file to a database. I use the following command: mysql> LOAD DATA INFILE "30000lines.txt" INTO TABLE mytable; and i get the following error: ERROR 1045 (28000): Access denied for user 'username'@'%' (using password: YES) When I do a grant for the table i get the following message: Code:
View Replies !
Regular Text File How To Sql 2000 Table Code
i have a text file as follow, line with ¡°|¡±and {LF}, 8|-000000186075919.|+000000000387820.|2008-03-31|20010423| 9|-000000000003919.|-000000000123620.|2008-03-31|20010123| 8|-000000018623419.|+000000000381230.|2008-05-30|20010423| i want to sign char(1)£¬year decimal(18,3) , month decimal(18,3), trandate smalldatetime£¬update smalldatetime£¬after to sql table is as follow, sign year month trandate update 8 -186075919.000 387820.000 3/31/2008 4/23/2001 9 -3919.000 -123620.000 3/31/2008 1/1/2001 8 -18623419.000 387820.000 5/30/2008 4/23/2001
View Replies !
Can't Load Large Text File Into MySQL
I have a file with over 11,000 records (of woody species worldwide) that I am trying to load into MySQL. Usually, I use a session of PHPMyAdmin to do this and this resides inside an installation of XAMPP. I am running XP. NO matter what setting I try (using the CSV or SQL choice, No SQL compatibility or MYSQL40, I get the following error after a long delay: Fatal error: Maximum execution time of 300 seconds exceeded in D:XAMPPphpmyadminlibrariessqlparser.lib.php on line 544 Do I have to lengthen the timeout parameter in sqlparser.lib.php or is there some other mistake I am making. I have tried to use a straight text file, a text file that is tab delimited and even imported this into Excel to make a CSV file to see. The fields in the file are: GenusEpithet Authorities
View Replies !
Choose Fields To Load From Text File
Have a text file with lots of fields but im only interested to load som of them into a table. Must i have the same amount of fields in my table as a have in the text file. Have tried to load a text file into a table with less fields than the text file. My database has a table with only three fields, tried to use @dummy to exclude the rest of the fields in the text file. The problem is it dont work, is it the right way to do it. Ex. load data infile 'log.txt' into table test fields terminated by ';' lines terminated by '.' (field1,field2,field3,@dymmy,@dummy,@dymmy,@dymmy,@dymmy,@dymmy, @dymmy,@dymmy,@dymmy,@dymmy,@dymmy,@dymmy,@dymmy,@dymmy, @dymmy,@dymmy,@dymmy,@dymmy,@dymmy,@dymmy,@dymmy,@dymmy, @dymmy,@dymmy,@dymmy,@dymmy,@dymmy,@dymmy,@dymmy,@dymmy,);
View Replies !
INSERT Syntax For Text File To Load Into Tables?
I have successfully created a number of tables in my database and am looking for an efficient way to load data into the tables. I have the following (successful) command to find the text file and load it into the target table: "load data local infile 'C:mySQL abledata.txt' into TABLE checks;" It loads into the 'checks' table successfully, but the data is incorrect. What is the syntax of the INSERT command that I'm using in the text file itself-- assuming that I do use the INSERT command? Here is what I have in the NotePad text file I'm using, which only produces zeroes in the first and third columns: insert into checks values ('1', 'Ma Bell', '150', 'Have sons next time', '2', 'Reading R.R.', '245.34', 'Train to Chicago', '3', 'Ma Bell', '200.32', 'Cellular phone', '4', 'Local Utilities', '98', 'Gas', '5', 'Joes Stale $ Dent', '150', 'Grocieries', '6', 'Cash', '25', 'Wild Night Out', '7', 'Joans Gas', '25.1', 'Gas');
View Replies !
LOAD DATA INFILE Command To Import A Text File
I am using the LOAD DATA INFILE command to import a text file into mysql. However, there is much more text in the file than needed in my tables. How can I make a selection from the text line. For example position 5 - 10 in field A, position 20-23 in field B, etc ...
View Replies !
Create Table Query
I just dump this script from another database so I could add the same create table query for different db, when I run the query I get the error. mysql> CREATE TABLE SENT_MESSAGES ( -> INDNUMBER int(11) NOT NULL auto_increment, -> CLIENT varchar(100) NOT NULL default '', -> CODE varchar(100) NOT NULL default '', -> TYPE varchar(100) NOT NULL default '', -> WHEN timestamp(14) NOT NULL, -> PRIMARY KEY (INDNUMBER) -> ) TYPE=MyISAM; ERROR 1064: You have an error in your SQL syntax near 'WHEN timestamp(14) NOT NULL, PRIMARY KEY (INDNUMBER) ) TYPE=MyISAM' at line 6
View Replies !
Create Table With Result Of A Query?
Is there a way to create a table with the results of a query? i.e. I want to create a table of the with the users who registered on my site on January, Feb and so on ... All this records are stored in a single table: users, and there is a field called date_join, so i want to create a table with only the users who registered on january and name it users_jan for example ..
View Replies !
CREATE TABLE And MySQL Query Browser
I am using the ODBC 3.51 Driver to Do a "create database" and then create some tables on that database. I see in the ..mysqldata directory my new database I have created and the associated .frm files, however MySQL Query Browser and Administrator do not see these schema's. I am guessing it may be due to user privileges. MySQL is running locally and the connection string does not contain a Username and password. Also, when I do show databases from within the Query Browser, it does not show my Database, only the one that I had created manually. There must be some more setup that I am not aware of to register the Database or something.
View Replies !
Memory Table Full
I've read that memory tables are supposed to gracefully 'fallback' to the default engine when they reach a certain size, but I'm not finding that they behave that way. Within my application, I had run into situations where CREATE TEMPORARY TABLE xxxx ENGINE=Memory SELECT xxx... would result in a "The table 'xxxx' is full" error. I had to change from memory to the default engine to ensure they would work, but most of the queries were much faster when using in memory temp tables where appropriate. Is there a way to ensure MySQL will gracefully switch to another engine when the temporary table becomes too large for the heap engine?
View Replies !
Index Large Table And Low Memory
Experienced users warned me against possible problems with *index* definition for large tables: they claim MySQL indices are stored completely in memory, which is problematic to me : I have a huge table and limited memory size (RAM). I did the math, and the entire index just won't fit into the RAM. Unfortunatelly it will be a while before management approves hardware improvements.
View Replies !
Memory Table Truncating Automatically
I have a memory table that has 6 fields, 3 indexes and about 650,000 rows. It loads into memory fine, and works well when my application runs select quieries on it. The problem is that the table likes truncating itself. I had loaded it yesterday, ran my application, went to sleep and today morning I find it truncated. The server has not been rebooted. What can be the possible reasons why this would happen? Couple of things to know: I do have a dedicated server but there are other developers using the machine (so it could be a possiblity that they use up memory or something?) The machine has enough memory to load the table My question pretty much boils down to this: If the machine runs out of memory, does it truncate the table?
View Replies !
ERROR 1005: Can't Create Table './db/table.frm' (errno: 150)
I found the key to solve this problem in: http://darkstar.ist.utl.pt/mysql/do...onstraints.html You'll probably need an INDEX for that new foreign key you are declaring in older versions this isn't neccesary but in latest ones it is a restriction. You can have more info about the error description if you have root access by typing mysql> show innodb status; LATEST FOREIGN KEY ERROR ------------------------ 030807 1:27:10 Error in foreign key constraint of table database/table: There is no index in the table database/table where the columns appear as the first columns. Constraint: foreign key (key) references database.table(key) on delete cascade) type=innodb also if you type: shell> perror 150 you obtain: 150 = Foreign key constraint is incorrectly formed I hope this to be enough...
View Replies !
ALTER TABLE Produces Error # 1005 Can't Create Table.
I'm a SQL 2000 DBA intern I'm replicating a current production MySQL DB into a WAMP enviornment on my computer. The production version is on a MySQL 5.0.22-Debian_0ubuntu6.06.3 and my WAMP sandbox is running MySQL 5.0.51b. So i exported the production database to a sql file and tried using phpMyAdmin to upload it to my WAMP sandbox. MySQL on my computer gave me and error on the piece of code: ALTER TABLE `Event_Crews` ADD CONSTRAINT `Event_Crews_ibfk_1` FOREIGN KEY ( `Event_ID` ) REFERENCES `Events` ( `Event_ID` ) ON DELETE CASCADE ON UPDATE CASCADE where Event_ID on both tables is an int. and I get this error from MySQL: #1005 - Can't create table '.ambulance#sql-11d8_7d.frm' (errno: 150)
View Replies !
Rights To Create Table, Select, Then Drop Table..
I have a need to get data from the db that requires me to=20 1) do a select and create a new table with the results=20 2) run a query against that new table=20 3) drop the new table=20 I have a script on my server that does this using the root account that has all on *.* for the db. It works fine.=20 I now want to get these results on a web page.=20 I want to create a new db user for my .php web page to use to connect to the db that only has the needed priviledges on that specific db to get the job done.=20 what priviledges do I need to give that user?=20 currently I have the following but the user can't even log into the db from the command line..=20 mysql> show grants for user;=20 +----------------------------------------------------------------------- ---------+=20 | Grants for user@% |=20 +----------------------------------------------------------------------- ---------+=20 | GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '6fe4c0ab2cf30ae3' |=20 | GRANT SELECT, INSERT, UPDATE, CREATE, DROP ON `db1`.* TO 'user'@'%' |=20 +----------------------------------------------------------------------- ---------+=20 2 rows in set (0.00 sec)=20 when I do a "show grants for user", what should I see to allow what I want?
View Replies !
Can't Create Table -- Error 1050 - Table Already Exists
I tried to create a table in the database. Here is the query sentence : CREATE TABLE word (aff_code varchar( 10 ) NOT NULL default '',position tinyint( 1 ) NOT NULL default '0',flag tinyint( 1 ) NOT NULL default '0',PRIMARY KEY ( aff_code, position )) ENGINE = MYISAM DEFAULT CHARSET = latin1; I was expecting to create a three column table. However, MySQL kept reporting error : ERROR 1050 (42S01): Table 'word' already exists. The fact is there is no table in this database. I then changed the table name, engine type, MySQL still report the same problem. I've tried changing database name. Still the same problem. Prior to the query, I run a program that created (and inserted data as well) 65,000 tables in another database (same server although). I'm wonderring would this cause some kind of dead lock that result in the problem of not able to create any more table? Can anybody give me some tips on how to solve the prolem?
View Replies !
Two Table Query: Grab Rows From One Table Even If No Related Row In Other Table
PHP $gettray = mysql_query("SELECT trailers.title, trailers.link, trailers.movie, movie.title AS mtitle FROM trailers,movie WHERE trailers.movie=movie.word ORDER BY trailerid DESC LIMIT 6",$connm); It works great, but there is one problem. It will not grab any rows from the 'trailers' table if a corresponding movie row does not exist in the 'movies' table. I want it to pull ALL rows from the 'trailers' table, even if the corresponding row in the 'movies' table does not exist yet. If the row does not exist in 'movies', the program than uses the entire trailer title like so PHP if($ttray['mtitle']) { $newttitle = explode("-",$ttray['title']); $newttitle = array_reverse($newttitle); $ttitle = $newttitle[0]; $ttitle = $ttray['mtitle'] ."- ". $ttitle; } else { $ttitle = $ttray['title']; } Thanks Ryan
View Replies !
Create Table / Column Table Order
When creating a table, is there any advantage in specifying the columns in any special order? For example, what about columns with data used more frequently listed first, or column/data types giving some? Or.... am I being pedantic even thinking about such?
View Replies !
Create Table / Alter Table Syntax
i have a table created with this statement create table spiderman ("Comic Name" varchar(20), "Issue Number" varchar(20), "Issue Date" date, "Issue Name" varchar(20), "Issue Value" double, "Mint Condition" bit) what i would like to do is change the column 'Issue Date" so that the word 'Date' is under the word 'Issue'. i have tried to use the alter table like so: alter table spiderman change "Comic Name" 'Comic|Name' which gives me a syntax error. also, is there any way to this in the create table statement?
View Replies !
Table Editor Hangs When Trying To Create A New Table
I'm having a problem trying to create a new table within MySQL. I'm able to log into the database through MySQL Query Browser 1.2.12. But when I try to create a new table from by: - right click on database in question - select "Create New Table" - set Table name: to "TESTING" - click Apply Changes Right after I click that button, the MySQL Table Editor freezes and only way out is to kill the application through Task Manager. Am I doing this incorrectly? Is there a step that I missed? Update: It turned out that the application did not hang, but rather the confirm popup message window was loading behind the MySQL Table Editor. With the confirm window in focus, but hidden behind, it looks like the application has hung. Solution: Move the MqSQL Table Editor to the edge of the window before working. This way, when the confirm popup windows comes up, you can click & drag it into view.
View Replies !
MySQL 4.0.13 Memory Problem Under Heavy Load
How to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH 7.2 using same MySQL version. Recently our master database server (2 AMD Cpu + 2Gb memory + 2Gb swap space) started to suffer from memory outages because of heavy load. During day available free memory is changing from 200Mb to 5Mb and when available memory reaches to 5Mb MySQL starts to give 'Too many connections' messages. Db server is working with 45-70 query/second and more than 25,712 connection per hour. There are active 10-13 threads serving databases. To increase available free memory I've did the following : 1- Optimized all tables 2- Removed unneccessary/old indexes 3- Moved unused databases to replication server 4- Increased key_buffer_size from 8Mb to 128Mb 5- Have increased max_connection from 100 to 150 6- Have increased thread_cache to 5 This changes helped a bit but still memory is a problem for MySQL. What should I do to prevent 'too many connections' messages and have more memory available on database servers? Should I remove more indexes from tables? Should I increase key_buffer_size to 256Mb or more? Key_buffer_size doesn't look like a problem since key efficiency looks 100% most of the time.
View Replies !
MySQL 4.0.13 Memory Problem On Heavy Load
I want to get your opinions on how to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH 7.2 using same MySQL version. Recently our master database server (2 AMD Cpu + 2Gb memory + 2Gb swap space) started to suffer from memory outages because of heavy load. During day available free memory is changing from 200Mb to 5Mb and when available memory reaches to 5Mb MySQL starts to give 'Too many connections' messages. Db server is working with 45-70 query/second and more than 25,712 connection per hour. There are active 13-18 threads serving databases. To increase available free memory I've did following : 1- Optimized all tables 2- Removed unneccessary/old indexes 3- Moved unused databases to replication server 4- Increased key_buffer_size from 8Mb to 16Mb This changes helped a bit but still memory is a problem for MySQL. Average MySQL thread used to consume 100-120 Mb memory before changes, now it is consuming 60-70Mb per thread. What should I do to prevent 'too many connections' messages and have more memory available on database servers? Should I remove more indexes from tables? Should I increase key_buffer_size to 32Mb or more? Key_buffer_size doesn't look like a problem since key efficiency looks 100% most of the time.
View Replies !
|
TRACKER
