texts:
I have a situation where I have three tables: sample, sample_name and run (extra columns removed to be only relevant information).

CREATE TABLE `sample` (
  `sample_id` int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sample_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `sample_name` (
  `sample_name_id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `project` varchar(255) NOT NULL,
  `sample_id` int unsigned NOT NULL,
  PRIMARY KEY (`sample_name_id`),
  FOREIGN KEY `fk_sample` (`sample_id`) REFERENCES `sample` (`sample_id`) ON DELETE CASCADE,
  UNIQUE KEY `un_sample_name` (`name`,`project`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `run` (
  `run_id` int unsigned NOT NULL AUTO_INCREMENT,
  `sample_id` int unsigned NOT NULL,
  PRIMARY KEY (`run_id`),
  FOREIGN KEY `fk_run1` (`sample_id`) REFERENCES `sample` (`sample_id`) ON DELETE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now I would like to check if a sample (based on sample_name) exists or if not, then insert both and use the sample_id to insert run entry as well. To do this I planned first to check if the unique name (name + project) exists and if so use it. However if it doesn't, then use trigger to do double insertion (I have a perl script to do this logic; I tried also the commented update version).

delimiter |
CREATE TRIGGER tr_new_sample BEFORE INSERT ON sample_name FOR EACH ROW BEGIN
  IF NEW.sample_id IS NULL THEN
    INSERT INTO sample () VALUES ();
    -- UPDATE sample_name SET NEW.sample_id = LAST_INSERT_ID();
    SET NEW.sample_id := (SELECT LAST_INSERT_ID());
  END IF;
END |
delimiter ;

So when sample name does not exists, I try to insert a record with null sample_id and thus the trigger reacts. However this doesn't work and I get a following error. Can someone suggest on how to fix this or make it work.

The database is actually MariaDB 5.2.12.

Thanks,

mysql> INSERT INTO sample_name (name,project) VALUES ('a','a_project');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`pipeline_runs`.`sample_name`, CONSTRAINT `sample_name_ibfk_1` FOREIGN KEY (`sample_id`) REFERENCES `sample` (`sample_id`) ON DELETE CASCADE)


texts: My MySQL server time is not the same as my Server

texts:
So a month ago I had purchased a server from a hosting company.

The info are;

Linux Centos (latest version).

I installed ZPanel which installs phpMyAdmin, MySQL and all the other good stuff. Anyway, I have managed to change my SERVER time to my local time which is GMT. However when I try to set it on my MySQL server it shows the right time, but when I use the

now()

function in PHP, the time stores +1 hour ahead of my current time, which pretty much messes up my system.

Does anyone know how to fix this? Please It's been a few weeks, yet nothing to fix it :/

Thank you.


texts:
I have a application, which runs for the entire hour, every hour of every day. It basically has 400ish 'projects' to execute within each hour, and loops over the scripts, which execute stored procedures, to complete the task.

Just as an overview, the application is querying the database for data, extracting that data, and writing to a file. The final stage of that is doing at the application layer.

For whatever reason, today, the database has been suffering from constant connection timeout issues. The time in which they happen varies, but it's often 8 minutes in - so after some 'projects' have completed their cycle. It always fails when the application moves on to find the information relevant to the next 'Project' - which is a basic script which says "what haven't I executed yet?".

I've tried extending the connection timeout from 20 seconds to 60 - this hasn't helped.

I have noticed the Virtual Machine is running low on memory, due to the host running out of physical memory. I'm not sure if memory can cause an issue regarding connections - I have given the VM a little more by taking it away from another VM.

This system has been running for months without issue (apart from maybe the odd connection issue every now and then).

Other than the above, is there anything else I should be looking at and/or thinking of?


texts: Why is mysqldump not saving newly added databases?

texts:
I am using the following command to backup all mysql databases in script that is run as cronjob.

mysqldump -u root -p'***' --all-databases > backupdb.sql

I have added new databases recently and I noticed that the new databases are not saved in the backupdb.sql.  What am I missing?


texts: Very high (99%) insertion cost in estimated execution plan

texts: I have four tables with 500,000 to 4,000,000 rows. The script joins them and insert into a temporary table with some where clause. It is taking long. The execution plan shows 99% cost only for the insert whereas there are multiple scans in the plan. Snapshot of the related part of the execution plan is shown in the image. The drive is solid state. How do I interpret the plan. SQL Server execution plan


texts: Why isn't partition-level lock escalation the default?

texts:
In SQL Server, locks are normally escalated from row or page -> table. Starting with SQL Server 2008, a new level of lock escalation was added - partition level.

However, this isn't automatically enabled for partitioned tables - by default, the table is set to skip partition locking and go right from row or page -> table. Why would this be the case? Is there a reason why I wouldn't want to switch all my tables from TABLE to AUTO so that they escalate locks to partition level instead of to table level?

Since the default is still TABLE, I figure there must be something I'm missing about the downside of AUTO.


texts: Update all foreign keys to a different primary key

texts:
In a MySQL database, I have a table full of business information.  Lots of businesses have been inserted twice due to human error, so the same information exists with two different primary keys.  Let's say the same delivery service is represented by primary key 3 and 7.

Businesses:
| id | name           |
| 3  | Planet Express |
| 7  | Planet Express |

Lots of other tables foreign key to this business table. (Employees, etc)

Employees:
| id  | name   | business |
| 999 | Fry    | 3        |
| 666 | Bender | 7        |

Fry and Bender really do work for the same business. I'd like to delete the business row with primary key 7, and tell all the foreign key tables that instead of cascade delete, or cascade null, they should update to point to primary key 3.

Obviously I could do this by hand, but I'd rather make the database do it on my behalf since it has a better memory for this kind of thing.  Is there a way to do this?  Like DELETE FROM business WHERE id = 7 ON CASCADE set 3; or something?


texts:
In my contact table, I'm getting ? symbols in LastName. How can I avoid that?

FirstName   MiddleName     LastName
Bjorn       Erik           N?ss
Gunn                       Gjos?ther
Oystein     Larsen         Indrev?r
Louise      Tulle          Pr?strud


texts:
I have this table with 2 million records
CREATE TABLE `my_table` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ID_USER` int(11) DEFAULT NULL,
  `BODY` mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  `CREATION_DATE` date DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `ID_USER` (`ID_USER`),
  KEY `CREATION_DATE` (`CREATION_DATE`)
) ENGINE=InnoDB AUTO_INCREMENT=2198623 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The first run of this select returns '1131' in 70 seconds
select count(*) from my_table where ID_USER=123 and CREATION_DATE>=2019-07-29

The second run returns '1131' in 60 milliseconds.
What do you recommend to improve performance?
More info:

ID as cardinality 2198623
ID_USER as cardinality 12788
CREATION_DATE as cardinality 11909


texts: Show all variations of 2 characters in postgres

texts:
How would I do this in postgres :

Lets say I have an x and a y and I want to show all possible combinations :

xx
yy
xy
yx

How can I make postgres do this for me ?


texts:
When I connect to my local server, I get this message

A network-related or instance-specific error occurred while establishing a connection to
SQL Server. The server was not found or was not accessible. Verify that the instance name
is correct and that SQL Server is configured to allow remote connections. (provider:
Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft
SQL Server, Error: 2)

This is my login information:

Server type :       Database Engine
Server name :       .
Authentication:     SQL Server Authentication
Login:              sa
Password:           123

How can I fix it?


texts: SQL Server Event Alerts Lack Enough Details

texts:
I setup SQL Server Alerts to send me emails whenever certain events (by severity) occur. Below is an example of en email I received for a "014 - Insufficient Permission" severity event of Type "SQL Server event alert." I looked in the Error Log as well, but no more detailed information can be found.

DATE/TIME:      1/9/2013 3:38:01 PM

DESCRIPTION:    Login failed for user 'DOMAIN\username'. [CLIENT: <local machine>]

COMMENT:        (None)

JOB RUN:        (None)

I want to increase the level of detail provided in these alerts to make it easier to debug and resolve issues. How can I accomplish this?


texts: sp_blitzfirst not showing memory grant columns

texts:
I saw several videos where the know procedure sp_blitzfirst shows the memory grant columns, I even saw it working like that, but on the latest version of the first responder kit, it doesn't show those columns, were those removed?

When I execute it with @expertmode=1 it just show on the messages tab:

Setting up configuration variables
Now starting diagnostic analysis
Capturing first pass of wait stats, perfmon counters, file stats
Beginning investigatory queries
Finished running investigatory queries
Waiting to match @Seconds parameter
Capturing second pass of wait stats, perfmon counters, file stats
Analyzing changes between first and second passes of DMVs
Analysis finished, outputting results


texts:
I have two databases database_a & database_b with the exact same structure, but different email data. How do I update all of database_b's email field with the ones from database_a based on the clients id number?

CREATE `clients` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `firstname` text NOT NULL,
  `lastname` text NOT NULL,
  `companyname` text NOT NULL,
  `email` text NOT NULL,
  `address1` text NOT NULL,


texts: MySQL: update instead of delete if foreign key constraint?

texts:
I have a bit of a strange question. I know about insert on duplicate key update. My question is, is there something similar for deletes that fail because of foreign key constraints?

For example:

delete from table1 where value='something';

But, table2 has a foreign key that depends on the the value I want to delete in table1, so the delete fails. I'd like to do something like this:

delete from table1 where value='something' on foreign key fail update some_other_value='something else';

I know that looks really weird, but I have a good reason for doing it (without getting into details, it has to do with versioning historical data that can't be destroyed in the event that a value is referenced elsewhere.) I can figure out how to do this with more than one query, of course, but I'd like to do it in a single query if I can. I'm pretty sure it's not possible, but I'd like to ask before giving up :)

Thanks!


texts: Import Date in proper format from Excel to SQL Server through OPENROWSET query

texts:
I am trying to import huge data without any data loss from excel sheet to SQL Server. The excel sheet has few columns with mixed data types and few column with dates. I have prepared a sample file as seen in the first image below.

First Try

When I try to import through OPENROWSET query with HDR=YES, data loss occurred under first column. Second column got imported fine. Please refer image below.

Second Try

When I try to import through OPENROWSET with HDR=NO, data imported fine under first column without any data loss, but second column got imported only with time whereas date was not imported. Please refer image below.

Edit 1:Third Try

After extensive internet search, I tried editing registry TypeGuessRows=1048576 according to this microsoft article. But this worked for the smaller data. But issue still persists with huge data. I have shared the data where you can test with it. Please download the excel file to test from this link and if it works please share the solution. In the excel file which I have shared in the link, please note that the row A1000011 has alphabetic chars which is not getting imported to SQL Server with HDR=YES method. If I use HDR=NO, date is not getting imported. Both column data is significant for me.

  I need solution to import all data with any kind of formatting from excel to
  SQL Server without any data loss. Please advise.

Note

I should not open the excel file manually and change formatting.
This step is not allowed.
Not all files are in same structure. I am getting many files with
many columns with different structure. So I cannot create a standard
table with data types fixed.


texts:
Using PostgreSQL 11.

I've tried digging through both Stack Overflow and here and was unable to find an answer on best practices.

I'm working on a database design and have arrived at a schema that uses a generic "join table". This join table contains five columns:

CREATE TABLE many_joins_table (
  id PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  object_id int NOT NULL,
  object_table joins_object_t NOT NULL,
  parent_id int NOT NULL,
  parent_table joins_parent_t NOT NULL);

I've been using this table to represent adjacent and many-to-many relationships between objects in my database. One such example is tags.

CREATE TABLE tag (
  id PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  name text NOT NULL UNIQUE);

CREATE TABLE comment (
  id PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY);

CREATE TYPE joins_object_t AS ENUM ('tag');
CREATE TYPE joins_parent_t AS ENUM ('comment');

When a tag is added to the comment table, I would insert a new row into this join table with the following fields:

INSERT INTO many_joins_table
VALUES(1, 'tag'::joins_object_t, 1, 'comment'::joins_parent_t);

Besides inflexibility of enums, addressed with PostgreSQL 9.1 https://stackoverflow.com/questions/1771543/adding-a-new-value-to-an-existing-enum-type/7834949#7834949.

Are there any significant disadvantages or advantages of such an approach? I'm worried that I've mistakenly implemented an anti-pattern. Are there any best practices I can apply to improve this implementation (indexing or constraints)?

Thanks!

Note: I'm aware there are better ways to implementing tags, namely using intarrays. I'm just using tags as an example since it's easily understood. https://stackoverflow.com/questions/23508551/integer-array-lookup-using-postgres

Edit: Removed the UUIDs since it may be a distraction to the question.


texts:
I'm designing a domotic system database and I'm trying to figure out how to represent two foreign keys.

This is the cardinality I want. Consider 1...N Areas (rooms, halls, kitchens, etc.):

Area 1 - has - N PeripheralUnit (where N ∈ ℕ)
PeripheralUnit 1 - contains - N Device (that can be either sensors or actuators)

My question is if the table Device needs to inherit as foreign key the Area id from PeripheralUnit or maybe from Area, directly.

This is my design:

Maybe I don't need at all to introduce the Area id as a FK in Device, because I can find it in PeripheralUnit.

Need your advice please.


texts: Performing efficient searches over two columns

texts:
Disclaimer: I am extremely inexperienced with databases, so forgive me if this is an ignorant question.

I have a table which contains several columns, but the ones that are important are the columns containing timestamps and MAC addresses. I need to be able to quickly determine if a certain MAC address appears within a certain range of timestamps. For example lets say I need to see if MAC address 01:02:03:04:05:06 appears in any record on last Monday. Can this be done in sublinear time?

My intuition says that we can construct the timestamp range of interest in logarithmic time, but would we then need to linearly search over this subset of the data to determine if the desired MAC address appears, or can we search for the desired MAC address in logarithmic time?

Does this have anything to do with creating indices? Thanks for any help.


texts: Does database require block storage always?

texts: I was reading the difference between block, file and object storage and I read somewhere that database require the underlying storage to be block storage. Is this true? Can I not install a database on a system that has file storage?


texts: Why full index scan over full table scan?

texts:
I have a query that pulls from small tables in MySQL:

SELECT C.cid, C.email, R.flight_number
FROM customer C INNER JOIN reservation R ON C.cid = R.cid
WHERE C.email LIKE '%d.com';

There exists an index in the customer table on the email column.

Based on what I've read on the subject, my thinking is that a full index scan would only slow down the query, and performing a full table scan and ignoring the index would be better than performing a full index scan in this case. However the visual plan shows that the query uses a full index scan over a full table scan. Why?


texts:
I've got postgres 9.1 running on raspbian installed with apt-get.
I've got NTFS usb HDD mounted in /mnt/usb.
What I'd like to achieve is to create a tablespace pointing to /mnt/usb/postgres so I invoke:

CREATE TABLESPACE usbwd LOCATION '/mnt/usb/postgres';

and I get:

  could not set permissions on directory "/mnt/usb/postgres": Operation
  not permitted

which is understandable since the manual states that the target directory has to be empty (it is in my case) and owned by postgres linux user. That's what I have problem with and

sudo chown postgres:postgres /mnt/usb/postgres

changes nothing, meaning the ownership does not change. The /mnt/usb/postgres is owned by pi user, and I need to have it owned by postgres user. The /mnt/usb is also owned by pi user.
What am I missing ?


texts: How to do a kind of selective grouping in a SQL result?

texts:
I have some query as select x1,x2,x3,x4 from foo where bar resulting something like:

CREATE TEMP TABLE foo
AS SELECT * FROM ( VALUES
 ( '00','01','03','AA' ),
 ( '01','03','03','BB' ),
 ( '02','09','05','CC' ),
 ( '03','08','05','DD' ),
 ( '03','08','03','EE' ),
 ( '03','08','03','FF' ),
 ( '03','08','03','FF' ),
 ( '03','08','03','FF' ),
 ( '03','08','09','GG' )
) AS t( x1, x2, x3, x4);

Note the x3 column: we have 03 twice then 05 twice then 03 again then 09. I need to calculate a route using the pgr_ksp function (PostGIS). Imagine the table here is the result and the x3 column is the road names. In this case I start on 03 street, walk on it for 2 segments  and take the 05 street just to take the 03 street again after.

My problem is I'm showing this segments to the user and I don't like to. The user know nothing about segments of roads and just want to see take 03 street, 05 street, 03 again and then 09.

If I just group by, I will lost the take 05 step between the two 03 and I'll see the all long 03 segments connected: take 03 street (all 6 segments together), 05 and then 09 and this is wrong.

My question: how can I group the x3 column as the two first 03, then group the two 05 and then group 03 again after 05 and then group all tuples left using this method.

Not so fast! I must have a sum of the x2 column as I group the x3. This will be the geometry column I must compute - concat all segments geometry to have the complete way representation. This is the result I want:

-----------
| x2 | x3 |
|----|----|
| 04 | 03 |
|----|----|
| 17 | 05 |
|----|----|
| 32 | 03 |
|----|----|
| 08 | 09 |
-----------

x2 is the way geometry and x3 is the name of the street or way. In this case I'll have the complete way segment (sum of small pieces) and the way name.


texts: Individual queries run on 10ms, with UNION ALL they are taking 290ms+ (7.7M records MySQL DB). How to optimise?

texts:
I have a table that stores available appointments for teachers, allowing two kinds of insertions:

Hourly based: with total freedom to add unlimited slots per day per teacher (as long as slots don't overlap): on 15/Apr a teacher may have slots at 10:00, 11:00, 12:00 and 16:00. A person is served after choosing a specific teacher time/slot.
Time period/range: on 15/Apr another teacher may work from 10:00 to 12:00 and then from 14:00 to 18:00. A person is served by order of arrival, so if a teacher works from 10:00 to 12:00, all persons that arrive in this period will be attended by order of arrival (local queue).

Since I have to return all available teachers in a search, I need all slots to be saved in the same table as the order of arrival ranges. This way I can order by date_from ASC, showing the first available slots first on the search results.

Current table structure

CREATE TABLE `teacher_slots` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `teacher_id` mediumint(8) unsigned NOT NULL,
  `city_id` smallint(5) unsigned NOT NULL,
  `subject_id` smallint(5) unsigned NOT NULL,
  `date_from` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_to` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `order_of_arrival` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `by_hour_idx` (`teacher_id`,`order_of_arrival`,`status`,`city_id`,`subject_id`,`date_from`),
  KEY `order_arrival_idx` (`order_of_arrival`,`status`,`city_id`,`subject_id`,`date_from`,`date_to`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Search query

I need to filter by: actual datetime, city_id, subject_id and if a slot is available (status = 0).

For hourly based I have to show all available slots for the first closest available day for every teacher (show all time slots of a given day and can't show more than one day for the same teacher). (I got the query with the help from mattedgod).

For range based (order_of_arrival = 1), I have to show the closest available range, just one time per teacher.

First query runs individually in around 0.10 ms, second query 0.08 ms and the UNION ALL an average of 300ms.

(
    SELECT id, teacher_slots.teacher_id, date_from, date_to, order_of_arrival
    FROM teacher_slots
    JOIN (
        SELECT DATE(MIN(date_from)) as closestDay, teacher_id
        FROM teacher_slots
        WHERE   date_from >= '2014-04-10 08:00:00' AND order_of_arrival = 0
                AND status = 0 AND city_id = 6015 AND subject_id = 1
        GROUP BY teacher_id
    ) a ON a.teacher_id = teacher_slots.teacher_id
    AND DATE(teacher_slots.date_from) = closestDay
    WHERE teacher_slots.date_from >= '2014-04-10 08:00:00'
        AND teacher_slots.order_of_arrival = 0
        AND teacher_slots.status = 0
        AND teacher_slots.city_id = 6015
        AND teacher_slots.subject_id = 1
)

UNION ALL

(
    SELECT id, teacher_id, date_from, date_to, order_of_arrival
    FROM teacher_slots
    WHERE order_of_arrival = 1 AND status = 0 AND city_id = 6015 AND subject_id = 1
        AND (
            (date_from <= '2014-04-10 08:00:00' AND  date_to >= '2014-04-10 08:00:00')
            OR (date_from >= '2014-04-10 08:00:00')
        )
    GROUP BY teacher_id
)

ORDER BY date_from ASC;

Question

Is there a way to optimise the UNION, so I can get a reasonable response of a maximum ~20ms or even return range based + hourly based in just one query (with an IF, etc)?

SQL Fiddle: http://www.sqlfiddle.com/#!2/59420/1/0

EDIT:

I tried some denormalization by creating a field "only_date_from" where I stored only the date, so I could change this...

DATE(MIN(date_from)) as closestDay / DATE(teacher_slots.date_from) = closestDay

... to this

MIN(only_date_from) as closestDay / teacher_slots.only_date_from = closestDay

It already saved me 100ms! Still 200ms on average.


texts: High MySQL "InnoDB Writes Per Second" - Can't see the queries?

texts:
On our live MySQL 5.5 database server, we use MySQL Workbench for running manual queries, checking running queries to see what's going on, etc.

I noticed today that on the "Server Status" tab there's a very high number of "Queries per Second" and "InnoDB Writes per Second", yet on the tab which shows the list of "Client Connections", which shows all running queries, it is barely showing any queries.. perhaps 2 or 3 every few seconds.

What am I misunderstanding here? The two tabs don't add up at all.

EDIT - here's the output for SHOW ENGINE INNODB STATUS;

 --------------
 ROW OPERATIONS
 --------------
 0 queries inside InnoDB, 0 queries in queue
 2 read views open inside InnoDB
 Main thread process no. 24494, id 139631733794560, state: sleeping
 Number of rows inserted 36474186, updated 163927609, deleted 11892445, read      1747097889216
 1.06 inserts/s, 13.91 updates/s, 0.00 deletes/s, 33615.95 reads/s
 ----------------------------
 END OF INNODB MONITOR OUTPUT
 ============================


texts: How to model changing OrderLine rows in fact table

texts:
In our business, we have Orders that are made up of OrderItems. Over time, these Orders can change status (e.g. Received, Challenged, Planned, Completed). During this process, the OrderItems for an Order can be changed, added, or removed.

For example, when an Order is finally fulfilled, the OrderItems might actually be for different Items and different prices to the OrderItems on the original Order.

I'm wondering how to model this. Any suggestions welcome!


texts: Used Memory not freed up after a SQL BULK Insert / BCP Export

texts:
I have created a very basic SQL table as following

CREATE TABLE [dbo].[TickData](
[Date] [varchar](12) NULL,
[Time] [varchar](12) NOT NULL,
[Symbol] [varchar](12) NOT NULL,
[Side] [varchar](2) NOT NULL,
[Depth] [varchar](2) NOT NULL,
[Quote] [varchar](12) NOT NULL,
[Size] [varchar](18) NOT NULL
    ) ON [PRIMARY]

I then performed a 3 Gig Bulk Insert

    BULK
    INSERT TickData
    FROM
    'C:\SUMO.csv'
    GO

Then RAM usage for SQL server went Skyrocking, eating up ~30Go of RAM :

I prefer to think this is an abnormal behavior and that action can be taken to avoid this.

EDIT :
Ok, This seems to be the default behavior. Fair enough.
However, why isn't memory freed up long after the Bulk Insert is finished ?

A couple of extra-considerations :
As of the comments concerning SQL server freeing the memory when it is "told to" by the OS, my hands-on experience on a 24-Core 32 Gb Xeon Server proves this to be inexact : Once a Memory-Voracious BCP extract is over I have a pool of .Net Instances of my data processing application that need to process the extracted data, and they are left choking/fighting to share the remaining memory to try to perform their jobs, which take faaaaar longer that when SQL Server is turned off and memory is available for all applications to share. I have to stop the SQL Server Agent to make everything go smoothly and prevent Apps from crashing for Articiallt caused OutOfMemmroy Exception.
As to artificial Brutal Memory Capping/Limitation, if Free Memory is available, why not use it ? Ideally it would rather be dinamically set to adapt to what is available rather than just being forcibly limited "randomly". But I guess this is by-design, so case closed on this last point.


texts: What is the quickest way to reset a column to a specific value?

texts:
I have 30 million rows inside a sql server database where I've added a simple tagging column. One character allowing null. I did add an index for the column.

I use it to keep track of which rows have been processed for a data extraction. During the initial development I will be restarting the extraction process from scratch (several times) and need to reset the value to '1'.

Being a neophyte database developer (who am I kidding...), my first attempt used a simple update script to reset the value of '2' (the I've processed this row) to a '1'. The first time I ran the script it executed in about 5 minutes. The last time I executed - it took 45 minutes. There has to be a better way.

Is it possible for me to speed this up by completely removing the column and recreating it with a default value of '1'? Will the speed stay consistent?

-- UPDATE ---

The next time I need to do this I'm going to try the following:

ALTER TABLE acmewidgets DROP COLUMN LegacyExtract;
ALTER TABLE acmewidgets
        ADD LegacyExtract CHAR NOT NULL DEFAULT (1);


texts: After a full backup, how does system know which transactions to restore from the transaction log backup?

texts:
Full backup does not truncate the transaction log file.

Assuming the Following Scenario

Full backup at 6AM
TLog backup at 10AM
Full backup at 1PM
TLog backup at 6PM
Crash immediately at the next second (so no tail log backup needed)

Steps to Restore:

Restore full backup from point 3 (contains data as at 1PM)
Then, restore the tlog backup from step 4 (contains log from 10AM to 6PM)

Questions

During recovery, how does the system know that only certain portion of the log file (post 1PM [exclude 10AM to 1PM]) needs to be replayed on the database?
Does it check the timestamps with in the transaction log backup to compare it with the full backup? Or does it check the LSN?

Additional Scenario

Similarly, in another scenario, let's say we do a Full backup at 10AM, then take a transaction log backup at 12PM. The transaction log will have all transactions up-till 12PM (and even prior to 10AM assuming db existed prior to 10AM and there was no previous transaction log backup).

Now, when we restore the full backup and then apply the transaction log backup, how does the system know that only transactions in the log post 10AM need to be replayed? Since all those prior to 10AM will be already there as part of the full backup restore. Is it checked via timestamps or LSN's?


texts: How would I grant select on a table being used in a different database to a user role in SQL?

texts: I have a view that's selecting rows from a table on a different database. When calling this view from my application I get an inner exception thrown saying the application user is not able to access the database being referenced. How do I grant permissions to this user on the separate database? I've tried it the traditional way but of course that user role wasn't found of that db. Should I create a user with the same name on the other db, this doesn't seem like it would work. I haven't been able to find a solution for this specific instance.


texts:
Is there a technical reason when T SQL gives this error?

  Msg 191Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.

My scenario is a function with several nested IFs with ELSEs :

IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE
IF (1=2) BEGIN SET @teste = 1 END ELSE

Changing it to the following statement doesn't raise any error:

IF (1=2) BEGIN SET @teste = 1 END
IF (1=2) BEGIN SET @teste = 1 END
IF (1=2) BEGIN SET @teste = 1 END


texts:
procedure involving dblinks gets invalidated regularly .we need to recompile every time.How to find what is causing the problem

in my case

procedure p1()--resides in db1
is
begin
....
select col1 from table1@db2;

...
end
/

for some reasons the procedure gets invalidated and the dblinks doesnt work and i get the following errors

ORA-04052
ORA-00604
ORA-02019

and i recompile all the invalid objects this seem to work.

i need to figure why the proc in db1 gets invalidated?


texts: Deleting with NOT EXISTS vs NOT IN gives different results

texts:
I'm working with an older schema, and don't understand why my delete operation (to clear out orphan records) doesn't work as expected in some cases.  In others both of the queries return identical results :-/

For example, I have 3 models: house, office, and address.  house and office both have a nullable reference to an address.id, so an address record can belong to either of them but the address itself doesn't indicate which.

-- query 1
DELETE FROM address adr
WHERE
  NOT EXISTS(SELECT * FROM house  H WHERE H.address_id = adr.id) AND
  NOT EXISTS(SELECT * FROM office O WHERE O.address_id = adr.id);

-- query 2
DELETE FROM address adr
WHERE
  NOT adr.id IN (select address_id from house) AND
  NOT adr.id IN (select address_id from office);

query 1: deletes 3000 records (correct)

query 2: deletes 0 records

What is the obvious problem with query #2?  When I check the records deleted by #1 they really do not exist in either house or office.


texts: SQL Server Always On Failover Cluster Design

texts:
I have two databases.  One I'd like to set up in an availability group between two servers (two instances, one on each server).  The database is not huge and I'm not expecting it to grow exponentially.  We'd like to utilize local storage for this database for performance reasons as it is a high usage OLTP database.

The second database would be on additional instances across the same two servers but I need to utilize shared storage because we are expecting this database to grow exponentially.  Our SAN is not designed for high I/Os and this database does not need the I/Os.  I'd like to use SQL Failover Clustering and keep this database on the SAN and take advantage of some High Availability.  Both of these databases need High Availability.

Is it possible to have two SQL instances on the same windows failover cluster with one instance using always on (no SQL failover cluster), and the second instance belonging to a SQL failover cluster?


texts: Connection pooling - NodeJS or Database products?

texts:
Consider the mysql package. The description covers connection pooling thus,

  Rather than creating and managing connections one-by-one, this module
  also provides built-in connection pooling using
  mysql.createPool(config).

Then, there is the connection pool setting in proxysql as described here.

Similarly, in case of PostgreSQL too, there is the pg package supporting connection pooling and pgboucner pooling feature.

How are these approaches (npm package versus product supporting tools) different?
Ideally, I would like to configure connection pooling at the database level so that a developer is free from handling connections. Can this be a preferred approach?


texts:
I have a client with a 2 TB database for a pension company. The database contains a lot of big documents.

They want to archive everything that's not from this year. So far no problem - I'll just do a while loop for batches insert and delete and then backup the log after every batch.

After I perform the archiving can I shrink the database? How long can it take if we're talking about maybe 400GB that was moved?

It sounds like a very heavy process for a server like that. I can do it on the weekend but I'm not sure if it'll stop running in time.


texts: Select one column by two different names in the same query

texts:
I have a table named Main that contains two fields id,name.

I want to select the name column by two different names. For example:

Table Main

id:int, name:varchar(20)

I have inserted the following records:

id  | name
----+-------
  1 | Ahmad
  2 | Sami

I want to select the data like this:

select Main.name as n1,Main.name as n2 from Main
where n1='Ahmad' and n2='Sami'

When I execute this query, it gives the following error:

  ERROR:  column "n1" does not exist
  LINE 1: select Main.name n1,Main.name n2 from Main where n1='Ahmad' ...`

I use PostgreSQL as my DBMS.


texts: How to update the time of each row and check if its updated or not

texts:
Name
Time
Status

T1
9/12/2020 12:12:09
expected

T2
8/06/2020 11:09:12
expected

These are table name and updated time in the DB I want to get the status and time of updation of specific tables .Set the status as "expected" for tables where updation happening daily and "not expected" when when updation miss out daily


texts:
(In case this matters: I'm using SQLite)

My silly little database has a surprising number of tables with identical structure:

CREATE TABLE GTag (
    ID            INTEGER PRIMARY KEY NOT NULL,
    Name          TEXT    UNIQUE      NOT NULL,
    Description   TEXT
);

Back here (Poland) they're known as "dictionary tables" ("tabele słownikowe"); I couldn't find the proper English equivalent. Their role is to provide a limited number of pre-determined choices, usually in drop-down boxes and the like. Storing tags seems like the sort of thing they're designed to handle.

As I'm finalizing the structure of my database, it struck me that all the tags and other tag-like entities (like "GStatus", "SStatus"...) have these exact 3 fields in common, yet are not connected to one another in any way. Should I make them all sub-types of a more general "Tag" table?

I see 3 solutions to my predicament:

Create a super-type with ID, Name, Description. All current tables become EMPTY save for primary ID keys that correspond to Key.IDs (i.e: are foreign keys). If I want to lookup all the GTags, I get all IDs from GTag, and then get data from corresponding Tag entries. The sub-types, however, bring completely nothing new to the picture. They don't have any fields that would set them apart from one another or even the parent type.
Dump everything into one big Tag super-table; add Type field. Since I NEVER want tags from more than on category at the same time, all queries will have to filter via the Type field. Seems clunky like hell, and a huge headache of a rewrite.
Leave things well enough alone. I avoid creating useless entities that have all structure but no meaning in common.

I get the impression that trying to "tidy up" my schema will ultimately result in tangling it up into a confusing mess, but I don't have the experience to say that with conviction. What am I missing here? What possible benefits (and drawbacks) does each solution offer (in terms of performance, neatness, following design principles...), and which should I ultimately implement?


texts:
Is there a method in T-SQL to see if error occurred in Stored Procedure, without the use of @Output or try catch?

We have a lot of legacy code 200+ stored procedures. I have seen @Output method and try catch, however we would have to refactor legacy code, which may break. Just curious, if there is a way to catch error without changing old code.

declare @TestErrorFlag bit -- or can be int or varchar(255)

-- trying to do something similar to this
set @TestErrorFlag  = if error(exec dbo.testprocedure)

Trying to store in some type of indicator variable

@TestErrorFlag = 1 if procedure succeeds,
@TestErrorFlag = 0 if procedure fails


texts: pg_dump - table dependencies when using --table

texts:
The documentation says this when using -t table:

  Note When -t is specified, pg_dump makes no attempt to dump any other
  database objects that the selected table(s) might depend upon.
  Therefore, there is no guarantee that the results of a specific-table
  dump can be successfully restored by themselves into a clean database.

However, when the table has a sequence, and the owned by for the sequence is set to the table, it is included in the dump.

Shouldn't it be excluded according to this part of the documentation? (If so, why are sequences exceptions?)

What kind of database objects are going to be excluded in -t table mode then?


texts: Use NOLOCK hint when calling table valued function

texts:
Suppose the following function:

CREATE FUNCTION [dbo].[ufnTest]()
RETURNS TABLE
AS RETURN SELECT 1 AS Nr

My actual function will select actual data from many different tables. I (mostly) understand the risks of using nolock hints, and decided that in this case I actually want them. So I try to call above function like this:

SELECT * FROM [dbo].[ufnTest]() WITH(NOLOCK)

However, this fails with the following message:

  Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

The tips for fixing this error are not relevant. Perhaps this table hint isn't available for table valued functions then?

There are several alternatives I'd consider. One is to use the hint on all tables in my select query, but I'd prefer not to as (a) I'll have to remember this whenever I change the function around and (b) it's repetitive. Another alternative would be to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, but a downside is that I don't quite know how to set it back to wat it was before (and statements after the select from my TVF should have the original isolation level).

So, I'd prefer to make WITH (NOLOCK) for a select from a table valued function work. Is this possible?


texts: Does SQL Server 2017, including older versions, support 8k disk sector sizes?

texts:
Disk (loosely worded to include not only rotational media but non-rotational media [SSD, NVMe, etc.]) drives are continuing to evolve in their underlying formats and hardware. Part of this was an "enhancement" from 512 byte physical sector sizes to 4k physical sector sizes, which changes the on disk layout (512n, 512e, 4kn).

This next evolution is in using 8k physical sector sizes, which some manufacturers are starting to produce and setup in production. Given this next step, is the 8k sector size disk supported in Windows? Does SQL Server care about sector sizes?


texts: What causes log shipping copy error : "The process cannot access the file because it is being used by another process."?

texts:
We log ship several DBs from primary to secondary server.  Looking at mdb.dbo.log_shipping_monitor_error_detail on secondary server.
Seeing this error pattern over and over many times a day..
Record 1 : Could not copy log backup file '\\SQLSERVER\LogShip\FOO\FOO_20201126153001.trn'. The copy operation will be re-attempted on the next run of the Copy job.

Record 2 : The process cannot access the file '\\SQLServer\LogShip\FOO\FOO_20201126153001.trn' because it is being used by another process.

Is this error simply caused because the tran file is still open at the time of the attempted log copy operation?  Is this expected behavior for log shipping secondary copy operation?


texts: Why can't sa be impersonated in a procedure?

texts:
In order to solve a request regarding temporarily giving elevated privileges to a user I noticed that it was not possible to use EXECUTE AS LOGIN = 'sa'; inside of a procedure, but I can't figure out the reason since the error message does not fit the situation.
Here's how to test it (I run this part as a sysadmin):
--Create the procedure with the impersonation
USE master;
GO

CREATE PROCEDURE impersonate_procedure
WITH EXECUTE AS OWNER
AS
BEGIN

    EXECUTE AS LOGIN = 'sa';
    CREATE LOGIN appname_owner WITH PASSWORD = 'randomlygeneratedpassword';

END;
GO

--The login who should receive the privileges temporarily
CREATE LOGIN ronaldo WITH PASSWORD = N'123StrongP@SSw0rd';
GO

CREATE USER ronaldo FOR LOGIN ronaldo WITH DEFAULT_SCHEMA = dbo;
GO

GRANT EXECUTE ON dbo.impersonate_procedure TO ronaldo;
GO

Now I try to execute the procedure while logged in as ronaldo:
--Fails
EXEC dbo.impersonate_procedure;
GO

I get the following error:

Cannot execute as the server principal because the principal "sa" does
not exist, this type of principal cannot be impersonated, or you do
not have permission.

When I make this (sysadmin again):
GRANT IMPERSONATE ON LOGIN::sa to ronaldo;
GO

The login ronaldo can execute the content of the procedure body successfully:
--Works
EXECUTE AS LOGIN = 'sa';
CREATE LOGIN appname_owner WITH PASSWORD = 'randomlygeneratedpassword';

But if he tries to run the procedure, the error remains and it is bothering me because:

the principal sa exists;
it's a type of principal that can be inpersonated;
the login ronaldo was granted the privilege to impersonate sa (but shouldn't be
needed since the procedure was created by a sysadmin with the clause
WITH EXECUTE AS OWNER.

Why do I get that error?


texts:
I am trying to create a logical replication between 2 postgres servers.
But when I try to create a subscription from the slave to the master I get the error

ERROR:  could not connect to the publisher: FATAL:  no pg_hba.conf entry for replication connection from host

Here are my configs.

Master server ip 192.168.100.21;
Slave server ip 192.168.100.22

Master server pg_hba.conf :

host  replication  replicate  192.168.100.22/32   md5

Slave server pg_hba.conf :

host  all   postgres   192.168.100.21/32  md5

User for replication is 'replicate'. On the master, I have granted database access to 'replicate'user and created the publication.

I have also done a schema dump from Master to Slave using pg_dump.

Have I missed anything here?


texts:
I am new to Always On Availability Groups and I am little bit confused with creation of logins.

Let's assume an environment NODE1(Primary) & NODE2(secondary) & NODE3(Stand by) & VAEWI(Listener).

I need to create a SQL authentication login.

For this, I assume I need to create it by connecting to listener that which will sync in all of the nodes.
I need to create a Windows authentication.

For this I think, as all of the nodes are window-clustered before creating the availability group. So, we need to create a Windows user 1st in primary node and then create a Windows authentication by connecting to the listener.

Can anyone let me know if my assumptions are true?


texts: Issues with TempDB mdf file ever increasing

texts:
I have a tempdb growth issue. Let me preface everything by giving my tempdb settings.

Even with no queries running on the database/server tempdb keeps on increasing in size, at first rapidly and then slowly without stopping. I've run many queries to figure out what is running, below is the result of the query below which actually gave me the results I could use.

As can be seen they are all internal spid's is there any way to find out why tempdb continues to grow out of control and how to mitigate it? Any help on this problem would be greatly appreciated.

--Query that returned the result set
SELECT session_id,
       SUM(internal_objects_alloc_page_count)   AS task_internal_objects_alloc_page_count,
       SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM   sys.dm_db_task_space_usage
GROUP  BY session_id
HAVING SUM(internal_objects_alloc_page_count) > 0


texts: SQL Server Cluster Install on Mount Point

texts:
I am trying to install a Failover cluster instance and I ran into the following issue:

  Updating permission setting for folder 'C:\ClusterStorage\PRODSQL\SQLData\MSSQL14.PRODSQL\MSSQL\DATA' failed. The folder permission setting were supposed to be set to 'D:P(A;OICI;FA;;;BA)(A;OICI;FA;;;SY)(A;OICI;FA;;;CO)(A;OICI;FA;;;S-1-5-80-3017142923-4147168812-3961353769-1417247779-429559477)'.

PRODSQL is clustered shared storage (CSV) and it is mounted to C:\ClusterStorage.

The service account is admin account.

Created sub folder - SQLData on the mount point as per Microsoft recommendation.

Please advice if anyone has seen this issue.


texts: Why SQL Server port number is not default 1433?

texts:
Installed latest SQL Server. It is listening on port number 62628 according to server log. Why is the port number is not the default of 1433?

How to change the port number back to the default 1433?

It was installed on Windows 10. The server name is MachineName\SQLEXPRESS. I did not give a name.


texts: Check only prerequisites when running runInstaller in silent mode

texts:
I want to check just prerequisites and generate a log without installing Oracle database.

When I run the following command:

./runInstaller -silentvalidate -responseFile /0/grid/response/crs_install_mk.rsp

It shows me the following error:

The command line arguments '-silentvalidate' are not valid options. Type 'oui -help' at the command line for instructions on appropriate command line usage.
Usage: runInstaller  [-options] [(<CommandLineVariable=Value>)*]

runInstaller has also an option -executePrereqs but it tries to launch installer in GUI mode.

So I want to check only prerequisites and generate log without launching a GUI.

Oracle Version: 11g R2


texts:
I need to know the location of a DTSX package which is stored in "file system",

It is visible under Stored pacakges-->filesystem-->Package1 in Integration services .

I tried to do a windows search using "Package1.dtsx" string, But the results lists 5 packages with the same name, How do i know which is the package listed in integration services?. Is there any table i can query to get this info?


texts: Avoiding null comparisons in where clause

texts:
It's my impression that comparing to null limits the use of an index. We had a DBA come in and that statement was part of his notes, however no better example was given.

If that statement is true, what is a better way to write the following?

SELECT * FROM dbo.DpdRoute
    WHERE DestinationCountry =@COUNTRY
    AND (  (BeginPostCode IS NULL      AND ENDPOSTCODE IS NULL)
        OR (BeginPostCode  = @POSTCODE AND ENDPOSTCODE IS NULL)
        OR (BeginPostCode IS NULL      AND ENDPOSTCODE  = @POSTCODE)
        OR (BeginPostCode <= @POSTCODE AND ENDPOSTCODE >= @POSTCODE))

I'm looking for a list of routes, for a given country where a postcode is either within a range of postcodes, equal to the min or max post code, or the min or max post codes are both null.

How does one "not compare to null" when the comparison is specifically about "nulls"?


texts: format() is a nondeterministic built-in string function... right?

texts:
Before I post a connect item regarding the lack of documentation about this, will someone confirm that I am not simply missing something here?

On the docs page where format is listed as a string function:

  "All built-in string functions are deterministic." - String Functions (Transact-SQL)

There is also no mention of format being nondeterministic on related pages:

Deterministic and Nondeterministic Functions
FORMAT (Transact-SQL)

However, when attempting to create a persisted computed column:

create table t (date_col date);
insert into t values (getdate());
alter table t add date_formatted_01 as format(date_col,'YYYY') persisted;

Returns the following error:

  Computed column 'date_formatted_01' in table 't' cannot be persisted because the column is non-deterministic.

The documentation states that

  If the culture argument is not provided, the language of the current
  session is used.

but adding a culture argument doesn't change things

This also fails

alter table t add date_formatted_02 as format(date_col, 'd', 'en-US' ) persisted

rextester demo: http://rextester.com/ZMS22966

dbfiddle.uk demo: http://dbfiddle.uk/?rdbms=sqlserver_next&fiddle=7fc57d1916e901cb561b551af144aed6


texts: How to “select * from table from database” with -e?

texts:
I want to select and show 1 row form a table with the mysql -e.

I was trying out the following but no luck,

mysql -u root -e “select * from table1 from db01 limit 1”

Actually I am trying to dump a single line from hundreads of tables to take a sample from them and redirect to a file. So I want to use shell and don’t want to be in MySQL console.

I have used show the above way but select is not working.

Everyone is suggesting to do select * from table1 but didn’t mentioned how to mention database.
So how can I achieve this?


texts: Setup to allow creating new tables in PostgreSQL replica

texts:
My goal is to create a replica of an existing database that would allow me to create new tables and populate them. I don't care about writes to replicated tables - those can and probably should throw errors.

Ideally I would like to accomplish this with standard postgresql streaming replication, but since that's not possible (or is there a way to do it?) I can go with a battle-tested and maintained extension. The least desirable solution would be a custom ETL process.

The replicated database will be used for analytics not real-time operations so synchronization delay is acceptable.

This seems like a fairly obvious use-case but I've been having trouble with finding any solution to this problem.


texts: Alternative Database Tools for Web Admin

texts:
I currently use phpmyadmin for most of my database administration.  It works well enough, but I'm interested in other tools that might work more quickly/efficiently/etc...

What are the best and/or alternative admin tools for MySQL on a *nix box?


texts: Why does MariaDB still use the "mysql" name everywhere within the file system?

texts: I understand that MariaDB forked a version of MySQl but is is very confusing to still deal my.cnf, /usr/share/mysql , /usr/lib64/mysql and so on? Are there any reasons, why they did not change the naming on most of those folders? This had been very confusing to me and that is the reason why i have not migrated to it yet. It's either you are MariaDB or MySQL but don't try to be both.


texts: How to mark a table as pre-populated with postgresql logical replication

texts:
I accidentally loaded data into a table before setting up a logical replication subscription for it and now I'm getting errors when I try to sync it (duplicate key already exists).

I can't delete the rows because "cannot truncate a table referenced in a foreign key constraint".

Is there a way to tell the subscription that the table was pre-synced or pre-populated?

We're getting lots of logs lines like:

ERROR: duplicate key value violates unique constraint

Thanks.


texts: select column values as fields from multiple tables in MySQL with customized conditions

texts:
I am not more familiar with MySQL.i want to select some column fields as values from another table by applying some customized conditions I tried out with some queries but not getting the proper result since it has some inner options.
please advice me on this

Thanks in advance

please refer to the fiddle.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e7c0ff159f300ed1dc220ca12b10dc2d


texts: How do you normalize a table that is indirectly dependent on a has-many relationship?

texts:
I currently have the following database design:

Product: contains various description attributes

Belongs to many ProductSets

ProductSet:

Has many Products
Belongs to many Stores

Store:

Has one ProductSet
Must, through some set of relationships, specify the price for each product in its product set

I'm specifically struggling with the best way to incorporate the final bullet in a normalized way. So far, the only solution I've come up with is to have a join table between Store and Product, but this doesn't guarantee that the store has prices for all of (and only) the products in its ProductSet. What (if any) is the best way to accomplish this?


texts: Oracle after run procedure returned ORA-02046

texts:
I made small changes in a procedure to calculate value for statistic monitoring.

In procedure calculate_1 on remote DB, I added check null or no null - PARAMETER_ID

    OPEN CALCULATE_PARAMETER;
    LOOP
      FETCH CALCULATE_PARAMETER INTO CALCULATE_PARAMETER_ARRAY;
      EXIT WHEN CALCULATE_PARAMETER%NOTFOUND;
      if (CALCULATE_PARAMETER_ARRAY.PARAMETER_VALUE is null) then CALCULATE_PARAMETER_ARRAY.PARAMETER_VALUE:=0; end if;
      IF (CALCULATE_PARAMETER_ARRAY.PARAMETER_VALUE is not null)
        THEN
    /* ADDED from this */
        PARAMETER_ID:=null;
        BEGIN
     EXECUTE_TEXT:='select t.PARAMETER_ID from SERVERS_PARAMETERS_DIR@BMCDB t '||
    'where '||
    't.server_id IN (select server_id from servers_dir@BMCDB a where upper(a.location) like upper(''%Moskow%'')) '||
    'and '||
    't.parameter_name=:PARAMETER_NAME';
       EXECUTE IMMEDIATE EXECUTE_TEXT INTO PARAMETER_ID USING CALCULATE_PARAMETER_ARRAY.PARAMETER_NAME;
          EXCEPTION
          when OTHERS then begin /*dbms_output.put_line(PARAMETER_NAME);*/ goto PARAMETER_ID_SELECT; end;
          end;
          <<PARAMETER_ID_SELECT>>

        if (PARAMETER_ID is not null)
        THEN
/* to this */
        insert into SERVERS_PARAMETERS@BMCDB
        (PARAMETER_VALUE_ID, PARAMETER_ID, PARAMETER_VALUE, DATE_CURRENT_CHECK)
        values
         (SERVERS_PARAMETERS_ID.nextval@BMCDB,

    (select PARAMETER_ID from SERVERS_PARAMETERS_DIR@BMCDB t
    where
    t.server_id IN (select server_id from servers_dir@BMCDB a where upper(a.location) like upper('%Moskow%'))
    and
    t.parameter_name=CALCULATE_PARAMETER_ARRAY.PARAMETER_NAME),
         CALCULATE_PARAMETER_ARRAY.PARAMETER_VALUE,
         sysdate);
        commit;
        END IF;
        END IF;
    END LOOP;

try run this code

begin
calculate_1@db_link1;
calculate_2@db_link1;
calculate_3@db_link1;
end;

Execution finished with the following error

  ORA-02046: Distributed transaction already begun

Why Oracle returned this error and how resolve this problem?

p.s.

Oracle version - Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
select * from dba_2pc_pending; returned no rows
procedures on remote DB,
calculate_1, calculate_2and calculate_3 are valid.


texts: What is default MySQL binlog retention in AWS RDS?

texts:
I tried to  retrieve my MySQL RDS binlogs in order to run manual "rollback" for some SQLs.
Unfortunately, I noticed that only 2 binlogs for the last 30 minutes are available.

"call mysql.rds_show_configuration" provides "binlog retention hours | NULL".

I guess (because RDS provides ability to point-in-time restore between daily backups) that AWS RDS keeps its binlogs for 24 hours. But it seems that it's unavailable for the end user...

TIA,
Vitay


texts: Why is there an option to rebuild/reorganize an index when SQL Server does this automatically?

texts:
SQL Server automatically handles index fragmentation issue when a certain number of rows are inserted/updated.
Then why is there an option to rebuild/reorganize an index?


texts: Setting sql_slave_skip_counter is not working on Percona 5.6.24

texts:
I am currently running a master - slave replication where on my slave there is an sql error due to which my replication was stopped.

when I tried to run the following good old solution

SHOW SLAVE STATUS \G;
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;
SHOW SLAVE STATUS \G;

it didn't work. The Skip_Counter shows a value of 1 but when I again ran SHOW SLAVE STATUS the value was decreased to 0 but the error was still there. Along with that, Exec_Master_Log_Pos doesn't change.

is there any thing wrong with my expectation ?

EDIT

Error is as follows:

Error 'String 'abcde_abcdefgh_abcd' is too long for user name (should be no longer than 16)' on query. Default database: ''. Query: 'GRANT SELECT ON mydb.* TO 'abcde_abcdefgh_abcd'@'192.168.1.4' IDENTIFIED WITH 'mysql_native_password' AS 'PASSWORD_HASH'

My Master version is 5.7 and that on slave is 5.6. Upgrading the slave did solve the problem as the query could be executed on slave as well but my question is still there. Why couldn't skip counter solve the replication blockage ?


texts: InnoDB: Could not find a valid tablespace file for 'database/table'. Mariadb 10.1.40

texts:
Generally server works fine without any issues. But recently I enabled error logs to test few things and I noticed an error
InnoDB: Could not find a valid tablespace file for 'database/table'.

This has a reference to a very old database that was in the server and which has been manually deleted for some unknown reason. Few have asked to delete the aria_log_ files but it didn't make any difference. With the error it gives a link to mariadb site but it is not related to this exact issue.
I just want to clean this stale data somewhere recorded in mariadb so the error will go away. What needs to be done to clean this mess?


texts: Storing a very large matrix on Postgresql

texts:
I have a list of 19100+ postal codes(with their associated centroid lat/long). Now I am planning to calculate the driving distances between each of them and store it in a database as a ready reckoner for use in another application. I am facing two problems:

Is storing this as a 19100 columns X 19100 rows table feasible (seems wrong in my gut) a viable option?
Since A->B is the same as B->A I can store maybe reduce it to a 9550 X 9550 elements table, which reduces the problem but still seems like optimisation of an incorrect approach.

[ 1 & 2 seem to be non starters as I am told max columns is 1300 in a table]

3.Approach three is storing the distance as key/value pairs. For example to store distance between 400703 and 400705, we store it as

{"400703400705":15"}

Is this the only option?

What's the best way to store this? It will be a write once operation, all future uses will be read only, if that helps in any way. Is there a better approach? (I cannot use Haversine distance formula because it is driving distance I need, not geographical distance)


texts: computed column making the select query to run slow

texts:
We are creating indexes, as per the requirements, on one server and copying all the indexes to the other servers. All servers have similar objects & schema but different resource configuration values.

We have an issue where a query that joins 4 views is completing on one server but the same query is taking 4 minutes on the other server. If I take out the computed column from the select list it is completing in 4 seconds.

The two execution plans are quite different.

1)  Is this a good plan to copy the indexes blindly where SQL is choosing different execution plans?

2) I have also tried to create the index on the computed column on the base table, still no luck, any further suggestions? ( We can’t create an index on views for some reason.)


texts:
I did my 5 minute research and have not come up with a concise list, or even a list really except for Trace Flags (2012) which is a bit lacking.

Looking at this query which uses 8691, I was wondering if such a list exists.

with cte(guid,other) as (
select newid(),1 union all
select newid(),2 union all
select newid(),3)
select a.guid, a.other, b.guid guidb, b.other otherb
from cte a
cross join cte b
order by a.other, b.other
OPTION (QUERYTRACEON 8691);


texts: What should be the structure for a person-to-person store database

texts:
I am going to create a person-to-person store with Laravel, a website whose users can share and sell their product or their mobile, TV, Computer, Shoes, Shirts, Books ,Cars, Bags, Animal etc.

I am going to create a table named Products. Other tables about product are, for example, Bugs, Animals, Cars, Shirts, Shoes or Computers.

products table:

product_id   |   cat_id     |   title    |   cost    |    timestamp

cars table:

      id     |  product_fk  |     brand   |   model   |    type    |  Fuel  | ...

Computers table:

     id     |  product_fk  |     brand   |   type     |    CPU     |  GPU    |  hard_disk  |  ...

I will create other, similar, tables.

I want to know if there is another way to do this which is better and easier?

What do you think about this structure?


texts:
I'm working on a database that stores address information. (Related to this question I posted)

I have 2 tables:

CREATE TABLE [dbo].[Country] (
[Code]          CHAR (2)     NOT NULL,
[Code3]         CHAR (3)     NOT NULL,
[CodeNumeric]   CHAR (3)     NOT NULL,
[Name]          VARCHAR (50) NOT NULL,
[ContinentCode] CHAR (2)     NOT NULL,
[CurrencyCode]  CHAR (3)     NOT NULL,
PRIMARY KEY CLUSTERED ([Code] ASC),
CONSTRAINT [CountryContinentFK] FOREIGN KEY ([ContinentCode]) REFERENCES [dbo].[Continent] ([Code]),
CONSTRAINT [CountryCurrencyFK] FOREIGN KEY ([CurrencyCode]) REFERENCES [dbo].[Currency] ([Code])
);

and

CREATE TABLE [dbo].[City] (
[Id]          INT           IDENTITY (1, 1) NOT NULL,
[Code]        VARCHAR (10)  NOT NULL,
[Name]        NVARCHAR (50) NOT NULL,
[CountryCode] CHAR (2)      NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [CityCountryFK] FOREIGN KEY ([CountryCode]) REFERENCES [dbo].[Country] ([Code])
);

Now I want to add a Capital to my Country table using a FK from the City table. I know that this is impossible because of the constraints (there has to be a City with the Capital's Id, but there also has to be a Country with the Country's Code for the City...if this makes any sense).

Approach 1:
I'm using this database together with C# and was thinking to write method that would first add a Country with a Capital that is nullable, then add the City to its table and update the Countries Capital with the City that was added.

Approach 2:
Another approach I consider is not adding Capital to the Country table but instead creating a new table CountryCapital and link both the Country and the City (Capital) together.

Which one of these 2 is the better approach? What I think is that approach 1 is a good solution if the database is maintained through C# but would be hard to keep up with if data is added manually. Approach 2 on the other hand looks easier to maintain if manual edits have to be made (add a City, add a Country and add a record in the CountryCapital table with the City's Id and the Country's Code).


texts: Select all rows where a column value occurs more than once

texts:
I have a following table structure in Postgres:

 id | account_id | plan_id | active
----+------------+---------+--------
  1 |    0cYd7Ak |       1 |      f
  2 |    Uk02q1d |       1 |      t
  3 |    eRlk810 |       2 |      f
  4 |    Uk02q1d |       2 |      t
  5 |    0cYd7Ak |       1 |      t
  6 |    yT3nv3p |       3 |      t

How I can select all row where account_id occurs more than once? For the example above it will return rows 1, 2, 4, 5

I tried this one query, but it does not return what I expect:

select * from table_name t1
where (select count(*) from table_name t2
  where t1.account_id = t2.account_id) > 1
order by t1.account_id;


texts:
We have a busy SSRS Reporting server (SQL Server 2008 R2) on which we see frequent blocking between the below processes:

[dbo].[CheckSessionLock]
[dbo].[WriteLockSession]

We are aware that some reports are running unacceptably slow. What I need to know is whether the blocking in the ReportServer database is symptomatic of an overloaded server or whether the blocking is causing the reports to run slow.

I am inclined to believe it is the first case but haven't been able to gather evidence prove it definitively as such.

I have read the thread at https://connect.microsoft.com/SQLServer/feedback/details/698388/blocking-in-ssrs-reportserver-database in which the issue is loosely acknowledged but I cant get to the link cited for addressing this issue.

Has some out there got to the bottom of same problem with blocking in the ReportServer database?


texts:
I'm trying to setup a replication, but the snapshot agent will not start.

It tells me

Error messages:
    Message:  Data source name not found and no default driver specified
    Stack:    at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowConnectionFailureException(CConnection* pNativeConnectionWrapper)
       at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.InitializeNativeBcpOutProvider(LogMessageCallback logMessageCallback, String strName, String strApplication, String strHost, String strServer, String strNetwork, Int32 encryptionLevel, String strDatabase, SecurityMode securityMode, String strLogin, SecureString strPassword, Int32 bcpBatchSize, Int32 loginTimeout, Int32 queryTimeout, Int32 packetSize, DeadlockPriority deadlockPriority, String strRowDelimiter, String strFieldDelimiter, Byte syncMethod, Boolean forceOdbcBcp, Boolean forceOleDbBcp, Boolean enableMultipleActiveResultSets, Boolean useReadPastHint, Boolean usePageLockHint, Boolean securePassword)
       at Microsoft.SqlServer.Replication.Snapshot.TransSnapshotProvider.InstantiateBcpOutProviderWorker(String strBcpConnectionName)
       at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.InstantiateBcpOutProvider(String strBcpConnectionName)
       at Microsoft.SqlServer.Replication.Snapshot.SqlServer.BcpOutThreadProvider.Initialize()
       at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc()
       at Microsoft.SqlServer.Replication.MainWorkerThread.AgentThreadProc()
       at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() (Source: MSSQLServer, Error number: 0)
    Get help: http://help/0
    Message:  Data source name not found and no default driver specified
    Stack:    at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException(CConnection* pNativeConnectionWrapper)
       at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowConnectionFailureException(CConnection* pNativeConnectionWrapper) (Source: MSSQLServer, Error number: 0)
    Get help: http://help/0

Any Suggestsions what I can do?


texts:
I have following requirement: main_set table has all the ids, user_input table has all user entered ids.

I want result of ids that exist in user_input table. If nothing in user_input table then I want all the ids from main_input table.

Here's what I have so far:

create table main_set as
select 1 id from dual
union all
select 2 from dual
union all
select 3 from dual

create table user_input as
select 1 id from dual
union all
select 2 from dual

select *
  from main_set ms
  where exists (select null
                        from user_input ui
                       where ui.id = ms.id)


texts: mysql data node not connecting to node manager on AWS EC2

texts:
I am trying to setup mySql manager + data node + sql server (Server A) and another data node cluster on (Server B). Both running on Ubuntu 16.04 amd_64

Server A

/etc/hosts also on Server B

Both private ip
172.xx.xx.xx ip-172-xx-xx-xx-eu-west-1.compute.internal
172.xx.xx.xx ip-172-xx-xx-xx.eu-east-1.compute.internal

/config.ini

[ndbd default]
 NoOfReplicas=2  # Number of replicas

[ndb_mgmd]
hostname=ip-172-xx-xx-xx.eu-west-1.compute.internal <- Server A
datadir=/var/lib/mysql-cluster
NodeId=1

[ndbd]
hostname=ip-172-xx-xx-xx.eu-west-1.compute.internal <- Server A
NodeId=3
datadir=/usr/local/mysql/data

[ndbd]
hostname=ip-172-xx-xx-xx.eu-east-1.compute.internal <- Server B
NodeId=2
datadir=/usr/local/mysql/data

[mysqld]
hostname=ip-172-xx-xx-xx.eu-west-1.compute.internal <- Server A
/etc/my.cnf

[mysqld]
ndbcluster

[mysq_cluster]
ndb-connectstring = ip-172-xx-xx-xx.eu-west-1.compute.internal <- Server A

Server B

/etc/my.cnf

[mysqld]
ndbcluster

[mysql_cluster]
ndb-connectstring=172.xx.xx.xx <- Server A private ip

Server A error

running ndbd gives me this error

[ndbd] INFO     -- Angel connected to '172.xx.xx.xx:1186'
[ndbd] ERROR    -- Failed to allocate nodeid, error: 'Error: Could not alloc node id at 172.xx.xx.xx port 1186: Connection done from wrong host ip 172.xx.xx.xx.'

Server B error

running ndbd gives me this error

Unable to connect with connect string: nodeid=0,172.xx.xx.xx:1186
Retrying every 5 seconds. Attempts left: 12

ndb_mgm

-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: 172.xx.xx.xx:1186 <- A
Cluster Configuration
---------------------
[ndbd(NDB)] 1 node(s)
id=2 (not connected, accepting connect from 172.xx.xx.xx) <- B

[ndb_mgmd(MGM)] 1 node(s)
id=1    @172.x.x.x  (mysql-5.7.22 ndb-7.6.6)

[mysqld(API)]   1 node(s)
id=3 (not connected, accepting connect from 172.xx.xx.xx) <- A

Not sure how would i connect to mysqld(API) that should work fine after i installed the server/client cluster.

Also i got inbound on both Instances A & B to accept all traffic between all port ranges 1000-60000 just in case i am missing something, and outbound is open to everything.

I wonder what is it i am missing, i got so tired today trying to find out what is going on with the setup.

Any help will be appreciated !


texts:
I used SSMS to put several databases in Single User Mode.  I started doing a full backup on each database and it was working sucessfully until I got to the 3rd database and I'm receiving an error telling me the database is already open.  I stopped all Agent Jobs before I started and the DB is off the network.  I tried putting the database back in Multi-User mode and I'm being told it's already in use.

How do I disconnect whatever is connected to it?  I restarted the SQL Server instance with no luck.


texts: Creating a flexible table containing prices and periodes

texts:
I'm creating a system of availability for rooms and with a calendar according to the days, with every day have it's own price.

Room Type |  01  |  02  |  03  |  04  |  05  | ... |  31  |
-----------------------------------------------------------
Single    | $100 | $100 | $200 | $200 | $200 | ... | $300 |
-----------------------------------------------------------
Double    | $150 | $150 | $200 | $250 | $350 | ... | $320 |

The best thing that I come accross is :

RoomsAvailable
--------------
 # ID
 room_type
 from_date
 to_date
 price

But, despite this table is flexible with the periodes, it is not with every single day's price.

Thanks in advance.


texts: Oracle Standby Database - Archivelog GAP

texts:
Good Afternoon!

I've done some research around fixing archivelog gap between Primary and Standby databases, and a few links provide tips on the querying V$archived_log, v$archive_gap and v$log_history. They are really great source of information, however, I have a scenario in which both servers need to have archivelog retention due storage limits, so standby database let's say, has archivelog sequences from D to P, and from P to R, being R today's.

What would be the implications, apart from having missing data, in a case of need to put standby temporarily as production db in case of failure? Would it work, or the best practice is to recreate stdb database and apply only continuous archives from now on?

Thanks.


texts:
I an unable to fix this as I don't know what cause this error.

Replication was stoped for maintenance activity and after completing the maintenance we started it and  then after some point the replication ended up with the below error:

  Last_SQL_Error: Could not execute Delete_rows_v1 event on table rnrinfotech.schedulers_times; Can't find record in 'schedulers_times', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000093, end_log_pos 896

Both on Centos 6.7:

Master Version MySQL-server-5.5.41-1.el6.x86_64
Slave1 Version mysql-community-server-5.6.28-2.el6.x86_64
Slave2 Version mysql-community-server-5.6.28-2.el6.x86_64

Slave2 is Working fine without any error. I am thinking that can I run multipal slave on my current config.

every time I create secondary slave replication after sync the very next day it get failed with the above reason.


texts: Bulk database update/insert from CSV file

texts:
I am implementing application specific data import feature from one database to another.

I have a CSV file containing say 10000 rows. These rows need to be inserted/updated into database.

There might be the case, where couple of rows may present in database that means those need to be updated. If not present in database, those need to be inserted.

One possible solution is that, I can read one by one line, check the entry in database and build insert/update queries accordingly. But this process may take much time to create update/insert queries and execute them in database. Some times my CSV file may have millions of records.

Is there any other faster way to achieve this feature?


texts: Finding contiguous ranges in grouped data

texts:
I have a table with the following structure:

CREATE TABLE `Rings` (
    ID_RingType CHAR(2),
    Number MEDIUMINT UNSIGNED,
    ID_User INT(11)
);

and with data:

INSERT INTO `Rings` VALUES
  ('AA',1,1),
  ('AA',2,1),
  ('AA',3,1),
  ('AA',11,1),
  ('AA',12,1),
  ('AA',13,1),
  ('AA',14,1),
  ('AA',15,1),
  ('AB',16,1),
  ('AB',17,1),
  ('AB',18,1),
  ('AB',19,1),
  ('AB',20,2),
  ('AB',21,2),
  ('AB',22,2);

I wish to group the data based on ID_User and ID_RingType and for each contiguous range of numbers list the MIN and MAX.

The results should look like:

ID_User | ID_RingType | MIN  | MAX
1       | 'AA'        | 1    | 3
1       | 'AA'        | 11   | 15
1       | 'AB'        | 16   | 19
2       | 'AB'        | 20   | 22

I went through several posts on this topic but was not able to tweak them to fit my data.

Any help would be appreciated.


texts: Very simple read-only SELECT not run in parallel in postrgresql

texts:
I am trying to do a simple SELECT on a large table, but it is not being run in parallel:

my_db=> explain (analyze, buffers) select value from my_table;
Seq Scan on my_table (cost=0.00..94433357.34 rows=5441810434 width=4) (actual time=0.029..1519364.787 rows=4897721407 loops=1)
   Buffers: shared hit=1126394 read=38888859 dirtied=1926396 written=284
 Planning time: 2.277 ms
 Execution time: 2468388.489 ms

If I do a max() operation it runs in parallel:

 my_db=> explain (analyze, buffers) select max(value) from my_table;
 Finalize Aggregate  (cost=48552456.41..48552456.42 rows=1 width=4) (actual time=1347796.993..1347796.993 rows=1 loops=1)
   Buffers: shared hit=1173785 read=38870105 dirtied=30813
   ->  Gather  (cost=48552455.58..48552456.39 rows=8 width=4) (actual time=1347796.953..1347796.969 rows=9 loops=1)
         Workers Planned: 8
         Workers Launched: 8
         Buffers: shared hit=1173785 read=38870105 dirtied=30813
         ->  Partial Aggregate  (cost=48551455.58..48551455.59 rows=1 width=4) (actual time=1347790.862..1347790.862 rows=1 loops=9)
               Buffers: shared hit=1172674 read=38870104 dirtied=30813
               ->  Parallel Seq Scan on my_table (cost=0.00..46849720.06 rows=680694206 width=4) (actual time=0.213..1048428.195 rows=544206420 loops=9)
                     Buffers: shared hit=1172674 read=38870104 dirtied=30813
 Planning time: 0.761 ms
 Execution time: 1347985.127 ms

Why is the SELECT not done in parallel?

The table has several billion rows. The DBA has done some indexing - I don't know what that really means, but I have read that index scans are not parallel in PostgreSQL 9.6.8, but it is doing a sequential scan anyway.

I am using PostgreSQL 9.6.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit

The table has structure:

my_db=> \d my_table
 id       | bigint                         | not null default nextval('my_table_id_seq'::regclass)
 datetime | timestamp(0) without time zone | not null
 value    | real                           | not null
 my_id    | smallint                       | not null
 my_flag  | boolean                        | not null

my_db=> \d my_table_id_seq
 sequence_name | name    | my_table_id_seq
 last_value    | bigint  | 4971842011
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 22
 is_cycled     | boolean | f
 is_called     | boolean | t

The parallel SELECT MAX(value) still takes quite a while:

my_db=> \timing on
Timing is on.
my_db=> select max(value) from my_table;
 9333

Time: 1342377.208 ms


texts: What is the best approach to write a lot of many to many relationships?

texts:
I have a lot of models that creates many to many relationship i.e. Business, Agency, Space, Report, Campaign.

1. Campaign hasMany Agencies and Agency hasMany Campaigns
2. A Business hasMany campaigns but a Campaign belongToOne Agency
3. Agency hasMany Spaces
4. A Report has many spaces, hasMany Campaigns, hasMany Agencies and One Business

Now there are multiple options to design the schema. I am thinking the below generic polymorphic relationship but do not know the costs:

attachable_id, attachable_type attached_with_id, attached_with_type

The other option is to make tables like:

space_id, spacable_id, spacable_type  (polymorphic relationship with space and space attachables)

I want to know the different kind of costs in terms of DB Design..


texts: Is there a Maintenance Plan that checks my database for corruption?

texts:
Is there any way to make a maintenance plan check all my databases on a single instance?
Is the maintenance plan Check Database Integrity Task enough ?


texts: Postgresql database disappeared after changing 'localhost' to local IP address

texts:
In pgAdmin4, I have changed my server's connection address to my machine's IP address (changed from localhost), and while some dbs are still there, one isn't.

Do you know how can I get it back?

Thanks.

PostgreSQL 10.4

EDIT: Here is the pg_hba.conf  https://pastebin.com/wb3wL35U

EDIT2: Also, in poistgresql.conf, if I set listen_addresses = 'localhost' I get the:

could not bind IPv4 address "127.0.0.1": Only one usage of each socket address (protocol/network address/port) is normally permitted.

If I revert it to: listen_addresses = '*' database server starts.


texts: Find SSIS package which calls stored procedure

texts:
We are trying to catch errors into an Error Log Table, similar to below.
We have many Master SSIS packages, calling many stored procedures.

Following code will log into the Error Log Table. Is there a SQL reserved keyword to find the SSIS package which executed the stored procedure?

insert into dbo.ErrorLogData
(
    ErrorNumber
    ,ErrorDescription
    ,ErrorProcedure
    ,ErrorState
    ,ErrorSeverity
    ,ErrorLine
    ,ErrorTime
    ,SSISPackage --???
)
VALUES
(
    ERROR_NUMBER()
    ,ERROR_MESSAGE()
    ,ERROR_PROCEDURE()
    ,ERROR_STATE()
    ,ERROR_SEVERITY()
    ,ERROR_LINE()
    ,GETDATE()
    -- what is reserved keyword for SSIS package?
);

SQL Server: Error Logging and Reporting within a Stored Procedure


texts: Recommended ratios for SGA and PGA size in oracle 11g

texts:
I want to change the size of my SGA parameters SGA_MAX_SIZE and SGA_TARGET. And PGA  _AGGREGATE_TARGET, what will be the ratio I should adopt to set these parameters in 11g.
My current database parameters size are;

sga_max_size                         big integer 15G
sga_target                           big integer 13G
pga_aggregate_target                 big integer 900M

Thanks


texts: How to permanently delete a row in oracle forever

texts:
I want to delete a record from a table and no one should ever be able to recover the deleted data.
I have done delete operation but the record is still in the undo segments. And one can do flashback and retrive that row.


texts: Is catclust.sql really required? What is the importance

texts:
We are in a process of upgrading RAC Databases, and when validating the installed components, we saw that some of the databases aren't displaying the RAC component, but still the RAC works normally just like the other ones that show RAC component as installed.

Why isn't the component showing up as installed?

And, if the documentation states that catclust.sql create rac additional views, shouldn't it be a mandatory component for RAC dbs?

Thanks in advance.


texts: Dropping a corrupt table in innodb_force_recovery 6

texts:
As often happens in mysterious shutdowns during large queries, things ran afowl with InnoDB on my XAMPP MySQL installation (Windows 7 x64).  I rebooted and ran Startup Repair, then loaded MySQL to truncate and restart the backup restore.  Only MySQL wouldn't even start.

I eventually found that it according to the log file, "something went terribly wrong".  The InnoDB tablespace was corrupted.  The only way to start the server was with innodb_force_recovery = 6 in the config file, which makes tables read-only.

I have three tables: two that I would like to save and one that I was restoring.  Is there a way to truncate, drop, or otherwise repair/remove the offending table, and would that solve my problem?


texts: Database design: How do i connect these two tables of my database in a normalisation way

texts:
I want to let a  user create a food schedule(schema table) let him create his own food (userProduct) or choise from the supermarkets(supermarketproduct)

the user should be able to make many food schedules(schemas) and should be able to include many userproducts or many supermarketproducts. Both of them can be in one food schedule but, i do not know how to implement an many to many relationship with this because if i include both userproduct and supermarket product one of them will always be null.

Solution:


texts: How can I update values based on the value from the previous day?

texts:
I have a MySQL database temporary table with a bunch of meter readings in it against a system ID and a date (which combined make up the primary key).
Some entries have NULL for the meter reading, in which case they need to be updated to have the same meter reading as the previous day.
There is also a "diff" column which needs to have the difference between the reading for that day, and for the previous day.
I know how to update the tables using code, I am wondering if there is a good way to do it with SQL. Multiple queries is fine (I would guess that at a minimum the NULL readings would need to be updated before the diffs are calculated).
Here is an example schema with fake data, also in this sqlfiddle:
CREATE TABLE Readings (`SystemID` INT, `ReadingDate` DATE, `Reading` INT NULL, `Diff` INT NULL, PRIMARY KEY(`SystemID`,`ReadingDate`));

INSERT INTO Readings VALUES
(1, '2021-02-01', 24, NULL),
(1, '2021-02-02', 26, NULL),
(1, '2021-02-03', 23, NULL),
(1, '2021-02-04', NULL, NULL),
(1, '2021-02-05', NULL, NULL),
(1, '2021-02-06', 28, NULL),
(1, '2021-02-07', 21, NULL),
(2, '2021-02-01', 124, NULL),
(2, '2021-02-02', 126, NULL),
(2, '2021-02-03', 123, NULL),
(2, '2021-02-04', NULL, NULL),
(2, '2021-02-05', NULL, NULL),
(2, '2021-02-06', 128, NULL),
(2, '2021-02-07', 121, NULL);

After running the updates, the values would be (INSERT format kept to make it easier to see):
(1, '2021-02-01', 24, NULL),
(1, '2021-02-02', 26, 2),
(1, '2021-02-03', 23, -3),
(1, '2021-02-04', 23, 0),
(1, '2021-02-05', 23, 0),
(1, '2021-02-06', 28, 5),
(1, '2021-02-07', 21, -7),
(2, '2021-02-01', 124, NULL),
(2, '2021-02-02', 126, 2),
(2, '2021-02-03', 123, -3),
(2, '2021-02-04', 123, 0),
(2, '2021-02-05', 123, 0),
(2, '2021-02-06', 128, 5),
(2, '2021-02-07', 121, -7);

EDIT: To further complicate things, this is a temporary table in the real application.


texts:
When I try to connect to a SQL Server from my local machine I get the following error:

  Cannot connect to Computer1\Instance1.
  The target principal name is incorrect.
  Cannot generate SSPI context.
  (Microsoft SQL Server, Error: 0)

When I tried to connect to the same instance through RDP, it throws the following error:

  TITLE: Microsoft SQL Server Management Studio
  Error connecting to 'Computer1\Instance1'.
  Attempted to divide by zero. (mscorlib)

At the same time it is connecting successfully through CMD. It was working fine, until last night. but now I am unable to connect to it.


texts: Does a backup include dropped, hidden columns still in a table's physical structure?

texts:
If I have a table with an NVARCHAR(MAX) column and I drop it, the standard way to reclaim the space it used is to either:

Rebuild the clustered index or
Run DBCC CLEANTABLE

If I didn't perform either of these, the column still remains (visible
 via sys.system_internals_partition_columns) in the physical structure of the table, and did a full backup & restore of the database, would the restored copy still have dropped column in its physical structure and take up the same amount of space as before I had dropped the column?

For my own use case I am looking at SQL Server 2012, but I presume this functions the same across all relatively modern versions. I am aware this is easy to test, but I am hoping someone has more background knowlegde on this as I haven't been able to find much myself.

References:

Clean unused space when a SQL Server table with a variable length column is dropped
SQL Server table columns under the hood


texts: MariaDB restore InnoDB db from file system files

texts: How can I restore a InnoDB database from filesystem files (FRM/IBD)? I cannot run db service from that system, so I tought to move its file to another Windows machine but I don't know if is there any procedure to follow in this case.


texts: SQL Server Persisted Computed Columns, Issues during Mass Insert or Update?

texts:
I have a database with a computed, persisted column which validates columns, checks if its an integer, and converts. Sometimes streetnumber is alphanumeric, like 5C, other times it's simply an integer.
Do SQL Server computed, persisted columns have any issues during mass inserts or mass updates?
We refresh load data during period, we tested in our dev environment, do not locate issues.
Just want to understand what other problems can occur.
If it affects performance, or  better to use non-computed column where the value is manually typed for each insert/update.
[StreetNumberInt] AS (TRY_CAST([StreetNumber] AS [int])) PERSISTED,

CREATE TABLE [PM].[Address]
(
    [AddressId]              [int] IDENTITY (1, 1) NOT NULL,
    [StreetName]             [varchar](32) NULL,
    [StreetNumber]           [varchar](12) NULL,
    [StreetNumberInt]        AS (TRY_CAST([StreetNumber] AS [int])) PERSISTED,
    [StreetNumberThrough]    [varchar](12) NULL,
    [StreetNumberThroughInt] AS (TRY_CAST([StreetNumberThrough] AS [int])) PERSISTED,
    [City]                   [varchar](25) NULL,
    [State]                  [varchar](2) NULL,

)

Using an Azure SQL database


texts:
I've got a replacement map table

CREATE TABLE #ReplacementMap (old NVARCHAR(10), new NVARCHAR(10))
INSERT INTO #ReplacementMap VALUES ('A',5)
INSERT INTO #ReplacementMap VALUES ('C',9)
INSERT INTO #ReplacementMap VALUES ('D',4)

and a table of strings

CREATE TABLE #String1 (name NVARCHAR(50), string1 NVARCHAR(100))
INSERT INTO #String1 VALUES ('John','AB')
INSERT INTO #String1 VALUES ('Kyle','ABC')
INSERT INTO #String1 VALUES ('Steven','ABCD')

in which I need to replace bits of string based on the replacement map table so that I get the below results:

John,5B
Kyle,5B9
Steven,5B94

My current solution is to nest REPLACE function but due to the number of replacements that I need to do, it's not an elegant way to it.


texts: Oracle writes to SSD drive are really slow

texts:
My customer recently ported their database to a new server that has an SSD drive.
This new server uses Windows 2016 Server OS, and the SSD drive model is a DELL PERC H740P Mini
The good news is the database reads are MUCH faster than the old server. BUT, the database writes are MUCH slower.
Consider the following simple SQL statement:
UPDATE MY_TABLE SET MY_COLUMN=:VALUE WHERE MY_INDEX=:INDEX_NO; COMMIT;
On the old server with a spinning disk, this operation takes about 40ms on average.  On the new server with an SSD, this operation takes about 200ms on average.  This was a shock for me!
I have read some articles indicating that DB writes on an SSD could be the same or worse performance, but  these posts were quite old so I am not sure whether to trust this...
SSDs with Oracle
https://www.databasejournal.com/features/oracle/oracle-11gr2-io-performance-tuning-solid-state-drives-1.html
If anyone has suggestions how I might be able to tune the performance via a configuration, that would be appreciated.


texts: Is 'data corruption occurs in the clustered index' still open?

texts:
Based on sp_Blitz Result: DBCC CHECKDB Not Run Recently, there is a reported bug in SQL Server 2012 & 2014, Data corruption occurs in the clustered index when you run online index rebuild in SQL Serve, is it still an open issue in Azure SQL Server 2019?
How can I investigate it?


texts: How to set the language of a doc to a DB field

texts: Is there a way to set the language of a document body to a value fetch from the DB in SSRS? I tried with a simple expression and I got this error. I wonder what the best workaround is


texts: SQL Server 2008 R2 Ent trial expired move to express?

texts:
So someone let our SQL server 2008 R2 enterprise trial expire on a web server. It was part of a plan to restart a project and move everything we had over to MySql instead. We don't need anything special other than keep the old website running for a couple months.

Is express or compact capable of running a small website?

Is it possible downgrade to express or compact and how?

How can I backup the existing databases now that we have been effectively locked out of them?


texts: MySQL 5.7: deprecated partitioning method warning error

texts:
I have here a couple of MySQL DB tables optimised with partitioning as in:

ALTER TABLE radpostauth PARTITION BY HASH(id) PARTITIONS 32;

With the upgrade do MySQL 5.7, the following warning started appearing in the error logs:

The partition engine, used by table 'y.radpostauth', is deprecated and
will be removed in a future release. Please use native partitioning instead.

I have an idea I can do an ALTER TABLE REBUILD however, no clue about what to do to make it compliant to 5.7 and beyond.

What do you advise?


texts:
I want to run ANALYZE TABLE and OPTIMIZE TABLE on a large production MySQL database (v 5.0) to increase performance.

But as this would lock tables causing downtime for an unknown amount of time, I want to test it on a separate instance first. I want to run some test queries on a test database, then optimize it, then run the test queries again.

Is it possible to copy a MySQL database while keeping the same un-optimized physical storage of table and index data?


texts: What is the difference between SET NOEXEC ON and SET PARSEONLY ON?

texts:
Currently, SQL server supports the following statements: SET NOEXEC and SET PARSEONLY.
From the documentation:
SET NOEXEC ON:

When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL statements but does not execute them.

SET PARSEONLY ON:

When SET PARSEONLY is ON, SQL Server only parses the statement.

I guess it's "compiles" vs. "parses". "Compiles" should include "parses".
If I want to verify the correctness of SQL Scripts which one should I use? I am inclined to answer: SET NOEXEC ON.
But what else does compile do? Does "compile" mean the execution plans are created/modified? Is the existence of objects checked?
What happens if you have:
SET NOEXEC ON
SET PARSEONLY OFF

I find having both options available confusing.
Thanks


texts:
I'm developing a web application that will support user authentication with corresponding roles for each user. Also, my users can be of different types and have different fields associated with them. Some of the fields that each user has will be the same, as in:

email, password, first_name, last_name, etc.

But some of the fields for different user types will be different. For example:

User Type: Instructor
Fields unique to this type of user
----------------------------------
hourly_rate, tax_status

==================================

User Type: Student
Fields unique to this type of user
----------------------------------
instrument, monthly_charge, program

==================================

User Type: Employee
Fields unique to this type of user
----------------------------------
hourly_rate, location

This is a brief example of the types of fields that can be similar and unique between these types of users.

The possible setups I've thought of are:

Table: `users`; contains all similar fields as well as a `user_type_id` column (a foreign key on `id` in `user_types`
Table: `user_types`; contains an `id` and a `type` (Student, Instructor, etc.)
Table: `students`; contains fields only related to students as well as a `user_id` column (a foreign key of `id` on `users`)
Table: `instructors`; contains fields only related to instructors as well as a `user_id` column (a foreign key of `id` on `users`)
etc. for all `user_types`

or:

Table: `users`; contains all possible columns for all users and allow columns that could be filled for one user type but not another to be NULL

My question: Is one of these a better approach over the other or are both terrible and I should look at something else entirely?


texts: SQL Server: trigger for insert update and delete caught when no data been changed?

texts:
I've added a simple table to a db called: aaa_log with columns :(id ,name, op))

CREATE TABLE aaa_log (
   [id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
   [name] [varchar](50) NOT NULL,
   [op] [varchar](50) NULL)

id column used just in manner to keep the order.

I've added a trigger (for insert, update, delete) to all other tables in a db ussing the following script:

declare @cmd varchar(max)
declare trigger_create cursor for
select 'Create trigger ['+TABLE_SCHEMA+'].[xxtr_'+TABLE_NAME+'_auto]
  on ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] fro insert,update,delete as
  BEGIN
   declare @op varchar(20)
   if exists(SELECT * from inserted)  and exists(SELECT * from deleted)
   begin
     set @op = ''update''
   end
   if exists(SELECT * from inserted)  and not exists(SELECT * from deleted)
   begin
     set @op = ''insert''
   end
   if not exists(SELECT * from inserted)  and exists(SELECT * from deleted)
   begin
     set @op = ''delete''
   end
   insert into aaa_log([name],[op]) values('+TABLE_SCHEMA+'.'+TABLE_NAME+', @op)
  END
'
from information_schema.tables
where table_type='BASE TABLE' AND table_name <> 'aaa_log'

open trigger_create
fetch next from trigger_create into @sql
while @@FETCH_STATUS =0
BEGIN
  exec(@sql)
  fetch next from trigger_create into @sql
END
close trigger_create
deallocate trigger_create

Nobody else update the aaa_log table, just these triggers, but when I check the  aaa_log table I see some rows where op is NULL.

The only option I can think off is that both inserted and updated is null, so how the trigger had been activated?

Any explenation?


texts: EF vs SP performance inserting multiple rows

texts:
I know of three most common ways of doing so:

Using a relation level as Entity Framework, which as far as I know has the worst performance.
Using a bulk insert, such as INSERT INTO table1 (x,y) VALUES (x1,y1), (x2,y2)
Using a stored procedure and calling it with a DataSet as a parameter.

What is considered to be the most efficient?