All Questions
9,096
questions
1
vote
1
answer
328
views
Generate SQL scripts in powershell
I want to be able to generate simple repetitive scripts in SQL using Powershell as the scripting language.
For example, If I want to generate a SQL GRANT command for every table in a database, and ...
1
vote
1
answer
2k
views
MySQL: My table size using a query does not match the IBD's file size on disk
I have a table with about 600 million rows. I ran the following commands and noticed
select table_schema "DB name (table_schema)",
-> sum((data_length+index_length)/1024/1024/1024) AS "DB ...
1
vote
0
answers
105
views
sql server connectivity broke down to server after its IP address has moved to a subnet. How to restore its connectivity?
I a server called Vermont on a different domain, with a trust relationship.
I can ping the server there.
But when I try to connect to it, from my SSMS I get the following error:
I used to connect ...
1
vote
1
answer
185
views
SQL Server 2016: Table can't grow while 313 MB is still available in the database
Yesterday, the below error was reported:
Could not allocated space for object 'dbo.X'.'Y' in database 'Z' because the 'PRIMARY' filegroup is full
After deletion of some records from the table by an ...
1
vote
2
answers
11k
views
Oracle Client trace not generating
I'm using Oracle Client 12.1.0.2 and I need to generate client trace because im unable to connect my sql developer to the database server. However, i'm unable to generate the trace.
Here is my sqlnet....
1
vote
1
answer
113
views
Comparing the contents of 2 .nsm (naming standards) files
I have 2 naming standards files from ERwin (.nsm extension), and I want to compare them to see if they contain the same rules. Is there some sort of automated way to check this?
If there isn't a way ...
1
vote
2
answers
472
views
How postgresql full text search disable file parser?
select to_tsvector('english_nostop', 'u,s') @@ to_tsquery('english_nostop', 'u<->s');
return t, while
select to_tsvector('english_nostop', 'u.s') @@ to_tsquery('english_nostop', 'u<->s');
...
1
vote
1
answer
1k
views
MySQL Group Replication Multi-Primary Setup
We are running three MySQL Servers in a Group Replication Multi-Primary Setup.
Here are is one of my configuration of one member of that GR group:
# General replication settings
gtid_mode = ON
...
1
vote
2
answers
2k
views
Cannot Attach Database to SQL Server Management Studio 2008 R2
I installed SQL Server 2008 R2 just now and wanted to attach my previous databases. Attaching a Database gives an error.
Attached is the image of the error.
Any Suggestions and help will be ...
1
vote
2
answers
75
views
stack data into a single column
SELECT * would result in:
course_id + group
-----------------
1 + SE123
1 + BA134
2 + SE213
3 + SE223
3 + BA344
4 + SE222
I would like it be like this:...
1
vote
2
answers
1k
views
Select column to be inserted based on input column value
Assume the following two tables (and ignore the quality of the database design):
Table 1 has columns id, sequence_number, value: it contains many rows.
Table 2 has columns id, seq1, seq2, seq3, seq4, ...
1
vote
2
answers
4k
views
what is the difference between db_datareader and db_denydatawriter?
I am trying to assign a SQL Server database role to a user who just needs to be able to see data across all tables.
I was wondering what is the difference between db_datareader and db_denydatawriter?
...
1
vote
2
answers
3k
views
MySQL query INSERT INTO ... failed - The Table "x" is full
I apologize if my question is incomplete or noobish, I have extremely limited knowledge of SQL but the boss wants me to figure this one out.
Environment: Webmin/Virtualmin | MySQL version 5.1.73 on ...
1
vote
1
answer
925
views
Delete query does not appear to open space on the SQL Server
Our SQL Server (version 2012) is housed on a cloud server, for which we pay whenever we need additional space. We determined that we could delete some of the data to prevent the need for continually ...
1
vote
1
answer
2k
views
Passing passwords more securely to mysql utilities on the command line
This question is related to another question I subsequently asked here: Using the output from mysqldiff.exe to transform database2 to match database1.
I have been trying to use mysqldiff.exe, part of ...
1
vote
1
answer
2k
views
Why the Accounting DB Schemas do not allow to maintain a clean Products Table?
As far as I know, the database schema of the vast majority of Accounting Systems follows this architecture:
Considering all the FKs, I know that the product table is going to grow in time and, if I ...
1
vote
1
answer
3k
views
What are the benefits vs. drawbacks to using a clustered index in SQL Server [closed]
I have scenario where the data will be loaded pretty much in the order it will be grouped by in reports (date, varchar foreign key, int foreign key). Is there still a benefit to indexing? I was ...
1
vote
1
answer
113
views
Added a new node but still getting RuntimeException: "Not enough space for compaction"
I have cluster cassandra-2.2.19-1 with 3 nodes. On each of them i have errors in system.log
ERROR [CompactionExecutor:2430] 2023-05-12 11:52:44,047 CassandraDaemon.java:231 - Exception in thread ...
1
vote
2
answers
6k
views
WSUS for SQL server patching
Can we patch Microsoft SQL server using WSUS?
Is it not recommended?
1
vote
1
answer
3k
views
How to filter multiple many to many relationship based on multiple tags?
I've a scheme of two main tables: problem and tag and a relation (which is a many to many connector) table: problem_tags which excerpt of them is like:
Problem table:
----+---------------------------...
1
vote
1
answer
494
views
Keyword not supported: 'statslog_on'
I am trying to connect to a SQL 2017 instance via SSMS 18.2 with the 'Additional Connection Parameters'
StatsLog_On=Yes;StatsLogFile=C:\Temp\TestODBC.txt
The documentation at Using Connection String ...
1
vote
1
answer
917
views
Table design for a dictionary that can have words with many different spellings
I'm working on a small, personal dictionary database in Microsoft Access (the 2013 version). There are a lot of words in English that have two or even more spellings. Realistically speaking though, ...
1
vote
1
answer
2k
views
How to refresh Oracle data dictionary to remove deleted tablespace and datafiles?
Long story short we have a server with two Oracle instances running on it: ora1 and ora2. Somebody defined an undo tablespace for ora2 and provided the same datafile of the undo tablespace datafile of ...
1
vote
2
answers
9k
views
Shrink my .mdf file in SQL Server?
I have a 10 GB .mdf file with 250 mb .LDF.
I want to shrink the 10 GB .mdf (if possible)
I read about the dbcc command, but I read it in context of shrinking the .LDF (into any size desired using ...
1
vote
1
answer
635
views
MySQL 8.0.20 - Master Replica scheme, increasing delay between Source and Replica
We have a Source Database of about 1TB, and a Replica on another server (similar architecture).
We have setup Master Slave replication and delay between Master and Slave is always growing since.
...
1
vote
1
answer
3k
views
Duplicate entry in MySQL slave
I have a MySQL master-slave replication setup. When I checked show slave status, I got the result as Slave_Sql_running:No and last error as Duplicate entry 'x' on query.
But when I tried to delete ...
1
vote
3
answers
188
views
Equality Range Query in MySQL runs very slowly
Table event (id, source_id, start_time), 140k records
Index (source_id asc, start_time desc)
My query:
SELECT * FROM event where source_id in (4, 5, 3, 10)
order by start_time desc limit 100;
Run ...
1
vote
1
answer
4k
views
Data Warehouse Kimball Customer and Customer Address Dimensions
I have a Customer Table and Multiple Customer Addresses. A customer can have multiple addresses: home, work, vacation,etc.
In Kimball data warehouse, would I utilize a bridge table to go between ...
1
vote
1
answer
762
views
Setting up DRBD on an active MySQL server
When it comes to setting up DRBD and MySQL, is the following possible?
Set up DRBD on an active MySQL server
Set up DRBD with no downtime allowed
1
vote
1
answer
6k
views
mysql 5.6 gtid replication slave stuck (system lock)?
I have set up 5.6 gtid based replication (on 5.6.26) it seemed to work when I did it, it replicated my random test db over that I created beside normal data.
However at some point something must have ...
1
vote
2
answers
2k
views
Calculating values from three related tables, without using join or union
I have a simple basic three tables on SQL Server database:
What is needed is to get the quantity available, without using joins or unions which is calculated:
Quantity Available = Items Opening ...
1
vote
3
answers
2k
views
Changing Compatibility level from 90 to 120 in sql 2014 migration
I have SQL 2008R2 server to migrate to SQL2014 Server.
A side by side upgrade is what we are thinking for few databases in SQL 2008R2 which are having compatibility level of 90 or 80.
Can I migrate ...
1
vote
3
answers
815
views
How to not allow to insert a new record if count of current records exceeds a specified limit
I have two tables as the following
team
`id` BIGINT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`member_limit` INT NOT NULL
team_member
`id` BIGINT PRIMARY KEY AUTO_INCREMENT,
`team_id` ...
1
vote
1
answer
1k
views
Generate random row order in a subquery
I know other answers here (and here) say to order by newid(). However if I am selecting top 1 in a subquery - so as to generate a random selection per row in the outer query - using newid() yields the ...
1
vote
1
answer
2k
views
Kill concurrent materialized view refresh PID
Concurrent refreshes of materialized views don't have a record in pg_stat_activity, other than the initial (~immediately completing) query to start them.
Is there some other way to obtain the PID, in ...
1
vote
1
answer
386
views
Master port is disabled from Public IP, MySQL log is not getting refreshed
My MySQL Replication setup is over internet.
Master is on Public IP.
If I stop mysql service in master, immediately an error message is thrown in slave. And when I start the service, slave is ...
1
vote
1
answer
1k
views
Upgrade production database during software development lifecycle
Background
Using Oracle, there are a number of tools that help with migrating and applying development changes into the production environment (such as Embarcadero Change Manager). These tools can be ...
1
vote
3
answers
4k
views
How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in subquery in Postgresql
I have legacy tables similar to the following:
employee
------------------------------------
| employee_id | name
------------------------------------
| 1 | David
| 2 ...
1
vote
1
answer
8k
views
INSERT ON DUPLICATE KEY UPDATE with IF
I have sy_version table which has a 2-column primary key: (mod_id, sub_mod):
CREATE TABLE `sy_version` (
`mod_id` VARCHAR(2) NOT NULL,
`sub_mod` VARCHAR(30) NOT NULL,
`version` VARCHAR(50) NULL ...
1
vote
1
answer
153
views
can I exclude or delete .pem files in backup data myqdl?
I am making a backup of the mysql data folder. In this folder are the databases. The method I am using is described in this post which uses ROBOCOPY
During installation, mysql (tested in 5.7.30) ...
1
vote
1
answer
3k
views
Unable to get SQL Server 2016 CTP3 service to start on Windows 10
During install of MS SQL Server 2016, on a developer/DBA workstation client PC which is joined to a domain, the installation using all logon and other installer defaults, completes with failures, and ...
1
vote
1
answer
98
views
Combining separate columns into smallest possible related ranges
I'm trying to discard multiple records that may or may not overlap based on the smallest possible contiguous ranges. I thought of doing something similar to This, however the ranges are numeric ...
1
vote
0
answers
105
views
Multi tenant .NET Core application
We are designing a new multi tenant .NET Core 2.1 application progra, where we are using one database per tenant.
We can give each tenant a different subdomain so that we can base the tenant lookup ...
1
vote
1
answer
2k
views
How to enable/disable SQL agent jobs during fail over (AlwaysOn)
Note: this is not duplicate question.
I have setup AlwaysOn. I have created SQL agent jobs manually on both the nodes. right now all the active jobs are running if node is PRIMARY.
Challenge
My ...
1
vote
2
answers
108
views
Is it possible to restore a database with database backups starting on top of a restored server point in time snapshot that is out of date?
If I regularly backup my whole server where a SQL instance and database lives, and restore it to a different machine, can I use the database backups that have occurred (on the original server) after ...
1
vote
2
answers
828
views
MySQL Command to Mass Change Specific DB from MyISAM to InnoDB and vice versa?
I have an mu WordPress database and I want to change it from MyISAM to InnoDB to test its performance.
Which MySQL command/commands will do it for all of the tables instead of doing manually one by ...
1
vote
2
answers
87
views
Most efficient way to identify records with any number of key columns
I work with large third party data-sets. Long experience has taught me that it is a really good idea to give incoming lines of data a surrogate ID as soon as it enters my system so I can track it ...
1
vote
0
answers
457
views
MariaDB: Terminate long-running clearnup process?
This is a follow up to my previous question about a long-running INSERT SELECT query that may have exceeded the max_statement_time and was Killed. It has been in the Reset for next command state for 3....
1
vote
1
answer
66
views
Necessary to have separate index for lookups against one of a multi-column PK?
Sorry if this is a FAQ, but I'm having problems turning up exactly what I'm asking:
I have a table (mysql) with a primary key which is composed of multiple columns only because this is necessary to ...
1
vote
1
answer
755
views
MYSQL MyISAM data recovery implementation
I am creating integration into eshop script, shop is using MyISAM tables and I need to implement something like transactions in InnoDB, what I actually need is full recovery of data after modifying or ...