Advertisement:

Author Topic: max_allowed_packets  (Read 11454 times)

Offline Nathan Allen Pinard

  • Semi-Newbie
  • *
  • Posts: 21
max_allowed_packets
« on: December 27, 2007, 12:27:44 PM »
I got:

 Caused the error:

    Got a packet bigger than 'max_allowed_packet' bytes

When converting from IPB to SMF

I understand this error is a result of the settings of the host. I was wondering if there's anywhere around this? I used invision hosting atm, and I'm converting from IPB 2.2 to SMF. I haven't tried yet but I don't know if invision will up the packet size for this.

Offline SleePy

  • Site Team Lead
  • SMF Master
  • *
  • Posts: 30,370
  • Gender: Male
  • Thats his happy face.
    • jdarwood007 on GitHub
    • @jdarwood on Twitter
    • SleePy Code - My personal site
Re: max_allowed_packets
« Reply #1 on: December 27, 2007, 03:12:27 PM »
What part in the conversion? If my memory serves (and it doesn't always) this is a issue with the mysql server locking out the query or php not accepting the large return.
Jeremy D — Site Team / SMF Developer
Support the SMF Support team!
Profiles:
GitHub

Offline Nathan Allen Pinard

  • Semi-Newbie
  • *
  • Posts: 21
Re: max_allowed_packets
« Reply #2 on: December 28, 2007, 12:42:34 PM »
It receives the error when it starts to convert posts. There are fairly large posts on this forum.

Offline Nathan Allen Pinard

  • Semi-Newbie
  • *
  • Posts: 21
Re: max_allowed_packets
« Reply #3 on: December 28, 2007, 12:44:39 PM »
The host is running PhP 5.0 and MySQL 5.0 Would this be a problem?

Offline SleePy

  • Site Team Lead
  • SMF Master
  • *
  • Posts: 30,370
  • Gender: Male
  • Thats his happy face.
    • jdarwood007 on GitHub
    • @jdarwood on Twitter
    • SleePy Code - My personal site
Re: max_allowed_packets
« Reply #4 on: December 29, 2007, 01:02:56 AM »
No there shouldn't be an issue.

The only thing I can think of is a really really big number of posts and it is being denied by mysql.
I did a search and oddly enough got a result from vb actually :P

Anyways, Your host will need to open my.cnf and edit this line and up the packet size. Even if its only temporarily that they will do it. x stands for a number. I would try 16 and if that doesn't work 32 or 64
Code: [Select]
set-variable=max_allowed_packet=xM
Jeremy D — Site Team / SMF Developer
Support the SMF Support team!
Profiles:
GitHub

Offline Nathan Allen Pinard

  • Semi-Newbie
  • *
  • Posts: 21
Re: max_allowed_packets
« Reply #5 on: December 30, 2007, 03:53:18 PM »
Also, for some other reason my current new host I'm trying to convert IPB to SMF, says they can access the database just fine but the convert keeps giving me the SELECT error. They were thinking it could be something with the software, not their database services.

The max_allowed packets error was with my old host I'm trying to get rid of. They won't change the packet size.

Other than using a host, what's another way I can do this? Can I convert on my own machinine locally?

I saw a suggestion for a program, but I can't seem to find it.

Offline SleePy

  • Site Team Lead
  • SMF Master
  • *
  • Posts: 30,370
  • Gender: Male
  • Thats his happy face.
    • jdarwood007 on GitHub
    • @jdarwood on Twitter
    • SleePy Code - My personal site
Re: max_allowed_packets
« Reply #6 on: December 30, 2007, 04:47:48 PM »
Tell your new host this if you are unable to do this.. The $db_user in SMF's Settings.php needs access to both the SMF database and the IPB database.

When the convert.php script runs it uses the user name and password information in SMF's Settings.php to access the SMF and XXX database. It basically setups up selects to match what the SMF tables needs and inserts those. Very neath little method :)
As well, SMF and IPB can be in the same database, as long as they have different prefixes if that helps.

Edit,
As well you could change your Settings.php manually to have $db_user and $db_passwd reflect your cpanel login name. As this account should have access to all databases. Then once you are done you can change this.
« Last Edit: December 30, 2007, 04:50:15 PM by SleePy »
Jeremy D — Site Team / SMF Developer
Support the SMF Support team!
Profiles:
GitHub

Offline Nathan Allen Pinard

  • Semi-Newbie
  • *
  • Posts: 21
Re: max_allowed_packets
« Reply #7 on: December 31, 2007, 04:25:43 PM »
Ok, I finally got past that stage. It turns out that when you transfer another board database from another host, it's config file needs to have the database reflect the NEW database. So if your database on the oldserver was "oldserver_ipb" you would need to change it to "newserver_ipb". Also you would need to change the user names too.

So moving on past that stage, I get this error:

This query:

    SELECT
    SUBSTRING(title, 1, 30) AS title,
    CONCAT(year, '-', month, '-', mday) AS eventDate
    FROM `nathpin1_ipb2`.ibf_calendar_events
    WHERE event_repeat = 1
    AND repeat_unit = 'y'
    LIMIT 0, 500;

Caused the error:

    Table 'nathpin1_ipb2.ibf_calendar_events' doesn't exist

My host says they can't help me, and I would have to contact the developer of the converter.

Offline SleePy

  • Site Team Lead
  • SMF Master
  • *
  • Posts: 30,370
  • Gender: Male
  • Thats his happy face.
    • jdarwood007 on GitHub
    • @jdarwood on Twitter
    • SleePy Code - My personal site
Re: max_allowed_packets
« Reply #8 on: January 01, 2008, 01:18:00 AM »
That error is very basic (and not sure why your host doesn't know what it means).

As it says, that table doesn't exist. Did the import complete correctly?
Jeremy D — Site Team / SMF Developer
Support the SMF Support team!
Profiles:
GitHub

Offline Nathan Allen Pinard

  • Semi-Newbie
  • *
  • Posts: 21
Re: max_allowed_packets
« Reply #9 on: January 01, 2008, 02:24:42 PM »
You mean import the database? I could probably try it myself again.

To check: You just export from old host, then import to new via phpmyadmin correct?

Offline SleePy

  • Site Team Lead
  • SMF Master
  • *
  • Posts: 30,370
  • Gender: Male
  • Thats his happy face.
    • jdarwood007 on GitHub
    • @jdarwood on Twitter
    • SleePy Code - My personal site
Re: max_allowed_packets
« Reply #10 on: January 01, 2008, 02:40:48 PM »
Yes that is the method, the error is saying the table can't be found in the IPB database. So either its a version mismatch (converter isn't made for your version of IPB) or some reason that table isn't be exported by your old host.
Jeremy D — Site Team / SMF Developer
Support the SMF Support team!
Profiles:
GitHub

Offline Nathan Allen Pinard

  • Semi-Newbie
  • *
  • Posts: 21
Re: max_allowed_packets
« Reply #11 on: January 01, 2008, 03:04:16 PM »
Hmmm... well I DL's the 2.x version, and I have 2.2

Offline Nathan Allen Pinard

  • Semi-Newbie
  • *
  • Posts: 21
Re: max_allowed_packets
« Reply #12 on: January 01, 2008, 06:22:02 PM »
Ok, the 2.2 sql file worked, but now I run into that "My SQL syntax error"

So I do exactly what it says in the sticky, but when I upload and then run the converter, it still reflects the old code for some reason. I've refreshed, cleared my cache, tried a different browser, etc.

Can't get it to change.

Here's the error:

This query:

    ALTER TABLE `nathpin1_ipb2`.smf_smileys
    ORDER BY LENGTH(code) DESC;

Caused the error:

    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 'LENGTH(code) DESC' at line 2

You see that ORDER BY LENGTH? I changed it to the other specified code. I even redownloaded the conver.php to check and see if the changes actually took place, and they did.

There is only one instance in the file where this code resides, and that has been changed to the specified code.

Perhaps there's another file I'm missing that has this. It says "line 2" but it's far down the document.


Offline SleePy

  • Site Team Lead
  • SMF Master
  • *
  • Posts: 30,370
  • Gender: Male
  • Thats his happy face.
    • jdarwood007 on GitHub
    • @jdarwood on Twitter
    • SleePy Code - My personal site
Re: max_allowed_packets
« Reply #13 on: January 02, 2008, 12:39:19 AM »
It also might exist  in the convert file
Jeremy D — Site Team / SMF Developer
Support the SMF Support team!
Profiles:
GitHub

Offline Nathan Allen Pinard

  • Semi-Newbie
  • *
  • Posts: 21
Re: max_allowed_packets
« Reply #14 on: January 02, 2008, 12:59:36 AM »
That's what the sticky says to edit, the convert.php.

I couldn't find it in any other film. Unless it's deep in the file hierarchy.

Offline SleePy

  • Site Team Lead
  • SMF Master
  • *
  • Posts: 30,370
  • Gender: Male
  • Thats his happy face.
    • jdarwood007 on GitHub
    • @jdarwood on Twitter
    • SleePy Code - My personal site
Re: max_allowed_packets
« Reply #15 on: January 02, 2008, 01:02:50 AM »
Can you attach the edited files?
Jeremy D — Site Team / SMF Developer
Support the SMF Support team!
Profiles:
GitHub

Offline Nathan Allen Pinard

  • Semi-Newbie
  • *
  • Posts: 21
Re: max_allowed_packets
« Reply #16 on: January 02, 2008, 01:30:32 AM »
Sure. Attached: convert.php


Offline SleePy

  • Site Team Lead
  • SMF Master
  • *
  • Posts: 30,370
  • Gender: Male
  • Thats his happy face.
    • jdarwood007 on GitHub
    • @jdarwood on Twitter
    • SleePy Code - My personal site
Re: max_allowed_packets
« Reply #17 on: January 02, 2008, 01:44:11 AM »
There is one in the .sql file:

Code: [Select]
ALTER TABLE {$to_prefix}smileys
ORDER BY LENGTH(code) DESC;

About lines 879-880
Jeremy D — Site Team / SMF Developer
Support the SMF Support team!
Profiles:
GitHub

Offline Nathan Allen Pinard

  • Semi-Newbie
  • *
  • Posts: 21
Re: max_allowed_packets
« Reply #18 on: January 02, 2008, 01:58:26 AM »
I see it, but the fix didn\'t work for some reason. Plus the fix stickied doesn\'t say to go into the .sql file.

Hmmmm.


The code in the .sql file is now this:


if (!empty($rows))
   convert_query(\"
      REPLACE INTO {$to_prefix}smileys
         (code, filename, description, smileyOrder)
      VALUES (\" . implode(\"),
         (\", $rows) . \")\");
---}

ALTER TABLE {$to_prefix}smileys
ORDER BY code DESC\");

/******************************************************************************/
--- Converting settings...
/******************************************************************************/

There must be something I can change to fix this. I actually removed the "---]" and is "sucessfully converted with about 9 errors. I'm getting close.

Wonder why this is so difficult.

EDIT: Suprisingly it finished the conversion. Posts are on there, but i'll have to see if anything is missing that is important.

« Last Edit: January 02, 2008, 02:05:30 AM by Nathan Allen Pinard »

Offline JayBachatero

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 19,562
  • Gender: Male
    • @jaycreations on Twitter
    • JayBachatero.com
Re: max_allowed_packets
« Reply #19 on: January 24, 2008, 04:59:36 AM »
You need the ---} in there.  ---{ and ---} are used for PHP blocks.  ---* ---* are used for SELECT blocks.
Follow me on Twitter

"HELP!!! I've fallen and I can't get up"
This moment has been brought to you by LifeAlert