texts: Why query_cache_type is disabled by default start from MySQL 5.6?

texts:
We've upgraded to MySQL 5.6 and start seeing the loading of db server increased significantly, and finally found out the  query_cache_type is defaulted to off start from 5.6.

We enabled it again and see the loading decrease, why this value is being disabled by default start from MySQL 5.6? I cannot see the problem in enabled it.


texts: Very slow merge replication synchronization

texts:
I had an unforeseen issue with a sub going down (generic error, was probably a connection issue) and now that I've reconnected the sub it is insanely slow re-syncing. It seems to download about 1000 changes then sit there for about an hour before adding another 1000.

Any ideas why this is going so slowly? This is just one table of dozens and I feel like this won't catch up before Monday (when I have users meant to be coming online).

Any help is appreciated!
Thanks!

UPDATE

It's been running for over 24 hours now and is slowly chewing through the records but at a still very slow pace. It now toggles between the shot above and 'Enumerating changes in filtered articles using Subscriber's assigned partition ID'. Yes, there is a filter in place but it's been there since I first set up replication and has never caused an issue before.

SECOND UPDATE

When I go into Replication Monitor there is an error:

The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

But what is strange is that there is zero system activity and I have another subscriber on this publication working as well a secondary replication from the same publisher to the same subscriber that is working perfectly. I've tried stopping and restarting, taking and applying a fresh snapshot but nothing seems to work. There are no blocked processes, no active queries that are deadlocking or anything else I can think to check.

To be clear:

Publication A => Sub 1: WORKING
Publication A => Sub 2: NOT WORKING
Publication B => Sub 1: WORKING
Publication B => Sub 2: WORKING

If anyone has any help, I'd much appreciate it.


texts: Why does MySQL produce so many temporary MYD files?

texts:
On a Debian Linux server, hosting many PHP/MySQL websites (photo galleries), sometimes I have "many" files like /tmp/#sql_6405_58.MYD.

For example today :

[2012-12-15 15:18:11] /tmp/#sql_6405_6.MYD : 88MB
[2012-12-15 15:18:11] /tmp/#sql_6405_3.MYD : 22MB
[2012-12-15 15:18:11] /tmp/#sql_6405_4.MYD : 138MB
[2012-12-15 15:18:11] /tmp/#sql_6405_10.MYD : 88MB
...
[2012-12-15 15:18:11] /tmp/#sql_6405_9.MYD : 15MB
[2012-12-15 15:18:11] /tmp/#sql_6405_65.MYD : 49MB
[2012-12-15 15:18:11] /tmp/#sql_6405_44.MYD : 69MB

(59 files at the same time, for more than 6GB... yes I monitor big files in /tmp)

Unfortunately, /tmp is on the same partition than / and it temporary breaks the web server, because / is full I suppose. Then files disappear and the server is back to normal.

All the file names follow the #sql_6405_*.MYD pattern. I would like to understand which MySQL operation implies so many temporary files. I have approximately 2000 databases on this server. Is it possible to know which database is concerned?


texts: Fastest method for inserting large number of records from query between tables

texts:
I have a query that returns 14 million results that I need to store in a table, the SELECT query itself returns in a little over 2 minutes but INSERT statement takes well over 30 minutes (which is unacceptable). We are running this in an Azure Database currently set as a Premium tier database, and I need to keep this process all contained within SQL (no exporting to file etc).

Sample Query:

SELECT
v.Param1,
v.Param2,
lt.Param3,
lt.Param4
INTO #tempTable
FROM <View> v
  join LargeTable lt
    on lt.nVarCharCol = v.nVarCharCol

INSERT INTO DestTable SELECT Para1, Param2, Param3, Param4 FROM #tempTable

Things I have tried so far:

Turned off indexes on DestTable
Batching inserts
Creating indexes on temp tables

What is the fastest/best way to insert these query records into a table?

Any recommendations to speed this process up?


texts: Enabling MSSQL System Trace for DB Objects

texts:
I am trying to get changed recorded in MSSQL server for tables, view and stored procedures.

I have tried to achieve the same by right clicking on the server->Reports->standard reports->schema changes history. But it doesn't show all the history from the beginning.

How far can I get sys trace from database as currently, I am getting records from last few months only. My requirement is to capture them from the very beginning.

Regards,
Zafor.


texts: How to select records that meets specific criteria

texts:
I want to get the result for a particular ID if only all the "Prospect" have after it "Qualify", if not all the calculation will be dropped

Below is example sample and output. How it can be done using sql query?

+----+----------+-----------+-------+
| id |  stage   | nextstage | value1 |
+----+----------+-----------+-------+
|  1 | prospect | qualify   |   200 |
|  1 | solution | develop   |   500 |
|  1 | prospect | null      |   400 |
|  2 | prospect | qualify   |   100 |
|  2 | develop  | solution  |   300 |
|  2 | prospect | qualify   |   100 |
+----+----------+-----------+-------+

Output:

+----+-------------+
| id | time in P2Q |
+----+-------------+
|  1 | NULL        |
|  2 | 200         |
+----+-------------+

I have tried the following, but didn't know how to drop the entire calculation if there is one prospect record that don't have qualify after it

SELECT

 SUM(CASE WHEN stage = 'Prospect' and nextstage='Qualify'  THEN value1 END) AS timeP2Q
 from table1
 group by ID


texts: DataGrip shows a time substantially slower than EXPLAIN ANALYZE

texts:
I searched alot for the answer to my question.

I have a table with 134 rows and 4 rows. My EXPLAIN ANALYZE says the execution time was: 0.038ms. But the actual execution time (using DataGrip) was: 123ms

A complex query with multiple joins shows these stats:
EXPLAIN ANALYZE execution time: 38.605ms
DataGrip execution time: 177ms

My VPS has 2GB ram and 2 vCores.

Is it more likely a problem with my server or my config, or is this behavior normal?

Here is the EXPLAIN ANALAYZE of the simple select:

Seq Scan on champions  (cost=0.00..4.34 rows=134 width=37) (actual time=0.007..0.024 rows=134 loops=1)
Planning time: 0.053 ms
Execution time: 0.044 ms

Here is the log of DataGrip:

[2017-02-10 21:38:30] 134 rows retrieved starting from 1 in 135ms (execution: 127ms, fetching: 8ms)

I am really confused!

EDIT:

These are my only changes to my config:

max_connections = 200
shared_buffers = 512MB
effective_cache_size = 1536MB
work_mem = 32MB
maintenance_work_mem = 128MB
min_wal_size = 1GB
max_wal_size = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
listen_addresses = '*'


texts: Replicate only specific records of a table

texts:
I have setup a merge replication and have got a new requirement:
The records of a table should not be replicated until the [state] column is changed from 0 to 1. I have created a filter for the replication to limit replication to records with state = 1, but now the records are removed from the subscriber when the state is set to 0.
Is it possible to keep those records on the subscriber?


texts: MySQL set ID of primary child with constraint

texts:
I have two MySQL tables where the primary relationship between the two is one-to-many. I also need a one-to-one relationship in the parent_table with the ID for the primary record from the child_table. You can kind of think of it as like a dad having lots of kids but having one kid that's his favorite ;P. Is it possible to setup a DB constraint such that a parent can have the ID of a child only if that child is a child of the parent?

parent_table (One)
  id: Primary Key
  primary_child_id: {ID from child table}

child_table (Many)
  id: Primary Key
  parent_table: {ID from parent table}


texts: How do databases deal with file system fragmentation?

texts:
Especially analytic databases that try to optimize for queries that scan large portions of tables instead of randomly accessing specific rows. For example Redshift has a concept of a sort key that, given you VACUUM after major inserts/updates/deletes, ensures the data is sorted on disk according to the sort key. Ideally this minimizes disk seeks and optimizes large scans.

However, my understanding is databases write their data blocks to a set of database files. And these files are managed by the file system which has it's own block scheme that is subject to fragmentation. So how does the database manage this (or maybe more precisely how does something like VACUUM manage this) so that database blocks are sorted correctly on disk?

I'm guessing it's to do with the fact that most databases run on servers where not much else is running? Or the database is usually given its own disk partitions? Or the database files reserve a bunch of space ahead of time? In either of these cases there aren't too many processes writing to the same disk space as the database so there isn't much fragmentation outside of the database's control?


texts:
My app runs a lot of queries like this

SELECT ... FROM Table
    WHERE CategoryID IN (SELECT CategoryID FROM OtherTable WHERE ..)

To save performance I decided to cache the second subquery in the application's memory (it rarely changes) and change the DB-query to

SELECT ... FROM Table
    WHERE CategoryID IN (123,234,345,456,567,678,789,...)

Question: does this make any sense?

PROS: it caches the data which is a good thing. Right?

CONS: the second query cannot be parameterized, so the execution plan is not compiled/cached by the SQL server. Also, the IN clause is treated like a dozen OR's, can this degrade performance?

CategoryID field  in Table is an integer with an index.

PS. I tried testing, but I cannot get my tests to the scale of my app (that runs 1000s of queries) so I'd sppreciate any "generic" explanation from someone... Thanks!


texts: AWS RDS MySQL some time get slow performance when update table1 set field='any word' where primarykey=1234

texts:
Normally when update table1 set field='any word' where primarykey=1234 take 1sec. or less but sometimes random take much more time possible 500sec. or more.

I was set very well and never see high CPU, or IOPS

I use MySQL RDS 8.0.17 and workaround was down wait_time from 3600(default) to 200 to close sleep connections and then working better.


texts: Importing data from MS Access database to an existing Postgres database

texts:
I'm developing a Django based website with a Postgres DB.  The problem I'm having is importing the data from an Access DB into it.  To be specific, I'm not trying to translate/import the schema from Access into Postgres flavoured SQL -- as far as I've gathered there are lots of tools to do that, and it seems more or less straightforward.  I'm trying to import data from just a number of tables and, hopefully, their relationships.

Basically, my problem is that I'm not sure at all what would be the most painless way of doing this.  The best idea I could come up with so far is dumping the database from Access into a Postgres compatible SQL, massaging the SQL to get it in line with the schema in the new DB and then loading that.  But this sounds like too much yak shaving.  Is there a better way to do this?

On the Postgres side of things everything is still in development stage, so the DB will be empty, I'm not at risk of loosing data from it.  I should note for completeness sake that I'm using South for migrations in Django and there are a few migrations done (not sure if this can complicate things). The Access DB is Access 2003 and Postgres is 9.1.

I'll be happy to give more details if needed. Thanks!


texts: Mysql : how to use column name as a filter in subquery?

texts:
I cannot find the right syntax to use a column from a table in a subquery.

In the following query, I want to make a sum of DISTINCT value from a subquery.
That results in an error : Unknown column wo.CODE_WORKORDER which is related to the HAVING clause. If I replace wo.CODE_WORKORDER by '17593', it works.

Any idea ?

select
(
    select
        sum(der.UC)
        FROM
            ( select distinct
                wo2.CODE_WORKORDER,
                invl2.UNITCOST as UC
            from
            tbl_workorder as wo2 inner join
            tbl_workorderitem as woi2 on wo2.CODE_WORKORDER=woi2.FK_CODE_WORKORDER inner join
            tbl_invoicesource as invsrc2 on wo2.CODE_WORKORDER=invsrc2.FK_CODE_WORKORDER inner join
            tbl_invoiceline as invl2 on
            (invsrc2.FK_CODE_INVOICE=invl2.FK_CODE_INVOICE AND woi2.FK_CODE_ITEM=invl2.FK_CODE_ITEM)
        HAVING
            wo2.`CODE_WORKORDER` = wo.`CODE_WORKORDER`) as der
)
from
    tbl_workorder as wo
where
    wo.`CODE_WORKORDER` = '17593'


texts: Postgresql 8.3: Limit resource consumption per query

texts:
I'm using PostgreSQL 8.3+PostGIS 1.3 to store geospatial data on Ubuntu 8.04 Hardy.

This particular version of PostGIS has a bug when calculating  a buffer() on very complex segments, which causes the query to take more and more memory until the entire machine gets stuck.

I'm looking for a PostgreSQL mechanism that can:

Limit the memory consumption (and perhaps other resources) used by a specific query.
Automatically stop queries whose execution time exceeds a certain threshold.

Any ideas?


texts:
Is AutoClose ON a bad option for a environment with over 15 thousand databases with the same structure?
I know that we should leave this OFF as best practices in 99.99% of the cases. So is it the one edge case that the feature is meant for?
What we should be aware of and also improve in a instance like this?


texts:
we're designing the database for a school. Just a couple of questions:

do we create one (1) table for users or a table for each user type (parents, teachers, students, admins)? I'm thinking both should work, just looking for a second opinion.
is a course (like math, calculus, history) linked to a room (a location)? or is a class (a student can be in grade 1, class 1) linked to a room?

so to rephrase the 2nd question:
- is math linked to a room, and gym to another room?
- or do we link all students from grade 1, class 1 to a room?

do we assign homework to a class or to a student? I'm leaning towards student, seems to make sense, but not everyone agrees with me. And if link to a class why?

Thank you.


texts: SQL Server CPU usage is overloaded sometimes

texts:
We do have a couple of servers, one is dedicated to the website without SQL and the other server is dedicated to SQL.

Now the server running SQL is quite powerful, yet sometimes the server CPU is just maxed out at 100%.

Here is a couple of screenshots showing whats going on.

CPU maxed out:

CPU running ok:

As you can see the server is quite powerful.

Additional notes.

We are running nopcommerce version 3.70
The website has been heavily customized, by other developers.
The website has about 4000 - 5000 products.
When the cpu is maxed out load times are shocking. > 30 seconds and sometimes more than 1 min.

Is anyone able to shed some light on what might be going on, or guide me through some things to check.

Cheers

UPDATE:
The following two screenshots are the results from the two queries @S4V1N suggested I run.


texts: How to force feed offset keyword a number calculated from table

texts:
I am trying something as shown here.

As in this sql fiddle http://sqlfiddle.com/#!9/8d217/4 I created table

CREATE TABLE something (`some_column` int);
INSERT INTO something (`some_column`) VALUES (10),(20),(30),(40),(50);

I am trying to find median value so following is the query

select some_column from something order by some_column limit 1 offset
(select floor(count(some_column) / 2) from something);

MySQL complains about the query part after offset; and it does accept hard coded values like any integer instead of sub-query like (select floor(count(some_column) / 2) from something).

I am guessing the offset expects to get hard coded value and not a column with a value. I did try other options like using variables @ but cannot make it work.

My question is; if I want to use a value from query operation in offset or limit and not a hard coded value, how should I go about it?


texts: Pentaho Kettle - Unable to connect to virtual Oracle Database

texts:
I'm attempting to connect to a virtualized Oracle database but I am receiving the following error:

Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
Listener refused the connection with the following error:

  ORA-12505, TNS:listener does not currently know of SID given in
  connect descriptor The Connection descriptor used by the client was:
  foohost:1521:foodba

Since SIDs are not usable with virtualized databases I need to provide a fully qualified URL (from what I have heard), however I'm not certain how to configure Pentaho to accomplish this.


texts: Azure Data Factory pipeline accessing MariaDB

texts:
I have MariaDB running in a virtual machine in Azure. I need to move data from a file (in Azure storage) to a table in MariaDB using Azure Data Factory (ADF).
There's no connector in ADF to write to MariaDB, it is only supported as input data, so I'm evaluating the use of ODBC. Anyone has experience writing in MariaDB with ODBC? Or any other suggestions such as writing the data using the REST API? The requirement is to use ADF.


texts: What types of corruption can DBCC CheckDB miss?

texts:
This question was prompted by this earlier post and my having a database filed away for future investigation that was restored following:

BACKUP 'BrokenDatabase' detected an error on page (1:123456) in file ’BrokenDatabase.mdf'.
Error: 3043, Severity: 16, State: 1.

In the linked question and the backup I have ready for DBCC PAGE investigations, DBCC CHECKDB passed without error but corruption is evidently present.

What types of corruption can occur whereby CHECKDB will pass but a BACKUP WITH CHECKSUM will fail?


texts:
What would be the most efficient way, within Postgres, to truncate a text field to a maximum number of bytes, in such a way that the encoding is preserved?
I.e. how to keep at most N bytes while keeping a valid UTF8 string representation.

E.g. assuming UTF8, if a field contains abc€, that's 4 characters for 5 bytes, if I want to truncate this field to at most 4 bytes, but keep a valid UTF8 string, I'd need to actually keep only the first 3 bytes.

I feel like I need the following, but instead of 'escape', I'd like to ignore the invalid bytes to only keep abc:

select encode(substring('abc€'::bytea, 0, 5), 'escape');
 encode
---------
 abc\342

What's the best way to achieve this?


texts:
I have a project where I have Products and SKU's. Each Product has one or many SKU's.

I also have Shops that can have many to many Products, So a Product can belong to multiple Shops and a Shop can have multiple Products.

To finish, each Shop can choose which SKU's they want for which Products.
So I as a Shop can choose to sell the Tshirt White, which may have (XS,S,M,L) and I can choose (XS,S) as SKU's I want for my Shop.

Currently

As you can see A Shop is connected to the Products, and to the SKU's. But I have a feeling there's something wrong

  My shop_skus and shop_products are not connected, So there can be a mismatch between the products a shop has, and the sku's..

How would I go about fixing that?

Thank you for your time!


texts:
I have a homework question that I am trying to understand but having a tough time with this question. Any explanation or even pointers would be very appreciated (no exact answer expected - I am trying to learn - but any help would be great).

I have two relations with a set of explicitly defined constraints.

D = {R1:CDE, R2:FGH}  Constraints = {R1: CD->E, R2:key(G), R1[DE] subset R2[FG]}

Are the two relations (D={R1 and R2}) in BCNF?

Thank you for any guidance you can provide.


texts: Directly import a csv gzip'ed file into SQLite 3

texts:
I'd like to import a 15GB file comma delimited gzip compressed file into Sqlite 3 without having to use temporary files.

I'd like to run a command like:

zcat input/surgical_code.csv.gz | tail -n +2 | sqlite3 db.sqlite ".import /dev/stdin surgical_code"

This decompresses the file, skips the header and tries to import.

The problem is that I am not able to specify .mode cvs and .separator "," on the same quoted command to SQlite3.

Any ideas?

Using $(echo -e 'line1\nline2') didn't work for me:

gzcat input/surgical_code.csv.gz | tail -n +2 | sqlite3 db.sqlite $(echo -e '.mode csv \n .separator \",\"\n.import /dev/stdin')

  Error: mode should be one of: ascii column csv html insert line list tabs tcl


texts: how can I convert multiple rows into one row


texts: Query: How to join on different tables depending on the filled column?

texts:
I'm pretty new to the field of SQL, but already solved a lot of problems, so until now i am familiar with the different possibilites of JOIN(s) (explicit and implicit syntax), group by, distinct and so on.

But the query i am working on right now is crushing my head and i'm not sure how to approach that one:

The data i got is about roads and junctions, one road segment is defined from one junction up until the next one.

Segment Table:

| ID | start_junc | end_junc | ... |
| 1  | 1518       | 3645     | ... |
| 2  | 3645       | 769      | ... |

But there are different types of junctions, so the start_junc and end_junc FK are for a table looking like this:

| ID   | type        | junc_type_A | junc_type_B | junc_type_C |
| 769  | junc_type_C | <NULL>      | <NULL>      | 63          |
| 1518 | junc_type_B | <NULL>      | 17          | <NULL>      |
| 3645 | junc_type_A | 42          | <NULL>      | <NULL>      |
| ...  | junc_type_B | <NULL>      | 16          | <NULL>      |
| ...  | junc_type_B | <NULL>      | 13          | <NULL>      |

So for every ID there is exactly one junction type, all other junction types are NULL value and additional the the type of it is written in the column 'type'. Finally there is a table for each junction type with the ID and attributes for the junction like name,...

junc_type_A
| ID | name | ... |
| 42 | abc  | ... |
| 43 | def  | ... |
| .. | ...  | ... |

junc_type_B
| ID | name | ... |
| 13 | jkl  | ... |
| 16 | mno  | ... |
| 17 | pqr  | ... |

and of course for junc_type_C which i will omit.

What is needed is a query that additional outputs the start and end junctions name to the segments table:

| ID | start_junc | end_junc | ... | start_junc_name | end_junc_name |
| 1  | 1518       | 3645     | ... | pqr             | abc           |

So, doing it manually it seems like an easy piece... from the start_junc ID join the next table on that ID, look which column is not null, join that table with the name of the column... But i have no idea how to approach this in SQL. Hope it is possible without writing a procedure.

And one additional question: How can this kind of relation (for the junction type table where there is a column for every type of junction table and all other values are NULL) be called, so that i have an idea how to search for that in the future?


texts: WHERE clause seems to be messing up FIELD function

texts:
I'm trying to do a simple query but just can't get it to work and can't find an answer online that's working correctly.  All I want to do is query a table with a where clause, then order those results based on some value.  Here is the query:
SELECT * FROM reservation_form_results
    WHERE campaign_id = $campaignId
    ORDER BY FIELD(status, '$sortValue')

Here is what the data looks like (id is an int, campaign_id is an int, and status is an enum where the values default to undecided, but include good_fit, bad_fit, added):
id | campaign_id | status
----------------------------
 1 |          80 | good_fit
 2 |          80 | good_fit
 3 |          81 | bad_fit
 4 |          85 | added
 5 |          80 | added
 6 |          80 | bad_fit
 7 |          85 | undecided

Any thoughts on what could be going wrong?  It currently works if I remove either the where clause or the Field function.  Thanks


texts: Should we always prefer clustering over a master-slave configuration if the goal is high-availability?

texts:
Given: I have two virtual machines on a SAN that I can use to host RavenDB.

Question: If my goal is high-availability, then should I always prefer clustering over a master-slave configuration?

From my vantage point, master-slave seems like more of a maintenance headache. Clustering seems much easier. [But perhaps I'm missing something.]


texts: How can I append new data from a particular mySQL table to an excel spreadsheet?

texts:
We have a database trigger that automatically adds new clients to a table when they first contact the server, it takes a number of unique identifiers that the client provides on first contact and stores them, things like processor number, mac address, firmware version, etc...

We want this data exported to an excel file. The current plan is that an excel file will be created and new clients added as they appear on the system, then once a specific number of clients have been added (we'll usually do it in batches) we'll take a copy of that file and delete the one on the server until the next time we want to add new clients.

I'd be grateful if any of you could point me towards some resources to show me  how to do this with mySQL / Mariadb.
I've found many solutions for importing excel files into the database, and a few about exporting to excel automatically from Microsoft SQL Server, but nothing about doing the same in mySQL, although I'll admit I haven't been looking very long. Anyway, any pointers you may have would be greatly appreciated. Also, just thinking, comma delimited text files may be good enough too.


texts: InnoDB: Error: Table “mysql”.“innodb_table_stats” not found

texts:
I've got the same error, although I'm running galera cluster with 3 nodes.
I tried following the instructions from InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6 but when I get to the step of running the create statements, I get the following error:

Error Code: 1813. Tablespace for table '`mysql`.`innodb_index_stats`' exists.
Please DISCARD the tablespace before IMPORT.

This made no sense to me, and when I tried to use

drop tablespace `innodb_index_stats`;

I got an SQL sysntax error.


texts: SQL Cluster Resources Disappear After Removing Disk Resource

texts:
In the process of removing an usused disk resource from cluster, I would follow these steps: 1) remove dependencies on the disks by the SQL Server resource, 2) take the disk resource offline, 3) delete the disk resource from the SQL resource group, 4) delete the disk resource from the Available storage pool.

I was proceeding to do this on a two node cluster with two SQL 2008 R2 named instances (running Win2K8R2 SP1 build 6701, 64-bit). On deleting a resource in step 3 above, SQL would go offline, all the disk resources would go back to the Available pool, and the SQL resources (Net Name, SQL Server, SQL Server Agent) would disappear. SQL services do restart OK after via the services console as all the disks are still on the same node.

I ran cluster res from the command prompt and I see the SQL service resources are still there, in Available Storage, and offline.

To bring the resources back to the appropriate group, can I move them via cluster commands?


texts: Misleading ERROR: relation [“schema_name.table_name”] does not exist

texts:
Should not it be ERROR: relation [“schema name”].["table_name"] does not exist instead? Lots of people use case sensitive relation names with dots and so forth. Eg:

t=# select * from public.t1;
ERROR:  relation "public.t1" does not exist
LINE 1: select * from public.t1;
                      ^
t=# create table "public.t1" (i int);
CREATE TABLE
t=# select * from public.t1;
ERROR:  relation "public.t1" does not exist
LINE 1: select * from public.t1;

I am used to getting this error so much that I did not notice that it is misleading. And yet today I hit it with relation.name and realized that error should not use double quotes like this. Is it intentional or should I address it as a bug?


texts: Preventing database access after application license key has expired

texts:
I'm fairly new to this so forgive me if this is a basic question.

I have an application that uses a MS SQL database as the back-end. Like most
applications, a user is prevented from using this application if the license key has expired. I would like to know if there is a way to also prevent users from accessing the database (via SSMS) when the key has expired?

Thank you in advance for your assistance!


texts: Can't create Windows Login's on a case sensitive SQL Server 2005 Instance - MSG 15401

texts:
I need to create 3 logins on a SQL Server 2005 instance:

DOMAIN\aaa
DOMAIN\bbb
DOMAIN\ccc

However two of them (aaa and bbb) fail with the following error:

  Msg 15401, Level 16, State 1, Line 5 Windows NT user or group
  'DOMAIN\aaa' not found. Check the name again.

When I try to add them with DOMAIN\AAA and DOMAIN\BBB it works.

What is causing this strange case sensitive behaviour? And how can I add all accounts
lower case?

I found a KB article from Microsoft for the 15401 error, but nothing in there explains this behaviour.


texts: How can user determine which hash their password uses?

texts:
Is there a query that a MySQL user that does not have admin rights can run to determine if they are using the old (pre-4.1) hash or the new (4.1-and-later) hash? This user was explicitly granted access only to their own database and not to any of the system tables (such as mysql.user).

This is on a MySQL 5.5 system.


texts: What's difference between standard view and indexed view in SQL Server

texts:
I learn that the indexed view holds the data from base tables while standard view does not holds data.Is it means that the indexed view need memory and standard view don't?

Indexed view needs to be bind with schema object.I am also confused about the point that '*' is not allow  in the schema-bind object.For instance:

create view viewname as select * from tablename

The statement above can not work. why?


texts: Correlated subqueries. Count Visits after Last Purchase Date

texts:
I'm pretty new to SQL and have been trying to solve this task for a while...still no luck. I would appreciate if someone here could help me out.

I have a database with columns:

ClientID
VisitID
Date
PurchaseID (array)
etc.

What I'm trying to achieve is to retrieve a list containing the following data:

ClientID
Last Visit Date
First Visit Date
Last Purchase Date
Visits Count
Purchases Count
Visits After Last Purchase Count

When trying to retrieve a value for Visits After Last Purchase Count this is where I am stuck.

SELECT
ClientID,
FirstVisit,
LastVisit,
LastPurchaseDate,
Visits,
Purchases,
VisitsAfterPurchase
FROM
(
SELECT
    h.ClientID,
    max(h.Date) AS LastVisit,
    min(h.Date) AS FirstVisit,
    count(VisitID) AS Visits
FROM s7_visits AS h
WHERE Date > '2017-12-01'
GROUP BY h.ClientID
LIMIT 100
)
ANY LEFT JOIN
(
SELECT
    d.ClientID,
    max(d.Date) AS LastPurchaseDate,
    sum(length(d.PurchaseID)) AS Purchases,
    sum(
    (
        SELECT count(x.VisitID)
        FROM s7_visits AS x
        WHERE x.ClientID = d.ClientID
        HAVING x.Date >= max(d.Date)
    )) AS VisitsAfterPurchase
FROM s7_visits AS d
WHERE (length(PurchaseID) > 0) AND (Date > '2017-12-01')
GROUP BY d.ClientID
) USING (ClientID)

The database system I'm using is Yandex ClickHouse.
The USING syntax is absolutely normal for ClickHouse. It is used instead of ON clause in other RDBMSs.

This query is giving me the following error:

DB::Exception: Column Date is not under aggregate function and not in GROUP BY..

Sample Data:

  +----------+---------+------------+------------+
  | CliendID | VisitID |    Date    | PurchaseID |
  +----------+---------+------------+------------+
  |      123 |     136 | 01.12.2017 |            |
  |      123 |     522 | 05.12.2017 |            |
  |      123 |     883 | 08.12.2017 |            |
  |      123 |     293 | 09.12.2017 | ['345']    |
  |      123 |     278 | 12.12.2017 |            |
  |      123 |     508 | 12.12.2017 |            |
  |      123 |     562 | 15.12.2017 |            |
  |      123 |     523 | 21.12.2017 |            |
  |      456 |     736 | 29.11.2017 |            |
  |      456 |     417 | 03.12.2017 |            |
  |      456 |     950 | 04.12.2017 |            |
  |      456 |     532 | 05.12.2017 | ['346']    |
  |      456 |     880 | 09.12.2017 |            |
  |      456 |     296 | 12.12.2017 |            |
  |      456 |     614 | 15.12.2017 |            |
  +----------+---------+------------+------------+

And the result should be:

  +----------+-----------------+------------------+--------------------+--------------+-----------------+----------------------------------+
  | ClientID | Last Visit Date | First Visit Date | Last Purchase Date | Visits Count | Purchases Count | Visits After Last Purchase Count |
  +----------+-----------------+------------------+--------------------+--------------+-----------------+----------------------------------+
  |      123 |      21.12.2017 |       01.12.2017 |         09.12.2017 |            8 |               1 |                                4 |
  |      456 |      15.12.2017 |       29.11.2017 |         05.12.2017 |            7 |               1 |                                3 |
  +----------+-----------------+------------------+--------------------+--------------+-----------------+----------------------------------+


texts: How do you identify database .mdf and .ldf file names?

texts:
In the DATA folder for SQL Server (Windows Server 2008):

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA

There are two files:

xxx.ldf (63.7GB)
xxx.mdf (5.38GB)

(Incidentally why is the size in the info bar at the bottom of the windows explorer screen vastly different to the 'size' column information? The .ldf file size shows a 4GB difference).

There is no corresponding database by that name (xxx).Is there a query on system tables that I can run to show an output of:

DB name | mdf name  | log name
yardi   | xxx.mdf   | xxx.ldf
test    | test.mdf  | test.ldf
prpdb   | prpdb.mdf | prpdb.ldf
etc

I suspect that the yardi DB is related to the xxx .mdf / .ldf files, this particular database we get a .mdf dump that we restore from every morning.

==== EDIT IN RESPONSE TO ANSWERS ====

Two view are mentioned in the answers below (that I only ran in context of the master DB)

sys.databases
sys.master_files

These views are relatable by the field database_id, and surprisingly I had to use both views because the name columns in each were different.

sys.databases.name corresponded to the names I see in SSMS
sys.master_files.name did not correspond to the names I was seeing on the databases

The query that solved my problem is this:

USE master;
GO

SELECT
   ds.[name] as [Database name (as in SSMS)],
   mf.[name] as [master_files name],
   mf.physical_Name as [File name],
   mf.SIZE * 8 / 1024 AS SizeMB
FROM   sys.master_files mf
LEFT OUTER JOIN sys.databases ds ON mf.database_id = ds.database_id
ORDER BY ds.name ASC, SizeMB DESC

==== AND ====

The SizeMB returns a completely different size of the log file compared to what is seen in windows explorer 'size' column, and is also different to the info bar in windows explorer. By several GB. Why?


texts: Why am I getting ERROR: column "GLOBE-BE-154" does not exist

texts:
I create a Table in a Database and looks as following
test1=# SELECT * FROM stunden_Plan;
 stunde | montag | dienstag | mittwoch | donnerstag | freitag
--------+--------+----------+----------+------------+---------
      1 |        |          |          |            |
(1 row)

From this distinction, I wanted to insert some values into the table below...
INSERT INTO stunden_Plan (stunde, montag, dienstag, mittwoch, donnerstag, freitag)
VALUES (2, "GLOBE-BE-154", "CH-SCH-156", "D-KR-477", "M-HA-477", "E-ZE-477" );

But I got an error of which I have no understanding!
ERROR:  column "GLOBE-BE-154" does not exist

I am grateful for any help. Thanks!


texts: Changing Database Transaction isolation level to SNAPSHOT for SQL Server replication "distribution" database

texts:
I have a couple transactional replication publications that make large changes (nearly 1 billion records) to a publication article.  These transactions are captured by the log reader and stored in the distribution database for up to a day before we run the distribution agents to the two subscribers.

In conjunction with these large transactional replication publications, we also have several smaller publications that run daily and sometimes multiple times per day.  What we are seeing is that while the log reader is reading the large transactions and storing them in the distribution database, the smaller, daily publications are being blocked until the log reader completes the write to the distribution database.  Note: these publications use the same distribution database on a single distributor server.

For both scenarios above, the distribution agents are not scheduled but are run on-demand: an SSIS package calls several CMD distrib.exe while passing the parameters for each publication/subscription combination.  There has not been an issue executing these agents.

Since these are two entirely different sets of data, although publishing from the same database, I am considering changing the transaction isolation level of the database to SNAPSHOT to avoid locks and allow concurrent processing of the transactions in the distribution database.

Does anybody have experience doing this?  I've used SNAPSHOT isolation level for another database where we were having deadlocks while accessing the same table but different records and it worked out fine.

If changing the isolation level isn't the way to go, how can I reduce the impact of this large log reader write to the distribution database?


texts: Jetbrains 0xDBE : How can I load a SQL Server .bak file?

texts: I'm trying this new Jetbrains IDE and I can't figure out how to load a SQL Server 2008 R2 .bak file. Maybe I'm looking in the wrong menus. Is it possible?


texts: Can't set default_schema_name for Windows User Group?

texts:
When I try to set the default schema for my Windows Role/Group for Windows Server 2008 R2, it won't allow me to do so...I run:

ALTER USER [UCPSAR\Service Provision] WITH DEFAULT_SCHEMA=[ServiceProvision_Schema]

And I get the error message:

Msg 15259, Level 16, State 2, Line 1
The DEFAULT_SCHEMA clause cannot be used with a Windows group or with principals mapped to certificates or asymmetric keys.

If I do a select to find out why

SELECT name, type_desc, default_schema_name
FROM sys.database_principals
WHERE type in ('S', 'U', 'G');

I end up with default_schema_name for my WINDOWS_GROUP being NULL

Is there any way to remedy this?


texts: Is it possible to use part of a composite primary key as a foreign key to another table?

texts:
In my attempt to model a one to many relationship, I am trying to achieve the following in SQL (MariaDB) given my basic DB knowledge:

Profiles Table:
Primary Key: profileName <-----
                              |
Repositories Table:           |
Composite Primary Keys: (profileName, repository_name)

Is this a possibility?


texts: Database Mail works in Production but not in Test Database

texts:
I have a Production database that is able to send mail. And a Test that is not able to. They are both on the same SQL Server. The error is (SQL Server 2008):

  Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1
  EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

I am an intern and have no previous experience with SQL server (just SQL DML, DDL). I tried to debug this by looking at the permissions of the 2 databases but they are the same. So I don't even know how people can send mail from the production.

I looked online and in order for users to be able to send mail they have to be a user under msdb and have the DatabaseMailUserRole. In my case it's a Windows domain. What's funny is that this domain isn't there, so how is production sending mail? I also tried putting the domain there as a user and gave them the permission, but still no luck for the test database.

How do I determine why/how the production database is able to send mail, so that I can debug? Thank you, I would appreciate any help.

Let's say it's too complicated to debug and I want to copy the production database to the test database (as an exact duplicate), will all permissions carry over?


texts: Why does 'plpgsql' not appear in the pg_extension table?


texts: Slow query speed using workstation processed joins with 100 Mbps connection speed?

texts:
In a department where they were complaining that a report was being run slowly, I jumped on one of the empty machines there and tried it out.  It took a good 6 and 15 seconds to run.

Run from a client on the VM on the Hypervisor that contains the files, it runs almost instantaneously.  And run from a client on a separate VM on the same Hypervisor, it takes about 19 seconds.  (Of course the VM on the Hypervisor is probably a good bit faster than the workstations in question).

I asked tech support for the app in question to confirm, that, in order for the report to be run, the files for every table (and it's index files) involved needed to be copied over to the local machine first by the client software (since there is not a server-side process for it); they confirmed it.

I also asked which files/tables were involved in the query, and I was told that about six tables were involved; do to the nature of the worst case query being one that spans before and after final quarter close (last years archived check lines are involved as well as this years).

The tables in question were the following:
employees, employerconfig, paychecklinehistory, paychecklinesthisyear, payrollCodes, paygroups

I summed up the total sizes of these six tables, and additionally the .cdx index files for each.

I also noted that all computers in HR are:
1. Attached to the network via their IP phones...which contain a switch which is 100 Mbps
2. The phone is then attached to a PoE device (to power the said IP phone) and that device, despite having a Gigbit ethernet port that connects it to the rest of the network (the network is Gigbit); is limited by 24 ports that are 100 Mbs.

So this lead me to believe that summing the file sizes and taking into account the speed of the file transfer would result in how long it might take to copy the files over to the client.

So to look at the full picture here the time involved would be the following:
1. The amount of time it takes to copy the tables and their indexes to the client.
2. The amount of time it takes to join/filter the tables (in whatever order that takes place)
3. The amount of time it takes to display / generate the report with the results of that query

For #1:
Using an equation and a website, I figured out that it should take about 3 minutes and 15 seconds to run given the size of the table and index files and the 100 Mbs lan speed.

For #2:
It will depend on the workstation specs which vary, so I can't include it.

For #3:
This too will depend on the workstation's specs which vary, so I can't include it.

Now my boss says he thinks it's something that can be repaired with the database, since there's a Gigabit Ethernet connection, but from what I can tell this isn't the case, and short of purging the tables, or hooking the HR workstations directly into the Gigabit Ethernet switch instead of their phones, I don't think anything else with the database can be done to speed this up unless we give them access to a VM on the same machine as the "server".

Am I correct in thinking that this is an issue with the dual bottlenecks in the network, and not the database itself?


texts: different settings for max_heap_table_size and tmp_table_size (MariaDB 10.1.x)

texts:
I run pt-variable-advisor and got a note about different settings for max_heap_table_size and tmp_table_size.

With a web search I found only old articles (around 2007).

pt-variable-advisor h=localhost,u=root,p=Quule0juqu7aifohvo2Ahratit --socket /var/vcap/sys/run/mysql/mysqld.sock
(...)
# NOTE tmp_table_size: The effective minimum size of in-memory implicit temporary tables used internally during query execution is min(tmp_table_size, max_heap_table_size), so max_heap_table_size should be at least as large as tmp_table_size.
(...)

Our configuration

max_heap_table_size = 16777216
tmp_table_size = 33554432

We didn't modify the defaults from cf-mysql-release. I saw that MariaDB KB recommends other default values.

  cf_mysql.mysql.tmp_table_size:
    description: 'The maximum size (in bytes) of internal in-memory temporary tables'
    default: 33554432

  cf_mysql.mysql.max_heap_table_size:
    description: 'The maximum size (in rows) to which user-created MEMORY tables are permitted to grow'
    default: 16777216

I found also Optimize MySQL tmp_table_size and checked our values and the configuration:

MariaDB [(none)]> show global status like 'created_tmp_disk_tables';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Created_tmp_disk_tables | 12727901 |
+-------------------------+----------+
1 row in set (0.01 sec)

MariaDB [(none)]> show global status like 'created_tmp_tables';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| Created_tmp_tables | 115714303 |
+--------------------+-----------+
1 row in set (0.01 sec)

MariaDB [(none)]> select (12727901*100)/(115714303 + 12727901) as "Created disk tmp tables ratio" from dual;
+-------------------------------+
| Created disk tmp tables ratio |
+-------------------------------+
|                        9.9094 |
+-------------------------------+
1 row in set (0.00 sec)

Is something wrong with our (default) configuration? We don't know our workload. We run about 500 small databases for small web apps with different usage pattern.


texts:
Where can I find ready-to-use database models ?

I don't need a database with data in it, but only schemas (UML diagrams). Perhaps something like the data models at this link, but much more complex and real world.


texts: How to export some rows of multiple tables linked between them according to one column value?

texts:
I'm not sure my question is clear so let's me rephrase it. I have three tables organized like that:

I want to make a dump of these tables but I'm not interested by all the rows, I just want those who were last updated during the last month.
Is there a way with Data Pump Export to do it? I thought to the QUERY parameter but I can't use it because there is no last_updated_date in the SON and DAUGHTER tables.

For instance, if, in my MOTHER table, I have 3 rows:

id_mother = 1 ; last_updated_date = 01/01/17 ;
id_mother = 2 ; last_updated_date = 01/02/17 ;
id_mother = 3 ; last_updated_date = 01/03/17.

And, in my SON table, I have 2 rows:

id_son = 1 ; id_mother = 2 ;
id_son = 2 ; id_mother = 3.

If I want to export the rows of month of March, the process will return one row for the MOTHER table (id_mother = 3) and one row for the SON table (id_son = 2).

How can I handle it?


texts: SQL Server 2014 SP3 Unexpected Service Stop

texts:
SQL Server 2014 SP3 CU1 service terminates automatically with the error,

  Error: 17311, Severity: 16, State: 1.   SQL Server is terminating
  because of fatal exception c0000005. This error may be caused by an
  unhandled Win32 or C++ exception, or by an access violation
  encountered during exception handling. Check the SQL error log for any
  related stack dumps or messages. This exception forces SQL Server to
  shutdown. To recover from this error, restart the server (unless
  SQLAgent is configured to auto restart).

There are multiple instances on the server, but I am having this problem in one instance.

There's not much information in the logs to say why. Do you have any similar problems? What can I do in more detail to find the problem?


texts: SSIS Error :Cannot roll back .No transaction or savepoint of that name was found

texts:
Hope you are Doing well.
I have 1 SSIS Package as you can see below :

In the "Insert" part of the Package which is an Execute sql task,
I'm executing a Stored procedure . this is the code inside the task

(Exec [dbo].[Main] ?,?,?,?,?)

Which has a transnational format like this Begin Try  Begin Tran..... Commit Tran End Try / Begin Catch  Roll Back Tran .. End Catch . And Then I configured the whole package as one "Transaction" by setting the transaction option of my entire package to "Required" . After executing the package I run into this error (The error is for Execute sql task) : "Cannot roll back Main. No transaction or savepoint of that name was found."

Guys , I'm sure that this error is because I'm  calling a stored procedure which has a transnational format inside another transaction which is the entire package but are there any ways that I can handle this?
The stored procedure must have the transnational format because I'm handling error inside it.

Thanks in advance

Update :
After removing the name "Main" From my transaction I ran into this error

:Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction


texts: How do I correct this value too long for type character varying(5)?

texts:
I created a small development database for practice. It has a table cities with columns cityname and state. The cityname in there is 'Cincinnati', long name right?

mytestdb=# SELECT * FROM cities;
 cityid |  cityname  | state
--------+------------+-------
 12345  | Cincinnati | Ohio
(1 row)

I am unclear as to how and why I am getting this error message upon attempting to add 'San Francisco'.

mytestdb=# INSERT INTO cities VALUES ('San Francisco','CA');
ERROR:  value too long for type character varying(5)


texts: How to keep the structure of the Tablix and Field names when there is no data to show

texts:
When the data set returns no data, the tablix column headers display, but I also need the detail rows to display with a blank or zero for all fields.  Adding an additional row to the tablix is not an option, as the textbox names on the additional row must be different within SSRS.  I am using this SSRS report as a data source to a PowerPivot model.  I do not want the PowerPivot model to break when it refreshes and no data exists yet.

I would like to avoid adding an empty row to the data set.  I would prefer a solution that can be implemented with in the SSRS report.


texts:
I am using PostgreSQL and I am adding a single column to a view.  Unfortunately, I will have to drop and recreate that view as well as the 9 other views that are dependant on the view.

What strategies or tools are good to manage doing this type of database management?


texts: How to execute SQL against all DBs on a Server

texts:
I have some standard SQL that I run against multiple databases on a single server to help me diagnose problems:

select
    so.name,
    so.type,
    MAX(case when sc.text like '%remote%' then '' ELSE 'N' END) AS Relevant,
    @@ServerName as Server,
    DB_Name() as DBName
from
    sysobjects so with (nolock)
    join syscomments sc with (nolock) on so.id = sc.id
where (sc.text like '%emote%')
group by so.name, so.type
order by so.type, so.name

How can I execute this against all databases on a single server? (besides manually connecting to one at a time and executing)


texts: How can I manipulate database in stack n-up order?

texts:
I need to print a mail merge 3-up.  The database ( Micorsoft Access ) is in order of ID 1 to 1845.  Instead of printing sheet#1 (ID 1, 2, 3)  I'd like to print sheet#1 (ID 1, 616, 1231) and so on.  The purpose is to stack each pile after I cut it and then it's in order.  I don't want to end up with 3 piles in order.
How can I set up my database in ID order to print every 615th record starting from ID #1?

Database should look like:

**ID**      **Address**
1       123 Sample Street
616     987 Hello Road
1231    456 Print Avenue
2       999 Access Parkway
617     111 Boogie Court
1232    555 Database Sreet


texts: Oracle extract slow function call from a WHERE clause

texts:
I have an oracle table in which dates are stored as the number of minutes since a specific date. It looks something like this:

CREATE TABLE MY_TABLE
(
  SOMETHING     NUMBER(15,1) NOT NULL,
  START_MINUTE  NUMBER(15,1) NOT NULL,
  STOP_MINUTE   NUMBER(15,1) NOT NULL
);

I've written two functions to convert between what are known as "MINUTE"s and the actual date/times that they represent. They look like this:

CREATE OR REPLACE FUNCTION FROM_MINUTE (MINUTE_IN IN NUMBER)
RETURN DATE AS
BEGIN
  /* Minute 0 = 12/30/1899 12:00am */
  RETURN (TO_DATE('1899-12-30', 'YYYY-MM-DD') + (MINUTE_IN / 1440));
END FROM_MINUTE;

CREATE OR REPLACE FUNCTION TO_MINUTE (DATE_IN IN DATE)
RETURN NUMBER AS
BEGIN
  /* Minute 0 = 12/30/1899 12:00am */
  RETURN
    (TRUNC(DATE_IN, 'DD') - TO_DATE('12/30/1899', 'MM/DD/YYYY')) * 1440 +
    TO_NUMBER(TO_CHAR(DATE_IN, 'HH24'))  * 60 +
    TO_NUMBER(TO_CHAR(DATE_IN, 'MI'));
END TO_MINUTE;

Now I'm trying to write a query that returns all rows that overlap with a specific time frame. It's taking quite a long time to run, presumably because of the function calls. I've tried several different versions of it:

-- This compares dates by converting the minutes to dates first
SELECT * FROM MY_TABLE
WHERE FROM_MINUTE(START_MINUTE) < TO_DATE('2013-01-31', 'YYYY-MM-DD')
AND FROM_MINUTE(STOP_MINUTE) > TO_DATE('2013-01-01', 'YYYY-MM-DD');

-- This compares minutes by converting the dates to minutes first
SELECT * FROM MY_TABLE
WHERE START_MINUTE < TO_MINUTE(TO_DATE('2013-01-31', 'YYYY-MM-DD'))
AND STOP_MINUTE > TO_MINUTE(TO_DATE('2013-01-01', 'YYYY-MM-DD'));

-- Finally I just got the minute values in a separate query...
SELECT
  TO_MINUTE(TO_DATE('2013-01-01', 'YYYY-MM-DD')) AS EARLIEST, -- Returns 59436000
  TO_MINUTE(TO_DATE('2013-01-31', 'YYYY-MM-DD')) AS LATEST    -- Returns 59479200
FROM DUAL;
-- and I plugged them directly into the query
SELECT * FROM MY_TABLE
WHERE START_MINUTE < 59479200
AND STOP_MINUTE > 59436000;

I'm pretty sure the problem is that the comparisons in the first two queries require a function call on every row to do a proper comparison. That's why the final query is so much faster, since it's just a direct comparison.

I would prefer to have a single query that runs as fast as the two parter above. Is there a way to force a query to run the function call just once and use the returned value for every subsequent comparison without having to be called for each row? I tried something like the following, but it didn't seem to work like I'd hoped:

SELECT * FROM MY_TABLE,
  (SELECT
    TO_MINUTE(TO_DATE('2013-01-01', 'YYYY-MM-DD')) AS EARLIEST, -- Returns 59436000
    TO_MINUTE(TO_DATE('2013-01-31', 'YYYY-MM-DD')) AS LATEST    -- Returns 59479200
  FROM DUAL) MY_MINUTES
WHERE START_MINUTE < MY_MINUES.LATEST
AND STOP_MINUTE > MY_MINUTES.EARLIEST;


texts: I can't connect to my servers SQL database via an IP Address

texts:
I have setup a server that runs Windows Server 2008, and has SQL Server 2008 Express installed.

I can connect to the machine's SQL Server Express database via the MACHINENAME/SQLEXPRESS.

However when we come to connecting through any software or script using an IP Address it won't allow the connection.

I have tried:

Turning off the Firewall.
Allowing Remote Connections for the SQL Database.
Enabling TCP/IP within the SQL Configuration.

When we attempt to connect via the software 'SQL Server Management Studio', we get the following message:

Error Message:

  An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)

  A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.) (Microsoft SQL Server, Error: 10053)

Can you please let me know when your free so we can take a look because I seem to be getting know where, I’ve amended the details as per some information UK Fast sent me but they have said "It’s not within the support remit", so they can’t help any further.

I look forward to hearing from you.


texts: How to set minimal permissions to change SQL Azure Firewall rules

texts:
I have a desktop app that connects to SQL Azure database.
Some users have dynamic IP addresses, and it is not possible to get them to use fixed addresses.

I've written an ASP.NET page hosted on the organisation's web server that allows users to validate their IP Address.
The web server has a fixed IP address, and can read a user's IP.

I'm using EXECUTE sp_set_database_firewall_rule against a contained database from the ASP.NET page.

According to documentation this system stored procedure requires CONTROL permission on the database.

Ideally, I'd like the web page that allows a user to add a firewall rule for their IP address to allow adding a firewall rule, but nothing else.
There don't appear to be any built in roles for this.

Is it possible to set up sufficient permission to add a firewall rule, but restrict other activities?


texts: SQL - Is it possible to have a Trigger statement with a WHERE clause that is an equality measure

texts:
I'm trying to add a counter to one of my tables. So far I have these tables

Heroes {hero_id, hero_name, villain_id, team_id}
Villains {villain_id, villain_name, team_id}
Teams {team_id, team_name, no_of_members}

I want to create a trigger that updates the member count whenever a person is added to a team. So far i have something like this but it keeps giving me Trigger compilation errors. I have seen other instances where people have used WHERE clauses in triggers but they arent similar to my problem. If anyone could tell me why this isnt compiling or what I'm doing wrong I'd be very grateful.

CREATE TRIGGER team_no_inc
    BEFORE INSERT ON Heroes
    FOR EACH ROW
BEGIN
    UPDATE Teams SET no_of_members = no_of_members + 1
    FROM Heroes, Teams WHERE Teams.team_id = Heroes.team_id;
END;
.
RUN;


texts:
As I understand:

UNDO: Undoing a write item operation consists of examining its log entry [write_item, T, X, old_value, new_value] and setting the value of item X in the database to old_value. UNDO is always performed in the reverse order from the order in which the operations were written in the log.

REDO: Redoing a write item operation consists of examining its log entry [write_item, T, X, new_value] and setting the value of item X in the database to new_value.

In a transaction recovery algorithm, for example the one outlined in the Q & A Understanding Transaction Recovery

and let's say we have a schedule such as:

R1(X) R2(Y) W1(X) C W2(Y)

where C denotes a crash. According to the algorithm, we should UNDO T1, but REDO T2, since it is still active at the time of crash. But T2 hasn't had any Write operations, so what exactly does an UNDO entail here? Does anything need to actually happen? And where does the concept of a rollback come in? Is that just implicit in an UNDO?


texts:
I think I encountered a bug with sp_execute_external_script, so here is the replay.

I will get an error if I run the following script:

-- error occurs
exec sp_execute_external_script @language=N'R'
, @script = N' result <- as.data.frame(c(i, j));'
, @params = N'@i varchar(100), @j varchar(100)'
, @i = 'hello'
, @j = 'world'
, @output_data_1_name = N'result' -- the position of this param caused problem
with result sets ((test varchar(100)));

I will get an error

  Msg 8144, Level 16, State 2, Line 1
  Procedure or function  has too many arguments specified.

However, if I just move up the @output_data_1_name like the following:

-- NO error occurs
exec sp_execute_external_script @language=N'R'
, @script = N' result <- as.data.frame(c(i, j));'
, @output_data_1_name = N'result' -- this position in front of @parms caused NO problem
, @params = N'@i varchar(100), @j varchar(100)'
, @i = 'hello'
, @j = 'world'
with result sets ((test varchar(100)));

I will get a normal output:

Does anyone know whether I am doing something wrong or it indeed could be a bug in SQL Server 2016?

My environment is (by select @@version):

Microsoft SQL Server 2016 (SP1-CU1) (KB3208177) - 13.0.4411.0 (X64)
    Jan  6 2017 14:24:37
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 14393: )

Update:
I tested a little bit more and indeed the position of parameters will cause errors, such as following:

exec sp_execute_external_script
 @script = N' result <- as.data.frame(c(i, j));'
, @output_data_1_name = N'result' -- the position of this param caused NO problem
, @params = N'@i varchar(100), @j varchar(100)'
, @i = N'hello'
, @j = N'world'
, @language=N'R' -- if I put @language here, it will cause error
with result sets ((test varchar(100)));

or this:

exec sp_execute_external_script  @language=N'R'
, @script = N' result <- as.data.frame(c(i, j));'
, @output_data_1_name = N'result' -- the position of this param caused NO problem
, @i = N'hello'
, @j = N'world'
, @params = N'@i varchar(100), @j varchar(100)'
with result sets ((test varchar(100)));

The error message will be:

  Msg 214, Level 16, State 175, Procedure sp_execute_external_script, Line 1 [Batch Start Line 19]
  Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'.

But I actually give each parameter of type nvarchar already.

I will consider open a bug report to MS if I do not see anyone pointing out my "errors".


texts: Are Database duplication and Mirroring one and the same?

texts:
I am going over the concepts of Distributed database management system. I read about duplication and replication. Then there is also something called Mirroring. I googled to find if duplication is the same as mirroring but could not find. Are they same?

How is duplication different from snapshot replication?


texts: How to do a select on a table based on a index-by table?

texts:
I have a table called getSalas:

CREATE TABLE getSalas(
    ID_SALA NUMBER(4),
    NOME_CARATERISTICA VARCHAR2(20),
    VALOR_CARATERISTICA NUMBER(4)
);

The contents of that table are like this:

  1, 'Pool', 2

  1, 'A/C', 1

  2, 'A/C', 2
  ...

Which would mean that the room with the ID_SALA 1 has 2 pools and A/C. And the room with ID_SALA 2 has 2 A/C's.
I've also created the following datatypes:

CREATE OR REPLACE TYPE c_valor FORCE IS OBJECT (CARATERISTICA VARCHAR2(20), VALOR NUMBER(30));
/
CREATE OR REPLACE TYPE tabc_valor FORCE IS TABLE OF c_valor;
/

So my problem is, I need to know if a given set of characteristics exists in a room (e.g Has at least 2 pools and 3 unicorns etc...). For that I've created the following procedure:

CREATE OR REPLACE PROCEDURE getSalas_Carateristicas
(v_carateristicas IN tabc_valor, lista OUT SYS_REFCURSOR)
IS BEGIN
    OPEN lista FOR SELECT getSalas.ID_SALA
    FROM getSalas, TABLE(v_carateristicas) v_carateristicas
    WHERE getSalas.NOME_CARATERISTICA = v_carateristicas.CARATERISTICA
    AND getSalas.VALOR >= v_carateristicas.VALOR
    GROUP BY getSalas.ID_SALA
    ORDER BY getSalas.ID_SALA;
END;
/

v_carateristicas is where I receive the set of characteristics, I would look something like this:

  'A/C', 1

  'Extinguisher', 2
   ...

The problem is that the procedure is returning rooms that have match one or more of the set of characteristics, not all! For example, if I make v_carateristicas equal to:

  'Pool', 2

  'A/C', 2

And if getSalas looks like this:

  1, 'Pool', 2

  1, 'A/C', 1

  2, 'A/C', 2

It will return both rooms, although only room with the ID_SALA 1 matches what is asked.


texts: How do I form an SQL query to show entire column of dates with modified values?

texts:
Assume that I have a table Purchases with column named PurchaseDate and with data format of 'YYYY-MM-DD HH:MI:SS'.
Suppose that I want to see the entire column of dates with the days increasing by 5.

Suppose I form the query in the following way:

SELECT PurchaseDate+5 FROM Purchases;

The result will be given in a form of single numbers, and the increased part will be seconds instead of days.
I.e.: This 1982-09-07 00:00 will turn into this 198209070005.

How do I form the right standard SQL query?

UPDATE: I've got answer. Thanks.

-- this will work for standard SQL:

SELECT PurchaseDate, PurchaseDate + INTERVAL 5 DAY FROM Purchases;

-- this will work for MySQL:

SELECT PurchaseDate, ADDDATE(PurchaseDate, INTERVAL 10 DAY) FROM Purchases;


texts: Accessing mysql database without logging in (from android java application) (using php server side)

texts: Just like all e-commerce applications (like amazon) let you access the database and browse items without logging in or signing up, how can i do this with mysql database (without logging in with username and password)


texts: SQL Server Migrate Database - Set Compatibility Level

texts:
We are planning on migrating from SQL Server 2008R2 to SQL Server 2014 (or 2016).

One 3rd party application is not supported after version 2008R2.

Is it OK to migrate this application database to 2014, and then change the Compatibility Level of that database to 100?

Is changing the compatibility level to 100 in effect the same as having it on SQL Server 2008R2? Or are there some potential issues?


texts: SQL Server best option for low-cost high-availability and queryable secondary?

texts:
We have a heavily used database server (SQL 2012 Web edition) which is currently a single point of failure (nightly backups and no additional hardware provisioned!)

The server has a constant stream of data being added and periodic heavy-duty read operations to extract data for applications.

We're going to upgrade to SQL 2017 and then:

1) Split out the reads onto one server and the writes onto another

2) Have a better Disaster recovery situation - i.e. not have to wait a day to provision new hardware and restore backups. It's ok to lose a few minutes of data if we have to.

I've spent a while reading up on options and it seems the Always On High Availability groups are the best option.... however they require the Enterprise edition of SQL server (which for two 8 core servers is about 20x more money than the "Server-5 CAL" option we need and just not justifiable!) - I'm aware there are also Basic Availability Groups with Standard edition, but they don't support querying the secondaries, which is no use to us.

Log shipping is not a bad option, except the fact that it either disconnects users or queues restores is a problem - we'll quite likely have users connected and they won't want to have their long-running queries terminated!

Transactional replication seems to be a bit too granular - table level, requiring tables to have primary keys etc. it feels like it's going to be a maintenance problem and not the appropriate solution

So, I'm left with Database mirroring - which Microsoft have deprecated! (although it's still there in SQL 2017, probably because there isn't a good, cheap alternative!)

I'd appreciate any thoughts/suggestions?


texts: How do I keep developer's MySQL DB up to date with production?

texts:
I have a slave copy of my production database that gets updated regularly. I cannot write to it, so I also have a developer copy of the DB. I would like to keep my developer copy up to date daily by running a stored procedure every morning to update yesterday's information.

Here's what I have so far:

CREATE DEFINER=`myName`@`%` PROCEDURE `spSyncMyDB`()
BEGIN

    DECLARE tableName VARCHAR(255);
    DECLARE primaryKey VARCHAR(255);
    DECLARE columnName VARCHAR(255);
    DECLARE dataType VARCHAR(50);
    DECLARE columnKey VARCHAR(255);
    DECLARE whereStatement VARCHAR(5000);
    DECLARE columnList VARCHAR(255);
    DECLARE currentSchema VARCHAR(255);

    DECLARE TableNameCursor CURSOR FOR
    SELECT DISTINCT TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE DATA_TYPE IN ('timestamp', 'datetime')
        AND TABLE_SCHEMA = currentSchema
    ORDER BY TABLE_NAME;

    DECLARE ColumnNameCursor CURSOR FOR
    SELECT DISTINCT COLUMN_NAME, DATA_TYPE, COLUMN_KEY
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = currentSchema
        AND TABLE_NAME = tableName
    ORDER BY COLUMN_NAME;

    SET currentSchema = DATABASE();

    OPEN TableNameCursor;

        FETCH TableNameCursor INTO tableName;
        SET whereStatement = '';
        SET primaryKey = '';
        SET columnList = '';

        OPEN ColumnNameCursor;
            FETCH ColumnNameCursor INTO columnName,dataType,columnKey;
            IF DATA_TYPE IN ('timestamp', 'datetime') THEN
                SET whereStatement = CONCAT(columnName, '>= DATE(DATE_SUB(NOW(), INTERVAL 1 DAY)) OR ', whereStatement);
            END IF;
            IF COLUMN_KEY = 'PRI' THEN
                SET primaryKey = columnName;
            END IF;
            SET columnList = CONCAT(columnName,',',columnList);
        CLOSE ColumnNameCursor;

        SET columnList = TRIM(TRAILING ',' FROM columnList);

        IF LENGTH(whereStatement) > 0 THEN
            DELETE currentSchema.tableName FROM currentSchema.tableName
                LEFT JOIN currentSchema.tableName USING(columnList)
            WHERE CONCAT(whereStatement, currentSchema.tableName.primaryKey,' IS NULL') ;
        END IF;
        INSERT IGNORE INTO currentSchema.tableName
            SELECT *
            FROM productionSchema.tableName
                LEFT JOIN currentSchema.tableName USING(columnList)
            WHERE CONCAT(whereStatement, currentSchema.tableName.primaryKey,' IS NULL') ;

    CLOSE TableNameCursor;

END

What do I need to do to get this working?


texts:
My question is for designing something in MSSQL server.

I work at an engineering company that designs embedded systems. Basically, we have 3 different hardware components in a product: one is for communication on a network (GPIB, Ethernet, Modbus...), one is for measurement/control functions, and the third is for miscellaneous stuff (like diagnostics and logging). Each piece of hardware has multiple firmware versions available. Each firmware has a set of registers used for configuration and a set of defaults.

I want to create a database to track the firmware, hardware, default configs, and register maps. I can create three separate tables for firmware, like commHardware, controlHardware, miscHardware, and then create tables that relate to these (i.e., commFirmware with a foreign key to commHardware and commDefaults with a FK to commFirmware).

Or... I can structure my tables so that there's one table for hardware, one for firmware, one for register maps, one for default configs, and one for hardware role (comm/misc/control).

The representation will be the same no matter which option I choose, so I don't have to shoehorn one design to fit the other. Right now, I'm leaning toward option 1, for two reasons.

If I confuse something, it might not work - if I try to download comm firmware 01 onto a control board, it'll be blatantly screwed up. I don't think it is physically possible to do so - the processors are different.
If I confuse something, it might actually work... for a while. Downloading the miscellaneous registers onto a communications board can be done, but it'll  screw up communications ("Oh, you wanted to suppress overflow alarms? Too bad, here's a new MAC Address.")

In writing this, I've almost convinced myself, but I want to get a second opinion. Is the first approach superior in a way I'm missing? I'm looking for problems that could cause:

Obvious performance issues
Data integrity problems
Maintenance nightmares
Software development headaches
Angry future coworkers visiting my house with hatchets, torches, and/or pitchforks in hand.


texts: I have started a Cassandra server using Bitnami AMI, I have attached volumes to the EC2 instance, but how would I set Cassandra to use the attached volume?


texts: Why is the schema/structure dump so slow?

texts:
I'm using Rails 6 with activerecord-oracle_enhanced-adapter adapter for Oracle 11 XE database.
At the beginning of the project I was working with Postgres, but due to customer requirements I had to migrate to Oracle.
My question is very similar to https://stackoverflow.com/questions/592444/rails-rake-dbmigrate-very-slow-on-oracle, but it's from 11 years ago, i'm using the updated version of the adapter.
I know that after all migrations are applied to database then rake db:migrate calls db:schema:dump task to generate schema.rb file from current database schema. I only have one schema, with many tables (around 90)
The answer for the previous question was something like

One way how to debug this issue would be if you would put some debug
messages in oracle_enhanced_adapter.rb file so that you could identify
which method calls are taking so long time.

== 20200820164111 RemoveSemesterFromProject: migrated (0.1347s) ===============
real    13m26,038s
user    0m5,494s
sys 0m0,401s

All migrations finished in an reasonable time, so I think the error occurs in the following action "db: schema: dump" but i don't have oracle_enchaced_adapter.rb neither oracle_enhanced_adapter.rb, so i don't know where to look at.
What can i do for improve this behaviour?
Time that takes when i run db:schema:dump
real    9m44,074s
user    0m3,701s
sys 0m0,327s

and this is the time that takes when i run db:structure:dump
real    21m40,073s
user    0m5,046s
sys 0m0,413s


texts: How can I set a column to NOT NULL without locking the table during a table scan?

texts:
(former question was: Will Postgres use a multi-column index when setting multiple colums not null?)

Typically, when I set a column not null, if it doesn't have an index then I add it first, so that postgres can (hopefully) use the index while doing the table scan while locking the table, so that the table is locked for a shorter amount of time.

I want to set several columns not null, like so:

alter table foos
  alter column bar1 set not null
  alter column bar2 set not null
  alter column bar3 set not null
  alter column bar4 set not null;

If I make a multicolumn index for these columns, will postgres use it when scanning the locked table before making this alteration?

CREATE INDEX CONCURRENTLY my_index on foos (bar1, bar2, bar3, bar4);

What if I made a partial index on IS NULL (or, IS NOT NULL)

CREATE INDEX CONCURRENTLY my_index on foos (bar1, bar2, bar3, bar4) where bar1 is null and bar2 is null and bar3 is null and bar4 is null;


texts:
I am using mysql 5.6.14, For a long time i got large memory usage by mysqld.exe, i was trying many different solutions and my.ini settings to fix this up but didn't succeed, This is my config file(my.ini), When i start mysqld via command line(mysqld --console) this is the output:

2013-11-14 18:30:36 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --ex
plicit_defaults_for_timestamp server option (see documentation for more details).
2013-11-14 18:30:36 4440 [Note] Plugin 'FEDERATED' is disabled.
2013-11-14 18:30:36 4440 [Note] InnoDB: The InnoDB memory heap is disabled
2013-11-14 18:30:36 4440 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2013-11-14 18:30:36 4440 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-11-14 18:30:36 4440 [Note] InnoDB: Not using CPU crc32 instructions
2013-11-14 18:30:36 4440 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-11-14 18:30:36 4440 [Note] InnoDB: Completed initialization of buffer pool
2013-11-14 18:30:36 4440 [Note] InnoDB: Highest supported file format is Barracuda.
2013-11-14 18:30:36 4440 [Note] InnoDB: The log sequence numbers 1796484 and 1796484 in ibdata files
 do not match the log sequence number 1796504 in the ib_logfiles!
2013-11-14 18:30:36 4440 [Note] InnoDB: Database was not shutdown normally!
2013-11-14 18:30:36 4440 [Note] InnoDB: Starting crash recovery.
2013-11-14 18:30:36 4440 [Note] InnoDB: Reading tablespace information from the .ibd files...
2013-11-14 18:30:37 4440 [Note] InnoDB: Restoring possible half-written data pages
2013-11-14 18:30:37 4440 [Note] InnoDB: from the doublewrite buffer...
2013-11-14 18:30:37 4440 [Note] InnoDB: 128 rollback segment(s) are active.
2013-11-14 18:30:37 4440 [Note] InnoDB: Waiting for purge to start
2013-11-14 18:30:37 4440 [Note] InnoDB: 5.6.14 started; log sequence number 1796504
2013-11-14 18:30:37 4440 [Note] Server hostname (bind-address): '*'; port: 3306
2013-11-14 18:30:37 4440 [Note] IPv6 is available.
2013-11-14 18:30:37 4440 [Note]   - '::' resolves to '::';
2013-11-14 18:30:37 4440 [Note] Server socket created on IP: '::'.
2013-11-14 18:30:38 4440 [Note] Event Scheduler: Loaded 0 events
2013-11-14 18:30:38 4440 [Note] mysqld: ready for connections.
Version: '5.6.14'  socket: ''  port: 3306  MySQL Community Server (GPL)

Usually i am using net start mysql to start mysql server but so you may notice some problem i used mysqld --console.

As you can see this is the capture from my resource monitor:

If anyone know how to solve/reduce memory usage I will be very thankful.

Edit:
Thank you for your answer, Without changing anything at my.ini this is the value i get for SHOW GLOBAL VARIABLES like 'join_buffer_size';:

+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+

If i understand you i should set all my my.ini settings up to 4mb, So After i set:

innodb_buffer_pool_size = 4M
join_buffer_size = 4M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M

This is the new memory usage:

About SHOW ENGINE PERFORMANCE_SCHEMA STATUS; i was amazed by the numbers: http://pastebin.com/J6GfV591

About per-connection memory, I am using a Singleton pattern for mysql connections so after i search for pre-connection memory i am not sure if it is pre-query memory or pre-connection memory, If its pre-query memory so my singleton pattern is not related to the subject, So if you can some me where do i set the pre-connection memory because i don't know if it is mysql variable or my.ini directive.


texts: Filling Empty Values when Calculating Standard Deviation

texts:
I am currently trying to calculate the standard deviation of the amount of products we have sold at our company over the past 12 months.

I have a table that contains every date over the past year and is updated moving forward. I use a left outer join on our sales table to come up with 4 columns:

Year | Month | Part ID | Value

The Year and Month columns come from the date table, and the Part ID and Value represent a summation from the sales table, where the summation is grouped based on Sales Year, Sales Month, and Part ID.

Now, I can calculate standard deviation fine using this.

A simple

SELECT
    PART_ID,
    STDEV(VALUE) STANDARD_DEV_VALUE
FROM QUERYX
GROUP BY PART_ID

This works fine when I have a sale for a given item each month, as it returns 12 months of data. But, if a part does not have  a sale, I do not have data for that month.

For example, here is a possible output:

YEAR | MONTH | PART | VALUE
2016 | 1     |   X  | 100.0

when what I need is:

YEAR | MONTH | PART | VALUE
2016 | 1     |   X  | 100.0
2016 | 2     |   X  | 0
2016 | 3     |   X  | 0

 and so on upto a full 12 months...

If I don't have this, I am not accurately calculating the standard deviation.

So, can someone help explain to me how I could fill in the Part ID when there are no sales? I have no link between the Part and the Dates, so I can only link on the sales dates. Thus, my issue.

This needs to be done in a single query, as there is other data being pulled here. Cursors are not an option. A temporary table is possible but it would have to execute very quickly.


texts: Avoiding "too many active scans" on MySQL Cluster

texts:
I'm running some deletions against tables in MySQL Cluster, and keep hitting the "Too many active scans" error. I'm aware of the article by Johan Andersson, however in this case the parameters are already set very high - I'm assuming the error occurs due to the way I'm accessing the system. I'd like to fix my code, not tweak my database config any more!

The query I'm running is along the lines of:

delete from mytable where mytable.id in (<big long list of 1000s of IDs>)

Would I be better off batching this up into smaller chunks? Would that help avoid the error?

What does "Too many active scans" actually mean, and how can I avoid it in this case (without setting parameters to even higher values)?


texts: How do I troubleshoot error 26: "Error Locating Server/Instance Specified"

texts:
I'm trying to configure TCP access to my SQL Server named instance (named SQL2012). I use management studio to connect to the database engine. I type in the server name: "tcp:localhost\SQL2012" and I receive this error message:

  (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

This post by the SQL Server team seems to be really useful, in it they suggest testing out the SqlBrowser service using PortQry. When I do, I get this info:

Querying target system called:

 localhost

Attempting to resolve name to IP address...

Name resolved to 127.0.0.1

querying...

UDP port 1434 (ms-sql-m service): LISTENING or FILTERED

Sending SQL Server query to UDP port 1434...

Server's response:

ServerName MSWART7
InstanceName SQL2012
IsClustered No
Version 11.0.5058.0
tcp 52329

   ♥K   K P☺K 5 - 2 1 qK 1 4  rK 8 7 0☺    1 qK

==== End of SQL Server query response ====

And sure enough, I'm able to connect to the database when I specify the server name "tcp:localhost,52329"

I'm not sure what I'm missing. SQL Browser seems to be functioning and ports and firewalls seem to be open or irrelevant (I'm trying to connect to localhost).

Where do I look next?


texts:
I am looking at the prospect of upgrading from oracle 11G to 12C, and I am looking at the cdb vs non-cdb models, and I am having a hard time seeing the benefit of using CDB, without multitenant licensing.

For the most part when I look for information regarding the two, I come up with a lot of forums and blogs saying its the way of the future, non-CDB might be desupported in some future version, its the next big thing for oracle, as reasons to make the switch.  Those all sound like marketing driven reasons to move from non-CDB to CDB, but I am not seeing much information about real benefits of going to a new architecture.  Most of the real benefits seem to come when multitenant cdb is an option, but for the time being I have to assume its not in my case.  We have looked at the cost, and I don't see the option being purchased.

My question then is from a DBA perspective: are there any real benefits from moving to a single tenant CDB vs staying with what seems to be a tried and true design of the non-cdb environment?

Edit: man, two weeks and nothing. Does anyone have anything regarding this?


texts: How to connect two attributes without a PK (MySQL)

texts:
I'm a new programmer and I'm doing a database on MySQL with phpmyadmin. I have this doubt - I know that if you want to link two different tables you need an attribute that is a Primary Key, and another in the other table that is a Foreign Key. In this particular case I want to link a normal attribute from one table to another, something like this:

Table 1: id (PK), age, birthday, name, Charge;

Table 2: id( PK), department, name, Charge (Reference);

So I can, for example, introduce the "charge" value to the first table, and then, when I visit the table 2, it would have that value as it was a FK calling a PK, or in other case, a way where I can link the PK of the table 1 with the table 2, so I can bring any attribute to it that I want like this:

Table 1: id1 (PK), age, birthday, name, Charge;

Table 2: id2, id2 (FK), department, name, Charge (Reference);

Is there any way to make this?


texts: Performance impact of adding a foreign key to a 1M rows table

texts:
I have an ecommerce database running MariaDB + InnoDB, which has a table to store the transactions (T) and another which stores quotes (Q).

I'd like to make a connection between T and Q by adding a foreign key on T referencing Q.

Considering that Q has over 1 million rows (constantly growing) and T about 100k, do you think that adding a foreign key in table T to Q would cause a significant performance impact during daily operation?
The alternative would be to add a simple INT column without a foreign key. Or anything else, I'm open to suggestions :)

For the details :

T has about 20 rows, mainly (SHORT) INTEGERs, some other foreign keys and a couple DateTime fields
Q has 2 SHORTINT fields and a couple VARCHAR fields (255b each).

Thanks in advance for your insights.


texts: How to insert values into a junction table?

texts:
I add values into my two tables using the code below, but I also have a junction table called book_main_cover and I want to add data into this table to make a connection between book_id and metadata_image_id, how can I do it when I add the records for the first time?

With the code below, I can only add individual records without a connection.

    $stmt = $sqli->prepare("INSERT INTO book(book_original_name, book_turkish_name, book_publication_date, book_synopsis) VALUES (?,?,?,?)");
    $stmt->bind_param("ssss", $_POST['book_original_name'], $_POST['book_tr_name'], $_POST['book_year'], $_POST['book_synopsis']);
    $stmt->execute();
    $stmt->close();

    $stmt = $sqli->prepare("INSERT INTO metadata_images(metadata_image_value, type, size) VALUES (?,?,?)");
    $stmt->bind_param("sss", $name, $_FILES['book_cover']['type'], $_FILES['book_cover']['size']);
    $stmt->execute();
    $stmt->close();

I am using MySQL and InnoDB.

My database structure:

Table : book
book_id (PK)
book_original_name
book_tr_name
book_publication_date
book_synopsis

Table : metadata_images
metadata_image_id (PK)
metadata_image_value
type
size

Junction Table : book_main_cover
book_id (FK from book)
book_metadata_id (FK from metadata_images)


texts:
In the very helpful response I received to a previous question, I am trying to write some JDBC code that first sets the role to a specific user before executing subsequent queries. On the safe side I would like to prevent SQL injection attacks by parameterizing the SET ROLE statement. My approach in Groovy (which uses JDBC) was:

def sql = Sql.newInstance('jdbc:postgresql:mydb', 'mydbweb', 'mydbwebpass', 'org.postgresql.Driver')
sql.execute 'SET ROLE ?', user

but this generates a syntax error. The documentation says SET ROLE can take a string literal, but I'm just not clear how I can validly pass it one. Any suggestions?


texts:
The more I learn about about the great things you can do with mysql, the more I keep pushing to learn.

I have two tables:

Tests

TestNumber (int primary key)
InactiveTestSlotBitwise (int)

TestUsers

UserId (int)
TestNumber (int - ties in with the TestNumber in Tests)
UserSlot (int the person's seating position in the test)

I had been doing the following in two stages in php, but now see that as cumbersome. I was collecting all the 'TestUsers' with the UserId = 25 and returning to the php code which then does a separate call the database and then alters the 'InactiveTestSlotBitwise' in the 'Tests' to show they had removed themselves from the Tests they were in.

However, now I think I should be doing something else instead in one call, on the lines:

UPDATE tests AS t SET
    t.InactiveTestSlotBitwise = (t.InactiveTestSlotBitwise | (1 << tu.UserSlot))
FROM
    (SELECT TestNumber, UserSlot FROM testusers
        WHERE UserId=25 AND UserSlot >= 0
    ) AS tu
    WHERE t.TestNumber= tu.TestNumber

Surely this is possible? It does not like it but does really say why. It says:

'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (SELECT TestNumber, UserSlot ...'


texts: The key of the relation resulting from a full outer join

texts: What is the key of the relation resulting from a full outer join? If there is no key, how is it said that the relational algebra is closed under its operators and the result of each operator is also a relation?


texts: Find the value OPTIMIZE FOR UNKNOWN is using

texts: If I have an OPTIMIZE FOR UNKNOWN in my stored procedure, am I able to see what value the database decided was optimal?


texts: Would a sequence provide any performance gains over SELECT MAX(id)?

texts:
I am working on re-factoring a database. There is a process that inserts a row in one of two tables, and the id needs to be unique across the two tables.

Currently the application layer iterates through a predetermined array of keys.

I am considering a Sequence to generate the next id, as apposed to a SELECT MAX(id) + 1.

Are there any performance gains to be realized from using a Sequence?


texts: How can I search the full text of a stored procedure for a value?

texts:
I use the following script to search the text of all stored procedures when I want to find specific values.

SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE @searchText
ORDER BY ROUTINE_NAME

I recently discovered that ROUTINE_DEFINITION stops after 4000 characters, so some procedures were not getting returned when they should have been.

How can I query the full text of a stored procedure for a value?


texts: MYSQL Select data from two different tables BUT represent the data in multiple rows

texts:
I am needing to select all email addresses from two different tables with a single query.  The goal of this is to list out all email addresses a client has per row.

Both tables are structured differently from one another.

Table 1 Structure

ID    First Name    Last Name    Email
1     Mike          Smith        mikesmith@test.com
2     Jane          Doe          jane@doe.com

Table 2 Structure

ID    Name             Value
1     work_email       mikesmith@company.email
1     alt_work_email   mikesmith@second.email
2     school_email     jane@school.edu
2     address 1        123 west main street
2     city             Houston

Normally, If I knew the the exact Name for Table 2 would do a LEFT JOIN and be golden but this is not the case.

SELECT
tb1.id,
tb1.first_name,
tb1.last_name,
tb1.email as Value,
tb2.value as Value2,
FROM  table1 as tb1
LEFT JOIN table2 as tb2 ON tb2.name LIKE '%email%' AND tb2.value LIKE '%@%'
AND tb2.value != '' AND tb2.client_id = tb1.id

I know the above query is inaccurate but I needing something like this to collect all email addresses from Table 1 and where Name contains the word email from Table 2 and display those emails along with the Name value associated with the email address.  Table 1 Name value will be the column header of Email.  Desired output of what is needed is above.

Desired Output

ID    First Name    Last Name    Name           Value
1     Mike          Smith        Email          mikesmith@test.com
1     Mike          Smith        work_email     mikesmith@company.email
1     Mike          Smith        alt_work_email mikesmith@second.email
2     Jane          Doe          Email          jane@doe.com
2     Jane          Doe          school_email   jane@school.edu

Any help on this would be amazing and very much appreciated!


texts:
We have a PostgreSQL based system which is installed at 20 customers.

Sometimes I run a SQL query over all systems. Up to now I do this with the command line tool parallel like this:

parallel -q -j0 ssh {} "echo {}; psql -tAc \"select count(*) from auth_user\";echo" ::: $(ssh j list-systems prod)

I am missing the ability to sort the output. I would like to avoid dirty shell scripting, and use SQL wisdom.

How to get the N SQL queries into PostgreSQL, so that I can run ordinary SQL on it?

(Sub question: Is there a matching tag for this kind of question? I mean questions where the SQL spans several databases?)


texts: Slow SQL Server query using a DB2 linked server in virtual environment

texts:
I have a problem with a slow query which uses a linked server. Let me list out my environment first and I'll get to the issue:

Virtual Windows 2003 R2 server (fresh deployments on XenServer 6.1 & ESXi 5)
SQL Server 2005 SP3
Linked Server created using IBM DB2 for iBMASQL OLE DB Provider

The query itself is a select statement with a where clause which selects transactions this month.

Alright, so the issue itself:

Virtual machine is set to 4 vCores, it takes roughly 35 seconds to complete
Virtual machine is set to 2 vCores, it takes roughly 20 seconds to complete
Virtual machine is set to 1 vCores, it takes roughly 5 seconds to complete

I've tried tinkering with:

Network card settings
MAXDOP 1 OPTION
Looking at the execution plan

I'm at a loss as to what to do from here on. We want to use 4 vCores as the server will be heavily used. However, the queries take a lot longer when compared to using only 1 vCore.

Is there any possible way to limit the amount of vCores the linked server uses?

Is there anything else that I should take a look at?

Thanks!


texts: Insert into DB2 table a result from temp table

texts:
Is there a way to insert a temp table results to DB2 table, for example:

WITH TEMP1 AS (
SELECT CUSTOMER_ID
FROM T_CUSTOMER WHERE CUSTOMER_ID BETWEEN 100 TO 300
),
TEMP2 AS (
SELECT CUTOMER_ID, ORDER_ID
FROM TEMP1 T1
JOIN T_ORDER R1 ON T1.CUSTOMER_ID = R1.CUSTOMER_ID)

SELECT CUTOMER_ID, ORDER_ID
FROM TEMP2

I tried the below way, but it did not work:

INSERT * INTO T_STORAGE (CUTOMER_ID, ORDER_ID)
SELECT *
FROM TEMP2

I searched a lot but couldn't find any way. Only to modify my query without temp tables, but then the query will be very slow.


texts:
Oracle instance I set up is using USE_DB_RECOVERY_FILE_DEST for archivelog location.

log_archive_dest_1                   string

LOCATION=use_db_recovery_file_
dest, valid_for=(ALL_ROLES,ALL
_LOGFILES)

I want to specify the exact location. Old and new location is same, only need to specify the path. I cannot restart the database as it is production server. Is there any way I can do it without restarting the database


texts: SQL Server 2014 In Memory Table Creation Error

texts:
When I create an in-memory table, I get this error:

  The data types char(n) and varchar(n) using a collation that has a code page other than 1252 are not supported with memory optimized tables


texts: Issues with MySQL 5.7 user-created temp tables

texts:
i have a CREATE TEMPORARY TABLE query that creates  a temp table in a particular database  and  multiple sub-select queries will use that same temp table.On MySQL 5.6, all the sub-select queries will execute successfully but now on MySQL 5.7,  I am having some problems where some of the sub-select queries will execute successfully  and then  some will fail with an error 'table doesn't exist'?..Any help please..

Thanks.


texts: Merging multple pg_dumps with columns using auto key

texts:
I have two unique instances of Postgres using the same database schema but with independent datasets. One of the tables has a column setup for auto increment and it is a primary key. No other tables depend on this primary key.

Is there a way to merge these two databases together and have the import mechanism reassign new keys (the auto increment column) on this specific table only?


texts: Purging Binary Logs from Master in a Replication Environment

texts:
Replication Set up

6 Masters and their respective Slave.
All 6 slaves writing to a Master - which has its own Slave.

Now, I want to purge binary logs on the 6 original masters and set expiry to 15 days. Currently, those servers are holding binlogs for over 6 months.

If I just go and purge binary logs on the 6 Masters, can it be a problem for the Slaves?

Replication Topology

M Con 1 is the slave replicating from all 6 slaves (which are acting as masters).


texts: Dataguard: preventing logical standby to loose track after flashback database on primary

texts:
We have 2 Oracle databases; a primary and a logical standby, configured with Dataguard. Whenever we have an application rollout on the primary, we create a restore point in case anything goes wrong.

Last Tuesday something went wrong and we had to restore the primary database to its restore point.

That caused the logical standby database to stop applying archive logs (which makes sense, because the primary database was basically reverted in time, leaving the logical standby in an undefined state).

How could we have prevented this situation?

I imagine this scenario:

Stop SQL Apply on logical standby
Create restore point on primary
Do stuff on primary, error occurs
Flashback primary database to restore point
Open primary database
Start SQL Apply on logical standby

Would this work? Would it allow the logical standby to continue applying without getting diverged from the primary database?


texts: General purpose view for last value in a group

texts:
I am trying to create a view to show the last value in the group.
Looking for a more general purpose query.

CREATE TABLE a (
   id INTEGER,
   seq INTEGER,
   val VARCHAR2(16),
   PRIMARY KEY(id,seq)
);

-- View 1
CREATE VIEW v1 AS SELECT a.*
 FROM
  a,
  (SELECT id, MAX(seq) from a GROUP BY id) b
 WHERE a.id=b.id AND a.seq=b.seq

-- View 2
CREATE VIEW v2 AS SELECT a.*
 FROM
  a
 WHERE a.seq=(SELECT seq from a b WHERE a.id=b.id ORDER BY seq DESC LIMIT 1)

View 1 perform better when I want to list all records.

View 2 perform better when I do  SELECT * FROM v2 WHERE id=?

Because this view is to be given to outsiders I want this to be as general purpose as possible. Is there any way to tell PostgreSQL these two query is equivalent and let it pick them automagically?