Processing A Fixed-width File To Database
could some one tell me who I could run a stored process and upload 4 large file to a database table?
I don't have much of a clue how to do this stuff.
I have a php file that I have the LOAD DATA INFILE / LOCAL INFILE but don't know how to get this to be in a store processing, also can this be ran by a bash script?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Loading Fixed-Width
I need to load fixed-delimited (positional fields) text files into mySQL tables, but don't know how to accomplish this. Sample text file data is as follows (data is properly aligned, but in sample below it gets messed up with this editor): aaaaa bbb 11111 4444 jkjxjk hjh 22222 3333 eeeee bbb 33333 8989 yuyuy ccc 22222 9999 I'm loading this data on mySQL in Windows and don't want to have to go through intermediate steps to get it loaded vi tab or comma-delimited fields since it will require a complete conversion of the files. Also, how do you selectevely load some fields and skip others with the load utility?
Fixed Width Text Files
I need to import multiple fixed-width text files into a database on a daily basis. My thought was to do this with a stored procedure, but I don't see any mysql syntax that would allow me to load a fixed-width file into a table. What am I missing?
Export Data To A Fixed Length File
Can someone tell me the best way to export table data to a fixed-length text file. I notice it gives me options for an xml, csv, and xls file, but I need a fixed length file.
Database Optimization Width MySQL ?
Object : How to optimize mysql architecture for better web connection ? Hello, I use phpMyAdmin 2.3.2 and MySQL 3.23.58 and I do not know how to well-organize mysql architecture for the user convience and speedness. On my web site, I call all the stucture from one table (5 colums and 50 rows). Today, 600.000 connections per day are made to this table. Each time, the queries using PHP do selections (example> SELECT * FROM table_website WHERE CODE like 'RLINK%'. The problems comes more and more often : like 1. mysql_connect(): Host 'localhost.localdomain' is blocked because of many connection errors. 2. or like the query is very slow. and I have to reboot the linux server each week to try to avoid these problems. We would like to have more people coming and to increase the numbers to 1 or 1.2M connections per day. My questions would be : 1. On a architecture point of vue, is it better to have only One table or to have multi-tables. 2. I read http://www.mysql.com/documentation/...timisation.html but I could not find advises concerning many connections to one single table. I did not changed any general variable provided in standard width mysql. From what you think, What kind of possible implementation or change in architecture I could make ? Here, is the tipical query I make to select items within the table : <?php srand ((double) microtime() * 948625); include "/config/config.php"; $idCnx = mysql_connect($host ,$user,$pass ) or die("Impossible de se connecter à la base de données"); mysql_select_db($bdd) or die("Impossible de se connecter à la base de données")..............................................
How Do I Upload A File To MySQL Database And View That File
I do not know how to upload a file to MySQL database and view the posting file on the webpage. I followed the code on-line that show me how to upload, but when I click "browse" to open my file in local drive and click "upload". Nothing happen ! How can I view it on the web page to make sure it was saved in data base.
Limiting Column Width
Is there any way to limit the width of a column? What i mean is, is there any way to stop a column thats defined as MEDIUMTEXT or TEXT messing up the table display in the terminal when its full of text.
Column Width In Union
Column width in a series of UNIONs is determined by the width of the first SELECT. Here is an example: mysql> ( SELECT "foo" ) UNION ( SELECT "fnord" ); +-----+ | foo | +-----+ | foo | | fno | +-----+ 2 rows in set (0.00 sec) This is in MySQL 4.0.21. In my real queries, I cannot know ahead of time the maximum length of any column as returned by any of the SELECT blocks. My workaround is to start my UNION chain with a SELECT "xxxxxxxxxxxxxxxx", "xxxxxxxxxxxxxxxx", ... And then in the code that uses the results, ignore any rows that have "xxxxxxxxxxxxxxxx" in them. This is extremely kludgy. There was as thread from 2003-08-24 with the subject "Union and Order By give strange results in Mysql 4.0.13" which describes this problem. It was unresolved (only workarounded) over a year ago.
Display Width Of Data Types
I was just reading about display width, like: CREATE TABLE test (col INT(5) ) ; I've red that part a couple of times and tested it with my DB, but I just don't get it. If I insert the number 12 I get mysql> select * from test ; +------+ | col__| +------+ | 12___| +------+ 1 row in set (0.00 sec) ( I've add '_' for a space) Where is this display width of 5 ? also inserting lager numbers are displayed (which is explained in the book). So can someone show me how this works and when this can be useful ?
Processing Requirements
What are the processing requirements for MySQL? Is it unusual to install on a laptop? I could not find these requirements on the MySQL site.
Transaction Processing
When doing an update in mysql in a multi-user session eg webpage/php/mysql , how do i have transaction locking on tables. As required data is being updated i don't want other users accessing it?
Adjusting The Command Line Client Width
I am new to MySQL. I was wondering if there were a way to adjust the width of the MySQL command line client. I am able to adjust it vertically, but thats it.
MySQL Sequential Processing
I'm trying to process a series of records sequentially using a routine something like this using Delphi 7/Zeos Components # retrieve result set: Query.SQL.Clear; Query.SQL.Add(' SELECT * FROM items WHERE invoice = :invoicenumber '); Query.Open; # process each record of result set WHILE (invoicenumber = item.invoice) DO BEGIN Query.SQL.Clear; Query.SQL.Add(' UPDATE items SET shipped = :shipped WHERE invoice = :invoice '); Query.ExecSQL; Query.Next; END; When reaching Query.Next, I get error that says I cannot perform on closed dataset
Mysql Stops Processing
Has anyone ever had any problems with MySQL w/ all InnoDB tables just stop processing queries? There doesn't seem to be any pattern to it, it happens at times of relatively high load (load avg of 4 on a dual proc) but the CPUs still have plenty of idle time, and the disks aren't maxing out. It doesn't happen everytime there is a high load either. The box just stops processing every query, and we quickly hit the max connection limit. The only solution we've found is to shutdown mysql and restart it -- at which point it works fine for a couple days. We are running mysql 3.23.56 w/ redhat 7.3 and 2.4.20 kernel.
Processing Time Using Mysql
How long would it take to select around 200 million records in mysql ? Are there any restrictions in mysql? I am using mysql 4.0.20. I understand that subqueries could not be used in mysql 4.0.20. Therefore is using temporary tables the only solution. Looks like when I load 1 million records the performance is very slow and it takes around 5 minutes to fetch. I am using proper indexes but there are lot of group by statements. Would this have an impact in performance?
Error Processing Directive
im getting this error: [an error occurred while processing this directive] all applications on my site that use a mysql database are affected. the rest of the pages are working fine. web host says it's php.ini file that i may have deleted. i suppose it's possible i deleted it once, but out of the blue, the applications work again. all the mysql databases suddenly start to work. tech support at the host does something and it's up again. not sure if im being misled or not. how can i get a php.ini file that will work (assuming i've deleted the original?) can it be regenerated?
Looped Select Queries Or Php Processing?
I'm creating a web interface that includes a feature to view data in graphical format. I'll draw the graphs using data from a mysql database. The data is a measurement of temperature over time, so the graph will have temperature on the y-axis and time on the x. It will have buttons below it that enable the user to move forward or backward through time (no Doctor Who jokes please :), with a timestamp being passed forward on a url when either button is clicked. It's this timestamp that will set what data is needed from the table. I could either do one 'BETWEEN' query fetching all data required between 2 timestamps and then loop through the results in PHP, or alternitavely put the query itself in a loop and pull the data out one record at a time. Now the code for the second of the above options (putting the query in a loop) is the easiest to do and requires the least amout of code to create. It would mean however making upto 600 queries to the database each time the page loads and the graph is drawn. I'm wondering if this is excessive? From a server resources standpoint do you think it would be better to pull all of the data out in one BETWEEN query and then process it with a larger and more complex piece of PHP code? Or is the query within the loop the better way to go?
Fixed Length Export
Is there a way to export mysql table data in a fixed column format? In foxpro there was an output format of 'sdf' that preserved position; is there a similar option in mysql? Some fields have null values.
Mysql Vulnerability Fixed
I have had the following vulnerability reported to me (using a program called FoundScan) and I am trying to find out if this bug was patched, but I can not find any documentation on it. Any suggestions on where I should look. I went to mysql.org and did not have any luck finding if they recongized the problem or if it was fixed. MySQL allows authorized users to switch to a different user account using the COM_CHANGE_USER command. Inadequate bounds checking allows any password greater than 16 characters that is parsed by COM_CHANGE_USER to cause a buffer overflow condition. Arbitrary data outside the buffer may be executed with elevated privileged or cause the MySQL daemon (mysqld) to crash. This allows attackers with access to a valid account to cause a denial-of-service condition or run arbitrary code on the targeted host
Want To Speed Up Mass INSERTS When There Is Processing Of The Insert_id()...
I am importing data from a large plaintext data set and it is going very slowly. I need to acquire and process the insert_id from each record so I cannot use INSERT DELAYED or LOAD DATA INFILE, for which I could speed things up by increasing the bulk_insert_buffer size. I'm not sure if there is really anything that can be done, but I thought I'd ask. The looping process is like this. 1. insert row into table 1 2. get insert id 3. one or more inserts on table 2 using the insert_id Repeat 2000 times or so per page load, because PHP just dies if the file isn't processed in small result sets. It is partly slow because table 1 has several indexes and table 2 has one. The indexes cannot just be added at the end either. In the end there will be +30million rows in table 1 and probably more than 100million in table 2 so if I tried to add the indexes at the end php would die off long before the script could complete.
MYSQL Suitability For Large Data Processing
I am setting up a PHP driven reporting tool and am trying to select my backend. I will be receiving data from multiple sources, probably about a million transactions a day to start, maybe increasing in the future. The data will be quite minimal, maybe 20 fields of data - the style will be something like web log records. I plan to add these to a single table, so by the end year 1 I expect 365million records, maybe nearer 600million. This will grow exponentially as years go on. Can MYSQL handle this? How fast will it process a query on that data?
Slow Inefficent Query (FIXED)
I have this query which pulls members from a table of 1200 members details, filtering out those without and email address and those that don't want Newsletters, and also any whose email address exists in a second table (emails that have hard bounced previously) My first attempt took an age and returned about 10,000 members, clearly loads of repeated rows My 'final' attempt with a DISTINCT shoved in works perfectly but is really slow, PHPAdmin says it takes 1.1s but on the website the data doesn't appear for over 30s, all the other sundry queries on the same table work nice and quick on the same website using the same php to generate the table of results The googling I've done suggests (to me) that there's nothing wrong with the query but I suspect that there's far too much checking of fields going on from the pre DISTINCT query's results ....
Select ... Outfile ... Fixed Length?
I have query that I export into a file using the "outfile" syntax. I can use the following lines of code to create a delimited file but I want to know if I can create a fixed length file? Any ideas? delimited file: Select GivenName, Surname, Address, City, State, ZipCode into outfile 'xx.csv' fields terminated by ',' lines terminated by ' ' from kbm where zipcode = '12345'
Force Fixed Decimal On Import
I have a text file that is length delimited, I would like to import this into a table. I was successful in importing the data using "load data local infile '/name of file' into table tablename FIELDS ENCLOSED BY '' AND TERMINATED BY '' Some of the fields in the file are numerical with different lengths an should be formatted set to 2 decimal places(example 47000 to 470.00 ) I have tried setting the fields in the table as decimal (10,2) but this just adds two addtional places. Is there a way to format the data so that will place a decimal holder at 2.
Import Fixed Lenght Field From Ascii Text
does anyone have some suggestion to import Asccii text into Mysql tables The tables are empty and can be created at import The records from ascii text are fixed lenght but delimited by CRLF The fields are not delimited but fixed lenght
MySQL To Excel -- Setting Column Width In Excel?
Is it possible to set the width of a column in Excel when exporting a table from MySQL to Excel? If so, how is it done and what other type of formatting can be done (ie, bold, background color, etc?)
File Database
I am trying to design a db that users can download files from. In simple terms a user is going to search for a picture and then according to the name of the pic the link to download is going to pop-up.I am working with PHP and mysql. Does anybody have any idea how I can implement this?
.sql Database File
i tried to instal the .sql data_base_ file that is supplied with some demo content for a joomla business template and it just says this - Error SQL query: -- phpMyAdmin SQL Dump -- version 2.10.1 -- http://www.phpmyadmin.net -- -- Host: sql.systemvista.nazwa.pl -- Czas wygenerowania: 13 Cze 2007, 19:01 -- Wersja serwera: 4.1.22 -- Wersja PHP: 4.4.7 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; MySQL said: Documentation #1193 - Unknown system variable 'SQL_MODE'
Database File On Usb HD
I have been given the job of setting up a family website, and I have a USB2 hard disk that will be plugged into a Linux server running apache/php/mysql etc. When running the website, I want to use mambo for ease of use and for the components and stuff that you can get. I am looking to keep as much of the website stored on the USB disk, as then if I need to relocate the website I can do so with minimal problems. Is there any way that the mySQL server on the linux box can store the database on the USB HD, so that it can be loaded into another server if it is relocated, and so that it dosen't get mixed up with existing databases on the server.
Tar File For Database
i have a large .tar file (1.3 gigs) that i want to import into a mysql database. database is at a remote hosting service. the file is made up of text and script to make database tables etc. i really don't know how to deal with tar files. will i need to use putty to open and separate files? or do i import the whole thing and mysql takes care of it? this is a tar file not a zipped tar.gz file.
Loading Database From .txt File, Please Help! Thanks
I'm very new to mysql and programming in general, just learning the basics, and currently I'm trying to access a database that was formerly on a different server and compressed into a large .txt file, and I also have a version of it in .tar . So the database is on my computer, and I'm just trying to view it through mysql on my machine, windows xp. I have tried the following: c:> "program filesmysqlinmysql.exe" -u root -ppassword < file.txt system unable to find the file I currently have the file in the 'database' folder I created in the mysql directory. should it be somewhere else? should I change its type through properties? If you have any suggestions for me could you please let me know.
Convert CSV File To SQL Database
i have this existing table of four columns and about 64 thousand rows as a csv file. is there a way i can convert these comma separated values to a valid mysql database without spending a fortune.
Saving Database To File
How would you go abouts saving all data in the mysql to a file and download it? I know how to retreive the data and write it (dont know how to make a download button) but how do i record the table data, such as make them inserts like you would in phpmyadmin. Then with a click of the button in mysqlsave.php, it retrieves the information, saves it to mysqlDATA.doc and then downloads it to your hard drive?
Recover Database Without .frm File ?
I have a database table which I wanted to backup. Stupid me, I only backed up the MYD and MYI file. I do not have the FRM file anymore. Is there a way to recover this data ?
Recover Database Without .frm File ?
I have a database table which I wanted to backup. I only backed up the MYD and MYI file. I do not have the FRM file anymore. Is there a way to recover this data ?
Database File Size
I running MySQL locally and have the following dir setup C:wampmysqldatamike - This is where the tables for the database mike are stored C:wampmysqldata - This is where I think the database data is stored. Basically the file in the data dir. (ibdata1) is 10,240kb in size. When I update a table in the mike database the file size of ibdata1 doesn't increase and I don't know why?
File To Database BLOB Question
I have today a database that containes paths (eg. file//K:/folder1/folder2/picture1.gif) to gif images in an directory on my server. Then I'm using a "a href" so the picture opens when clicking on link. Is it possible to make an automation script that copy the gif from it's possition into a field in the database as BLOB?
Import .xls File Into A Mysql Database
I have a .xls file that i would like to import into a mysql database, I am ok creating the mysql table in phpmyadmin on my webspace but i cant work out or find any useful tutorials on how to import the database from the .xls format and get it into the database short of building a form and submitting by hand which would take far to long.
Beginner: How Do I Upload A .sql File To My Database?
I searched about the forum for this, but it only came up with issues of people have problems with uploading a database through phpMyAdmin that was too large. I don't have this problem (I don't think, the file is 7,000 KB), my problem is, I am completely lost in this area. The instruction for the files I have read as: 'Upload the database database.sql to the mysql database.' I presume I use phpMyAdmin for this? So I log into it, I have my fresh database created, I have all my details, dbname dbpass etc at hand... ha... now what? Do I have to upload the .sql file to my webspace through my FTP? Do I need a command line to type into the phpMyAdmin thing? Something else? Anything I have ever used previously, such a forums auto-created the databases for me or did whatever it needed to!
Convert Text File To Database?
I have a text file that looks like this: col1 col2 col3 col4 1 asfd 67867 ghj 2 fghh 12312 3 tyut 90890 fgh But it is over 200k lines. How would you recommend getting it into a MySQL format?
MySQL File / Database Structure
I have a question about the file structure of mysql. Where are the users as e.g. root stored? When I make a fresh install of mysql I have the datamysql database in place then I add a secound database e.g. test. Is the root user stored then in both databases or is he only in the mysql database or somewhere else? And if I change the password for the user root with the cmdline tool mysql is it necessary to select a database before with "use XXX" or not? And if I wanna start from scratch on an running mysql server and therefore delet the datamysql database is the root user lost then and how I recreate all I need then?
Batch FIle To Create Database
I am trying to create a batch file to create a database and populate it with create table statements. I can't figure out why it will not create the database and load the tables. I have the following in my batch file /mysql/bin/mysql.exe -vvv -u root -pcs3911 < C:mySql est.txt and the following is my test.txt file CREATE DATABASE 'lovettsite'; CREATE TABLE `applications` ( `username` varchar(255) NOT NULL default '', `firstname` varchar(255) NOT NULL default '', `middlename` varchar(255) NOT NULL default '', `lastname` varchar(255) NOT NULL default '', `current_address_line_1` varchar(255) NOT NULL default '', `current_address_line_2` varchar(255) NOT NULL default '', `current_city_town` varchar(255) NOT NULL default '', `current_state_province` varchar(255) NOT NULL default '', `current_zip_code` varchar(16) NOT NULL default '', `p.........................................................................
MySQL Database File Location
I have a Windows 2003 Server and have installed MySQL 4.3.13-nt on it. I want to create 3 databases on it called d_one, d_two and d_three etc. My problem is that I want to use separate folders for each database. Let’s say, I want to store the database files for d_one at c:database1 folder, d_two at d:database2 folder and database d_three at e:database3 folder. Is this possible to achieve this? If yes then can I get some guidance on how to go about this.
Upload File Contents To Database Using C API
What I am trying to do is to take the contents of a very large text file and place it into a LONGTEXT field in a database. I am looking for a solution using the C API to help me handle this. The code I use right now is very ineffecient. I basically use a VERY large character array that is like 600,000 characters long and store an UPDATE query to the database in this. I place the contents of the file being uploaded into this query where I SET the LONGTEXT field to the file contents. I know there has to be a much better way of handling this, so if you have any ideas, please let me know.
How Do I Import .csv File Into My Mysql Database?
I woould like to know how to import csv files into my mysql databse, I tried the: LOAD DATA LOCAL INFILE '/importfile.csv' INTO TABLE test_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '' Commands but I dont know where would i put these command and wher do put the "importfile.csv", what directory and location?
How To Dump .sql File Into Database Using Phpmyadmin...
i have dumped my db from my home comp, and saved it in a file, called mydb.sql...no problem. i want to take that file and upload it to my current database on my webserver...i have created a db with the same name as the one on my home comp... but after that, i'm lost. i don't see an 'import' link on phpmyadmin. any ideas?
Accessing Mysql Database From Php File
I can access mysql in dos with the command of c:mysqlinmysql -u root mysql but when i write the command of mysql_connect ("localhost", "username", "password") i don't know what my username or password is. I have installed Apache to run the mysql database and php on my computer although I don't know where to find out the username and password so if someone could help that would be great.
|
TRACKER
