texts:
I'm struggling with SSMS 17 and SQL Server 17

I have just added some data into a row (multiple columns). When I move away from the row in SSMS, it commits fine. If I try to change any column, I get the error message

  No row was updated.
  The data in row 24 was not committed.
  Error Source: .Net SqlClient Data Provider.
  String or binary data would be truncated.
  The statement has been terminated.

A quick review of the row shows that I'm not hitting any limits with the nvarchar cols

I have a text column but I can add a single character and the issue still occurs

Even if I try to change a bit column from 0 to 1, or an int column from 100 to 10, the same error message is shown.

The fact SSMS/SQL Server accepted these values initially suggests that the data is valid (based upon the constraints).

I don't have any odd plugins other than SQL Prompt so I don't think that's the cause.

I'm very confused as to why I'm seeing this message?

Edit

The same message even shows when I try to delete the row via SSMS.

Running a query to update/delete works fine.


texts: SQL Server Resource governor user-defined resource pools

texts:
In the context of the SQL Server resource governor, why would I create a user-defined resource pool? It seems like you could get almost the same result by using the default resource pool for everything, and controlling resources instead using the workload group settings.

I've heard that there can be issues if too many resource pools are created, so I'm wondering what the advantages are over the workload group settings?


texts:
why 'sp_releaseschemalock' causing me a blocking issue?
what proccess that must execute sp_releaseschemalock or what function of sp_releaseschemalock?

I have searched various forums or blogs about the explanation of the sp_releaseschemalock but have not found a proper explanation. what sp_releaseschemalock do?


texts: import mysql schema data folder instead of from a dump

texts:
current state

I have prod and dev mySQL databases.

To create the dev database, I used the various schema backups generated from production and imported them like so:

mysql -u my_user -p target_db < target_db_backup.sql

This was an extremely time-consuming process (over 24 hours for some schemas). I'd like to speed it up.

ideal solution

I'd prefer to just be able to:

stop mysql
rsync production schema data folder (/var/lib/mysql/my_schema/) to the same location on dev.
start mysql
everything just works!

the issue

When I attempt to do something simple like select data, the following errors are generated:

2016-01-11 21:47:14 7f293d454780  InnoDB: Error: table 'my_schema/my_table'
InnoDB: in InnoDB data dictionary has tablespace id 1450,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name my_schema/my_table and id 2451, though. Have
InnoDB: you deleted or moved .ibd files?

So I assume there's some innoDB finagling that needs to happen to get this to work.

Is it possible to just mess with innoDB settings in mysql to allow the data files in the schema to be read properly without having to import data from a dump?


texts:
I have a need to store products along with their prices and quantities (and many other things) and track the history.
Now, given both price and quantity are stored as integers, it is my understanding these are fact measures. So we might have a table like:
f_products:
  id
  sku
  price
  quantity

As the price varies over time we can insert a new fact, and I can introduce effective dates to the table to allow for querying the price or quantity at a given time.
But with this design, it means if the quantity changes, I must insert another row, repeating the unchanged price value again. And while this isn't too bad for 2 columns, I have in fact 10.
So, my design is:
f_products:
  id
  sku

price:
   id
   product_id
   price
   start_date
   end_date

another "dimension":
   id
   product_id
   value
   start_date
   end_date

and so on

Now the "fact" no longer needs to change when the price changes, and I can query for historical values across these tables.
So my question is, this doesn't seem to fit the standard fact / dimension designs, so I wonder how more experienced modellers would approach these requirements.


texts: Can't connect to MySQL from remote computer

texts:
A MySQL Server is newly installed on linux computer LPNBUNTU.
On local linux I can get a DB-connection and I was finally able to set a root password.
When connected I get
MYSQL>status
root@localhost

I rather think MySQL should be aware on that computer it is installed. Thus I guess the user should be root@LPNBUNTU. Just an idea.
I open the port 3306 on linux
sudo ufw allow 3306
My problem is, I can't connect from my Window-10 computer via mysqlsh. I think should doing this like
mySQL\SQL>
 \connect --mysql root@lpnbuntu:3306
Creating a Classic session to 'root@lpnbuntu:3306'
Please provide the password for 'root@lpnbuntu:3306': *********
after some timeout I get
MySQL Error 2003 (HY000): Can't connect to MySQL server on 'lpnbuntu' (10061)

I guess the MYSQLServer should be made aware, that it on computer lpnbuntu, and should react to all connection request addresses from any computer on port lpnbuntu:3306.
How can I make this happen?


texts:
I did post this question at https://dba.stackexchange.com/questions/94087/is-pdo-oci-php-pdo-using-oracle-instant-client-classed-as-odbc anonymously, but then it wouldn't allow me to comment on it so I registered and am posting it again here. I'm not sure what else to do.

I have a question that's been concerning me for some time.

We use Oracle at our company and we're about to upgrade to a newer release. I understand that Oracle have some additional licensing if you wish to connect to the database via ODBC.

My question is, we have a web application that connects to the Oracle database using PDO_OCI (PHP PDO using the Oracle Instant Client) - is this classed as accessing the Oracle database via ODBC?

In answer to this comment on the original question:

  "I understand that Oracle have some additional licensing if you wish
  to connect to the database via ODBC" - where did you read that? The
  connection method is mostly irrelevant for licensing AFAIK

I'm not sure. I asked our software vendor to upgrade our Oracle database version and they are doing this but have raised a question "Do you access our database directly via ODBC? As this affects our licensing costs.".


texts: Linked server from SQL server on AWS RDS to On premises MS SQL server database

texts: We are planing to migrate an on-premises SQL Server db to AWS RDS SQL Server. My only concern is how can I create a linked server from AWS RDS SQL Server to on-premises SQL Server db? Is it possible? I am checking it because there are many views using linked server to other on-premises dbs.


texts: Under what conditions are polymorphic associations used? What are the alternatives?

texts:
As I understand it, polymorphic relationships among tables in a database allow one table to have one foreign key that references many tables.

I have found that tags are a common application of this structure. Many tables may have a tag, and a polymorphic relationship allows each of these tables to reference a single tags table.

Unfortunately, I have not been able to find very much background on this topic, either on google or on this site. Most of the writing is either too general (c.s. theory) or too specific (implementation).

I would like to learn more about their use, and how these structures compare to alternatives. Is there a good reference on the use of polymorphic associations in databases?


texts: How do I find what keys mysql will use for a query?

texts:
I just had a query encounter a deadlock, where the lock it was trying to acquire was for an index. But when I used explain to visualize the query, I saw that a different index was listed under key and possible_key. Going back to the query, I found that the key causing the deadlock was on a column only used in the order by clause, and nowhere else in the query.

This set me thinking: Given an arbitrary SQL statement, how do I find out all of the indexes mysql is going to need to acquire a lock on in order for it to run?


texts: Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes

texts:
I try installing SQL2008R2 - SQL2012 - sql2014

2014-07-17 16:31:16.00 spid14s     Error: 17190, Severity: 16, State: 1.
2014-07-17 16:31:16.00 spid14s     Initializing the FallBack certificate failed with error code: 1, state: 20, error number: 0.
2014-07-17 16:31:16.00 spid14s     Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
2014-07-17 16:31:16.00 spid7s      Starting up database 'msdb'.
2014-07-17 16:31:16.01 spid12s     Starting up database 'mssqlsystemresource'.
2014-07-17 16:31:16.01 spid14s     Error: 17182, Severity: 16, State: 1.
2014-07-17 16:31:16.01 spid14s     TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
2014-07-17 16:31:16.01 spid14s     Error: 17182, Severity: 16, State: 1.
2014-07-17 16:31:16.01 spid14s     TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.
2014-07-17 16:31:16.01 spid14s     Error: 17826, Severity: 18, State: 3.
2014-07-17 16:31:16.01 spid14s     Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2014-07-17 16:31:16.01 spid14s     Error: 17120, Severity: 16, State: 1.
2014-07-17 16:31:16.01 spid14s     SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.


texts: Visual Studio database project and Enity Framework code-first

texts:
At work we are developing our latest module in C# Entity framework code-first approach. I have been given the role of developing databases from the DBContexts from within the project/s.

I generated the tables/entities from the DBContext and created database projects and all is fine.

My question is: When a developer changes the DBContext entities or changes a property of an entity, how can I become aware of such changes and update the database projects?


texts: When was Torn Page Detection and Checksum introduced to SQL Server and what are the upgrade behaviors?

texts:
There are two different options in modern SQL Server for page verify; being Torn Page Detection and Checksum. None is also of course an option.

I believe Checksum was introduced in SQL Server 2005 and that upgrading or restoring a DB from a prior version would maintain its previous page verify method. i.e. there was no implicit upgrade.

The problem involved is that we have a production database that went into production using SQL Server 2000 and has since moved to a SQL Server 2008 R2 server. Page Verify is set to None when I had been expecting it to be Torn Page Detection. Going back this amount of time we seem to think the DB was originally developed in SQL Server 7.0 then migrated to SQL Server 2000 and this may explain the observed result.

I was wondering when Torn Page Detection and Checksum became a feature of SQL Server, and how they behaved when migrated or upgraded to newer versions.

Edit: Summing up some of the answers:

There is a little discrpenacy over some of the dates for when Torn Page Detection came into SQL Server.
Link 1: http://support.microsoft.com/kb/230785
Link 2: http://technet.microsoft.com/en-us/library/aa337525(v=sql.90).aspx

The first link indicates SQL 7.0 and the second SQL2000. I tend to put my faith in the SQL7.0 suggestion and that link two was confused over it being off by default in SQL7.0 and on by default in SQL2000.


texts: SQL Server 2012 - Is There a Log That Captures DB Maintenance Type Activity?

texts:
I had a backup maintenance job setup for all system databases. I also have another job that sends me an email whenever a job fails.

After receiving notification of the system database backup job failure, I noticed that the maintenance job was no longer there. Now, I've setup hundreds of maintenance jobs and they don't normally just disappear. I'm guessing that it was deleted.

Are there any logs I can monitor that captures activity like that?

I know ideally, the solution would be to lock down the permissions but it's would be very difficult to do that in this environment as well as DBA's sometime make mistakes. But at least a DBA would know how to recreate the job - hopefully.

Thanks!


texts: Does The Stored Procedure sp_removedbreplication Remove Rowguids

texts:
In merge replication if I run sp_removedbreplication will that remove rowguids or not?

Will it take into account the preserve_rowguidcol values in the sysmergearticles table?


texts: Update value equal to the count of not appearance on the last X records

texts:
I have a MySQL table where a number between 0 and 99 is inserted every now an then. Every time a new value is inserted I need to count how many numbers between 0 and 99 does not appears in the last 100 previous records and update not_in_last_100_repetitions.

You can assume the structure of my table is:

num (int, primary, auto-increment) | winner (varchar) | not_in_last_100_repetitions (int)


texts: What is the technical meaning of "Bulk" in the “Bulk-Logged Recovery Model” and why this name?

texts:
With this model there are certain bulk operations such as BULK INSERT, CREATE INDEX, SELECT INTO, etc... that are not fully logged in the transaction log and therefore do not take as much space in the transaction log.

What is the technical meaning of "Bulk" in the "Bulk-Logged Recovery Model"? I need clarity on what-all is classified as bulk please. Is every bulk insert, create index, select into classified as bulk or does it depend on volume of data involved?

As I understand, the Bulk-Logged Recovery Model doesn't fully log the bulk transactions. Then why is it named like this?


texts: Query fails from application but runs fine in SSMS

texts:
I have a query that runs fine when executed from SSMS but when done so from the application, it fails with a (database level) exception.

select ksprintid
from kshistory_akt h
where cast(h.serialno as decimal(28)) >= 1
    and h.sku = '11105031'

The application error is

System.Data.SqlClient.SqlException: Error converting data type varchar to numeric.
   bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   bei System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   bei System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   bei System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   bei System.Data.SqlClient.SqlDataReader.Read()
   bei NHibernate.Driver.NHybridDataReader.Read()
   bei NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer)
   bei NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer)
   bei NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer)

The code that executes it:

ISessionFactory _factory = new Configuration().Configure().BuildSessionFactory();
CurrentSessionContext.Bind(_factory.OpenSession());
IList printedJobs = _factory.GetCurrentSession()
    .CreateSQLQuery("select 1 from kshistory_akt h where cast(h.serialno as decimal(28)) >= :start and h.sku = :material")
    .SetString("material", "11105031")
    .SetParameter<int>("start", 1)
    .List();

But, the weird thing is that the result set of

select ksprintid
from kshistory_akt h
where h.sku = '11105031'

(without the actual cast) is empty:

So - how can an empty result cause an conversion error? And why doesn't it happen from within SSMS?

And to top it off: This only happens on one of three servers where this application runs. Every other server happily runs the command from the application without any errors (as it should be, because empty results should not cause problems when converting, right?)

SSMS Connection options

ANSI_WARNINGS
ANSI_PADDING
ANSI_NULLS
ARITHABORT
QUOTED_IDENTIFIER
ANSI_NULL_DFLT_ON
CONCAT_NULL_YIELDS_NULL

Client Connection options

ANSI_WARNINGS
ANSI_PADDING
ANSI_NULLS
QUOTED_IDENTIFIER
ANSI_NULL_DFLT_ON
CONCAT_NULL_YIELDS_NULL


texts:
I have several questions about the (streaming) replication in PostgreSQL 10:

In case of a failover, how should I rebuild the standby ? Do I
have to take a backup of the new primary and rebuild the new standby (in this case, I'm sure to have the latest version of all the file in my standby) ? Or do I have to take the backup of the old primary, restore it and apply some WAL (the restore operation will be shorted in this case) ? Both solutions work and both solutions have theirs pros and cons.
The modifications in the configurations files
aren't replicated (postgresql.conf, pg_hba.conf). Do you have any
solutions/feedbacks to keep the configuration up to date in the
standby database ?

Thanks for your help,


texts: Oracle - Monitoring tablespace warnings and critical events via Microsoft Windows Event log

texts:
I would like to monitor tablespace warning and critical events of my Oracle database (12c Standard Release 12.2.0.1.0) via Microsoft Windows Event Viewer and attach tasks to it.
Right now only a few event log entries of the Oracle db are available in Event Viewer - i assume this is because audit_trail is currently set to DB.
Is it the correct way to set audit_trail to OS to get the desired events in Windows Event Viewer?
Anything i have to consider regarding this change?


texts: sp_BlitzCache slow results on @SortOrder='cpu'

texts:
I've been using sp_BlitzCache to investigate a few problems with a DB of mine for a few days. It's been a huge help, but just today it started acting a little odd and I'm curious if anyone has any ideas what's up.

Yesterday I ran many checks with @SortOrder as either 'reads' or 'CPU' and it worked fine, and always returned the results in about the same amount of time.

Today, 'reads' comes back in about the same amount of time as yesterday, but 'cpu' takes 10X or more as long.  It gets to 'Checking for functions' and then sits there for a very long time.  'xpm' is also as fast as usual.

I'm using v5.7(20170901)

I realized my SQL session had been open for +24hours, and thought that the ##temp files were perhaps getting too big.  But after I restarted the session and those tables went away, cpu sort order is still really slow.  So that wasn't it.

Any thoughts on what would be causing this?


texts: Columns count in SELECT query and SQL Server buffer cache

texts:
Let's assume that I have a table with 30-50 millions records. It has ~40 columns. Currently when I request data it always requests all columns because of legacy ORM that we use.

I know that if we specify fewer result columns in SELECT query, then it will be easier for SQL Server as it doesn't need to load them from disk and return to client. But what about SQL Server buffer cache?

Lets say my application executes such query:

SELECT Column1,Column2,...,Column40
FROM ...
WHERE OwnerID = 42

So it will load all columns for these records, cache them in memory, and return to client.

And next query after it will be

SELECT Column1,Column2
FROM ...
WHERE OwnerID = 42

So it won't load other columns and return much less data, but will it matter if anyway they are loaded from cache and not from disk, as we loaded all columns in previous query. Will it help SQL Server or it won't matter at all because of SQL cache? How much will be difference in terms of complexity for SQL Server?

I can't remove first query for now, so I'm trying to optimize next queries for the same entity and trying to figure out a good ways.

Thanks!


texts: Add column with a sum total to crosstab() query in PostgreSQL 9.0

texts:
Following on from my previous question:

Creating crosstab() pivot table in PostgreSQL 9.0

I managed to create a pivot table for ageband using the crosstab() function. I can use this to either create a view or table of the base geometry-less table.

However, this still isn't much use as I need to link it to the gazetteers_and_addresses.unit_postcode table in order to assign geometries for further analysis.

I will attach the table structure for both tables and the original code that worked to create my crosstab.

CREATE OR REPLACE VIEW adult_social_care.vw_ageband AS (
    SELECT * FROM crosstab(
        'SELECT postcode_nospace_, ageband, count(ageband) as total_count
         FROM adult_social_care.activities_in_localities_asc
         GROUP BY postcode_nospace_, ageband
         ORDER BY postcode_nospace_'

         ,$$VALUES ('18-24'::text), ('25-34'), ('35-44'), ('45-54'), ('55-64'), ('65-74'), ('75-84'), ('85-94'), ('95 AND OVER')$$)
    AS ct("postcode" text, "18-24" numeric, "25-34" numeric,"35-44" numeric, "45-54" numeric, "55-64" numeric, "65-74" numeric, "75-84" numeric, "85-94" numeric, "95 AND OVER" numeric));

Table defintions:

activities_in_localities_asc:

CREATE TABLE adult_social_care.activities_in_localities_asc (
  ogc_fid integer NOT NULL,
  sort numeric(5,0),
  ageband character(12),
  postcode_nospace_ character(8),
  wkb_geometry geometry,
  CONSTRAINT activities_in_localities_asc_pkey PRIMARY KEY (ogc_fid)
);

unit_postcode:

CREATE TABLE gazetteers_and_addresses.unit_postcode (
  oogc_fid serial NOT NULL,
  pc_area character(10),
  postcode_nospaces text,
  wkb_geometry geometry
);

If possible too, assign another field at the end which states a sum of all the fields to give a total_count.

If this can be done then I can create dynamic views on different factors using one geometry-less table and unit_postcode.


texts: How to index cube style queries with changing aggrigations

texts:
I've got a query that emulates the CUBE function.
This is many different aggregations unioned together.
select x,y,z, sum(a) from tbl group by x,y,z
union all
select x,'all',z, sum(a) from tbl group by x,z
union all
select 'all',y,z, sum(a) from tbl group by y,z
union all
select x,'all','all', sum(a) from tbl group by x
etc.

Unfortunately, this is really slow. Everything is distribution aligned. Analysing the estimated query plans shows that a columnstore index always uses hash match to perform this operation. A clustered index will allow one of the queries to use a stream aggregate, although I was expecting a few more ( see plan below)

I was hoping that the below index:
clustered index (x,y,z)

would enable stream aggrigate for the below groupings

x,y,z
x,y
x

Is there something else I'm overlooking?


texts: How to use a conditional ORDER BY in MySQL?

texts:
I have the table courses where we have some fields and also the field course(string), favorited(bool), favoritedDate(dateTime), dateUpload.

The teacher uploads the course and this date is stored in the dateUpload field. If the teacher flag this course as favorite this information is a number 1 in the field favorited and the date it was flagged is stored in the field favoritedDate.

Now I need to retrieve all courses from a specific teacher and then order the list by date: If the course is flagged favorited=1 should ordered by favoritedDate,dateUpload desc, if favorited=0 should be ordered only by  dateUpload desc.

This is what I have so far but it is not working:

SELECT * from courses where teacherId = 23
ORDER BY
  CASE favorited WHEN 1 THEN favoritedDate, dateUpload
    else dateUpload
  END
  DESC
;

Any idea how to solve this?


texts:
We are running Postgres 9.3, and we have occasional deadlock issues. Currently someone notices and will manually kill off the processes. Is there a best practice in dealing with these when they happen? I know there is a statement_timeout, lock_timeout and deadlock_timeout but most the places I read state that you don't want to set this in the postgres.conf file.

Just wanted to see if there is a typical/best practice method for this. It looks like setting deadlock_timeout to something like 10 minutes would be sufficient, but I wanted to make sure I wasn't missing some key something as to why that would be a bad idea.


texts: MySQL parent / child table not showing parent entries

texts:
I have a table in which categories are listed, and each product can belong to a parent category or not. There is only one level of possible depth.

CREATE TABLE `categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `parent` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
);

I then insert some records:

INSERT INTO `categories` (`id`, `name`, `parent`) VALUES
(1, 'Cat 1',    NULL),
(2, 'Cat 2',    NULL),
(3, 'Cat 3',    NULL),
(4, 'Subcat 2.1',   2),
(5, 'Subcat 2.2',   2);

Now I'd like to get a list that includes all the categories and subcategories, ordered by main category and with the subcategories underneath, like so:

Cat 1
Cat 2
Subcat 2.1
Subcat 2.2
Cat 3

When I try the following it almost works, but it doesn't return the record for "Cat 2", only it children.

SELECT e.name AS parent_name, e.id AS parent_id, r.id AS child_id, r.name AS child_name
     FROM categories e
LEFT JOIN categories r ON e.id = r.parent
    WHERE e.parent IS NULL
 ORDER BY parent_name, child_name

Results:

Cat 1   1   NULL    NULL
Cat 2   2   4   Subcat 2.1
Cat 2   2   5   Subcat 2.2
Cat 3   3   NULL    NULL

How can I make it return all 5 records including the "Cat 2" parent category?


texts:
I changed my password from old hash to a new hash using the following query

SET SESSION old_passwords=FALSE;
SET PASSWORD = PASSWORD('[your password]');

I found this query on https://stackoverflow.com/questions/1892607/mysql-password-hashing-method-old-vs-new. I replaced [your password] to my own password.

Now I can't log into my db again using the password I set. I was wondering is this password change forever or is the session going to expire and how long till it does.


texts: Updating multiple columns using case conditional clause

texts:
In the following TSQL code the second column TypeId2 is not updated.
What could be the problem here?

declare @dd table(Id int,number int, TypeId int)
declare @tt table(Id int,TypeId1 int, TypeId2 int)
insert into @dd values(25,10, 1),(25,15, 2)
insert into @tt values(25,0,0)

update t set
t.TypeId1=case when TypeId=1 then number end,
t.TypeId2=case when TypeId=2 then number end
from @tt t inner join @dd d on d.Id=t.Id

select * from @tt;

After executing the above code and selecting data from @tt I get the following result:

Id  TypeId1 TypeId2
-------------------
25    10      NULL

which is not acceptable because the value of TypeId2 is null, it should be 15.


texts: How to store Alternate Names for Places or City in Table

texts:
What is best way to store Alternate Names of the Places (City or Locality) in MySQL database columns which user will be searching?
Examples:
In India user may search for "Chennai" or "Madras" and both are same City.
And "Sanjeeva Reddy Nagar" or "SR Nagar" are same Locality in Hyderabad City that user may search for.
Properties
-----------
PropertyId INT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
PropertyName VARCHAR(64),
City VARCHAR(32),
Locality VARCHAR(32)

So in above structure how shall we accomodate alternate names for City and Locality?

Option-1:
May be having 2 more columns (AlternateCity, AlternateLocality) and have search function search in both the columns (Ex: if user search for Locality then it will search in Locality and AlternateLocality column)

Option-2:
May be having the same column store multiple data values in one column. Ex: City can store "Chennai (Madras)" and search can use Like operator?

What is the best way to store these data knowing one place may have 2 - 3 names.
Thanks.


texts: SQL Server - Capture queries created in tempdb using Extended Events

texts:
Our reporting platform extensively using creating temp tables (#tmp) to display data from multiple sources.

Using extended events we can capture growth events on tempdb, but
Can we capture queries created in tempdb using extended events or DMVs.

Below Query covers all queries, I want it to capture all TEMPDB queries

CREATE EVENT SESSION [Caputre ALL Queries] ON SERVER
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text)
   ),
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.session_id)
    )

ADD TARGET package0.event_file
(
  SET FILENAME = 'F:\PerformanceLog\CaptureAllQueries.xel',
      MAX_FILE_SIZE = 32768,
      MAX_ROLLOVER_FILES = 10
),
--ADD TARGET package0.event_file(SET filename=N'Capture All Queries',max_file_size=(100)),
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO


texts:
I have my 200 gb database.

I want to move EC2 from RDS.

I work on a research to prevent the problems that you may see,but it has not been done before.

There may be problems;

*Security weaknesses,

*High availability,

*Restore process takes a long time,

*In RDS, the time zone will be UTC, and UTC + 3 in EC2.

What other problems might occur from this problems.

Thank you.


texts: How to create two same name databases in two different MySQL users?

texts:
I am using MySQL 5.5 running on Ubuntu 12.04.

I have created two different users as 'root' and 'abc' and I want to create 'Items' database in this both user accounts but I want this two databases separated.

Now my problem is that I have created 'Items' database in root user with 10 tables and when I am trying to create a new user 'abc' then 'Items' database comes automatically in that new user with 10 tables and that is what I don't want to do.

How I can create 2 same name databases in different user accounts with different tables in both DBs?


texts: Postgres HAVING ilike any wildcard in array_agg

texts:
I need a filter where the input value is similar to any value in an aggregate, using ilike and wildcards. My attempt at a query so far is:

SELECT p.id, p.name, array_agg(vo.name)
FROM product p
LEFT JOIN product_variation pv
ON p.id = pv.product_id
LEFT JOIN variation_option vo
ON pv.variation_option_id = vo.id
GROUP BY p.id
HAVING $1 ilike any(array_agg('%' || vo.name || '%'));

But it doesn't work. It gets even more complicated: ideally, I'd be able to input an array of strings, where any of them are similar to the aggregate. Any tips?

I realize this kind of works, just not as I need it to. The input value should be able to be a partial match for any of the array elements, but it is the other way around. The wild card would need to be in the input value.


texts: Can we enable alwayson feature of sql 2012 in windows 8.1?

texts:
I want to enable "AlwaysOn" feature of MS SQL Server 2012 in Windows 8.1

Is it possible to do so?


texts: Please explain SYSCS_UTIL.SYSCS_CREATE_USER() function

texts:
Can someone please explain to me how SYSCS_UTIL.SYSCS_CREATE_USER() works? I tried adding a new user like this: SYSCS_UTIL.SYSCS_CREATE_USER('user', 'someP@ss'), and it gave an exception Error code 30000, SQL state 4251K: The first credentials created must be those of the DBO.

I found the function in the Derby Docs, but it showed the Fred example, which I also tried, but it gave me the same exception.

I created the database via NetBeans.


texts: Oracle 12c. Is it possible to export a TDE encrypted table using expdp / impdp to a database without TDE / wallet enabled?

texts: I have an Oracle 12c database where I have encrypted certain columns of several tables, I want to import or load these tables into another database which does not have TDE enabled, as would be recommended with datapump? or dblink? And if it were the last one, could it be done without having TDE in the destination? thanks


texts: What could possibly cause a numeric overflow when enqueuing an Oracle AQ message?

texts:
We create and enqueue messages with various properties, like this:

    message :=  sys.aq$_jms_message.construct(0);
                  message.set_string_property('a', v_a));
                  message.set_int_property('b', v_b);
                  message.set_int_property('c', v_c);
                  message.set_string_property('d', v_d);
    DBMS_AQ.ENQUEUE(queue_name         => SOME_QUEUE,
                    enqueue_options    => queue_options,
                    message_properties => message_properties,
                    payload            => message,
                    msgid              => message_id);

After running this successfully for a long time, we are all of sudden getting

ORA-01426: numeric overflow ORA-06512: at "SYS.DBMS_AQ"

What could possibly cause this?
Running the same enqueuing code against a newly created queue with new queue table produces the same result.


texts: Postgres IDLE State affect Max_Connection value set in Postgresql.conf

texts:
I am using Postgres 9.5 and we have a max_connection set to 200 in our database postgresql.conf file.
We have 2 apps server running java apps which are load balanced. When we run our tests via JMETER, I run pg_stat_activity and I can se there is alot of IDLE connections with most of the queries called COMMIT.
Do I need to be concerned with the IDLE connections and will this affect any new tests I run. Could this be an issue with my Java apps not auto_commiting?
My assumption is that the connections will be re-used but concerned if we run more tests new connections will spin up and the max_connection of 200 users will be hit.
Any help is much apprecaited.


texts: What Is the Next Best Ingredient to Add?

texts:
I am building a program that allows users to optimize their grocery shopping so they can make the most recipes using the fewest ingredients.

One of the features of this program is a function I’m calling the “NEXT BEST INGREDIENT” or NBI.  For example, if you already have salt, oil, and rosemary, what NBI will unlock the most additional recipes?

Let’s say the answer is beef.  If you buy beef, you’ll be able to create more new recipes than if you bought any other ingredient.

After buying beef, what is the NEXT BEST INGREDIENT one should add after that?  And so on.

The feature will allow a user to enter any number of starting ingredients (including zero).  So someone could start with 0, 3, or even 50 ingredients.  What should they add next?

I’m using a database of recipes (about 500K recipes in all) to compile results.

I can map out the individuals steps for writing out a specific algorithm.  But I need help writing a more generic algorithm.

Here’s a specific algorithm:

A user enters in 3 ingredients A, B, and C

Isolate all 4-ingredient recipes that use use A+B+C + BLANK.  Note the BLANK with highest frequency.
Isolate all 3-ingredient recipes that use A+B + BLANK… A+C + BLANK… B+C + BLANK. Note the BLANK with highest frequency.
Isolate all 2-ingredient recipes that use A + BLANK….B + BLANK…C+ BLANK. Note the BLANK with highest frequency.
Isolate all 1-ingredient recipes that simply use BLANK. Note the BLANK with highest frequency.
Calculate the BLANK with the highest TOTAL frequency.  This is the NEXT BEST INGREDIENT.

But I need a way to write a more generic algorithm for when a user enters N number of ingredients (anything from 0 ingredients to 100).

I can write out the rules in plain English – but my programmer needs a way to write a generic rule using coding logic.

Here it is in plain English.

When user searches N ingredients (including zero), instantly ignore any recipes that require N+2 ingredients or more.  And then isolate ALL remaining recipes that use ALL, SOME, or NONE of those ingredients such that there is 1 (and only 1) vacant slot for an “unsearched” ingredient.
Tally up the frequency of all “unsearched” ingredients.  Whichever “unsearched” ingredient has the highest frequency becomes the NEXT BEST INGREDIENT.

The goal is to make this search feature accurate (or as accurate as my database is) and fast.  A few ingredients is easy.  But when looking at 10+ ingredients, it may slow down a bit.

Any thoughts?


texts: Cannot connect to RDS Aurora Mysql Database Even Though It Exists

texts:
We have an RDS aurora mysql instance that has been up and running for a while without issue, however, today our application's connection to the db failed, with error ERROR 1049 (42000): Unknown database 'campspot_development'.

Things we've tried:

Connecting to the database without specifying the db name is successful:

mysql -h write.staging-database.campspot.com -u campspot_staging -p

SHOW DATABASES; returns the database in question.

mysql> SHOW DATABASES;
+----------------------+
| Database             |
+----------------------+
| information_schema   |
| campspot_development |
+----------------------+
2 rows in set (0.01 sec)

If we connect using the string above and query USE campspot_development it is unsuccessful. But if we run a query with the schema prefixed to the table, it works.

SELECT * FROM campspot_development.Park;

Connecting while specifying the db is not successful, resulting in the Unknown database error:
mysql -h write.staging-database.campspot.com -u campspot_staging -p campspot_development

What could be causing the database to be queryable and show in the list of databases, but stopping it from being connected to directly?


texts: I have SQL Server Management Studio 18 - Where I can find SQLServerManager / SQL Server Configuration Manager

texts:
I need to install SQL Server Configuration Manager in order to configure SQL Aliases, but I don't want to install whole SQL Server
I expected it to be installed with SSMS, but I'm unable to find anything on disk with name SQLServerManager
How can I get it as easily(without installing heavy components) as possible?
Thanks in advance


texts: Why is pg_restore unable to create database with --create option before restoring from dump?

texts:
Why pg_restore is unable to create database with --create option before restoring from dump?

pg_restore --username=myuser --create --dbname=mydb --verbose  mybackup

Error,

pg_restore: [archiver (db)] connection to database "mydb" failed: FATAL:  database "mydb" does not exist


texts:
I have a Server level Audit running and informational messages are continuously placed in the SQL Error log (every hour):

Audit: Server Audit: 65976, Initialized and Assigned State: STARTED
Audit: Server Audit: 65976, Initialized and Assigned State: START_FAILED
Audit: Server Audit: 65975 Session has been closed
Audit: Server Audit: 65975, State changed from: STARTED to: SHUTTING_DOWN

My question is, why does this happen and more important can this be prevented (while continuing the audit)?


texts: I have a stored procedure that searches for rows given a long list of parameters. Some of the queries result in extremely slow count queries. Is it possible to stop counting after, say, one hundred rows so that I can simply display "100+" for the row count? It's not important that the users know exactly how many rows there are - especially when the result is in the thousands.


texts: Oracle 10g express database link to Oracle 8i

texts:
I upgraded a 9i install to 11g with only one problem.  Database links between 11g and our legacy 8i database are no longer supported.  I have to transfer data from 11g to 8i two or three times a day so I thought it might be possible to use 10g express to link the databases.
The data would be created in 11g, written to a table in 10g and then written to 8i.  (I agree this is not the most elegant solution and I can improve it using advanced queue tables).

Does 10g express support database links to Oracle 8i?

Edit: thanks for the link to the Oracle documentation.  Has anyone actually done this with 10g express?

Edit:  The 8i database is Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
The intermediate would be the 10g express for windows
The origin database is Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production


texts: Sql Anywhere 11: Restoring incremental backup failure

texts:
We want to create remote incremental backups after a full backup.  This will allow us to restore in the event of a failure and bring up another machine with as close to real time backups as possible with SQL Anywhere network servers.

We are doing a full backup as follows:

dbbackup -y -c "eng=ServerName.DbName;uid=dba;pwd=sql;links=tcpip(host=ServerName)"
    c:\backuppath\full

This makes a backup of the database and log files and can be restored as expected.  For incremental backups I've tried both live and incremental transaction logs with a renaming scheme if there are multiple incremental backups:

dbbackup -y -t -c "eng=ServerName.DbName;uid=dba;pwd=sql;links=tcpip(host=ServerName)"
    c:\backuppath\inc

dbbackup -y -l -c "eng=ServerName.DbName;uid=dba;pwd=sql;links=tcpip(host=ServerName)"
    c:\backuppath\live

However, on applying the transaction logs on restore I always receive an error when applying the transaction logs to the database:

  10092: Unable to find table definition for table referenced in transaction log

The transaction log restore command is:

dbeng11 "c:\dbpath\dbname.db" -a "c:\backuppath\dbname.log"

The error doesn't specify what table it can't find but this is a controlled test and no tables are being created or dropped.  I insert a few rows then kick off an incremental backup before attempting to restore.

Does anyone know the correct way to do incremental backup and restore on Sql Anywhere 11?

UPDATE: Thinking it may be related to the complexity of the target database I made a new blank database and network service.  Then added one table with two columns and inserted a few rows.  Made a full backup, then inserted and deleted a few more rows and committed transactions, then made an incremental backup.  This also failed with the same error when attempting to apply the incremental backups of transaction logs after restoring the full backup ...

Edit:

You can follow this link to see the same question with slightly more feedback on SA: http://sqlanywhere-forum.sybase.com/questions/4760/restoring-incrementallive-backup-failure


texts: Non clustered index not used for nullable column

texts:
I'm having a strange (to me) issue with a non clustered index on SQL Server 11.0. I have a quite large table with about 4 million rows. I've created a non clustered index which looks like this

Neither is colum is primary key
I then run the follow queries with different results. One uses the non clustered index but the other does not and I can't figure out why. The only difference I can see is that AggregateEntityId is nullable.

The second query takes about 15 seconds the first takes about 50 milliseconds. Why doesn't the second query use the non clustered index?


texts: What is the best way to model an invoicing system with different forms of invoice items?

texts:
I am doing some preliminary database modeling for a Line-Of-Business application that I am creating. The application will be a Practice Management System for a medical practice/clinic. I am looking for advice on the best practice this particular situation to ease maintenance and data access in the future.

My situation is as follows:

This practice offers both products (vitamins, supplements, etc) and services (consultations with practitioners).
Billing Item is the name given to the items (lines) on a particular invoice for a customer.
Product billing items have attributes such as name, cost, description, etc.
Service billing items are related to a particular appointment (and only one), represented by the foreign key AppointmentID.
Each appointment is also related to an AppointmentType table entry (many appointments to one appointment type) which describes the cost, name & description of the appointment (e.g. Its attributes).

In modeling the BillingItem table, in order for it to hold both types of data, it would need to contain redundant columns to hold both types of data (either an AppointmentID or product details). The downside to this being the loss of the ability to constrain data since I must now allow nulls on all columns.

The alternative is to have two tables, ProductBillingItem & ServiceBillingItem. Each of the tables contains the relevant data (either an AppointmentID or product details). The downside to this being that for the retrieval of every single invoice, both tables must be queried and then added together in my application.

I have attached a screenshot of my invoice E/R model so far for the sake of reference. (The billing item table obviously has not been modeled to either of the approaches yet)

Which approach is the best practice for this kind of situation?


texts: Optimising filter with dynamic computed column

texts:
I need to implement user risk scoring in model

The user risk scoring must compile dynamically on changes (eg: profile, transaction) - meaning for any changes on profile or any incoming transaction, the new risk scoring value needs to be be able to reflect immediately

It will span across 10+ different tables

The query with user list need to be able to filter by RiskScore

Options

I can create a user defined function [udf].[CalculateRiskScore] and make a computed column in [dbo].[User] or query out during view
The advantage with this is good maintainability but bad performance.

Another way is to have a trigger for those 10 tables to call [udf].[CalculateRiskScore] and update [RiskScore] into [dbo].[tblUser] with indexing on [RiskScore].
The advantage with this is good performance but poor maintainability since now i have to repeat x times depending on how many table that will affect [RiskScore].

There may be another solution I am not aware of. Is there any way that I can get the best of both worlds while adhering to the requirement?
My objective is to call [udf].[CalculateRiskScore] in one single place only while not sacrificing performance of filtering user with [RiskScore]


texts: Why won't node-1 restart successfully on my 4-node MariaDB Galera cluster, but the others will?

texts:
I setup a 4-node Galera cluster on the following platform:

CentOS 8.1.1911, with MariaDB 10.3.17 and Galera 3.26.

Galera config:
wsrep_cluster_address="gcomm://192.168.10.47,192.168.10.48,192.168.11.47,192.168.11.48"

I setup a new cluster on node 1 running "galera_new_cluster". Then I started mariadb on the other nodes which all joined successully.

I wrote a script to examine the wsrep status on each node and noted the following values:

wsrep_cluster_status: Primary
wsrep_cluster_size: 4
wsrep_local_state_comment: Synced
wsrep_evs_state: OPERATIONAL

Then to test the cluster, I restarted each node starting with node 4, 3, 2 and then 1. I monitored the above status and noticed the cluster size reduced to 3 on a reboot of nodes 4, 3 and 2 and then went back to 4 after re-joining.

However, when rebooting node 1, it did NOT re-join the cluster successfully, and the status on each of the other 3 nodes was:

wsrep_local_state_comment: Initialising

The logs on node 1 showed:

2020-04-30 12:31:36 0 [Note] WSREP: gcomm: connecting to group 'Web_Cluster', peer '192.168.10.47:,192.168.10.48:,192.168.11.47:,192.168.11.48:'
2020-04-30 12:31:36 0 [Note] WSREP: (26c8ad71, 'tcp://0.0.0.0:4567') connection established to 26c8ad71 tcp://192.168.10.47:4567
2020-04-30 12:31:36 0 [Warning] WSREP: (26c8ad71, 'tcp://0.0.0.0:4567') address 'tcp://192.168.10.47:4567' points to own listening address, blacklisting
2020-04-30 12:31:36 0 [Note] WSREP: (26c8ad71, 'tcp://0.0.0.0:4567') connection established to 150abdb8 tcp://192.168.10.48:4567
2020-04-30 12:31:36 0 [Note] WSREP: (26c8ad71, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers:
2020-04-30 12:31:36 0 [Note] WSREP: (26c8ad71, 'tcp://0.0.0.0:4567') connection established to 2f355b90 tcp://192.168.11.48:4567
2020-04-30 12:31:36 0 [Note] WSREP: (26c8ad71, 'tcp://0.0.0.0:4567') connection established to c8c39206 tcp://192.168.11.47:4567
2020-04-30 12:31:38 0 [Note] WSREP: declaring 150abdb8 at tcp://192.168.10.48:4567 stable
2020-04-30 12:31:38 0 [Note] WSREP: declaring 2f355b90 at tcp://192.168.11.48:4567 stable
2020-04-30 12:31:38 0 [Note] WSREP: declaring c8c39206 at tcp://192.168.11.47:4567 stable
2020-04-30 12:31:39 0 [Note] WSREP: (26c8ad71, 'tcp://0.0.0.0:4567') connection to peer 26c8ad71 with addr tcp://192.168.10.47:4567 timed out, no messages seen in PT3S
2020-04-30 12:31:40 0 [Note] WSREP: (26c8ad71, 'tcp://0.0.0.0:4567') turning message relay requesting off
2020-04-30 12:32:07 0 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110 (Connection timed out)
         at gcomm/src/pc.cpp:connect():158
2020-04-30 12:32:07 0 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():209: Failed to open backend connection: -110 (Connection timed out)
2020-04-30 12:32:07 0 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1458: Failed to open channel 'Web_Cluster' at 'gcomm://192.168.10.47,192.168.10.48,192.168.11.47,192.168.11.48': -110 (Connection timed out)
2020-04-30 12:32:07 0 [ERROR] WSREP: gcs connect failed: Connection timed out
2020-04-30 12:32:07 0 [ERROR] WSREP: wsrep::connect(gcomm://192.168.10.47,192.168.10.48,192.168.11.47,192.168.11.48) failed: 7
2020-04-30 12:32:07 0 [ERROR] Aborting

I couldn't figure out why there was a connection time-out as all nodes could talk to each other. In the end the only resolution was to run "galera_new_cluster" on node 1 again.

When running:

systemctl status mariadb

The only difference I could see on node 1 compared to the others was the extra parameter:

9195 /usr/libexec/mysqld --basedir=/usr --wsrep-new-cluster
9051 /usr/libexec/mysqld --basedir=/usr
18193 /usr/libexec/mysqld --basedir=/usr
16338 /usr/libexec/mysqld --basedir=/usr

Any insight as to how I can get node 1 to re-join after a reboot would be appreciated, as this is a potential issue waiting to happen.


texts:
the short version of my question is: "During the merge of two partitions is the whole partitioned table being blocked. How can I access, for read&write, the part that is not affected by the merge?"

And the context:

I am working on quite common scenario - there is a production table that grows in linear way (a few hundreds rows every 5 minutes). But we do not have to keep historical data online, so we decided to use partitioning to "bite" the historical tail from the database and move it to another one.

There are many tutorials how to achieve this goal - create partition function, scheme, partition the table with it's indices, switch the partition to identical empty table and so on. And if I want to do this one more time, the "standard" way is to split the current partition and merge it's tail with the historical one.

Now what I want to end up with (example):
The table T is partitioned by some integer column P. Lets say there are at the moment three partitions and three filegroups. The partition function is set to split by values 30 and 60. The current value of the "counter" that produces values of the P column is 50. So there are some rows in partition 1 (HISTORY) which is now read-only, some rows in partition 2 (PRIMARY) that gets the periodical inserts and empty partition 3 (FUTURE).

Let the value of the counter be 70. Now the partition 1 and 2 are in the same situation. They are both read-only. The inserts are incoming with P value belonging to partition 3 only. And now I want to merge partitions 1 and 2 and move the result to another database. But I can see that during the merge of these two partitions all operations with the table T are being blocked. Even if it is INSERT to the third partition or there is selection predicate matching the partitioning function.  How can I set-up the scheme or something else to unblock SELECT/INSERT with P value from partition 3 only (that is out of the game with respect to the merge)?


texts: Compare row value when records are not successive

texts:
I have a table wich holds the records for all the products purchases made. What I'm trying to do is to get a list of all the purchases made that contain one specific product to get a price variation history. I also want to display an icon showing if the price increased, decreased or is the same value as the previous record.

I almost made it work, however, the way I found to do it is based on the unique ID of each rows. The problem with this approach is that they are not sequential, so I can't rely on this method. What I need to compare is the date, but I'm finding it difficult to do.

First of all, here is my table structures:

**tb_purchase**
id | value_purchase | value_delivery | date
1  | 80.90          | 0.00           | 2019-07-01
2  | 24.90          | 5.00           | 2019-07-03
3  | 18.90          | 5.00           | 2019-07-03
[...continue]

**tb_purchase_product**
id | id_purchase | id_product | id_provider | price | quantity | total
1  | 27          | 33         | 2           | 16.90 | 1        | 16.90
2  | 27          | 87         | 2           | 34.90 | 1        | 34.90
3  | 28          | 64         | 2           | 19.90 | 1        | 19.90
4  | 29          | 33         | 3           | 15.80 | 3        | 47.40
5  | 30          | 48         | 3           | 18.90 | 1        | 18.90
6  | 31          | 33         | 1           | 15.80 | 1        | 15.80
7  | 32          | 33         | 3           | 17.90 | 2        | 35.80
[...continue]

**tb_provider**
id | name
1  | 'Mega Food'
2  | 'Best Market'
3  | 'Amazing Sales Store'
[...continue]

This is the query I did:

SELECT
    a.price,
    DATE_FORMAT(b.date, '%d/%m/%Y') as 'date',
    c.name as 'provider',

    CASE
        WHEN a.price > d.price THEN 1
        WHEN a.price < d.price THEN -1
    ELSE 0 END AS 'variation'

FROM
    tb_purchase_product a,
    tb_purchase b,
    tb_provider c,
    tb_purchase_product d

WHERE a.id_product = :id_product -- {ex.: 33}
AND a.id_purchase = b.id
AND b.id_provider = c.id
AND b.date BETWEEN :date_initial AND :date_final
AND d.id = a.id + 1

ORDER BY b.date DESC

The expected result for id_product = 33 is as follow, but I'm getting inconsistent results since it's comparing the value from the previous id and not previous record of the id_product. I'm also having trouble to fix this since I don't know how to mach the date when it's the first smaller date.

price | date       | provider              | variation
15.80 | 05/07/2019 | 'Amazing Sales Store' | -1
17.90 | 04/07/2019 | 'Mega Food'           | 1
15.80 | 03/07/2019 | 'Amazing Sales Store' | -1
16.90 | 02/07/2019 | 'Best Market'         | 0

  Note: :id_product, :date_initial and :date_final are passed via php array argument when running the application online.


texts: Microsoft Master Data Services (MDS) Migration | 2017 to 2019

texts:
We're in the process of upgrading our MDS instance, from 2017 to 2019, by means of a migration.  I can't find any good documentation online on how to do this in an efficient way.  Has anyone performed these steps and have some guidance to share?

Thanks in advance.


texts: Storing multiple types of users and their respective credentials in separate or same table?

texts:
So there have been many questions on Stack Exchange DBA and Stack Overflow that go close to answering this, but there's still some dispute within my team on how to properly do a database design.

In our case, we have two different types of accounts - students and sponsors.

The students have the following attributes associated: id, firstname, lastname, gender, university, colelgeyear, major, ethnicity, housing, etc.

The sponsors have the following: id, name, businesstype, firstname,lastname, position, phone, address1, address2, etc.

So there's a reasonable difference in the type of functionality that the two accounts are involved with and the type of information we store about them. The question however has been brought up of storing account credentials i.e. hashedpasword, salt, etc. I know that ideally we would not store this on the same DB and access only via API. But, let's say that we have to in this case for proof of concept reasons.

Should we create a new general account table that contains the following: id, email, hashedpassword, salt, resetpasswordexpires, tokens, verification, role (this determines whether an account is for a student or a sponsor).

The argument for creating this new table so far has been the following:

doing so would allow us to reuse some of our DB queries for all authentication, forgotpassword, tokens and related functionality.
Reduce the number of joins necessary
Reduce the amount of data being queried. E.g. you don't need to know a student's college to reset his/her password.

The argument against creating this new table and instead baking the credentials into the students table and sponsors table
has been the following:

that it adds unnecessary complexity to the DB design
the students and sponsors dont share enough commonality in functionality.
That eventually saving passwords offsite would allow us to easily delete a column the sponsors table and students table and then proceed as we usually would.
That DB Queries have to be written anyways to eventually find out from the accounts table what type of account was authenticated

Here are some related links that I have found but haven't quite answered, and in particular have never addressed the code-reuse argument:

Users and Pass in Same Table?

Users and Pass in Same Table? Part 2

Multiple types of users, different tables?


texts:
I have a JSONB column (jb_column) that may have a key (let's call it schrodinger) or not. When the schrodinger key is present, it's value can either be null::JSONB or a regular JSONB object.

First, a table:

CREATE TABLE my_table
(
    id INTEGER NOT NULL,
    jb_column JSONB
);

The structure I'm trying to extract from the schrodinger column:

CREATE TYPE schrodinger AS
(
    foo NUMERIC,
    bar TEXT
);

And this was my initial approach:

SELECT mt.id, s.*
FROM my_table mt
LEFT JOIN LATERAL jsonb_populate_record(null::schrodinger, mt.jb_column -> 'schrodinger') s

But this resulted in the annoying error below:

[22023] ERROR: cannot call populate_composite on a scalar

After a couple of hours trying to debug it, I nailed it down to the null::JSONP values of the schrodinger key. So, my next attempt was to add some criteria to the JOIN, so that PG could ignore those...

SELECT mt.id, s.*
FROM my_table mt
LEFT JOIN LATERAL jsonb_populate_record(null::schrodinger, mt.jb_column -> 'schrodinger') s
          ON jsonb_typeof(mt.jp_column -> 'schrodinger') != 'null'

I'd expect this condition to skip the JOIN when both the key is not present as well as when it's there, but null::JSONP... Unfortunately that doesn't seem to be working, since I still get the same error.

I also tried jsonb_typeof(mt.jp_column -> 'schrodinger') = 'object', to no avail.

Thoughts?

edit: online example https://dbfiddle.uk/?rdbms=postgres_11&fiddle=66c90482a33aa458c6a90e1bbaa1ec9d


texts: Is there a name for a query that returns results which have sub headings?

texts:
I inhered a set of queries / macros from the previous employee in my position and one of the other people here showed me a query result (output from when the query worked correctly) which had subheadings as well as rows.  They described it as a report.

In reconstructing how the series of queries worked I determined that first the rows of the employers were added to a table. Next a list of employees were added to the same table.  And finally it was sorted on a hidden row which placed the employees under their subsequent employers.

This seems to me to be a very unnatural thing to do with a query...my first instinct is to place it in some other kind of report so that it loops through the employers and adds the employees below them in a sub loop; running the employee query multiple times for each employer.

Is there a name for a query like this so I can look up the technique he is using in a book?  Is it an unnatural thing to do because the actual field headings don't really apply to the rows with the employer records?


texts:
The recommendations I have come across advise that single quotes in SQL Server should be escaped using two single quotes. Thus Ravi's becomes Ravi''s.

Another alternative is to use parameters.

In most of the examples I have seen the words usually have only one quote. Most times it works but I have been unable to make it work while creating a database login.

CREATE LOGIN Ng''ang''a  WITH PASSWORD  'Ng''0ng''0'

Where I am going wrong?


texts: how to minimize the data scanned on my database using index?

texts:
Is there anyone how to minimize the data scanned on my database??
please show me.. using index?

query

        SELECT
        IF (e.channel LIKE 'IAX2%', e.calldate,
            IF(d.channel LIKE 'IAX2%',d.calldate,
                 IF(c.channel LIKE 'IAX2%',c.calldate,
                   IF(b.channel LIKE 'IAX2%',b.calldate,
                     a.calldate ))))
                     AS true_calldate,

        IF (e.channel LIKE 'IAX2%', e.src,
            IF(d.channel LIKE 'IAX2%',d.src,
                 IF(c.channel LIKE 'IAX2%',c.src,
                   IF(b.channel LIKE 'IAX2%',b.src,
                     a.src ))))
                     AS true_source,

        IF (e.channel LIKE 'IAX2%', e.dst,
            IF(d.channel LIKE 'IAX2%',d.dst,
                 IF(c.channel LIKE 'IAX2%',c.dst,
                   IF(b.channel LIKE 'IAX2%',b.dst,
                     a.dst ))))
                     AS true_destination, a.dcontext AS context, a.channel AS trim_channel

        FROM
        cdr a
        LEFT JOIN cdr b
        ON (
        SUBSTRING(a.channel, 1, CHAR_LENGTH(a.channel) - 2) = SUBSTRING(b.dstchannel, 1, CHAR_LENGTH(b.dstchannel) - 2)
        )
        LEFT JOIN cdr c ON (
        SUBSTRING(b.channel, 1, CHAR_LENGTH(b.channel) - 2) = SUBSTRING(c.dstchannel, 1, CHAR_LENGTH(c.dstchannel) - 2)
        )
        LEFT JOIN cdr d ON (
        SUBSTRING(c.channel, 1, CHAR_LENGTH(c.channel) - 2) = SUBSTRING(d.dstchannel, 1, CHAR_LENGTH(d.dstchannel) - 2)
        )
        LEFT JOIN cdr e ON (
        SUBSTRING(d.channel, 1, CHAR_LENGTH(d.channel) - 2) = SUBSTRING(e.dstchannel, 1, CHAR_LENGTH(e.dstchannel) - 2)
        )
        WHERE a.accountcode = 003586 AND
        a.dstchannel = '' AND
        (a.channel LIKE 'LOCAL%' OR a.channel LIKE 'IAX2%')
        ORDER BY a.calldate

here is the query EXPLAIN output

  id    select_type     table   type    possible_keys               key             key_len     ref     rows    Extra
  1     SIMPLE          a       ref     accountcode,accountcode_2   accountcode     4           const   334     Using where; Using temporary; Using filesort
  1     SIMPLE          b       ALL     NULL                        NULL            NULL        NULL    801     Using where; Using join buffer (Block Nested Loop)
  1     SIMPLE          c       ALL     NULL                        NULL            NULL        NULL    801     Using where; Using join buffer (Block Nested Loop)
  1     SIMPLE          d       ALL     NULL                        NULL            NULL        NULL    801     Using where; Using join buffer (Block Nested Loop)
  1     SIMPLE          e       ALL     NULL                        NULL            NULL        NULL    801     Using where; Using join buffer (Block Nested Loop)


texts: Trouble Shooting "Unable to complete login process"

texts:
Issue
On one of our SQL Server witness servers (This is part of our Database Mirroring setup for SQL Server 2008 R2) we have just started getting the below event in the windows event log.

  The description for Event ID 17052 from source MSSQLSERVER cannot be found.
      Either the component that raises this event is not installed on your local
      computer or the installation is corrupted. You can install or repair the
      component on the local computer.

  If the event originated on another computer, the display information had to
      be saved with the event.

  The following information was included with the event:

  Severity: 16 Error:0, OS: 0 [Microsoft][SQL Server Native Client 10.0]Unable
      to complete login process due to delay in opening server connection

  the message resource is present but the message is not found in the
      string/message table

These events are happening at varying times and so far we have had 22 errors starting on 27/08/2015.  I have looked through our change log and I can't see any changes for that day that look relevant.

I have done some investigation and found the following article. This looks at the xml information in the below table; however none of the events in the result set correspond to the times of any of the failures.

SELECT CAST(record as xml) as record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'

To make matters worse the errors seem sporadic with no determinable pattern.

Question
How can I find out what is causing the above events?

Version
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
    Jun 17 2011 00:54:03
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)


texts: How to mount a query to join by searching by last date

texts:
I have the following tables:
TB_PRODUCTS

ID     COD_PROD     Name
===============================================
1       100    Video monitor
2       101    Keyboard
3       102    Mouse

TB_COSTS

COD_PROD    DATE_ATT    COST
===============================================
100        2016-03-01    1.00
100        2016-02-29    2.00
101        2016-03-01    1.00
101        2016-02-29    2.00
102        2016-03-01    1.00
102        2016-02-29    2.00

This is the query that I have:

SELECT
TB_PRODUCTS.ID,
TB_PRODUCTS.COD_PROD,
TB_PRODUCTS.Name,
TB_COSTS.DATE_ATT,
TB_COSTS.COST
FROM
TB_PRODUCTS
INNER JOIN TB_COSTS ON TB_COSTS.COD_PROD = TB_PRODUCTS.COD_PROD
WHERE
TB_COSTS.COST > 0

But the query does not return me the cost of the last date, and what I need is to do a join with the cost of the last date.
How could I filter the Join only taking the cost of the most recent date ?


texts: SSIS data transfers between two remote servers

texts: Let's say I have three servers, ServerA, ServerB, and ServerC.  ServerA has the production database, ServerB has the staging database, and ServerC has the data warehouse.  I've created an SSIS package to transfer data from ServerA to the staging database on ServerB (Stage 1), then from the staging database to the data warehouse on ServerC (Stage 2).  Would it make any sense to have separate SSIS packages for each stage?  Is there network traffic between ServerA and the other two servers during Stage 2 because that's where the job is executed from?


texts:
If anyone is an X-Cart 4 user, or has experience, I am experiencing an issue with the table xcart_variants. It keeps getting "corrupted" or something. It's giving an error Table './elements_xcart/xcart_variants' is marked as crashed and last (automatic?) repair failed

When I try to REPAIR TABLE xcart_variants I get the following error
Can't create new tempfile:

Please help!


texts:
I have data in the form of

 DateTime -- Status  --   Duration Seconds
 12:00:00,   Available,   10800
 15:00:00,   Unavailable, 460

I need to translate that into

 DateTime -- Status
 12:00:00,   Available
 13:00:00,   Available
 14:00:00,   Available
 15:00:00,   Unavailable

The logic to extrapolate the data this way is simple, but translating it into SQL is proving to be difficult. Any assistance would be greaty appreciated!


texts:
I need to store 128 bits unsigned integers into MySQL and I was wondering what is the best data type to store such big numbers.

Right now, I'm using binary(16) but that involves a lot of conversion function pack(/huge number in hex .../).

Is there a best datatype to store a 128 bit unsigned integer?


texts:
I am planning to create FCI + HA solution in the same datacenter, but running into some problems.  I'd really appreciate if someone can provide some guidance.

Here is the current configuration (All nodes are in same datacenter, Win 2012 R2, SQL 2012):

WINCluster1
SQL1 - Part of FCI instance SQLFCI1 (S drive for data, L drive for log, Q drive for quorum)
SQL2 - Part of FCI instance SQLFCI1 (S drive for data, L drive for log, Q drive for quorum)

The above is a standard SQL FCI cluster and it works great.

SQL3 - Standalone box for HA (Separate S drive for data, separate L drive for log, No Q drive)

I added SQL3 to WINCluster1 and setup Node Majority for quorum (Removed Q drive disk witness from quorum) as I have odd number of nodes.

I removed SQL3 from possible owner for SQLFCI1.

Then, I setup AlwaysOn Availability Group AG1, with SQLFCI1 as Primary replica and SQL3 as secondary replica.  The setup is Asynchronous with Manual failover and readable secondary.

When I use SSMS to failover between SQLFCI1 and SQL3 (back and forth), it works fine.
When I use FCI under WinCluster1 to failover between SQL1 and SQL2 for SQLFCI1, it works fine.

But, when I shutdown SQL1, the SQLFCI1 fails over to SQL2, which is perfect, but SQL3 goes offline.  I can remote into SQL3, click on drives, etc, but the cluster resources goes offline.

Same thing happens when my network engineer unplugs NIC cards, simulating NIC fail scenario, from SQL1.  And both cases happens when SQL1 is online but SQL2 is down or NIC unplugged.

Here is the error message under event viewer:
"The cluster service is shutting down because quorum was lost.  This could be due to the loss of network connectivity between some or all nodes in the cluster, or a failover of the witness disk."

And another strange thing is, when I shutdown both SQL1 and SQL2, under WinCluster1, the current owner for SQLFCI1 says SQL3, even though there is nothing shared between SQL3 and SQL1, SQL2.  SQL3 is just standalone box.  It brings down the Windows cluster and nothing works.  Once SQL1 and SQL2 come up, it starts working.

Hope this explains the current configuration and issue.  Thank you so much for your assistance in advance.

Have a wonderful day.


texts: How do I compare large stored procedures?

texts:
I want to compare stored procedures that should be identical in several different databases, by retrieving and comparing  the definitions from sys.sql_modules.

Currently I'm thinking about procedures which are "identical" except that one or other copy has blank lines or other white space at the start or end.  These seem to be added by making a script of a procedure.  I'd like to remove the blank lines and such and compare what's left.  So I could do:

IF ( LEFT(@definition1, 2) = NCHAR(13) + NCHAR(10) )
    SET @definition1 = SUBSTRING(@definition1, 3, 1048576)

That will work if the length of the procedure is less than 1048576 bytes.  But what is the actual limit on length - if any?

I suppose actually I should ask: what is a good way to compare procedures?  (Apart from "Buy the SQL Compare utility from Red Gate.")

Updated

(revised)
I just realised that sys.sql_modules.definition has data type nvarchar(max) and that has a specified length... in bytes, POWER(2, 31)-1, except that that formula doesn't work ;-)  In nchar characters, no more than POWER(2, 30)-1.

So this ought to do it:

IF ( LEFT(@definition1, 2) = NCHAR(13) + NCHAR(10) )
    SET @definition1 = SUBSTRING(@definition1, 3, 2147483647)

Yes, that is 2^31-1, it ought to be 2^30-1, but as long as it's bigger than @definition1 is or can be, I get away with it.  Until they increase "max" to 2^63-1 bytes.  Before there was "max", I used 8000.

And maybe I should include double-checking the DATALENGTH() in case it accidentally gets truncated to the first 8000 bytes somewhere.

As for your wise words about good code management to not have this need in the first place - useful ammunition, thank you (now to extract the bullets from my own hide).  I recognise the value of methodical working practices, even when they have to apply to me, as well.  But a problem is management who let's just say hypothetically just want a report or a result produced -now-, and who have been getting and expecting service like that for many years - and now they've learned to call it "Agile".


texts: Query Plan - Estimated Row Count Very High

texts:
I have a query that populates a temp table with 1692 rows. I had used a table variable but performance was terrible. Now it is improved but still not ideal.

However, I then need to split the TaskName values into separate columns. For this I use APPLY (maybe there is a better way to do this?)

The Execution Plan returns 'Actual Number of Rows' value of over 100,000. Any idea why this is? There are only 1692 rows in the table!

Query plan is below.

https://www.brentozar.com/pastetheplan/?id=rJ6_JkKfQ

UPDATE: Tried below CASE query from @Biju jose but does not give desired output.

Output Required:

Output Returned:


texts: removing specific Key and value from Hstore in postgres

texts:
i have a data which is in Hstore form so i want to select a specific key and value  from data and then delete them,  as i use this query it is find all rows which this key and value exist on it but i want those rows which is only involve this key and value not all

select * from topdog where tags @> 'created_by=>JOSM'

thanks


texts: Basic Design Question about one to many in a table

texts:
Sorry for the basic question but I am new to all of this!

Say I have a table named Runs, and each table has

Run:
  id (primary_key)
  data (String - path to a data file)
  status
  timestamp
  ... etc etc

Another part of the application takes the data and compares it to other runs, so I want a Comparison table.

Comparison:
  id (primary_key)
  tag
  timestamp
  base (foreign_key to run.id)
  reference_file (foreign_key to run.id)

But a comparison will have at least one reference, but possibly many reference. I'd need to tag a comparison to one reference differently than a comparison to many, even if they both contain the same reference file.

Is this the correct approach?

Edit: Clarifying a comparison

A comparison will have one base file, and at least one reference file.

In general two comparisons are run:

First - compare current run against previous run
Second - compare current run against previous 10 runs.

Basically, like this:

status = compare_files(current, reference_files)
status = compare_files(run_70, [run_69])
status = compare_files(run_70, [run_69, run_68, run_67, run_66, run_65, run_64, run_63, run_62, run_61, run_60])

Since both have a different number of reference files, there will be two different results with logs generated.

Eventually I'll care about the logs and status, but for now I want to intelligently map runs with their comparisons. So I can query a run and a comparison table to get all the required info.


texts: MariaDB remote access ERROR 1045 (28000): Access denied

texts:
MariaDB 10.3.27.
Debian 10 Buster.
I've followed the instructions to grant remote access to a user to my remote MariaDB server:

Edited /etc/mysql/my.cnf:
 [mysqld]
 #skip-networking
 #bind-address = <some ip-address>
 skip-networking=0
 skip-bind-address

Created a user:
 CREATE USER 'theuser'@'%' IDENTIFIED BY 'mypass';
 GRANT ALL PRIVILEGES ON *.* TO 'theuser'@'%' IDENTIFIED BY 'mypass' WITH GRANT OPTION;
 FLUSH PRIVILEGES;
 SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';
 +---------+------+
 | User    | Host |
 +---------+------+
 | theuser | %    |
 +---------+------+

Restarted MariaDB:
 $ sudo systemctl restart mariadb
 $ systemctl status mariadb
 ● mariadb.service - MariaDB 10.3.27 database server
    Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
    Active: active (running) since Mon 2021-05-17 01:00:59 -03; 20min ago

Opened the 3306 port:
 $ sudo ufw allow 3306/tcp
 $ sudo ufw status | grep 3306
 3306                       ALLOW       Anywhere
 3306/tcp                   ALLOW       Anywhere

Checked open port locally:
 $ nmap -p3306 remote_ip
 PORT     STATE SERVICE
 3306/tcp open  mysql

But when I try to access from my local machine to the remote server:
$ mysql -utheuser -pmypass -hremote_ip
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'theuser'@'my_ip' (using password: YES)

I'm missing something or there are some errors I can't see?


texts:
I have the following database table, that saves all outgoing emails to students:

    CREATE TABLE [dbo].[tblEmailsSent](
        [id] [int] IDENTITY(1,1) NOT NULL,
        [Sent] [datetime] NULL,
        [SentByUser] [nvarchar](50) NULL,
        [ToEmail] [nvarchar](150) NULL,
        [StudentID] [int] NULL,
        [SubjectLine] [nvarchar](200) NULL,
        [MessageContent] [ntext] NULL,
        [ReadStatus] [bit] NULL,
        [Folder] [nvarchar](50) NULL,
     CONSTRAINT [PK_tblMessages] 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]
    GO

The table currently stores 18,700 records.

I have a number of issues, which I guess might be related:

The table, for some reason, weighs 460 MB - which means I am pretty much hitting the limit of the hosting package. I wouldn't have thought that 18,700 rows would weigh so much. Is there something that can be done about this?
When I load the top X rows from the table, the response is VERY slow. We are talking about a run time of a little more than 2 minutes (in SSMS), for a simple query that looks like this:

SELECT top 500  *
FROM tblEmailsSent
ORDER BY id desc

The following query gives zero records, when I know for a fact (ie, i can see in the table,) that emails were sent during the day of the 18th:

 SELECT id FROM tblEmailsSent
 WHERE  convert(datetime,[sent],103) = convert(datetime,'18/11/2016',103)

I am using Microsoft SQL Server 2005 - 9.00.5000.00.

I am using ntext because i am using Unicode (for Hebrew support).

SET STATISTICS TIME ON for query 2 returns:

(500 row(s) affected).

SQL Server Execution Times:
    CPU time = 141 ms,  elapsed time = 112347 ms.

A SELECT AVG(DATALENGTH(MessageContent)) query returns 23,363.

I changed the MessageContent column to nvarchar(MAX), and ran query 2 again:

SQL Server Execution Times:
    CPU time = 47 ms,  elapsed time = 116726 ms.

...only a few seconds different from before.

The server is remote. It generally responds fairly quickly, but these results for such a simple query are really very very slow.

Using SET STATISTICS IO ON returns:

Table 'tblEmailsSent'.
 Scan count 1, logical reads 17, physical reads 0, read-ahead reads 354,
 lob logical reads 3308, lob physical reads 660, lob read-ahead reads 0.
SQL Server Execution Times:
 CPU time = 62 ms,  elapsed time = 107245 ms.


texts:
I have two tables:

First one contains unique mentees under one mentor, so mentors can have more than one mentee in that table.
Second one contains interactions of mentors with mentees on different dates, so mentos and mentees may appear multiple times.

I'm trying to create a join between those two tables where the result would be:

'mentor_id'|'# of people'|'# of distinct interactions'

This way I would know whom did the mentor advice during a given period, against how many they are supposed to as %.

What I've done..

SELECT INTER.mentor_id, COUNT(DISTINCT INTER.mentee_id), COUNT(f.mentee_id)
FROM INTER WITH (NOLOCK)
INNER JOIN
    (SELECT mt.mentee_id, mt.mentor_id
     FROM mentee_table mt WITH (NOLOCK)
    ) as f
ON f.mentor_id = INTER.mentor_id
WHERE (//period)
GROUP BY INTER.mentor_id

The problem with this is that when viewing the result without any groupings or aggregates, I receive duplicates from the subquery, since there are more records in INTER.

Schema's

mentee_table

|mentee_id|mentor_id
|1        |3
|2        |3
|3        |5

INTER

|mentee_id|mentor_id
|1        |3
|1        |3
|1        |3
|2        |3
|3        |5
|3        |5

In the end I will just use the counts to calculate percentage, such as

COUNT(DISTINCT INTER.mentee_id)*100/COUNT(f.mentee_id) which means the INTER table, when distinct, can only have <= # of people

Thanks

Edit

I ended up doing this query to also get mentors who never had any interactions. I had to use DISTINCT on both counts..

SELECT mt.mentor_id, COUNT(DISTINCT mt.mentee_id), COUNT(DISTINCT INTER.mentee_id)
FROM mentee_table mt WITH (NOLOCK)
   LEFT JOIN INTER WITH (NOLOCK)
   ON INTER.mentor_id = mt.mentor_id AND INTER.mentee_id = mt.mentee_id
GROUP BY mt.mentor_id
ORDER_BY mt.mentor_id


texts:
I want to store 2 coordinate points (latitude, longitude) in a table variable.

I have tried:

declare @coordinates table(latitude1 decimal(12,9),
                           longitude1 decimal(12,9),
                           latitude2 decimal(12,9),
                           longitude2 decimal(12,9))

select latitude,
       longitude into @coordinates
from   loc.locations
where  place_name IN ('Delhi', 'Mumbai')
select @coordinates

It's showing error:

  Msg 102, Level 15, State 1, Line 2
  Incorrect syntax near '@coordinates'.

The result of the select query:

select latitude,
       longitude
from   loc.locations
where  place_name IN ('Delhi', 'Mumbai')

is:

latitude    longitude
28.666670000    77.216670000
19.014410000    72.847940000

How can I store the values in table datatype?

I ran the query SELECT @@VERSION and got the result:

  Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Apr 29 2016 23:23:58 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows 10 Enterprise 6.3  (Build 16299: )


texts: Can I restore a MySQL table from a file (non-sql) backup?

texts:
I am using XAMPP 5.6.23-0 on a Mac.

I have experienced many problems with MySQL, where the server simply refuses to restart after something has happened (not sure which events make it crash). In the end, the only working solution was to re-install XAMPP. The problem is that the new installation does not automatically pick up the databases that were created in the old installation, even if they are in the same physical location on my disk.

The obvious restore method for a MySQL table or database is of course running the restore by importing an earlier SQL backup from phpMyAdmin, but you need to have that backup, and that is impossible to create after the MySQL server dies and refuses to restart. Running a backup after every single database change is not feasible and should not be required IMHO.

I have tried to completely remove the remaining xamppfiles folder before a reinstall of XAMPP, and that works, but of course the databases I have created are not available anymore. I dug out the files from a Time Machine backup, manually added them to the xamppfiles/var/mysql directory and changed permissions to match those of the databases included with the XAMPP package (chmod 770 on the custom database directories in xamppfiles/var/mysql).

Running phpMyAdmin shows that the custom databases and the tables are visible, but when I try to access any of them, there is an error that the table does not exist in the engine. I did add the user account in the new XAMPP installation that has the same name, password and privileges as used in the old installation, so it is not an issue with access privileges. Simply the message that the table does not exist in the engine.

There must be some file(s) in the XAMPP installation that the MySQL server uses to find the various databases and tables. If anyone can point me to that file I can probably dig it out of the Time Machine backup as well and try to make the new installation (same XAMPP version) run with that configuration file to regain access to my custom databases.

Any pointers are welcome. If this cannot be done, also please let me know so I can stop wasting my time on XAMPP and get a stable MySQL server installation instead.


texts: How can I do a differential query (delta plus/minus) telling me what rows are in view A that are not in view B and vice versa?

texts:
I have two views which use radically different means to accomplish what I hope is the same end view. One uses complex query expressions and walks entire giant tables  and is highly expensive, and the other uses a new boolean bit flag which has been added to the database for precisely the purpose of accelerating this view.

I can write this kind of union, and then (in my program code) use this to determine that a row in view A is not in the results of view B, but I'm hoping I can do this entirely in SQL:

Here's the union I came up with:

select trantype,product_code, 0 as type from vwVIEW1 where key = 'DEMO'
union
select trantype,product_code, 1 as type  from vwVIEW2 where key = 'DEMO'
order by product_code, trantype

My old view is fictionally called vwVIEW1 above, and it walks giant database tables to determine its output, whereas vwVIEW2  does a very quick index-lookup on two fields to achieve the same results (select a,b,c from tblUnderlyingTable where tblUnderlyingTable.HeyRickyCanIBeInYourShow=1).

Typical results coming from the above union would in the healthy case, return a pair of rows for each trantype+product_code, plus my invented "type" flag:

trantype product_code  type
-------- ------------  ----
0        MATCHY        0
0        MATCHY        1
0        NONMATCHY     0

Out of the above set of data, I would want to only see the row NONMATCHY, because it is in vwVIEW1 but not in vwVIEW2.  All fields in the union above are non-null. No nulls are ever expected in trantype, or product_code.


texts: Azure SQL - is it possible to assign CREATE PROCEDURE permission for the master database?

texts:
I am trying to add a stored procedure to the master database (I deliberately am trying this for the master db) on Azure SQL. Is this possible? I suspect the answer is "no", but I'd like to see some confirming documentation on it if that is the case.
What I've tried so far:
Here's the procedure (just as a test):
CREATE PROCEDURE dbo.blah AS RETURN 0;

I've tried the above using the admin login, and it failed:
Msg 262, Level 14, State 18, Procedure blah, Line 2 [Batch Start Line 21]
CREATE PROCEDURE permission denied in database 'master'.

I then tried to create another login and user and then assigned them to the "dbmanager" role and tried the create procedure again - same failure.


texts: Error Updating Record on Table with Partition on a Read-Only Filegroup

texts:
We are working to partition our data warehouse (SQL Server 2012 Enterprise): moving old records we don't expect to change to a read-only filegroup on a monthly basis. We're getting errors that records cannot be updated even when the record is in the read-write filegroup and would not move to the read-only partition (we are not updating the partitioning column) due to the update.

The partitioning column is ETL_VERS_END_DTM. The following update statement fails as if the row needed to be moved to partition 1 (values less than 1899-12-31 23:59:59 which is expected to be an empty partition).

UPDATE  DW_STAGE_PHOENIX.dbo.STG_PHX_LN_ACCT_INT_OPT
SET     ETL_JOB_SEQ_NUM = 1027140 ,
        ETL_IUD_CD = 'D' ,
        ETL_UPD_DTM = CONVERT(DATETIME, '03/16/2016 01:01:17')
WHERE   ETL_VERS_START_DTM = CONVERT(DATETIME, '12/27/2014 00:26:24.137')
        AND ACCT_NO = '5001194157'
        AND ACCT_TYPE = 'ILN';

Error:

  Msg 652, Level 16, State 1, Line 1 The index
  "PK_STG_PHX_LN_ACCT_INT_OPT" for table "dbo.STG_PHX_LN_ACCT_INT_OPT"
  (RowsetId 72057594415022080) resides on a read-only filegroup
  ("ARCHIVE"), which cannot be modified.

Explicitly setting the partitioned column to its own existing value or putting anything in the where clause that gets us the correct partition elimination removes the condition that causes this error. Neither solution is considered a 'fix' for the issue by the vendor or our data warehouse admin. What is causing SQL Server to attempt to move the record if the column value is not changing?

UPDATE:
So my problem is me misunderstanding that if the execution plan thinks it might touch the read-only partition it will error out (even though no records have been touched). The problem ultimately is that the database was not built originally (or loaded) with a partitioning as a consideration as well as lack of desire to customize update processes by our DW team. Partitioning is being built in to manage maintenance times as the database continues to grow due to a business requirement to never delete anything ever. [Did that sound a little bitter?]


texts: Master updates Slave but not Master itself


texts: Restoring postgresql 9.4 crashed database

texts:
Can anyone help me to restore my crashed database. Is there a way to use pg_resetxlog. I read a number of documentations telling about it but i'm always seeing the same log when i restart my postgresql service

2016-04-11 8:47:59 p.m. GMT [1710-1] LOG: the database system was interrupted during a restore at 2016-04-11 11:04:19 GMT
2016-04-11 8:47:59 p.m. GMT [1710-2] TIP: This probably means that the data has been corrupted and you
need to use the last backup for recovery.
2016-04-11 8:47:59 p.m. GMT [1710-3] LOG: the database system was not shut properly; restoration
automatic current
2016-04-11 8:47:59 p.m. GMT [1710-4] LOG: the re-execution starts at 4 / 32DF8E90
2016-04-11 8:47:59 p.m. GMT [1710-5] FATAL: could not access the transaction status 5598
2016-04-11 8:47:59 p.m. GMT [1710-6] DETAIL: Could not read the file "pg_multixact / members / 0000" at offset 57344: Success
2016-04-11 8:47:59 p.m. GMT [1710-7] BACKGROUND: xlog redo create mxiD 5598 offset 11603 Nmembers 2: 6486476 (keysh) 6,486,482 (keysh)
2016-04-11 8:47:59 p.m. GMT [1709-1] LOG: launch process (PID 1710) exits with exit code 1
2016-04-11 8:47:59 p.m. GMT [1709-2] LOG: start cancellation due to a failure in the launch process

I tried to fix my problem like this :

First one, saving my data in a external drive

  scp -r /var/lib/postgresql/9.4/main /media/myhdd/data

Second One, as postgres user i run the pg_resetxlog command like this :

./pg_resetxlog -f /var/lib/postgresql/9.4/main/

The command result after pg_resetxlog is

  Resetting the transaction log

But when i try to restart my postgresql service i get the same error message. Am i doing the right command whith pg_resetxlog ? I'm new on postgres


texts:
I have a VM with Ubuntu 16.04.4 LTS. I use it for wordpress sites, so it haves mysql, nginx and php. I migrated from Azure to Vultr. For doing so I backed up all the databases in Azure and restored them into my VM at Vultr.

That was a couple of months ago. Everything was working fine until today, when I wanted to add a new wordpress site. As usual I started by configuring the database for mysql, so I tried to loging to mysql and my issues started. A problem with the socket appeared, and then I ran mysqld_safe --skip-grant-tables & to be able to access mysql.

I was able to access it and to fix my issue I ran update user set plugin="mysql_native_password"; and exited mysql. After struggling a while some other sockets errors appeared.

Reading on StackOverflow an upgrade was recommended, therefore I updated my database and now this is my current issue. I can login with mysql -u root -p. But, when I enter use mysql; I get: ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Then, when I run ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxxxxx'; i get ERROR 1805 (HY000): Column count of mysql.user is wrong. Expected 45, found 46. The table is probably corrupted

The password column doesn't exists in the user table, so instead of the previous statement I used update mysql.user set authentication_string=password('xxxxxxx') where user='root'; and I got ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. so it seams to me that I'm in a dead end.

This is my current error when I run mysql_upgrade --force -uroot -p...mysql_upgrade: Got error: 1862: Your password has expired. To log in you must change it using a client that supports expired passwords. while connecting to the MySQL server
Upgrade process encountered error and will not continue.

I appreciate any help you can give me.

UPDATE

Looking at the user.frm files I noticed that the column that the is_role column is the extra column in my user table. I tried to edit the user.frm file but I wasn't able to make it work.


texts: New user creation option not available in SSMS sql server 2106 , how to enable?

texts: i need to create New user through SSMS in SQL server 2016


texts: Connecting to server which located remotely throws timeout exception

texts:
Below are the steps which I follow while connecting manually:

Connect to VPN
RDP to the remote machine using my credentials
Open SQL Server Management Studio
Connect to Server > database engine, Server Name > "Name", Authentication >  Windows Authentication
Run query

And the connection string that I am using is:

ConnectionString = "Data Source =tcp:<IPAddress of RDP Machine>\<ServerName>;
Initial Catalog =<DBName>; User Id = <Username>;
Password = <Password>; Trusted_Connection = True;
Integrated Security = SSPI; Connect Timeout = 30; Encrypt = False; TrustServerCertificate = False"


texts: Best way to get last identity inserted in a table

texts:
Which one is the best option to get the identity value I just generated via an insert? What is the impact of these statements in terms of performance?

SCOPE_IDENTITY()
Aggregate function MAX()
SELECT TOP 1 IdentityColumn FROM TableName ORDER BY IdentityColumn DESC


texts: I have recently joined a team in my corp office that has mysql as a part of their work profile. Though the mysql work not often in this team, but when they do it comes in big scale. The main mysql technology part that this team works on is High availability. Please do not mix high availability with clustering. Which was the mistake i understaood when i heard this first time. By high availability i mean db replication, master/slave/relay master, etc setup. I won't be doing any query optimization or any of those closely code related STUFF. But more of database administration stuff like looking for replication failures and fixing it and setting up replication, troubleshoot issues maybe like slow query (rarely). Please suggest me a book that concentrates on this part. I have some experince with mysql query language like creating db & tables, looking at tables schema and db priveldges and grants priveldges and backing up and restoring db with mysqldump and writing small select  queries and like for mainly find out where it is going wrong or use it in my report generation script from bash command line. I am mainly a bash scripting and apache httpd and tomcat expert and new to this filed. Although the httpd and tomcat have always maintanined a close relation with mysql and db in general . But where i worked previously had huge db and were maintained by a special dedicated team of experts who work solely on db. And that was in oracle and their size was in tera bytes and had advanced structure like oracle rack and real time replications setup, etc and i wan't expected to touch any parts. I was concerating on the app tier and web tier. So can you guys suggest me a way to quickly learn the important stuff of mysql and know the internals or more subtile part as we move on. A book or a online course or a tutorial site. Ah forgot to mention we are using mysql 5 and 4 in some *nix machines. And these are huge setup with lots of machine in many DC. As i said before my primary filed of expertise is bash scripting and web and app layer stuff. So please guide. I am not familiar with windows though worked on AD (and to some extent on SCCM) related stuff for sometime and i am certified RHCE (current and valid). Thanks. ;)


texts: MariaDB 'You do not have privileges to manipulate with the users!'

texts:
Apache/2.4.41
PHP 7.3.9
Windows 10 64bit

I have been using XAMPP without issue for months now, and this morning when I turned on my PC adn tried to accedd my databases via HeidiSQL I received some strange errors. They all seem to relate to users / permissions. I haven't (or don't recall!) changed any database user accounts, so this is confusing me.

Using HeidiSQL I can access my localhost and view the available databases. I can create a new database. When I click on the 'manage users' tabe the program freezes and i have to force close.

Using Phpmyadmin I can't get any further. Except there's a message displayed;

  You do not have privileges to manipulate with the users!

I have a root user with no password as this is a local testing environment.

The whole localhost database setup is running painfully slow. My localhost has 25 databases, mosts Joomla / Wordpress so not huge.

I've checked that everything is running on the correct port, and I don't have multiple instances opened. I've restarted my PC numerous times, no change.

The mysql error log doesn't have anything unusual.

Is there anything else I should be looking at?


texts: Limit MySQL server login attempts without fail2ban

texts:
I would like to limit login attempts via phpMyAdmin to protect against brute-force attacks. I found solution above, but it is based on fail2ban.

[mysqld-iptables]
enabled  = true
filter   = mysqld-auth
action   = iptables[name=mysql, port=3306, protocol=tcp]
       sendmail-whois[name=MySQL, dest=root, sender=fail2ban@example.com]
logpath  = /var/log/mysqld.log
maxretry = 5

Are there any other, maybe "native" solution for this purpose? Should I use maybe access restriction instead of limiting?