texts: update stats on specific statistic not working?

texts:
I am using Ola Hallengren's maintenance solution and have come across something really strange.  I grabbed the command from the log table as below:
UPDATE STATISTICS [dbo].[APIActivityLog] [PK_APIActivityLog] WITH SAMPLE 25 PERCENT

But when I checked the stats the next day, I see that the timestamp for updating the stat matches when the command above ran (so i know it was accepted, and no errors were present), but the screenshot below shows the stat not being updated.

Next I manually ran the same command again to update with 25 percent and ssms said command completed, but the stat did not show updated.  I then ran it with fullscan as below:
update statistics apiactivitylog with fullscan

and finally all the stats on the table show as updated.  Any idea what might cause this?

This database is a clone of another database, and i checked the source database and I don't see a similar problem.  does anyone now if using dbccclonedatabase would behave like this?  i don't recall seeing anything in the documentation about that.  I am on SQL 2016 SP2-CU11 enterprise.  you'll also notice in the 1st screenshot that the rows count is really high, and the 2nd is really low.  the 1st shows the rows that i assume came from stats in the source database that had a lot more data.  the 2nd screenshot shows the true rows count in the DB since only a portion of data was moved into that DB.


texts:
I am having an issue which I can't resolve.

  delete from bb_gamelist_league

this operation is taking too long. There are 276224 rows in that column. Delete is performed without any WHERE conditions.

Here is the table:

CREATE TABLE bb_gamelist_league (
  id SERIAL  NOT NULL ,
  bb_league_id INTEGER   NOT NULL ,
  day_number INTEGER,
  date BIGINT   ,
  team_id1 INTEGER    ,
  team_id2 INTEGER    ,
  score1 SMALLINT    ,
  score2 SMALLINT    ,
  attended_people INTEGER    ,
  is_play_off BOOL    ,
  play_off_code VARCHAR(5),
  game_status BOOL    ,
  is_finished BOOL    ,
  was_taken_by_gameserv BOOL,
  taken_by_coordinator_status BOOL,
  seed TIMESTAMP,
  managerA_watching BOOL,
  managerB_watching BOOL,
  day_period VARCHAR(10),
  group_number VARCHAR(30),
PRIMARY KEY(id)  ,
  FOREIGN KEY(bb_league_id) REFERENCES bb_league(id),
  FOREIGN KEY (team_id1) REFERENCES bb_team_info(id),
  FOREIGN KEY (team_id2) REFERENCES bb_team_info(id));

CREATE INDEX bb_gamelist_league_FKIndex1 ON bb_gamelist_league (bb_league_id);

And there is no locks happening, the delete query is active and not waiting(information from pg_stat_activity).

I waited for one day but it didn't end. Also the CPU load is 100% by postgresql, though I'm having 2.24 Ghz CPU and memory is enough (there is free memory).

How can I understand the cause?

EDIT: there is an index for id column in that table (not listed in above create statement)


texts: Add NOT NULL constraint to large table without table scan

texts: Trying to add a NOT NULL constraint to a table with 1 billion rows. I cannot afford a table lock for more than a couple of seconds. Is there a way to prevent a full table scan during the alter table statement? I created an index on the column hoping it would be used but that doesn't seem to work. May be a check constraint? Other options? Thank you!


texts: Treat database field as a flat file source

texts:
We have a partner sending us csv data to a web service of ours that gets sent to a messaging system that is SQL Server based.

I am writing data to disk and then importing to another database by using this file as a flat file source in an SSIS Data Flow task.

Although this works I'd like to avoid using the file system and treat the database field as a flat file source.

Is this possible?

I'm very new to SSIS packages and searching for this scenario is very difficult because of the number of hits just relating to importing a csv file.


texts: Why Postgres uses a custom defined index instead of the default one on the primary key

texts:
I have a table called alpha with many columns, the primary key is just alpha_id. I have also an index (super_index) on that table that uses alpha_id and status, which is just another column.

I run a query against it,

EXPLAIN SELECT alpha_id,
       name,
       descr,
       data,
       to_char(past_date, 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"'),
       status,
       failure,
       FROM job
WHERE alpha_id = 'example_value';

and despite the fact that Postgres has created automatically an index for the primary key

pk_alpha

and that my only condition is using it, this one is never used, and super_index is always visited. Here is also the result of the explain command,

Index Scan using super_index on alpha  (cost=0.14..8.16 rows=1 width=798)
Index Cond: ((alpha_id)::text = 'example_value'::text)

I have read that all the decisions are based on statistics, but I do not understand the logic behind this one.

Additional info

The table and super_index are actually created using Liquibase, but this is what I get using pgAdmin,

    CREATE TABLE public.alpha
(
    alpha_id character varying(48) COLLATE pg_catalog."default" NOT NULL,
    name character varying(255) COLLATE pg_catalog."default",
    descr text COLLATE pg_catalog."default",
    data text COLLATE pg_catalog."default",
    past_date timestamp without time zone NOT NULL,
    status alpha_status NOT NULL DEFAULT 'Suspended'::alpha_status,
    percentage_complete double precision NOT NULL DEFAULT 0.00,
    failure text COLLATE pg_catalog."default",
    CONSTRAINT pk_alpha PRIMARY KEY (alpha_id)
)

CREATE INDEX super_index
    ON public.alpha USING btree
    (alpha_id COLLATE pg_catalog."default", alpha_status)
    TABLESPACE pg_default;

pg_alpha is the index created by Postgres by default on the primary key, and I do not have it listed in the Indexes list on pgAdmin, so I do not know how to retrieve its definition.


texts: Auto update "TIMESTAMP" column only when a manual phpmyadmin edit occurs?

texts:
I have a column in my MySQL database that uses a "TIMESTAMP" to capture the current time.  I have an attribute set to "on update CURRENT_TIMESTAMP" so it auto updates the column whenever and update change is made.

Works great, except I have a small issue. My database also tracks views/votes for each row so basically the TIMESTAMP column is always being update because of the views/votes being updated. This isn't exactly what I want as I only want the TIMESTAMP column to update when significant information is changed that is not views/votes.

Is there a way to make phpmyadmin force this column to update when I make a manual edit on a row using the phpmyadmin edit gui? Besides having to type in the current TIMESTAMP which is just too time consuming for me.

Thanks!


texts: How to join multiple tables (SQL) to find the difference between an order and shipped quantities?

texts:
I have a collection of orders that were submitted to an internal ERP, which were processed and partially shipped.

The table hierarchy is as follows:

Order table contains OrderReference(PO), OrderID
OrderItems table contains Product/Lot/Qty requested (OrderID is parentID that joins these tables)
Shipping contains OrderReference(PO), ShippingID
ShippingArchive contains Product/Lot/Qty that was shipped (ShippingID is parent that joins these tables)

I want to create a report that looks as follows:

OrderReference#

Order Requested / Shipped Items / Missing Pieces

PC/Lot/Qty    -   PC/Lot/Qty    - PC/Lot/Qty

I have used a temp table to get some of the data but I realize I'm taking the wrong approach. I should be able to do all of this with a single query, though it will seemingly be a bit complex.

I get held up when I try to think about how to join where pieces don't exist, and where to perform the mathematics in my sorting and filtering. Please help!

Sincere thanks :)


texts:
I am sorry if this is basic question. I am new to Cassandra and need help on a query.
Assume I want to host a service which accepts SurveyMonkey , now users are given a URL to take the survey and at backend results are stored in DB for persistence.
I have Cassandra DB with following Entities:
User ID - User Location -  Survey ID - Survey Content - URL - Survey response
User ID and Location is of user who created the survey.
Now I want to provide least latency to users taking this survey from all over the world. How should I Shard my DB so that some Shards are placed closest to user location and user gets ack once data is written to local quorum and then it can be synched to other locations.
EDIT:
Approach 1:
I will shard it on user ID and have local rep factor as 3 and 3 nodes at each DC location can be asynchronously be updated. Now when the request for write hits any of those other DCs then again they will search for concerned nodes based on user hash and after local quorum is satisfied then user will get ack and Db will asynchronously replicate on other Dc locations.
Approach 2:
Is there a way we can get user location who creates survey and then shard our DB based on location/region as majority of survey respondents may be from that region itself and then these DB nodes can replicate data to other DCs. In this approach is it possible to further shard data based on user id in each region ? Or the approach 1 looks good ?


texts: I have hard time understanding Postgresql OVERLAP function when provided Interval

texts:
On postgresql documentation whilst reading for date and time operations I have hard time understanding the following example:

SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
       (DATE '2001-10-30', DATE '2002-10-30');
Result: false

I failed to understand the reason why it returns false, is it because an Interval is being passed as a parameter?


texts:
I had a database in status SUSPECT (No items).  I have not Backup. I've only .mdf and .ldf files.
After reading all answer regarding this topic, I executed all commands to fix this status, but without success.
I also executed the following commands for recovering my database :

sp_detach_db 'Mydb'

and the following message:

  "Successfully detached database 'Mydb'." )

then executed
    sp_attach_db @dbname = N'Mydb',
       @filename1 = N'E:\MSSQL7\Data\Mydb_Data.MDF',
       @filename2 = N'E:\MSSQL7\Data\Mydb_Log.LDF'

and the following error message :

  ("Server: Msg 823, Level 24, State 1, Line 1 I/O error 38(Reached end
  of file.) detected during read of BUF pointer = 0x11c01980, page ptr =
  0x1f6bc000, pageid = (0x1:0x7bea01), dbid = 7, status = 0x801, file =
  E:\MSSQL7\Data\Mydb_Data.MDF. Connection Broken "

I executed command with the Service Manager stopped (I don't know if it's important)
Please I'm desperate. What can I do to recover my db.
I'm not expert in SQL Server and it would be appreciate if the answers were very clear


texts: How to convert a datefield from string to date?

texts:
Here is the query that I am using. What I need to extract is the data for the last 10 minutes. But no records found is the output for this query, I think this is due to the type of data that were inserted to created_at field, it should be date field but string were inserted. How to convert this field so I can use it on the $match

db.collectionName.aggregate([{
   $match : {
        created_at : {
               $gte : new Date(ISODate().getTime() - 1000 * 60 * 10)
                     }
            }
      }]);


texts: Postgresql: pg_dump with specified user generate different backup from su postgres -c pg_dump

texts:
I am trying to do a backup using this syntax:

su postgres -c "pg_dump mydatabase" >mydatabase.sql

The database dump is generated without erros but some data is missing.

The strangest thing is that if i run:

pg_dump --user=postgres -h 127.0.0.1 -p 5432 mydatabase >mydatabase.sql

The data looks fine. Wrong socket perhaps? But i do not have any other database installed on this machine.

Also, pg_dumpall looks like to runs in both forms without any problems? What am i missing?


texts: DB2 LUW V10.5 on Windows. How to Manually rotate the Diaglog using scripts

texts: In DB2 LUW V10.5 on Windows, is there a way to manually rotate the diaglog log file? Currently, it is almost 1GB and it is difficult to open it to find info.The diagsize is set to 0 and I cannot change it due to other constraints.


texts:
I am building a story site for pre school

The basic functions are students will be posting stories and others will be able to like/comment on that story

Below is the model of the DB

Stories
Story_Id
StoryTitle
StoryContent
StoryAuthor
StoryLikes {
LikerId,LikedTime
}
StoryComments{
AuthorId,AuthorComment
}

Is this model OK? How else can this be remodelled?

The problem I see here is, if I store it in this way, will be it faster when I query for all comments by a particular user and so on?


texts: Is there a way to insert multiple rows into a table with default values for all columns?

texts:
I can insert multiple rows into a table with default values for all columns the RBAR way:

create table course(course_id serial primary key);

do $$
begin
  for i in 1..100000 loop
    insert into course default values;
  end loop;
end;$$;

Is there a way of doing the same with a single SQL statement?


texts: Why am I not seeing any benefits of parallel execution when parallel clause is used?

texts:
I have these two methods of creating a table and populating with rows. The first one uses a
parallel (degree 3) clause, so I expected the inserts to be much faster. But I timed the time taken for a) and b) and it turns out to be almost the same. There is no indication that method a) is faster, in fact in some of the runs, method b) was faster.

I thought that parallel clause would perform the inserts in parallel and hence be much faster. What am I missing here?

a)

create table TAB1
(col0 number not null,
 col1 number,
 col2 number,
 col3 varchar2(25)) parallel (degree 3)
 storage (initial 100K next 100K pctincrease 0);

begin
 for i in 1..300000 loop
  insert into TAB1 values(i-1,i,300000-i,null);
 end loop;
 commit;
end;
/

b)

create table TAB2
(col0 number not null,
 col1 number,
 col2 number,
 col3 varchar2(25))
 storage (initial 100K next 100K pctincrease 0);

begin
 for i in 1..300000 loop
  insert into TAB2 values(i-1,i,300000-i,null);
 end loop;
 commit;
end;
/


texts: Why is this datetime index not being used?

texts:
I have a table like this:

CREATE TABLE TestTable
(
    [TestTableID] [int] IDENTITY(1,1) NOT NULL,
    [IntField1] [int] NOT NULL,
    [IntField2] [int] NOT NULL,
    [IntField3] [int] NOT NULL,
    [IntField4] [int] NOT NULL,
    [IntField5] [int] NOT NULL,
    [DateField1] [datetime] NOT NULL,
    [IntField6] [int] NOT NULL,
    [IntField7] [int] NOT NULL,
    [TextField1] [nvarchar](300) NULL,
    [DateField2] [datetime] NULL,
    [TextField2] [nvarchar](300) NULL,
    [DateField3] [datetime] NULL,
    [BoolField1] [bit] NULL
)

I have created an index like this:

CREATE NONCLUSTERED INDEX IX_TestTable_DateField1
ON TestTable(DateField1);

And now I have this query:

    DECLARE @startDate DATETIME = '20190101'
          , @endDate   DATETIME = '20200101'

    SELECT [TestTableID],
           [IntField1],
           [IntField2],
           [IntField3],
           [IntField4],
           [IntField5],
           [DateField1],
           [IntField6],
           [IntField7],
           [TextField1],
           [DateField2],
           [TextField2],
           [DateField3],
           [BoolField1]
      FROM TestTable
     WHERE DateField1 >= @startDate
       AND DateField1 < @endDate

This table has nearly 10,000,000 records, and this query will return nearly 10,000 records.

Now, I expected the query to at least use my index IX_TestTable_DateField1 (Index Scan + Key Lookup) but it is doing a Clustered Index Scan (on PK field). I think this is because the query is returning all fields for the table.

My previous thinking was:

If the index has INCLUDEd all fields, so the SqlServer will do an Index Seek;
If not included all fields, but if the field is used in WHERE or ORDER, it will use Index Scan + Key Lookup;
If neither 1 or 2, it will do a Clustered Index Scan;

Is this correct? Why an "Index Scan + Key Lookup" is not happening?


texts: How to add setParameter in mongod config?

texts:
When I am using setParameter in mongod config, I start mongod with error:

Unrecognized option: setParameter = enableLocalhostAuthBypass=1

How can I use setParameter from config file ?

P/S: I am using YAML config file format.


texts: SQL Server Occupies more than allocated memory. Possible memory leak

texts:
Using SQL Server 2012 64 bit (v11.0.6020.0 - 2012 SP3)

Below is the scenario:

On our production we have 32 GB DDR3 RAM installed:

Maximum memory limit has been set in SQL server to 16 GB ie 50% capacity:

When I fire up the task manager and check memory occupied in value it shows 16 GB which is correct:

But when I select memory occupied by Percentage, it shows 80%-85% which is NOT RIGHT:

This will keep on Increasing, until it occupies more than 95% and the

Entire system will slow down
Queries will time out

The only way to correct this is a server restart

My questions are

Is SQL Server leaking memory?
Any quick fix so I don't have to restart?
Permanent resolution?


texts: Check status of SQL restore with NORECOVERY before RECOVERY

texts:
We are going to move some 100+ databases and some of them are 1 TB each. To save time on Friday, we are going to perform restore from sunday's Full  backups using with norecovery. And we do this during the week. And Diff restore with recovery on Friday to catch up.

Now I noticed during the week that if a full restore with norecovery fails, it still says "Restoring" ??. One would expect that the database would disappear.

So the question is. How can I double check on thursday let's say, that all databases that are restored from full backups with norecovery, actually are successfully restored so far, when both failed (but initiated successfully) and non failed restores, seems to both show "restoring" state?

Regards


texts: What is hot spotting in the context of adding files to tempdb?

texts:
I'm trying to find out whether it is possible to add tempdb files to a SQL Server without having to restart the SQL Server service. I saw this answer here on Database Administrators:

Tempdb Add files require Restart

And one answer states:

  ADD - no outage required. Although as Sean from Microsoft pointed out,
  SQL will prefer to use the lower filled files. If you are going from 1
  data file and adding more, then SQL will use the new ones for a while,
  but your performance won't be worse than only having one file.
  However, if you have 2+ already and add one more, then it will hotspot
  on the new one and decrease performance.

However, a comment cautions the following:

  I would put an addendum on the "Add" part: "Add: No, but you'll most
  likely be imbalanced so you'll be hot spotting which could make things
  much worse."

I have the following questions about that comment but was instructed to ask those questions in a new question of my own (this one) rather than asking the commenter via comment in that question's answers.

Specifically:

What is hot spotting? (I got some info via Google but not in detail
what happens with hotspotting on tempdb after adding files)
What about hot spotting makes things much worse in tempdb?
What specific things in the DB would get much worse?


texts: How to automatically rename columns from same table join?

texts:
When I join two tables to avoid conflicts I use column aliases. But that is prone to errors if there a lot of columns. Also, some ORMS require to use hardcoded or dynamic prefixes to column names. Is there automatic way to rename columns in such a way that all columns from T22 start with "c_t22_" and all columns from T23 start with "c_t23_"?

select T1.id,
       T1.p1, T21.name as p1_name,
       T1.p2, T22.name as p2_name,
       T1.p3, T23.name as p3_name
from T1
join T2 as T21 on T1.p1 = T21.id,
join T2 as T22 on T1.p2 = T22.id,
join T2 as T23 on T1.p3 = T23.id


texts: Change login name for all objects on server

texts:
Example,

I have a create a windows login DOMAIN\GROUP01, and mapped that login to a database DATABASE01 with lets say DB_OWNER permissions

I want to change the login name, I went into Active Directory, changed the name to DOMAIN\GROUP01_OLD, and ran the following query in SQL, on the master db :

alter login [DOMAIN\GROUP01] with name = [DOMAIN\GROUP01_OLD]

So far so good, the login is renamed.
Now comes the actual problem. When I look into the security->users settings of DATABASE01, the login still has the old value; DOMAIN\GROUP01

How can I change this at one level, instead of doing it at multiple databases?

Reason I am asking this, i need to rename +/- 500 groups, I am doing this with scripts. This will take a hell of time if I need to change it manually for all databases at DOMAIN\GROUP01 level

PS: I am a SQL newbie, so please bear with me.


texts: Efficient schema to handle time interval queries on a huge table?

texts:
We have huge table (+10 millions rows) where we aggregate the values by a using a time interval search on a Datetime column. And right now, to build single page on our app, we are querying this table several times, resulting in a high delay on our queries.

This table have one particular property, the records are never updated after the insertion.

I see two solutions for this scenario, but I'm not sure which is better, and also which is recommended by database experts.

Optimize the query at maximum, trying to fetch everything in a single trip.
We can improve our database architecture to reduce the number of records, aggregating the old rows into auxiliar tables.

Example

My data look similar to a currency market, like this one: https://bitcoinity.org/markets. How they allow a quick query over different time intervals (minutes, hours, days, months, years...)?

Is there a well know solution for schemas like this one?

Background info

Ruby on Rails App;
MySQL
Few (or none) query optimization;
First rows dated from 2011.

Schema Details

Earnings

CREATE TABLE `earnings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `earner_id` int(11) DEFAULT NULL,
  `sale_id` int(11) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_earnings_on_sale_id` (`sale_id`),
  KEY `index_earnings_on_created_at` (`created_at`)
) ENGINE=Inno

Sample Query

SELECT DISTINCT *, count(amount) total FROM earnings
WHERE (created_at BETWEEN '2015-09-01 07:00:00' AND '2015-10-01 06:59:59')
  AND sale_id IN [....]
GROUP BY earner_id

Besides this query being very simple, it runs a lot of times for different timespan, like by month, or by the last 10 days. Thats why on my second idea (2.) I'm considering an aux table to cache the sums by each timespam. (see this example for the desired data aggregation https://bitcoinity.org/markets)


texts: Different thread states in query execution

texts:
My MySQL 5.1 based Intranet system is facing very bad performance during high load. When observed through MonYog, I found below thread states:

We can simply understand the different thread state by its name. Can someone provide the

differences among these states?
Why "sending data" is taking 76%?


texts: SQL Server inconsistently reporting missing indexes

texts:
I have a stored procedure that is not performing well and I am trying to tune it.  I am confused by what I see in SSMS and the Database Tuning Engine Advisor.

In SSMS I have 'Include Actual Execution Plan' turned on.  If I execute the stored procedure and look at the execution plan tab, no missing index is reported.  However if I take the body of the stored procedure and paste it into SSMS and execute the raw SQL, the execution plan tab does show a missing index.  Outside of that the plans displayed are the same.

Additionally, if I select either the stored procedure or the SQL statement and ask SSMS to Analyze Query in Database Engine Tuning Advisor (from the query menu) no missing index is reported for either scenario.

Why does executing the direct SQL statement show a missing index in SSMS but not in the Tuning Advisor?  Why would the missing index report be different for the direct sql statement vs the stored procedure that does the exact same thing?


texts: CREATE SEQUENCE using expressions with psql variables for parameters

texts:
I've been playing with Postgres for a week or so and I'm seeing if it's possible to set the min/max values for a sequence using an expression.
The specific goal is to automate create-scripts for disjoint ranges between servers to avoid key-conflicts in a multi-master setup.

For example, using psql:

CREATE SEQUENCE key_seq MINVALUE (:servernum * :stride)
                        MAXVALUE ((:servernum + 1) * :stride - 1);

This gives me a syntax error. Variables are interpolated verbatim, no calculations happen.

An alternative would be to set a variable based on the output of an expression:

\set minvalue (:servernum * :stride)

But the \set statement doesn't evaluate expressions.


texts: ROW_NUMBER() without PARTITION BY still generates Segment iterator

texts:
I'm writing on an upcoming blog post of mine on ranking and aggregate window functions, specifically the Segment and Sequence Project iterators. The way I understand it is that Segment identifies rows in a stream that constitute the end/beginning of a group, so the following query:

SELECT ROW_NUMBER() OVER (PARTITION BY someGroup ORDER BY someOrder)

Will use Segment to tell when a row belongs to a different group other than the previous row. The Sequence Project iterator then does the actual row number calculation, based on the output of the Segment iterator's output.

But the following query, using that logic, shouldn't have to include a Segment, because there's no partition expression.

SELECT ROW_NUMBER() OVER (ORDER BY someGroup, someOrder)

However, When I try this hypothesis both these queries use a Segment operator. The only difference is that the second query does not need a GroupBy on the Segment. Doesn't that eliminate the need for a Segment in the first place?

Example

CREATE TABLE dbo.someTable (
    someGroup   int NOT NULL,
    someOrder   int NOT NULL,
    someValue   numeric(8, 2) NOT NULL,
    PRIMARY KEY CLUSTERED (someGroup, someOrder)
);

--- Query 1:
SELECT ROW_NUMBER() OVER (PARTITION BY someGroup ORDER BY someOrder)
FROM dbo.someTable;

--- Query 2:
SELECT ROW_NUMBER() OVER (ORDER BY someGroup, someOrder)
FROM dbo.someTable;


texts:
Let's say I'm selling 3 products. I have a products table with id and name (so that table would have 3 entries). I want each product to have a serial number, each independent from one another, but I don't want to create a table for each product as the product count might grow.

How can I achieve this so that the serial number auto increments for each product? I figured I would need some pivot table but that requires me to make 3 pivot tables for each product.


texts:
I'm experiencing some troubles with the execution of DB2 express c on Mac OSX 10.6 Snow Leopard.

I have installed the db system, creating two users, db2 and db2-user. The latter is the instance owner. I have sourced db2profile and the env variables seem to be set correctly.

Db2 starts successfully, but I'm not able to run db2sampl, the error is:

Attempt to start DB2 instance failed.
SQL1019N  The node name "db2-user" specified in the command is not valid.

I have googled the error and it looks like I have to change the DB2INSTANCE variable, but I do not know what value should be.


texts:
Possible Duplicate:
  Why shouldn't we allow NULLs?

I've heard that it's a bad idea to do database design such that nullable fields exist. However, eliminating nullables often cause much more complex schemas.

Given a schema where elimination of the NULL is considerably more complex (e.g. replace any instance of NULLs with a separate table which gets JOIN'd), or using a nullable field, which is the better action, and why?


texts: Are there alternatives to the TOAD tool for SQL Server?

texts:
Are there any great tools as an alternative TOAD to be used for SQL Server?

The tool can be commerical or open source.


texts:
Few questions on ops-manager backup process

While backup ops manager creates a head db which is copy of the monogdb deployment
I want to know the reason behind this. Why create another copy and increase space consumed for backup
Ops manager takes snapshot of the head db which got created where Backup Daemon is running
What is the type of snapshot?
How ops manager maintains consistency in snapshots for whole cluster?


texts:
CREATE OR REPLACE TRIGGER  "TRANSACTION"
before
update of  totalunitsbought on SIPHOLDER referencing new AS NEW old AS OLD
for each row
begin
  declare
    mportfolioname varchar2(20);
    mportfoliono number;
    moperationmode varchar2(8);
    mtransactionworth number;
    munits number;

    mnav number;

  begin
    select portfoliono into mportfoliono from sipholder where accno=:new.accno;
    select portfolioname into mportfolioname from sip_price
      where portfoliono=mportfoliono;
    munits:= :new.totalunitsbought - :old.totalunitsbought;

    select nav into mnav from sip_price where portfoliono=mportfoliono;

   if :new.totalunitsbought>:old.totalunitsbought then
      moperationmode:='B';
      mtransactionworth:=munits*mnav;
   else
      moperationmode:='S';
      mtransactionworth:=munits*mnav;
   end if;

   insert into transaction
     (accno, ransdate, modeofoperation, units, transactionworth, portfoliono,
      portfolioname, oldunits, newunits)
    values
      (:new.accno, sysdate, moperationmode, munits, mtransactionworth,
       :new.portfoliono, mportfolioname, :old.totalunitsbought,
       :new.totalunitsbought);

  end;
end;

  ERROR:

  ERROR at line 1:
     ORA-04091: table ORA-04091: table NETBANKING.SIPHOLDER is mutating,
     trigger/function may not see it
     ORA-06512: at "NETBANKING.TRANSACTION", line 12
     ORA-04088: error during execution of trigger 'NETBANKING.TRANSACTION'
     ORA-06512: at "NETBANKING.BUYUNITS_FIFTEEN", line 23
     . is mutating, trigger/function may not see it
     ORA-06512: at "SYS.DBMS_ISCHED", line 150
     ORA-06512: at "SYS.DBMS_SCHEDULER", line 441
     ORA-06512: at line 1


texts: Preordering the table by a specified column

texts:
I have a table and I am always getting the data by ordering a column. In my case, I  order by datenum. However, the selection is so slow because of the ordering.

Is it possible to order the table after datenum. This column is defined as unique. If  defined as an index, would the selection will be faster?


texts:
Convert list of dates in a date range in SQL Server

For ex:


texts: Identify threads with maximum difference in time

texts:
I have many parallel executing queries in my SQL Server database. I need to find out queries where  the difference in time for the longest thread and the  shortest thread  is high (excluding co-ordinator thread). In fact, I need to find top 10 such queries with highest difference (from the queries that are getting exeuted in the span of 1 hour). How to track this information in SQL Server?

Note: I am using SQL Server 2012.

References

SQL Server 2012’s Information on Parallel Thread Usage - Joe Sack
Parallel Execution Plans – Branches and Threads - Paul White


texts:
I have a production database that is experiencing wildly fluctuating Page Life Expectancy (PLE) issues.  (It crashes to zero at random times.)
I have been researching the PLE issue and have found something that seems to point to a VMWare issue, but I am not sure I am using the data right.  It seems like I am losing buffer/cache pages.
I am using this query:
SELECT  COUNT(*) AS cached_pages_count,
        CASE database_id
            WHEN 32767 THEN 'ResourceDb'
            ELSE DB_NAME(database_id)
        END AS database_name
FROM    sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id), database_id
ORDER BY cached_pages_count DESC;

(Found here)
I am totaling the results (the count) before and after my PLE crashes.  An example is 1,097,820 before and 131,394 after.  So I seem to "lose" 966,426 pages.
My guess is that the hardware for all the virtual machines is under stress, so it will randomly swap out some memory from the server for a while.  (This is just a guess.)  When that happens all the pages are lost, so the PLE plummets.
So, am I using the sys.dm_os_buffer_descriptors view correctly?  From what I read it always shows used buffer/cached pages.  So if it is empty (or significantly reduced), I either don't have the memory anymore, or it is empty.
(I would love a way to confirm this conclusion.)
Or is there another explanation as to why the count drops so much?
Information below the line was added from the OP's comments

Our System Admins manage the VMs. I am hoping to understand my query before I go to them with this data. The timing of the PLE crashes seems random from the database point of view. (No re-indexing or other high performance stuff happening during the PLE Crashes)
I have done a ton of work to see if it was work load related. And while there is one poorly performing query, it is not enough to use up all the cache. [There is]  no rebuilding or other non-routine user activity on the server when the buffer counts go down. And even if it was, would I not see that being used in my query above? (Meaning if it was a SQL Server action, wouldn't the counts stay the same, just with different stuff?)
I don't have access to the VMWare settings. I was hoping to understand my findings better before involving those that do.  The point of this question was to ensure I was using the view correctly first.
At the end of the comment chain:
I was trying to say that the PLE issue lead me to the loss of Buffer Pages issue.  The query I was using to get PLE would show a low PLE because the pages were being lost. So what was in them was gone. It was a false reading because the amount of memory was reduced.
Here is my @@Version:
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
    Dec 28 2012 20:23:12
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)


texts:
First of all newbie here and to the world of Oracle, just trying to get some code working:

UPDATE CUSTOMERS
SET RE.LASTNAME = 'NO REFERRAL'
WHERE RE.LASTNAME IS NULL
   (SELECT CU.CUSTOMER#, INITCAP(CU.LASTNAME || ', ' || SUBSTR(CU.FIRSTNAME,1,1)) ||'.' AS "CUSTOMER NAME",INITCAP(RE.LASTNAME) AS "REFERRED LAST NAME", INITCAP(RE.FIRSTNAME) AS "REFERRED FIRST NAME"
FROM CUSTOMERS CU LEFT OUTER JOIN CUSTOMERS RE
ON CU.REFERRED = RE.CUSTOMER#);

The nested block works fine by giving me a listing of all customers and the person first and last name who referred them including customers who have no one who has referred them. The problem I'm having is the first and last name of referrer will come up as NULL for the people with no referrer. (as it should with the left join), but instead of saying null I would like it to say "No Referral" in the column instead.
I also had the referrer column concatenated together like the customer name at one point, but couldn't get it to do as I wanted hence splitting the name back up. If there is a solution to either problem I would be grateful as it has me stumped.


texts: Trans Log fills during delete, conceptual question

texts:
I'm learning everything I can about backups and transaction logs and such. I want a little feedback on if I'm understanding this, so I have a conceptual question. First, here is what I observed:

I did a massive delete of data (using SQL Management studio, typed the delete command manually), so much that the trans log filled up. The query appeared to keep running even when the trans log got full. I then cancelled the query, and it showed cancelling.

I then did a trans log backup, to clear the trans log, followed by a full backup (while the query was still cancelling). I got the expected error that the trans log was full and backup couldn't happen. I waited until the query finally stopped, then was able to do another trans log backup, and this time the full backup worked.

All of this was done on a test DB, as part of a tutorial on doing backups, to help teach about what the transaction log does. I was told it would behave this way, and my observation matched what I was told as part of the tutorial.

My conceptual question:

The trans log wouldn't backup and clear while the query was cancelling because the data in the trans log was being used to undo the delete, but once the delete was undone, the trans log was freed up to be cleared. Is that correct?

Thanks in advance!


texts:
I'm trying to design a database that contains, among others, entities "Photo", "Venues" and "Products".
Given both Venues and Products could contain a collection of photos... how could I tell whether the photo is from a Venue or a product?

Photos
    Id PK
    Venue_Id FK
    Product_Id FK

Venues
    ID PK
    Photos_Id FK

Products
    ID PK
    Photos_Id Fk


texts: What is the use of the select statement in SQL(ite) triggers?

texts:
In SQLite, triggers may contain four types of statements: insert, update, delete, and select. What is the use of the select statement, namely, how can I access the result-set of the select statement?

For example, with the following setup,

create table "Data" ("data" Text);
create trigger "Data-AI" after insert on "Data" begin
    /* some insert, update, or delete statements */
    select "data" as "selected" from "Data" where length("data") < 4;
            -- assume that there at least two rows in the result set
    /* some insert, update, or delete statements */
end;

how can I use "selected" in the second batch of insert, update, or delete statements?


texts: One table with many rows or many tables with a few rows

texts: I'm having a question rounding my mind. It's better to create one table and store a lot of rows there or create various tables and have a few rows on them? What's the performance and the I/O saved at physical level? How many space will use many tables instead of one table?  I'm thinking in how Wordpress handling the multiblog feature because I'm starting a new project and I'm looking for the best approach.


texts: CLR Assemblies and SQL Server Availability Groups

texts:
I have a SQL 2012 Availability group. On that I also have a database with a .Net assembly that is using PERMISSION_SET = UNSAFE.

CREATE ASSEMBLY drawing
  FROM C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Drawing.dll'
  WITH PERMISSION_SET = UNSAFE;
GO

When the system fails over to the Secondary Node the Assemblies don't work with an error like:

  Could not load file or assembly System.Drawing....

I can't seem to find a good reference on how CLR assemblies function in an Availability Group. Any assistance is greatly appreciated.


texts: Moving database objects to another database without wizard

texts:
I have several databases on production and i have also test environment. I don't have permission to do backup and restore operations. Actually i am using wizard to generate scripts (schema + data) and run sql on another database. Is there a way i can do this via script (SQL, PowerShell, C#, etc..) ? Can i move all database objects from production to test on one click ideally, it is possible that script will universal and input parameter will be database name for example ? The problems with generated scripts are two => SQL can be too large to open in sql manager and also it is time consuming to run wizard everytime, because i need production data often.

The final vision based on that universal script or job is that i have an web dashboard, where i can click on database or to have set of command shell scripts and the database will replicate to the test environment where i can modify data.

Any Idea ?


texts:
I am trying to create this procedure in MySQL and show the error mentioned below.

CREATE DEFINER=`root`@`%` PROCEDURE `synctables`()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
begin

insert into tdest
select s.keyval,s.val1,s.val2,s.val3 from tsource s left join tdest d
on s.keyval = d.keyval where d.keyval is null;

declare finished INTEGER DEFAULT 0;
declare keyv decimal;
declare v1 decimal;
declare v2 decimal;
declare v3 decimal;

DECLARE c1 CURSOR FOR select s.* from tsource s inner join tdest d on s.keyval = d.keyval;
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET finished = 1;
open c1;

getvals: loop
fetch c1 into keyv,v1,v2,v3;

if finished = 1 then
leave getvals;
end if;

update tdest set val1=@v1,val2=@v2,val3=@v3 where keyval=@keyv;

end loop getvals;
close c1;

end

  Error de SQL (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
  declare finished INTEGER DEFAULT 0;
  declare keyv decimal;
  declare v1 decimal;
  at line 13 */

When I delete this line the procedure is ok:

   insert into tdest
    select s.keyval,s.val1,s.val2,s.val3 from tsource s left join tdest d
    on s.keyval = d.keyval where d.keyval is null;

Any ideas why the insert into is giving me an error?


texts: SQL Server 2014 - Full text index never stops populating

texts:
I have a server that is running SQL Server 2014. I have full text indexes on some of my tables in my databases. The problem is that it's always saying populating and it never finishes. So my queries with contains in them never return anything.

I've compared this server to another server of mine that is working. The Full text feature is installed the service is running. They both are logging in as the same user.

The SQLFT logs have nothing in them. The event viewer is showing no errors.

Running a status query on the full text catalog returns.

  LastPopulated                       PopulateStatus
  1990-01-01 00:00:00.000     Full Population In Progress

running

EXEC sp_fulltext_table 'fullTextIndex', 'start_full'

or

ALTER FULLTEXT INDEX ON dbo.Table
START FULL POPULATION;

returns

  Warning: Request to start a full-text index population on table or
  indexed view 'fullTextIndex' is ignored because a population is
  currently active for this table or indexed view.

The CPU is low and there is about 3 gigs of memory available.

Looking at performance monitor for SQL Full-text filter Daemon it shows its not doing anything. Also stopping the population and restarting doesn't help nor does restarting the service.

Any Ideas on the issue or what to look at next?

UPDATE: I've still never be able to find the cause of this issue. The solution for now will be to migrate the databases off the machine that has the never ending full text index population.

UPDATE 2: While clearly not the same issue has I originally had, this did come up again for me and restarting the SQL server and machine fixed it. In my original issue this didn't work and we just moved our databases off that server.


texts: How should I set up these tables in my relational database?

texts:
I'm working on a project where a person can create a survey with custom questions and display the questions in whatever order they choose. They can break the questions into sections. The problem I'm having is that there are 2 or more groups of people. Each group can have the same question in different sections and/or in a different order. My tables right now are:

Group: key(gId), gName
Question: key(qId), qName
Section: key(sId), sName
QOrder: key(gId, qId), sId, qOrder

Is this okay or is there a better/more efficient way of doing this? Any help would be appreciated.


texts:
I want to update column in table A if the value of column A1 exist in one column of B table for example B2

update A
set A1 = 'F'
WHERE  A1 in (SELECT B2 FROM B );

I tried somethig like this but no success, i tried too with exists


texts: What is the "proper" way to design a database architecture when entry counts belogning to different users may vary wildly?

texts:
To give a context; we are going rewrite our application in Django framework. The database that is being used by the old system at the moment designed very poorly, without any respect to methodology of DB design, so we decided to re-design the database architecture as well.

One table, that we've been having trouble was something akin to "Items". Different users have different items, and the item amount varies wildly between users, so holding everything in shared tables seems like a bad idea. We thought asssigning private db processes to every single user (like, every user will have their own sqlite process, invisible from others), but I'm skeptical about this idea too as I've never seen or heard something like this in practice. It also feels like we are breaking a layer of abstraction for something we are not sure of.

My experience with such situations are very dim, so every bit of advice-help will be much appreciated.


texts: How do I write a conditional statement (IF) with MySQL?

texts:
What is wrong in below simple query? I tried to google it but not found

IF 1 = 1 THEN
  SELECT 1;
ELSE
  SELECT 12;
END IF;


texts:
I came across a linked server that is pointing to production from test environment with full access. Is there a way to find out who and when this linked server was created? Based on last modified date, I came to know this was created long back so but with least hope I took a look into default trace - as expected there is no luck.

My test server version is 2008R2.


texts: Converting MySQL AES_DECRYPT() to Postgres decrypt()

texts:
I have this MySQL query using AES_DECRYPT() that is the following:

SELECT *
FROM data_tbl
WHERE AES_DECRYPT(data_point,'aBcDeF')='data_1';

I've figured out as much that the equivalent in Postgres 11 is decrypt() (F.25.4. Raw Encryption Functions) and the query would look something like this:

SELECT *
FROM data_tbl
AND decrypt(data_point,'aBcDeF','aes')='data_1';

At least, the documentation for both leads me to believe that these are equivalent.

The data was dumped from MySQL and imported into PostgreSQL.

The data-point field in MySQL is type varbinary(255) and the values all show BLOB in MySQL Workbench.
The data_point field in Postgres is type bytea and the values all show [binary data] in pgAdmin.

Whether those data types are equivalent, I admit, I'm not sure.

The MySQL query works and finds a record based on he criteria. The PostgreSQL does not, although the record is in there. So it seems like one of three things:

The data types (varbinary(255) vs bytea) are not equivalent
The data does not match (BLOB vs [binary data]) are not equivalent
The functions (AES_DECRYPT vs decrypt()) are not equivalent

I did try to decrypt the value in PostgreSQL just to see what it is in a readable format by first trying:

SELECT decrypt(data_point, 'aBcDeF', 'aes')
FROM data_tbl;

But that just returned [binary data] for all the rows.

Then I came across this answer to use convert_from as in:

SELECT convert_from(decrypt(data_point, 'aBcDeF', 'aes'), 'SQL_ASCII')
FROM data_tbl;

However, I just get this error:

ERROR:  invalid byte sequence for encoding "UTF8": 0xcf 0xf5
SQL state: 22021

So at this point I'm stumped as to how to resolve the primary issue. The primary issue being doing the equivalent of AES_DECRYPT but in PostgreSQL 11. The last error message isn't so much an issue, but it is likely related to decrypt() not returning the same results.

Any suggestions?


texts:
Oracle 11.2.0.3 restoring to 11.2.0.4

Without going full blown Data Guard, how can I get Oracle to restore to a fairly current (within the last hour or two) state?  Moving the datafiles over takes about 12 hours, so I can't do a full blown restore in the timeframe.

So I was thinking I could move them over and keep moving the archivelogs over up into the point I want to restore and then replay all the logs since the datafiles move...BUT the logs only seem to want to replay until the point that I started the restore 12 hours earlier.  What else besides copying needs to happen?


texts: SQL Server Backup/Restore IO performance on Azure VM

texts:
We are currently experiencing an issue where the IO performance of our SQL Server running on an Azure VM isn't quite up to snuff.

We are running a Azure VM (Standard DS2). This one has a 127 GB SSD C: drive throttled at 64 MB/s. We added an extra drive to this VM because we believe it should be able to perform faster than the limited 64 MB/s.

When doing copy operations (using AZCopy) to the newly added & faster F: drive we can see that the IO speeds are a lot higher. However, when restoring a database backup from this new & fast F: drive to a location on that same drive we're still getting throttled at 64MB/s.

We've contacted Microsoft about this but up until now they were unable to provide a proper explanation. Anyone has any experience with this?

Attached you'll find a screenshot of the performance monitor during the restore, showing that the read & write operations are only happening on the new F: drive, while showing a fairly stable 64MB/s.


texts: Oracle: converting a TNS entry with SID to instantclient?

texts:
How do I convert a TNS entry that has a SID to instantclient format?

I'm able to connect with a TNS string but not with the equivalent (I thought!) instantclient string.

$ ./sqlplus 'myname/mypass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.example.com)(PORT=1521))(CONNECT_DATA=(SID=mysid)))'
Connected to: ...

$ ./sqlplus myname/mypass@//myhost.example.com:1521/mysid

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor


texts:
We have a job that is not scheduled to run but  was kicked off by what we suspect was another job. There is no job history but it does appear in the error logs. How can we find out if this is being called by another job? I know that we could use sp_start_job to immediately call a job within a job step but, if that is the case, how can we find it?

And, why would there not be any history for the job that ran?

Error log entry:

2017-05-01 10:01:50.40 spid6s         SPID: 131 ECID: 0 Statement Type: ALTER INDEX Line #: 1
2017-05-01 10:01:50.40 spid6s         Input Buf: Language Event: EXECUTE [msdb]. dbo.IndexOptimize @Databases = 'Database1,Database2'


texts:
I am getting the following error whist trying to execute a long running query.

  The table 'C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp#sql1664_349_19be' is full

The C drive (NTFS) on the server has 135GB free space.
The D drive (NTFS) which holds the data has 365GB free out of 800GB.

The server has 32GB RAM.

The query I am running is reasonably simple but it is run against 61 million rows.

I have 18 indexes on tblinvoice and the table is INNODB.

SELECT
    InvoiceNo,
    NetAmountAbs,
    InvoiceDate
  FROM tblinvoice
  WHERE GrossAmountAbs >= 200
  GROUP BY InvoiceNo,
           NetAmountAbs,
           InvoiceDate
  HAVING COUNT(*) > 1
  AND SUM(CASE WHEN NetAmount_Doc >= 0 THEN 1 ELSE 0 END) > 1
  AND MIN(AccountNumber) != MAX(AccountNumber)
  AND MAX(GrossAmountAbs) != MIN(GrossAmountAbs)
  AND MAX(ImportID) = 2

This is my.ini file

[mysqld]
log_bin_trust_function_creators=1
datadir=E:/MariaDB 10.2.8
port=3306
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
default_storage_engine=innodb
innodb_buffer_pool_size=25000M
character-set-server=utf8
innodb_io_capacity=200
innodb_read_io_threads=64
innodb_thread_concurrency=0
innodb_write_io_threads=64
max_connections=1000
innodb_lock_wait_timeout=50
lower_case_table_names=2
event_scheduler=on
character-set-server=utf8
max_allowed_packet=100M
connect_timeout=10
innodb-log-file-size=1G
innodb_log_buffer_size=64M
tmp_table_size=2G
max_heap_table_size=2G
[client]
port=3306
plugin-dir=C:/ProgramFiles/MariaDB 10.2/lib/plugin

Thanks

EDIT: Updated my.ini with suggested changes to innodb-log-file-size,
    innodb_log_buffer_size,
    tmp_table_size,
    max_heap_table_size,


texts:
If I drop a large index and re-create it, does that increase the size of the datafile where the index is stored?

When I drop an index, it's quite fast. However, when I build one, it takes time, and when checking free space on the datafile, I see it has been reduced.


texts: Need some new solution to shrink my SQL Server database log file

texts:
I am new bee for SQL Server dba. I need some suggestion to shrink the database log file.

I have tried and found some solution to shrink the file. But i think this is not opted solution for this.

Solution 1:

ALTER DATABASE nologtest2 SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (nologtest2_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE nologtest2 SET RECOVERY FULL;

I am not sure I can do this in production.

Solution 2:

  Backup log nologtest2 to disk='C:\MAS\nologtest32_log.trn'
  DBCC SHRINKFILE (nologtest2_log, 1);

We can took some transaction log backup and then we can shrink the database. But i believe  it will break the lsn chain. Then we may face some issue in logshipping environment.

I need some very opted solution for reduce the database log file issues.

Note:

I am not very familiar with being a SQL Server dba. If i am ask anything wrong or understandingly please correct me.


texts: SSAS - Applying Conversion Rates for Units of Measure

texts:
So I've been playing with this for the last day or so, and haven't been able to get an closer.

I have a SSAS cube that has a measure group which displays total execution times for events in seconds. What I'm looking to do is give our users the option to select which time unit they want to display their results in (ie. minutes, hours, days, weeks, etc.).

On the surface, it seems similar to performing currency conversion. So using AdventureWorks as my template, I created 2 additional tables.

CREATE TABLE [Dim_DurationUnits]
(
    [DurationUnit_SK] INT NOT NULL,
    [DurationName] VARCHAR(50) NOT NULL,
    CONSTRAINT [PK_Dim_DurationUnits] PRIMARY KEY CLUSTERED ([DurationUnit_SK] ASC)
)
CREATE TABLE [Fact_DurationConversions]
(
    [DurationConversion_SK] INT NOT NULL,
    [DurationUnit_SK] INT NOT NULL,
    [DurationConversionValue] DECIMAL(18, 7) NOT NULL,
    CONSTRAINT [PK_Fact_DurationConversions] PRIMARY KEY CLUSTERED ([DurationConversion_SK] ASC),
    CONSTRAINT [FK_Dim_DurationTypes_Fact_DurationConversions] FOREIGN KEY ([DurationUnit_SK]) REFERENCES [Dim_DurationUnits]([DurationUnit_SK])
)
INSERT INTO [Dim_DurationUnits] ([DurationUnit_SK], [DurationName])
SELECT [DurationUnit_SK], [DurationName]
FROM (
    SELECT 1 [DurationUnit_SK], 'Seconds' [DurationName]
    UNION ALL
    SELECT 2 [DurationUnit_SK], 'Minutes' [DurationName]
    UNION ALL
    SELECT 3 [DurationUnit_SK], 'Hours' [DurationName]
    UNION ALL
    SELECT 4 [DurationUnit_SK], 'Days' [DurationName]
    UNION ALL
    SELECT 5 [DurationUnit_SK], 'Weeks' [DurationName]
    UNION ALL
    SELECT 6 [DurationUnit_SK], 'Months (30 days)' [DurationName]
) A

INSERT INTO [Fact_DurationConversions] ([DurationConversion_SK],[DurationUnit_SK],[DurationConversionValue])
SELECT [DurationConversion_SK],[DurationUnit_SK],[DurationConversionValue]
FROM (
    SELECT 1 [DurationConversion_SK], 1 [DurationUnit_SK], 1 [DurationConversionValue]  -- Seconds
    UNION ALL
    SELECT 2 [DurationConversion_SK], 1 [DurationUnit_SK], 60 [DurationConversionValue]  -- Minutes
    UNION ALL
    SELECT 3 [DurationConversion_SK], 1 [DurationUnit_SK], 60 * 60 [DurationConversionValue]  -- Hours
    UNION ALL
    SELECT 4 [DurationConversion_SK], 1 [DurationUnit_SK], 24 * 60 * 60 [DurationConversionValue]  -- Days
    UNION ALL
    SELECT 5 [DurationConversion_SK], 1 [DurationUnit_SK], 7 * 24 * 60 * 60 [DurationConversionValue]  -- Weeks
    UNION ALL
    SELECT 6 [DurationConversion_SK], 1 [DurationUnit_SK], 30 * 7 * 24 * 60 * 60 [DurationConversionValue]  -- Months
) A

With that complete, I added the duration unit dimension and the conversion rate measure group to my cube, and updated my total duration measure with a measure expression ([total duration] / [conversion rate]). And it's at this point I'm stuck. Because there isn't a bridge between the conversion rate table and my fact table (ie. the exchange rate date like in Adv.Wrks), I can't define the relationship in the dimension usage tab. As well, the lack of a time dimension is preventing me from using any of the semi-additive measures adventure works uses (AverageOfChildren, LastNonEmpty) on the conversion rate measure.

Anyone know how I can get this to work? I've been searching and trying things, and can't seem to come up with a solution for what I thought would be a common problem.


texts: Using Echo statement writes Echo is ON in the text file

texts:
Following code is used for write a number in the text file but instead it is writing "Echo is ON" into the text file.

SET @CMD = 'ECHO '+@Ver+'>'+@Vercheck
  Print @CMD

EXEC xp_cmdshell @CMD
  ,NO_OUTPUT

Print @CMD  gives  "ECHO 1>C:\Pharmsuite\Vercheck.txt"

Here instead of 1, the query is writing "Echo is ON".


texts:
Our ETL flow has a long-running SELECT INTO-statement, that's creating a table on the fly, and populating it with several hundred million records.

The statement looks something like SELECT ... INTO DestTable FROM SrcTable

For monitoring purposes, we would like to get a rough idea of the progress of this statement, while it is executing (approx. rowcount, written number of bytes, or similar).

We tried the following to no avail:

-- Is blocked by the SELECT INTO statement:
select count(*) from DestTable with (nolock)

-- Returns 0, 0:
select rows, rowmodctr
from sysindexes with (nolock)
where id = object_id('DestTable')

-- Returns 0:
select rows
from sys.partitions
where object_id = object_id('DestTable')

Furthermore, we can see the transaction in sys.dm_tran_active_transactions, but I was not able to find a way to get the count of affected rows on a given transaction_id (something similar to @@ROWCOUNT perhaps, but with the transaction_id as argument).

I understand that on SQL Server the SELECT INTO-statement is both a DDL and a DML statement in one, and as such, the implicit table creation will be a locking operation. I still think there must be some clever way to obtain some kind of progress information while the statement is running.


texts: How can I generate the code-containing comments like mysqldump?

texts:
I'm needing to duplicate the mysqldump command in Python, and I am trying to figure out how automatically generate the code comments like mysqldump generates.

If you're wondering why I am doing such a stupid thing, it's because I am creating a utility that will make daily dumps of the production server for use in dev environments, but I must anonymize all of the fields that contain private data during the dump.  It will also change API keys to test accounts, change passwords for important user accounts, etc.  (Obviously this is not our main backup system, but something tailored for use by the devs, so that they can always use a safe snapshot of the production database to work with.)

I'm able to do a lot of this already, but I can't figure out how to automatically generate the correct "code comments" (e.g., "/*!40101 SET @saved_cs_client = @@character_set_client */;") that mysqldump includes before/after the CREATE TABLE statement and the INSERT statements.  Obviously I could hard code them, but that just feels wrong, and is potentially short-sighted in terms of portability of code and longevity of use.

Thanks for any ideas you can share!


texts: Innobackupex restore steps from 1 incremental and multiple incremental steps

texts:
My current backup plan is:
Full -> Incremental1 based on full -> incremental2 based on incremental1 -> incremental3 based on incremental2...

Please validate if this steps is correct during emergency restore:

to restore from 1 incremental backup:
innobackupex --apply-log --redo-only FULL-BACKUP-DIR
innobackupex --apply-log FULL-BACKUP-DIR --incremental-dir=INC-BACKUP-DIR1

to restore from multiple incremental backups:
innobackupex --apply-log --redo-only FULL-BACKUP-DIR
innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1
innobackupex --apply-log --redo-only INCREMENTAL-DIR-1 --incremental-dir=INCREMENTAL-DIR-2
innobackupex --apply-log --redo-only INCREMENTAL-DIR-2 --incremental-dir=INCREMENTAL-DIR-3
.
.
innobackupex --apply-log FULL-BACKUP-DIR --incremental-dir=INCREMENTAL-DIR-LAST


texts: (Ola Hallengren) Deleting transaction log backups

texts:
We're using Ola's script to run weekly full, nightly diff and hourly transaction logs. All the jobs are correctly creating their backups, and the full and diff jobs are cleaning up after a week, transaction logs are set to clean up after 48 hours (we backup the NAS that the files are stored on nightly so these files are still accessible if we need them, they're just out of the way).

I'm finding transaction logs on certain databases aren't cleaning up, ie when I look at the file-system I can find transaction logs from a month ago dating right forward to today. The only reason I could think for this  is if a more recent diff backup doesn't exists, but they do, and they've not been moved to a different directory. None of the jobs are recording any any kind of failure triggered manually or running from the schedule. The other jobs are running and cleaning up just fine, ruling out permission issues (I've also checked on the NAS, the SQL server agent has Full Control).

Current step being run by the log backup job:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'\\server\folder\', @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 48, @CheckSum = 'Y', @LogToTable = 'Y'" -b

Here's the Diff job that cleans up just fine for comparison

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'\\servername\folder', @BackupType = 'DIFF', @Verify = 'Y', @CleanupTime = 192, @CheckSum = 'Y', @LogToTable = 'Y'" -b

Does anyone know  more about how the job checks for the presence of the a full or diff backup? Or any other reason this could be happening?

Thanks


texts: MySQL workbench alter table is incomplete

texts:
I have just upgraded MySQL 5.6 to 5.7 and workbench from 6.2 to 6.3.6 to have the latest and do some modelling. The upgrade went fine and the app runs perfectly but something is different and I can't figure it out...

When I right click and look at Table Inspector, everything is as expected with columns, indexes and foreign keys. However when right click and select Alter Table the column list is incomplete, there are no indexes  and no foreign keys.

This is only failing since the upgrade but I can't find anything with the config or privileges to get it working. Any help would be greatly appreciated.

*** further observation:
If there is no timestamp datatype everything looks fine. However when there is, the alter table window always stops showing columns at the first timestamp and never shows indexes or FK's even though every other approach proves they exist.


texts: Prometheus High Memory and CPU Usage in PMM

texts:
We are running PMM v1.17.0 and prometheus is causing huge cpu and mem usage (200% CPU and 100% RAM), and pmm went down because of this. We are running PMM on a VM with 2vCPUs and 7.5G RAM, and are monitoring about 25 servers. PMM is running with below command >>

docker run -d -it --volumes-from pmm-data --name pmm-server  -e QUERIES_RETENTION=1095   -p 80:80   -e METRICS_RESOLUTION=3s  --restart always percona/pmm-server:1

The prometheus.log is filled with below entries:

level=warn ts=2020-01-30T10:27:12.8156514Z caller=scrape.go:713 component="scrape manager" scrape_pool=mysql-mr target=https://10.40.4.21:42002/metrics-mr msg="append failed" err="out of order sample"
level=warn ts=2020-01-30T10:27:26.464361371Z caller=scrape.go:945 component="scrape manager" scrape_pool=mysql-mr target=https://10.40.4.223:42002/metrics-mr msg="Error on ingesting samples with different value but same timestamp" num_dropped=1
level=warn ts=2020-01-30T10:27:27.81316996Z caller=scrape.go:942 component="scrape manager" scrape_pool=mysql-mr target=https://10.40.4.21:42002/metrics-mr msg="Error on ingesting out-of-order samples" num_dropped=2
level=warn ts=2020-01-30T10:27:27.813257165Z caller=scrape.go:713 component="scrape manager" scrape_pool=mysql-mr target=https://10.40.4.21:42002/metrics-mr msg="append failed" err="out of order sample"
level=warn ts=2020-01-30T10:27:41.462420708Z caller=scrape.go:945 component="scrape manager" scrape_pool=mysql-mr target=https://10.40.4.223:42002/metrics-mr msg="Error on ingesting samples with different value but same timestamp" num_dropped=1
level=warn ts=2020-01-30T10:27:42.813356387Z caller=scrape.go:942 component="scrape manager" scrape_pool=mysql-mr target=https://10.40.4.21:42002/metrics-mr msg="Error on ingesting out-of-order samples" num_dropped=2
level=warn ts=2020-01-30T10:27:42.813441108Z caller=scrape.go:713 component="scrape manager" scrape_pool=mysql-mr target=https://10.40.4.21:42002/metrics-mr msg="append failed" err="out of order sample"
level=warn ts=2020-01-30T10:27:56.463798729Z caller=scrape.go:945 component="scrape manager" scrape_pool=mysql-mr target=https://10.40.4.223:42002/metrics-mr msg="Error on ingesting samples with different value but same timestamp" num_dropped=1
level=warn ts=2020-01-30T10:27:57.82083775Z caller=scrape.go:942 component="scrape manager" scrape_pool=mysql-mr target=https://10.40.4.21:42002/metrics-mr msg="Error on ingesting out-of-order samples" num_dropped=2
level=warn ts=2020-01-30T10:27:57.820912309Z caller=scrape.go:713 component="scrape manager" scrape_pool=mysql-mr target=https://10.40.4.21:42002/metrics-mr msg="append failed" err="out of order sample"

Can someone please let me know why prometheus is causing issue? Any parameters we need to add/change?


texts:
Follow up of question

I have the following data:

I have the following sample data for permutations and combination.

create table tbltest
(
    name varchar(50),
    addres varchar(100)
);

insert into tbltest values('Sam Mak John','Street 1 HNo 101 USA');
insert into tbltest values('Donatella Nobatti','HNo 101 UK');
insert into tbltest values('Sam Buca','Main Road B Block UAE');
insert into tbltest values('Juan Soponatime','Hight Street CA');
insert into tbltest values('Aaron Spacemuseum','HNo A10 100 feet Road A Block ');
insert into tbltest values('Nasir Ahmed Amir Ahmed Mehendy','UAE High Street');

For an example expected result:

name
----------------
John Mak Sam
John Sam Mak
Mak John Sam
Mak Sam John
Sam John Mak
Sam Mak John
....
....

Tried the solution from previous question:

--To store Words
CREATE TABLE dbo.NameParts
(
  ID int NOT NULL,
  NamePart varchar(30) NOT NULL
);

--Table variable to identity column for each words
DECLARE @Inter TABLE (id int identity(1,1), names varchar(max));

--Insert into table variable
INSERT INTO @Inter(names) SELECT name from TblTest;

--Query to insert into NameParts table
;WITH splitdata
AS
(
    SELECT id,splitname
    FROM
    (
        SELECT *,Cast('<X>' + Replace(F.Names, ' ', '</X><X>') + '</X>' AS XML) AS  xmlfilter
        FROM   @Inter F
    )F1
    CROSS apply
    (
        SELECT fdata.d.value('.', 'varchar(50)') AS splitName
        FROM   f1.xmlfilter.nodes('X') AS fdata(d)
    ) O
)
INSERT INTO dbo.NameParts
SELECT *
FROM splitdata;

Query for Permutation and Combination:

--Query for Permutation and Combination
;WITH
  permutations AS
  (
    SELECT
      ID = t.ID,
      FullNameVariation = CAST(t.NamePart AS varchar(500)),
      Level = COUNT(*) OVER (PARTITION BY t.ID)
    FROM
      dbo.NameParts AS t
    UNION ALL
    SELECT
      ID = t.ID,
      FullNameVariation = CAST(p.FullNameVariation + ' ' + t.NamePart AS varchar(500)),
      Level = p.Level - 1
    FROM
      dbo.NameParts AS t
      INNER JOIN permutations AS p ON t.ID = p.ID
    WHERE 1=1
      AND p.Level > 1
      AND ' ' + p.FullNameVariation + ' ' NOT LIKE '% ' + t.NamePart + ' %'
  )
SELECT
  ID,
  FullNameVariation
FROM
  permutations
WHERE
  Level = 1
ORDER BY
  ID,
  FullNameVariation
OPTION (MAXRECURSION 5100);

Note: Above query works fine but unable to get following record in the result set:

Nasir Ahmed Amir Ahmed Mehendy

and that is because of this condition:

' ' + p.FullNameVariation + ' ' NOT LIKE '% ' + t.NamePart + ' %'

The word Ahmed is repeated and the above condition not met.


texts: MySQL warm up time after restart because of lost connection

texts:
MySQL 5.6.10 on Windows 7.

I'm cleaning up and paring down a large INNODB table. Its .ibd file is originally 22 gigs. While I was in the middle of an ALTER, dropping some columns, the mysqld.exe process died.

Upon restarting and starting up mysqld, I noticed a lot of disk activity (~10mb/s) from mysqld, that has been going on for around 15 minutes so far. Also, in my data folder, there are files with names like "#sql-7b8_3.frm".

Could someone shed light on what's going on?


texts:
I'm a SQL newbie and would like some help please.

This is my table:

ID  Name  Number  Src_Call_Leg  Dest_Call_Leg
--  ----  ------  ------------  -------------
1   John  5555    100           1000
2   John  5555    100           102

The table stores call records. When a call is made to 5555, the system first took source call leg 100 and destination call leg 1000 and a final destination call leg of 102 before it was answered by John. The source call leg does not change in the whole process.

What I'd like is to show only the data which has Name, Number, Source Call Leg and the final destination call leg for records where the interim call leg starts with 1000.

I tried doing this but it didn't work. This is my attempt:

select
  tb.name,
  tb.number,
  tb.Src_Call,
  tb.Dest_Call_leg
from
  table tb
where
  tb.Src_Call_Leg IN (
    select
      tb1.Src_Call_Leg
    from
      table tb1
    where
      tb1.Dest_Call_Leg like '1000%'
  );

I know I'm doing something really silly, would appreciate it if someone could point that out.


texts: PostgreSQL upsert implications on read performance after bulk load

texts:
In PostgreSQL documentation on populating databases, they mention how we can improve bulk loading operations by disabling constraints and indexes, but seeing how INSERT ... ON CONFLICT (upsert) requires the use of a unique constraint to work, and that perfectly makes sense, I've been wondering how good the following strategy is:

Create multiple connections to load data in batches in parallel into a staging unlogged table
Create deferred constraints to do integrity validation on the table's data
Upsert the staging data into a big target table (5GB+), which also would need to have a PK, unique index or unique constraint to make the upsert possible.

The insert on conflict approach is known to be better than manualing creating SQL functions or scripts to join between the two tables to discover the "new records" to insert and "common records" to be updated. That's the whole point of why INSERT ... ON CONFLICT exists.

Yet I'm still wondering the performance implications for reading the data after loading it.

Questions:

In terms of index bloat, an upsert would be worse than simple bulk copying inserts + update to a table without constraints? Is upsert bad for index maintenance?
If that is the case, I believe faster loads would also imply bad query performance after the table is loaded. Should I recreate the indexes after INSERT ... ON CONFLICT?
Compared to INSERT ... ON CONFLICT, the function/script way of doing it, while less performant for loads, would be better for index maitenance since the target table doesn't need to have an index to do the upserting?


texts:
Where should I put the filters on a TSQL query?

SELECT a.colum FROM A
JOIN B  ON A.ID =  B.ID AND B.STATUS  = 1

OR

SELECT a.colum FROM A
JOIN B  ON A.ID =  B.ID
WHERE  B.STATUS  = 1

I'm looking for performance issues or good practices.


texts:
Within a container, I have SqlServer running in a docker container. It was created like so:

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=$1" -p 1433:1433 --name sql1 -v sql1-sqlvolume:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2017-latest

I then use SSMS to import a data-tier application. Then in the wizard I import from disk and verify all the data until it then runs the import.

With Sql Server Settings:

Data file path: /var/opt/mssql/data
Log file path: /var/opt/mssql/data

But every time, it fails with this:

Creating database on target - Success
Importing database - Error
Creating deployment plan - Error
Initializing deployment - Error
Refreshing Object Exporer - Success

The Error is:

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

Object reference not set to an instance of an object. (Microsoft.Data.Tools.Schema.Sql)

------------------------------
Program Location:

at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.DeploymentScriptDomGenerator.GenerateFragment(Int32 operation, IModelElement element)
at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.DeploymentScriptDomGenerator.GenerateSteps(Int32 operation, IModelElement element)
at Microsoft.Data.Tools.Schema.Sql.Deployment.OrderedStepGenerator.Add(DeploymentScriptDomGenerator scriptDom, IModelElement element, Int32 operation)
at Microsoft.Data.Tools.Schema.Sql.Deployment.Analyzers.PlanMediator.BuildDependencyOrderedSteps(Int32 operation, List`1 classOrder, List`1 operationOrder, Dictionary`2 changes, Boolean preserveGraphs, Dictionary`2& relating, Dictionary`2& related)
at Microsoft.Data.Tools.Schema.Sql.Deployment.Analyzers.PlanMediator.BuildOperations()
at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.OnGeneratePlan()
at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentPlanGenerator.GeneratePlan(List`1 drops)
at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.BuildPlan()
at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.Controller.CreatePlan()
at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass3.<>c__DisplayClass5.<CreatePlanInitializationOperation>b__1()
at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action)
at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass3.<CreatePlanInitializationOperation>b__0(Object operation, CancellationToken token)
at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)
at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext, Action`3 reportPlanOperation, Boolean executePlan)
at Microsoft.SqlServer.Dac.DacServices.ImportBacpac(BacPackage package, String targetDatabaseName, DacImportOptions importOptions, Nullable`1 cancellationToken)
at Microsoft.SqlServer.Dac.DacServices.ImportBacpac(BacPackage package, String targetDatabaseName, DacAzureDatabaseSpecification creationDefaults, Nullable`1 cancellationToken)
at Microsoft.SqlServer.Management.Dac.DacWizard.ImportDatabase.DoWork()
at Microsoft.SqlServer.Management.TaskForms.SimpleWorkItem.Run()

Can anybody see what I am doing wrong here?

======

UPDATE: The .bacpac is created from a db running in Azure Cloud. This also happens when using 2019-latest.


texts: Querying special character from SQL Server

texts:
When I saved special characters in a column of type nvarchar, I did not use the prefix 'N', so the characters got saved as ??.

Is there any way to retrieve the original special characters from these question marks?

Any kind of suggestion will be appreciated.


texts:
The SS error log indicates the following at a frequency of 2 to 3 times per hour:

  Logon failed for login 'NT AUTHORITY\NETWORK SERVICE' due to trigger execution. [CLIENT: ]

There is no logon trigger. How do I discover what trigger is causing the problem since the error log does not say?

(This is on a Win 7 Pro computer running SS 2008r2 developer.)


texts: postgres: do I really need to use Collate and Ctype when restoring database?

texts:
Need to import in Linux a Windows postgres exported database.
In windows it's listed as follows:
    Name     |  Owner   | Encoding |      Collate       |       Ctype        |   Access privileges
-------------+----------+----------+--------------------+--------------------+-----------------------
   the_db    | postgres | UTF8     | Hebrew_Israel.1255 | Hebrew_Israel.1255 |

Do I really need to create the database using the same collate and ctype before restoring?
I already tested, and I can import just by creating a new database which will make it using its defaults:
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 the_db    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

Restoring was successful. Hoever, I am afraid because of the application, not to cause any errors when interacting with the db later.
What are your consideration gentleman?


texts:
I have the following query

select
ps.id, ps.title
, IFNULL(s.likes,0) as num_likes
, IFNULL(s.comments,0) as num_comments
, IFNULL(s.ratings,0) as num_ratings
, IFNULL(s.views,0) as num_views
, IFNULL(s.avg_rating,0) as avg_rating

from  ps
left outer join (
    select presiid,
    sum(views) as views,
    sum(likes) as likes,
    sum(ratings) as ratings,
    avg(ratings) as avg_rating,
    sum(downloads) as downloads,
    sum(comments) as comments,
    sum(embeds) as embeds,
    sum(shares) as shares
    from tblstatistics
    group by presiid
) s
on s.presiid = ps.id

where ps.active = 1
order by
datepublished desc
LIMIT 0, 12

Running EXPLAIN on the above shows this

1   PRIMARY ps  ref active  active  1   const   402 Using temporary; Using filesort
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    334
2   DERIVED tblstatistics   ALL NULL    NULL    NULL    NULL    2643    Using temporary; Using filesort

As you can see this is not good....tblstatistics is not using any indexes and is scanning the entire table. How can I optimize this query and make it use indexes? There are over 100,000 rows in the DB on the production DB where this query runs.

Any help will be appreciated....


texts: Closed queries re-open when connecting to DB using MySQL Workbench

texts:
Using MySQL Workbench latest version (6.3) I've set up an Amazon EC2 connection successfully. Every time I open the connection, I've noticed all queries I did before are being opened, even if I closed them using right click -> "Close All" option. These queries have dynamic names like {0303FFF4-FB6D-4EF3-A9EC-2...

I don't want these queries to remain stored, as most of the time they're just temporary queries I need for some quick data gathering.

Can somebody please help me with these? What do I need to do?


texts: Methods for identifying differences in accumulating history table?

texts:
This question will probably be elementary to most DBAs.

I'm doing light DB work which isn't my specialty and I'd like to create some kind of "change log" for a table that is copy/appended to a history table each morning. The table is being created in the fashion below, where the entire contents of one table is being appended to a history table with a column on the end called backup_date.

I wrote some SQL to catch new records and deleted records from the previous day but what is the best method to determine what records have changed?

For example: on March 9th, Mr. Gates told HR that he prefers to go by "Bill" instead of "William".  Data for March 10th shows that HR made a change to his first name.  What is the best method of attack to compare to previous data in the table?  I'm mostly interested in comparing to the previous day (each day) but what if I wanted a list of all changes to a particular record?


texts: Implementation Idea for login structure (login id with two different tables)

texts:
I am creating a sample web application for a business and I am stuck at the login implementation. There is supposed to be two different users (login types?). I will have employees and customers. I have a table for employees and a table for customers set up already. Let me caveat that I understand the following is not SQL, just the notes I've taken so far in a .txt file.

I have my employees

EMPLOYEES
    Employee_ID  CHAR(5) - 'EM###' - NOT NULL //PK
    ...(columns unimportant to the scope of this question)
    Web_Login_ID

I have my customers

CUSTOMERS
    Customer_ID //PK
    ...(more columns)
    Web_Login_ID

And here is where I get stuck at the web login

WEB_USER_LOGIN
    Web_Login_ID
    ???

I've read other methods to create a table for login information and they referenced a LEFT JOIN in SQL, but that seems like it would only be good if I was referencing 1 other table and not two different tables. How can I pull EMPLOYEE information if the Login ID entered is from the EMPLOYEE tables and vice versa for customers?


texts: sp_configure set at code execution - Pro/con's of turning on/off

texts:
The right answer of course is 'it depends', but on the surface it seems to make sense to turn on/off some options when needed as the code sample below for Ole Automation Procedures would do. Could also be xp_cmdshell or Ad Hoc Distributed Queries among a handful of others. EDIT: The question - is this a good or bad idea? why/why not

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

--Sample Code:
Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
 Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get', '<URL>&output=XML','false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

Select CONVERT(xml,@ResponseText)

Exec sp_OADestroy @Object;

GO
sp_configure 'Ole Automation Procedures', 0;
GO
RECONFIGURE;
GO


texts:
It seems like a magical technology that can

Do spatial search
do full text search

The problem is,

It doesn't say where does it store all the data. Does it have it's own proprietary database

If it store it's data in mysql, for example, I know that mysql cannot do spatial search well how can something build on top of it can?

Then someone said they use solr with mongodb. So solr can read mongodb and mysql? Who writes the glue? We?

Ultimately, what is this?

Database?
Index database?
API interface?

What is SOLR?


texts: Is there a good way to link tables and sequences?

texts:
I'm pretty sure you can't do this like you can with foreign keys but I'd like to document at the database level (even if it's just as a bit of free text somewhere) these relationships.

Is there some way of adding comments to tables or something like that?  I've noticed comments in the sql used to create things are often preserved but not sure how you edit these without recreating the whole object or how you view them without dumping ddl.

I'm interested in doing this in sqlserver and oracle.  I'm primarily doing edits from the command line/scripts but often look at the databases using SSMS and Sql developer.

The reason I'm interested in this is to let other developers know how to use the database - both future devs + the ones I'm currently working with.  We've got 3 applications using the same db and unfortunately its missing primary key and foreign key constraints in a lot of areas, whilst putting those in will go a long way to making it easier to understand, this would also help a lot aswell.

One hacky way I can think of doing it is adding an extra column to each table with the sequence name but I dont really like that - think its a bit misleading.


texts: Cleaning up transaction replication del/ins/upd stored procedures?

texts:
I'm referring to the ones that start with:

dbo.sp_MSdel...
dbo.sp_MSins...
dbo.sp_MSupd...

I've noticed they are not removed when I run exec sp_dropsubscription on the Publisher then run sp_removedbreplication on the subscriber (on the database).  I also tried sp_subscription_cleanup to no avail.

Do these have to be deleted manually if I want them removed?

Subscriber is SQL 2000
Publisher is SQL 2008 (Distribution database is on the Publisher)


texts:
I have a measure that show number in millions / billions.

The formatting of the number for now is like this : "$#,##0.0;-$#,##0.0"

I would like to format the number to show something like this:

If the Orginal number is: 117,100,000 $

I want to see it like that: 117.1 M $.

Can we do it on SSAS cube?

Thanks.


texts:
I have three tables: Product, Accessory and m:n relation between Product and Accessory. There are currently around 200 products and around 600 accessories. The user wants to have tags on the Accessories and then add the tags to the Products to create the relationship among them. Both Products and Accessories can have multiple tags.
The tags are only used to improve adding/creating the relationships among them, so the feature is for administrators only. The most used queries then are select "something" from Product inner join Accesory.
My first thoughts are that there should be a Tag table and two relation tables Tag-Product and Tag-Accessory. Once a tag is added to a Product or an Accessory to a Tag that is linked to a Product, the system modifies the relation table ProductAccessory.
What would be my other options?
What would be an optimal solution?
Example
+---------+   +--------------+
| Product |   |  Accessory   |
+---------+   +--------------+
| toyota  |   | tyres T20    |
| hyundai |   | tyres T40    |
| chevy   |   | tyres T40s   |
+---------+   | fresh tree   |
              | fuzzy rolldie|
              +--------------+

tyres T20 might have tags: tyres winter tyres
tyres T40 might have tags: tyres summer tyres
fresh tree and fuzzy rolldie might have tags: silly things
Not all accessories are available in all products, but in 80 percent of cases some group/tag of accessories is available. There are also cases when 1 accessory is available only in one product.
So you could have toyota tagged with tyres, hyundai with winter tyres, chevy with tyres and silly things, etc.
The purpose of the tags is just to say which accessories can go with which products.


texts: High rise in SQL Server CPU after adding a DB

texts:
I have just added a "new" DB to my Server (2005), I have restored from a dead SQL2000 server to my active one (2005), and changed the compatibility to 90 (SQL Server 2005).
My average CPU was on 48% connections on 1600, after restoring the DB from my side and changing the connection string so that the site will work with the active server hell has broken loose, my connections have stayed on the avg. 1600 but my CPU spiked to an avg. of 70%.
I thought ok, I have some tuning problems, so I have rebuilt all the indexes, and started a profiler on the "new" DB, but alas I was wrong on an avg. hour had 100 SP runs and all of them with less than 50 ms CPU and the highest number of reads were 2.2k (no writes and duration less the a sec for most, highest duration 3 sec).

I would love to get directions on what should I check next, SQL Server side or Web side.


texts: How frequent do we Analyze Table Statistic

texts:
We have a Oracle 11g database running with a table that insert entries with around 100records/min. We met some spiking issue mainly due to table is performing full table scan. We have indexed the required key, however, sometime it does not take into effect.

What we do is to do a 'analyze table statistic' and it resolve the issue.

May i ask, why do oracle require a table to be analyzed? how often do we analyze a table? Currently we have set a job scheduler for every 3 hours, however, it seems like sometime within 30mins it require another statistic to be analyzed


texts: How do I format the output from OBJECT_DEFINITION so that it looks the same as sp_helptext?

texts:
object_definition and sp_helptext both return the source code of a stored procedure - but how can I use object_definition and get the source code including the line breaks as the sp_helptext currently does?

for example in the code below I create a stored procedure:

if OBJECT_ID('usp_radhe') is not null
   drop procedure usp_radhe
go
-- this procedure is just a test
-- it just returns a date in the past
-- how will I get its source code?
create procedure usp_radhe as
begin
select dateadd(dd,-31,GETDATE())
end

using sp_helptext I get a nice view of the source code:

sp_helptext 'usp_radhe'

using object_definition I get the source code in a single line and that is not good for me:

select OBJECT_DEFINITION(object_id('usp_radhe'))


texts:
I'm in the process of migrating from one MySQL server (5.0.95) to out new MySQL server (5.1.73).  I have a script that is transferring the schema's over and doing a table checksum using CHECKSUM TABLE schema.table; on both the source and destination servers.
This process on my testing schema can validate all but 90 of my 930 tables.  For those 90 how can I find out what the differences are?

I've tried saving them both, source and destination, out with mysqldump and using Beyond Compare on the resulting sql files.  This doesn't go well since Beyond Compare will split the insert lines where it chooses and in turn finds differences where it has done that.
Is there a tool or other MySQL check that I can do to find out why tables aren't the same?

Due to company policy I have to use RHEL 6 thus is why the old server is going away since it is on RHEL 5 and won't support RHEL 6.


texts:
I have a log shipping process running. Today I realized that the log files must have been overwritten during the clock change period yesterday, where the clock was set back from 3 AM to 2 AM. So I am concerned that something may be wrong.
I looked at the files that have been backed up and found out they are stored with a filename in UTC time. The period of 01 AM - 02 AM UTC is missing here.

The same output is in log_shipping_monitor_history_detail's output: Here 01AM-02AM are missing as well while the log_time colum is ongoing wihtout any duplicates.

I do backup, copy and restore in 15 minute periods.

The genreal status (master.dbo.sp_help_log_shipping_monitor) of the log shipping process shows 0 (OK).

Does logshipping consider the clock change or do I have to take any actions?


texts: Why filtered index on IS NULL value is not used?

texts:
Assume we have a table definition like this:

CREATE TABLE MyTab (
    ID INT IDENTITY(1,1) CONSTRAINT PK_MyTab_ID PRIMARY KEY
    ,GroupByColumn NVARCHAR(10) NOT NULL
    ,WhereColumn DATETIME NULL
    )

And a filtered non-clustered index like this:

CREATE NONCLUSTERED INDEX IX_MyTab_GroupByColumn ON MyTab
    (GroupByColumn)
WHERE (WhereColumn IS NULL)

Why this index is not "covering" for this query:

SELECT
    GroupByColumn
    ,COUNT(*)
FROM MyTab
WHERE WhereColumn IS NULL
GROUP BY GroupByColumn

I'm getting this execution plan:

The KeyLookup is for the WhereColumn IS NULL predicate.

Here is the plan: https://www.brentozar.com/pastetheplan/?id=SJcbLHxO7


texts: MySql load data infile with path in secure folder but get error

texts:
Im using Wamp Server 3.2.3, in my.ini I set secure_file_priv="C:\wamp64\tmp" and then in the query
LOAD DATA INFILE 'C:/wamp64/tmp/com_task_status.csv' INTO TABLE yZPAeTUEjB FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

I run it in navicat, I got an error and same thing when run it with php:

1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Why can't I load file from this folder? I don't want to set secure_file_priv="".


texts:
I just upgraded my old MySQL instance to 5.7.31.
When I do a
select version();

I get
5.7.31-log

I am used to seeing something like (on my older version)
5.5.2

What does the -log stand for in 5.7.31-log and it is an issue of concern or just a change in how the version is outputted?


texts: Guidance for using composite keys to identify rows

texts:
Is it good practice (or would it have any adverse effects) to use a set of 4 columns to identify a row as being unique (one being a foriegn key, the other three being float data types)? I'm attempting to build a table that (with 4 keys linked) would describe a unique entry in the table. I'm curious if this is a good plan of attack or if there is a better way.

For visual purposes, picture the following table. We have inventory items that are organized like the following table: ([K] is symbolic of the primary key, the lines are relationships)

    Sheet_Class        Sheet_Type         Sheet_Size
    ===========        ==========         ==========
[K] Sheet_Class-.  [K] Sheet_Type--.  [K] Sheet_Size
                 '---- Sheet_Class  '---- Sheet_Type
                                          Length
                                          Width
                                          Thickness

The data may present itself in the following way, but for brevity I've excluded bringing over the linked columns:

 Sheet_Class    Sheet_Type    Sheet_Size                        (Tables)
[Sheet_Class]  [Sheet_Type]  [Length], [Width], [Thickness]     (Column Values)
=============  ============  ==============================

Aluminum
               5052-H32
                             48, 96, 0.032
                             48, 96, 0.040
                             48, 96, 0.063

               6061-T6
                             60, 120,0.032
                             60, 120,0.040
                             60, 120,0.063

Steel
               1018-CRS
                             48, 96, 0.018
                             48, 96, 0.023
                             48, 96, 0.031

As it stands (and I've shown in my "schema" above), I use a simple (auto-increment) integer primary key for entries in the Sheet_Size table. However, I'd like to know if it's better to use a combination of the Sheet_Type, Length, Width, & Thickness columns instead? Given each entry in Sheet_Size should share all these unique qualities, and that an auto-incrementing field wouldn't demonstrate this well enough, is this the best route to take?

If I'm not explaining the situation well enough, please let me know. I'm finding myself needing to break out these portions (Class vs. Type vs. Actual stock sizes) of an inventoried material for other logic purposes, but I'm up for any other kind of feedback.

Any guidance would be appreciated.

Update (08-12-2011)

After the answers posted, I've decided to do a combination of Mark's answer & X-Zero's answer. I decided it's a good idea to place a unique constraint on the length, width and thickness columns, but I also like the idea of breaking out material sizes in to unique rows and linking them with a relationship.

Unfortunately I can not accept both answers, so I am going to accept X-Zeros for taking (what I feel) to be a more critical look at the problem and offering a schema adjustment.

Thank you everyone for your answers.


texts: How can i branch based on a columns value in oracle?

texts:
I am trying to have a dynamic inner join..on in an Oracle database.

For this purpose, I am getting the table name, and User id which acts as an access filter (return this person's records for example), and a cursor which returns the resulting dataTabe).

There is a slight problem here. The userId which is passed belongs to two different groups - buyers and sellers.  So, whenever I want to run my stored procedure and get the needed record I need to know if it's a buyer or a seller id, then have the correct condition applied in my where clause.

To cut it short, this is my half-complete query:

CREATE OR REPLACE PROCEDURE MYDB.GET_DEAL_LIST
  ( PersonID NUMBER,
    TableName NVARCHAR2,
    BuyerOrSeller INTEGER,
    a_CURSOR OUT sys_refcursor
  )
AS
BEGIN

  OPEN a_CURSOR FOR
      SELECT TBLDEALS.*, TableName.* FROM TBLDEALS
      inner JOIN Tablename on
      Tablename.ID = TBLDEALS.id
      --Now here I need a branch, something like this:
      --if (buyerOrSeller == 1 )//then this is a buyer
          and personID = TBLDEALS.Buyer_ID
      --else if (buyerOrSeller = 2 ) /then it is a seller and we need to return his record
          and PersonID = tblDeals.Seller_ID

END;
/

For this I used case like this :

CREATE OR REPLACE PROCEDURE MYDB.GET_DEAL_LIST
  ( PersonID NUMBER,
    TableName NVARCHAR2,
    BuyerOrSeller INTEGER,
    a_CURSOR OUT sys_refcursor
  )
AS
BEGIN

  OPEN a_CURSOR FOR
      SELECT TBLDEALS.*,TableName.* FROM TBLDEALS
      inner JOIN Tablename on
      Tablename.ID = TBLDEALS.id and
      case when BuyerOrSeller = 1 then TBLDEALS.Buyer_ID = PersonID
        when BuyerOrSeller = 2 then TBLDEALS.Buyer_ID = PersonID;
END;
/

But it seems to be wrong!( I get a

  Error(14,56): PL/SQL: ORA-00905: missing keyword

near equality sign in TBLDEALS.Buyer_ID = PersonID

And I am stuck on how to specify my where condition according to the buyerOrSeller variable.