How to include in the result of a `SELECT ... GROUP BY ...` all the other columns that are functionally dependent on the grouping ones? I'll base this question on a toy example.

Let this be table A:

            A
-------------------------
 U | V | W | X | Y |  Z
-------------------------
 a | b | c | 1 | 6 | 8.3
 a | b | c | 1 | 4 | 3.7
 a | b | f | 3 | 4 | 2.6
 a | b | f | 3 | 2 | 6.0
 a | e | c | 1 | 0 | 3.5
 a | e | c | 1 | 5 | 8.8
 d | b | f | 1 | 0 | 3.5
 d | b | f | 1 | 3 | 2.3
 d | e | c | 2 | 6 | 2.2
 d | e | c | 2 | 4 | 3.3
 d | e | f | 0 | 7 | 5.0
 d | e | f | 0 | 6 | 3.6

I can produce a second table B by grouping the rows of A by columns U, V, and W, and computing the average of column Z for each group.

         B
-------------------
 U | V | W | Z_avg
-------------------
 a | b | c |  6.0
 a | b | f |  4.3
 a | e | c |  6.2
 d | b | f |  2.9
 d | e | c |  2.7
 d | e | f |  4.3

The SQL for this would be something like

SELECT U, V, W, AVG(Z) AS Z_avg FROM A GROUP BY U, V, W;

But I want the new table to include all the columns of the original table that have a functional dependence on the grouping columns U, V, and W.  In this example there is one such column, namely column X.

In other words, I want to generate the table C shown below:

           C
-----------------------
 U | V | W | X | Z_avg
-----------------------
 a | b | c | 1 |  6.0
 a | b | f | 3 |  4.3
 a | e | c | 1 |  6.2
 d | b | f | 1 |  2.9
 d | e | c | 2 |  2.7
 d | e | f | 0 |  4.3

So this problem has two parts, at least conceptually.

How to determine which columns are functionally dependent on
 columns U, V, and W?
What is the SQL to generate table C?

I know how to implement a (say, Python) script that can answer (1), but it is tedious and slow.  (Basically, for each of the candidate columns, in this case X and Y, the script would collect all of its values for each distinct combination of values in columns U, V, and Z, and then, if each of these sets of values has exactly one element, then the column is functi\onally related to U, V, and Z.)

Likewise, once I have identfied the functionally dependent columns, I can muddle may way through (using temporary tables and what not) to eventually end up with something like table C above (thus, effectively solving (2)).

I figure, however, that this task is sufficiently common that there may be standard tools/techniques to carry it out.

Under the assumptions that you have some columns (say x and y in your example), that you don't know if they are functionally equivalent or not - and that these columns do not have any NULL values (which would complicate things), you can use:

SELECT
    u, v, w,                    -- the grouping columns

    AVG(z) AS z_avg,            -- the non-functionally dependent

    CASE WHEN MIN(x) = MAX(x)   -- a possibly functionally dependent
      THEN MIN(x) ELSE NULL     -- column
    END AS x,

    CASE WHEN MIN(y) = MAX(y)   -- another one
      THEN MIN(y) ELSE NULL
    END AS y
FROM
    a
GROUP BY
    u, v, w ;

If a column is not functionally dependent (with the current data), it will have at least 2 distinct values, so the MIN and MAX will be different and the result will be shown as NULL in that column, in all rows. You can then remove it from the query.


SQL Server 2012 - Insufficient memory to continue the execution of the program. (mscorlib) I have a sql file, which is up to 180 Mb. When I try to open and execute it on Microsoft SQL Server Managment Studio 2012, I am getting this error.
 . What do do ? I researched on Google. And there were a few answers. One solution they say was running a C# script. Here is it . But I don't know how to run, and anyway if that script will work or not. Thanks for help.

If you need to execute sql file use sqlcmd documented in the MSDN: Run Transact-SQL Script Files Using sqlcmd

This did the trick for me:

sqlcmd -S serverName\instanceName -i script.sql  -o output.txt


Does an empty column value occupy same storage space as a filled column value? I have a table with 2 columns. The type of both columns is set to varchar(38). If I create a row with an empty value for one of the columns, will it take same storage space as if the value was not empty?

In other words, will MySQL reserve storage space for the column (depending on its type) when a row is created?

From the Innodb Physical Row Structure, bulletpoint #7 under REDUNDANT ROW_FORMAT

  An SQL NULL value reserves one or two bytes in the record directory. Besides that, an SQL NULL value reserves zero bytes in the data part of the record if stored in a variable length column. In a fixed-length column, it reserves the fixed length of the column in the data part of the record. Reserving the fixed space for NULL values enables an update of the column from NULL to a non-NULL value to be done in place without causing fragmentation of the index page.

From the Innodb Physical Row Structure, bulletpoint #2 under COMPACT ROW_FORMAT

  The variable-length part of the record header contains a bit vector for indicating NULL columns. If the number of columns in the index that can be NULL is N, the bit vector occupies CEILING(N/8) bytes. (For example, if there are anywhere from 9 to 15 columns that can be NULL, the bit vector uses two bytes.) Columns that are NULL do not occupy space other than the bit in this vector. The variable-length part of the header also contains the lengths of variable-length columns. Each length takes one or two bytes, depending on the maximum length of the column. If all columns in the index are NOT NULL and have a fixed length, the record header has no variable-length part.

Based on these bulletpoints, here is what a NULL value takes up for a column's storage

variable length: a NULL value takes up no storage in the row itself
fixed length: Takes up the reserved space

Now, you must decide between use CHAR and VARCHAR because of what the first point brought out

  Reserving the fixed space for NULL values enables an update of the column from NULL to a non-NULL value to be done in place without causing fragmentation of the index page

This will prevent introducing any fragmentation of a row going down the road once non-NULL data is stored. This is something I have discussed before with regards to MyISAM : See my old post What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?.


Pivot add-on in cross-tab table Postgresql Given the schema here I wonder how I can use Pivot add-on to calculate a cross-tab table in the below format please?

Africa   America  Asia       Australia/Oceania   Europe     Total
#cities  #cities  #cities    #cities             #cities    #cities

The below script returns a normal result but I want it to appear in a cross-tab

SELECT  en.continent,count(ci.name)
FROM encompasses AS en
INNER JOIN city AS ci ON en.country = ci.country
GROUP BY en.continent
Union all
select 'Total', COUNT(ci.name)
from city AS ci;

The create/insert commands can be found here:

CREATE TABLE Country
(Name VARCHAR(35) NOT NULL UNIQUE,
 Code VARCHAR(4) CONSTRAINT CountryKey PRIMARY KEY,
 Capital VARCHAR(35),
 Province VARCHAR(35),
 Area NUMERIC CONSTRAINT CountryArea
   CHECK (Area >= 0),
 Population NUMERIC CONSTRAINT CountryPop
   CHECK (Population >= 0));

CREATE TABLE City
(Name VARCHAR(35),
 Country VARCHAR(4),
 Province VARCHAR(35),
 Population NUMERIC CONSTRAINT CityPop
   CHECK (Population >= 0),
 Longitude NUMERIC CONSTRAINT CityLon
   CHECK ((Longitude >= -180) AND (Longitude <= 180)) ,
 Latitude NUMERIC CONSTRAINT CityLat
   CHECK ((Latitude >= -90) AND (Latitude <= 90)) ,
 CONSTRAINT CityKey PRIMARY KEY (Name, Country, Province));

CREATE TABLE encompasses
(Country VARCHAR(4) NOT NULL,
 Continent VARCHAR(20) NOT NULL,
 Percentage NUMERIC,
   CHECK ((Percentage > 0) AND (Percentage <= 100)),
 CONSTRAINT EncompassesKey PRIMARY KEY (Country,Continent));

 INSERT INTO country VALUES ('Albania','AL','Tirane','Albania',28750,3249136);
 INSERT INTO country VALUES ('Greece','GR','Athens','Attiki',131940,10538594);
 INSERT INTO country VALUES ('Macedonia','MK','Skopje','Macedonia',25333,2104035);
 INSERT INTO country VALUES ('Serbia','SRB','Belgrade','Serbia',77474,7379339);
 INSERT INTO country VALUES ('Montenegro','MNE','Podgorica','Montenegro',14026,672180);
 INSERT INTO country VALUES ('Kosovo','KOS','Pristina','Kosovo',10887,1804838);
 INSERT INTO country VALUES ('Andorra','AND','Andorra la Vella','Andorra',450,72766);
 INSERT INTO country VALUES ('France','F','Paris','Ile de France',547030,58317450);
 INSERT INTO country VALUES ('Spain','E','Madrid','Madrid',504750,39181114);

 INSERT INTO encompasses VALUES ('LS','Africa',100);
 INSERT INTO encompasses VALUES ('RM','Africa',100);
 INSERT INTO encompasses VALUES ('MW','Africa',100);
 INSERT INTO encompasses VALUES ('MOC','Africa',100);
 INSERT INTO encompasses VALUES ('MS','Africa',100);
 INSERT INTO encompasses VALUES ('MAYO','Africa',100);
 INSERT INTO encompasses VALUES ('SD','Africa',100);
 INSERT INTO encompasses VALUES ('REUN','Australia/Oceania',100);
 INSERT INTO encompasses VALUES ('HELX','Australia/Oceania',100);
 INSERT INTO encompasses VALUES ('STP','Africa',100);
 INSERT INTO encompasses VALUES ('SY','Africa',100);

INSERT INTO city VALUES ('Tirane','AL','Albania',192000,19.8,41.3);
 INSERT INTO city VALUES ('Shkoder','AL','Albania',62000,19.2,42.2);
 INSERT INTO city VALUES ('Durres','AL','Albania',60000,19.3,41.3);
 INSERT INTO city VALUES ('Vlore','AL','Albania',56000,19.3,40.3);
 INSERT INTO city VALUES ('Elbasan','AL','Albania',53000,20.1,41.1);
 INSERT INTO city VALUES ('Korce','AL','Albania',52000,20.5,40.4);
 INSERT INTO city VALUES ('Komotini','GR','Anatoliki Makedhonia kai Thraki',NULL,NULL,NULL);
 INSERT INTO city VALUES ('Kavalla','GR','Anatoliki Makedhonia kai Thraki',56705,NULL,NULL);
 INSERT INTO city VALUES ('Athens','GR','Attiki',885737,23.7167,37.9667);
 INSERT INTO city VALUES ('Piraeus','GR','Attiki',196389,NULL,NULL);

It the sample data it should only include 2 continents Africa and Australia/Oceania.

caveats below

With tablefunc

First you need to add the tablefunc extension

CREATE EXTENSION tablefunc;

What you need is something like this,

SELECT *
FROM crosstab(
  $$
    SELECT 0, coalesce(continent, 'Total'), count(city.name)
    FROM encompasses AS e
    JOIN country ON e.country = country.code
    JOIN city ON country.code = city.country
    GROUP BY GROUPING SETS ((e.continent), ())
    ORDER BY 1, 2;
  $$,
  $$VALUES ('Africa'), ('America'), ('Asia'), ('Australia/Oceania'), ('Europe'), ('Total')$$
) AS t(rowid int, africa int, america int, asia int, australia_oceania int, europe int, total int);

Should get ya done.

Of course none of this works with the sample data because none of those countries have entires in encompasses,

INSERT INTO encompasses (country, continent) VALUES ('GR', 'Africa');

 rowid | africa | america | asia | australia_oceania | europe | total
-------+--------+---------+------+-------------------+--------+-------
     0 |      4 |         |      |                   |        |     4
(1 row)

You may want to replace

$$VALUES ('Africa'), ('America'), ('Asia'), ('Australia/Oceania'), ('Europe')$$

with

$$ SELECT DISTINCT continent FROM encompasses ORDER BY 1; $$

But either way, you'll have to type that out in the table definition (the list in the t(rowid int...))

Caveats

I don't actually do this in my code, ever. This pivot is a waste of time and computation. I would always do this in whatever language I'm using to interact with the db. I don't actually think this is a good idea (here). But this is how it's done.


Converting (a) an entity-relationship diagram about an airline booking scenario to (b) a relational schema I am currently following notes on a certain "7 step strategy" to transform an ERD to a relational schema and was wondering some points about step 4, converting one-to-many (1:N) relationships.

The relevant ERD is the following one:

Questions

Would SEAT contain

Seat_no, Date (from LEG_INSTANCE)

or

Seat_no, Customer_name (from RESERVATION), Cphone (from RESERVATION), Date (from LEG_INSTANCE)?

Would FLIGHT_LEG contain Scheduled_dep_time & Scheduled_arr_time?

First, a 1-N relationship, the table on the N side would have the key of the table on the 1 side configured as Foreign Key.

So depending how leg_instance is keyed, either:

  Seat: seat_no, Date(from leg_instance), leg_no (from flight_leg), Number (from flight),
  with key (Number,leg_no,seat_no)
  and foreign key (leg_no,Date) => leg_instance(leg_no,Date)

or

Seat: seat_no, leg_instance_id (from leg_instance)
with key (leg_instance_id, seat_no)
and foreign key (leg_instance_id) => leg_instance(leg_instance_id)

Then, the table on the N side caries all attributes of the relationship, so here Seat would also have Customer_name and Cphone.  It would be permissible to introduce a separate table for Reservation that is 1-1 with Seat, but 1-1 relationships are normally just merged into a single table.  That table might end up being named SEAT, RESERVATION or something like SEAT_RESERVATION, as it implements the ERD shapes with both those names.

  Another question I have is would flight_leg contain scheduled_dep_time & scheduled arr_time ?

You should be able to answer that yourself, now.


Date Conversion error This is just an example:

SELECT * FROM A.B where B_DATE='19-05-2016 12.00.00.000000000 AM';

I always get an error:

  Msg 241, Level 16, State 1, Line 1
  Conversion failed when converting date and/or time from character string.#

I have looked at trying to use CONVERT, but I can't see any format that matches the datetime format I have above. Any advice on how I can rectify this?

If you are using a 'literal date' you need to type the format differently.  If you are using a variable or field you will need to use substring and parse into the correct format.

I have a DateTime2 field in a table and the following works, similar to your query but formatted differently:

SELECT * FROM Table_1 where CreatedOn='2016-05-19T00:00:00.0000000';


MongoDB - Don't know what to do with file Windows 10 and MongoDB 3.2

I have 2 files. One with .ns extension and one with. They are both in c:\data\dump

I am trying to run

mongorestore -d gnome c:\data\dump

However all I get is

2015-12-11T23:14:12.860+0000    building a list of collections to restore from c:\data\dump dir
2015-12-11T23:14:12.862+0000    don't know what to do with file "c:\data\dump\gnome.0", skipping...
2015-12-11T23:14:12.862+0000    don't know what to do with file "c:\data\dump\gnome.ns", skipping...
2015-12-11T23:14:12.863+0000    done

Any advice?

The .ns file is the file in which MongoDB stores the namespaces of a database. So most likely, what you have is not a dump, but a copy of the datafiles of a single database. Note that this most likely is only a partial copy of a MongoDB instance.

Here is what to do in case you already have an instance of MongoDB running.

Start an other MongoDB instance on another port with the dbpath pointing to the directory with those files.
Connect to the database instance and verify the data.
Make a dump of the data.
Stop the new instance.
Restore the dump to the original instance
Optionally delete the "rogue" datafiles

In case you do not have any instance running yet, simply start MongoDB with dbpath pointing to the directory with the data files or copy the datafiles into your dbpath.


Modelling the structure of a database involving products with multiple barcodes What would be the best way to store barcodes for products? I have a situation where the same product can have many different barcodes, let's say I have 1 product and this product can have 100 different barcodes (which must be unique).

Is it better to store those barcodes in separate table? For example, the following diagram

means I will have 1 product and 100 rows with different barcodes for this product (same name and everything).

Or is it better to have 100 products with the same name, each with barcode column, as shown below?

If you have a truly one-to-many relationship between your products and barcodes, it makes sense to keep it in a separate table for barcodes (don't repeat yourself-- your name and description would be identical for each repeated barcode).

IF however there is any variation in the product itself (name or description could vary) then you need to store each as a discrete record.


MySQL or Oracle Associate Certification? I have 15+ years in software development and 7+ in web development, and I want to get a certification in another area, like DBA. I have 7+ years of experience like MySQL developer. Which is better for certification? MySQL or Oracle 11g? Which of the two is more hired and better paid?

Forgive me for sounding a little biased, but I would recommend MySQL DBA Certification for you without hesitation. I say this without reservation because that is how I become a MySQL DBA.

I actually wrote an earlier post about making such a transition from school, from a willing developer, from an unwilling developer, and from a sysadmin.

I also wrote another post on how DBAs should properly interact with developers and project managers.

I would personally say MySQL positions are very underrated in the eyes of most companies, yet the demand is out there. I feel have been respected because of my certification (unique experience, result vary based on who hires). I certified back in August 2007 with my employer paying for the course. At that time, no instructor-led MySQL course was required for certification. Now that Oracle owns MySQL, that may have changed. Please check with Oracle on that.

Most companies that hire MySQL DBAs may expect you to know everything else, (Linux, Apache, Nagios, Munin, JBoss, PHP, Perl, Python, etc). Many other companies may use only MySQL. Overall, you have to be selective when looking. The more disciplines/skills you have outside of MySQL, the better your employment chances are.

What makes the most money is a different story. Hands down, Oracle DBA/Developers make more money. Certification is more expensive because Oracle Associate requires only an exam. To earn deeper Certifications in Oracle, you take necessary exam(s) and you must attend at least one instructor-led Oracle course (Exams run about $200-$300, while the courses run $3,000/each). While Oracle Certification is more rigorous, the respect you get for working with Oracle and the money will indeed come. You can pretty much write your own ticket an an OCP. Going for OCA is a good start.

Regardless of MySQL, Oracle or any other RDBMS, nothing can substitute for having working experience. Due diligence in all DBAs takes will train you to certify quickly.

If there are any Oracle gurus out there, please post your own answers on Oracle as I am sure there a lot more positive things that can be said for Oracle.


PostgreSQL psql - not working but no errors I'm a complete noob to PostgreSQL, so I'm probably missing something completely obvious.

Connected to my local db via psql on terminal. Any command I run to make db changes, or even a simple select does nothing. No error, just no results.

I can use the shell commands createdb, dropdb just fine.

Got a site running in Ruby on Rails, RefineryCMS, using the local PostgreSQL db, so I know the db has data and is working - I just can't seem to query or modify it via psql.

What am I missing?

Mac OS X Lion, pg v 9.2.

Jumped on the IRC postgresql channel and had this answered in 30 seconds.

Issue was that I was not ending my SQL commands with a semicolon. Doh!


Londiste replication fails intermittenly I have skytools setup for postrgesql replication. It keeps on failing intermittently. When I checked londiste status I get following error

$ londiste /etc/skytools/mydb_data_0.ini status
Queue: mydb_data   Local node: mydb_data_0

mydb_data (root)
  |                   Tables: 18/0/0
  |                   Lag: 9s, Tick: 2620740
  +--: mydb_data_0 (leaf)
  |                   Tables: 18/0/0
  |                   Lag: 15m8s, Tick: 2620670
  |                   ERR: mydb_data_0: Lost position: batch 620669..2620669, dst has 2620670
  +--: mydb_data_1 (leaf)
                      Tables: 18/0/0
                      Lag: 9s, Tick: 2620740

I really don't understand what's going wrong. I get the same error message in postgres logs also,

Exception: Lost position: batch 2620669..2620669, dst has 2620670

I found this article to solve the error which I'm getting. It says you would have to use --reset option of worker to reset the queue position on remote site and then issue wait-sync to get the table queue moving again.

So I did this,

$ londiste /etc/skytools/mytestdb_data_0.ini worker --reset
Ignoring stale pidfile
2016-12-23 17:00:34,278 15245 INFO Resetting queue tracking on dst side

It resets the queue successfully, but when I check londiste status I get this error,

$ londiste /etc/skytools/mydb_data_0.ini status
Queue: mydb_data   Local node: mydb_data_0

mydb_data (root)
  |                   Tables: 18/0/0
  |                   Lag: 9s, Tick: 2620740
  +--: mydb_data_0 (leaf)
  |                   Tables: 18/0/0
  |                   Lag: 15m8s, Tick: 2620670
  |                   ERR: mydb_data_0: [ev_id=84594950,ev_txid=702851528] duplicate key value violates unique constraint "dmn_pkey"
  +--: mydb_data_1 (leaf)
                      Tables: 18/0/0
                      Lag: 9s, Tick: 2620740

I don't know what is causing this, can you please guide me on this.

Postgresql verion : 9.5, Skytools version : 3.2

Update

I found this skytools logs for master db,

2016-12-27 05:36:23,369 15563 ERROR Job mydb_data_0 crashed: Lost position: batch 2681655..2681655, dst has 2681656
Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/skytools-3.0/skytools/scripting.py", line 578, in run_func_safely
    r = func()
  File "/usr/lib/python2.7/dist-packages/skytools-3.0/pgq/cascade/consumer.py", line 199, in work
    return BaseConsumer.work(self)
  File "/usr/lib/python2.7/dist-packages/skytools-3.0/pgq/baseconsumer.py", line 257, in work
    self._launch_process_batch(db, batch_id, ev_list)
  File "/usr/lib/python2.7/dist-packages/skytools-3.0/pgq/baseconsumer.py", line 286, in _launch_process_batch
    self.process_batch(db, batch_id, list)
  File "/usr/lib/python2.7/dist-packages/skytools-3.0/pgq/cascade/consumer.py", line 168, in process_batch
    if self.is_batch_done(state, self.batch_info, dst_db):
  File "/usr/lib/python2.7/dist-packages/skytools-3.0/pgq/cascade/worker.py", line 185, in is_batch_done
    done = CascadedConsumer.is_batch_done(self, state, batch_info, dst_db)
  File "/usr/lib/python2.7/dist-packages/skytools-3.0/pgq/cascade/consumer.py", line 254, in is_batch_done
    prev_tick, cur_tick, dst_tick))
Exception: Lost position: batch 2681655..2681655, dst has 2681656
2016-12-27 05:37:11,988 18190 INFO Resetting queue tracking on dst side
2016-12-27 05:37:47,776 18578 INFO pgq.maint_operations is installed
2016-12-27 05:37:48,038 18578 INFO {count: 0, duration: 0.295}
2016-12-27 05:37:48,115 18578 ERROR Job mydb_data_0 got error on connection 'db': duplicate key value violates unique constraint "dmn_pkey"
DETAIL:  Key (id)=(31780560) already exists..   Query: update only public.percent_bleed set percent_redir_sid = '43 ...
Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/skytools-3.0/skytools/scripting.py", line 578, in run_func_safely
    r = func()
  File "/usr/lib/python2.7/dist-packages/skytools-3.0/pgq/cascade/consumer.py", line 199, in work
    return BaseConsumer.work(self)

Sorry for the answer, but I cannot comment....

You got the pretty straightforward message :

ERR: mydb_data_0: [ev_id=84594950,ev_txid=702851528] duplicate key value violates unique constraint "dmn_pkey"

It says you're trying to insert a value which is already present in the table.
You have to work on your enqueue/dequeue process to avoid duplicates.


PostgreSQL: date/time field value out of range Software:

Linux cjz-eshop1-p 5.4.0-33-generic #37-Ubuntu SMP Thu May 21 12:53:59 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
psql (PostgreSQL) 12.3 (Ubuntu 12.3-1.pgdg18.04+1)

I am getting errors due to CSV import into the database on my server.

ERROR: current transaction is aborted, commands ignored until end of transaction block
Price 244385 ERROR: date/time field value out of range: "30.06.2020"
  Hint: Perhaps you need a different "datestyle" setting.
  Position: 160
ERROR: current transaction is aborted, commands ignored until end of transaction block
Price 244386 ERROR: date/time field value out of range: "30.06.2020"
  Hint: Perhaps you need a different "datestyle" setting.
  Position: 160

But on my local system, the import is working.

I tried almost everything regarding this issue, but no luck (I drew mostly from here).

Any ideas to solve this issue?

UPDATE

Import is done by java. I believe it is not important, because on my local computer, it is working with same OS and same configuration. And as you can see, when I try select related to date (as @Laurenz helped me), it is ok.

postgres@cjz-eshop1-p:~$ psql
psql (12.2 (Ubuntu 12.2-4))
Type "help" for help.

postgres=# SELECT '30.06.2020'::date;
    date
------------
 30.06.2020
(1 row)

postgres=#

Before, it was not possible. Do you want some more details?

UPDATE 2

                    if (body[5].length() != 0) {
                        akc_from = "'" + body[5] + "'";
                    } else {
                        akc_from = "null";
                    }

                    if (body[6].length() != 0) {
                        akc_to = "'" + body[6] + "'";
                    } else {
                        akc_to = "null";
                    }

                    if (body[7].length() != 0) {
                        akc_type = body[7];
                    } else {
                        akc_type = "null";
                    }

                    insertPriceCommand = "insert into stg_price(art_no,store_no,sell_pr,akc_price,akc_from,akc_to,akc_type,run_id,proc_flag,proc_note) values(" + art_no + "," + store_no + "," + sell_pr + "," + akc_price + "," + akc_from + "," + akc_to + "," + akc_type + "," + run_id + ",0,null)";
//                System.out.println(insertPriceCommand);
```

Follow the hint and adjust your datestyle setting appropriately.

The parameter consists of two parts: the output style and the expected ordering of day, month and year.

With US settings, parsing 30.06.2020 causes an error:

SET datestyle = US, MDY;

SELECT '30.06.2020'::date;

ERROR:  date/time field value out of range: "30.06.2020"
LINE 1: SELECT '30.06.2020'::date;
               ^
HINT:  Perhaps you need a different "datestyle" setting.

This is because there are only 12 months in a year.

You should probably use this setting:

SET datestyle = GERMAN, DMY;

SELECT '30.06.2020'::date;

    date
------------
 30.06.2020
(1 row)

You can use the options connection parameter to set datestyle when you connect.


Root max connections per hour exceed I made a mistake and set the max_connections column for root to '1', so now every time I try to connect to change it I get:

  ERROR 1226 (42000): User 'root' has exceeded the 'max_connections_per_hour' resource (current value: 1)

It was my personal server, so I don't have any other user.

I've tried:

Resetting the server
Resetting the mysql process

But nothing works. Does anyone know how to change it?

You have to remove that attribute via MySQL

APPROACH #1

This only works for MySQL 5.7

ALTER USER root@localhost MAX_CONNECTIONS_PER_HOUR 0;

If you have other root users (like root@'127.0.0.1' and root@'%'), you will have to run a separate ALTER USER for each root user.

APPROACH #2

UPDATE mysql.user SET max_connections = 0 WHERE user='root';
FLUSH PRIVILEGES;

Log out and login to MySQL and you should notice the difference

NOTES

If you are logged out, you have to come back in an hour and try again.

If you can't wait one hour and you can restart mysql, you could enable --skip-grant-tables

service mysql restart --skip-grant-tables --skip-networking

Next, you can login and perform APPROACH #2

Then, restart mysql normally

service mysql restart

UPDATE 2016-11-25 14:49 EDT

With Windows, it's a little more involved.

Assuming you installed MySQL in Windows using the MSI Installer, there is no my.ini.

Here is where it gets crazy, but here we go ...

STEP 01

Login to the Windows Command Line as Administrator and run

C:\> net stop mysql

STEP 02

Find the folder where mysqld.exe is located on the Windows.

Usually, it would be something like C:\ProgramData\MySQL\MySQL Server 5.6

Start mysqld and login (no password needed)

cd "C:\ProgramData\MySQL\MySQL Server 5.6"
start mysqld --skip-grant-tables --skip-networking
mysql

STEP 03

Apply APPROACH #2

UPDATE mysql.user SET max_connections = 0 WHERE user='root';
FLUSH PRIVILEGES;
exit

STEP 04

Shutdown mysqld manually

mysqladmin shutdown

STEP 05

Start mysqld as a service

net start mysql

GIVE IT A TRY !!!


Oracle Shutdown Method Shutting down a database before doing an upgrade or a patch can be done several ways.

shutdown immediate;

or

shutdown abort;
startup restrict;
shutdown immediate;

or

shutdown abort;
startup restrict;
shutdown;

or

alter system checkpoint;
shutdown abort;
startup restrict;
shutdown immediate;

Of course there are other options as well.  Which should be preferred and why?

The aim when shutting down for maintenance (or cold backup) is that the database is left in a consistent state with no need for rollback/recovery on startup.

There are 3 SQL*Plus shutdown commands that achieve this in theory, all of which immediately prevent new sessions connecting to the instance:

shutdown normal or just shutdown: waits for all sessions to disconnect. This mode is rarely used in practice because it relies on well-behaved clients not leaving connections open. This used to be the only shutdown mode that did not cancel running transactions.
shutdown transactional: disconnects sessions once currently running transactions complete, preventing new transactions from beginning.
shutdown immediate: disconnects all sessions immedately and rolls back interrupted transactions before shutting down. Note that the disconnections are immediate, but the shutdown may not be as any interrupted transactions may take time to roll back.

The fourth mode of shutdown is shutdown abort. This is like pulling the power cord - the instance stops now without any cleanup. You usually want to bring the database up again afterwards and shut down cleanly immediately afterwards as in your example. The concepts guide says:

  This mode is intended for emergency situations, such as when no other form of shutdown is successful.

All the examples you give perform a checkpoint as part of the shutdown [normal] or shutdown immediate so explicit checkpointing is presumably to reduce the time required for recovery.

general advice:

Do not use shutdown normal.
Use shutdown transactional for attended shutdown only, when you want to minimise cancelled transactions (attended only because this kind of shutdown is not guaranteed to shut the database down at all if timeouts are breached).
Use shutdown immediate for unattended shutdown or when you do not care about currently running transactions.
Do not use shutdown abort (plus startup/shutdown) unless you have to - this was more common in much earlier versions of Oracle that it is today. In other situations (not patch/upgrade), if you have a need to minimise downtime this mode may be appropriate.


Changing MASTER_CONNECT_RETRY - Anything pit falls to keep in mind? We run your basic Master + Multi-slave mysql replication setup.

We would like to lower the MASTER_CONNECT_RETRY parameter on each of our slaves.

Is there anything beyond re-executing CHANGE MASTER TO ... on each of our slaves with the new connect_retry paramater (and of course MASTER_HOST, MASTER_USERNAME, etc etc, which will be the same) that we need to keep in mind?

As mentioned, this is an already running replication setup. All that we want to do is lower the MASTER_CONNECT_RETRY time.

Thanks.

The number of times that the slave tries to connect to the master before giving up. Reconnects are attempted at intervals set by the --master-connect-retry option (or the MASTER_CONNECT_RETRY option of the CHANGE MASTER TO statement) and reconnects are triggered when data reads by the slave time out according to the --slave-net-timeout option. The default value is 86400. A value of 0 means “infinite”; the slave attempts to connect forever.

Slave-net-timeout --> The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the CHANGE MASTER TO statement or --master-connect-retry option and the number of reconnection attempts is limited by the --master-retry-count option. The default is 3600 seconds (one hour).

So in conclusion you have to follow these steps in all slaves:

stop slave;
CHANGE MASTER TO MASTER_HOST = 'master_host',
MASTER_USER = 'user',
MASTER_PASSWORD = 'password',
MASTER_PORT = port,
MASTER_LOG_FILE = 'filename',
MASTER_CONNECT_RETRY=50;
start slave;


dbms_output hard limit in SQL Developer I have an issue with SQL Developer - it is not possible to set dbms_output buffer larger than 1 000 000 symbols, while it works in SQL*Plus.
This script:

SET SERVEROUTPUT ON SIZE UNLIMITED

    BEGIN
       for c in (SELECT rpad('a',1000,'a') ll from dual connect by level <=2001) loop
         dbms_output.put_line (c.ll);
       end loop;
       dbms_output.put_line ('www');
    END;
    /

works in SQL*Plus, but fails with:

ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 3

in SQL Developer.

I've tried to use dbms_output view in SQL Developer and set size to unlimited as described here, but the same amount of symbols is printed and there is no 'www' line. Version of SQL Developer is 4.03.16. Is it a known issue?

In SQL Developer, SET SERVEROUTPUT ON SIZE UNLIMITED sets the buffer to the maximum value 1000000 instead of unlimited (tested with 12.1.0.2.161018 database and SQL Developer 4.1.5.21). You can confirm this easily with SQL tracing.

As an alternative, the below really sets the the buffer to unlimited:

begin
  dbms_output.enable(null);
end;
/


How to find user who Drop any thing without auditing applied on database In my scenario, I have a database with no flashback on, Only Archivelogs are active. No Auditing applied on database. 3 days ago some one drop all triggers, views, sequence from a specific user. Now is there any way to find out who did this? Can I go 3 days back without flashback on?

Please try it:

Select * from user_audit_object

And for restore dropped table , you should know , that drop did without purge command.
If he/she used purge you can just restore your object just with dump or flashback and if he/she didn't use purge you can use this select * from dba_recyclebin where orginal_name=<object_name>, if the select has result you can restore that and otherwise just falshback or restore backup


ORA-01031: insufficient privileges ALTER PLUGGABLE DATABASE to OPEN I'm currently working with Oracle database version 12c. I have created a common admin user - "C##ADMIN" with DBA Role. But when trying to execute the following script:

ALTER PLUGGABLE DATABASE TESTPDB OPEN;

I hit error:

  ORA-00990: missing or invalid privilege

How should I assign the correct roles/privileges to "C##ADMIN" in order to run script above?

Just grant SYSDBA/DBA privilege to the user to all containers and login as SYSDBA to perform the task.

Demo:

SQL> create user c##admin identified by c##admin container=all;

User created.

SQL> grant sysdba to c##admin container=all;

Grant succeeded.

SQL> conn c##admin as sysdba
Enter password:
Connected.
SQL> alter pluggable database orclpdb open;


SELECT MIN(id) id,
       ParentId,
       ProductId,
       SUM(Quantity) Quantity
FROM [source_table_name]
GROUP BY ParentId,
         ProductId


Cannot get geometry object from data you send to the GEOMETRY field The following simple test returns an error

CREATE TABLE track (
  position point NOT NULL
);
INSERT INTO track VALUES (
  position=ST_PointFromText('POINT(-45.62390335574153 -3.9551761173743847)')
);

Error:

  ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field

What am I doing wrong?

Wrong syntax...  Instead of

INSERT INTO track VALUES (
    position=ST_PointFromText(...)
);

use

INSERT INTO track SET
    position=ST_PointFromText(...);

Reference: https://dev.mysql.com/doc/refman/5.7/en/insert.html


Locating all records relevant for a specific date I have a MySQL InnoDB table with about 1GB of records and growing.
each record in this table has, among other fields two date fields :

AddedDateRecord - The date the record was added (datetime).
LastUpdatedRecord - The date the record was last updated (datetime).

The problem is that if i want to find all records relevant for a single date i have to use 3 "OR" statements. assuming "date" is the date I need :

AddedDateRecord >= date:00:00:00 AND AddedDateRecord <= date:23:59:59

OR

LastUpdatedRecord >= date:00:00:00 AND LastUpdatedRecord <= date:23:59:59

OR

AddedDateRecord <= date:00:00:00 AND LastUpdatedRecord >= date:00:00:00

I'm sure you can see the performance hit for using these 3 ORs in a Query on a 1GB records table;

I'm looking for a solution that does not involve moving away from MySQL.

Any help would be greatly appreciated.

Thanks

No INDEX can handle that OR.  Nor can any combination of indexes.

UNION can help with the first 2 range tests, but it won't help with the 3rd.

The 3rd range may include one-fourth of the table.

Is there any limit on the time between Adding and Updating?  We might be able to use a partitioning or spatial trick with that information.

Barring that, I recommend a UNION DISTINCT of the 3 tests, together with these two indexes:

INDEX(AddedDateRecord, LastUpdateDateRecord)  -- for Q1 and sometimes Q3
INDEX(LastUpdateDateRecord, AddedDateRecord)  -- for Q2 and sometimes Q3

The UNION is needed because MySQL is not good with ORs.

The DISTINCT is important because you can get dups.  To be faster, change to ALL and worry about the case where both columns are exactly date:00:00:00 -- where all 3 tests succeed.

Don't trust what you find for LIMIT, unless there is a significant pattern to the order how the rows were inserted.  Also, we need to see the PRIMARY KEY.


Getting database sizes on an MSSQL Server named instance I need to get a list of database sizes for a SQL server migration project.  The SQL Server server we're migrating from has three installed instances, one default and and two named.  It's running SQL Server 2008 R2, on Server 2008 R2, if it matters.

To get this list, I dusted off the old T-SQL "script" I used at my last position as the defacto/accidental DBA, and it works as expected on the default instance, but returns no data on either of the named instances.

SELECT [Database Name] = DB_NAME(database_id),

   [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'

                 WHEN Type_Desc = 'LOG'  THEN 'Log File(s)'

                 ELSE Type_Desc END,

   [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )

FROM   sys.master_files

GROUP BY      GROUPING SETS

          (

                 (DB_NAME(database_id), Type_Desc),

                 (DB_NAME(database_id))

          )

ORDER BY      DB_NAME(database_id), Type_Desc DESC

GO

Can someone tell me why (and how to fix it), or suggest a better solution?

(It's gotta be the FROM sys.master_files bit, because what else could it be, but I'm not sure what I should be using instead on a named instance.)

EDIT:  Below is what I'm seeing on my named instances, anyway... so if this code does/should work on named instances, thoughts on why it doesn't work on these would be much appreciated.

If you just need the size of the entire database then below script will help you :

SELECT  @@servername as server_name,
        d.name,
        CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00)) Size_MBs
FROM    sys.master_files mf
        INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE   d.database_id > 4 -- Skip system databases
AND     d.state_desc = 'ONLINE'-- ONLY get size for ONLINE database.
GROUP BY d.name
ORDER BY d.name

If you want to automate your migration of databases from one server to another, I highly recommend using sqlmigration - a powershell script that will migrate your databases, jobs, logins, etc from one server to another. Also, be aware of the pre and post migration steps if you are moving from one version of sql server to higher (2012 or 2014) version.

Depending on your size of databases and the amount of downtime, there are ways that you can implement to minimize the downtime for your entire migration - e.g. using logshipping or mirroring and then during cutover, just failover to the new server, etc.

Always prepare and test your migration and rollback procedures.

Edit: From your screenshot, it seems that you do not have permissions. Can you just do a select * from sys.master_files


Same script produces 'Specified key was too long' on one server and not on another Senario: I have a CMS with a database .sql file.

When I try to load the .sql file on my local phpMyAdmin, its successful, it loads all the tables into phpMyAdmin.
When I try to load the same file on Real CMS host(Network Solutions) phpMyAdmin, it is not loading the tables.

The error I get on the Real CMS Server is:

  Specified key was too long; max key length is 767 bytes

What could cause the same script to produce this error on one server and not on the other?

There are 2 possible scenarios that can cause this, it's hard to tell which one you are having if we don't see the SQL file you are trying to import.

Maybe your default engine isn't the same on both servers.
If you are loading data in a MyISAM table on your localhost and a InnoDB table on Real CMS this could cause the issue because the maximum prefix is 767 bytes long for InnoDB and 1000 bytes for MyISAM. So if you have a key with a prefix of, lets say, 800 bytes it would load fine on your localhost but not in Real CMS.

It could also be caused by a different server configuration. The max key length for a MyISAM table can be controlled by the innodb_large_prefix system variable.

Please see the documentation for CREATE INDEX which states:

  Prefix support and lengths of prefixes (where supported) are storage
  engine dependent. For example, a prefix can be up to 767 bytes long
  for InnoDB tables or 3072 bytes if the innodb_large_prefix option is
  enabled. For MyISAM tables, the prefix limit is 1000 bytes. The NDB
  storage engine does not support prefixes


ALTER TABLE - Rename a column This is driving me nuts!

I want to rename a column from read-more to read_more in my blog table

I tried all this:

  ALTER TABLE blog RENAME COLUMN read-more to read_more;
  ALTER TABLE blog CHANGE COLUMN 'read-more' 'read_more' VARCHAR(255) NOT NULL;

And I always get this!

ERROR 1064 (42000): 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 'COLUMN read-more to read_more' at line 1

I'm using MySQL 5.5

The valid syntax is close to your second try, but you need to escape the column names with backticks not with single quotes:

ALTER TABLE `blog` CHANGE COLUMN `read-more` `read_more` VARCHAR(255) NOT NULL;


Users and Roles in MongoDB Following users are created in MongoDB.

First user created with name "user" having read write role for "project" database use project.

db.createUser(
    {
      user: "user",
      pwd: "user123",
      roles: [
         { role: "readWrite", db: "project" },
         { role: "readAnyDatabase", db:"admin" }
      ]
    }
)

Second user created with name "stud" having read write role for "student" database use student.

db.createUser(
    {
      user: "stud",
      pwd: "stud123",
      roles: [
         { role: "readWrite", db: "stud" },
         { role: "readAnyDatabase", db:"admin" }
      ]
    }
)

When I connected to database having name project using "user" I am getting the project as well as the student database and I'm also able to create new collection in the student database. I haven't give any permission to "user" to access the student database.

Still it is possible to access the student database. How can I avoid this?

You've given 'user'  the readAnyDatabase permission.
This means it has read access for every database in the cluster.
Change the permission to readOnly so user will have read permission on admin DB only.


Data truncated for column - return fatal error I get this error in my error log.
Warning: 1265 Data truncated for column ...

How to configure mysql to return a fatal error on this kind of warnings?
Mysql 5.6.27
cnf
sql_mode
STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Probably you need to upgrade to 5.7 or MariaDB to get STRICT_TRANS_TABLES to give you an error (and abort the transaction) for that situation.

https://mariadb.com/kb/en/mariadb/sql_mode/
http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html


How to get all messages raised when a statement fails Executing the below query will generate two error messages

ALTER TABLE [MFClassProperty] DROP CONSTRAINT [FK_MFClassProperty_MFProperty]

Error messages are:

  Msg 3728, Level 16, State 1, Line 3 'FK_MFClassProperty_MFProperty' is
  not a constraint. Msg 3727, Level 16, State 0, Line 3 Could not drop
  constraint. See previous errors.

If I use a try..catch block, then it shows only the last message.

Why isn't it moving to the catch block on the first error?
Is there any way to get all error messages?

This is sample situation.

This is expected behavior with TRY/CATCH in T-SQL.  The related Connect bug report was closed as "won't fix":

You'll need to use a client application (e.g. SQLCMD, PowerShell) to capture all error messages.


Best way to backup & truncate transaction logs on a schedule I'm no DBA, but things being what they are, I have to wear the DBA hat and set up maintenance plans on my SQL Server instance.

So for a while I've been having my SSIS overnight process run a Execute SQL Task to perform the backups - basically running master.dbo.xp_create_subdir to ensure the destination folders exist, and then BACKUP DATABASE [DbName] TO  DISK = 'G:\Backups\DbName\DbName.bak' WITH INIT.

Whenever that task failed, the rest of the process would abort and I'd get a notification, and come in the next morning to notice the drive for transaction logs was filled to capacity, and so I'd manually truncate them and move on... until the story repeated itself and the transaction logs outgrew the available disk space again.

The "manual truncate" script looks like this:

use Staging;
alter database Staging set recovery simple
alter database Staging set recovery full
dbcc shrinkfile ('Staging_log', 0, truncateonly);
go

So I'm growing tired of this, and I decided to try and do things properly instead, and follow the steps here and create an actual maintenance plan:

Thing is, I've never done this before, so I have a few questions:

Will backing up the transaction logs like this automatically truncate them, or there's something else I need to do?
Is it okay to run data and transaction log backups concurrently? If not, then what's a proper way of doing this?
The backup files are being picked up overnight by another process that grabs all the files on the server and stores them elsewhere - would it be a good idea to expire the backup set after 2 days? Do I need to make them expire at all?
Cleanup tasks respectively remove "old" .bak and .trn files under the subfolders of G:\Backups. Does that make sense?
Would it be better to do this in SSIS, so I can fail my ETL if/when the backups fail? Or should my ETL process even care?

Sorry if this is too many questions for one post, if needed I'll edit and ask multiple questions instead - I think they're all tightly related though.

Only overnight SSIS is making writes, daytime is all reads - I only need daily recovery.

You should choose your recovery model based on your business needs :

How much data business can loose and at the same time survive ?

Based on the above answer, you should carefully choose your database recovery model.

In simple terms (not discussing bulk logged recovery model),

A full recovery model allows log backups that allows point-in-time recovery.

Log truncation can occur when you take transaction log backups i.e. Log file space will be reused after each log backup and wont bloat !

A Simple recovery model only allows you to take FULL backups. Point-in-time recovery is not possible.

Log truncation can only occur when  a checkpoint occurs (manually or automatic) i.e since you do regular full backups, you do not have to worry about transaction log as CHECKPOINT will take care of reusing the inactive portion of the log file.

Remember that log truncation is NOT physical reduction in size of transaction log file It means that the inactive portion of the transaction log file is marked as reusable.

Hence you should properly presize your transaction log file (and data files). Growing the log file will kick in autogrowth events (if your database is set to autogrow as a last resort). Check my answer - Autogrowth - Percentage Use?

I would highly suggest you to ditch maintenance plans and implement [a smart maintenance solution - that is easy, flexible and follows best practices] - 5. - Ola's backup solution (and Index maintenance solution as well).

lets address your questions :

  Will backing up the transaction logs like this automatically truncate them, or there's something else I need to do?

Please do not append backups or set them to expire. They create a big mess. Use INIT and take separate log backups with datetime stamp. Easy to maintain. Use Ola's backup solution for that. The solution is flexible to delete old backups as well.

  Is it okay to run data and transaction log backups concurrently? If not, then what's a proper way of doing this?

A full backup has no effect on a T-log backup. A full backup contains only enough transaction log necessary so that in the event of a restore, the database can be transactionally consistent to the time at which the data reading portion of the full backup completed. Check - how much transaction log a full backup includes ?

Also, a log backup during a full backup will not truncate the transaction log. A (couple of) log backup/s after the full backup finishes will truncate the log.

  The backup files are being picked up overnight by another process that grabs all the files on the server and stores them elsewhere - would it be a good idea to expire the backup set after 2 days? Do I need to make them expire at all?

  Cleanup tasks respectively remove "old" .bak and .trn files under the subfolders of G:\Backups. Does that make sense?
  Would it be better to do this in SSIS, so I can fail my ETL if/when the backups fail? Or should my ETL process even care?

For above both, use Ola's backup maintenance solution. It will take care of deleting old files.


How to union tables into a new table in mysql? I have a table as following:

id col1 col2 col3 type
1  12   12   23   1
2  23   33   1    0
3  333  2    21   1
.....

I try to select 1% sample of where type=1 and all the data where type=0, I wrote the code as following:

 create table tbl2(
    SELECT *
    FROM tbl1
    WHERE type=1 AND RAND() <= 0.01
    UNION
    SELECT *
    FROM tbl1
    WHERE type =0
    );

It ends up as an error :

ERROR 1064 (42000): 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 'UNION
SELECT *
FROM tbl1
WHERE type =0
)' at line 3

Anyone can help me?

I just loaded this into MySQL 5.5.37 on my Windows Laptop
use test
drop table if exists tbl1;
create table tbl1
(id int not null,
col1 int not null,
col2 int not null,
col3 int not null,
type int not null);
insert into tbl1 values
(1,  12,   12,   23,   1),
(2,  23,   33,   1,    0),
(3,  333,  2,    21,   1);

Then, I tried out the query
mysql> SELECT *
    -> FROM tbl1
    -> WHERE type=1 AND RAND() <= 0.01
    -> UNION
    -> SELECT *
    -> FROM tbl1
    -> WHERE type =0 ;
+----+------+------+------+------+
| id | col1 | col2 | col3 | type |
+----+------+------+------+------+
|  2 |   23 |   33 |    1 |    0 |
+----+------+------+------+------+
1 row in set (0.00 sec)

mysql>

Your syntax is just fine !!!
Your error message has UNION SELECT * SELECT * as the problem. Take a closer look at the message:
ERROR 1064 (42000): 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 'UNION
SELECT *
SELECT *
FROM tbl1
WHERE type =0
)' at line 3

I see SELECT * twice and a trailing right parenthesis that you did not type in the query.
If your version of MySQL consistently produces that error message, then you must have a bug within in the Query Parser. You may need to upgrade MySQL as a last resort.
SUGGESTION
Your CREATE TABLE will not have any indexes.
Try creating the table with indexes and loading afterwards
create table tbl2 like tbl1;
insert into tbl2
    SELECT *
    FROM tbl1
    WHERE type=1 AND RAND() <= 0.01
    UNION
    SELECT *
    FROM tbl1
    WHERE type =0
;


Issuing Inserts/Updates to Progress OpenEdge via ODBC I'm attempting to build some ETL to integrate our SQL Server environment with a remotely hosted 3rd party app running Progress OpenEdge. I have a linked server to it set up in SQL Server 2014.
Selects are working well enough, though I find that using openquery is much, much faster than the simpler select * from SERVER.DB.SCHEMA.TABLE syntax.
I'm struggling with writes, however. I've found several references for valid update syntax for linked servers, but these all give permissions errors:
select * from openquery(SERVER,'update FOO.BAR set "COL" = 0 where "COL" = 1')

update fb set [COL] = 0
   from SERVER.DB.FOO.BAR fb
   where [COL] = 1

update openquery(SERVER,'select * from FOO.BAR where "COL" = 1')
       set [COL] = 0

This syntax is the only one I've found that works:
declare @script nvarchar(max) = N'update FOO.BAR set "COL" = 0 where "COL" = 1'
execute (@script) at SERVER

This works for both inserts and updates, but I can't see a way to load a set of data this way, because I can't reference my local tables.
In SSIS, an ODBC connection passes the connectivity test, but fails during initialization:

Error: 0xC0014020 at Package1, Connection manager "SERVER": An ODBC error -1 has occurred.
Error: 0xC0014009 at Package1, Connection manager "SERVER": There was an error trying to establish an Open
Database Connectivity (ODBC) connection with the database server.
Error: 0x20F at Data Flow Task, ODBC Destination [2]: The
AcquireConnection method call to the connection manager SERVER failed with error code 0xC0014009.  There may be
error messages posted before this with more information on why the
AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: ODBC Destination
failed validation and returned error code 0x80004005.
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more
component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task
validation.

Ultimately, I'm searching for a better way to merge/upsert a set of data (anything from a few dozen rows to a couple hundred thousand) without resorting to using RPC to insert/update one row at a time.

Once you've identified which one you need, probably 32 bit version, you'd need to ensure your project is using the appropriate run-time. Right click on your project and select Properties and then navigate to the Debugging tab under the Configuration Properties. After inverting the Run64BitRuntime value, I assume your package will work from within SSDT.


is this dead simple query too slow? I have added and removed a few indexes from my user table and, while testing, didn't see the performance gains I had hoped. As a result, I removed the indexes and tried a simple query to see what my baseline speed is.

With so few records in the table, it seems like this "baseline speed" should be way faster, but I'm not experienced in MySQL performance tuning, so I'm not sure.

My user table (InnoDb) has 4344 records.

If I run select firstName from user; the query takes roughly 0.5 seconds to complete.

Thanks in advance for any insight:

+-----------------------+------------------+------------+---------------+
|         Field         |       Type       | Allow Null | Default Value |
+-----------------------+------------------+------------+---------------+
| userId                | int(10) UNSIGNED | No         |               |
| email                 | varchar(150)     | No         |               |
| password              | varchar(50)      | Yes        |               |
| passwordSalt          | varchar(50)      | Yes        |               |
| firstName             | varchar(50)      | Yes        |               |
| lastName              | varchar(50)      | Yes        |               |
| country               | varchar(50)      | Yes        |               |
| phone1                | varchar(10)      | Yes        |               |
| phone2                | varchar(10)      | Yes        |               |
+-----------------------+------------------+------------+---------------+

The statement you are trying to tune ("select firstName from user;") doesn't have a where clause so it will perform a full table scan.  When firstName is indexed, then a scan of the index will occur.  This will be faster on the basis that the amount of data the engine must read and is substantially less than the entire row.

Most applications are interested in smaller sets of data (i.e., what are the users with a first name of Peter) and not all of the stored data values.  This is where tuning really pays off.  A firstName index would be beneficial to find people with a certain firstName but comes at a cost - inserting, updating and deleting row becomes slower.  You do not want to add indexes indiscriminately.


How do I explicitly define data types for a MongoDB collection? How do I explicitly define data types for a MongoDB collection?

I understand that in Mongo if a collection doesn't exist it will create it when you insert the document, but I know the fields and data types of the collection before hand and I want to create it.
I have created an example collection using:

db.createCollection("log", { capped : true, size : 5242880, max : 5000 } )

According to this document from the MongoDB documentation, there are various data types in MongoDB, but again,
How do I explicitly define data types for a MongoDB collection?

Use the mongoDB document validator. It was introduced in version 3.2 and allow you to validate documents during updates and insertions. It need to be defined when the collection is being created.

You can define the data types for a collection like this:

db.createCollection( "log",
   { validator: { $and:
      [
         { capped: { $type: "bool" } },
         { size: { $type: "int" } },
         { max: { $type: "int" } }
      ]
   }
} )

Document validator support types:

https://docs.mongodb.com/manual/reference/operator/query/type/

Official info about document validator:

https://docs.mongodb.com/manual/core/document-validation/


Select based on list of pairs I have a table with two main columns, id1 and id2:

drop table if exists pairs;
create table pairs (id1 int, id2 int);
insert into pairs values
    (1,2), (1,3), (3,1), (2,4), (5,4), (3,6), (7,8);

I want to select rows which have these pairs [(1,2),(5,4),(3,1)]. This array could have more than 500 items, so how I can write a select query to select those rows?
I am using 'Netezza' database but I need to find the logic to do this, I can figure out how to implement this logic in my database engine.

I would start with:

SELECT id1, id2
FROM pairs
WHERE id1 IN (1,5,3) AND id2 IN (2,4,1)

The '1,5,3' and '2,4,1' can also be results from another query.


How to create a normalized database to manage inventory for a small business? I am writing a Java application to manage the inventory of a small furniture business. I know little about databases, I have a bit of experience with SQL queries and more experience with Java programming, however I have never created a program that requires a database and I do not know how to properly design the database tables for maximum efficiency and no redundancy.

The program requires tables for the items being sold and the customers they will be sold to. The main product being sold is carpets and I expect it to have its own table? Other products like vases, desks, etc. can be under one table because they don't have unique details like the carpet size.

Thus far my database is looking like this:
(Primary and foreign keys are in italics)

Customer:

-CustomerID PK,

-FirstName,

-LastName,

-Address,

-Phone,

-Email

Order:

-OrderID PK,

-CustomerID FK >- Customer.CustomerID

OrderDetails:

-OrderDate,

-SoldPrice,

-OrderDescription,

-InvoiceNumber,

-OrderID FK >- Order.OrderID,

-ProductID FK >- product.ProductID

Product:

-ProductID PK,

-ProductName,

-ProductPrice,

-ProductCode,

-ProductTypeID FK >- ProductType.ProductTypeID

ProductType:

-ProductTypeID PK,

-ProductTypeDesc

CarpetProduct:

-CarpetID PK,

-CarpetName,

-CarpetCode,

-CarpetLength,

-CarpetWidth,

-CarpetDescription,

-ProductID FK >- Product.ProductID

OtherProduct:

-OtherProductID PK,

-OtherProductDescription,

-OtherProductCode,

-ProductID FK >- Product.ProductID

Please help me organize this database by adding, removing, or joining tables and fixing any issues. I know this table is far from being normalized and efficient. Any help would be greatly appreciated. Let me know if further details are required.

All the carpets follow one ID system (starting from 1 to around 4000) and all other items (that are not carpets) follow a separate ID system. So their are two separate codes used depending on whether it's a product or not.

Its a good start.

You probably want to split out the customer table: One for the businesses and one for the people.
On tblCarpets and tblOtherProducts, what about a keyword field? You could do some nifty stuff in Java with that.
Each table could benefit from a create_timestamp, modify_timestamp and, if you've got a way to track users (a tblUsers), created_by and modified_by.
Hide_ind - you might want to hide information from the front end, but keep it on the back end for reporting purposes.
You can get rid of 'tbl' in SQL. Its like saying ATM Machine - the M stands for Machine.
A discounts table, maybe? How would you track price changes?
Do they have their own trucks? How many staff? Do you want to track who sent what where, and how?

A few updates from the comments - I'd create a few tables like this:

create table items (itemID int, Type varchar(255), item_name varchar(255), keyword nvarchar(1000), starting_length int,
create_timestamp datetime, modify_timestamp datetime)

create table prices (item_id int, price float, create_timestamp datetime, modify_timestamp datetime)

create table inventory (itemID int, current_length int, create_timestamp datetime, modify_timestamp datetime)

and for a keyword search, you might (might, i don't know java) use a stored procedure like this:

create procedure fetch_items_by_keyword (@keyword varchar(255))

as

set @keyword = '%' + isnull(@keyword,'') + '%'

select item_id,item_name, type, keyword
from items
where keyword like @keyword

I'd let everything stew in your brain for a while. sleep on it and start making your changes tomorrow.


How to update data with a trigger function and a join I have two relations :

CREATE TABLE relation_a (id_a int PRIMARY KEY, field_1 int);
CREATE TABLE relation_b (id_b int PRIMARY KEY,fk_a int REFERENCES relation_a(id_a), field_1 int);
INSERT INTO relation_a VALUES (1,100), (2,101), (3,102);
INSERT INTO relation_b VALUES (1,1), (2,2), (3,3)

I want to create a trigger which always set :

SET b.field_1 = a.field_1 FROM relation_a a, relation_b b WHERE b.fk_a = a.id_a

I tried something like this :

CREATE OR REPLACE FUNCTION function1() RETURNS trigger AS
$$
BEGIN
    UPDATE relation_b as b
    SET field_1 = a.field_1
    FROM relation_a a
    WHERE a.id_a = new.fk_a;
END
$$
LANGUAGE PLPGSQL;

CREATE TRIGGER trigger1
   AFTER INSERT OR UPDATE
   ON relation_b
   FOR EACH ROW EXECUTE PROCEDURE function1();

If i try to insert or update data in relation_b, i got a very long error message i can't understand :

Can someone explain me the right way ? Thanks a lot

Just SET the value for field_1 BEFORE INSERT OR UPDATE.  Just, make sure you use the alias for the new row of data.

needs to be edited for postgresql syntax

CREATE TRIGGER trigger1
   BEFORE INSERT OR UPDATE
   ON relation_b
   FOR EACH ROW
begin
  -- "new" represent "current row being inserted/updated"
  -- just modify the values BEFORE it is stored in the DB

  select a.field_1
    into new.field_1
  from relation_a a
  where a.id_a = new.fk_a;

  -- add exception handle where "no data is found"
end;


Selecting different values from different columns I need a query to select different values from different columns as shown below:

timestamp          queues           queuesname        groupnumb
23-01-2014         FNG              CGI               15467P
24-02-2014         ACS              GHK               15467P
26-02-2014         ERT              FNG               15467P
01-03-2014         ACS              TYU               15467P
04-05-2014         TYI              GHK               15467P

I need the the row with queues = ACS (latest one has timestamp 01-03-2014) and queuesname = GHK (latest one has timestamp 04-05-2014)

For the above example I need this output:

Min_timestamp      max_timestamp       queues       queuesname    groupn
01-03-2014         04-05-2014          ACS          GHK           15467P

I don't need a hard coded query.

It's not very clear what you are after but here's a query under my understanding that you want to find the last timestamp where queues = 'ACS' and the last timestamp where queuesname = 'GHK' for every groupnumb:

with grp as
  ( select groupnumb, max(timestamp) as timestamp
    from tableX
    where queues = 'ACS'
    group by groupnumb
  union all
    select groupnumb, max(timestamp)
    from tableX
    where  queuesname = 'GHK'
    group by groupnumb
  )
select min(timestamp) as min_timestamp,
       max(timestamp) as max_timestamp,
       groupnumb
from grp
group by groupnumb ;


How do you copy a table from MySqlServer_A to MySqlServer_B? I have a daily task that collects data from 3 MySql Servers and then pushes that aggregate data back out to each of the servers deleting the previous days data. All servers are MySql. I would like to put this into a stored proc.

I have been searching for a way to connect from one MySql Server to another, run a query, next server - rinse and repeat. Besides not finding any examples of a stored procedure that connects to another MySql server, my immediate impression is that this is not really acceptable, nor intended.

I can select into an outfile which creates a CSV. Is there any way to force an Xml document instead? I don't want to have to read into a datatable just to get Xml. Of course, Xml won't work with the Bulkload method, will it?

What are my realistic options? One concern about selecting into an outfile is the use of weird characters: ' ,  \ \\ & @ `` / // etc...how will those characters affect the Bulkload process?

I tend to shy away from the FEDERATED storage engine for three(3) reasons:

It only supports MyISAM
DDL performed on the source table requires manually changing the FEDERATED table design on external services.
Bulk operations against a FEDERATED table can become an instant mightmare !!!

What to do ???

One possibility is to use mysqldump and merge them

EXAMPLE
Assuming all tables have identical structures
Assuming all tables are not using auto_increment IDs
Assuming all tables are InnoDB

On the server that will combine the three tables, create four(4) import databases (one time operation):

CREATE DATABASE ImportDB1;
CREATE DATABASE ImportDB2;
CREATE DATABASE ImportDB3;
CREATE DATABASE ImportCombined;

Import Process Goes Something Like This

mysqldump the three(3) tables

mysqldump -hHOSTIP1 -Pportnumber1 -u... -p... dbname tbname > /root/ImportTable1.sql &
mysqldump -hHOSTIP2 -Pportnumber2 -u... -p... dbname tbname > /root/ImportTable2.sql &
mysqldump -hHOSTIP3 -Pportnumber3 -u... -p... dbname tbname > /root/ImportTable3.sql &
wait

Load the mysqldumps into the separate import databases in parallel:

mysql -hHOSTIP4 -u... -p... -A -DImportDB1 < /root/ImportTable1.sql &
mysql -hHOSTIP4 -u... -p... -A -DImportDB2 < /root/ImportTable2.sql &
mysql -hHOSTIP4 -u... -p... -A -DImportDB3 < /root/ImportTable3.sql &
wait
rm -f /root/ImportTable1.sql &
rm -f /root/ImportTable2.sql &
rm -f /root/ImportTable3.sql &
wait

Next, combine the data.

DROP TABLE IF EXISTS ImportCombined.tbname;
CREATE TABLE ImportCombined.tbname LIKE ImportDB1.tbname;
ALTER TABLE ImportCombined.tbname DISABLE KEYS;
INSERT INTO ImportCombined.tbname SELECT * FROM ImportDB1.tbname;
INSERT INTO ImportCombined.tbname SELECT * FROM ImportDB2.tbname;
INSERT INTO ImportCombined.tbname SELECT * FROM ImportDB3.tbname;
ALTER TABLE ImportCombined.tbname ENABLE KEYS;
ALTER TABLE ImportCombined.tbname ENABLE KEYS;

Now, perform the task you already have in place against the ImportCombined.tbname table.

"You fill in these steps"

Then, when the table is ready to be exported, mysqldump it and copy 2 times:

mysqldump -hHOSTIP4 -Pportnumber4 -u... -p... ImportCombined tbname > /root/ExportTable1.sql
cp /root/ExportTable1.sql /root/ExportTable2.sql &
cp /root/ExportTable1.sql /root/ExportTable3.sql &
mysql -hHOSTIP1 -Pportnumber1 -u... -p... -A -Ddbname < /root/ExportTable1.sql &
mysql -hHOSTIP2 -Pportnumber2 -u... -p... -A -Ddbname < /root/ExportTable2.sql &
mysql -hHOSTIP3 -Pportnumber3 -u... -p... -A -Ddbname < /root/ExportTable3.sql &
wait
rm -f /root/ExportTable[123].sql &

As show from these steps, these things should be shell scripted and perhaps crontab'd.

Here is another maneuver: If the tables are MyISAM, you could copy the MyISAM tables (.frm, .MYD, and .MYI files for each table) in other folders (databases in the eyes of mysqld) instead of doing mysqldumps, and perform similar operations as mentioned before. Make sure no writes to the tables take place during the copy.


Table or dictionary in code on a client for data that's not changed often? Suppose I have a table where data won't be changed frequently. Say, a table "currencies" with a list of currencies which I accept on my website. I'd change it once in a while probably.
What are pros and cons of keeping "currencies" directly in code in a web application, as a dictionary, as opposed to keeping them in a database in a table? How is it usually done?

Any data that can change should not be hard-coded into an app.  Especially if your app already uses a database.

Create a table to store currencies so you can update the list of currencies without needing to rewrite and redistribute the app.

As pointed out by Gareth Lyons in a comment, referential integrity relies on the database being self-contained; that is no references to code outside the database.  As an example, if you conduct transactions, you can maintain a list of valid currencies in a table like this:

CREATE TABLE dbo.Currencies
(
    CurrencyCode varchar(3) NOT NULL
        CONSTRAINT PK_Currencies
        PRIMARY KEY CLUSTERED
    , CurrencyName varchar(100) NOT NULL
);

INSERT INTO dbo.Currencies (CurrencyName, CurrencyCode)
VALUES ('East Caribbean Dollar', 'XCD')
    , ('Argentine Peso', 'ARS')
    , ('Armenian Dram', 'AMD')
    , ('Aruban Florin', 'AWG')
    , ('Australian Dollar', 'AUD')
    , ('Euro', 'EUR')
    , ('Azerbaijanian Manat', 'AZN')
    , ('Bahamian Dollar', 'BSD')
    , ('Bahraini Dinar', 'BHD')
    , ('Taka', 'BDT')
    , ('Barbados Dollar', 'BBD')
    , ('Belarusian Ruble', 'BYN')
    , ('Belize Dollar', 'BZD')
    , ('CFA Franc BCEAO', 'XOF')
    , ('Bermudian Dollar', 'BMD')
    , ('Indian Rupee', 'INR')
    , ('Ngultrum', 'BTN')
    , ('Boliviano', 'BOB');

And a list of transactions in a table list this:

CREATE TABLE dbo.CurrencyTransactions
(
    TransactionID int NOT NULL
        CONSTRAINT PK_CurrencyTransactions
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , CurrencyCode varchar(3) NOT NULL
        CONSTRAINT FK_CurrencyTransactions_CurrencyCode
        FOREIGN KEY
        REFERENCES dbo.Currencies(CurrencyCode)
    , DebitAmount DECIMAL(38,4) NOT NULL
    , CreditAmount DECIMAL(38, 4) NOT NULL
);

Attempting to insert a transaction into the table, whether within your app, or from any other source, will result in the currency code being validated:

INSERT INTO dbo.CurrencyTransactions (CurrencyCode, DebitAmount, CreditAmount)
VALUES ('CAD', 1000000, 0);

Attempting to do the insert above results in this error:

  Msg 547, Level 16, State 0, Line 45
  The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CurrencyTransactions_CurrencyCode". The conflict occurred in database "tempdb", table "dbo.Currencies", column 'CurrencyCode'.


SQL Agent jobs in Basic Availability Group setup I have the following scenario on SQL Server 2016 Standard Edition 13.0.4411.0 (SP1-CU1):

2 database servers (DBNode1 and DBNode2). I have a database in a Basic Availability Group, with DBNode1 being the Primary and DBNode2 being the Secondary. As expected, if I try connecting to the DB on DBNode2, it fails as expected with the "The target database, ..., is participating in an availability group and is currently not accessible for queries" error

I've created a SQL Server Agent Job on DBNode2 only (the Secondary) to perform a dummy UPDATE on the database every minute as a test and expected this to fail as that DB should not be accessible on that Secondary node.

UPDATE dbo.SomeTable SET Field1=GETDATE() WHERE Id = 1

The "Database" option for the job step is set to my DB.
However, it succeeds and has actually performed the operation on the Primary node. (See images 1 and 2 below)

If I change the Job to target the master database, and use the following TSQL command structure, this does then error as I'd expect, being the Secondary node (See images 3 and 4 below).

UPDATE MyDB.dbo.SomeTable SET Field1=GETDATE() WHERE Id = 1

It seems like behind the scenes, despite targetting the local server, this is actually working out that the target DB is in an Availability Group and so connects to the Primary node and runs the command there. Is this normal / documented behaviour?

UPDATE:

Added screenshots to show the setup, to avoid confusion.

1 - original job setup showing the unexpected behaviour

2 - original job history output, showing the successful execution

3 - revised job setup showing the expected behaviour

4 - revised job history output, showing the failed execution as expected

Sadly, I'm going to argue that you're just 'scratching the surface' in terms of some of the complexity associated with SQL Server Agent Jobs and some of the complexities you face with Availability Groups (AGs). And, I say "sadly" because Microsoft has actually done a VERY poor job of addressing these concerns to the point where there's virtually no guidance from them.

So, for example - assume you get this working as expected and then you've got a job set to run at the same time on both servers and it'll automatically run on the current/correct replica (depending upon which is the primary) - great/perfect. Until someone comes in and disables the job or changes the schedule - but only makes that change on ONE server (not both) - and then there's a failover. Now what? (Or, in other words, if it's important that a job run (or NOT run) then it's important that whatever changes are made to the job should be synchronized between one server and the next (in a 2-node AG) and, yet, it's incredibly easy to let jobs like this get out of sync - even when there's only 1 'DBA' managing the servers.

As such, I spewed out a set of blog posts on all of this a while back:
http://sqlmag.com/blog/alwayson-availability-groups-and-sql-server-jobs-part-27-options-and-concerns-more-advanced-dep

The blog posts are VERY MUCH a 'stream of consciousness' sort of affair - and there are a couple of mis-steps in the posts and things are LESS insightful than I'd like. Still, by skimming through those posts, you can gain some great insights (i think) on some of the problems to expect and OPTIONS for working around those (with various pros/cons configured).

Eventually, I'll rewrite that set of posts into something more concise (and more 'wise' - i.e., that benefits from the number of AGs I've worked with since those posts - and all of the changes I've made). That said, here's a rough overview of what I do now to address these details:

I fully use the UDFs that determine whether the replica is the principal or not (either based on db-name or ag-name).
Rather than use the 'job state table' I outlined (as that's too clunky), jobs are either a) run as 'server-level' jobs (backups, server event logs/history maintenance/etc.) or b) assigned a 'disabled category' - meaning the job is/should be disabled (and then my code checks to make sure 'disabled' jobs are disabled and so on) or c) set to a SQL Server Agent Job category where the NAME of the category = the name of the AG they're in - so that when failover happens, code traps said failover and responds on the new primary by enabling all jobs of categoryname = 'ag-name' and doing the opposite on the secondary (if it's still up).
Still running synchronization checks (vs server-level details and jobs) every x minutes - to catch situations where something changes on one server - but not the other. (I don't bother letting CODE try and sync the differences - instead, it just reports that something is different.)


Table and Index fragmentation Is there any way to check whether a table or index has been fragmented?

How to check if table is fragmented or not?

How to check if index is fragmented?

I searched Google but didn't get satisfactory answers. Some points like explain plan, table fragmentation, index rebuild etc I got. But what should be our first step and how should be resolve. How step by step we can move forward to resolve the issue?

You need to use INDEX_STATS view to determine fragmentation.

Firstly, You need to populate this view with an individual index by using :

SQL> analyze index <your_index> validate structure;

and then look at these values ratio and height :

select round((del_lf_rows/lf_rows)*100,2) ratio, height, lf_blks, lf_rows
  from index_stats;

If ratio is more than %20

or height is more than or equal to 4

or  lf_rows is lower than lf_blks,

then you need to rebuild your index against fragmentation.

The blevel column of dba_indexes table is my main point of interest. This gives the number of branch levels (including the root node) for a b-tree index, and the column height is closely related to blevel.

If you have access to MOSC, search for the blevel keyword, especially the subject Index Health. The Oracle Community was also suggesting rebuilding indexes for indexes have blevel more than or equal to 4.


Here is your original query

SELECT i.id, i.uid, i.expiration, u.nick, u.mainpicture
FROM online i join usertable u on i.uid = u.id
order by i.expiration DESC limit 0,12;

Try refactoring the query to coerce the retrieval of 12 rows before doing the JOIN

SELECT i.id, i.uid, i.expiration, u.nick, u.mainpicture FROM
(SELECT * FROM online ORDER BY expiration DESC limit 0,12) i
LEFT JOIN usertable u on i.uid = u.id;

The subquery may be forced to use the expiration index now.

You can also try this one:

SELECT i.id, i.uid, i.expiration, u.nick, u.mainpicture FROM
(SELECT id FROM online ORDER BY expiration DESC limit 0,12) ik
LEFT JOIN online i on ik.uid = u.id;
LEFT JOIN usertable u on ik.uid = u.id;

but you will need redo the expiration index

ALTER TABLE online DROP INDEX expiration;
ALTER TABLE online ADD INDEX expiration (expiration,id);

Give it a Try !!!


How to enforce an "exactly-one-per-group" constraint? I have a table x that I've defined like this:

CREATE TABLE x (
    xid INTEGER NOT NULL PRIMARY KEY,
    yid INTEGER NOT NULL REFERENCES y(yid),
    is_principal BOOLEAN NOT NULL
);

This definition misses one constraint that x must satisfy.  In English, this constraint could be described like this:

  there may be one or more rows with a given value in the yid field, but among them there must always be exactly one whose is_principal field is TRUE1.

I'm looking for a way to enforce this constraint.

(In case it matters, I'm particularly interested in solutions applicable to SQLite3 and PostgreSQL.)

EDIT: Just to be clear, the description above does not preclude the existence of rows in table y whose value of yid is not mentioned at all in table x.  For such values of yid there is no value of xid at all, principal or otherwise.  It is only for those values of yid that appear in table x that there must be one and only one row in table x having is_principal = TRUE.

1 Another way to express the same constraint would be to say that the following two queries should always produce identical outputs:

SELECT DISTINCT yid FROM x                    ORDER BY yid;
SELECT          yid FROM x WHERE is_principal ORDER BY yid;

The problem is similar to this one: How to have a one-to-many relationship with a privileged child?

The "at most one per group" part of the constraint can be solved with a partial index:

CREATE UNIQUE INDEX is_FavoriteChild
  ON x (yid)
  WHERE is_principal  ;

Another way to solve the problem is by removing the is_principal column and add a 3rd table. This doesn't solve the "exactly one" either:

CREATE TABLE x (
    xid INTEGER NOT NULL PRIMARY KEY,
    yid INTEGER NOT NULL REFERENCES y (yid),
    UNIQUE (yid, xid)
);

CREATE TABLE x_principal (
    xid INTEGER NOT NULL,
    yid INTEGER NOT NULL PRIMARY KEY,
    FOREIGN KEY (yid, xid) REFERENCES x (yid, xid)
);

If you want to enforce the "exactly one" restriction using DDL alone, it can be done in Postgres with deferrable constraints (I don't think this is an option in SQLite).

For more details and options, you can see the excellent answer in the SO question by @Erwin: Complex foreign key constraint in SQLAlchemy.

(Editing the answer for the additional detail that not all values of y.yid have to appear in table x. One table is added):

--- This table will hold all values of yid that appear in table x
CREATE TABLE y_x (
    yid INTEGER NOT NULL PRIMARY KEY REFERENCES y (yid),
    --- **no other columns**
    principal_xid INTEGER NOT NULL
);

CREATE TABLE x (
    xid INTEGER NOT NULL PRIMARY KEY,
    yid INTEGER NOT NULL REFERENCES y_x (yid)
                         DEFERRABLE INITIALLY DEFERRED,
    UNIQUE (yid, xid)
);

ALTER TABLE y_x
ADD CONSTRAINT y_principal_x_fk
   FOREIGN KEY (yid, principal_xid)
   REFERENCES x (yid, xid)
   DEFERRABLE INITIALLY DEFERRED;


Saving LZW encoded data into a mySQL database I send LZW compressed data from the client side with JavaScript to the server.
The problem is that the data becomes corrupted after saving it to database.

So my question is, what collation should I use to accomplish that?
(My current one is latin1-default.)

I already checked if the problem arises during the data transfer from client to server and vice versa by sending encoded data to the HTTP server and sending it back (PHP-echo) immediately without processing it. I could decode LZW properly. So it should definitely be a problem with the database.

More information about the schema:
I only have a single table with three columns:

data BLOB -- I also tried TEXT
user_id INT
type VARCHAR

This is how I save the data:

INSERT INTO `svg`.`saved_data` (`user_id`, `data`, `type`)
VALUES ('{$user_id}', '{$data}', '{$type}');

You most likely have codepage issues here, where some parts of the process is interpreting the binary data as text and performing some sort of conversion instead of leaving it well alone. This may not be in your database, it could also be in the server-side code that is performing the inserts/updates and selects, or it could even by your browser that is interfering at one end of the process - so this question may be better suited to StackOverflow. Whether the question belongs here on over on SO, you need to give a lot more detail to get a good answer. Extra detail that would be useful includes:

What datatype(s) are you using to store the information? mySQL has specific types for binary data which have no collation so you should be using them not normal text types - if you are not then this is most likely the cause of the problem you are experiencing.
What language and database bindings are you using for the server-side code?
How are you getting the data into the database? Fully manufactured (ad-hoc) SQL? A parametrised statement? Stored procedure call?
How are you getting the data out again?
How is the data being corrupted? Extra characters added? Characters dropped? Changes to specific characters? Almost complete bejigglement of the data? Providing an example of the input data and the corrupt output may help if you can create a small example (adding massive amounts of data to your question won't be a popular action).

One option to remove the need to worry about such codepage issues is to keep your data in plain ASCII which should result in clean passage in, through & out of almost anything. You can do this by encoding your data using base64 immediately after compression and decoding it immediately before decompression. This has one obvious significant disadvantage though: you are going to increase the size of the stored data by 33%. If you are getting a large compression rate from the input data this may not be a massive issue (if you are getting compressed data at about 25% of the original then you are still getting ~33% after base64).


Distinct with timestamp and miliseconds How can I perform a distinct when the system displays millisecond in my timestamps? I just need the date.

My query is like this:

select distinct orders.client_po,
                orders.order_code,
                orders.created_at,
                carts.checkout_at
            from orders
 left outer join carts on carts.code = orders.cart_id

...carts.checkout_at is the same for all my orders but orders.created_at can be different. Some orders can be created the next day, the next minute and I want to know that but when they are created during the same second I get too much duplicate information.

Format of my dates is timestamp without time zone.

I found a workaround with

select distinct orders.client_po, orders.order_code, to_char(orders.created_at, 'DD-Mon-YYYY') , to_char(carts.checkout_at, 'DD-Mon-YYYY') from orders
left outer join carts on carts.code = orders.cart_id


What options are there for rebuilds of indexes on large tables for a data warehouse? I have a bulk loading process that loads millions of records into a few fact tables within a warehouse. Those tables are primarily clustered on time. The non-clustered indexes are in place for how the data is used to speed up performance.

I typically drop some of the non-clustered indexes to both speed up inserts and to reduce index fragmentation after large data loads. However, this process of dropping and rebuilding is causing a huge amount of time as the data grows.

Example: One table took 2 hours to apply a new non-clustered index on 100m+ rows.

Likewise, if I leave the non-clustered indexes in place, they will increase the inserts 3x to 10x in some cases that force your hand to drop and rebuild.

While it's awesome to drop and rebuild indexes, they don't really pan out that well as data grows in those tables. What options are available to me? Should I bulk up the server with more memory (currently 32GB) and cpu (4 vCPU)? Should I rethink my indexes? Should I find a balance of keeping some indexes on for reorganization versus dropping and rebuilding?

(Note: I don't have enterprise edition.)

I'm thinking my only option is enterprise edition with table partitioning where I can rebuild indexes per partition as opposed to the whole table.

At 4 vCPU you're on the smallest Enterprise Edition, so your cost isn't prohibitive. You will need to keep up to date with the Software Assurance if you want HA on your VM.

I would recommend adding memory to this (assuming its available in the hypervisor) and taking this VM to the maximum that edition of SQL can support (64GB for 2008 R2). That's probably your lowest cost option.

If that doesn't give what you want / need then realistically you are looking at Enterprise Edition and table partitioning to manage the data which isn't being manipulated by the ETL.

If the majority of the data isn't being changed, and it just gets reloaded time after time, then that needs to change as its not going to be sustainable. Only load the data which needs to be reloaded.


Is it possible to dynamically retrieve the number of columns in a view in Oracle? As the title says, is it possible to retrieve the number of columns in a view dynamically?

I'm learning Oracle 11gR2 and I'm checking out the V$ views. One of the things I want to do is output them to file (for various reasons I am unable to spool the document) so I was going to output using java, however I need to know how many columns are in each view - a daunting task with 536 views, and now I'm curious to if I can do this dynamically.

I tried using user_tab_columns but it returns 0 for views.

Note: performance is not key, this is a learning exercise for me, so doing it correctly is more important than doing it quickly (if its even possible)

To spool the columns of the V$ views in Oracle you can use:

select owner, table_name, column_name, data_type, column_id
from dba_tab_columns c
where table_name = 'V_$PARAMETER'
order by owner, table_name, column_id


Copy primary keys/clustered indexes from Prod to DEv/Test env We followed the best practices of creating Primary Keys/Clustered indexes on tables which are actively queried on prod.

Now there is a scenario where we need to get the similar above changes done onto DEV/test. Since we did not expect we might require this on dev, we never documented so as to map similar changes.

Is there a better and faster way of doing this rather than , manually scripting out and creating them henceforth.

Thanks.

For a start unless this was an absolute emergency you have done everything backwards: you should not make changes to production that have not been made in dev/test first to make sure they have the desired effect. Develop, test, then release the changes to production.

Setting that aside: There are a number of tools out there that claim to compare schema, and some try to generate a script to bring one schema into sync with another reference one (though I'd be wary of running such a generated script without a detailed review first!).

http://opendbdiff.codeplex.com/ came up in a quick search though I've not used the project myself. It seems to fit the need you describe. There are also products from RedGate & other SQL related software houses (search for "schema compare") and a tool built in to some editions of Visual Studio which your developers may already have available.


PostgreSQL - Functions-Body-Security I'm thinking about how to hide as many attributes of relations from a user as possible & if functions can help with that.
Example:

The user "dummy_user" should have as little insight as possible.
The table "user" contains user data incl. password. The password should not be accessible for the dummy_user.
The function "does_password_match(user_id, password)" checks if the password matches the user.

My question now: I read in the PostgreSQL doc: "SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that owns it.".  So that means I can create the function does_password_match as admin and it can access the password that way and the user is allowed to execute it with the privileges of the admin?
But the dummy_user can still see the body of the function. So you can't really hide an internal structure this way, right?
And if within this function the password is searched via a SELECT, can the dummy_user then potentially still see the value of the field? And so the password of a user can potentially still be read?

The most secure way is to revoke the select privilege on the columns.
Then the user will never be able to see the contents no matter what. You can't really hide the body of a function, and you can't hide the table structure from a user (e.g. by querying pg_attribute).
But the information that there is a column named "password" doesn't reveal anything the user didn't know beforehand, so I don't consider this a problem.
A password stored in the database should always be encrypted anyway and with a one way encryption, so it can't be decrypted (e.g. SHA-256).
So even in the extremely unlikely case that the user finds a way to work around the privilege system in Postgres, he would only see the encrypted value and couldn't do anything with it.


Find the largest databases on my server I have several hundred databases running on the same MySQL server. How can I get a list sorted by the size of the databases?

If you want to query in the INFORMATION_SCHEMA database I have the following:

Total Storage By Database in MB

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "DataSize",
CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "IndexSize",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Total Size"
FROM (SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,
SUM(XSize) SXSize,SUM(TSize) STSize
FROM (SELECT table_schema DB,data_length DSize,index_length XSize,
data_length+index_length TSize FROM information_schema.tables WHERE
table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA
GROUP BY DB WITH ROLLUP) AA,(SELECT 2 pw) BB ORDER BY (SDSize+SXSize);

Total Storage By Storage Engine

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 2 pw) A ORDER BY TSize;

Total Storage Database and Storage Engine

SELECT IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,
"Storage for All Databases",IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,
CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "DataSize",
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "IndexSize",
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT table_schema,engine,SUM(data_length) DSize,
SUM(index_length) ISize,SUM(data_length+index_length) TSize
FROM information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 2 pw) A ORDER BY TSize;

Please note in all the queries you see a subquery that looks like this: (SELECT 2 pw)

(SELECT 0 pw) present the Database Sizes in Bytes
(SELECT 1 pw) present the Database Sizes in KiloBytes
(SELECT 2 pw) present the Database Sizes in MegaBytes
(SELECT 3 pw) present the Database Sizes in GigaBytes
(SELECT 4 pw) present the Database Sizes in TeraBytes
(SELECT 5 pw) present the Database Sizes in PetaBytes (If you ever run with this, email me)