SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Addict
    Join Date
    Oct 2010
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    why my sql table crash

    i m facing some problem from 2 days my table which has round about 1 million colums and 10 rows using mysql
    go crash i repaired it but after 1 day it go again crash to again have fix that but what's the real problem that how can i know how can i stop that from being crashed

  2. #2
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Crash is quite a word. Maybe you should explain what's happening!

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    1 million columns? no wonder it crashed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    1 million columns? no wonder it crashed
    I wonder how the INSERT query looks like

  5. #5
    SitePoint Addict
    Join Date
    Oct 2010
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    1 million columns? no wonder it crashed
    don't understand what's the solution if it do next time

  6. #6
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by realcoder View Post
    don't understand what's the solution if it do next time
    Like I asked before, can you explain what is happening. Is it happening when you query the database? Or what??

  7. #7
    SitePoint Addict
    Join Date
    Oct 2010
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ohh sorry little wrong statement
    1 million rows not colums 10 colums

  8. #8
    SitePoint Addict
    Join Date
    Oct 2010
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well in mysql_error i got statment ur table crashed repair that when i repair that work

  9. #9
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by realcoder View Post
    well in mysql_error i got statment ur table crashed repair that when i repair that work
    ... but when. Where is this happening. On your website while doing a search? or? The information you provide is very little to do something with.

  10. #10
    SitePoint Addict
    Join Date
    Oct 2010
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well during inserting some data as i already describe in 10 rows data is not much big

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    could we clear something up please?

    realcoder, please do a SHOW CREATE TABLE for your table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you post the show create table and the query this is happening with

    Edit: sorry had the post still open and didn't see Rudi's reply

  13. #13
    SitePoint Addict
    Join Date
    Oct 2010
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it's structure look like

    CREATE TABLE `posts` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `aid` int(11) NOT NULL,
    `name` varchar(250) NOT NULL,
    `email` varchar(250) NOT NULL,
    `subject` varchar(250) NOT NULL,
    `message` mediumtext NOT NULL,
    `cap` varchar(250) NOT NULL,
    `ip` varchar(250) NOT NULL,
    `date` varchar(250) NOT NULL,
    `time` varchar(250) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=57 DEFAULT CHARSET=latin1

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by realcoder View Post
    it's structure look like
    i've seen worse

    how about the query that's causing the crash?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is this the real table or a test table? I only see AUTO_INCREMENT=57 while you were talking about 1.000,000 rows.

    Off Topic:


    I couldn't help but notice the use of MyISAM. Where INNODB would be more appropriate, unless you use fulltext search

  16. #16
    SitePoint Addict
    Join Date
    Oct 2010
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    its real one that was from my local host

    Code SQL:
    CREATE TABLE `posts` (
     `id` INT(11) NOT NULL AUTO_INCREMENT,
     `aid` INT(11) NOT NULL,
     `name` VARCHAR(250) NOT NULL,
     `email` VARCHAR(250) NOT NULL,
     `subject` VARCHAR(250) NOT NULL,
     `message` mediumtext NOT NULL,
     `cap` VARCHAR(250) NOT NULL,
     `ip` VARCHAR(250) NOT NULL,
     `date` VARCHAR(250) NOT NULL,
     `time` VARCHAR(250) NOT NULL,
     PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=1021679 DEFAULT CHARSET=latin1



    and it's query
    Code SQL:
    INSERT INTO posts (aid,name,email,subject,message,cap,ip,DATE,TIME) VALUES ('$id','$name','$email','$subject','$message','$cap','$ip','$date','$time')
    Last edited by SpacePhoenix; Dec 18, 2010 at 01:39. Reason: placed sql syntax highlighting tags around the sql

  17. #17
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What is happening with the aid?

    Code MySQL:
    `aid` int(11) NOT NULL,

  18. #18
    SitePoint Addict
    Join Date
    Oct 2010
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    getting id of some form

  19. #19
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But you're not inserting it? You really need to be more specific please. Try to supply all details not just half.

  20. #20
    SitePoint Addict
    Join Date
    Oct 2010
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    inserting as u can see $id
    and there is not any problem related to query execute problem is table crashed not all time
    Why that Crashed?

  21. #21
    SitePoint Addict
    Join Date
    Oct 2010
    Posts
    207
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    any reply or suggestion on it ?

  22. #22
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The reason behind crashing of sql table can be corruption. So, it is advisable to go for mysql repair tool that is especially meant to resolve all the corruption issues existing within mysql database by following only few steps.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •