User:Dispenser/Wrong Extension

From Wikimedia Commons, the free media repository
Jump to: navigation, search
-- 2> /dev/null; date; echo '
/* MIME type mismatches file extension
 * 
 * License: Public domain
 * Run time: 25 minutes <SLOW_OK>
 */
CREATE TEMPORARY TABLE u2815__p.mime_ext (
  img_page INT(8) UNSIGNED NOT NULL PRIMARY KEY
) ENGINE=MyISAM AS 
SELECT page_id AS img_page, img_width, img_height,
  SUBSTRING_INDEX(LOWER(CONVERT(img_name USING latin1)), ".", -1) AS img_ext,
  img_media_type, img_major_mime, img_minor_mime, img_metadata
FROM image
JOIN page ON page_namespace=6 AND page_title=img_name
GROUP BY page_id;

SELECT
  CONCAT("[[:File:", REPLACE(page_title, "_", " "), "]]") AS "File",
  img_ext AS "Ext",
  CONCAT(img_major_mime, "/", img_minor_mime, IF(img_media_type="MULTIMEDIA"," (MULTIMEDIA)", "")) AS "MIME",
  (SELECT COUNT(DISTINCT oi_minor_mime) FROM oldimage WHERE oi_name=page_title AND oi_minor_mime!=img_minor_mime) AS Conflicts,
  (SELECT COUNT(*) FROM globalimagelinks WHERE gil_to=page_title) AS "Global usage"
FROM u2815__p.mime_ext
JOIN page ON page_id=img_page
WHERE NOT (
   (img_ext="djvu" AND img_major_mime="image" AND img_minor_mime="vnd.djvu" AND img_width>0)
OR (img_ext="gif"  AND img_major_mime="image" AND img_minor_mime="gif"  AND img_width>0)
OR (img_ext="jpeg" AND img_major_mime="image" AND img_minor_mime="jpeg" AND img_width>0)
OR (img_ext="jpg"  AND img_major_mime="image" AND img_minor_mime="jpeg" AND img_width>0)
OR (img_ext="flac" AND img_major_mime="audio" AND img_minor_mime="x-flac" AND img_width=0)
OR (img_ext="mid"  AND img_major_mime="audio" AND img_minor_mime="midi" AND img_width=0)
OR (img_ext="wav"  AND img_major_mime="audio" AND img_minor_mime="wav"  AND img_width=0)
OR (img_ext="ogg"  AND img_major_mime="application" AND img_minor_mime="ogg" AND img_media_type="AUDIO" AND img_width=0)
OR (img_ext="ogg"  AND img_major_mime="application" AND img_minor_mime="ogg" AND img_media_type="MULTIMEDIA"
   AND img_width=0 AND img_metadata LIKE "%\"type\";s:4:\"Opus\"%")
OR (img_ext="ogg"  AND img_major_mime="application" AND img_minor_mime="ogg" AND img_media_type="VIDEO" AND img_width>0)
OR (img_ext="oga"  AND img_major_mime="application" AND img_minor_mime="ogg" AND img_width=0)
OR (img_ext="ogv"  AND img_major_mime="application" AND img_minor_mime="ogg" AND img_width>0)
OR (img_ext="opus"  AND img_major_mime="application" AND img_minor_mime="ogg" AND img_media_type="MULTIMEDIA"
   AND img_width=0 AND img_metadata LIKE "%\"type\";s:4:\"Opus\"%")
OR (img_ext="pdf"  AND img_major_mime="application" AND img_minor_mime="pdf" AND img_width>0)
OR (img_ext="png"  AND img_major_mime="image" AND img_minor_mime="png" AND img_width>0)
OR (img_ext="svg"  AND img_major_mime="image" AND img_minor_mime="svg+xml" AND img_width>0)
OR (img_ext="tif"  AND img_major_mime="image" AND img_minor_mime="tiff" AND img_width>0)
OR (img_ext="tiff" AND img_major_mime="image" AND img_minor_mime="tiff" AND img_width>0)
OR (img_ext="xcf"  AND img_major_mime="image" AND img_minor_mime="x-xcf" AND img_width>0)
OR (img_ext="webm" AND img_major_mime="audio" AND img_minor_mime="webm" AND img_width=0)
OR (img_ext="webm" AND img_major_mime="video" AND img_minor_mime="webm" AND img_width>0)
)
ORDER BY 3, 2, 1
;-- ' | mysql -ch commonswiki.labsdb commonswiki_p --html > ~/public_html/logs/wrongext-commonswiki.html; date
See also: Special:MediaStatistics
File Ext MIME Conflicts Global usage
File:BMP-signaling-modulation-attenuates-cerebral-arteriovenous-malformation-formation-in-a-vertebrate-jcbfm2014134x4.oga oga application/ogg 0 0
File:Different-Inward-and-Outward-Conduction-Mechanisms-in-NaVMs-Suggested-by-Molecular-Dynamics-pcbi.1003746.s006.oga oga application/ogg 0 0
File:Different-Inward-and-Outward-Conduction-Mechanisms-in-NaVMs-Suggested-by-Molecular-Dynamics-pcbi.1003746.s007.oga oga application/ogg 0 0
File:Fg019-cradle-to-cradle.oga oga application/ogg 0 2
File:Genome-Wide-Screening-Identified-That-miR-134-Acts-as-a-Metastasis-Suppressor-by-Targeting-Integrin-pone.0087665.s006.oga oga application/ogg 0 0
File:Genome-Wide-Screening-Identified-That-miR-134-Acts-as-a-Metastasis-Suppressor-by-Targeting-Integrin-pone.0087665.s007.oga oga application/ogg 0 0
File:Genome-Wide-Screening-Identified-That-miR-134-Acts-as-a-Metastasis-Suppressor-by-Targeting-Integrin-pone.0087665.s008.oga oga application/ogg 0 0
File:Mercury-Redstone 3 - ground audio - 548-AAG.oga oga application/ogg 0 0
File:Augmented-AMPK-activity-inhibits-cell-migration-by-phosphorylating-the-novel-substrate-Pdlim5-ncomms7137-s12.ogv ogv application/ogg 0 0
File:Bellignies - Grand Prix des Marbriers, 25 août 2015 (B34A).ogv ogv application/ogg 0 0
File:Burst-Feeding-of-Pelagia-noctiluca-ephyrae-on-Atlantic-Bluefin-Tuna-(Thunnus-thynnus)-Eggs-pone.0074721.s002.ogv ogv application/ogg 0 0
File:Cost-and-value-in-healthcare-professionals-education-should-we-consider-a-monopsony-model-jeehp-11-04-abstract-recording.ogv ogv application/ogg 0 0
File:Cultural-competency-in-a-physician-assistant-curriculum-in-the-United-States-a-longitudinal-study-jeehp-11-02-abstract-recording.ogv ogv application/ogg 0 0
File:Developmental-changes-in-attention-to-faces-and-bodies-in-static-and-dynamic-scenes-Movie1.ogv ogv application/ogg 0 0
File:Different-Types-of-Door-Opening-Motions-as-Contributing-Factors-to-Containment-Failures-in-Hospital-pone.0066663.s003.ogv ogv application/ogg 0 0
File:Does-curricular-change-improve-faculty-perceptions-of-student-experiences-with-the-educational-jeehp-11-07-abstract-recording.ogv ogv application/ogg 0 0
File:Evaluation-of-a-continuing-professional-development-training-program-for-physicians-and-physician-jeehp-13-21-abstract-recording.ogv ogv application/ogg 0 0
File:Extremely-durable-biofouling-resistant-metallic-surfaces-based-on-electrodeposited-nanoporous-ncomms9649-s3.ogv ogv application/ogg 0 0
File:Impact-of-Marine-Submergence-and-Season-on-Faunal-Colonization-and-Decomposition-of-Pig-Carcasses-pone.0149107.s002.ogv ogv application/ogg 0 0
File:Incentive-structure-in-team-based-learning-graded-versus-ungraded-Group-Application-exercises-jeehp-11-06-abstract-recording.ogv ogv application/ogg 0 0
File:Inflammasome-dependent-IL-1β-release-depends-upon-membrane-permeabilisation-cdd2015176x5.ogv ogv application/ogg 0 0
File:Inflammasome-dependent-IL-1β-release-depends-upon-membrane-permeabilisation-cdd2015176x6.ogv ogv application/ogg 0 0
File:Intraciliary-calcium-oscillations-initiate-vertebrate-left-right-asymmetry-NIHMS661880-supplement-3.ogv ogv application/ogg 0 0
File:Intraciliary-calcium-oscillations-initiate-vertebrate-left-right-asymmetry-NIHMS661880-supplement-4.ogv ogv application/ogg 0 0
File:Intraciliary-calcium-oscillations-initiate-vertebrate-left-right-asymmetry-NIHMS661880-supplement-5.ogv ogv application/ogg 0 0
File:Intraciliary-calcium-oscillations-initiate-vertebrate-left-right-asymmetry-NIHMS661880-supplement-6.ogv ogv application/ogg 0 0
File:Intraciliary-calcium-oscillations-initiate-vertebrate-left-right-asymmetry-NIHMS661880-supplement-7.ogv ogv application/ogg 0 0
File:Intraciliary-calcium-oscillations-initiate-vertebrate-left-right-asymmetry-NIHMS661880-supplement-8.ogv ogv application/ogg 0 0
File:Lillers - Grand Prix de Lillers-Souvenir Bruno Comini, 6 mars 2016 (D12A).ogv ogv application/ogg 0 0
File:Network-analysis-of-time-lapse-microscopy-recordings-Video1.ogv ogv application/ogg 0 0
File:Physical-therapy-students-perceptions-of-team-based-learning-in-gross-anatomy-using-the-Team-Based-jeehp-11-01-abstract-recording.ogv ogv application/ogg 0 0
File:Prononciation glamping.ogv ogv application/ogg 0 0
File:Prononciation greeter.ogv ogv application/ogg 0 0
File:Relevance-of-the-test-content-of-the-Korean-Nursing-Licensing-Examination-to-nursing-job-jeehp-13-23-abstract-recording.ogv ogv application/ogg 0 0
File:Simple-Display-System-of-Mechanical-Properties-of-Cells-and-Their-Dispersion-pone.0034305.s002.ogv ogv application/ogg 0 0
File:Small-group-effectiveness-in-a-Caribbean-medical-schools-problem-based-learning-sessions-jeehp-11-05-abstract-recording.ogv ogv application/ogg 0 0
File:Sonification-of-Arm-Movements-in-Stroke-Rehabilitation-–-A-Novel-Approach-in-Neurologic-Music-Audio 2.ogv ogv application/ogg 0 0
File:Sonification-of-Arm-Movements-in-Stroke-Rehabilitation-–-A-Novel-Approach-in-Neurologic-Music-Audio 3.ogv ogv application/ogg 0 0
File:Speed-Dependent-Modulation-of-the-Locomotor-Behavior-in-Adult-Mice-Reveals-Attractor-and-Video1.ogv ogv application/ogg 0 0
File:Speed-Dependent-Modulation-of-the-Locomotor-Behavior-in-Adult-Mice-Reveals-Attractor-and-Video3.ogv ogv application/ogg 0 0
File:Speed-Dependent-Modulation-of-the-Locomotor-Behavior-in-Adult-Mice-Reveals-Attractor-and-Video4.ogv ogv application/ogg 0 0
File:Speed-Dependent-Modulation-of-the-Locomotor-Behavior-in-Adult-Mice-Reveals-Attractor-and-Video6.ogv ogv application/ogg 0 0
File:Speed-Dependent-Modulation-of-the-Locomotor-Behavior-in-Adult-Mice-Reveals-Attractor-and-Video7.ogv ogv application/ogg 0 0
File:Sustainably-powering-wearable-electronics-solely-by-biomechanical-energy-ncomms12744-s2.ogv ogv application/ogg 0 0
File:The-AERO-System-A-3D-Like-Approach-for-Recording-Gene-Expression-Patterns-in-the-Whole-Mouse-Embryo-pone.0075754.s002.ogv ogv application/ogg 0 0
File:The-AERO-System-A-3D-Like-Approach-for-Recording-Gene-Expression-Patterns-in-the-Whole-Mouse-Embryo-pone.0075754.s006.ogv ogv application/ogg 0 0
File:The-job-analysis-of-Korean-nurses-as-a-strategy-to-improve-the-Korean-Nursing-Licensing-Examination-jeehp-13-24-abstract-recording.ogv ogv application/ogg 0 0
File:Snowflake - Like Music (Renaissance).oga oga application/ogg (MULTIMEDIA) 0 0
File:MR-73 depart.ogg ogg application/ogg (MULTIMEDIA) 0 0
File:Mar Portuguez recitado.ogg ogg application/ogg (MULTIMEDIA) 0 2
File:Pronounciation of Shpend.ogg ogg application/ogg (MULTIMEDIA) 0 1
File:Sediqeh Dowlatabadi (pronunciation).ogg ogg application/ogg (MULTIMEDIA) 0 2
File:140304 U Detainee's written submission PUBLIC.pdf pdf application/pdf 0 0
File:A imprensa em Goa nos séculos XVI, XVII e XVIII.pdf pdf application/pdf 0 0
File:Administrative Review Boards Decision Memos 2954-3508.pdf pdf application/pdf 0 0
File:Afluencia Diaria 2014 - Sistema de Transporte Colectivo Metro Ciudad de México.pdf pdf application/pdf 0 1
File:Almanak democratico.pdf pdf application/pdf 0 0
File:Cnic 048378.pdf pdf application/pdf 0 0
File:DoD Biometrics Consortium Conference 13 Sep 2007.pdf pdf application/pdf 0 0
File:Gerald Ford Papers- Final Issues for Decision, Army Corps of Engineers- Puerto Rico - Compact of Permanent Union (1)(Gerald Ford Library)(1554455).pdf pdf application/pdf 0 0
File:Igv 1904 3875.pdf pdf application/pdf 0 0
File:Igv 1911 5436.pdf pdf application/pdf 0 0
File:Igv 1913 5640.pdf pdf application/pdf 0 0
File:MV 2010 Rechenschaft SM.pdf pdf application/pdf 0 0
File:MV 2010 Rechenschaft.pdf pdf application/pdf 0 0
File:Verzeichniß einiger Brüderschaften, die im katholischen Franken im Gange sind.pdf pdf application/pdf 0 6
File:Библиотека для чтения 1838 том 29.pdf pdf application/pdf 0 0
File:Биографии российских генералиссимусов и генерал-фельдмаршалов Часть 3 1840.pdf pdf application/pdf 0 0
File:Вестник Европы 1867 02 Гарвард.pdf pdf application/pdf 0 0
File:Государственная дума Второй созыв Стенографические отчеты 1907 Сессия 2 Том 1.pdf pdf application/pdf 0 0
File:Дело 1881 № 12.pdf pdf application/pdf 0 0
File:Земля и люди Всеобщая география Книга 7 Том 12 и 13 1899.pdf pdf application/pdf 0 0
File:Мордовцев Д Л Гайдамачина 1884.pdf pdf application/pdf 0 0
File:Отечественные записки 1848 56 часть 1.pdf pdf application/pdf 0 0
File:Первые четрые века христианства 1840.pdf pdf application/pdf 0 0
File:Русская мысль 1885 Книга 10.pdf pdf application/pdf 0 0
File:Русское слово 1865 05-06 996 с..pdf pdf application/pdf 0 0
File:Сборник императорского исторического общества 091.pdf pdf application/pdf 0 0
File:Собрание актов, относящихся к обозрению истории армянскаго народа Часть 2 1838.pdf pdf application/pdf 0 0
File:Устав частной лечебницы, учрежд. врачем П.Ф. Ивановым и дантистом В.Я. Дубовским в г. Харькове утв. 28 июн. 1903 г..pdf pdf application/pdf 0 0
File:Хозяйственно-экономический обзор Кобелякского уезда Полтавской губернии Василенко В.И. 1895.pdf pdf application/pdf 0 0
File:Чтения в Императорском Обществе Истории и Древностей Российских. 1895. Кн. 3.pdf pdf application/pdf 0 0
File:Aqua Cleaner - Plecostomus Stall - Indian National Championship - WRO - Kolkata 2016-10-23 8872.JPG jpg image/jpeg 0 0
File:CAM00035-1.jpg jpg image/jpeg 0 0
File:Fangruida Brief introduction and achievements.jpg jpg image/jpeg 0 0
File:Fangruida New natural structure composite polypolitism philosophical outline New Natural Rationalism and New Social Rationalism.jpg jpg image/jpeg 0 0
File:Fangruida and New Natural Rationalism and New Social Rationalism.jpg jpg image/jpeg 0 0
File:Fangruida ebook New natural structure composite polypolitism philosophical outline.jpg jpg image/jpeg 0 0
File:From the Balcony at C'era Una Volta Italian Restaurant - panoramio.jpg jpg image/jpeg 0 0
File:Introduction of Fang Ruida and his achievements Professor Fang Ruida Contact Email.jpg jpg image/jpeg 0 0
File:Introduction of Fang Ruida and his achievements Professor Fang Ruida.jpg jpg image/jpeg 0 0
File:Introduction of Fang Ruida and his achievements.jpg jpg image/jpeg 0 0
File:Kleefeld, Hannover, Germany - panoramio (1).jpg jpg image/jpeg 0 0
File:Petanahan, Kebumen Regency, Central Java, Indonesia - panoramio.jpg jpg image/jpeg 0 0
File:Uwe Groß in front of his paintings ape & essence.jpg jpg image/jpeg 0 0
File:Возле Урала - panoramio - Сергей Метик (2).jpg jpg image/jpeg 0 0
File:1 4-dialkylcyclohexene.svg svg image/png 1 0
File:Venetian seside properties in Montenegro 1448.svg svg image/png 1 8
File:Island county test.svg svg image/svg+xml 0 1
File:Maricopa county roads test.svg svg image/svg+xml 0 1
File:Miami-Dade County Florida Test.svg svg image/svg+xml 0 1
File:Mobile county test 101.svg svg image/svg+xml 0 1
File:Mohave County Arizona Test.svg svg image/svg+xml 0 1
File:Sarasota Florida Test 100.svg svg image/svg+xml 0 3
File:Sarasota Florida Test.svg svg image/svg+xml 0 0
File:Stroke-dasharray chromium bug full.svg svg image/svg+xml 0 0
File:Abadilla from Philippines pronunciation (Voice of America).webm webm video/webm 0 1
File:Badonviller - U.S. Marine Band.webm webm video/webm 0 0
File:Budabukka.webm webm video/webm 0 0
File:Clywch Clywch Newyddion Gorau Partir Berfeddwlad Llanrwst 2016.webmsd.webm webm video/webm 0 1
File:Daeth Nadolig fel Arferol Parti Llanrwst 2016.webmsd.webm webm video/webm 0 2
File:Domingo Iturbe Abasolo from Spain pronunciation (Voice of America).webm webm video/webm 0 0
File:Dyma'r dydd y ganed Iesu Llanrwst 2016.webmsd.webm webm video/webm 0 1
File:Egil Aarvik from Norway pronunciation (Voice of America).webm webm video/webm 0 0
File:Entrevista Josep Lluís Carod Rovira (256kbit).webm webm video/webm 0 0
File:Fira del Llibre 2016 Felip Bens - El cas Forlati.webm webm video/webm 0 0
File:Fru Herber.webm webm video/webm 0 0
File:Hörprobe Morgenfreude.webm webm video/webm 0 0
File:Ich geh mit meiner Laterne.webm webm video/webm 0 1
File:Instrumental Oriental Music of India.webm webm video/webm 0 2
File:Jorge Abadia from Panama pronunciation (Voice of America).webm webm video/webm 0 0
File:Leonid Abalkin from Russia pronunciation (Voice of America).webm webm video/webm 0 0
File:Marco Raaphorst 10y 3VOOR12-leader.webm webm video/webm 0 0
File:Obama2009HRCSpeechPart1.webm webm video/webm 0 0
File:Parti Llanfair Deffrown Deffrown Llanrwst 2016.webmsd.webm webm video/webm 0 1
File:Parti'r Berfeddwlad Plygain Llanrwst Pan oedd y Baban Iesu.webmsd.webm webm video/webm 0 1
File:Penjači u V. Paklenici 125422.webm webm video/webm 0 0
File:Sain Hedd Mewn Preseb Llwm.webmsd.webm webm video/webm 0 1
File:Sain Tramwywn Hedd a Robin Llanrwst 2016.webmsd.webm webm video/webm 0 1
File:Sani Abacha from Nigeria pronunciation (Voice of America).webm webm video/webm 0 0
File:Sara Danius announces the Nobel Prize in Literature 2016 02.webm webm video/webm 0 0
File:Silja Opera Kustaanmiekka strait.webm webm video/webm 0 0
File:Tchaikovsky - Otto Klemperer conducts Symphony No. 5.webm webm video/webm 0 2
File:Teetar Bird Calls.webm webm video/webm 0 0