Igor Delovski Board

[Ike]

adc: MySQL on Mac OS X

If you're running Mac OS X Server, you are in luck - MySQL is already there.
Just go to Applications/Server/MySQL Manager to access it. If you are running
Mac OS X Client, you'll have to install MySQL.

If you have already installed a version of MySQl and want to upgrade, I can
recommend the upgrade instructions from http://www.entropy.ch. For a new
installation, follow the Mac OS X installation instructions for the MySQL
provided binary distribution ( a true Mac OS X installer package file) at
http://dev.mysql.com/doc/refman/5.0/en/mac-os-x-installation.html and be
done with it.

/usr/local/mysql/bin/mysql
/usr/local/mysql/bin/mysqldump

[Ike]

MySQL GUI Tools Downloads

"This is the MySQL GUI Tools Bundle for 5.0. It includes the following products:

* MySQL Administrator 1.2 Generally Available (GA)
* MySQL Query Browser 1.2 Generally Available (GA)
* MySQL Migration Toolkit 1.1 Generally Available (GA)

The major features of the individual products are described on the product
information pages."

[Ike]

MySQL C API

"The C API code is distributed with MySQL. It is included in the mysqlclient
library and allows C programs to access a database.

Many of the clients in the MySQL source distribution are written in C. If you
are looking for examples that demonstrate how to use the C API, take a look
at these clients. You can find these in the clients directory in the MySQL source
distribution."

[Ike]

UCL: Calling MySQL from C

"Full details of the C API (Application Program Interface) are given in the
MySQL manual"

Mark's Blog: A Tour of the MySQL C API

"Using Eclipse, we'll develop an example application that will document key
structures, and demonstrate several API calls. This article will also cover
configuring Ubuntu and Eclipse for MySQL client development."

[XNote]

Forum: MySQL Forums & MySQL Forums :: C/C++

[delovski]

Dev Shed Forums > Databases > MySQL Help

"MySQL Help forum discussing administration, SQL syntax, and other
MySQL-related topics. MySQL is an open-source relational database
management system (RDBMS)."

[Ike]

Dan Benjamin: Installing MySQL on Mac OS X

"What follows are instructions for building and installing MySQL 5 on Mac OS X.
These instructions should work perfectly on both Tiger and Leopard."

[delovski]

uf - SQL tutorial?

"Does anyone know were i could find a good free sql tutorial?"

[delovski]

MF - MySQL Objective C API?

"I've found a few links about a MySQL Objective C API - however I haven't
been able to get the examples to work so far...

Question is... Anyone have any experience coding on XCode 3 and MySQL?"

[XNote]

cboard - Accessing an SQL database through C++

"I'd like to access an SQL database from within a Win32 application using C++."

[delovski]

MF: Which DBMS in Obj-C?

"Basically I just want a way to use a database with Objective-C.

I could use C as a last resort. "

[XNote]

MF - Using mysql with cocoa

"I would need to use mysql with cocoa for my app and found only the
outdated version of some library, what would you guys recommend? Is
there any new ones possibly because I read that OS X doesn't support
mysql already."

[delovski]

mf - How to connect mysql remote database from iphone

"Here's a blog post where someone has listed the steps to build libmysql
for iPhone. It should be exactly what you need.
...
If you want to save yourself the trouble of having to compile the static lib-
rary yourself, I have a 6 way fat version as part of my MysqlConnector for
Objective-C. You can find more details on my blog."

[delovski]

MF - MySQL connector in Xcode

"Objective C is built on top of C and there are native OSX libraries for My-
SQL, so just include it like you would with the normal MySQL library. You
can probably practically read the instructions for using MySQL with GCC
and be good to go..."

[Ike]

slashdot - Installation of MySQL server on Mac OS X Lion

"1) Download the latest 64-Bit DMG Archive (Apple Disk Image) from MySQL
Site...
2) ..."

[delovski]

cp - Accessing: MySQL Database using MySQL C API

"Today MySQL database is the world's most popular open source database
because of its fast performance, high reliability, ease of use, and dramatic
cost savings.

It is very popular in UNIX hosting packages available in all world, then in this
article, I will show how you can access this very powerful relational database
system.

We will encapsulate the provided MySQL C API in a set of C++ classes, and
in the next article, I will use these same classes to do the same thing in
PostgreSQL.

Well, by now let?s start with MySQL."

[delovski]

cs.wichita.edu - MySQL C API by Example

"I see that lots of c/c++ authors are looking for MySQL API examples in
c/c++. So I thought to make MySQL C API examples in this web site. And
in near future I will publish this as book with a full web system as bonus."

[Ike]

mysql.com - MySQL 5.5 Reference Manual

"Chapter 2 Installing and Upgrading MySQL

2.1 General Installation Guidance
2.2 Installing MySQL on Unix/Linux Using Generic Binaries
2.3 Installing MySQL on Microsoft Windows
2.4 Installing MySQL on OS X
2.5 Installing MySQL on Linux
2.6 Installing MySQL Using Unbreakable Linux Network (ULN)
2.7 Installing MySQL on Solaris and OpenSolaris
2.8 Installing MySQL on FreeBSD
2.9 Installing MySQL from Source
2.10 Postinstallation Setup and Testing
2.11 Upgrading or Downgrading MySQL
2.12 Environment Variables
2.13 Perl Installation Notes"

[Ike]

mysql.com - 2.4 Installing MySQL on OS X

"2.4.1 General Notes on Installing MySQL on OS X
2.4.2 Installing MySQL on OS X Using Native Packages
2.4.3 Installing a MySQL Launch Daemon
2.4.4 Installing and Using the MySQL Preference Pane"

How to remove connector:

sudo rm -rf /var/db/receipts/com.mysql.*

[Ike]

4.7.2 mysql_config ? Display Options for Compiling Clients

"mysql_config provides you with useful information for compiling your
MySQL client and connecting it to MySQL. It is a shell script, so it is available
only on Unix and Unix-like systems."

[Ike]

https://code.google.com/p/mysql-cocoa-framework/downloads/list

This program is a standard API for connecting Cocoa applications to MySQL.

The codebase was originally written by Serge Cohen and found at https://sourceforge.net/projects/mysql-cocoa/. He seems to have abandoned the project. The latest release of the project was in February 2006 and there was for PPC only. I wanted to make sure there was a Universal Binary version was available to anyone who wanted to use this library. It's a Universal Binary with support for PPC, PPC64, i386, and x86_64.

[delovski]

so - dyld: Library not loaded: libmysqlclient.18.dylib

"dyld: Library not loaded: libmysqlclient.18.dylib

trying:

sudo ln -s /usr/local/mysql/lib/libmysqlclient.18.dylib /usr/lib/libmysqlclient.18.dylib

I got:

ln: /usr/lib/libmysqlclient.18.dylib: Operation not permitted"

[delovski]

github - mysql_store_use_result_stmt_cursor.c

"mysql api example, include store_result, use_result, stmt, server cursor,
fetch row setting"

[delovski]

lgallardo - MySQL Prepared statements for C

"Although the book explains how to execute a SQL sentence with prepared
parameters (prepared statements) and how to get data from a query, at the
end you can't see the whole thing together so I decided to post a complete
example.

The important thing here is the fact that there are two special structures from
the MySQL API: param which set the parameters fro the query, and result
which will be used to store the data retrieved from the database."

[Ike]

Official - Chapter 23 Connectors and APIs

23.8 MySQL C API

... 23.8.8 C API Prepared Statements

[Ike]

Tim Cotten - A Taste of MySQL in C

"This article discusses the type of C code necessary to use the standard MySQL
C library wrappers. Examples are provided in an Ubuntu environment using the
libmysqlclient-dev and libmysqlclient20 packages."

[Ike]

Why doesn't MySQL use the primary key on JOIN plus ORDER?

"I'm never using anything except the primary key in the customer_order table
for ordering, and the primary key in the customer_invoice table for the JOIN.

So then, in the name of all that is good and right, why does it switch to filesort
all of a sudden?! And more importantly, how do I avoid this?"

[Ike]

Using mysql concat() in WHERE clause?

"select * from table where concat_ws(' ',first_name,last_name) like '%$search_term%';"

[Ike]

so - MySQL : Concat in where clause not working

"select * from postal_address where concat(address1, ' ', address2) = 'ABC DEF';"

[Ike]

10.2 Character Sets and Collations in MySQL

mysql> SHOW CHARACTER SET;

10.3.5 Column Character Set and Collation

[Ike]

https://info.sice.indiana.edu/~dingying/Teaching/I590SQL/order.txt

(script from the book Beginning databases with MySQL)

Found here:

https://info.sice.indiana.edu/~dingying/S511Fall2012.html
https://info.sice.indiana.edu/~dingying/Z511Spring2017.html

[Ike]

https://downloads.mysql.com/archives/c-c/

Mac OS X 10.5 (PowerPC), Compressed TAR Archive
Mac OS X 10.5 (x86, 64-bit), Compressed TAR Archive
Mac OS X 10.5 (x86, 32-bit), Compressed TAR Archive
Mac OS X 10.5 (PowerPC, 32-bit), Compressed TAR Archive
Mac OS X 10.4 (x86, 32-bit), Compressed TAR Archive
Mac OS X 10.4 (PowerPC), Compressed TAR Archive
Mac OS X 10.4 (PowerPC, 32-bit), Compressed TAR Archive

...

Mac OS X 10.6 (x86, 32-bit), DMG Archive
Mac OS X 10.6 (x86, 64-bit), DMG Archive
Mac OS X 10.6 (x86, 64-bit), Compressed TAR Archive

...


Archive - Index of /pub/mysql/

[DIR] Connector-C/ 13-Jan-2015 15:50 1K
[DIR] Connector-C++/ 26-Nov-2014 11:29 1K
[DIR] Connector-J/ 04-Nov-2014 03:00 1K
[DIR] ...
[DIR] MySQL-4.1/ 15-Oct-2013 16:58 1K
[DIR] MySQL-5.0/ 08-Oct-2012 08:32 1K
[DIR] MySQL-5.1/ 06-Nov-2014 11:23 1K
[DIR] MySQL-5.2/ 08-Oct-2012 09:00 1K
[DIR] MySQL-5.4/ 08-Oct-2012 09:00 1K
[DIR] MySQL-5.5/ 27-Nov-2014 04:51 1K
[DIR] MySQL-5.6/ 01-Feb-2015 14:58 1K
[DIR] MySQL-5.7/ 25-Sep-2014 09:05 1K
[DIR] MySQL-6.0/ 08-Oct-2012 08:59 1K
[DIR] ...
[DIR] NT/ 28-Aug-2002 14:01 1K
[DIR] SCO/ 06-Apr-2006 18:27 1K
[DIR] Win32/ 01-Feb-2007 04:54 1K
[DIR] eventum/ 08-Oct-2012 08:46 1K

[Ike]

git - OHMySQL

"The library supports Objective-C and Swift, iOS and macOS. You can connect to
your remote MySQL database using OHMySQL API. It allows you doing queries
in easy and object-oriented way. Common queries such as SELECT, INSERT,
DELETE, JOIN are wrapped by Objective-C code and you don't need to dive into
MySQL C API."

git - mysql-ios

"This repo is a convenience project for building mysql for iOS (in particular
mysqlclient). It contains the mysql source at version 8.0.12 and generated
or modified XCode project for building mysqlclient for iOS."

[Ike]

Resetting MySQL Root Password on Mac OS

"Here's the text for the temporary password that you can search on your mac.
[Note] A temporary password is generated for root@localhost: rt4rvI,%lfr<"

[Ike]

so - c program to add a user to mysql

"You can however use the mysql_change_user() call of the API.

syntax:

my_bool mysql_change_user(MYSQL *mysql, const char *user, const char *password, const char *db)

More info in the respective api description here, it's implemented since MySQL 3.23.3."

[Ike]

so - How to get next/previous record in MySQL?

"next record:

SELECT * FROM foo WHERE id > 4 ORDER BY id LIMIT 1;

previous record:

SELECT * FROM foo WHERE id < 4 ORDER BY id DESC LIMIT 1;"

[Ike]

git - krglaws / 3rd-Place

"Basic HTTP server + frontend

My end goal is to have something like a text only reddit. For now it can only
handle GET requests."

[Ike]

r - How to check if indexes are created correctly

"Use:
> SHOW INDEXES FROM table_name;
To list the indexes.
Use:
> EXPLAIN the_query;
To check if your query utilize the indexes;"

[Ike]

so - Proper place to store description about a MySQL database

"Is there a proper place to store a high level description of a database?"
"Comment metadata is not available for MySQL databases, but you can
create a table to store some comments: (I have this table in a generic tools
database)"

[Ike]

MySQL C API Handle TEXT field

"... how to be with TEXT fields where data length can vary from small sizes to
megabytes?"

"Accomplish this ... Before invoking mysql_stmt_fetch() to retrieve individual
rows, pass STMT_ATTR_UPDATE_MAX_LENGTH to mysql_stmt_attr_set(), then
invoke mysql_stmt_store_result() to buffer the entire result on the client side.
Setting the STMT_ATTR_UPDATE_MAX_LENGTH attribute causes the maximal
length of column values to be indicated by the max_length member of the
result set metadata returned by mysql_stmt_result_metadata()."

[Ike]

so - Troubleshooting 'Illegal mix of collations' error in mysql

"This is generally caused by comparing two strings of incompatible collation or
by attempting to select data of different collation into a combined column.

Your solution is to specify a shared collation for the two columns within the
query. Here is an example that uses the COLLATE clause. Another option is to
use the BINARY operator"

[Ike]

r - MySQL Database, User/Password Creator

"This Script helps you to create a new MySQL database with specific user
and password with just a single command."

[Ike]

arctype.com - Search MySQL Like Google Using Match and Full-Text Search

"You can have Google-like superpowers to find words and phrases across your
entire database. Search engines use Full-Text Search to find results in data-
bases. These databases often contain a lot of textual data."

[Ike]

r - A single person answered 76k questions about SQL on
StackOverflow. Averaging 22.8 answers per day, every day,
for the past 8.6 years.

"He also literally wrote a book on SQL. Several of them, actually. And on
datamining as well."

[Ike]

jetbrains - DataGrip - Many databases, one tool

"Meet DataGrip, our new database IDE that is tailored to suit the specific needs
of professional SQL developers.
Allows you to execute queries in different modes and provides a local history
that keeps track of all your activity and protects you from losing your work.
DataGrip provides context-sensitive code completion, helping you to write SQL
code faster. Completion is aware of the tables structure, foreign keys, and even
database objects created in code you're editing."

[Ike]

https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

https://downloads.mysql.com/archives/c-c/

[Ike]

so - Signed or unsigned in MySQL

"Warning, there is an issue with Unsigned Int (UINT) and Entity Framework or ADO.NET. It might involve an integration problem by MySql Connector version 6."

[Ike]

https://dev.mysql.com/doc/refman/8.0/en/macos-installation-pkg.html

https://downloads.mysql.com/archives/community/

[Ike]

so - Easiest way to copy a table from one database to another?

[Ike]

Hayden James - 'MySQL server has gone away' error - Solution(s)

"Also, check the variables: net_read_timeout, net_write_timeout and
interactive_timeout. Adjust or add the following lines in my.cnf to meet
your requirements..."

[Ike]

Derek Jones - MySQL 5.7 on OS X: Server Has Gone Away

"MySQL 5.7 will break on OS X after too many connections are left open
within eight hours. Restarting the server will fix it until the next time this
occurs. You might be able to prevent this by dramatically reducing MySQL's
interactive_timeout and wait_timeout."

[Ike]

popsql.com - How to Duplicate a Table in MySQL

"You can duplicate or "clone" a table's contents by executing a CREATE TABLE
... AS SELECT statement:

To inherit all table definitions, use the CREATE TABLE... LIKE syntax..."

[Ike]

kb.synology.com - Synology Knowledge Center - MariaDB

"MariaDB 5 is a community-developed fork of MySQL and is one of the most
widely used open-source relational database management system (RDBMS).
It is highly compatible with MySQL and allows users to effortlessly migrate
databases from MySQL to MariaDB..."

ssh - access denied - Do you have user homes enabled? If not, you may get
access denied as your user account won't have permission to browse to the
root directory.

[Ike]

mariadb.com - Authentication Plugin - mysql_native_password

"The easiest way to create a user account with the mysql_native_password
authentication plugin is to make sure that old_passwords=0 is set, and then
create a user account via CREATE USER that does not specify an authentication
plugin, but does specify a password via the IDENTIFIED BY clause."

github.com - Use this query instead:

[Ike]

Enable the remote access of MariaDB in Synology NAS

[Ike]

syno - How can I sign in to DSM/SRM with root privilege via SSH?

"Enter the password of your DSM/SRM's administrator account.

Type sudo -i and press Enter.

Enter the password of your DSM/SRM's administrator account again."

Create an custom/extra my.cnf, besides my_port.cnf and synology.cnf:

> cd /var/packages/MariaDB10/etc

[Ike]

eversql.com - Free Text to SQL, AI-powered

"Auto-generate SQL queries from text for free. Just select your database type
and write a textual description of the SQL query"

[Ike]

sex - Table is dropped but ibd file still exists

"I was dropping some unwanted tables I have in the MySQL. When I run a drop
table, its showing table doesnot exists. But when a check in the data directory,
the .ibd file exists and its almost 1GB. How can i free that space/drop table
now ?"

[Ike]

so - Schrodingers MySQL table: exists, yet it does not

"The fix turns out to be easy; at least what I worked out, worked for me.
Create a table "zzz" on another MySQL instance, where zzz is the problem
table name. (i.e. if the table is called schrodinger, substitute that for zzz
whever written.) It does not matter what the definition of the table is. It's a
temporary dummy; Copy the zzz.frm file to the database directory on server
where the table should be, making sure file ownership and permissions are still
correct on the file. On MySQL, you can now do "show tables;", and the table
zzz will be there. mysql> drop table zzz; ...should now works. Clear any
zzz.MYD or ZZZ.MYI files in the directory if necessary."

[Ike]

so - How can I dump a MySQL database from mysql c library

"I want to archive my database of mysql. Kindly give me some guide lines how
I can make it possible, I am using mysql c library for insertion and selection
etc. I dont know how to use dump command."

[Ike]

Configuring MariaDB for Optimal Performance

"Note that by default MariaDB is configured to work on a desktop system and
should because of this not take a lot of resources. To get things to work for a
dedicated server, you have to do a few minutes of work."

[Ike]

so - mariaDB 10.5.8 very slow stored view after moving to newer version

"Only one function reduced the wait by 10s, skip-name-resolve."

[Ike]

medium - A Modern Guide to MySQL Performance Monitoring

"MySQL performance monitoring metrics can be queried from three different
locations: server status variables, sys schema, and performance schema."

[Ike]

wiki.tnonline.net - SQLite Performance on Btrfs

"By using Write-Ahead Logging we can gain a 300% performance boost,
compared to about 25-30% increase using the nocow attribute!."

[Ike]

MySQL Innodb Performance Tuning For Disk-Bound Workloads On CMT Servers

"MySQL server's performance can be optimized using various configuration
settings. The first step is to read the configuration and system variables by
running the command:

mysql> show variables;"

[Ike]

synology.com - MariaDB 10.11x Slow performance over WAN

"Now I am finding that queries that were very fast before across a WAN (sub
2 seconds) are now taking more than 2 minutes to run. "

[Ike]

dev.mysql.com - Multiple Statement Execution Support

"CLIENT_MULTI_STATEMENTS enables mysql_real_query() & mysql_query()
to execute statement strings containing multiple statements separated by
semicolons.

At the end of the loop, call mysql_next_result() to check whether another
result exists and initiate retrieval for it if so. If no more results are available,
exit the loop."

Found via: https://stackoverflow.com/questions/77697248/how-to-execute-sql-file-with-mysql-connector-in-c

[Ike]

so - Duplicating a MySQL table, indices, and data

To copy with indexes and triggers do these 2 queries:

CREATE TABLE new_table LIKE old_table;
INSERT INTO new_table SELECT * FROM old_table;

To copy just structure and data use this one:

CREATE TABLE new_table AS SELECT * FROM old_table;

[Ike]

so - Slow Update, Delete and Insert Queries under MariaDB

"I would like to tell you how I managed to fix the problem with the slow
update, insert and delete queries.

I added this value to the my.cnf file:

innodb_flush_log_at_trx_commit = 2

After I restarted the mysql server, the server load dropped suddenly and the
update, insert and delete queries also dropped from about 0.22222 - 0.91922
seconds to 0.000013 under load."

[Ike]

systemdesign.one - How YouTube Was Able to Support 2.49 Billion Users With MySQL

References

Vitess Official Site
Vitess Architecture Official Docs
Scaling YouTube's Backend: The Vitess Trade-offs - @Scale 2014 - Data
Vitess: A Distributed Scalable Database Architecture
What Is Vitess?
Vitess on GitHub
Scalability at YouTube
Vitess Supported Databases
Do Mysql slaves run multiple threads to read the Relay log to sync up the Master's operation
11 Reasons Why YouTube Was Able to Support 100 Million Video Views a Day With Only 9 Engineers
Most Visited Websites In The World (May 2024)

[Ike]

so - How can I compile mysql-connector-c for macOS 10.13.x using C?

> brew install mysql-connector-c
> clang mysql_connect_test.c -l mysqlclient -o mysql_test
> ./mysql_test
MySQL client version: 6.1.11

....

As that lib is not signed. Simply in Entitlemens:

(in XML): ..

<dict>
<key>com.apple.security.cs.disable-library-validation</key>
<true/>
</dict>
...

[Ike]

mysql.com - Running the program on OSX fails

"Failed to load shared library liblibdbloader.dylib
dlopen(liblibdbloader.dylib, 2). Library not loaded: /usr/local/lib/liblibmysql.dylib.
Referenced from
Reason: image not found"

[Ike]

sqlprostudio.com - MySQL with Swift and Objective-C

"MariaDBKit supports Carthage. The easiest way to get started is by adding
the following to a Cartfile in your projects root directory."

[Ike]

git - Files are no more accessible in Sequoia #2092

"After the update to macOS Sequoia, I cannot add accessible files anymore.
Also all previous added files are no more accessible to Sequel Ace. They appear
red in the list."