Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Doesn't work on Excel 2016 and macOS High Sierra #29

Closed
delitestudio opened this issue Mar 1, 2018 · 35 comments
Closed

Doesn't work on Excel 2016 and macOS High Sierra #29

delitestudio opened this issue Mar 1, 2018 · 35 comments

Comments

@delitestudio
Copy link

@delitestudio delitestudio commented Mar 1, 2018

I've macOS High Sierra 10.13.3. Using the package installer I installed the most recent iODBC Administrator 3.52.12 and the MySQL Connector ODBC 5.3.10.

After setting up a test database in MySQL and verifying that it all works well using Sequel Pro, I setup a user DSN in iODBCAdministrator.app for that database. The iODBC test of the DSN fails with:

IM003 
[iODBC][Driver Manager]Specified driver could not be loaded 

If I move the driver files from /usr/local/mysql-connector-odbc-5.3.10-macos10.13-x86-64bit/lib to ~, I get the previous IM003 error followed by this additional error:

00000 
[iODBC][Driver Manager]dlopen(/Users/name/libmyodbc5w.so, 6): no suitable image found.  Did find:
	/Users/name/libmyodbc5w.so: mach-o, but wrong architecture
	/Users/name/libmyodbc5w.so: stat() failed with errno=17

If I open iODBCAdministrator64.app it works. But when I switch to Excel 2016 and I go on Data > Get External Data > From Database and I select the same DSN file, I keep getting the usual:

IM003 
[iODBC][Driver Manager]Specified driver could not be loaded 

Any ideas?

@timhaynesopenlink

This comment has been minimized.

Copy link

@timhaynesopenlink timhaynesopenlink commented Mar 1, 2018

Hi,
As your tests show, you need the entire pipeline to be a consistent architecture, either 32- or 64-bit, from the application (including iODBC Administrator) to the iODBC driver manager to the MySQL ODBC driver.

Example:

/Applications/iODBC/iODBC Administrator64.app/Contents/MacOS/iODBC Administrator64: Mach-O universal binary with 2 architectures: [x86_64:Mach-O 64-bit executable x86_64] [i386:Mach-O executable i386]
/Applications/iODBC/iODBC Administrator64.app/Contents/MacOS/iODBC Administrator64 (for architecture x86_64):	Mach-O 64-bit executable x86_64
/Applications/iODBC/iODBC Administrator64.app/Contents/MacOS/iODBC Administrator64 (for architecture i386):	Mach-O executable i386

This shows our iODBC Administrator 64-bit supports both 32- and 64-bit architectures; for the 32-bit version the same command only shows an i386 architecture.

My betting is if you were to run file on Excel 2016, you'd find it was 32-bit.

You can either:

HTH

@delitestudio

This comment has been minimized.

Copy link
Author

@delitestudio delitestudio commented Mar 1, 2018

Hello, first of all thanks for your answer!

I've the last Excel version (16.10), and launching file on it I get:

[/Applications/Microsoft Excel.app/Contents/MacOS]$ file Microsoft\ Excel 
Microsoft Excel: Mach-O 64-bit executable x86_64

I confirm instead that launching file on iODBCAdministrator64.appI get:

[/Applications/iODBC/iODBC Administrator64.app/Contents/MacOS]$ file iODBC\ Administrator64 
iODBC Administrator64: Mach-O universal binary with 2 architectures: [x86_64:Mach-O 64-bit executable x86_64] [i386:Mach-O executable i386]
iODBC Administrator64 (for architecture x86_64):	Mach-O 64-bit executable x86_64
iODBC Administrator64 (for architecture i386):	Mach-O executable i386

The thing I can't explain is therefore that Excel, while being 64-bit, would seem to start the 32-bit version of iODBC. Will there be a way to force it to launch the 64-bit one (or to remove the 32-bit one)?

Thanks again!

@TallTed

This comment has been minimized.

Copy link

@TallTed TallTed commented Mar 2, 2018

Excel 2016 v16.10 is definitely 64-bit only.

The iODBC libraries are "fat" binaries containing both 32-bit and 64-bit, and only the 64-bit portion of the iODBC libraries is loaded when the 64-bit Excel 16.10 (or iODBC Administrator64.app, or any other 64-bit app) calls.

That said --

I think your issue is with the mysql-connector ODBC driver, not with the iODBC driver manager. This can easily be confirmed by downloading and testing with any of our own ODBC Drivers for MySQL; the Lite Edition provides easiest setup with optimal compatibility with all 32-bit and 64-bit ODBC applications. (Free two-week trial license is provided with download.)

Randomly moving library files around is not usually recommended; please put the libraries you moved from /usr/local/mysql-connector-odbc-5.3.10-macos10.13-x86-64bit/lib to ~ back in their originally installed location.

One current question is the libmyodbc5w.so. Output of file /usr/local/mysql-connector-odbc-5.3.10-macos10.13-x86-64bit/lib/*.so is likely to be revealing.

The full details of the DSN configuration are also likely to be relevant. Checking these requires review of four configuration files, to start --

  • /Library/ODBC/odbc.ini
  • /Library/ODBC/odbcinst.ini
  • ~/Library/ODBC/odbc.ini
  • ~/Library/ODBC/odbcinst.ini
@delitestudio

This comment has been minimized.

Copy link
Author

@delitestudio delitestudio commented Mar 2, 2018

Hello and thanks for your answer. At the moment I have not yet found the solution. Libraries seem ok:

$ file /usr/local/mysql-connector-odbc-5.3.10-macos10.13-x86-64bit/lib/*.so
/usr/local/mysql-connector-odbc-5.3.10-macos10.13-x86-64bit/lib/libmyodbc5a.so: Mach-O 64-bit bundle x86_64
/usr/local/mysql-connector-odbc-5.3.10-macos10.13-x86-64bit/lib/libmyodbc5w.so: Mach-O 64-bit bundle x86_64

Here is the content of the ~/.odbc.ini:

[ODBC Data Sources]
fissolo = MySQL ODBC 5.3 Unicode Driver

[ODBC]
TraceLibrary  = 
Trace         = 0
TraceAutoStop = 0
TraceFile     = /Users/name/sql.log
TraceDLL      = 

[fissolo]
DRIVER = /usr/local/mysql-connector-odbc-5.3.10-macos10.13-x86-64bit/lib/libmyodbc5w.so
SERVER = fissolo.lan
PORT   = 3306

When I use iODB Administrator64.app I get:

shot_01

So it's fine. When I do the same in Excel I get:

shot_02

Here are the contents of the files you mentioned:

$ cat /Library/ODBC/odbc.ini
[ODBC Data Sources]
myodbc  = MySQL ODBC 5.3 Unicode Driver
myodbca = MySQL ODBC 5.3 ANSI Driver

[ODBC]
TraceLibrary = 

[myodbc]
Driver = MySQL ODBC 5.3 Unicode Driver
SERVER = fissolo.lan
PORT   = 3306

[myodbca]
Driver = MySQL ODBC 5.3 ANSI Driver
SERVER = fissolo.lan
PORT   = 3306
$ cat /Library/ODBC/odbcinst.ini
[ODBC Drivers]
MySQL ODBC 5.3 Unicode Driver = Installed
MySQL ODBC 5.3 ANSI Driver    = Installed

[ODBC Connection Pooling]
PerfMon    = 0
Retry Wait = 

[MySQL ODBC 5.3 Unicode Driver]
Driver = /usr/local/mysql-connector-odbc-5.3.10-macos10.13-x86-64bit/lib/libmyodbc5w.so

[MySQL ODBC 5.3 ANSI Driver]
Driver = /usr/local/mysql-connector-odbc-5.3.10-macos10.13-x86-64bit/lib/libmyodbc5a.so
$ cat ~/Library/ODBC/odbc.ini
[ODBC Data Sources]
fissolo = MySQL ODBC 5.3 Unicode Driver

[ODBC]
TraceLibrary  = 
Trace         = 0
TraceAutoStop = 0
TraceFile     = /Users/name/sql.log
TraceDLL      = 

[fissolo]
DRIVER = /usr/local/mysql-connector-odbc-5.3.10-macos10.13-x86-64bit/lib/libmyodbc5w.so
SERVER = fissolo.lan
PORT   = 3306
$ cat ~/Library/ODBC/odbcinst.ini
[ODBC Drivers]

[ODBC Connection Pooling]
PerfMon    = 0
Retry Wait = 

[ODBC]
FileDSNPath = /Users/name/Documents

Do you see something wrong?

@TallTed

This comment has been minimized.

Copy link

@TallTed TallTed commented Mar 3, 2018

AH! Yes, there are a few things to be addressed here.

immediate possible success

Excel should be able to make use of your existing apparently-correctly configured User DSN, fissolo.

possible error in FileDSN being created through Excel

I don't know what settings you made in the DSN whose name is fully hidden behind the error dialog. I'm betting there's at least one error there. Best to provide the text of the file for me to check, if you want to continue working with this or other FileDSNs.

Please also note that every DSN should be named uniquely. This DSN should not be named fissolo, as it appears it might be.

errors in existing configuration files

In the two DSNs in your system-level /Library/ODBC/odbc.ini (myodbc and myodbca), there are two blatant errors. The Driver keyword should be set to either the full path to the driver library (most common) or the brace-wrapped name of that driver as registered in /Library/ODBC/odbcinst.ini. To be clear, these stanzas --

[myodbc]
Driver = MySQL ODBC 5.3 Unicode Driver
SERVER = fissolo.lan
PORT   = 3306

[myodbca]
Driver = MySQL ODBC 5.3 ANSI Driver
SERVER = fissolo.lan
PORT   = 3306

-- should become these --

[myodbc]
Driver = /usr/local/mysql-connector-odbc-5.3.10-macos10.13-x86-64bit/lib/libmyodbc5w.so
SERVER = fissolo.lan
PORT   = 3306

[myodbca]
Driver = /usr/local/mysql-connector-odbc-5.3.10-macos10.13-x86-64bit/lib/libmyodbc5a.so
SERVER = fissolo.lan
PORT   = 3306

-- or these --

[myodbc]
Driver = {MySQL ODBC 5.3 Unicode Driver}
SERVER = fissolo.lan
PORT   = 3306

[myodbca]
Driver = {MySQL ODBC 5.3 ANSI Driver}
SERVER = fissolo.lan
PORT   = 3306

possible rogue configuration files

Please note that ~/.odbc.ini and ~./odbcinst.ini should be symlinks to ~/Library/ODBC/odbc.ini and ~/Library/ODBC/odbcinst.ini, respectively. That fits with the content you provided, but it's worth confirmation with --

sudo find / -name '*odbc*.ini' -ls

That output may reveal some other files that need tidying, due to a number of components doing ODBC things on their own, rather than making appropriate API calls.

@delitestudio

This comment has been minimized.

Copy link
Author

@delitestudio delitestudio commented Mar 4, 2018

Hello,

I've made the changes you indicated (symbolic link ~/.odbc.ini => /Library/ODBC/odbc.ini, ~/.odbcinst.ini => /Library/ODBC/odbcinst.ini, and use of the path instead of the name of the driver), but nothing changed: in Excel I still get the "Specified driver could not be loaded", while in the iODBCAdministrator64.app all works fine.

In all honesty, please note I had not made any particular customisations, so these things derive from the standard installation of MySQL Connector ODBC 5.3.10 or iODBC Administrator 3.52.12. Please also note I had already tried to use all the possible combinations of configuration files, and none of these worked in Excel (no problem instead in iODBCAdministrator64.app).

Here is the output of the last command:

$ sudo find / -name '*odbc*.ini' -ls
8601338116        8 -rw-r--r--    1 root          wheel             669 Jan 19 11:40 /usr/local/mysql-connector-odbc-5.3.10-macos10.13-x86-64bit/test/odbc.ini
8601338117        8 -rw-r--r--    1 root          wheel            1285 Jan 19 11:40 /usr/local/mysql-connector-odbc-5.3.10-macos10.13-x86-64bit/test/odbcinst.ini
8601337136        8 -rw-rw-r--    1 root          admin             406 Mar  4 19:04 /Library/ODBC/odbc.ini
8601337137        8 -rw-rw-r--    1 root          admin             394 Mar  1 15:46 /Library/ODBC/odbcinst.ini
8601337133        8 -rw-rw-r--    1 user          wheel             320 Mar  1 17:49 /Users/user/Library/ODBC/odbc.ini
8601337134        8 -rw-rw-r--    2 user          wheel             118 Mar  1 17:28 /Users/user/Library/ODBC/odbcinst.ini
8601337134        8 -rw-rw-r--    2 user          wheel             118 Mar  1 17:28 /Users/user/Library/Containers/com.microsoft.Excel/Data/Library/ODBC/odbcinst.ini
8601338703        0 lrwxr-xr-x    1 user          staff              84 Mar  1 15:47 /Users/user/Library/Containers/com.microsoft.Excel/Data/.odbcinst.ini -> /Users/user/Library/Containers/com.microsoft.Excel/Data/Library/ODBC/odbcinst.ini
8601466705        0 lrwxr-xr-x    1 user          staff              22 Mar  4 19:16 /Users/user/.odbc.ini -> /Library/ODBC/odbc.ini
8601466801        0 lrwxr-xr-x    1 user          staff              26 Mar  4 19:16 /Users/user/.odbcinst.ini -> /Library/ODBC/odbcinst.ini

Thanks again for your time and patience!

@TallTed

This comment has been minimized.

Copy link

@TallTed TallTed commented Mar 5, 2018

@delitestudio -

First thing -- I think there was a misunderstanding of my earlier. Symlinks should be —

  • ~/.odbc.ini => ~/Library/ODBC/odbc.ini
    (not => /Library/ODBC/odbc.ini)
  • ~/.odbcinst.ini => ~/Library/ODBC/odbcinst.ini
    (not => /Library/ODBC/odbcinst.ini)

There are a couple other oddities in the output you provided. Please execute this sequence of commands (adjusting /Users/user/ to include the correct username) to clean these up --

rm ~/.odbcinst.ini ~/.odbc.ini

ln -s ~/Library/ODBC/odbcinst.ini ~/.odbcinst.ini

ln -s ~/Library/ODBC/odbc.ini ~/.odbc.ini

rm /Users/user/Library/Containers/com.microsoft.Excel/Data/Library/ODBC/odbcinst.ini

ln /Library/ODBC/odbc.ini /Users/user/Library/Containers/com.microsoft.Excel/Data/Library/ODBC/odbc.ini

ln /Library/ODBC/odbcinst.ini /Users/user/Library/Containers/com.microsoft.Excel/Data/Library/ODBC/odbcinst.ini

ln -s /Users/user/Library/Containers/com.microsoft.Excel/Data/Library/ODBC/odbc.ini /Users/user/Library/Containers/com.microsoft.Excel/Data/.odbc.ini

ln -s /Users/user/Library/Containers/com.microsoft.Excel/Data/Library/ODBC/odbcinst.ini /Users/user/Library/Containers/com.microsoft.Excel/Data/.odbcinst.ini

At this point, I would expect your MySQL Connector ODBC DSN to be testable via iODBC Administrator64.app, and usable with iODBC Demo.app and iODBC Test.command. Assuming all that works, I'd suggest trying again with Excel 2016.

If Excel doesn't now work with the MySQL Connector ODBC DSN, I urge you to download and test our Lite Edition ODBC Driver for MySQL with your Excel -- as this will show whether your Excel installation is working with your iODBC installation.

Assuming our driver works with your Excel, and the MySQL Connector ODBC does not, I must send you to the MySQL folks for further assistance with that driver. I'm afraid we can only provide minimal support for Microsoft Excel and MySQL Connector ODBC, as these are not our products. (That said, please do let us know how things go.)

@delitestudio

This comment has been minimized.

Copy link
Author

@delitestudio delitestudio commented Mar 6, 2018

I've done all the steps you have indicated, but unfortunately the behavior has not changed: in Excel I still get the "Specified driver could not be loaded", while in the iODBCAdministrator64.app all works fine.

So I've installed your Lite Edition ODBC Driver for MySQL:

screen shot 2018-03-06 at 09 25 18

And now it works!

screen shot 2018-03-06 at 09 25 32

So, in your opinion, this is a problem with MySQL Connector ODBC 5.3.10?

Thanks for your precious help!

@smalinin

This comment has been minimized.

Copy link
Collaborator

@smalinin smalinin commented Mar 6, 2018

The MS Office is Sandboxed application, so (may be) it doesn't have access to
/usr/local/mysql-connector-odbc-5.3.10-macos10.13-x86-64bit/lib/libmyodbc5w.so
try to move your MySQL ODBC driver to
/Library/ODBC/mysql-connector-odbc-5.3.10-macos10.13-x86-64bit/libmyodbc5w.so

@delitestudio

This comment has been minimized.

Copy link
Author

@delitestudio delitestudio commented Mar 6, 2018

OMG, it worked! THANKS!

But, so, it's something that should be fixed (or at least documented) in the MySQL Connector ODBC 5.3.10 installer?

@smalinin

This comment has been minimized.

Copy link
Collaborator

@smalinin smalinin commented Mar 6, 2018

Yes, but you have write to MySQL team about this, I think.

@delitestudio

This comment has been minimized.

Copy link
Author

@delitestudio delitestudio commented Mar 6, 2018

Ok. Thanks again guys for your invaluable help!

@glenncogar

This comment has been minimized.

Copy link

@glenncogar glenncogar commented Mar 14, 2018

Thanks so much, this helped me get my SQLite3 drivers working too

@delitestudio

This comment has been minimized.

Copy link
Author

@delitestudio delitestudio commented Mar 14, 2018

A brief update: I reported the problem to the MySQL team (issue #89931), and the ticket was accepted and passed into the "verified" state. Hopefully then it will be solved soon!

@erbastian

This comment has been minimized.

Copy link

@erbastian erbastian commented Jul 24, 2018

Thank you for posting this. Was having the exact same issue with the latest mysql connector (version 8.0.11). Moved my ansi library (and all related libraries) to /Library/ODBC/mysql-connector-blahblah-/libmyodbc8a.so and it now works with Excel. Took me half a day but I never would have solved without this information. I guess that means MySQL has not yet addressed issue #89931.

@delitestudio

This comment has been minimized.

Copy link
Author

@delitestudio delitestudio commented Jul 24, 2018

Unfortunately you're right, I haven't received updates on the issue #89931 from the MySQL team :(

@samsgit

This comment has been minimized.

Copy link

@samsgit samsgit commented Oct 3, 2018

I created a script to solve this:

#!/bin/bash

# https://github.com/openlink/iODBC/issues/29
# https://bugs.mysql.com/bug.php?id=89931

base_src_dir="/usr/local"
mysql_odbc_name=$(ls "$base_src_dir" | grep -m 1 "mysql-connector-odbc")
odbc_dir="/Library/ODBC"

src="$base_src_dir/$mysql_odbc_name/lib"
dst="$odbc_dir/$mysql_odbc_name/lib"

echo "creating '$dst'"
sudo mkdir -p "$dst"

echo "copying '$src' to '$dst'"
sudo cp -af "$src/." "$dst"


odbc_ini_path="$odbc_dir/odbc.ini"
odbc_ini_bak_path="$odbc_ini_path.bak"

odbcinst_ini_path="$odbc_dir/odbcinst.ini"
odbcinst_ini_bak_path="$odbcinst_ini_path.bak"

echo "backing up '$odbc_ini_path' to '$odbc_ini_bak_path'"
sudo cp -f "$odbc_ini_path" "$odbc_ini_bak_path"

echo "backing up '$odbcinst_ini_path' to '$odbcinst_ini_bak_path'"
sudo cp -f "$odbcinst_ini_path" "$odbcinst_ini_bak_path"

# https://stackoverflow.com/a/29626460
function replace {
  sudo sed -i '' "s/$(sed 's/[^^]/[&]/g; s/\^/\\^/g' <<< "$1")/$(sed 's/[&/\]/\\&/g' <<< "$2")/g" "$3"
}

ansi_driver=$(ls "$dst" | grep -m 1 "^lib.*a\.so$")
unicode_driver=$(ls "$dst" | grep -m 1 "^lib.*w\.so$")

old_ansi_path="$src/$ansi_driver"
new_ansi_path="$dst/$ansi_driver"

old_unicode_path="$src/$unicode_driver"
new_unicode_path="$dst/$unicode_driver"

echo "updating '$old_ansi_path' to '$new_ansi_path' in '$odbc_ini_path'"
replace "$old_ansi_path" "$new_ansi_path" "$odbc_ini_path"

echo "updating '$old_ansi_path' to '$new_ansi_path' in '$odbcinst_ini_path'"
replace "$old_ansi_path" "$new_ansi_path" "$odbcinst_ini_path"

echo "updating '$old_unicode_path' to '$new_unicode_path' in '$odbc_ini_path'"
replace "$old_unicode_path" "$new_unicode_path" "$odbc_ini_path"

echo "updating '$old_unicode_path' to '$new_unicode_path' in '$odbcinst_ini_path'"
replace "$old_unicode_path" "$new_unicode_path" "$odbcinst_ini_path"
@domenicofusco

This comment has been minimized.

Copy link

@domenicofusco domenicofusco commented Oct 6, 2018

Thank you for the script .
It all tests OK and am able to get the SQL window in Mac Excel but when I type a simple SQL query mine gives me an error code
use ShareHistory
SELECT * FROM Holding

error message

[MySQL][ODBC 8.0(a) Driver][mysqld-5.5.44-MariaDB-log]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT * FROM Holding' at line 2.
I have tried different syntax on the select statement to no success.
any suggestions .

@TallTed

This comment has been minimized.

Copy link

@TallTed TallTed commented Oct 11, 2018

@domenicofusco - I think your current error is unrelated to iODBC, and should be raised with the folks who provided that ODBC driver for MySQL.

@domenicofusco

This comment has been minimized.

Copy link

@domenicofusco domenicofusco commented Oct 16, 2018

@TallTed

This comment has been minimized.

Copy link

@TallTed TallTed commented Oct 17, 2018

@domenicofusco -

Looking a little closer, I think your syntax error is a simple missing semicolon. This is what you said you executed --

use ShareHistory
SELECT * FROM Holding

I think that should be --

use ShareHistory ;
SELECT * FROM Holding
@domenicofusco

This comment has been minimized.

Copy link

@domenicofusco domenicofusco commented Oct 17, 2018

@domenicofusco

This comment has been minimized.

Copy link

@domenicofusco domenicofusco commented Oct 17, 2018

@TallTed

This comment has been minimized.

Copy link

@TallTed TallTed commented Oct 17, 2018

@domenicofusco

The _0 in the query which works comes from its FROM clause, which creates an alias targeting the qualified tablename, obviating the preceding USE statement --

FROM ShareHistory.Holding Holding_0

Many tools and DBMS would have that read --

FROM ShareHistory.Holding AS Holding_0

I wonder if there are multiple Holding tables in the catalogs made available by this MariaDB instance? You might find this works just as well --

SELECT * FROM ShareHistory.Holding
@domenicofusco

This comment has been minimized.

Copy link

@domenicofusco domenicofusco commented Oct 21, 2018

@domc1

This comment has been minimized.

Copy link

@domc1 domc1 commented Nov 21, 2018

This thread was immensely helpful in solving the connection issue. The sandboxing was causing repeated crashes

@Hochiss

This comment has been minimized.

Copy link

@Hochiss Hochiss commented Mar 23, 2019

Sorry gents...
I have the same issue while using MariaDB connector (instead of OP's MySQL connctor), i.e. 64-bit works and non-64 NOK:

@smalinin: as you mentioned about EXCEL sandboxing access ...
I checked my driver is at:
/Library/MariaDB/MariaDB-Connector-ODBC/libmaodbc.dylib

part of my odbc.ini in /Library/ODBC/odbc.ini

 [ODBC Data Sources]
 MariaDB-server = MariaDB ODBC 3.1 Driver

 [MariaDB-server]
 Description = MariaDB server
 Driver      = /Library/MariaDB/MariaDB-Connector-ODBC/libmaodbc.dylib 
@TallTed

This comment has been minimized.

Copy link

@TallTed TallTed commented Mar 25, 2019

@Hochiss - I am not sure I understand your report. If by "64-bit works and non-64 NOK" you mean "Excel 2016 works with the 64-bit MariaDB ODBC 3.1 Driver, and not with the 32-bit MariaDB ODBC 3.1 Driver", this is as expected. 64-bit ODBC apps such as Excel 2016 cannot load 32-bit ODBC drivers.

It's best not to point to details in an external and locked silo. If my comment above does not answer your concern, please post whatever details you provided on the other forum to this thread, along with summary (or, if permitted, full text) of responses you received there.

@Hochiss

This comment has been minimized.

Copy link

@Hochiss Hochiss commented Mar 26, 2019

Thanks @TallTed for still keeping eyes on this previously closed thread.

My case is almost the same as OP except it is MariaDB, i.e. EXCEL(v16.16.8 = 64bit) cannot connect via ODBC to MariaDB in LAN. But testing within iODBC.app(64-bit) was successful, but change to non-64bit iODBC.app was NOK(IM003 error), also I cannot add user DSN via this app (apparently due to the driver not recognized).

My basic settings:

  • MariaDB connector installed
  • iODBC administator GUI installed
  • odbc.ini & odbcinst.ini configured
  • OS: MacOS (10.13.2;
  • EXCEL 2016 v16.16.8 = 64bit

MariaDB ODBC 3.1 driver, which is 64-bit(checked with file -N):

/Library/MariaDB/MariaDB-Connector-ODBC/libmaodbc.dylib 

Here are some screenshots:

  1. OK with iODBC-admin.app (64-bit)
    4d65b9b9-8e16-47f8-8334-56be275815dd

2.NOK with 1. OK with iODBC-admin.app | EXCEL ODBC gets same error message

8c5da02c-05de-4bed-b834-fe88d5349022

If I understand correctly, the route goes like that:
EXCEL's ODBC calls iODBC manager, within it, calls DSN, DSN uses MariaDB driver to communicate the DB server in LAN.

What I observed is, EXCEL(64) calls IODBC manager(non-64) instead of 64-bit, while only the latter works and is able to connect to the MariaDB server. So the key question here is, why EXCEL calls non-64bit iODBC manager? PS: LibreOffice has no issue, also no bring-up any ODBC manager.
(I cannot help thinking that if Microsoft is again discriminating EXCEL for macOS in terms of its functionalities = evil again.)

PS: This issue has been reported at mariaDB@jira, both for Excel2019@macOS 10.14 and for Excel2016@macOS10.13, external url removed as you suggested.

Please let me know if you need any further info. thanks!

@smalinin

This comment has been minimized.

Copy link
Collaborator

@smalinin smalinin commented Mar 26, 2019

@Hochiss

  1. run OSX Console.app and open system.log
  2. run MSExcel and try connect to MySQL via MariaDB Connector
  3. you will see the next messages related with MSExcel
Mar 26 20:38:43 mac15-10 kernel[0]: Sandbox: Microsoft Excel(29707) deny file-read-data /usr/local/Cellar/openssl/1.0.2d_1/lib/libssl.1.0.0.dylib
Mar 26 20:38:43 mac15-10.local sandboxd[251] ([14239]): plugin-container(14239) deny iokit-get-properties IOConsoleUsers

The MariaDB ODBC driver could not load openssl library.
It is because sandboxed app doesn't have access to dir /usr/local
4) You could fix this issue by hand (or to ask MariaDB team to create ODBC driver with static linked openssl)
Steps of hand fixing:

  • check ODBC driver
# cd /Library/MariaDB/MariaDB-Connector-ODBC
# otool -L libmaodbc.dylib 
libmaodbc.dylib:
    /Users/admin/mariadb-connector-odbc/build/libmaodbc.dylib (compatibility version 0.0.0, current version 0.0.0)
    /usr/local/opt/libiodbc/lib/libiodbcinst.2.dylib (compatibility version 4.0.0, current version 4.25.0)
    /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1252.50.4)
    /usr/lib/libiconv.2.dylib (compatibility version 7.0.0, current version 7.0.0)
    /usr/local/opt/openssl/lib/libssl.1.0.0.dylib (compatibility version 1.0.0, current version 1.0.0)
    /usr/local/opt/openssl/lib/libcrypto.1.0.0.dylib (compatibility version 1.0.0, current version 1.0.0)
  • the path /usr/local/opt must be fixed.
  • copy openssl libs from /usr/local/Cellar/openssl/1.0.2d_1/lib to /Library/MariaDB/MariaDB-Connector-ODBC
  • you must to have( in /Library/MariaDB/MariaDB-Connector-ODBC) after the copy
# ls -l
total 5984
drwxr-xr-x  6 root    wheel      204 Jan 28 23:44 examples
-r--r--r--  1 sergei  wheel  1990028 Jan  8  2016 libcrypto.1.0.0.dylib
lrwxr-xr-x  1 root    wheel       21 Mar 26 20:51 libcrypto.dylib -> libcrypto.1.0.0.dylib
-rwxr-xr-x  1 root    wheel   695648 Mar 26 20:59 libmaodbc.dylib
drwxr-xr-x  7 root    wheel      238 Jan 28 18:21 libmariadb
-r--r--r--  1 sergei  wheel   367912 Mar 26 21:04 libssl.1.0.0.dylib
lrwxr-xr-x  1 root    wheel       18 Mar 26 20:51 libssl.dylib -> libssl.1.0.0.dylib
  • fix path to openssl libs
# install_name_tool -change /usr/local/opt/openssl/lib/libssl.1.0.0.dylib /Library/MariaDB/MariaDB-Connector-ODBC/libssl.1.0.0.dylib  libmaodbc.dylib
# install_name_tool -change /usr/local/opt/openssl/lib/libcrypto.1.0.0.dylib /Library/MariaDB/MariaDB-Connector-ODBC/libcrypto.1.0.0.dylib  libmaodbc.dylib 
# install_name_tool -change /usr/local/Cellar/openssl/1.0.2d_1/lib/libcrypto.1.0.0.dylib /Library/MariaDB/MariaDB-Connector-ODBC/libcrypto.1.0.0.dylib  libssl.1.0.0.dylib
  • you must to have the next after the path fixing
# otool -L libmaodbc.dylib 
libmaodbc.dylib:
    /Users/admin/mariadb-connector-odbc/build/libmaodbc.dylib (compatibility version 0.0.0, current version 0.0.0)
    /usr/local/opt/libiodbc/lib/libiodbcinst.2.dylib (compatibility version 4.0.0, current version 4.25.0)
    /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1252.50.4)
    /usr/lib/libiconv.2.dylib (compatibility version 7.0.0, current version 7.0.0)
    /Library/MariaDB/MariaDB-Connector-ODBC/libssl.1.0.0.dylib (compatibility version 1.0.0, current version 1.0.0)
    /Library/MariaDB/MariaDB-Connector-ODBC/libcrypto.1.0.0.dylib (compatibility version 1.0.0, current version 1.0.0)

# otool -L libssl.1.0.0.dylib 
libssl.1.0.0.dylib:
    /usr/local/opt/openssl/lib/libssl.1.0.0.dylib (compatibility version 1.0.0, current version 1.0.0)
    /Library/MariaDB/MariaDB-Connector-ODBC/libcrypto.1.0.0.dylib (compatibility version 1.0.0, current version 1.0.0)
    /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1213.0.0)
  1. DONE. Now you could start MSExcel again and try connect to DSN.
    It worked for me on my OSX box with MariaDB ODBC driver and MSExcel.

p.s.
The problem is related with MariaDB ODBC driver, so you must to write questions to MariaDB team.

@TallTed

This comment has been minimized.

Copy link

@TallTed TallTed commented Mar 26, 2019

@Hochiss - To (hopefully) clarify my earlier -- I did not mean for you to remove the link to the Microsoft forum (and it would be good now both to restore that link, and to add the link(s) to the report(s) you've made to/about MariaDB and/or others).

My point was that when posting such links to external forums here -- especially when those links require a login to read -- those links should accompany (not stand in for) the information provided in the other space, so that someone working on this (or any other) github-based Open Source project does not need to create an account on some other forum, especially just to read details about a report made here.

(It would also be good to go back to your posts on those other forums, and add links to your initial comment in this post, and more importantly to @smalinin's response, such that it's easier for current and future readers there to find that answer.)

@Hochiss

This comment has been minimized.

Copy link

@Hochiss Hochiss commented Mar 27, 2019

@smalinin thanks for stopping by..

I tried to reproduce your system.log ... but not for me... My feedback was always this:

"Microsoft Excel[14049]: dynamic_cast error 2: One or more of the following type_info's has hidden visibility. They should all have public visibility. N3Mso9Clipboard22IMsoClipboardAsyncDataE, N3Mso9Clipboard3Ole16OleClipboardDataE, N3Art10DataObjectE."

I will try to come back to you also @TallTed (thanks!) for your further test /amendment in the posts.

/BR

@sensukrit7

This comment has been minimized.

Copy link

@sensukrit7 sensukrit7 commented Jun 3, 2019

Hello @smalinin . I moved the file to the /Library/ODBC as suggested, but I still have issue with ODBC connection set up for Mac Excel. I created the User DSN on ODBC Administrator and IODBC Administrator and tested the connection for the same which is successfully connected. But when I try to connect it on Excel for Mac using External Database option in Data Tab, then it has the same issue: IM003 -- [iODBC][Driver Manager]Specified driver could not be loaded.
Can you please suggest some solution.

@sensukrit7

This comment has been minimized.

Copy link

@sensukrit7 sensukrit7 commented Jun 3, 2019

I am using 64-bit Excel and Mac OS is latest version

@TallTed

This comment has been minimized.

Copy link

@TallTed TallTed commented Jun 3, 2019

@sensukrit7 -

As you are new to this thread, it's hard to tell exactly what you're working with (particularly, what ODBC driver? both name and version generally matter), and what you've done.

Have you applied all updates from Microsoft to your Excel? Is that Excel 2016, Excel 2019, or Excel 365?

What file did you move to /Library/ODBC? Please also confirm that this was not accidentally /Users/username/Library/ODBC.

Are you able to create and test both User and System DSNs with this driver with only the 64-bit iODBC Administrator64.app, or only the 32-bit iODBC Administrator.app, or both?

Are you able to connect to and query these DSNs through iODBC Test.command and/or iODBC Test.app?

What happens when you try to have Excel connect to each of these DSNs?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
You can’t perform that action at this time.