texts:
In SQL server instance(s) I manage; I see a lot of activity and messages in SQL server error log for e.g. backup or transaction log backup completed etc.. We have quite a few databases in one instance and amount of error log messages is sometimes quite a few to dig into.

As an accidental DBA, What approach or script can I use to filter the error log for date range specified or finding or filtering troubling errors out of lots of informational messags?

I meant to ask, is there is a T-SQL script or DMVs you can use to filter the error log?


texts: Oracle EM 13c automatically logged in as dbsnmp

texts:
I created a custom super administrator user in Oracle EM 13C.

When this super administrator is accessing a portable container (PDB) it is always logged in as dbsnmp. I cannot find any option to set custom own credentials.

When I use the sysman account on EM 13C, I get asked for credentials to connect to database.

Why it does not ask the custom super administrator user for credentials? Only the sysman? Am I missing some privileges in the super administrator user?


texts: What are Objective Business Reasons to Prefer SQL Server 2008R2 over 2012?

texts:
Basically, I want to take this discussion -- What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2? -- and turn it on its head.

We are a small business, and we are struggling with the pricing-structure change in SQL Server 2012.  We are essentially running a development server as our production/live server; in other words, they're the same box.  Thus we could (until we got legal advice that said otherwise) get away with using a dev. license for it.  But our server is pretty high-end, so the per-core license fees would add up to something outrageous.  Thus, we're considering moving the server back to 2008-R2.  (Or worse, to MySQL or something completely different! if we can't get the pricing problem resolved.)

Our queries and software do not specifically take advantage of any new-in-2012 features that I'm aware of.  But of course the backward migration would be quite a large undertaking.

So what do the pros think?  Is this a recipe for disaster, or are we not totally insane for wanting to do this?  I've listed 2 reasons so far (pricing and non-use-of-features), but I'd like to hear more, on either side of the fence.  Thank you.


texts: Efficient way to query set of rows from MySQL

texts:
I am using MySQL database engine. My Java application uses simple JDBC to retrieve data from database.
I have following database schema: main table with two columns: (INTEGER id, BLOB data). id is primary key.
From user actions I have set of few ids (x, y, ... all integers) for which I want to fetch data.
What is most efficient way to build query? Is there any performance difference if I use

SELECT data FROM table WHERE id IN (x, y, ...)

or

SELECT data FROM table WHERE id = x OR id = y OR ...

or maybe there is another way to build that query?


texts: A database in an availibility group on MS SQL 2014 with a decent log file which at some occasions has been shrinked because of some major jobs filling it up. Usually don't shrink logfiles but as there's a lot of other databases that don't grow at the same time it has been done instead of extending disk, it's a VMWare environment. Now the logfile has grown a lot again, filling up the disk. The log disk was extended, transaction logs backed up and trying to shrink the file about 10-20 GB but there's still only 200 MB free space in the logfile.


texts: benefit of using [md5] over [trust] authentication for local Unix sockets

texts:
I am following a PostgreSQL installation guide that advises (subsequent to installing the database for the first time), to setup a password for the postgres user:

alter role postgres password '<<super-secret-password>>';

... and then change the pg_hba.conf file so that the line for local (Unix) sockets reads:

local all all md5

...  instead of:

local all all trust

It would seem to me that this only adds an extra layer of defense but for somebody to take advantage of local all all trust he would first have to gain access to the local machine, right?

Question 1: do I understand correctly that if access to the local machine is password protected (UNIX password) then there should be no worries over a local all all trust ?

Also, the same guide additionally recommends that a .pgpass file is located in my home folder (I guess for backup scripts that cannot be prompted for a password). The password is stored clear-text in that ~/.pgpass file:

echo 'localhost:5432:*:postgres:<<super-secret-password>>' > .pgpass
chmod 0600 ~/.pgpass

Question 2: So, what is the benefit of using md5 and not trust for local sockets? If one gains access to local machine, he can also read the .pgpass file. Am I missing anything?


texts: Why MySQL doesn't utilize leftmost Index field (UK - col1, col2, col3) in select query?

texts:
I have a department table with the following structure

Create Table

CREATE TABLE `department` (
  `id` binary(16) NOT NULL,
  `name` varchar(255) NOT NULL,
  `type` int(11) NOT NULL,
  `status` tinyint(4) NOT NULL,  -- Possible values are 0,1,2 only
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_2xsp2nild3xbgkg4pln7cviib` (`status`,`type`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Following query doesn't utilise the status index as it is the left most column in unique constraint.

EXPLAIN SELECT * FROM department d WHERE d.status = 1;

    id  select_type  table   partitions  type    possible_keys                 key     key_len  ref       rows  filtered  Extra
------  -----------  ------  ----------  ------  ----------------------------  ------  -------  ------  ------  --------  -------------
     1  SIMPLE       d       (NULL)      ALL     UK_2xsp2nild3xbgkg4pln7cviib  (NULL)  (NULL)   (NULL)       4    100.00  Using where

But when querying on another table which has unique constraint on a single column, it uses the index.

CREATE TABLE `account_unit` (
  `id` binary(16) NOT NULL,
  `status` tinyint(4) NOT NULL,
  `unit_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_2x8b55sxftahu8cjbqld9sw9b` (`unit_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

EXPLAIN SELECT * FROM account_unit au WHERE au.`unit_name` = 'AU1'

    id  select_type  table   partitions  type    possible_keys                 key                           key_len  ref       rows  filtered  Extra
------  -----------  ------  ----------  ------  ----------------------------  ----------------------------  -------  ------  ------  --------  --------
     1  SIMPLE       au      (NULL)      const   UK_2x8b55sxftahu8cjbqld9sw9b  UK_2x8b55sxftahu8cjbqld9sw9b  767      const        1    100.00  (NULL)

As per my understanding, left most column on a multi column constraint will be utilized  as index on select queries. Enlighten me if I'm wrong.


texts: MSSQL - Insert Into adding data into the middle of the table?

texts:
A bit of a random one here... I've got a basic table that I'm using for temporary data inserts - and for that reason, I don't need or want a unique ID - however, I'm seeing odd behaviour! (or it feels odd to me at least)
If I insert data into the table, and then immediately run a select statement on the same table (no ordering going on) - the new row ends up in the middle of the results - how / why ?
This isn't a problem for me - I don't particularly care how it orders the data for this particular table (and I'm aware we could add a unique column, PK etc, autoinc identifier etc to handle this situation) - but it's just something I've never seen before - I'm convinced that on the hundreds of occasions I've done this previously, new data always ends up at the bottom of the list! - Am I going mad? is this normal? is there a way to always insert data to the end ?
Thanks
Chris


texts: how to find similar word with more similarities

texts:
how to Find words with length less than or equal...

  declare @inp nvarchar(max),@data nvarchar(max)
  set @inp='You can dance, you can jive, having .... jove... jve, ...'
  set @data = 'jeve'
  select @inp as results where @inp like '%' + @data +'%'

@inp not return any data, but i want show:

results
=====
jive
jove
jve


texts: restore mysql database from ibdata1 and frm files

texts:
I am trying to restore some database data from my tables frm files. I am running a mariadb database. The data structure works fine and i can see the tables etc. But as soon as i add the ibdata1 and logfiles i run into trouble and get the errors below.
I've tried to follow recommendations of the other similar posts but nothing seems to work... Any ideas? BR Lukas

my my.cnf config file:

[client-server]

#
# This group is read by the server
#
[mysqld]
# Disabling symbolic-links is recommended to prevent assorted security risks
innodb_log_file_size=170M
innodb_force_recovery = 1
symbolic-links=0

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Journalctl -xe error:

jun 09 01:29:04 ipx.eu-central-1.compute.internal sudo[15322]: pam_unix(sudo:session): session opened for user root by ec2-user(uid=0)
jun 09 01:29:04 ipx.eu-central-1.compute.internal systemd[1]: Starting MariaDB 10.2 database server...
-- Subject: Unit mariadb.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mariadb.service has begun starting up.
jun 09 01:29:04 ipx.eu-central-1.compute.internal mysql-prepare-db-dir[15360]: Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.
jun 09 01:29:04 ipx.eu-central-1.compute.internal mysql-prepare-db-dir[15360]: If this is not the case, make sure the /var/lib/mysql is empty before running mysql-prepare-db-dir.
jun 09 01:29:05 ipx.eu-central-1.compute.internal mysqld[15399]: 2020-06-09  1:29:05 140284526120768 [Note] /usr/libexec/mysqld (mysqld 10.2.10-MariaDB) starting as process 15399 ...
jun 09 01:29:05 ipx.eu-central-1.compute.internal mysqld[15399]: 2020-06-09  1:29:05 140284526120768 [Warning] Changed limits: max_open_files: 1024  max_connections: 151  table_cache: 431
jun 09 01:29:05 ipx.eu-central-1.compute.internal systemd[1]: Started MariaDB 10.2 database server.
-- Subject: Unit mariadb.service has finished start-up
-- Defined-By: systemd
--
-- Unit mariadb.service has finished starting up.
--
-- The start-up result is done.
jun 09 01:29:05 ipx.eu-central-1.compute.internal sudo[15322]: pam_unix(sudo:session): session closed for user root

mariadb.log file:

2020-06-09  1:17:36 140094574546752 [ERROR] InnoDB: Page [page id: space=0, page number=308] log sequence number 41316604 is in the future! Current system log sequence number 1620080.
    2020-06-09  1:17:36 140094574546752 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to http://dev.mysql.com/doc/refman$
    2020-06-09  1:17:36 140094574546752 [ERROR] InnoDB: Operating system error number 13 in a file operation.
    2020-06-09  1:17:36 140094574546752 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
    2020-06-09 01:17:36 0x7f6a4f59cf40  InnoDB: Assertion failure in file /builddir/build/BUILD/mariadb-10.2.10/storage/innobase/fil/fil0fil.cc line 752


texts: SHRINKFILE best practices and experience

texts:
Preamble: In general it's a big no-no, but believe me that are rare cases when space is really needed. For example Express Edition is limited to 10GB. Imagine that you discovered that with a data type conversion (a blob column) you can free up significant amount of space. But after that the DB file still has the same size as we know, and the 10GB limit also didn't change magically. So some kind of SHRINK is needed. That was an example.

In my test environment I performed:

DBCC SHRINKFILE (DBFile, NOTRUNCATE)
DBCC SHRINKFILE (DBFile, TRUNCATEONLY)

That did the trick (I know that that minimizes the free space, in real world I would leave free space). it took many-many hours to finish. As we know it's a single threaded process Strange behaviour DBCC Shrinkfile, "it works as a series of very small system transactions so there is nothing to rollback." - Paul Randal http://www.sqlservercentral.com/Forums/Topic241295-5-1.aspx. We also know that it messes up the index fragmentation big time http://www.mssqltips.com/sqlservertip/2055/issues-with-running-dbcc-shrinkfile-on-your-sql-server-data-files/ and I can confirm that. I didn't experience log file grow though described in http://www.karaszi.com/SQLServer/info_dont_shrink.asp

I issued some INDEX REBUILD and REORGANIZE and those finished within seconds.

My questions:

What's the big deal about shrink if I can just fix the index fragmentation after the shrink within seconds? I don't understand. Here on DBA stackexchange the "shrink" topic (https://dba.stackexchange.com/tags/shrink/info) says "Pretty much the worst thing you could do to a SQL Server database. In short: It sacrifices performance to gain space." plus refers to another popular article about it. But index fragmentation can be fixed.
Why I didn't experience any log file growth?
What if REBUILD the index first after the space free-up operation. Can that substitute the first DBCC SHRINKFILE (DBFile, NOTRUNCATE) so I just need to DBCC SHRINKFILE (DBFile, TRUNCATEONLY)? I have a feeling that the two work on different logical level but I have to ask this.

Bottom line: I promise I won't do shrink regularly or anything. But this is a situation where a cap is hit and shrink is needed.

Answer to my 2nd question: I didn't experience transaction log file growth, because I was fiddling in a developer test environment. The transaction log file would grow in a production environment (assuming Full Recovery Model). You should mimic that in your testing environment too. The tr log file growth is actually bigger than the space you can free up inside the database file. In the end you not only want to shrink the database file, but also the transaction log (how and when to do that depends on the recovery model). Production environments I saw had so screwed up index fragmentation that it can be just better. My script reindexes every index which fragmentation level is above 30%.

To handle the growth, you may have to switch from full recovery to simple while you are doing the once-in-a-lifetime conversion. But that will break the backup chain.

Answer to my 3rd question: do the reindex/index rebuild after the DB file shrink, because the shrink messes up index fragmentation.

I haven't experimented how all of this affects query statistics.

Script for reindexing: see Example D of TechNet sys.dm_db_index_physical_stats (Transact-SQL). An article on incremental shrink, which will lead to other valuable content: http://www.mssqltips.com/sqlservertip/3178/incrementally-shrinking-a-large-sql-server-data-file-using-powershell


texts: Insert performance increases under load: Why?

texts:
I have a piece of code that performs inserts into highly denormalized tables. The tables have numbers of columns ranging from ~100 to 300+. This is SQL Server 2008 R2, running on Windows Server 2008.

Each insert consists of inserting to a number of tables under the same transaction. Some inserts are batched by NHibernate, but some cannot be, but they are all under the same transaction nonetheless.

When I perform inserts for say 500 times by repeatedly calling a piece of code that performs the insert, I get an average of ~360 ms.

The weird bit is, when I run the test code simultaneously using 4 processes (the same exe run from 4 different command prompts under windows server 2008), the insertion performance per call gets much better. I see bursts that go as fast as 90 ms (almost X4 faster). I'm measuring the insertion time from the code.

Since the 4 processes know nothing about each other, I'm assuming that this has something to do with SQL Server, but I have absolutely no clue why. I'd like to know why this is happening and if there is any configuration that would allow me to get the same performance when the inserts are not that frequent.

Suggestions regarding SQL Server monitoring methods to understand what is going on at the db level are equally welcome.


texts: understanding ORA-01792 maximum number of columns in a table or view is 1000

texts:
We've recently migrated a server from Oracle 11 to Oracle 12c and ran into a few annoying issues. One of them is that a few a reports give the following error:

  ORA-01792: maximum number of columns in a table or view is 1000

OK, fine, I understand there have to be some limits. The problem is that the query worked before and that I see no reason for the DB to load all columns. None of tables in the query have more than 1000 columns. Combined they could have. So if I did a select * from everything; I understand that the output runs into limits because a result set is virtualy the same thing as a view/table (is it? I'm just guessing here?)

The "problem query" joins a bunch of big tables and a few smaller ones multiple times, so yes, the total column count could be more than 1000.

However, the query only selects 30 or so columns. Add the fields used for joining and maybe even some additional fields used by indices in the background it might add up to 50~70 columns that are needed to preform the query. All of that information is well defined within the query and table information. So why on earth would the DB even care about all the additional columns?

The solutions I've read so far are to change the query or to disable this warning/error. Both of which are non-solutions to me. The only reason I can think of for disabling the warning is that Oracle decided to inform users earlier and that this "problem" existed in the background in both versions.

Any ideas about this? Or my understanding of how 'big' queries are handled?


texts: MySQL database backup every day using java code

texts:
We are developing a Java web application using Spring, Hibernate and MySQL.

I want our application to automatically backup the MySQL database daily; is there any way to accomplish that with Java code?


texts: How can I search SQL Server for maintenance plans that execute specific tasks?

texts:
Sometimes certain SQL Server maintenance plan tasks can be problematic, like Shrink Database, Rebuild Index, Update Statistics, etc. I'd like to search for the use of one or more of these tasks on an instance of SQL server, for further review of whether they are necessary.

Is there a way to query for maintenance plans that perform one or more specific tasks?

(Not limited to the tasks that I mentioned.)


texts:
I'm teaching myself SQL and I ran into a question that may be related to joining tables. I'm just a bit unsure.

Say, you have two tables, one listing the customer's creditcard_number, and one listing the customer's order_number. I am trying to match a customer's creditcard_number with their order_number.

What might this query look like? Would I first have to join the tables?


texts: Is it possible to decouple row size estimation from maximum column width?

texts:
In other words, is it possible to declare a column VARCHAR(n) and have the estimated size be something other than n/2?

An example use-case might be storing URLs - you expect most to be relative and less than 20 characters or so (eg "About/AboutUs"), but want to support the occasional long URL with lots of query string parameters.


texts: How many Read IOPS are too many? (Should I upgrade my RDS instance memory?)

texts:
Amazon's best practices indicate you should have enough memory on your RDS instance to hold most of your dataset in memory, and that not having enough will result in more Read IOPS, as information is swapped into memory.

Our instance (SQL Server m3.medium) generally shows 150MB of "freeable memory," which doesn't seem like much of a margin. Our read iops ranges from 6-8 on average with momentary spikes to as much as 120 (or in one isolated case, 224).

We are not unhappy with our performance right now (coming from a far-inferior hosting environment), but as we try to decide whether to purchase a reserved instance, we'd like to know we're "ok" on resources for the term of the lease.

Thanks for any insight!


texts: Postgres service restart in a Master - Replica

texts:
Postgres 10 database running on a Centos RHEL 7. This Database is a Master and it has 1 stand_by replica acting as a mirror. Well my intention was to change the wal_level in the configuration from replica to logical, so I did, I changed it in the postgresql.conf and tried to restart the service as the instructions in the postgresql.conf said.

systemctl restart postgresql-10.service didn't worked, when I execute systemctl status postgresql-10.service this is shown:

● postgresql-10.service - PostgreSQL 10 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-10.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since mar 2018-10-30 10:40:40 GMT; 12min ago
     Docs: https://www.postgresql.org/docs/10/static/
  Process: 19359 ExecStart=/usr/pgsql-10/bin/postmaster -D ${PGDATA} (code=exited, status=1/FAILURE)
  Process: 19353 ExecStartPre=/usr/pgsql-10/bin/postgresql-10-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 19359 (code=exited, status=1/FAILURE)

oct 30 10:40:40 server systemd[1]: Starting PostgreSQL 10 database server...
oct 30 10:40:40 server postmaster[19359]: 2018-10-30 10:40:40.431 GMT [19359] FATAL:  lock file "postmaster.pid" already exists
oct 30 10:40:40 server postmaster[19359]: 2018-10-30 10:40:40.431 GMT [19359] HINT:  Is another postmaster (PID 8545) running in data directory "/var/lib/pgsql/10/data"?
oct 30 10:40:40 server systemd[1]: postgresql-10.service: main process exited, code=exited, status=1/FAILURE
oct 30 10:40:40 server systemd[1]: Failed to start PostgreSQL 10 database server.
oct 30 10:40:40 server systemd[1]: Unit postgresql-10.service entered failed state.
oct 30 10:40:40 server systemd[1]: postgresql-10.service failed

Ok, now the Centos service interface shows that the service has failed, due to some postmaster.pid lock file that the postmaster process uses, that's true, the file exists and the postmaster process is still running, the database cluster is still accessible.

I read in the Postgres docs that the Smart Shutdown mode may be waiting for some sessions in the pg_stat_activity to finish.

What can I do to safely restart the cluster with the new wal_level setting and sync the status of the postmaster process and the postgresql-10 service


texts: Is it possible to restore a database from 10.50.4033 in 10.00.1600

texts:
I did one project for my last year exam I want to restore a database.

Is it possible to restore a database from v10.50.4033 to 10.00.1600.

If yes, how is this possible.


texts: Can there be situations where using (noexpand) on an indexed view is undesirable?

texts:
From the past experience using the schema bound and indexed view in a query with (noexpand) hint usually dramatically improves performance if the view is designed well.
Apparently using the indexed views is not implicit, as it requires a table hint.
But can there be any scenarios where supplying (noexpand) hint may be undesirable while the view remains schema bound and indexed?
In other words, should schema bound indexed views be "not expanded" implicitly at all times instead of a developer having to remember to supply the hint to turn off view expansion?
The way I read BOL it states that Enterprise is supposed to imply (noexpand). But I know for certain that it does not, as I am on Enterprise and adding (noexpand) provides dramatic performance improvement, it never failed to. So it is not correct to state that noexpand is an Enterprise feature.


texts: Which DBs hide rows INSERT order (header metadata related)?

texts:
I discovered that PostgreSQL has a "t_xmin" row header metadata field that is used to store the transaction ID of an INSERT command.
So, as I understand, even if I use a random INT primary key instead of an auto-incrementing one, it is still possible for someone with full disk access to learn in which order the rows were created.
There may be more or less painful workarounds but I wonder if there are more "tight lipped" DBs that would let me avoid the problem altogether.
From my research, SQLite may work as it does not seem to have any transaction id row header field and journaling can be turned off, but I am not sure to be right and what other options exist.
All other requirements are secondary at this stage : I do not mind about ACID compliance, SQL vs NoSQL, embedded vs server, ... it just needs to be open-source and mature / well known.
Bonus question for the hardware layer : SSD vs "fully in memory", does it matter ?
I guess HDD are a no-go due to the risk of closely stored sectors positions leaking informations about rows insert order / time.


texts:
This is a bit of an embarrassing question, and I can't believe I've missed this for so many years.

I have a vendor third-party database that has 401 heap tables. I've recently got into using Brent Ozar's scripts and setup sp_BlitzFirst to run every 15 minutes to gather wait stats, etc.

What it uncovered was every time it ran across a 24 hour period it was telling me to fix Forwarded Records. What will probably shock some readers that I ran a query across the DMVs and got back some tables with over 150,000 forwarded record values.

I understand to fix this is to have a clustered index across the table, or as a temporary work-around to run ALTER TABLE [tablename] REBUILD.

What I haven't been able to find however is whether this takes the table offline, and whether there are any other problems I should be aware of before running this command.

I am using the Enterprise Edition of 2008 R2, and I wonder whether running it this way will remove the need for an outage?

ALTER TABLE [tablename] REBUILD WITH (ONLINE = ON);

Does anyone have any experience with this?


texts: Moving DBs (including System DBs) to new LUNs on a single node cluster

texts:
We have a single node cluster (SQL2K8 R2) currently utilizing storage on our old SAN.  We'd like to migrate to storage on the new SAN without rebuilding or moving the user DB.  The infrastructure team's idea is to present the new LUNs to the server/cluster and then move all the files to these new LUNs.

The obvious problem here is that the system DBs can't simply be moved via the methods user DBs can be moved.  And, while I could use the documented methods to move the system DBs, under SQL2K8 and later, the Resource DB can't be moved at all.  So, it doesn't seem like this is possible in a straightforward way.

I'm thinking this would possible using sort of a shell game of shutting down SQL Server, copying all files to the new LUNs, renaming the old LUNs, renaming the new LUNs to the original old LUN names, and then bringing up SQL Server.  I've not tried this before but it seems like as long as the new LUNs eventually had the original name of the old when the instance came back up (and we'd properly presented the LUNs to the cluster), this might work.

I know this isn't ideal.  Ideally, I'd move all these DBs to the existing "real" cluster on the new SAN but changing client connectivity is a challenge right now.

Any gotchas to consider?  There's no failover to test since it's a single-node cluster.


texts: How to get a last DML operation in Oracle 10g-11g

texts:
I can get a number of last INSERT's/UPDATE's/DELETE's from SQL%ROWCOUNT var. Also there exists a way to get a last DML time of each row.

But is there a way to get a last DML operation performed on a table - INSERT/UPDATE/DELETE?


texts:
This might sound like a stupid question but I can't seem to find a better way.
It's a posting system where stores send us info on a daily basis (can be for multiple days). That info is placed in TempTable. For every day found in TempTable it is always for a full day, and the TempTable is good for all stores found in it. So If TempTable has 2 different Stores and 2 different Days such as Day1-Store1 and Day2-Store2, It would mean that nothing happened on Day1 for Store2 and Day2 for Store1.

So, I want to delete any record that BigTable has (for a Specific Day and Store) that is not in the tempTable (for the Days found and Stores Found in the TempTable)
In other words, If Temptable has an entry for Day1-Store1 and Day2-Store2, I need to delete from the Big Table Day1-Store2 and Day2-Store1 and any other entry that is not in the temp Table...

This is what, I'm using right now (it works) but it takes way too long.  I'm looking for a way to minimize the number of joins on the TempTable.
Matching Clustered indexes exist on both BigTable and TempTable (Date/Store/SKU_id)

Delete X
From BigTable X
join (Select Distinct postingDate from TempTable) D on X.PostingDate = D.PostingDate
Join (Select Distinct postingStore from TempTable) S on X.Store = S.PostingStore
Left join TempTable T on X.PostingDate = T.PostingDate
    and X.Store = T.PostingStore and X.SKU_id = T.SKU_id
Where T.Sku_id is null

I'm sure this isn't the best way to do this.
Thanking you all in advance

[MORE INFO]
Since I've noticed that the suggestions given posted are to delete everything in BigTable that is not in TempTable, i thought that I should add the following point:

BigTable contains All days for all stores that have already been posted.
TempTable contains the Posting info of specific Days and Stores found in TempTable.  I want to delete from BigTable the Stores and Days found in TempTable but without deleting the good entries that are already in BigTable.

If Big Table has the following entries:

Day1 Store1 Sku123 ...
Day1 Store2 Sku124 ...
Day1 Store3 Sku123 ...
Day2 Store1 Sku124 ...
Day2 Store2 Sku123 ...
Day2 Store3 Sku123 ...

And TmpTable has the following entries:

Day1 Store1 Sku124 ...
Day2 Store2 Sku123 ...

I would want to delete from BigTable any record for Day1 and Day2 for Store1 and Store2 that is not in TempTable... So I would want to Delete the Following Records from BigTable:

Day1 Store1 Sku123 ...
Day1 Store2 Sku124 ...
Day2 Store1 Sku124 ...


texts: Automating SQL Server Merge Replication Processes

texts: Hi all is there anyway I can automate the entire process of the Merge Replication?


texts: Too much data queued in replication slots

texts:
Periodically we run a batch process that sequentially backups (delete, vacuum and later remote storage in AWS' S3) for a set of about 20.000 tables with the following shell script:

#!/bin/bash
DATA_DIR=/home/admin/backup_avl_historico/data
DB_HOST=localhost
DB_USER=waypoint

#extract table list
logger 'Extracting Table List'
psql -h $DB_HOST -U $DB_USER -c "select table_name from information_schema.tables where table_name like 'avl_historico_%';" -t -o $DATA_DIR/tables.list
array=($(wc -l $DATA_DIR/tables.list))
logger ''$array
total_tables=${array[0]}
logger 'Total tables: '$total_tables

#Get max date
max_date=$(psql -h $DB_HOST -U $DB_USER -t -c "select now() - interval '12 months'")
logger 'Max date: '$max_date

array=($max_date)
#date=${array[0]}
date=${array[0]}-${array[1]}; date=${date:0:16}
logger 'Only date: '$date

#Dump each table
while read table_name
do
logger 'looping...'
    if [ ! -z "$table_name" ]; then
        logger 'Processing table '$table_name
        slots=`psql -U waypoint -h localhost -c "SELECT pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn) AS retained_bytes FROM pg_replication_slots" -qt waypoint`
        while [ $slots -gt 50000000 ]
        do
            sleep 1
            echo Sleeping
              slots=`psql -U waypoint -h localhost -c "SELECT pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn) AS retained_bytes FROM pg_replication_slots" -qt waypoint`
        done
        output=${table_name}_pre_${date}.csv
        psql -h $DB_HOST -U $DB_USER -t -F , -c "COPY (select * from reports.$table_name where fecha < '$max_date') TO STDOUT WITH CSV" -o ${DATA_DIR}/$output
        if [ -f ${DATA_DIR}/$output ];then
            if test -s ${DATA_DIR}/$output
            then
                logger 'Deleting records'
                psql -h $DB_HOST -U $DB_USER -c "delete from reports.$table_name where fecha < '$max_date'"
                                logger 'Gzipping '$output
                                pigz  ${DATA_DIR}/$output
                logger 'Moving to S3'
                aws s3 mv ${DATA_DIR}/$output.gz s3://backup-avl-historico/avl_historico/
                logger 'Vacuuming table'
                psql -h $DB_HOST -U $DB_USER -c "vacuum full analyze reports.$table_name"
            else
                rm ${DATA_DIR}/$output
            fi
        fi
    fi
done < $DATA_DIR/tables.list

We've recently changed improved our replication method to use replication slots,  to allow the DB handle peaks or large queues of data without de-synching between master and slave. When that change was introduced, executing the backup script would fill the slave's drive, thus rendering the process unusable. We later added a validation to wait if the replication queue exceeded 50MB but, that didn't actually help and last night the process again used all space left on the data drive of the replica node.

What could be wrong/misconfigured? What config and/or log files should I provide?


texts: Any known issues upgrading from MySQL 5.1.73 to 5.6.21?

texts:
I am planning my MySQL DB ver5.1.73 running on a CentOS 6.5 machine to MySQL 5.6.21.

I understand the basic procedure for this based on what I have read on StackOverflow, but I was curious if anyone has encountered any specific problems when they have gone the 5.1 to 5.6 route?

One that I have heard of is that certain TIMESTAMP columns might end up with incorrect timestamps. But I am curious if there are any other ones that people have heard of or run into.

To narrow down on the issues I am concerned about, I am only concerned of anything where there might be data loss or consistency issues

Thanks! IS


texts:
I want to rename SQL Server Agent operator, that is change it's name.

Is this a "valid" thing to do? I noticed that trying to change name in SSMS via Operator/Properties dialog doesn't actually do anything. However, clicking on selected operator in Object Explorer also allows you to rename it and produces desired result.

My other concern is existing jobs that were setup to send notifications using old operator's name. I wouldn't want them to fail to send notifications but on the other hand would like to avoid changing all of them manually. I dug around a bit and found that table dbo.sysjobs in msdb uses operator ID, so it seems it shouldn't be a problem. I also tried it on a test job and after renaming operator everything still worked as expected plus job properties displayed new operator name without me having to do anything. So it looks like it should not cause any problems at all but I would like some confirmation on this.


texts: Sort Records in Original Order After UNION ALL

texts:
My question seems to have been worded poorly the first time around, so this is my attempt to explain it more clearly + provide table structure and data below.
Please consider table dt:
Create Table dt
(LogID Int Not Null Identity Primary Key, StartTime DateTime, EndTime DateTime)

Insert Into dt(StartTime,EndTime)
Values
    ('2019-07-31 07:00', '2019-07-31 7:30'),
    ('2019-07-31 06:30', '2019-07-31 07:00'),
    ('2019-07-31 12:00', '2019-07-31 13:15'),
    ('2019-07-31 7:30', '2019-07-31 12:00'),
    ('2019-7-31 05:30', '2019-07-31 06:30')

LogID
StartTime
EndTime

1
2019-07-31 07:00:00.000
2019-07-31 07:30:00.000

2
2019-07-31 06:30:00.000
2019-07-31 07:00:00.000

3
2019-07-31 12:00:00.000
2019-07-31 13:15:00.000

4
2019-07-31 07:30:00.000
2019-07-31 12:00:00.000

5
2019-07-31 05:30:00.000
2019-07-31 06:30:00.000

I'm trying to come up with a query that would return

each record twice, once with StartTime and once with EndTime as EventTime
Order by EventTime and then by StartTime first, EndTime second.

Desired result:

LOGID
EVENTTIME

5
31/07/2019 5:30

5
31/07/2019 6:30

2
31/07/2019 6:30

2
31/07/2019 7:00

1
31/07/2019 7:00

1
31/07/2019 7:30

4
31/07/2019 7:30

4
31/07/2019 12:00

3
31/07/2019 12:00

3
31/07/2019 13:15

I have tried
Select
LogID, StartTime As EventTime, 'Start' As EventType from dt
Union All
Select LogID, EndTime, 'End'from dt
Order By
EventTime ASC, EventType Desc

But the order is not correct
Thank you for your help!


texts: Can I specify a different disk for a specific MySQL table?

texts: I use my home machine as my personal database for my hobby analytics project.  This machine has an SSD for the main drive where MySQL stores its data.  It also has a secondary hard drive that is larger and not an SSD.  I am about to create a table that I fear might be too large to fit on my SSD; is it possible for me to instead store that one table on the larger (but slower) drive?  If it matters, I'm generally using MyISAM tables but could be persuaded to use InnoDB if that would help.


texts: Multiple criteria inside CASE WHEN statement

texts:
Here UserFlag, QtyAvailable and OrderStatus are three different columns and Im trying to create a two additional columns named "BackOrder Status" and "Shipped Status" in another view.
for BackOrder Status the condition would be
UserFlag =”On Order”
,UserFlag =”Sold Out”
, UserFlag =”On Order
, Sold Out”
, QtyAvailable=0
, OrderStatus =“Pending Fulfillment“
then it's a "BackOrder" ELSE "Not a BackOrder"

also, for Shipped Status the condition would be
Orders Shipped: OrderStatus =”Billed”
                , OrderStatus =”Partially Fulfilled”
                ,OrderStatus =”Pending Billing”
                , OrderStatus =”Pending Billing/ Partially Fulfilled”
 then it's "Shipped" ELSE "Not Shipped"

Please let me know if you come up with the query for this.Thanks in advance.
Attempt 1:
CASE
  WHEN Userflag = 'On Order' AND QtyAvailable = '0' THEN 'BackOrder'
  WHEN Userflag = 'Sold Out' AND QtyAvailable = '0' THEN 'BackOrder'
  WHEN Userflag = 'On Order, Sold Out' AND QtyAvailable = '0' THEN 'BackOrder'
  ELSE 'Not a Backorder'
END

Attempt 2:
CASE
  WHEN OrderStatus = 'Billed' THEN 'Order Shipped'
  WHEN OrderStatus = 'Partially Fulfilled' THEN 'Order Shipped'
  WHEN OrderStatus = 'Pending Billing' THEN 'Order Shipped'
  WHEN OrderStatus = 'Pending Billing/Partially Fulfilled' THEN 'Order Shipped'
  ELSE 'Not Shipped'
END


texts: How to restrict in SSMS to allow remote connections only specific IP / Host

texts: I cannot change sql server user. How can I restrict in SSMS to allow remote connections only specific IP / HostAddress.


texts: inconsistent read before/after checkpoint?

texts:
Let's say the transaction in connection 1 tried to modify record A and then commit.Because the commit only flush log record in buffer to disk, it's checkpoint's job to flush all dirty page to disk (mdf).
And there is a connection B tried to read record A after the connection 1's commit.(since A has committed, there is no lock thing here)

so my questions are:

Q1- if connection B read record A before checkpoint, does it mean it will read the old value?

Q2- if connection B read record A after checkpoint, does it mean it will read the new value?

Q3- isn't that weird that sometimes connection B get new value and sometimes it get old value, is the way to fix this problem is A needs to manually trigger a checkpoint?


texts: Should Amazon RDS SQL Server 2017 Web Edition Max Memory settings be different from that of a locally installed instance?

texts:
We have an Amazon RDS SQL Server Web Edition on Windows Server 2012 with 2 processors and 8GB RAM.
Should I configure the Max Memory similar to that of an instance on a local server i.e keeping back 3-4GB for OS, memory pool etc. or does RDS require a different calculation?


texts: What known bugs still exist in SQL Server 2005 after SP4 CU4?

texts:
I'm assuming CU4 will be the last update for SQL 2005.  We intend to stay on 2005 for awhile.  Does anyone know (or perhaps a list is maintained) which bugs STILL exist on 2005?  I assume these would mainly be bugs that have been fixed in 2008 and 2012, but still exist in 2005, even after applying SP4 CU4.

Seems like we should track this if we are staying on 2005...


texts: Select countries and counts of people per country

texts:
I have 2 tables, one is a list of countries (id, country) and another is the users (where the country column is the id of the country in the countries table).

Is it possible to make a single query that returns both the country name and the count of people who has that country id in their country column?

like

country   users
--------  -----
uk        4
portugal  2
usa       10
...


texts: Meaning of FETCH_CURSOR jconnect_implicit_1 in Sybase

texts:
Situation
I am trying to troubleshoot performance when connecting to Sybase ASE Version 15.7 from Sqoop over JDBC.
Though I cannot check things myself it appears to be 10x slower to extract data this way then with another tool (which uses ODBC).
Observation
The DB team has observed the following:

Question:
What is the meaning of FETCH_SIZE jconnect_implicit_1:?
Of course other hints on what may be causing the performance issue are welcome as well.

The DB team believes it means a cursor is made for each row, perhaps because of this link which contains something vaguely similar, but I suspect it means something else as I have not found any reference of Oozie causing this over JDBC (we enforce a larger fetch size in the oozie command).


texts: How to put SQL Server 2012 in single-user mode

texts:
I am trying to restore master DB from a backup and getting below error:

  To restore the master database, the server must be running in
  single-user mode. For information on starting in single-user mode, see
  "How to: Start an Instance of SQL Server (sqlservr.exe)" in Books
  Online.

I thought it would be simple however I have tried three different approach to meet the requirement but none of them worked.

First I used SSMS, right click on master DB > Tasks > Restore > Database.
And check Close existing connections to destination database after selecting the backup.
It returns me with below:

  System.Data.SqlClient.SqlError: Option 'SINGLE_USER' cannot be set in
  database 'master'. (Microsoft.SqlServer.SmoExtended)

So I searched a bit and found an instruction from Microsoft

But after I added the startup parameter with -m and restarted the MSSQL Service.
I still got the first error message while I was trying to restore master by

RESTORE DATABASE master FROM DISK='C:\...\master.bak' WITH REPLACE;

My last attempt was to launch SQL Server service by

net start MSSQLSERVER /m

Then connect it by sqlcmd and send my restore by it, like

sqlcmd -S localhost

And execute the restore cmd in dos command window.

Unfortunately, this also threw the first error message.
It seems all operations above didn't really turn the SQL Server in single user mode for me.

Don't know which part I am doing wrong or what should I do next.


texts: Data warehouse server. How do you calculate RAM/CPU specifications?

texts:
I am trying to write a spec for a data warehouse server for our planned data warehouse upgrade.

As we run virtual servers on VMWare hosts we have the ability to add or remove resources as necessary. In the past we've incrementally added RAM and CPU as required. As our demands have increased we've lobbied for more resources. (primarily disk & RAM).

We ask for more. They give us as little as possible.

However recently whenever we talk about resources we are now criticized for not spec'ing the machine right in the first place, and I am now being told the dev hosts are maxed out, there is no more RAM available.

We're a small Local Government organisation with ~50 regular users of the DW. In normal daily use it runs fine. We get good mdx query performance, and our reports and dashboards are fast. Users are happy.

However our ETL processes run throughout the night, and we're starting to see evidence of memory pressure when processing datamarts simultaneously. Last night SSIS failed with warnings about an "out of memory error".

Our existing DW server is Win 2008 R2 with 4 CPU's and 16Gb of RAM running SQL 2012 Std. I have max server memory set to 12GB, leaving 4GB for OS and services etc. Our existing DW has 3 datamarts/OLAP cubes, and we are developing 2 more.

+----------+----------+---------------+-----------+---------------+
| Datamart | Files GB |  Fact (Rows)  | Fact (Mb) | ETL & Process |
| OLAP cube|          |               |           | Time (hours)  |
+----------+----------+---------------+-----------+---------------+
| PBI      |       3  |  190,000      |  180      |  0.2          |
| FBI      |      30  |  26,100,000   |  10,000   |  1.5          |
| RBI      |     175  |  62,000,000   |  32,000   |  8.3          |
| ABI*     |     100  |  44,050,000   |  21,000   |  4.0          |
| EBI*     |      11  |  100,000,000  |  6,000    |  2.0          |
+----------+----------+---------------+-----------+---------------+
* Planned/Estimated

Our new server is planned to be Win 2012 running SQL 2016 Enterprise. It will run SQL, SSIS, SSRS & SSAS. Storage isn't an issue, but i'm not sure about RAM & CPU.

According to the Fast Track Data Warehouse Reference Guide for SQL Server 2012,  the minimum I should have is 128Gb for a 2 socket machine... which seems a bit excessive. The Hardware and Software Requirements for Installing SQL Server 2016 recommends a minimum of 4Gb of RAM for SQL 2016.
That's quite a difference!

So.. What is a good starting point? 32Gb? 64Gb? How do I justify my starting position (spec) to IT?

Are there any good guides about how to calculate server resources?

Are there any good rules of thumb?

What are the key ingredient/metrics for RAM sizing in a DW context?

The volume of data?
The number of cubes?
The time it takes to do ETL or process a cube?
Peak processing load overnight or performance as viewed by end users during the day?


texts: fn_dblog not showing records while LDF file is large

texts:
I need to find some specific transactions in the transactionlog file, so I use fn_dblog function. But the function is only returning a few rows. Meanwhile the LDF file for my database is a couple of gigabytes at least.

I know the fn_dblog function is only searching the active part of the log:

But what does that mean?
When does a part of the transactionlog becomes inactive?
And how can I search the inactive part?


texts: Creating a unique constraint on a PostGIS 'raster' type column

texts:
I am using the following command to add constraints to one of the raster image in PostGIS-2.1.3 (PostgreSQL-9.1.14).

ALTER TABLE schema1.table1 ADD CONSTRAINT enforce_scalex_rast unique (rast);

But getting the following errors:

ERROR:  data type raster has no default operator class for access method "btree"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

Kindly someone help me to fix this error up. I have no basic idea about the operator classes. Thx.

Zia.


texts: Find all the SP's where a particular column is not updated

texts:
I want to find stored procedures where a particular column is not updated.

As an example

SP1:

BEGIN
      UPDATE YourTable
      SET    Foo = @Foo,
             Bar = @Bar
      WHERE  Id = @Id
  END

SP2:

 BEGIN
      UPDATE YourTable
      SET    Foo = @Foo
      WHERE  Id = @Id
  END

So I want to get all the SP's where Bar is not updated in above example it should return SP2.

Note: one SP can have multiple update statements. Is there any possibility to get the table name of that update statement ?


texts: Oracle log miner permission: which permission authorize user to use logMiner as scheduled JOB?

texts:
I'm trying to config user to run logMiner from scheduled job periodly.

my user called admin
I've grant the admin user the following:

 grant select on v_$database to admin
 grant execute on DBMS_LOGMNR to admin
 grant select on v_$logmnr_contents to admin

but still, when I'm trying to select from v$logmnr_contents I get - ORA-01031 insufficient privileges.

for testing I'm running the following script:

create or replace procedure Test_LogMiner AS
first_scn NUMBER(8);
last_scn NUMBER(8);
log_count NUMBER(8);
BEGIN
  Select current_scn into last_scn from V$DATABASE;
  --Select max(mined_scn) into first_scn from my_table;
  first_scn := last_scn-100;
  DBMS_LOGMNR.START_LOGMNR(startScn=>first_scn, endScn=>last_scn,
     options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.CONTINUOUS_MINE);
  WRITE_LOG('LOG_DIR', 'After START_LOGMNR');
  -- get error on this select - ORA-01031 insufficient privileges--
  select count(*) into log_count from v$logmnr_contents;
  WRITE_LOG('LOG_DIR', 'select count(*)='||log_count);
  DBMS_LOGMNR.END_LOGMNR();
  WRITE_LOG('LOG_DIR', 'After END_LOGMNR');
EXCEPTION WHEN OTHERS THEN
  WRITE_LOG('LOG_DIR', 'Error: '||SQLERRM);
END;

I can ran the same scenario, mean start logMiner and select from v$logmnr_contents, when it not run as procedure, from SQL developer as admin user (means: the create proc. removed and the rest code put in another begin / end)

It also runs when I run the proc from 'sys as dba' user.

My question is: What else should I grant in manner the procedure work?


texts:
This is the scenario.

Have two MySQL servers (S1, S2) on different machines, with a database on each (DB1, DB2).

I have a table (T2) on DB2 that needs to "fetch" rows from another table (T1) on DB1.

I have created a temporary table (base on DB1 and federated on DB2), so when I insert a row on T1 which complies with some requirements I copy to my DB1 temporary table.

That's OK.

With the federated table on DB2 I get the row I need for T2.

I presumed that with a trigger on the federated temporary table of DB2 it's possible to insert that row to T2, but the trigger never fires.

Any suggestions?


texts:
Please help me my mysql is keep crashing again and again and generating following error i am out of any clue what will be the reason for this issue

  WordPress database error Table './databasename/wep_options' is marked as crashed and should be repaired for query SELECT option_value FROM wep_options WHERE option_name = '_otgs_twig_cache_disabled' LIMIT 1 made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/RIVIERA/404.php'), get_header, locate_template, load_template, require_once('/themes/RIVIERA/header.php'), do_action('icl_language_selector'), WP_Hook->do_action, WP_Hook->apply_filters, call_user_func_array, WPML_LS_Actions->callback, WPML_LS_Public_API->render, WPML_LS_Render->render, WPML_LS_Template->get_html, WPML_Templates_Factory->get_view, WPML_Templates_Factory->maybe_init_twig, WPML_Templates_Factory->is_caching_enabled, get_option, referer: http://www.jjweem.com/A/?C-1-348.Html


texts: Store large compressed documents in Postgresql

texts:
Where I work we keep approximately 500,000 image files related to processing for our customer in a file system attached to our Redhat 5.11 production server. We are planning an upgrade to Redhat 7.4 and have already done this for our development database servers. The number of documents is increasing and while we do need to archive older documents, we are talking about moving storage of the documents into a database where we have access to auditing and database level security. If we store the objects in Oracle, they won't be compressed unless we pay for the advanced compression option. We would like top avoid that cost.

Does Postgresql have similar compression for large binary objects? Does it make sense to try using Postgresql for this? I would use Database Heterogeneous Connectivity from Oracle to read and write the large objects. Are there other tools that people have used to store large objects in databases? Thanks


texts: converting mysql columns to bits in a single column

texts:
I have a mySQL table with multiple columns (tinyint) with 0 or 1 values

I want to convert all these values into bits in a single integer column
So a column named column1 will go to MSB another column will set 2nd bit etc...

How do I do it?


texts: String index for a list of values using IN($1,$2): will a btree index work?

texts:
We have a query that needs to compare a value to a list of values in a where clause. The strings are known and discrete. Does IN use the = operator to compare values? Would a b-tree index work for this use case? Example query:
 create table fruit (
   name character varying NOT null
 )

 select * from fruit where name in ('Banana', 'Apple');

Let's assume at the moment we do not want to normalize the data (even though I agree that makes the most sense).


texts: Simple way to list order of referential integrity in Postgres

texts:
Is there a clean, simple, and preferably prepackaged (ready function?) way to list the order of referential integrity in a Postgres database, rather than trying to read the system tables to find that order manually, which is tedious and time consuming?

What I mean is, if the entity hierarchy is

  USER --> ACCOUNT --> TRANSACTION

meaning that a single user can have multiple accounts and that a single account can have multiple transactions, if I want to delete all the three tables without breaking any foreign keys, I would have to do it in the exact following order:

DELETE FROM TRANSACTION;
DELETE FROM ACCOUNT;
DELETE FROM USER;

Now this is a very simple and obvious example. But real life databases have a lot more tables and foreign keys. If I wanted to delete every table down to 0 rows, is there a quick way to obtain the exact order of referential integrity to do it in?


texts: MySQL Foreign Key Not Cascading On Delete

texts:
I have 2 tables, coupon:

CREATE TABLE `coupon` (
  `coupon_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `code` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `type` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `discount` decimal(15,4) NOT NULL,
  `logged` tinyint(1) NOT NULL,
  `shipping` tinyint(1) NOT NULL,
  `total` decimal(15,4) NOT NULL,
  `date_start` date NOT NULL DEFAULT '0000-00-00',
  `date_end` date NOT NULL DEFAULT '0000-00-00',
  `uses_total` int(11) NOT NULL,
  `uses_customer` varchar(11) COLLATE utf8_unicode_ci NOT NULL,
  `status` tinyint(1) NOT NULL,
  `date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`coupon_id`),
  UNIQUE KEY `code_u_idx` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

and offers:

CREATE TABLE `offers` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `email` varchar(96) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `telephone` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `product_id` int(11) unsigned NOT NULL,
  `code` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL,
  `price` decimal(15,4) unsigned NOT NULL DEFAULT '0.0000',
  `contacted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `approved` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `ip_address` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `notes` text COLLATE utf8_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `offers_code_foreign` (`code`),
  CONSTRAINT `offers_code_foreign` FOREIGN KEY (`code`)
  REFERENCES `coupon` (`code`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The workflow for these tables is as follows:

Customer creates an offer on a product from the front of the website.
Admin then calls the customer, verifies their info, generates a coupon, and gives it to the customer over the phone and updates the offer entry.

In other words the coupon is not generated with the offer, it's generated after and the offer entry updated.

This seems like it should work fine, but when I delete an offer, the coupon is not getting deleted via the CASCADE and I can't figure out why.

Am I missing something in my table set up that would make MySQL ignore the constraint?

Thanks


texts: Connect to SQL Server without specifying port

texts:
I am trying to use what appears to be a rather badly written application which connects to an SQL Server database which I specify.

I cannot seem to connect to the SQL Server instance I wish to use without specifying the port with a comma e.g. DBServer\DBInstance,<port_number>

Now, the problem that this causes, and why I think the application is badly written, is that for some reason the application then thinks that the port number is the name of the database, and the server\instance name is DBServer\DBInstance. I believe it is not expecting to have to deal with a port number like this.

Now, I have used SQL Server instances before which did not require the port number to be specified. I know this must be some setting under the TCP/IP settings in SQL Server Configuration manager, and I have tried setting the port to the default 1433 as well as turning on dynamic ports, but neither of these have worked.

I am still fairly new to SQL Server, if anybody could point me in the right direction it would be greatly appreciated.

Edit

Having solved the problem as per Ste Bov's answer, I also now know why the application could not handle the port being specified. Internally, the application was storing its data in a temporary csv file and didn't bother to check input for commas or enclose it in quotes, so when it came to the port in the csv it thought it was the next field which was supposed to be the Database Name.

So yes, a badly written application indeed!


texts: Just how secure is the data I put on SQL Azure?

texts:
My wife has been asking me to create a web application for her business and from what I've read using SQL Azure would be perfect.  Except for a couple of small (really really big) gotchas.

She works in the financial industry and is HEAVILY regulated.
The data she works with is very sensitive and has to have a minimum risk of exposure.

So my question is, if I put her companies information on SQL Azure how secure will it be?  I'm not asking about general SQL Server/Azure security.  I'm talking about the fact that this is a shared space.  How possible is it to hack into the data, bypassing the security that I put into place?


texts: Reasonable way to predict query performance over time

texts:
I have a SQL Server 2012 database that I am using to save data from processed files. We read data from a folder, process it with python and save the results into the database.

One of the first things we do in our ETL process is to check if the file has already been processed. We simply do a:

SELECT id FROM table1 WHERE basename = <basename>

If there is a result we skip the file, if there is no result we process the file. Right now this query takes ~250ms with ~5m records. We have a nonclustered index on the basename column already.

We will see about 100-200k records added per month. We get the files in batches. So we might see 2k files, and then 2 hours later another 2k files. Some days we will get 10k files, other days we might only get 4k files.

Keeping all other variables the same is there a rule of thumb for projecting when we might run into performance (queries taking longer than 1s) issues with this query other than inserting 15-20 million records into the table and seeing what happens?

Table DDL:

CREATE TABLE [dbo].[raw_records](
[id] [int] IDENTITY(1,1) NOT NULL,
[basename] [varchar](512) NULL,
[filename] [varchar](1024) NULL,
[file_size] [int] NULL,
[machine] [varchar](10) NULL,
[insert_timestamp] [datetime] NULL,
[raw_xml] [xml] NULL,
[process_status] [varchar](2048) NULL,

PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Index:

CREATE NONCLUSTERED INDEX [basename_index] ON [dbo].[raw_records]
(
    [basename] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

This table was created long before I started working on it, so I'm assuming someone just made filename 1024 maximum length to hold "enough". Certainly changeable.

The files that are produced have a timestamp and unique information within the filename itself (e.g. system1_metadata_timestamp.xml) one "system" couldn't produce (or should never) produce a file with the same timestamp.

select max(len(basename)), max(len(filename)) from dbo.raw_records;

returns: basename - 143, filename - 168. Probably a good thing to change down to a max of 260.

process_status probably doesn't need to be that long either, but I feel that one is reasonable to guess at, because the column is there to hold error messages from the processing phase. I ran a query on it and had a max of 600 characters. We don't typically query on that column though. It is just more informational for debugging.

I'm going through the application cleaning up stuff such as that. In certain places I can't get away from it but in others can't do much about it unfortunately (e.g. need to actually retrieve the XML column to extract data from it). This question simply stemmed from seeing the performance of the query in question and not wanting it to get away from me. It is the first thing that gets done for every file so if that doesn't work, nothing else will either.


texts: One database or multiple referencing one?

texts:
Database design: one database or multiple databases, which is best?

We have a database which has about a 100 or so tables, accessed by about five different applications. Five different applications have their own set of tables but also need to access about 20 master tables (used by all our systems: users, accounts, contacts, shops, etc). Now we are going to have another 15 or so applications with their own set of tables but also again need access to get information from the master tables. So before we get set up what do you think is the best schema and database set up. i.e. one database with all applications including the master ones. Each application has its own database with the master records staying in master?

Anyone's thoughts here would be much appreciated. I think I am leaning towards separate databases so they can be managed better, and performance should be better (maybe not?).

If I go with separate are their any implications: setting up references wont be possible, performance joining databases for selects, updates, asp.net needs 2 connections strings (is that even possible with say entity framework database first or LINQ DBML).


texts:
I'm after suggestions on how to increase mysql performance on my server, any help would be greatly appreciated.

CPU - 4 core
RAM - 16GB
Databases - 14 Innodb databases with around 20 tables in each (no other databases).
The server is also used as an application and web server.

My my.cnf is as follows:-

[mysql]

# CLIENT #
port                            = 3306
socket                          = /var/run/mysqld/mysqld.sock

[mysqld_safe]

# SAFE #
socket                          = /var/run/mysqld/mysqld.sock
nice                            = 0

[mysqld]

# GENERAL #
user                            = mysql
default-storage-engine          = InnoDB
socket                          = /var/run/mysqld/mysqld.sock
pid-file                        = /var/run/mysqld/mysqld.pid
lc-messages-dir                 = /usr/share/mysql
tmpdir                          = /tmp
basedir                         = /usr
bind-address                    = 127.0.0.1
skip-external-locking

# MyISAM #
key_buffer_size                 = 32M
myisam-recover-options          = FORCE,BACKUP

# SAFETY #
max_allowed_packet              = 16M
max_connect_errors              = 1000000

# DATA STORAGE #
datadir                         = /var/lib/mysql/

# CACHES AND LIMITS #
tmp_table_size                  = 32M
max_heap_table_size             = 32M
query_cache_limit               = 1M
query_cache_size                = 200M
max_connections                 = 500
thread_cache_size               = 50
open_files_limit                = 65535
table_definition_cache          = 1024
table_open_cache                = 2048

# INNODB #
innodb_flush_method             = O_DIRECT
innodb_log_files_in_group       = 2
innodb_log_file_size            = 256M
innodb_flush_log_at_trx_commit  = 1
innodb_file_per_table           = 1
innodb_buffer_pool_size         = 1G

# LOGGING #
log_error                       = /var/log/mysql/error.log

[mysqldump]

max_allowed_packet              = 16M
quick
quote-names

[isamchk]

key_buffer                      = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/


texts: Unable to create a self signed Certificate for SQL Server 2017(14.x.xxxx)

texts:
Using SQL Server 2017 Developer Edition

The command I'm using is:

  New-SelfSignedCertificate -CertStoreLocation Cert:\LocalMachine\My -DnsName host.doman.com -KeySpec KeyExchange -FriendlyName SQLCert

The error message is:

  New-SelfSignedCertificate : A parameter cannot be found that matches parameter name 'KeySpec'.

My powerShell version is:

PS C:\Windows\system32> $PSversionTable

Name                           Value
----                           -----
PSVersion                      4.0
WSManStackVersion              3.0
SerializationVersion           1.1.0.1
CLRVersion                     4.0.30319.42000
BuildVersion                   6.3.9600.18773
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0}
PSRemotingProtocolVersion      2.2


texts: Query where there are only two rows with specific data

texts:
I have a table with room which has a data of who are in it. Each room has have 0 to any number of people and each person can be in multiple rooms simultaneously. (simplified below)

id      name

1       John
1       Peter
1       Jill
2       John    <--- room # I want
2       Jill    <--- room # I want
3       Someoneelse
...

If I want to look for a room where there is John and Jill and no one else, what would be an elegant & efficient way to select the room number?

Something like this works...

SELECT r1.id
FROM  `room` r1,  `room` r2
WHERE
    r1.name != r2.name
    AND r1.id = r2.id
    AND r1.name = 'John'
    AND r2.name = 'Jill'
    AND r1.id
    IN (
        SELECT id
        FROM user_conversation
        GROUP BY id
        HAVING COUNT( * ) = 2
    )

But I feel like there would be a much more efficient & elegant way to get this.


texts: Transaction log growing out of control on random times

texts:
We have a problem with one of our databases. At random times something causes our transaction log to swell up to 20x its original size (it basically fills up the whole HD, so 20x is not accurate since it might grow more if there was space left on disk)

We do full backup once a week and differentials every day. Our database is using SIMPLE recovery mode.
I am trying to go through the transaction log of the differential and full backup files using fn_dblog and fn_dump_dblog, yet for whatever reason, in both cases it is showing me [Begin Time] and [End Time] for a very small amount of time. For differential it is showing records for a time span of only around 1/2 a minute. For the full backup, it is showing records within a span of 5-10 minutes.

Here is the query I was running, hoping from here i can find long running transaction logs from the past:

SELECT
    [Current LSN], a.[Transaction ID], [Transaction Name], [Operation], [Begin Time], [End Time], SUSER_SNAME([TRANSACTION SID]) as LoginName
FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, 'DB_backup_path',
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
    DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) as a
WHERE
    Operation in ('LOP_BEGIN_XACT', 'LOP_COMMIT_XACT')
order by
    [Transaction ID]

Here is the query I was running to see long transaction which are still in activate transaction log:

SELECT
    [Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time], [End Time], SUSER_SNAME([TRANSACTION SID]) as LoginName
FROM
    fn_dblog(NULL, NULL)
WHERE [Operation] in ('LOP_BEGIN_XACT', 'LOP_COMMIT_XACT')

In both cases, why am I getting data only for a small span of time?

Also, on more generic level, is this the best way to investigate why on random times the transaction log grows out of control. We looked at all the reoccurring sql jobs we are running and there is nothing extra ordinary there. When we kick them off, they run for at most 5 minutes. Even running multiple jobs at the same time causes no bottlenecks. Our application does not use any transactions when it talks to DB, so we are completely confused.

Any ideas?


texts: How to prevent concurrency issues in a ssis package?

texts:
Have a SQL Server 2017 Server.

Have several SQL Server jobs that call a single SSIS package using the SYNCHRONIZED flag.

In the package there is a data flow that runs a stored proc (please see below) that returns a Data Source that checks to see if there are any labels to be printed.  If there are any labels to be printed then it selects those labels into a table variable, selects the labels, and updates the labels IsProcessed flag to say that they have been processed.  The problem is that if more than one job calls the package then it prints out more than one label even thought there is only one label to be processed.  This situation does not consistently occur.  So, my question is:  How do I prevent this from happening?

CREATE PROC [dbo].[spCreateOutput_LabelDataSet]

AS

BEGIN
    BEGIN TRY
        BEGIN TRAN

        DECLARE @Intermediate TABLE(
            PrintLabelsID INT NULL,
            Labels XML NULL
        )

        INSERT INTO @Intermediate(PrintLabelsID, Labels)
        SELECT
            pl.PrintLabelsID,
            pl.Label
        FROM
            Staging.dbo.PrintLabels pl
        WHERE
            pl.IsProcessed = 0

        SELECT
            CAST(Labels AS VARCHAR(MAX)) 'Labels'
        FROM
            @Intermediate i

        UPDATE pl
        SET IsProcessed = 1,
            ProcessedDate = GETDATE()
        FROM
            Staging.dbo.PrintLabels pl
            INNER JOIN @Intermediate i
                ON i.PrintLabelsID = pl.PrintLabelsID

        COMMIT TRAN
    END TRY
    -- Error Logging
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            BEGIN
                ROLLBACK TRANSACTION;
                DECLARE @ErrorNumber INT = ERROR_NUMBER();
                DECLARE @ErrorLine INT = ERROR_LINE();
                DECLARE @ErrorMessage NVARCHAR(4000) = LEFT(ERROR_MESSAGE(), 4000);
                DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
                DECLARE @ErrorState INT = ERROR_STATE();
                DECLARE @Error_Procedure VARCHAR(200) = LEFT(ERROR_PROCEDURE(), 200);

                EXEC Logs.dbo.spTrackErrors @ErrorNumber, @ErrorSeverity, @ErrorState, @Error_Procedure, @ErrorLine, @ErrorMessage;
                THROW;
            END;
    END CATCH

END


texts: The benefits of ALGORITHM=INPLACE in ALTER TABLE queries

texts:
For some reason I can't find any information on this.
A colleague insists me to use that on an Alter Table query but can't really say why.
It is a small table concerning the application's basic configurations with a very small amount of rows.
What are the benefits of ALGORITHM=INPLACE in a query that adds one MEDIUMINT NOT NULL type of column to a table which will have 10 rows at most?
The database is MySql.


texts:
I came across the term Transaction Path Analysis which I am not familiar with. A Google search resulted in only a few results, most of which seem to assume everyone knows what it means. From these my understanding is that it is about tracking what columns are used by queries to access data, in preparation for deciding indexes. This seems like common sense, but I'm surprised I can't find more specifics on it.

Is there a concise definition for transaction path analysis, a process for completing one, or any alternative more commonly used phrases that mean the same thing?


texts: How can I upgrade PostgreSQL while using PgQ queue system?

texts:
I'm using PostgreSQL 9.3 and PgQ 3.2.6 for about two years and now it's time to upgrade. I found out that upgrading PostgreSQL is fairy simple. Just install new Postgres version and then:

sudo pg_dropcluster 9.5 main --stop
sudo pg_upgradecluster 9.5 main
sudo pg_dropcluster 9.5 main

But I wonder, how the fact that I have working PgQ on top of my DB server affects this process? It looks like there is no related information in the Internet.


texts: How to structure db where main records are large

texts:
I was wondering if I could ask for some quick opinions.
I am prototyping an app which seems simple but I can’t readily arrive at an architecture that feels right.
We have data that is simple and fits well into standard sql format, eg users, organisations, projects, reports.
However, the ‘reports’ are the problem. They can typically be between 1000 and 100,000 'line items'. Each item with around 10 fields. A bit like a large spreadsheet, and in fact this is where they originate usually.
A user (lets say 100 of them) generates say 10 projects a year, and each project has say 10 reports, and each report has say 10000 items (or rows) .. then if there was one massive table quickly growing to millions of rows, with each row ‘belonging’ to a report etc .. well this doesn’t feel right.
To make this more tricky, the reports themselves can almost be considered 'mini-databases' in themselves, in that although the meat of the data is thousands of 'line items' (with uniform column requirements), they are structured into 'zones', 'sections', 'subsections', and there is also an 'overview/meta' section as well.
In theory, I could normalize this right from 'user' to 'item' something like users->projects->reports->zones->sections->subsections->items ... but again this just doesn't feel right to me; what the DB has to do to simply go and get a report seems overkill. Also all of this report info would then be all mashed together for all the different user accounts.
Every report query would have to go a collect thousands of rows out of millions, and assemble the client json from different tables, and there would be no other real benefit from being able to query the table in this way. All the reports are self-contained really.
As such, I am wondering whether this a case for a nosql route, like mongo or documentDB, where I can just throw thousands of lines of json in a Report Collection or similar. This would have further implication though, as the backend frameworks I like to use don’t play all that nice with nosql, and we lose a bit of the standard easy relational schema that would still seem to fit the other tables of the app (e.g. user accounts, RBAC, users belonging to organisations, etc).
.. but there would be a similar question with a json store .. is it better to maintain a 'tree' structure, and keep all the line items within their sections, and sections within their zones etc, or normalize out so there is one big load of lineitems, that have references to their sections within them, etc.
I’ve messed around with using MySQL for everything, and using the json field type to store the reports as one big json, but I don’t know whether this is pushing the intention of the field-type a little, and it’s hard to query into.
A consideration here is that on the frontend, users will update one (or a few) row(s) at a time, and I need to be able to handle that. In this way, I suppose there's a similarity with google sheets, or airTables or something. Do these big apps tend to use rdbms?
I notice there seem to be more and more database options appearing all the time now, eg fauna supabase etc .. makes it even more tricky to choose the right solution.
Thanks in advance for any tips


texts:
We've got a complicated stored procedure.  The biggest pain point of the procedure is a join on a complicated view--it has unions and subqueries.  I can't add indexes to it.

So, I want to cheat.

By creating a procedure that copies the view to an actual table with indexes.  The procedure runs quickly, less than a second.  But the effect for our stored procedure is amazing.  50 executions used to take 12 seconds.  Now it takes only 4.  I'm thinking of implementing this method and then recreating the "compiled" table as needed, but never more than every 2 or 3 seconds.

My question is this.  How bad is this practice?  Are there better ways of handling such a complicated view?


texts: Merge multiple rows into fewest number of rows of distinct values

texts:
In SQL Server does anyone know of a nice way to merge/flatten multiple rows of data into the fewest possible rows containing only the distinct non null values.

I.e.

A dataset like this:

As this:

If it helps, the before dataset is a pivoted line listing but without the aggregate.  I can't aggregate it during the pivot as I want to keep each of the distinct values and not take the MAX or MIN.

The only way I can think of doing it involves splitting the data up and joining it all back together which wont be very efficient.


texts: (Partially) clone a huge DB for DEV purposes

texts:
I want to setup a dev and test environment for website that has a huge MySQL database. Since this DB is so big, it is quite inconvenient to clone it 1-on-1, because it simply takes too long and uses too much storage.

My ideas was to only dump / copy certain records, matching some SQL. For example, only the last 1000 users and last 1000 products in the DB, assuming we only need this much data for testing and development.

My questions are:

How to best go about this, keeping in mind the DB is live at the time of the dumping (referential integrity), and keep it from going offline
The data is complex and has many joins, making insert into ... select ... statements a bit complex, but still possible.

My ideas was to use mysqldump to set up only the structure (no data), then use a long list of insert into ... select ... statements to fill the tables 1 by 1.

Only issues I am uncertain of is the referential integrity and processing the tables in the right order, and maybe there is a better way?

The DEV environment is my personal laptop, so not a server with huge disks.


texts: How to order by COUNT(*) of unique entries in GROUP BY?

texts:
I search through a tag_map as
SELECT ArticleID,COUNT(*) AS C FROM WordMap
WHERE WordID IN(
SELECT WordID FROM Words WHERE Word IN ('word1', 'word2')
)
GROUP BY ArticleID
ORDER BY C DESC

I want to get article ids in which all words (here word1 and word2) appear first.
The above query returns in order of the number (e.g., if word1 appears 100 times).
NOTE: WordMap table is huge, and this is the reason I went for sub-query rather than JOIN.


texts: Should I use a separate table to store image file names?

texts:
I am learning about database design and I'm writing a Java GUI program with a back-end database.  The main table in my database stores a different product in each row.  I have some columns such as: product_id (PK), price, stock_quantity, etc.  I also have eight columns that store the names of the file names for that product's images: img_file_1, img_file_2 ... img_file_8.  The Java program uses those file names to find the images to display on the screen for that product.

Is this poor design?  Should I be storing these file names in their own table, and adding a foreign key to that table in my main table?

The program works fine as is, but I want to make sure I am learning good habits.


texts: MS SQL Server - Checkpoint process stuck in SLEEP_BPOOL_FLUSH

texts:
We seem to have a problem that came out of nowhere. The checkpoint process is stuck in SLEEP_BPOOL_FLUSH state, and not actually reducing the usage in the log file.

This never seemed to happen before.

I understand that SLEEP_BPOOL_FLUSH is a normal wait for the CHECKPOINT process when the disk subsystem is busy. Actually, we've always had a very busy disk subsystem so I don't understand why this has become an issue.

(If you manually run a checkpoint command, same results...your user process waits in SLEEP_BPOOL_FLUSH).

Is there ANYTHING we can do to push the checkpoint to occur? Can the parameters for the checkpoint be tweaked so this does not occur?

(SQL Server 2005, RAID subsystem, Simple Recovery Model)


texts: postgres - SUM Time of this Interval and Group by 30 and 30 minutes


texts: Ordering rows based on cross reference between columns

texts:
I have a table like this:

Lat1  Lng1  Lat2  Lng2
----  ----  ----  ----
1     1     2     2
2     2     5     5
3     3     4     4
4     4     3     3
5     5     4     4

Is it possible to return the rows in the following order?

Lat1  Lng1  Lat2  Lng2
----  ----  ----  ----
1     1     2     2
2     2     5     5
5     5     4     4
4     4     3     3
3     3     4     4

There is a unique rowid column.

The (Lat1,Lng1) and (Lat2,Lng2) pairs will always be unique.

lat1 and long1 of row n+1 must be the same as lat2 and long2 of row n.


texts: Getting data from Postgres through Linked server of SQL

texts:
I am trying to get the data from PostgreSql db through linked server of the SQL Server. But getting the following error on running this query
select * from openquery(SALESFORCE,'select closeddate
from case
where closeddate > convert(datetime2, "2020-01-01 00:00:00.0000000")')
OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE" returned message "Error: MALFORMED_QUERY:
where closeddate > convert(datetime2, "2020-01-01
^
ERROR at Row:3:Column:28
Bind variables only allowed in Apex code".
Msg 7320, Level 16, State 2, Line 21
Cannot execute the query "select closeddate
from case1
where closeddate > convert(datetime2, "2020-01-01 00:00:00.0000000")" against OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE".
Completion time: 2021-04-22T03:00:25.5943467-04:00
Where I am getting wrong


texts: Duplicate rows with primary key constraint in PostgreSQL 10.6

texts:
I have a table that's had CONSTRAINT table_pkey PRIMARY KEY (id) since setup.

However, I recently found that I have around 100 duplicate rows (I ran select count(*) from (select count(*) from table group by id having count(*) > 1) as t1 as per this old stack post. However, I'm running Postgres 10.6, which should be far after that bug.
Any ideas?


texts:
Team,

We have been loading shapefiles into DB2 using db2se import utilities.

I have been trying to find help to load Raster files and i wasnt able to find any help on this. it would be really helpful if I can get a guide on loading raster files into DB2.


texts:
I need to login to a database. I have good credentials, but when I was trying to login with SQL Developer, I got this error message:

  ORA-28001: the password has expired

Then I tried to login with SQL*Plus, which worked fine, I connected successfully and I was asked to change my password.

So I typed in a new password and then retyped it. Then I got other message:

  ORA-01017: invalid username/password; logon failed

Can you help me how can I change this password? I don't have administrative permissions.


texts: How to get multiple max values for 1 column form multiple tables

texts:
I am using sqlplus and i need to achieve this result, where stipend is the max stipend for each faculty
There are 3 tables, student ( with name and surname), faculty ( with faculty name) and money (with the stipends), connections are faculty to student and student to money
This is my current code, which onyly returns one main maximums from one faculty
SELECT ROW_NUMBER() OVER (ORDER BY faculty_name DESC) AS "Nr.",
 faculty.faculty_name,
 student.surname,
student.name,
money.stipend AS "STIPEND"
FROM faculty INNER JOIN student ON faculty.id_faculty = student.faculty_id INNER JOIN money ON student.id_student = money.student_id
GROUP By money.stipend, faculty.faculty_name, student.surname, student.name
having max(money.stipend) =
(
select max(stipend)
FROM faculty INNER JOIN student ON faculty.id_faculty = student.faculty_id INNER JOIN money ON student.id_student = money.student_id
);

how would i get this end result


texts: Why can read only users create objects inside postgres database?

texts:
I've just started working with Postgres and I noticed the following.

If I create a user they automatically have the ability to do whatever they want inside the database. User was created with:

CREATE ROLE read_access
    NOLOGIN NOSUPERUSER INHERIT
    NOCREATEDB NOCREATEROLE NOREPLICATION;

So I tried:

REVOKE ALL ON DATABASE dbname FROM USER username ;
GRANT CONNECT ON DATABASE dbname TO username ;

I expected the user should be able to connect to the db with no further privileges on the underlying objects. But this user is still able to create objects inside the database. What am I missing here?


texts: Are implicit transactions per batch or per session in SQL Server?

texts: Imagine I am running multiple batches through management studio, separated by the GO command. I'd like to know how implicit transactions will behave - is the transaction committed on a per batch basis or once for the entire execution.


texts: In postgresql binary replication, indexes are replicated or not? And if i dont like it?

texts:
I want to know if indexes are replicated in a binary replication in postgresql.

And if it is, how can i disable this behavior?

So if slave becomes master i can start to index it using reindex?


texts: ASMCA(ASM Configuration Assistant) GUI is not rendering properly

texts:
Hi Gurus,

While running ASMCA utility on OEL 7.5, Oracle is not able to render the GUI of the tool. I clicked on exit button and next screen comes as a vertical line (Screen attached above).No option there to expand the window.

Have ever anyone come across this type of issue? Please let me know if you have solution for this.

Any help is appreciated.

Thanks
Prabhakar


texts:
Hi I'm new to database design but am trying to make sure I get things right first time so as not to pick up bad habits.
My question is I have a set of people for which I want to record needs mapped to importance.
Needs should be taken from a set to avoid duplicates creeping in. Each person will take a subset of these and grade their importance.
i.e.
needs =[tea, biscuits, toilet facilities, internet access]
People = [Phil, Bryan]

Phil needs tea and biscuits, tea with an importance of 10 biscuits with an importance of 3.

Bryan needs internet access and biscuits with importance of 4 and 7 respectively.

How would you suggest organizing the tables to implement this.
Thanks


texts:
Possible Duplicate:
  SQL services wont start due to incorrect drive letter after windows restore

SQL services wont start due to incorrect drive letter after windows restore

Had a server crash, was successfully able to reinstall windows and restore the server SQL and all the necessary services were already installed after restore. all the sql databases were unharmed they were on a different partion.

Only problem the windows drives letters are wrong. So the services are pointed to the incorrect drive letter. I cant change the services in regedit or change the drive letter in disk management. Is there a way to do a repair in SQL add remove programs to associate's the necessary services with the correct drive letter?

Here's a example of the services
Path to executable
I:\Program Files\Microsoft SQL Server\MSSQL.1\

only problem the actual drive letter is E:

Services that wont start
SQL SERVER
SQL SERVER AGENT
SQL Server Analysis
SQL Server Analysis Services
SQL Server Integration Services
SQL Server Reporting Services

Solutions I have tried
Changing drive letter within disk management
Tried Substitute command


texts: Log Sql Server Transactions (Queries + username)

texts:
I need to see which user has made what query with a lookback of ~90(?) days.

I have activated change tracking on the database with 90 days.

I have found several solutions:

SELECT
    deqs.*,
    dest.*,
    deqs.last_execution_time AS [Time],
    dest.TEXT AS [Query]
FROM
    sys.dm_exec_query_stats AS deqs
CROSS APPLY
    sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY
    deqs.last_execution_time DESC

But this query doesn't give me any information about the user who made the query.

Then I got this query

SELECT
    *
FROM
    FN_TRACE_GETTABLE('E:\MSSQLServer\MSSQL12.DBNAME\MSSQL\Log\log_29.trc', default)
WHERE
    TextData IS NOT NULL
ORDER BY
    TransactionID DESC

This give me the username but no statements about DELETE/UPDATE etc.

I read about the SQL Profiler but I can't copy/read the _log.mdf-files because it is always in use.

I need a tip what s the right way to do this.. :/


texts:
Can I know why am I getting weird output in my SQL statement whereby my table has two dates of the month May but it only appears for one month, the same things I test for dates range it is working.

    select distinct To_Char (attendance_date,'dd/MM/yyyy')
    from DIT_2010MAR_CIT4114A_FYP1_NO
    where attendance_Date between To_Date ('05', 'MM') and
    To_Date ('05', 'MM');


texts: SQL Server - How to protect against SQL Injection when dealing with a Dynamic WHERE clause passed to a Stored Procedure

texts:
Given the following stored procedure, can I protect against SQL injection in the supplied additional WHERE condition?

CREATE PROCEDURE [dbo].[ProtectDynamicWhereClause]
   (@TableName varchar(50),
    @OldestRecordDate varchar(15),
    @WhereCondition varchar(250) = NULL)
AS
BEGIN
    -- Protect the table name from SQL Injection.
    DECLARE @TableNameClean varchar(150) = QUOTENAME(@TableName)

    DECLARE @sql nvarchar(4000)
    SET @sql =
            N'
            SELECT * FROM ' + 'dbo.' + @TableNameClean
            + ' WHERE EntryAge > DATEDIFF(year, @OldestRecordDate, GETDATE()) '

    IF (@WhereCondition IS NOT NULL)
    BEGIN
        SET @sql = @sql + ' AND ' + @WhereCondition
    END

    EXEC  sp_executesql @sql, N'@TableNameClean varchar(50), @OldestRecordDate varchar(15)',
                                @TableNameClean = @TableNameClean, @OldestRecordDate = @OldestRecordDate
END

EXEC ProtectDynamicWhereClause @TableName='CustomerTbl', @OldestRecordDate = '2012-01-01', @WhereCondition = 'CustomerName LIKE ''%Smith%'''
EXEC ProtectDynamicWhereClause @TableName='ProductTbl', @OldestRecordDate = '2010-01-01', @WhereCondition = 'ProductId IN (123, 345, 567)'

Could you please tell me how to protect against SQL injection in this statement when the @WhereCondition is actually passed-into the stored procedure as a parameter as shown?

SET @sql = @sql + ' AND ' + @WhereCondition

Thank you.


texts: How do I force a JOIN to use a specific index in MySQL?

texts:
I have a query that JOINs 2 tables; lineitem and part,

select
        sum(l_extendedprice* (1 - l_discount)) as revenue
from
        lineitem force index for join (l_pk),
        part
where
        (
                p_partkey = l_partkey
                and p_brand = 'Brand#12'
                and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
                and l_quantity >= 1 and l_quantity <= 1 + 10
                and p_size between 1 and 5
                and l_shipmode in ('AIR', 'AIR REG')
                and l_shipinstruct = 'DELIVER IN PERSON'
        )
        or
        (
                p_partkey = l_partkey
                and p_brand = 'Brand#23'
                and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
                and l_quantity >= 10 and l_quantity <= 10 + 10
                and p_size between 1 and 10
                and l_shipmode in ('AIR', 'AIR REG')
                and l_shipinstruct = 'DELIVER IN PERSON'
        )
        or
        (
                p_partkey = l_partkey
                and p_brand = 'Brand#34'
                and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
                and l_quantity >= 20 and l_quantity <= 20 + 10
                and p_size between 1 and 15
                and l_shipmode in ('AIR', 'AIR REG')
                and l_shipinstruct = 'DELIVER IN PERSON'
        );

I have an index on lineitem and want the query to use this index for the join p_partkey = l_partkey

create index l_pk on tpch.lineitem(l_partkey);

MySQL explain shows:

| id | select_type | table    | type   | possible_keys | key     | key_len | ref                     | rows    | Extra       |
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+---------+-------------+
|  1 | SIMPLE      | lineitem | ALL    | l_pk          | NULL    | NULL    | NULL                    | 5982534 | Using where |
|  1 | SIMPLE      | part     | eq_ref | PRIMARY       | PRIMARY | 4       | tpch.lineitem.L_PARTKEY |       1 | Using where |
+----+-------------+----------+--------+---------------+---------+---------+-------------------------+---------+-------------+

Why is the index l_pk not used?


texts: MySQL multiple table joins and multiple data levels - best practice

texts:
I have multiple tables i wish to join up to produce one big query with specific columns summed.
I was having a problem getting the correct summed columns in the final query
(i believe this was because i was due to data being joined to many lines and summing after the join)

is the correct way to handle this type of problem to join on select statements?
This is the way i achieved it but wasnt sure if this is the correct method?

ie

select
    t1.id_a,
    sel_t2.sum_mc
from xyz as t1
    left join
        (select id_a, sum(occ) as sum_mc
        from t2
        group by id_a) as sel_t2
    on t1.id_fh = sel_t2.id_a
group by t1.id_a;


texts: MySQL NOW() function - whose clock is used?

texts: For the mysql NOW() function, whose system time is used for time calculations? The host of the database or the client executing the statement?


texts:
I'm trying to deploy a tabular model to a server using the "Analysis Services Deployment Wizard".

When attempting to deploy,I get the below error.

  The JSON DDL request failed with the following error: Failed to execute XMLA. Error returned: 'The column 'Date Offset' in table 'Date' has invalid bindings specified.

The column in question uses the below calculation, which was found here

INT([Date] - TODAY())

What should I look for in order to resolve this error?


texts: How to copy a schema with another name but with same data?

texts:
I want to copy all the objects of Scott schema to another schema.

I can not understand what the code should be. Like bellow one?

create user scott1 identified by tiger1;


texts: Giving file system access to the SQLSERVERAGENT virtual account

texts:
I'm trying to grant the NT Service\SQLSERVERAGENT Windows account file system access, based on the marked answer in this question. I believe it's a virtual service account, and it does not show up in the control panel -> user accounts area.

How do I give this service account access to the file system? Specifically, on Windows 7.

I've read several approaches and none seem to be an option for me. I tried a PowerShell approach, but the AD commands were not valid commands. I even downloaded and installed the required windows patch for those commands. I've also read that I should be able to do this via the SQL Server Configuration Manager or the Management Studio. I can't seem to figure out where to adjust these permissions, though.

The SQL Server Agent process fails to start, with the following error message:

Login failed for user 'NT SERVICE\SQLSERVERAGENT'.
Reason: Failed to open the explicitly specified database 'msdb'.
[CLIENT: <local machine>]

Based on my Googling, it has to do with this account's permissions.

I'm not sure if the database is corrupt, but it says the database is in a Normal status. The SQL Server Agent login is part of the sysadmin and public groups.


texts: Set column values based on condition statement

texts:
I want to add values to a new column based on: if board_stage is less than alight_stage then service_direction equals 1, to indicate Inbound; if not, then service_direction equals 0, indicating Outbound.
The code I have tried is below. The main issue is that it keeps returning syntax errors before the CASE statement. I have also played around with WHERE and IF statements. However this returns the same syntax error in the same location, i.e. just before the condition statement.
Any tips? Many thanks
ALTER TABLE smartcardtable
ADD service_direction boolean;

UPDATE smartcardtable
SET service_direction
CASE board_stage < alight_stage THEN service_direction = '1'
ELSE '0' END;