search

openlink / iODBC

An open-source ODBC driver manager and SDK that facilitates the development of database-independent applications on linux, freebsd, unix and MacOS X platforms.
http://www.iodbc.org/
Other
164 stars 53 forks source link

Unable to connect between Office 365 Excel and Mysql database #97

Open BUMPRW opened 10 months ago

BUMPRW commented 10 months ago

Using: 2020 MAC mini M1 with OS14.2.1 Sonoma, also tested on 2019 MacBookPro INTEL Using the Mysql drivers 8.2 iODBC tests successfully in standalone from DSN and System DSN After starting Excel and from 'Data', 'GetData', 'From Data (Microsoft Query)' On the MAC Mini unable to Add and configure a DSN, but that works fine on the MacBookPro On both using the test function from system DSN the connection fails with: (the MacBookPro DSN test yielded the same result) [iODBC][Driver Manager]dlopen(/usr/local/mysql-connector-odbc-8.2.0-macos13-x86-64bit/lib/libmyodbc8w.so, 0x0006): tried: '/usr/local/mysql-connector-odbc-8.2.0-macos13-x86-64bit/lib/libmyodbc8w.so' (file system sandbox blocked open()), '/System/Volumes/Preboot/Cryptexes/OS/usr/local/mysql-connector-odbc-8.2.0-macos13-x86-64bit/lib/libmyodbc8w.so' (no such file), '/usr/local/mysql-connector-odbc-8.2.0-macos13-x86-64bit/lib/libmyodbc8w.so' (file system sandbox blocked open())

Again both test fine outside of Excel in both system and DSN. I have contacted Microsoft and Apple and they don't have a clue, but Apple has all this information and more. I did attempt to add a subdirectory with sim link by doing a csrutil disable, but that section of the software is readonly.

I did try the Microsoft drivers and they appear to be broke and tech support at Microsoft didn't' disagree

Any help would be appreciated.

Richard Bump

HughWilliams commented 10 months ago

The [iODBC][Driver Manager]dlopen(/usr/local/mysql-connector-odbc-8.2.0-macos13-x86-64bit/lib/libmyodbc8w.so, 0x0006): tried: '/usr/local/mysql-connector-odbc-8.2.0-macos13-x86-64bit/lib/libmyodbc8w.so' (file system sandbox blocked open()) error probably indicates there is some form of notarisation capability issue with the Office 365 Excel being used as indicated on this stack overflow post.

Is the MySQL ODBC driver Intel 64bit only as implied it the directory path name and can be confirmed with the command:

file /usr/local/mysql-connector-odbc-8.2.0-macos13-x86-64bit/lib/libmyodbc8w.so

As if so and Excel is an Intel only app the macOS Rosetta 2 needs to be installed on M1 machines for it to work. See this post on OpenLink ODBC Driver and iODBC usage on Apple Silicon M1 machines for more details.

Note also that OpenLink provide MySQL ODBC Drivers for macOS with both Intel 64 and M1 arm64 binaries bundled and will work against Intel 64 or arm64 application on macOS M1 machines.

smalinin commented 10 months ago

Read here https://github.com/openlink/iODBC/issues/29 at first Note:

BUMPRW commented 10 months ago

Hugh Williams, Thank you for the response. I noted there was only one suggestion on the Stack Exchange link you provided and I had tried something similar. I went through the process of disabling system integrity, shutdowns and reboots, csrutil disable and csrutil enable, and in-between those steps adding a symbolic link, but that failed since the /System/Preboot is read only. Without the correct bind commands from Apple, I'm hesitant to risk my computer in establishing the link in that read only portion of the software. To get what I did done was over a 16 step procedure and I'm not sure that is worthwhile for software that is being frequently updated. When I talked to Apple, they seemed to want to help, but pointed back to Microsoft. When I talked to Microsoft, they were aware that they had a problem, but didn't have a solution. In February 2023, Microsoft appeared to have the problem fixed in a Beta version of MS Office 365, but we are now beyond that version in stable and the ability to connect to an SQL database on a silicon MAC does not appear, nor does it appear on an Intel Mac that runs Sonoma. So I'm still researching and looking to see if I can solve this one. Again, thanks you for the response.

BUMPRW commented 10 months ago

Smalinin, just saw your comment after my last comment. Thanks, I'll give that a try.

BUMPRW commented 10 months ago

Smalinin, Just finished trying. As before works outside of Excel, but testing inside Excel yields the same error message. I think I need to fix that problem with '/System/Volumes/Preboot/Cryptexes/OS' but without Apple providing the proper commands and sequence I'm not willing to risk my computer, although Macs are very resilient. Again, thanks, appreciate all the help I can get.

smalinin commented 10 months ago

@BUMPRW The next solution must works => https://github.com/openlink/iODBC/issues/29#issuecomment-476630516 It is for MariaDB odbc driver, but basic idea is the same. Did you use these steps ?

BUMPRW commented 10 months ago

Smalinin, Nope, moved it to ~/Library/ODBC. Will give it a try. Thanks

BUMPRW commented 10 months ago

Smalinin,

  In the original configuration all the crypto libraries were in the same directory as the driver, except v1.1 which supposedly had an end of life last Feb-April timeframe.  When I questioned Microsoft why we were loading version 1.1 for their driver, I got a we need to sort that out.  So I need to move the 1.1 files over there also I assume . . . bad word to use but is that correct?
smalinin commented 10 months ago
  1. You need move ALL libs, that is used by drivers and that is located in /usr/local/..

  2. What kind of MySQL ODBC installer did you use the

    • DMG Archive -- mysql-connector-odbc-8.2.0-macos13-x86-64bit.dmg
      OR
    • Compressed TAR Archive -- mysql-connector-odbc-8.2.0-macos13-x86-64bit.tar.gz

    I have rechecked Compressed TAR Archive installer, it contains all files, that could be simply copy to /Library/ODBC/.. without additional fix, the library pathes use RPATH already, so it must work, I think. Try download Compressed TAR Archive installer, extract it and just copy ALL files from ./lib/* to /Library/ODBC/...

BUMPRW commented 10 months ago

this is what tool looks like for ~/Library/ODBC/mysql-connector-odbc-8.2.0-macos13-arm64/lib otool -L libmyodbc8w.so
libmyodbc8w.so: @rpath/libssl.3.dylib (compatibility version 3.0.0, current version 3.0.0) @rpath/libcrypto.3.dylib (compatibility version 3.0.0, current version 3.0.0) /usr/lib/libresolv.9.dylib (compatibility version 1.0.0, current version 1.0.0) /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1319.0.0) @rpath/libiodbcinst.dylib (compatibility version 1.0.0, current version 3.52.15) /usr/lib/libc++.1.dylib (compatibility version 1.0.0, current version 1300.32.0) ls -l for same above total 46920 -r--r--r-- 1 root wheel 2192208 Jan 5 14:02 libcrypto.1.1.dylib -rw-r--r-- 1 root wheel 3471232 Jan 5 10:23 libcrypto.3.dylib lrw-r--r-- 1 root wheel 17 Jan 5 10:23 libcrypto.dylib -> libcrypto.3.dylib -rw-r--r-- 1 root wheel 392320 Jan 5 10:23 libfido2.1.13.0.dylib lrwxr-xr-x 1 root wheel 21 Jan 5 10:23 libfido2.1.dylib -> libfido2.1.13.0.dylib lrwxr-xr-x 1 root wheel 16 Jan 5 10:23 libfido2.dylib -> libfido2.1.dylib -rwxr-xr-x 1 root wheel 8413824 Jan 5 10:23 libmyodbc8a.so -rwxr-xr-x 1 root wheel 8430560 Jan 5 10:23 libmyodbc8w.so -r--r--r-- 1 root wheel 506336 Jan 5 14:05 libssl.1.1.dylib -rw-r--r-- 1 root wheel 598912 Jan 5 10:23 libssl.3.dylib lrwxr-xr-x 1 root wheel 14 Jan 5 10:23 libssl.dylib -> libssl.3.dylib

Still have the same problem, but we gave it a try. Hope I have this right. I also compared to the original and except for the openssl 1.1 files it was the same. The tool output was identical, only openssl 3.0. It doesn't see the earlier version of openssl and although Microsoft didn't come out and say it, they certainly implied it when they said they need to sort that one out

TallTed commented 10 months ago

@BUMPRW — I suggest that you code fence terminal output and similar blocks, as this generally helps with clarity. A line with just three backticks (```) above and below each block will do the job. It can also be helpful to code fence terminal commands, SQL queries, and similar short inline items, by putting a single backtick before and after (for example, `ls -l`, which displays as ls -l).

BUMPRW commented 10 months ago

Tall Ted, Thanks, but I'm just a worthless pogue who comes to GitHub when I need help or clarity. Frankly, I'm clueless and will have to look that one up, but will give it a try, so it would be 'otool -L libmyodbc8w.so' 'libmyodbc8w.so:' '@rpath/libssl.3.dylib (compatibility version 3.0.0, current version 3.0.0)' '@rpath/libcrypto.3.dylib (compatibility version 3.0.0, current version 3.0.0)' '/usr/lib/libresolv.9.dylib (compatibility version 1.0.0, current version 1.0.0)' '/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1319.0.0)' '@rpath/libiodbcinst.dylib (compatibility version 1.0.0, current version 3.52.15)' '/usr/lib/libc++.1.dylib (compatibility version 1.0.0, current version 1300.32.0)'

or should i start off as:

'otool -L ~/Library/ODBC/mysql-connector-odbc-8.2.0-macos13-arm64/lib/libmyodbc8w.so'

because I just normally 'cd' to the directory I'm working with

smalinin commented 10 months ago

Everything works for me on macOS 12.6 on M1 cpu I did next:

[MySQL] Driver = /Library/ODBC/MySQL/libmyodbc8w.so


- add DSN to  /Library/ODBC/odbc.ini

[ODBC Data Sources] mysql = MySQL

[mysql] Driver = /Library/ODBC/MySQL/libmyodbc8w.so Server=192.168.0.22 Database=world



- run macOS app `iODBC Administrator` and tested DSN
<img width="672" alt="Screen Shot 2024-01-06 at 12 08 33 AM" src="https://github.com/openlink/iODBC/assets/765462/8eb33b5d-31d1-4bbc-90f6-da92c1a4d905">

- run MS Excell and try Import data `From Database (Microsoft Query)`

https://github.com/openlink/iODBC/assets/765462/f63886b2-6d69-4a91-a98d-841c3fb4b0db
TallTed commented 10 months ago

Backticks (`) a/k/a GRAVE ACCENTs (U+0060); not single-quotes (') a/k/a APOSTROPHEs (U+0027).

Inline would look like `otool -L libmyodbc8w.so` (or `otool -L ~/Library/ODBC/mysql-connector-odbc-8.2.0-macos13-arm64/lib/libmyodbc8w.so`), and the block that follows would look like this to you, as the writer --

```
libmyodbc8w.so:
@rpath/libssl.3.dylib (compatibility version 3.0.0, current version 3.0.0)
...
/usr/lib/libc++.1.dylib (compatibility version 1.0.0, current version 1300.32.0)
```

-- which would then look like this to us, as the readers --

libmyodbc8w.so:
@rpath/libssl.3.dylib (compatibility version 3.0.0, current version 3.0.0)
...
/usr/lib/libc++.1.dylib (compatibility version 1.0.0, current version 1300.32.0)

This is particularly important when the blocks include characters which GitHub might interpret as Markdown, radically changing what is presented to your readers. Here, it just helps distinguish Terminal input/output Vs. human writing (yours or ours).

BUMPRW commented 10 months ago

Sergey,

  Okay, 1st I'm going to make mine look like yours, except I'm running OS14.2.1.  By the way I haven't run it in a couple of years but I know it works on my Mac-mini which is running High Sierra.  If that doesn't work I'm going to uninstall the DMG version and reinstall using 'mysql-connector-odbc-8.2.0-macos13-arm64.tar.gz'.  Again, thanks.
BUMPRW commented 10 months ago

Tall Ted,

 Where is that on the keyboard? alt character? 

 I use those character codes in Latex, R and Python and when I want to print something special, but that's like $$\U0060$$
TallTed commented 10 months ago

@BUMPRW — On my US keyboards, ` is lowercase on the key where uppercase is tilde ~, at upper-left, to the left of 1, below ESC and above TAB.

BUMPRW commented 10 months ago

Tall Ted,

 ``` Okay, I've never used that key.  They don't teach that in mid 1970s Fortran crash courses for linear methods.  Thanks.
BUMPRW commented 10 months ago

Sergey,

  Step1 failed with the same results. 

So I'm cleaning out the old and loading the .gz file
smalinin commented 10 months ago

First - your DSN must work in iODBC Administrator app when you click on Test connection

BUMPRW commented 10 months ago

Sergey,

 It works fine when Excel is not running.  

As soon as you try to test it in Office 365 Excel v 16.80 you get the message.
smalinin commented 10 months ago

I updated macos to Sonoma 14.2.1 - it works also in MS Excell . I use MS Excell ver 16.80 (23121017)

smalinin commented 10 months ago

Try to check your Excell app with Finder, open Get Info for app and recheck, that Excell app is Universal and that you don't have checked option Open using Rosetta - actual for M1 cpu

BUMPRW commented 10 months ago

Sergey, I'm going to reload MsOffice and rosetta was not checked.

Another strange behavior is that User DSN does not allow adding in Excel.

I don't know of any strange configurations I've done with Excel, but I do use it frequently.

BUMPRW commented 10 months ago

Sergey,

I have a user DSN configured in iODBC, I just have the server and database data filled in.  You appear to use your IP address, I use local host but have tried the IP address and 127.0.0.1 could that be a factor?
smalinin commented 10 months ago

I have MySQL server on an another host.

BUMPRW commented 10 months ago

Symptoms are completely different today. I can see changes I make in iODBC outside Excel, and I did use your init setups initially Inside of Excel I see nothing but the driver and get Request Failed on any attempt. I've played around with permissions, but even with everyone that doesn't make a difference.

TallTed commented 10 months ago

Tall Ted,

 ``` Okay, I've never used that key.  They don't teach that in mid 1970s Fortran crash courses for linear methods.  Thanks.

Note that when you use the ``` Markdown code fence on leading and trailing lines, you need to include line breaks in your text, because it's like HTML's <code><pre> ... </pre></code>. When you use the ` Markdown code fence, it is more like HTML's <code> ... </code> (no <pre>).

Also note, I did not suggest wrapping all of your messages in code fences; only the CODE (e.g., odbc.ini content) and Terminal blocks (e.g., ls -l command and output).

To your continuing issues --

It is generally best to use only System DSNs, for a variety of reasons, including the way the sandboxing works on recent versions of macOS. Even if you need to switch to User DSNs or File DSNs later, I suggest you start by getting a System DSN working.

You may also want to consider using a commercial ODBC driver, such as one from OpenLink Software (maintainer of iODBC). We have strong incentive to make our drivers work "out of the box", which you can test with a $0.00 (FREE!) 30-day evaluation license. Only you can decide whether continuing to tinker with mysql-connector-odbc will "cost" you less than the license fees for such a commercial driver (see current special offers here).

BUMPRW commented 10 months ago

Tall Ted,

Thanks, I'll look into that, but I was trying to avoid the expense because I use databases infrequently.

The connector saves time in converting the output to a ".csv" file.

I have a few more thoughts regarding permission issues that I'm going to try.

I have read some of your previous posts and have reset permissions on the ".ini" files, plus I'm going to re-read Sergey's notes.

I reconfigured last night following his notes, I thought, but Excel is still not reading the odbc.ini file.

Though my wife and I are much older, she is a grad-student and sometimes it is easier to deal with databases than large Excel files.

It is too bad Borland's Dbase is no longer around, I wrote some pretty heavy duty stand-alone programs in that language back in the mid 80s.

Again, Thanks, got to work on consolidating some "csvs", but if I solve my Dbase problem, I'll post a solution.

P.S. How do you get this editor out of the code mode? It seems to randomly jump into that mode, so I'm spacing sentences

TallTed commented 10 months ago

"Code mode" is turned on and off (for either selected text or text as you're entering it) by clicking the <> in the Write | Preview | H B I ... "Add a comment" toolbar. It can also be started by starting any given line with 4 spaces; ending such a start requires an empty line. You may find GitHub's Markdown documentation (also linked from the bottom of the "Add a comment" section, where it says "Markdown is supported") helpful in figuring this part out.