MYSQL :: Set Commit When Loading Data?

Loading Data
To log onto mysql client server I entered
mysql -h linuxproj -u myusername -p --local-infile=1

I was able to get into mysql with no problem after entering my password. Then to load the data from a .txt file stored on my computer I entered

LOAD DATA LOCAL INFILE 'C:/Program Files......../file.txt INTO TABLE Tablename FIELDS TERMINATED BY ',' LINES TERMINATED BY '
';

I got the error 'C:/Program Files....../file.txt'- Error 2 is not found. I dont know why.

Loading Data
how should my data format look like to load it to MySQL table?
Where should field name stand and where the records, what are deviders, which format

Loading Binary Data
I have a large data set of binary data files and I'd like to create a database so that I can search on the data contained in these files. I'm new to database development but from what I've seen it is only possible to store & search variables in the database in text format. Is it possible to load the files as binary variables and still use MYSQL commands to search and manipulate the database?

For example, assume a file contains a variable X which is a 32-bit floating point number ranging between 0 and 10. Does the variable X have to be converted to text first (possibly using a C program), stored into another text file, which is then loaded into the database/table? Or is it possible to load the 32-bit floating point number directly from the original binary file and perform a MYSQL query looking for instances of variable X with values 1.2 and 4.8?

Loading Data Into A Table
I have to load some data into a table (several tables actually). I obviously don't want to/can't insert the records one-by-one. I'm running mySQL versoin 4.0.13. Apparently I can't use the 'LOAD DATA LOCAL INFILE' with this version. Surely there should be another way of doing it?

Fail Loading Data
I have a file creted using notepad where fields are separated by tabs. I try to load it using LOAD DATA LOCAL INFILE '/../name.txt' INTO TABLE mytabke

I get the following message:

Data truncated for column 'column name' ay row 1 error 1265

and this is repeated for al columns in the windows file.

Bulk Loading Data
I want to insert massive amounts of data in near real-time into a MySQL database. The data is in a structured binary format, and I have code that parses the data into logical data structures.

Right now I can load into a MySQL database via ODBC, but I need to improve throughput. How can I bulk insert the data more efficiently?

I would prefer not to use LOAD FILE because it involves converting the binary format to text, which will have a much larger footprint. To illustrate the pain of text files in my application, a single binary file might contain up to a million rows! And I might load hundreds, if not thousands of these files in a day!

How can I load data directly into the database at a lower more direct level than LOAD FILE? What are my options to integrate my data parser with MySQL?

Reduce Data Loading Time
At present i am working with a database that contains all countries and their cities. My database is in mysql. The city table contains 3079703 cities.

When a user selects a country i need to populate the city dropdown for that country. I use Ajax technology to retrieve city from database based on the selected country.

But I am facing a problem with the speed as it takes a long time to retrieve city data and return generated dropdown control from server using php.

I have already tried to use filebased caching (transferring all the city data into separate files for each state and country) and then tried to load from these files but didn't gain any significant speed improvement. I also then tried using indexing in city table on country code and state code but it still takes time to load.

Could someone please give me any techniques or optimisation tips so that i can load the cities within a short time.

Data Loading With Foreign Key Contraint
a question regarding adding a record into a database that has foreign key constraints. Remember, I'm a total newbie so I'm hoping I'm using the right words to express this. I'm taking a class that required us to use an ER diagramming tool. This tool generates SQL table create scripts in mySQL. After a little tweaking I got the scripts to work. An example is as follows:

# Create Table : 'Jobdesc' Job Description for Requisition
# desccode :
# jobdescription :
# levelcode : (references JobCode.levelcode)
# jobcode1 : (references JobCode.jobcode)
# jobcode :
# titlecode : (references JobCode.titlecode)
#
CREATE TABLE Jobdesc (
desccode CHAR(8) NOT NULL UNIQUE,
jobdescription MEDIUMTEXT NOT NULL,
levelcode CHAR(2) NOT NULL,
jobcode1 CHAR(8) NOT NULL,
jobcode CHAR(8) NOT NULL,
titlecode CHAR(7) NOT NULL,
PRIMARY KEY (desccode,jobcode),
INDEX idx_fk_Jobdesc (titlecode,jobcode1,levelcode),
CONSTRAINT fk_Jobdesc FOREIGN KEY (titlecode,jobcode1,levelcode)
REFERENCES JobCode (titlecode,jobcode,levelcode)
ON DELETE CASCADE
ON UPDATE CASCADE) TYPE=INNODB;

This is a create script for a job description table. Job descriptions are related to a jobcode table. That table in turn is related to joblevel and jobtitle tables (i.e., the job title and job level determine the job code). The jobcode is needed for each job description.

One problem I have is that the create scripts generated from the ER tool makes all fields in the job description entity NOT NULL. If I try to just enter the description code (desccode), the job description (jobdescription) and the associated job code (jobcode) I get the following error:

#1216 - Cannot add or update a child row: a foreign key constraint fails

This happens if I just try to enter the desccode, the jobdescription, and jobcode datta. I think this is happening because jobcode1, levelcode, and titlecode are NOT NULL so when I update the record it attempts to enter data (NULL) into these fields which are child row. Can someone explain what I should do? Should I just change these fields of data into NULL?

Loading A Database Table With Data From PHP
I tried making a php file that updates my database when it's executed from a browser... But the table doesn't get loaded properly with the data. I can't get it to work. Here's my code:

Loading Data & Null Columns
Having used Oracle's SQL*Ldr for over a decade now, I'm puzzled about the treatment of null values when using mysql's LOAD DATA INFILE syntax. This is the describe of the table being loaded: Code:

Loading New Data By Replacing Files...
I regularly have to update the contents of my database which serves a a placeholder for the information of my website. What I do is that I work on my local PC where I have the same database installed, update this very one, and then try to update the database on the remote server by replacing the tables' files directly in the folder of my database.
I chose this because I need to update all the information at once, as my web site is accesible all through the process of updating the database.

Thing is the new data is not taken into account if I do not restart the WHOLE SERVER!! It is not enough to just restart mysql...

How could I do this properly?

Data Structure Vs Table Loading
I have just started designing database driven websites with MySQL and I would like to know if there is likely to be any performance issues if lots of pages share records from a single table.

I want to design a simple website where the contents are derived from two tables "PageDetails" and "contentDetails". The page details will contain all page related info that occurs only once (titles, meta tags etc) and the contents page will contain all the repeating "block" on each page.

I am used to designing relational databses for use on a LAN. when I do this I always aim to normalise the design as much as possible for ease of data entry and design maintenance.

When I design on a LAN I have a good idea how many concurrent users there are likely to be and how loaded my database tables will be.

My concern is, if I use this approach online, the resources of one table will be shared by all pages and all users and I have no idea how many concurrent users there will be on this site but it could be potentially 200. Will there be a noticable performance degredation?

The alternative would be to create a pair of tables per page. This would mean any one table would have much less loading, but it would be a maintenance nightmare.

Loading 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.
%%

Loading Data Results In NULL Rows??
I am using LOAD DATA very simply but it is resulting in all the rows being NULL and I can't understand why.

Here is what I'm doing:
LOAD DATA INFILE '/var/www/Data/testfile.csv'
INTO TABLE trial.testdb
FIELDS TERMINATED BY 'Â¿'
LINES TERMINATED BY '
'
IGNORE 2 LINES;

The command runs successful but when I check the data, the first column which is a date field, comes up at 0000-00-00 and all other columns are loaded as NULL.

Any clues on why this is happening? The # of columns in the file match up with what's in my table.

Loading Data Into A Table From Excel Spreadsheet
I have a excel spreadsheet from which I would like to load data into a table in mysql.

Loading Data Into Tables Using The Load Command
I have been trying to load data from a notepad into a table but iam finding it very difficult.Can someone please tell me what i need to o.i.e specify the path, the command for a windows os and if i need to change a config for this to work.

Loading Lots Of Data Into Mysql Table
So I need to load lots of data into my database.

So I discover LOAD DATA INFILE.

Great! This little gem loads my CSV in blazing times (compared to
parsing the file and doing INSERT for each row). Its still slow on
large files, but just barely acceptable.

Only one problem. It truncates fields to 256 characters, even on a
text field.

Loading Data In Tables With Foreign Keys
It's sample database contains tables with foreign keys.

When I created the tables (with Type = InnoDB), I did so without creating the foreign keys and then went back and did "alter tables" to add them. That process seemed to work perfectly.

However, with the foreign keys added, I CANNOT LOAD DATA INTO THE TABLES. I have used the MySQL Control Center's insert row feature, the load data file SQL command and the tedious SQL insert command to insert one table row at a time. Nothing works. I get error messages about "cannot create or modify a child".

I have tried deleting the foreign keys. I found that the system will only allow me to delete them on some tables. On the tables where I can delete the foreigh keys, I can bulk load the data using the load file command but then, after data exists in the table, I cannot restore the foreign key.

UNLOCK Before COMMIT
SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

In theory, if instead of the recommended pattern above, you were to switch the order of the COMMIT and UNLOCK TABLES statement, what would happen if another statement altered something in the locked tables?

SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
UNLOCK TABLES;
... ***another statement makes a change here***
ROLLBACK; ***rollback instead of commit

Would the changes from the other table be rolled back, or would only those changes explicitly made by this statement be rolled back? Could anybody help to clarify the principle (in terms of locking or isolation levels) to explain what's actually going on in that case?

Commit Or Rollback?
I'm working with tables stored by the InnoDB engine and would like to
be able to commit only if there are no errors generated by a group of
statements like this.

/* -*- sql -*- */
SET AUTOCOMMIT=0;
use db1;
begin work;
sql statement 1;
sql statement 2;
..
..
..
sql statement n;

At this point I'd like to say, in sql,
if no errors then
commit;
else
rollback
end

From what I read in the manual I can do one or the other (commit or
rollback) but there didn't seem to be a way of conditionally doing one
or the other of them.

Commit Rollback
I have to copy my local database to my global database, if its done the local database must be erased and if there is a problem it should not copy anything. So I've heard that I have to use the actions commit and rollback but I don't understand how they work,

MyISAM Commit And Rollback
I was reading the MySQLmanual and became confused about myISAM and
commit/rollback.

Can someone explain what I have to do in my queries to garuanty the
dataintegrity of my db in case I use myISAM? Do I have to use the commit and
rollback statements or is this done by MySQL self?

COMMIT And ROLLBACK With C API Mysql_query()
I:m just about to wrap my update queries in transactions and want to confirm something.

I:m using the C API mysql_query() function with the connection opened in CLIENT_MULTI_STATEMENTS mode.

Do I decide to COMMIT or ROLLBACK based on mysql_next_result(), mysql_store_result() and mysql_errno() checks like this:

The query string basically amounts to something like this:

START TRANSACTION;
INSERT something somewhere;
DELETE something else;
UPDATE yet another thing;
etc

... and this is sent to the server, >without< a COMMIT. Notice there are no SELECTs so no data is returned. Is the following code the right way to proceed, only sending the COMIT or ROLLBACK after everything has been checked?

'____int action = commit; // default
'____if (mysql_query(mysql_handle, query_string) != 0)
'____{
'________// Error in the very first statement
'________action = rollback;
'____}
'____else
'____{
'________do
'________{
'____________// Make sure each command in the SQL query string passed
'____________if ((mysql_result = mysql_store_result(mysql_handle)) == NULL)
'____________{
'________________if (mysql_errno(mysql_handle) != 0)
'________________{
'____________________// Something went wrong - rollback
'____________________action=rollback;
'____________________break;
'________________}
'____________}
'____________else
'____________{ // This wont happen for just UPDATEs and INSERTs
'________________mysql_free_result( mysql_result );
'____________}
'____________next_results_status = mysql_next_result( mysql_handle );
'________} while (next_results_status == 0);
'________
'________// Should fall out the do loop without an error
'________if ( next_result_status > 0 )
'____________action = rollback;
'____}
'____
'____// Do whichever action is appropriate
'____mysql_query( mysql_handle, (action==commit) ? "COMMIT" : "ROLLBACK" );

Check Foreign Keys At Commit
Is it possible, in mysql 4.1.8 using InnoDB backend, to check foreign key constraints when commiting?

Does MYSQL Support Transactions Like Rollback And Commit Now?
Does MYSQL support transactions like rollback and commit now? How about
stored procedures and subqueries?

Loading A New DB
I'm trying to populate a new copy of my database from the dump script taken from an overnight backup. It starts out OK but then errors with: ERROR 1060 (42S21) at line nnn: Duplicate column name 'id'.
Taking a look at the script I can see that the previous couple of tables have loaded without problem but the offending CREATE TABLE looks like this:

CREATE TABLE xxx (id int(11) NOT NULL auto_increment, <other columns...> ,PRIMARY KEY (id,id)) ...

The id column is only defined once but is duplicated in the PRIMARY KEY definition - strange! Looking through the script I see this is the case on some of the other CREATE TABLE statements also

Loading Blobs
does anyone have a perl example of loading blobs from either a bin file
or an input stream?

Loading A Database
I try to load a new database like this:

mysql> mysql < databasenamedb_2006-06-29.Thursday.sql

but then I get:
mysql> mysql < databasenamedb_2006-06-29.Thursday.sql
---> ...........

so a prompt. What is that prompt wanting? A username or a password?
What should I enter.

And in order to create a new database like this, should I do something else first? Like adding the database with "create databasenamedb;"?

Databases Are Not Loading
I am using Mysql 4.0.18. I load my database (tele) in "data" directory at c:mysql. The first time, I opened WinMySQLadmin and 3 databases appeared: test,mysql and tele. It was Ok. Then I restarted my computer and I went to WinMySQLadmin again and didn't appear mysql and tele database. What I am doing wrong or what is the problem?

Load_file() Not Loading
I'm trying to load a picture into an image database. On my old server it worked fine and on this new one it returns null all the time. Let's say I try:

UPDATE tbl_1 SET blob_col=LOAD_FILE("/tmp/picture.jpg") WHERE id=1;

I get a null even though I should have permissions, the file exists, and the max_allowed_packet is set high enough. The file is on the server and I'm typing into the MySQL command line but nothing. All other UPDATE statements work fine. Just the load_file doesn't work. What am I doing wrong?

Loading Schema
I've been importing a database from a MSSQL server - not massive, 146 tables, about innodb_buffer_size 20GB total data and everything is fine with running queries - performance is good considering it's on a dev box at the moment.

Problem I have though is when you are using phpmyadmin or the mysql administrator it takes an age to load table data (in mysql admin it says 'Fetching schema table status').

These are innodb tables (I'm concerned myisam won't cope with us - lots of small updates and big slow queries) and if I convert the tables to myisam the problem goes away - loads fast as you like.

Is this a problem inherent to innodb or is there a configuration to get around it?

I'm running with default my.ini atm with the exception of innodb_buffer_pool_size=500M.

Loading A Database
I am running MySQL 3.23.46 on my laptop (Win XP), and wondered how I go about loading a database schema?Can this be done from the command prompt for instance, or is it easier to do so using PHP? I have PHP 4 loaded.

Loading Many Files
Say i have a database that has 3 varchar fields and then a blob field. I can easily fill the first 3 fields with a tab delimited file. However how can i load multiple files into the blob field. Say i have 700 records- i can't do it by typing out the command over and over again. Is there a way to write a file specifying all the files that need to be loaded and then feed it mysql like i can do with a tab delimited file? Or is there another way?

Loading Rate
I'm working on a project where we need to load several 10s of
thousands of rows per second (roughly 50 bytes/row) into MySQL tables.
We're using LOAD DATA INFILE, which works fine. The problem is, that
we have to write this data from our application into ASCII files so
load data infile can read them. The bottleneck is the writing of the
data. We were thinking that if we could write the data in binary
format, the writing might be quicker. Is there a way to load binary
data into mysql tables with something akin to load data infile?

Loading Text
a tab delimited file like this:

name is varchar (60)
bio is TEXT data type

name<tab>bio
Jack<tab>name: My name is Jack
car: I have a chevy car
pet: My pet's named Fido

Jane<tab>name: My name's Jane
car: I have a VW car
pet: I don't have a pet

How do I LOAD that into MYSQL ?I want name field and the a text bio field but how do I get it in a table assuming I have line breaks...I'm willing to globally edit the file if needed

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.

Error Loading Mod_auth_mysql
I am running mysql-3.23.52 on OS X (10.2.6) and I'd like to install the
mod_auth_mysql module for a job I'm working on that used it under a
different platform.

I compiled the module using:

apxs -c -D APACHE1 -L/usr/local/mysql/lib/ -I/usr/include/mysql/
-lmysqlclient -lm -lz mod_auth_mysql.c

and installed it:

sudo apxs -i mod_auth_mysql.so

both of which seemed to go fine. In the httpd.conf file I have put the two
lines:

LoadModule mysql_auth_module libexec/httpd/mod_auth_mysql.so

AddModule mod_auth_mysql.c

but when I try to do a graceful restart of apache I get the following response:

% sudo apachectl graceful
/usr/sbin/apachectl graceful: configuration broken, ignoring restart
/usr/sbin/apachectl graceful: (run 'apachectl configtest' for details)
% sudo apachectl configtest
Syntax error on line 241 of /etc/httpd/httpd.conf:
Cannot load /usr/libexec/httpd/libphp4.so into server: (reason unknown)

Is there a particular order that this had to go in the httpd.conf file? I
can't understand why it is complaining about libphp4.