Bug 3019 - pdb_mysql makes wrong SQL queries
Summary: pdb_mysql makes wrong SQL queries
Status: RESOLVED WONTFIX
Alias: None
Product: Samba 3.0
Classification: Unclassified
Component: pdb_sql (show other bugs)
Version: 3.0.20
Hardware: x86 Linux
: P3 normal
Target Milestone: none
Assignee: pdb_sql maintainers mail alias
QA Contact: Samba QA Contact
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2005-08-20 03:54 UTC by Florian Effenberger
Modified: 2006-02-10 08:42 UTC (History)
4 users (show)

See Also:


Attachments
Patch for mysql.dump (671 bytes, patch)
2005-08-20 06:10 UTC, Florian Effenberger
no flags Details

Note You need to log in before you can comment on or make changes to this bug.
Description Florian Effenberger 2005-08-20 03:54:44 UTC
Beginning with 3.0.20 (3.0.14a did not have this problem), pdb_mysql seems to
make wrong SQL queries.

My configure line is:
./configure --with-pam --with-pam_smbpass --with-quotas --with-sys-quotas
--with-expsam=mysql --with-mysql-prefix=/usr/local/mysql --with-smbmount
--with-winbind

And I have to modify passdb/pdb_mysql.c to read
#include <mysql.h>
so the libraries are found. MySQL is version 4.1.12, self compiled.

A quick and dirty hack, using the pdb_sql.c and pdb_mysql.c from 3.0.14a, did
not help, as they seem to be incompatible to some other files in 3.0.20.

I cannot logon to the domain anymore, as the query seems to be wrong. The log
file reads:

[2005/08/20 12:55:10, 0] smbd/server.c:main(802)
  smbd version 3.0.20 started.
  Copyright Andrew Tridgell and the Samba Team 1992-2004
[2005/08/20 12:55:10, 1, effective(0, 0), real(0, 0)]
param/loadparm.c:lp_do_parameter(3283)
  WARNING: The "printer admin" option is deprecated
[2005/08/20 12:55:10, 1, effective(0, 0), real(0, 0)]
passdb/pdb_mysql.c:mysqlsam_init(474)
  Connecting to database server, host: localhost, user: samba, database: domain,
port: 3306
[2005/08/20 12:55:10, 0, effective(0, 0), real(0, 0)]
passdb/pdb_mysql.c:mysqlsam_select_by_field(248)
  Error while executing MySQL query Unbekanntes Tabellenfeld 'logon_hours' in
field list

"Unbekanntes Tabellenfeld" means as much as "Unknown field in table".
Comment 1 Florian Effenberger 2005-08-20 03:57:36 UTC
The logon attempt in Windows XP tells me that the computer account cannot be
found. The log file says:

[2005/08/20 12:56:10, 0, effective(1010, 1006), real(1010, 0)]
rpc_server/srv_pipe.c:api_pipe_bind_req(981)
  Attempt to bind using schannel without successful serverauth2
[2005/08/20 12:56:10, 0, effective(0, 0), real(0, 0)]
passdb/pdb_mysql.c:mysqlsam_select_by_field(248)
  Error while executing MySQL query Unbekanntes Tabellenfeld 'logon_hours' in
field list
[2005/08/20 12:56:10, 0, effective(1010, 1006), real(1010, 0)]
rpc_server/srv_netlog_nt.c:get_md4pw(242)
  get_md4pw: Workstation MYMACHINE$: no account in domain
[2005/08/20 12:56:10, 0, effective(0, 0), real(0, 0)]
passdb/pdb_mysql.c:mysqlsam_select_by_field(248)
  Error while executing MySQL query Unbekanntes Tabellenfeld 'logon_hours' in
field list
[2005/08/20 12:56:10, 0, effective(1010, 1006), real(1010, 0)]
rpc_server/srv_netlog_nt.c:get_md4pw(242)
  get_md4pw: Workstation MYMACHINE$: no account in domain
Comment 2 Florian Effenberger 2005-08-20 04:15:38 UTC
Attached ist the SQL scheme of the table. Seems the logon_hours is missing? I
checked the mysql.dump in the examples directory and it is missing there, too.
May that be the culprit?

CREATE TABLE smbuser (
  logon_time int(9) default NULL,
  logoff_time int(9) default NULL,
  kickoff_time int(9) default NULL,
  pass_last_set_time int(9) default NULL,
  pass_can_change_time int(9) default NULL,
  pass_must_change_time int(9) default NULL,
  username varchar(255) default NULL,
  domain varchar(255) default NULL,
  nt_username varchar(255) default NULL,
  nt_fullname varchar(255) default NULL,
  home_dir varchar(255) default NULL,
  dir_drive varchar(4) default NULL,
  logon_script varchar(255) default NULL,
  profile_path varchar(255) default NULL,
  acct_desc varchar(255) default NULL,
  workstations varchar(255) default NULL,
  unknown_str varchar(255) default NULL,
  munged_dial varchar(255) default NULL,
  uid int(9) NOT NULL auto_increment,
  gid int(9) default NULL,
  user_sid varchar(255) default NULL,
  group_sid varchar(255) default NULL,
  lm_pw varchar(255) default NULL,
  nt_pw varchar(255) default NULL,
  acct_ctrl int(9) default NULL,
  unknown_3 int(9) default NULL,
  logon_divs int(9) default NULL,
  hours_len int(9) default NULL,
  unknown_5 int(9) default NULL,
  unknown_6 int(9) default '1260',
  bad_password_count int(9) default NULL,
  logon_count int(9) default NULL,
  PRIMARY KEY  (uid),
  KEY username (username)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4163 ;
Comment 3 Florian Effenberger 2005-08-20 06:07:49 UTC
Logon seems to work again if I add

 logon_hours varchar(42) default NULL,

to the database scheme. I did not test if logon hours restriction works, nor if
the above syntax is correct, so please verify it. If we are at it, the line

 uid int(9) NOT NULL DEFAULT "0" PRIMARY KEY auto_increment,

produces an SQL error for me (bug is also in 3.0.14a) and has to be changed to

 uid int(9) NOT NULL auto_increment,
Comment 4 Florian Effenberger 2005-08-20 06:10:35 UTC
Created attachment 1390 [details]
Patch for mysql.dump

Tab spacing might be wrong as I just did a copy and paste out of my SSH
session.
Comment 5 Florian Effenberger 2005-08-20 09:18:41 UTC
Changing severity to normal; bug was not as big as I first thought.
Comment 7 Florian Effenberger 2005-08-22 09:19:08 UTC
just as an information: DO NOT USE THE 3.0.20 PDB MYSQL MODULE BY ANY MEANS!

I just found out that it seems to destroy my SQL database. At least the password
fields are being reset, resulting in

 1. no machine has a domain account anymore
 2. no user can logon

Running (the 3.0.11) smbpasswd over the users and entering their passwords
manually and joining the domain with the clients as well helps, but as soon as I
do a change with pdbedit or even with the Windows integrated password change,
the database gets corrupted again. I repeat: ALL DATABASE ENTRIES are affected!

Seems something is terribly wrong here...

Changing severity to critical because of that, hope that is right.
Comment 8 Florian Effenberger 2005-08-22 10:08:41 UTC
Don't know if my patch is right, guess I missed something, so here is a working
SQL schema for 3.0.20:

CREATE TABLE smbuser (
  logon_time int(9) default NULL,
  logoff_time int(9) default NULL,
  kickoff_time int(9) default NULL,
  pass_last_set_time int(9) default NULL,
  pass_can_change_time int(9) default NULL,
  pass_must_change_time int(9) default NULL,
  username varchar(255) default NULL,
  domain varchar(255) default NULL,
  nt_username varchar(255) default NULL,
  nt_fullname varchar(255) default NULL,
  home_dir varchar(255) default NULL,
  dir_drive varchar(4) default NULL,
  logon_script varchar(255) default NULL,
  profile_path varchar(255) default NULL,
  acct_desc varchar(255) default NULL,
  workstations varchar(255) default NULL,
  unknown_str varchar(255) default NULL,
  munged_dial varchar(255) default NULL,
  uid int(9) NOT NULL auto_increment,
  gid int(9) default NULL,
  user_sid varchar(255) default NULL,
  group_sid varchar(255) default NULL,
  lm_pw varchar(255) default NULL,
  nt_pw varchar(255) default NULL,
  acct_ctrl int(9) default NULL,
  unknown_3 int(9) default NULL,
  logon_divs int(9) default NULL,
  hours_len int(9) default NULL,
  unknown_5 int(9) default NULL,
  unknown_6 int(9) default '1260',
  bad_password_count int(9) default NULL,
  logon_count int(9) default NULL,
  logon_hours varchar(42) default NULL,
  PRIMARY KEY  (uid),
  KEY username (username)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4222 ;
Comment 9 Jelmer Vernooij 2005-09-23 11:17:11 UTC
The experimental pdb modules (pdb_mysql, pdb_pgsql and pdb_xml) have 
been a bit neglected during the last few releases of Samba 3 as they 
haven't been actively maintained. I was the original author and 
maintainer, but I no longer work on Samba 3 and I no longer use any of 
the modules in production. I was hoping I could nonetheless keep the code 
working for those that still use it, but this turned out to be harder then I 
expected. 
 
I'll remove these modules unless somebody steps up as a maintainer. If 
there's anyone willing to take over maintainance of either one of 
these modules, please let me know. Otherwise, I'll put the modules up 
on my samba.org homepage as a separate tarball for those interested 
(including some of the patches that have been published). 
Comment 10 Florian Effenberger 2005-09-24 01:22:23 UTC
We run a pdb_mysql environment with Samba 3.0.20 and the patches by Peter
Rindfuss, which I'm adding to CC, hoping that this is okay for him.

The patches are in talloc.c and pdb_sql.c, and then pdb_mysql works fine
(pdb_pgsql untested).

Unfortunately, I am no programmer, but we actively use the pdb_mysql module, as
we have set up a GUI that relies on this, and switching to tdbsam or LDAP is no
intermediate option for us.

I hope that we can find some people willing to help debugging on coding on these
modules.

Darrell, Peter, would you be willing to help? I would be helping, too!
Comment 11 Florian Effenberger 2005-09-24 01:22:58 UTC
Adding Darrell to Cc so he doesn't miss one of the associated Bugzilla entries -
hope that is okay.
Comment 12 Gerald (Jerry) Carter (dead mail address) 2006-02-10 08:42:06 UTC
See bug 3375.  If someone wants to maintain these modules outside
the Samba source tree, that is fine.  But the source has been removed
from the upcoming 3.0.22 release.