Welcome to Geeklog, Anonymous Monday, April 22 2024 @ 03:19 am EDT

Geeklog Forums

SQL syntax error while batch importing users


Status: offline

sakuny

Forum User
Junior
Registered: 02/05/04
Posts: 29
The following SQL syntax error showed up in my error log while trying to import users:

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 ')' at line 1. SQL in question: INSERT INTO gl_group_assignments (ug_main_grp_id,ug_uid) VALUES (13, )

any ideas where this might be and how to fix it?

thanks in advance for any help
 Quote

Status: offline

sakuny

Forum User
Junior
Registered: 02/05/04
Posts: 29
freakingout
i have a lot of users to import and i have no idea how to fix the SQL error, even which file to look at to try different things.

any help here?

thanks in advance. Frown
 Quote

Status: offline

Dirk

Site Admin
Admin
Registered: 01/12/02
Posts: 13073
Location:Stuttgart, Germany
How big is the list you want to import? I've noticed that the import code is a bit odd and may have problems when the file is bigger than 4096 bytes.

Try to import in smaller batches or download version 1.89 of admin/user.php from CVS.

bye, Dirk
 Quote

Status: offline

sakuny

Forum User
Junior
Registered: 02/05/04
Posts: 29
i'm only doing a test file thats just 4.5K (2 users).
i d'led the new user.php from CVS and am still getting an SQL error:

From the log:
Wed Feb 2 09:21:28 2005 - 1064: 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 ')' at line 1. SQL in question: INSERT INTO gl_group_assignments (ug_main_grp_id,ug_uid) VALUES (13, )


which seems to refer to this line in /public_html/admin/user.php:
$sql = "INSERT INTO {$_TABLES['group_assignments']} (ug_main_grp_id, ug_uid) VALUES (" . current($groups) . ",$uid)";

 Quote

Status: Banned

machinari

Forum User
Full Member
Registered: 03/22/04
Posts: 1512
a tab delimited text file with 2 users shouldn't be 4.5k... maybe 100 or 120 bytes and no more.
you may want to check your editor. don't use a word processor.
 Quote

Status: offline

sakuny

Forum User
Junior
Registered: 02/05/04
Posts: 29
actually, because i'm on a Mac, it was reading Mac's resouce fork information. i am using a text editor and recently switched to TextWrangler which is more UNIX-aware. the "true" file size is 113 bytes.

does the file size have to do with the SQL error? i'll try
 Quote

Status: offline

sakuny

Forum User
Junior
Registered: 02/05/04
Posts: 29
I am still having this problem and it is a SQL syntax error, not a problem with the file.

any one can help here?
 Quote

Status: Banned

machinari

Forum User
Full Member
Registered: 03/22/04
Posts: 1512
the function, USER_createAccount, is where the sql is that you are looking for. that function resides in lib-user.php and is called from importusers().

the problem is that a user id is not being sent to this particular sql statement. the 2nd parm should be a uid immediately retreived before this statement is executed.

I can see no reason why this would happen except if your tab-delimited text file is somehow not properly formatted, ie, containing 3 values (fullname, username, and email address) which are tab-delimited.

I did find one thing though. Dirk you may be interested.
I'm looking at user.php,v 1.86 (1.89 has this same error) and see on line 573 the following
Text Formatted Code
            if ($ucount == 0 && ecount == 0) {

 
which should probably be
Text Formatted Code
            if ($ucount == 0 && $ecount == 0) {

 
notice the inserted dollar sign.

I don't think that would cause this problem because USER_createAccount is being called anyway, but it looks to be an error nonetheless.
 Quote

Status: offline

sakuny

Forum User
Junior
Registered: 02/05/04
Posts: 29
my users.php is version 1.93 and that line appears fine:


if ($ucount == 0 AND $ecount == 0) {



what i did notice is that the first name on my batch list seems to have been
partially imported. the way it appears makes me wonder if the problem is
the limit on the username number of characters.

i have my users sign in with Firstname_Lastname as user name. do you think
that is too long and causing the SQL error?

if so, how do change the allowable length for usernames in all the places i should change?
(especially in SQL)

thanks!
 Quote

Status: offline

sakuny

Forum User
Junior
Registered: 02/05/04
Posts: 29
yes, it seems that this error is cause when the username is more than 16 characters. is there a valid reason for this limitation? as mentioned in my earlier post, i have people login using a full name like so:

username: First_Lastname
fullname: First Lastname

so that everyone can be identified. we have no need for "nicknames" but the longer username seems to be make this error.

i think i found the place in the SQL table:

$_SQL[37] = "
CREATE TABLE {$_TABLES['users']} (
uid mediumint(Cool NOT NULL auto_increment,
username varchar(16) NOT NULL default '',

can i just change the limit on the username ? will it create other problems?
 Quote

Status: Banned

machinari

Forum User
Full Member
Registered: 03/22/04
Posts: 1512
Quote by sakuny: my users.php is version 1.93 and that line appears fine:

if ($ucount == 0 AND $ecount == 0) {
not users.php, but user.php, which is in the admin dir. its latest version is 1.89.

and I don't know for sure if changing the field length will have any adverse effect in other places, but by itself it shouldn't be a problem. only way to know for sure is to give it a shot. Leaves me speechless
 Quote

All times are EDT. The time is now 03:19 am.

  • Normal Topic
  • Sticky Topic
  • Locked Topic
  • New Post
  • Sticky Topic W/ New Post
  • Locked Topic W/ New Post
  •  View Anonymous Posts
  •  Able to post
  •  Filtered HTML Allowed
  •  Censored Content