texts: MySQL Explain has different row count than slow query log

texts:
I have this entry in slow query log:

# User@Host: user[host] @  [ip]
# Thread_id: 1514428  Schema: db  Last_errno: 0  Killed: 0
# Query_time: 2.795454  Lock_time: 0.000116  Rows_sent: 15  Rows_examined: 65207  Rows_affected: 0  Rows_read: 65207
# Bytsent: 26618
SET timestamp=1407511874;

select off.*,translated_title,translated_description
from ephpb2b_products off  USE INDEX(id_viewed)
  INNER JOIN ephpb2b_members mem
    ON  off.uid = mem.id
  Left Join ephpb2b_product_language_new pol
    ON  off.id = pol.offer_id
    and pol.language='en'
where off.approved=1
order by off.viewed
LIMIT 15;

When I explain this query, it's absolutely fine.

mysql> explain select off.*,translated_title,translated_description from ephpb2b_products off  USE INDEX(id_viewed)  INNER JOIN ephpb2b_members mem ON off.uid = mem.id Left Join ephpb2b_product_language_new pol ON off.id = pol.offer_id and pol.language='en' where off.approved=1 order by off.viewed  LIMIT 15;

+----+-------------+-------+--------+-------------------------+-------------+---------+---------------------------+------+-------------+
| id | select_type | table | type   | possible_keys           | key         | key_len | ref                       | rows | Extra       |
+----+-------------+-------+--------+-------------------------+-------------+---------+---------------------------+------+-------------+
|  1 | SIMPLE      | off   | index  | NULL                    | id_viewed   | 4       | NULL                      |    3 | Using where |
|  1 | SIMPLE      | mem   | eq_ref | PRIMARY                 | PRIMARY     | 4       | db.off.uid |    1 | Using index |
|  1 | SIMPLE      | pol   | ref    | offer_id,id_language | offer_id | 5       | db.off.id  |    4 |             |
+----+-------------+-------+--------+-------------------------+-------------+---------+---------------------------+------+-------------+
3 rows in set (0.17 sec)

How do I optimize this query? Why does explain show 3 rows and slow query log says it examined 65207 rows.


texts:
I have two tables with the structure of

CREATE TABLE table1
(
Month date,
Value1 varchar(255),
PRIMARY KEY(Month)
) ENGINE=InnoDB

CREATE TABLE table2
(
Day date,
Value2 varchar(255),
PRIMARY KEY(Day)
) ENGINE=InnoDB

In table1, the date values are stored in the form of

2018-04-01
2018-03-01
2018-02-01
2018-01-01

and in table2

2018-04-01
2018-04-02
2018-04-03
2018-04-04
2018-04-05

How can I JOIN two tables to have the month value (Value1 from table1) for each row of table2?

The desirable result

Day           Month        Value1        Value2
2018-04-01    2018-04-01
2018-04-02    2018-04-01
2018-04-03    2018-04-01
2018-04-04    2018-04-01
2018-04-05    2018-04-01


texts: How to delete rows from a table when we're joining and return data?

texts:
I have two queries:

The first one is as below:

SELECT *
FROM batch bp
  INNER JOIN sender_log sl ON sl.id=bp.log_id
    AND bp.protocol='someprotocol'

The second query is used to delete rows:

delete from batch where protocol='someprotocol'

I know that in postgres we have RETURNING * to return deleted rows. What I want to achieve is to delete rows and return them in one query. How can I accomplish this?


texts: Need to make MySQL's LOAD DATA LOCAL INFILE load large data faster

texts:
I need to load 4 million rows of data into a MySQL InnoDB table using LOAD DATA INFILE and would like to know if there are server configuration options I can tweak to get faster load.

It took me 15 minutes to loaded 2 million rows, a performance I thought was disappointing for the LOAD DATA INFILE. My statement looks like this

  LOAD DATA LOCAL INFILE 'path/file.csv'
  INTO TABLE table FIELDS
  TERMINATED BY ','
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES (column1, column2, etc);


texts:
I have a query which joins two tables based on a timestamp value (don't ask).

One of these tables stores the data in a column with a Timestamp data type. The other stores it as a varchar.

Previously, the query converted the timestamp column to a varchar for the join. As I understand it this is not sargable, so I'd like to do the reverse. (The timestamp table is much larger and efficiency is more of an issue).

SELECT *
FROM TABLE1
INNER JOIN TABLE2
    ON UPPER(master.dbo.fn_sqlvarbasetostr(cast(TimestampColumn as binary(8))))
       = VARCHARTIMESTAMPCOLUMN

I've tried:

CONVERT(ROWVERSION, N'0x0000000003306BDD')

No error, but no match on the table (when a row definitely exists).

CONVERT(ROWVERSION, CAST(N'0x0000000003306BDD' as bigint))

This gives an error converting nvarchar to bigint.

How can I convert the varchar value into a timestamp rather than the other way around?


texts:
I need to configure mysql master/master replication with 3 nodes. Currently I have master/slave setup and I need to move it to master/master with 3 nodes. I have gone through some posts about configuring it in ring structure, I'm little nervous about it.

If anyone has the similar setup on production, would be great if you can suggest me how to achieve this.


texts: What to check for when receiving intermittent error on Kill Command

texts:
I have a C# scheduled task which runs every night and includes a kill command to drop an existing database so that it can be recreated. It randomly generates this error about once a week.

  Kill database failed. User does not have permission to alter database
  'Foo', the database does not exist, or the database is not in a state
  that allows access checks. Kill database failed.

The C# used is as follows.

srv.KillAllProcesses;
srv.KillDatabase;

So I am already trying to kill all of the processes. Are there any other checks or commands I could do before the kill command to ensure it happens successfully? It can't be a user permission problem as the operation works most of the time so what could be causing the "not in a state that allows access checks"?


texts: Find the source of a recurrent mass SQL edit on a server

texts:
I'll try to explain my problem as clear as possible.

The server of a company I support runs many websites. This server runs Windows Server 2012 with Microsoft SQL Server 2014.

Almost all of the websites are running a proprietary web application, made by the same company.

Only some of the websites are affected by a regular mass editing of (almost) every TEXT, NTEXT and NVARCHAR(MAX) fields in their respective databases.

HTML with malicious or spam links is added to every record of the table, only in the field with the type specified above.

The server has already been scanned with several tools and all the main password (administrator, sa of SQL Server) has been changed. I also tried to run the SQL Profiler to try to identify the mass update query, but without success.

As I can imagine for now, this may be an SQL Injection attack that uses a vulnerability in the software that runs those websites, but why only some of them? Other websites with the same version never got this problem.

As you know, is there something else I can try, or I'm missing? Reply without problems if you may need other data.


texts: aggregate column of nvarchar2 and obtain result in nvarchar2 too

texts:
I need to create a view with an aggregate column of nvarchar2(4000) in Oracle 11g.

What I have:

Table1 with every column of type nvarchar2(4000):

Col_Name   txt_value
X                   a
X                   b
X                   c
Y                   a
Y                   c

The result should be a view like this with both columns of type nvarchar2(4000):

Col_Name   txt_value
X                   a;b;c
Y                   a;c

I have tried with:

CREATE VIEW dict (col_Name, txt_value) AS
SELECT col_Name, LISTAGG (txt_value,';') WITHIN GROUP (ORDER BY txt_value)
FROM table1
GROUP BY col_Name

The only  problem is that LISTAGG() is converting the result in a column of type varchar2(4000) bit I need nvarchar2.


texts: Mysql join query improvement and GROUP_CONCAT on GROUP BY

texts:
I have two tables which I need to perform a left outer join on to get the result set I want. The query I have now works but its taking too long to process. Any suggestions?

Query:

SELECT Date_format(a.call_date, '%Y-%m-%d') Call_Date,
   Date_format(a.call_date, '%H:%i:%s') Call_Time,
   a.lead_id,
   customer_number,
   status,
   a.call_type,
   agent,
   skill,
   campaign,
   disposition,
   hangup,
   a.uniqueid,
   time_to_answer,
   talk_time,
   hold_sec,
   wrapup_sec,
   Date_format(start_time, '%H:%i:%s')  Start_Time,
   Date_format(end_time, '%H:%i:%s')    End_Time,
   Ifnull(a.transfered, b.transfered)   AS transfer,
   comments,
   location,
   duration,
   handling_time,
   number_dialed                        AS DID
FROM   cdr_temp a
   LEFT OUTER JOIN (SELECT USER,
                           Substring(number_dialed, 18, 11) AS transfered,
                           uniqueid
                    FROM   transfertable)
                   b
                ON a.uniqueid = b.uniqueid
WHERE  a.call_date BETWEEN '2019-01-01 00:00:00' AND '2019-03-23 00:00:00'
GROUP  BY a.lead_id,
      b.uniqueid

Tables:cdr_temp

transfertable

Index:

uniqueid and call_date for transfertable
uniqueid and lead_id for cdr_temp

Explain on query

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  a   ALL     NULL    NULL    NULL    NULL    1333    Using where; Using temporary; Using filesort
1   SIMPLE  transfertable   ref     uniqueid    uniqueid    22  test.a.uniqueid     1

PS: I need to concatenate skills and number_dialed on GROUP BY. I've tried using GROUP_CONCAT but it didn't work and I have no idea why.

UPDATE: I've tried this query using a temp table. Is it better?

CREATE TEMPORARY TABLE transfertable_temp (
    INDEX idx (uniqueid, transfered)
)
SELECT uniqueid, SUBSTRING(number_dialed, 18, 11) AS transfered
FROM transfertable WHERE call_date BETWEEN '2019-03-25 00:00:00' AND '2019-03-25 23:59:59';
SELECT
    DATE_FORMAT(a.call_date, '%Y-%m-%d') Call_Date,
    DATE_FORMAT(a.call_date, '%H:%i:%s') Call_Time,
    a.lead_id,
    customer_number,
    status,
    a.call_type,
    agent,
    GROUP_CONCAT(skill SEPARATOR ';') AS Skill,
    campaign,
    disposition,
    hangup,
    a.uniqueid,
    time_to_answer,
    talk_time,
    hold_sec,
    wrapup_sec,
    Date_format(start_time, '%H:%i:%s') Start_Time,
    Date_format(end_time, '%H:%i:%s') End_Time,
    IFNULL(a.transfered, b.transfered) transfer,
    comments,
    location,
    duration,
    handling_time,
    number_dialed AS DID
 FROM cdr_temp a
 LEFT JOIN transfertable_temp b
    ON a.uniqueid = b.uniqueid
 WHERE a.call_date BETWEEN '2019-03-25 00:00:00' AND '2019-03-25 23:59:59'
 GROUP BY a.lead_id,
            b.uniqueid


texts:
In a table ( in Microsoft Access), I have a large number of records that have duplicated field, for example field-1. I have to change the table and set field-1 as primary key. But as you know, it is not possible. So I want to change the value of field-1 for duplicated data. For example, now I have a table like this:

field-1  | field-2
1        | f1
1        | f2
2        | f3
2        | f4

And want to change to this:

field-1  | field-2
1        | f1
201      | f2
2        | f3
202      | f4

Note:

Maximum number of duplicated record is 2.

How I can do that?


texts: Get result of joining multiple tables as one row

texts:
I have these 2 tables:

table1:

id | name
---------
1  | john
2  | jack

table2:

id | profile_id | institution
-----------------------------
1  | 1          | SFU
2  | 1          | UBC
3  | 2          | BU
4  | 2          | USC
5  | 2          | SFU

If I want to get all the information about a user using his userid, I can simply join them using this:

select a.id, a.name, b.institution from table1 a, table2 b
where a.id = USER_ID and a.id = b.profile_id

which for USER_ID = 1 returns:

id | name | institution
-----------------------
1  | john | SFU
1  | john | UBC

What I need is actually 1 unique row instead of multiple rows. Is it in any way possible to get something like this? (I haven't seen something to do it but am not sure)

id | name | institution
-----------------------
1  | john | [SFU, UBC]


texts: Is it possible to install a single instance of sql server on a server with 2 NICS and 2 different subnet?

texts: I have a server that has 2 NICS and each is connected to 2 different subnet.  Is it possible to connect to this Sql server in each of the different subnet?  If so, what special procedure do I have to do to configure the Sql Server?  I am using sql server 2008.  Thank you!


texts: Tool to export data with all relational data?

texts:
Is there a tool to export data from selected rows in a table with all data stored in other tables in other tables linked by relational design?

The purpose is to ease migrations of bits of data between servers for adhoc migrations. I am looking specifically for a tool for MySQL InnoDB with defined foreign keys.


texts: Changing SQL Server2000 settings after converting to VM?

texts:
We have migrated a SQL 2000 server to VM and I am wondering what changes will need to be made on the VM?
After the VM migration an SSIS package that transferred data from the 2000Server to a 2008Server will not connect. Nothing changed on the 2008Server but none the less the SSIS connection fails to authenticate. I can log in remotely from the 2008Server to the 2000Server using the credentials in the SSIS package.
So, is there anything that needs to be configured on the VM side that should be configured? I have checked services, account privileges, etc. The two servers can communicate, ports are open, osql -L and sqlcmd -L see the servers. Nothing on the VM server has changed from the physical server configuration wise.


texts: Query to get queue position for each group

texts:
I have a queue table with the following sample data:

id  company location
1   acme    new york
2   acme    philadelphia
3   genco   st.louis
4   genco   san diego
5   genco   san francisco
6   acme    miami

I want to query for the queue position of each company group to show where in line each company is (imagine acme has 1,000 rows before 500 Genco starts and then acme has 5,000 more after the Genco records process).  Result I would want would look like the following:

queuePositionId company
1    acme
3    genco
6    acme

I played around with ranking and grouping but the fact that a company group can be multiple times anywhere in the queue kept messing with my aggregates.  I also tried a dense_rank but couldn't figure out the ordering.  Any ideas?


texts: Is it possible to use one certificate with aliases for MongoDB cluster?

texts:
I currently configure a MongoDB cluster to use TLS certificate.

It could be far more easier to deploy, one certificate with aliases accross all cluster, instead of one certificate per node. But there is no mention of this in documentation, is it possible ?


texts:
I have a PostgreSQL 9.2 setup (with Postgis) and am trying to upgrade it to 9.6.

When performing the pg_upgrade, I get the following error:

ERROR:  could not find function "geography_analyze" in file "/usr/lib/postgresql/9.6/lib/postgis-2.0"
STATEMENT:  CREATE FUNCTION "public"."geography_analyze"("internal") RETURNS boolean
        LANGUAGE "c" STRICT
        AS '$libdir/postgis-2.0', 'geography_analyze';

    -- For binary upgrade, handle extension membership the hard way
    ALTER EXTENSION "postgis" ADD FUNCTION "public"."geography_analyze"("internal");

I've tried to upgrade Postgis on my 9.2 clusters, but I get an error as well:

postgres=# ALTER EXTENSION postgis UPDATE;
ERROR:  cannot drop function "geometry_gist_sel_2d"("internal","oid","internal",integer) because other objects depend on it
DETAIL:  operator &&("geometry","geometry") depends on function "geometry_gist_sel_2d"("internal","oid","internal",integer)
operator class "gist_geometry_ops_2d" for access method gist depends on operator &&("geometry","geometry")
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

How should I proceed?


texts: Grouping results and filtering duplicates by the row's date


texts:
I'm quite new to SQL programming, so i apologize in Advance for all the irrelevant Information and stupid Questions :)

I have a database in Microsoft SQL Server Management Studio.

I select data from multiple tables in the database according to different criterias. I join these datas toigether with UNION. Because UNION has its own order, I also created a column in each SELECT statement called filtering, and then at the end of the UNION I ORDERed the data BY this column> filtering.
Then I insert my results into a table I have previously created. Until now everything functions perfectly.
Then I close the statement, and start a new one with basically almost the same information. (Only the weekday changes from 21 to 22). I also insert these records into the new table, and there I have my "error".

Becuase of the ORDER BY column (filtering) these records are inserted into row 4, 5 and 6, instead of 1, 2 and 3.

Is there a waz to correct this, for example not to display the filtering column, or JOIN by the filtering column?

As I said I'm quite new to SQL (started learning it about about 2 Weeks ago) any help is highly appreciated!

My code and the results

DELETE FROM [Test].[dbo].[Übersicht_Woche_RAP]
INSERT INTO [Test].[dbo].[Übersicht_Woche_RAP] (W21, id)
SELECT SUM(try_convert(numeric(38, 5), Rüsten_Ist)) AS Rüsten, 1 AS filtering
FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 21)
UNION
SELECT SUM(ISNULL(try_convert(numeric(38, 5), Arb__Plan), 0) + ISNULL(try_convert(numeric(38, 5), Masch__Ist), 0) + ISNULL(try_convert(numeric(38, 5), IstArb), 0)) AS B, 2 AS filtering
FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 21)
UNION
SELECT SUM(try_convert(numeric(38, 5), Ausschuß)) AS C, 4 AS filtering
FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 21)
ORDER BY filtering;

INSERT INTO [Test].[dbo].[Übersicht_Woche_RAP] (W22, id)
SELECT SUM(try_convert(numeric(38, 5), Rüsten_Ist)) AS Rüsten, 1 AS filtering
FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 22)
UNION
SELECT SUM(ISNULL(try_convert(numeric(38, 5), Arb__Plan), 0) + ISNULL(try_convert(numeric(38, 5), Masch__Ist), 0) + ISNULL(try_convert(numeric(38, 5), IstArb), 0)) AS B, 2 AS filtering
FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 22)
UNION
SELECT SUM(try_convert(numeric(38, 5), Ausschuß)) AS C, 4 AS filtering
FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 22)
ORDER BY filtering


texts: When is SELECT necessary in PL/pgSQL functions?

texts:
While assigning the result of a function or expression to a variable in PL/pgSQL, when is it necessary to use a SELECT statement (sub-query)?

This may be just my misconception from SQL. I thought that everything should be enclosed in a (SELECT ...) (even if it just involves computing a number), e.g.

$$
DECLARE parts text[];
BEGIN
  parts := (SELECT string_to_array(my_str,'_') );
...
$$

but I just found that it works without the SELECT:

parts := string_to_array(my_str,'_');

(It's almost as if I can use PL/pgSQL as if its Pascal.)

In general, what kind of expressions can be used directly without SELECT in computing values?


texts: Group by's in materialized views and query planning

texts:
In part out of curiosity, I wondered if I could use an indexed (materialized) view to speed up a count query on some base table.

The query is something like

SELECT COUNT(*)
FROM BaseTable
WHERE Slot = ?;

so I created a view

CREATE VIEW IndexedView
WITH SCHEMABINDING AS
SELECT bt.Slot, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;

with a clustered index

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);

And that works, I can now write the original query as

SELECT COUNT
FROM IndexedView
WHERE Slot = ?

and get the desired result much faster.

Alas, it's hardly much use for me, as my queries are usually not hand-crafted. I really need the original query to become faster by using the indexed view as some kind of index for BaseTable - and I think I read somewhere that this can happen in some circumstances, but according to my tests, not in this one.

So my question(s) would be:

Can indexed views still help me in this scenario somehow?
Can anybody recommend sources/literature than explains in which cases indexed views do get used for optimizations of queries on the tables they are based on?

EDIT: On the duplicate question - I'm more interested in the GROUP BY and aggregate aspect of indexed views. The answer helped me find a stupid mistake I made, it's now working for me as well.

JOYOUS ADDENDUM: Now that I got it working, I successfully tested that it even works in cases where the query contains a left join in those cases where the left join can in fact be optimized away (ie. the on-clause covers a unique index in the joined table).

That is really awesome, because it means that even in cases of a query with left joins one can design the schema in such a way that getting a total row count of everything or specific groupings is fast.


texts: Fast paginated result, for various filter clauses

texts:
I have been working on obtaining a paginated result from a table in my database (11g). While I have a query that does work (ie, the result is correct) it does not perform as well as I'd like to and I am trying to improve its efficiency (est. 60 calls per second on that query only).

So first of all, I read What is an Effective Way to Count the Number of Pages in Oracle?, and the article pointed too, but unfortunately it does not discuss the execution plan of the queries presented at all (I'll get to see them come Monday).

Here is the query I had come up with (the table is partitioned on part_code which is a date range):

select <all-columns> from (
    select rownum as rnum, <all-columns> from (
        select /*+index (my_table index)*/ <all-columns> from my_table
        where part_code in (?, ?, ?)
          and date between ? and ?
          and type in (?, ?, ?)
          and func_col1 = ?
          /*potentially: and func_col2 = ? and func_col3 = ? ... */
        order by date, type, id
     )
) where rnum between M and N; /* N-M ~= 30 */

Note: most of the queries will be carried out with a single func_xxx filter, and I think M will be small... but have no guarantee and in theory M can be up to 9999.

Note: ~72 partitions in total, but only ~39 active at most, ~300,000 different values of func_col1 (but with some having ~50,000 rows and some having 1 row), ~10 values of type, id is unique (generated through a sequence).

It does work, but there is a nasty surprise in the execution plan: the 2nd query (with rownum as rnum) is completely executed, fetching up to ~50,000 rows from the DB, before the pagination kicks in. Fetching ~50,000 rows from the DB to return some ~30 of them seems particularly inefficient.

In the execution-plan this shows up as:

View
\_ Filter (rnum)
   \_ View <= here comes the trouble
      \_ Sort
         \_ ...

I can create an index over the table if necessary, and I can transform the existing partitioned index (part_code, func_col1, date, type, id). The index has exactly the order required by my order by clause; but it does not seem to be sufficient (and removing the order by clause does not get me any closer anyway).

Is there a way to prevent the "materialization" of the view and have Oracle build it on the fly as the outer query needs more data (ie, moving to lazy evaluation of the inner queries) ?


texts:
How can I have a working PSQL console using UTF8 encoding under Windows?
I have a Windows server and client. The Postgres 12 database contains tables with content in multiple languages (ex: English, French (with characters such as é), Korean (with characters such as 브)).
The DB is encoded as UTF8.
The client_encoding is set to UTF8.
The console code page is set to 65001 (Microsoft's UTF8 codepage), and has a suitable font (
Lucida Console)
I must be able to properly display English and French text, query the DB in English and French and be able to "output something", i.e. not get any error, if touching text in another language.
The issue is I can't type / copy-paste any accented character. If I do, the special character acts as a comment but the input continues on the next line, and I need to close it again.
In the following example, note that é_fg is gone, that there is no prompt on the hij line and that the output concatenates abcd_hij on one line
myuser=> select 'abcd_é_fg';
hij
myuser'> klm';
 ?column?
----------
 abcd_hij+
 klm
(1 row)

The issue is really with the typing, because I can export and load files containing such chars.
Ex: save select 'é'; in sel.sql, run psql -U myuser -d mydb -f C:/sel.sql
will output
 ?column?
----------
 é

On the working well side, I can display what I need
select chr(8217); --sort of apostrophe
 chr
-----
 ’

select chr(48652);
 chr
-----
 브 --(though displayed as a square - unknown character, ok)

I have tried to set Windows language for non-Unicode program to UTF8, which had no effect on the issue.
If I set the code page to 1252 and the client-encoding to Win1252, I can display the accented chars, I can type them, but if I want to display text in another language like Korean I get and error
select chr(48652);

ERROR:  character with byte sequence 0xeb 0xb8 0x8c in encoding "UTF8" has no equivalent in encoding "WIN1252"


texts:
I need to get a count of rows in a table day wise.
The table looks like below:
Table ABC:
f_id|reg_date
1|2020-09-08
2|2020-09-12
3|2020-10-01
4|2020-09-07
5|2020-09-08
6|2020-09-09

Expected output if I am running query saying I want the count of rows till 2020-09-15 since a week before of 15th:
count|date
1|2020-09-07
3|2020-09-08
4|2020-09-09
4|2020-09-10
4|2020-09-11
5|2020-09-12
5|2020-09-13
5|2020-09-14

I am not sure how to get this above output.
The rouble with the date range and group by is just giving me the the count of rows for that date, not a total of the count till that date. For example group by date gives me: 1|2020-09-07, 2|2020-09-08 and so on.


texts:
I am trying to translate the ISA relationship in this ER diagram to database tables, and I am not sure how to enforce the total and disjoint participation constraint of Laptop and Desktop in Computer. (There are other attributes, but they are not shown here for simplicity.) Here are some options I've explored:

a) Have just one table for Computer, with the attributes of the subtypes in the parent entity. This is not an option, since some functional dependencies in the next part of the question forbid this. There isn't a "type" flag attribute in the diagram either.

b) Two tables, one each for Laptop and Desktop, and pull the attributes from Computer into the separate tables. I can't do this, since it would destroy the relationships that Computer has with other entities.

c) One table each for Computer, Desktop, and Laptop, with foreign key constraints on cid. I don't like this option, since it does not enforce the total and disjoint participation constraint.

This is a practice exercise, but there are no solutions. I would greatly appreciate any help!

                           /  \
        ------------     /      \    ---------
 (cid)--| Computer |====/Supplies\---| Store |
        -----||-----     \     /     ---------
             ||   \        \ /
             ||    \
             ||     \     / \        ---------------
            /d \     \--/Fixes\------| TechSupport |
(battery)   -----        \   /       ---------------
  \        /    \         \ /
--------- /      \ ----------
| Laptop |        | Desktop |--(monitor)
----------        -----------


texts:
One I selected the query from my product table with an additional Row_Number() column (Num), I'm getting the answer like below (I limited only 20 rows to retrieve).

Query

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ProductCode) AS Num,
             ProductName
      FROM   BASE_Product
      WHERE IsActive = 1) BASE_Product
WHERE Num > 0
AND   Num < (21)

Result

My requirement is how to skip each and every 3 rows and retrieve only 7 limit rows.

Expected result

I need the result such as below:


texts: Find the size of each index in a MySQL table

texts:
I am trying to figure out size of each index of a table.

SHOW TABLE STATUS gives "Index_length" is the summation of all the indices of the table.
However if a table has multiple indices (e.g. in an employee table) then emp_id, ename and deptno are 3 different indices for which I want sizes separately.

emp_id : xx Kb
ename  : yy Kb
deptno : zz Kb

How can I get these?


texts: MS SQL Server IP address change considerations

texts: I am very new to MS SQL Server Administration and wants to learn on Migration of SQL Server in detail. We are in a Datacenter migration Project where the Windows server (SQL Server is residing in it) is going to be migrated AS-IS from Source to Destination. I would like to know what are the things that we need to take care for the SQL Server IP address connectivity issues and other related connectivity problems. correct me if anything wrong in framing the question and thanks in advance


texts: Does oracle workspace manager work with oracle xe 11g

texts:
According to this forum it can be using a patch but I need a support identifier to access patches.
https://community.oracle.com/thread/340084?start=0&tstart=0

This describes how to install workspace manager on custom databases using the
owminst.plb file which doesn't seem to come with xe
https://docs.oracle.com/cd/B19306_01/appdev.102/b14253/long_inst.htm#g640808

I've searched through many web pages and I can't find a process for installing workspace manager on an oracle xe installation.  Is the only way to get a patch or just download SE?


texts: importing postgres tar file throws parallel not supported in postgres 12

texts:
I'm trying to import a db that someone sent me: testdb.tar (43GB size)
First I've imported using:
pg_restore --host localhost --port 5432 --username postgres --dbname testdb --role postgres --no-password  --verbose testdb.tar

It took 23 hours to complete.
I supose it used just a single core.
Now, trying to do it faster, using the available 16 cores, I tried:
pg_restore --host localhost --port 5432 --username postgres --dbname testdb --role postgres --no-password -j 16 --verbose testdb.tar

However, it throws the following error:
pg_restore: error: parallel restore is not supported with this archive file format

Not really sure why it complains.


texts: List of changes required to upgrade scripts after starting to use replication in SQL Server

texts:
We have recently setup transactional replication on our test and production databases. As part of that change, we've had to make changes to how we create upgrade scripts as well. The goal is to make changes that do not require invalidating the current snapshot.

Thus far, we've had two significant changes:

For stored procedures, use ALTER instead of DROP/CREATE. This involves creating a dummy empty procedure the first time (by checking if exists) and then altering in all cases.
For renaming table columns, instead of sp_rename, add new column / populate column / drop old column.

I'd like to know what other types of changes we may need to make in order to successfully upgrade the database without invalidationg the snapshot.


texts: Datetime conversion fails when filtered index is placed on partition column

texts:
I've got a mysterious error of char data conversion at the moment of insertion a new row into a table. Troubleshooting reveals that a filtered index is the source of the problem.

The index's filter uses a DateTime column, which also is a partitioning column of the table. Some clients connect with Russian by default and get the error.
I've managed to reproduce it on a test table.

Does anyone have any ideas on why this happens?

use YOUR_DATABASE ;

go

select @@VERSION ;

-- Microsoft SQL Server 2016 (SP2-CU1) ...

-----------------------------------------------------------------------------

-- CREATE DATETIME PARTITIONED TABLE

set language english ;

drop table if exists dbo.test_of_filtered_idx ;

drop partition scheme ps_test_of_filtered_idx ;

drop partition function pf_test_of_filtered_idx ;

go

set language english ;

go

create partition function pf_test_of_filtered_idx (datetime)

   as range right

   for values ('1999-11-01 00:00:00.000' , '1999-12-01 00:00:00.000' , '2000-01-01 00:00:00.000' , '2000-02-01 00:00:00.000')

create partition scheme ps_test_of_filtered_idx

  as partition pf_test_of_filtered_idx

  ALL to ([primary])

create table dbo.test_of_filtered_idx

(

  id int not null identity (1,1) ,

  dt datetime not null ,

  payload char(127) not null default(replicate('A' , 127)) ,

  constraint PK__test_of_filtered_idx primary key clustered (id , dt)

) on [ps_test_of_filtered_idx] (dt) ;

go

-----------------------------------------------------------------------------

-- INSERT TEST ROW AND CREATE DATETIME FILTERED INDEX

set language russian ;

go

insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;

go

set language english ;

go

create index IXF__test_of_filtered_idx__dt

  on dbo.test_of_filtered_idx

  (dt) include (id)

  where dt >= '1999-12-10 00:00:00.000'

    and dt < '2000-01-20 00:00:00.000' ;

go

-----------------------------------------------------------------------------

-- CHECK

set language english ;

go

insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('12/15/1999 00:00:00.000' /* native american datetime format MDY */) ;

go

-- GET ERROR

set language russian ;

go

insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;

-- Преобразование типа данных varchar в тип данных datetime привело к выходу значения за пределы диапазона.

-- The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

go

-----------------------------------------------------------------------------

-- DROP INDEX AND GET ERRORFREE INSERTION

set language english ;

go

drop index IXF__test_of_filtered_idx__dt

  on dbo.test_of_filtered_idx

;

go

set language russian ;

go

insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;

go

-----------------------------------------------------------------------------


texts: I have a Mongo replica set (MongoDB server version: 4.0.3) with primiry, secondary and arbiter. Size of database is about 7 Tb. I add a member to replica set. It stucks in sturtup2 mode for a month! In first day it synhronize 3 Tb and then go slower and slower synchronyzing about 200 Gb each day. Previous member I add to this replica set was added for about a two or three days. I remove all load from replica set, there is nothing else take place - only synchronisation. Maybe there is a way to tell it to synchronyse faster?


texts: firebird is very slow compared to sqlite

texts:
I am using Firebird for past few months and today a customer complained about performance issue.

I checked into client's database and they had 167,77,216 rows in one table. So I made a clone of that table into another empty database and loaded with dummy records and tested with a simple query

select count(journal_id) from acc_journal

It took 14.28 seconds.

After that I made a sqlite database and loaded with this data and it took 2.216 seconds.

Is this Firebid's nature or I am missing something?

Firebird table structure:

CREATE TABLE ACC_JOURNAL (
  JOURNAL_ID       INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  ACCOUNT_HEAD_ID  INTEGER,
  REF_ID           INTEGER,
  REF_TYPE         VARCHAR(20),
  DESCRIPTION      VARCHAR(512),
  DEBIT            DECIMAL(18,4),
  DEBIT_EX         DECIMAL(18,4),
  CREDIT           DECIMAL(18,4),
  CREDIT_EX        DECIMAL(18,4),
  CURRENCY_ID      INTEGER,
  STATUS           VARCHAR(20),
  EX_RATE          DECIMAL(18,4),
  ENTRY_DATE       VARCHAR(20),
  ENTRY_TIME       VARCHAR(20),
  BRANCH_CODE      VARCHAR(20),
  LAST_SYNCED      VARCHAR(20),
  LAST_UPDATED     DECIMAL(18,4),
  ENTRY_DATE2      VARCHAR(20),
  /* Keys */
  PRIMARY KEY (JOURNAL_ID)
);

SQLite table structure:

CREATE TABLE ACC_JOURNAL (
    ACCOUNT_HEAD_ID INTEGER,
    REF_ID          INTEGER,
    REF_TYPE        VARCHAR (20),
    DESCRIPTION     VARCHAR (512),
    DEBIT           DECIMAL (18, 4),
    DEBIT_EX        DECIMAL (18, 4),
    CREDIT          DECIMAL (18, 4),
    CREDIT_EX       DECIMAL (18, 4),
    CURRENCY_ID     INTEGER,
    STATUS          VARCHAR (20),
    EX_RATE         DECIMAL (18, 4),
    ENTRY_DATE      VARCHAR (20),
    ENTRY_TIME      VARCHAR (20),
    BRANCH_CODE     VARCHAR (20),
    LAST_SYNCED     VARCHAR (20),
    LAST_UPDATED    DECIMAL (18, 4),
    ENTRY_DATE2     VARCHAR (20),
    JOURNAL_ID      INTEGER         PRIMARY KEY AUTOINCREMENT
);

Both files can be downloaded from following links:

Firebird DB
SQLite DB


texts: What is the meaning of `(ORDER BY x RANGE BETWEEN n PRECEDING...)` if x is a date?

texts:
In another thread:

https://stackoverflow.com/questions/37759659/db2-query-to-find-average-sale-for-each-item-1-year-previous

the OP wanted a sliding average for the last 365 days. Using ROWS BETWEEN ... would be fine if it where guaranteed that there where exactly one occurrence per day, but that is not the case here. RANGE BETWEEN ...seems like a good fit, but it is not clear to me what it means in DB2. Not sure if it matters that db2 does not have an INTERVAL type, but mimics it with labled durations.

The documentation says: (https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0023461.html)

unsigned-constant PRECEDING

Specifies either the range or number of rows preceding the current row. If ROWS is specified, then unsigned-constant must be zero or a positive integer indicating a number of rows. If RANGE is specified, then the data type of unsigned-constant must be comparable to the type of the sort-key-expression of the window-order-clause. There can only be one sort-key-expression, and the data type of the sort-key-expression must allow subtraction. This clause cannot be specified in group-bound2 if group-bound1 is CURRENT ROW or unsigned-constant FOLLOWING.

unsigned-constant FOLLOWING

Specifies either the range or number of rows following the current row. If ROWS is specified, then unsigned-constant must be zero or a positive integer indicating a number of rows. If RANGE is specified, then the data type of unsigned-constant must be comparable to the type of the sort-key-expression of the window-order-clause. There can only be one sort-key-expression, and the data type of the sort-key-expression must allow addition.

DB2 allow constructions like:

values current_date - 1

the default unit date is day so this means:

values current_date - 1 day

Given this I would expect this example to work:

create table test
( d date not null
, x decimal(3,0) not null);

insert into test (d,x)
values ('2016-01-01',10),('2016-01-07',20),('2016-01-12',30);

I would expect the query:

select d, avg(x) over (order by d
                       range between 30 preceding
                                 and current row)
from test
order by d;

to return:

2016-01-01-00.00.00  10
2016-01-07-00.00.00  15
2016-01-12-00.00.00  20

or possibly generate an error, but instead the result is:

2016-01-01-00.00.00  10
2016-01-07-00.00.00  20
2016-01-12-00.00.00  25

I also tried adding day to the query:

select d, avg(x) over (order by d
                       range between 30 days preceding

select d, avg(x) over (order by d
                       range between (cast 30 as day) preceding

just in case, but both of these attempts results in:

 SQL0104N  An unexpected token "day" was found following "y ...
 SQL0104N  An unexpected token "cast(30 as day)" was found following "r ...

First suspicion I had that the unit is something smaller than day, but increasing the preceding part to 300 returns the same result. Perhaps even more surprising is that increasing it to 500 changes the result to:

 select d, avg(x) over (order by d
                        range between 500 preceding
                                  and current row)
 from test
 order by d

2016-01-01-00.00.00  10
2016-01-07-00.00.00  20
2016-01-12-00.00.00  30

Given the query:

 select d, avg(x) over (order by d
                        range between n preceding
                                  and current row)

for 300 <= n <= 399 the result of the last row is 25, for n<300 or n>399 the result of the last row is 30.

I can not figure out which rows that are seen by the avg function, my best guess is that there is some implicit cast of date to something else in the framing clause, but don't have any idea how to prove or disprove this assumption. Can someone shed some light on this?


texts: Updating table based on conditions (overlapping dates)

texts:
Say I have a table name CustomerBooking which contains Customers, checkInDate and checkOutDate as follows. I would like to achieve the outcome as shown in condition 1 and condition 2 with update statement.

I have tried some solutions online but to no avail that it does not include NULL as infinite date in overlapping (for condition 1) and/or only takes the max date of overlapping date into account instead of taking max date from last row of overlapping dates. (for condition 2).

Do take in mind that condition 1 and condition 2 are two independent separate outcome that should be achieved with update statement based on the first table.

ID  CustomerID CheckInDate CheckOutDate
1   1          2015-04-02  2015-04-05
2   2          2015-03-04  2015-05-02
3   1          2015-04-01  NULL(which represents infinite date)
4   1          2015-03-04  2015-05-03
5   1          2015-01-03  2015-02-03

Condition 1: Merge the overlapping date of same member ID (take earliest start date and max end date of overlapping dates) and update the table.

Note: Row with ID 1, ID 3 and ID 4 has overlapped dates. Therefore we combine these 3 rows to CheckInDate(2015-03-04) and CheckOutDate(NULL) by taking the min date and max date of overlapping dates. Only row with ID 1 is updated with the new dates and remaining ID 3 and ID 4 date is set to null. Rows with ID 2 and ID 5 do not have overlapping dates of same member ID therefore it remains as it is.

Final result is as shown below:

ID CustomerID CheckInDate CheckOutDate
1   1         2015-03-04  NULL
2   2         2015-03-04  2015-05-02
3   1         NULL        NULL
4   1         NULL        NULL
5   1         2015-01-03  2015-02-03

Condition 2: Merge the overlapping date of same member ID (take earliest start date and last end date(largest ID) of overlapping dates) and update the table.

Similar as condition 1, but this time we take the min date of overlapping dates (2015-03-04) and the check out date of the last row (row with ID 4) of overlappings date (which if 2015-05-03).

Final result is as shown below:

ID CustomerID CheckInDate CheckOutDate
1   1         2015-03-04   2015-05-03
2   2         2015-03-04   2015-05-02
3   1         NULL              NULL
4   1         NULL               NULL
5   1         2015-01-03  2015-02-03


texts: mysql add a column to a number of tables

texts:
I want to add a column to a number of tables:

my tables are in the format of No_tmp e.g 1_tmp .. 9999_tmp

No = a number 1 - 1000 ...
I want to be able to add a column to all No_tmp tables every single table

Then I want to insert a text value eg "abcdef" in some tables

is it posible, I don't wish to do it manually on each table

ie

tables 1 - 223 I want to insert "guppy" into the "group" field

tables 224 - 399 I want to insert "barb" into the "group" field

tables 400 - 475 I want to insert "goldfish" into the "group" field

I figured out what I needed was the ALTER command ..

I probably did not word my question very well.

Both answers provided are good.

What I have is a large number of csv files with different sized tables, different column sizes, different numbers of columns etc, so I'm making new tables for each group of items, and logging the details of each new table in a master, from that I can analyse the structure and find common fields.

My choice of using numbers as part of the table name just makes it more readable than choosing random characters, and is easily accomplished and automated. Once I have the data I'll then start to look at normalising / formatting it.

Thank you for the answers.


texts: Generate tables from entities with hibernate

texts:
I'm using postgresql with a spring/hibernate to create a multi-tenant application where every company will have its own seperate schema and tables in the database.

So when the super administrator tries to add a new company, a new schema is supposed to be added to the database as well as the new tables generated from the hibernate entities (only the entities where the schema is set to dynamic).

@Table(name = "vehicule", schema="dynamic")

The problem is that I can't figure out how to generate the database tables from the entities using a hibernate query.

I know tha it's possible to do that using :

<property name="hbm2ddl.auto" value="create"/>

But it only will work when the sessionFactory is created and not on demand.

Any help will be appreciated..


texts: Store email in a separate Dimension or in a Degenerate Dimension?

texts:
I just started learning about dimensional modeling and I am creating a star to analyse email newsletter signups for an online business.

I have a fact table that records the signups and links to a contact dimension, traffic source dimension, page dimension and a junk dimension with various flags.

I also have a degenerate dimension with the signup ID of the source system.

But one of the pieces I can't figure out where to put is the email address.

I won't need it for filtering, but it will be good to have for when I dig into the data and show the signups.

The data high cardinality and contains about 70% unique emails and 30% duplicates.

Option 1: Junk Dimension

I considered putting it in the junk dimension, but that would swell the rows from ~1,000 to currently 133,000.

So that's clearly a bad choice

Option 2: Email Dimension

But having a dimension with just one column doesn't seem right. It would provide barely any space savings.

Option 3: Degenerate Dimension
Another option is to put it as a degenerate dimension like one would do with natural keys. But my understanding is that you are only supposed to use degenerate dimensions for values that have a unique value for each fact row.

On top of that the that textual values should be avoided. Would 70% qualify for this?

Finally, probably the best option would be to record the email through the contact dimension and make it an SCD Type 2.

But I already have a contact dimension that's an SCD Type 1 and currently don't have the knowhow to implement and maintain a type 2 properly. On top of that the data is loaded once per day, so it wouldn't account for a person who signs up with one email and changes it on the same day to another email before the ETL pulls the data.

Anyways, to make my question more clear I'd like to get guidance on...

Does it make sense to have a dimension table with just one column and almost as many rows as the fact table?
Is it ok to use degenerate dimensions for values that are not 1-to-1 in the fact table (but still high cardinality)?
How bad is it to add a text column to a fact table?


texts: sp_AskBrent not logging queries when saving to table vs. sp_WhoIsActive

texts:
sp_AskBrent, when run as an agent job every 5 minutes is logging to a table but never seems to populate QueryText. However, sp_WhoIsActive, when also run every 5 minutes on the same SQL Server instance does capture the queries. I'll get an Execution Plan (QueryPlan) every so often, but that only seems to show up during MP tasks like DB backups.

Am I invoking sp_AskBrent incorrectly, e.g. do I need to add an additional parameter? If so, which parameter(s)?

Here's what I'm using in job step of the agent job:

EXEC sp_AskBrent @OutputDatabaseName = 'DbaTools', @OutputSchemaName = 'dbo', @OutputTableName = 'AskBrentResults'

Does this functionality not exist in sp_AskBrent? I'm using AskBrentVersion 23 according to the table results). Didn't think BlitzFirst was anything but a name change, so I'm using what I thought was the latest sp_AskBrent.

Am testing both sp_AskBrent and sp_WhoIsActive run periodically to see which may be better for having a general idea of what's going on with my instance over time. Seems that sp_AskBrent wants to show me its summation of issues only when logging to a table.

Here's how I invoke sp_WhoIsActive as a job step in an agent job and it's returning query text and execution plans:

DECLARE @destination_table VARCHAR(4000) = '[DbaTools].dbo.WhoIsActiveResults' ;
EXEC dbo.sp_WhoIsActive @get_transaction_info = 1, @get_plans = 1,
            @destination_table = @destination_table ;

Has running one or both of these scripts every 5 to 10 minutes as an agent job provided proven value / saved your bacon in terms of follow-up troubleshooting when something went wrong after hours? I realize these scripts are typically run ad hoc in response to users asking about SQL Server "being slow." But I find the logging to table options intriguing.


texts: How to DRY record metadata in SQL Server

texts:
There is a database that has been given to me, and all tables contain these columns:

CreatorUserId
CreatorUsername
CreationDate
ModifierUserId
ModifierUsername
ModificationDate

However, it seems that this design is not DRY and it's really hard to maintain because this database contains like 300 tables.

On the other hand, the modification history and creation history and lot's of other actions on the database level matter to us.

Is there any better way to record any DDL and DML operations?


texts: Can't find compiled parameters values in the plan cache

texts:
I have found, through the query store, a query that's performing on average 297582 logical reads.

I wanted to see if I was able to tune that query a bit, and after that, try to execute the query again to see if there was any improvement.

The problem is that I couldn't find compiled parameters value in the cached plan.

Am I missing something? Maybe some reason/setting that prevents the caching of parameters values?

I can't find the parameters even if I open the execution plan as XML.

Additional informations: the query is executed by a third party application that prepares the statements and then executes them with sp_prepare and sp_execute.

Full XML plan

Query store:

Plan cache DMV's:


texts:
I run a task in my Python application of ~100K jobs which takes around 5 hours, since the beginning my in RDS instance CPU starts growing...

Instance type: Postgresql 9.5 | db.t2.large

I have the following performance counters:

CPU 68.1%
Memory 7,250 MB
Storage 4,010 MB
Read IOPS 0.55/sec
Write IOPS 27.2/sec
Swap Usage 0 MB

Settings:

max_connections {DBInstanceClassMemory/12582880}
pg_stat_statements.track ALL
shared_preload_libraries  pg_stat_statements
tcp_keepalives_count  1
tcp_keepalives_idle   120
tcp_keepalives_interval 60
temp_buffers  {DBInstanceClassMemory/512}
track_activity_query_size 2048

I'm using pganalyze to monitor database.

Any pointers how to debug CPU increase?
All 100K job consist of same queries (1 inserts/4 updates per job).


texts: Is it normal for tempdb to not give up disk space when it releases things?

texts:
So my tempdb is getting huge. Like 200+ GB.

USE tempdb  GO  EXEC sp_spaceused

database_name   database_size   unallocated space
tempdb          211668.88 MB    206803.45 MB

It's using a SIMPLE recovery model, which according to Microsoft:

  Set the recovery model of tempdb to SIMPLE. This model automatically
  reclaims log space to keep space requirements small.

But does 'reclaim log space' mean that it still maintains the disk allocation?

Kind of like if I had an influx of patrons at my business so I borrowed a lot across the street to put the extra cars, but then once I no longer need all that space I still reserved that extra parking lot for later (didn't allow anyone else to use it) despite it remaining empty?

If this is normal behavior then I might expect that it's hording space that it thinks it might use again one day, and thus if it had 'infinite' space to expand it would eventually stop growing based on the determined usage patterns?

Or, is it suppose to be giving back disk space to the OS when it releases?

UPDATE

Also found this: http://technet.microsoft.com/en-us/library/ms176037(v=sql.105).aspx

  By default, the tempdb database automatically grows as space is
  required, because the MAXSIZE of the files is set to UNLIMITED.
  Therefore, tempdb can continue growing until space on the disk that
  contains tempdb is exhausted. You can prevent tempdb from growing
  without limits by setting a MAXSIZE for tempdb, however this is not
  recommended. Limiting the size of tempdb may cause the database to run
  out of disk space. This can cause significant disruptions in your
  production environment and can prohibit applications that are running
  from completing operations.

So maybe I've answered my own question here but hopefully someone can clarify: Does it then follow that I should have it on a drive/partition by itself? Then it can grow 'as big as it can' while at the same time not taking up disk space that I want to use for other things? But in that case, I wonder what the difference is between MAXSIZE and actually not having any more space to physically grow.


texts: How to determine if the SQL Server running is Express version?

texts:
is there a way to check whether the SQL Server I am connected is Express version?

How can I find how much space is left until the 10GB limit of the SQL Server Express?

Thank you!


texts: How long can the log reader agent run for before a restart will do it some good?

texts:
As a developer who has for the first time successfully implemented a replication topology for our production database, I'm monitoring it very closely and fretting over potential problems.

I'm pleased that the Log Reader Agent has been running for 2 days 20 hours 6 minutes and 2 seconds and don't want to get ahead of myself (as I know that problems will come) but how large will this figure get?

Do dba's ever restart the log reader agent if it's been running for 30, 100 or 365 days? Is this beneficial or is there a website where I can submit a snapshot of Log Reader Agent status if it ever hits 2 years without a problem? (Presuming we have no server reboots - which if course we will).

Ta


texts: How do I upload the .mdf file and associated .ldf file from visual studio to a host using WebsitePanel?

texts:
I have created an ASP.NET MVC 3 project in Visual Studio and the database is a SQL Server Express database BeerRecommenderContext.mdf with a log file BeerRecommenderContext.ldf. I can upload these to the standard App_Data directory but the connection string doesn't seem to work. I think it's because my host doesn't recognize the files as SQL Server files. My host, dotnet-host.com is using WebsitePanel. My current connection string is

Server=mssql0921.dotnet-host.com;Database=BeerRecommenderContext;UserID=timoteo1097;Password=●●●●●●●●

(of course the password is actually filled in properly)

The


texts: Phpmyadmin / mysql error defining primary keys as foreign keys of two separate tables

texts:
I am designing a mysql database using phpmyadmin 3.3.10.4. Everything in the database is InnoDB.

I have two tables, person with primary key person.id_numberand e-mail_address with pk e-mail_address.id_number. Since one person can have many e-mail addresses and since sometimes two people share a single e-mail address, I consider this a many to many relationship. I am trying to create a mapping table to relate them, map_person_e-mail_address. I have the table already created with PK on two columns, map_person_e-mail_address.person_id_number and map_person_e-mail_address.e-mail_address_id_number. I also have an auto-incrementing, unique map_person_e-mail_address.id_number. I need this because later a table for tracking the history of e-mails sent will have a foreign key on this ID number to indicate who was e-mailed and what e-mail address was used.

All of this has worked so far. However, in the relation page for my mapping table, I am trying to set map_person_e-mail_address.person_id_number as the a foreign key on person.id_number and map_person_e-mail_address.e-mail_address_id_number as a foreign key on e-mail_address.id_number. I can set either foreign key individually, but when I try to make both columns foreign keys I receive a message that just says "Error" without any explanation.

Am I not able to set the two columns in the PK as FKs from two different tables? If not, can someone please let me know why and what I should do instead? Or if that's not the problem, then what is?


texts: (MySQL) user table and profile separate, or same table?

texts:
I'm creating a blogging site that anyone can sign up and be an author for. I want each author to have their own public profile. My main question is; would it be better practice to have a separate profile table that contains fields that would be specific to only the profile page, or should I just add those profile fields to a general users table as this example shows:

CREATE TABLE IF NOT EXISTS `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(255) NOT NULL,
 `first_name` varchar(255) NOT NULL,
 `last_name` varchar(255) NOT NULL,
 `email` varchar(255) NOT NULL,
 `password` varchar(32) NOT NULL,
 `sign_up_date` date NOT NULL,
 `activated` enum('0','1') NOT NULL,
 `admin` int(1) 0 ,
 `bio` text NOT NULL,
 `profile_pic` text NOT NULL,
 PRIMARY KEY (`id`)
)

bio and profile_pic are both profile fields in this case. This is also just an example, I plan to have more fields for general user stuff and not to many more for the public profile, but that could change as I move further into development.

Sorry if this an opinion and not a best practice question. I'm new to Database architecture.


texts: How can I use the same primary key across multiple tables as a foreign key?

texts: I am working on a school project. I have a table of employees with a given employee ID. The employee ID on this table is the primary key. I have already set the employee ID as a foreign key on another table, and I am trying to use it as a foreign key on another table as well. How can I do this? I am currently getting an error message saying "duplicate foreign key". Thanks!


texts: when were the sys-dm_os_wait_stats last cleared? sql-2005!

texts:
I intend to monitor the execution of my stored procedures in a specific database, using the following script, which I got from
How can I monitor the call count per stored procedure per timespan?

SELECT
    OBJECT_NAME(qt.objectid)
  , qs.execution_count AS [Execution Count]
  , qs.execution_count / DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second]
  , qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
  , qs.total_worker_time AS [TotalWorkerTime]
  , qs.total_elapsed_time / qs.execution_count AS [AvgElapsedTime]
  , qs.max_logical_reads
  , qs.max_logical_writes
  , qs.total_physical_reads
  , DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE
    qt.[dbid] = DB_ID()
ORDER BY
    qs.execution_count DESC
OPTION (RECOMPILE);

The DMV sys.dm_exec_query_stats reports cumulative values since the last restart. If you want to measure over a fixed period, use the command below to reset wait stats.

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);

DBCC SQLPREF("sys.dm_os_latch_stats",CLEAR); --this one never worked for me

When was sys.dm_os_wait_stats last cleared? But in Sql Server 2005, how do I find it out?


texts: MySQL Cluster lock wait timeout exceeded when trying to deal with huge amount of queries

texts:
I'm trying to deal with many connections and sometime I get following error:

ERROR : Lock wait timeout exceeded; try restarting transaction

The bigger number of queries the least number of connections working.

It's happening with all queries (INSERT, UPDATE, SELECT). For example, simple query:

INSERT IGNORE INTO table (first,second,third,forth) VALUES (10, 2, 1, UTC_TIMESTAMP).

Here are some results:

[user@localhost ~]$ mysqlslap  -u my_user -p  -h host --create-schema=my_database --query=query.sql --concurrency=2 --number-of-queries=1000 --iterations=5 --delimiter=";"
Enter password:
Benchmark
    Average number of seconds to run all queries: 0.804 seconds
    Minimum number of seconds to run all queries: 0.793 seconds
    Maximum number of seconds to run all queries: 0.843 seconds
    Number of clients running queries: 2
    Average number of queries per client: 500

[user@localhost ~]$ mysqlslap  -u my_user -p  -h host --create-schema=my_database --query=query.sql --concurrency=50 --number-of-queries=1000 --iterations=5 --delimiter=";"
Enter password:
Benchmark
    Average number of seconds to run all queries: 0.837 seconds
    Minimum number of seconds to run all queries: 0.795 seconds
    Maximum number of seconds to run all queries: 0.884 seconds
    Number of clients running queries: 50
    Average number of queries per client: 20

[user@localhost ~]$ mysqlslap  -u my_user -p  -h host --create-schema=my_database --query=query.sql --concurrency=4 --number-of-queries=10000 --iterations=5 --delimiter=";"
Enter password:
mysqlslap: Cannot run query INSERT IGNORE INTO table (first,second,third,forth) VALUES (10, 2, 1, UTC_TIMESTAMP) ERROR : Lock wait timeout exceeded; try restarting transaction
mysqlslap: Cannot run query INSERT IGNORE INTO table (first,second,third,forth) VALUES (10, 2, 1, UTC_TIMESTAMP) ERROR : Lock wait timeout exceeded; try restarting transaction

[user@localhost ~]$ mysqlslap  -u my_user -p  -h host --create-schema=my_database --query=query.sql --concurrency=2 --number-of-queries=5000 --iterations=5 --delimiter=";"
Enter password:
mysqlslap: Cannot run query INSERT IGNORE INTO table (first,second,third,forth) VALUES (10, 2, 1, UTC_TIMESTAMP) ERROR : Lock wait timeout exceeded; try restarting transaction

[user@localhost ~]$ mysqlslap  -u my_user -p  -h host --create-schema=my_database --query=query.sql --concurrency=1 --number-of-queries=10000 --iterations=5 --delimiter=";"
Enter password:
Benchmark
    Average number of seconds to run all queries: 7.639 seconds
    Minimum number of seconds to run all queries: 7.237 seconds
    Maximum number of seconds to run all queries: 7.903 seconds
    Number of clients running queries: 1
    Average number of queries per client: 10000

So, what the matter? As you can see I can work with only one thread when trying to deal with high number of queries (but, for some reason, the speed remains the same, not as in usual MySQL). How can I fix it?

UPD:
I didn't use real names in examples, but i have asked other people from work, they say I can use.
So, here is actual table structure:

 t_likes | CREATE TABLE `t_likes` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `like_to` int(11) unsigned NOT NULL,
  `like_to_type` int(11) unsigned NOT NULL,
  `like_from` int(11) unsigned NOT NULL,
  `like_datetime` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `like_to_like_to_type_like_from` (`like_to`,`like_to_type`,`like_from`),
  KEY `like_from` (`like_from`),
  KEY `like_to_type` (`like_to_type`),
 CONSTRAINT `t_likes_ibfk_2` FOREIGN KEY(`like_to_type`) REFERENCES `t_objects` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `t_likes_ibfk_1` FOREIGN KEY(`like_from`) REFERENCES `t_users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=ndbcluster AUTO_INCREMENT=1375 DEFAULT CHARSET=utf8


texts: Set Backup Compression on SQL Server 2008R2 on by default

texts:
I can't seem to find the option to set the server property that will enable compression on SQL database backup to be ON by default, so  I don't have to manually set it every time.

Can anyone point me at that please.


texts: select subquery condition in where statement

texts:
I am getting this error:

  ORA-06550: line 43, column 16: ORA-00904: "STOCK_BALANCE": invalid identifier

...for the following query:

select PARTY_ID,
       PARTY_NAME,
       IT_ID,
       IT_ID || ' - ' || IT_DESC || ' - ' || IT_BRAND,
       (select SUM(BALANCE)
          FROM (
            select
             WEAVING_YARN_TR.TR_ID,
             WEAVING_YARN_TR.GP_NO,
             WEAVING_YARN_TR.YR_ID,
             WEAVING_YARN_GATEPASS.TO_FROM as PARTY,
             WEAVING_YARN_GATEPASS.GP_DATE,
             WEAVING_YARN_TR.BAGS_IN,
             WEAVING_YARN_TR.BAGS_OUT,
             NVL(WEAVING_YARN_TR.BAGS_OUT,0)-NVL(WEAVING_YARN_TR.BAGS_IN,0) as BALANCE
            from WEAVING_YARN_TR,WEAVING_YARN_GATEPASS
            where WEAVING_YARN_TR.GP_NO=WEAVING_YARN_GATEPASS."GP_NO." and WEAVING_YARN_TR.YR_ID=WEAVING_YARN_ITEMS.IT_ID and WEAVING_YARN_GATEPASS.TO_FROM=WEAVING_PARTIES.PARTY_ID

          UNION ALL

          select
           null as TR_ID,
           WEAVING_WARP_SET.SET_ID,
           WEAVING_WARP_SET.YARN_ITEM,
           WEAVING_WARP_SET.PARTY,
           WEAVING_WARP_SET.RECEIVED_DATE,
           WEAVING_WARP_SET.TOTAL_BAGS_CONSUMED,
           null as BAGS_OUT,
           NVL(null,0)-NVL(WEAVING_WARP_SET.TOTAL_BAGS_CONSUMED,0) as BALANCE
          from WEAVING_WARP_SET
          where WEAVING_WARP_SET.YARN_ITEM=WEAVING_YARN_ITEMS.IT_ID and WEAVING_WARP_SET.PARTY= WEAVING_PARTIES.PARTY_ID
          ) t
        ) AS STOCK_BALANCE
FROM WEAVING_PARTIES
  CROSS JOIN WEAVING_YARN_ITEMS;
WHERE STOCK_BALANCE is not null

Even when I remove the AS as suggested by Vérace I still receive an error message:

...
    from WEAVING_WARP_SET
    where WEAVING_WARP_SET.YARN_ITEM=WEAVING_YARN_ITEMS.IT_ID and WEAVING_WARP_SET.PARTY= WEAVING_PARTIES.PARTY_ID
    ) t

    ) STOCK_BALANCE

    FROM WEAVING_PARTIES
    CROSS JOIN WEAVING_YARN_ITEMS
....

Error message:

  ORA-06550: line 41, column 19: ORA-00904: "STOCK_BALANCE": invalid identifier

Verace Still getting this error. ORA-20999: Failed to parse SQL query! ORA-06550: line 41, column 19: ORA-00904: "STOCK_BALANCE": invalid identifier

select
         PARTY_ID,
         PARTY_NAME,
         IT_ID,
         IT_ID || ' - ' || IT_DESC || ' - ' || IT_BRAND,
    (select

     SUM(BALANCE)

    FROM (

    select
     WEAVING_YARN_TR.TR_ID,
     WEAVING_YARN_TR.GP_NO,
     WEAVING_YARN_TR.YR_ID,
     WEAVING_YARN_GATEPASS.TO_FROM as PARTY,
     WEAVING_YARN_GATEPASS.GP_DATE,
     WEAVING_YARN_TR.BAGS_IN,
     WEAVING_YARN_TR.BAGS_OUT,
     NVL(WEAVING_YARN_TR.BAGS_OUT,0)-NVL(WEAVING_YARN_TR.BAGS_IN,0) as BALANCE
    from WEAVING_YARN_TR,WEAVING_YARN_GATEPASS
    where WEAVING_YARN_TR.GP_NO=WEAVING_YARN_GATEPASS."GP_NO." and WEAVING_YARN_TR.YR_ID=WEAVING_YARN_ITEMS.IT_ID and WEAVING_YARN_GATEPASS.TO_FROM= WEAVING_PARTIES.PARTY_ID

    UNION ALL

    select
     null as TR_ID,
     WEAVING_WARP_SET.SET_ID,
     WEAVING_WARP_SET.YARN_ITEM,
     WEAVING_WARP_SET.PARTY,
     WEAVING_WARP_SET.RECEIVED_DATE,
     WEAVING_WARP_SET.TOTAL_BAGS_CONSUMED,
     null as BAGS_OUT,
     NVL(null,0)-NVL(WEAVING_WARP_SET.TOTAL_BAGS_CONSUMED,0) as BALANCE
    from WEAVING_WARP_SET
    where WEAVING_WARP_SET.YARN_ITEM=WEAVING_YARN_ITEMS.IT_ID and WEAVING_WARP_SET.PARTY= WEAVING_PARTIES.PARTY_ID
    ) t

    ) STOCK_BALANCE

    FROM WEAVING_PARTIES
    CROSS JOIN WEAVING_YARN_ITEMS

    WHERE STOCK_BALANCE is not null;


texts:
short version
In master master replication, How to have one master write data with odd IDs and the other one with even IDs? Is it the way to avoid conflict?
detailed version
I wanted to setup a MySQL master-master replication on two virtual machine I have on my system, to just play around with replication and get familiar with the concept.
I edited [mysqld] section of /etc/my.cnf of Master-1 and it looks like:
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
datadir         = /var/lib/mysql

log-bin         = mysql-bin
binlog-ignore_db= mysql
binlog_format   = mixed

server-id       = 1

my.cnf of the Master-2 is almost identical to Master-2 except server-id which I set to 2. Then I restart the mysql service, and create slave users in each VMs:
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'<the-other-VM-ip>'
IDENTIFIED BY 'somePass'

Followed to that I set the Master config in each VM:
CHANGE MASTER TO MASTER_HOST='<the-other-VM-ip>',
MASTER_USER='<replicator>',
MASTER_PASSWORD='somePass';

And finally start slave on both machines. I created a database in Master-1 and added a table with some value. When I commit and check in Master-2 I can see all the data. But When I do the same in Master-2, the changes are not committed to the Master-1. SHOW SLAVE STATUS \G in Master-2 show me the following error:
Last_Errno: 1146
Last_Error: Error 'Table 'mydb.taggregate_temp_1212047760' doesn't exist'
            on query. Default database: 'mydb'.
Query: 'UPDATE thread AS thread,taggregate_temp_1212047760 AS aggregate
       SET thread.views = thread.views + aggregate.views WHERE thread.threadid = aggregate.threadid'

I stopped the salve, wrote SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; in mysql and then start the slave and ask for the status, which I get a new error:
Last_SQL_Errno: 1062
Last_SQL_Error: Could not execute Write_rows event on table mysql.user;
                Duplicate entry 'localhost-root' for key 'PRIMARY',
                Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;
                the event's master log mysql-bin.000001, end_log_pos 26231
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1

I believe this could be solve using auto-increment-offset = 2 in one config file, although I'm not sure about the procedure... beside not quite sure what SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; is and what it does, when Googled my first problem found it here.
I would be thankful for a description of the errors and how to overcome them.


texts:
Could the error shown on the screenshot be the reason why my site is very slow?

If so, I really need help to fix it since I am far from being an engineer!


texts:
I am wondering about the best way to design indexes. For Example i have this table

CREATE TABLE [dbo].[CoursePrerequisiteAssignment](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[PrerequisiteId] [int] NOT NULL,
[CourseId] [int] NULL,
[ProfileId] [int] NOT NULL,
[InstanceId] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[UpdatedDate] [datetime] NOT NULL,
[ProgramSelectedId] [int] NULL,
[OptOut] [bit] NULL,
 CONSTRAINT [PK_CoursePrerequisiteAssignment] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_CoursePrerequisiteAssignment] ON [dbo].     [CoursePrerequisiteAssignment]
(
[ProfileId] ASC,
[InstanceId] ASC
)
INCLUDE (   [CourseId]) GO

CREATE NONCLUSTERED INDEX [IX_CoursePrerequisiteAssignment_1] ON [dbo].[CoursePrerequisiteAssignment]
(
[CourseId] ASC
)
 GO

CREATE NONCLUSTERED INDEX [IX_CoursePrerequisiteAssignment_2] ON [dbo].[CoursePrerequisiteAssignment]
(
[ProgramSelectedId] ASC
)
 GO

CREATE NONCLUSTERED INDEX [IX_CoursePrerequisiteAssignment_3] ON [dbo].[CoursePrerequisiteAssignment]
(
[ProgramSelectedId] ASC,
[PrerequisiteId] ASC,
[InstanceId] ASC,
[ProfileId] ASC
)
 INCLUDE (  [CourseId],
[CreatedDate],
[UpdatedDate],
[OptOut])
 GO

CREATE NONCLUSTERED INDEX [IX_CoursePrerequisiteAssignment_4] ON [dbo].[CoursePrerequisiteAssignment]
(
[CourseId] ASC,
[PrerequisiteId] ASC,
[ProfileId] ASC,
[InstanceId] ASC,
[ProgramSelectedId] ASC
)
GO

This Table is very active and has high reads and writes. this is the usage of these indexes

note : the counts for IX_CoursePrerequisiteAssignment is not accurate because i just rebuild it

These columns are the predicts for the top executed queries

ProgramSelectedId,ProfileId,PrerequisiteId,InstanceId
CourseId,PrerequisiteId,ProfileId,InstanceId,ProgramSelectedId
ProfileId,InstanceId
ProfileId,InstanceId,ProgramSelectedId
ProgramSelectedId,ProfileId,id,InstanceId
CourseId

I see that i don't need all these indexes as they overlapping. What is your recommendation for best performance ?


texts: How to block user access to perform database application update?

texts:
Where I work we have a SQL Server database (Microsoft SQL Server 2008 R2) that serves as the back-end with two different user interfaces, a .NET web interface and a FoxPro interface.

Every month we need to apply updates to both the Web and FoxPro clients.  Before doing this, we are advised to make sure that no one is accessing the database during the update process.

What it the easiest way to prevent access to the database while we update it? Follow-up question: What is the best way to prevent access while we update it?


texts: Querying an enormous table (~150 million rows) with multiple joins - SQL Server

texts:
First off, I don't have SHOWPLAN permission on this server or permission to add indexes, etc. I am not a database administrator by trade; I'm just trying to query a database. There is already indexes set up on both tables using the join variables in the order in which they appear in the join.

Here is the code:

SELECT rt.VehicleID,rt.PersonalAutoCovID,SUM(CAST(rd.OutputValue AS INT)) VehicleScore
FROM #RatingDataTemp1 rt
INNER JOIN modelingdata md ON md.BranchID = rt.BranchID
    AND (rt.EditEffectiveDate >= md.EffectiveDate OR md.EffectiveDate IS NULL)
    AND (rt.EditEffectiveDate < md.ExpirationDate OR md.ExpirationDate IS NULL)
GROUP BY rt.VehicleID,rt.PersonalAutoCovID

#RatingDataTemp1 is 250K rows, and modelingdata is 150M rows. This join currently takes 4 minutes, but I really feel it should be faster. Any way I can speed it up?


texts:
I am using pgpool-II 3.3.1 to load balance connections to my postgres 9.3. I am experiencing an ocassional stability issue where clients are not able to connect to pgpool (but they are able to connect directly to postgres). I am no where near any configured connections limit. What's strange is the following:

pgpool shows that some clients are still connected (has 1 near them in the list) even though clients were shut down hours ago (I have 60 seconds timeout for idle connections)
pgpool shows that there are available connections (0 near them in the list) but still clients cannot connect
There are a few queries hanging in the db (should be short but stack for minutes). Those are SELECT and no reason to have any locks. Once I kill those queries in postgres, pgpool can get connections again. (remember I could connect directly to db at any time, pgpool was the issue)

Did anyone see any of these issues hapenning? Does anyone have a stable pgpool instllation so I would know it is mature enough?


texts: Export just rows modified in last month (date defined by user)

texts: I'm asking if it's possible in MySQL to export just rows modified in a period of time for example in the past month, if it is possible, how?


texts: How to measure or find cost of creating a query plan?

texts:
I have a typical case where parameter sniffing causes a "bad" execution plan to land in the plan cache, causing subsequent executions of my stored procedure to be very slow. I can "solve" this problem with local variables, OPTIMIZE FOR ... UNKNOWN, and OPTION(RECOMPILE). However, I can also dive into the query and try to optimize it.

I'm trying to determine whether I should: given limited time to fix problems I would like to know the cost of not doing it. As I see it, if I just stick with OPTION(RECOMPILE), the net effect is that a query plan is recreated every time the query is run. So, I think I need to know:

How to find out what the cost of creating a query plan is?

To answer my own question, I've Googled (e.g. with this query), and I've gone through the documentation of columns for the  dm_exec_query_stats DMV. I've also inspected the output window in SSMS for "Actual Query Plan" to find this info. Finally, I've searched DBA.SE. None of those led to an answer.

Can anyone tell me? Is it possible ot find or measure time needed for plan creation?


texts: SQL Server - NTEXT columns and string manipulation

texts:
I have a table with an NTEXT column called comments. I have a second string, let's call it anothercomment (a varchar) that needs placing inside a given comments string after the word UPDATEHERE.

Casting to nvarchar(max) truncates the comments string, so I cannot use the likes of CHARINDEX() (Msg 8152, Level 16, State 10, Line 2
String or binary data would be truncated.). I have used datalength() to check that there are a few thousand columns that are >8000 characters.

An example of what I want to achieve (albeit with much longer strings):

  comments - This is a test UPDATEHERE This is the end of the test

  anothercomment - . This is inserted.

  Resulting string -  This is a test UPDATEHERE. This is inserted.  This is the end of the test

I realize that this is trivial with a normal varchar()/nvarchar(), but ntext is a complete and utter nightmare to work with. I realize it's a deprecated data type, but I did not write the application in question.


texts: ORA-00205 error during the startup of physical standby database

texts:
Steps I have followed:-

Created a physical standby database by using DUPLICATE TARGET DATABASE.
Then checked the log gap between these two databases which was fine and in sync.
After that standby database closed using the shutdown abort command.
Then when tried to open the standby in mount mode it was showing this error.

  ORA-00205: error in identifying control file, check alert log for more info)

Checked the alert log where in the mentioned path there is no control file but in initSID.ora file, controlfile path mentioned correctly and which was different from what is showing in the alert log.


texts:
I'm trying to represent this:

A message can be sent to one or more recipients.
A user (recipient) can receive zero or more messages (but he/she could receive a
message only once).

Is my database design correct?


texts: Teradata to drive responsive web application

texts:
Does anyone have experience using Teradata to drive a typical, responsive client-side web application? What is your evaluation? Specifically, how well does it perform in single-read and single-write?

Single-read: The total time-to-first-byte between a request being sent and a response being received to read a single document or row that contains meaningful information for a webapp. For example, consider a table called profiles with 100,000 records in which various attributes are stored on each row. How much time does it take to get a single profile up to a client-side webapp by querying on a primary key? How much time does it take to do the same operation, querying on a non-indexed field?

Single-write: How much time does it take to insert a new row or update an existing row. Assume that all indexes that are needed to perform the single-read quickly need to be written during a single-write operation.

If you have built a client-side web application with a Teradata backend, what sort of challenges did you face? I have seen Teradata as primarily an analytical and data warehousing tool rather than an operational database, but I am being asked to investigate Teradata as the backend for an operational web application.

I would like to find out if anyone has tried this, and whether it could keep up with more commonly used databases for webapps. I'm not looking for opinions, but for honest evaluations of how the technology works in this unusual use-case by people who have tested it, and for people's experiences using Teradata to drive a responsive webapp.

I understand there are many variables here, but I'm looking for real-world examples of Teradata being used as a backend for a client-side webapp, and how it performs in practice. Thus I'm more interested in TTFB than I am in DBS time, though a thorough breakdown of how long each piece of a transaction takes would be appreciated.

My previous question, Teradata REST API performance metrics, was specifically about Teradata REST performance. However, I've realized that almost no one uses Teradata's REST capabilities in this community (questions have gone unanswered for years).

My particular use case

Insac requested that I share some details of my specific use-case to help focus answers. With the caveat that I'm looking for an answer about how Teradata performs in general for webapps, here is what my app will need to do:

Client-side webapp with RESTful interface
Estimated ~5,000 users
Would be driven by ~25 Terdata views and a handful of materialized tables
Teradata instance would be shared with a data warehouse
Some views would perform joins across several underlying views
The underlying tables for these views are structured for analytics
Almost all queries are read-only operations; certainly all intensive queries.
A typical page refresh would send off ~100 queries to Teradata after receiving a response from a few initial queries to dynamically generate SQL on the client side.
At a minimum, any user action would send off about 20 queries.
The web application needs to be responsive and fluid. Responses in less than 2 seconds are a requirement, with sub-second response-times greatly preferred.

A very high-level overview of the purpose is accessing data from a social-network-like timeline feed history for a particular profile. The initial queries would return a chronologically sorted list of events that match basic filters such as category and date. This result set then would drive dynamically generated SQL queries for the first results, returning the content of each event on the feed.

I am evaluating Teradata for this application, but would like to know in general if Teradata is suited to client-side webapps so that I can support comparative analysis of database technologies for future projects as well.

Edit: The scope of my project is shifting to include more web-2.0 functionality, meaning the application needs to write to several views, and writes will be a common part of webapp use. With the read-only constraint removed, what would a webapp on Teradata look like?


texts: Can't stop "SQL Server Polybase Data Movement"

texts:
When I start Windows the "SQL Server Polybase Data Movement" service is in the "Change pending..." state and I can't close it. When I try to stop it mmc crashes.

In Windows services, the status = starting, but it never changes either. I cannot stop it here either.

Does anyone know how to stop it?


texts: Identical subquery optimisation in an update

texts:
update activitybooking set `submitted`='1' where id='958'
    and (select SUM(pool1_count) from (select pool1_count from `activitybooking` where `abt`='12' and
                            (id='958' or `submitted`='1' or `submitted`='3' or `submitted`='8')) as temp_pool1_count) &lt 10
    and (select SUM(pool2_count) from (select pool2_count from `activitybooking` where `abt`='12' and
                            (id='958' or `submitted`='1' or `submitted`='3' or `submitted`='8')) as temp_pool2_count) &lt 5
    and (select SUM(pool3_count) from (select pool3_count from `activitybooking` where `abt`='12' and
                            (id='958' or `submitted`='1' or `submitted`='3' or `submitted`='8')) as temp_pool3_count) &lt 20

The statement above has three identical subqueries and seems to execute them independently. How can I best rewrite the update to perform the subquery only once?

(The idea of this query is to only perform the update if doing so would not cause the sum of pool1_count to exceed the maximum allowed pool1_count and the same for pool2, pool3 ....)


texts: What specifically does OracleBulkCopy do, and how can I optimize its performance?

texts:
To summarize the specifics: We need to stage approximately 5 million rows into a vendor (Oracle) database. Everything goes great for batches of 500k rows using OracleBulkCopy (ODP.NET), but when we try to scale up to 5M, the performance starts slowing to a crawl once it hits the 1M mark, gets progressively slower as more rows are loaded, and eventually times out after 3 hours or so.

I suspect it's related to a primary key on the table, but I've been trawling the Oracle forums and Stack Overflow for information and a lot of what I'm reading contradicts that (also, a lot of posts seem to contradict each other).  I'm hoping that somebody can set the record straight on some closely-related questions about the process:

Does the OracleBulkCopy class use conventional or direct-path loading? Is there some way I can confirm this, one way or another?
Assuming it does use direct-path loading: Is it true that Oracle automatically sets all indexes to unusable during the load and puts them back online afterward? I've read several statements to this effect but again, cannot confirm it.
If #2 is true, then should it make any difference what indexes are on the table before I initiate a bulk copy operation?  If so, why?
Related to #3, is there any practical difference, in general, between bulk loading with an unusable index vs. actually dropping the index before the load and recreating it afterward?
If #2 is not correct, or if there are some caveats I'm not understanding, then would it make any difference to explicitly make the index unusable before the bulk load, and then  explicitly rebuild it afterward?
Is there anything else, other than index builds, that could cause a bulk copy operation to grow progressively slower as more and more records are added? (Maybe something to do with logging, although I would expect that bulk operations are not logged?)
If there really is no other way to get the performance up to snuff aside from dropping the PK/index first, what steps can I take to make sure that the index doesn't completely disappear, i.e. if the connection to the database is lost in the middle of the process?


texts:
In my setup for a web application using PHP and MySQL, I am using separate databases for each user's private data. So in essence, every authenticated user will have access to only two different databases myappdb_public and myappdb_user_###. User credentials will be saved in a separate database myappdb_creds. The idea behind this setup is to increase the security of private data while maintaining a shared data resource.

Is there a way to create a new database from a "template" of another database structural setup.
Is there a way to update all "child" databases structures when the template is updated?

I have used replication before, but that was a totally different setup and not sure if there is a way to replicate structure only across multiple databases on the same local server.


texts: Oracle DB what is the difference between ALL, USER and DBA Objects?

texts:
As the title suggests, I am trying to understand the structure of our Oracle DBs a little more and a little confused by the ALL, DBA & USER Objects.

A quick SQL

select count(*) ALL_OBJS from all_objects where owner = 'XXXX';
select count(*) DBA_OBJS from dba_objects where owner = 'XXXX';
select count(*) USER_OBJS from USER_OBJECTS; (logged on as XXXX)

Returns:

  ALL_OBJS
----------
      1591

  DBA_OBJS
----------
      1632

 USER_OBJS
----------
      1632

As I presume our user has DBA rights and the USER objects and DBA objects are the same? But what are ALL objects?
If you are looking to clear out a User's SCHEMA which would you remove from or would you drop objects from all three?


texts: PgAdmin 4 - How to connect to database using unix socket via ssh tunnel?

texts:
Even though it might sound related to this question, it isn't, because I'm intreseted in the use of unix sockets instead of TCP/IP ones.
However, I am unable to connect to a remote PostgreSQL database using unix sockets and an ssh tunnel with pgAdmin4. pgAdmin always claims a refused connection by the server and asks for a password, which makes me wonder. When sshing into the server and using psql a connection can be made just flawlessly using the unix socket (/var/run/postgresql). Is there anything special that needs to be set to allow such a setup, or might this even be a bug in pgAdmin itself? I'd appreciate any ideas to solve this problem without having to resort on TCP/IP connections on the remote side.
I also post some screens from my current config:


texts:
There are two SQL Server 2012 instances on the server. Named instance gives following error when Management > SQL Server Logs is clicked. Default instance works OK and opens Log file viewer.

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Standard Edition

  TITLE: Microsoft SQL Server Management Studio
  Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

  ADDITIONAL INFORMATION:
  An exception occurred while executing a Transact-SQL statement or batch.
  (Microsoft.SqlServer.ConnectionInfo)
  A severe error occurred on the current command.  The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)


texts: Restore a SQL 7 database into SQL 2014 without in-between versions?

texts:
Our software is over 20 years old, and dates way back to databases such as Sybase and SQL 7. Now let's ignore the Sybase part. We have many old customers using very old versions of our software, varying between SQL 7 (2001-2002) to SQL 2014 (We support today). Technically speaking, our software can still actually run on MSDE 2000. But that's besides the point. We enforce all updated clients to use at least SQL Server 2008 R2, but officially support up to 2014. And yes, we do have many old customers still using ancient versions of our software on Windows '95.

Anyway, down to the question. Currently, whenever we have one of these old customers return and wish to upgrade, we have to go through a big elaborate upgrade process. Now the scripts are the easy part. I am actually just finalizing the latest major upgrade script, which supports upgrading databases from our 2001 (SQL 7) version to our latest version. The very same script accommodates for any in-between version.

However, before I can run these scripts, I first have to take the database through a series of manual and tedious steps. I have it sped up to this process:

Acquire database over internet from customer
Restore SQL 7 database directly into MSDE 2000 (on a dedicated XP machine)
Raise database compatibility to MSDE 2000
Backup database to a new file
Restore MSDE 2000 database directly into SQL Server 2008 R2
Raise database compatibility to SQL Server 2008
Backup database to a new file
Restore SQL 2008 R2 database directly into SQL Server 2014
Raise database compatibility to SQL Server 2014
Run all scripts on database for software changes over time
Backup database to a new file
After installing software for customer, restore database over internet

So as you can imagine, a lot of returning customers would be a full-time job. I suppose I could build some sort of automation on this XP machine, but this is still a manual process I'm looking to eliminate. We don't want to even need to acquire their database, and many of them don't want us to see their data.

Is there a method which I can easily distribute onto our client's machine, as part of the installer, which can automatically perform the upgrade from any given database version from SQL 7 to SQL Server 2014?

EDIT

To be a bit more clear on the installation process, this is an installer we already have capable of restoring databases directly from 2000 to 2008 R2. But we need to expand this installer to accommodate for upgrading databases ranging from MSSQL 7 to 2014. As I now understand, I can jump straight from 7 > 2005 > 2014. But that's still a hop I would like to eliminate, if possible. I would hate for our installer to load both 2005 and 2014 on our clients' machines.


texts: How to avoid DROP TABLE for a specific MySQL table

texts:
I have used a Wordpress plugin that for some unknown reason is always deleting a database table (cerber_files).

The plugin's author is not giving support for this issue.

I think in order to solve this, there would be some option that would prevent DROP TABLE from being executed ONLY FOR THIS TABLE.

I've researched a lot but found no solution that worked for this issue.

Any idea?


texts: Is it possible for a FOR LOOP cursor execute operations asynchronously?

texts:
I was having a problem in which the number in a table isn't updated between each commit, it was supposed to be incremented 1 by 1 but it was been incremented 2 by 2, I included logs between the execution, and encountered that during the for loop the procedure was dealing with 2 "some_number" before even passing thought the place where it commits.

The structure in which I had the problem was like this:

create or replace procedure test_procedure(unique_code number) is

Cursor C01 is
    select  some_number,
            nvl(some_varchar_bool,'Y') some_varchar_bool
    from    some_place
    where   some_code = unique_code;

begin

for r_c01 in C01 loop
    begin
    if  (r_c01.some_varchar_bool = 'Y') then
        first_procedure(r_c01_w.some_number); -- This one doesn't commit, selects a number to save in other table, and updates the number selected from that table by 1
        second_procedure(r_c01_w.some_number, 'varchar_word'); -- This one commits at the end
    end if;
    end;
end loop; --C01

commit;

end;

The 2 by 2 problem was resolved when I changed the for loop statement to the open cursor statement like this:

open C01;
loop
fetch C01 into
    some_number,
    some_varchar_bool;
exit when C01%notfound;
    if  (some_varchar_bool = 'Y') then
        first_procedure(some_number); -- This one doesn't commit, selects a number to save in other table, and updates the number selected from that table by 1
        second_procedure(some_number, 'varchar_word'); -- This one commits at the end
    end if;
end loop;
close C01;

So again I raise the question: Is it possible for this FOR LOOP cursor execute the "first_procedure" 2 times before executing the "second_procedure", that commits the changes?


texts:
I'm using MS Access 2010.

I have a form with a number of fields of various types (number, text, lookup, checkbox).

Users can right click on any field to filter the form.

Then I have a button that generates a report based on this filtered data:

Private Sub Filter_and_Report_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "WallsRpt", acViewPreview, , strWhere
End Sub

My question is how to list the fields that were filtered in the report (probably in the header/footer).


texts:
I'm designing a section of my database which links User, Team and Hunt. I would like users to be members of a team but the team can play multiple hunts (so can be used multiple times).

The first idea involves a 'HuntTeam' which links Hunt to Team directly however I need to know which users within the team will be playing so I've added a 'Player' table like below:

The second idea is to remove HuntTeam completely and link Player directly to Hunt so it does the job of linking teams and users playing like below:

I'm worried that although the second option looks more simple, I would need to run quite a few 'belongs to through' or JOIN methods to reference the teams within a Hunt. Would it

Would you recommend one over the other and if so why? Perhaps you have an alternative solution?


texts: MongoDB setting permission for functions

texts:
I want to create execute permission on some MongoDB functions
like permissions in Sql Server.

I want users access data that function provides for them. This is, because of security of application
that don't other team staf change collections.

Is there any solution for doing this?


texts: How To get cube dimension in ssas 2008r2

texts: How To get number of dimensions from each cube in ssas 2008r2 through query?


texts: Cold backup database, what files to copy, slow shutdown?

texts:
After a broken server installation I am going to backup a mysql database with a cold backup. This is, because I am not able to dump the sql tables and somehow everything is broken. My questions are at the bottom of this post.

$ tail /var/log/mysql/error.log
140511  0:23:35 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
140511  0:23:35 [Note] Server socket created on IP: '127.0.0.1'.
140511  0:23:35 [ERROR] Can't start server: Bind on TCP/IP port: Cannot assign requested address
140511  0:23:35 [ERROR] Do you already have another mysqld server running on port: 3306 ?
140511  0:23:35 [ERROR] Aborting

140511  0:23:35  InnoDB: Starting shutdown...
140511  0:23:36  InnoDB: Shutdown completed; log sequence number 16358534
140511  0:23:36 [Note] /usr/sbin/mysqld: Shutdown complete

and

netstat -lp | grep 3306
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)

gives no output, an so on and so forth..

However, following the cold backups section here, I have to

1. Do a slow shutdown of the MySQL server and make sure that it stops without errors.
2. Copy all InnoDB data files (ibdata files and .ibd files) into a safe place.
3. Copy all the .frm files for InnoDB tables to a safe place.
4. Copy all InnoDB log files (ib_logfile files) to a safe place.
5. Copy your my.cnf configuration file or files to a safe place.

How do I find out if my database was shutdown slowly? What may happen if I do the other steps and it did not get a cold shutdown?
Is there any chance to find out, whats the meaning of "all"? In /var/lib/mysql I find one ibdata1 file,
in /var/lib/mysql/ e.g. wordpress/ I find several .frm files,
There are two ib_lofiles in /var/lib/mysql, and
in /etc/mysql/ is one my.cnf file.