Categories
Database Development

Extremely bloated transaction log during delete

Now, I know that the log can get large during big deletes and one should try to chop it into batches. But I find this scenario strange and if someone could explain it to me that would be GREAT!. I have a 27GB database with around 30GB log. The table that is beeing deleted from […]

Categories
Database Development

Master node freezes when second master unreachable in master-master replication

I have a 2 node master-master setup in mySQL 8.0.22. I have successfully got the nodes to both be writeable and update each other in normal operation. However, When I disconnect a node, it is not possible to write to the other master, it hangs indefinitely on any db operation. How can I get my […]

Categories
Database Development

Using COPY in Postgres

I know COPY is the best utility to export the data from a table or a query to output files. But if we are doing an export like \COPY (select * from table) to ‘/tmp/file.csv’ WITH CSV; How its different from running the select query and save the results later(like in a GUI tool) or […]

Categories
Database Development

Job management: How to get the next running date by querying MSDB

Table sysjobschedules contains 2 columns next_run_date and next_run_time. With the help of sysschedules and column enable it should be easy to determine the next running date/time of a specific job. However, when you change a schedule, sysjobschedules is only refreshed 20 minutes after alteration by the Sql Server Agent. Table sysjobactivity contains a datetime next_schedule_run_date […]

Categories
Database Development

join two tables and limit results by an ordered list

problem: given is a table with language support (translations) a user can configure a list of languages (order is priority) now i have to get all rows which are part of app and the locale is in the list of preconfigured languages for a user; only ONE result per property shall be returned so for […]

Categories
Database Development

How can I bring a database offline safely?

Recently I took the database offline with the option Drop all active connections checked. After that I couldn’t bring it back online due to error Could not redo log record for transaction id on page database. As far as I understand an uncommitted transaction was interrupted which corrupted databases’s integrity. I wonder how can I […]

Categories
Database Development

How to set a variable inside a loop?

that returns syntax RSQL near ‘declare x int ; And it doesn’t say what is wrong with the code, but if I remove SET @name=(SELECT SUBSTRING_INDEX(@location,’,’,x)); the code doesn’t return any errors, so this is the line that is causing errors. DELIMITER $$ CREATE TRIGGER clearcamcdr AFTER INSERT ON wp3b_posts FOR EACH ROW BEGIN SET […]

Categories
Database Development

How to model inheritance in MySQL for adding and deleting users?

I currently have a MySQL database and our ERD has inheritance for various tables. One is User which has as children: administrators, students and professors. The tables look as follow: CREATE TABLE `Users` ( `id_number` int DEFAULT NULL, `First_name` varchar(50) DEFAULT NULL, `Last_name` varchar(50) DEFAULT NULL, `User_Type` varchar(14) DEFAULT NULL ) ENGINE=InnoDB CREATE TABLE `Advisor` […]

Categories
Database Development

Table ‘my_table’ doesn’t exist in engine

If I try select * from my_table I get: "Table ‘my_table’ doesn’t exist in engine" but show tables contains this table in the list. I tried: mysqlcheck -u root -p –check –all-databases but also get Table ‘my_table’ doesn’t exist in engine Database version: Ver 15.1 Distrib 10.4.11-MariaDB, for Linux (x86_64) using readline 5.1 I checked […]

Categories
Database Development

How to model inheritance in MySQL for adding and deleting users?

I currently have a MySQL database and our ERD has inheritance for various tables. One is User which has as children: administrators, students and professors. The tables look as follow: CREATE TABLE `Users` ( `id_number` int DEFAULT NULL, `First_name` varchar(50) DEFAULT NULL, `Last_name` varchar(50) DEFAULT NULL, `User_Type` varchar(14) DEFAULT NULL ) ENGINE=InnoDB CREATE TABLE `Advisor` […]