Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have to import data from a MySQL database using phpMyAdmin because that's the only interface my hosting provider supports.

I have exported my database from my local machine using phpMyAdmin. After that I imported the script file to my host. All of data in the columns that is BIT type are changed to '1'. Hosting database version is 5.5.29.

Is it a phpMyAdmin problem, or MySQL version problem? How can I fix this?

share|improve this question
add comment

closed as off-topic by Mark Storey-Smith, RolandoMySQLDBA, Max Vernon, bluefeet, Michael - sqlbot Dec 1 '13 at 23:23

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Mark Storey-Smith, RolandoMySQLDBA, Max Vernon, bluefeet, Michael - sqlbot
If this question can be reworded to fit the rules in the help center, please edit the question.

2 Answers

I think we'll need some more information to pin down your problem. I just tried to recreate your issue and I could not.

I created a table named bitTest with two columns:
textName VARCHAR(5)
bitValue BIT(1)

I inserted two rows into the table using phpMyAdmin's "Insert" feature

before

Then I did a phpMyAdmin "Export" using the "Quick method". The resulting .sql file was

-- phpMyAdmin SQL Dump
-- version 3.4.10.1deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 01, 2013 at 03:34 PM
-- Server version: 5.5.29
-- PHP Version: 5.3.10-1ubuntu3.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `zzz`
--

-- --------------------------------------------------------

--
-- Table structure for table `bitTest`
--

CREATE TABLE IF NOT EXISTS `bitTest` (
  `textName` varchar(5) NOT NULL,
  `bitValue` bit(1) NOT NULL,
  PRIMARY KEY (`textName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `bitTest`
--

INSERT INTO `bitTest` (`textName`, `bitValue`) VALUES
('False', b'0'),
('True', b'1');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Then I dropped the table and used phpMyAdmin's "Import" feature ro re-create and re-populate it using that .sql file. The BIT values were (correctly) imported as 0 and 1.

Perhaps you could update your question with some excerpts from the .sql file you exported to see what differences might exist between your export file and mine.

share|improve this answer
add comment

When you say that all the bit fields are set to 1, I assume you mean all the bit values in the imported row, not that it has somehow affected the whole game including rows that existed before the import.

You don't include any sample roots from the import which would greatly help diagnosis - if there is sensitive information in the data such as real personal details disguise that of course before adding it to your question.

The most likely cause of this on my experience is the values in the import being misinterpreted. I don't have experience of that particular import routine but some expect to find 1 or 0, some expect the text values true/false, some can interpret many values correctly. A common issue with import code though is them doing "if I see 0 then store 0, else store 1" or similar - in that instance a file with booleans listed as the strings true/false will end up with all 1s. This is bad coding of course (it would generally be considered much better to error on values that are not explicitly handled) but I've seen that sort of thing a lot.

tl;dr: check the importer's documentation and verify that your boolean values are formatted as it expects in the file you are giving it.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.