texts:
I have a table with around 2 million records in it. I created a spatial index, using the defaults other than the bounding box. I've been noticing that some queries are extremely fast, and some are extremely slow. The determining factor appears to the size of the polygon used in the query.

On larger search areas, using WITH(INDEX(SIX_FT5)) slows down the query considerably (from 0 seconds, to 15+ seconds). On smaller search areas, the exact opposite it true.

Here's some of the queries I'm testing with:

Fast:

SELECT TOP(1000)
  *
FROM [FT5]
WHERE
  shape.STIntersects(geometry::STGeomFromText('POLYGON ((-133462.805381701 -668610.241000959, 2934415.68824241 -668610.241000959, 2934415.68824241 2200521.65831815, -133462.805381701 2200521.65831815, -133462.805381701 -668610.241000959))', 2264)) = 1

Slow:

SELECT TOP(1000)
  *
FROM [FT5] WITH(INDEX(SIX_FT5)) -- Index hint is the only difference
WHERE
  shape.STIntersects(geometry::STGeomFromText('POLYGON ((-133462.805381701 -668610.241000959, 2934415.68824241 -668610.241000959, 2934415.68824241 2200521.65831815, -133462.805381701 2200521.65831815, -133462.805381701 -668610.241000959))', 2264)) = 1

Why is that WITH clause slowing things down sometimes?


texts: How to use GROUP BY in an efficient way without losing attributes?

texts:
I am working with PostgreSQL.

My idea is to group players who appear more than 3 times in table-a.

table-a = <player_id, year_world, c, d, e>

With the first subquery I get player_id and the number of times that player played a world cup.
And with the NATURAL JOIN I recover the year or other attributes in the tuples that player appears.

The following query works but I want to optimize it:

SELECT player_id , year_world
FROM    (SELECT player_id , count(player_id ) player_id_count
        FROM <table-a>
        GROUP BY player_id
        HAVING count(player_id ) > 3) playersMoreThree
NATURAL JOIN <table-a>

I would like to know if it is possible not to use the NATURAL JOIN to obtain the other attributes of the table. Because when I put the rest of the attributes I want to project into SELECT and in theGROUP BY I get an empty table.

  Keep in mind that the attribute that is added to the SELECT of the subquery of the GROUP BY has to be determined by the attribute that we are grouping. That is, for each player_id value, I can always have the same value of the new attribute, if for a same value of player_id I have different values of the new attribute, this is not determined. For add an attribute that is not correlated by what group I use NATURAL JOIN or another solution.

The following query without NATURAL JOIN does not work for me:

SELECT player_id, count(player_id ) player_id_count, year_world, c, d, e
FROM <table-a>
GROUP BY player_id, year_world, c, d, e
HAVING count(player_id ) > 3

What would be the most efficient way in DML?


texts: How to create an object and then alter it

texts:
I'm using sql server 2012:

I would like to script out creation of an object(if it doesn't exist first), and then alter it.

I'm trying to avoid the usual drop/create as it would impact stats.  It also has to do with how we're scripting out changes to the DB.

What I have:

Go
If object_id(N'My_Function', N'FN') IS NULL
Begin
Create Function My_Function
(
    @SomeParameter int
)
Returns int
As
Begin
    -- Intentionally empty
End

 End

Go
-- A header/comments
Alter Function My_Function
(
@SomeParameter int
)
Returns int
As
Begin
Declare @returnValue int
    -- A bunch of sql code
Return @returnValue
End
Go

The problem I'm having is "Create" must be first in the script(or first after a "GO"), but I can't put a "GO" inside an "If" conditional.  Is there a way to conditionally create something if it doesn't exist, and then alter it later?


texts:
Find when a specific database went offline and the reason for same and when did it recover back.

All I know there is a SCOM alert set for the server and I got a notification as Database is offline and later when I checked I found it as online. So, I want to check at what time it went offline and how and when did it come online?


texts: Why does Mongo query hang for a long time when creating index?

texts:
We are migrating our single Mongo instance to Mongo cluster with sharding. I haven't started sharding yet. However, I found out when I try to add an index, I can't query any collection anymore. We don't have this issue in our current Mongo instance. At our current Mongo, we can apply indexing and query the collection at the same time.

This is the command I run to create index on new cluster:

mongos> db.lc_data.ensureIndex({"name": 1})

Now, I open another terminal and query the collection or other collections in the same database.

mongos> db.lc_other.find()

The query will hang!  Why does this happen?


texts: mongodb version difference between config and mongos

texts: In my mongodb config server has 3.4 version and mongos has 3.6 version but i am unable to start mongos service should both be same version as 3.6 then i have rollback both to 3.4 version now the mongos service started.my doubt is the config server and mongos server should have same version as 3.6 to start the mongos service


texts: SQL Server does not failback to the primary node

texts:
We are running SQL Server 2008 R2 on a Windows Server 2008 R2 two-node cluster.

My choice of machine is primary, and the secondary is just a low-end machine for contingency planing.

We had a failover the last day during night time and when I came to the office the next morning, I noticed that SQL Server service did not failback to the primary machine although it came back online.

All settings seems to be in place:

Attachment 1 shows the general tab of SQL Server service properties dialog box from Server Manager - > Failover Cluster Manager. Both machines are checked, primary being at the top.

Attachment 2 shows failover tab of the same dialog box. Immediate failback is activated.

How can I modify these settings so that whenever the primary comes back, system will failback onto it.


texts: When is Service Master Key Exactly generated?

texts:
As it is created automatically, on Microsoft official source it says during setup.

but in the 70-764 official exam prep book page 18, also there is another non official blog which say

  Any time SQL Server has to protect one of those entities(DMK or Linked Server Login), it
  automatically creates a new service master key, if none already
  exists.

So I see a contradiction here. If it is created during a setup then it should not wait for some objects to be created, and if it is created after the setup, then it is not right to mention about setup generation. Should we correct the information in the blog?


texts: How can I generate call stack on error on the fly

texts:
I have an issue with an index on a Sql2k server:

Event Log shows the following errors happening:

Date        2/14/2012 9:54:19 AM
Log     SQL Server (Current - 2/14/2012 10:26:00 AM)

Source      spid111

Message
Error: 644, Severity: 21, State: 5

and then ...

Date        2/14/2012 9:54:19 AM
Log     SQL Server (Current - 2/14/2012 10:26:00 AM)

Source      spid111

Message
Could not find the index entry for RID '16958f0b001e00020000' in index page (4:964157), index ID 74, database 'db'..

I've been chasing this error and have already ruled out http://support.microsoft.com/kb/822747

Then I found the following forum discussion:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60518

My question, I guess, is whether or not anyone knows this "undocumented way to turn on dumping for a particular error." (last forum entry).

Thank you, in advance.


texts: bcp in of datetime type data results in an "Invalid date / time format"

texts:
I am familiarizing myself with the bcp utility, and I have a large table containing datetime data awhich I bcp out in several ways: native, character, delimited.

I then bcp in to a truncated table with the same columns/format as the original table, however, I obtain errors like:

----------------
Starting copy...
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid date format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid date format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid date format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid date format

4 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 15     Average : (266.67 rows per sec.)
----------------

And for the rows that do get copied, the other columns contain garbage data.

If I do the bcp out/in with data in other datatypes, everything seems okay?

How can I make this to work?


texts: SELECT xyz INTO #Temp from SSMS is 5x longer in one environment than another

texts:
First time posting, I hope I'm doing it right.

I'm a "dba by accident" who is learning a lot but admittedly still has a lot more to learn. Here is my brain dump:

Prod server has 5 million rows. I copied those rows to a Test server.
Using SNAPSHOT or READ UNCOMMITTED produce different elapsed times, but the ratio between Prod and Test is always ~5:1 (14 minutes:3 minutes with SNAPSHOT, 4 minutes:sub-1 with READ UNCOMMITTED)
Using STATISTICS IO ON reveals that most of the reads on Prod are logical (~90%) while none of the reads on Test are logical. I think that should mean Prod would be faster if anything.
Most of the time appears to be going to waits of type "PAGEIOLATCH_SH."
I don't think different statistics can matter, since no index should be used on an unfiltered, unjoined, unsorted query.
SELECTing the top n rows on both servers reveals a very linear scale... the ratio is always ~5:1.
The comparison is not apples-to-apples, but I think the only relevant differences are activity and storage.
I have performed the comparison when activity was next to nothing, and saw the same results.
I discovered that autogrowth was set to 1MB (ugh), which I have since changed but I wonder if this has led to fragmented storage at the file system level.
The database is on a separate volume from the Windows installation, but it does share a volume with tempdb and with a FILESTREAM store, which I assume could also lead to fragmented storage.
The "optimize drives" windows utility says the volume is "OK (98% space efficiency)" which seems to suggest that maybe file system fragmentation isn't a problem.

Questions:

Are my thoughts and assumptions above correct?
Is there anything I'm blatantly overlooking in my thought process?
What are good candidates for the next steps in this troubleshooting process?
What other information can/should I have provided?

Thank you for your attention and help!

EDIT: Added rest of time information in 2nd statement.


texts:
Good day All,

I have a 1000 text files that each contain 1000 insert into statements. What is the most efficient way to read and execute each insert statement into a MySQL DB table?

Thanks for any assistance here.


texts: How can I provision more CPU from VMware when MAXDOP is set to 1?

texts:
We have a BizTalk Server group hosted over seven servers, all hosted in VMware.  This group comprises four BizTalk application servers and three SQL Server instances.
As per Microsoft's out of the box configuration of BizTalk, one of the SQL Server instances in the group has its MAXDOP intentionally set to 1.  According to Microsoft, this setting must not be changed.
We believe that some of the queries running on this SQL instance may be CPU-bound, and would like to consider whether we can boost the CPU resources of this particular virtual machine.
My understanding is that adding additional vCPU cores would be pointless: MAXDOP is set to 1, so any queries constrained by CPU would not be able to make use of any additional CPU cores because they may not run parallel operations.
Is it possible to do anything in this situation?  My assumption is that the clock speed of the vCPU in the virtual machine is the same as the clock speed of the physical CPU in the VMware host, so there is no way of increasing that.

Are all of my assumptions above correct?
If so, does that mean there is no way to boost the CPU available to these queries?
Is there anything else we can do?


texts: Reinstall MySql but keep database tables and data

texts:
Please help!

There are server issues and MySql is no longer running on our server (Ubuntu).  The service is not recognized and needs to be reinstalled.  Unfortunately, the database has not been backed up for 48 hours and that is a lot of information.

How do I reinstall MySql AND keep all my database data?  Please note - I can't access mysql at all.  I can't use command line mysql nor phpmyadmin.

Thanks in advance and let me know if I am missing important details.


texts: SSRS table populating data into a field that should be blank

texts:
I am having trouble resolving an issue. I have a table on an SSRS report. The query for the table gets the definition, the total count of that definition and calculates the percentage of all the counts. However, as you can see in the image, one field is being populated with another fields data when it should be blank. Are there any SSRS experts out there who can lend a hand?

Here is the query:

SELECT fundingyear
    , loan_type
    , COUNT() AS total_lntype
    , COUNT() * 1.0 / SUM(COUNT(*)) OVER (PARTITION BY fundingyear) as lntype_perc
FROM vw_LoanRpt
WHERE loan_type not in ('Line of Credit')
GROUP by fundingyear, loan_type


texts: Is dirty-read a problem in an always-commit database?

texts:
Is this statement true:

  If a DB engine disallows rollback and always uses a redo log to enforce commits, then dirty-reads never happen (because all changes will be commited finally).

If so, are there any use cases where dirty reads can be avoided by disallowing rollback?

The only obstacle I can think of is for a transaction to violate some constraints so that commits always fail. But there may be some use cases where such constraints don't exist at all.


texts: Applying Transaction Logs to an existing Database

texts:
I am wondering if this possible, I can not seem to apply another transaction log.

I have restored the Full database backup, the differential backup and my last Transaction Log WITH RECOVERY. If later on I need to apply the next Transaction Log in sequence to it, what do I need to do?

I tried applying the Transaction Log and it states:

  The log or differential backup cannot be restored because no files are ready to rollforward.

For each time I need to add a new Transaction Log, do I have to go through the steps of restoring the backup database, restoring the differential database and all transaction logs again?


texts: Is there any special meaning for numbers in braces like {0} or {1} in LDIF files of OpenLDAP?

texts:
I'm playing a bit with OpenLDAP.

In the configuration LDIF files there are often numbers in braces like {0} or {1}, e.g:

dn: olcDatabase={0}config,cn=config

or

olcAttributeTypes: {0}( 1.3.6.1.1.1.1.2 NAME 'gecos' DESC 'The GECOS field;
 the common name' EQUALITY caseIgnoreMatch SUBSTR caseIgnoreSubstringsMatch
 SYNTAX 1.3.6.1.4.1.1466.115.121.1.15 SINGLE-VALUE )
olcAttributeTypes: {1}( 1.3.6.1.1.1.1.3 NAME 'homeDirectory' DESC 'The absol
 ute path to the home directory' EQUALITY caseExactIA5Match SYNTAX 1.3.6.1.4
 .1.1466.115.121.1.26 SINGLE-VALUE )
olcAttributeTypes: {2}( 1.3.6.1.1.1.1.4 NAME 'loginShell' DESC 'The path to
 the login shell' EQUALITY caseExactIA5Match SYNTAX 1.3.6.1.4.1.1466.115.121
 .1.26 SINGLE-VALUE )

Do the {0}, {1} and so on have any special meaning (like parameters) or are they just strings?


texts: How do I unit test queries which are based on dates from now?

texts:
I've had a hard time making unit test for most of my queries because they are tied very closely to current dates and data which is added to the database could modify the result of the unit test.

For the most part I've been able to avoid problems by relying on a second version of the database filled with immutable dummy data. So my unit tests can go on with expected results without much of a hitch.

But there are some queries that I still don't know what to do with. I have a query that fetches two columns, a date and an associated value from a table based on:

WHERE Date > Now()

So my enemy here is that tomorrow isn't today. I am using the actual table not a dev version of this table for the test as data entered in this table is sensibly static and shouldn't be changed once added. It's like a reference table so to speak.

If I were to make an immutable dev version of the table then my problem would be that eventually the table's dates will in two years time pass the latest date within the table and thus result in zero records found.

And regardless of whether I had a dev version or a real version, the results for this table will always change based on the current date.

Why does an answer come only once I have finished writing my question out. A thought just occurred to me, which I must say, I want to avoid, but it seems to me that in order to make  a unit test function, I would have to have my unit test generate the table before running the query. But then, do I write a unit test for the version of the unit test that populates that table??

Is there a more sensical way of achieving this goal?

Conclusion

All the answers are valid. The one I selected as the accepted answer is the one I believe to be closer to a standard tdd way (a purist viewpoint), however it's more complex and annoying. The other answers though are very simple and nice, should not be overlooked. In fact the little database trick that RolandoMySQLDBA mention is genious for mysql tests.

Since I'm working with Access, For now what I am doing is making a default db which will be programmatically appended to the dev tables which are static. When time permits that table will have it's own append table that will alter the dates to work with the now() query.

For now() I guess that will do, time permitted.


texts:
I have a couple triggers that automatically update invoice data and set some statuses in the database.

However, it appears that if 2 Triggers have variables that are named the same, they get overwritten when one trigger performs an update the causes the other trigger to run.

For Example,

My BEFORE INSERT trigger on my payments table sets the variable @amountPaid

SET @amountPaid = @totalPayments + NEW.amount

Sometimes the before insert on payments will trigger an update to the main record which fires my BEFORE UPDATE trigger which selects the total payments into @amountPaid for it's own conditionals. However, at this point @amountPaid is 0 from the this perspective because the insert on payments hasn't completed, but this isn't important. What's very odd is that the @amountPaid variable in the payments trigger is now 0.

So now I'm curious as to how scope works in MySQL?


texts:
SSCCE:
The following script:

$ cat test.sql
CREATE TABLE public.foo (
loop INTEGER
);

CREATE OR REPLACE FUNCTION public.foo_fun(loop INTEGER) RETURNS BOOLEAN AS $$
    SELECT TRUE;
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION public.foo_tr_fun() RETURNS TRIGGER AS $$
DECLARE
    fRV BOOLEAN;
BEGIN
    SELECT public.foo_fun(NEW.loop) INTO fRV;
IF fRV THEN
    RETURN NEW;
ELSE
    RAISE EXCEPTION 'bar';
END IF;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER foo_tr AFTER INSERT OR UPDATE ON public.foo
FOR EACH ROW
EXECUTE PROCEDURE public.foo_tr_fun();

... which I run with:

psql -v ON_ERROR_STOP=1 --quiet -X -U some-user -d some-db -f test.sql

... when I then try to insert a row into foo (from psql):

$ psql -U some-user some-db
psql (9.1.14)
Type "help" for help.

RegTAP=> SELECT * FROM public.foo;
 loop
------
(0 rows)

RegTAP=> INSERT INTO public.foo(loop) VALUES(0);
ERROR:  record "new" has no field "loop"
LINE 1: SELECT public.foo_fun(NEW.loop)
                              ^
QUERY:  SELECT public.foo_fun(NEW.loop)
CONTEXT:  PL/pgSQL function "foo_tr_fun" line 5 at SQL statement
RegTAP=>


texts: Should date formats be specified in SQL statements?

texts:
I see code from developers using implicit date conversion.  I would like a definitive answer to why they should not do this.

SELECT * from dba_objects WHERE Created >= '06-MAR-2012';


texts: Hourly Transaction Log files size not consistent - is this Differential backup file?

texts:
I am new to DBA task here.
I am trying to restore database with transactional logs (in SQB file format)  that our company get 24 times each hour daily thru FTP site, and then I have to convert them to BAK file format using "RedGate's SQL Backup" application.
Then, I am planning to restore each transactional log with "NoRecovery" as we would have to restore each hour.
I found there is a pattern of file number "..04" which is really big compared to other hourly transactional logs.
I did not get specific instruction regards to this file "..04" from the vendor's documentation who provides data.

I am curious whether I should treat this particular file as a regular transactional log or is this like "differential backup file" that I have treat it differently?


texts: Know which table is READ / UPDATE / INSERT / DELETE

texts:
Context : I have a legacy PostgreSQL database (currently 9.5 but can be any newer version) and a tons of script / program acceding it.
All this script can do various operations on the table's row (READ, INSERT, UPDATE, DELETE).
Problem : I don't know which script do what on which table, and it become really problematic when I when to modify some table structure (like make the primary key a SERIAL and not a plain bigint with "SELECT MAX(id) + 1" to have the next id ...). I have also some "ninja" script that connect to the database using god know forsaken login/pwd, and for legacy reason, I can't just change the pwd.
What I want to do : I want to know which table are "used" by wich connection (I can know which program do which connection).
So far, I tried to apply a trigger on every table that I want to supervise.
The main idea is to simply insert into a table "trace" that the "pg_backend_pid()" have do a "INSERT" into the table "foo" if the query
"INSERT INTO foo (bar) VALUES (10);"
But a trigger on a SELECT event doesn't exist, so it's not enough.
I activated all the logging that PostgreSQL 9.5 can offer
extract of postgresql.conf

- What to Log -
debug_print_parse = on
debug_print_rewritten = on
debug_print_plan = on
debug_pretty_print = on
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_error_verbosity = terse
log_hostname = on

But I can't find a way to get what I want.
There is the query plan that seem promising, but this "thing" is so frickkin' huge that I can't begin to understand how to read it (also, I don't find any documentation even is the EXPLAIN keyword documentation on how to read that !).
So I begin to be clueless.
Is there a way to do it ? Is the query plan the good lead ?


texts: MySQL trigger to update a key if record exists

texts:
I'm trying to build a trigger in MariaDB to follow this logic:

1. If the primary key val exists, increment the counter column
2. Else, insert the primary key into the table

Here is my prosed trigger:

delimiter //
CREATE TRIGGER volume_manager BEFORE INSERT ON individual_key_log
    FOR EACH ROW
    BEGIN
        IF NEW.reference_key not in (
            select *
            From individual_key_log
            where (NEW.reference_key = 'test')
        ) THEN -- MISSING THEN
           CALL `Insert not allowed`;
        END IF;
    END;
//
delimiter ;


texts: Is it possible to rename the columns of a resultset?

texts:
I have two tables joined on an ID.

they might look like this:

table_overview
| id | version | description |
------------------------------
| 1  | 1.2.3   | new stuff.. |

table_files
| id | type    | file_name   |
------------------------------
| 1  | TEXT    | as.txt      |
| 1  | IMG     | abc.png     |
| 1  | AUDIO   | def.wav     |

Where the primary key of table_overview is id and the primary key of table_files is id and type.

I want to return a result set where each row contains all of the information, but replace the heading file_name with the type instead, so a row in the result set would look something like this:

| id | version | description | text   | img     | audio   |
| 1  | 1.2.3   | new stuff.. | as.txt | abc.png | def.wav |

Is something like this possible? Or am I better off simply creating the table that way in the first place.
The initial idea was that it should be easy to add new types in the future.


texts: How to disable duplicate entry checks in MySQL workbench?

texts:
I am trying to import a large database dump (~30 GB) with millions of rows. There are some duplicate rows in there for sure, and I'd like MySQL to just skip those rows and move on without giving me the following error:

ERROR 1062 (23000) at line 23: Duplicate entry '31' for key 'PRIMARY'


texts: Why does PK constraint require a separate index when I already have a suitable unique clustered index?

texts:
This is part of table definition from here:

CREATE TABLE [dbo].[JobItems] (
[ItemId]            UNIQUEIDENTIFIER NOT NULL,
-- lots of other columns
CONSTRAINT [PrimaryKey_GUID_HERE] PRIMARY KEY NONCLUSTERED ([ItemId] ASC)
);

CREATE UNIQUE CLUSTERED INDEX [JobItemsIndex]
ON [dbo].[JobItems]([ItemId] ASC);

This is some legacy design, so please don't ask "why". Anyway when I look up the list of indexes I see that there're two indexes - one of them is JobItemsIndex and the other is PK-GUID-HERE and they are both for JobItems table.

My question is...

Why is there a need for a separate index to maintan the PK when I already have JobItemsIndex which is unique and includes the very same column and so suitable for maintaining the PK constraint?


texts:
I am trying to run an RMAN backup that is either hung or is taking a very long time. Some background:

I inherited this database, and I am unfamiliar with Oracle, as I am an SQL Server guy, so please point out / forgive any basic gaps in my knowledge
This database is large but infrequently-used, and is loaded up with external data for analysis.  There are no transactions.  Therefore, the database is in NOARCHIVELOG mode.
I need to do a one-time backup for now, so I think I don't need a recovery catalog.
I'm not quite sure of the database size, but it is on the order of 1TB

Here's what I did, cobbled together from various sources on the internet:

> rman target mydatabase

shutdown;
startup mount;
run {
    allocate channel ch1 device type disk format 'C:/Backup_Folder/%U';
    backup format 'C:/Backup_Folder/db_%t_%s.bk' (database);
    release channel ch1;
}

The backup seems to be stuck on channel ch1: starting piece 1 at 01-MAY-15

RMAN creates a tiny (90K) file in C:/Backup_Folder but then nothing else has happened for over an hour.  Do I just wait?  Am I doing something wrong?


texts: Is using SUM on multiple columns allowed in general SQL?

texts:
I had a question in my GATE Mock Test Paper that was as follows:

Which of the following can appear in position marked as [..]?
"Select [..] from R group by A, B"
I. SUM(C+D)
II. A, Min(C)
III. A,B, Min(C)

I answered all of the above, but my friends and answer key stated that only II and III are correct. I ran the query on mysql with no problem, and saw this code listed on few websites as well.
So is this query valid for general SQL, or it is implemented only by certain applications such as MySQL?


texts: Alternative to MakeValid() for spatial data in SQL Server 2016

texts:
I have a very large table of geography LINESTRING data that I'm moving from Oracle to SQL Server.  There are a number of evaluations that are executed against this data in Oracle, and they will need to be executed against the data in SQL Server, too.

The problem: SQL Server has tighter requirements for a valid LINESTRING than Oracle; "The LineString instance cannot overlap itself over an interval of two or more consecutive points".  It just so happens that a percentage of our LINESTRINGs do not meet that criterion, which means that the functions we need to evaluate the data fail. I need to adjust the data so that it can be successfully validated in SQL Server.

For example:

Validating a very simple LINESTRING that doubles back on itself:

select geography::STGeomFromText(
    'LINESTRING (0 0 1, 0 1 2, 0 -1 3)',4326).IsValidDetailed()

24413: Not valid because of two overlapping edges in curve (1).

Executing the MakeValid function against it:

select geography::STGeomFromText(
    'LINESTRING (0 0 1, 0 1 2, 0 -1 3)',4326).MakeValid().STAsText()

LINESTRING (0 -0.999999999999867, 0 0, 0 0.999999999999867)

Unfortunately the MakeValid function changes the order of the points and removes the third dimension, which makes it unusable for us.  I'm looking for another approach that solves this problem without reordering or removing the 3rd dimension.

Any ideas?

My actual data contains hundreds/thousands of points.


texts: Speed up PostgreSQL logical decoding plugin

texts:
I'm writing a new logical decoding output plugin for PostgreSQL inspired by wal2json.  But mine produces JSON by using SPI to repeatedly invoke to_json(...) on column values (instead of wal2json's simpler JSON output that doesn't work well for some data types).

Testing shows that this is pretty CPU intensive.  Is there a way to achieve the same result with better performance?

Could you somehow directly invoke row_to_json on the tuple given to the change callback?

Is there a way to directly invoke to_json on each column value without SPI?

(My attempts to call those functions directly with DirectFunctionCall1Coll crashed the server, so any pointers are welcome!)

Here's what the functioning, but CPU-intensive, code looks like:

spiArgType = columnInfo->atttypid;
/* Prepare the SQL */
spiPlan = SPI_prepare("SELECT to_json($1)::TEXT", 1, &spiArgType);
/* Execute it */
spiReturnValue = SPI_execute_plan(spiPlan, &columnValue, NULL, false, 1);
/* And if it succeeded, append to output string */
if (spiReturnValue < 0) {
    elog(ERROR, "SPI_execute_plan returned %d", spiReturnValue);
    appendStringInfo(out, "null");
} else {
  jsonDatum = SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isNull);
  appendStringInfo(out, "%s", TextDatumGetCString(jsonDatum));
}
SPI_freeplan(spiPlan);

A failed attempt using OidFunctionCall1Coll:

TupleDesc cols = ...;
HeapTuple tuple = ...;
Form_pg_attribute colInfo;
Datum colValue;
bool isNull;

for (i=0; i < cols->natts; ++i) {
  colInfo = TupleDescAttr(cols, i);

  // (Omitted checks for dropped or system column)

  colValue = heap_getattr(tuple, i+1, cols, &isNull);
  if (! isNull) {
    // The following call fails, and emits this error:
    // ERROR:  could not determine input data type
    jsonDatum = OidFunctionCall1Coll(F_TO_JSON, InvalidOid, colValue);
    // This is never reached:
    appendStringInfo(out, "%s", TextDatumGetCString(jsonDatum));
  }
  // ...
}


texts: SQL Server 2000 optimzation job hasn't run for a while

texts:
Our SQL Server 2000 used to run a Sunday morning 3am job of optimizing the database. Somehow it was turned off and hasn't run in a year. We have noticed slow access to the database.

We turned it back on and it will run for about 4 hours and complete. Our issue is when it runs on its scheduled Sunday at 3am it will cause the applications that connect to stop working as long as it runs. Before it was shut off the application would continue to work but I believe since it has been off for a year and the job only ran once it wasn't enough to clean the DB up enough.

Should we run it three times in a row? Or what is the best way to fix this?

thanks in advance!


texts: How to create BAG on non-wsfc nodes (SQL Server 2016 STANDARD Edition)

texts:
Stumbled over this a few times now, while trying to figure out if a consultant is right or not in his claim. I hope you can shed light on it.

Is it, or is it not possible to configure a Basic Availability Group on a database present in two AD-Servers with each its own standard edition SQL Server 2016 SP2 cu2 (default instance)
(Both had the SSMS 16.5.3 – now upgraded to SSMS 17.9)
None of them are configured as members in any failover cluster.

Almost everywhere I seem to find, it looks as if they all require that nodes be member(s) of a WSFC.

“Almost”, because I read a Microsoft note, stating that it’s possible with SSMS 17. I did install that and it also fails to enable the options to switch on AG?

If it should be possible, do you know where I could go to read what’s needed…

Re:
https://www.brentozar.com/archive/2015/06/how-to-set-up-standard-edition-alwayson-availability-groups-in-sql-server-2016/#comment-2654300


texts: SQL Server drop and create all users in every DB

texts:
I would like to drop and recreate every user in a database without having to manually do this for 500 users. I have this so far:

USE [Maintenance]
GO

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'xxxxxx\xxxxx')
DROP USER [xxxxx\xxxxx]
GO

USE [Maintenance]
GO

CREATE USER [xxxxx\xxxxx] FOR LOGIN [xxxxx\xxxxx] WITH DEFAULT_SCHEMA=[dbo]
GO

The real problem is when I detach and reattach db from a SQL Server 2005 and restore to a SQL Server 2008, all the logins carry over, but the do not work unless I drop and add them back. I have read many many links and other threads about them and tried How to transfer logins and passwords between instances of SQL Server(http://support.microsoft.com/kb/246133) but it did not work.

On a LAN, we have 500 domain users that access a SQL Server 2005 database. I have created a backup from the SQL Server 2005 databases and want to move all the databases to a new server that will run SQL Server 2008. I have 12 people that have SQL Server login's. I have remade those and they work. I have 500 user's that, through there dsn, should be able to access a database, if the are assigned roles db_datareader/db_datawriter to the DBNAME->Security->User list. The ones that don't work are the domain database users. Under the DBname/instancename->Security->Logins every db domain user is not currently listed under the logins

The message I keep getting on the SQL Server 2005 is This is the message I keep getting. Login failed for user 'COMPANY\USER'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: XX.XXX.XX.XX]

Here is the dsn:

Microsoft SQL Server ODBC Driver Version 03.85.1132
Data Source Name: xxxxsql002
Data Source Description: xxxxsql002
Server: xxxxsql002\xxxxbaconsql
Database: (Default)
Language: (Default)
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Integrated Security: Yes
Use Regional Settings: No
Prepared Statements Option: Drop temporary procedures on disconnect
Use Failover Server: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Data Encryption: No

The UAC and firewall are disabled.

Thanks!


texts: FTs catalog in SQL Server for multiple FT index

texts: I have created one catalog for FTS index, can the same catalog be used by by another table in index.


texts: Backups jobs are successful but cannot see differential backup in the media

texts:
I have a backup job running on my Sql server that takes a complete backup at start of month and diff every two hour between 9 to 5.Now the problem backup jobs are running and successful but I cannot see differential backup in the media.

Backup job
USE [msdb]
GO

/****** Object:  Job [Backup]    Script Date: 04/06/2018 12:31:21 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 04/06/2018 12:31:21 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Backup',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=2,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'Server\user',
        @notify_email_operator_name=N'AdminOperator', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Step-1]    Script Date: 04/06/2018 12:31:21 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step-1',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'DECLARE @File varchar(1000)
DECLARE @i int
declare @m as varchar(2)
declare @d as varchar(2)
declare @dt as varchar(8)
DECLARE @strSQL nvarchar(1000)
--if (day(getdate())<10)
--  set @d = ''0'' + cast(day(getdate()) as varchar(2))
--else
--  set @d =  cast(day(getdate()) as varchar(2))

if (month(getdate())<10)
    set @m = ''0'' + cast(month(getdate()) as varchar(2))
else
    set @m =  cast(month(getdate()) as varchar(2))
Set @dt = cast(year(getdate()) as varchar(4)) +  @m

SET @File = ''D:\SQLBackups\SQLBackups_database\database'' + @dt + ''.BAK''

EXEC master..xp_fileexist @File, @i out
 IF @i = 1 BEGIN     --FILE EXISTS
    set @strSQL = ''BACKUP DATABASE database  TO  DISK = N'''''' + @File + ''''''  WITH  DIFFERENTIAL''
 END
 ELSE BEGIN --FILE NOT EXISTS
    set @strSQL = ''BACKUP DATABASE database  TO  DISK = N'''''' + @File + ''''''  WITH  INIT''
 END

EXEC sp_executesql @strSQL

',
        @database_name=N'database',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule-1',
        @enabled=1,
        @freq_type=8,
        @freq_interval=62,
        @freq_subday_type=8,
        @freq_subday_interval=2,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20100823,
        @active_end_date=99991231,
        @active_start_time=90000,
        @active_end_time=190000,
        @schedule_uid=N'50451ca4-7f63-45ce'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Log:


texts:
I am having dificulty designing a table that stores the information about a parking lot with 100 spaces that are arranged by 5 columns x 20 rows for employees, guests, handicap, executives, contractors and using an outer join sql statement.

I am using an Oracle  DBMS. I have a project to do for a Database Fundamentals course in college that requires the creation of several tables (employees, retirementplan, healthplan, healthcost, healthtype, ParkingSpace).

The requirement is that the HR personnel will assign the parking space. So I am assuming that it needs a create view table or some kind of scenario where there is a prompt for HR to assign the space to that person.  I am also of the mindset that each space should be named so it can be referenced.  How is this accomplished.  Can someone please guide me or provide the basics, please.  I am a newbie so please take it easy on me.  Thanks a million.


texts: I would like to know what ports I need have open for a sharded mongodb cluster. For example does the mongos need to be able to accapt connections to and from mongoc. I dont want to have to many open ports that I dont need to have.


texts: Get names of games that have the highest number of guilds

texts:
I've a database which contains tables:

Quest:

PK  Name
PK  qName
    goal

Guild:

PK  Name
PK  gName
    level

Game:

PK  Name
    MaxPlayer
    levels
    uName

GuildQuest:

PK  Name
PK  gName
PK  qName

I need to retrieve the names of the games that contain the highest number of guilds.

Guilds are assigned by the game.

My attempted query:

SELECT Name, Count(*) guildName
FROM Game game, Guild guild
WHERE game.Name = Guild.gName

and somehow use the MAX function.

I'm kind of confused how to perform this, any help is much appreciated.


texts: Database design for part-number generator

texts:
I'm working on a generator for part numbers, where each space will have a meaning and each space will have a number of possible values. What is a "correct" database design for this?

Example part number layout: A B C D - ####(0000-9999)

A = Fabric backer (set length of 1 digit, 5 different options)

B = Stitching type (set length of 1 digit, 10 different options)

C = Stitching color (set length of 1 digit, 3 different options)

D = Trim color (set length of 1 digit, 10 different options)

#### = can be any 4-digit long number (allows preceding or following zeroes) that corresponds to a fabric color that we use (not all numbers between 0000-9999 are valid, but it isn't in serial order, either, as there are gaps)

We will be creating new part number generators in the future, and they will have different layout, different possible values, and different lengths. I was thinking of doing something like this:

TABLE: GENERATOR (the overall instance of generator)

FIELD_NAME, FIELD_TYPE, FIELD_LENGTH
id, int (autoincrement), 5
name, str, 100
is_active, bool, 1
notes, text, 999

TABLE: GENERATOR_VALUESET (connects the generator with the spaces for valuesets, basically sets the space order and valueset used for each space)

FIELD_NAME, FIELD_TYPE, FIELD_LENGTH
gen_id, int, 5 (correlates to GENERATOR.id)
valueset_id, int, 5 (correlates to VALUESET.id)
ordinal, int, 3
is_active, bool, 1

TABLE: VALUESET (collection of associated values to form a coherent valueset relevant to a particular product option)

FIELD_NAME, FIELD_TYPE, FIELD_LENGTH
id, int(auto increment), 5
name, str, 100
is_active, bool, 1

TABLE: VALUESET_VALUE (connects values to the valueset and sets order)

FIELD_NAME, FIELD_TYPE, FIELD_LENGTH
valueset_id, int, 5 (correlates to VALUESET.id)
value_id, int, 10 (correlates to VALUE.id)
ordinal, int, 3
is_active, bool, 1

TABLE: VALUE (the actual values, includes a displayed value as well as a long-format name)

FIELD_NAME, FIELD_TYPE, FIELD_LENGTH
id, int(auto increment), 10
name, str, 100
displayed_value, str, 5
is_active, bool, 1


texts: SQL Server : how to create user with access only from specific HOSTNAME or IP addres

texts:
I have one stupid app which I rely on, which has hardcoded connection string inside.

To increase security of my SQL Server I'd love to make SQL users same as one hardcoded inside app, but I want to allow that user to be able use SQL Server only from certain host (ip address).


texts: Oracle implicit parameter casting - why doesn't it always work? (ORA-06502 error)

texts:
I have a Java (Spring) app that uses Oracle 10 as DB. Some legacy code has wrong parametrization of PL/SQL procedures. For instance, some input parameters are declared as OracleTypes.VARCHAR in Java DAO layer, while in fact they're NUMBER in PL/SQL procedure. 99% times this works without a problem (of course, if the sent data is actually a number), but sometimes I get the ORA-06502: PL/SQL: numeric or value error: character to number conversion error.

I understand how to fix the problem, but I wonder why this occurs on some DB installations and on most it doesn't. This is of high importance for me because it's very difficult to inspect legacy code for wrong parameter types.


texts: Change the system wide default for maxrecursion

texts:
How do I change the system-wide default value of MAXRECURSION?

By default it is 100, but I need to increase it to something like 1000.

I am unable to use query hints since I'm using a program that takes my query and executes it for me and I can't get around this limitation, unfortunately.

However, I do have admin rights on the server instance.  I've poked around in the server facets, but I don't see anything there related to query options or recursion.  I assume there has to be a place somewhere where I can update the system-wide default.

Any ideas?


texts: UML database schema based on the creteria

texts:
I have the following requirements to design a database schema (brief):

A Graphic Designer (we will call them designer) gets a call from a client with a new job to design a logo.
The designer logs-in to JobBook and registers the job by job name, description and a client.
The designer can assign an existing client to a job, or register a new client.
Registering a client means imputing a basic billing information (name, address, VAT number, telephone, email).
Every time the designer works on a job, they open the JobBook and note down the date and hours worked on the specific job. They should also note down the type of task they have been doing (e.g. logo colour iteration).
Each job has a status: open, invoiced or paid. Once the job is complete, the designer changes the status from open to invoiced. The JobBook now has all the required information to issue an invoice.
Once the job is paid, the designer changes the status form invoiced to paid. Each status change should have a timestamp.

Based on the above, I need to design schema in form of UML.

This is what I have so far:

Versions 1:

In this diagram I assume that there can be more designers working with one client and the other way around. So that job can be split into different parts (for example, one job can be separated into 1. design logo, 2. design web-design, 3. design banner and etc). This is why I have such database schema. Also another reason for me doing this schema is because of the future expansion of the database, because currently, this is not considered by the brief (above).

Versions 2:

This version just allows just one designer working with one client and not splitting of the job is considered in this version.

Question

Would you consider version 1 or version 2 to be correct database schema? If not, please help me to defined correct solution for the requirements mentioned above.

PS. This is homework (not actual project, etc).


texts: How to know when did a blocking session start and end?

texts:
I created one application where requests in XML format come to my application, my application runs some select and update SQLs and then responds to the other system. Total TAT for this whole process is 1.2 seconds max, but 2 requests took more than 3 seconds to respond.

So I asked for the ASH report from the DBA team to find out why this delay happened and I can see one blocking session during both of these requests. Now in the ASH report, I have the data like the following:

But to be sure I want to find out when this blocking session started and when it ended, if it coincides with request entry time to my application, I can be sure.

So is there a way to do so? I tried to find from Google but could not find any substantial answer.


texts: PostgreSQL select jsonb key value from an array while joining table

texts:
I want to extract a string value from an object's key inside a jsonb array, coming from a table join_table which is left joined, meaning it could be empty for some values.

   -- NOT WORKING
   SELECT
        t.id,
        concat(t.content, array_to_string(jsonb_array_elements(j.arr)->'Name'->>0, ',')) AS content
    FROM
        main_table t
    LEFT JOIN join_table j ON t.j_id = t.id
    WHERE
        t.id IN (1,2,3,4,...)

tables:

CREATE TABLE main_table (
        id bigserial PRIMARY KEY,
        j_id BIGINT,
        content text
);

CREATE TABLE join_table (
        id BIGSERIAL PRIMARY KEY,
        arr JSONB
);

example of join_table.arr

[
  {
    "Id": 1,
    "Name": "name1",
  },
  {
    "Id": 2,
    "Name": "name2",
  }
]

What I am trying to achieve is to concat content with a comma separated string of all the join_table.arr Name values


texts:
We have a project which does write data on a second generation MySQL 5.6

The master's write behaviour:
Average 70 write operations per second.
The thing is 2 times a day, application writes data along 3 hours each time. And the each of them writes 2500 operations per second.

I believe this write operations cause the never ending replication delay.
Once the replication delay starts, not able to recover. Increasing the replica's source(cpu and memory) is not working.

What should i do to make replicas sync? I presume master/slave architecture is not the solution for this write operations.

Should i use bigger master for both read and write operations instead of master-slave?

Thank you.

Current master:
4cpu 15gb ram and 900gb ssd

Current replica:
8cpu 30gb ram and 900gb ssd

EDIT

It's happening like 50k inserts in a single transaction.
Replication is row based.
Inserts can be batched.
All operations going into a single database though there are other databases exist in the master.
Upgrading to 5.7 is likely possible.

I'm trying to create an external read replica because setting global variables is not possible due to super privilege. As you describe, i'm going to increase parallel workers for multi threaded replication and innodb_flush_log_at_trx_commit.

Also i'll try max allowed packets.
The delayed replica is destroyed. So the status and variables below are  represent the master.

SHOW GLOBAL STATUS

SHOW GLOBAL VARIABLES


texts: MYSQL fulltext - What's the right way to get the relevance from one column?

texts:
I want to make one column more valuable than others like it's been done in query bellow.This example  shows that only one fulltext index is needed And this answersuggests that three fulltext indexes are needed to do the same.

Here is the queries from the first link:

ALTER TABLE products ADD FULLTEXT(title, sdescription, ldescription)

SELECT *,
MATCH(`title`) AGAINST ('+iphone +case +4s' IN BOOLEAN MODE) * 10 as rel1,
MATCH(`sdescription`) AGAINST ('+iphone +case +4s' IN BOOLEAN MODE) * 3 as rel2,
MATCH(`ldescription`) AGAINST ('+iphone +case +4s' IN BOOLEAN MODE) as rel3,
FROM products
WHERE MATCH (title, sdescription, ldescription)
  AGAINST ('+iphone +case +4s' IN BOOLEAN MODE)
ORDER BY (rel1)+(rel2)+(rel3) DESC;

I'm confused how many indexes are needed for that, one or three?
I asked this question on stackoverflow but no answer yet.


texts: What is branching factor of mysql myisam's b-tree

texts:
I am doing some research but I am struggling to find what is branching factor of MYISAM's b-tree, e.g. how many keys per page (node).
I also would like to know how the char/varchar keys are stored - are they stored in full length or just first "meaningful" characters.

By "meaningful" characters I mean:
create table a(
   name varchar(1000) primary key
);

insert into a('london');

"london" key is 1000 characters long (assuming ASCII), but the key
itself is just 6 characters (7 with null character, if we represent it as C-string).


texts: Summing a column based on uniqueness of another column without using temporary table

texts:
I have a table like this:

CREATE TABLE `files` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `hash` char(40) DEFAULT NULL,
  `size` bigint(20) unsigned DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `ndx_hash` (`hash`),
  KEY `hash_and_size_ndx` (`hash`,`size`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

This table has files that contains same (hash, size) with different name in it.

It should be noted that two file records containing the same hash will have the same size.

What I'm looking for is the total size of unique files in this table. Uniqueness is based on hash column.

Here is a query that provides what I want:

SELECT sum(t.unique_size) FROM (SELECT MIN(size) as unique_size FROM files GROUP BY hash) AS t;

Output is:

+--------------------+
| sum(t.unique_size) |
+--------------------+
|           64233241 |
+--------------------+

The reason I don't like this query is because its slow. Its slow because it creates a temporary table. My table has more than 5m records in it.

I think it is a waste to create a giant temporary table since it is theoretically possible to allocate one integer field and increment it as the unique_size's (see my query) are generated.

Is there a way to get what I want without creating a temporary table?

Note: I've experimented WITH ROLLUP and found no use of it.


texts:
Hello i have a small database i'm working on and when i import on another server it tells me you have 6 warnings. when i show warnings it says 1031 This table doesn't have this option. I've searched on google about this problem but i didn't find something that relates my problem and the warning level is on note.

Localhost Version

# mysql --version
mysql  Ver 14.14 Distrib 5.5.34, for Win32 (x86)

Server Version

-- --------------------------------------------------------
-- Host:
-- Server version:               5.5.35-cll - MySQL Community Server (GPL)
-- Server OS:                    Linux
-- HeidiSQL Version:             8.3.0.4694
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- Dumping database structure for AquaMercury
DROP DATABASE IF EXISTS `AquaMercury`;
CREATE DATABASE IF NOT EXISTS `AquaMercury` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `AquaMercury`;

-- Dumping structure for table AquaMercury.bex_categories
DROP TABLE IF EXISTS `bex_categories`;
CREATE TABLE IF NOT EXISTS `bex_categories` (
  `ProductCategoryId` int(11) NOT NULL AUTO_INCREMENT,
  `ProductCategoryName` varchar(50) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ProductCategoryId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 COMMENT='Stores product categories';

-- Data exporting was unselected.

-- Dumping structure for table AquaMercury.bex_products
DROP TABLE IF EXISTS `bex_products`;
CREATE TABLE IF NOT EXISTS `bex_products` (
  `ProductId` int(11) NOT NULL AUTO_INCREMENT,
  `ProductName` mediumtext NOT NULL,
  `ProductPrice` varchar(100) NOT NULL,
  `ProductManufacturerCode` varchar(100) NOT NULL,
  `BexProductCode` varchar(100) NOT NULL,
  `ProductDetails` mediumtext,
  `ProductPicture` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ProductId`,`BexProductCode`,`ProductManufacturerCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 COMMENT='Place of storing products';

-- Data exporting was unselected.
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;


texts: Cast varchar into int, converting any value under 0 to 0

texts:
I am inserting values form a staging table in SQL Azure into a production table. Values in the staging table are varchar, while the target value in the production table is tinyint (it is an age field for humans, so I reckon that up to 255 will suffice ;) )

Problem is that due to a bug in the source system, we tend to get some values as -1 or -2 when they should be 0. This causes issues, since TINYINT only supports values form 0 to 255 (and also because this is factually wrong). I was wondering if there is some sort of case statement that I could use to convert values to 0 if they fall under 0, or perhaps if they fall outside the 0-255 range?


texts: MariaDB REGEXP_REPLACE Invalid utf8 byte sequence

texts:
I'm working with a fairly old (15 years) old database. It started as a MySQL3, was upgraded several times, the frontends had problems with proper encoding, the whole database was converted to utf8mb4 (partly successful) - short: especially in old sets, there is a little mess, encoding wise.

Now I need to do some manipulations with regular expressions, and those fail. The statement is like this:

UPDATE table
    SET text=REGEXP_REPLACE(text,'something','else')
    WHERE text REGEXP '\\bsomething\\b';

Generally, this works as expected, but on some tables, this fails with the following error message:

 ERROR 1139 (42000):
 Got error 'pcre_exec: Invalid utf8 byte sequence in the subject string' from regexp

It does not have to do anything with rows on which the WHERE clause matches.

So my question is: how do I find the rows with invalid utf8 byte sequences, and might even repair them?

Things like

SELECT * FROM TABLE where length(COLUMN) > char_length(COLUMN)

are not working for me; that is only working if you expect only ASCII characters. But there are a lot of valid UTF8 characters in the database, and the statement is not failing on those as well.


texts: SQL Editor features usually existing in an IDE?

texts:
I'm a fan of dbeaver user, but i miss the features that are usually existing in a modern java IDE, such as finding references, navigating codes, refactoring:

Autocompletes db view columns
Find references for a table -- the db views making use of this table
Find references for a table col -- the db views making use of this table col
Find references for a view -- the db views making use of this view
Find references for a view col -- the db views making use of this view col
When reading a view, I can easily click a shortcut like F3 to navigate to the table, or table col, or to another view used in that view based on the cursor position
A keyboard shortcut to go back and forth after a navigation action is performed. For example, when reading a view, my cursor goes to a table name, i F3 to jump to table definition, alt+leftArrow to 'navigate' back to the previous view and cursor position.
Refactoring into a sql migration file. For example, changing a column name of a table of a view might affect other views, or tables, and the collection of statements to achieve this could be exported to an sql file that can be migrated to our db.

Any pointers on your the sql editor[s] that might having the aforementioned features ? I'm currently using postgres exclusively.


texts:
What is the safe way to remove an old IP address from SQL Server configuration?

The scenario is, I have a server with 2 NIC's that should have been configured for teaming but weren't at the time SQL was installed.  Each had a different IP so SQL picked up on that and added each IP to its configuration.

The NICs are now teamed and the box is down to a single IP.  I want to remove from SQL configuration the IP that is no longer assigned to the server.  I don't want to just deactivate it, but rather I want to completely remove it.

Is there a safe way to do this?  Or does one just delete the keys under HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.[Instance Name]\MSSQLServer\SuperSocketNetLib\Tcp\ related to the IP in question and restart?


texts: Changing Data Directory on MySQL DB Server

texts:
I have installed mysql server 5.6.24 on Windows Server 2012 R2.

I imported dump files and the data files are stored in C:\ProgramData\MySQL\MySQL Server 5.6\data

I want to change directory like D:\ProgramData\Data.

Can you please help me, anyone ?


texts: How to identify which query is filling up the tempdb transaction log?

texts: I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.


texts: Does MySQL 8 on Ubuntu 20.04 allow password authentication for root user?

texts:
Just installed MySQL on a new development server and ran into an issue where root access seems to be denied to all Linux users other than root, even if they use the correct password. Also MySQL root access doesn't seem to require a password, only that you're the root user or sudo.
Is it possible to require password authentication for MySQL root user even if you're logging in as Linux root? I understand that if your Linux root, you already have more privileges, I just wanted to know.
Is it possible for non root Linux user to be able to log into MySQL as MySQL root? If so, how?


texts: Does Index Intersection work with Included Columns?

texts:
I've been asked by a report designer whether it is acceptable to add a new index to a table to speed up a particular report.  The index would be on a single column, CreatedDate, but would have included columns.

There are already five indexes on the table and I want to avoid creating separate indexes for specific reports or queries.  However, I figure a single-column index might be useful in index intersection, particularly on a column that is likely to be used in multiple queries, like CreatedDate.

What I'd like to know, to help me make my decision, is whether an index which has included columns could be used in index intersection, or whether having included columns would prevent the index from being used in index intersection.

I've tried to Google this question and haven't found any information about the effect of included columns on index intersection.


texts: Why index is not used after replacing "WHERE field=x" to "field in (x,y)"?


texts: keeping track of certain related field changes

texts:
Possible Duplicate:
  Save history editable data RDBMS

I am totally new to databases and MySQL. I am working on a project in which I have a few related fields such as question and multiple choice asnwers. The value of those fields changes from time to time as data gathers.

Now my question is: how can I track those changes so that at any given time I am able to display the correct combination of the data? Any hints would be really appreciated.


texts:
Performing a large index reorg on a mirrored SQL Server 2014 TDE database creates a large redo log on the mirror and the mirroring monitor displays an infinite restore time.  The exact same process with the database without TDE keeps up in near real time.

I don't expect the exact performance with TDE enabled, but the difference is night and day. Am I missing a setting or are my expectations unrealistic?  Any advice or links is much appreciated.


texts: How to use the COUNT function to count up to the current row for each row?

texts:
I had trouble phrasing the question, but I think the following example will clear things up.

Context: Trying to create a query for Microsoft Report Builder, need to perform some logic on the data in one of the tables.

I have a SQL Table (let's call it AllEvents) in the database with a format like this:

UserName   |    Grouping    | EventDate | NumberOfEvents
________________________________________________________
Alice      | Red Grouping   | 1/1/2000  | 1
Alice      | Red Grouping   | 1/2/2000  | 2
Alice      | Red Grouping   | 1/3/2000  | 3
Alice      | Blue Grouping  | 1/4/2000  | 1
Alice      | Blue Grouping  | 1/5/2000  | 2
Anderson   | Red Grouping   | 1/3/2000  | 1
Anderson   | Blue Grouping  | 1/5/2000  | 1
Anderson   | Green Grouping | 1/6/2000  | 1
Anderson   | Green Grouping | 1/7/2000  | 2

And I want to select the results such that I get an output of the following form, where it counts the number of events for the UserName before and up to the row's EventDate.

UserName    | EventDate   | EventNumber
_______________________________________
Alice       | 1/1/2000    | 1
Alice       | 1/2/2000    | 2
Alice       | 1/3/2000    | 3
Alice       | 1/4/2000    | 4
Alice       | 1/5/2000    | 5
Anderson    | 1/3/2000    | 1
Anderson    | 1/5/2000    | 2
Anderson    | 1/6/2000    | 3
Anderson    | 1/7/2000    | 4

We can ignore the grouping column, since we're only interested in the rows with the same UserName.

What I have been trying to do is use a subquery like below:

SELECT

   SUM
   (
      CASE WHEN AE.EventDate> AllEvents.EventDate
         THEN 1
         ELSE 0
      END
   )
   OVER (PARTITION BY UserName, Grouping)

FROM AllEvents AS AF

WHERE
   AE.UserName= AllEvents.PatientId
   AND AE.EventDate= AllEvents.EventDate

In the following query:

SELECT
   UserName,
   EventDate,
   [The subquery above] AS EventNumber

FROM
  AllEvents

However, the subquery is returning more than one value and I'm struggling to determine how to troubleshoot.

I have also been toying with the COUNT function, but have failed to have it count conditionally based upon the current row's EventDate value.

Any advice or suggestions would be appreciated.  I have been trying to be creative in my approach, but I don't know if I've hit the best method.


texts: Can you direct Availability Groups to fail over to a particular node?

texts: Can you direct Availability Groups to fail over to a specific node in the cluster?


texts: SQL Server Replication - Only weeks worth of data

texts:
I have a need for a test server that hosts a small subset of data from our production systems. Is it possible to setup a SQL Server Replication Job that only keeps a week's worth of data so developers can develop reports?

Keep running 7 days of data, keeping the storage need small is the goal.


texts: Equivalent functions in MySQL that exist in SQL Server

texts:
In an EXIT HANDLER block, what are the MySQL equivalents of

OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)
ERROR_MESSAGE()

Edit

What I want to do.
In SQL Server I'd have

CREATE PROC ...
AS
SET NOCOUNT, XACT_ABORT ON;

BEGIN TRY
 ...
END TRY
BEGIN CATCH
   ROLLBACK etc
   EXEC someGenericProc @@PROCID, @errmsg OUTPUT;
   RAISERROR ( @errmsg, 16, 1);
END CATCH
GO

CREATE PROC someGenericProc
   @LoggingProcID int
AS
..
SELECT
    @LoggingObject = OBJECT_SCHEMA_NAME(@LoggingProcID ) + '.' + OBJECT_NAME(@LoggingProcID),
    @ErrorObject = ERROR_PROCEDURE(),
    @ErrorMessage = ERROR_MESSAGE();

-- some processing to deal with nesting etc

INSERT SomeErrorTable (...)
VALUES (@LoggingObject, @ErrorObject, @ErrorMessage)
...
GO

Cheers


texts: Access 2013 update rows from multiple rows

texts:
I need help to construct an update query to populate MynewField with contents from multiple fields and rows where the MapID is the same.

RowID---MapID-----Extension-----  Name------    MynewField
7        318        #222                         [#222Tom]
22       318                       Tom           [#222Tom]


texts:
I have a table1 with fluctuating number of columns. (colname1,colname2,colname3...colnamen)

I want to :

ALTER TABLE table2 CHANGE COLUMN `columns` `columns` ENUM('colname1','colname2','colname3' .... '') ...

each time the table1 gets changed in the structure

I know how to get the column names in a table with:

SELECT COLUMN_NAME FROM information_schema.columns WHERE table_schema=

But how can I now get them in a string into the ALTER TABLE ?


texts: How can I justify making changes to my redo logs?

texts:
I don't spend a lot of time with my Oracle environment. I'm more of a SQL Server guy. I need to look into the alert logs to check for correlation of an application error. What I found instead was collections of this:
Thread 1 cannot allocate new log, sequence 947
Checkpoint not complete
  Current log# 1 seq# 946 mem# 0: D:\ORACLE\DATA\TRAXTRAI\ONLINELOG\O1_MF_1_G0B8NV7H_.LOG
  Current log# 1 seq# 946 mem# 1: D:\ORACLE\RECOVERY\TRAXTRAI\ONLINELOG\O1_MF_1_G0B8NVLP_.LOG
Thread 1 cannot allocate new log, sequence 947
Private strand flush not complete
  Current log# 1 seq# 946 mem# 0: D:\ORACLE\DATA\TRAXTRAI\ONLINELOG\O1_MF_1_G0B8NV7H_.LOG

These are happening, in my test environment, rather frequently. At least once every 20 minutes for the last several years according to the log. I have read that I can ignore these but I was see more often is that I use this as motivation to play with my redo log settings/framework. It's just that I don't know exactly what I need to do with them. My test environment is not running archive logs which is supposed to play a factor in this. (My production environment is doing this more frequently so I am using this to try and get a handle on my solution approach.) I am sure this has a performance impact and should be looked at.

This is what I know so far...

SELECT name,value
  FROM v$sysstat
  where name in ('redo entries','redo log space requests');

redo entries             30463497
redo log space requests  89

I also have this

select t1.group#, t1.bytes/1024/1024 as "Size(MB)", t2.member, t1.status
from v$log t1, v$logfile t2
where t1.group# = t2.group# ;

GROUP SIZE  MEMBER                                                      STATUS
  3   50    D:\ORACLE\DATA\TRAXTRAI\ONLINELOG\O1_MF_3_G0B8NW8F_.LOG     ACTIVE
  3   50    D:\ORACLE\RECOVERY\TRAXTRAI\ONLINELOG\O1_MF_3_G0B8NWFR_.LOG ACTIVE
  2   50    D:\ORACLE\DATA\TRAXTRAI\ONLINELOG\O1_MF_2_G0B8NVNN_.LOG     CURRENT
  2   50    D:\ORACLE\RECOVERY\TRAXTRAI\ONLINELOG\O1_MF_2_G0B8NVPM_.LOG CURRENT
  1   50    D:\ORACLE\DATA\TRAXTRAI\ONLINELOG\O1_MF_1_G0B8NV7H_.LOG     ACTIVE
  1   50    D:\ORACLE\RECOVERY\TRAXTRAI\ONLINELOG\O1_MF_1_G0B8NVLP_.LOG ACTIVE

50MB seems large enough based on other suggestions but I have also seen people suggest making them smaller or adding new ones. I am still making my way though Managing the Redo Log of the admin guide but I am not confident I will find the answers there.

How can I justify making changes to my redo tables? I know there is likely not enough information to make a suggestion from what little I provide so I basically need to know what else to get. I don't want to make guesses if there is something more I can do.


texts: When creating a view how do I separate one column into two depending on a foreign key value?

texts:
I'm supposed to create a view that joins the Customer table with the Address table and Address type table. Call it Customer_View.  Have two sections for the Address; Physical and Mailing. Make sure that your column headings reflect the two sections. If they are both you can just put the details under the Physical.

These are my tables with columns

Customers
Columns:
Customer_Id
Last_Name
First_Name
Middle_Name
Phone
Email

Address
Columns:
Address_Id
Customer_Id
Address_Type_Id
Address_1
Address_2
City
State
Zip
Country

address_type
Columns:
Address_Type_Id
Description

I know how to join the tables but I don't know how to make it so the address column shows as two columns, one for physical and one for mailing, depending on if the address_type_id value is 1 & 3 or 2... does that make sense?

This is the code I have:

    CREATE OR REPLACE VIEW Customer_View AS
SELECT  c.customer_id, CONCAT(First_Name, ' ', Last_Name) AS 'Full_Name',
        Address_1, Address_2, Phone, Email, City, State, Zip, Country,
FROM address AS a
    JOIN customer AS c
        ON a.customer_id = c.customer_id
    JOIN address_type AS aty
        ON a.address_type_id = atp.address_type_id


texts: Update a Column with sequential number based on the order by of a column in Oracle Sql

texts:
I have table with below create scripts

create table training(
 action_id       NUMBER not null,
 training_id       NUMBER,
 course_name       VARCHAR2(100),
 display_order     NUMBER default 0

)

  Below is the data

ACTION_ID   TRAINING_ID DISPLAY_ORDER
 675150      770650       0
 675150      780700       0
 675150      780701       0
 675180      770550       0
 675180      780800       0
 675180      780801       0

  I need write a db script that will update  DISPLAY_ORDER so that it
  gets order from 1 order by  TRAINING_ID   for  a particular   ACTION_ID

  So outcome will be

ACTION_ID   TRAINING_ID DISPLAY_ORDER
 675150      770650       1
 675150      780700       2
 675150      780701       3
 675180      770550       1
 675180      780800       2
 675180      780801       3


texts: Can I disable journalling to improve performance

texts:
We have a 16 shard MongoDB 2.4 installation running in AWS that is eating money.

The data is volatile, it only gets used within 15 minutes of creation
If the cluster goes down we wipe it out and restart ~ 30 seconds downtime

I note that the majority of disk stress appears to be journalling

Is there any reason I cannot disable journalling?


texts:
I want to implement cross multiplication in SQL queries, can anyone suggest me how to do it.

For example consider the values below

Column 1       Column 2
  10              5
  3               6
  4               8

I want to display this output

Column 1            Column 2
  10                  10
  3                  30(i.e 10*3)
  4                 120(i.2 30*4)

as so on.


texts: Find all the queries executed recently on a database

texts:
[I am a beginner level T-SQL Programmer]
[..and hopefully I am on the right stack exchange site]

I would like to get a list of all the queries I executed (in the least, those I executed today since morning). I need to make a report on the execution times of the queries.

Online search didn't yield me much useful info. The only query that I found online which seemed pretty close is

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

This query returned some odd results (most of which were a bunch of sprocs). Moreover, all these results show queries executed since today afternoon (I need queries from morning).

I couldn't find anything in the previous questions (if similar question has already been asked, please point me to it).

I saw some suggestions regarding SQL Profiler, but I guess profiler would help me only if I have already started the trace (correct me if I am wrong).

Can someone suggest me how I should go about to get a list of all queries that have been executed on the database since morning (including the query execution times)..

[It would be helpful (not a requirement) if I can also somehow get the username of the user who executed the query]


texts: Oracle - Apply SQL to all schema in database

texts:
I'm trying to run the same SELECT against all the schema in my Oracle database.
I found an example of how to do this, but when I try to run it against my database I get the below error:

PLS-00306: wrong number or types of arguments in call to

This is my PLSQL

DECLARE
v_sql varchar2(128);
 cursor c1 is
    select username from dba_users WHERE account_status = 'OPEN' and DEFAULT_TABLESPACE not in ('SYSAUX','SYSTEM');
BEGIN
    for REC in c1 loop
        v_sql := 'select * from '||REC||'.accountdetails';
        EXECUTE IMMEDIATE v_sql;
    end loop;
end;
/

So this should in effect be doing this...

SELECT * FROM USER1.ACCOUNTDETAILS;
SELECT * FROM USER2.ACCOUNTDETAILS;
SELECT * FROM USER3.ACCOUNTDETAILS;
etc.

Any advice greatly appreciated

C

From Akina comments, I now have

DECLARE
cursor c1 is select username from dba_users WHERE account_status = 'OPEN' and DEFAULT_TABLESPACE not in ('SYSAUX','SYSTEM');
BEGIN
    for REC in c1 loop
        select * from REC.accountdetails;
    end loop;
end;
/

but i get

PL/SQL: ORA-00942: table or view does not exist
PL/SQL: SQL Statement ignored


texts: Extra column when migrating Sybase databases to single SQL Server database

texts:
I'm trying to use Microsoft's SQL Server Migration Assistant for Sybase ('SSMA') to migrate multiple Sybase databases (that have the same schema) to a single SQL Server database.

All of the tables in the new SQL Server database will have an extra column project_id, which should have a fixed value depending on which Sybase database data is being migrated from.

I've migrated the schema using SSMA, and then created the new columns in SSMS. But I the new columns aren't visible in SSMA, and I can't see any way to achieve what I want to do.

How can I migrate data from each of the Sybase databases whilst utilising the extra project_id column?


texts: Personal and Global messaging database design

texts:
I am trying to implement a database design system that will be sending messages to a person(thus 1 to 1) or many persons (1 to many).

    table --users
              |u_id | Name |
              | 01  | Aku  |
              | 02  | Sika |
              | 03  | Admin|

    table --messages
              |m_id | sender_id | Body   | Time |
              | 100 |  01       | hello  | 16:04|
              | 200 |  02       | Hi     | 16:08|
              | 300 |  03       | hi Guys| 19:12|

    table --recipient
              |r_id| m_id | recipient_id|
              | 50 | 100  |  02         |
              | 51 | 200  |  01         |
              | 52 | 300  |  01         |
              | 53 | 300  |  02         |

    table --message_status
              |m_id | recipient_id | status | deleted |
              |100  |   02         | read   |  no     |
              |200  |   01         | unread |  no     |
              |300  |   01         | read   |  yes    |
              |300  |   02         | unread |  no     |

From my schema above, a user can send a message to one person and he can also send a message to many persons.Once a user sends a message to a person, we will have all the recipients userid in the recipient table as well as in the message_status table . So assuming a user is sending one message to 1 million users at a time, it means, there will be a 1 million rows in both the recipient and message_status table.

Please can there be a way to handle this by reducing the many insertion of rows when a user sends a message to many users or that is the right thing to do.

NOTE

Please the system should allow every recipient to delete a recieved message if he/she so wishes, however their delete should not affect other recipients. So if user with u_id = 02 deletes her recieved message, user with u_id=01 should still view his message.

Thanks for helping.


texts: Microsoft.ACE.OLEDB.12.0 Error when querying Excel

texts:
I am running the following query:

SELECT         *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                           'Excel 8.0;Database=D:\GSS.xlsx;HDR=YES',
                           'SELECT * FROM [DATA$]');

and I get the following error:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

I have reproduced this error on another server and have installed the 2010 redistributable:

https://www.microsoft.com/en-gb/download/details.aspx?id=13255

and also the 2007 driver components:

https://www.microsoft.com/en-us/download/details.aspx?id=23734

Do I actually have to install a full copy of office to get this to work?


texts: How to handle table design with variable columns

texts:
I have a table design scenario and as a non-DBA type, would like opinions on which is more scaleable.

Say you are asked to record information on houses for a metro area, starting with a small neighborhood (200 houses) but eventually growing to 5000000+ houses.

You are required to store base information: ID# (A unique lot # we can use as a unique index), Addr, City, State, Zip. Fine, simple table will handle it.

But each year, you are going to be asked to record extra information about all of the houses - and WHAT information will change each year. So, for example, first year, you are asked to record the owners last name and square footage. The second year, you are asked to keep the last name, but dump the square footage and instead begin collecting the owners first names.

Lastly - each year the # of extra columns will change. Might start with 2 extra columns, then go to 6 next year, then back down to 2.

So one table approach is to try to add the custom information as columns in the house tables so there is only one table.

But I have a situation where someone laid out the tables for this as:

"House Table" columns: ID, Addr, City, State, Zip - with one row per house

ID   Addr              City     State  Zip
-------------------------------------------
1    10 Maple Street   Boston      MA  11203

2    144 South Street  Chelmsford  MA  11304

3    1 Main Avenue     Lowell      MA  11280

"Custom Info Table" columns: ID, Name, Value - with table looking like:

ID   Name             Value

1    Last Name        Smith

2    Last Name        Harrison

3    Last Name        Markey

1    Square Footage   1200

2    Square Footage   1930

3    Square Footage

So there are multiple of rows for each individual house record. Each year when the optional information required changes, this table is literally rebuilt, so next year it might look like:

1    Last Name    Smith

2    Last Name    Harrison

3    Last Name    Markey

1    First Name   John

2    First Name   Harry

3    First Name   Jim

Eventually you amass 100,000 house rows AND one year there are 10 extra pieces of information; the second table now is 1,000,000 rows of information, many of which have redundant (description) information. The database requirements overall are that people will need to get the house row information + associated custom field values thousands of times per day.

So my question: Would it be bad (or horrible) practice to instead either:

A) Lay out house table with guess at max # of custom columns (called perhaps "1" through "10") and insert those custom values right in the house rows

OR

B) Store the custom information in the house table, but each year when the requirements change, rebuild the house table with only the # of columns needed for custom information, with the idea that the requirements could go nuts and you never know how many maximum optional fields might be asked for?

Thanks, hope this makes sense!


texts:
This is my DB architecture:

a 3.000.000 records product (memory) table that must be shared by all databases
a x (say 1) records product table specific for each single database
a view in each database that must UNION the two (in order to show common and specific products)

These are my tests:

When I create the view only on memory table

CREATE VIEW all_products AS
    SELECT f1, f2... FROM shareddb.products

the query on that view

SELECT * FROM all_products LIMIT 1000

is very fast (0.9 sec)
When I create the UNION view (this is my target)

CREATE VIEW all_products AS
    SELECT f1, f2... FROM shareddb.products
    UNION ALL
    SELECT f1, f2... FROM products

the speed of the same query decreases drastically (39 sec)

How does MYSQL work in this situation and how must I set the view in order to speed it?

I use 5.7.14 version.

The query EXPLAIN returns:

  id" :1 "select_type":PRIMARY "table" NULL "partitions" NULL "type"
  NULL "possible_keys" NULL "key" "key_len" NULL "ref" NULL "rows" NULL
  "filtered" NULL "Extra", matching row in const table" || "id" :2
  "select_type":UNION "table" llx_product_local "partitions" NULL "type"
  ALL "possible_keys" NULL "key" "key_len" NULL "ref" NULL "rows" 100
  "filtered" 199 "Extra" NULL


texts: AlwaysOn Availability listener access issue

texts:
Good morning all ,
Who has any idea how to do create an SQL alias on a listen alwayson which uses a port other than 1433
thanks


texts: MySQL (MariaDB) bound to 0.0.0.0, but doesn't appear to be listening to all IPs?

texts:
My MySQL (actually MariaDB) server accepts connections at port 43210 without problems on the public IP address of, we'll say, 123.123.123.123 on eth0. This is on an Ubuntu 18.04 server that also has 192.168.1.10 configured for eth0.
I have a user configured to with permissions for all hosts (%):
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| myuser           | %         |
...

From a remote server (another host that is on the same LAN) I can connect to the public IP address of this MariaDB server:
$ mysql --user=myuser -pMYPASSWORD --host=123.123.123.123 --port=43210 mydatabase
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 340
Server version: 10.2.31-MariaDB-1:10.2.31+maria~bionic-log mariadb.org binary distribution
...

However, the problem is that when I try to connect to the LAN IP address, mysql times out:
$ mysql --user=myuser -pMYPASSWORD --host=192.168.1.10 --port=43210 mydatabase
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.10' (110 "Connection timed out")

Just to verify, netstat -tlpn | grep mysql shows it is listening on 0.0.0.0 which should be listening on all interfaces (I believe including the local IP):
tcp        0      0 0.0.0.0:43210           0.0.0.0:*               LISTEN      964/mysqld

I also run PostgreSQL on this same server with MariaDB and if I connect remotely from the same other test host from which I'm trying to make the mysql connection, it connects without a problem:
$ pg_isready -d mydb -h 192.168.1.10 -p 43211 -U myuser
192.168.1.10:43211 - accepting connections

The point of the last note about PGSQL is that I just want to clarify that connections are able to get through to the actual Linux server itself, just not MySQL (MariaDB).
I have also disabled the UFW firewall temporarily in order to keep that out of the equation.
What could be going wrong here?  What can I test further to isolate the problem?
I suspect that perhaps the bind-address = 0.0.0.0 in /etc/mysql/mariadb.conf.d/50-server.cnf may be the problem, but I thought that the quadruple 0 was in fact able to handle this.


texts: SQL Server 2012 High availability Groups for OLTP and Reporting environments

texts:
I would like to seek your advice in designing a high availability architecture at my current work place.

We are moving to SQL Server 2012 platform and we currently have a set of OLTP databases serving for the application usage and these databases  are being replicated (transactional, every 3 min) to another box for reporting purposes, I'm thinking of replacing replication with SQL Server 2012 High availability groups (asynchronous) (one of the reasons to bring high availability groups in to picture is to have HA in cases like database corruption (ex: page level corruption))

Q: as the indexing on report databases is different to indexing on application dB’s, the failover between app db and reporting db will not provide better performance to application or reports  on failover, this makes it a bit hard to design an effective cluster.

Please let me know if you can suggest a better solution for this situation.

Appreciate your help.

Cheers,
Kiran


texts: Query latency increases when connections to DB increases without any change in query throughput

texts:
We are currently using HikariCP to manage our connection pool and our ORM is eclispelink.

We have the following observation: On production, when the max number of DB connections to master database crosses a comfortable threshold then the performance (query latency increases) degrades. And this relation is independent of throughput. We have tried to reproduce this scenario multiple times in our performance testing environment. The observation is as follows: Max DB connections : 420
Transactional TPS reached (write operations) ~ 600

As soon as, we had added new connections and went to around 680. Latency had kicked in. There was no change in load from application servers. This is strange, since we have not added any more load, just by increasing number of connections, performance starts degrading. This continues till we bring back the number of connections to its comfortable value.

Are we missing something here?


texts: Tricking SQL server into thinking it's a different date

texts:
I have a unique problem (at least for me) that I have solved in one way but would like an easier way if possible :)

I often have to test our Windows app with dates in the past. Our app queries a SQL server which uses GETDATE.

I have setup a VM with our app and SQL server on it. I can change the system date backward or forward as needed after doing the below through powershell:

stopping windows time service
if running on HyperV, stop the vmictimesync service
setting the new date
restart time services
restarting the sql server (just in case) and our app

It seems to work very well - both our app and the SQL server can't tell the difference.

If I need to reset the date back to current I simply restart the windows time service. Easy.

However, it's a bit of effort to set that up at moments notice and was wondering if there is a way to fool the SQL server or an individual database into thinking it's a different date as needed?

So with the individual db perhaps I can set some properties such that it would shift GETDATE calls back to a certain time period.

All a stretch I know but if anyone has any other ideas I'm all ears.

Unfortunately our devs can't change the app to make this all easier.


texts:
Background

I'm managing a relatively small database project in which we are adding support for reporting involving status updates for items in out product table. I was bit thrown into this and I've only got about a month of ever writing SQL.

Problem Description
At it's core we have a central table [product] with a bigint unique key. We now want to record various messages that come in from a satellite application. The messages come in 2 major types (MessageA and MessageB) that are almost identical, MessageB contains an extra column that MessageA doesn't posses. Also of note is that there is no overlap between the 2 message_type columns and no columns are NULL. That is to say both messages have their own set of message_types.

MessageA:

id
timestamp
message_type
product_id
floor_id

MessageB:

id
timestamp
message_type
product_id
floor_id
section_number

What I tried
My initial design was to add 2 tables, one for each new data type exactly mirroring the datatypes. This "seemed" more "normalized" based off my month of so of SQL experience. But after I started writing a query that tried to combine the data into a report, I couldn't come up with a non-redundant query to build the dataset. My primitive query looked like:

Pseudocode

(
   SELECT MessageA.* FROM product
   WHERE <filtering crieteria on product>
   JOIN MessageA ON MessageA.product_id = product.id
)
UNION ALL
(
   SELECT MessageB.* FROM product
   WHERE <identical to first filter>
   JOIN MessageB ON MessageB.product_id = product.id
)

I'm a little paranoid about the long-term performance implications of querying [product] twice since it's our biggest table (adds up to 1M rows a year, maybe more) and the DB runs largely unmaintained off-site on consumer level hardware for an average life-cycle of 3-5 years between upgrades and we have had some reports trickle in of issue at out largest sites. These new 2 tables would potentially grow at 3-7 times the rate of [product] (possibly 5 million rows per year or more).

I started to think it might be simpler to just have 1 table and make section_number NULL. If section_number = NULL then it is or type A otherwise it is B.

 The actual question
Is this a good idea?

Should I be worrying about this optimization?

Is this even an optimization or just a more accommodating design?

I'm looking for some guidance whether I should shape the data based on "input" format or "output". Normalization is elegant but at what point should I bend the schema to look like the desired output structure?


texts: MySQL CURDATE() and CURRENT_TIMESTAMP return different value?

texts:
I am facing a little confusion because of CURRENT_DATE value and CURRENT_TIMESTAMP value shows different date on MySQL.

Scenario :
1) User calls API from Lumen backend to insert data on a table. One column used CURRENT_DATE (sales_date) as insert value and one column use CURRENT_TIMESTAMP (temp_timestamp) as default value

2) Query exactly looks like this :

$_reg = DB::insert("INSERT INTO t_sales(sales_no,ref_no, sales_date,customer_id,branch_id,user_id,sales_info) VALUES(?,?,curdate(),?,?,?,?)", [$a, $b, $cid, $branch, $uid,$info]);

3) Querying the result would give different value. Date column shows '2020-01-09' AND TIMESTAMP column shows '2020-01-10 06:44:33' (as per date insert.. I am on GMT+0700 timezone)

4) weirder results, if I log into mysql using CLI or SQLYog from my PC and issue SELECT CURRENT_DATE, CURRENT_TIMESTAMP both shows correct date '2020-01-10'

Environment :

Lumen backend located on same physical machine as MySQL backend
Lumen explicitely set timezone to Asia/Jakarta (which shouldn't matter as it is in PHP)
MySQL version is 8.0 with time_zone set to SYSTEM on my.cnf
System use Ubuntu 18.04 server with tzdata set to Asia/Jakarta (UTC+7). timedatectl shown like this as per now (of course when I insert earlier it shows 2020-01-09 for Universal Time and RTC time)

Local time: Fri 2020-01-10 06:33:37 WIB
Universal time: Fri 2020-01-10 01:33:37 UTC
RTC time: Fri 2020-01-10 01:33:38
Time zone: Asia/Jakarta (WIB, +0700)
System clock synchronized: yes
systemd-timesyncd.service active: yes
RTC in local TZ: no

Why CURRENT_DATE and CURRENT_TIMESTAMP have different value?


texts: MySQL - Performing an IF statement for each row in a query


texts: mysqldump has structure but no data, only when run from cron

texts:
I have a script with a mysqldump command that looks like this:
 mysqldump ${mysql_auth_args} \
        --force \
        --single-transaction \
        --quick \
        --compact \
        --extended-insert \
        --order-by-primary

I'm seeing the strangest behavior, and only on one machine: when I run the script directly, it works as expected. When it runs from cron though, the resulting file only has CREATE statements and no INSERTS
I've used this command to try to simulate the environment it runs in under cron and the output does contain data:
env -i "$(cat /etc/crontab | grep -v '#' | grep -v '^$' | grep -v /etc/cron)" /etc/cron.daily/restic-backup

On other seemingly identical VMs, this issue doesn't happen. Admittedly, this system is pretty dated:
$ mysqldump --version
mysqldump  Ver 10.13 Distrib 5.5.58, for debian-linux-gnu (x86_64)

What factors could possibly be causing mysqldump to behave as if --no-data were passed, but only when run by cron?


texts: Create a New AWS RDS Instance of only one database from a snapshot

texts:
I'm trying to Create a new AWS RDS SQL Server instance of only one database from a snapshot.

I've gone through the AWS documentation, but according to it when I restore from a snapshot it will create all the databases that were in it.

I wanted to just create a new instance with just one specific database from the snapshot.

I was hoping to achieve it using AWS SDK for .NET either using powershell or C#.


texts: Cannot Add "Active Directory - Password" as Linked Server into my SSMS

texts:
I'm trying to add Azure Database with Active Directory - Password as Linked Server, but its giving error while login.
I'm using Domain account (for e.g. abc@my-domain.com and password)
These credentials are working and I'm able to connect normally BUT NOT WORKING IN LINKED SERVER
EDIT:
Adding the screenshots.

ERROR DETAILS BELOW:

===================================
Create failed for LinkedServer ''.  (SqlManagerUI)
------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17213.0+((SSMS_Rel).171128-2020)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+LinkedServer&LinkId=20476
------------------------------ Program Location:
at
Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerPropertiesData.ApplyChanges()
at
Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerProperties.DoPreProcessExecution(RunType
runType, ExecutionMode& executionResult)    at
Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType
runType)    at
Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo
executionInfo, ExecutionMode& executionResult)    at
Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo
executionInfo, ExecutionMode& executionResult)    at
Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType
runType, Object sender)
===================================
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------ Program Location:
at
Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType, Boolean retry)    at
Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection
sqlCommands, ExecutionTypes executionType, Boolean retry)    at
Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection
queries, Boolean retry)    at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection
queries, Boolean includeDbContext, Boolean executeForAlter)    at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplFinish(StringCollection
createQuery, ScriptingPreferences sp)    at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
===================================
Name cannot be NULL. (.Net SqlClient Data Provider)
------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.1000&EvtSrc=MSSQLServer&EvtID=15004&LinkId=20476
------------------------------ Server Name: . Error Number: 15004 Severity: 16 State: 1 Procedure: sys.sp_validname Line Number: 17
------------------------------ Program Location:
at
Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction
action, Object execObject, DataSet fillDataSet, Boolean
catchException)    at
Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String
sqlCommand, ExecutionTypes executionType, Boolean retry)