Expose SQL server externally – Database Administrators Stack Exchange

In what sense do you want to expose the database? For reading, editing and writing by end users or forming the back end of an application installed in respective end user clients? Or you want the data therein to be readable and the associated tables written to via a RESTful application? Otherwise external access would command that you have a public IP configured on the station hosting the server database or explore paid or free VPNs like Hamachi and OpenVPN

Kafka Newbie Question – Database Administrators Stack Exchange

Pls don’t give a negative vote to this, just coz this is a n00b question. I am trying to learn this new tech, and it looks promising.

My data source, producer, consumer are all on the same server (lab setup). And:

  1. I created a Kaka topic:
    bin/kafka-topics.sh --create --topic T1 --bootstrap-server localhost:9092

  2. I loaded many csv into the topic:
    bin/kafka-console-producer.sh --topic T1 --bootstrap-server localhost:9092 < FILENAME

  3. The csv are in many GB in size, and are in the format one row for one record. Format:
    Email,event_ID,timestamp,Description. All CSV files will always have a header-row

  4. I can see all data successfully , using:
    bin/kafka-console-consumer.sh --topic T1 --from-beginning --bootstrap-server localhost:9092

Question1 How can I search and return rows only where email address equals email@foo.com.

Question2 How can I search and return rows where email address equals “email@foo.com” AND event_ID equals “Z9284M”

I am aware I can grep the relevant rows. But that will be too slow & resource intensive. Is there a way for Kafka to index the data and search efficiently?

Would appreciate all help!

mysql – Room availability – Database Administrators Stack Exchange

MariaDB-10.5 has a feature Application Time Periods – WITHOUT OVERLAPS who’s example even lists availability for rooms as an example.

The example gives:

CREATE OR REPLACE TABLE rooms (
 room_number INT,
 guest_name VARCHAR(255),
 checkin DATE,
 checkout DATE,
 PERIOD FOR p(checkin,checkout),
 PRIMARY KEY (room_number, p WITHOUT OVERLAPS)
 );

As an example syntax.

Attempting to overlap a booking will result in an error:

INSERT INTO rooms VALUES 
 (1, 'Regina', '2020-10-01', '2020-10-03'),
 (2, 'Cochise', '2020-10-02', '2020-10-05'),
 (1, 'Nowell', '2020-10-03', '2020-10-07'),
 (2, 'Eusebius', '2020-10-04', '2020-10-06');
ERROR 1062 (23000): Duplicate entry '2-2020-10-06-2020-10-04' for key 'room_number'

So in your application you need to catch the duplicate key error as an exception and treat that as already booked.

DB2 Linux authentication fails – Database Administrators Stack Exchange

I have DB2 Express-C v10.5 instance configured to authenticate against LDAP. The LDAP sever is going to be shutdown and I should configure the same DB2 instance to use Linux authentication.

I copied users from the LDAP  server to a local Linux host running DB2. Then I did shutdown the LDAP server. After that I changed DB2 authentication settings db2 update dbm cfg using SRVCON_PW_PLUGIN IBMOSauthserver (used to be IBMLDAPauthserver before) and restarted DB2.

Applications access the database with the username db2smth (name changed due to privacy reasons). I can connect to a database with db2 connect to dbname user db2inst1 using '********' but connecting to the same database as db2smth fails:

db2 => connect to dbname user db2smth using '********'

SQL30082N Security processing failed with reason "24" ("USERNAME AND/OR PASSWORD INVALID"). SQLSTATE=08001

su - db2smth and su - db2inst1 works fine which means that Linux authentication works fine.

How can I diagnose what’s wrong with the authentication?

Letsencrpyt components for MySQL? – Database Administrators Stack Exchange

So, these are the three pieces MySQL looks for in the .cnf file:

(mysqld)

ssl_ca=ca.pem

ssl_cert=server-cert.pem

ssl_key=server-key.pem

And I’m using Letsencrypt for SSL cert management. How do I transpose the certificates generated by Letsencrpyt (on Ubuntu) to these three pieces for MySQL? Specifically I’m struggling most with the CA certificate. Not seeing anything analogous in my letsencrypt certs.

Adding user in mysql – Database Administrators Stack Exchange

Why i’m not able to add user with privileges in mysql. i’m running command grant all privileges on *.* to 'username'@localhost identified by 'strong password';. i’m keep getting error ERROR 1064 (42000): 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 'identified by 'strong password'' at line 1. I can add user separately and then assign him the privileges on db but i like to add user and assign privileges in single command. I’m running following mysql version

| version                  | 8.0.23-0ubuntu0.20.04.1       |
| version_comment          | (Ubuntu)                      |
| version_compile_machine  | x86_64                        |
| version_compile_os       | Linux                         |
| version_compile_zlib     | 1.2.11                        |

MySQL Heap Table – Database Administrators Stack Exchange

I have learnt that table can be created with order, like adding primary key. On the other hand, heap table is unordered, as mentioned in this page: https://www.sqlshack.com/clustered-index-vs-heap.

However, seems Heap Table is Mysql only refer to table in memory. Reference: https://www.oreilly.com/library/view/mysql-reference-manual/0596002653/ch07s04.html.

So does MYSQL database have any terminology for unordered table?

Please correct me if I get any wrong concepts. Thanks.