Help building database

[mexaros]

I am relatively new to mysql. I am updating a site for a customer, and I'm using mysql 5 w/ phpMyAdmin. I was supplied an .sql (echeck.sql) script to build the database for this site. I create a database named "echeck" in phpMyAdmin, and once created go to the import tab of phpMyAdmin to import the .sql script...when i click the "go" button i recieve this:

Error

SQL query:

--
-- Dumping data for table `tbl_tax`
--
INSERT INTO `tbl_tax` ( `taxid` , `taxdesc1` , `taxper1` , `taxst1` , `taxdesc2` , `taxper2` , `taxst2` , `taxdesc3` , `taxper3` , `taxst3` , `taxdesc4` , `taxamt1` , `taxst4` , `taxdesc5` , `taxamt2` , `taxst5` , `tax_prt_id` )
VALUES ( 1, 'tax1', 0.00, '1', '', 0.00, '', 'tax2', 0.00, '', 'tax4', 0.00, '1', '', 0.00, '', 26 ) , ( 2, 'taxa', 2.00, '1', 'taxb', 2.00, '1', 'taxc', 2.00, '1', 'taxd', 23.00, '1', 'taxE', 2.00, '1', 52 ) ;

MySQL said: Documentation
#1265 - Data truncated for column 'taxst2' at row 1

The script apparently kicks out and not all the tables are created. Does anyone know the cause of this? should i not be using import to run the script? if so how do i run it then?

[mwasif]

It looks like a bug. Here is a bug report .

What is your MySQL version?

You can also use command line utility .

[mexaros]

I am running version 5

[mexaros]

from what i read, it looks like this .sql file that i have is the dump file created from mysql dump? is this correct? If so, how do I now restore the database using this file? ie: from command line, what would the command be?

[mwasif]

The command will be

mysql databasename < echeck.sql -uMYSQL-USERNAME -p

[mexaros]

ok, so here is what i type:
mysql echeck < c:/Program Files/Apache Software Foundation/Apache2.2/htdocs/admin/echeck.sql -uMYSQL-ROOT -p

where "root" is a valid user in my mysql. I recieve the error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql echeck < c:/Program Files/Apache Software Foundation/Apache2.2/htdocs/admin' at line 1
What am i doing wrong?

[trudge]

ok, so here is what i type: mysql echeck < c:/Program Files/Apache Software Foundation/Apache2.2/htdocs/admin/echeck.sql -uMYSQL-ROOT -p

where "root" is a valid user in my mysql. I recieve the error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql echeck < c:/Program Files/Apache Software Foundation/Apache2.2/htdocs/admin' at line 1 What am i doing wrong?

You said 'root' is a valid user, but you use 'MYSQL-ROOT' in the command. Is this what you intended?

[mwasif]

Kindly replace MYSQL-USERNAME with root.

[mexaros]

ok so i got the syntax correct and it started to run the .sql but i am still getting this error:
ERROR 1265 (01000) at line 894 in file: 'c:\echeck.sql' : Data truncated for column 'taxst2' at row 1

In the echeck.sql, under the create table directive, taxst2 is defined as:
`taxst2` enum('0','1') NOT NULL default '0',

and line 894 in the file is as follows
INSERT INTO `tbl_tax` (`taxid`, `taxdesc1`, `taxper1`, `taxst1`, `taxdesc2`, `taxper2`, `taxst2`, `taxdesc3`, `taxper3`, `taxst3`, `taxdesc4`, `taxamt1`, `taxst4`, `taxdesc5`, `taxamt2`, `taxst5`, `tax_prt_id`) VALUES
(1, 'tax1', 0.00, '1', '', 0.00, '', 'tax2', 0.00, '', 'tax4', 0.00, '1', '', 0.00, '', 26),
(2, 'taxa', 2.00, '1', 'taxb', 2.00, '1', 'taxc', 2.00, '1', 'taxd', 23.00, '1', 'taxE', 2.00, '1', 52);

Where is the problem? How do I correct it?

[mwasif]

It seems to be a MySQL bug like this http://bugs.mysql.com/bug.php?id=9029 .

Do you have latest MySQL version?

Also try source command.

[mwasif]

Did you execute these commands in phpmyadmin?

[trudge]

I've run into similar problems, and sometimes the culprit is a value before the indicated one.

I notice some anomalies with your INSERT statement.

First are those really back-ticks surrounding the table name and field names? Why do you need them? What database are you using?

Second, there seems to be a mis-match in the number of fields. You have 17 in the 'INSERT INTO' section, but it looks like you are trying to insert more fields. If you look at your data, you will see that you first try to insert only 12 fields, then another insert of 17. The data may be corrupt at that point.

And why are you double-quoting what appear to be numeric data? The value just before the 'tax2st' value is

'', 0.00, '',

[mexaros]

I am relatively new to mysql. I am running 5.0, and updating a site for someone. There was a file called echeck.sql which i was to run in order to automate a build of the database and tables. However i get this error when i try to run or import the table:
SQL query:

-- 
-- Dumping data for table `tbl_tax`
-- 
INSERT INTO `tbl_tax` ( `taxid` , `taxdesc1` , `taxper1` , `taxst1` , `taxdesc2` , `taxper2` , `taxst2` , `taxdesc3` , `taxper3` , `taxst3` , `taxdesc4` , `taxamt1` , `taxst4` , `taxdesc5` , `taxamt2` , `taxst5` , `tax_prt_id` )
VALUES ( 1, 'tax1', 0.00, '1', '', 0.00, '', 'tax2', 0.00, '', 'tax4', 0.00, '1', '', 0.00, '', 26 ) , ( 2, 'taxa', 2.00, '1', 'taxb', 2.00, '1', 'taxc', 2.00, '1', 'taxd', 23.00, '1', 'taxE', 2.00, '1', 52 ) ;

MySQL said: Documentation
#1265 - Data truncated for column 'taxst2' at row 1

The build kicks out and i am left with a partial database. I was told that this is due to a bug in mysql5. how do I correct it? I attempted the solution in which you download the source tree from bitkeeper. However I am running windows which so I then needed to download cygwin. I followed all the steps supplied on: http://dev.mysql.com/doc/refman/5.0/en/installing-source-tree.html
but when i get to the point where i need to issue the command:
shell> cd mysql-5.0
shell> (cd bdb/dist; sh s_all)
shell> (cd innobase; autoreconf --force --install)
shell> autoreconf --force --install
shell> ./configure # Add your favorite options here
shell> make
I recieve an error stating: bash: autoreconf: Command not found.

So i have 2 questions. 1. How do i correct this issue w/ attempting to install the database.
and 2.Why does bash not recognize the autoreconf command?

HEEEEEEEEEEEEELLLLLLLLLLP!!!!!

[trudge]

And again, I am suggesting you look at the raw data. You have double quotes around part of the VALUE data:
VALUES ( 1, 'tax1', 0.00, '1', '', 0.00, '', 'tax2', 0.00, ''
I'm quite certain that is where the problem is.

[mexaros]

Sorry, the font makes it appear that way. Those are single quotes...as in:
'1', ' ', 0.00, ' ', 'tax2'
there is a space there, it just doesnt look like it. I am running mysql 5.0
here is the full statement:

CREATE TABLE `tbl_tax` (
  `taxid` bigint(50) NOT NULL auto_increment,
  `taxdesc1` varchar(100) NOT NULL,
  `taxper1` float(8,2) NOT NULL,
  `taxst1` enum('0','1') NOT NULL default '0',
  `taxdesc2` varchar(100) NOT NULL,
  `taxper2` float(8,2) NOT NULL,
  `taxst2` enum('0','1') NOT NULL default '0',
  `taxdesc3` varchar(100) NOT NULL,
  `taxper3` float(8,2) NOT NULL,
  `taxst3` enum('0','1') NOT NULL default '0',
  `taxdesc4` varchar(100) NOT NULL,
  `taxamt1` float(8,2) NOT NULL,
  `taxst4` enum('0','1') NOT NULL default '0',
  `taxdesc5` varchar(100) NOT NULL,
  `taxamt2` float(8,2) NOT NULL,
  `taxst5` enum('0','1') NOT NULL default '0',
  `tax_prt_id` int(50) NOT NULL,
  PRIMARY KEY  (`taxid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

-- 
-- Dumping data for table `tbl_tax`
-- 

INSERT INTO `tbl_tax` (`taxid`, `taxdesc1`, `taxper1`, `taxst1`, `taxdesc2`, `taxper2`, `taxst2`, `taxdesc3`, `taxper3`, `taxst3`, `taxdesc4`, `taxamt1`, `taxst4`, `taxdesc5`, `taxamt2`, `taxst5`, `tax_prt_id`) VALUES 
(1, 'tax1', 0.00, '1', '', 0.00, '', 'tax2', 0.00, '', 'tax4', 0.00, '1', '', 0.00, '', 26),
(2, 'taxa', 2.00, '1', 'taxb', 2.00, '1', 'taxc', 2.00, '1', 'taxd', 23.00, '1', 'taxE', 2.00, '1', 52);

Does this look correct? The reason I am not understanding what the issue is, is that this file was created via mysqldump...this table did at one point run properly from a db, but once i try and load this dump file back into a db im getting this issue.

[mexaros]

I could see where the font can play games, it appeared as though i was only inserting 12 objects but , '', was one of the 17 inserts

[trudge]

Then you are trying to insert empty data into 5 fields which are defined as NOT NULL. There seems to be some data missing.

For some debugging:
Make a copy of your original .sql file and work with the copy.

Try pulling that line out of the copied .sql file and save it into a separate file.

Delete it from the copied .sql file.

Then try running your command again on this .sql file. If all the INSERTS are OK, then you know that it is that line that is causing the problem.

[trudge]

Just a caution. If you've already successfully INSERTED some records, before you try another one, you may have to empty the table, otherwise you will get duplicated data.

[mwasif]

trudge is right. Actualy your MySQL is running in strict mode. Either resolve the issues which trudge has mentioned or change sql-mode.

To change sql-mode, either change the value of sql_mode to empty or ANSI in my.cnf or by executing the query for a session i.e.

SET sql_mode = '';

[mexaros]

Thanks to both trudge and mwasif. this helped correct the issue